[Home] [Help]
PACKAGE BODY: APPS.EAM_OP_COMP
Source
1 PACKAGE BODY eam_op_comp AS
2 /* $Header: EAMOCMPB.pls 120.8 2006/09/18 08:33:47 cboppana noship $ */
3
4
5 FUNCTION IS_WORKFLOW_ENABLED
6 (p_maint_obj_source IN NUMBER,
7 p_organization_id IN NUMBER
8 ) RETURN VARCHAR2
9 IS
10 l_workflow_enabled VARCHAR2(1);
11 BEGIN
12
13 BEGIN
14 SELECT enable_workflow
15 INTO l_workflow_enabled
16 FROM EAM_ENABLE_WORKFLOW
17 WHERE MAINTENANCE_OBJECT_SOURCE =p_maint_obj_source;
18 EXCEPTION
19 WHEN NO_DATA_FOUND THEN
20 l_workflow_enabled := 'N';
21 END;
22
23 --IF EAM workorder,check if workflow is enabled for this organization or not
24 IF(l_workflow_enabled ='Y' AND p_maint_obj_source=1) THEN
25 BEGIN
26 SELECT eam_wo_workflow_enabled
27 INTO l_workflow_enabled
28 FROM WIP_EAM_PARAMETERS
29 WHERE organization_id =p_organization_id;
30 EXCEPTION
31 WHEN NO_DATA_FOUND THEN
32 l_workflow_enabled := 'N';
33 END;
34 END IF; --check for workflow enabled at org level
35
36
37 RETURN l_workflow_enabled;
38
39 END IS_WORKFLOW_ENABLED;
40
41
42 PROCEDURE op_comp (
43 x_err_code OUT NOCOPY NUMBER,
44 x_err_msg OUT NOCOPY VARCHAR2,
45
46 p_wip_entity_id IN NUMBER,
47 p_operation_seq_num IN NUMBER,
48 p_transaction_type IN NUMBER,
49 p_transaction_date IN DATE,
50 p_actual_start_date IN DATE,
51 p_actual_end_date IN DATE,
52 p_actual_duration IN NUMBER,
53 p_shutdown_start_date IN DATE,
54 p_shutdown_end_date IN DATE,
55 p_reconciliation_code IN VARCHAR2,
56 p_attribute_category IN VARCHAR2 := NULL,
57 p_attribute1 IN VARCHAR2 := NULL,
58 p_attribute2 IN VARCHAR2 := NULL,
59 p_attribute3 IN VARCHAR2 := NULL,
60 p_attribute4 IN VARCHAR2 := NULL,
61 p_attribute5 IN VARCHAR2 := NULL,
62 p_attribute6 IN VARCHAR2 := NULL,
63 p_attribute7 IN VARCHAR2 := NULL,
64 p_attribute8 IN VARCHAR2 := NULL,
65 p_attribute9 IN VARCHAR2 := NULL,
66 p_attribute10 IN VARCHAR2 := NULL,
67 p_attribute11 IN VARCHAR2 := NULL,
68 p_attribute12 IN VARCHAR2 := NULL,
69 p_attribute13 IN VARCHAR2 := NULL,
70 p_attribute14 IN VARCHAR2 := NULL,
71 p_attribute15 IN VARCHAR2 := NULL,
72 p_qa_collection_id IN NUMBER,
73 p_vendor_id IN NUMBER := NULL,
74 p_vendor_site_id IN NUMBER := NULL,
75 p_vendor_contact_id IN NUMBER := NULL,
76 p_reason_id IN NUMBER := NULL,
77 p_reference IN VARCHAR2 := NULL
78 ) IS
79
80 l_op_completed VARCHAR2(1);
81
82 l_last_update_date DATE;
83 l_last_updated_by NUMBER;
84 l_last_update_login NUMBER;
85 l_organization_id NUMBER;
86 l_department_id NUMBER;
87 l_asset_number VARCHAR2(30);
88 l_asset_group_id NUMBER;
89 l_asset_activity_id NUMBER;
90 l_max_prior_end_date DATE;
91
92 l_prev_uncomplete NUMBER := 0;
93 l_prev_completed_after NUMBER := 0;
94 l_status_id NUMBER;
95 l_transaction_id NUMBER;
96 l_validate_msg VARCHAR2(100);
97 l_job_status NUMBER ;
98 l_maint_obj_source NUMBER;
99 l_workflow_enabled VARCHAR2(1);
100 l_op_completed_event VARCHAR2(240);
101 l_workflow_type NUMBER;
102 l_is_last_operation VARCHAR2(1);
103 l_parameter_list wf_parameter_list_t;
104 l_event_key VARCHAR2(200);
105 l_wf_event_seq NUMBER;
106 l_op_sched_end_date DATE;
107 l_wip_entity_name VARCHAR2(240);
108
109 --bug 3572376: pass following parameters to EAM_ASSET_STATUS_HISTORY
110 l_maintenance_object_type NUMBER := NULL ;
111 l_maintenance_object_id NUMBER := NULL ;
112 l_shutdown_type VARCHAR2(30) :=NULL;
113 CURSOR C IS
114 select
115 wo.organization_id,
116 wo.department_id,
117 wdj.asset_number,
118 wdj.asset_group_id,
119 wdj.primary_item_id ,
120 wo.shutdown_type,
121 wdj.maintenance_object_type,
122 wdj.maintenance_object_id,
123 wdj.maintenance_object_source,
124 ewod.workflow_type,
125 wo.last_unit_completion_date,
126 we.wip_entity_name
127 from
128 wip_operations wo,
129 wip_discrete_jobs wdj,
130 eam_work_order_details ewod,
131 wip_entities we
132 where
133 wdj.wip_entity_id = p_wip_entity_id AND
134 wdj.wip_entity_id = wo.wip_entity_id AND
135 wo.operation_seq_num = p_operation_seq_num
136 AND wdj.wip_entity_id = ewod.wip_entity_id(+)
137 AND wdj.wip_entity_id = we.wip_entity_id;
138
139 CURSOR CON IS
140 select count(won.prior_operation)
141 from wip_operation_networks won
142 where
143 won.wip_entity_id = p_wip_entity_id and
144 won.next_operation = p_operation_seq_num and
145 exists (
146 select 1 from wip_operations
147 where
148 wip_entity_id = p_wip_entity_id and
149 operation_seq_num = won.prior_operation and
150 nvl(operation_completed,'N') <> 'Y'
151 );
152 CURSOR CON1 IS
153 select count(won.next_operation)
154 from wip_operation_networks won
155 where
156 won.wip_entity_id = p_wip_entity_id and
157 won.prior_operation = p_operation_seq_num and
158 exists (
159 select 1 from wip_operations
160 where
161 wip_entity_id = p_wip_entity_id and
162 operation_seq_num = won.next_operation and
163 operation_completed = 'Y'
164 );
165
166
167 --added for fix to bug 3543834:
168 CURSOR CON3 IS
169 select nvl(max(actual_end_date),sysdate-20000)
170 from eam_op_completion_txns eoct,wip_operation_networks won
171 where eoct.wip_entity_id = p_wip_entity_id
172 and eoct.operation_seq_num=won.prior_operation
173 and won.wip_entity_id=eoct.wip_entity_id
174 and won.next_operation=p_operation_seq_num
175 and transaction_type=1
176 and transaction_id = (select max(transaction_id)
177 from eam_op_completion_txns
178 where wip_entity_id = p_wip_entity_id
179 and operation_seq_num = eoct.operation_seq_num
180 );
181
182
183
184 CURSOR CT IS
185 select transaction_id from eam_op_completion_txns
186 where transaction_id = l_transaction_id;
187 CURSOR CH IS
188 select asset_status_id from eam_asset_status_history
189 where asset_status_id = l_status_id;
190
191 BEGIN
192 x_err_code := 0;
193 if (p_transaction_type = 1) then
194 l_validate_msg := 'EAM_PREV_OP_NOT_COMPLETED';
195 else
196 l_validate_msg := 'EAM_NEXT_OP_COMPLETED';
197 end if;
198 if (p_transaction_type = 1) then
199 open CON;
200 fetch CON into l_prev_uncomplete;
201 if (CON%NOTFOUND) then
202 l_prev_uncomplete := 0;
203 end if;
204 close CON;
205 else
206 open CON1;
207 fetch CON1 into l_prev_uncomplete;
208 if (CON1%NOTFOUND) then
209 l_prev_uncomplete := 0;
210 end if;
211 close CON1;
212 end if;
213
214 IF (l_prev_uncomplete > 0) THEN
215 x_err_code := 1;
216 x_err_msg := l_validate_msg;
217 fnd_message.set_name(
218 'EAM',
219 x_err_msg);
220 APP_EXCEPTION.Raise_Exception;
221 END IF;
222
223 IF(p_transaction_type=1) THEN
224 --changed code for 3543834:
225 open CON3;
226 fetch CON3 into l_max_prior_end_date;
227 if(p_actual_start_date < l_max_prior_end_date) then
228 fnd_message.set_name('EAM','EAM_PRIOR_OP_COMPLETED_AFTER');
229 fnd_message.set_token('MIN_START_DATE',TO_CHAR(l_max_prior_end_date,'dd-MON-yyyy HH24:MI:SS'));
230 APP_EXCEPTION.Raise_Exception;
231 end if;
232 close CON3;
233 END IF;
234
235 --code added for bug 5476770
236 IF(p_shutdown_start_date > sysdate or p_shutdown_end_date > sysdate) THEN
237 fnd_message.set_name('EAM','EAM_SHUTDOWN_DATE_IN_FUTURE');
238 APP_EXCEPTION.Raise_Exception;
239 END IF;
240 --end of code for bug 5476770
241
242
243 IF ( p_transaction_type = 1 ) THEN
244 l_op_completed := 'Y';
245 ELSE
246 l_op_completed := 'N';
247 END IF;
248
249 /* Fix for Bug 2050412 -- Uncomplete the work ordder before uncompleting an operation */
250
251 IF (p_wip_entity_id is not null) and (l_op_completed = 'N') THEN
252 select status_type
253 into l_job_status
254 from wip_discrete_jobs
255 where wip_entity_id = p_wip_entity_id;
256
257 IF (l_job_status = 4 ) THEN
258 x_err_code := 1;
259 x_err_msg := l_validate_msg;
260 fnd_message.set_name(
261 'EAM',
262 'EAM_OP_COMP_WOCOMP_TEST');
263 --following line also added as part of bug 5440339
264 fnd_message.set_token('OP_SEQ_NO', p_operation_seq_num);
265 APP_EXCEPTION.Raise_Exception;
266 END IF;
267
268 END IF;
269
270 /* End of Fix 2050412 */
271
272 l_last_updated_by := FND_GLOBAL.USER_ID;
273 l_last_update_login := FND_GLOBAL.LOGIN_ID;
274 l_last_update_date := sysdate;
275
276 -- update TABLE wip_operations
277 UPDATE wip_operations
278 SET
279 operation_completed = l_op_completed,
280 quantity_completed = 1,
281 last_updated_by = l_last_updated_by,
282 last_update_date = l_last_update_date,
283 last_update_login = l_last_update_login
284 WHERE
285 wip_entity_id = p_wip_entity_id AND
286 operation_seq_num = p_operation_seq_num;
287
288 IF (SQL%NOTFOUND) THEN
289 x_err_code := 1;
290 x_err_msg := 'EAM_OP_NOT_FOUND';
291 fnd_message.set_name(
292 'EAM',
293 x_err_msg);
294 APP_EXCEPTION.Raise_Exception;
295 END IF;
296
297 OPEN C;
298 FETCH C into
299 l_organization_id,
300 l_department_id,
301 l_asset_number,
302 l_asset_group_id,
303 l_asset_activity_id,
304 l_shutdown_type,
305 l_maintenance_object_type,
306 l_maintenance_object_id,
307 l_maint_obj_source,
308 l_workflow_type,
309 l_op_sched_end_date,
310 l_wip_entity_name;
311
312 IF (C%NOTFOUND) THEN
313 close C;
314 x_err_code := 1;
315 x_err_msg := 'EAM_OP_NOT_FOUND';
316 fnd_message.set_name(
317 'EAM',
318 x_err_msg);
319 APP_EXCEPTION.Raise_Exception;
320 END IF;
321 close C;
322
323 select eam_op_completion_txns_s.nextval into l_transaction_id from dual;
324
325 -- -insert into TABLE eam_op_completion_txns
326 INSERT INTO EAM_OP_COMPLETION_TXNS(
327 TRANSACTION_ID,
328 TRANSACTION_DATE,
329 TRANSACTION_TYPE,
330 WIP_ENTITY_ID,
331 ORGANIZATION_ID,
332 OPERATION_SEQ_NUM,
333 ACCT_PERIOD_ID,
334 QA_COLLECTION_ID,
335 REFERENCE,
336 RECONCILIATION_CODE,
337 DEPARTMENT_ID,
338 ---ASSET_GROUP_ID,
339 --ASSET_NUMBER,
340 ASSET_ACTIVITY_ID,
341 ACTUAL_START_DATE,
342 ACTUAL_END_DATE,
343 ACTUAL_DURATION,
344 VENDOR_ID,
345 VENDOR_SITE_ID,
346 VENDOR_CONTACT_ID,
347 REASON_ID,
348 TRANSACTION_REFERENCE,
349 CREATED_BY,
350 CREATION_DATE,
351 LAST_UPDATED_BY,
352 LAST_UPDATE_DATE,
353 LAST_UPDATE_LOGIN,
354 attribute_category,
355 attribute1,
356 attribute2,
357 attribute3,
358 attribute4,
359 attribute5,
360 attribute6,
361 attribute7,
362 attribute8,
363 attribute9,
364 attribute10,
365 attribute11,
366 attribute12,
367 attribute13,
368 attribute14,
369 attribute15
370
371 ) VALUES (
372 l_transaction_id,
373 p_transaction_date,
374 p_transaction_type,
375 p_wip_entity_id,
376 l_organization_id,
377 p_operation_seq_num,
378 NULL,
379 p_qa_collection_id,
380 NULL,
381 p_reconciliation_code,
382 l_department_id,
383 --l_asset_group_id,
384 --l_asset_number,
385 l_asset_activity_id,
386 decode(p_transaction_type,1,p_actual_start_date,2,null),
387 decode(p_transaction_type,1,p_actual_end_date,2,null),
388 decode(p_transaction_type,1,p_actual_duration,2,null),
389 p_vendor_id,
390 p_vendor_site_id,
391 p_vendor_contact_id,
392 p_reason_id,
393 p_reference,
394 l_last_updated_by,
395 l_last_update_date,
396 l_last_updated_by,
397 l_last_update_date,
398 l_last_update_login,
399 p_attribute_category,
400 p_attribute1,
401 p_attribute2,
402 p_attribute3,
403 p_attribute4,
404 p_attribute5,
405 p_attribute6,
406 p_attribute7,
407 p_attribute8,
408 p_attribute9,
409 p_attribute10,
410 p_attribute11,
411 p_attribute12,
412 p_attribute13,
413 p_attribute14,
414 p_attribute15
415 );
416
417 OPEN CT;
418 fetch CT into l_transaction_id;
419 IF (CT%NOTFOUND) THEN
420 close CT;
421 x_err_code := 1;
422 x_err_msg := 'EAM_OP_TXN_NOT_FOUND';
423 fnd_message.set_name(
424 'EAM',
425 x_err_msg);
426 APP_EXCEPTION.Raise_Exception;
427 END IF;
428 close CT;
429
430 -- Enhancemnet Bug 3852846
431 IF NVL(to_number(l_shutdown_type),1) = 2 THEN
432 UPDATE eam_asset_status_history
433 SET enable_flag = 'N'
434 , last_update_date = SYSDATE
435 , last_updated_by = FND_GLOBAL.user_id
436 , last_update_login = FND_GLOBAL.login_id
437 WHERE organization_id = l_organization_id
438 AND wip_entity_id = p_wip_entity_id
439 AND operation_seq_num = p_operation_seq_num
440 AND enable_flag = 'Y' OR enable_flag IS NULL;
441 END IF;
442
443 -- SHUTDOWN History
444 if (p_shutdown_start_date is not null) or
445 (p_shutdown_end_date is not null) then
446
447 select eam_asset_status_history_s.nextval into l_status_id from dual;
448
449 INSERT INTO EAM_ASSET_STATUS_HISTORY(
450 ASSET_STATUS_ID,
451 ASSET_GROUP_ID,
452 ASSET_NUMBER,
453 ORGANIZATION_ID,
454 START_DATE,
455 END_DATE,
456 WIP_ENTITY_ID,
457 OPERATION_SEQ_NUM,
458 CREATED_BY,
459 CREATION_DATE,
460 LAST_UPDATED_BY,
461 LAST_UPDATE_DATE,
462 LAST_UPDATE_LOGIN,
463 attribute_category,
464 attribute1,
465 attribute2,
466 attribute3,
467 attribute4,
468 attribute5,
469 attribute6,
470 attribute7,
471 attribute8,
472 attribute9,
473 attribute10,
474 attribute11,
475 attribute12,
476 attribute13,
477 attribute14,
478 attribute15
479 ,MAINTENANCE_OBJECT_TYPE
480 ,MAINTENANCE_OBJECT_ID
481 ,enable_flag -- Enhancemnet Bug 3852846
482
483 ) VALUES (
484 l_status_id,
485 l_asset_group_id,
486 l_asset_number,
487 l_organization_id,
488 p_shutdown_start_date,
489 p_shutdown_end_date,
490 p_wip_entity_id,
491 p_operation_seq_num,
492 l_last_updated_by,
493 l_last_update_date,
494 l_last_updated_by,
495 l_last_update_date,
496 l_last_update_login,
497 p_attribute_category,
498 p_attribute1,
499 p_attribute2,
500 p_attribute3,
501 p_attribute4,
502 p_attribute5,
503 p_attribute6,
504 p_attribute7,
505 p_attribute8,
506 p_attribute9,
507 p_attribute10,
508 p_attribute11,
509 p_attribute12,
510 p_attribute13,
511 p_attribute14,
512 p_attribute15
513 ,l_maintenance_object_type
514 ,l_maintenance_object_id
515 ,'Y' -- Enhancemnet Bug 3852846
516 );
517
518 OPEN CH;
519 IF (CH%NOTFOUND) THEN
520 CLOSE CH;
521 x_err_code := 1;
522 x_err_msg := 'EAM_OP_HISTORY_NOT_FOUND';
523 fnd_message.set_name(
524 'EAM',
525 x_err_msg);
526 APP_EXCEPTION.Raise_Exception;
527 END IF;
528 CLOSE CH;
529 end if; -- history insert
530
531 l_workflow_enabled:= Is_Workflow_Enabled(l_maint_obj_source,l_organization_id);
532 l_op_completed_event := 'oracle.apps.eam.workorder.operation.completed';
533
534 IF(l_workflow_enabled='Y' AND (p_transaction_type = 1)
535 AND (Wf_Event.TEST(l_op_completed_event)<>'NONE' ) ) THEN
536
537 l_is_last_operation := 'N';
538 select DECODE(count(won.next_operation),0,'Y','N')
539 INTO l_is_last_operation
540 from wip_operation_networks won
541 where won.wip_entity_id = p_wip_entity_id and
542 won.prior_operation =p_operation_seq_num;
543
544 SELECT EAM_WORKFLOW_EVENT_S.NEXTVAL
545 INTO l_wf_event_seq
546 FROM DUAL;
547
548 l_parameter_list := wf_parameter_list_t();
549
550 l_event_key := TO_CHAR(l_wf_event_seq);
551 WF_CORE.CONTEXT('Enterprise Asset Management...','Work Order Op Completed event','Building parameter list');
552 -- Add Parameters
553 Wf_Event.AddParameterToList(p_name =>'WIP_ENTITY_ID',
554 p_value => TO_CHAR(p_wip_entity_id),
555 p_parameterlist => l_parameter_list);
556 Wf_Event.AddParameterToList(p_name =>'WIP_ENTITY_NAME',
557 p_value =>l_wip_entity_name,
558 p_parameterlist => l_parameter_list);
559 Wf_Event.AddParameterToList(p_name =>'ORGANIZATION_ID',
560 p_value => TO_CHAR(l_organization_id),
561 p_parameterlist => l_parameter_list);
562 Wf_Event.AddParameterToList(p_name =>'ACTUAL_COMPLETION_DATE',
563 p_value => TO_CHAR(p_actual_end_date),
564 p_parameterlist => l_parameter_list);
565 Wf_Event.AddParameterToList(p_name =>'SCHEDULED_COMPLETION_DATE',
566 p_value => TO_CHAR(l_op_sched_end_date),
567 p_parameterlist => l_parameter_list);
568 Wf_Event.AddParameterToList(p_name =>'IS_LAST_OPERATION',
569 p_value => l_is_last_operation,
570 p_parameterlist => l_parameter_list);
571 Wf_Event.AddParameterToList(p_name =>'WORKFLOW_TYPE',
572 p_value => TO_CHAR(l_workflow_type),
573 p_parameterlist => l_parameter_list);
574 Wf_Event.AddParameterToList(p_name =>'REQUESTOR',
575 p_value =>FND_GLOBAL.USER_NAME ,
576 p_parameterlist => l_parameter_list);
577
578 Wf_Event.Raise( p_event_name => l_op_completed_event,
579 p_event_key => l_event_key,
580 p_parameters => l_parameter_list);
581 l_parameter_list.DELETE;
582 WF_CORE.CONTEXT('Enterprise Asset Management...','Work Order Operation Completed Event','After raising event');
583
584 END IF; --end of check for raising op complete event
585
586
587 END op_comp;
588
589 PROCEDURE get_op_defaults
590 (p_wip_entity_id IN NUMBER,
591 p_tx_type IN NUMBER,
592 p_operation_seq_num IN NUMBER,
593 x_start_date out NOCOPY date,
594 x_end_date out NOCOPY date,
595 x_return_status out NOCOPY varchar2,
596 x_msg_data out NOCOPY varchar2
597 )
598 IS
599 l_api_name constant varchar2(30) := 'get_op_defaults';
600 l_max_prior_end_date DATE;
601 l_scheduled_start_date DATE;
602 l_scheduled_end_date DATE;
603 begin
604 eam_debug.init_err_stack('eam_workorders_jsp.' || l_api_name);
605
606 select first_unit_start_date,last_unit_completion_date
607 into l_scheduled_start_date,l_scheduled_end_date
608 from wip_operations
609 where wip_entity_id=p_wip_entity_id
610 and operation_seq_num=p_operation_seq_num;
611
612
613 if (p_tx_type = 1) then --completion
614 x_msg_data := 'Completion: ';
615
616
617 begin
618 --fix for 3543834.changed queries to fetch correct data
619 select max(actual_end_date)
620 into l_max_prior_end_date
621 from eam_op_completion_txns eoct,wip_operation_networks won
622 where eoct.wip_entity_id = p_wip_entity_id
623 and eoct.operation_seq_num=won.prior_operation
624 and won.wip_entity_id=eoct.wip_entity_id
625 and won.next_operation=p_operation_seq_num
626 and transaction_type=1
627 and transaction_id = (select max(transaction_id)
628 from eam_op_completion_txns
629 where wip_entity_id = p_wip_entity_id
630 and operation_seq_num = eoct.operation_seq_num
631 );
632
633 end;
634 if((l_max_prior_end_date is not null) and (l_scheduled_start_date <l_max_prior_end_date)) then
635 x_start_date := l_max_prior_end_date;
636 x_end_date := sysdate;
637 else
638 x_start_date := l_scheduled_start_date;
639 x_end_date := l_scheduled_end_date;
640 end if;
641 end if; -- of p_tx_type = 1
642 if (p_tx_type = 2) then --uncompletion
643 x_msg_data := 'Uncompletion: ';
644 x_start_date := l_scheduled_start_date;
645 x_end_date := l_scheduled_end_date;
646 end if;
647
648 IF(x_start_date > SYSDATE) THEN
649 x_start_date := SYSDATE;
650 x_end_date := SYSDATE;
651 ELSIF (x_end_date > SYSDATE) THEN
652 x_end_date := SYSDATE;
653 END IF;
654
655
656 x_return_status := FND_API.G_RET_STS_SUCCESS;
657
658 exception
659 when others then
660 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
661 x_msg_data := x_msg_data || ' UNEXPECTED ERROR: ' || SQLERRM;
662 eam_debug.init_err_stack('Exception has occured in ' || l_api_name);
663 end get_op_defaults;
664
665
666 END eam_op_comp;