[Home] [Help]
PACKAGE BODY: APPS.EAM_WO_NETWORK_DEFAULT_PVT
Source
1 PACKAGE BODY EAM_WO_NETWORK_DEFAULT_PVT AS
2 /* $Header: EAMVWNDB.pls 120.4.12010000.3 2009/11/05 21:17:39 mashah ship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- EAMVWNDB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package EAM_WO_NETWORK_DEFAULT_PVT
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 11-SEP-2003 Basanth Roy Initial Creation
21 -- 29-Sep-2003 samjain modified the resize_wo procedure to call the
22 process master child procedure for updating the workorder.
23 ***************************************************************************/
24
25
26
27 G_Pkg_Name VARCHAR2(30) := 'EAM_WO_NETWORK_DEFAULT_PVT';
28
29 g_token_tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
30 g_dummy NUMBER;
31
32
33 /*******************************************************************
34 * Procedure : Add_WO_To_Network
35 * Returns : None
36 * Parameters IN :
37 * Parameters OUT NOCOPY:
38 * Mesg Token Table
39 * Return Status
40 * Purpose :
41 *********************************************************************/
42 PROCEDURE Add_WO_To_Network
43 (
44 p_api_version IN NUMBER,
45 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
46 p_commit IN VARCHAR2 := FND_API.G_FALSE,
47 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
48
49 p_child_object_id IN NUMBER,
50 p_child_object_type_id IN NUMBER,
51 p_parent_object_id IN NUMBER,
52 p_parent_object_type_id IN NUMBER,
53 p_adjust_parent IN VARCHAR2 := FND_API.G_FALSE,
54 p_relationship_type IN NUMBER := 1,
55
56 x_return_status OUT NOCOPY VARCHAR2,
57 x_msg_count OUT NOCOPY NUMBER,
58 x_msg_data OUT NOCOPY VARCHAR2,
59 x_mesg_token_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
60 )
61
62
63 IS
64 l_api_name CONSTANT VARCHAR2(30) := 'Add_WO_To_Network';
65 l_api_version CONSTANT NUMBER := 1.0;
66
67 l_work_object_id NUMBER;
68 l_work_object_type_id NUMBER;
69
70 l_stmt_num NUMBER;
71 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
72 l_msg_count NUMBER;
73 l_msg_data VARCHAR2(1000);
74
75 l_exception_msg VARCHAR2(1000);
76
77 l_count NUMBER;
78 l_child_status_type NUMBER;
79 l_parent_status_type NUMBER;
80 l_first_constraining_parent NUMBER;
81 l_first_constr_parent_type NUMBER;
82 l_sched_relationship_id NUMBER;
83 l_wo_relationship_id NUMBER;
84 l_top_level_object_id NUMBER;
85 l_top_level_object_type_id NUMBER;
86 l_relationship_status NUMBER := 0; -- pending validation
87
88 l_parent_maint_obj_src NUMBER := 1;
89 l_child_maint_obj_src NUMBER := 1;
90 l_maint_obj_src NUMBER := 1;
91 l_rebuild_item_id NUMBER := null;
92
93 TYPE l_relationship_records IS REF CURSOR RETURN WIP_SCHED_RELATIONSHIPS%ROWTYPE;
94 l_constraining_parents l_relationship_records;
95 l_constraining_children l_relationship_records;
96 l_relationship_record WIP_SCHED_RELATIONSHIPS%ROWTYPE;
97
98 l_child_object_id NUMBER := p_child_object_id;
99 l_child_object_type_id NUMBER := p_child_object_type_id;
100 l_parent_object_id NUMBER := p_parent_object_id;
101 l_parent_object_type_id NUMBER := p_parent_object_type_id;
102 l_adjust_parent VARCHAR2(30) := p_adjust_parent;
103 l_relationship_type NUMBER := p_relationship_type;
104 l_parent_firm_flag NUMBER;
105
106 invalid_values exception;
107
108 l_err_text VARCHAR2(2000) := NULL;
109 l_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
110 l_out_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
111 l_Token_Tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
112
113 l_other_message VARCHAR2(20000);
114 l_other_token_tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
115
116 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
117 l_old_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
118 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type ;
119 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
120 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type ;
121 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type ;
122 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type ;
123 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type ;
124 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type ;
125
126 l_parent_workorder VARCHAR2(240);
127 l_child_workorder VARCHAR2(240);
128 l_wo_relationship_exc_tbl EAM_PROCESS_WO_PUB.wo_relationship_exc_tbl_type;
129 BEGIN
130
131 -- Standard Start of API savepoint
132 SAVEPOINT EAM_WN_ADD_WO;
133
134 -- Standard call to check for call compatibility.
135 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
136 p_api_version ,
137 l_api_name ,
138 G_PKG_NAME )
139 THEN
140 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
141 END IF;
142 -- Initialize message list if p_init_msg_list is set to TRUE.
143 IF FND_API.to_Boolean( p_init_msg_list ) THEN
144 FND_MSG_PUB.initialize;
145 END IF;
146 -- Initialize API return status to success
147 x_return_status := FND_API.G_RET_STS_SUCCESS;
148 -- API body
149
150
151 x_return_status := l_return_status;
152
153
154 SELECT wip_entity_name into l_parent_workorder
155 FROM wip_entities we
156 WHERE we.wip_entity_id = l_parent_object_id;
157
158 SELECT wip_entity_name into l_child_workorder
159 FROM wip_entities we
160 WHERE we.wip_entity_id = l_child_object_id;
161
162 -- Validations for input parameters
163
164 -- Check for null values
165 if l_child_object_id is null or
166 l_child_object_type_id is null or
167 l_parent_object_id is null or
168 l_parent_object_type_id is null then
169
170
171 l_out_mesg_token_tbl := l_mesg_token_tbl;
172 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
173 ( p_message_name => 'EAM_WN_NOT_NULL'
174 , p_token_tbl => l_token_tbl
175 , p_mesg_token_tbl => l_mesg_token_tbl
176 , x_mesg_token_tbl => l_out_mesg_token_tbl
177 );
178 l_mesg_token_tbl := l_out_mesg_token_tbl;
179
180 x_mesg_token_tbl := l_out_mesg_token_tbl;
181
182 x_return_status := 'E';
183 return;
184 end if;
185
186 -- Check that the Child and parent do not already have
187 -- a relation.
188 -- I am commenting this out because as per latest design
189 -- on 08/26/2003, we can have 2 relationships for the same
190 -- set of work orders. For eg. type 1 and 3
191 /*select count(*) into l_count
192 from eam_wo_relationships where
193 child_object_id = l_child_object_id
194 and child_object_type_id = l_child_object_type_id
195 and parent_object_id = l_parent_object_id
196 and parent_object_type_id = l_parent_object_type_id;
197 if l_count <> 0 then
198 x_return_status := 'E';
199 return;
200 end if;
201 */
202
203 -- Check that the parent_object_id and child_object_id are
204 -- valid wip_entity_id s. And if they are, then get their sources
205 begin
206 select status_type into l_child_status_type
207 from wip_discrete_jobs where
208 wip_entity_id = l_child_object_id;
209 exception
210 WHEN NO_DATA_FOUND THEN
211
212 l_token_tbl(1).token_name := 'Child Object Id';
213 l_token_tbl(1).token_value := l_child_object_id;
214
215 l_out_mesg_token_tbl := l_mesg_token_tbl;
216 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
217 ( p_message_name => 'EAM_WN_CHILD_OBJECT_ID'
218 , p_token_tbl => l_token_tbl
219 , p_mesg_token_tbl => l_mesg_token_tbl
220 , x_mesg_token_tbl => l_out_mesg_token_tbl
221 );
222 l_mesg_token_tbl := l_out_mesg_token_tbl;
223
224 x_mesg_token_tbl := l_out_mesg_token_tbl;
225
226 x_return_status := 'E';
227 return;
228 end;
229
230 select maintenance_object_source into
231 l_child_maint_obj_src
232 from wip_discrete_jobs where
233 wip_entity_id = l_child_object_id;
234
235
236 begin
237 select status_type into l_parent_status_type
238 from wip_discrete_jobs where
239 wip_entity_id = l_parent_object_id;
240
241 exception
242 WHEN NO_DATA_FOUND THEN
243 l_token_tbl(1).token_name := 'Parent Object Id';
244 l_token_tbl(1).token_value := l_parent_object_id;
245
246 l_out_mesg_token_tbl := l_mesg_token_tbl;
247 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
248 ( p_message_name => 'EAM_WN_PARENT_OBJECT_ID'
249 , p_token_tbl => l_token_tbl
250 , p_mesg_token_tbl => l_mesg_token_tbl
251 , x_mesg_token_tbl => l_out_mesg_token_tbl
252 );
253
254 l_mesg_token_tbl := l_out_mesg_token_tbl;
255
256 x_mesg_token_tbl := l_out_mesg_token_tbl;
257
258 x_return_status := 'E';
259 return;
260 end ;
261
262 select maintenance_object_source, rebuild_item_id into
263 l_parent_maint_obj_src, l_rebuild_item_id
264 from wip_discrete_jobs where
265 wip_entity_id = l_parent_object_id;
266
267
268 if l_parent_maint_obj_src <> l_child_maint_obj_src then
269
270 l_out_mesg_token_tbl := l_mesg_token_tbl;
271 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
272 ( p_message_name => 'EAM_WN_ADD_MAINT_OBJ_SRC'
273 , p_token_tbl => l_token_tbl
274 , p_mesg_token_tbl => l_mesg_token_tbl
275 , x_mesg_token_tbl => l_out_mesg_token_tbl
276 );
277 l_mesg_token_tbl := l_out_mesg_token_tbl;
278
279 x_mesg_token_tbl := l_out_mesg_token_tbl;
280
281 x_return_status := 'E';
282 return;
283 end if;
284
285 -- Check that the Child does not already have an existing relation
286 -- of the same type that is being created currently.
287 if l_parent_maint_obj_src = 1 then -- EAM
288 select count(*) into l_count
289 from eam_wo_relationships where
290 child_object_id = l_child_object_id
291 and child_object_type_id = l_child_object_type_id
292 and parent_relationship_type = l_relationship_type;
293 elsif l_parent_maint_obj_src = 2 then -- CMRO
294 select count(*) into l_count
295 from wip_sched_relationships where
296 child_object_id = l_child_object_id
297 and child_object_type_id = l_child_object_type_id
298 and relationship_type = l_relationship_type;
299 end if;
300
301 if l_count <> 0 then
302
303 l_token_tbl(1).token_name := 'Child Object Id';
304 l_token_tbl(1).token_value := l_child_object_id;
305 l_token_tbl(2).token_name := 'Rel Type';
306 l_token_tbl(2).token_value := l_relationship_type;
307
308 l_out_mesg_token_tbl := l_mesg_token_tbl;
309 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
310 ( p_message_name => 'EAM_WN_DUPLICATE_REL'
311 , p_token_tbl => l_token_tbl
312 , p_mesg_token_tbl => l_mesg_token_tbl
313 , x_mesg_token_tbl => l_out_mesg_token_tbl
314 );
315 l_mesg_token_tbl := l_out_mesg_token_tbl;
316
317 x_mesg_token_tbl := l_out_mesg_token_tbl;
318
319 x_return_status := 'E';
320 return;
321 end if;
322
323 --fix for 3572050.should not have cancelled workorders in any hierarchy
324 IF(l_child_status_type=7 or l_parent_status_type=7) then
325 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
326 ( p_message_name => 'EAM_WN_CANCEL_NOT_ALLOWED'
327 , p_token_tbl => l_token_tbl
328 , p_mesg_token_tbl => l_mesg_token_tbl
329 , x_mesg_token_tbl => l_out_mesg_token_tbl
330 );
331
332 x_mesg_token_tbl := l_out_mesg_token_tbl;
333
334 x_return_status := FND_API.G_RET_STS_ERROR;
335 return;
336 END IF;
337
338 --fix for 3433757.added validation so that relationships can't be created if either
339 --the parent or the child workorders are in following statuses
340 -- Closed,Pending-close,Failed-close
341 IF ((l_child_status_type in (12,14,15) or
342 l_parent_status_type in (12,14,15)) and
343 l_relationship_type =1) THEN
344
345
346 l_token_tbl(1).token_name := 'PARENT';
347 l_token_tbl(1).token_value := l_parent_object_id;
348 l_token_tbl(2).token_name := 'CHILD';
349 l_token_tbl(2).token_value := l_child_object_id;
350
351
352 l_out_mesg_token_tbl := l_mesg_token_tbl;
353 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
354 ( p_message_name => 'EAM_WN_CH_PAR_INVALID'
355 , p_token_tbl => l_token_tbl
356 , p_mesg_token_tbl => l_mesg_token_tbl
357 , x_mesg_token_tbl => l_out_mesg_token_tbl
358 );
359 l_mesg_token_tbl := l_out_mesg_token_tbl;
360
361 x_mesg_token_tbl := l_out_mesg_token_tbl;
362
363 x_return_status := FND_API.G_RET_STS_ERROR;
364 return;
365 END IF;
366
367
368
369 -- Get some common variables
370 -- 1. top_level_object_id
371 select count(*) into l_count
372 from wip_sched_relationships
373 where child_object_id = l_parent_object_id
374 and child_object_type_id = l_parent_object_type_id
375 and relationship_type = 1;
376 IF l_count = 0 THEN -- Adding directly to topmost node
377 l_top_level_object_id := l_parent_object_id;
378 l_top_level_object_type_id := l_parent_object_type_id;
379 ELSE
380 select distinct top_level_object_id, top_level_object_type_id
381 into l_top_level_object_id , l_top_level_object_type_id
382 from wip_sched_relationships
383 where child_object_id = l_parent_object_id
384 and child_object_type_id = l_parent_object_type_id;
385 END IF;
386
387
388 -- Check if new relationship is type 1 or 3.
389 if l_relationship_type = 3 then
390
391 if l_parent_maint_obj_src <> 1 then -- type 3 can only be for EAM
392
393 l_token_tbl(1).token_name := 'Parent Object Id';
394 l_token_tbl(1).token_value := l_parent_object_id;
395
396 l_out_mesg_token_tbl := l_mesg_token_tbl;
397 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
398 ( p_message_name => 'EAM_WN_PARENT_NON_EAM'
399 , p_token_tbl => l_token_tbl
400 , p_mesg_token_tbl => l_mesg_token_tbl
401 , x_mesg_token_tbl => l_out_mesg_token_tbl
402 );
403 l_mesg_token_tbl := l_out_mesg_token_tbl;
404
405 x_mesg_token_tbl := l_out_mesg_token_tbl;
406
407 x_return_status := 'E';
408 return;
409 end if;
410
411 -- insert the type 3 relationship first
412 select eam_wo_relationships_s.nextval
413 into l_wo_relationship_id from dual;
414 insert into eam_wo_relationships
415 ( wo_relationship_id,
416 parent_object_id,
417 parent_object_type_id,
418 child_object_id,
419 child_object_type_id,
420 parent_relationship_type,
421 relationship_status,
422 created_by,
423 creation_date,
424 last_updated_by,
425 last_update_date,
426 top_level_object_id,
427 top_level_object_type_id
428 ) values
429 ( l_wo_relationship_id,
430 l_parent_object_id,
431 l_parent_object_type_id,
432 l_child_object_id,
433 l_child_object_type_id,
434 3,
435 l_relationship_status,
436 l_created_by,
437 sysdate,
438 l_last_updated_by,
439 sysdate,
440 null,--l_top_level_object_id,
441 null--l_top_level_object_type_id
442 );
443
444
445 elsif l_relationship_type = 1 then
446
447 wip_sched_relation_grp.insertRow(
448 p_parentObjectID => l_parent_object_id,
449 p_parentObjectTypeID => l_parent_object_type_id,
450 p_childObjectID => l_child_object_id,
451 p_childObjectTypeID => l_child_object_type_id,
452 p_relationshipType => l_relationship_type,
453 p_relationshipStatus => l_relationship_status,
454 x_return_status => l_return_status,
455 x_msg_count => l_msg_count,
456 x_msg_data => l_msg_data,
457 p_api_version => 1.0,
458 p_init_msg_list => FND_API.G_FALSE,
459 p_commit => FND_API.G_FALSE);
460
461 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
462
463 l_token_tbl(1).token_name := 'Parent Object Id';
464 l_token_tbl(1).token_value := l_parent_object_id;
465
466 l_out_mesg_token_tbl := l_mesg_token_tbl;
467 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
468 ( p_message_name => 'EAM_WN_ADD_INS_FAIL'
469 , p_token_tbl => l_token_tbl
470 , p_mesg_token_tbl => l_mesg_token_tbl
471 , x_mesg_token_tbl => l_out_mesg_token_tbl
472 );
473 l_mesg_token_tbl := l_out_mesg_token_tbl;
474
475 x_mesg_token_tbl := l_out_mesg_token_tbl;
476
477 x_return_status := 'E';
478 return;
479
480 end if;
481
482
483 select eam_wo_relationships_s.nextval
484 into l_wo_relationship_id from dual;
485 insert into eam_wo_relationships
486 ( wo_relationship_id,
487 parent_object_id,
488 parent_object_type_id,
489 child_object_id,
490 child_object_type_id,
491 parent_relationship_type,
492 relationship_status,
493 created_by,
494 creation_date,
495 last_updated_by,
496 last_update_date,
497 top_level_object_id,
498 top_level_object_type_id
499 ) values
500 ( l_wo_relationship_id,
501 l_parent_object_id,
502 l_parent_object_type_id,
503 l_child_object_id,
504 l_child_object_type_id,
505 l_relationship_type,
506 l_relationship_status,
507 l_created_by,
508 sysdate,
509 l_last_updated_by,
510 sysdate,
511 l_top_level_object_id,
512 l_top_level_object_type_id
513 );
514
515
516
517 -- Stamp the hierarchy underneath with the new value
518 -- of top level object id
519 IF NOT l_constraining_children%ISOPEN THEN
520 OPEN l_constraining_children FOR
521
522 select * from wip_sched_relationships wsr
523 WHERE wsr.relationship_type in (1,2)
524 START WITH wsr.parent_object_id = l_parent_object_id
525 CONNECT BY wsr.parent_object_id = PRIOR wsr.child_object_id;
526
527 END IF;
528 --for bug 9053183
529 select sched_relationship_id
530 into l_sched_relationship_id
531 from wip_sched_relationships
532 where parent_object_id= l_parent_object_id
533 and parent_object_type_id = l_parent_object_type_id
534 and child_object_id=l_child_object_id
535 and child_object_type_id = l_child_object_type_id
536 and relationship_type=l_relationship_type;
537
538
539 update wip_sched_relationships set
540 top_level_object_id = l_top_level_object_id,
541 top_level_object_type_id = l_top_level_object_type_id
542 where sched_relationship_id = l_sched_relationship_id;
543
544
545 select maintenance_object_source into l_maint_obj_src
546 from wip_discrete_jobs where wip_entity_id = l_parent_object_id;
547
548 -- test_mesg('after update wip_sched_relationships sched rel id '||l_sched_relationship_id||' l_parent_object_id ' ||l_parent_object_id ||' l_child_object_id '||l_child_object_id );
549 if l_maint_obj_src = 1 then -- EAM
550
551 LOOP FETCH l_constraining_children into
552 l_relationship_record;
553
554 if l_relationship_record.parent_object_id is not null then
555 l_relationship_record.top_level_object_id := l_top_level_object_id;
556 l_relationship_record.top_level_object_type_id := l_top_level_object_type_id;
557
558 /* update wip_sched_relationships set
559 top_level_object_id = l_top_level_object_id,
560 top_level_object_type_id = l_top_level_object_type_id
561 where sched_relationship_id = l_relationship_record.sched_relationship_id;
562
563 select maintenance_object_source into l_maint_obj_src
564 from wip_discrete_jobs where wip_entity_id = l_relationship_record.parent_object_id;*/
565
566 update eam_wo_relationships set
567 top_level_object_id = l_top_level_object_id,
568 top_level_object_type_id = l_top_level_object_type_id
569 where
570 parent_object_id = l_relationship_record.parent_object_id
571 and parent_object_type_id = l_relationship_record.parent_object_type_id
572 and child_object_id = l_relationship_record.child_object_id
573 and child_object_type_id = l_relationship_record.child_object_type_id
574 and parent_relationship_type = l_relationship_record.relationship_type;
575
576 end if;
577
578 EXIT WHEN l_constraining_children%NOTFOUND;
579
580 END LOOP;
581 end if;-- End for bug 9053183
582
583 CLOSE l_constraining_children;
584
585 elsif l_relationship_type = 4 then -- Follow up
586
587
588 select eam_wo_relationships_s.nextval
589 into l_wo_relationship_id from dual;
590 insert into eam_wo_relationships
591 ( wo_relationship_id,
592 parent_object_id,
593 parent_object_type_id,
594 child_object_id,
595 child_object_type_id,
596 parent_relationship_type,
597 relationship_status,
598 created_by,
599 creation_date,
600 last_updated_by,
601 last_update_date,
602 top_level_object_id,
603 top_level_object_type_id
604 ) values
605 ( l_wo_relationship_id,
606 l_parent_object_id,
607 l_parent_object_type_id,
608 l_child_object_id,
609 l_child_object_type_id,
610 l_relationship_type,
611 l_relationship_status,
612 l_created_by,
613 sysdate,
614 l_last_updated_by,
615 sysdate,
616 null,
617 null
618 );
619
620
621
622 end if;
623
624
625 -- See whether we need to expand parents further up the chain.
626 if p_relationship_type = 1 then -- only for constrained children
627
628 -- find the list of subsequent constraining parents
629 -- in the upward direction
630 IF NOT l_constraining_parents%ISOPEN THEN
631 OPEN l_constraining_parents FOR
632
633 select * from wip_sched_relationships wsr
634 WHERE wsr.relationship_type = 1
635 START WITH wsr.child_object_id = l_child_object_id
636 CONNECT BY PRIOR wsr.parent_object_id = wsr.child_object_id;
637
638 END IF;
639
640 -- Adjust durations of all subsequent parent work orders
641 -- to be the maximum of it's children. Stop at first firm parent.
642 LOOP FETCH l_constraining_parents into
643 l_relationship_record;
644
645 if l_relationship_record.parent_object_id is not null then
646
647 select firm_planned_flag into l_parent_firm_flag from
648 wip_discrete_jobs where wip_entity_id = l_relationship_record.parent_object_id;
649
650 EXIT WHEN l_parent_firm_flag = 1;
651
652 Adjust_Parent(
653 p_parent_object_id => l_relationship_record.parent_object_id,
654 p_parent_object_type_id => l_relationship_record.parent_object_type_id);
655
656 end if;
657
658 EXIT WHEN l_constraining_parents%NOTFOUND;
659
660 END LOOP;
661
662 CLOSE l_constraining_parents;
663
664 end if;
665
666 if l_parent_maint_obj_src <> 2 then -- CMRO for bug 7943516
667
668 EAM_WO_NETWORK_VALIDATE_PVT.Validate_Structure
669 (
670 p_api_version => 1.0,
671 p_init_msg_list => FND_API.G_FALSE,
672 p_commit => FND_API.G_FALSE,
673 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
674
675 p_work_object_id => l_child_object_id,
676 p_work_object_type_id => l_child_object_type_id,
677 p_exception_logging => 'Y',
678
679 p_validate_status => 'N',
680 p_output_errors => 'N',
681
682 x_return_status => l_return_status,
683 x_msg_count => l_msg_count,
684 x_msg_data => l_msg_data,
685 x_wo_relationship_exc_tbl => l_wo_relationship_exc_tbl
686 );
687
688 --dbms_output.put_line('After VALIDATE_STRUCTURE:ret stat ='||l_return_status);
689
690
691
692 IF l_return_status = FND_API.G_RET_STS_ERROR OR
693 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
694
695 l_token_tbl(1).token_name := 'Parent_WorkOrder';
696 l_token_tbl(1).token_value := l_parent_workorder;
697 l_token_tbl(2).token_name := 'Child_WorkOrder';
698 l_token_tbl(2).token_value := l_child_workorder;
699
700 l_out_mesg_token_tbl := l_mesg_token_tbl;
701 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
702 ( p_message_name => 'EAM_WN_ADD_VALIDATE_STRUCT'
703 , p_token_tbl => l_token_tbl
704 , p_mesg_token_tbl => l_mesg_token_tbl
705 , x_mesg_token_tbl => l_out_mesg_token_tbl
706 );
707 l_mesg_token_tbl := l_out_mesg_token_tbl;
708
709 x_mesg_token_tbl := l_out_mesg_token_tbl;
710
711 ROLLBACK TO EAM_WN_ADD_WO;
712 END IF;
713
714
715 x_return_status := l_return_status;
716
717 end if; --bug 7943516
718
719 -- End of API body.
720 -- Standard check of p_commit.
721 IF FND_API.To_Boolean( p_commit ) THEN
722 --dbms_output.put_line('committing');
723 COMMIT WORK;
724 END IF;
725
726
727 EXCEPTION
728
729 WHEN OTHERS THEN
730
731 rollback to EAM_WN_ADD_WO;
732
733 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
734
735 l_token_tbl(1).token_name := 'Parent_WorkOrder';
736 l_token_tbl(1).token_value := l_parent_workorder;
737 l_token_tbl(2).token_name := 'Child_WorkOrder';
738 l_token_tbl(2).token_value := l_child_workorder;
739
740 l_out_mesg_token_tbl := l_mesg_token_tbl;
741 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
742 ( p_message_name => 'EAM_WN_ADD_UNKNOWN_ERR'
743 , p_token_tbl => l_token_tbl
744 , p_mesg_token_tbl => l_mesg_token_tbl
745 , x_mesg_token_tbl => l_out_mesg_token_tbl
746 );
747 l_mesg_token_tbl := l_out_mesg_token_tbl;
748
749 x_mesg_token_tbl := l_out_mesg_token_tbl;
750
751 return;
752
753 END Add_WO_To_Network;
754
755
756
757
758
759 PROCEDURE Adjust_Parent
760 (
761 p_parent_object_id IN NUMBER,
762 p_parent_object_type_id IN NUMBER
763 ) IS
764
765 l_parent_object_id NUMBER := p_parent_object_id;
766 l_parent_object_type_id NUMBER := p_parent_object_type_id;
767
768 l_min_date DATE := null;
769 l_max_date DATE := null;
770 l_wo_start_date DATE := null;
771 l_wo_end_date DATE := null;
772 l_status_type NUMBER;
773 l_date_completed DATE;
774
775 TYPE l_relationship_records IS REF CURSOR RETURN WIP_SCHED_RELATIONSHIPS%ROWTYPE;
776 l_constrained_children l_relationship_records;
777 l_relationship_record WIP_SCHED_RELATIONSHIPS%ROWTYPE;
778
779 BEGIN
780
781 -- Find the min start date and max end date of all
782 -- constrained children for this parent
783
784 -- find the list of constrained children
785 IF NOT l_constrained_children%ISOPEN THEN
786 OPEN l_constrained_children FOR
787 select * from
788 wip_sched_relationships
789 where relationship_type = 1
790 and parent_object_id = l_parent_object_id
791 and parent_object_type_id = l_parent_object_type_id;
792 END IF;
793
794 LOOP FETCH l_constrained_children into
795 l_relationship_record;
796
797 if l_relationship_record.child_object_id is not null then
798
799 select scheduled_start_date, scheduled_completion_date, status_type, date_completed
800 into l_wo_start_date, l_wo_end_date,l_status_type,l_date_completed
801 from wip_discrete_jobs
802 where wip_entity_id = l_relationship_record.child_object_id;
803
804 --do not consider child workorders which are cancelled or [closed and date_completed is null](closed from cancelled status)
805 IF NOT(
806 l_status_type = 7
807 OR ((l_status_type IN (12,14,15)) AND (l_date_completed IS NULL))
808 ) THEN
809 IF l_min_date is null OR
810 l_min_date > l_wo_start_date THEN
811 l_min_date := l_wo_start_date;
812 END IF;
813
814 IF l_max_date is null OR
815 l_max_date < l_wo_end_date THEN
816 l_max_date := l_wo_end_date;
817 END IF;
818 END IF;
819 end if;
820
821 EXIT WHEN l_constrained_children%NOTFOUND;
822 END LOOP;
823
824 CLOSE l_constrained_children;
825
826 select scheduled_start_date, scheduled_completion_date
827 into l_wo_start_date, l_wo_end_date from wip_discrete_jobs
828 where wip_entity_id = l_relationship_record.parent_object_id;
829
830 if l_wo_start_date > nvl(l_min_date, l_wo_start_date + 1) then
831 l_wo_start_date := l_min_date;
832 end if;
833 if l_wo_end_date < nvl(l_max_date, l_wo_end_date - 1) then
834 l_wo_end_date := l_max_date;
835 end if;
836
837 UPDATE WIP_DISCRETE_JOBS set
838 scheduled_start_date = l_wo_start_date,
839 scheduled_completion_date = l_wo_end_date
840 where wip_entity_id = l_parent_object_id;
841
842 END Adjust_Parent;
843
844
845
846
847 /*Bug3521886: Pass requested start date and due date*/
848 PROCEDURE Resize_WO
849 (
850 p_api_version IN NUMBER,
851 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
852 p_commit IN VARCHAR2 := FND_API.G_FALSE,
853 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
854 p_object_id IN NUMBER,
855 p_object_type_id IN NUMBER,
856 p_start_date IN DATE,
857 p_completion_date IN DATE,
858 p_required_start_date IN DATE := NULL,
859 p_required_due_date IN DATE := NULL,
860 p_org_id IN VARCHAR2,
861 p_firm IN NUMBER,
862 x_return_status OUT NOCOPY VARCHAR2,
863 x_msg_count OUT NOCOPY NUMBER,
864 x_msg_data OUT NOCOPY VARCHAR2
865 ) IS
866
867 l_count NUMBER;
868 l_first_constraining_parent NUMBER;
869
870 l_object_id NUMBER := p_object_id;
871 l_object_type_id NUMBER := p_object_type_id;
872 l_start_date DATE := p_start_date;
873 l_completion_date DATE := p_completion_date;
874
875 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
876 l_msg_count NUMBER;
877 l_msg_data VARCHAR2(1000);
878
879 l_date_chk_return_status VARCHAR2(1);
880 l_output_dir VARCHAR2(512);
881
882 l_eam_wo_rec eam_process_wo_pub.eam_wo_rec_type;
883
884 l_eam_wo_relations_tbl EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type;
885 l_eam_wo_tbl EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
886 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
887 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
888 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
889 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
890 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
891 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
892 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
893 l_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
894 l_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
895 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
896 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
897 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
898 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
899 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
900 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
901
902 l_out_eam_wo_relations_tbl EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type;
903 l_out_eam_wo_tbl EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
904 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
905 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
906 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
907 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
908 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
909 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
910 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
911 l_out_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
912 l_out_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
913 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
914 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
915 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
916 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
917 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
918 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
919 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
920 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
921
922
923 BEGIN
924
925 x_return_status := l_return_status;
926
927 -- Standard Start of API savepoint
928 SAVEPOINT EAM_WN_RESIZE;
929
930 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
931
932 -- create the record for the workorder which needs to be shifted.
933
934 l_eam_wo_rec.batch_id := 1;
935 l_eam_wo_rec.header_id := p_object_id;
936 l_eam_wo_rec.wip_entity_id := p_object_id;
937 l_eam_wo_rec.organization_id := p_org_id;
938 l_eam_wo_rec.scheduled_start_date := p_start_date;
939 l_eam_wo_rec.scheduled_completion_date := p_completion_date;
940 l_eam_wo_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
941 l_eam_wo_rec.FIRM_PLANNED_FLAG := p_firm;
942 /*Bug3521886: Pass requested start date and due date*/
943 l_eam_wo_rec.REQUESTED_START_DATE := p_required_start_date;
944 l_eam_wo_rec.DUE_DATE := p_required_due_date;
945
946 -- insert into the table
947 l_eam_wo_tbl(1) := l_eam_wo_rec;
948 EAM_PROCESS_WO_PUB.Process_Master_Child_WO(
949 p_bo_identifier => 'EAM'
950 , p_init_msg_list => TRUE
951 , p_api_version_number => 1.0
952 , p_eam_wo_tbl => l_eam_wo_tbl
953 , p_eam_wo_relations_tbl => l_eam_wo_relations_tbl
954 , p_eam_op_tbl => l_eam_op_tbl
955 , p_eam_op_network_tbl => l_eam_op_network_tbl
956 , p_eam_res_tbl => l_eam_res_tbl
957 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
958 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
959 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
960 , p_eam_direct_items_tbl => l_eam_direct_items_tbl
961 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
962 , p_eam_wo_comp_tbl => l_eam_wo_comp_tbl
963 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
964 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
965 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
966 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
967 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
968 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
969 , p_eam_request_tbl => l_eam_request_tbl
970 , x_eam_wo_tbl => l_out_eam_wo_tbl
971 , x_eam_wo_relations_tbl => l_out_eam_wo_relations_tbl
972 , x_eam_op_tbl => l_out_eam_op_tbl
973 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
974 , x_eam_res_tbl => l_out_eam_res_tbl
975 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
976 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
977 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
978 , x_eam_direct_items_tbl => l_out_eam_direct_items_tbl
979 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
980 , x_eam_wo_comp_tbl => l_out_eam_wo_comp_tbl
981 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
982 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
983 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
984 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
985 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
986 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
987 , x_eam_request_tbl => l_out_eam_request_tbl
988 , x_return_status => l_return_status
989 , x_msg_count => l_msg_count
990 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
991 , p_debug_filename => 'resizewo.log'
992 , p_output_dir => l_output_dir
993 , p_commit => p_commit
994 , p_debug_file_mode => 'A'
995 );
996
997 /* if the status returned is sucess then commit the work in case caller wants it to be committed. Else * raise exception
998 */
999 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1000 IF FND_API.TO_BOOLEAN(p_commit)THEN
1001 COMMIT WORK;
1002 END IF;
1003 ELSE
1004 RAISE FND_API.G_EXC_ERROR;
1005 END IF;
1006
1007 EXCEPTION
1008 when others then
1009 x_return_status := FND_API.G_RET_STS_ERROR;
1010 return;
1011
1012
1013 END Resize_WO;
1014
1015
1016
1017
1018
1019 PROCEDURE Delete_Dependency
1020 (
1021 p_api_version IN NUMBER,
1022 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1023 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1024 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1025
1026 p_prior_object_id IN NUMBER,
1027 p_prior_object_type_id IN NUMBER,
1028 p_next_object_id IN NUMBER,
1029 p_next_object_type_id IN NUMBER,
1030
1031 x_return_status OUT NOCOPY VARCHAR2,
1032 x_msg_count OUT NOCOPY NUMBER,
1033 x_msg_data OUT NOCOPY VARCHAR2 ,
1034 x_mesg_token_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
1035 ) IS
1036
1037 l_prior_object_id NUMBER := p_prior_object_id;
1038 l_prior_object_type_id NUMBER := p_prior_object_type_id;
1039 l_next_object_id NUMBER := p_next_object_id;
1040 l_next_object_type_id NUMBER := p_next_object_type_id;
1041
1042 l_count_prior NUMBER := 0;
1043 l_count_next NUMBER := 0;
1044 l_status_type NUMBER := 0;
1045
1046 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1047 l_msg_count NUMBER;
1048 l_msg_data VARCHAR2(1000);
1049 l_sched_relationship_id NUMBER;
1050
1051
1052 l_err_text VARCHAR2(2000) := NULL;
1053 l_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
1054 l_out_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
1055 l_Token_Tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
1056
1057 l_other_message VARCHAR2(20000);
1058 l_other_token_tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
1059
1060 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
1061 l_old_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
1062 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type ;
1063 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
1064 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type ;
1065 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type ;
1066 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type ;
1067 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type ;
1068 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type ;
1069
1070 l_prior_workorder VARCHAR2(240);
1071 l_next_workorder VARCHAR2(240);
1072
1073 BEGIN
1074
1075 savepoint EAM_WN_DEL_DEP;
1076
1077 x_return_status := l_return_status;
1078
1079 SELECT wip_entity_name into l_prior_workorder
1080 FROM wip_entities we
1081 WHERE we.wip_entity_id = l_prior_object_id;
1082
1083 SELECT wip_entity_name into l_next_workorder
1084 FROM wip_entities we
1085 WHERE we.wip_entity_id = l_next_object_id;
1086
1087
1088 -- Validate that the relationship is a leaf node
1089 select count(*) into l_count_prior from
1090 wip_sched_relationships where
1091 child_object_id = l_prior_object_id
1092 and child_object_type_id = l_prior_object_id
1093 and relationship_type = 2;
1094 select count(*) into l_count_next from
1095 wip_sched_relationships where
1096 parent_object_id = l_next_object_id
1097 and parent_object_type_id = l_next_object_id
1098 and relationship_type = 2;
1099 if l_count_prior <> 0 and l_count_next <> 0 then
1100
1101 l_token_tbl(1).token_name := 'Prior_WorkOrder';
1102 l_token_tbl(1).token_value := l_prior_workorder;
1103 l_token_tbl(2).token_name := 'Next_WorkOrder';
1104 l_token_tbl(2).token_value := l_next_workorder;
1105
1106 l_out_mesg_token_tbl := l_mesg_token_tbl;
1107 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
1108 ( p_message_name => 'EAM_WN_NOT_LEAF_NODE'
1109 , p_token_tbl => l_token_tbl
1110 , p_mesg_token_tbl => l_mesg_token_tbl
1111 , x_mesg_token_tbl => l_out_mesg_token_tbl
1112 );
1113 l_mesg_token_tbl := l_out_mesg_token_tbl;
1114
1115 x_mesg_token_tbl := l_out_mesg_token_tbl;
1116
1117 x_return_status := 'E';
1118 return;
1119 end if;
1120
1121 -- Check that the prior work order is not completed
1122 select status_type into l_status_type
1123 from wip_discrete_jobs where
1124 wip_entity_id = l_prior_object_id;
1125 if l_status_type in (4,5,12,14,15) then
1126
1127 l_token_tbl(1).token_name := 'Prior_WorkOrder';
1128 l_token_tbl(1).token_value := l_prior_workorder;
1129
1130 l_out_mesg_token_tbl := l_mesg_token_tbl;
1131 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
1132 ( p_message_name => 'EAM_WN_PRIOR_COMPLETED'
1133 , p_token_tbl => l_token_tbl
1134 , p_mesg_token_tbl => l_mesg_token_tbl
1135 , x_mesg_token_tbl => l_out_mesg_token_tbl
1136 );
1137 l_mesg_token_tbl := l_out_mesg_token_tbl;
1138
1139 x_mesg_token_tbl := l_out_mesg_token_tbl;
1140
1141 x_return_status := 'E';
1142 return;
1143 end if;
1144
1145 -- Simple delete of type 2 relationship.
1146 select sched_relationship_id into l_sched_relationship_id
1147 from WIP_SCHED_RELATIONSHIPS
1148 where parent_object_id = l_prior_object_id
1149 and parent_object_type_id = l_prior_object_type_id
1150 and child_object_id = l_next_object_id
1151 and child_object_type_id = l_next_object_type_id
1152 and relationship_type = 2;
1153
1154 wip_sched_relation_grp.deleteRow(
1155 p_relationshipID => l_sched_relationship_id,
1156 x_return_status => l_return_status,
1157 x_msg_count => l_msg_count,
1158 x_msg_data => l_msg_data,
1159 p_api_version => 1.0,
1160 p_init_msg_list => FND_API.G_FALSE,
1161 p_commit => FND_API.G_FALSE);
1162
1163 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1164
1165 l_token_tbl(1).token_name := 'Parent Object Id';
1166 l_token_tbl(1).token_value := l_prior_object_id;
1167
1168 l_out_mesg_token_tbl := l_mesg_token_tbl;
1169 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
1170 ( p_message_name => 'EAM_WN_DEL_DEP_API_FAIL'
1171 , p_token_tbl => l_token_tbl
1172 , p_mesg_token_tbl => l_mesg_token_tbl
1173 , x_mesg_token_tbl => l_out_mesg_token_tbl
1174 );
1175 l_mesg_token_tbl := l_out_mesg_token_tbl;
1176
1177 x_mesg_token_tbl := l_out_mesg_token_tbl;
1178
1179 x_return_status := 'E';
1180 return;
1181
1182 end if;
1183
1184 DELETE from EAM_WO_RELATIONSHIPS
1185 where parent_object_id = l_prior_object_id
1186 and parent_object_type_id = l_prior_object_type_id
1187 and child_object_id = l_next_object_id
1188 and child_object_type_id = l_next_object_type_id
1189 and parent_relationship_type = 2;
1190
1191 x_return_status := FND_API.G_RET_STS_SUCCESS;
1192
1193 EXCEPTION
1194 when others then
1195
1196 rollback to EAM_WN_DEL_DEP;
1197
1198 l_token_tbl(1).token_name := 'Prior_WorkOrder';
1199 l_token_tbl(1).token_value := l_prior_workorder;
1200 l_token_tbl(2).token_name := 'Next_WorkOrder';
1201 l_token_tbl(2).token_value := l_next_workorder;
1202
1203 l_out_mesg_token_tbl := l_mesg_token_tbl;
1204 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
1205 ( p_message_name => 'EAM_WN_DEL_DEP_UNKWN_ERR'
1206 , p_token_tbl => l_token_tbl
1207 , p_mesg_token_tbl => l_mesg_token_tbl
1208 , x_mesg_token_tbl => l_out_mesg_token_tbl
1209 );
1210 l_mesg_token_tbl := l_out_mesg_token_tbl;
1211
1212 x_mesg_token_tbl := l_out_mesg_token_tbl;
1213
1214 x_return_status := FND_API.G_RET_STS_ERROR;
1215 return;
1216
1217 END Delete_Dependency;
1218
1219
1220
1221
1222 PROCEDURE Add_Dependency
1223 (
1224 p_api_version IN NUMBER,
1225 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1226 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1227 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1228
1229 p_prior_object_id IN NUMBER,
1230 p_prior_object_type_id IN NUMBER,
1231 p_next_object_id IN NUMBER,
1232 p_next_object_type_id IN NUMBER,
1233
1234 x_return_status OUT NOCOPY VARCHAR2,
1235 x_msg_count OUT NOCOPY NUMBER,
1236 x_msg_data OUT NOCOPY VARCHAR2 ,
1237 x_mesg_token_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
1238 ) IS
1239
1240 l_prior_object_id NUMBER := p_prior_object_id;
1241 l_prior_object_type_id NUMBER := p_prior_object_type_id;
1242 l_next_object_id NUMBER := p_next_object_id;
1243 l_next_object_type_id NUMBER := p_next_object_type_id;
1244 l_prior_status_type NUMBER := 0;
1245 l_next_status_type NUMBER := 0;
1246 l_prior_start_date DATE := SYSDATE;
1247 l_prior_completion_date DATE := SYSDATE;
1248 l_next_start_date DATE := SYSDATE;
1249 l_next_completion_date DATE := SYSDATE;
1250
1251 l_sched_relationship_id NUMBER;
1252 l_wo_relationship_id NUMBER;
1253 l_top_level_object_id NUMBER;
1254 l_top_level_object_type_id_1 NUMBER;
1255 l_top_level_object_id_1 NUMBER;
1256 l_top_level_object_type_id NUMBER;
1257 l_relationship_status NUMBER := 0; -- pending validation
1258
1259 l_count NUMBER := 0;
1260 l_status_type NUMBER := 0;
1261 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1262 l_msg_count NUMBER;
1263 l_msg_data VARCHAR2(1000);
1264 l_err_text VARCHAR2(2000) := NULL;
1265 l_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
1266 l_out_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
1267 l_Token_Tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
1268 l_other_message VARCHAR2(20000);
1269 l_other_token_tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
1270
1271 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
1272 l_old_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
1273 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type ;
1274 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
1275 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type ;
1276 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type ;
1277 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type ;
1278 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type ;
1279 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type ;
1280
1281 l_prior_workorder VARCHAR2(240);
1282 l_next_workorder VARCHAR2(240);
1283 l_wo_relationship_exc_tbl EAM_PROCESS_WO_PUB.wo_relationship_exc_tbl_type;
1284
1285 BEGIN
1286
1287
1288 x_return_status := l_return_status;
1289
1290 SAVEPOINT EAM_WN_ADD_DEP;
1291
1292 SELECT wip_entity_name into l_prior_workorder
1293 FROM wip_entities we
1294 WHERE we.wip_entity_id = l_prior_object_id;
1295
1296 SELECT wip_entity_name into l_next_workorder
1297 FROM wip_entities we
1298 WHERE we.wip_entity_id = l_next_object_id;
1299
1300
1301 -- Check that both work orders are part of
1302 -- some sched hierarchies
1303 select count(*) into l_count from
1304 wip_sched_relationships where
1305 child_object_id = l_prior_object_id
1306 and child_object_type_id = l_prior_object_type_id
1307 and relationship_type = 1;
1308 if l_count = 0 then
1309
1310 l_token_tbl(1).token_name := 'Prior_WorkOrder';
1311 l_token_tbl(1).token_value := l_prior_workorder;
1312
1313 l_out_mesg_token_tbl := l_mesg_token_tbl;
1314 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
1315 ( p_message_name => 'EAM_WN_PRIOR_NOT_IN_HIER'
1316 , p_token_tbl => l_token_tbl
1317 , p_mesg_token_tbl => l_mesg_token_tbl
1318 , x_mesg_token_tbl => l_out_mesg_token_tbl
1319 );
1320 l_mesg_token_tbl := l_out_mesg_token_tbl;
1321
1322 x_mesg_token_tbl := l_out_mesg_token_tbl;
1323
1324 x_return_status := 'E';
1325 return;
1326 end if;
1327
1328 select count(*) into l_count from
1329 wip_sched_relationships where
1330 child_object_id = l_next_object_id
1331 and child_object_type_id = l_next_object_type_id
1332 and relationship_type = 1;
1333 if l_count = 0 then
1334
1335 l_token_tbl(1).token_name := 'Next_WorkOrder';
1336 l_token_tbl(1).token_value := l_next_workorder;
1337
1338 l_out_mesg_token_tbl := l_mesg_token_tbl;
1339 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
1340 ( p_message_name => 'EAM_WN_NEXT_NOT_IN_HIER'
1341 , p_token_tbl => l_token_tbl
1342 , p_mesg_token_tbl => l_mesg_token_tbl
1343 , x_mesg_token_tbl => l_out_mesg_token_tbl
1344 );
1345 l_mesg_token_tbl := l_out_mesg_token_tbl;
1346
1347 x_mesg_token_tbl := l_out_mesg_token_tbl;
1348
1349 x_return_status := 'E';
1350 return;
1351 end if;
1352
1353 -- Check that the prior work order is not completed
1354 select status_type, scheduled_start_date,
1355 scheduled_completion_date
1356 into l_status_type, l_prior_start_date,
1357 l_prior_completion_date
1358 from wip_discrete_jobs where
1359 wip_entity_id = l_prior_object_id;
1360 l_prior_status_type := l_status_type;
1361 if l_status_type in (4,5,12,14,15) then
1362
1363 l_token_tbl(1).token_name := 'Prior_WorkOrder';
1364 l_token_tbl(1).token_value := l_prior_workorder;
1365
1366 l_out_mesg_token_tbl := l_mesg_token_tbl;
1367 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
1368 ( p_message_name => 'EAM_WN_PRIOR_COMPL'
1369 , p_token_tbl => l_token_tbl
1370 , p_mesg_token_tbl => l_mesg_token_tbl
1371 , x_mesg_token_tbl => l_out_mesg_token_tbl
1372 );
1373 l_mesg_token_tbl := l_out_mesg_token_tbl;
1374
1375 x_mesg_token_tbl := l_out_mesg_token_tbl;
1376
1377 x_return_status := 'E';
1378 return;
1379 end if;
1380
1381
1382 select status_type, scheduled_start_date,
1383 scheduled_completion_date
1384 into l_status_type, l_next_start_date,
1385 l_next_completion_date
1386 from wip_discrete_jobs where
1387 wip_entity_id = l_next_object_id;
1388 l_next_status_type := l_status_type;
1389 if l_status_type in (4,5,12,14,15) then
1390
1391 l_token_tbl(1).token_name := 'Next_WorkOrder';
1392 l_token_tbl(1).token_value := l_next_workorder;
1393
1394 l_out_mesg_token_tbl := l_mesg_token_tbl;
1395 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
1396 ( p_message_name => 'EAM_WN_NEXT_COMPL'
1397 , p_token_tbl => l_token_tbl
1398 , p_mesg_token_tbl => l_mesg_token_tbl
1399 , x_mesg_token_tbl => l_out_mesg_token_tbl
1400 );
1401 l_mesg_token_tbl := l_out_mesg_token_tbl;
1402
1403 x_mesg_token_tbl := l_out_mesg_token_tbl;
1404
1405 x_return_status := 'E';
1406 return;
1407 end if;
1408
1409
1410 -- Validate that we are not building a relationship
1411 -- between a released WO and a cancelled WO.
1412
1413 if (l_prior_status_type = 3 and l_next_status_type = 7) OR
1414 (l_prior_status_type = 7 and l_next_status_type = 3) then
1415
1416 l_token_tbl.delete;
1417
1418 l_out_mesg_token_tbl := l_mesg_token_tbl;
1419 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
1420 ( p_message_name => 'EAM_WN_DEP_REL_CANCEL'
1421 , p_token_tbl => l_token_tbl
1422 , p_mesg_token_tbl => l_mesg_token_tbl
1423 , x_mesg_token_tbl => l_out_mesg_token_tbl
1424 );
1425 l_mesg_token_tbl := l_out_mesg_token_tbl;
1426
1427 x_mesg_token_tbl := l_out_mesg_token_tbl;
1428
1429 x_return_status := 'E';
1430 return;
1431 end if;
1432
1433
1434
1435 -- Validate that the prior WO end date is before the
1436 -- next WO start date
1437
1438 if l_prior_completion_date > l_next_start_date
1439 and l_prior_status_type IN (3,4,5,6,7,12,14,15)
1440 and l_next_status_type IN (3,4,5,6,7,12,14,15) THEN
1441
1442 l_token_tbl(1).token_name := 'Prior Object Id';
1443 l_token_tbl(1).token_value := l_prior_object_id;
1444
1445 l_out_mesg_token_tbl := l_mesg_token_tbl;
1446 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
1447 ( p_message_name => 'EAM_WN_DEP_REL_DATE_ERR'
1448 , p_token_tbl => l_token_tbl
1449 , p_mesg_token_tbl => l_mesg_token_tbl
1450 , x_mesg_token_tbl => l_out_mesg_token_tbl
1451 );
1452 l_mesg_token_tbl := l_out_mesg_token_tbl;
1453
1454 x_mesg_token_tbl := l_out_mesg_token_tbl;
1455
1456 x_return_status := 'E';
1457 return;
1458 end if;
1459
1460
1461
1462
1463 -- Get some common variables
1464 -- 1. top_level_object_id
1465 select distinct top_level_object_id, top_level_object_type_id
1466 into l_top_level_object_id , l_top_level_object_type_id
1467 from wip_sched_relationships
1468 where child_object_id = l_prior_object_id
1469 and child_object_type_id = l_prior_object_type_id
1470 and relationship_type = 1;
1471
1472 select distinct top_level_object_id, top_level_object_type_id
1473 into l_top_level_object_id_1 , l_top_level_object_type_id_1
1474 from wip_sched_relationships
1475 where child_object_id = l_next_object_id
1476 and child_object_type_id = l_next_object_type_id
1477 and relationship_type = 1;
1478
1479 -- Validate that both objects have a common parent somewhere
1480 -- up the hierarchy. Just check the top_level_object_id
1481 IF l_top_level_object_id <> l_top_level_object_id_1 OR
1482 l_top_level_object_type_id <> l_top_level_object_type_id_1 THEN
1483
1484 l_token_tbl(1).token_name := 'Prior_WorkOrder';
1485 l_token_tbl(1).token_value := l_prior_workorder;
1486 l_token_tbl(2).token_name := 'Next_WorkOrder';
1487 l_token_tbl(2).token_value := l_next_workorder;
1488
1489 l_out_mesg_token_tbl := l_mesg_token_tbl;
1490 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
1491 ( p_message_name => 'EAM_WN_NOT_IN_SAME_HIER'
1492 , p_token_tbl => l_token_tbl
1493 , p_mesg_token_tbl => l_mesg_token_tbl
1494 , x_mesg_token_tbl => l_out_mesg_token_tbl
1495 );
1496 l_mesg_token_tbl := l_out_mesg_token_tbl;
1497
1498 x_mesg_token_tbl := l_out_mesg_token_tbl;
1499
1500 x_return_status := FND_API.G_RET_STS_ERROR;
1501 return;
1502 END IF;
1503
1504 wip_sched_relation_grp.insertRow(
1505 p_parentObjectID => l_prior_object_id,
1506 p_parentObjectTypeID => l_prior_object_type_id,
1507 p_childObjectID => l_next_object_id,
1508 p_childObjectTypeID => l_next_object_type_id,
1509 p_relationshipType => 2,
1510 p_relationshipStatus => l_relationship_status,
1511 x_return_status => l_return_status,
1512 x_msg_count => l_msg_count,
1513 x_msg_data => l_msg_data,
1514 p_api_version => 1.0,
1515 p_init_msg_list => FND_API.G_FALSE,
1516 p_commit => FND_API.G_FALSE);
1517
1518 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1519
1520 l_token_tbl(1).token_name := 'Parent Object Id';
1521 l_token_tbl(1).token_value := l_prior_object_id;
1522
1523 l_out_mesg_token_tbl := l_mesg_token_tbl;
1524 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
1525 ( p_message_name => 'EAM_WN_ADD_DEP_INS_FAIL'
1526 , p_token_tbl => l_token_tbl
1527 , p_mesg_token_tbl => l_mesg_token_tbl
1528 , x_mesg_token_tbl => l_out_mesg_token_tbl
1529 );
1530 l_mesg_token_tbl := l_out_mesg_token_tbl;
1531
1532 x_mesg_token_tbl := l_out_mesg_token_tbl;
1533
1534 x_return_status := 'E';
1535 return;
1536
1537 end if;
1538
1539
1540 select eam_wo_relationships_s.nextval
1541 into l_wo_relationship_id from dual;
1542 insert into eam_wo_relationships
1543 ( wo_relationship_id,
1544 parent_object_id,
1545 parent_object_type_id,
1546 child_object_id,
1547 child_object_type_id,
1548 parent_relationship_type,
1549 relationship_status,
1550 created_by,
1551 creation_date,
1552 last_updated_by,
1553 last_update_date,
1554 top_level_object_id,
1555 top_level_object_type_id
1556 ) values
1557 ( l_wo_relationship_id,
1558 l_prior_object_id,
1559 l_prior_object_type_id,
1560 l_next_object_id,
1561 l_next_object_type_id,
1562 2,
1563 l_relationship_status,
1564 l_created_by,
1565 sysdate,
1566 l_last_updated_by,
1567 sysdate,
1568 l_top_level_object_id,
1569 l_top_level_object_type_id
1570 );
1571
1572
1573 EAM_WO_NETWORK_VALIDATE_PVT.Validate_Structure
1574 (
1575 p_api_version => 1.0,
1576 p_init_msg_list => FND_API.G_FALSE,
1577 p_commit => FND_API.G_FALSE,
1578 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1579
1580 p_work_object_id => l_prior_object_id,
1581 p_work_object_type_id => l_prior_object_type_id,
1582 p_exception_logging => 'Y',
1583
1584 p_validate_status => 'N',
1585 p_output_errors => 'N',
1586
1587 x_return_status => l_return_status,
1588 x_msg_count => l_msg_count,
1589 x_msg_data => l_msg_data,
1590 x_wo_relationship_exc_tbl => l_wo_relationship_exc_tbl
1591 );
1592
1593
1594 IF l_return_status = FND_API.G_RET_STS_ERROR OR
1595 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1596
1597 l_token_tbl(1).token_name := 'Prior_WorkOrder';
1598 l_token_tbl(1).token_value := l_prior_workorder;
1599 l_token_tbl(2).token_name := 'Next_WorkOrder';
1600 l_token_tbl(2).token_value := l_next_workorder;
1601
1602 l_out_mesg_token_tbl := l_mesg_token_tbl;
1603 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
1604 ( p_message_name => 'EAM_WN_AD_VALIDATE_STRUC_ERR'
1605 , p_token_tbl => l_token_tbl
1606 , p_mesg_token_tbl => l_mesg_token_tbl
1607 , x_mesg_token_tbl => l_out_mesg_token_tbl
1608 );
1609 l_mesg_token_tbl := l_out_mesg_token_tbl;
1610
1611 x_mesg_token_tbl := l_out_mesg_token_tbl;
1612
1613 ROLLBACK TO EAM_WN_ADD_DEP;
1614 END IF;
1615
1616 x_return_status := l_return_status;
1617
1618 EXCEPTION
1619 when others then
1620
1621 rollback to EAM_WN_ADD_DEP;
1622
1623 l_token_tbl(1).token_name := 'Prior_WorkOrder';
1624 l_token_tbl(1).token_value := l_prior_workorder;
1625 l_token_tbl(2).token_name := 'Next_WorkOrder';
1626 l_token_tbl(2).token_value := l_next_workorder;
1627
1628 l_out_mesg_token_tbl := l_mesg_token_tbl;
1629 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
1630 ( p_message_name => 'EAM_WN_AD_UNKNOWN_ERR'
1631 , p_token_tbl => l_token_tbl
1632 , p_mesg_token_tbl => l_mesg_token_tbl
1633 , x_mesg_token_tbl => l_out_mesg_token_tbl
1634 );
1635 l_mesg_token_tbl := l_out_mesg_token_tbl;
1636
1637 x_mesg_token_tbl := l_out_mesg_token_tbl;
1638
1639 x_return_status := FND_API.G_RET_STS_ERROR;
1640 return;
1641
1642 END Add_Dependency;
1643
1644
1645
1646
1647 PROCEDURE Delink_Child_From_Parent
1648 (
1649 p_api_version IN NUMBER,
1650 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1651 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1652 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1653
1654 p_child_object_id IN NUMBER,
1655 p_child_object_type_id IN NUMBER,
1656 p_parent_object_id IN NUMBER,
1657 p_parent_object_type_id IN NUMBER,
1658 p_relationship_type IN NUMBER,
1659
1660 x_return_status OUT NOCOPY VARCHAR2,
1661 x_msg_count OUT NOCOPY NUMBER,
1662 x_msg_data OUT NOCOPY VARCHAR2 ,
1663 x_mesg_token_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
1664 )
1665 IS
1666
1667 l_relationship_type NUMBER := p_relationship_type;
1668 l_count NUMBER;
1669
1670 l_child_object_id NUMBER := p_child_object_id;
1671 l_child_object_type_id NUMBER := p_child_object_type_id;
1672 l_parent_object_id NUMBER := p_parent_object_id;
1673 l_parent_object_type_id NUMBER := p_parent_object_type_id;
1674
1675 l_relationship_status NUMBER := 0;
1676 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1677
1678 l_sched_relationship_id NUMBER;
1679 l_msg_count NUMBER;
1680 l_msg_data VARCHAR2(1000);
1681
1682 l_err_text VARCHAR2(2000) := NULL;
1683 l_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
1684 l_out_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
1685 l_Token_Tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
1686
1687 l_other_message VARCHAR2(20000);
1688 l_other_token_tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
1689
1690 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
1691 l_old_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
1692 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type ;
1693 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
1694 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type ;
1695 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type ;
1696 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type ;
1697 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type ;
1698 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type ;
1699
1700 TYPE l_relationship_records IS REF CURSOR RETURN WIP_SCHED_RELATIONSHIPS%ROWTYPE;
1701 l_constraining_children l_relationship_records;
1702 l_relationship_record WIP_SCHED_RELATIONSHIPS%ROWTYPE;
1703
1704 l_maint_obj_src NUMBER;
1705
1706 l_parent_workorder VARCHAR2(240);
1707 l_child_workorder VARCHAR2(240);
1708
1709 l_constraining_parents l_relationship_records;
1710 l_parent_record WIP_SCHED_RELATIONSHIPS%ROWTYPE;
1711 l_parent_firm_flag NUMBER;
1712
1713 BEGIN
1714
1715 savepoint EAM_WN_DELINK_PAR_CH;
1716
1717 x_return_status := FND_API.G_RET_STS_SUCCESS;
1718
1719 SELECT wip_entity_name into l_parent_workorder
1720 FROM wip_entities we
1721 WHERE we.wip_entity_id = l_parent_object_id;
1722
1723 SELECT wip_entity_name into l_child_workorder
1724 FROM wip_entities we
1725 WHERE we.wip_entity_id = l_child_object_id;
1726
1727
1728 -- See if there are dependency relationships for the child
1729 -- if it is a scheduling relationship that is being deleted.
1730 l_count := 0;
1731 if l_relationship_type = 1 then
1732 select count(*) into l_count from
1733 wip_sched_relationships where
1734 ((child_object_id = l_child_object_id and
1735 child_object_type_id = l_child_object_type_id) OR
1736 (parent_object_id = l_child_object_id and
1737 parent_object_type_id = l_child_object_type_id)
1738 ) AND
1739 relationship_type = 2;
1740 end if;
1741
1742 if l_count = 0 then
1743
1744 delete from eam_wo_relationships where
1745 child_object_id = l_child_object_id
1746 and child_object_type_id = l_child_object_type_id
1747 and parent_object_id = l_parent_object_id
1748 and parent_object_type_id = l_parent_object_type_id
1749 and parent_relationship_type = l_relationship_type;
1750
1751 if l_relationship_type = 1 then
1752
1753 select sched_relationship_id into l_sched_relationship_id
1754 from WIP_SCHED_RELATIONSHIPS
1755 where parent_object_id = l_parent_object_id
1756 and parent_object_type_id = l_parent_object_type_id
1757 and child_object_id = l_child_object_id
1758 and child_object_type_id = l_child_object_type_id
1759 and relationship_type = 1;
1760
1761 wip_sched_relation_grp.deleteRow(
1762 p_relationshipID => l_sched_relationship_id,
1763 x_return_status => l_return_status,
1764 x_msg_count => l_msg_count,
1765 x_msg_data => l_msg_data,
1766 p_api_version => 1.0,
1767 p_init_msg_list => FND_API.G_FALSE,
1768 p_commit => FND_API.G_FALSE);
1769
1770 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1771
1772 l_token_tbl(1).token_name := 'Parent Object Id';
1773 l_token_tbl(1).token_value := l_parent_object_id;
1774
1775 l_out_mesg_token_tbl := l_mesg_token_tbl;
1776 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
1777 ( p_message_name => 'EAM_WN_DEL_REL_API_FAIL'
1778 , p_token_tbl => l_token_tbl
1779 , p_mesg_token_tbl => l_mesg_token_tbl
1780 , x_mesg_token_tbl => l_out_mesg_token_tbl
1781 );
1782 l_mesg_token_tbl := l_out_mesg_token_tbl;
1783
1784 x_mesg_token_tbl := l_out_mesg_token_tbl;
1785
1786 x_return_status := 'E';
1787 return;
1788
1789 end if;
1790
1791 end if;
1792
1793
1794 elsif l_count > 0 then
1795
1796 l_token_tbl(1).token_name := 'Parent_WorkOrder';
1797 l_token_tbl(1).token_value := l_parent_workorder;
1798 l_token_tbl(2).token_name := 'Child_WorkOrder';
1799 l_token_tbl(2).token_value := l_child_workorder;
1800
1801 l_out_mesg_token_tbl := l_mesg_token_tbl;
1802 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
1803 ( p_message_name => 'EAM_WN_DELINK_DEP_EXS'
1804 , p_token_tbl => l_token_tbl
1805 , p_mesg_token_tbl => l_mesg_token_tbl
1806 , x_mesg_token_tbl => l_out_mesg_token_tbl
1807 );
1808 l_mesg_token_tbl := l_out_mesg_token_tbl;
1809
1810 x_mesg_token_tbl := l_out_mesg_token_tbl;
1811
1812 x_return_status := FND_API.G_RET_STS_ERROR;
1813 return;
1814 end if;
1815
1816 IF l_relationship_type = 1 THEN
1817 select firm_planned_flag into l_parent_firm_flag from
1818 wip_discrete_jobs where wip_entity_id = l_parent_object_id;
1819
1820 IF(l_parent_firm_flag = 2) THEN
1821 Shrink_Parent(
1822 p_parent_object_id => l_parent_object_id,
1823 p_parent_object_type_id => l_parent_object_type_id);
1824
1825 -- find the list of subsequent constraining parents in the upward direction
1826 IF NOT l_constraining_parents%ISOPEN THEN
1827 OPEN l_constraining_parents FOR
1828
1829 select * from wip_sched_relationships wsr
1830 WHERE wsr.relationship_type = 1
1831 START WITH wsr.child_object_id = l_parent_object_id
1832 CONNECT BY PRIOR wsr.parent_object_id = wsr.child_object_id;
1833
1834 END IF;
1835
1836 -- Adjust durations of all subsequent parent work orders
1837 -- to be the maximum of it's children and its operations. Stop at first firm parent.
1838 LOOP FETCH l_constraining_parents into l_parent_record;
1839
1840 if l_parent_record.parent_object_id is not null then
1841
1842 select firm_planned_flag into l_parent_firm_flag from
1843 wip_discrete_jobs where wip_entity_id = l_parent_record.parent_object_id;
1844
1845 EXIT WHEN l_parent_firm_flag = 1;
1846
1847 Shrink_Parent(
1848 p_parent_object_id => l_parent_record.parent_object_id,
1849 p_parent_object_type_id => l_parent_record.parent_object_type_id);
1850
1851 end if;
1852
1853 EXIT WHEN l_constraining_parents%NOTFOUND;
1854
1855 END LOOP;
1856
1857 CLOSE l_constraining_parents;
1858 END IF;
1859 END IF;
1860
1861 if l_relationship_type = 1 then
1862 -- Stamp the hierarchy underneath with the new value
1863 -- of top level object id
1864 IF NOT l_constraining_children%ISOPEN THEN
1865 OPEN l_constraining_children FOR
1866
1867 select * from wip_sched_relationships wsr
1868 WHERE wsr.relationship_type in (1,2)
1869 START WITH wsr.parent_object_id = l_child_object_id
1870 CONNECT BY wsr.parent_object_id = PRIOR wsr.child_object_id;
1871
1872 END IF;
1873
1874 LOOP FETCH l_constraining_children into
1875 l_relationship_record;
1876
1877 if l_relationship_record.parent_object_id is not null then
1878 l_relationship_record.top_level_object_id := l_child_object_id;
1879 l_relationship_record.top_level_object_type_id := l_child_object_type_id;
1880
1881 update wip_sched_relationships set
1882 top_level_object_id = l_child_object_id,
1883 top_level_object_type_id = l_child_object_type_id
1884 where sched_relationship_id = l_relationship_record.sched_relationship_id;
1885
1886 select maintenance_object_source into l_maint_obj_src
1887 from wip_discrete_jobs where wip_entity_id = l_relationship_record.parent_object_id;
1888 if l_maint_obj_src = 1 then -- EAM
1889 update eam_wo_relationships set
1890 top_level_object_id = l_child_object_id,
1891 top_level_object_type_id = l_child_object_type_id
1892 where
1893 parent_object_id = l_relationship_record.parent_object_id
1894 and child_object_id = l_relationship_record.child_object_id;
1895 end if;
1896 end if;
1897
1898 EXIT WHEN l_constraining_children%NOTFOUND;
1899
1900 END LOOP;
1901
1902 CLOSE l_constraining_children;
1903
1904 end if;
1905
1906 EXCEPTION
1907 when others then
1908
1909 rollback to EAM_WN_DELINK_PAR_CH;
1910
1911 l_token_tbl(1).token_name := 'Parent_WorkOrder';
1912 l_token_tbl(1).token_value := l_parent_workorder;
1913 l_token_tbl(2).token_name := 'Child_WorkOrder';
1914 l_token_tbl(2).token_value := l_child_workorder;
1915
1916 l_out_mesg_token_tbl := l_mesg_token_tbl;
1917 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
1918 ( p_message_name => 'EAM_WN_DELINK_UNKNOWN_ERR'
1919 , p_token_tbl => l_token_tbl
1920 , p_mesg_token_tbl => l_mesg_token_tbl
1921 , x_mesg_token_tbl => l_out_mesg_token_tbl
1922 );
1923 l_mesg_token_tbl := l_out_mesg_token_tbl;
1924
1925 x_mesg_token_tbl := l_out_mesg_token_tbl;
1926
1927 x_return_status := FND_API.G_RET_STS_ERROR;
1928 return;
1929
1930 END Delink_Child_From_Parent;
1931
1932
1933
1934
1935
1936 -- This procedure will check that the workorder / operation/ resources duration wont be negative
1937
1938 PROCEDURE Check_Wo_Negative_Dates
1939 (
1940 p_api_version IN NUMBER,
1941 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1942 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1943 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1944 p_wip_entity_id IN NUMBER,
1945 p_organization_id IN NUMBER,
1946 x_return_status OUT NOCOPY VARCHAR2,
1947 x_msg_count OUT NOCOPY NUMBER,
1948 x_msg_data OUT NOCOPY VARCHAR2
1949 ) IS
1950
1951
1952 TYPE l_op_records IS RECORD (first_unit_start_date DATE,
1953 last_unit_completion_date DATE);
1954
1955 l_op_record l_op_records;
1956
1957 TYPE l_resource_records IS RECORD (res_start_date DATE,
1958 res_completion_date DATE);
1959
1960 l_resource_record l_resource_records;
1961
1962 CURSOR l_op_resources is
1963 select
1964 wor.start_date as res_start_date,
1965 wor.completion_date as res_completion_date
1966 from wip_operation_resources wor
1967 where wor.wip_entity_id = p_wip_entity_id
1968 and wor.organization_id = p_organization_id;
1969
1970 CURSOR l_op_records_cur is
1971 select
1972 wo.first_unit_start_date as first_unit_start_date,
1973 wo.last_unit_completion_date as last_unit_completion_date
1974 from wip_operations wo
1975 where wo.wip_entity_id = p_wip_entity_id
1976 and wo.organization_id = p_organization_id;
1977
1978 TYPE l_resource_inst_records IS RECORD (
1979 resinst_start_date DATE,
1980 resinst_completion_date DATE);
1981
1982 l_resource_inst_record l_resource_inst_records;
1983
1984 CURSOR l_resource_instances is
1985 select
1986 wori.start_date as resinst_start_date,
1987 wori.completion_date as resinst_completion_date
1988 from wip_op_resource_instances wori
1989 where wori.wip_entity_id = p_wip_entity_id;
1990
1991
1992 TYPE l_sub_resource_records IS RECORD (
1993 res_start_date DATE,
1994 res_completion_date DATE);
1995 l_sub_resource_record l_sub_resource_records;
1996
1997 CURSOR l_op_sub_resources is
1998 select
1999 wor.start_date as res_start_date,
2000 wor.completion_date as res_completion_date
2001 from
2002 wip_sub_operation_resources wor
2003 where
2004 wor.wip_entity_id = p_wip_entity_id
2005 and wor.organization_id = p_organization_id;
2006
2007
2008 l_wip_entity_id NUMBER := p_wip_entity_id;
2009 l_organization_id NUMBER := p_organization_id;
2010 l_wo_start_date DATE;
2011 l_wo_completion_date DATE;
2012
2013 BEGIN
2014
2015 select scheduled_start_date, scheduled_completion_date
2016 into l_wo_start_date, l_wo_completion_date
2017 from wip_discrete_jobs
2018 where wip_entity_id = l_wip_entity_id and
2019 organization_id = l_organization_id;
2020
2021
2022 -- check if work order has -ve duration
2023 IF l_wo_start_date > l_wo_completion_date THEN
2024 x_return_status := FND_API.G_RET_STS_ERROR;
2025 RETURN;
2026 END IF;
2027
2028
2029 /* -- find the list of wo operations
2030 IF NOT l_wo_operations%ISOPEN THEN
2031 OPEN l_wo_operations FOR
2032 select * from
2033 wip_operations
2034 where wip_entity_id = l_wip_entity_id and
2035 organization_id = l_organization_id;
2036 END IF;
2037 */
2038
2039 -- Check if any of operation has negative duration
2040 OPEN l_op_records_cur;
2041 LOOP FETCH l_op_records_cur into
2042 l_op_record;
2043
2044 IF l_op_record.first_unit_start_date > l_op_record.last_unit_completion_date THEN
2045 x_return_status := FND_API.G_RET_STS_ERROR;
2046 RETURN;
2047 END IF;
2048
2049 EXIT WHEN l_op_records_cur%NOTFOUND;
2050 END LOOP;
2051
2052 CLOSE l_op_records_cur;
2053
2054 -- Check if resource has negative duration
2055 OPEN l_op_resources;
2056 LOOP FETCH l_op_resources into l_resource_record;
2057
2058 IF l_resource_record.res_start_date > l_resource_record.res_completion_date THEN
2059 x_return_status := FND_API.G_RET_STS_ERROR;
2060 RETURN;
2061 END IF;
2062
2063 EXIT WHEN l_op_resources%NOTFOUND;
2064 END LOOP;
2065
2066 CLOSE l_op_resources;
2067
2068 -- Check if resource instance has negative duration
2069 OPEN l_resource_instances;
2070 LOOP FETCH l_resource_instances into l_resource_inst_record;
2071
2072 IF l_resource_inst_record.resinst_start_date > l_resource_inst_record.resinst_completion_date THEN
2073 x_return_status := FND_API.G_RET_STS_ERROR;
2074 RETURN;
2075 END IF;
2076
2077 EXIT WHEN l_resource_instances%NOTFOUND;
2078 END LOOP;
2079
2080 CLOSE l_resource_instances;
2081
2082 -- Check if substitute resource has negative duration
2083 OPEN l_op_sub_resources;
2084 LOOP FETCH l_op_sub_resources into l_sub_resource_record;
2085
2086 IF l_sub_resource_record.res_start_date > l_sub_resource_record.res_completion_date THEN
2087 x_return_status := FND_API.G_RET_STS_ERROR;
2088 RETURN;
2089 END IF;
2090
2091 EXIT WHEN l_op_sub_resources%NOTFOUND;
2092 END LOOP;
2093
2094 CLOSE l_op_sub_resources;
2095
2096
2097 x_return_status := FND_API.G_RET_STS_SUCCESS;
2098
2099 EXCEPTION
2100 when others then
2101 x_return_status := FND_API.G_RET_STS_ERROR;
2102 return;
2103
2104 END Check_Wo_Negative_Dates;
2105
2106
2107 -- This procedure will check whether the operation dates fall within the
2108 -- WO dates and whether the resource dates fall within the operation dates
2109 -- This procedure can be used while moving or resizing work orders
2110 PROCEDURE Check_WO_Dates
2111 (
2112 p_api_version IN NUMBER,
2113 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2114 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2115 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2116
2117 p_wip_entity_id IN NUMBER,
2118
2119 x_return_status OUT NOCOPY VARCHAR2,
2120 x_msg_count OUT NOCOPY NUMBER,
2121 x_msg_data OUT NOCOPY VARCHAR2
2122 ) IS
2123
2124 TYPE l_operation_records IS REF CURSOR RETURN WIP_OPERATIONS%ROWTYPE;
2125 l_wo_operations l_operation_records;
2126 l_operation_record WIP_OPERATIONS%ROWTYPE;
2127
2128 TYPE l_resource_records IS RECORD (wip_entity_id NUMBER,
2129 operation_seq_num NUMBER,
2130 resource_seq_num NUMBER,
2131 op_start_date DATE,
2132 op_completion_date DATE,
2133 res_start_date DATE,
2134 res_completion_date DATE);
2135 l_resource_record l_resource_records;
2136
2137 CURSOR l_op_resources is
2138 select p_wip_entity_id as wip_entity_id, wo.operation_seq_num,
2139 wor.resource_seq_num,
2140 wo.first_unit_start_date as op_start_date,
2141 wo.last_unit_completion_date as op_completion_date,
2142 wor.start_date as res_start_date,
2143 wor.completion_date as res_completion_date
2144 from wip_operations wo,
2145 wip_operation_resources wor
2146 where wo.wip_entity_id = p_wip_entity_id
2147 and wor.wip_entity_id = p_wip_entity_id
2148 and wo.operation_seq_num = wor.operation_seq_num;
2149
2150
2151 TYPE l_resource_inst_records IS RECORD (wip_entity_id NUMBER,
2152 operation_seq_num NUMBER,
2153 resource_seq_num NUMBER,
2154 res_start_date DATE,
2155 res_completion_date DATE,
2156 resinst_start_date DATE,
2157 resinst_completion_date DATE);
2158
2159 l_resource_inst_record l_resource_inst_records;
2160
2161 CURSOR l_resource_instances is
2162 select p_wip_entity_id as wip_entity_id, wor.operation_seq_num,
2163 wor.resource_seq_num,
2164 wor.start_date as res_start_date,
2165 wor.completion_date as res_completion_date,
2166 wori.start_date as resinst_start_date,
2167 wori.completion_date as resinst_completion_date
2168 from wip_op_resource_instances wori,
2169 wip_operation_resources wor
2170 where wor.wip_entity_id = p_wip_entity_id
2171 and wori.wip_entity_id = p_wip_entity_id
2172 and wori.operation_seq_num = wor.operation_seq_num
2173 and wori.resource_seq_num = wor.resource_seq_num;
2174
2175 CURSOR l_res_usage_instances is
2176 select
2177 p_wip_entity_id as wip_entity_id,
2178 woru.operation_seq_num,
2179 woru.resource_seq_num,
2180 woru.start_date as res_usg_start_date,
2181 woru.completion_date as res_usg_completion_date,
2182 wori.start_date as resinst_start_date,
2183 wori.completion_date as resinst_completion_date,
2184 wori.instance_id as resinst_instance_id
2185 from
2186 wip_op_resource_instances wori,
2187 wip_operation_resource_usage woru
2188 where
2189 woru.wip_entity_id = p_wip_entity_id
2190 and wori.wip_entity_id = p_wip_entity_id
2191 and wori.operation_seq_num = woru.operation_seq_num
2192 and wori.resource_seq_num = woru.resource_seq_num
2193 and wori.instance_id = woru.instance_id
2194 and nvl(wori.serial_number,1) = nvl(woru.serial_number,1);
2195
2196 CURSOR l_res_usages is
2197 select
2198 p_wip_entity_id as wip_entity_id,
2199 woru.start_date as res_usg_start_date,
2200 woru.completion_date as res_usg_completion_date,
2201 wor.start_date as res_start_date,
2202 wor.completion_date as res_completion_date
2203 from
2204 wip_operation_resources wor,
2205 wip_operation_resource_usage woru
2206 where
2207 wor.wip_entity_id = p_wip_entity_id
2208 and woru.wip_entity_id = p_wip_entity_id
2209 and wor.resource_seq_num = woru.resource_seq_num
2210 and wor.operation_seq_num = woru.operation_seq_num
2211 and woru.instance_id is null;
2212
2213 l_wip_entity_id NUMBER := p_wip_entity_id;
2214 l_wo_start_date DATE;
2215 l_wo_completion_date DATE;
2216
2217 l_res_usage_inst_record l_res_usage_instances%rowtype;
2218 l_res_usages_record l_res_usages%rowtype;
2219
2220 BEGIN
2221
2222 select scheduled_start_date, scheduled_completion_date
2223 into l_wo_start_date, l_wo_completion_date
2224 from wip_discrete_jobs
2225 where wip_entity_id = l_wip_entity_id;
2226
2227 -- find the list of wo operations
2228 IF NOT l_wo_operations%ISOPEN THEN
2229 OPEN l_wo_operations FOR
2230 select * from
2231 wip_operations
2232 where wip_entity_id = l_wip_entity_id;
2233 END IF;
2234
2235
2236 -- Check whether all operations lie within WO dates.
2237 LOOP FETCH l_wo_operations into
2238 l_operation_record;
2239
2240 IF l_operation_record.first_unit_start_date < l_wo_start_date OR
2241 l_operation_record.last_unit_completion_date > l_wo_completion_date THEN
2242 x_return_status := FND_API.G_RET_STS_ERROR;
2243 RETURN;
2244 END IF;
2245
2246 EXIT WHEN l_wo_operations%NOTFOUND;
2247 END LOOP;
2248
2249 CLOSE l_wo_operations;
2250
2251
2252 OPEN l_op_resources;
2253 LOOP FETCH l_op_resources into l_resource_record;
2254
2255 IF l_resource_record.res_start_date < l_resource_record.op_start_date OR
2256 l_resource_record.res_completion_date > l_resource_record.op_completion_date THEN
2257 x_return_status := FND_API.G_RET_STS_ERROR;
2258 RETURN;
2259 END IF;
2260
2261 EXIT WHEN l_op_resources%NOTFOUND;
2262 END LOOP;
2263
2264 CLOSE l_op_resources;
2265
2266
2267 OPEN l_resource_instances;
2268 LOOP FETCH l_resource_instances into l_resource_inst_record;
2269
2270 IF l_resource_inst_record.resinst_start_date < l_resource_inst_record.res_start_date OR
2271 l_resource_inst_record.resinst_completion_date > l_resource_inst_record.res_completion_date THEN
2272 x_return_status := FND_API.G_RET_STS_ERROR;
2273 RETURN;
2274 END IF;
2275
2276 EXIT WHEN l_resource_instances%NOTFOUND;
2277 END LOOP;
2278
2279 CLOSE l_resource_instances;
2280
2281 --
2282 OPEN l_res_usages;
2283 LOOP FETCH l_res_usages into l_res_usages_record;
2284
2285 IF l_res_usages_record.res_start_date > l_res_usages_record.res_usg_start_date OR
2286 l_res_usages_record.res_usg_completion_date > l_res_usages_record.res_completion_date THEN
2287 x_return_status := FND_API.G_RET_STS_ERROR;
2288 RETURN;
2289 END IF;
2290
2291 EXIT WHEN l_res_usages%NOTFOUND;
2292 END LOOP;
2293
2294 CLOSE l_res_usages;
2295
2296 --
2297 OPEN l_res_usage_instances;
2298 LOOP FETCH l_res_usage_instances into l_res_usage_inst_record;
2299
2300 IF l_res_usage_inst_record.resinst_start_date > l_res_usage_inst_record.res_usg_start_date OR
2301 l_res_usage_inst_record.res_usg_completion_date > l_res_usage_inst_record.resinst_completion_date THEN
2302 x_return_status := FND_API.G_RET_STS_ERROR;
2303 RETURN;
2304 END IF;
2305
2306 EXIT WHEN l_res_usage_instances%NOTFOUND;
2307 END LOOP;
2308
2309 CLOSE l_res_usage_instances;
2310
2311
2312 x_return_status := FND_API.G_RET_STS_SUCCESS;
2313
2314 EXCEPTION
2315 when others then
2316 x_return_status := FND_API.G_RET_STS_ERROR;
2317 return;
2318
2319 END Check_WO_Dates;
2320
2321 -- To check dates of wori,woru,wor
2322 PROCEDURE Check_Resource_Dates
2323 (
2324 p_api_version IN NUMBER,
2325 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2326 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2327 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2328
2329 p_wip_entity_id IN NUMBER,
2330
2331 x_return_status OUT NOCOPY VARCHAR2,
2332 x_msg_count OUT NOCOPY NUMBER,
2333 x_msg_data OUT NOCOPY VARCHAR2
2334 ) IS
2335
2336 CURSOR l_resource_instances is
2337 select
2338 p_wip_entity_id as wip_entity_id,
2339 wor.operation_seq_num,
2340 wor.resource_seq_num,
2341 wor.start_date as res_start_date,
2342 wor.completion_date as res_completion_date,
2343 wori.start_date as resinst_start_date,
2344 wori.completion_date as resinst_completion_date,
2345 wori.instance_id as resinst_instance_id
2346 from
2347 wip_op_resource_instances wori,
2348 wip_operation_resources wor
2349 where
2350 wor.wip_entity_id = p_wip_entity_id
2351 and wori.wip_entity_id = p_wip_entity_id
2352 and wori.operation_seq_num = wor.operation_seq_num
2353 and wori.resource_seq_num = wor.resource_seq_num;
2354
2355 CURSOR l_res_usage_instances is
2356 select
2357 p_wip_entity_id as wip_entity_id,
2358 woru.operation_seq_num,
2359 woru.resource_seq_num,
2360 woru.start_date as res_usg_start_date,
2361 woru.completion_date as res_usg_completion_date,
2362 wori.start_date as resinst_start_date,
2363 wori.completion_date as resinst_completion_date,
2364 wori.instance_id as resinst_instance_id
2365 from
2366 wip_op_resource_instances wori,
2367 wip_operation_resource_usage woru
2368 where
2369 woru.wip_entity_id = p_wip_entity_id
2370 and wori.wip_entity_id = p_wip_entity_id
2371 and wori.operation_seq_num = woru.operation_seq_num
2372 and wori.resource_seq_num = woru.resource_seq_num
2373 and wori.instance_id = woru.instance_id ;
2374
2375 l_resource_inst_record l_resource_instances%rowtype;
2376 l_res_usage_inst_record l_res_usage_instances%rowtype;
2377
2378 l_wip_entity_id NUMBER := p_wip_entity_id;
2379
2380 BEGIN
2381
2382 OPEN l_resource_instances;
2383 LOOP FETCH l_resource_instances into l_resource_inst_record;
2384
2385 IF l_resource_inst_record.resinst_start_date < l_resource_inst_record.res_start_date THEN
2386
2387 update wip_operation_resources wor
2388 set start_date = l_resource_inst_record.res_start_date
2389 where wor.wip_entity_id = l_resource_inst_record.wip_entity_id
2390 and wor.operation_seq_num = l_resource_inst_record.operation_seq_num
2391 and wor.resource_seq_num = l_resource_inst_record.resource_seq_num ;
2392
2393
2394 END IF;
2395
2396 IF l_resource_inst_record.resinst_completion_date > l_resource_inst_record.res_completion_date THEN
2397
2398 update wip_operation_resources wor
2399 set completion_date = l_resource_inst_record.res_completion_date
2400 where wor.wip_entity_id = l_resource_inst_record.wip_entity_id
2401 and wor.operation_seq_num = l_resource_inst_record.operation_seq_num
2402 and wor.resource_seq_num = l_resource_inst_record.resource_seq_num;
2403 END IF;
2404
2405 EXIT WHEN l_resource_instances%NOTFOUND;
2406 END LOOP;
2407
2408 CLOSE l_resource_instances;
2409
2410 OPEN l_res_usage_instances;
2411 LOOP FETCH l_res_usage_instances into l_res_usage_inst_record;
2412
2413 IF l_res_usage_inst_record.res_usg_start_date < l_res_usage_inst_record.resinst_start_date THEN
2414
2415 update wip_op_resource_instances wori
2416 set start_date = l_res_usage_inst_record.res_usg_start_date
2417 where wori.wip_entity_id = l_res_usage_inst_record.wip_entity_id
2418 and wori.operation_seq_num = l_res_usage_inst_record.operation_seq_num
2419 and wori.resource_seq_num = l_res_usage_inst_record.resource_seq_num
2420 and wori.instance_id = l_res_usage_inst_record.resinst_instance_id
2421 and wori.serial_number IS NULL;
2422
2423 END IF;
2424
2425 IF l_res_usage_inst_record.res_usg_completion_date > l_res_usage_inst_record.resinst_completion_date THEN
2426
2427 update wip_op_resource_instances wori
2428 set completion_date = l_res_usage_inst_record.res_usg_completion_date
2429 where wori.wip_entity_id = l_res_usage_inst_record.wip_entity_id
2430 and wori.operation_seq_num = l_res_usage_inst_record.operation_seq_num
2431 and wori.resource_seq_num = l_res_usage_inst_record.resource_seq_num
2432 and wori.instance_id = l_res_usage_inst_record.resinst_instance_id
2433 and wori.serial_number IS NULL;
2434
2435 END IF;
2436
2437 EXIT WHEN l_res_usage_instances%NOTFOUND;
2438 END LOOP;
2439
2440 CLOSE l_res_usage_instances;
2441
2442 x_return_status := FND_API.G_RET_STS_SUCCESS;
2443
2444 EXCEPTION
2445 when others then
2446 x_return_status := FND_API.G_RET_STS_ERROR;
2447 return;
2448
2449 END Check_Resource_Dates;
2450
2451
2452
2453 /*******************************************************************
2454 * Procedure : Snap_Right
2455 * Returns : None
2456 * Parameters IN :
2457 * Parameters OUT NOCOPY: Work Object ID, Work Object Type
2458 * Mesg Token Table
2459 * Return Status
2460 * Purpose : This API snap the Work Order to the right. Assumes
2461 * backward scheduling
2462 *********************************************************************/
2463
2464 PROCEDURE Snap_Right
2465 (
2466 p_api_version IN NUMBER,
2467 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2468 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2469 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2470
2471 p_work_object_id IN NUMBER,
2472 p_work_object_type_id IN NUMBER,
2473
2474 x_return_status OUT NOCOPY VARCHAR2,
2475 x_msg_count OUT NOCOPY NUMBER,
2476 x_msg_data OUT NOCOPY VARCHAR2
2477
2478 )
2479
2480
2481 IS
2482 l_api_name CONSTANT VARCHAR2(30) := 'Snap_Right';
2483 l_api_version CONSTANT NUMBER := 1.0;
2484
2485 l_stmt_num NUMBER;
2486 l_work_object_id NUMBER;
2487 l_work_object_type_id NUMBER;
2488 l_right_snap_window NUMBER;
2489
2490 l_return_status VARCHAR2(1);
2491 l_msg_count NUMBER;
2492 l_msg_data VARCHAR2(1000);
2493
2494
2495
2496 BEGIN
2497 -- Standard Start of API savepoint
2498 SAVEPOINT EAM_WO_NETWORK_DEFAULT_PVT;
2499 -- Standard call to check for call compatibility.
2500 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2501 p_api_version ,
2502 l_api_name ,
2503 G_PKG_NAME )
2504 THEN
2505 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2506 END IF;
2507 -- Initialize message list if p_init_msg_list is set to TRUE.
2508 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2509 FND_MSG_PUB.initialize;
2510 END IF;
2511 -- Initialize API return status to success
2512 x_return_status := FND_API.G_RET_STS_SUCCESS;
2513 -- API body
2514
2515 /* Initialize the local variables */
2516 l_stmt_num := 10;
2517 l_work_object_id := p_work_object_id;
2518 l_work_object_type_id := p_work_object_type_id;
2519 l_return_status := FND_API.G_RET_STS_SUCCESS;
2520 l_msg_count := 0 ;
2521 l_msg_data := NULL;
2522
2523
2524 /* Find the right snap window */
2525 EAM_WO_NETWORK_DEFAULT_PVT.Snap_Right_Window
2526 (
2527 p_api_version => 1.0,
2528 p_work_object_id => l_work_object_id,
2529 p_work_object_type_id => l_work_object_type_id,
2530
2531 x_right_snap_window => l_right_snap_window,
2532 x_return_status => l_return_status,
2533 x_msg_count => l_msg_count,
2534 x_msg_data => l_msg_data
2535 );
2536
2537 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2538 x_return_status := FND_API.G_RET_STS_ERROR;
2539 RETURN;
2540
2541 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2542 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2543 RETURN;
2544 END IF;
2545
2546 /* Initialize the local variables */
2547 l_stmt_num := 10;
2548 l_return_status := FND_API.G_RET_STS_SUCCESS;
2549 l_msg_count := 0 ;
2550 l_msg_data := NULL;
2551
2552
2553 /* Call the MOVE API with the right_snap_window to move the entire structure
2554 and call scheduler when necessary */
2555 EAM_WO_NETWORK_UTIL_PVT.Move_WO
2556 (
2557 p_api_version => 1.0,
2558
2559 p_work_object_id => l_work_object_id,
2560 p_work_object_type_id => l_work_object_type_id,
2561 p_offset_days => l_right_snap_window,
2562 p_offset_direction => 1, -- Right/Forward
2563 p_schedule_method => 2, -- Backward Scheduling
2564
2565 x_return_status => l_return_status,
2566 x_msg_count => l_msg_count,
2567 x_msg_data => l_msg_data
2568
2569 );
2570
2571 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2572 x_return_status := FND_API.G_RET_STS_ERROR;
2573 RETURN;
2574
2575 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2576 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2577 RETURN;
2578 END IF;
2579
2580
2581
2582 -- End of API body.
2583 -- Standard check of p_commit.
2584 IF FND_API.To_Boolean( p_commit ) THEN
2585 --dbms_output.put_line('committing');
2586 COMMIT WORK;
2587 END IF;
2588 -- Standard call to get message count and if count is 1, get message info.
2589 FND_MSG_PUB.Count_And_Get
2590 ( p_count => x_msg_count ,
2591 p_data => x_msg_data
2592 );
2593 EXCEPTION
2594 WHEN FND_API.G_EXC_ERROR THEN
2595 x_return_status := FND_API.G_RET_STS_ERROR ;
2596 FND_MSG_PUB.Count_And_Get
2597 ( p_count => x_msg_count ,
2598 p_data => x_msg_data
2599 );
2600
2601
2602 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2603 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2604 FND_MSG_PUB.Count_And_Get
2605 (
2606 p_count => x_msg_count,
2607 p_data => x_msg_data
2608 );
2609
2610 WHEN OTHERS THEN
2611 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2612 IF FND_MSG_PUB.Check_Msg_Level
2613 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2614 THEN
2615 FND_MSG_PUB.Add_Exc_Msg
2616 ( G_PKG_NAME,
2617 l_api_name||'('||l_stmt_num||')'
2618 );
2619 END IF;
2620 FND_MSG_PUB.Count_And_Get
2621 ( p_count => x_msg_count,
2622 p_data => x_msg_data
2623 );
2624
2625
2626 END Snap_Right;
2627
2628
2629 /*******************************************************************
2630 * Procedure : Snap_Left
2631 * Returns : None
2632 * Parameters IN :
2633 * Parameters OUT NOCOPY: Work Object ID, Work Object Type
2634 * Mesg Token Table
2635 * Return Status
2636 * Purpose : This API snap the Work Order to the left. Assumes
2637 * forward scheduling
2638 *********************************************************************/
2639
2640 PROCEDURE Snap_Left
2641 (
2642 p_api_version IN NUMBER,
2643 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2644 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2645 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2646
2647 p_work_object_id IN NUMBER,
2648 p_work_object_type_id IN NUMBER,
2649
2650 x_return_status OUT NOCOPY VARCHAR2,
2651 x_msg_count OUT NOCOPY NUMBER,
2652 x_msg_data OUT NOCOPY VARCHAR2
2653
2654 )
2655
2656
2657 IS
2658 l_api_name CONSTANT VARCHAR2(30) := 'Snap_Left';
2659 l_api_version CONSTANT NUMBER := 1.0;
2660
2661 l_stmt_num NUMBER;
2662 l_work_object_id NUMBER;
2663 l_work_object_type_id NUMBER;
2664 l_left_snap_window NUMBER;
2665
2666 l_return_status VARCHAR2(1);
2667 l_msg_count NUMBER;
2668 l_msg_data VARCHAR2(1000);
2669
2670
2671
2672 BEGIN
2673 -- Standard Start of API savepoint
2674 SAVEPOINT EAM_WO_NETWORK_DEFAULT_PVT;
2675 -- Standard call to check for call compatibility.
2676 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2677 p_api_version ,
2678 l_api_name ,
2679 G_PKG_NAME )
2680 THEN
2681 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2682 END IF;
2683 -- Initialize message list if p_init_msg_list is set to TRUE.
2684 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2685 FND_MSG_PUB.initialize;
2686 END IF;
2687 -- Initialize API return status to success
2688 x_return_status := FND_API.G_RET_STS_SUCCESS;
2689 -- API body
2690
2691 /* Initialize the local variables */
2692 l_stmt_num := 10;
2693 l_work_object_id := p_work_object_id;
2694 l_work_object_type_id := p_work_object_type_id;
2695 l_left_snap_window := 0;
2696 l_return_status := FND_API.G_RET_STS_SUCCESS;
2697 l_msg_count := 0 ;
2698 l_msg_data := NULL;
2699
2700
2701 /* Find the right snap window */
2702 EAM_WO_NETWORK_DEFAULT_PVT.Snap_Left_Window
2703 (
2704 p_api_version => 1.0,
2705 p_work_object_id => l_work_object_id,
2706 p_work_object_type_id => l_work_object_type_id,
2707
2708 x_left_snap_window => l_left_snap_window,
2709 x_return_status => l_return_status,
2710 x_msg_count => l_msg_count,
2711 x_msg_data => l_msg_data
2712 );
2713
2714 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2715 x_return_status := FND_API.G_RET_STS_ERROR;
2716 RETURN;
2717
2718 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2719 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2720 RETURN;
2721 END IF;
2722
2723 /* Initialize the local variables */
2724 l_stmt_num := 10;
2725 l_return_status := FND_API.G_RET_STS_SUCCESS;
2726 l_msg_count := 0 ;
2727 l_msg_data := NULL;
2728
2729
2730 /* Call the MOVE API with the right_snap_window to move the entire structure
2731 and call scheduler when necessary */
2732 EAM_WO_NETWORK_UTIL_PVT.Move_WO
2733 (
2734 p_api_version => 1.0,
2735
2736 p_work_object_id => l_work_object_id,
2737 p_work_object_type_id => l_work_object_type_id,
2738 p_offset_days => l_left_snap_window,
2739 p_offset_direction => 2, -- Left/Backward
2740 p_schedule_method => 1, -- Forward Scheduling
2741
2742 x_return_status => l_return_status,
2743 x_msg_count => l_msg_count,
2744 x_msg_data => l_msg_data
2745
2746 );
2747
2748 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2749 x_return_status := FND_API.G_RET_STS_ERROR;
2750 RETURN;
2751
2752 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2753 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2754 RETURN;
2755 END IF;
2756
2757
2758
2759 -- End of API body.
2760 -- Standard check of p_commit.
2761 IF FND_API.To_Boolean( p_commit ) THEN
2762 --dbms_output.put_line('committing');
2763 COMMIT WORK;
2764 END IF;
2765 -- Standard call to get message count and if count is 1, get message info.
2766 FND_MSG_PUB.Count_And_Get
2767 ( p_count => x_msg_count ,
2768 p_data => x_msg_data
2769 );
2770 EXCEPTION
2771 WHEN FND_API.G_EXC_ERROR THEN
2772 x_return_status := FND_API.G_RET_STS_ERROR ;
2773 FND_MSG_PUB.Count_And_Get
2774 ( p_count => x_msg_count ,
2775 p_data => x_msg_data
2776 );
2777
2778
2779 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2780 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2781 FND_MSG_PUB.Count_And_Get
2782 (
2783 p_count => x_msg_count,
2784 p_data => x_msg_data
2785 );
2786
2787 WHEN OTHERS THEN
2788 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2789 IF FND_MSG_PUB.Check_Msg_Level
2790 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2791 THEN
2792 FND_MSG_PUB.Add_Exc_Msg
2793 ( G_PKG_NAME,
2794 l_api_name||'('||l_stmt_num||')'
2795 );
2796 END IF;
2797 FND_MSG_PUB.Count_And_Get
2798 ( p_count => x_msg_count,
2799 p_data => x_msg_data
2800 );
2801
2802
2803 END Snap_Left;
2804
2805
2806 /*******************************************************************
2807 * Procedure : Snap_Right_Window
2808 * Returns : None
2809 * Parameters IN :
2810 * Parameters OUT NOCOPY: Work Object ID, Work Object Type
2811 * Mesg Token Table
2812 * Return Status
2813 * Purpose : This API return the Max Right Snap Window for a Work Order
2814 * in number of days. The Max value of the return Variable is 1 day.
2815 *********************************************************************/
2816 PROCEDURE Snap_Right_Window
2817 (
2818 p_api_version IN NUMBER,
2819 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2820 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2821 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2822
2823 p_work_object_id IN NUMBER,
2824 p_work_object_type_id IN NUMBER,
2825
2826 x_right_snap_window OUT NOCOPY NUMBER,
2827 x_return_status OUT NOCOPY VARCHAR2,
2828 x_msg_count OUT NOCOPY NUMBER,
2829 x_msg_data OUT NOCOPY VARCHAR2
2830
2831 )
2832
2833
2834 IS
2835 l_api_name CONSTANT VARCHAR2(30) := 'Snap_Right_Window';
2836 l_api_version CONSTANT NUMBER := 1.0;
2837
2838 l_stmt_num NUMBER;
2839 l_work_object_id NUMBER;
2840 l_work_object_type_id NUMBER;
2841 l_wo_in_planning NUMBER;
2842
2843 l_this_level_min_window NUMBER;
2844 l_next_level_min_window NUMBER;
2845 l_min_right_snap_window NUMBER;
2846
2847 l_maintenance_object_source NUMBER;
2848
2849 l_return_status VARCHAR2(1);
2850 l_msg_count NUMBER;
2851 l_msg_data VARCHAR2(1000);
2852
2853 l_exception_msg VARCHAR2(1000);
2854
2855 BEGIN
2856 -- Standard Start of API savepoint
2857 SAVEPOINT EAM_WO_NETWORK_DEFAULT_PVT;
2858 -- Standard call to check for call compatibility.
2859 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2860 p_api_version ,
2861 l_api_name ,
2862 G_PKG_NAME )
2863 THEN
2864 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2865 END IF;
2866 -- Initialize message list if p_init_msg_list is set to TRUE.
2867 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2868 FND_MSG_PUB.initialize;
2869 END IF;
2870 -- Initialize API return status to success
2871 x_return_status := FND_API.G_RET_STS_SUCCESS;
2872 -- API body
2873
2874 /* Initialize the local variables */
2875 l_stmt_num := 10;
2876 l_work_object_id := p_work_object_id;
2877 l_work_object_type_id := p_work_object_type_id;
2878 l_wo_in_planning := 0;
2879 l_maintenance_object_source := 1; --EAM
2880
2881
2882 /* Commenting this out because we have now changed the
2883 design to not return 1 even if WO is in planning
2884
2885 If the current work order is still in planning, return 1
2886 l_stmt_num := 20;
2887 BEGIN
2888 SELECT COUNT(WDJ.WIP_ENTITY_ID)
2889 INTO l_wo_in_planning
2890 FROM WIP_DISCRETE_JOBS WDJ
2891 WHERE WDJ.WIP_ENTITY_ID = l_work_object_id
2892 AND l_work_object_type_id = 1
2893 AND WDJ.STATUS_TYPE NOT IN (3,4,5,6,7,12,14,15);
2894 EXCEPTION
2895 WHEN OTHERS THEN
2896 l_wo_in_planning := 0;
2897 END;
2898 If work order is still in planning stages, return the Max value of 1
2899 l_stmt_num := 30;
2900 IF (l_wo_in_planning = 1) THEN
2901 x_right_snap_window := 1.0;
2902 RETURN;
2903 END IF;
2904 */
2905
2906 /* Find Constraining Parent Window window to the right*/
2907 BEGIN
2908 l_stmt_num := 40;
2909
2910 SELECT (WDJ1.SCHEDULED_COMPLETION_DATE - WDJ2.SCHEDULED_COMPLETION_DATE)
2911 INTO l_this_level_min_window
2912 FROM WIP_SCHED_RELATIONSHIPS WSR,
2913 WIP_DISCRETE_JOBS WDJ1,
2914 WIP_DISCRETE_JOBS WDJ2
2915 WHERE WSR.CHILD_OBJECT_ID = l_work_object_id
2916 AND WSR.CHILD_OBJECT_TYPE_ID = l_work_object_type_id
2917 AND WSR.CHILD_OBJECT_TYPE_ID = 1
2918 AND WSR.RELATIONSHIP_TYPE = 1
2919 AND WSR.PARENT_OBJECT_TYPE_ID = 1
2920 AND WDJ1.WIP_ENTITY_ID = WSR.PARENT_OBJECT_ID
2921 AND WDJ2.WIP_ENTITY_ID = l_work_object_id;
2922
2923 -- Commented the below where clause because no status checks as per new design.
2924 -- AND WDJ2.STATUS_TYPE IN (3,4,5,6,7,12,14,15);
2925
2926 EXCEPTION
2927 WHEN NO_DATA_FOUND THEN
2928 --dbms_output.put_line('Inside parent NO DATA FOUND');
2929 l_this_level_min_window := 1; -- Max possible Value is One Day
2930 l_next_level_min_window := 1; -- Max possible Value is One Day
2931 x_right_snap_window := 1; -- Max possible Value is One Day
2932 WHEN OTHERS THEN
2933 FND_MSG_PUB.Add_Exc_Msg
2934 ( G_PKG_NAME,
2935 l_api_name||'('||l_stmt_num||')'
2936 );
2937 END;
2938
2939 /* Commenting this out because as per new design,
2940 negative values are allowed.
2941 Reset Value to 0 if computed value is negative
2942 l_stmt_num := 50;
2943 IF (l_this_level_min_window < 0) THEN
2944 l_this_level_min_window := 0;
2945 END IF;
2946 */
2947
2948 --dbms_output.put_line('PARENT = '||l_this_level_min_window*24);
2949
2950 /* Find right anp window for Dependencies with Siblings */
2951
2952 BEGIN
2953 l_stmt_num := 60;
2954
2955 SELECT MIN(WDJ2.SCHEDULED_START_DATE - WDJ1.SCHEDULED_COMPLETION_DATE)
2956 INTO l_min_right_snap_window
2957 FROM WIP_SCHED_RELATIONSHIPS WSR,
2958 WIP_DISCRETE_JOBS WDJ1,
2959 WIP_DISCRETE_JOBS WDJ2
2960 WHERE WSR.PARENT_OBJECT_TYPE_ID = 1
2961 AND WSR.CHILD_OBJECT_TYPE_ID = 1
2962 AND WSR.PARENT_OBJECT_ID = l_work_object_id
2963 AND WSR.PARENT_OBJECT_TYPE_ID = l_work_object_type_id
2964 AND WSR.RELATIONSHIP_TYPE = 2
2965 AND WDJ2.WIP_ENTITY_ID = WSR.CHILD_OBJECT_ID
2966 -- AND WDJ2.STATUS_TYPE IN (3,4,5,6,7,12,14,15)
2967 AND WDJ1.WIP_ENTITY_ID = l_work_object_id;
2968
2969 -- Commented the below where clause because no status checks as per new design.
2970 --AND WDJ1.STATUS_TYPE IN (3,4,5,6,7,12,14,15);
2971 EXCEPTION
2972 WHEN NO_DATA_FOUND THEN
2973 --dbms_output.put_line('Inside Sibling NO DATA FOUND');
2974 l_min_right_snap_window := l_this_level_min_window;
2975 WHEN OTHERS THEN
2976 FND_MSG_PUB.Add_Exc_Msg
2977 ( G_PKG_NAME,
2978 l_api_name||'('||l_stmt_num||')'
2979 );
2980 END;
2981
2982
2983 /* Commented out the check below because -ive values are allowed now.
2984 Reset value to 0 is computed value is negative
2985 l_stmt_num := 70;
2986 IF (l_min_right_snap_window < 0 ) THEN
2987 l_min_right_snap_window := 0;
2988 END IF;
2989 */
2990
2991
2992 --dbms_output.put_line('SIBLINGS = '||l_min_right_snap_window*24);
2993
2994 /* Find the Min of parent and siblings value */
2995 l_stmt_num := 80;
2996 IF (l_min_right_snap_window < l_this_level_min_window) THEN
2997 l_this_level_min_window := l_min_right_snap_window;
2998 END IF;
2999
3000 /* Reset other variable */
3001 l_stmt_num := 90;
3002 l_next_level_min_window := l_this_level_min_window;
3003 x_right_snap_window := l_this_level_min_window;
3004
3005 --dbms_output.put_line('THIS LEVEL = '||l_this_level_min_window);
3006
3007 /* Call API to Calculate reccusively for successive levels for AHL Jobs*/
3008 l_stmt_num := 95;
3009
3010 SELECT NVL(WDJ.MAINTENANCE_OBJECT_SOURCE,1)
3011 INTO l_maintenance_object_source
3012 FROM WIP_DISCRETE_JOBS WDJ
3013 WHERE WDJ.WIP_ENTITY_ID = l_work_object_id
3014 AND l_work_object_type_id = 1;
3015
3016 IF (l_maintenance_object_source = 2) THEN -- ONLY for AHL Jobs
3017
3018 l_stmt_num := 100;
3019 EAM_WO_NETWORK_DEFAULT_PVT.Find_Right_Snap_Window
3020 (
3021 p_api_version => 1.0,
3022 p_starting_object_id => l_work_object_id,
3023 p_starting_obj_type_id => l_work_object_type_id,
3024
3025 p_parent_object_id => l_work_object_id,
3026 p_parent_object_type_id => l_work_object_type_id,
3027 p_cur_right_snap_window => l_this_level_min_window,
3028
3029 x_right_snap_window => l_next_level_min_window,
3030 x_return_status => l_return_status,
3031 x_msg_count => l_msg_count,
3032 x_msg_data => l_msg_data
3033 );
3034 END IF;
3035
3036 /* Store returned min value into the Return Variable */
3037 l_stmt_num := 110;
3038 x_right_snap_window := l_next_level_min_window;
3039
3040
3041 -- End of API body.
3042 -- Standard check of p_commit.
3043 IF FND_API.To_Boolean( p_commit ) THEN
3044 --dbms_output.put_line('committing');
3045 COMMIT WORK;
3046 END IF;
3047 -- Standard call to get message count and if count is 1, get message info.
3048 FND_MSG_PUB.Count_And_Get
3049 ( p_count => x_msg_count ,
3050 p_data => x_msg_data
3051 );
3052 EXCEPTION
3053 WHEN FND_API.G_EXC_ERROR THEN
3054 x_return_status := FND_API.G_RET_STS_ERROR ;
3055 FND_MSG_PUB.Count_And_Get
3056 ( p_count => x_msg_count ,
3057 p_data => x_msg_data
3058 );
3059
3060
3061 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3062 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3063 FND_MSG_PUB.Count_And_Get
3064 (
3065 p_count => x_msg_count,
3066 p_data => x_msg_data
3067 );
3068
3069 WHEN OTHERS THEN
3070 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3071 IF FND_MSG_PUB.Check_Msg_Level
3072 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3073 THEN
3074 FND_MSG_PUB.Add_Exc_Msg
3075 ( G_PKG_NAME,
3076 l_api_name||'('||l_stmt_num||')'
3077 );
3078 END IF;
3079 FND_MSG_PUB.Count_And_Get
3080 ( p_count => x_msg_count,
3081 p_data => x_msg_data
3082 );
3083
3084
3085 END Snap_Right_Window;
3086
3087
3088
3089 /*******************************************************************
3090 * Procedure : Snap_Left_Window
3091 * Returns : None
3092 * Parameters IN :
3093 * Parameters OUT NOCOPY: Work Object ID, Work Object Type
3094 * Mesg Token Table
3095 * Return Status
3096 * Purpose : This API return the Max Left Snap Window for a Work Order
3097 * in number of days. The Max value of the return Variable is 1 day.
3098 *********************************************************************/
3099 PROCEDURE Snap_Left_Window
3100 (
3101 p_api_version IN NUMBER,
3102 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
3103 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3104 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3105
3106 p_work_object_id IN NUMBER,
3107 p_work_object_type_id IN NUMBER,
3108
3109 x_left_snap_window OUT NOCOPY NUMBER,
3110 x_return_status OUT NOCOPY VARCHAR2,
3111 x_msg_count OUT NOCOPY NUMBER,
3112 x_msg_data OUT NOCOPY VARCHAR2
3113
3114 )
3115
3116
3117 IS
3118 l_api_name CONSTANT VARCHAR2(30) := 'Snap_Left_Window';
3119 l_api_version CONSTANT NUMBER := 1.0;
3120
3121 l_stmt_num NUMBER;
3122 l_work_object_id NUMBER;
3123 l_work_object_type_id NUMBER;
3124 l_wo_in_planning NUMBER;
3125
3126 l_this_level_min_window NUMBER;
3127 l_next_level_min_window NUMBER;
3128 l_min_left_snap_window NUMBER;
3129
3130 l_maintenance_object_source NUMBER;
3131
3132 l_return_status VARCHAR2(1);
3133 l_msg_count NUMBER;
3134 l_msg_data VARCHAR2(1000);
3135
3136 l_exception_msg VARCHAR2(1000);
3137
3138 BEGIN
3139 -- Standard Start of API savepoint
3140 SAVEPOINT EAM_WO_NETWORK_DEFAULT_PVT;
3141 -- Standard call to check for call compatibility.
3142 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
3143 p_api_version ,
3144 l_api_name ,
3145 G_PKG_NAME )
3146 THEN
3147 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3148 END IF;
3149 -- Initialize message list if p_init_msg_list is set to TRUE.
3150 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3151 FND_MSG_PUB.initialize;
3152 END IF;
3153 -- Initialize API return status to success
3154 x_return_status := FND_API.G_RET_STS_SUCCESS;
3155 -- API body
3156
3157 /* Initialize the local variables */
3158 l_stmt_num := 10;
3159 l_work_object_id := p_work_object_id;
3160 l_work_object_type_id := p_work_object_type_id;
3161 l_wo_in_planning := 0;
3162 l_maintenance_object_source := 1; --EAM
3163
3164
3165
3166 /* Commenting this out because no status checks as per new design.
3167 If the current work order is still in planning, return 1
3168 l_stmt_num := 20;
3169 BEGIN
3170 SELECT COUNT(WDJ.WIP_ENTITY_ID)
3171 INTO l_wo_in_planning
3172 FROM WIP_DISCRETE_JOBS WDJ
3173 WHERE WDJ.WIP_ENTITY_ID = l_work_object_id
3174 AND l_work_object_type_id = 1
3175 AND WDJ.STATUS_TYPE NOT IN (3,4,5,6,7,12,14,15);
3176 EXCEPTION
3177 WHEN OTHERS THEN
3178 l_wo_in_planning := 0;
3179 END;
3180 If work order is still in planning stages, return the Max value of 1
3181 l_stmt_num := 30;
3182 IF (l_wo_in_planning = 1) THEN
3183 x_left_snap_window := 1.0;
3184 RETURN;
3185 END IF;
3186 */
3187
3188
3189
3190 /* Find Constraining Parent Window window to the left*/
3191 BEGIN
3192 l_stmt_num := 40;
3193
3194 SELECT (WDJ2.SCHEDULED_START_DATE - WDJ1.SCHEDULED_START_DATE)
3195 INTO l_this_level_min_window
3196 FROM WIP_SCHED_RELATIONSHIPS WSR,
3197 WIP_DISCRETE_JOBS WDJ1,
3198 WIP_DISCRETE_JOBS WDJ2
3199 WHERE WSR.CHILD_OBJECT_ID = l_work_object_id
3200 AND WSR.CHILD_OBJECT_TYPE_ID = l_work_object_type_id
3201 AND WSR.CHILD_OBJECT_TYPE_ID = 1
3202 AND WSR.RELATIONSHIP_TYPE = 1
3203 AND WSR.PARENT_OBJECT_TYPE_ID = 1
3204 AND WDJ1.WIP_ENTITY_ID = WSR.PARENT_OBJECT_ID
3205 AND WDJ2.WIP_ENTITY_ID = l_work_object_id;
3206
3207 -- Commenting out line below bcos no status checks as per new design.
3208 -- AND WDJ2.STATUS_TYPE IN (3,4,5,6,7,12,14,15);
3209
3210 EXCEPTION
3211 WHEN NO_DATA_FOUND THEN
3212 --dbms_output.put_line('Inside parent NO DATA FOUND');
3213 l_this_level_min_window := 1; -- Max possible Value is One Day
3214 l_next_level_min_window := 1; -- Max possible Value is One Day
3215 x_left_snap_window := 1; -- Max possible Value is One Day
3216 WHEN OTHERS THEN
3217 FND_MSG_PUB.Add_Exc_Msg
3218 ( G_PKG_NAME,
3219 l_api_name||'('||l_stmt_num||')'
3220 );
3221 END;
3222
3223 /* commenting out; as -ive values are allowed as per new design.
3224 Reset Value to 0 if computed value is negative
3225 l_stmt_num := 50;
3226 IF (l_this_level_min_window < 0) THEN
3227 l_this_level_min_window := 0;
3228 END IF;
3229 */
3230
3231 --dbms_output.put_line('LEFT PARENT = '||l_this_level_min_window*24);
3232
3233 /* Find left snap window for Dependencies with Siblings */
3234
3235 BEGIN
3236 l_stmt_num := 60;
3237
3238 SELECT MIN(WDJ1.SCHEDULED_START_DATE - WDJ2.SCHEDULED_COMPLETION_DATE)
3239 INTO l_min_left_snap_window
3240 FROM WIP_SCHED_RELATIONSHIPS WSR,
3241 WIP_DISCRETE_JOBS WDJ1,
3242 WIP_DISCRETE_JOBS WDJ2
3243 WHERE WSR.PARENT_OBJECT_TYPE_ID = 1
3244 AND WSR.CHILD_OBJECT_TYPE_ID = 1
3245 AND WSR.CHILD_OBJECT_ID = l_work_object_id
3246 AND WSR.CHILD_OBJECT_TYPE_ID = l_work_object_type_id
3247 AND WSR.RELATIONSHIP_TYPE = 2
3248 AND WDJ2.WIP_ENTITY_ID = WSR.PARENT_OBJECT_ID
3249 --AND WDJ2.STATUS_TYPE IN (3,4,5,6,7,12,14,15)
3250 AND WDJ1.WIP_ENTITY_ID = l_work_object_id;
3251
3252 -- Commented out bcos no status checks as per new design.
3253 -- AND WDJ1.STATUS_TYPE IN (3,4,5,6,7,12,14,15);
3254 EXCEPTION
3255 WHEN NO_DATA_FOUND THEN
3256 --dbms_output.put_line('Inside Sibling NO DATA FOUND');
3257 l_min_left_snap_window := l_this_level_min_window;
3258 WHEN OTHERS THEN
3259 FND_MSG_PUB.Add_Exc_Msg
3260 ( G_PKG_NAME,
3261 l_api_name||'('||l_stmt_num||')'
3262 );
3263 END;
3264
3265 /* Commented out; bcos -ive values are allowed per new design.
3266 Reset value to 0 is computed value is negative
3267 l_stmt_num := 70;
3268 IF (l_min_left_snap_window < 0 ) THEN
3269 l_min_left_snap_window := 0;
3270 END IF;
3271 */
3272
3273 --dbms_output.put_line('SIBLINGS = '||l_min_left_snap_window*24);
3274
3275 /* Find the Min of parent and siblings value */
3276 l_stmt_num := 80;
3277 IF (l_min_left_snap_window < l_this_level_min_window) THEN
3278 l_this_level_min_window := l_min_left_snap_window;
3279 END IF;
3280
3281 /* Reset other variable */
3282 l_stmt_num := 90;
3283 l_next_level_min_window := l_this_level_min_window;
3284 x_left_snap_window := l_this_level_min_window;
3285
3286 --dbms_output.put_line('THIS LEVEL = '||l_this_level_min_window);
3287
3288 /* Call API to Calculate reccusively for successive levels for AHL Jobs*/
3289 l_stmt_num := 95;
3290
3291 SELECT NVL(WDJ.MAINTENANCE_OBJECT_SOURCE,1)
3292 INTO l_maintenance_object_source
3293 FROM WIP_DISCRETE_JOBS WDJ
3294 WHERE WDJ.WIP_ENTITY_ID = l_work_object_id
3295 AND l_work_object_type_id = 1;
3296
3297 IF (l_maintenance_object_source = 2) THEN -- ONLY for AHL Jobs
3298
3299 l_stmt_num := 100;
3300 EAM_WO_NETWORK_DEFAULT_PVT.Find_Left_Snap_Window
3301 (
3302 p_api_version => 1.0,
3303 p_starting_object_id => l_work_object_id,
3304 p_starting_obj_type_id => l_work_object_type_id,
3305
3306 p_parent_object_id => l_work_object_id,
3307 p_parent_object_type_id => l_work_object_type_id,
3308 p_cur_left_snap_window => l_this_level_min_window,
3309
3310 x_left_snap_window => l_next_level_min_window,
3311 x_return_status => l_return_status,
3312 x_msg_count => l_msg_count,
3313 x_msg_data => l_msg_data
3314 );
3315 END IF;
3316
3317 /* Store returned min value into the Return Variable */
3318 l_stmt_num := 110;
3319 x_left_snap_window := l_next_level_min_window;
3320
3321
3322 -- End of API body.
3323 -- Standard check of p_commit.
3324 IF FND_API.To_Boolean( p_commit ) THEN
3325 --dbms_output.put_line('committing');
3326 COMMIT WORK;
3327 END IF;
3328 -- Standard call to get message count and if count is 1, get message info.
3329 FND_MSG_PUB.Count_And_Get
3330 ( p_count => x_msg_count ,
3331 p_data => x_msg_data
3332 );
3333 EXCEPTION
3334 WHEN FND_API.G_EXC_ERROR THEN
3335 x_return_status := FND_API.G_RET_STS_ERROR ;
3336 FND_MSG_PUB.Count_And_Get
3337 ( p_count => x_msg_count ,
3338 p_data => x_msg_data
3339 );
3340
3341
3342 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3343 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3344 FND_MSG_PUB.Count_And_Get
3345 (
3346 p_count => x_msg_count,
3347 p_data => x_msg_data
3348 );
3349
3350 WHEN OTHERS THEN
3351 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3352 IF FND_MSG_PUB.Check_Msg_Level
3353 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3354 THEN
3355 FND_MSG_PUB.Add_Exc_Msg
3356 ( G_PKG_NAME,
3357 l_api_name||'('||l_stmt_num||')'
3358 );
3359 END IF;
3360 FND_MSG_PUB.Count_And_Get
3361 ( p_count => x_msg_count,
3362 p_data => x_msg_data
3363 );
3364
3365
3366 END Snap_Left_Window;
3367
3368
3369 /*******************************************************************
3370 * Procedure : Find_Right_Snap_Window
3371 * Returns : None
3372 * Parameters IN :
3373 * Parameters OUT NOCOPY: Work Object ID, Work Object Type
3374 * Mesg Token Table
3375 * Return Status
3376 * Purpose : This function will be called resccursively to find
3377 * The min allowable right snap window between dependent
3378 * work orders that do not belong to the same starting parent
3379 *********************************************************************/
3380
3381
3382 PROCEDURE Find_Right_Snap_Window
3383 (
3384 p_api_version IN NUMBER,
3385 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3386 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3387 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3388
3389 p_starting_object_id IN NUMBER,
3390 p_starting_obj_type_id IN NUMBER,
3391 p_parent_object_id IN NUMBER,
3392 p_parent_object_type_id IN NUMBER,
3393 p_cur_right_snap_window IN NUMBER, -- IN Days
3394
3395 x_right_snap_window OUT NOCOPY NUMBER, -- In Days
3396 x_return_status OUT NOCOPY VARCHAR2,
3397 x_msg_count OUT NOCOPY NUMBER,
3398 x_msg_data OUT NOCOPY VARCHAR2
3399 )
3400
3401 IS
3402 l_api_name CONSTANT VARCHAR2(30) := 'Find_Right_Snap_Window';
3403 l_api_version CONSTANT NUMBER := 1.0;
3404
3405 l_stmt_num NUMBER;
3406 l_starting_object_id NUMBER;
3407 l_starting_obj_type_id NUMBER;
3408 l_parent_object_id NUMBER;
3409 l_parent_object_type_id NUMBER;
3410 l_released_rowcount NUMBER;
3411 l_cur_right_snap_window NUMBER; -- In Days
3412 l_min_right_snap_window NUMBER; -- In Days
3413 l_this_level_min_window NUMBER; -- In Days
3414 l_next_level_min_window NUMBER; -- In Days
3415
3416 l_return_status VARCHAR2(1);
3417 l_msg_count NUMBER;
3418 l_msg_data VARCHAR2(1000);
3419 --l_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
3420
3421
3422 CURSOR constrained_children_cur (l_p_object NUMBER, l_p_object_type NUMBER) IS
3423 SELECT WSR.CHILD_OBJECT_ID,
3424 WSR.CHILD_OBJECT_TYPE_ID
3425 FROM WIP_SCHED_RELATIONSHIPS WSR
3426 WHERE WSR.PARENT_OBJECT_ID = l_p_object
3427 AND WSR.PARENT_OBJECT_TYPE_ID = l_p_object_type
3428 AND WSR.RELATIONSHIP_TYPE = 1;
3429
3430
3431 BEGIN
3432 -- Standard Start of API savepoint
3433
3434 -- Standard call to check for call compatibility.
3435 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
3436 p_api_version ,
3437 l_api_name ,
3438 G_PKG_NAME )
3439 THEN
3440 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3441 END IF;
3442 -- Initialize message list if p_init_msg_list is set to TRUE.
3443 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3444 FND_MSG_PUB.initialize;
3445 END IF;
3446 -- Initialize API return status to success
3447 x_return_status := FND_API.G_RET_STS_SUCCESS;
3448 -- API body
3449
3450 /* Initialize the local variables */
3451 l_stmt_num := 10;
3452
3453 l_parent_object_type_id := p_parent_object_type_id;
3454 l_parent_object_id := p_parent_object_id;
3455 l_cur_right_snap_window := p_cur_right_snap_window;
3456 l_min_right_snap_window := p_cur_right_snap_window;
3457 l_this_level_min_window := p_cur_right_snap_window;
3458 l_next_level_min_window := p_cur_right_snap_window;
3459 x_right_snap_window := p_cur_right_snap_window;
3460 l_starting_object_id := p_starting_object_id;
3461 l_starting_obj_type_id := p_starting_obj_type_id;
3462
3463 /* Open Cursor for the current parent */
3464
3465 FOR child IN constrained_children_cur (l_parent_object_id, l_parent_object_type_id)
3466 LOOP
3467
3468 --dbms_output.put_line('Parent ='|| l_parent_object_id);
3469 --dbms_output.put_line('Child ='|| child.child_object_id);
3470 --dbms_output.put_line(' ');
3471
3472 l_stmt_num := 20;
3473
3474 /* *****************************************************************************
3475 Find Min Window between Dependent released work orders that does
3476 not fall within the current parent.
3477 Stop further processing and RETURN
3478 Statuses are: 3- Released, 4 - Complete, 5- Complete No Charge
3479 6 - On Hold, 7- Cancelled, 12 - Closed, 14- Pending Close, 15 - Failed Close
3480 ************************************************************************** */
3481
3482 BEGIN
3483 SELECT MIN(WDJ2.SCHEDULED_START_DATE-WDJ1.SCHEDULED_COMPLETION_DATE)
3484 INTO l_min_right_snap_window
3485 FROM WIP_SCHED_RELATIONSHIPS WSR,
3486 WIP_DISCRETE_JOBS WDJ1,
3487 WIP_DISCRETE_JOBS WDJ2
3488 WHERE WSR.PARENT_OBJECT_TYPE_ID = 1
3489 AND WSR.CHILD_OBJECT_TYPE_ID = 1
3490 AND WSR.PARENT_OBJECT_ID = l_parent_object_id
3491 AND WSR.PARENT_OBJECT_TYPE_ID = l_parent_object_type_id
3492 AND WSR.RELATIONSHIP_TYPE = 2
3493 AND WSR.RELATIONSHIP_STATUS = 3
3494 AND WDJ2.WIP_ENTITY_ID = WSR.CHILD_OBJECT_ID
3495 -- AND WDJ2.STATUS_TYPE IN (3,4,5,6,7,12,14,15)
3496 AND WDJ1.WIP_ENTITY_ID = l_parent_object_id
3497 -- Commented out;bcos no status checks as per new design.
3498 -- AND WDJ1.STATUS_TYPE IN (3,4,5,6,7,12,14,15)
3499 AND WDJ2.WIP_ENTITY_ID NOT IN (
3500 SELECT WSR2.CHILD_OBJECT_ID CHILD_OBJECT_ID
3501 FROM WIP_SCHED_RELATIONSHIPS WSR2
3502 WHERE WSR2.RELATIONSHIP_TYPE = 1
3503 AND l_starting_obj_type_id = 1
3504 CONNECT BY prior WSR2.CHILD_OBJECT_ID = WSR2.PARENT_OBJECT_ID
3505 START WITH WSR2.PARENT_OBJECT_ID = l_starting_object_id
3506 );
3507
3508 EXCEPTION
3509 WHEN OTHERS THEN
3510 l_min_right_snap_window := l_cur_right_snap_window;
3511 --dbms_output.put_line('Inside Exception');
3512 END;
3513
3514 /* Commented out;bcos -ive values are allowed as per new design.
3515 Reset value to 0 if computed value is negative
3516 l_stmt_num := 30;
3517 IF (l_min_right_snap_window < 0 ) THEN
3518 l_min_right_snap_window := 0;
3519 END IF;
3520 */
3521
3522
3523 /* Find the Min of input parameter and the calculated value for the current level */
3524 l_stmt_num := 40;
3525 IF (l_min_right_snap_window < l_cur_right_snap_window) THEN
3526 l_this_level_min_window := l_min_right_snap_window;
3527 END IF;
3528
3529
3530 /* Recursive Call to the Min Window Finding API */
3531 l_stmt_num := 50;
3532 EAM_WO_NETWORK_DEFAULT_PVT.Find_Right_Snap_Window
3533 (
3534 p_api_version => 1.0,
3535 p_starting_object_id => l_starting_object_id,
3536 p_starting_obj_type_id => l_starting_obj_type_id,
3537
3538 p_parent_object_id => child.child_object_id,
3539 p_parent_object_type_id => child.child_object_type_id,
3540 p_cur_right_snap_window => l_this_level_min_window,
3541
3542 x_right_snap_window => l_next_level_min_window,
3543 x_return_status => l_return_status,
3544 x_msg_count => l_msg_count,
3545 x_msg_data => l_msg_data
3546 );
3547
3548 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3549 x_return_status := FND_API.G_RET_STS_ERROR;
3550
3551 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3552 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3553
3554 END IF;
3555
3556
3557
3558 END LOOP;
3559
3560 /* Store Min Value in the return Variable */
3561 l_stmt_num := 60;
3562 x_right_snap_window := l_next_level_min_window;
3563
3564 /* Commented out;bcos -ive value is allowed as per new design.
3565 Reset value to 0 if computed value is negative
3566 l_stmt_num := 70;
3567 IF ( x_right_snap_window < 0 ) THEN
3568 x_right_snap_window := 0;
3569 END IF;
3570 */
3571
3572 -- End of API body.
3573 -- Standard check of p_commit.
3574 IF FND_API.To_Boolean( p_commit ) THEN
3575 --dbms_output.put_line('committing');
3576 COMMIT WORK;
3577 END IF;
3578 -- Standard call to get message count and if count is 1, get message info.
3579 FND_MSG_PUB.Count_And_Get
3580 ( p_count => x_msg_count ,
3581 p_data => x_msg_data
3582 );
3583 EXCEPTION
3584 WHEN FND_API.G_EXC_ERROR THEN
3585 x_return_status := FND_API.G_RET_STS_ERROR ;
3586 FND_MSG_PUB.Count_And_Get
3587 ( p_count => x_msg_count ,
3588 p_data => x_msg_data
3589 );
3590
3591 RETURN;
3592 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3593
3594 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3595 FND_MSG_PUB.Count_And_Get
3596 (
3597 p_count => x_msg_count,
3598 p_data => x_msg_data
3599 );
3600
3601 RETURN;
3602 WHEN OTHERS THEN
3603 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3604 IF FND_MSG_PUB.Check_Msg_Level
3605 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3606 THEN
3607 FND_MSG_PUB.Add_Exc_Msg
3608 ( G_PKG_NAME,
3609 l_api_name||'('||l_stmt_num||')'
3610 );
3611 END IF;
3612 FND_MSG_PUB.Count_And_Get
3613 ( p_count => x_msg_count,
3614 p_data => x_msg_data
3615 );
3616
3617 RETURN;
3618 END Find_Right_Snap_Window;
3619
3620
3621
3622
3623 /*******************************************************************
3624 * Procedure : Find_Left_Snap_Window
3625 * Returns : None
3626 * Parameters IN :
3627 * Parameters OUT NOCOPY: Work Object ID, Work Object Type
3628 * Mesg Token Table
3629 * Return Status
3630 * Purpose : This function will be called resccursively to find
3631 * The min allowable left snap window between dependent
3632 * work orders that do not belong to the same starting parent
3633 *********************************************************************/
3634
3635
3636 PROCEDURE Find_Left_Snap_Window
3637 (
3638 p_api_version IN NUMBER,
3639 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3640 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3641 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3642
3643 p_starting_object_id IN NUMBER,
3644 p_starting_obj_type_id IN NUMBER,
3645 p_parent_object_id IN NUMBER,
3646 p_parent_object_type_id IN NUMBER,
3647 p_cur_left_snap_window IN NUMBER, -- IN Days
3648
3649 x_left_snap_window OUT NOCOPY NUMBER, -- In Days
3650 x_return_status OUT NOCOPY VARCHAR2,
3651 x_msg_count OUT NOCOPY NUMBER,
3652 x_msg_data OUT NOCOPY VARCHAR2
3653 )
3654
3655 IS
3656 l_api_name CONSTANT VARCHAR2(30) := 'Find_Left_Snap_Window';
3657 l_api_version CONSTANT NUMBER := 1.0;
3658
3659 l_stmt_num NUMBER;
3660 l_starting_object_id NUMBER;
3661 l_starting_obj_type_id NUMBER;
3662 l_parent_object_id NUMBER;
3663 l_parent_object_type_id NUMBER;
3664 l_released_rowcount NUMBER;
3665 l_cur_left_snap_window NUMBER; -- In Days
3666 l_min_left_snap_window NUMBER; -- In Days
3667 l_this_level_min_window NUMBER; -- In Days
3668 l_next_level_min_window NUMBER; -- In Days
3669
3670 l_return_status VARCHAR2(1);
3671 l_msg_count NUMBER;
3672 l_msg_data VARCHAR2(1000);
3673 --l_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
3674
3675
3676 CURSOR constrained_children_cur (l_p_object NUMBER, l_p_object_type NUMBER) IS
3677 SELECT WSR.CHILD_OBJECT_ID,
3678 WSR.CHILD_OBJECT_TYPE_ID
3679 FROM WIP_SCHED_RELATIONSHIPS WSR
3680 WHERE WSR.PARENT_OBJECT_ID = l_p_object
3681 AND WSR.PARENT_OBJECT_TYPE_ID = l_p_object_type
3682 AND WSR.RELATIONSHIP_TYPE = 1;
3683
3684
3685 BEGIN
3686 -- Standard Start of API savepoint
3687
3688 -- Standard call to check for call compatibility.
3689 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
3690 p_api_version ,
3691 l_api_name ,
3692 G_PKG_NAME )
3693 THEN
3694 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3695 END IF;
3696 -- Initialize message list if p_init_msg_list is set to TRUE.
3697 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3698 FND_MSG_PUB.initialize;
3699 END IF;
3700 -- Initialize API return status to success
3701 x_return_status := FND_API.G_RET_STS_SUCCESS;
3702 -- API body
3703
3704 /* Initialize the local variables */
3705 l_stmt_num := 10;
3706
3707 l_parent_object_type_id := p_parent_object_type_id;
3708 l_parent_object_id := p_parent_object_id;
3709 l_cur_left_snap_window := p_cur_left_snap_window;
3710 l_min_left_snap_window := p_cur_left_snap_window;
3711 l_this_level_min_window := p_cur_left_snap_window;
3712 l_next_level_min_window := p_cur_left_snap_window;
3713 x_left_snap_window := p_cur_left_snap_window;
3714 l_starting_object_id := p_starting_object_id;
3715 l_starting_obj_type_id := p_starting_obj_type_id;
3716
3717 /* Open Cursor for the current parent */
3718
3719 FOR child IN constrained_children_cur (l_parent_object_id, l_parent_object_type_id)
3720 LOOP
3721
3722 --dbms_output.put_line('Parent ='|| l_parent_object_id);
3723 --dbms_output.put_line('Child ='|| child.child_object_id);
3724 --dbms_output.put_line(' ');
3725
3726 l_stmt_num := 20;
3727
3728 /* *****************************************************************************
3729 Find Min Window between Dependent released work orders that does
3730 not fall within the current parent.
3731 Stop further processing and RETURN
3732 Statuses are: 3- Released, 4 - Complete, 5- Complete No Charge
3733 6 - On Hold, 7- Cancelled, 12 - Closed, 14- Pending Close, 15 - Failed Close
3734 ************************************************************************** */
3735
3736 BEGIN
3737 SELECT MIN(WDJ2.SCHEDULED_START_DATE-WDJ1.SCHEDULED_COMPLETION_DATE)
3738 INTO l_min_left_snap_window
3739 FROM WIP_SCHED_RELATIONSHIPS WSR,
3740 WIP_DISCRETE_JOBS WDJ1,
3741 WIP_DISCRETE_JOBS WDJ2
3742 WHERE WSR.PARENT_OBJECT_TYPE_ID = 1
3743 AND WSR.CHILD_OBJECT_TYPE_ID = 1
3744 AND WSR.CHILD_OBJECT_ID = l_parent_object_id
3745 AND WSR.CHILD_OBJECT_TYPE_ID = l_parent_object_type_id
3746 AND WSR.RELATIONSHIP_TYPE = 2
3747 AND WSR.RELATIONSHIP_STATUS = 3
3748 AND WDJ1.WIP_ENTITY_ID = WSR.PARENT_OBJECT_ID
3749 -- AND WDJ1.STATUS_TYPE IN (3,4,5,6,7,12,14,15)
3750 AND WDJ2.WIP_ENTITY_ID = l_parent_object_id
3751 -- Commented out;bcos no status checks as per new design.
3752 -- AND WDJ2.STATUS_TYPE IN (3,4,5,6,7,12,14,15)
3753 AND WDJ1.WIP_ENTITY_ID NOT IN (
3754 SELECT WSR2.CHILD_OBJECT_ID CHILD_OBJECT_ID
3755 FROM WIP_SCHED_RELATIONSHIPS WSR2
3756 WHERE WSR2.RELATIONSHIP_TYPE = 1
3757 AND l_starting_obj_type_id = 1
3758 CONNECT BY prior WSR2.CHILD_OBJECT_ID = WSR2.PARENT_OBJECT_ID
3759 START WITH WSR2.PARENT_OBJECT_ID = l_starting_object_id
3760 );
3761
3762 EXCEPTION
3763 WHEN OTHERS THEN
3764 l_min_left_snap_window := l_cur_left_snap_window;
3765 --dbms_output.put_line('Inside Exception');
3766 END;
3767
3768 /* Commented out;bcos -ive values are allowed as per new design.
3769 Reset value to 0 if computed value is negative
3770 l_stmt_num := 30;
3771 IF (l_min_left_snap_window < 0 ) THEN
3772 l_min_left_snap_window := 0;
3773 END IF;
3774 */
3775
3776
3777 /* Find the Min of input parameter and the calculated value for the current level */
3778 l_stmt_num := 40;
3779 IF (l_min_left_snap_window < l_cur_left_snap_window) THEN
3780 l_this_level_min_window := l_min_left_snap_window;
3781 END IF;
3782
3783
3784 /* Recursive Call to the Min Window Finding API */
3785 l_stmt_num := 50;
3786 EAM_WO_NETWORK_DEFAULT_PVT.Find_Left_Snap_Window
3787 (
3788 p_api_version => 1.0,
3789 p_starting_object_id => l_starting_object_id,
3790 p_starting_obj_type_id => l_starting_obj_type_id,
3791
3792 p_parent_object_id => child.child_object_id,
3793 p_parent_object_type_id => child.child_object_type_id,
3794 p_cur_left_snap_window => l_this_level_min_window,
3795
3796 x_left_snap_window => l_next_level_min_window,
3797 x_return_status => l_return_status,
3798 x_msg_count => l_msg_count,
3799 x_msg_data => l_msg_data
3800 );
3801
3802 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3803 x_return_status := FND_API.G_RET_STS_ERROR;
3804
3805 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3806 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3807
3808 END IF;
3809
3810
3811
3812 END LOOP;
3813
3814 /* Store Min Value in the return Variable */
3815 l_stmt_num := 60;
3816 x_left_snap_window := l_next_level_min_window;
3817
3818 /* Commented out;bcos -ive values are allowed as per new design.
3819 Reset value to 0 if computed value is negative
3820 l_stmt_num := 70;
3821 IF ( x_left_snap_window < 0 ) THEN
3822 x_left_snap_window := 0;
3823 END IF;
3824 */
3825
3826
3827 -- End of API body.
3828 -- Standard check of p_commit.
3829 IF FND_API.To_Boolean( p_commit ) THEN
3830 --dbms_output.put_line('committing');
3831 COMMIT WORK;
3832 END IF;
3833 -- Standard call to get message count and if count is 1, get message info.
3834 FND_MSG_PUB.Count_And_Get
3835 ( p_count => x_msg_count ,
3836 p_data => x_msg_data
3837 );
3838 EXCEPTION
3839 WHEN FND_API.G_EXC_ERROR THEN
3840 x_return_status := FND_API.G_RET_STS_ERROR ;
3841 FND_MSG_PUB.Count_And_Get
3842 ( p_count => x_msg_count ,
3843 p_data => x_msg_data
3844 );
3845
3846 RETURN;
3847 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3848
3849 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3850 FND_MSG_PUB.Count_And_Get
3851 (
3852 p_count => x_msg_count,
3853 p_data => x_msg_data
3854 );
3855
3856 RETURN;
3857 WHEN OTHERS THEN
3858 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3859 IF FND_MSG_PUB.Check_Msg_Level
3860 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3861 THEN
3862 FND_MSG_PUB.Add_Exc_Msg
3863 ( G_PKG_NAME,
3864 l_api_name||'('||l_stmt_num||')'
3865 );
3866 END IF;
3867 FND_MSG_PUB.Count_And_Get
3868 ( p_count => x_msg_count,
3869 p_data => x_msg_data
3870 );
3871
3872 RETURN;
3873
3874 END Find_Left_Snap_Window;
3875
3876
3877 --This procedure is called from procedure 'Delink_Child_From_Parent'
3878 --This sets the workorder dates to be the maximum of its operations and child workorders dates
3879 PROCEDURE Shrink_Parent
3880 (
3881 p_parent_object_id IN NUMBER,
3882 p_parent_object_type_id IN NUMBER
3883 )
3884 IS
3885 l_parent_object_id NUMBER;
3886 l_parent_object_type_id NUMBER;
3887
3888 l_min_date DATE;
3889 l_max_date DATE;
3890 l_op_start_date DATE;
3891 l_op_end_date DATE;
3892 l_wo_start_date DATE;
3893 l_wo_end_date DATE;
3894 l_requested_start_date DATE;
3895 l_requested_due_date DATE;
3896 l_status_type NUMBER;
3897 l_date_completed DATE;
3898
3899 TYPE l_relationship_records IS REF CURSOR RETURN WIP_SCHED_RELATIONSHIPS%ROWTYPE;
3900 l_constrained_children l_relationship_records;
3901 l_relationship_record WIP_SCHED_RELATIONSHIPS%ROWTYPE;
3902
3903 BEGIN
3904
3905 l_parent_object_id := p_parent_object_id;
3906 l_parent_object_type_id := p_parent_object_type_id;
3907
3908 -- Find the min start date and max end date of all
3909 -- constrained children for this parent
3910
3911 -- find the list of constrained children
3912 IF NOT l_constrained_children%ISOPEN THEN
3913 OPEN l_constrained_children FOR
3914 select * from
3915 wip_sched_relationships
3916 where relationship_type = 1
3917 and parent_object_id = l_parent_object_id
3918 and parent_object_type_id = l_parent_object_type_id;
3919 END IF;
3920
3921 LOOP FETCH l_constrained_children into
3922 l_relationship_record;
3923
3924 if l_relationship_record.child_object_id is not null then
3925
3926 select scheduled_start_date, scheduled_completion_date, status_type, date_completed
3927 into l_wo_start_date, l_wo_end_date,l_status_type,l_date_completed
3928 from wip_discrete_jobs
3929 where wip_entity_id = l_relationship_record.child_object_id;
3930
3931 --do not consider child workorders which are cancelled or [closed and date_completed is null](closed from cancelled status)
3932 IF NOT(
3933 l_status_type = 7
3934 OR ((l_status_type IN (12,14,15)) AND (l_date_completed IS NULL))
3935 ) THEN
3936 IF l_min_date is null OR
3937 l_min_date > l_wo_start_date THEN
3938 l_min_date := l_wo_start_date;
3939 END IF;
3940
3941 IF l_max_date is null OR
3942 l_max_date < l_wo_end_date THEN
3943 l_max_date := l_wo_end_date;
3944 END IF;
3945 END IF;
3946 end if;
3947
3948 EXIT WHEN l_constrained_children%NOTFOUND;
3949 END LOOP;
3950
3951 CLOSE l_constrained_children;
3952
3953 SELECT requested_start_date,due_date
3954 INTO l_requested_start_date,l_requested_due_date
3955 FROM WIP_DISCRETE_JOBS
3956 WHERE wip_entity_id=l_parent_object_id;
3957
3958 select min(first_unit_start_date),max(last_unit_completion_date)
3959 INTO l_op_start_date,l_op_end_date
3960 from wip_operations
3961 where wip_entity_id=l_parent_object_id;
3962
3963 IF(l_op_start_date IS NULL AND l_min_date IS NULL) THEN --no op or children
3964 l_wo_start_date:= NVL(l_requested_start_date,l_requested_due_date); --pick up requested_start_date or due_date
3965 l_wo_end_date:=l_wo_start_date;
3966 ELSIF(l_op_start_date IS NULL OR l_min_date IS NULL) THEN --either op or children present
3967 l_wo_start_date:= NVL(l_op_start_date,l_min_date); --pick up dates of op or children
3968 l_wo_end_date:=NVL(l_op_end_date,l_max_date);
3969 ELSE --both op and children present
3970 IF(l_min_date<l_op_start_date) --find min and max of op and children dates
3971 THEN l_wo_start_date:=l_min_date;
3972 ELSE
3973 l_wo_start_date:=l_op_start_date;
3974 END IF;
3975
3976 IF(l_max_date>l_op_end_date)
3977 THEN l_wo_end_date:=l_max_date;
3978 ELSE
3979 l_wo_end_date := l_op_end_date;
3980 END IF;
3981 END IF;
3982
3983 UPDATE WIP_DISCRETE_JOBS set
3984 scheduled_start_date = l_wo_start_date,
3985 scheduled_completion_date = l_wo_end_date
3986 where wip_entity_id = l_parent_object_id;
3987
3988 END SHRINK_PARENT;
3989
3990
3991 END EAM_WO_NETWORK_DEFAULT_PVT;