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