1 PACKAGE BODY EAM_WO_CHANGE_STATUS_PVT AS
2 /* $Header: EAMVWOSB.pls 120.37.12020000.2 2012/07/05 13:18:05 vpasupur 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 g_dummy NUMBER;
139
140 BEGIN
141
142 -- Standard Start of API savepoint
143 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('=============================================== '); END IF;
144 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.change_status: Entering Status Change Package '); END IF;
145
146 SAVEPOINT change_status;
147
148 -- Standard call to check for call compatibility.
149 IF NOT fnd_api.compatible_api_call(
150 l_api_version
151 ,p_api_version
152 ,l_api_name
153 ,g_pkg_name) THEN
154 RAISE fnd_api.g_exc_unexpected_error;
155 END IF;
156
157 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.change_status: Initializing Message list for Status Change'); END IF;
158
159 -- Initialize message list if p_init_msg_list is set to TRUE.
160 IF fnd_api.to_boolean(p_init_msg_list) THEN
161 fnd_msg_pub.initialize;
162 END IF;
163
164 -- Initialize API return status to success
165 x_return_status := fnd_api.g_ret_sts_success;
166
167 x_mesg_token_tbl := l_mesg_token_tbl;
168
169
170 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.change_status: Entering Status Change API body'); END IF;
171
172 /********************************************************************/
173 -- API body
174
175 l_wip_entity_id := p_wip_entity_id;
176 l_organization_id := p_organization_id;
177 l_to_status_type := p_to_status_type;
178 l_user_id := p_user_id;
179 l_responsibility_id := p_responsibility_id;
180
181 SELECT wip_entity_name
182 INTO l_work_order_name
183 FROM wip_entities
184 WHERE wip_entity_id = p_wip_entity_id;
185
186 -- Validate status_id
187 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.change_status: Validating Status'); END IF;
188
189 IF l_to_status_type NOT IN (WIP_CONSTANTS.UNRELEASED,WIP_CONSTANTS.RELEASED, WIP_CONSTANTS.COMP_CHRG,WIP_CONSTANTS.COMP_NOCHRG, WIP_CONSTANTS.CLOSED,
190 WIP_CONSTANTS.HOLD, WIP_CONSTANTS.CANCELLED, WIP_CONSTANTS.PEND_SCHED, WIP_CONSTANTS.DRAFT)
191 THEN
192
193 raise fnd_api.g_exc_unexpected_error;
194
195 END IF;
196
197 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.change_status: Find current work order status'); END IF;
198
199 -- Get current status of work order
200
201 BEGIN
202
203 SELECT nvl(wdj.status_type,1),
204 nvl(wdj.firm_planned_flag,2),
205 wdj.organization_id,
206 nvl(wdj.asset_group_id,0),
207 nvl(wdj.asset_number,''),
208 nvl(wdj.rebuild_item_id,0),
209 nvl(wdj.rebuild_serial_number,''),
210 wdj.primary_item_id,
211 wdj.class_code,
212 we.wip_entity_name,
213 wdj.date_completed,
214 wdj.date_closed,
215 wdj.maintenance_object_source,
216 wdj.po_creation_time,
217 wdj.maintenance_object_id
218 INTO l_current_status,
219 l_firm_flag,
220 l_organization_id,
221 l_asset_group_id,
222 l_asset_number,
223 l_rebuild_item_id,
224 l_rebuild_serial_number,
225 l_primary_item_id,
226 l_class_code,
227 l_wip_entity_name,
228 l_date_completed,
229 l_date_closed,
230 l_maintenance_obj_src,
231 l_po_creation_time,
232 l_maint_obj_id
233 FROM wip_discrete_jobs wdj, wip_entities we
234 where wdj.wip_entity_id = l_wip_entity_id
235 and we.wip_entity_id = wdj.wip_entity_id
236 and we.organization_id = wdj.organization_id;
237
238 EXCEPTION
239 WHEN OTHERS THEN
240 l_current_status := 0; -- work order does not exist
241 l_firm_flag := 2;
242 END;
243 l_final_status := l_current_status;
244
245 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.change_status: Current WO Status: '||l_current_status||' To Status: '||l_to_status_type); END IF;
246
247 -- Determine whether it is a rebuild work order
248 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.change_status: Checking if this is a rebuild WO'); END IF;
249
250 IF(l_rebuild_item_id > 0) THEN
251 l_rebuild_flag := 'Y';
252 ELSIF (l_asset_group_id > 0) THEN
253 l_rebuild_flag := 'N';
254 ELSE
255 RAISE fnd_api.g_exc_unexpected_error;
256 END IF;
257
258 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.change_status: Is this a rebuild WO : '||l_rebuild_flag ); END IF;
259
260 -- Get WPS Parameters
261 -- WPS parameters
262 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.change_status: Getting WPS parameters'); END IF;
263
264 IF(WPS_COMMON.Get_Install_Status = 'I') THEN
265 WPS_COMMON.GetParameters(
266 P_Org_Id => l_organization_id,
267 X_Use_Finite_Scheduler => l_use_finite_scheduler,
268 X_Material_Constrained => l_material_constrained,
269 X_Horizon_Length => l_horizon_length);
270 ELSE
271 l_use_finite_scheduler := 2;
272 l_material_constrained := 2;
273 l_horizon_length := 0;
274 END IF;
275
276 -- End of WPS Parameters
277
278 -- Direct Change to Pending Bill Load, Failed Bill Load, Pending Routing Load,
279 -- Failed Routing Load, Pending - Mass Loaded, Pending Close, Failed Close
280 -- Pending Scheduling and Draft not Possible.
281
282 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.change_status: Checking if status change is allowed'); END IF;
283
284 IF (l_to_status_type in (8,9,10,11,13,14,15,16,17)) THEN
285 IF l_current_status <> l_to_status_type AND l_current_status <> 0 THEN
286
287 raise CHANGE_STATUS_NOT_POSSIBLE;
288
289 END IF;
290 END IF;
291
292 /************************************************/
293 -- Change to Unreleased Status
294 /************************************************/
295 IF (l_to_status_type = 1) THEN
296
297 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.change_status: Changing to Unreleased Status'); END IF;
298
299
300 IF p_work_order_mode <> EAM_PROCESS_WO_PVT.G_OPR_CREATE and
301 p_to_status_type = wip_constants.unreleased
302 THEN
303 EAM_ASSET_LOG_PVT.INSERT_ROW
304 (
305 p_api_version => 1.0,
306 p_event_date => sysdate,
307 p_event_type => 'EAM_SYSTEM_EVENTS',
308 p_event_id => 7,
309 p_organization_id => p_organization_id,
310 p_instance_id => l_maint_obj_id,
311 p_comments => null,
312 p_reference => l_work_order_name,
313 p_ref_id => p_wip_entity_id,
314 p_operable_flag => null,
315 p_reason_code => null,
316 x_return_status => l_asset_ops_return_status,
317 x_msg_count => l_asset_ops_msg_count,
318 x_msg_data => l_asset_ops_msg_data
319 );
320 END IF;
321
322 -- Delete data from EAM_WORK_ORDER_ROUTE table
323
324 DELETE FROM EAM_WORK_ORDER_ROUTE
325 WHERE wip_entity_id = p_wip_entity_id;
326
327 IF (l_current_status in (17,3,6,7,9,11,15)) THEN
328
329 BEGIN
330 EAM_WORKORDER_UTIL_PKG.UNRELEASE(X_Org_Id => l_organization_id,
331 X_Wip_Id => l_wip_entity_id,
332 X_Rep_Id => -1,
333 X_Line_Id => -1,
334 X_Ent_Type=> 6 );
335
336 --Fix for bug 8940736: Adding code to make DATE_RELEASED in WDJ to null when work order status is changed to unreleased
337 update wip_discrete_jobs
338 set date_released = null
339 where wip_entity_id = l_wip_entity_id
340 and organization_id=l_organization_id;
341
342
343 EXCEPTION
344 WHEN OTHERS THEN
345 raise INVALID_UNRELEASE;
346 END;
347
348 l_final_status := 1;
349
350 -- Bug#3499973. removing check for unlcose work order since cannot change to unreleased from closed.
351
352 ELSE
353 raise CHNGE_ST_FRM_TO_NOT_PSSBLE;
354 END IF; -- end of check for l_current_status
355
356
357
358 END IF; -- end of check for l_to_status_type
359
360
361
362 /********************************************
363 * Change to Release or On Hold Status *
364 *********************************************/
365
366
367 IF (l_to_status_type in (3,6) ) THEN
368
369
370 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.change_status: Changing to Released or On Hold Status'); END IF;
371
372 IF (l_current_status in (1,17,3,6,7,9,11,15)) THEN
373
374 IF (l_to_status_type = 3 ) THEN -- changed check for bug 3681752
375 IF (l_rebuild_flag = 'N') THEN
376 l_valid := EAM_WORKORDER_UTIL_PKG.check_released_onhold_allowed(
377 l_rebuild_flag,
378 l_organization_id,
379 l_asset_group_id,
380 l_asset_number,
381 l_primary_item_id);
382 ELSE
383 l_valid := EAM_WORKORDER_UTIL_PKG.check_released_onhold_allowed(
384 l_rebuild_flag,
385 l_organization_id,
386 l_rebuild_item_id,
387 l_rebuild_serial_number,
388 l_primary_item_id);
389 END IF; -- end of check for l_rebuild_flag
390
391 IF (l_valid = 1 ) THEN
392 RAISE INVALID_RELEASE;
393 END IF;
394 END IF; /* end if for l_to_status_type = 3 */
395
396
397 -- Call Finite Scheduler
398
399 -- Finite scheduler has been decommisioned as of 11.5.10
400 -- Hence commenting out the code below and hardcode the
401 -- the value of the l_use_finite_scheduler flag.
402
403 l_use_finite_scheduler := 2;
404
405 IF ((l_to_status_type = 3) AND (l_use_finite_scheduler = 1) AND (l_firm_flag =2) ) THEN
406
407 null;
408
409 ELSE
410
411 IF l_to_status_type IN (3) AND
412 l_current_status IN (0,1,6,7,16,17) AND
413 l_to_status_type <> l_current_status THEN
414 --Added for bug 12836690
415 IF l_current_status IN (6,7) THEN
416 BEGIN
417 /*Check if the workorder was previously released in an accounting peroid
418 which is closed now.If it is, dont call WIP_CHANGE_STATUS.Release as
419 INSERT_PEROID_BALANCES throws an exception in such case*/
420 select 1
421 into g_dummy
422 from org_acct_periods
423 where organization_id = l_organization_id
424 and trunc(p_date_released)
425 between period_start_date and schedule_close_date
426 and period_close_date is NULL;
427
428 WIP_CHANGE_STATUS.Release(
429 l_wip_entity_id,
430 l_organization_id,
431 NULL,
432 NULL,
433 l_class_code,
434 l_current_status,
435 l_to_status_type,
436 l_tmp,
437 nvl(p_date_released,sysdate));
438
439 EXCEPTION
440 WHEN NO_DATA_FOUND THEN
441 NULL;
442 END;
443 ELSE
444 WIP_CHANGE_STATUS.Release(
445 l_wip_entity_id,
446 l_organization_id,
447 NULL,
448 NULL,
449 l_class_code,
450 l_current_status,
451 l_to_status_type,
452 l_tmp,
453 nvl(p_date_released,sysdate));
454 END IF;
455
456 END IF;
457
458 IF l_to_status_type IN (6) AND
459 l_current_status IN (0,1,6,7,16,17) AND
460 l_to_status_type <> l_current_status THEN
461
462
463 select scheduled_start_date into
464 l_wo_sched_start_date from wip_discrete_jobs
465 where wip_entity_id = l_wip_entity_id
466 and organization_id = l_organization_id;
467 IF (l_wo_sched_start_date < sysdate) THEN
468 select nvl(min(period_start_date),l_wo_sched_start_date)
469 into l_min_open_period_date from org_acct_periods
470 where organization_id=l_organization_id
471 and open_flag = 'Y' and period_close_date is null;
472 l_date_released_calc := greatest(l_min_open_period_date,l_wo_sched_start_date);
473 ELSE
474 l_date_released_calc := sysdate;
475 END IF;
476
477 WIP_CHANGE_STATUS.Release(
478 l_wip_entity_id,
479 l_organization_id,
480 NULL,
481 NULL,
482 l_class_code,
483 l_current_status,
484 l_to_status_type,
485 l_tmp,
486 nvl(l_date_released_calc,sysdate));
487
488 END IF;
489
490 l_final_status := l_to_status_type;
491
492 END IF;
493
494 -- End of Check for Scheduling
495
496
497 -- Create Requisitions for OSP
498 IF ((l_to_status_type in (3)) AND (l_current_status in (1,6,17))) THEN
499
500 --IF po_creation_time for workorder is at_job_schedule_release then only create requisitions
501 IF (l_po_creation_time = WIP_CONSTANTS.AT_JOB_SCHEDULE_RELEASE) THEN
502 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.change_status: Creating osp req at release and po_creation_time correct'); END IF;
503 create_osp_req_at_rel(
504 p_wip_entity_id => l_wip_entity_id,
505 p_organization_id => l_organization_id); -- for Bug 8594830
506 END IF; --end of check for po_creation_time
507 END IF;
508
509 -- End of Creating Requisitions
510
511 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.change_status: Creating default operation at released'); END IF;
512
513 -- Create Default Operation if none exists
514 IF (l_to_status_type = 3) THEN
515
516
517 IF l_maintenance_obj_src <> 2 THEN
518
519 EAM_WORKORDER_UTIL_PKG.create_default_operation
520 (p_organization_id => l_organization_id
521 ,p_wip_entity_id => l_wip_entity_id
522 );
523
524 END IF;
525
526 UPDATE wip_requirement_operations
527 SET operation_seq_num = (SELECT MIN(operation_seq_num)
528 FROM wip_operations
529 WHERE wip_entity_id = l_wip_entity_id
530 AND organization_id = l_organization_id)
531 WHERE wip_entity_id = l_wip_entity_id
532 AND organization_id = l_organization_id
533 AND operation_seq_num = 1;
534
535 UPDATE wip_eam_direct_items
536 SET operation_seq_num = (SELECT MIN(operation_seq_num)
537 FROM wip_operations
538 WHERE wip_entity_id = l_wip_entity_id
539 AND organization_id = l_organization_id)
540 WHERE wip_entity_id = l_wip_entity_id
541 AND organization_id = l_organization_id
542 AND operation_seq_num = 1;
543
544
545 END IF;
546 -- End of Creating Default Operation
547
548
549 /* Insert the route snapshot, only if it does not already exist */
550
551 BEGIN
552 select count(*)
553 into l_route
554 from EAM_WORK_ORDER_ROUTE
555 where wip_entity_id = l_wip_entity_id;
556
557 if l_route = 0 then
558 INSERT INTO EAM_WORK_ORDER_ROUTE
559 (
560 wip_entity_id ,
561 route_asset_seq_id ,
562 instance_id ,
563 last_update_date ,
564 last_updated_by ,
565 creation_date ,
566 created_by ,
567 last_update_login
568 )
569 SELECT
570 wdj.wip_entity_id,
571 EAM_WORK_ORDER_ROUTE_S.nextval,
572 mena.maintenance_object_id,
573 sysdate,
574 fnd_global.login_id,
575 sysdate,
576 fnd_global.user_id,
577 fnd_global.login_id
578 FROM
579 WIP_DISCRETE_JOBS wdj,
580 MTL_EAM_NETWORK_ASSETS mena,
581 CSI_ITEM_INSTANCES CII,
582 MTL_PARAMETERS mp
583 WHERE
584 mena.network_object_id = wdj.maintenance_object_id
585 AND wdj.organization_id = p_organization_id
586 AND wdj.wip_entity_id = p_wip_entity_id
587 AND mena.maintenance_object_id = cii.instance_id
588 AND cii.last_vld_organization_id = mp.organization_id
589 AND mp.maint_organization_id = p_organization_id
590 AND nvl(mena.start_date_active, sysdate) <= nvl(wdj.date_released, sysdate)
591 AND nvl(mena.end_date_active, sysdate) >= nvl(wdj.date_released, sysdate);
592 end if;
593
594 EXCEPTION
595 When others THEN
596 null;
597 END;
598
599 IF p_work_order_mode <> EAM_PROCESS_WO_PVT.G_OPR_CREATE and
600 ( p_to_status_type = wip_constants.released)
601 THEN
602 EAM_ASSET_LOG_PVT.INSERT_ROW
603 (
604 p_api_version => 1.0,
605 p_event_date => sysdate,
606 p_event_type => 'EAM_SYSTEM_EVENTS',
607 p_event_id => 6,
608 p_organization_id => p_organization_id,
609 p_instance_id => l_maint_obj_id,
610 p_comments => null,
611 p_reference => l_work_order_name,
612 p_ref_id => p_wip_entity_id,
613 p_operable_flag => null,
614 p_reason_code => null,
615 x_return_status => l_asset_ops_return_status,
616 x_msg_count => l_asset_ops_msg_count,
617 x_msg_data => l_asset_ops_msg_data
618 );
619 END IF;
620
621 IF (l_to_status_type in (3) ) THEN -- Added the check for bug 13102446
622 -- create requisitions for direct items.
623 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.change_status: Check for Requisition creation'); END IF;
624
625 -- Bug # 4862404 : Replace eam_direct_item_recs_v by base table
626
627 BEGIN
628 SELECT 1 INTO l_di_count
629 FROM DUAL
630 WHERE EXISTS
631 (
632 SELECT 1
633 FROM wip_eam_direct_items wedi
634 WHERE wedi.wip_entity_id = l_wip_entity_id
635 AND wedi.organization_id = l_organization_id
636 )
637 OR EXISTS
638 (
639 SELECT 1
640 FROM wip_requirement_operations wro, mtl_system_items_b msi
641 WHERE wro.wip_entity_id = l_wip_entity_id
642 AND wro.organization_id = l_organization_id
643 AND wro.inventory_item_id = msi.inventory_item_id
644 AND wro.organization_id = msi.organization_id
645 AND nvl(msi.stock_enabled_flag, 'N') = 'N'
646 );
647 EXCEPTION
648 WHEN NO_DATA_FOUND THEN
649 l_di_count := 0;
650 END;
651
652 IF l_di_count > 0 THEN
653
654 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.change_status: Creating direct item requisitions at release '); END IF;
655 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.change_status: Calling EAM_PROCESS_WO_UTIL_PVT.create_reqs_at_wo_rel'); END IF;
656 EAM_PROCESS_WO_UTIL_PVT.create_reqs_at_wo_rel
657 ( p_api_version => 1.0
658 ,p_init_msg_list => FND_API.G_FALSE
659 ,p_commit => FND_API.G_FALSE
660 ,p_validate_only => FND_API.G_TRUE
661 ,x_return_status => l_di_return_status
662 ,x_msg_count => l_di_msg_count
663 ,x_msg_data => l_di_msg_data
664 ,p_user_id => l_user_id
665 ,p_responsibility_id => l_responsibility_id
666 ,p_wip_entity_id => l_wip_entity_id
667 ,p_organization_id => l_organization_id);
668
669 END IF; -- end of check for l_di_count
670
671 -- end create requisitions for direct items.
672
673 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.change_status: Creating direct item requisitions completed with status '||l_di_return_status); END IF;
674
675 IF NVL(l_di_return_status,'S') <> 'S' THEN
676 x_return_status := fnd_api.g_ret_sts_error;
677 END IF;
678 END IF; -- End of If (l_to_status_type in (3))
679
680 l_final_status := l_to_status_type;
681
682
683 -- Bug#3499973. removing check for unclose work order since cannot change to released/on-hold from closed.
684
685 ELSE
686 raise CHNGE_ST_FRM_TO_NOT_PSSBLE;
687 END IF;
688 -- End of Check for Current Status
689
690 END IF;
691 -- End of Check for Release and On Hold status
692
693
694 /***********************************************************/
695 -- Change to Cancel Status
696 /***********************************************************/
697
698 IF (l_to_status_type = 7) THEN
699
700 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.change_status: Chaning to Cancel Status'); END IF;
701
702 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.
703 l_final_status := l_to_status_type;
704 ELSE
705 raise CHNGE_ST_FRM_TO_NOT_PSSBLE;
706 END IF;
707
708 -- Moved the code to EAMWOMDF.pld as part of fix 3489907
709
710 --fix for 3572050
711 --fix for 3701696
712 BEGIN
713 SELECT 1
714 INTO l_relations_count
715 FROM eam_wo_relationships
716 WHERE (parent_object_id=l_wip_entity_id
717 OR child_object_id=l_wip_entity_id)
718 AND parent_relationship_type =2 AND rownum<=1;
719 EXCEPTION
720 WHEN NO_DATA_FOUND THEN
721 null;
722 END;
723
724 IF(l_relations_count=1) THEN
725 x_return_status := fnd_api.g_ret_sts_error;
726 l_token_tbl(1).token_name := 'WORKORDER';
727 l_token_tbl(1).token_value := l_wip_entity_id;
728 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
729 ( p_message_name => 'EAM_DELINK_CANCELLED'
730 , p_token_tbl => l_token_tbl
731 , p_mesg_token_tbl => l_mesg_token_tbl
732 , x_mesg_token_tbl => x_mesg_token_tbl
733 );
734 return;
735 END IF;
736
737 END IF; -- end of check for l_to_status_type
738
739 /***********************************************************/
740 -- Change between Complete and Complete No Charge Status
741 /***********************************************************/
742 IF (l_to_status_type IN (4,5)) THEN
743 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.change_status: Chaning to Complete Status'); END IF;
744
745 IF (l_current_status in (4,5)) THEN
746 l_final_status := l_to_status_type;
747
748 ELSIF (l_current_status = 12) AND (l_date_completed is not null) THEN
749 l_unclose := 1;
750 l_final_status := l_to_status_type; -- bug #3499973
751 ELSIF (l_current_status = 15) AND (l_date_completed is not null) THEN
752 l_final_status := l_to_status_type;
753 ELSE
754 raise CHNGE_ST_FRM_TO_NOT_PSSBLE;
755 END IF;
756
757 END IF;
758
759
760 /***********************************************************/
761 -- Change to Closed Status
762 /***********************************************************/
763
764 IF (l_to_status_type = 12) THEN
765 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.change_status: Chaning to Closed Status'); END IF;
766 IF (l_current_status IN (4,5,7)) THEN
767 l_final_status := 14; --set l_final_status to 14(Pending close)
768
769 BEGIN
770 SELECT 1
771 INTO l_po_exists
772 FROM (
773 SELECT 1
774 FROM PO_RELEASES_ALL PR,
775 PO_HEADERS_ALL PH,
776 PO_DISTRIBUTIONS_ALL PD,
777 PO_LINE_LOCATIONS_ALL PLL
778 WHERE pd.po_line_id IS NOT NULL
779 AND pd.line_location_id IS NOT NULL
780 AND PD.WIP_ENTITY_ID = l_wip_entity_id
781 AND PD.DESTINATION_ORGANIZATION_ID = l_organization_id
782 AND PH.PO_HEADER_ID = PD.PO_HEADER_ID
783 AND PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
784 AND PR.PO_RELEASE_ID (+) = PD.PO_RELEASE_ID
785 AND (pll.cancel_flag IS NULL OR
786 pll.cancel_flag = 'N')
787 /* AND (
788 (PLL.QUANTITY_RECEIVED < (PLL.QUANTITY-PLL.QUANTITY_CANCELLED))
789 OR
790 (PLL.AMOUNT_RECEIVED < (PLL.AMOUNT-PLL.AMOUNT_CANCELLED))
791 ) -- ADDED AMOUNT condition for Bug7497877 commented this for bug8297942*/
792 AND nvl(pll.closed_code,'OPEN') not in ('FINALLY CLOSED', 'CLOSED') --Added CLOSED status:Bug#6142700
793 UNION ALL
794 SELECT 1
795 FROM PO_REQUISITION_LINES_ALL PRL
796 WHERE PRL.WIP_ENTITY_ID = l_wip_entity_id
797 AND PRL.DESTINATION_ORGANIZATION_ID = l_organization_id
798 AND nvl(PRL.cancel_flag, 'N') = 'N'
799 AND PRL.LINE_LOCATION_ID is NULL
800 AND nvl(PRL.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED') /*12392266, FP of 8286428*/
801 UNION ALL
802 SELECT 1
803 FROM PO_REQUISITIONS_INTERFACE_ALL PRI
804 WHERE PRI.WIP_ENTITY_ID = l_wip_entity_id
805 AND PRI.DESTINATION_ORGANIZATION_ID = l_organization_id
806 ) ;
807
808 IF l_po_exists = 1 THEN
809 x_return_status := fnd_api.g_ret_sts_error;
810 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
811 ( p_message_name => 'WIP_CANCEL_JOB/SCHED_OPEN_PO'
812 , p_token_tbl => l_token_tbl
813 , p_mesg_token_tbl => l_mesg_token_tbl
814 , x_mesg_token_tbl => x_mesg_token_tbl
815 );
816 return;
817 END IF;
818
819 EXCEPTION
820 WHEN No_Data_Found THEN
821 NULL;
822 WHEN TOO_MANY_ROWS THEN
823 x_return_status := fnd_api.g_ret_sts_error;
824 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
825 ( p_message_name => 'WIP_CANCEL_JOB/SCHED_OPEN_PO'
826 , p_token_tbl => l_token_tbl
827 , p_mesg_token_tbl => l_mesg_token_tbl
828 , x_mesg_token_tbl => x_mesg_token_tbl
829 );
830 return;
831 END;
832 BEGIN
833 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.change_status: Selecting group_id'); END IF;
834 SELECT wip_dj_close_temp_s.nextval
835 INTO l_gid
836 FROM dual;
837 EXCEPTION
838 WHEN OTHERS THEN
839 raise fnd_api.g_exc_unexpected_error;
840 END;
841
842 BEGIN
843 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.change_status: Insert into WIP_DJ_CLOSE_TEMP'); END IF;
844 INSERT INTO WIP_DJ_CLOSE_TEMP
845 (WIP_ENTITY_ID,
846 ORGANIZATION_ID,
847 WIP_ENTITY_NAME,
848 PRIMARY_ITEM_ID,
849 STATUS_TYPE,
850 actual_close_date,
851 GROUP_ID)
852 VALUES
853 (l_wip_entity_id,
854 l_organization_id,
855 l_wip_entity_name,
856 l_primary_item_id,
857 decode(l_current_status, 16, 1, l_current_status),
858 NVL(p_actual_close_date,SYSDATE),
859 l_gid);
860
861 EXCEPTION
862 WHEN OTHERS THEN
863 raise fnd_api.g_exc_unexpected_error;
864 END;
865
866 -- This call to fnd_global.apps_initialize is needed because this is
867 -- part of the WO API which can also be used as a standalone API
868 -- and there needs to be a call to APPS_INITIALIZE before
869 -- concurrent programs are called
870
871 IF (p_user_id IS NOT NULL AND p_responsibility_id IS NOT NULL) THEN
872 FND_GLOBAL.APPS_INITIALIZE(p_user_id, p_responsibility_id,426,0);
873 END IF;
874
875 IF(l_maintenance_obj_src =1 ) THEN --for EAM invoke online WIP close API
876
877 EAM_JOBCLOSE_PRIV.EAM_CLOSE_WO
878 (p_submission_date => p_submission_date,
879 p_organization_id => l_organization_id,
880 p_group_id => l_gid,
881 p_select_jobs => 2,
882 p_report_type => NVL(p_report_type,'4'),
883 x_request_id => l_request_id
884 );
885
886 UPDATE EAM_WORK_ORDER_DETAILS
887 SET user_defined_status_id = l_closed_status,
888 last_update_date = SYSDATE,
889 last_updated_by = fnd_global.user_id,
890 last_update_login = fnd_global.login_id
891 WHERE wip_entity_id = l_wip_entity_id;
892
893
894 ELSE --for other prodcuts like CMRO invoke normal conc. program.
895
896 l_request_id := fnd_request.submit_request('WIP', 'WICDCL', NULL,
897 NULL,
898 FALSE,
899 p_organization_id,'','','','','','','','','',
900 '','','',l_gid,2,'','','','1','',
901 chr(0),'','','','','','','','','',
902 '','','','','','','','','','',
903 '','','','','','','','','','',
904 '','','','','','','','','','',
905 '','','','','','','','','','',
906 '','','','','','','','','','',
907 '','','','','','','','','','',
908 '','','','','','','','','','');
909
910
911
912
913 END IF;
914
915 ELSE
916 raise CHNGE_ST_FRM_TO_NOT_PSSBLE;
917 END IF; -- end of check for l_current_status
918
919 END IF; -- End of Close Status
920
921
922 /******************************************************/
923 -- Update WIP_DISCRETE_JOBS with the changed job status
924 /******************************************************/
925
926 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.change_status: Updating WO with status'); END IF;
927
928 BEGIN
929
930 UPDATE WIP_DISCRETE_JOBS
931 SET STATUS_TYPE = l_final_status
932 WHERE ORGANIZATION_ID = l_organization_id
933 AND WIP_ENTITY_ID = l_wip_entity_id;
934
935 EXCEPTION
936 WHEN OTHERS THEN
937 raise fnd_api.g_exc_unexpected_error;
938 END;
939
940
941 /*******************************************/
942 -- Unclose a Job
943 /*******************************************/
944
945 IF (l_unclose = 1) THEN
946 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.change_status: Unclosing WO'); END IF;
947
948 IF WIP_CLOSE_UTILITIES.Unclose_Job(l_wip_entity_id,
949 l_organization_id,
950 l_class_code) = 1 THEN
951
952 BEGIN
953
954 UPDATE WIP_ENTITIES
955 SET ENTITY_TYPE = 6
956 WHERE ORGANIZATION_ID = l_organization_id
957 AND WIP_ENTITY_ID = l_wip_entity_id;
958
959 /*Code Added for bug#4760468 Start*/
960 UPDATE wip_discrete_jobs
961 SET date_closed = NULL
962 WHERE organization_id = l_organization_id
963 AND WIP_ENTITY_ID = l_wip_entity_id;
964 /*Code added for bug#4760468 End*/
965
966
967 EXCEPTION
968 WHEN OTHERS THEN
969 raise fnd_api.g_exc_unexpected_error;
970 END;
971
972 ELSE
973
974 BEGIN
975
976 UPDATE WIP_ENTITIES
977 SET ENTITY_TYPE = 7
978 WHERE ORGANIZATION_ID = l_organization_id
979 AND WIP_ENTITY_ID = l_wip_entity_id;
980
981 EXCEPTION
982 WHEN OTHERS THEN
983 raise fnd_api.g_exc_unexpected_error;
984 END;
985
986 END IF; -- end of check for WIP_CLOSE_UTILITIES.Unclose_Job
987
988 END IF; -- end of check for l_unclose
989
990 /***********************************************************/
991 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.change_status: Exiting Status Change API'); END IF;
992
993 -- Standard call to get message count and if count is 1, get message info.
994 fnd_msg_pub.count_and_get(
995 p_count => x_msg_count
996 ,p_data => x_msg_data);
997
998 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.change_status: Schedule change request id '||l_request_id); END IF;
999
1000 x_request_id := l_request_id;
1001
1002 EXCEPTION
1003 WHEN fnd_api.g_exc_error THEN
1004 ROLLBACK TO change_status;
1005 x_return_status := fnd_api.g_ret_sts_error;
1006 fnd_msg_pub.count_and_get(
1007 p_count => x_msg_count
1008 ,p_data => x_msg_data);
1009
1010 WHEN CHANGE_STATUS_NOT_POSSIBLE THEN
1011 x_msg_count := 1;
1012 x_msg_data := 'Change to ' || l_to_status_type || ' is not possible';
1013 x_return_status := fnd_api.g_ret_sts_error;
1014
1015 WHEN CHNGE_ST_FRM_TO_NOT_PSSBLE THEN
1016
1017 x_msg_count := 1;
1018 x_msg_data := 'Change from ' || l_current_status || ' to ' || l_to_status_type || ' is not possible';
1019 x_return_status := fnd_api.g_ret_sts_error;
1020
1021 WHEN INVALID_RELEASE THEN
1022 x_msg_count := 1;
1023 x_msg_data := 'Work Order Cannot be Released';
1024 x_return_status := fnd_api.g_ret_sts_error;
1025
1026 WHEN INVALID_UNRELEASE THEN
1027 --Start of fix for 3357656.Get the message name from message stack and populate the token table
1028 x_return_status := fnd_api.g_ret_sts_error;
1029 l_encoded_message := fnd_message.get_encoded();
1030 fnd_message.parse_encoded(l_encoded_message,
1031 l_application_name,
1032 l_mesg_name);
1033 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
1034 ( p_message_name => l_mesg_name
1035 , p_token_tbl => l_token_tbl
1036 , p_mesg_token_tbl => l_mesg_token_tbl
1037 , x_mesg_token_tbl => x_mesg_token_tbl
1038 );
1039 --end of fix for 33575656
1040 WHEN fnd_api.g_exc_unexpected_error THEN
1041 ROLLBACK TO change_status;
1042 x_return_status := fnd_api.g_ret_sts_unexp_error;
1043
1044 fnd_msg_pub.count_and_get(
1045 p_count => x_msg_count
1046 ,p_data => x_msg_data);
1047
1048 WHEN OTHERS THEN
1049 ROLLBACK TO change_status;
1050 x_return_status := fnd_api.g_ret_sts_unexp_error;
1051 IF fnd_msg_pub.check_msg_level(
1052 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1053 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1054 END IF;
1055
1056 fnd_msg_pub.count_and_get(
1057 p_count => x_msg_count
1058 ,p_data => x_msg_data);
1059
1060 END change_status;
1061
1062
1063 /*Procedure to create osp requisitions at workorder release
1064 This procedure will check if there are any existing requisitions for each operation.
1065 If reqs. are not there then osp requisitions will be created for such operations
1066 */
1067 PROCEDURE create_osp_req_at_rel (
1068 p_wip_entity_id IN NUMBER,
1069 p_organization_id IN NUMBER
1070 )
1071 IS
1072 CURSOR Cdisc IS
1073 SELECT WOR.OPERATION_SEQ_NUM,
1074 WOR.RESOURCE_SEQ_NUM
1075 FROM WIP_OPERATION_RESOURCES WOR,
1076 WIP_OPERATIONS WO
1077 WHERE WO.WIP_ENTITY_ID = p_wip_entity_id
1078 AND WO.ORGANIZATION_ID = p_organization_id
1079 AND WOR.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
1080 AND WOR.ORGANIZATION_ID = WO.ORGANIZATION_ID
1081 AND WOR.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM
1082 AND WOR.AUTOCHARGE_TYPE = WIP_CONSTANTS.PO_RECEIPT
1083 AND WO.COUNT_POINT_TYPE <> WIP_CONSTANTS.NO_DIRECT;
1084 l_call_req_import VARCHAR2(10);
1085 l_request_id number;
1086 l_ou_id number;
1087 l_wip_error_flag Number := 0; /*Added for FP 6814440*/
1088 l_message varchar2(1000); /*6814440*/
1089 l_status boolean;
1090 errbuf varchar2(2000);
1091 l_str_application_id VARCHAR2(30);
1092 l_req_import_group_by VARCHAR2(50);
1093 BEGIN
1094 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('===============EAM_WO_CHANGE_STATUS_PVT.create_osp_req_at_rel============= '); END IF;
1095 SAVEPOINT create_osp_req_at_rel;
1096
1097 l_call_req_import := 'NO';
1098
1099 FOR cdis_rec in Cdisc LOOP
1100 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.create_osp_req_at_rel'); END IF;
1101 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' req for osp op : '||cdis_rec.OPERATION_SEQ_NUM||' osp resource : '||cdis_rec.RESOURCE_SEQ_NUM); END IF;
1102 --start for Bug 8594830
1103 --if requisitions are not already created then only create now
1104 IF ( NOT PO_REQ_EXISTS(
1105 p_wip_entity_id => p_wip_entity_id,
1106 p_rep_sched_id => null,
1107 p_organization_id => p_organization_id,
1108 p_op_seq_num => cdis_rec.OPERATION_SEQ_NUM,
1109 p_res_seq_num => cdis_rec.RESOURCE_SEQ_NUM,
1110 p_entity_type => 6)) THEN -- end for Bug 8594830
1111 l_call_req_import := 'YES'; --Do not call Req Import, if the status change is from hold to release and Requisition already exists
1112
1113 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.create_osp_req_at_rel: create req for osp resource now '); END IF;
1114 BEGIN
1115 WIP_OSP.CREATE_REQUISITION(
1116 P_Wip_Entity_Id => p_wip_entity_id,
1117 P_Organization_Id => p_organization_id,
1118 P_Repetitive_Schedule_Id => null,
1119 P_Operation_Seq_Num => cdis_rec.OPERATION_SEQ_NUM,
1120 P_Resource_Seq_Num => cdis_rec.RESOURCE_SEQ_NUM);
1121 EXCEPTION
1122 WHEN OTHERS THEN /*Added for FP 6814440*/
1123 l_wip_error_flag :=1;
1124 l_message := SUBSTR(FND_MESSAGE.get,1,500);
1125 FND_MESSAGE.SET_NAME('WIP', 'WIP_RELEASE_PO_MOVE');
1126 fnd_msg_pub.add;
1127 EAM_ERROR_MESSAGE_PVT.Add_Message
1128 ( p_mesg_text => l_message
1129 , p_entity_id => 1
1130 , p_entity_index => 1
1131 , p_message_type => 'E'
1132 );
1133 APP_EXCEPTION.RAISE_EXCEPTION;
1134 END;
1135 END IF; --end of check if reqs exist or not --for 8594830
1136 END LOOP;
1137
1138 IF (l_call_req_import = 'YES') THEN -- if req import has to be callled
1139 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.create_osp_req_at_rel: invoking req import program'); END IF;
1140
1141 BEGIN
1142 select to_number(ho.ORG_INFORMATION3)
1143 into l_ou_id
1144 from hr_organization_information ho
1145 where ho.organization_id = p_organization_id
1146 and ho.ORG_INFORMATION_CONTEXT = 'Accounting Information';
1147 EXCEPTION
1148 WHEN NO_DATA_FOUND THEN
1149 ROLLBACK TO create_osp_req_at_rel;
1150 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||'EAM_WO_CHANGE_STATUS_PVT.create_osp_req_at_rel: No operating unit found'); END IF;
1151 END;
1152
1153 fnd_request.set_org_id(l_ou_id);
1154
1155 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
1156 EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.create_osp_req_at_rel: user_id:'||fnd_global.user_id || 'resp_id:'||fnd_global.resp_id||' appl id:'||FND_GLOBAL.PROG_APPL_ID);
1157 END IF;
1158
1159 l_status := fnd_request.set_options(datagroup => 'Standard');
1160 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.create_osp_req_at_rel: set_options is set '); END IF;
1161
1162 l_str_application_id := fnd_profile.value('RESP_APPL_ID');
1163
1164 if (fnd_global.user_id is not null and fnd_global.resp_id is not null and l_str_application_id is not null) then
1165 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
1166 EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.create_osp_req_at_rel: calling FND_GLOBAL.APPS_INITIALIZE ');
1167 END IF;
1168
1169 FND_GLOBAL.APPS_INITIALIZE(fnd_global.user_id, fnd_global.resp_id, to_number(l_str_application_id),0);
1170 end if;
1171
1172 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
1173 EAM_ERROR_MESSAGE_PVT.Write_Debug(' EAM_WO_CHANGE_STATUS_PVT.create_osp_req_at_rel: user_id:'||fnd_global.user_id || 'resp_id:'||fnd_global.resp_id||' appl id:'||FND_GLOBAL.PROG_APPL_ID||' Strappid:'||l_str_application_id);
1174 END IF;
1175
1176 SELECT REQIMPORT_GROUP_BY_CODE into l_req_import_group_by FROM PO_SYSTEM_PARAMETERS_ALL where ORG_ID=l_ou_id;
1177
1178 l_request_id := fnd_request.submit_request(
1179 'PO', 'REQIMPORT', NULL, NULL, FALSE,'WIP', NULL, l_req_import_group_by,
1180 NULL ,'N', 'Y' , chr(0), NULL, NULL, NULL,
1181 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1182 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1183 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1184 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1185 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1186 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1187 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1188 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1189 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
1190 ) ;
1191 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||'EAM_WO_CHANGE_STATUS_PVT.create_osp_req_at_rel: Launced req import program for osp resource : request id : '||l_request_id); END IF;
1192 errbuf := fnd_message.get;
1193 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||'EAM_WO_CHANGE_STATUS_PVT.create_osp_req_at_rel: Error msg: '||errbuf); END IF;
1194
1195 END IF; --end of check for req import to be called or not
1196
1197 EXCEPTION
1198
1199 WHEN OTHERS THEN
1200 ROLLBACK TO create_osp_req_at_rel;
1201 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||'EAM_WO_CHANGE_STATUS_PVT.create_osp_req_at_rel: error creating reqs for osp at rel '); END IF;
1202 IF (l_wip_error_flag = 1) THEN /*Added for FP 6814440*/
1203 APP_EXCEPTION.RAISE_EXCEPTION;
1204 END IF;
1205 END create_osp_req_at_rel;
1206
1207 --Added for 8594830
1208 FUNCTION PO_REQ_EXISTS (p_wip_entity_id in NUMBER,
1209 p_rep_sched_id in NUMBER,
1210 p_organization_id in NUMBER,
1211 p_op_seq_num in NUMBER default NULL,
1212 p_res_seq_num in NUMBER,
1213 p_entity_type in NUMBER
1214 ) RETURN BOOLEAN IS
1215
1216 CURSOR disc_check_po_req_cur IS
1217 SELECT 'PO/REQ Linked'
1218 FROM PO_REQUISITION_LINES_ALL PRL,
1219 PO_REQUISITION_HEADERS_ALL PRH
1220 WHERE PRL.requisition_header_id = PRH.requisition_header_id(+)
1221 AND upper(NVL(PRH.authorization_status, 'APPROVED') ) not in ('CANCELLED', 'REJECTED','SYSTEM_SAVED')
1222 AND PRL.WIP_ENTITY_ID = p_wip_entity_id
1223 AND PRL.DESTINATION_ORGANIZATION_ID = p_organization_id
1224 AND (p_op_seq_num is NULL OR
1225 PRL.WIP_OPERATION_SEQ_NUM = p_op_seq_num)
1226 AND (p_res_seq_num is NULL OR
1227 PRL.WIP_RESOURCE_SEQ_NUM = p_res_seq_num)
1228 AND nvl(PRL.cancel_flag, 'N') = 'N'
1229 --AND PRL.LINE_LOCATION_ID is NULL
1230 UNION ALL
1231 SELECT 'PO/REQ Linked'
1232 FROM PO_REQUISITIONS_INTERFACE_ALL PRI
1233 WHERE PRI.WIP_ENTITY_ID = p_wip_entity_id
1234 AND PRI.DESTINATION_ORGANIZATION_ID = p_organization_id
1235 AND (p_op_seq_num is NULL OR PRI.WIP_OPERATION_SEQ_NUM = p_op_seq_num)
1236 AND (p_res_seq_num is NULL OR PRI.WIP_RESOURCE_SEQ_NUM = p_res_seq_num)
1237 AND ((PRI.process_flag is null) or (Upper(Trim(PRI.process_flag)) = 'IN PROCESS'));
1238
1239
1240 po_req_exist VARCHAR2(20);
1241
1242 begin
1243 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
1244 EAM_ERROR_MESSAGE_PVT.Write_Debug('===============EAM_WO_CHANGE_STATUS_PVT.PO_REQ_EXISTS ============= ');
1245 END IF;
1246
1247 /*FOR DISCRETE, OSFM, AND EAM*/
1248 OPEN disc_check_po_req_cur;
1249 FETCH disc_check_po_req_cur INTO po_req_exist;
1250
1251 IF (disc_check_po_req_cur%FOUND) THEN
1252 CLOSE disc_check_po_req_cur;
1253 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
1254 EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.PO_REQ_EXISTS : Yes ');
1255 END IF;
1256
1257 return TRUE;
1258 ELSE
1259 CLOSE disc_check_po_req_cur;
1260 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
1261 EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_WO_CHANGE_STATUS_PVT.PO_REQ_EXISTS : No');
1262 END IF;
1263 return FALSE;
1264 END IF;
1265
1266 END PO_REQ_EXISTS;
1267
1268 END EAM_WO_CHANGE_STATUS_PVT; -- package body
1269
[Home] [Help]