[Home] [Help]
PACKAGE BODY: APPS.EAM_WO_CHANGE_STATUS_PVT
Source
1 PACKAGE BODY EAM_WO_CHANGE_STATUS_PVT AS
2 /* $Header: EAMVWOSB.pls 120.28.12010000.2 2008/10/30 06:13:33 srkotika ship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- EAMVWOSB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package EAM_WO_CHANGE_STATUS_PVT
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 30-JUN-2002 Amit Mondal Initial Creation
21 -- 15-Jul-2005 Anju Gupta Changes for MOAC in R12
22 ***************************************************************************/
23
24 G_Pkg_Name VARCHAR2(30) := 'EAM_WO_CHANGE_STATUS_PVT';
25
26 /*************************************************************************************
27 -- WIP_JOB_STATUS *
28 UNRELEASED := 1; -- Unreleased - no charges allowed *
29 SIMULATED := 2; -- Simulated *
30 RELEASED := 3; -- Released - charges allowed *
31 COMP_CHRG := 4; -- Complete - charges allowed *
32 COMP_NOCHRG := 5; -- Complete - no charges allowed *
33 HOLD := 6; -- Hold - no charges allowed *
34 CANCELLED := 7; -- Cancelled - no charges allowed *
35 PEND_BOM := 8; -- Pending bill of material load *
36 FAIL_BOM := 9; -- Failed bill of material load *
37 PEND_ROUT := 10; -- Pending routing load *
38 FAIL_ROUT := 11; -- Failed routing load *
39 CLOSED := 12; -- Closed - no charges allowed *
40 PEND_REPML := 13; -- Pending - repetitively mass loaded *
41 PEND_CLOSE := 14; -- Pending Close *
42 FAIL_CLOSE := 15; -- Failed Close *
43 PEND_SCHED := 16; -- Pending Scheduling (FS) *
44 DRAFT := 17; -- Draft *
45 ************************************************************************************/
46
47 PROCEDURE change_status (
48 p_api_version IN NUMBER
49 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
50 ,p_commit IN VARCHAR2 := fnd_api.g_false
51 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
52 ,p_wip_entity_id IN NUMBER
53 ,p_organization_id IN NUMBER
54 ,p_to_status_type IN NUMBER := wip_constants.unreleased
55 ,p_user_id IN NUMBER := null
56 ,p_responsibility_id IN NUMBER := null
57 ,p_date_released IN DATE := sysdate
58 , p_report_type IN NUMBER := null
59 , p_actual_close_date IN DATE := sysdate
60 , p_submission_date IN DATE := sysdate
61 ,p_work_order_mode IN NUMBER := EAM_PROCESS_WO_PVT.G_OPR_CREATE
62 ,x_request_id OUT NOCOPY NUMBER
63 ,x_return_status OUT NOCOPY VARCHAR2
64 ,x_msg_count OUT NOCOPY NUMBER
65 ,x_msg_data OUT NOCOPY VARCHAR2
66 ,x_Mesg_Token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type)
67 IS
68 l_api_name CONSTANT VARCHAR2(30) := 'change_status';
69 l_api_version CONSTANT NUMBER := 1.0;
70 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
71 l_wip_entity_id NUMBER := 0;
72 l_current_status NUMBER := 0;
73 l_to_status_type NUMBER := 0;
74 l_organization_id NUMBER := 0;
75 l_firm_flag NUMBER := 2;
76 l_final_status NUMBER := 0; -- this status will be updated in WDJ
77 l_user_id NUMBER :=0;
78 l_responsibility_id NUMBER :=0;
79
80 l_use_finite_scheduler NUMBER := 0;
81 l_material_constrained NUMBER := 0;
82 l_horizon_length NUMBER := 0;
83 l_asset_group_id NUMBER := 0;
84 l_asset_number VARCHAR2(30) := '';
85 l_rebuild_item_id NUMBER := 0;
86 l_rebuild_serial_number VARCHAR2(80) := '';
87 l_primary_item_id NUMBER := 0;
88 l_rebuild_flag VARCHAR2(1);
89 l_valid NUMBER := 0;
90 l_class_code VARCHAR2(10) := '';
91 l_tmp NUMBER := 0;
92 l_gid NUMBER := 0;
93 l_wip_entity_name VARCHAR2(240) := '';
94 l_date VARCHAR2(100);
95 l_date_completed DATE;
96 l_date_closed DATE;
97 l_unclose NUMBER := 0;
98 l_maintenance_obj_src NUMBER := 1;
99 l_di_count NUMBER := 0;
100 l_di_msg_count NUMBER := 0;
101 l_di_msg_data VARCHAR2(80) := '';
102 l_di_return_status VARCHAR2(80) := '';
103 l_po_creation_time NUMBER;
104 l_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
105
106 --fix for bug 3357656.Added following 4 parameters to get the message from eam_workutil_pkg.unrelease
107 l_encoded_message VARCHAR2(800);
108 l_application_name VARCHAR2(10);
109 l_mesg_name VARCHAR2(100);
110 l_token_tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
111
112 l_request_id NUMBER;
113 l_return_status VARCHAR2(80);
114 l_msg_count NUMBER := 0;
115 l_msg_data varchar2(2000) ;
116
117 l_relations_count NUMBER:=0;
118
119 CHANGE_STATUS_NOT_POSSIBLE EXCEPTION;
120 CHNGE_ST_FRM_TO_NOT_PSSBLE EXCEPTION;
121 INVALID_RELEASE EXCEPTION;
122 INVALID_UNRELEASE EXCEPTION;
123
124 l_work_order_name VARCHAR2(240);
125 l_asset_ops_msg_count NUMBER;
126 l_asset_ops_msg_data VARCHAR2(2000);
127 l_asset_ops_return_status VARCHAR2(1);
128 l_maint_obj_id NUMBER;
129 l_warning VARCHAR2(100);
130 l_closed_status NUMBER;
131 l_route NUMBER;
132
133 l_date_released_calc DATE;
134 l_min_open_period_date DATE;
135 l_wo_sched_start_date DATE;
136
137 l_po_exists NUMBER := 0;
138
139 BEGIN
140
141 -- Standard Start of API savepoint
142 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Entering Status Change Package'); END IF;
143
144 SAVEPOINT change_status;
145
146 -- Standard call to check for call compatibility.
147 IF NOT fnd_api.compatible_api_call(
148 l_api_version
149 ,p_api_version
150 ,l_api_name
151 ,g_pkg_name) THEN
152 RAISE fnd_api.g_exc_unexpected_error;
153 END IF;
154
155 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Initializing Message list for Status Change'); END IF;
156
157 -- Initialize message list if p_init_msg_list is set to TRUE.
158 IF fnd_api.to_boolean(p_init_msg_list) THEN
159 fnd_msg_pub.initialize;
160 END IF;
161
162 -- Initialize API return status to success
163 x_return_status := fnd_api.g_ret_sts_success;
164
165 x_mesg_token_tbl := l_mesg_token_tbl;
166
167
168 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Entering Status Change API body'); END IF;
169
170 /********************************************************************/
171 -- API body
172
173 l_wip_entity_id := p_wip_entity_id;
174 l_organization_id := p_organization_id;
175 l_to_status_type := p_to_status_type;
176 l_user_id := p_user_id;
177 l_responsibility_id := p_responsibility_id;
178
179 SELECT wip_entity_name
180 INTO l_work_order_name
181 FROM wip_entities
182 WHERE wip_entity_id = p_wip_entity_id;
183
184 -- Validate status_id
185 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating Status'); END IF;
186
187 IF l_to_status_type NOT IN (WIP_CONSTANTS.UNRELEASED,WIP_CONSTANTS.RELEASED, WIP_CONSTANTS.COMP_CHRG,WIP_CONSTANTS.COMP_NOCHRG, WIP_CONSTANTS.CLOSED,
188 WIP_CONSTANTS.HOLD, WIP_CONSTANTS.CANCELLED, WIP_CONSTANTS.PEND_SCHED, WIP_CONSTANTS.DRAFT)
189 THEN
190
191 raise fnd_api.g_exc_unexpected_error;
192
193 END IF;
194
195 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Find current work order status'); END IF;
196
197 -- Get current status of work order
198
199 BEGIN
200
201 SELECT nvl(wdj.status_type,1),
202 nvl(wdj.firm_planned_flag,2),
203 wdj.organization_id,
204 nvl(wdj.asset_group_id,0),
205 nvl(wdj.asset_number,''),
206 nvl(wdj.rebuild_item_id,0),
207 nvl(wdj.rebuild_serial_number,''),
208 wdj.primary_item_id,
209 wdj.class_code,
210 we.wip_entity_name,
211 wdj.date_completed,
212 wdj.date_closed,
213 wdj.maintenance_object_source,
214 wdj.po_creation_time,
215 wdj.maintenance_object_id
216 INTO l_current_status,
217 l_firm_flag,
218 l_organization_id,
219 l_asset_group_id,
220 l_asset_number,
221 l_rebuild_item_id,
222 l_rebuild_serial_number,
223 l_primary_item_id,
224 l_class_code,
225 l_wip_entity_name,
226 l_date_completed,
227 l_date_closed,
228 l_maintenance_obj_src,
229 l_po_creation_time,
230 l_maint_obj_id
231 FROM wip_discrete_jobs wdj, wip_entities we
232 where wdj.wip_entity_id = l_wip_entity_id
233 and we.wip_entity_id = wdj.wip_entity_id
234 and we.organization_id = wdj.organization_id;
235
236 EXCEPTION
237 WHEN OTHERS THEN
238 l_current_status := 0; -- work order does not exist
239 l_firm_flag := 2;
240 END;
241 l_final_status := l_current_status;
242
243 -- Determine whether it is a rebuild work order
244 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Checking if this is a rebuild WO'); END IF;
245
246 IF(l_rebuild_item_id > 0) THEN
247 l_rebuild_flag := 'Y';
248 ELSIF (l_asset_group_id > 0) THEN
249 l_rebuild_flag := 'N';
250 ELSE
251 RAISE fnd_api.g_exc_unexpected_error;
252 END IF;
253
254
255 -- Get WPS Parameters
256 -- WPS parameters
257 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Getting WPS parameters'); END IF;
258
259 IF(WPS_COMMON.Get_Install_Status = 'I') THEN
260 WPS_COMMON.GetParameters(
261 P_Org_Id => l_organization_id,
262 X_Use_Finite_Scheduler => l_use_finite_scheduler,
263 X_Material_Constrained => l_material_constrained,
264 X_Horizon_Length => l_horizon_length);
265 ELSE
266 l_use_finite_scheduler := 2;
267 l_material_constrained := 2;
268 l_horizon_length := 0;
269 END IF;
270
271 -- End of WPS Parameters
272
273 -- Direct Change to Pending Bill Load, Failed Bill Load, Pending Routing Load,
274 -- Failed Routing Load, Pending - Mass Loaded, Pending Close, Failed Close
275 -- Pending Scheduling and Draft not Possible.
276 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Checking if status change is allowed'); END IF;
277
278 IF (l_to_status_type in (8,9,10,11,13,14,15,16,17)) THEN
279 IF l_current_status <> l_to_status_type AND l_current_status <> 0 THEN
280
281 raise CHANGE_STATUS_NOT_POSSIBLE;
282
283 END IF;
284 END IF;
285
286 /************************************************/
287 -- Change to Unreleased Status
288 /************************************************/
289 IF (l_to_status_type = 1) THEN
290
291 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Chaning to Unreleased Status'); END IF;
292
293
294 IF p_work_order_mode <> EAM_PROCESS_WO_PVT.G_OPR_CREATE and
295 p_to_status_type = wip_constants.unreleased
296 THEN
297 EAM_ASSET_LOG_PVT.INSERT_ROW
298 (
299 p_api_version => 1.0,
300 p_event_date => sysdate,
301 p_event_type => 'EAM_SYSTEM_EVENTS',
302 p_event_id => 7,
303 p_organization_id => p_organization_id,
304 p_instance_id => l_maint_obj_id,
305 p_comments => null,
306 p_reference => l_work_order_name,
307 p_ref_id => p_wip_entity_id,
308 p_operable_flag => null,
309 p_reason_code => null,
310 x_return_status => l_asset_ops_return_status,
311 x_msg_count => l_asset_ops_msg_count,
312 x_msg_data => l_asset_ops_msg_data
313 );
314 END IF;
315
316 -- Delete data from EAM_WORK_ORDER_ROUTE table
317
318 DELETE FROM EAM_WORK_ORDER_ROUTE
319 WHERE wip_entity_id = p_wip_entity_id;
320
321 IF (l_current_status in (17,3,6,7,9,11,15)) THEN
322
323 BEGIN
324 EAM_WORKORDER_UTIL_PKG.UNRELEASE(X_Org_Id => l_organization_id,
325 X_Wip_Id => l_wip_entity_id,
326 X_Rep_Id => -1,
327 X_Line_Id => -1,
328 X_Ent_Type=> 6 );
329
330 EXCEPTION
331 WHEN OTHERS THEN
332 raise INVALID_UNRELEASE;
333 END;
334
335 l_final_status := 1;
336
337 -- Bug#3499973. removing check for unlcose work order since cannot change to unreleased from closed.
338
339 ELSE
340 raise CHNGE_ST_FRM_TO_NOT_PSSBLE;
341 END IF; -- end of check for l_current_status
342
343
344
345 END IF; -- end of check for l_to_status_type
346
347
348
349 /********************************************
350 * Change to Release or On Hold Status *
351 *********************************************/
352
353
354 IF (l_to_status_type in (3,6) ) THEN
355
356
357 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Chaning to Released or On Hold Status'); END IF;
358
359 IF (l_current_status in (1,17,3,6,7,9,11,15)) THEN
360
361 IF (l_to_status_type = 3 ) THEN -- changed check for bug 3681752
362 IF (l_rebuild_flag = 'N') THEN
363 l_valid := EAM_WORKORDER_UTIL_PKG.check_released_onhold_allowed(
364 l_rebuild_flag,
365 l_organization_id,
366 l_asset_group_id,
367 l_asset_number,
368 l_primary_item_id);
369 ELSE
370 l_valid := EAM_WORKORDER_UTIL_PKG.check_released_onhold_allowed(
371 l_rebuild_flag,
372 l_organization_id,
373 l_rebuild_item_id,
374 l_rebuild_serial_number,
375 l_primary_item_id);
376 END IF; -- end of check for l_rebuild_flag
377
378 IF (l_valid = 1 ) THEN
379 RAISE INVALID_RELEASE;
380 END IF;
381 END IF; /* end if for l_to_status_type = 3 */
382
383
384 -- Call Finite Scheduler
385
386 -- Finite scheduler has been decommisioned as of 11.5.10
387 -- Hence commenting out the code below and hardcode the
388 -- the value of the l_use_finite_scheduler flag.
389
390 l_use_finite_scheduler := 2;
391
392 IF ((l_to_status_type = 3) AND (l_use_finite_scheduler = 1) AND (l_firm_flag =2) ) THEN
393
394 null;
395
396 ELSE
397
398 IF l_to_status_type IN (3) AND
399 l_current_status IN (0,1,6,16,17) AND
400 l_to_status_type <> l_current_status THEN
401
402
403 WIP_CHANGE_STATUS.Release(
404 l_wip_entity_id,
405 l_organization_id,
406 NULL,
407 NULL,
408 l_class_code,
409 l_current_status,
410 l_to_status_type,
411 l_tmp,
412 nvl(p_date_released,sysdate));
413
414
415 END IF;
416
417 IF l_to_status_type IN (6) AND
418 l_current_status IN (0,1,6,16,17) AND
419 l_to_status_type <> l_current_status THEN
420
421
422 select scheduled_start_date into
423 l_wo_sched_start_date from wip_discrete_jobs
424 where wip_entity_id = l_wip_entity_id
425 and organization_id = l_organization_id;
426 IF (l_wo_sched_start_date < sysdate) THEN
427 select nvl(min(period_start_date),l_wo_sched_start_date)
428 into l_min_open_period_date from org_acct_periods
429 where organization_id=l_organization_id
430 and open_flag = 'Y' and period_close_date is null;
431 l_date_released_calc := greatest(l_min_open_period_date,l_wo_sched_start_date);
432 ELSE
433 l_date_released_calc := sysdate;
434 END IF;
435
436 WIP_CHANGE_STATUS.Release(
437 l_wip_entity_id,
438 l_organization_id,
439 NULL,
440 NULL,
441 l_class_code,
442 l_current_status,
443 l_to_status_type,
444 l_tmp,
445 nvl(l_date_released_calc,sysdate));
446
447 END IF;
448
449 l_final_status := l_to_status_type;
450
451 END IF;
452
453 -- End of Check for Scheduling
454
455 -- Create Requisitions for OSP
456 IF ((l_to_status_type in (3)) AND (l_current_status in (1,6,17))) THEN
457
458 --IF po_creation_time for workorder is at_job_schedule_release then only create requisitions
459 IF (l_po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE) THEN
460 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Creating osp req at release and po_creation_time correct'); END IF;
461 create_osp_req_at_rel(
462 p_wip_entity_id => l_wip_entity_id,
463 p_organization_id => l_organization_id);
464 END IF; --end of check for po_creation_time
465 END IF;
466
467 -- End of Creating Requisitions
468
469 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Creating default operation at released'); END IF;
470
471 -- Create Default Operation if none exists
472 IF (l_to_status_type = 3) THEN
473
474
475 IF l_maintenance_obj_src <> 2 THEN
476
477 EAM_WORKORDER_UTIL_PKG.create_default_operation
478 (p_organization_id => l_organization_id
479 ,p_wip_entity_id => l_wip_entity_id
480 );
481
482 END IF;
483
484 UPDATE wip_requirement_operations
485 SET operation_seq_num = (SELECT MIN(operation_seq_num)
486 FROM wip_operations
487 WHERE wip_entity_id = l_wip_entity_id
488 AND organization_id = l_organization_id)
489 WHERE wip_entity_id = l_wip_entity_id
490 AND organization_id = l_organization_id
491 AND operation_seq_num = 1;
492
493 UPDATE wip_eam_direct_items
494 SET operation_seq_num = (SELECT MIN(operation_seq_num)
495 FROM wip_operations
496 WHERE wip_entity_id = l_wip_entity_id
497 AND organization_id = l_organization_id)
498 WHERE wip_entity_id = l_wip_entity_id
499 AND organization_id = l_organization_id
500 AND operation_seq_num = 1;
501
502
503 END IF;
504 -- End of Creating Default Operation
505
506
507 /* Insert the route snapshot, only if it does not already exist */
508
509 BEGIN
510 select count(*)
511 into l_route
512 from EAM_WORK_ORDER_ROUTE
513 where wip_entity_id = l_wip_entity_id;
514
515 if l_route = 0 then
516 INSERT INTO EAM_WORK_ORDER_ROUTE
517 (
518 wip_entity_id ,
519 route_asset_seq_id ,
520 instance_id ,
521 last_update_date ,
522 last_updated_by ,
523 creation_date ,
524 created_by ,
525 last_update_login
526 )
527 SELECT
528 wdj.wip_entity_id,
529 EAM_WORK_ORDER_ROUTE_S.nextval,
530 mena.maintenance_object_id,
531 sysdate,
532 fnd_global.login_id,
533 sysdate,
534 fnd_global.user_id,
535 fnd_global.login_id
536 FROM
537 WIP_DISCRETE_JOBS wdj,
538 MTL_EAM_NETWORK_ASSETS mena,
539 CSI_ITEM_INSTANCES CII,
540 MTL_PARAMETERS mp
541 WHERE
542 mena.network_object_id = wdj.maintenance_object_id
543 AND wdj.organization_id = p_organization_id
544 AND wdj.wip_entity_id = p_wip_entity_id
545 AND mena.maintenance_object_id = cii.instance_id
546 AND cii.last_vld_organization_id = mp.organization_id
547 AND mp.maint_organization_id = p_organization_id
548 AND nvl(mena.start_date_active, sysdate) <= nvl(wdj.date_released, sysdate)
549 AND nvl(mena.end_date_active, sysdate) >= nvl(wdj.date_released, sysdate);
550 end if;
551
552 EXCEPTION
553 When others THEN
554 null;
555 END;
556
557 IF p_work_order_mode <> EAM_PROCESS_WO_PVT.G_OPR_CREATE and
558 ( p_to_status_type = wip_constants.released)
559 THEN
560 EAM_ASSET_LOG_PVT.INSERT_ROW
561 (
562 p_api_version => 1.0,
563 p_event_date => sysdate,
564 p_event_type => 'EAM_SYSTEM_EVENTS',
565 p_event_id => 6,
566 p_organization_id => p_organization_id,
567 p_instance_id => l_maint_obj_id,
568 p_comments => null,
569 p_reference => l_work_order_name,
570 p_ref_id => p_wip_entity_id,
571 p_operable_flag => null,
572 p_reason_code => null,
573 x_return_status => l_asset_ops_return_status,
574 x_msg_count => l_asset_ops_msg_count,
575 x_msg_data => l_asset_ops_msg_data
576 );
577 END IF;
578
579 -- create requisitions for direct items.
580 -- Bug # 4862404 : Replace eam_direct_item_recs_v by base table
581
582 BEGIN
583 SELECT 1 INTO l_di_count
584 FROM DUAL
585 WHERE EXISTS
586 (
587 SELECT 1
588 FROM wip_eam_direct_items wedi
589 WHERE wedi.wip_entity_id = l_wip_entity_id
590 AND wedi.organization_id = l_organization_id
591 )
592 OR EXISTS
593 (
594 SELECT 1
595 FROM wip_requirement_operations wro, mtl_system_items_b msi
596 WHERE wro.wip_entity_id = l_wip_entity_id
597 AND wro.organization_id = l_organization_id
598 AND wro.inventory_item_id = msi.inventory_item_id
599 AND wro.organization_id = msi.organization_id
600 AND nvl(msi.stock_enabled_flag, 'N') = 'N'
601 );
602 EXCEPTION
603 WHEN NO_DATA_FOUND THEN
604 l_di_count := 0;
605 END;
606
607 IF l_di_count > 0 THEN
608
609 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Creating direct item requisitions at release'); END IF;
610 EAM_PROCESS_WO_UTIL_PVT.create_reqs_at_wo_rel
611 ( p_api_version => 1.0
612 ,p_init_msg_list => FND_API.G_FALSE
613 ,p_commit => FND_API.G_FALSE
614 ,p_validate_only => FND_API.G_TRUE
615 ,x_return_status => l_di_return_status
616 ,x_msg_count => l_di_msg_count
617 ,x_msg_data => l_di_msg_data
618 ,p_user_id => l_user_id
619 ,p_responsibility_id => l_responsibility_id
620 ,p_wip_entity_id => l_wip_entity_id
621 ,p_organization_id => l_organization_id);
622
623 END IF; -- end of check for l_di_count
624
625 -- end create requisitions for direct items.
626
627 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Creating direct item requisitions completed with status '||l_di_return_status); END IF;
628
629 IF NVL(l_di_return_status,'S') <> 'S' THEN
630 x_return_status := fnd_api.g_ret_sts_error;
631 END IF;
632
633
634 l_final_status := l_to_status_type;
635
636
637 -- Bug#3499973. removing check for unclose work order since cannot change to released/on-hold from closed.
638
639 ELSE
640 raise CHNGE_ST_FRM_TO_NOT_PSSBLE;
641 END IF;
642 -- End of Check for Current Status
643
644 END IF;
645 -- End of Check for Release and On Hold status
646
647
648 /***********************************************************/
649 -- Change to Cancel Status
650 /***********************************************************/
651
652 IF (l_to_status_type = 7) THEN
653
654 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Chaning to Cancel Status'); END IF;
655
656 IF (l_current_status in (17,9,1,3,6,15,12)) THEN -- bug#3499973 included status 12(cancelled) for unclose closed work orders to cancelled.
657 l_final_status := l_to_status_type;
658 ELSE
659 raise CHNGE_ST_FRM_TO_NOT_PSSBLE;
660 END IF;
661
662 -- Moved the code to EAMWOMDF.pld as part of fix 3489907
663
664 --fix for 3572050
665 --fix for 3701696
666 BEGIN
667 SELECT 1
668 INTO l_relations_count
669 FROM eam_wo_relationships
670 WHERE (parent_object_id=l_wip_entity_id
671 OR child_object_id=l_wip_entity_id)
672 AND parent_relationship_type =2 AND rownum<=1;
673 EXCEPTION
674 WHEN NO_DATA_FOUND THEN
675 null;
676 END;
677
678 IF(l_relations_count=1) THEN
679 x_return_status := fnd_api.g_ret_sts_error;
680 l_token_tbl(1).token_name := 'WORKORDER';
681 l_token_tbl(1).token_value := l_wip_entity_id;
682 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
683 ( p_message_name => 'EAM_DELINK_CANCELLED'
684 , p_token_tbl => l_token_tbl
685 , p_mesg_token_tbl => l_mesg_token_tbl
686 , x_mesg_token_tbl => x_mesg_token_tbl
687 );
688 return;
689 END IF;
690
691 END IF; -- end of check for l_to_status_type
692
693 /***********************************************************/
694 -- Change between Complete and Complete No Charge Status
695 /***********************************************************/
696 IF (l_to_status_type IN (4,5)) THEN
697 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Chaning to Complete Status'); END IF;
698
699 IF (l_current_status in (4,5)) THEN
700 l_final_status := l_to_status_type;
701
702 ELSIF (l_current_status = 12) AND (l_date_completed is not null) THEN
703 l_unclose := 1;
704 l_final_status := l_to_status_type; -- bug #3499973
705 ELSIF (l_current_status = 15) AND (l_date_completed is not null) THEN
706 l_final_status := l_to_status_type;
707 ELSE
708 raise CHNGE_ST_FRM_TO_NOT_PSSBLE;
709 END IF;
710
711 END IF;
712
713
714 /***********************************************************/
715 -- Change to Closed Status
716 /***********************************************************/
717
718 IF (l_to_status_type = 12) THEN
719 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Chaning to Closed Status'); END IF;
720 IF (l_current_status IN (4,5,7)) THEN
721 l_final_status := 14; --set l_final_status to 14(Pending close)
722
723 BEGIN
724 SELECT 1
725 INTO l_po_exists
726 FROM (
727 SELECT 1
728 FROM PO_RELEASES_ALL PR,
729 PO_HEADERS_ALL PH,
730 PO_DISTRIBUTIONS_ALL PD,
731 PO_LINE_LOCATIONS_ALL PLL
732 WHERE pd.po_line_id IS NOT NULL
733 AND pd.line_location_id IS NOT NULL
734 AND PD.WIP_ENTITY_ID = l_wip_entity_id
735 AND PD.DESTINATION_ORGANIZATION_ID = l_organization_id
736 AND PH.PO_HEADER_ID = PD.PO_HEADER_ID
737 AND PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
738 AND PR.PO_RELEASE_ID (+) = PD.PO_RELEASE_ID
739 AND (pll.cancel_flag IS NULL OR
740 pll.cancel_flag = 'N')
741 AND (
742 (PLL.QUANTITY_RECEIVED < (PLL.QUANTITY-PLL.QUANTITY_CANCELLED))
743 OR
744 (PLL.AMOUNT_RECEIVED < (PLL.AMOUNT-PLL.AMOUNT_CANCELLED))
745 ) /*ADDED AMOUNT condition for Bug7497877*/
746 AND nvl(pll.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') --Added CLOSED status:Bug#6142700
747 UNION ALL
748 SELECT 1
749 FROM PO_REQUISITION_LINES_ALL PRL
750 WHERE PRL.WIP_ENTITY_ID = l_wip_entity_id
751 AND PRL.DESTINATION_ORGANIZATION_ID = l_organization_id
752 AND nvl(PRL.cancel_flag, 'N') = 'N'
753 AND PRL.LINE_LOCATION_ID is NULL
754 UNION ALL
755 SELECT 1
756 FROM PO_REQUISITIONS_INTERFACE_ALL PRI
757 WHERE PRI.WIP_ENTITY_ID = l_wip_entity_id
758 AND PRI.DESTINATION_ORGANIZATION_ID = l_organization_id
759 ) ;
760
761 IF l_po_exists = 1 THEN
762 x_return_status := fnd_api.g_ret_sts_error;
763 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
764 ( p_message_name => 'WIP_CANCEL_JOB/SCHED_OPEN_PO'
765 , p_token_tbl => l_token_tbl
766 , p_mesg_token_tbl => l_mesg_token_tbl
767 , x_mesg_token_tbl => x_mesg_token_tbl
768 );
769 return;
770 END IF;
771
772 EXCEPTION
773 WHEN No_Data_Found THEN
774 NULL;
775 WHEN TOO_MANY_ROWS THEN
776 x_return_status := fnd_api.g_ret_sts_error;
777 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
778 ( p_message_name => 'WIP_CANCEL_JOB/SCHED_OPEN_PO'
779 , p_token_tbl => l_token_tbl
780 , p_mesg_token_tbl => l_mesg_token_tbl
781 , x_mesg_token_tbl => x_mesg_token_tbl
782 );
783 return;
784 END;
785 BEGIN
786 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Selecting group_id'); END IF;
787 SELECT wip_dj_close_temp_s.nextval
788 INTO l_gid
789 FROM dual;
790 EXCEPTION
791 WHEN OTHERS THEN
792 raise fnd_api.g_exc_unexpected_error;
793 END;
794
795 BEGIN
796 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Insert into WIP_DJ_CLOSE_TEMP'); END IF;
797 INSERT INTO WIP_DJ_CLOSE_TEMP
798 (WIP_ENTITY_ID,
799 ORGANIZATION_ID,
800 WIP_ENTITY_NAME,
801 PRIMARY_ITEM_ID,
802 STATUS_TYPE,
803 actual_close_date,
804 GROUP_ID)
805 VALUES
806 (l_wip_entity_id,
807 l_organization_id,
808 l_wip_entity_name,
809 l_primary_item_id,
810 decode(l_current_status, 16, 1, l_current_status),
811 NVL(p_actual_close_date,SYSDATE),
812 l_gid);
813
814 EXCEPTION
815 WHEN OTHERS THEN
816 raise fnd_api.g_exc_unexpected_error;
817 END;
818
819 -- This call to fnd_global.apps_initialize is needed because this is
820 -- part of the WO API which can also be used as a standalone API
821 -- and there needs to be a call to APPS_INITIALIZE before
822 -- concurrent programs are called
823
824 IF (p_user_id IS NOT NULL AND p_responsibility_id IS NOT NULL) THEN
825 FND_GLOBAL.APPS_INITIALIZE(p_user_id, p_responsibility_id,426,0);
826 END IF;
827
828 IF(l_maintenance_obj_src =1 ) THEN --for EAM invoke online WIP close API
829
830 EAM_JOBCLOSE_PRIV.EAM_CLOSE_WO
831 (p_submission_date => p_submission_date,
832 p_organization_id => l_organization_id,
833 p_group_id => l_gid,
834 p_select_jobs => 2,
835 p_report_type => NVL(p_report_type,'4'),
836 x_request_id => l_request_id
837 );
838
839 UPDATE EAM_WORK_ORDER_DETAILS
840 SET user_defined_status_id = l_closed_status,
841 last_update_date = SYSDATE,
842 last_updated_by = fnd_global.user_id,
843 last_update_login = fnd_global.login_id
844 WHERE wip_entity_id = l_wip_entity_id;
845
846
847 ELSE --for other prodcuts like CMRO invoke normal conc. program.
848
849 l_request_id := fnd_request.submit_request('WIP', 'WICDCL', NULL,
850 NULL,
851 FALSE,
852 p_organization_id,'','','','','','','','','',
853 '','','',l_gid,2,'','','','1','',
854 chr(0),'','','','','','','','','',
855 '','','','','','','','','','',
856 '','','','','','','','','','',
857 '','','','','','','','','','',
858 '','','','','','','','','','',
859 '','','','','','','','','','',
860 '','','','','','','','','','',
861 '','','','','','','','','','');
862
863
864
865
866 END IF;
867
868 ELSE
869 raise CHNGE_ST_FRM_TO_NOT_PSSBLE;
870 END IF; -- end of check for l_current_status
871
872 END IF; -- End of Close Status
873
874
875 /******************************************************/
876 -- Update WIP_DISCRETE_JOBS with the changed job status
877 /******************************************************/
878
879 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Updating WO with status'); END IF;
880
881 BEGIN
882
883 UPDATE WIP_DISCRETE_JOBS
884 SET STATUS_TYPE = l_final_status
885 WHERE ORGANIZATION_ID = l_organization_id
886 AND WIP_ENTITY_ID = l_wip_entity_id;
887
888 EXCEPTION
889 WHEN OTHERS THEN
890 raise fnd_api.g_exc_unexpected_error;
891 END;
892
893
894 /*******************************************/
895 -- Unclose a Job
896 /*******************************************/
897
898 IF (l_unclose = 1) THEN
899 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Unclosing WO'); END IF;
900
901 IF WIP_CLOSE_UTILITIES.Unclose_Job(l_wip_entity_id,
902 l_organization_id,
903 l_class_code) = 1 THEN
904
905 BEGIN
906
907 UPDATE WIP_ENTITIES
908 SET ENTITY_TYPE = 6
909 WHERE ORGANIZATION_ID = l_organization_id
910 AND WIP_ENTITY_ID = l_wip_entity_id;
911
912 /*Code Added for bug#4760468 Start*/
913 UPDATE wip_discrete_jobs
914 SET date_closed = NULL
915 WHERE organization_id = l_organization_id
916 AND WIP_ENTITY_ID = l_wip_entity_id;
917 /*Code added for bug#4760468 End*/
918
919
920 EXCEPTION
921 WHEN OTHERS THEN
922 raise fnd_api.g_exc_unexpected_error;
923 END;
924
925 ELSE
926
927 BEGIN
928
929 UPDATE WIP_ENTITIES
930 SET ENTITY_TYPE = 7
931 WHERE ORGANIZATION_ID = l_organization_id
932 AND WIP_ENTITY_ID = l_wip_entity_id;
933
934 EXCEPTION
935 WHEN OTHERS THEN
936 raise fnd_api.g_exc_unexpected_error;
937 END;
938
939 END IF; -- end of check for WIP_CLOSE_UTILITIES.Unclose_Job
940
941 END IF; -- end of check for l_unclose
942
943 /***********************************************************/
944 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Exiting Status Change API'); END IF;
945
946 -- Standard call to get message count and if count is 1, get message info.
947 fnd_msg_pub.count_and_get(
948 p_count => x_msg_count
949 ,p_data => x_msg_data);
950
951 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Schedule change request id '||l_request_id); END IF;
952
953 x_request_id := l_request_id;
954
955 EXCEPTION
956 WHEN fnd_api.g_exc_error THEN
957 ROLLBACK TO change_status;
958 x_return_status := fnd_api.g_ret_sts_error;
959 fnd_msg_pub.count_and_get(
960 p_count => x_msg_count
961 ,p_data => x_msg_data);
962
963 WHEN CHANGE_STATUS_NOT_POSSIBLE THEN
964 x_msg_count := 1;
965 x_msg_data := 'Change to ' || l_to_status_type || ' is not possible';
966 x_return_status := fnd_api.g_ret_sts_error;
967
968 WHEN CHNGE_ST_FRM_TO_NOT_PSSBLE THEN
969
970 x_msg_count := 1;
971 x_msg_data := 'Change from ' || l_current_status || ' to ' || l_to_status_type || ' is not possible';
972 x_return_status := fnd_api.g_ret_sts_error;
973
974 WHEN INVALID_RELEASE THEN
975 x_msg_count := 1;
976 x_msg_data := 'Work Order Cannot be Released';
977 x_return_status := fnd_api.g_ret_sts_error;
978
979 WHEN INVALID_UNRELEASE THEN
980 --Start of fix for 3357656.Get the message name from message stack and populate the token table
981 x_return_status := fnd_api.g_ret_sts_error;
982 l_encoded_message := fnd_message.get_encoded();
983 fnd_message.parse_encoded(l_encoded_message,
984 l_application_name,
985 l_mesg_name);
986 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
987 ( p_message_name => l_mesg_name
988 , p_token_tbl => l_token_tbl
989 , p_mesg_token_tbl => l_mesg_token_tbl
990 , x_mesg_token_tbl => x_mesg_token_tbl
991 );
992 --end of fix for 33575656
993 WHEN fnd_api.g_exc_unexpected_error THEN
994 ROLLBACK TO change_status;
995 x_return_status := fnd_api.g_ret_sts_unexp_error;
996
997 fnd_msg_pub.count_and_get(
998 p_count => x_msg_count
999 ,p_data => x_msg_data);
1000
1001 WHEN OTHERS THEN
1002 ROLLBACK TO change_status;
1003 x_return_status := fnd_api.g_ret_sts_unexp_error;
1004 IF fnd_msg_pub.check_msg_level(
1005 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1006 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1007 END IF;
1008
1009 fnd_msg_pub.count_and_get(
1010 p_count => x_msg_count
1011 ,p_data => x_msg_data);
1012
1013 END change_status;
1014
1015
1016 /*Procedure to create osp requisitions at workorder release
1017 This procedure will check if there are any existing requisitions for each operation.
1018 If reqs. are not there then osp requisitions will be created for such operations
1019 */
1020 PROCEDURE create_osp_req_at_rel (
1021 p_wip_entity_id IN NUMBER,
1022 p_organization_id IN NUMBER
1023 )
1024 IS
1025 CURSOR Cdisc IS
1026 SELECT WOR.OPERATION_SEQ_NUM,
1027 WOR.RESOURCE_SEQ_NUM
1028 FROM WIP_OPERATION_RESOURCES WOR,
1029 WIP_OPERATIONS WO
1030 WHERE WO.WIP_ENTITY_ID = p_wip_entity_id
1031 AND WO.ORGANIZATION_ID = p_organization_id
1032 AND WOR.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
1033 AND WOR.ORGANIZATION_ID = WO.ORGANIZATION_ID
1034 AND WOR.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM
1035 AND WOR.AUTOCHARGE_TYPE = WIP_CONSTANTS.PO_RECEIPT
1036 AND WO.COUNT_POINT_TYPE <> WIP_CONSTANTS.NO_DIRECT;
1037 l_call_req_import VARCHAR2(10);
1038 l_request_id number;
1039 l_ou_id number;
1040 l_wip_error_flag Number := 0; /*Added for FP 6814440*/
1041 l_message varchar2(1000); /*6814440*/
1042 BEGIN
1043
1044 SAVEPOINT create_osp_req_at_rel;
1045
1046 l_call_req_import := 'NO';
1047
1048 FOR cdis_rec in Cdisc LOOP
1049 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('req for osp op'||cdis_rec.OPERATION_SEQ_NUM); END IF;
1050 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('req for osp resource'||cdis_rec.RESOURCE_SEQ_NUM); END IF;
1051
1052 --if requisitions are not already created then only create now
1053 /* IF NOT (WIP_OSP.PO_REQ_EXISTS(
1054 p_wip_entity_id => p_wip_entity_id,
1055 p_rep_sched_id => null,
1056 p_organization_id => p_organization_id,
1057 p_op_seq_num => cdis_rec.OPERATION_SEQ_NUM,
1058 p_entity_type => 6)
1059 ) THEN */
1060 l_call_req_import := 'YES'; --call req import later once
1061
1062 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('create req for osp resource now '); END IF;
1063 BEGIN
1064 WIP_OSP.CREATE_REQUISITION(
1065 P_Wip_Entity_Id => p_wip_entity_id,
1066 P_Organization_Id => p_organization_id,
1067 P_Repetitive_Schedule_Id => null,
1068 P_Operation_Seq_Num => cdis_rec.OPERATION_SEQ_NUM,
1069 P_Resource_Seq_Num => cdis_rec.RESOURCE_SEQ_NUM);
1070 EXCEPTION
1071 WHEN OTHERS THEN /*Added for FP 6814440*/
1072 l_wip_error_flag :=1;
1073 l_message := SUBSTR(FND_MESSAGE.get,1,500);
1074 FND_MESSAGE.SET_NAME('WIP', 'WIP_RELEASE_PO_MOVE');
1075 fnd_msg_pub.add;
1076 EAM_ERROR_MESSAGE_PVT.Add_Message
1077 ( p_mesg_text => l_message
1078 , p_entity_id => 1
1079 , p_entity_index => 1
1080 , p_message_type => 'E'
1081 );
1082 APP_EXCEPTION.RAISE_EXCEPTION;
1083 END;
1084 -- END IF; --end of check if reqs exist or not
1085 END LOOP;
1086
1087 IF (l_call_req_import = 'YES') THEN -- if req import has to be callled
1088
1089 BEGIN
1090 select to_number(ho.ORG_INFORMATION3)
1091 into l_ou_id
1092 from hr_organization_information ho
1093 where ho.organization_id = p_organization_id
1094 and ho.ORG_INFORMATION_CONTEXT = 'Accounting Information';
1095 EXCEPTION
1096 WHEN NO_DATA_FOUND THEN
1097 ROLLBACK TO create_osp_req_at_rel;
1098 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('No operating unit found'); END IF;
1099 END;
1100
1101 fnd_request.set_org_id(l_ou_id);
1102
1103 l_request_id := fnd_request.submit_request(
1104 'PO', 'REQIMPORT', NULL, NULL, FALSE,'WIP', NULL, 'ITEM',
1105 NULL ,'N', 'Y' , chr(0), NULL, NULL, NULL,
1106 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1107 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1108 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1109 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1110 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1111 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1112 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1113 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1114 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
1115 ) ;
1116 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('invoke req import program for osp reqs '||l_request_id); END IF;
1117 END IF; --end of check for req import to be called or not
1118
1119 EXCEPTION
1120
1121 WHEN OTHERS THEN
1122 ROLLBACK TO create_osp_req_at_rel;
1123 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('error creating reqs for osp at rel '); END IF;
1124 IF (l_wip_error_flag = 1) THEN /*Added for FP 6814440*/
1125 APP_EXCEPTION.RAISE_EXCEPTION;
1126 END IF;
1127 END create_osp_req_at_rel;
1128
1129 END EAM_WO_CHANGE_STATUS_PVT; -- package body
1130