[Home] [Help]
PACKAGE BODY: APPS.EAM_OPERATIONS_JSP
Source
1 package body EAM_OPERATIONS_JSP AS
2 /* $Header: EAMOPSJB.pls 120.10.12020000.4 2012/12/05 12:58:02 somitra ship $
3 $Author: somitra $ */
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'eam_operations_jsp';
6 g_debug_sqlerrm VARCHAR2(250);
7 g_shutdown_type VARCHAR2(30) := EAM_CONSTANTS.G_SHUTDOWN_TYPE;
8 g_supply_type VARCHAR2(30) := EAM_CONSTANTS.G_SUPPLY_TYPE;
9
10
11 -------------------------------------------------------------------------
12 -- Procedure to check whether the handover operation is being
13 -- conducted properly or not
14 -- Bug fix # 2113203 - baroy
15 -- Bug 3133704 - removed l_completed_yn and merged the 2 sql's
16 -------------------------------------------------------------------------
17 procedure handover_validate
18 ( p_wip_entity_id IN NUMBER,
19 p_operation_sequence_number IN NUMBER,
20 p_organization_id IN NUMBER,
21 x_return_stat OUT NOCOPY NUMBER
22 ) IS
23
24 l_op_complete_count NUMBER;
25
26 BEGIN
27 x_return_stat := 1;
28 -- Bug 3133704
29 SELECT count(operation_completed) into l_op_complete_count
30 FROM wip_operation_networks won, wip_operations wo
31 WHERE won.wip_entity_id = p_wip_entity_id
32 AND won.next_operation = p_operation_sequence_number
33 AND won.organization_id = p_organization_id
34 AND wo.wip_entity_id = p_wip_entity_id
35 AND wo.operation_seq_num = won.prior_operation
36 AND wo.organization_id = p_organization_id
37 AND nvl(wo.operation_completed,'N')='N' ;
38
39 IF l_op_complete_count > 0 THEN
40 x_return_stat := 0;
41 END IF;
42
43
44 END handover_validate;
45
46 -- removed procedure charge_resource_validate
47
48 -------------------------------------------------------------------------
49 -- Procedure to check whether the assign employee operation is being
50 -- conducted on a completed or uncompleted operation
51 -- Bug fix # 2113203 - baroy
52 -------------------------------------------------------------------------
53 procedure assign_employee_validate
54 ( p_wip_entity_id IN NUMBER,
55 p_operation_sequence_number IN NUMBER,
56 p_organization_id IN NUMBER,
57 x_return_stat OUT NOCOPY NUMBER
58 ) IS
59
60 l_complete_yn VARCHAR2(1);
61
62 BEGIN
63 select operation_completed
64 into l_complete_yn
65 from wip_operations where
66 wip_entity_id = p_wip_entity_id and
67 operation_seq_num = p_operation_sequence_number and
68 organization_id = p_organization_id;
69
70 IF nvl(upper(l_complete_yn),'N') = 'Y' THEN
71 x_return_stat := 0; -- operation should not be allowed to charge resource/employee
72 -- as it is already completed
73 ELSE
74 x_return_stat := 1; -- operation can be allowed to charge resource/employee
75 END IF;
76 END assign_employee_validate;
77
78
79 -------------------------------------------------------------------------
80 -- Procedure to check whether the operation uncompletion/completion
81 -- is being conducted properly or not
82 -- Bug fix # 2113203 - baroy
83 -------------------------------------------------------------------------
84 procedure complete_uncomplete_validate
85 ( p_wip_entity_id IN NUMBER,
86 p_operation_sequence_number IN NUMBER,
87 p_organization_id IN NUMBER,
88 x_return_stat OUT NOCOPY NUMBER
89 ) IS
90
91 l_completed_yn VARCHAR2(1);
92 l_cur_completed_yn VARCHAR2(1);
93
94
95 BEGIN
96 x_return_stat := 1;
97
98 select operation_completed
99 into l_cur_completed_yn
100 from wip_operations where
101 wip_entity_id = p_wip_entity_id and
102 operation_seq_num = p_operation_sequence_number and
103 organization_id = p_organization_id;
104
105 IF( nvl(upper(l_cur_completed_yn),'N') = 'Y') THEN
106 -- operation being contemplated by user is a uncomplete op. Hence check whether
107 -- all next ops are uncomplet or not
108 FOR cur_operation_record IN (select next_operation from wip_operation_networks where
109 wip_entity_id = p_wip_entity_id and
110 prior_operation = p_operation_sequence_number and
111 organization_id = p_organization_id) LOOP
112
113 SELECT operation_completed INTO
114 l_completed_yn from wip_operations where
115 wip_entity_id = p_wip_entity_id and
116 operation_seq_num = cur_operation_record.next_operation and
117 organization_id = p_organization_id;
118
119 IF nvl(upper(l_completed_yn),'N') = 'Y' THEN
120 x_return_stat := 2; -- some next ops are complete
121 -- error msg : uncomplete them first.
122 END IF;
123 END LOOP;
124 ELSIF( nvl(upper(l_cur_completed_yn),'N') = 'N') THEN
125 -- operation being contemplated by user is a complete op. Hence check whether
126 -- all previous ops have been completed or not
127 FOR cur_operation_record IN (select prior_operation from wip_operation_networks where
128 wip_entity_id = p_wip_entity_id and
129 next_operation = p_operation_sequence_number and
130 organization_id = p_organization_id) LOOP
131
132 SELECT operation_completed INTO
133 l_completed_yn from wip_operations where
134 wip_entity_id = p_wip_entity_id and
135 operation_seq_num = cur_operation_record.prior_operation and
136 organization_id = p_organization_id;
137
138 IF nvl(upper(l_completed_yn),'N') = 'N' THEN
139 x_return_stat := 3; -- some previous ops are still uncomplete
140 -- error msg : complete them first.
141 END IF;
142 END LOOP;
143
144 ELSE
145 -- Proceed to operation completion/uncompletion page
146 x_return_stat := 1;
147 END IF;
148
149 END complete_uncomplete_validate;
150
151
152 --------------------------------------------------------------------------
153 -- A wrapper to the operation completion logic, cache the return status
154 -- and convert it the the message that can be accepted by JSP pages
155 --------------------------------------------------------------------------
156 procedure complete_operation
157 ( p_api_version IN NUMBER := 1.0
158 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
159 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
160 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
161 ,p_record_version_number IN NUMBER := NULL
162 ,x_return_status OUT NOCOPY VARCHAR2
163 ,x_msg_count OUT NOCOPY NUMBER
164 ,x_msg_data OUT NOCOPY VARCHAR2
165 ,p_wip_entity_id IN NUMBER -- data
166 ,p_operation_seq_num IN NUMBER
167 ,p_actual_start_date IN DATE
168 ,p_actual_end_date IN DATE
169 ,p_actual_duration IN NUMBER
170 ,p_transaction_date IN DATE
171 ,p_transaction_type IN NUMBER
172 ,p_shutdown_start_date IN DATE
173 ,p_shutdown_end_date IN DATE
174 ,p_reconciliation_code IN VARCHAR2
175 ,p_stored_last_update_date IN DATE -- old update date, for locking only
176 ,p_qa_collection_id IN NUMBER
177 ,p_vendor_id IN NUMBER := NULL
178 ,p_vendor_site_id IN NUMBER := NULL
179 ,p_vendor_contact_id IN NUMBER := NULL
180 ,p_reason_id IN NUMBER := NULL
181 ,p_reference IN VARCHAR2 := NULL
182 ,p_attribute_category IN VARCHAR2 := NULL
183 ,p_attribute1 IN VARCHAR2 := NULL
184 ,p_attribute2 IN VARCHAR2 := NULL
185 ,p_attribute3 IN VARCHAR2 := NULL
186 ,p_attribute4 IN VARCHAR2 := NULL
187 ,p_attribute5 IN VARCHAR2 := NULL
188 ,p_attribute6 IN VARCHAR2 := NULL
189 ,p_attribute7 IN VARCHAR2 := NULL
190 ,p_attribute8 IN VARCHAR2 := NULL
191 ,p_attribute9 IN VARCHAR2 := NULL
192 ,p_attribute10 IN VARCHAR2 := NULL
193 ,p_attribute11 IN VARCHAR2 := NULL
194 ,p_attribute12 IN VARCHAR2 := NULL
195 ,p_attribute13 IN VARCHAR2 := NULL
196 ,p_attribute14 IN VARCHAR2 := NULL
197 ,p_attribute15 IN VARCHAR2 := NULL
198 ) IS
199
200 l_api_name CONSTANT VARCHAR(30) := 'complete_operation';
201 l_api_version CONSTANT NUMBER := 1.0;
202 l_return_status VARCHAR2(250);
203 l_error_msg_code VARCHAR2(250);
204 l_msg_count NUMBER;
205 l_msg_data VARCHAR2(250);
206 l_err_code NUMBER;
207 l_err_msg VARCHAR2(250);
208 l_err_stage VARCHAR2(250);
209 l_err_stack VARCHAR2(250);
210 l_data VARCHAR2(250);
211 l_msg_index_out NUMBER;
212
213 l_new_status VARCHAR2(30);
214 l_db_status VARCHAR2(30);
215 l_db_last_update_date DATE;
216 l_transaction NUMBER;
217 l_actual_end_date DATE;
218 l_reconciliation_code VARCHAR2(30);
219 l_shutdown_type VARCHAR2(30);
220 l_open_acct_per_date DATE;
221
222 l_act_st_date DATE;
223 l_act_end_date DATE;
224 l_act_duration NUMBER;
225
226
227
228 BEGIN
229 SAVEPOINT complete_workorder;
230
231 l_act_st_date :=p_actual_start_date;
232 l_act_end_date :=p_actual_end_date;
233 l_act_duration :=p_actual_duration;
234
235
236
237 eam_debug.init_err_stack('eam_operations_jsp.complete_operation');
238
239 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
240 p_api_version,
241 l_api_name,
242 g_pkg_name)
243 THEN
244 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
245 END IF;
246
247 IF FND_API.TO_BOOLEAN(p_init_msg_list)
248 THEN
249 FND_MSG_PUB.initialize;
250 END IF;
251
252 x_return_status := FND_API.G_RET_STS_SUCCESS;
253
254 -- check if data is stale or not
255 -- using last_update_date as indicator
256 BEGIN
257 SELECT last_update_date, operation_completed, shutdown_type
258 INTO l_db_last_update_date, l_db_status, l_shutdown_type
259 FROM wip_operations
260 WHERE wip_entity_id = p_wip_entity_id
261 and operation_seq_num = p_operation_seq_num
262 FOR UPDATE;
263
264 IF p_transaction_type = 2 THEN
265 select actual_start_date ,actual_end_date ,actual_duration
266 into l_act_st_date ,l_act_end_date,l_act_duration
267 from eam_op_completion_txns
268 where
269 wip_entity_id = p_wip_entity_id and
270 operation_seq_num = p_operation_seq_num and
271 transaction_type = 1 and
272 last_update_date = (select max(last_update_date)
273 from eam_op_completion_txns
274 where wip_entity_id = p_wip_entity_id and
275 operation_seq_num = p_operation_seq_num and
276 transaction_type = 1);
277
278 END IF;
279
280
281 IF l_db_last_update_date <> p_stored_last_update_date THEN
282 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_WO_STALED_DATA');
283 x_return_status := FND_API.G_RET_STS_ERROR;
284 END IF;
285 IF ( (p_transaction_type = 1 AND l_db_status = 'Y') or
286 (p_transaction_type = 2 and nvl(l_db_status,'N') = 'N' )) THEN
287 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_WO_STALED_DATA');
288 x_return_status := FND_API.G_RET_STS_ERROR;
289 END IF;
290 IF ( not( p_shutdown_start_date is null and p_shutdown_end_date is null) and
291 ( p_shutdown_start_date is null or p_shutdown_end_date is null) ) THEN
292 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_SHUTDOWN_DATE_MISS');
293 x_return_status := FND_API.G_RET_STS_ERROR;
294 END IF;
295 --changed the following if condition as part of bug 5476770
296 IF ( p_shutdown_start_date is not null and p_shutdown_end_date is not null and
297 p_shutdown_end_date > sysdate ) THEN
298 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_SHUTDOWN_DATE_IN_FUTURE');
299 x_return_status := FND_API.G_RET_STS_ERROR;
300 ELSIF ( p_shutdown_start_date is not null and p_shutdown_end_date is not null and
301 p_shutdown_start_date > p_shutdown_end_date ) THEN
302 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_SHUTDOWN_DATE_BAD');
303 x_return_status := FND_API.G_RET_STS_ERROR;
304 END IF;
305 --end of change for bug 5476770
306 IF (l_act_duration < 0) THEN
307 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_COMP_DURATION_BAD');
308 x_return_status := FND_API.G_RET_STS_ERROR;
309 END IF;
310
311 EXCEPTION WHEN NO_DATA_FOUND THEN -- Bug 3133704 .changed WHEN OTHERS to WHEN NO_DATA_FOUND
312 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_OP_NOT_FOUND');
313 x_return_status := FND_API.G_RET_STS_ERROR;
314 END;
315
316
317 /* Fix for Bug 2100416 */
318
319 select nvl(min(period_start_date), sysdate+1)
320 into l_open_acct_per_date
321 from org_acct_periods
322 where organization_id = (select organization_id from wip_discrete_jobs where wip_entity_id = p_wip_entity_id)
323 and open_flag = 'Y';
324
325 if (l_act_st_date is not null) and (l_act_duration is not null) then
326 if (l_act_end_date > sysdate) then
327 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_END_LATER_THAN_TODAY');
328 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
329 end if;
330 /* The following line is commented out for bug no:2728447 */
331 -- if (p_actual_start_date < l_open_acct_per_date) then
332 /*Fix for bug 3235163*/
333 --Previously end date was checked with closed period.Changed that to check transaction_date
334 if (p_transaction_date < l_open_acct_per_date) then
335 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_TRANSACTION_DATE_INVALID');
336 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
337 end if;
338 /*End of fix for bug 3235163*/
339 end if;
340
341 /* End of Fix 2100416 */
342
343 BEGIN
344 l_reconciliation_code := null;
345 if( p_reconciliation_code is not null) then
346 select ml.lookup_code
347 into l_reconciliation_code
348 from mfg_lookups ml -- Fix for Bug 3509465
349 where ml.lookup_type = 'WIP_EAM_RECONCILIATION_CODE'
350 and ml.meaning = p_reconciliation_code;
351 end if;
352 EXCEPTION WHEN NO_DATA_FOUND THEN -- Bug 3133704,changed OTHERS to NO_DATA_FOUND
353 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_RECONCILIATION_CODE_INV');
354 x_return_status := FND_API.G_RET_STS_ERROR; --Bug .
355 END;
356
357 -- if validate not passed then raise error
358 l_msg_count := FND_MSG_PUB.count_msg;
359 IF l_msg_count = 1 THEN
360 eam_execution_jsp.Get_Messages
361 (p_encoded => FND_API.G_FALSE,
362 p_msg_index => 1,
363 p_msg_count => l_msg_count,
364 p_msg_data => l_msg_data, -- removed g_miss_char
365 p_data => l_data,
366 p_msg_index_out => l_msg_index_out);
367 x_msg_count := l_msg_count;
368 x_msg_data := l_msg_data;
369 ELSE
370 x_msg_count := l_msg_count;
371 END IF;
372
373 IF l_msg_count > 0 THEN
374 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
375 RAISE FND_API.G_EXC_ERROR;
376 END IF;
377
378 -------------------------------------------
379 -- how to compute date by interval, how many hours a day???
380 l_actual_end_date := l_act_st_date + (l_act_duration/24);
381
382 begin
383 eam_op_comp.op_comp(
384 -- p_api_version => p_api_version,
385 -- p_init_msg_list => p_init_msg_list,
386 -- p_commit => p_commit,
387 -- p_validation_level => p_validation_level,
388 -- p_validation_only => p_validate_only,
389 -- p_record_version_number => p_record_version_number,
390 -- x_return_status => x_return_status,
391 -- x_msg_count => x_msg_count,
392 -- x_msg_data => x_msg_data,
393 x_err_code => l_err_code,
394 x_err_msg => l_err_msg,
395 p_wip_entity_id => p_wip_entity_id,
396 p_operation_seq_num => p_operation_seq_num,
397 p_transaction_type => p_transaction_type,
398 p_transaction_date => p_transaction_date,
399 p_actual_start_date => l_act_st_date,
400 p_actual_end_date => l_actual_end_date,
401 p_actual_duration => l_act_duration,
402 p_shutdown_start_date => p_shutdown_start_date,
403 p_shutdown_end_date => p_shutdown_end_date,
404 p_reconciliation_code => l_reconciliation_code,
405 p_qa_collection_id => p_qa_collection_id,
406 p_vendor_id => p_vendor_id,
407 p_vendor_site_id => p_vendor_site_id,
408 p_vendor_contact_id => p_vendor_contact_id,
409 p_reason_id => p_reason_id,
410 p_reference => p_reference,
411 p_attribute_category => p_attribute_category,
412 p_attribute1 => p_attribute1,
413 p_attribute2 => p_attribute2,
414 p_attribute3 => p_attribute3,
415 p_attribute4 => p_attribute4,
416 p_attribute5 => p_attribute5,
417 p_attribute6 => p_attribute6,
418 p_attribute7 => p_attribute7,
419 p_attribute8 => p_attribute8,
420 p_attribute9 => p_attribute9,
421 p_attribute10 => p_attribute10,
422 p_attribute11 => p_attribute11,
423 p_attribute12 => p_attribute12,
424 p_attribute13 => p_attribute13,
425 p_attribute14 => p_attribute14,
426 p_attribute15 => p_attribute15
427 );
428 exception when others then
429 fnd_msg_pub.add;
430 end;
431
432 if( l_err_code >0) then
433 -- add_message(p_app_short_name => 'EAM', p_msg_name => l_err_msg);
434 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
435 end if;
436
437 l_msg_count := FND_MSG_PUB.count_msg;
438 IF l_msg_count = 1 THEN
439 eam_execution_jsp.Get_Messages
440 (p_encoded => FND_API.G_FALSE,
441 p_msg_index => 1,
442 p_msg_count => l_msg_count,
443 p_msg_data => l_msg_data, -- removed g_miss_char
444 p_data => l_data,
445 p_msg_index_out => l_msg_index_out);
446 x_msg_count := l_msg_count;
447 x_msg_data := l_msg_data;
448 ELSE
449 x_msg_count := l_msg_count;
450 END IF;
451
452 IF l_msg_count > 0 THEN
453 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
454 RAISE FND_API.G_EXC_ERROR;
455 END IF;
456
457 IF FND_API.TO_BOOLEAN(P_COMMIT)
458 THEN
459 COMMIT WORK;
460 END IF;
461
462 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
463 ROLLBACK TO complete_workorder;
464
465
466 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'eam_operations_jsp.complete_operation',
467 p_procedure_name => EAM_DEBUG.G_err_stack);
468 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
469 WHEN FND_API.G_EXC_ERROR THEN
470 ROLLBACK TO complete_workorder;
471
472
473 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'eam_operations_jsp.complete_operation',
474 p_procedure_name => EAM_DEBUG.G_err_stack);
475 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
476 WHEN OTHERS THEN
477 ROLLBACK TO complete_workorder;
478
479
480 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'eam_operations_jsp.complete_operation',
481 p_procedure_name => EAM_DEBUG.G_err_stack);
482 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
483 END complete_operation;
484
485 ------------------------------------------------------------------------------------
486 -- performing operation handover for jsp pages
487 -- use the column last_update_date for optimistic locking
488 ------------------------------------------------------------------------------------
489 procedure operation_handover
490 ( p_api_version IN NUMBER := 1.0
491 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
492 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
493 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
494 ,p_record_version_number IN NUMBER := NULL
495 ,x_return_status OUT NOCOPY VARCHAR2
496 ,x_msg_count OUT NOCOPY NUMBER
497 ,x_msg_data OUT NOCOPY VARCHAR2
498 ,p_wip_entity_id IN NUMBER -- data
499 ,p_old_op_seq_num IN NUMBER
500 ,p_new_op_seq_num IN NUMBER
501 ,p_description IN VARCHAR2
502 ,p_assigned_department IN VARCHAR2
503 ,p_start_date IN DATE
504 ,p_completion_date IN DATE
505 ,p_shutdown_type IN NUMBER
506 ,p_stored_last_update_date IN DATE -- old update date, for locking only
507 ,p_duration IN NUMBER
508 ,p_reconciliation_value IN VARCHAR2
509 ) IS
510
511 l_api_name CONSTANT VARCHAR(30) := 'operation_handover';
512 l_api_version CONSTANT NUMBER := 1.0;
513 l_return_status VARCHAR2(250);
514 l_error_msg_code VARCHAR2(250);
515 l_msg_count NUMBER;
516 l_msg_data VARCHAR2(250);
517 l_err_code VARCHAR2(250);
518 l_err_stage VARCHAR2(250);
519 l_err_stack VARCHAR2(250);
520 l_data VARCHAR2(250);
521 l_msg_index_out NUMBER;
522
523 l_db_last_update_date DATE;
524 l_actual_start_date DATE;
525 l_actual_end_date DATE;
526 l_completed VARCHAR2(30);
527 l_count NUMBER;
528 l_department_id NUMBER;
529 x_row_id VARCHAR2(250);
530 l_org_id NUMBER;
531 l_old_dept_id NUMBER;
532 l_transaction_id number;
533 l_old_op_duration number;
534 l_new_op_completion_date date;
535 TYPE OpCurType IS REF CURSOR RETURN wip_operations%ROWTYPE;
536 opCur OpCurType;
537 opRow wip_operations%ROWTYPE;
538
539 /* added for calling WO API */
540
541 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
542 l_eam_op_rec EAM_PROCESS_WO_PUB.eam_op_rec_type;
543 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
544 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
545 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
546 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
547 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
548 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
549 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
550 l_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
551 l_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
552 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
553 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
554 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
555 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
556 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
557 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
558 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
559
560 l_out_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
561 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
562 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
563 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
564 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
565 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
566 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
567 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
568 l_out_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
569 l_out_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
570 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
571 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
572 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
573 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
574 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
575 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
576 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
577
578 l_output_dir VARCHAR2(512);
579
580 BEGIN
581 SAVEPOINT operation_handover;
582
583
584 eam_debug.init_err_stack('eam_operations_jsp.operation_handover');
585
586 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
587 p_api_version,
588 l_api_name,
589 g_pkg_name)
590 THEN
591 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
592 END IF;
593
594 IF FND_API.TO_BOOLEAN(p_init_msg_list)
595 THEN
596 FND_MSG_PUB.initialize;
597 END IF;
598
599 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
600
601
602 x_return_status := FND_API.G_RET_STS_SUCCESS;
603
604 -----------------------------------------------------------------
605 -- validation
606 -- check if data is stale or not
607 -- using last_update_date as indicator
608 BEGIN
609 SELECT
610 last_update_date
611 ,operation_completed
612 ,first_unit_start_date
613 ,last_unit_completion_date
614 ,organization_id --
615 ,department_id
616 INTO
617 l_db_last_update_date
618 ,l_completed
619 ,l_actual_start_date
620 ,l_actual_end_date
621 ,l_org_id
622 ,l_old_dept_id
623 FROM wip_operations
624 WHERE
625 wip_entity_id = p_wip_entity_id
626 and operation_seq_num = p_old_op_seq_num
627 FOR UPDATE;
628
629
630 -- checking stuff
631 IF l_db_last_update_date <> nvl(p_stored_last_update_date, l_db_last_update_date) THEN
632 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_OP_STALED_DATA');
633 x_return_status := FND_API.G_RET_STS_ERROR;
634 END IF;
635 IF ( nvl(l_completed, 'N') = 'Y' ) THEN
636 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_HANDOVER_CANT_STATUS_Y');
637 x_return_status := FND_API.G_RET_STS_ERROR;
638 END IF;
639
640 IF (p_start_date > p_completion_date) THEN
641 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_HANDOVER_DATE_BAD');
642 x_return_status := FND_API.G_RET_STS_ERROR;
643 END IF;
644
645
646 select count(*)
647 into l_count from wip_operations
648 where wip_entity_id = p_wip_entity_id and operation_seq_num = p_new_op_seq_num;
649 IF( l_count > 0 ) THEN
650 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_OP_EXISTED');
651 x_return_status := FND_API.G_RET_STS_ERROR;
652 END IF;
653
654 -- Bug 3133704 . removed count for department_code within an org. For a given org id , dept code is unique.
655 select department_id
656 into l_department_id
657 from bom_departments
658 where organization_id = l_org_id
659 and department_code like p_assigned_department;
660 if(l_department_id = l_old_dept_id) then
661 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_HANDOVER_DEPT_SAME');
662 x_return_status := FND_API.G_RET_STS_ERROR;
663 end if;
664
665
666 EXCEPTION WHEN NO_DATA_FOUND THEN
667 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_OP_NOT_FOUND');
668 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
669 END;
670
671 -- if validate not passed then raise error
672 l_msg_count := FND_MSG_PUB.count_msg;
673 IF l_msg_count = 1 THEN
674 eam_execution_jsp.Get_Messages
675 (p_encoded => FND_API.G_FALSE,
676 p_msg_index => 1,
677 p_msg_count => l_msg_count,
678 p_msg_data => l_msg_data, -- removed g_miss_char
679 p_data => l_data,
680 p_msg_index_out => l_msg_index_out);
681 x_msg_count := l_msg_count;
682 x_msg_data := l_msg_data;
683 ELSE
684 x_msg_count := l_msg_count;
685 END IF;
686
687 IF l_msg_count > 0 THEN
688 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
689 RAISE FND_API.G_EXC_ERROR;
690 END IF;
691
692 ---------------------------------------------------
693 -- prepare for DML
694
695
696 -----------------------------------
697 -- DML goes here
698
699 -- keep the data before complete the op, use it to create new op
700 select *
701 into opRow
702 from wip_operations
703 where wip_entity_id = p_wip_entity_id
704 and operation_seq_num = p_old_op_seq_num;
705
706 if l_actual_start_date <= sysdate then --added by akalaval for bug 4162307
707
708 l_old_op_duration := (sysdate - l_actual_start_date)*24;
709
710 complete_operation(
711 x_return_status => x_return_status
712 ,x_msg_count => x_msg_count
713 ,x_msg_data => x_msg_data
714 ,p_wip_entity_id => p_wip_entity_id
715 ,p_operation_seq_num => p_old_op_seq_num
716 ,p_actual_start_date => l_actual_start_date
717 ,p_actual_end_date => sysdate
718 ,p_actual_duration => l_old_op_duration
719 ,p_transaction_date => sysdate
720 ,p_transaction_type => 1
721 ,p_shutdown_start_date => null
722 ,p_shutdown_end_date => null
723 ,p_reconciliation_code => p_reconciliation_value
724 ,p_stored_last_update_date => p_stored_last_update_date
725 );
726
727 else --condition added for handling operation completion in case of
728 --operation start date is in future
729
730 complete_operation(
731 x_return_status => x_return_status
732 ,x_msg_count => x_msg_count
733 ,x_msg_data => x_msg_data
734 ,p_wip_entity_id => p_wip_entity_id
735 ,p_operation_seq_num => p_old_op_seq_num
736 ,p_actual_start_date => sysdate
737 ,p_actual_end_date => sysdate
738 ,p_actual_duration => 0
739 ,p_transaction_date => sysdate
740 ,p_transaction_type => 1
741 ,p_shutdown_start_date => null
742 ,p_shutdown_end_date => null
743 ,p_reconciliation_code => p_reconciliation_value
744 ,p_stored_last_update_date => p_stored_last_update_date
745 );
746
747 end if;
748
749 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
750
751
752 select max(transaction_id) into l_transaction_id
753 from eam_op_completion_txns
754 where wip_entity_id = p_wip_entity_id
755 and operation_seq_num = p_old_op_seq_num;
756
757 update eam_op_completion_txns
758 set handover_operation_seq_num = p_new_op_seq_num
759 where wip_entity_id = p_wip_entity_id
760 and operation_seq_num = p_old_op_seq_num
761 and transaction_id = l_transaction_id;
762
763 l_new_op_completion_date := (p_start_date + p_duration/24);
764 -- create new operation
765 opRow.Operation_Seq_Num := p_new_op_seq_num;
766 opRow.Last_Update_Date := sysdate;
767 opRow.Last_Updated_By := g_last_updated_by;
768 opRow.Last_Update_Login := g_last_update_login;
769 opRow.Creation_Date := sysdate;
770 opRow.Created_By := g_created_by;
771 opRow.Department_Id := l_department_id;
772 opRow.Description := p_description;
773 opRow.First_Unit_Start_Date := p_start_date;
774 opRow.First_Unit_Completion_Date := l_new_op_completion_date;
775 opRow.Last_Unit_Start_Date := p_start_date;
776 opRow.Last_Unit_Completion_Date := l_new_op_completion_date;
777
778 if ((p_shutdown_type is not null) and (p_shutdown_type <> -1)) then
779 opRow.Shutdown_Type := p_shutdown_type;
780 end if;
781
782 opRow.Operation_Sequence_Id := null;
783
784 l_eam_op_rec.wip_entity_id := opRow.Wip_Entity_Id;
785 l_eam_op_rec.operation_seq_num := opRow.Operation_Seq_Num;
786 l_eam_op_rec.organization_id := opRow.Organization_Id;
787 l_eam_op_rec.operation_sequence_id := opRow.Operation_Sequence_Id;
788 /* l_eam_op_rec.standard_operation_id := opRow.Standard_Operation_Id; do not handover std op, commented for bug14032750 */
789 l_eam_op_rec.department_id := opRow.Department_Id;
790 l_eam_op_rec.description := opRow.Description;
791 l_eam_op_rec.start_date := opRow.First_Unit_Start_Date;
792 l_eam_op_rec.completion_date := opRow.Last_Unit_Completion_Date;
793 l_eam_op_rec.count_point_type := opRow.Count_Point_Type;
794 l_eam_op_rec.backflush_flag := opRow.Backflush_Flag;
795 l_eam_op_rec.minimum_transfer_quantity := opRow.Minimum_Transfer_Quantity;
796 l_eam_op_rec.attribute_category := opRow.Attribute_Category;
797 l_eam_op_rec.attribute1 := opRow.Attribute1;
798 l_eam_op_rec.attribute2 := opRow.Attribute2;
799 l_eam_op_rec.attribute3 := opRow.Attribute3;
800 l_eam_op_rec.attribute4 := opRow.Attribute4;
801 l_eam_op_rec.attribute5 := opRow.Attribute5;
802 l_eam_op_rec.attribute6 := opRow.Attribute6;
803 l_eam_op_rec.attribute7 := opRow.Attribute7;
804 l_eam_op_rec.attribute8 := opRow.Attribute8;
805 l_eam_op_rec.attribute9 := opRow.Attribute9;
806 l_eam_op_rec.attribute10 := opRow.Attribute10;
807 l_eam_op_rec.attribute11 := opRow.Attribute11;
808 l_eam_op_rec.attribute12 := opRow.Attribute12;
809 l_eam_op_rec.attribute13 := opRow.Attribute13;
810 l_eam_op_rec.attribute14 := opRow.Attribute14;
811 l_eam_op_rec.attribute15 := opRow.Attribute15;
812 l_eam_op_rec.long_description := opRow.Long_Description;
813 l_eam_op_rec.shutdown_type := opRow.Shutdown_Type;
814
815
816
817
818 l_eam_op_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_CREATE;
819
820
821 l_eam_op_tbl(1) := l_eam_op_rec ;
822
823
824 EAM_PROCESS_WO_PUB.Process_WO
825 ( p_bo_identifier => 'EAM'
826 , p_init_msg_list => TRUE
827 , p_api_version_number => 1.0
828 , p_commit => 'N'
829 , p_eam_wo_rec => l_eam_wo_rec
830 , p_eam_op_tbl => l_eam_op_tbl
831 , p_eam_op_network_tbl => l_eam_op_network_tbl
832 , p_eam_res_tbl => l_eam_res_tbl
833 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
834 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
835 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
836 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
837 , p_eam_direct_items_tbl => l_eam_di_tbl
838 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
839 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
840 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
841 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
842 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
843 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
844 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
845 , p_eam_request_tbl => l_eam_request_tbl
846 , x_eam_wo_rec => l_out_eam_wo_rec
847 , x_eam_op_tbl => l_out_eam_op_tbl
848 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
849 , x_eam_res_tbl => l_out_eam_res_tbl
850 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
851 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
852 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
853 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
854 , x_eam_direct_items_tbl => l_out_eam_di_tbl
855 , x_eam_wo_comp_rec => l_out_eam_wo_comp_rec
856 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
857 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
858 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
859 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
860 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
861 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
862 , x_eam_request_tbl => l_out_eam_request_tbl
863 , x_return_status => x_return_status
864 , x_msg_count => x_msg_count
865 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
866 , p_debug_filename => 'ophandover.log'
867 , p_output_dir => l_output_dir
868 , p_debug_file_mode => 'w'
869 );
870
871 -- copy network relations
872 copy_operation_network(
873 p_wip_entity_id => p_wip_entity_id
874 ,p_old_op_seq_num => p_old_op_seq_num
875 ,p_new_op_seq_num => p_new_op_seq_num
876 ,p_operation_start_date => p_start_date
877 ,p_operation_completion_date => p_completion_date
878 ,x_return_status => x_return_status
879 );
880 END IF;
881
882 -- check error
883 l_msg_count := FND_MSG_PUB.count_msg;
884 IF l_msg_count = 1 THEN
885 eam_execution_jsp.Get_Messages
886 (p_encoded => FND_API.G_FALSE,
887 p_msg_index => 1,
888 p_msg_count => l_msg_count,
889 p_msg_data => l_msg_data,
890 p_data => l_data,
891 p_msg_index_out => l_msg_index_out);
892 x_msg_count := l_msg_count;
893 x_msg_data := l_msg_data;
894 ELSE
895 x_msg_count := l_msg_count;
896 END IF;
897
898 IF (l_msg_count > 0 AND Nvl(x_return_status,'E') <> 'S') THEN
899 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
900 RAISE FND_API.G_EXC_ERROR;
901 END IF;
902
903
904 IF FND_API.TO_BOOLEAN(P_COMMIT)
905 THEN
906 COMMIT WORK;
907 END IF;
908
909 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
910 ROLLBACK TO operation_handover;
911
912 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'eam_operations_jsp.operation_handover',
913 p_procedure_name => EAM_DEBUG.G_err_stack);
914 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
915 WHEN FND_API.G_EXC_ERROR THEN
916 ROLLBACK TO operation_handover;
917
918
919 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'eam_operations_jsp.operation_handover',
920 p_procedure_name => EAM_DEBUG.G_err_stack);
921 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
922 WHEN OTHERS THEN
923 ROLLBACK TO operation_handover;
924
925
926 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'eam_operations_jsp.operation_handover',
927 p_procedure_name => EAM_DEBUG.G_err_stack);
928 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
929 g_debug_sqlerrm := SQLERRM;
930
931 END operation_handover;
932
933
934 -----------------------------------------------------------------------------------------
935 -- copy the operation network data for the new operation
936 -----------------------------------------------------------------------------------------
937
938 procedure copy_operation_network
939 (
940 p_wip_entity_id IN NUMBER -- data
941 ,p_old_op_seq_num IN NUMBER
942 ,p_new_op_seq_num IN NUMBER
943 ,p_operation_start_date IN DATE
944 ,p_operation_completion_date IN DATE
945 ,x_return_status OUT NOCOPY VARCHAR2
946 ) IS
947
948 l_actual_end_date DATE;
949 l_completed VARCHAR2(30);
950 l_actual_start_date DATE;
951
952
953 -- cursor to copy: xxx-> newop
954
955 CURSOR nxtOpCur IS
956 SELECT prior_operation
957 ,next_operation
958 ,wip_entity_id
959 ,organization_id
960 ,created_by
961 ,creation_date
962 ,last_updated_by
963 ,last_update_date
964 ,last_update_login
965 ,attribute_category
966 ,attribute1
967 ,attribute2
968 ,attribute3
969 ,attribute4
970 ,attribute5
971 ,attribute6
972 ,attribute7
973 ,attribute8
974 ,attribute9
975 ,attribute10
976 ,attribute11
977 ,attribute12
978 ,attribute13
979 ,attribute14
980 ,attribute15
981 FROM wip_operation_networks
982 WHERE wip_entity_id = p_wip_entity_id
983 AND next_operation = p_old_op_seq_num;
984
985 -- cursor to copy new op --> xxx
986
987
988 CURSOR prvOpCur IS
989 SELECT prior_operation
990 ,next_operation
991 ,wip_entity_id
992 ,organization_id
993 ,created_by
994 ,creation_date
995 ,last_updated_by
996 ,last_update_date
997 ,last_update_login
998 ,attribute_category
999 ,attribute1
1000 ,attribute2
1001 ,attribute3
1002 ,attribute4
1003 ,attribute5
1004 ,attribute6
1005 ,attribute7
1006 ,attribute8
1007 ,attribute9
1008 ,attribute10
1009 ,attribute11
1010 ,attribute12
1011 ,attribute13
1012 ,attribute14
1013 ,attribute15
1014 FROM wip_operation_networks
1015 WHERE wip_entity_id = p_wip_entity_id
1016 AND prior_operation = p_old_op_seq_num;
1017
1018
1019
1020
1021 BEGIN
1022 -- copy: xxx-> newop
1023
1024 x_return_status := FND_API.G_RET_STS_SUCCESS;
1025
1026 FOR nxtOpCurVar IN nxtOpCur LOOP
1027
1028
1029 BEGIN
1030
1031 IF (nxtOpCurVar.prior_operation IS NOT NULL) THEN
1032
1033 SELECT
1034 last_unit_completion_date,
1035 operation_completed
1036 INTO
1037 l_actual_end_date,
1038 l_completed
1039 FROM wip_operations
1040 WHERE
1041 wip_entity_id = p_wip_entity_id
1042 AND operation_seq_num = nxtOpCurVar.prior_operation;
1043
1044 END IF;
1045
1046 IF (p_operation_start_date < l_actual_end_date) THEN
1047 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_HANDOVER_INV_START_DATE');
1048 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1049 END IF;
1050
1051 IF NVL(l_completed,'N') = 'N' THEN
1052 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_INVALID_COMPLETE_OP2');
1053 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1054 END IF;
1055
1056
1057 EXCEPTION WHEN NO_DATA_FOUND THEN -- Bug 3133704, others -> no_data_found
1058 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_HANDOVER_OP_NOTFOUND');
1059 x_return_status := FND_API.G_RET_STS_ERROR;
1060 END;
1061
1062 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1063
1064 nxtOpCurVar.next_operation := p_new_op_seq_num;
1065 nxtOpCurVar.last_updated_by := FND_GLOBAL.user_id;
1066 nxtOpCurVar.last_update_login := FND_GLOBAL.user_id;
1067 nxtOpCurVar.created_by := FND_GLOBAL.user_id;
1068 nxtOpCurVar.last_update_date := sysdate;
1069 nxtOpCurVar.creation_date := sysdate;
1070
1071 INSERT INTO wip_operation_networks
1072 ( prior_operation
1073 ,next_operation
1074 ,wip_entity_id
1075 ,organization_id
1076 ,created_by
1077 ,creation_date
1078 ,last_updated_by
1079 ,last_update_date
1080 ,last_update_login
1081 ,attribute_category
1082 ,attribute1
1083 ,attribute2
1084 ,attribute3
1085 ,attribute4
1086 ,attribute5
1087 ,attribute6
1088 ,attribute7
1089 ,attribute8
1090 ,attribute9
1091 ,attribute10
1092 ,attribute11
1093 ,attribute12
1094 ,attribute13
1095 ,attribute14
1096 ,attribute15
1097 ) VALUES
1098 ( nxtOpCurVar.prior_operation
1099 ,p_new_op_seq_num
1100 ,nxtOpCurVar.wip_entity_id
1101 ,nxtOpCurVar.organization_id
1102 ,nxtOpCurVar.created_by
1103 ,nxtOpCurVar.creation_date
1104 ,nxtOpCurVar.last_updated_by
1105 ,nxtOpCurVar.last_update_date
1106 ,nxtOpCurVar.last_update_login
1107 ,nxtOpCurVar.attribute_category
1108 ,nxtOpCurVar.attribute1
1109 ,nxtOpCurVar.attribute2
1110 ,nxtOpCurVar.attribute3
1111 ,nxtOpCurVar.attribute4
1112 ,nxtOpCurVar.attribute5
1113 ,nxtOpCurVar.attribute6
1114 ,nxtOpCurVar.attribute7
1115 ,nxtOpCurVar.attribute8
1116 ,nxtOpCurVar.attribute9
1117 ,nxtOpCurVar.attribute10
1118 ,nxtOpCurVar.attribute11
1119 ,nxtOpCurVar.attribute12
1120 ,nxtOpCurVar.attribute13
1121 ,nxtOpCurVar.attribute14
1122 ,nxtOpCurVar.attribute15
1123 );
1124 END IF; -- end of check for x_return_status
1125
1126 END LOOP; -- end loop for nxtOpCurVar
1127
1128
1129 -- copy new op --> xxx
1130
1131 FOR prvOpCurVar IN prvOpCur LOOP
1132
1133
1134 BEGIN
1135
1136 IF (prvOpCurVar.next_operation IS NOT NULL) THEN
1137
1138 SELECT
1139 operation_completed
1140 ,first_unit_start_date
1141 INTO
1142 l_completed
1143 ,l_actual_start_date
1144 FROM wip_operations
1145 WHERE
1146 wip_entity_id = p_wip_entity_id
1147 AND operation_seq_num = prvOpCurVar.next_operation;
1148
1149 END IF;
1150
1151 IF (NVL(l_completed, 'N' ) = 'Y') THEN
1152 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_INVALID_COMPLETE_OP1');
1153 x_return_status := FND_API.G_RET_STS_ERROR;
1154 END IF;
1155
1156 IF (l_actual_start_date < p_operation_completion_date ) THEN
1157 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_HANDOVER_INV_END_DATE');
1158 x_return_status := FND_API.G_RET_STS_ERROR;
1159 END IF;
1160
1161 EXCEPTION WHEN NO_DATA_FOUND THEN -- Bug 3133704, others -> no_data_found
1162 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_HANDOVER_OP_NOTFOUND');
1163 x_return_status := FND_API.G_RET_STS_ERROR;
1164 END;
1165
1166 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1167
1168 prvOpCurVar.prior_operation := p_new_op_seq_num;
1169 prvOpCurVar.Last_Updated_By := FND_GLOBAL.user_id;
1170 prvOpCurVar.Last_Update_Login := FND_GLOBAL.user_id;
1171 prvOpCurVar.Created_By := FND_GLOBAL.user_id;
1172 prvOpCurVar.Last_Update_Date := sysdate;
1173 prvOpCurVar.Creation_Date := sysdate;
1174
1175
1176 INSERT INTO wip_operation_networks
1177 ( prior_operation
1178 ,next_operation
1179 ,wip_entity_id
1180 ,organization_id
1181 ,created_by
1182 ,creation_date
1183 ,last_updated_by
1184 ,last_update_date
1185 ,last_update_login
1186 ,attribute_category
1187 ,attribute1
1188 ,attribute2
1189 ,attribute3
1190 ,attribute4
1191 ,attribute5
1192 ,attribute6
1193 ,attribute7
1194 ,attribute8
1195 ,attribute9
1196 ,attribute10
1197 ,attribute11
1198 ,attribute12
1199 ,attribute13
1200 ,attribute14
1201 ,attribute15
1202 ) VALUES
1203 ( p_new_op_seq_num
1204 ,prvOpCurVar.next_operation
1205 ,prvOpCurVar.wip_entity_id
1206 ,prvOpCurVar.organization_id
1207 ,prvOpCurVar.created_by
1208 ,prvOpCurVar.creation_date
1209 ,prvOpCurVar.last_updated_by
1210 ,prvOpCurVar.last_update_date
1211 ,prvOpCurVar.last_update_login
1212 ,prvOpCurVar.attribute_category
1213 ,prvOpCurVar.attribute1
1214 ,prvOpCurVar.attribute2
1215 ,prvOpCurVar.attribute3
1216 ,prvOpCurVar.attribute4
1217 ,prvOpCurVar.attribute5
1218 ,prvOpCurVar.attribute6
1219 ,prvOpCurVar.attribute7
1220 ,prvOpCurVar.attribute8
1221 ,prvOpCurVar.attribute9
1222 ,prvOpCurVar.attribute10
1223 ,prvOpCurVar.attribute11
1224 ,prvOpCurVar.attribute12
1225 ,prvOpCurVar.attribute13
1226 ,prvOpCurVar.attribute14
1227 ,prvOpCurVar.attribute15
1228 );
1229
1230 END IF; -- end of check for x_return_status
1231
1232 END LOOP; -- end loop for prvOpCurVar
1233
1234
1235 EXCEPTION WHEN NO_DATA_FOUND THEN
1236 RAISE FND_API.G_EXC_ERROR;
1237 END copy_operation_network;
1238
1239
1240 ---------------------------------------------------------------------------------------
1241 -- handover the selected resources of one operation
1242 ---------------------------------------------------------------------------------------
1243 procedure operation_handover_resource
1244 ( p_api_version IN NUMBER := 1.0
1245 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1246 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1247 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1248 ,p_record_version_number IN NUMBER := NULL
1249 ,x_return_status OUT NOCOPY VARCHAR2
1250 ,x_msg_count OUT NOCOPY NUMBER
1251 ,x_msg_data OUT NOCOPY VARCHAR2
1252 ,p_wip_entity_id IN NUMBER -- data
1253 ,p_old_op_seq_num IN NUMBER
1254 ,p_resource_seq_num IN NUMBER
1255 ,p_new_op_seq_num IN NUMBER
1256 ,p_department IN VARCHAR2
1257 ,p_start_date IN DATE
1258 ,p_duration IN NUMBER
1259 ,p_new_op_start_date IN DATE
1260 ,p_new_op_end_date IN DATE
1261 ,p_employee_id IN NUMBER -- instance id
1262 ,p_complete_rollback IN VARCHAR2 := FND_API.G_FALSE -- Added parameter to handle rollback for Mobile Handover Page.
1263 ) IS
1264
1265 curRow wip_operation_resources%ROWTYPE;
1266 newRow wip_operation_resources%ROWTYPE;
1267
1268 l_api_name CONSTANT VARCHAR(30) := 'operation_handover_resource';
1269 l_api_version CONSTANT NUMBER := 1.0;
1270 l_return_status VARCHAR2(250);
1271 l_error_msg_code VARCHAR2(250);
1272 l_msg_count NUMBER;
1273 l_msg_data VARCHAR2(250);
1274 l_data VARCHAR2(250);
1275 l_msg_index_out NUMBER;
1276
1277 l_department_id NUMBER;
1278 l_quantity_open NUMBER;
1279 l_start_quantity NUMBER;
1280 l_num_non_compatible_resources NUMBER;
1281 l_duration NUMBER;
1282 l_new_op_seq_num NUMBER;
1283 l_resource_id VARCHAR2(20);
1284 l_dept NUMBER;
1285 l_res_valid NUMBER;
1286 l_inst_valid NUMBER;
1287 l_employee_name VARCHAR2(165);
1288
1289 /* added for calling WO API */
1290
1291 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
1292 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
1293 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
1294 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
1295 l_eam_res_rec EAM_PROCESS_WO_PUB.eam_res_rec_type;
1296 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
1297 l_eam_res_inst_rec EAM_PROCESS_WO_PUB.eam_res_inst_rec_type;
1298 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
1299 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
1300 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
1301 l_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
1302 l_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
1303 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
1304 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
1305 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
1306 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
1307 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
1308 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
1309 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
1310
1311 l_out_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
1312 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
1313 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
1314 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
1315 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
1316 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
1317 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
1318 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
1319 l_out_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
1320 l_out_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
1321 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
1322 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
1323 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
1324 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
1325 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
1326 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
1327 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
1328
1329 l_output_dir VARCHAR2(512);
1330
1331 BEGIN
1332
1333 SAVEPOINT operation_handover_resource;
1334
1335 eam_debug.init_err_stack('eam_operations_jsp.operation_handover_resource');
1336
1337 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1338 p_api_version,
1339 l_api_name,
1340 g_pkg_name)
1341 THEN
1342 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1343 END IF;
1344
1345 IF FND_API.TO_BOOLEAN(p_init_msg_list)
1346 THEN
1347 FND_MSG_PUB.initialize;
1348 END IF;
1349
1350 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
1351
1352 x_return_status := FND_API.G_RET_STS_SUCCESS;
1353
1354 -----------------------------------------------------------------
1355 -- validation
1356 if(p_duration <0) then
1357 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_HANDOVER_DURATION');
1358 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1359 else
1360 l_duration := trunc(p_duration * 60 * 60, 0) / ( 24 * 60 * 60);
1361 end if;
1362
1363 /* Fix for Bug 2108778 */
1364 -- Validate the new operation
1365 begin
1366 select operation_seq_num
1367 into l_new_op_seq_num
1368 from wip_operations
1369 where wip_entity_id = p_wip_entity_id
1370 and operation_seq_num = p_new_op_seq_num;
1371 Exception when others then
1372 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_HANDOVER_OP_NOTFOUND');
1373 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1374 END;
1375 /* Fix for Bug 2108778 */
1376
1377 begin
1378 l_dept :=null;
1379 select resource_id into l_resource_id
1380 from wip_operation_resources
1381 where wip_entity_id = p_wip_entity_id
1382 and operation_seq_num = p_old_op_seq_num
1383 and resource_seq_num = p_resource_seq_num;
1384
1385 select bd.department_id into l_dept
1386 from bom_department_resources bdr,bom_departments bd
1387 where bd.department_id = bdr.department_id
1388 and resource_id = l_resource_id
1389 and bd.department_id in (select department_id
1390 from bom_departments
1391 where department_code=p_department);
1392 if(l_dept=null) then
1393 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_HANDOVER_DEPT_INVALID'
1394 ,p_token1 => 'RESOURCE_SEQ_NUM', p_value1 => p_resource_seq_num ,p_token2 => 'DEPARTMENT',p_value2 =>p_department);
1395 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1396 END IF;
1397 end;
1398
1399 -- get the resource
1400 BEGIN
1401 select *
1402 into curRow
1403 from wip_operation_resources r
1404 where r.wip_entity_id = p_wip_entity_id
1405 and r.operation_seq_num = p_old_op_seq_num
1406 and r.resource_seq_num = p_resource_seq_num;
1407
1408 select quantity_open
1409 into l_quantity_open
1410 from wip_operation_resources_v v
1411 where v.wip_entity_id = curRow.Wip_Entity_Id
1412 and v.operation_seq_num = curRow.Operation_Seq_Num
1413 and v.resource_seq_num = curRow.Resource_Seq_Num;
1414
1415 IF l_quantity_open < 0 THEN
1416 l_quantity_open := 0;
1417 END IF ;
1418
1419 Exception when others then
1420 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_HANDOVER_RSRC_NOTFOUND'
1421 ,p_token1 => 'RESOURCE_SEQ_NUM', p_value1 => p_resource_seq_num);
1422 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1423
1424
1425 END;
1426
1427
1428 if( curRow.Wip_Entity_Id is not null) then
1429 BEGIN
1430 select department_id
1431 into l_department_id
1432 from bom_departments bd
1433 where bd.department_code like p_department
1434 and bd.organization_id = curRow.Organization_Id
1435 and nvl(bd.disable_date, sysdate) >= sysdate;
1436
1437
1438 Exception when others then
1439 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_HANDOVER_DEPT_INV'
1440 ,p_token1 => 'RESOURCE_SEQ_NUM', p_value1 => p_resource_seq_num
1441 ,p_token2 => 'ERR', p_value2 => SQLERRM );
1442 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1443 END;
1444
1445
1446 -- verify that resource can be handover to that department
1447 if (x_return_status = FND_API.G_RET_STS_SUCCESS ) then
1448 select count(*)
1449 into l_num_non_compatible_resources
1450 from wip_operation_resources wor
1451 where wor.wip_entity_id = p_wip_entity_id
1452 and wor.operation_seq_num = p_old_op_seq_num
1453 and wor.resource_seq_num = p_resource_seq_num
1454 and wor.resource_id not in (
1455 select bdr.resource_id
1456 from bom_department_resources bdr
1457 where bdr.department_id = l_department_id
1458 );
1459 if( l_num_non_compatible_resources >0) then
1460 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_HANDOVER_DEPT_NOTOK'
1461 ,p_token1 => 'RESOURCE_SEQ_NUM', p_value1 => p_resource_seq_num);
1462 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1463 end if;
1464 end if;
1465 end if;
1466
1467 -- get start quantity
1468 select start_quantity
1469 into l_start_quantity
1470 from wip_discrete_jobs wdj
1471 where wdj.wip_entity_id = p_wip_entity_id;
1472
1473 if(l_start_quantity <> 1 ) then
1474 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_HANDOVER_S_QUANTITY_INV');
1475 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1476 end if;
1477
1478
1479 if( x_return_status = FND_API.G_RET_STS_SUCCESS) then
1480 BEGIN
1481
1482 SELECT 1 INTO l_res_valid
1483 FROM wip_operation_resources
1484 WHERE wip_entity_id = p_wip_entity_id
1485 AND operation_seq_num = p_new_op_seq_num
1486 AND resource_seq_num = p_resource_seq_num;
1487
1488 EXCEPTION WHEN NO_DATA_FOUND THEN
1489 l_res_valid := 0;
1490 END;
1491
1492 BEGIN
1493
1494 SELECT 1 INTO l_inst_valid
1495 FROM wip_op_resource_instances
1496 WHERE wip_entity_id = p_wip_entity_id
1497 AND operation_seq_num = p_new_op_seq_num
1498 AND resource_seq_num = p_resource_seq_num
1499 AND instance_id = p_employee_id;
1500
1501 EXCEPTION WHEN NO_DATA_FOUND THEN
1502 l_inst_valid := 0;
1503 END;
1504
1505 -- copy row
1506 newRow := curRow;
1507 newRow.Operation_Seq_Num := p_new_op_seq_num;
1508 newRow.Start_Date := p_start_date;
1509 newRow.Completion_Date := p_start_date + l_duration;
1510 newRow.Department_Id := l_department_id;
1511 newRow.Usage_Rate_Or_Amount := nvl(l_quantity_open, 0) / l_start_quantity;
1512 newRow.Applied_Resource_Units := 0;
1513 newRow.Applied_Resource_Value := 0;
1514
1515 -- row who
1516 newRow.Last_Update_Date := sysdate;
1517 newRow.Creation_Date := sysdate;
1518 newRow.Last_Updated_By := g_last_updated_by;
1519 newRow.Last_Update_Login := g_last_updated_by;
1520 newRow.Created_By := g_last_updated_by;
1521
1522
1523 BEGIN
1524
1525 IF l_res_valid <> 1 THEN
1526
1527 l_eam_res_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_CREATE;
1528 l_eam_res_rec.wip_entity_id := newRow.Wip_Entity_Id;
1529 l_eam_res_rec.operation_seq_num := newRow.Operation_Seq_Num;
1530 l_eam_res_rec.organization_id := newRow.Organization_Id;
1531 l_eam_res_rec.resource_seq_num := newRow.resource_seq_num;
1532 l_eam_res_rec.resource_id := newRow.resource_id;
1533 l_eam_res_rec.uom_code := newRow.uom_code;
1534 l_eam_res_rec.basis_type := newRow.basis_type;
1535 l_eam_res_rec.usage_rate_or_amount := newRow.usage_rate_or_amount;
1536 l_eam_res_rec.activity_id := newRow.activity_id;
1537 l_eam_res_rec.scheduled_flag := newRow.scheduled_flag;
1538 l_eam_res_rec.firm_flag := newRow.firm_flag;
1539 l_eam_res_rec.assigned_units := newRow.assigned_units;
1540 l_eam_res_rec.maximum_assigned_units := newRow.maximum_assigned_units;
1541 l_eam_res_rec.autocharge_type := newRow.autocharge_type;
1542 l_eam_res_rec.standard_rate_flag := newRow.standard_rate_flag;
1543 l_eam_res_rec.applied_resource_units := newRow.applied_resource_units;
1544 l_eam_res_rec.applied_resource_value := newRow.applied_resource_value;
1545 l_eam_res_rec.start_date := newRow.start_date;
1546 l_eam_res_rec.completion_date := newRow.completion_date;
1547 l_eam_res_rec.schedule_seq_num := newRow.schedule_seq_num;
1548 l_eam_res_rec.substitute_group_num := newRow.substitute_group_num;
1549 l_eam_res_rec.attribute_category := newRow.attribute_category;
1550 l_eam_res_rec.department_id := newRow.department_id;
1551 l_eam_res_rec.attribute1 := newRow.Attribute1;
1552 l_eam_res_rec.attribute2 := newRow.Attribute2;
1553 l_eam_res_rec.attribute3 := newRow.Attribute3;
1554 l_eam_res_rec.attribute4 := newRow.Attribute4;
1555 l_eam_res_rec.attribute5 := newRow.Attribute5;
1556 l_eam_res_rec.Attribute6 := newRow.Attribute6;
1557 l_eam_res_rec.Attribute7 := newRow.Attribute7;
1558 l_eam_res_rec.Attribute8 := newRow.Attribute8;
1559 l_eam_res_rec.Attribute9 := newRow.Attribute9;
1560 l_eam_res_rec.Attribute10 := newRow.Attribute10;
1561 l_eam_res_rec.attribute11 := newRow.Attribute11;
1562 l_eam_res_rec.attribute12 := newRow.Attribute12;
1563 l_eam_res_rec.attribute13 := newRow.Attribute13;
1564 l_eam_res_rec.attribute14 := newRow.Attribute14;
1565 l_eam_res_rec.attribute15 := newRow.Attribute15;
1566
1567
1568 l_eam_res_tbl(1) := l_eam_res_rec ;
1569
1570 IF l_inst_valid <> 1 THEN
1571
1572 IF p_employee_id IS NOT NULL THEN
1573
1574 l_eam_res_inst_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_CREATE ;
1575 l_eam_res_inst_rec.wip_entity_id := p_wip_entity_id ;
1576 l_eam_res_inst_rec.organization_id := newRow.organization_id ;
1577 l_eam_res_inst_rec.operation_seq_num := p_new_op_seq_num ;
1578 l_eam_res_inst_rec.resource_seq_num := p_resource_seq_num ;
1579 l_eam_res_inst_rec.instance_id := p_employee_id ;
1580 l_eam_res_inst_rec.serial_number := NULL ;
1581 l_eam_res_inst_rec.start_date := p_start_date ;
1582 l_eam_res_inst_rec.completion_date := (p_start_date+l_duration) ;
1583
1584 l_eam_res_inst_tbl(1) := l_eam_res_inst_rec ;
1585
1586 END IF; -- end of p_employee_id
1587
1588 ELSE
1589 SELECT full_name
1590 INTO l_employee_name
1591 FROM per_all_people_f papf,bom_resource_employees bre
1592 WHERE bre.instance_id = p_employee_id
1593 and papf.person_id = bre.person_id
1594 and( trunc(sysdate) between papf.effective_start_date
1595 and papf.effective_end_date);
1596
1597 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_RI_ALREADY_EXISTS'
1598 ,p_token1 => 'INSTANCE_NAME', p_value1 => l_employee_name);
1599 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1600 END IF; -- end of l_inst_valid check
1601
1602 EAM_PROCESS_WO_PUB.Process_WO
1603 ( p_bo_identifier => 'EAM'
1604 , p_init_msg_list => TRUE
1605 , p_api_version_number => 1.0
1606 , p_commit => 'N'
1607 , p_eam_wo_rec => l_eam_wo_rec
1608 , p_eam_op_tbl => l_eam_op_tbl
1609 , p_eam_op_network_tbl => l_eam_op_network_tbl
1610 , p_eam_res_tbl => l_eam_res_tbl
1611 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
1612 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
1613 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
1614 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
1615 , p_eam_direct_items_tbl => l_eam_di_tbl
1616 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
1617 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
1618 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
1619 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
1620 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
1621 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
1622 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
1623 , p_eam_request_tbl => l_eam_request_tbl
1624 , x_eam_wo_rec => l_out_eam_wo_rec
1625 , x_eam_op_tbl => l_out_eam_op_tbl
1626 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
1627 , x_eam_res_tbl => l_out_eam_res_tbl
1628 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
1629 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
1630 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
1631 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
1632 , x_eam_direct_items_tbl => l_out_eam_di_tbl
1633 , x_eam_wo_comp_rec => l_out_eam_wo_comp_rec
1634 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
1635 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
1636 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
1637 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
1638 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
1639 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
1640 , x_eam_request_tbl => l_out_eam_request_tbl
1641 , x_return_status => x_return_status
1642 , x_msg_count => x_msg_count
1643 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
1644 , p_debug_filename => 'opreshandover.log'
1645 , p_output_dir => l_output_dir
1646 , p_debug_file_mode => 'w'
1647 );
1648
1649 END IF ; -- end of l_res_valid check
1650
1651
1652
1653 EXCEPTION WHEN OTHERS THEN
1654 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_HANDOVER_EXCEPTION'
1655 ,p_token1 => 'RESOURCE_SEQ_NUM', p_value1 => p_resource_seq_num
1656 ,p_token2 => 'ERR_MSG', p_value2 => SQLERRM);
1657 END;
1658
1659 end if;
1660
1661 -- check error
1662 l_msg_count := FND_MSG_PUB.count_msg;
1663 IF l_msg_count = 1 THEN
1664 eam_execution_jsp.Get_Messages
1665 (p_encoded => FND_API.G_FALSE,
1666 p_msg_index => 1,
1667 p_msg_count => l_msg_count,
1668 p_msg_data => l_msg_data, -- removed g_miss_char
1669 p_data => l_data,
1670 p_msg_index_out => l_msg_index_out);
1671 x_msg_count := l_msg_count;
1672 x_msg_data := l_msg_data;
1673 ELSE
1674 x_msg_count := l_msg_count;
1675 END IF;
1676
1677 IF l_msg_count > 0 THEN
1678 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1679 RAISE FND_API.G_EXC_ERROR;
1680 END IF;
1681
1682 IF FND_API.TO_BOOLEAN(P_COMMIT)
1683 THEN
1684 COMMIT WORK;
1685 END IF;
1686
1687 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1688 IF FND_API.TO_BOOLEAN(p_complete_rollback)
1689 THEN
1690 ROLLBACK; -- Complete rollback for Mobile Handover Page
1691 ELSE
1692 ROLLBACK TO operation_handover_resource; -- Method rollback for Desktop HandoverPage.
1693 END IF;
1694 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'eam_operations_jsp.operation_handover_resource',
1695 p_procedure_name => EAM_DEBUG.G_err_stack);
1696 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1697
1698 WHEN FND_API.G_EXC_ERROR THEN
1699 IF FND_API.TO_BOOLEAN(p_complete_rollback)
1700 THEN
1701 ROLLBACK; -- Complete rollback for Mobile Handover Page
1702 ELSE
1703 ROLLBACK TO operation_handover_resource; -- Method rollback for Desktop HandoverPage.
1704 END IF;
1705 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'eam_operations_jsp.operation_handover_resource',
1706 p_procedure_name => EAM_DEBUG.G_err_stack);
1707 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1708
1709 WHEN OTHERS THEN
1710 IF FND_API.TO_BOOLEAN(p_complete_rollback)
1711 THEN
1712 ROLLBACK; -- Complete rollback for Mobile Handover Page
1713 ELSE
1714 ROLLBACK TO operation_handover_resource; -- Method rollback for Desktop HandoverPage.
1715 END IF;
1716 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'eam_operations_jsp.operation_handover_resource',
1717 p_procedure_name => EAM_DEBUG.G_err_stack);
1718 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1719
1720 END operation_handover_resource;
1721
1722
1723 -- Procedure to validate all fields entered through the Add Resource JSP
1724
1725 procedure validate_insert (p_wip_entity_id IN NUMBER
1726 ,p_operation_seq_num IN NUMBER
1727 ,p_department_code IN VARCHAR2
1728 ,p_organization_id IN NUMBER
1729 ,p_resource_code IN VARCHAR2
1730 ,p_uom_code IN VARCHAR2
1731 ,p_usage_rate IN NUMBER
1732 ,p_assigned_units IN NUMBER
1733 ,p_start_date IN DATE
1734 ,p_end_date IN DATE
1735 ,p_activity IN VARCHAR2
1736 ,x_uom_status OUT NOCOPY NUMBER
1737 ,x_operation_status OUT NOCOPY NUMBER
1738 ,x_department_status OUT NOCOPY NUMBER
1739 ,x_res_status OUT NOCOPY NUMBER
1740 ,x_usage_status OUT NOCOPY NUMBER
1741 ,x_assigned_units OUT NOCOPY NUMBER
1742 ,x_assigned OUT NOCOPY NUMBER
1743 ,x_dates OUT NOCOPY NUMBER
1744 ,x_activity OUT NOCOPY NUMBER) IS
1745
1746 l_res_code varchar2(80);
1747 l_uom varchar2(30);
1748 l_invalid_uom number := 0;
1749 l_invalid_resource number := 0;
1750 l_stmt_num number := 0;
1751 l_invalid_usage number := 0;
1752 l_operation_seq_num number := 0;
1753 l_department_code varchar2(80);
1754 l_invalid_operation number := 0;
1755 l_invalid_department number := 0;
1756 l_invalid_assgned_units number := 0;
1757 l_assigned number := 0;
1758 l_invalid_dates number := 0;
1759 l_activities varchar2(80);
1760 l_invalid_activity number := 0;
1761 l_capacity_units number := 0;
1762
1763 resource_exists number := 1;
1764 uom_exists number := 1;
1765 operation_exists number := 1;
1766 department_exists number := 1;
1767 activity_exists number := 1;
1768 TYPE CUR_TYP is ref cursor;
1769
1770 -- c_res_cur CUR_TYP;
1771 -- c_oper_cur CUR_TYP;
1772 -- c_act_cur CUR_TYP;
1773
1774
1775
1776 CURSOR c_res_cur IS --rhshriva
1777 select res.resource_code,
1778 res.unit_of_measure
1779 from cst_activities cst, mtl_uom_conversions muc, bom_resources res, bom_department_resources bdr
1780 where nvl(res.disable_date,sysdate+2) > sysdate
1781 and res.resource_id = bdr.resource_id
1782 and res.default_activity_id = cst.activity_id(+)
1783 and (cst.organization_id = res.organization_id or cst.organization_id is null)
1784 and nvl(cst.disable_date(+), sysdate+2) > sysdate
1785 and res.unit_of_measure = muc.uom_code
1786 and muc.inventory_item_id = 0
1787 and res.organization_id = p_organization_id
1788 and department_id = (select department_id
1789 from wip_operations
1790 where wip_entity_id = p_wip_entity_id
1791 and operation_seq_num = p_operation_seq_num);
1792
1793
1794
1795 CURSOR c_oper_cur IS --rhshriva
1796 select wo.operation_seq_num, bd.department_code
1797 from wip_operations wo, bom_departments bd
1798 where bd.department_id = wo.department_id
1799 and bd.organization_id = wo.organization_id
1800 and wo.organization_id = p_organization_id
1801 and wo.wip_entity_id = p_wip_entity_id;
1802
1803
1804 CURSOR c_act_cur IS --rhshriva
1805 select activity
1806 from cst_activities
1807 where nvl(disable_date, sysdate + 2) > sysdate and
1808 (organization_id is null or organization_id = p_organization_id ) ;
1809
1810 BEGIN
1811 -- Check for Usage Rate
1812 /* Commenting out the validation as this is already present in WO API */
1813 -- Check for Resource
1814
1815 open c_res_cur ;
1816 l_stmt_num := 10;
1817
1818 loop
1819
1820 fetch c_res_cur into l_res_code, l_uom;
1821 exit when c_res_cur % NOTFOUND;
1822
1823 l_stmt_num := 20;
1824
1825 -- Check for resource_code and uom validation
1826
1827 if (p_resource_code = l_res_code) then
1828 resource_exists := 0 ;
1829 end if;
1830
1831 if (p_uom_code = l_uom) then
1832 uom_exists := 0;
1833 end if;
1834
1835
1836 end loop;
1837
1838 close c_res_cur;
1839
1840
1841 if (uom_exists = 1) then
1842 l_invalid_uom := 1;
1843 end if;
1844
1845 if (resource_exists = 1) then
1846 l_invalid_resource := 1;
1847 end if;
1848
1849 x_uom_status := l_invalid_uom;
1850
1851 x_res_status := l_invalid_resource;
1852
1853 -- Bug 3133704 changed when_others
1854 EXCEPTION WHEN NO_DATA_FOUND THEN
1855 raise FND_API.G_EXC_ERROR;
1856
1857 END validate_insert;
1858
1859
1860 -- Insert into WIP_OPERATION_RESOURCES from Add Resources JSP
1861
1862 procedure insert_into_wor( p_api_version IN NUMBER
1863 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1864 ,p_commit IN VARCHAR2 := fnd_api.g_false
1865 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1866 ,p_wip_entity_id IN NUMBER
1867 ,p_operation_seq_num IN NUMBER
1868 ,p_organization_id IN NUMBER
1869 ,p_usage_rate IN NUMBER
1870 ,p_resource_code IN VARCHAR2
1871 ,p_uom_code IN VARCHAR2
1872 ,p_resource_seq_num IN NUMBER
1873 ,p_dept_code IN VARCHAR2
1874 ,p_assigned_units IN NUMBER
1875 ,p_basis IN NUMBER
1876 ,p_scheduled_flag IN NUMBER
1877 ,p_charge_type IN NUMBER
1878 ,p_schedule_sequence IN NUMBER
1879 ,p_std_rate IN VARCHAR2
1880 ,p_start_date IN DATE
1881 ,p_end_date IN DATE
1882 ,p_activity IN VARCHAR2
1883 ,p_mod IN VARCHAR2
1884 ,x_update_status OUT NOCOPY NUMBER
1885 ,x_return_status OUT NOCOPY VARCHAR2
1886 ,x_msg_count OUT NOCOPY NUMBER
1887 ,x_msg_data OUT NOCOPY VARCHAR2) IS
1888
1889 l_api_name CONSTANT VARCHAR2(30) := 'insert_into_wor';
1890 l_api_version CONSTANT NUMBER := 1.0;
1891 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
1892
1893 l_resource_id NUMBER;
1894 l_update_status NUMBER := 1;
1895 l_activity_id NUMBER;
1896 l_usage_rate NUMBER := 0;
1897 l_stmt_num NUMBER;
1898
1899 -- baroy
1900 l_old_scheduled_flag number;
1901 l_old_schedule_sequence number;
1902 l_old_start_date date;
1903 l_old_end_date date;
1904 l_old_usage_rate number;
1905 l_old_uom_code varchar2(3);
1906 l_old_assigned_units number;
1907 l_call_scheduler number := 0;
1908 -- baroy
1909
1910 l_res_seq_exists NUMBER := 0;
1911
1912
1913 /* added for calling WO API */
1914
1915 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
1916 l_eam_res_rec EAM_PROCESS_WO_PUB.eam_res_rec_type;
1917 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
1918 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
1919 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
1920 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
1921 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
1922 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
1923 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
1924 l_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
1925 l_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
1926 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
1927 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
1928 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
1929 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
1930 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
1931 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
1932 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
1933
1934 l_out_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
1935 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
1936 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
1937 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
1938 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
1939 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
1940 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
1941 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
1942 l_out_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
1943 l_out_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
1944 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
1945 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
1946 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
1947 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
1948 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
1949 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
1950 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
1951
1952
1953 l_output_dir VARCHAR2(512);
1954 BEGIN
1955
1956 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
1957
1958
1959 -- Standard Start of API savepoint
1960 l_stmt_num := 10;
1961 SAVEPOINT get_insert_into_wor_pvt;
1962
1963 l_stmt_num := 20;
1964 -- Standard call to check for call compatibility.
1965 IF NOT fnd_api.compatible_api_call(
1966 l_api_version
1967 ,p_api_version
1968 ,l_api_name
1969 ,g_pkg_name) THEN
1970 RAISE fnd_api.g_exc_unexpected_error;
1971 END IF;
1972
1973 l_stmt_num := 30;
1974 -- Initialize message list if p_init_msg_list is set to TRUE.
1975 IF fnd_api.to_boolean(p_init_msg_list) THEN
1976 fnd_msg_pub.initialize;
1977 END IF;
1978
1979 l_stmt_num := 40;
1980 -- Initialize API return status to success
1981 x_return_status := fnd_api.g_ret_sts_success;
1982
1983 l_stmt_num := 50;
1984 -- API body
1985
1986 -- Check for Usage Rate
1987
1988 if(p_usage_rate is not null) then
1989 l_usage_rate := p_usage_rate;
1990 else
1991 l_usage_rate := 0;
1992 end if;
1993
1994
1995 -- Derive Resource Id from Resource Code
1996
1997 begin
1998
1999 select resource_id
2000 into l_resource_id
2001 from bom_resources
2002 where resource_code = p_resource_code
2003 and organization_id = p_organization_id;
2004
2005 exception
2006 when others then
2007 null;
2008 end;
2009
2010 if (p_activity is not null) then
2011
2012 begin
2013 select activity_id
2014 into l_activity_id
2015 from cst_activities
2016 where activity = p_activity
2017 and organization_id = organization_id;
2018
2019 exception
2020 when others then
2021 null;
2022 end;
2023 end if;
2024 if (p_mod='UPDATE') then
2025 -- first query up the old resource for use in scheduling decision.
2026 select scheduled_flag, schedule_seq_num,
2027 start_date, completion_date, usage_rate_or_amount, uom_code, assigned_units
2028 into l_old_scheduled_flag, l_old_schedule_sequence, l_old_start_date
2029 , l_old_end_date, l_old_usage_rate, l_old_uom_code, l_old_assigned_units
2030 from wip_operation_resources
2031 where wip_entity_id = p_wip_entity_id
2032 and operation_seq_num = p_operation_seq_num
2033 and resource_seq_num = p_resource_seq_num
2034 and organization_id = p_organization_id;
2035 if p_scheduled_flag = 1 and (
2036 l_old_scheduled_flag <> p_scheduled_flag
2037 or l_old_schedule_sequence <> p_schedule_sequence
2038 or l_old_start_date <> p_start_date
2039 or l_old_end_date <> p_end_date
2040 or l_old_usage_rate <> p_usage_rate
2041 or l_old_uom_code <> p_uom_code
2042 or l_old_assigned_units <> p_assigned_units)
2043 then
2044 l_call_scheduler := 1;
2045 end if;
2046
2047 l_eam_res_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_UPDATE;
2048 l_eam_res_rec.wip_entity_id := p_wip_entity_id;
2049 l_eam_res_rec.organization_id := p_organization_id;
2050 l_eam_res_rec.operation_seq_num := p_operation_seq_num;
2051 l_eam_res_rec.resource_seq_num := p_resource_seq_num;
2052 l_eam_res_rec.resource_id := l_resource_id;
2053 l_eam_res_rec.uom_code := p_uom_code;
2054 l_eam_res_rec.basis_type := p_basis;
2055 l_eam_res_rec.usage_rate_or_amount := p_usage_rate;
2056 l_eam_res_rec.activity_id := l_activity_id;
2057 l_eam_res_rec.scheduled_flag := p_scheduled_flag;
2058 l_eam_res_rec.assigned_units := p_assigned_units;
2059 l_eam_res_rec.autocharge_type := p_charge_type;
2060 if ( p_std_rate = 'Y') then
2061 l_eam_res_rec.standard_rate_flag := 1;
2062 else
2063 l_eam_res_rec.standard_rate_flag := 2;
2064 end if;
2065 l_eam_res_rec.start_date := p_start_date;
2066 l_eam_res_rec.completion_date := p_end_date;
2067 l_eam_res_rec.schedule_seq_num := p_schedule_sequence;
2068
2069 l_eam_res_tbl(1) := l_eam_res_rec ;
2070
2071
2072 EAM_PROCESS_WO_PUB.Process_WO
2073 ( p_bo_identifier => 'EAM'
2074 , p_init_msg_list => TRUE
2075 , p_api_version_number => 1.0
2076 , p_commit => 'N'
2077 , p_eam_wo_rec => l_eam_wo_rec
2078 , p_eam_op_tbl => l_eam_op_tbl
2079 , p_eam_op_network_tbl => l_eam_op_network_tbl
2080 , p_eam_res_tbl => l_eam_res_tbl
2081 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
2082 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
2083 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
2084 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
2085 , p_eam_direct_items_tbl => l_eam_di_tbl
2086 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
2087 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
2088 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
2089 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
2090 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
2091 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
2092 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
2093 , p_eam_request_tbl => l_eam_request_tbl
2094 , x_eam_wo_rec => l_out_eam_wo_rec
2095 , x_eam_op_tbl => l_out_eam_op_tbl
2096 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
2097 , x_eam_res_tbl => l_out_eam_res_tbl
2098 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
2099 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
2100 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
2101 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
2102 , x_eam_direct_items_tbl => l_out_eam_di_tbl
2103 , x_eam_wo_comp_rec => l_out_eam_wo_comp_rec
2104 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
2105 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
2106 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
2107 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
2108 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
2109 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
2110 , x_eam_request_tbl => l_out_eam_request_tbl
2111 , x_return_status => x_return_status
2112 , x_msg_count => x_msg_count
2113 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
2114 , p_debug_filename => 'updatewor.log'
2115 , p_output_dir => l_output_dir
2116 , p_debug_file_mode => 'w'
2117 );
2118
2119 l_update_status := 0;
2120
2121 elsif (p_mod='INSERT') then
2122 -- first find out whether we will need to call the
2123 -- scheduler finally
2124 if p_scheduled_flag = 1 then
2125 l_call_scheduler := 1;
2126 end if;
2127
2128
2129 l_eam_res_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_CREATE;
2130 l_eam_res_rec.wip_entity_id := p_wip_entity_id;
2131 l_eam_res_rec.organization_id := p_organization_id;
2132 l_eam_res_rec.operation_seq_num := p_operation_seq_num;
2133 l_eam_res_rec.resource_seq_num := p_resource_seq_num;
2134 l_eam_res_rec.resource_id := l_resource_id;
2135 l_eam_res_rec.uom_code := p_uom_code;
2136 l_eam_res_rec.basis_type := p_basis;
2137 l_eam_res_rec.usage_rate_or_amount := p_usage_rate;
2138 l_eam_res_rec.activity_id := l_activity_id;
2139 l_eam_res_rec.scheduled_flag := p_scheduled_flag;
2140 l_eam_res_rec.assigned_units := p_assigned_units;
2141 l_eam_res_rec.autocharge_type := p_charge_type;
2142 if ( p_std_rate = 'Y') then
2143 l_eam_res_rec.standard_rate_flag := 1;
2144 else
2145 l_eam_res_rec.standard_rate_flag := 2;
2146 end if;
2147 l_eam_res_rec.start_date := p_start_date;
2148 l_eam_res_rec.completion_date := p_end_date;
2149 l_eam_res_rec.schedule_seq_num := p_schedule_sequence;
2150
2151 l_eam_res_tbl(1) := l_eam_res_rec ;
2152
2153
2154 EAM_PROCESS_WO_PUB.Process_WO
2155 ( p_bo_identifier => 'EAM'
2156 , p_init_msg_list => TRUE
2157 , p_api_version_number => 1.0
2158 , p_commit => 'N'
2159 , p_eam_wo_rec => l_eam_wo_rec
2160 , p_eam_op_tbl => l_eam_op_tbl
2161 , p_eam_op_network_tbl => l_eam_op_network_tbl
2162 , p_eam_res_tbl => l_eam_res_tbl
2163 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
2164 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
2165 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
2166 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
2167 , p_eam_direct_items_tbl => l_eam_di_tbl
2168 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
2169 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
2170 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
2171 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
2172 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
2173 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
2174 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
2175 , p_eam_request_tbl => l_eam_request_tbl
2176 , x_eam_wo_rec => l_out_eam_wo_rec
2177 , x_eam_op_tbl => l_out_eam_op_tbl
2178 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
2179 , x_eam_res_tbl => l_out_eam_res_tbl
2180 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
2181 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
2182 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
2183 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
2184 , x_eam_direct_items_tbl => l_out_eam_di_tbl
2185 , x_eam_wo_comp_rec => l_out_eam_wo_comp_rec
2186 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
2187 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
2188 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
2189 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
2190 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
2191 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
2192 , x_eam_request_tbl => l_out_eam_request_tbl
2193 , x_return_status => x_return_status
2194 , x_msg_count => x_msg_count
2195 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
2196 , p_debug_filename => 'insertwor.log'
2197 , p_output_dir => l_output_dir
2198 , p_debug_file_mode => 'w'
2199 );
2200
2201
2202 else
2203 x_return_status :='MODE_DOES_NOT_EXIST';
2204 end if;-- end of insertion and updation
2205 x_update_status := l_update_status;
2206
2207
2208 -- End of API body.
2209 -- Standard check of p_commit.
2210 IF fnd_api.to_boolean(p_commit)
2211 and x_return_status = 'S' THEN
2212 COMMIT WORK;
2213 END IF;
2214
2215 IF(x_return_status <> 'S') THEN --added for 3817679
2216 ROLLBACK TO get_insert_into_wor_pvt;
2217 END IF;
2218
2219
2220 l_stmt_num := 999;
2221 -- Standard call to get message count and if count is 1, get message info.
2222 fnd_msg_pub.count_and_get(
2223 p_count => x_msg_count
2224 ,p_data => x_msg_data);
2225 EXCEPTION
2226 WHEN fnd_api.g_exc_error THEN
2227 ROLLBACK TO get_insert_into_wor_pvt;
2228 x_return_status := fnd_api.g_ret_sts_error;
2229 fnd_msg_pub.count_and_get(
2230 -- p_encoded => FND_API.g_false
2231 p_count => x_msg_count
2232 ,p_data => x_msg_data);
2233
2234 WHEN fnd_api.g_exc_unexpected_error THEN
2235 ROLLBACK TO get_insert_into_wor_pvt;
2236 x_return_status := fnd_api.g_ret_sts_unexp_error;
2237
2238 fnd_msg_pub.count_and_get(
2239 p_count => x_msg_count
2240 ,p_data => x_msg_data);
2241
2242 WHEN OTHERS THEN
2243 ROLLBACK TO get_insert_into_wor_pvt;
2244 x_return_status := fnd_api.g_ret_sts_unexp_error;
2245 IF fnd_msg_pub.check_msg_level(
2246 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2247 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2248 END IF;
2249
2250 fnd_msg_pub.count_and_get(
2251 p_count => x_msg_count
2252 ,p_data => x_msg_data);
2253
2254
2255
2256 END insert_into_wor;
2257
2258
2259
2260
2261 -- API to validate entries in Material Page
2262
2263 PROCEDURE material_validate (
2264 p_organization_id IN NUMBER
2265 ,p_wip_entity_id IN NUMBER
2266 ,p_description IN VARCHAR2
2267 ,p_uom IN VARCHAR2
2268 ,p_concatenated_segments IN VARCHAR2
2269 ,p_operation_seq_num IN VARCHAR2
2270 ,p_department_code IN VARCHAR2
2271 ,p_supply IN VARCHAR2
2272 ,p_subinventory_code IN VARCHAR2
2273 ,p_locator IN VARCHAR2
2274 ,x_invalid_asset OUT NOCOPY NUMBER
2275 ,x_invalid_description OUT NOCOPY NUMBER
2276 ,x_invalid_uom OUT NOCOPY NUMBER
2277 ,x_invalid_subinventory OUT NOCOPY NUMBER
2278 ,x_invalid_locator OUT NOCOPY NUMBER
2279 ,x_invalid_department OUT NOCOPY NUMBER
2280 ,x_invalid_operation OUT NOCOPY NUMBER
2281 ,x_invalid_supply OUT NOCOPY NUMBER
2282 )
2283
2284 IS
2285
2286 l_concatenated_segments VARCHAR2(2000);
2287 l_organization_id NUMBER;
2288 l_description VARCHAR2(240);
2289 l_uom VARCHAR2(30);
2290 l_operation_seq_num NUMBER;
2291 l_department_code VARCHAR2(80);
2292 l_supply VARCHAR2(80);
2293 l_subinventory VARCHAR2(80);
2294 l_on_hand_quantity NUMBER;
2295 l_locator VARCHAR2(2000);
2296 l_stmt_num NUMBER:= 0;
2297
2298 invalid_uom NUMBER := 0;
2299 invalid_description NUMBER := 0;
2300 invalid_asset NUMBER := 0;
2301 material_exists NUMBER := 1;
2302 description_exists NUMBER := 1;
2303 uom_exists NUMBER := 1;
2304
2305 subinventory_exists NUMBER := 1;
2306 locator_exists NUMBER := 1;
2307 invalid_subinventory NUMBER := 0;
2308 invalid_locator NUMBER := 0;
2309
2310 invalid_department NUMBER := 0;
2311 invalid_operation NUMBER := 0;
2312 operation_exists NUMBER := 1;
2313 department_exists NUMBER := 1;
2314
2315 supply_exists NUMBER := 1;
2316 invalid_supply NUMBER := 0;
2317 constant_yes VARCHAR2(1) := 'Y';
2318 constant_supply_type VARCHAR2(30) := 'WIP_SUPPLY';
2319
2320
2321
2322 TYPE CUR_TYP is ref cursor;
2323
2324 CURSOR c_supply_cur IS --rhshriva
2325 select meaning
2326 from mfg_lookups
2327 where lookup_type = g_supply_type
2328 and (lookup_code = 1 or lookup_code = 4) ;
2329
2330 CURSOR c_subinv_cur IS --rhshriva
2331 select msinv.secondary_inventory_name,
2332 SUM(moq.transaction_quantity) on_hand_quantity
2333
2334 from mtl_secondary_inventories msinv, mtl_onhand_quantities moq
2335 where moq.organization_id=msinv.organization_id
2336 and nvl(msinv.disable_date, sysdate+2) > sysdate
2337 and moq.subinventory_code = msinv.secondary_inventory_name
2338 and msinv.organization_id = p_organization_id
2339 and moq.inventory_item_id = (select inventory_item_id from mtl_system_items_kfv
2340 where organization_id = p_organization_id
2341 and concatenated_segments =p_concatenated_segments)
2342 group by msinv.secondary_inventory_name, moq.inventory_item_id, msinv.organization_id, msinv.description, msinv.locator_type
2343 order by msinv.secondary_inventory_name;
2344
2345
2346 CURSOR c_locator_cur IS --rhshriva
2347 select inv_project.Get_locator(milk.inventory_location_id, milk.organization_id) as concatenated_segments
2348 from mtl_item_locations_kfv milk
2349 where (milk.disable_date > sysdate or milk.disable_date is null)
2350 and milk.organization_id = p_organization_id
2351 and milk.subinventory_code = p_subinventory_code ;
2352
2353
2354
2355
2356 BEGIN
2357
2358 -- API body
2359
2360 l_organization_id := p_organization_id;
2361
2362 l_stmt_num := 60;
2363
2364 if (material_exists = 1) then
2365
2366 invalid_asset := 1;
2367
2368 end if;
2369
2370 if (description_exists = 1) then
2371
2372 invalid_description := 1;
2373
2374 end if;
2375
2376 if (uom_exists = 1) then
2377
2378 invalid_uom := 1;
2379
2380 end if;
2381
2382
2383 l_stmt_num := 70;
2384
2385
2386 -- Check whether the operation is valid and matches with assigned department
2387 /* Commenting out the validation on department as it is present in WO API */
2388 l_stmt_num := 80;
2389
2390 -- Check Supply Type
2391 /* Commenting out the validation on supply type as it is present in WO API */
2392 l_stmt_num := 90;
2393
2394
2395 -- Check for Subinventory
2396 if (p_subinventory_code is not null) then
2397
2398 open c_subinv_cur ;
2399 l_stmt_num := 95;
2400
2401 loop
2402 fetch c_subinv_cur into l_subinventory, l_on_hand_quantity;
2403 EXIT WHEN c_subinv_cur%NOTFOUND;
2404
2405 if (l_subinventory = p_subinventory_code) then
2406 subinventory_exists := 0;
2407 end if;
2408
2409 end loop;
2410 close c_subinv_cur;
2411
2412 if (subinventory_exists = 1) then
2413 invalid_subinventory := 1;
2414 end if;
2415
2416 end if ; -- end of p_subinventory not null
2417
2418 l_stmt_num := 100;
2419
2420
2421 -- Check for Locator
2422
2423
2424 if (p_locator is not null) then
2425
2426 open c_locator_cur ;
2427 l_stmt_num := 105;
2428
2429 loop
2430 fetch c_locator_cur into l_concatenated_segments;
2431 EXIT WHEN c_locator_cur%NOTFOUND;
2432
2433
2434 if (l_concatenated_segments = p_locator) then
2435
2436 locator_exists := 0;
2437
2438 end if;
2439
2440 end loop;
2441 close c_locator_cur;
2442
2443 if (locator_exists = 1) then
2444
2445 invalid_locator := 1;
2446
2447 end if;
2448
2449 end if; -- end of check for p_locator
2450
2451
2452
2453 l_stmt_num := 110;
2454
2455 x_invalid_asset := invalid_asset;
2456 x_invalid_description := invalid_description;
2457 x_invalid_uom := invalid_uom;
2458 x_invalid_subinventory := invalid_subinventory;
2459 x_invalid_locator := invalid_locator;
2460 x_invalid_department := invalid_department;
2461 x_invalid_operation := invalid_operation;
2462 x_invalid_supply := invalid_supply;
2463
2464
2465 END material_validate;
2466
2467
2468
2469 --- Insert into WRO
2470
2471
2472 PROCEDURE insert_into_wro(
2473 p_api_version IN NUMBER
2474 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
2475 ,p_commit IN VARCHAR2 := fnd_api.g_false
2476 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
2477 ,p_wip_entity_id IN NUMBER
2478 ,p_organization_id IN NUMBER
2479 ,p_concatenated_segments IN VARCHAR2
2480 ,p_description IN VARCHAR2
2481 ,p_operation_seq_num IN NUMBER
2482 ,p_supply IN VARCHAR2
2483 ,p_required_date IN DATE
2484 ,p_quantity IN NUMBER
2485 ,p_comments IN VARCHAR2
2486 ,p_supply_subinventory IN VARCHAR2
2487 ,p_locator IN VARCHAR2
2488 ,p_mrp_net_flag IN VARCHAR2
2489 ,p_material_release IN VARCHAR2
2490 ,x_invalid_update_operation OUT NOCOPY NUMBER
2491 ,x_invalid_update_department OUT NOCOPY NUMBER
2492 ,x_invalid_update_description OUT NOCOPY NUMBER
2493 ,x_return_status OUT NOCOPY VARCHAR2
2494 ,x_msg_count OUT NOCOPY NUMBER
2495 ,x_msg_data OUT NOCOPY VARCHAR2
2496 ,x_update_status OUT NOCOPY NUMBER
2497 ,p_supply_code IN NUMBER :=NULL
2498 ,p_one_step_issue IN varchar2:=fnd_api.g_false
2499 ,p_released_quantity IN NUMBER := NULL)
2500
2501 IS
2502 l_api_name CONSTANT VARCHAR2(30) := 'insert_into_wro';
2503 l_api_version CONSTANT NUMBER := 1.0;
2504 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
2505
2506 l_stmt_num NUMBER;
2507 l_wip_entity_id NUMBER;
2508 l_inventory_item_id NUMBER;
2509 l_department_id NUMBER;
2510 l_supply NUMBER;
2511 l_locator NUMBER;
2512 l_mrp_net_flag NUMBER;
2513 l_material_release VARCHAR2(1);
2514 l_material_exists NUMBER := 0;
2515 l_existing_operation NUMBER;
2516 l_existing_department NUMBER;
2517 l_existing_description VARCHAR2(240);
2518 l_req_qty NUMBER := 0;
2519 l_status_type NUMBER := 0;
2520 l_material_issue_by_mo VARCHAR2(1);
2521 l_auto_request_material VARCHAR2(1);
2522 invalid_update_operation NUMBER := 0;
2523 invalid_update_department NUMBER := 0;
2524 invalid_update_description NUMBER := 0;
2525 l_update_status NUMBER := 0;
2526 l_return_status NUMBER := 0;
2527 l_msg_count NUMBER := 0;
2528 l_msg_data VARCHAR2(2000) ;
2529 l_return_status1 VARCHAR2(30) ;
2530 l_material_issue_by_mo_temp VARCHAR2(1) ;
2531 l_wo_changed BOOLEAN := FALSE;
2532
2533
2534
2535 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
2536 l_eam_mat_req_rec EAM_PROCESS_WO_PUB.eam_mat_req_rec_type;
2537 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
2538 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
2539 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
2540 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
2541 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
2542 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
2543 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
2544 l_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
2545 l_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
2546 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
2547 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
2548 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
2549 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
2550 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
2551 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
2552 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
2553
2554 l_out_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
2555 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
2556 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
2557 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
2558 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
2559 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
2560 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
2561 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
2562 l_out_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
2563 l_out_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
2564 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
2565 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
2566 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
2567 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
2568 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
2569 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
2570 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
2571
2572 l_output_dir VARCHAR2(512);
2573
2574 BEGIN
2575 -- Standard Start of API savepoint
2576 l_stmt_num := 10;
2577 SAVEPOINT get_insert_into_wro_pvt;
2578
2579 l_stmt_num := 20;
2580 -- Standard call to check for call compatibility.
2581 IF NOT fnd_api.compatible_api_call(
2582 l_api_version
2583 ,p_api_version
2584 ,l_api_name
2585 ,g_pkg_name) THEN
2586 RAISE fnd_api.g_exc_unexpected_error;
2587 END IF;
2588
2589 l_stmt_num := 30;
2590 -- Initialize message list if p_init_msg_list is set to TRUE.
2591 IF fnd_api.to_boolean(p_init_msg_list) THEN
2592 fnd_msg_pub.initialize;
2593 END IF;
2594
2595 l_stmt_num := 40;
2596 -- Initialize API return status to success
2597 x_return_status := fnd_api.g_ret_sts_success;
2598
2599 l_stmt_num := 50;
2600 -- API body
2601
2602 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
2603
2604
2605 l_wip_entity_id := p_wip_entity_id ;
2606
2607 -- Get Inventory Item Id
2608 select inventory_item_id
2609 into l_inventory_item_id
2610 from mtl_system_items_kfv
2611 where concatenated_segments = p_concatenated_segments
2612 and organization_id = p_organization_id;
2613
2614 begin
2615
2616 select 1, wro.operation_seq_num,wro.department_id,msikfv.description
2617 into l_material_exists, l_existing_operation, l_existing_department, l_existing_description
2618 from wip_requirement_operations wro, mtl_system_items_kfv msikfv
2619 where wro.inventory_item_id = l_inventory_item_id
2620 and wro.organization_id = p_organization_id
2621 and wro.wip_entity_id = p_wip_entity_id
2622 and wro.organization_id = msikfv.organization_id
2623 and wro.operation_seq_num = p_operation_seq_num
2624 and wro.inventory_item_id = msikfv.inventory_item_id;
2625
2626 exception
2627 when others then
2628 null;
2629 end;
2630
2631 -- Get Department Id
2632 select department_id
2633 into l_department_id
2634 from wip_operations
2635 where wip_entity_id = l_wip_entity_id
2636 and operation_seq_num = p_operation_seq_num
2637 and organization_id = p_organization_id;
2638
2639 -- Get Supply TYpe
2640 if(p_supply is not null) then
2641 select lookup_code
2642 into l_supply
2643 from mfg_lookups
2644 where lookup_type = g_supply_type
2645 and meaning = p_supply;
2646 else
2647 l_supply := p_supply_code;
2648 end if;
2649 -- Get Locator Id
2650 if (p_locator is not null) then
2651
2652 select milk.inventory_location_id
2653 into l_locator
2654 from mtl_item_locations_kfv milk
2655 where milk.organization_id = p_organization_id
2656 and inv_project.Get_locator(milk.inventory_location_id, milk.organization_id) = p_locator
2657 and milk.subinventory_code = p_supply_subinventory ;
2658
2659 end if;
2660 -- Get MRP Net Flag
2661
2662 if (p_mrp_net_flag is not null) then
2663 l_mrp_net_flag := 1;
2664 else
2665 l_mrp_net_flag := 2;
2666 end if;
2667
2668 if (p_material_release is null) then
2669 l_material_release := 'N';
2670 else
2671 if upper(p_material_release) = 'ON' then
2672 l_material_release := 'Y';
2673 else
2674 l_material_release := 'N';
2675 end if;
2676 end if;
2677
2678 /* To avoid the material allocation from the WO API
2679 ** for OneStep Issue page since allocation api will
2680 ** be called seperatly.
2681 */
2682 if(p_one_step_issue = fnd_api.g_true) then
2683 l_wo_changed := TRUE;
2684 select material_issue_by_mo into l_material_issue_by_mo_temp
2685 from wip_discrete_jobs
2686 where
2687 wip_entity_id = p_wip_entity_id and
2688 organization_id = p_organization_id;
2689 update wip_discrete_jobs set material_issue_by_mo='N'
2690 where
2691 wip_entity_id = p_wip_entity_id and
2692 organization_id = p_organization_id;
2693 end if; -- end of p_one_step_issue check
2694
2695
2696 IF (l_material_exists = 1) THEN
2697
2698 if (l_existing_operation <> p_operation_seq_num) then
2699 invalid_update_operation := 1;
2700 end if;
2701
2702 if (l_existing_department <> l_department_id ) then
2703 invalid_update_department := 1;
2704 end if;
2705
2706 if (l_existing_description <> p_description) then
2707 invalid_update_description := 1;
2708 end if;
2709
2710
2711 if ((invalid_update_operation = 0) and (invalid_update_department = 0)
2712 and (invalid_update_description = 0)) then
2713
2714 l_eam_mat_req_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_UPDATE;
2715 l_eam_mat_req_rec.wip_entity_id := p_wip_entity_id;
2716 l_eam_mat_req_rec.organization_id := p_organization_id;
2717 l_eam_mat_req_rec.operation_seq_num := p_operation_seq_num;
2718 l_eam_mat_req_rec.inventory_item_id := l_inventory_item_id;
2719 l_eam_mat_req_rec.quantity_per_assembly := p_quantity;
2720 l_eam_mat_req_rec.department_id := l_department_id;
2721 l_eam_mat_req_rec.wip_supply_type := l_supply;
2722 l_eam_mat_req_rec.date_required := p_required_date;
2723 l_eam_mat_req_rec.required_quantity := p_quantity;
2724 l_eam_mat_req_rec.supply_subinventory := p_supply_subinventory;
2725 l_eam_mat_req_rec.supply_locator_id := l_locator;
2726 l_eam_mat_req_rec.mrp_net_flag := l_mrp_net_flag;
2727 l_eam_mat_req_rec.comments := p_comments;
2728
2729
2730 l_eam_mat_req_tbl(1) := l_eam_mat_req_rec;
2731
2732 EAM_PROCESS_WO_PUB.Process_WO
2733 ( p_bo_identifier => 'EAM'
2734 , p_init_msg_list => TRUE
2735 , p_api_version_number => 1.0
2736 , p_commit => 'N'
2737 , p_eam_wo_rec => l_eam_wo_rec
2738 , p_eam_op_tbl => l_eam_op_tbl
2739 , p_eam_op_network_tbl => l_eam_op_network_tbl
2740 , p_eam_res_tbl => l_eam_res_tbl
2741 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
2742 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
2743 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
2744 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
2745 , p_eam_direct_items_tbl => l_eam_di_tbl
2746 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
2747 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
2748 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
2749 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
2750 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
2751 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
2752 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
2753 , p_eam_request_tbl => l_eam_request_tbl
2754 , x_eam_wo_rec => l_out_eam_wo_rec
2755 , x_eam_op_tbl => l_out_eam_op_tbl
2756 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
2757 , x_eam_res_tbl => l_out_eam_res_tbl
2758 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
2759 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
2760 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
2761 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
2762 , x_eam_direct_items_tbl => l_out_eam_di_tbl
2763 , x_eam_wo_comp_rec => l_out_eam_wo_comp_rec
2764 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
2765 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
2766 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
2767 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
2768 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
2769 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
2770 , x_eam_request_tbl => l_out_eam_request_tbl
2771 , x_return_status => x_return_status
2772 , x_msg_count => x_msg_count
2773 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
2774 , p_debug_filename => 'updatewro.log'
2775 , p_output_dir => l_output_dir
2776 , p_debug_file_mode => 'w'
2777 );
2778 l_update_status := 1;
2779
2780
2781 end if;
2782
2783 ELSE
2784
2785 -- If entry does not exists in WIP_REQUIREMENT_OPERATIONS then place a new
2786 -- entry into WIP_REQUIREMENT_OPERATIONS
2787
2788 l_eam_mat_req_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_CREATE;
2789 l_eam_mat_req_rec.wip_entity_id := p_wip_entity_id;
2790 l_eam_mat_req_rec.organization_id := p_organization_id;
2791 l_eam_mat_req_rec.operation_seq_num := p_operation_seq_num;
2792 l_eam_mat_req_rec.inventory_item_id := l_inventory_item_id;
2793 l_eam_mat_req_rec.quantity_per_assembly := p_quantity;
2794 l_eam_mat_req_rec.department_id := l_department_id;
2795 l_eam_mat_req_rec.wip_supply_type := l_supply;
2796 l_eam_mat_req_rec.date_required := p_required_date;
2797 l_eam_mat_req_rec.required_quantity := p_quantity;
2798 l_eam_mat_req_rec.supply_subinventory := p_supply_subinventory;
2799 l_eam_mat_req_rec.supply_locator_id := l_locator;
2800 l_eam_mat_req_rec.mrp_net_flag := l_mrp_net_flag;
2801 l_eam_mat_req_rec.comments := p_comments;
2802 l_eam_mat_req_rec.released_quantity := p_released_quantity;
2803
2804 l_eam_mat_req_tbl(1) := l_eam_mat_req_rec;
2805
2806
2807 EAM_PROCESS_WO_PUB.Process_WO
2808 ( p_bo_identifier => 'EAM'
2809 , p_init_msg_list => TRUE
2810 , p_api_version_number => 1.0
2811 , p_commit => 'N'
2812 , p_eam_wo_rec => l_eam_wo_rec
2813 , p_eam_op_tbl => l_eam_op_tbl
2814 , p_eam_op_network_tbl => l_eam_op_network_tbl
2815 , p_eam_res_tbl => l_eam_res_tbl
2816 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
2817 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
2818 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
2819 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
2820 , p_eam_direct_items_tbl => l_eam_di_tbl
2821 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
2822 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
2823 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
2824 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
2825 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
2826 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
2827 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
2828 , p_eam_request_tbl => l_eam_request_tbl
2829 , x_eam_wo_rec => l_out_eam_wo_rec
2830 , x_eam_op_tbl => l_out_eam_op_tbl
2831 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
2832 , x_eam_res_tbl => l_out_eam_res_tbl
2833 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
2834 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
2835 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
2836 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
2837 , x_eam_direct_items_tbl => l_out_eam_di_tbl
2838 , x_eam_wo_comp_rec => l_out_eam_wo_comp_rec
2839 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
2840 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
2841 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
2842 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
2843 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
2844 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
2845 , x_eam_request_tbl => l_out_eam_request_tbl
2846 , x_return_status => x_return_status
2847 , x_msg_count => x_msg_count
2848 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
2849 , p_debug_filename => 'insertwro.log'
2850 , p_output_dir => l_output_dir
2851 , p_debug_file_mode => 'w'
2852 );
2853
2854
2855
2856 END IF ; -- Material does not exist
2857
2858 /* To check whether the WDJ table was changed before call to WO API */
2859 if(l_wo_changed = true) then
2860 update wip_discrete_jobs set material_issue_by_mo=l_material_issue_by_mo_temp
2861 where
2862 wip_entity_id = p_wip_entity_id and
2863 organization_id = p_organization_id;
2864 end if; -- end of l_wo_changed check
2865
2866 x_invalid_update_operation := invalid_update_operation ;
2867 x_invalid_update_department := invalid_update_department;
2868 x_invalid_update_description := invalid_update_description;
2869 x_update_status := l_update_status;
2870
2871
2872 -- End of API body.
2873 -- Standard check of p_commit.
2874 IF fnd_api.to_boolean(p_commit)
2875 and x_return_status = 'S' THEN
2876 COMMIT WORK;
2877 END IF;
2878
2879 IF(x_return_status <> 'S') THEN
2880 ROLLBACK TO get_insert_into_wro_pvt;
2881 END IF;
2882
2883 l_stmt_num := 999;
2884 -- Standard call to get message count and if count is 1, get message info.
2885 fnd_msg_pub.count_and_get(
2886 p_count => x_msg_count
2887 ,p_data => x_msg_data);
2888 EXCEPTION
2889 WHEN fnd_api.g_exc_error THEN
2890 ROLLBACK TO get_insert_into_wro_pvt;
2891 x_return_status := fnd_api.g_ret_sts_error;
2892 fnd_msg_pub.count_and_get(
2893 -- p_encoded => FND_API.g_false
2894 p_count => x_msg_count
2895 ,p_data => x_msg_data);
2896 WHEN fnd_api.g_exc_unexpected_error THEN
2897 ROLLBACK TO get_insert_into_wro_pvt;
2898 x_return_status := fnd_api.g_ret_sts_unexp_error;
2899
2900 fnd_msg_pub.count_and_get(
2901 p_count => x_msg_count
2902 ,p_data => x_msg_data);
2903 WHEN OTHERS THEN
2904 ROLLBACK TO get_insert_into_wro_pvt;
2905 x_return_status := fnd_api.g_ret_sts_unexp_error;
2906 IF fnd_msg_pub.check_msg_level(
2907 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2908 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2909 END IF;
2910
2911 fnd_msg_pub.count_and_get(
2912 p_count => x_msg_count
2913 ,p_data => x_msg_data);
2914
2915
2916 END insert_into_wro;
2917
2918 --Start of bug 12631479
2919 --This procedure is not called in R12.This was added to maintain dual check in between R12->R12.1
2920 PROCEDURE insert_into_wro(
2921 p_api_version IN NUMBER
2922 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
2923 ,p_commit IN VARCHAR2 := fnd_api.g_false
2924 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
2925 ,p_wip_entity_id IN NUMBER
2926 ,p_organization_id IN NUMBER
2927 ,p_concatenated_segments IN VARCHAR2
2928 ,p_description IN VARCHAR2
2929 ,p_operation_seq_num IN NUMBER
2930 ,p_supply IN VARCHAR2
2931 ,p_required_date IN DATE
2932 ,p_quantity IN NUMBER
2933 ,p_comments IN VARCHAR2
2934 ,p_supply_subinventory IN VARCHAR2
2935 ,p_locator IN VARCHAR2
2936 ,p_mrp_net_flag IN VARCHAR2
2937 ,p_material_release IN VARCHAR2
2938 ,x_invalid_update_operation OUT NOCOPY NUMBER
2939 ,x_invalid_update_department OUT NOCOPY NUMBER
2940 ,x_invalid_update_description OUT NOCOPY NUMBER
2941 ,x_return_status OUT NOCOPY VARCHAR2
2942 ,x_msg_count OUT NOCOPY NUMBER
2943 ,x_msg_data OUT NOCOPY VARCHAR2
2944 ,x_update_status OUT NOCOPY NUMBER
2945 ,p_supply_code IN NUMBER :=NULL
2946 ,p_one_step_issue IN varchar2:=fnd_api.g_false
2947 ,p_released_quantity IN NUMBER := NULL
2948 ,p_attribute_category IN VARCHAR2
2949 ,p_attribute1 IN VARCHAR2
2950 ,p_attribute2 IN VARCHAR2
2951 ,p_attribute3 IN VARCHAR2
2952 ,p_attribute4 IN VARCHAR2
2953 ,p_attribute5 IN VARCHAR2
2954 ,p_attribute6 IN VARCHAR2
2955 ,p_attribute7 IN VARCHAR2
2956 ,p_attribute8 IN VARCHAR2
2957 ,p_attribute9 IN VARCHAR2
2958 ,p_attribute10 IN VARCHAR2
2959 ,p_attribute11 IN VARCHAR2
2960 ,p_attribute12 IN VARCHAR2
2961 ,p_attribute13 IN VARCHAR2
2962 ,p_attribute14 IN VARCHAR2
2963 ,p_attribute15 IN VARCHAR2
2964
2965
2966 )
2967
2968 IS
2969 l_api_name CONSTANT VARCHAR2(30) := 'insert_into_wro';
2970 l_api_version CONSTANT NUMBER := 1.0;
2971 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
2972
2973 l_stmt_num NUMBER;
2974 l_wip_entity_id NUMBER;
2975 l_inventory_item_id NUMBER;
2976 l_department_id NUMBER;
2977 l_supply NUMBER;
2978 l_locator NUMBER;
2979 l_mrp_net_flag NUMBER;
2980 l_material_release VARCHAR2(1);
2981 l_material_exists NUMBER := 0;
2982 l_existing_operation NUMBER;
2983 l_existing_department NUMBER;
2984 l_existing_description VARCHAR2(240);
2985 l_req_qty NUMBER := 0;
2986 l_status_type NUMBER := 0;
2987 l_material_issue_by_mo VARCHAR2(1);
2988 l_auto_request_material VARCHAR2(1);
2989 invalid_update_operation NUMBER := 0;
2990 invalid_update_department NUMBER := 0;
2991 invalid_update_description NUMBER := 0;
2992 l_update_status NUMBER := 0;
2993 l_return_status NUMBER := 0;
2994 l_msg_count NUMBER := 0;
2995 l_msg_data VARCHAR2(2000) ;
2996 l_return_status1 VARCHAR2(30) ;
2997 l_material_issue_by_mo_temp VARCHAR2(1) ;
2998 l_wo_changed BOOLEAN := FALSE;
2999
3000
3001
3002 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
3003 l_eam_mat_req_rec EAM_PROCESS_WO_PUB.eam_mat_req_rec_type;
3004 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
3005 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
3006 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
3007 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
3008 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
3009 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
3010 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
3011 l_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
3012 l_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
3013 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
3014 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
3015 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
3016 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
3017 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
3018 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
3019 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
3020
3021 l_out_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
3022 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
3023 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
3024 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
3025 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
3026 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
3027 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
3028 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
3029 l_out_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
3030 l_out_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
3031 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
3032 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
3033 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
3034 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
3035 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
3036 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
3037 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
3038
3039 l_output_dir VARCHAR2(512);
3040
3041 BEGIN
3042
3043 -- Standard Start of API savepoint
3044 l_stmt_num := 10;
3045 SAVEPOINT get_insert_into_wro_pvt;
3046
3047 l_stmt_num := 20;
3048 -- Standard call to check for call compatibility.
3049 IF NOT fnd_api.compatible_api_call(
3050 l_api_version
3051 ,p_api_version
3052 ,l_api_name
3053 ,g_pkg_name) THEN
3054 RAISE fnd_api.g_exc_unexpected_error;
3055 END IF;
3056
3057 l_stmt_num := 30;
3058 -- Initialize message list if p_init_msg_list is set to TRUE.
3059 IF fnd_api.to_boolean(p_init_msg_list) THEN
3060 fnd_msg_pub.initialize;
3061 END IF;
3062
3063 l_stmt_num := 40;
3064 -- Initialize API return status to success
3065 x_return_status := fnd_api.g_ret_sts_success;
3066
3067 l_stmt_num := 50;
3068 -- API body
3069
3070 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
3071
3072
3073 l_wip_entity_id := p_wip_entity_id ;
3074
3075 -- Get Inventory Item Id
3076 select inventory_item_id
3077 into l_inventory_item_id
3078 from mtl_system_items_kfv
3079 where concatenated_segments = p_concatenated_segments
3080 and organization_id = p_organization_id;
3081
3082 begin
3083
3084 select 1, wro.operation_seq_num,wro.department_id,msikfv.description
3085 into l_material_exists, l_existing_operation, l_existing_department, l_existing_description
3086 from wip_requirement_operations wro, mtl_system_items_kfv msikfv
3087 where wro.inventory_item_id = l_inventory_item_id
3088 and wro.organization_id = p_organization_id
3089 and wro.wip_entity_id = p_wip_entity_id
3090 and wro.organization_id = msikfv.organization_id
3091 and wro.operation_seq_num = p_operation_seq_num
3092 and wro.inventory_item_id = msikfv.inventory_item_id;
3093
3094 exception
3095 when others then
3096 null;
3097 end;
3098
3099 -- Get Department Id
3100 select department_id
3101 into l_department_id
3102 from wip_operations
3103 where wip_entity_id = l_wip_entity_id
3104 and operation_seq_num = p_operation_seq_num
3105 and organization_id = p_organization_id;
3106
3107 -- Get Supply TYpe
3108 if(p_supply is not null) then
3109 select lookup_code
3110 into l_supply
3111 from mfg_lookups
3112 where lookup_type = g_supply_type
3113 and meaning = p_supply;
3114 else
3115 l_supply := p_supply_code;
3116 end if;
3117 -- Get Locator Id
3118 if (p_locator is not null) then
3119
3120 /*select inventory_location_id
3121 into l_locator
3122 from mtl_item_locations_kfv
3123 where organization_id = p_organization_id
3124 and concatenated_segments = p_locator
3125 and subinventory_code = p_supply_subinventory ;*/
3126
3127 select milk.inventory_location_id
3128 into l_locator
3129 from mtl_item_locations_kfv milk
3130 where milk.organization_id = p_organization_id
3131 and inv_project.Get_locator(milk.inventory_location_id, milk.organization_id) = p_locator
3132 and milk.subinventory_code = p_supply_subinventory ;
3133
3134 end if;
3135 -- Get MRP Net Flag
3136
3137 if (p_mrp_net_flag is not null) then
3138 l_mrp_net_flag := 1;
3139 else
3140 l_mrp_net_flag := 2;
3141 end if;
3142
3143 if (p_material_release is null) then
3144 l_material_release := 'N';
3145 else
3146 if upper(p_material_release) = 'ON' then
3147 l_material_release := 'Y';
3148 else
3149 l_material_release := 'N';
3150 end if;
3151 end if;
3152
3153 /* To avoid the material allocation from the WO API
3154 ** for OneStep Issue page since allocation api will
3155 ** be called seperatly.
3156 */
3157 if(p_one_step_issue = fnd_api.g_true) then
3158 l_wo_changed := TRUE;
3159 select material_issue_by_mo into l_material_issue_by_mo_temp
3160 from wip_discrete_jobs
3161 where
3162 wip_entity_id = p_wip_entity_id and
3163 organization_id = p_organization_id;
3164 update wip_discrete_jobs set material_issue_by_mo='N'
3165 where
3166 wip_entity_id = p_wip_entity_id and
3167 organization_id = p_organization_id;
3168 end if; -- end of p_one_step_issue check
3169
3170
3171 IF (l_material_exists = 1) THEN
3172
3173 if (l_existing_operation <> p_operation_seq_num) then
3174 invalid_update_operation := 1;
3175 end if;
3176
3177 if (l_existing_department <> l_department_id ) then
3178 invalid_update_department := 1;
3179 end if;
3180
3181 if (l_existing_description <> p_description) then
3182 invalid_update_description := 1;
3183 end if;
3184
3185 if ((invalid_update_operation = 0) and (invalid_update_department = 0)
3186 and (invalid_update_description = 0)) then
3187
3188 l_eam_mat_req_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_UPDATE;
3189 l_eam_mat_req_rec.wip_entity_id := p_wip_entity_id;
3190 l_eam_mat_req_rec.organization_id := p_organization_id;
3191 l_eam_mat_req_rec.operation_seq_num := p_operation_seq_num;
3192 l_eam_mat_req_rec.inventory_item_id := l_inventory_item_id;
3193 l_eam_mat_req_rec.quantity_per_assembly := p_quantity;
3194 l_eam_mat_req_rec.department_id := l_department_id;
3195 l_eam_mat_req_rec.wip_supply_type := l_supply;
3196 l_eam_mat_req_rec.date_required := p_required_date;
3197 l_eam_mat_req_rec.required_quantity := p_quantity;
3198 l_eam_mat_req_rec.supply_subinventory := p_supply_subinventory;
3199 l_eam_mat_req_rec.supply_locator_id := l_locator;
3200 l_eam_mat_req_rec.mrp_net_flag := l_mrp_net_flag;
3201 l_eam_mat_req_rec.comments := p_comments;
3202 l_eam_mat_req_rec.attribute_category:=p_attribute_category;
3203 l_eam_mat_req_rec.attribute1:=p_attribute1;
3204 l_eam_mat_req_rec.attribute2:=p_attribute2;
3205 l_eam_mat_req_rec.attribute3:=p_attribute3;
3206 l_eam_mat_req_rec.attribute4:=p_attribute4;
3207 l_eam_mat_req_rec.attribute5:=p_attribute5;
3208 l_eam_mat_req_rec.attribute6:=p_attribute6;
3209 l_eam_mat_req_rec.attribute7:=p_attribute7;
3210 l_eam_mat_req_rec.attribute8:=p_attribute8;
3211 l_eam_mat_req_rec.attribute9:=p_attribute9;
3212 l_eam_mat_req_rec.attribute10:=p_attribute10;
3213 l_eam_mat_req_rec.attribute11:=p_attribute11;
3214 l_eam_mat_req_rec.attribute12:=p_attribute12;
3215 l_eam_mat_req_rec.attribute13:=p_attribute13;
3216 l_eam_mat_req_rec.attribute14:=p_attribute14;
3217 l_eam_mat_req_rec.attribute15:=p_attribute15;
3218
3219
3220 l_eam_mat_req_tbl(1) := l_eam_mat_req_rec;
3221
3222 EAM_PROCESS_WO_PUB.Process_WO
3223 ( p_bo_identifier => 'EAM'
3224 , p_init_msg_list => TRUE
3225 , p_api_version_number => 1.0
3226 , p_commit => 'N'
3227 , p_eam_wo_rec => l_eam_wo_rec
3228 , p_eam_op_tbl => l_eam_op_tbl
3229 , p_eam_op_network_tbl => l_eam_op_network_tbl
3230 , p_eam_res_tbl => l_eam_res_tbl
3231 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
3232 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
3233 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
3234 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
3235 , p_eam_direct_items_tbl => l_eam_di_tbl
3236 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
3237 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
3238 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
3239 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
3240 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
3241 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
3242 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
3243 , p_eam_request_tbl => l_eam_request_tbl
3244 , x_eam_wo_rec => l_out_eam_wo_rec
3245 , x_eam_op_tbl => l_out_eam_op_tbl
3246 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
3247 , x_eam_res_tbl => l_out_eam_res_tbl
3248 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
3249 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
3250 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
3251 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
3252 , x_eam_direct_items_tbl => l_out_eam_di_tbl
3253 , x_eam_wo_comp_rec => l_out_eam_wo_comp_rec
3254 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
3255 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
3256 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
3257 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
3258 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
3259 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
3260 , x_eam_request_tbl => l_out_eam_request_tbl
3261 , x_return_status => x_return_status
3262 , x_msg_count => x_msg_count
3263 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
3264 , p_debug_filename => 'updatewro.log'
3265 , p_output_dir => l_output_dir
3266 , p_debug_file_mode => 'w'
3267 );
3268 l_update_status := 1;
3269
3270
3271 end if;
3272
3273 ELSE
3274
3275 -- If entry does not exists in WIP_REQUIREMENT_OPERATIONS then place a new
3276 -- entry into WIP_REQUIREMENT_OPERATIONS
3277
3278 l_eam_mat_req_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_CREATE;
3279 l_eam_mat_req_rec.wip_entity_id := p_wip_entity_id;
3280 l_eam_mat_req_rec.organization_id := p_organization_id;
3281 l_eam_mat_req_rec.operation_seq_num := p_operation_seq_num;
3282 l_eam_mat_req_rec.inventory_item_id := l_inventory_item_id;
3283 l_eam_mat_req_rec.quantity_per_assembly := p_quantity;
3284 l_eam_mat_req_rec.department_id := l_department_id;
3285 l_eam_mat_req_rec.wip_supply_type := l_supply;
3286 l_eam_mat_req_rec.date_required := p_required_date;
3287 l_eam_mat_req_rec.required_quantity := p_quantity;
3288 l_eam_mat_req_rec.supply_subinventory := p_supply_subinventory;
3289 l_eam_mat_req_rec.supply_locator_id := l_locator;
3290 l_eam_mat_req_rec.mrp_net_flag := l_mrp_net_flag;
3291 l_eam_mat_req_rec.comments := p_comments;
3292 l_eam_mat_req_rec.released_quantity := p_released_quantity;
3293
3294
3295 l_eam_mat_req_rec.attribute_category:=p_attribute_category;
3296 l_eam_mat_req_rec.attribute1:=p_attribute1;
3297 l_eam_mat_req_rec.attribute2:=p_attribute2;
3298 l_eam_mat_req_rec.attribute3:=p_attribute3;
3299 l_eam_mat_req_rec.attribute4:=p_attribute4;
3300 l_eam_mat_req_rec.attribute5:=p_attribute5;
3301 l_eam_mat_req_rec.attribute6:=p_attribute6;
3302 l_eam_mat_req_rec.attribute7:=p_attribute7;
3303 l_eam_mat_req_rec.attribute8:=p_attribute8;
3304 l_eam_mat_req_rec.attribute9:=p_attribute9;
3305 l_eam_mat_req_rec.attribute10:=p_attribute10;
3306 l_eam_mat_req_rec.attribute11:=p_attribute11;
3307 l_eam_mat_req_rec.attribute12:=p_attribute12;
3308 l_eam_mat_req_rec.attribute13:=p_attribute13;
3309 l_eam_mat_req_rec.attribute14:=p_attribute14;
3310 l_eam_mat_req_rec.attribute15:=p_attribute15;
3311
3312 l_eam_mat_req_tbl(1) := l_eam_mat_req_rec;
3313
3314
3315
3316 EAM_PROCESS_WO_PUB.Process_WO
3317 ( p_bo_identifier => 'EAM'
3318 , p_init_msg_list => TRUE
3319 , p_api_version_number => 1.0
3320 , p_commit => 'N'
3321 , p_eam_wo_rec => l_eam_wo_rec
3322 , p_eam_op_tbl => l_eam_op_tbl
3323 , p_eam_op_network_tbl => l_eam_op_network_tbl
3324 , p_eam_res_tbl => l_eam_res_tbl
3325 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
3326 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
3327 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
3328 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
3329 , p_eam_direct_items_tbl => l_eam_di_tbl
3330 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
3331 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
3332 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
3333 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
3334 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
3335 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
3336 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
3337 , p_eam_request_tbl => l_eam_request_tbl
3338 , x_eam_wo_rec => l_out_eam_wo_rec
3339 , x_eam_op_tbl => l_out_eam_op_tbl
3340 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
3341 , x_eam_res_tbl => l_out_eam_res_tbl
3342 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
3343 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
3344 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
3345 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
3346 , x_eam_direct_items_tbl => l_out_eam_di_tbl
3347 , x_eam_wo_comp_rec => l_out_eam_wo_comp_rec
3348 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
3349 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
3350 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
3351 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
3352 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
3353 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
3354 , x_eam_request_tbl => l_out_eam_request_tbl
3355 , x_return_status => x_return_status
3356 , x_msg_count => x_msg_count
3357 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
3358 , p_debug_filename => 'insertwro.log'
3359 , p_output_dir => l_output_dir
3360 , p_debug_file_mode => 'w'
3361 );
3362
3363
3364
3365 END IF ; -- Material does not exist
3366
3367 /* To check whether the WDJ table was changed before call to WO API */
3368 if(l_wo_changed = true) then
3369 update wip_discrete_jobs set material_issue_by_mo=l_material_issue_by_mo_temp
3370 where
3371 wip_entity_id = p_wip_entity_id and
3372 organization_id = p_organization_id;
3373 end if; -- end of l_wo_changed check
3374
3375 x_invalid_update_operation := invalid_update_operation ;
3376 x_invalid_update_department := invalid_update_department;
3377 x_invalid_update_description := invalid_update_description;
3378 x_update_status := l_update_status;
3379
3380
3381 -- End of API body.
3382 -- Standard check of p_commit.
3383 IF fnd_api.to_boolean(p_commit)
3384 and x_return_status = 'S' THEN
3385 COMMIT WORK;
3386 END IF;
3387
3388 IF(x_return_status <> 'S') THEN
3389 ROLLBACK TO get_insert_into_wro_pvt;
3390 END IF;
3391
3392 l_stmt_num := 999;
3393 -- Standard call to get message count and if count is 1, get message info.
3394 fnd_msg_pub.count_and_get(
3395 p_count => x_msg_count
3396 ,p_data => x_msg_data);
3397 EXCEPTION
3398 WHEN fnd_api.g_exc_error THEN
3399 ROLLBACK TO get_insert_into_wro_pvt;
3400 x_return_status := fnd_api.g_ret_sts_error;
3401 fnd_msg_pub.count_and_get(
3402 -- p_encoded => FND_API.g_false
3403 p_count => x_msg_count
3404 ,p_data => x_msg_data);
3405 WHEN fnd_api.g_exc_unexpected_error THEN
3406 ROLLBACK TO get_insert_into_wro_pvt;
3407 x_return_status := fnd_api.g_ret_sts_unexp_error;
3408
3409 fnd_msg_pub.count_and_get(
3410 p_count => x_msg_count
3411 ,p_data => x_msg_data);
3412 WHEN OTHERS THEN
3413 ROLLBACK TO get_insert_into_wro_pvt;
3414 x_return_status := fnd_api.g_ret_sts_unexp_error;
3415 IF fnd_msg_pub.check_msg_level(
3416 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3417 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3418 END IF;
3419
3420 fnd_msg_pub.count_and_get(
3421 p_count => x_msg_count
3422 ,p_data => x_msg_data);
3423
3424
3425 END insert_into_wro;
3426 --End of bug 12631479
3427
3428
3429 PROCEDURE delete_resources (
3430 p_api_version IN NUMBER
3431 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
3432 ,p_commit IN VARCHAR2 := fnd_api.g_false
3433 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
3434 ,p_wip_entity_id IN NUMBER
3435 ,p_operation_seq_num IN NUMBER
3436 ,p_resource_seq_num IN NUMBER
3437 ,x_return_status OUT NOCOPY VARCHAR2
3438 ,x_msg_count OUT NOCOPY NUMBER
3439 ,x_msg_data OUT NOCOPY VARCHAR2) IS
3440
3441
3442 l_api_name CONSTANT VARCHAR2(30) := 'delete_resources';
3443 l_api_version CONSTANT NUMBER := 1.0;
3444 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
3445
3446 l_stmt_num NUMBER;
3447 l_wip_entity_id NUMBER;
3448 l_operation_seq_num NUMBER;
3449 l_resource_seq_num NUMBER;
3450 l_organization_id NUMBER;
3451 l_resource_id NUMBER;
3452 l_applied_units NUMBER;
3453 l_exists NUMBER := 0;
3454 l_msg_count NUMBER;
3455 l_msg_data VARCHAR2(250);
3456 l_data VARCHAR2(250);
3457 l_msg_index_out NUMBER;
3458
3459 l_validate_st NUMBER := 0;
3460
3461
3462 /* added for calling WO API */
3463
3464 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
3465 l_eam_res_rec EAM_PROCESS_WO_PUB.eam_res_rec_type;
3466 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
3467 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
3468 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
3469 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
3470 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
3471 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
3472 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
3473 l_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
3474 l_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
3475 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
3476 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
3477 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
3478 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
3479 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
3480 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
3481 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
3482
3483 l_out_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
3484 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
3485 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
3486 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
3487 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
3488 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
3489 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
3490 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
3491 l_out_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
3492 l_out_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
3493 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
3494 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
3495 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
3496 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
3497 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
3498 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
3499 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
3500
3501 l_output_dir VARCHAR2(512);
3502
3503 BEGIN
3504 -- Standard Start of API savepoint
3505 l_stmt_num := 10;
3506 SAVEPOINT get_delete_resources_pvt;
3507
3508 l_stmt_num := 20;
3509 -- Standard call to check for call compatibility.
3510 IF NOT fnd_api.compatible_api_call(
3511 l_api_version
3512 ,p_api_version
3513 ,l_api_name
3514 ,g_pkg_name) THEN
3515 RAISE fnd_api.g_exc_unexpected_error;
3516 END IF;
3517
3518 l_stmt_num := 30;
3519
3520 -- Initialize message list if p_init_msg_list is set to TRUE.
3521 IF fnd_api.to_boolean(p_init_msg_list) THEN
3522 fnd_msg_pub.initialize;
3523 END IF;
3524
3525 l_stmt_num := 40;
3526 -- Initialize API return status to success
3527 x_return_status := fnd_api.g_ret_sts_success;
3528
3529 l_stmt_num := 50;
3530 -- API body
3531
3532 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
3533
3534
3535 l_wip_entity_id := p_wip_entity_id;
3536 l_operation_seq_num := p_operation_seq_num;
3537 l_resource_seq_num := p_resource_seq_num;
3538 if ((l_wip_entity_id is not null) AND (l_operation_seq_num is not null) and (l_resource_seq_num is not null)) then
3539
3540 begin
3541
3542 select organization_id, resource_id
3543 into l_organization_id, l_resource_id
3544 from wip_operation_resources
3545 where wip_entity_id = l_wip_entity_id
3546 and operation_seq_num = l_operation_seq_num
3547 and resource_seq_num = l_resource_seq_num;
3548
3549 exception
3550 when others then
3551 null;
3552 end;
3553
3554 end if;
3555
3556
3557 if (( l_resource_id is not null) AND (l_resource_seq_num is not null) AND (l_operation_seq_num is not null)) then
3558
3559 --check if there are any instances attached to the resource
3560 select count(*)
3561 into l_exists
3562 from wip_op_resource_instances
3563 where wip_entity_id = l_wip_entity_id and
3564 operation_seq_num = l_operation_seq_num and
3565 resource_seq_num = l_resource_seq_num;
3566
3567 if(l_exists <> 0) then
3568
3569 l_validate_st := 1;
3570 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_INSTANCES_EXIST');
3571 x_return_status := FND_API.G_RET_STS_ERROR;
3572 end if;
3573
3574 end if; -- End of l_resource_id is not null ........
3575
3576 -- if validate not passed then raise error
3577 l_msg_count := FND_MSG_PUB.count_msg;
3578 IF l_msg_count = 1 THEN
3579
3580 eam_execution_jsp.Get_Messages
3581 (p_encoded => FND_API.G_FALSE,
3582 p_msg_index => 1,
3583 p_msg_count => l_msg_count,
3584 p_msg_data => l_msg_data,
3585 p_data => l_data,
3586 p_msg_index_out => l_msg_index_out);
3587 x_msg_count := l_msg_count;
3588 x_msg_data := l_msg_data;
3589 ELSE
3590 x_msg_count := l_msg_count;
3591 END IF;
3592
3593 IF l_msg_count > 0 THEN
3594 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3595 RAISE FND_API.G_EXC_ERROR;
3596 END IF;
3597
3598
3599
3600 -- Perform delete if all the validations have passed
3601
3602 if (l_validate_st = 0) then
3603 l_eam_res_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_DELETE;
3604 l_eam_res_rec.wip_entity_id := p_wip_entity_id;
3605 l_eam_res_rec.organization_id := l_organization_id;
3606 l_eam_res_rec.operation_seq_num := p_operation_seq_num;
3607 l_eam_res_rec.resource_seq_num := p_resource_seq_num;
3608 l_eam_res_rec.resource_id := l_resource_id;
3609
3610 l_eam_res_tbl(1) := l_eam_res_rec ;
3611
3612 EAM_PROCESS_WO_PUB.Process_WO
3613 ( p_bo_identifier => 'EAM'
3614 , p_init_msg_list => TRUE
3615 , p_api_version_number => 1.0
3616 , p_commit => 'N'
3617 , p_eam_wo_rec => l_eam_wo_rec
3618 , p_eam_op_tbl => l_eam_op_tbl
3619 , p_eam_op_network_tbl => l_eam_op_network_tbl
3620 , p_eam_res_tbl => l_eam_res_tbl
3621 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
3622 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
3623 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
3624 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
3625 , p_eam_direct_items_tbl => l_eam_di_tbl
3626 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
3627 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
3628 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
3629 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
3630 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
3631 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
3632 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
3633 , p_eam_request_tbl => l_eam_request_tbl
3634 , x_eam_wo_rec => l_out_eam_wo_rec
3635 , x_eam_op_tbl => l_out_eam_op_tbl
3636 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
3637 , x_eam_res_tbl => l_out_eam_res_tbl
3638 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
3639 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
3640 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
3641 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
3642 , x_eam_direct_items_tbl => l_out_eam_di_tbl
3643 , x_eam_wo_comp_rec => l_out_eam_wo_comp_rec
3644 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
3645 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
3646 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
3647 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
3648 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
3649 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
3650 , x_eam_request_tbl => l_out_eam_request_tbl
3651 , x_return_status => x_return_status
3652 , x_msg_count => x_msg_count
3653 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
3654 , p_debug_filename => 'delwor.log'
3655 , p_output_dir => l_output_dir
3656 , p_debug_file_mode => 'w'
3657 );
3658
3659 end if;
3660
3661
3662
3663 -- End of API body.
3664 -- Standard check of p_commit.
3665 IF fnd_api.to_boolean(p_commit)
3666 and x_return_status = 'S' THEN
3667 COMMIT WORK;
3668 END IF;
3669
3670 IF(x_return_status <> 'S') THEN
3671 ROLLBACK TO get_delete_resources_pvt;
3672 END IF;
3673
3674 l_stmt_num := 999;
3675
3676 -- Standard call to get message count and if count is 1, get message info.
3677 fnd_msg_pub.count_and_get(
3678 p_count => x_msg_count
3679 ,p_data => x_msg_data);
3680
3681 EXCEPTION
3682 WHEN fnd_api.g_exc_error THEN
3683 ROLLBACK TO get_delete_resources_pvt;
3684 x_return_status := fnd_api.g_ret_sts_error;
3685 fnd_msg_pub.count_and_get(
3686 -- p_encoded => FND_API.g_false
3687 p_count => x_msg_count
3688 ,p_data => x_msg_data);
3689
3690 WHEN fnd_api.g_exc_unexpected_error THEN
3691 ROLLBACK TO get_delete_resources_pvt;
3692 x_return_status := fnd_api.g_ret_sts_unexp_error;
3693
3694 fnd_msg_pub.count_and_get(
3695 p_count => x_msg_count
3696 ,p_data => x_msg_data);
3697
3698 WHEN OTHERS THEN
3699 ROLLBACK TO get_delete_resources_pvt;
3700 x_return_status := fnd_api.g_ret_sts_unexp_error;
3701 IF fnd_msg_pub.check_msg_level(
3702 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3703 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3704 END IF;
3705
3706 fnd_msg_pub.count_and_get(
3707 p_count => x_msg_count
3708 ,p_data => x_msg_data);
3709
3710 END delete_resources;
3711
3712
3713 --------------------------------------------------------------------------
3714 -- Procedure to validate department
3715 -- Used in Operations Page
3716 -- Author : rethakur
3717 --------------------------------------------------------------------------
3718
3719 procedure validate_dept ( p_wip_entity_id IN NUMBER
3720 ,p_operation_seq_num IN NUMBER
3721 ,p_organization_id IN NUMBER
3722 ,p_department_code IN VARCHAR2
3723 ,x_department_id OUT NOCOPY NUMBER
3724 ,x_return_status OUT NOCOPY NUMBER) IS
3725
3726 l_department_id NUMBER := null;
3727 l_return_status NUMBER := 0;
3728
3729 BEGIN
3730
3731 SELECT department_id
3732 INTO l_department_id
3733 FROM BOM_DEPARTMENTS bd
3734 WHERE bd.organization_id = p_organization_id
3735 AND department_code = p_department_code
3736 AND NVL (bd.disable_date, sysdate+2) > sysdate
3737 AND NOT EXISTS
3738 (
3739 SELECT '1'
3740 FROM WIP_OPERATION_RESOURCES wor
3741 WHERE wor.organization_id = p_organization_id
3742 AND wor.wip_entity_id = p_wip_entity_id
3743 AND wor.operation_seq_num = p_operation_seq_num
3744 AND wor.resource_id not in
3745 (
3746 SELECT bdr.resource_id
3747 FROM BOM_DEPARTMENT_RESOURCES bdr
3748 WHERE bdr.department_id = bd.department_id
3749 )
3750 );
3751
3752 x_return_status := l_return_status;
3753 x_department_id := l_department_id;
3754
3755 EXCEPTION
3756 WHEN NO_DATA_FOUND THEN
3757 l_return_status := 1;
3758 x_return_status := l_return_status ;
3759 x_department_id := null;
3760
3761 END validate_dept;
3762
3763
3764 --------------------------------------------------------------------------
3765 -- Procedure to validate shutdown type
3766 -- Used in Operations Page
3767 -- Author : rethakur
3768 --------------------------------------------------------------------------
3769
3770 procedure validate_shutdown_type ( p_meaning IN VARCHAR2
3771 ,x_lookup_code OUT NOCOPY NUMBER
3772 ,x_return_status OUT NOCOPY NUMBER) IS
3773
3774 l_meaning VARCHAR2(80);
3775 l_lookup_code NUMBER := 0;
3776 l_return_status NUMBER := 0;
3777
3778 BEGIN
3779
3780 SELECT lookup_code
3781 INTO l_lookup_code
3782 FROM MFG_LOOKUPS
3783 WHERE lookup_type = g_shutdown_type
3784 AND meaning = p_meaning ;
3785
3786 x_return_status := l_return_status;
3787 x_lookup_code := l_lookup_code;
3788
3789 EXCEPTION
3790 WHEN NO_DATA_FOUND THEN
3791 l_return_status := 1;
3792 x_return_status := l_return_status ;
3793 x_lookup_code := null;
3794
3795 END validate_shutdown_type;
3796
3797
3798
3799 --------------------------------------------------------------------------
3800 -- Procedure to validate standard operation
3801 -- Used in Operations Page
3802 -- Author : rethakur
3803 --------------------------------------------------------------------------
3804
3805 procedure validate_std_operation ( p_organization_id IN NUMBER
3806 ,p_operation_code IN VARCHAR2
3807 ,x_standard_operation_id OUT NOCOPY NUMBER
3808 ,x_department_id OUT NOCOPY NUMBER
3809 ,x_shutdown_type OUT NOCOPY VARCHAR2
3810 ,x_return_status OUT NOCOPY NUMBER) IS
3811
3812 l_standard_operation_id NUMBER := null;
3813 l_department_id NUMBER := null;
3814 l_shutdown_type VARCHAR2(10);
3815 l_return_status NUMBER := 0;
3816
3817 BEGIN
3818
3819 SELECT bdp.department_id, bso.standard_operation_id,
3820 bso.shutdown_type
3821 INTO l_department_id, l_standard_operation_id,
3822 l_shutdown_type
3823 FROM BOM_DEPARTMENTS bdp,
3824 BOM_STANDARD_OPERATIONS bso
3825 WHERE bso.organization_id = p_organization_id
3826 AND bso.operation_code = p_operation_code
3827 AND bso.line_id IS NULL
3828 AND NVL ( bso.operation_type, 1) = 1
3829 AND bdp.organization_id = p_organization_id
3830 AND bso.department_id = bdp.department_id
3831 AND NVL ( bdp.disable_date, sysdate + 2) > sysdate ;
3832
3833 x_return_status := l_return_status;
3834 x_department_id := l_department_id;
3835 x_standard_operation_id := l_standard_operation_id;
3836 x_shutdown_type := l_shutdown_type;
3837
3838 EXCEPTION
3839 WHEN NO_DATA_FOUND THEN
3840 l_return_status := 1;
3841 x_return_status := l_return_status ;
3842 x_department_id := null;
3843 x_standard_operation_id := null;
3844 x_shutdown_type := null;
3845 END validate_std_operation;
3846
3847 --------------------------------------------------------------------------
3848 -- Procedure to add an operation to a work order
3849 -- Used in Operations Page
3850 -- Author : rethakur
3851 --------------------------------------------------------------------------
3852 procedure insert_into_wo ( p_wip_entity_id IN NUMBER
3853 ,p_operation_seq_num IN NUMBER
3854 ,p_standard_operation_id IN NUMBER
3855 ,p_organization_id IN NUMBER
3856 ,p_description IN VARCHAR2
3857 ,p_department_id IN NUMBER
3858 ,p_shutdown_type IN VARCHAR2
3859 ,p_first_unit_start_date IN VARCHAR2
3860 ,p_last_unit_completion_date IN VARCHAR2
3861 ,p_duration IN NUMBER
3862 ,p_long_description IN VARCHAR2 := null
3863 ,x_return_status OUT NOCOPY NUMBER
3864 ,x_msg_count OUT NOCOPY NUMBER ) IS
3865
3866
3867 l_return_status VARCHAR2(1);
3868 x_row_id VARCHAR2(250);
3869 l_first_unit_start_date DATE := SYSDATE;
3870 l_last_unit_completion_date DATE := SYSDATE;
3871 l_duration NUMBER := 0;
3872
3873
3874
3875 /* Added for WO API */
3876
3877 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
3878 l_eam_op_rec EAM_PROCESS_WO_PUB.eam_op_rec_type;
3879 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
3880 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
3881 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
3882 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
3883 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
3884 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
3885 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
3886 l_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
3887 l_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
3888 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
3889 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
3890 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
3891 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
3892 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
3893 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
3894 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
3895
3896 l_out_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
3897 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
3898 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
3899 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
3900 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
3901 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
3902 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
3903 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
3904 l_out_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
3905 l_out_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
3906 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
3907 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
3908 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
3909 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
3910 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
3911 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
3912 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
3913
3914 l_output_dir VARCHAR2(512);
3915 invalid_autochrg_exp EXCEPTION;
3916
3917 CURSOR chk_autocharge IS
3918 SELECT 1
3919 FROM bom_standard_operations bso,
3920 bom_std_op_resources bsor
3921 WHERE bso.standard_operation_id = bsor.standard_operation_id
3922 AND bsor.standard_operation_id = p_standard_operation_id
3923 AND bso.organization_id = p_organization_id
3924 AND bsor.autocharge_type NOT IN (2,3);
3925
3926 BEGIN
3927 -- Fix for Bug 3582756
3928 SAVEPOINT label_insert_into_wo;
3929
3930 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
3931
3932
3933 IF (p_first_unit_start_date is NOT NULL AND p_last_unit_completion_date is NOT NULL) THEN
3934 l_first_unit_start_date := to_date(p_first_unit_start_date,'YYYY/MM/DD HH24:MI:SS'); --,WIP_CONSTANTS.DATETIME_FMT);
3935 l_last_unit_completion_date := to_date(p_last_unit_completion_date,'YYYY/MM/DD HH24:MI:SS'); --,WIP_CONSTANTS.DATETIME_FMT);
3936 ELSIF ( p_last_unit_completion_date is NULL) THEN
3937 l_duration := p_duration/24;
3938 l_first_unit_start_date := to_date(p_first_unit_start_date,'YYYY/MM/DD HH24:MI:SS'); --,WIP_CONSTANTS.DATETIME_FMT);
3939 l_last_unit_completion_date := l_first_unit_start_date + l_duration;
3940 ELSIF ( p_first_unit_start_date is NULL) THEN
3941 l_duration := p_duration/24;
3942 l_last_unit_completion_date := to_date(p_last_unit_completion_date,'YYYY/MM/DD HH24:MI:SS'); --,WIP_CONSTANTS.DATETIME_FMT);
3943 l_first_unit_start_date := l_last_unit_completion_date + l_duration;
3944 END IF ; /* end if of duration check if */
3945 l_eam_op_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_CREATE;
3946 l_eam_op_rec.wip_entity_id := p_wip_entity_id;
3947 l_eam_op_rec.organization_id := p_organization_id;
3948 l_eam_op_rec.operation_seq_num := p_operation_seq_num;
3949 l_eam_op_rec.description := p_description;
3950 l_eam_op_rec.long_description := p_long_description;
3951 l_eam_op_rec.shutdown_type := p_shutdown_type;
3952 l_eam_op_rec.start_date := l_first_unit_start_date;
3953 l_eam_op_rec.completion_date := l_last_unit_completion_date;
3954 if ( nvl(p_standard_operation_id,0)= 0 ) then -- added OR clause for bug#3541316
3955 l_eam_op_rec.standard_operation_id := null ;
3956 else -- added else clause for bug#3518663
3957 l_eam_op_rec.standard_operation_id := p_standard_operation_id;
3958 end if;
3959 l_eam_op_rec.department_id := p_department_id;
3960
3961 l_eam_op_tbl(1) := l_eam_op_rec ;
3962
3963 EAM_PROCESS_WO_PUB.Process_WO
3964 ( p_bo_identifier => 'EAM'
3965 , p_init_msg_list => TRUE
3966 , p_api_version_number => 1.0
3967 , p_commit => 'N'
3968 , p_eam_wo_rec => l_eam_wo_rec
3969 , p_eam_op_tbl => l_eam_op_tbl
3970 , p_eam_op_network_tbl => l_eam_op_network_tbl
3971 , p_eam_res_tbl => l_eam_res_tbl
3972 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
3973 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
3974 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
3975 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
3976 , p_eam_direct_items_tbl => l_eam_di_tbl
3977 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
3978 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
3979 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
3980 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
3981 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
3982 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
3983 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
3984 , p_eam_request_tbl => l_eam_request_tbl
3985 , x_eam_wo_rec => l_out_eam_wo_rec
3986 , x_eam_op_tbl => l_out_eam_op_tbl
3987 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
3988 , x_eam_res_tbl => l_out_eam_res_tbl
3989 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
3990 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
3991 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
3992 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
3993 , x_eam_direct_items_tbl => l_out_eam_di_tbl
3994 , x_eam_wo_comp_rec => l_out_eam_wo_comp_rec
3995 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
3996 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
3997 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
3998 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
3999 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
4000 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
4001 , x_eam_request_tbl => l_out_eam_request_tbl
4002 , x_return_status => l_return_status
4003 , x_msg_count => x_msg_count
4004 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
4005 , p_debug_filename => 'insertwo.log'
4006 , p_output_dir =>l_output_dir
4007 , p_debug_file_mode => 'w'
4008 );
4009
4010 IF ( l_return_status = 'S' ) THEN
4011 x_return_status := 0 ;
4012 COMMIT;
4013 ELSE
4014 x_return_status := 1 ;
4015 ROLLBACK TO label_insert_into_wo; -- Fix for 3582756
4016 END IF;
4017
4018 EXCEPTION
4019 WHEN invalid_autochrg_exp THEN
4020 l_return_status := 3;
4021 x_return_status := l_return_status;
4022 ROLLBACK TO label_insert_into_wo; -- Fix for 3823415
4023 WHEN DUP_VAL_ON_INDEX THEN
4024 l_return_status := 2;
4025 x_return_status := l_return_status;
4026 ROLLBACK TO label_insert_into_wo; -- Fix for 3582756
4027 WHEN OTHERS THEN
4028 l_return_status := 1;
4029 x_return_status := l_return_status ;
4030 ROLLBACK TO label_insert_into_wo; -- Fix for 3582756
4031
4032 END insert_into_wo;
4033 --------------------------------------------------------------------------
4034 -- Procedure to update operations in wip_operations
4035 -- Used in Operations Page
4036 -- Author : rethakur
4037 --------------------------------------------------------------------------
4038 procedure update_wo ( p_wip_entity_id IN NUMBER
4039 ,p_operation_seq_num IN NUMBER
4040 ,p_organization_id IN NUMBER
4041 ,p_description IN VARCHAR2
4042 ,p_shutdown_type IN VARCHAR2
4043 ,p_first_unit_start_date IN VARCHAR2
4044 ,p_last_unit_completion_date IN VARCHAR2
4045 ,p_duration IN NUMBER
4046 ,p_long_description IN VARCHAR2 := null
4047 ,x_return_status OUT NOCOPY NUMBER
4048 ,x_msg_count OUT NOCOPY NUMBER ) IS
4049
4050 l_return_status VARCHAR2(1);
4051 l_first_unit_start_date DATE := SYSDATE;
4052 l_last_unit_completion_date DATE := SYSDATE;
4053 l_duration NUMBER := 0;
4054
4055
4056 -- baroy
4057 l_call_scheduler number := 0;
4058
4059
4060 /* Added for WO API */
4061
4062 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
4063 l_eam_op_rec EAM_PROCESS_WO_PUB.eam_op_rec_type;
4064 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
4065 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
4066 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
4067 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
4068 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
4069 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
4070 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
4071 l_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
4072 l_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
4073 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
4074 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
4075 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
4076 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
4077 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
4078 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
4079 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
4080
4081 l_out_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
4082 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
4083 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
4084 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
4085 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
4086 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
4087 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
4088 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
4089 l_out_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
4090 l_out_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
4091 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
4092 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
4093 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
4094 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
4095 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
4096 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
4097 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
4098
4099 l_output_dir VARCHAR2(512);
4100 BEGIN
4101 SAVEPOINT UPDATE_WO;
4102
4103 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
4104
4105
4106 IF (p_first_unit_start_date is NOT NULL AND p_last_unit_completion_date is NOT NULL) THEN
4107 l_first_unit_start_date := to_date(p_first_unit_start_date,'YYYY/MM/DD HH24:MI:SS'); -- ,WIP_CONSTANTS.DATETIME_FMT);
4108 l_last_unit_completion_date := to_date(p_last_unit_completion_date,'YYYY/MM/DD HH24:MI:SS'); -- ,WIP_CONSTANTS.DATETIME_FMT);
4109 ELSIF ( p_last_unit_completion_date is NULL) THEN
4110 l_duration := p_duration/24;
4111 l_first_unit_start_date := to_date(p_first_unit_start_date,'YYYY/MM/DD HH24:MI:SS'); -- ,WIP_CONSTANTS.DATETIME_FMT);
4112 l_last_unit_completion_date := l_first_unit_start_date + l_duration;
4113 ELSIF ( p_first_unit_start_date is NULL) THEN
4114 l_duration := p_duration/24;
4115 l_last_unit_completion_date := to_date(p_last_unit_completion_date,'YYYY/MM/DD HH24:MI:SS'); --,WIP_CONSTANTS.DATETIME_FMT);
4116 l_first_unit_start_date := l_last_unit_completion_date + l_duration;
4117 END IF ; /* end if of duration check if */
4118
4119 l_eam_op_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_UPDATE;
4120 l_eam_op_rec.wip_entity_id := p_wip_entity_id;
4121 l_eam_op_rec.operation_seq_num := p_operation_seq_num;
4122 l_eam_op_rec.description := p_description;
4123 l_eam_op_rec.long_description := p_long_description;
4124 l_eam_op_rec.shutdown_type := p_shutdown_type;
4125 l_eam_op_rec.start_date := l_first_unit_start_date;
4126 l_eam_op_rec.completion_date := l_last_unit_completion_date;
4127 l_eam_op_rec.organization_id := p_organization_id;
4128
4129 l_eam_op_tbl(1) := l_eam_op_rec ;
4130
4131
4132 EAM_PROCESS_WO_PUB.Process_WO
4133 ( p_bo_identifier => 'EAM'
4134 , p_init_msg_list => TRUE
4135 , p_api_version_number => 1.0
4136 , p_commit => 'N'
4137 , p_eam_wo_rec => l_eam_wo_rec
4138 , p_eam_op_tbl => l_eam_op_tbl
4139 , p_eam_op_network_tbl => l_eam_op_network_tbl
4140 , p_eam_res_tbl => l_eam_res_tbl
4141 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
4142 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
4143 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
4144 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
4145 , p_eam_direct_items_tbl => l_eam_di_tbl
4146 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
4147 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
4148 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
4149 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
4150 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
4151 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
4152 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
4153 , p_eam_request_tbl => l_eam_request_tbl
4154 , x_eam_wo_rec => l_out_eam_wo_rec
4155 , x_eam_op_tbl => l_out_eam_op_tbl
4156 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
4157 , x_eam_res_tbl => l_out_eam_res_tbl
4158 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
4159 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
4160 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
4161 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
4162 , x_eam_direct_items_tbl => l_out_eam_di_tbl
4163 , x_eam_wo_comp_rec => l_out_eam_wo_comp_rec
4164 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
4165 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
4166 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
4167 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
4168 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
4169 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
4170 , x_eam_request_tbl => l_out_eam_request_tbl
4171 , x_return_status => l_return_status
4172 , x_msg_count => x_msg_count
4173 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
4174 , p_debug_filename => 'updatewo.log'
4175 , p_output_dir => l_output_dir
4176 , p_debug_file_mode => 'w'
4177 );
4178
4179 IF ( l_return_status = 'S' ) THEN
4180 x_return_status := 0 ;
4181 COMMIT; -- Fix for Bug 3521871
4182 ELSE
4183 ROLLBACK TO UPDATE_WO;
4184 x_return_status := 1 ;
4185 END IF;
4186
4187 EXCEPTION
4188
4189 WHEN OTHERS THEN
4190 ROLLBACK TO UPDATE_WO;
4191 l_return_status := 4;
4192 x_return_status := l_return_status;
4193
4194 END update_wo;
4195
4196 -- ------------------------------------------------------------------------
4197 -- Validation API for new link between operaions in
4198 -- Dependency definitions
4199 -- ------------------------------------------------------------------------
4200
4201 Procedure validate_new_link(p_from_operation IN NUMBER,
4202 p_to_operation IN NUMBER,
4203 p_dep_direction IN NUMBER,
4204 p_wip_entity_id IN NUMBER,
4205 p_sche_start_date IN DATE,
4206 p_sche_end_date IN DATE,
4207 x_error_flag OUT NOCOPY VARCHAR2,
4208 x_error_mssg OUT NOCOPY VARCHAR2
4209 ) IS
4210 l_to_scheduled_start_date DATE;
4211 l_to_scheduled_end_date DATE;
4212 l_to_operation_completed VARCHAR2(1);
4213 l_from_scheduled_start_date DATE;
4214 l_from_scheduled_end_date DATE;
4215 l_from_operation_completed VARCHAR2(1);
4216 l_op_already_available NUMBER;
4217 l_loop_available NUMBER :=0 ;
4218 l_available_value NUMBER := 0;
4219 l_restrict_date_change NUMBER := 0;
4220 Begin
4221
4222 l_op_already_available := 0;
4223 x_error_flag := FND_API.G_RET_STS_SUCCESS;
4224 x_error_mssg := '';
4225
4226
4227 --check for the availability of all the values
4228 if(p_dep_direction is null or p_from_operation is null or
4229 p_to_operation is null or p_sche_start_date is null or
4230 p_sche_end_date is null) then
4231 x_error_flag := FND_API.G_RET_STS_ERROR;
4232 x_error_mssg := 'EAM_NOT_ENOUGH_VALUES';
4233 return;
4234 end if;
4235
4236 -- check for from and to operation
4237 if(p_from_operation = p_to_operation) then
4238 x_error_flag := FND_API.G_RET_STS_ERROR;
4239 x_error_mssg := 'EAM_FROM_TO_OPERATION_EQUAL';
4240 return;
4241 end if;
4242
4243 if(p_sche_end_date < p_sche_start_date) then
4244 x_error_flag := FND_API.G_RET_STS_ERROR;
4245 x_error_mssg := 'EAM_START_LESS_END_DATE';
4246 return;
4247 end if;
4248
4249 -- initialize scheduled dates of from and to operations .
4250 Begin
4251 select
4252 first_unit_start_date ,
4253 last_unit_completion_date,
4254 operation_completed
4255 into
4256 l_from_scheduled_start_date,
4257 l_from_scheduled_end_date,
4258 l_from_operation_completed
4259 from
4260 wip_operations
4261 where
4262 wip_entity_id = p_wip_entity_id and
4263 operation_seq_num = p_from_operation ;
4264 Exception
4265 when NO_DATA_FOUND then
4266 x_error_flag := FND_API.G_RET_STS_ERROR;
4267 x_error_mssg := 'EAM_FROM_OPERATION_NOT_FOUND';
4268 return;
4269 End; -- end of
4270
4271 Begin
4272 select
4273 first_unit_start_date ,
4274 last_unit_completion_date,
4275 operation_completed
4276 into
4277 l_to_scheduled_start_date,
4278 l_to_scheduled_end_date,
4279 l_to_operation_completed
4280 from
4281 wip_operations
4282 where
4283 wip_entity_id = p_wip_entity_id and
4284 operation_seq_num = p_to_operation ;
4285 Exception
4286 when NO_DATA_FOUND then
4287 x_error_flag := FND_API.G_RET_STS_ERROR;
4288 x_error_mssg := 'EAM_TO_OPERATION_NOT_FOUND';
4289 return;
4290 End; -- end of
4291
4292 -- check for the scheduled atart/end date updation
4293 if(p_dep_direction = 1) then
4294 if(p_sche_start_date <> l_from_scheduled_start_date or
4295 p_sche_end_date <> l_from_scheduled_end_date) then
4296
4297 -- check Prior/Next Operation conflict with the modified Start and End Date .
4298 select
4299 count(*) into l_restrict_date_change
4300 from
4301 dual
4302 where
4303 exists
4304 (select '1' from eam_prior_operations_v
4305 where next_operation = p_from_operation
4306 and schedule_end_date > p_sche_start_date
4307 and wip_entity_id = p_wip_entity_id);
4308
4309 if(l_restrict_date_change = 0) then
4310 select count(*) into l_restrict_date_change
4311 from dual
4312 where
4313 exists
4314 (select '1' from eam_next_operations_v
4315 where prior_operation = p_from_operation
4316 and schedule_start_date < p_sche_end_date
4317 and wip_entity_id = p_wip_entity_id);
4318 end if;
4319
4320
4321 if(l_restrict_date_change > 0) then
4322 x_error_flag := FND_API.G_RET_STS_ERROR;
4323 x_error_mssg := 'EAM_SCHEDULED_DATE_CHANGE';
4324 return;
4325 elsif(l_restrict_date_change = 0) then
4326 update wip_operations
4327 set
4328 first_unit_start_date = p_sche_start_date,
4329 last_unit_start_date = p_sche_start_date,
4330 first_unit_completion_date = p_sche_end_date,
4331 last_unit_completion_date = p_sche_end_date
4332 where
4333 wip_entity_id = p_wip_entity_id and
4334 operation_seq_num = p_from_operation ;
4335 l_from_scheduled_start_date := p_sche_start_date ;
4336 l_from_scheduled_end_date := p_sche_end_date;
4337 end if;
4338 end if; -- end of date check
4339 elsif(p_dep_direction = 2) then
4340 if(p_sche_start_date <> l_to_scheduled_start_date or
4341 p_sche_end_date <> l_to_scheduled_end_date) then
4342
4343 -- check Prior/Next Operation conflict with the modified Start and End Date .
4344 select
4345 count(*) into l_restrict_date_change
4346 from
4347 dual
4348 where
4349 exists
4350 (select '1' from eam_prior_operations_v
4351 where next_operation = p_to_operation
4352 and schedule_end_date > p_sche_start_date
4353 and wip_entity_id = p_wip_entity_id);
4354
4355 if(l_restrict_date_change = 0) then
4356 select count(*) into l_restrict_date_change
4357 from dual
4358 where
4359 exists
4360 (select '1' from eam_next_operations_v
4361 where prior_operation = p_to_operation
4362 and schedule_start_date < p_sche_start_date
4363 and wip_entity_id = p_wip_entity_id);
4364 end if;
4365
4366 if(l_restrict_date_change > 0) then
4367 x_error_flag := FND_API.G_RET_STS_ERROR;
4368 x_error_mssg := 'EAM_SCHEDULED_DATE_CHANGE';
4369 return;
4370 elsif(l_restrict_date_change = 0) then
4371 update wip_operations
4372 set
4373 first_unit_start_date = p_sche_start_date,
4374 last_unit_start_date = p_sche_start_date,
4375 first_unit_completion_date = p_sche_end_date,
4376 last_unit_completion_date = p_sche_end_date
4377 where
4378 wip_entity_id = p_wip_entity_id and
4379 operation_seq_num = p_to_operation ;
4380 l_to_scheduled_start_date := p_sche_start_date ;
4381 l_to_scheduled_end_date := p_sche_end_date;
4382 end if;
4383 end if; -- end of date check
4384 end if;-- end of dep_direction check if
4385
4386
4387 -- check for the scheduled completion and start date of from and to operation respectively
4388 if (l_to_scheduled_start_date < l_from_scheduled_end_date ) then
4389 x_error_flag := FND_API.G_RET_STS_ERROR;
4390 x_error_mssg := 'EAM_DEP_OP_START_DATE_INVALID';
4391 return;
4392 end if;
4393
4394 -- check for loop in the dependency network
4395 select count(1) into l_loop_available
4396 from dual
4397 where
4398 p_from_operation in (select next_operation
4399 from (select * from wip_operation_networks
4400 where next_operation <> p_to_operation and
4401 wip_entity_id = p_wip_entity_id)
4402 start with prior_operation = p_to_operation
4403 connect by prior_operation = prior next_operation) ;
4404
4405 if(l_loop_available <> 0) then
4406 x_error_flag := FND_API.G_RET_STS_ERROR;
4407 x_error_mssg := 'EAM_OPMDF_OP_DEP_LOOP';
4408 end if;
4409
4410 End validate_new_link;
4411
4412
4413 Procedure create_new_link( p_from_operation IN NUMBER,
4414 p_to_operation IN NUMBER,
4415 p_dep_direction IN NUMBER,
4416 p_wip_entity_id IN NUMBER,
4417 p_organization_id IN NUMBER,
4418 p_user_id IN NUMBER,
4419 p_sche_start_date IN DATE,
4420 p_sche_end_date IN DATE,
4421 x_error_flag OUT NOCOPY VARCHAR2,
4422 x_error_mssg OUT NOCOPY VARCHAR2 ) IS
4423
4424 /* Added for implementing the WO API */
4425
4426 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
4427 l_eam_op_network_rec EAM_PROCESS_WO_PUB.eam_op_network_rec_type;
4428 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
4429 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
4430 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
4431 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
4432 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
4433 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
4434 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
4435 l_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
4436 l_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
4437 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
4438 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
4439 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
4440 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
4441 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
4442 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
4443 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
4444
4445 l_out_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
4446 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
4447 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
4448 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
4449 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
4450 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
4451 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
4452 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
4453 l_out_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
4454 l_out_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
4455 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
4456 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
4457 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
4458 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
4459 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
4460 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
4461 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
4462
4463 l_mssg_token_tbl_type EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
4464 l_return_status VARCHAR2(240);
4465 l_data VARCHAR2(2000);
4466 l_mssg_index_out NUMBER;
4467 l_mssg_index NUMBER;
4468 l_mssg_data VARCHAR2(250);
4469 l_msg_count NUMBER := 0;
4470 l_output_dir VARCHAR2(512);
4471 Begin
4472
4473 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
4474
4475
4476 x_error_flag := FND_API.G_RET_STS_SUCCESS;
4477 x_error_mssg := '';
4478
4479 -- validate the link
4480 validate_new_link(
4481 p_from_operation,
4482 p_to_operation ,
4483 p_dep_direction ,
4484 p_wip_entity_id ,
4485 p_sche_start_date,
4486 p_sche_end_date,
4487 x_error_flag ,
4488 x_error_mssg ) ;
4489
4490 if(x_error_flag <> FND_API.G_RET_STS_SUCCESS) then
4491 FND_MSG_PUB.Initialize;
4492 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => x_error_mssg);
4493 eam_execution_jsp.Get_Messages(
4494 p_encoded => FND_API.G_FALSE,
4495 p_msg_index => 1,
4496 p_msg_count => 1,
4497 p_msg_data => l_mssg_data,
4498 p_data => l_data,
4499 p_msg_index_out => l_mssg_index_out);
4500 -- fnd_message.set_name('EAM',x_error_mssg);
4501 x_error_mssg := l_data;
4502 return;
4503 end if;
4504
4505 SAVEPOINT add_op_network;
4506
4507 -- initializing the structure of dependency network
4508 l_eam_op_network_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_CREATE;
4509 l_eam_op_network_rec.wip_entity_id := p_wip_entity_id;
4510 l_eam_op_network_rec.organization_id := p_organization_id;
4511 l_eam_op_network_rec.prior_operation := p_from_operation;
4512 l_eam_op_network_rec.next_operation := p_to_operation;
4513
4514 l_eam_op_network_tbl(1) := l_eam_op_network_rec ;
4515
4516 EAM_PROCESS_WO_PUB.Process_WO
4517 ( p_bo_identifier => 'EAM'
4518 , p_init_msg_list => TRUE
4519 , p_api_version_number => 1.0
4520 , p_commit => 'N'
4521 , p_eam_wo_rec => l_eam_wo_rec
4522 , p_eam_op_tbl => l_eam_op_tbl
4523 , p_eam_op_network_tbl => l_eam_op_network_tbl
4524 , p_eam_res_tbl => l_eam_res_tbl
4525 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
4526 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
4527 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
4528 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
4529 , p_eam_direct_items_tbl => l_eam_di_tbl
4530 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
4531 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
4532 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
4533 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
4534 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
4535 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
4536 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
4537 , p_eam_request_tbl => l_eam_request_tbl
4538 , x_eam_wo_rec => l_out_eam_wo_rec
4539 , x_eam_op_tbl => l_out_eam_op_tbl
4540 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
4541 , x_eam_res_tbl => l_out_eam_res_tbl
4542 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
4543 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
4544 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
4545 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
4546 , x_eam_direct_items_tbl => l_out_eam_di_tbl
4547 , x_eam_wo_comp_rec => l_out_eam_wo_comp_rec
4548 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
4549 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
4550 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
4551 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
4552 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
4553 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
4554 , x_eam_request_tbl => l_out_eam_request_tbl
4555 , x_return_status => x_error_flag
4556 , x_msg_count => l_msg_count
4557 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
4558 , p_debug_filename => 'createopdep.log'
4559 , p_output_dir => l_output_dir
4560 , p_debug_file_mode => 'w'
4561 );
4562
4563 IF(x_error_flag <> 'S') THEN
4564 ROLLBACK TO add_op_network;
4565 END IF;
4566
4567 End create_new_link;
4568
4569
4570 PROCEDURE delete_link(p_from_operation IN NUMBER,
4571 p_to_operation IN NUMBER,
4572 p_dep_direction IN NUMBER,
4573 p_wip_entity_id IN NUMBER,
4574 p_organization_id IN NUMBER,
4575 p_user_id IN NUMBER,
4576 x_error_flag OUT NOCOPY VARCHAR2,
4577 x_error_mssg OUT NOCOPY VARCHAR2 ) IS
4578
4579 /* Added for implementing WO API */
4580 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
4581 l_eam_op_network_rec EAM_PROCESS_WO_PUB.eam_op_network_rec_type;
4582 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
4583 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
4584 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
4585 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
4586 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
4587 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
4588 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
4589 l_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
4590 l_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
4591 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
4592 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
4593 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
4594 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
4595 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
4596 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
4597 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
4598
4599 l_out_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
4600 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
4601 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
4602 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
4603 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
4604 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
4605 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
4606 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
4607 l_out_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
4608 l_out_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
4609 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
4610 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
4611 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
4612 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
4613 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
4614 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
4615 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
4616
4617 l_mssg_token_tbl_type EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
4618 l_return_status VARCHAR2(240);
4619 l_data VARCHAR2(2000);
4620 l_mssg_index_out NUMBER;
4621 l_mssg_data VARCHAR2(250);
4622 l_msg_count NUMBER := 0;
4623 l_output_dir VARCHAR2(512);
4624 Begin
4625
4626 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
4627
4628
4629 x_error_flag := FND_API.G_RET_STS_SUCCESS;
4630 x_error_mssg := '';
4631
4632 -- initializing the structure of dependency network
4633 l_eam_op_network_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_DELETE;
4634 l_eam_op_network_rec.wip_entity_id := p_wip_entity_id;
4635 l_eam_op_network_rec.organization_id := p_organization_id;
4636 l_eam_op_network_rec.prior_operation := p_from_operation;
4637 l_eam_op_network_rec.next_operation := p_to_operation;
4638
4639 l_eam_op_network_tbl(1) := l_eam_op_network_rec ;
4640
4641 SAVEPOINT delete_op_network;
4642
4643 EAM_PROCESS_WO_PUB.Process_WO
4644 ( p_bo_identifier => 'EAM'
4645 , p_init_msg_list => TRUE
4646 , p_api_version_number => 1.0
4647 , p_commit => 'N'
4648 , p_eam_wo_rec => l_eam_wo_rec
4649 , p_eam_op_tbl => l_eam_op_tbl
4650 , p_eam_op_network_tbl => l_eam_op_network_tbl
4651 , p_eam_res_tbl => l_eam_res_tbl
4652 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
4653 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
4654 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
4655 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
4656 , p_eam_direct_items_tbl => l_eam_di_tbl
4657 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
4658 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
4659 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
4660 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
4661 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
4662 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
4663 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
4664 , p_eam_request_tbl => l_eam_request_tbl
4665 , x_eam_wo_rec => l_out_eam_wo_rec
4666 , x_eam_op_tbl => l_out_eam_op_tbl
4667 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
4668 , x_eam_res_tbl => l_out_eam_res_tbl
4669 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
4670 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
4671 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
4672 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
4673 , x_eam_direct_items_tbl => l_out_eam_di_tbl
4674 , x_eam_wo_comp_rec => l_out_eam_wo_comp_rec
4675 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
4676 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
4677 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
4678 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
4679 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
4680 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
4681 , x_eam_request_tbl => l_out_eam_request_tbl
4682 , x_return_status => x_error_flag
4683 , x_msg_count => l_msg_count
4684 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
4685 , p_debug_filename => 'delopdep.log'
4686 , p_output_dir => l_output_dir
4687 , p_debug_file_mode => 'w'
4688 );
4689
4690 IF(x_error_flag='S') THEN
4691 COMMIT;
4692 END IF;
4693
4694 IF(x_error_flag <> 'S') THEN
4695 ROLLBACK TO delete_op_network;
4696 END IF;
4697
4698 End delete_link;
4699
4700
4701
4702 procedure schedule_workorders ( p_organization_id IN NUMBER,
4703 p_wip_entity_id IN NUMBER
4704 ) IS
4705
4706 l_organization_id NUMBER;
4707 l_wip_entity_id NUMBER;
4708 l_status_type NUMBER;
4709 l_use_finite_scheduler NUMBER;
4710 l_material_constrained NUMBER;
4711 l_horizon_length NUMBER;
4712 l_firm NUMBER;
4713 l_date VARCHAR2(100);
4714 l_user_id NUMBER;
4715 l_responsibility_id NUMBER;
4716 l_request_id NUMBER;
4717 l_final_status NUMBER;
4718 l_start_date DATE;
4719 l_completion_date DATE;
4720 l_err_text VARCHAR2(240) ;
4721 l_return_status VARCHAR2(30) := 'S';
4722 l_first_unit_start_date DATE := SYSDATE;
4723 l_last_unit_completion_date DATE := SYSDATE;
4724
4725 begin
4726
4727 l_organization_id := p_organization_id;
4728 l_wip_entity_id := p_wip_entity_id;
4729
4730 select status_type , nvl(firm_planned_flag,2), scheduled_start_date,
4731 scheduled_completion_date
4732 into l_status_type, l_firm, l_start_date, l_completion_date
4733 from wip_discrete_jobs
4734 where wip_entity_id = l_wip_entity_id
4735 and organization_id = l_organization_id;
4736
4737 -- Get WPS Parameters
4738
4739 IF(WPS_COMMON.Get_Install_Status = 'I') THEN
4740 WPS_COMMON.GetParameters(
4741 P_Org_Id => l_organization_id,
4742 X_Use_Finite_Scheduler => l_use_finite_scheduler,
4743 X_Material_Constrained => l_material_constrained,
4744 X_Horizon_Length => l_horizon_length);
4745 ELSE
4746 l_use_finite_scheduler := 2;
4747 l_material_constrained := 2;
4748 l_horizon_length := 0;
4749 END IF;
4750
4751
4752 IF (l_status_type in (1,3,6,17) ) then
4753
4754 -- baroy
4755 -- Finite scheduler has been decommisioned for 11.5.10
4756 -- Hence commenting out this code. Also, hardcode the value
4757 -- of the l_use_finite_scheduler flag
4758
4759 l_use_finite_scheduler := 2;
4760
4761 if ((l_status_type = 3) and (l_use_finite_scheduler = 1) and (l_firm = 2) ) then
4762
4763 null;
4764
4765 else
4766 SAVEPOINT schedule_wo_pvt;
4767
4768 EAM_WO_SCHEDULE_PVT.SCHEDULE_WO
4769 ( p_organization_id => l_organization_id
4770 , p_wip_entity_id => l_wip_entity_id
4771 , p_start_date => l_start_date
4772 , p_completion_date => l_completion_date
4773 , p_validation_level => null
4774 , p_commit => 'N'
4775 , x_error_message => l_err_text
4776 , x_return_status => l_return_status
4777 );
4778 IF(l_err_text <> 'S') THEN
4779 ROLLBACK TO schedule_wo_pvt;
4780 END IF;
4781
4782 end if;
4783
4784 END IF;
4785
4786 END schedule_workorders;
4787
4788 /*-------------------------------------------------------------------------
4789 -- API for geting the operation_seq_num and the department_code
4790 -- for the wip_entity_id.Added for the bug 2762202
4791 -------------------------------------------------------------------------*/
4792 PROCEDURE count_op_seq_num(p_organization_id IN NUMBER,
4793 p_wip_entity_id IN NUMBER,
4794 op_seq_num OUT NOCOPY NUMBER,
4795 op_dept_code OUT NOCOPY VARCHAR2,
4796 op_count OUT NOCOPY NUMBER,
4797 l_return_status OUT NOCOPY VARCHAR2,
4798 l_msg_data OUT NOCOPY VARCHAR2,
4799 l_msg_count OUT NOCOPY NUMBER)
4800 IS
4801 l_op_count NUMBER;
4802 l_op_dept_code VARCHAR2(240);
4803 l_op_seq_num NUMBER;
4804 BEGIN
4805
4806 SELECT count(operation_seq_num)
4807 INTO l_op_count
4808 FROM wip_operations
4809 WHERE wip_entity_id = p_wip_entity_id and
4810 organization_id = p_organization_id;
4811 op_count := l_op_count;
4812
4813 if (l_op_count = 1 ) then
4814 SELECT wo.operation_seq_num, bd.department_code
4815 INTO op_seq_num, op_dept_code
4816 FROM wip_operations wo, bom_departments bd
4817 WHERE wo.wip_entity_id = p_wip_entity_id and
4818 wo.organization_id = p_organization_id and
4819 wo.organization_id = bd.organization_id and
4820 wo.department_id = bd.department_id;
4821 end if;
4822
4823 END count_op_seq_num;
4824 /*-------------------------------------------------------------------------
4825 -- API for geting the operation_seq_num,the department_code and start/end dates
4826 -- for a given wip entity id. Added for bug#3544893
4827 -------------------------------------------------------------------------*/
4828 PROCEDURE default_operation (p_organization_id IN NUMBER,
4829 p_wip_entity_id IN NUMBER,
4830 x_op_seq_num OUT NOCOPY NUMBER,
4831 x_op_dept_code OUT NOCOPY VARCHAR2,
4832 x_op_count OUT NOCOPY NUMBER,
4833 x_op_start_date OUT NOCOPY DATE,
4834 x_op_end_date OUT NOCOPY DATE,
4835 x_return_status OUT NOCOPY VARCHAR2,
4836 x_msg_data OUT NOCOPY VARCHAR2,
4837 x_msg_count OUT NOCOPY NUMBER)
4838 IS
4839 l_op_count NUMBER;
4840 l_op_dept_code VARCHAR2(240);
4841 l_op_seq_num NUMBER;
4842 l_op_start_date DATE;
4843 l_op_end_date DATE;
4844 BEGIN
4845
4846 SELECT count(operation_seq_num)
4847 INTO l_op_count
4848 FROM wip_operations
4849 WHERE wip_entity_id = p_wip_entity_id and
4850 organization_id = p_organization_id;
4851 x_op_count := l_op_count;
4852
4853 if (l_op_count = 1 ) then
4854 SELECT wo.operation_seq_num, wo.first_unit_start_date, wo.last_unit_completion_date, bd.department_code
4855 INTO x_op_seq_num, x_op_start_date, x_op_end_date, x_op_dept_code
4856 FROM wip_operations wo, bom_departments bd
4857 WHERE wo.wip_entity_id = p_wip_entity_id and
4858 wo.organization_id = p_organization_id and
4859 wo.organization_id = bd.organization_id and
4860 wo.department_id = bd.department_id;
4861 end if;
4862
4863 END default_operation;
4864
4865
4866 /* ------------------------------------------------------------------------
4867 API for checking whether the resources associated with a work order and
4868 an operation are available in the department chosen.
4869 --------------------------------------------------------------------------*/
4870 procedure handover_department_validate
4871 ( p_wip_entity_id IN NUMBER,
4872 p_operation_seq_num IN NUMBER,
4873 p_department IN VARCHAR2,
4874 p_organization_id IN NUMBER,
4875 p_resource_code IN VARCHAR2,
4876 x_return_status OUT NOCOPY NUMBER
4877 ) IS
4878
4879 l_count NUMBER;
4880 l_department_id NUMBER;
4881 l_resource_id NUMBER;
4882
4883 BEGIN
4884 x_return_status := 0;
4885 l_resource_id := 0;
4886
4887 SELECT department_id
4888 INTO l_department_id
4889 FROM bom_departments
4890 WHERE department_code like p_department
4891 AND organization_id = p_organization_id;
4892
4893 -- get resources available in the assigned department
4894 IF(p_resource_code IS NOT NULL) THEN
4895 SELECT bdr.resource_id
4896 INTO l_resource_id
4897 FROM bom_department_resources bdr , bom_resources br
4898 WHERE bdr.department_id = l_department_id
4899 AND bdr.resource_id = br.resource_id
4900 AND br.resource_code like p_resource_code
4901 AND br.organization_id = p_organization_id;
4902
4903 IF (l_resource_id=0) THEN
4904 x_return_status := 0;
4905 END IF;
4906 END IF;
4907
4908 EXCEPTION
4909 WHEN NO_DATA_FOUND THEN
4910 x_return_status := 1;
4911 return ;
4912
4913 END handover_department_validate;
4914
4915 /* API to check if operation can be deleted from self service side */
4916
4917 procedure check_op_deletion
4918 ( p_wip_entity_id IN NUMBER,
4919 p_operation_seq_num IN NUMBER,
4920 x_return_status OUT NOCOPY NUMBER
4921 ) IS
4922 l_wip_entity_id NUMBER;
4923 l_operation_seq_num NUMBER;
4924 l_count_routing NUMBER;
4925 l_count_mat NUMBER;
4926 l_count_di NUMBER;
4927 l_count_res NUMBER;
4928 l_completed varchar2(10);
4929
4930 BEGIN
4931 -- Check whether there are material requirements or resource requirements
4932 -- or operation has been completed
4933
4934 l_wip_entity_id := p_wip_entity_id;
4935 l_operation_seq_num := p_operation_seq_num;
4936
4937 select count(*)
4938 into l_count_routing
4939 from wip_operation_networks
4940 where wip_entity_id = l_wip_entity_id and ( prior_operation = p_operation_seq_num or next_operation = p_operation_seq_num);
4941
4942 select count(*)
4943 into l_count_mat
4944 from wip_requirement_operations
4945 where wip_entity_id = l_wip_entity_id
4946 and operation_seq_num = l_operation_seq_num;
4947
4948 select count(*)
4949 into l_count_di
4950 from wip_eam_direct_items
4951 where wip_entity_id = l_wip_entity_id
4952 and operation_seq_num = l_operation_seq_num
4953 and rownum =1;
4954
4955 select count(*)
4956 into l_count_res
4957 from wip_operation_resources
4958 where wip_entity_id = l_wip_entity_id
4959 and operation_seq_num = l_operation_seq_num;
4960
4961 begin
4962 select operation_completed
4963 into l_completed
4964 from wip_operations
4965 where wip_entity_id = l_wip_entity_id
4966 and operation_seq_num = l_operation_seq_num;
4967 exception
4968 when others then
4969 null;
4970 end;
4971
4972 if l_count_routing >0 or l_count_mat > 0 or l_count_res > 0 or l_count_di > 0 or nvl(l_completed, 'N') = 'Y' then
4973 x_return_status := 1;
4974 else
4975 x_return_status := 0;
4976 end if;
4977
4978 EXCEPTION
4979 WHEN NO_DATA_FOUND THEN
4980 x_return_status := 1;
4981 return ;
4982 END check_op_deletion;
4983
4984
4985 /* API to delete operation from self service side */
4986
4987 procedure delete_operation (
4988 p_api_version IN NUMBER := 1.0
4989 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
4990 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
4991 ,p_organization_id IN NUMBER
4992 ,p_wip_entity_id IN NUMBER
4993 ,p_operation_seq_num IN NUMBER
4994 ,p_department_id IN NUMBER
4995 ,x_return_status OUT NOCOPY VARCHAR2
4996 ,x_msg_count OUT NOCOPY NUMBER
4997 ,x_msg_data OUT NOCOPY VARCHAR2
4998 ) is
4999
5000 l_api_name constant varchar2(30) := 'Delete_Operations';
5001 l_api_version CONSTANT NUMBER := 1.0;
5002 l_msg_data VARCHAR2(10000) ;
5003 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
5004 l_msg_count NUMBER;
5005 l_message_text VARCHAR2(1000);
5006
5007 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
5008 l_eam_op_rec EAM_PROCESS_WO_PUB.eam_op_rec_type;
5009 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
5010 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
5011 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
5012 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
5013 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
5014 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
5015 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
5016 l_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
5017 l_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
5018 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
5019 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
5020 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
5021 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
5022 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
5023 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
5024 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
5025
5026 l_out_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
5027 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
5028 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
5029 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
5030 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
5031 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
5032 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
5033 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
5034 l_out_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
5035 l_out_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
5036 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
5037 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
5038 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
5039 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
5040 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
5041 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
5042 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
5043
5044 l_output_dir VARCHAR2(512);
5045 begin
5046
5047 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
5048
5049
5050 SAVEPOINT DELETE_OPERATION_JSP;
5051
5052
5053 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
5054 p_api_version,
5055 l_api_name,
5056 g_pkg_name)
5057 THEN
5058 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5059 END IF;
5060
5061 IF FND_API.TO_BOOLEAN(p_init_msg_list)
5062 THEN
5063 FND_MSG_PUB.initialize;
5064 END IF;
5065
5066
5067 l_eam_op_rec.WIP_ENTITY_ID :=p_wip_entity_id;
5068 l_eam_op_rec.ORGANIZATION_ID :=p_organization_id;
5069 l_eam_op_rec.OPERATION_SEQ_NUM :=p_operation_seq_num;
5070 l_eam_op_rec.DEPARTMENT_ID :=p_department_id;
5071 l_eam_op_rec.TRANSACTION_TYPE :=EAM_PROCESS_WO_PUB.G_OPR_DELETE;
5072
5073 l_eam_op_tbl(1) := l_eam_op_rec;
5074
5075 EAM_PROCESS_WO_PUB.Process_WO
5076 ( p_bo_identifier => 'EAM'
5077 , p_init_msg_list => TRUE
5078 , p_api_version_number => 1.0
5079 , p_commit => 'N'
5080 , p_eam_wo_rec => l_eam_wo_rec
5081 , p_eam_op_tbl => l_eam_op_tbl
5082 , p_eam_op_network_tbl => l_eam_op_network_tbl
5083 , p_eam_res_tbl => l_eam_res_tbl
5084 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
5085 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
5086 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
5087 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
5088 , p_eam_direct_items_tbl => l_eam_di_tbl
5089 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
5090 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
5091 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
5092 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
5093 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
5094 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
5095 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
5096 , p_eam_request_tbl => l_eam_request_tbl
5097 , x_eam_wo_rec => l_out_eam_wo_rec
5098 , x_eam_op_tbl => l_out_eam_op_tbl
5099 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
5100 , x_eam_res_tbl => l_out_eam_res_tbl
5101 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
5102 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
5103 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
5104 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
5105 , x_eam_direct_items_tbl => l_out_eam_di_tbl
5106 , x_eam_wo_comp_rec => l_out_eam_wo_comp_rec
5107 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
5108 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
5109 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
5110 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
5111 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
5112 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
5113 , x_eam_request_tbl => l_out_eam_request_tbl
5114 , x_return_status => l_return_status
5115 , x_msg_count => l_msg_count
5116 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
5117 , p_debug_filename => 'delop.log'
5118 , p_output_dir => l_output_dir
5119 , p_debug_file_mode => 'w'
5120 );
5121
5122 l_msg_count := FND_MSG_PUB.count_msg;
5123 x_return_status := l_return_status;
5124 x_msg_count := l_msg_count;
5125
5126 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
5127 ROLLBACK TO DELETE_OPERATION_JSP;
5128 fnd_msg_pub.get(p_msg_index => FND_MSG_PUB.G_NEXT,
5129 p_encoded => 'F',
5130 p_data => l_message_text,
5131 p_msg_index_out => l_msg_count);
5132 fnd_message.set_name('EAM','EAM_ERROR_UPDATE_WO');
5133
5134 fnd_message.set_token(token => 'MESG',
5135 value => l_message_text,
5136 translate => FALSE);
5137 APP_EXCEPTION.RAISE_EXCEPTION;
5138
5139 x_msg_data := 'Error ';
5140 END IF;
5141
5142 IF p_commit = FND_API.G_TRUE THEN
5143 COMMIT WORK;
5144 end if;
5145 EXCEPTION
5146
5147 when others then
5148 ROLLBACK TO DELETE_OPERATION_JSP;
5149
5150 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5151 return;
5152
5153
5154 end delete_operation;
5155
5156 /*---------------------------------------------------------------------------
5157 API for updating/deleting material used in one step issue page
5158 -----------------------------------------------------------------------------*/
5159
5160 PROCEDURE update_wro
5161 (
5162 p_commit IN VARCHAR2 := FND_API.G_FALSE
5163 ,p_organization_id IN NUMBER
5164 ,p_wip_entity_id IN NUMBER
5165 ,p_operation_seq_num IN NUMBER
5166 ,p_inventory_item_id IN NUMBER
5167 ,p_update IN NUMBER
5168 ,p_required_qty IN NUMBER
5169 ,x_return_status OUT NOCOPY VARCHAR2
5170 ,x_msg_count OUT NOCOPY NUMBER
5171 ,x_msg_data OUT NOCOPY VARCHAR2
5172 )
5173 IS
5174 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
5175 l_eam_op_rec EAM_PROCESS_WO_PUB.eam_op_rec_type;
5176 l_eam_mat_req_rec EAM_PROCESS_WO_PUB.eam_mat_req_rec_type;
5177 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
5178 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
5179 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
5180 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
5181 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
5182 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
5183 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
5184 l_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
5185 l_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
5186 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
5187 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
5188 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
5189 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
5190 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
5191 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
5192 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
5193
5194 l_out_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
5195 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
5196 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
5197 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
5198 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
5199 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
5200 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
5201 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
5202 l_out_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
5203 l_out_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
5204 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
5205 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
5206 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
5207 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
5208 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
5209 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
5210 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
5211
5212 l_output_dir VARCHAR2(512);
5213 BEGIN
5214 SAVEPOINT update_wro;
5215
5216 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
5217
5218
5219 IF(p_update=1) THEN --update wro
5220 l_eam_mat_req_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_UPDATE;
5221 l_eam_mat_req_rec.wip_entity_id := p_wip_entity_id;
5222 l_eam_mat_req_rec.organization_id := p_organization_id;
5223 l_eam_mat_req_rec.operation_seq_num := p_operation_seq_num;
5224 l_eam_mat_req_rec.inventory_item_id := p_inventory_item_id;
5225 l_eam_mat_req_rec.required_quantity := p_required_qty;
5226
5227 l_eam_mat_req_tbl(1) := l_eam_mat_req_rec;
5228
5229 EAM_PROCESS_WO_PUB.Process_WO
5230 ( p_bo_identifier => 'EAM'
5231 , p_init_msg_list => FALSE
5232 , p_api_version_number => 1.0
5233 , p_commit => 'N'
5234 , p_eam_wo_rec => l_eam_wo_rec
5235 , p_eam_op_tbl => l_eam_op_tbl
5236 , p_eam_op_network_tbl => l_eam_op_network_tbl
5237 , p_eam_res_tbl => l_eam_res_tbl
5238 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
5239 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
5240 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
5241 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
5242 , p_eam_direct_items_tbl => l_eam_di_tbl
5243 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
5244 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
5245 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
5246 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
5247 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
5248 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
5249 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
5250 , p_eam_request_tbl => l_eam_request_tbl
5251 , x_eam_wo_rec => l_out_eam_wo_rec
5252 , x_eam_op_tbl => l_out_eam_op_tbl
5253 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
5254 , x_eam_res_tbl => l_out_eam_res_tbl
5255 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
5256 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
5257 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
5258 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
5259 , x_eam_direct_items_tbl => l_out_eam_di_tbl
5260 , x_eam_wo_comp_rec => l_out_eam_wo_comp_rec
5261 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
5262 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
5263 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
5264 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
5265 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
5266 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
5267 , x_eam_request_tbl => l_out_eam_request_tbl
5268 , x_return_status => x_return_status
5269 , x_msg_count => x_msg_count
5270 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
5271 , p_debug_filename => 'onestepwro.log'
5272 , p_output_dir => l_output_dir
5273 , p_debug_file_mode => 'w'
5274 );
5275 ELSE --delete from wro
5276 l_eam_mat_req_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_DELETE;
5277 l_eam_mat_req_rec.wip_entity_id := p_wip_entity_id;
5278 l_eam_mat_req_rec.organization_id := p_organization_id;
5279 l_eam_mat_req_rec.operation_seq_num := p_operation_seq_num;
5280 l_eam_mat_req_rec.inventory_item_id := p_inventory_item_id;
5281
5282 l_eam_mat_req_tbl(1) := l_eam_mat_req_rec;
5283
5284 EAM_PROCESS_WO_PUB.Process_WO
5285 ( p_bo_identifier => 'EAM'
5286 , p_init_msg_list => FALSE
5287 , p_api_version_number => 1.0
5288 , p_commit => 'N'
5289 , p_eam_wo_rec => l_eam_wo_rec
5290 , p_eam_op_tbl => l_eam_op_tbl
5291 , p_eam_op_network_tbl => l_eam_op_network_tbl
5292 , p_eam_res_tbl => l_eam_res_tbl
5293 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
5294 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
5295 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
5296 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
5297 , p_eam_direct_items_tbl => l_eam_di_tbl
5298 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
5299 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
5300 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
5301 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
5302 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
5303 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
5304 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
5305 , p_eam_request_tbl => l_eam_request_tbl
5306 , x_eam_wo_rec => l_out_eam_wo_rec
5307 , x_eam_op_tbl => l_out_eam_op_tbl
5308 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
5309 , x_eam_res_tbl => l_out_eam_res_tbl
5310 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
5311 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
5312 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
5313 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
5314 , x_eam_direct_items_tbl => l_out_eam_di_tbl
5315 , x_eam_wo_comp_rec => l_out_eam_wo_comp_rec
5316 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
5317 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
5318 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
5319 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
5320 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
5321 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
5322 , x_eam_request_tbl => l_out_eam_request_tbl
5323 , x_return_status => x_return_status
5324 , x_msg_count => x_msg_count
5325 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
5326 , p_debug_filename => 'onestepwro.log'
5327 , p_output_dir => l_output_dir
5328 , p_debug_file_mode => 'w'
5329 );
5330 END IF;
5331
5332 IF(x_return_status <>'S') THEN
5333 ROLLBACK TO update_wro;
5334 END IF;
5335
5336 -- Standard check of p_commit.
5337 IF fnd_api.to_boolean(p_commit)
5338 and x_return_status = 'S' THEN
5339 COMMIT WORK;
5340 END IF;
5341
5342
5343 EXCEPTION
5344 WHEN OTHERS THEN
5345 ROLLBACK TO update_wro;
5346 x_return_status := fnd_api.g_ret_sts_unexp_error;
5347
5348 END update_wro;
5349
5350 PROCEDURE delete_instance (
5351 p_api_version IN NUMBER
5352 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
5353 ,p_commit IN VARCHAR2 := fnd_api.g_false
5354 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
5355 ,p_wip_entity_id IN NUMBER
5356 ,p_organization_id IN NUMBER
5357 ,p_operation_seq_num IN NUMBER
5358 ,p_resource_seq_num IN NUMBER
5359 ,p_instance_id IN NUMBER
5360 ,x_return_status OUT NOCOPY VARCHAR2
5361 ,x_msg_count OUT NOCOPY NUMBER
5362 ,x_msg_data OUT NOCOPY VARCHAR2) IS
5363
5364
5365 l_api_name CONSTANT VARCHAR2(30) := 'delete_instance';
5366 l_api_version CONSTANT NUMBER := 1.0;
5367 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
5368
5369 l_stmt_num NUMBER;
5370 l_msg_count NUMBER;
5371 l_msg_data VARCHAR2(250);
5372 l_data VARCHAR2(250);
5373 l_msg_index_out NUMBER;
5374
5375
5376 /* added for calling WO API */
5377
5378 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
5379 l_eam_res_inst_rec EAM_PROCESS_WO_PUB.eam_res_inst_rec_type;
5380 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
5381 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
5382 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
5383 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
5384 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
5385 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
5386 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
5387 l_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
5388 l_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
5389 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
5390 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
5391 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
5392 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
5393 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
5394 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
5395 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
5396
5397 l_out_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
5398 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
5399 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
5400 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
5401 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
5402 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
5403 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
5404 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
5405 l_out_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
5406 l_out_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
5407 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
5408 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
5409 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
5410 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
5411 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
5412 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
5413 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
5414
5415 l_output_dir VARCHAR2(512);
5416
5417 BEGIN
5418 -- Standard Start of API savepoint
5419 l_stmt_num := 10;
5420 SAVEPOINT delete_instance_pvt;
5421
5422 l_stmt_num := 20;
5423 -- Standard call to check for call compatibility.
5424 IF NOT fnd_api.compatible_api_call(
5425 l_api_version
5426 ,p_api_version
5427 ,l_api_name
5428 ,g_pkg_name) THEN
5429 RAISE fnd_api.g_exc_unexpected_error;
5430 END IF;
5431
5432 l_stmt_num := 30;
5433
5434 -- Initialize message list if p_init_msg_list is set to TRUE.
5435 IF fnd_api.to_boolean(p_init_msg_list) THEN
5436 fnd_msg_pub.initialize;
5437 END IF;
5438
5439 l_stmt_num := 40;
5440 -- Initialize API return status to success
5441 x_return_status := fnd_api.g_ret_sts_success;
5442
5443 l_stmt_num := 50;
5444 -- API body
5445
5446 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
5447
5448
5449
5450 l_eam_res_inst_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_DELETE;
5451 l_eam_res_inst_rec.wip_entity_id := p_wip_entity_id;
5452 l_eam_res_inst_rec.organization_id := p_organization_id;
5453 l_eam_res_inst_rec.operation_seq_num := p_operation_seq_num;
5454 l_eam_res_inst_rec.resource_seq_num := p_resource_seq_num;
5455 l_eam_res_inst_rec.instance_id := p_instance_id;
5456
5457 l_eam_res_inst_tbl(1) := l_eam_res_inst_rec ;
5458
5459 EAM_PROCESS_WO_PUB.Process_WO
5460 ( p_bo_identifier => 'EAM'
5461 , p_init_msg_list => TRUE
5462 , p_api_version_number => 1.0
5463 , p_commit => 'N'
5464 , p_eam_wo_rec => l_eam_wo_rec
5465 , p_eam_op_tbl => l_eam_op_tbl
5466 , p_eam_op_network_tbl => l_eam_op_network_tbl
5467 , p_eam_res_tbl => l_eam_res_tbl
5468 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
5469 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
5470 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
5471 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
5472 , p_eam_direct_items_tbl => l_eam_di_tbl
5473 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
5474 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
5475 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
5476 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
5477 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
5478 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
5479 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
5480 , p_eam_request_tbl => l_eam_request_tbl
5481 , x_eam_wo_rec => l_out_eam_wo_rec
5482 , x_eam_op_tbl => l_out_eam_op_tbl
5483 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
5484 , x_eam_res_tbl => l_out_eam_res_tbl
5485 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
5486 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
5487 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
5488 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
5489 , x_eam_direct_items_tbl => l_out_eam_di_tbl
5490 , x_eam_wo_comp_rec => l_out_eam_wo_comp_rec
5491 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
5492 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
5493 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
5494 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
5495 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
5496 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
5497 , x_eam_request_tbl => l_out_eam_request_tbl
5498 , x_return_status => x_return_status
5499 , x_msg_count => x_msg_count
5500 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
5501 , p_debug_filename => 'delwor.log'
5502 , p_output_dir => l_output_dir
5503 , p_debug_file_mode => 'w'
5504 );
5505
5506
5507 -- End of API body.
5508 -- Standard check of p_commit.
5509 IF fnd_api.to_boolean(p_commit)
5510 and x_return_status = 'S' THEN
5511 COMMIT WORK;
5512 END IF;
5513
5514 IF(x_return_status <> 'S') THEN
5515 ROLLBACK TO delete_instance_pvt;
5516 END IF;
5517
5518 l_stmt_num := 999;
5519
5520 -- Standard call to get message count and if count is 1, get message info.
5521 fnd_msg_pub.count_and_get(
5522 p_count => x_msg_count
5523 ,p_data => x_msg_data);
5524
5525 EXCEPTION
5526 WHEN fnd_api.g_exc_error THEN
5527 ROLLBACK TO get_delete_resources_pvt;
5528 x_return_status := fnd_api.g_ret_sts_error;
5529 fnd_msg_pub.count_and_get(
5530 -- p_encoded => FND_API.g_false
5531 p_count => x_msg_count
5532 ,p_data => x_msg_data);
5533
5534 WHEN fnd_api.g_exc_unexpected_error THEN
5535 ROLLBACK TO get_delete_resources_pvt;
5536 x_return_status := fnd_api.g_ret_sts_unexp_error;
5537
5538 fnd_msg_pub.count_and_get(
5539 p_count => x_msg_count
5540 ,p_data => x_msg_data);
5541
5542 WHEN OTHERS THEN
5543 ROLLBACK TO get_delete_resources_pvt;
5544 x_return_status := fnd_api.g_ret_sts_unexp_error;
5545 IF fnd_msg_pub.check_msg_level(
5546 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
5547 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
5548 END IF;
5549
5550 fnd_msg_pub.count_and_get(
5551 p_count => x_msg_count
5552 ,p_data => x_msg_data);
5553
5554 END delete_instance;
5555
5556 end eam_operations_jsp;