[Home] [Help]
PACKAGE BODY: APPS.EAM_WO_NETWORK_VALIDATE_PVT
Source
1 PACKAGE BODY EAM_WO_NETWORK_VALIDATE_PVT AS
2 /* $Header: EAMVWNVB.pls 120.6.12020000.3 2012/12/19 12:51:01 somitra ship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- EAMVWNVB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package EAM_WO_NETWORK_VALIDATE_PVT
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 11-SEP-2003 Basanth Roy Initial Creation
21 ***************************************************************************/
22
23 G_Pkg_Name VARCHAR2(30) := 'EAM_WO_NETWORK_VALIDATE_PVT';
24
25 g_token_tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
26 g_dummy NUMBER;
27
28
29 /*******************************************************************
30 * Procedure : Validate_Structure
31 * Returns : None
32 * Parameters IN :
33 * Parameters OUT NOCOPY: Work Object ID, Work Object Type
34 * Mesg Token Table
35 * Return Status
36 * Purpose : The purpose of this procedure is to check the structural
37 * validation errors within a work order network. It checks
38 * for parent child as well as completion dependency
39 * constraints.
40 *********************************************************************/
41 PROCEDURE Validate_Structure
42 (
43 p_api_version IN NUMBER,
44 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
45 p_commit IN VARCHAR2 := FND_API.G_FALSE,
46 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
47
48 p_work_object_id IN NUMBER,
49 p_work_object_type_id IN NUMBER,
50 p_exception_logging IN VARCHAR2 := 'N',
51
52 p_validate_status IN VARCHAR2 := 'N',
53 p_output_errors IN VARCHAR2 := 'N',
54
55 x_return_status OUT NOCOPY VARCHAR2,
56 x_msg_count OUT NOCOPY NUMBER,
57 x_msg_data OUT NOCOPY VARCHAR2,
58 x_wo_relationship_exc_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.wo_relationship_exc_tbl_type
59 )
60
61
62 IS
63 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Structure';
64 l_api_version CONSTANT NUMBER := 1.0;
65
66 l_stmt_num NUMBER;
67 l_work_object_id NUMBER;
68 l_work_object_type_id NUMBER;
69 l_top_level_object_id NUMBER;
70 l_top_level_object_type_id NUMBER;
71 l_released_rowcount NUMBER;
72
73 l_return_status VARCHAR2(1);
74 l_msg_count NUMBER;
75 l_msg_data VARCHAR2(1000);
76
77 l_exception_msg VARCHAR2(1000);
78 l_wo_relationship_exc_tbl EAM_PROCESS_WO_PUB.wo_relationship_exc_tbl_type;
79
80 CURSOR exception_writer_cur(topLvlObj NUMBER, topLvlObjType NUMBER, l_relationship_type NUMBER,l_status_check NUMBER) IS
81 SELECT WSR.SCHED_RELATIONSHIP_ID,
82 WE1.WIP_ENTITY_NAME AS PARENT_JOB,
83 WE2.WIP_ENTITY_NAME AS CHILD_JOB,
84 WDJ1.SCHEDULED_START_DATE AS PARENT_START_DATE,
85 WDJ1.SCHEDULED_COMPLETION_DATE AS PARENT_COMPLETION_DATE,
86 WDJ2.SCHEDULED_START_DATE AS CHILD_START_DATE,
87 WDJ2.SCHEDULED_COMPLETION_DATE AS CHILD_COMPLETION_DATE
88 FROM WIP_SCHED_RELATIONSHIPS WSR,
89 WIP_ENTITIES WE1,
90 WIP_ENTITIES WE2,
91 WIP_DISCRETE_JOBS WDJ1,
92 WIP_DISCRETE_JOBS WDJ2
93 WHERE WSR.RELATIONSHIP_STATUS = 3
94 AND WSR.RELATIONSHIP_TYPE = l_relationship_type
95 AND WSR.PARENT_OBJECT_TYPE_ID = 1
96 AND WSR.CHILD_OBJECT_TYPE_ID = 1
97 AND WE1.WIP_ENTITY_ID = WSR.PARENT_OBJECT_ID
98 AND WE2.WIP_ENTITY_ID = WSR.CHILD_OBJECT_ID
99 AND WDJ1.WIP_ENTITY_ID = WE1.WIP_ENTITY_ID
100 AND WDJ2.WIP_ENTITY_ID = WE2.WIP_ENTITY_ID
101 AND WSR.TOP_LEVEL_OBJECT_ID = topLvlObj
102 AND WSR.TOP_LEVEL_OBJECT_TYPE_ID = topLvlObjType
103 AND WDJ2.STATUS_TYPE = nvl(l_status_check,WDJ2.STATUS_TYPE);
104
105 BEGIN
106 -- Standard Start of API savepoint
107 SAVEPOINT EAM_WO_NETWORK_VALIDATE_PVT;
108 -- Standard call to check for call compatibility.
109 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
110 p_api_version ,
111 l_api_name ,
112 G_PKG_NAME )
113 THEN
114 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
115 END IF;
116 -- Initialize message list if p_init_msg_list is set to TRUE.
117 IF FND_API.to_Boolean( p_init_msg_list ) THEN
118 FND_MSG_PUB.initialize;
119 END IF;
120 -- Initialize API return status to success
121 x_return_status := FND_API.G_RET_STS_SUCCESS;
122 -- API body
123
124 /* Initialize the local variables */
125 l_stmt_num := 10;
126
127 l_work_object_type_id := p_work_object_type_id;
128 l_work_object_id := p_work_object_id;
129 l_top_level_object_id := NULL;
130 l_top_level_object_type_id := NULL;
131
132 /* Obtain TOP Parent Object Information */
133
134 BEGIN
135 l_stmt_num := 20;
136
137
138 SELECT WSR.TOP_LEVEL_OBJECT_ID,
139 WSR.TOP_LEVEL_OBJECT_TYPE_ID
140 INTO l_top_level_object_id,
141 l_top_level_object_type_id
142 FROM WIP_SCHED_RELATIONSHIPS WSR
143 WHERE WSR.CHILD_OBJECT_ID = l_work_object_id
144 AND WSR.CHILD_OBJECT_TYPE_ID = l_work_object_type_id
145 AND WSR.RELATIONSHIP_TYPE = 1;
146 EXCEPTION
147 WHEN NO_DATA_FOUND THEN
148 l_top_level_object_id := l_work_object_id;
149 l_top_level_object_type_id := l_work_object_type_id;
150 --dbms_output.put_line ('TOP = '||l_top_level_object_id);
151 WHEN OTHERS THEN
152 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
153 END;
154
155
156
157 /* Reset Status Flag for the entire structure */
158 l_stmt_num := 30;
159
160 -- for fix 7943516
161
162 UPDATE WIP_SCHED_RELATIONSHIPS WSR
163 SET WSR.RELATIONSHIP_STATUS = 1
164 WHERE WSR.TOP_LEVEL_OBJECT_TYPE_ID = l_top_level_object_type_id
165 AND WSR.TOP_LEVEL_OBJECT_ID = l_top_level_object_id
166 AND WSR.CHILD_OBJECT_ID = l_work_object_id
167 AND WSR.CHILD_OBJECT_TYPE_ID = l_work_object_type_id;
168
169 /*UPDATE EAM_WO_RELATIONSHIPS EWR
170 SET EWR.RELATIONSHIP_STATUS = 1
171 WHERE EWR.TOP_LEVEL_OBJECT_TYPE_ID = l_top_level_object_type_id
172 AND EWR.TOP_LEVEL_OBJECT_ID = l_top_level_object_id;*/
173
174 UPDATE EAM_WO_RELATIONSHIPS EWR
175 SET EWR.RELATIONSHIP_STATUS = 1
176 WHERE EWR.TOP_LEVEL_OBJECT_TYPE_ID = l_top_level_object_type_id
177 AND EWR.TOP_LEVEL_OBJECT_ID = l_top_level_object_id
178 AND EWR.CHILD_OBJECT_ID = l_work_object_id
179 AND EWR.CHILD_OBJECT_TYPE_ID = l_work_object_type_id;
180
181 -- for fix 7943516
182
183
184
185 /* *****************************************************************************
186 Check Completion Dependancy between work orders for this structure
187 ************************************************************************** */
188 l_stmt_num := 40;
189
190 UPDATE WIP_SCHED_RELATIONSHIPS WSR
191 SET WSR.RELATIONSHIP_STATUS = 3
192 WHERE WSR.SCHED_RELATIONSHIP_ID IN
193 (
194 SELECT WSR1.SCHED_RELATIONSHIP_ID
195 FROM WIP_SCHED_RELATIONSHIPS WSR1,
196 WIP_DISCRETE_JOBS WDJ1,
197 WIP_DISCRETE_JOBS WDJ2
198 WHERE WSR1.PARENT_OBJECT_TYPE_ID = 1
199 AND WSR1.CHILD_OBJECT_TYPE_ID = 1
200 AND WDJ1.WIP_ENTITY_ID = WSR1.PARENT_OBJECT_ID
201 AND WDJ2.WIP_ENTITY_ID = WSR1.CHILD_OBJECT_ID
202 AND WDJ1.SCHEDULED_COMPLETION_DATE > WDJ2.SCHEDULED_START_DATE
203 AND WSR1.TOP_LEVEL_OBJECT_ID = l_top_level_object_id
204 AND WSR1.TOP_LEVEL_OBJECT_TYPE_ID = l_top_level_object_type_id
205 AND WSR1.RELATIONSHIP_TYPE = 2
206 );
207
208 UPDATE EAM_WO_RELATIONSHIPS EWR
209 SET EWR.RELATIONSHIP_STATUS = 3
210 WHERE EWR.WO_RELATIONSHIP_ID IN
211 (
212 SELECT EWR1.WO_RELATIONSHIP_ID
213 FROM EAM_WO_RELATIONSHIPS EWR1,
214 WIP_DISCRETE_JOBS WDJ1,
215 WIP_DISCRETE_JOBS WDJ2
216 WHERE EWR1.PARENT_OBJECT_TYPE_ID = 1
217 AND EWR1.CHILD_OBJECT_TYPE_ID = 1
218 AND WDJ1.WIP_ENTITY_ID = EWR1.PARENT_OBJECT_ID
219 AND WDJ2.WIP_ENTITY_ID = EWR1.CHILD_OBJECT_ID
220 AND WDJ1.SCHEDULED_COMPLETION_DATE > WDJ2.SCHEDULED_START_DATE
221 AND EWR1.TOP_LEVEL_OBJECT_ID = l_top_level_object_id
222 AND EWR1.TOP_LEVEL_OBJECT_TYPE_ID = l_top_level_object_type_id
223 AND EWR1.PARENT_RELATIONSHIP_TYPE = 2
224 );
225
226 /* *****************************************************************************
227 Check Completion Dependancy between released work orders for this structure
228 and Raise ERROR Condition. Stop further processing and RETURN
229 Statuses are: 3- Released, 4 - Complete, 5- Complete No Charge
230 6 - On Hold, 7- Cancelled, 12 - Closed, 14- Pending Close, 15 - Failed Close
231 ************************************************************************** */
232
233 IF (SQL%ROWCOUNT > 0) THEN
234
235 l_released_rowcount := 0;
236 l_stmt_num := 50;
237
238
239 SELECT COUNT(WSR.SCHED_RELATIONSHIP_ID)
240 INTO l_released_rowcount
241 FROM WIP_SCHED_RELATIONSHIPS WSR,
242 WIP_DISCRETE_JOBS WDJ
243 WHERE WSR.PARENT_OBJECT_TYPE_ID = 1
244 AND WSR.CHILD_OBJECT_TYPE_ID = 1
245 AND WDJ.WIP_ENTITY_ID = WSR.CHILD_OBJECT_ID
246 AND WDJ.STATUS_TYPE IN (3,4,5,6,7,12,14,15)
247 AND WSR.TOP_LEVEL_OBJECT_ID = l_top_level_object_id
248 AND WSR.TOP_LEVEL_OBJECT_TYPE_ID = l_top_level_object_type_id
249 AND WSR.RELATIONSHIP_TYPE = 2
250 AND WSR.RELATIONSHIP_STATUS = 3;
251 -- No Need to Check parent status as Parent will always be released if child is released
252
253
254 IF (l_released_rowcount > 0 ) THEN
255 -- Error between two released work orders
256 x_return_status := FND_API.G_RET_STS_ERROR;
257
258 END IF;
259 END IF;
260
261
262 /* Call Parent Child Constraint Checks */
263 l_return_status := NULL;
264
265 l_stmt_num := 60;
266 EAM_WO_NETWORK_VALIDATE_PVT.Check_Constrained_Children
267 (
268 p_api_version => 1.0,
269 p_parent_object_id => l_top_level_object_id,
270 p_parent_object_type_id => l_top_level_object_type_id,
271 x_return_status => l_return_status,
272 x_msg_count => l_msg_count,
273 x_msg_data => l_msg_data
274 --x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
275 );
276
277 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
278 x_return_status := FND_API.G_RET_STS_ERROR;
279
280 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
281 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
282
283 END IF;
284
285
286 IF p_validate_status = 'N' THEN
287 --fix for 3433757
288 validate_status(p_work_object_id => p_work_object_id,
289 p_work_object_type_id => p_work_object_type_id,
290 x_return_status => l_return_status
291 );
292
293 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
294 x_return_status := l_return_status;
295 END IF;
296 ELSE
297 -- Added for Detailed Scheduling
298 Validate_Network_Status(p_work_object_id => l_top_level_object_id,
299 p_work_object_type_id => l_top_level_object_type_id,
300 p_wo_relationship_exc_tbl => l_wo_relationship_exc_tbl
301 );
302 IF (l_wo_relationship_exc_tbl.count >0 ) THEN
303 x_return_status := FND_API.G_RET_STS_ERROR;
304 END IF;
305
306 l_stmt_num := 70;
307
308 IF p_output_errors = 'Y' THEN
309
310 FND_MESSAGE.CLEAR;
311
312 FOR type_1 IN exception_writer_cur(l_top_level_object_id, l_top_level_object_type_id, 1,3)
313 LOOP
314 l_stmt_num := 80;
315
316 FND_MESSAGE.SET_NAME('EAM','EAM_TYPE_1_NETWORK_ERROR');
317 FND_MESSAGE.SET_TOKEN('PARENT_JOB',type_1.parent_job);
318 FND_MESSAGE.SET_TOKEN('PARENT_START_DATE',TO_CHAR(type_1.parent_start_date, 'DD-MON-YYYY HH24:MM:SS'));
319 FND_MESSAGE.SET_TOKEN('PARENT_COMPLETION_DATE',TO_CHAR(type_1.parent_completion_date, 'DD-MON-YYYY HH24:MM:SS'));
320 FND_MESSAGE.SET_TOKEN('CHILD_JOB',type_1.child_job);
321 FND_MESSAGE.SET_TOKEN('CHILD_START_DATE',TO_CHAR(type_1.child_start_date, 'DD-MON-YYYY HH24:MM:SS'));
322 FND_MESSAGE.SET_TOKEN('CHILD_COMPLETION_DATE',TO_CHAR(type_1.child_completion_date, 'DD-MON-YYYY HH24:MM:SS')); l_exception_msg := FND_MESSAGE.GET;
323
324 IF type_1.parent_job IS NOT NULL THEN
325
326 IF l_wo_relationship_exc_tbl.COUNT = 0 THEN
327 l_wo_relationship_exc_tbl(1) :=l_exception_msg;
328 ELSE
329 l_wo_relationship_exc_tbl(l_wo_relationship_exc_tbl.LAST+1) :=l_exception_msg;
330 END IF;
331 l_exception_msg := NULL;
332 FND_MESSAGE.CLEAR;
333
334 END IF;
335
336 END LOOP;
337
338 FOR type_2 IN exception_writer_cur(l_top_level_object_id, l_top_level_object_type_id, 2,3)
339 LOOP
340 IF type_2.parent_job IS NOT NULL THEN
341
342 l_stmt_num := 90;
343
344 FND_MESSAGE.SET_NAME('EAM','EAM_TYPE_2_NETWORK_ERROR');
345 FND_MESSAGE.SET_TOKEN('PARENT_JOB',type_2.parent_job);
346 FND_MESSAGE.SET_TOKEN('PARENT_COMPLETION_DATE',TO_CHAR(type_2.parent_completion_date, 'DD-MON-YYYY HH24:MM:SS'));
347 FND_MESSAGE.SET_TOKEN('CHILD_JOB',type_2.child_job);
348 FND_MESSAGE.SET_TOKEN('CHILD_START_DATE',TO_CHAR(type_2.child_start_date, 'DD-MON-YYYY HH24:MM:SS'));
349 l_exception_msg := FND_MESSAGE.GET;
350
351 IF l_wo_relationship_exc_tbl.COUNT =0 Then
352 l_wo_relationship_exc_tbl(1) :=l_exception_msg;
353 ELSE
354 l_wo_relationship_exc_tbl(l_wo_relationship_exc_tbl.LAST+1) :=l_exception_msg;
355 END if;
356
357 l_exception_msg := NULL;
358 FND_MESSAGE.CLEAR;
359
360 END IF;
361 END LOOP;
362 END IF; -- END IF for p_output_errors = 'Y'
363 END IF; -- End for IF p_validate_status = 'N'
364
365 --Bug3868292: Replaced top_level_object_id with l_top_level_object_id.
366
367 IF (UPPER(p_exception_logging) <> 'N' ) THEN
368
369 -- Purge the WIP_SCHEDULING_EXCEPTIONS table of error messages
370 -- from previous runs of Validate_Structure
371 delete from wip_scheduling_exceptions
372 where exception_type = 2
373 and sched_relationship_id in
374 (select /*+ index (WIP_SCHED_RELATIONSHIPS WIP_SCHED_RELATIONSHIPS_N1)*/ sched_relationship_id from
375 wip_sched_relationships
376 start with parent_object_id = l_top_level_object_id
377 connect by parent_object_id = prior child_object_id);
378
379 l_stmt_num := 100;
380
381 FND_MESSAGE.CLEAR;
382
383 FOR type_1 IN exception_writer_cur(l_top_level_object_id, l_top_level_object_type_id, 1,to_number(null))
384 LOOP
385 l_stmt_num := 110;
386
387 FND_MESSAGE.SET_NAME('EAM','EAM_TYPE_1_NETWORK_ERROR');
388 FND_MESSAGE.SET_TOKEN('PARENT_JOB',type_1.parent_job);
389 FND_MESSAGE.SET_TOKEN('PARENT_START_DATE',TO_CHAR(type_1.parent_start_date, 'DD-MON-YYYY HH24:MM:SS'));
390 FND_MESSAGE.SET_TOKEN('PARENT_COMPLETION_DATE',TO_CHAR(type_1.parent_completion_date, 'DD-MON-YYYY HH24:MM:SS'));
391 FND_MESSAGE.SET_TOKEN('CHILD_JOB',type_1.child_job);
392 FND_MESSAGE.SET_TOKEN('CHILD_START_DATE',TO_CHAR(type_1.child_start_date, 'DD-MON-YYYY HH24:MM:SS'));
393 FND_MESSAGE.SET_TOKEN('CHILD_COMPLETION_DATE',TO_CHAR(type_1.child_completion_date, 'DD-MON-YYYY HH24:MM:SS')); l_exception_msg := FND_MESSAGE.GET;
394
395
396 if type_1.parent_job is not null then
397
398 BEGIN
399
400 INSERT INTO WIP_SCHEDULING_EXCEPTIONS
401 (
402 wip_entity_id,
403 organization_id,
404 mesg_sequence,
405 scheduling_source,
406 scheduling_source_id,
407 message_text,
408 message_type,
409 marked_flag,
410 reported_date,
411 last_update_date,
412 creation_date,
413 created_by,
414 last_update_login,
415 last_updated_by,
416 operation_seq_num,
417 resource_seq_num,
418 resource_id,
419 inventory_item_id,
420 instance_id,
421 serial_number,
422 sched_relationship_id,
423 exception_type
424 )
425 VALUES
426 (
427 NULL, --wip_entity_id,
428 NULL, --organization_id,
429 1, --mesg_sequence,
430 NULL, --scheduling_source,
431 NULL,--scheduling_source_id,
432 l_exception_msg, --message_text,
433 NULL, --message_type,
434 NULL, --marked_flag,
435 SYSDATE, --reported_date,
436 SYSDATE, --last_update_date,
437 SYSDATE, --creation_date,
438 -1, --created_by,
439 -1, --last_update_login,
440 -1, --last_updated_by,
441 NULL, --operation_seq_num,
442 NULL, --resource_seq_num,
443 NULL, --resource_id,
444 NULL, --inventory_item_id,
445 NULL, --instance_id,
446 NULL, --serial_number,
447 type_1.sched_relationship_id,
448 2 -- exception_type
449 );
450 EXCEPTION
451 WHEN OTHERS THEN
452 FND_MSG_PUB.Add_Exc_Msg
453 ( G_PKG_NAME,
454 l_api_name||'('||l_stmt_num||')'
455 );
456 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
457 END;
458
459 l_exception_msg := NULL;
460 FND_MESSAGE.CLEAR;
461
462 end if;
463
464 END LOOP;
465
466 FOR type_2 IN exception_writer_cur(l_top_level_object_id, l_top_level_object_type_id, 2,to_number(null))
467 LOOP
468 if type_2.parent_job is not null then
469
470 l_stmt_num := 120;
471
472 FND_MESSAGE.SET_NAME('EAM','EAM_TYPE_2_NETWORK_ERROR');
473 FND_MESSAGE.SET_TOKEN('PARENT_JOB',type_2.parent_job);
474 FND_MESSAGE.SET_TOKEN('PARENT_COMPLETION_DATE',TO_CHAR(type_2.parent_completion_date, 'DD-MON-YYYY HH24:MM:SS'));
475 FND_MESSAGE.SET_TOKEN('CHILD_JOB',type_2.child_job);
476 FND_MESSAGE.SET_TOKEN('CHILD_START_DATE',TO_CHAR(type_2.child_start_date, 'DD-MON-YYYY HH24:MM:SS'));
477 l_exception_msg := FND_MESSAGE.GET;
478
479
480 BEGIN
481 INSERT INTO WIP_SCHEDULING_EXCEPTIONS
482 (
483 wip_entity_id,
484 organization_id,
485 mesg_sequence,
486 scheduling_source,
487 scheduling_source_id,
488 message_text,
489 message_type,
490 marked_flag,
491 reported_date,
492 last_update_date,
493 creation_date,
494 created_by,
495 last_update_login,
496 last_updated_by,
497 operation_seq_num,
498 resource_seq_num,
499 resource_id,
500 inventory_item_id,
501 instance_id,
502 serial_number,
503 sched_relationship_id,
504 exception_type
505 )
506 VALUES
507 (
508 NULL, --wip_entity_id,
509 NULL, --organization_id,
510 1, --mesg_sequence,
511 NULL, --scheduling_source,
512 NULL,--scheduling_source_id,
513 l_exception_msg, --message_text,
514 NULL, --message_type,
515 NULL, --marked_flag,
516 SYSDATE, --reported_date,
517 SYSDATE, --last_update_date,
518 SYSDATE, --creation_date,
519 -1, --created_by,
520 -1, --last_update_login,
521 -1, --last_updated_by,
522 NULL, --operation_seq_num,
523 NULL, --resource_seq_num,
524 NULL, --resource_id,
525 NULL, --inventory_item_id,
526 NULL, --instance_id,
527 NULL, --serial_number,
528 type_2.sched_relationship_id,
529 2 -- exception_type
530 );
531
532
533 EXCEPTION
534 WHEN OTHERS THEN
535 FND_MSG_PUB.Add_Exc_Msg
536 ( G_PKG_NAME,
537 l_api_name||'('||l_stmt_num||')'
538 );
539 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
540 END;
541
542 l_exception_msg := NULL;
543 FND_MESSAGE.CLEAR;
544
545 end if;
546 END LOOP;
547
548 END IF;
549
550
551 /* Status Flag for the successful Rows */
552 l_stmt_num := 130;
553
554 --fix for 7943516
555
556 UPDATE WIP_SCHED_RELATIONSHIPS WSR
557 SET WSR.RELATIONSHIP_STATUS = 2
558 WHERE WSR.TOP_LEVEL_OBJECT_TYPE_ID = l_top_level_object_type_id
559 AND WSR.TOP_LEVEL_OBJECT_ID = l_top_level_object_id
560 AND WSR.CHILD_OBJECT_ID = l_work_object_id
561 AND WSR.CHILD_OBJECT_TYPE_ID = l_work_object_type_id
562 AND WSR.RELATIONSHIP_STATUS <> 3;
563
564
565 /*UPDATE EAM_WO_RELATIONSHIPS EWR
566 SET EWR.RELATIONSHIP_STATUS = 2
567 WHERE EWR.TOP_LEVEL_OBJECT_TYPE_ID = l_top_level_object_type_id
568 AND EWR.TOP_LEVEL_OBJECT_ID = l_top_level_object_id
569 AND EWR.RELATIONSHIP_STATUS <> 3;*/
570
571 UPDATE EAM_WO_RELATIONSHIPS EWR
572 SET EWR.RELATIONSHIP_STATUS = 2
573 WHERE EWR.TOP_LEVEL_OBJECT_TYPE_ID = l_top_level_object_type_id
574 AND EWR.TOP_LEVEL_OBJECT_ID = l_top_level_object_id
575 AND EWR.CHILD_OBJECT_ID = l_work_object_id
576 AND EWR.CHILD_OBJECT_TYPE_ID = l_work_object_type_id
577 AND EWR.RELATIONSHIP_STATUS <> 3;
578
579 --fix for 7943516
580
581 -- End of API body.
582 -- Standard check of p_commit.
583 IF FND_API.To_Boolean( p_commit ) THEN
584 --dbms_output.put_line('committing');
585 COMMIT WORK;
586 END IF;
587 -- Standard call to get message count and if count is 1, get message info.
588 x_msg_count := FND_MSG_PUB.Count_Msg;
589 x_wo_relationship_exc_tbl := l_wo_relationship_exc_tbl;
590
591 EXCEPTION
592 WHEN FND_API.G_EXC_ERROR THEN
593 x_return_status := FND_API.G_RET_STS_ERROR ;
594 FND_MSG_PUB.Count_And_Get
595 ( p_count => x_msg_count ,
596 p_data => x_msg_data
597 );
598
599 /* Reset Status Flag for the entire structure */
600 UPDATE WIP_SCHED_RELATIONSHIPS WSR
601 SET WSR.RELATIONSHIP_STATUS = 0
602 WHERE WSR.TOP_LEVEL_OBJECT_TYPE_ID = l_top_level_object_type_id
603 AND WSR.TOP_LEVEL_OBJECT_ID = l_top_level_object_id;
604
605 UPDATE EAM_WO_RELATIONSHIPS EWR
606 SET EWR.RELATIONSHIP_STATUS = 0
607 WHERE EWR.TOP_LEVEL_OBJECT_TYPE_ID = l_top_level_object_type_id
608 AND EWR.TOP_LEVEL_OBJECT_ID = l_top_level_object_id;
609
610 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
611 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
612 FND_MSG_PUB.Count_And_Get
613 (
614 p_count => x_msg_count,
615 p_data => x_msg_data
616 );
617
618 /* Reset Status Flag for the entire structure */
619 UPDATE WIP_SCHED_RELATIONSHIPS WSR
620 SET WSR.RELATIONSHIP_STATUS = 0
621 WHERE WSR.TOP_LEVEL_OBJECT_TYPE_ID = l_top_level_object_type_id
622 AND WSR.TOP_LEVEL_OBJECT_ID = l_top_level_object_id;
623
624 UPDATE EAM_WO_RELATIONSHIPS EWR
625 SET EWR.RELATIONSHIP_STATUS = 0
626 WHERE EWR.TOP_LEVEL_OBJECT_TYPE_ID = l_top_level_object_type_id
627 AND EWR.TOP_LEVEL_OBJECT_ID = l_top_level_object_id;
628
629 WHEN OTHERS THEN
630 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
631 IF FND_MSG_PUB.Check_Msg_Level
632 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
633 THEN
634 FND_MSG_PUB.Add_Exc_Msg
635 ( G_PKG_NAME,
636 l_api_name||'('||l_stmt_num||')'
637 );
638 END IF;
639 FND_MSG_PUB.Count_And_Get
640 ( p_count => x_msg_count,
641 p_data => x_msg_data
642 );
643
644 /* Reset Status Flag for the entire structure */
645 UPDATE WIP_SCHED_RELATIONSHIPS WSR
646 SET WSR.RELATIONSHIP_STATUS = 0
647 WHERE WSR.TOP_LEVEL_OBJECT_TYPE_ID = l_top_level_object_type_id
648 AND WSR.TOP_LEVEL_OBJECT_ID = l_top_level_object_id;
649
650 UPDATE EAM_WO_RELATIONSHIPS EWR
651 SET EWR.RELATIONSHIP_STATUS = 0
652 WHERE EWR.TOP_LEVEL_OBJECT_TYPE_ID = l_top_level_object_type_id
653 AND EWR.TOP_LEVEL_OBJECT_ID = l_top_level_object_id;
654
655 END Validate_Structure;
656
657
658
659 /*******************************************************************
660 * Procedure : Check_Constrained_Children
661 * Returns : None
662 * Parameters IN :
663 * Parameters OUT NOCOPY: Work Object ID, Work Object Type
664 * Mesg Token Table
665 * Return Status
666 * Purpose : This procedure is called to validate that all immediate
667 * children a constraining parent falls within the timespan
668 * of the parent work order. The procedure is called
669 * recurssively to process multilevel structures
670 *********************************************************************/
671
672
673 PROCEDURE Check_Constrained_Children
674 (
675 p_api_version IN NUMBER,
676 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
677 p_commit IN VARCHAR2 := FND_API.G_FALSE,
678 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
679
680 p_parent_object_id IN NUMBER,
681 p_parent_object_type_id IN NUMBER,
682
683
684 x_return_status OUT NOCOPY VARCHAR2,
685 x_msg_count OUT NOCOPY NUMBER,
686 x_msg_data OUT NOCOPY VARCHAR2
687 )
688
689 IS
690 l_api_name CONSTANT VARCHAR2(30) := 'Check_Constrained_Children';
691 l_api_version CONSTANT NUMBER := 1.0;
692
693 l_stmt_num NUMBER;
694 l_parent_object_id NUMBER;
695 l_parent_object_type_id NUMBER;
696 l_released_rowcount NUMBER;
697
698
699 l_return_status VARCHAR2(1);
700 l_msg_count NUMBER;
701 l_msg_data VARCHAR2(1000);
702
703 CURSOR constrained_children_cur (l_p_object NUMBER, l_p_object_type NUMBER) IS
704 SELECT WSR.CHILD_OBJECT_ID,
705 WSR.CHILD_OBJECT_TYPE_ID
706 FROM WIP_SCHED_RELATIONSHIPS WSR
707 WHERE WSR.PARENT_OBJECT_ID = l_p_object
708 AND WSR.PARENT_OBJECT_TYPE_ID = l_p_object_type
709 AND WSR.RELATIONSHIP_TYPE = 1;
710
711
712 BEGIN
713 -- Standard Start of API savepoint
714
715 -- Standard call to check for call compatibility.
716 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
717 p_api_version ,
718 l_api_name ,
719 G_PKG_NAME )
720 THEN
721 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
722 END IF;
723 -- Initialize message list if p_init_msg_list is set to TRUE.
724 IF FND_API.to_Boolean( p_init_msg_list ) THEN
725 FND_MSG_PUB.initialize;
726 END IF;
727 -- Initialize API return status to success
728 x_return_status := FND_API.G_RET_STS_SUCCESS;
729 -- API body
730
731 /* Initialize the local variables */
732 l_stmt_num := 10;
733
734 l_parent_object_type_id := p_parent_object_type_id;
735 l_parent_object_id := p_parent_object_id;
736
737
738 /* Open Cursor for the current parent */
739
740 FOR child IN constrained_children_cur (l_parent_object_id, l_parent_object_type_id)
741 LOOP
742
743 l_stmt_num := 20;
744 --Code in the following conditional block is for replacing original update statements with performance issue
745 --Fix for bug 7660880
746 IF (child.child_object_type_id = 1 AND l_parent_object_type_id = 1) THEN
747 UPDATE WIP_SCHED_RELATIONSHIPS WSR
748 SET WSR.RELATIONSHIP_STATUS = 3
749 WHERE WSR.CHILD_OBJECT_TYPE_ID = child.child_object_type_id
750 AND WSR.CHILD_OBJECT_ID = child.child_object_id
751 AND WSR.PARENT_OBJECT_TYPE_ID = l_parent_object_type_id
752 AND WSR.PARENT_OBJECT_ID = l_parent_object_id
753 AND WSR.RELATIONSHIP_TYPE = 1
754 AND EXISTS (SELECT *
755 FROM WIP_DISCRETE_JOBS WDJ1,
756 WIP_DISCRETE_JOBS WDJ2
757 WHERE WDJ1.WIP_ENTITY_ID = l_parent_object_id
758 AND WDJ2.WIP_ENTITY_ID = child.child_object_id
759 AND (WDJ2.SCHEDULED_START_DATE < WDJ1.SCHEDULED_START_DATE
760 OR
761 WDJ2.SCHEDULED_COMPLETION_DATE > WDJ1.SCHEDULED_COMPLETION_DATE)
762 AND NOT (WDJ2.STATUS_TYPE = 7
763 OR
764 (WDJ2.STATUS_TYPE IN (12,14,15) AND WDJ2.DATE_COMPLETED IS NULL)));
765
766
767 UPDATE EAM_WO_RELATIONSHIPS EWR
768 SET EWR.RELATIONSHIP_STATUS = 3
769 WHERE EWR.CHILD_OBJECT_TYPE_ID = child.child_object_type_id
770 AND EWR.CHILD_OBJECT_ID = child.child_object_id
771 AND EWR.PARENT_OBJECT_TYPE_ID = l_parent_object_type_id
772 AND EWR.PARENT_OBJECT_ID = l_parent_object_id
773 AND EWR.PARENT_RELATIONSHIP_TYPE = 1
774 AND EXISTS (SELECT *
775 FROM WIP_DISCRETE_JOBS WDJ1,
776 WIP_DISCRETE_JOBS WDJ2
777 WHERE WDJ1.WIP_ENTITY_ID = l_parent_object_id
778 AND WDJ2.WIP_ENTITY_ID = child.child_object_id
779 AND (WDJ2.SCHEDULED_START_DATE < WDJ1.SCHEDULED_START_DATE
780 OR
781 WDJ2.SCHEDULED_COMPLETION_DATE > WDJ1.SCHEDULED_COMPLETION_DATE)
782 AND NOT (WDJ2.STATUS_TYPE = 7
783 OR
784 (WDJ2.STATUS_TYPE IN (12,14,15) AND WDJ2.DATE_COMPLETED IS NULL)));
785 END IF;
786
787
788
789 /* *****************************************************************************
790 Check Parent Child Constraints between released work orders for this structure
791 and Raise ERROR Condition. Stop further processing and RETURN
792 Statuses are: 3- Released, 4 - Complete, 5- Complete No Charge
793 6 - On Hold, 12 - Closed, 14- Pending Close, 15 - Failed Close
794 ************************************************************************** */
795
796 IF (SQL%ROWCOUNT > 0) THEN
797 l_stmt_num := 30;
798 l_released_rowcount := 0;
799
800
801 SELECT COUNT(SCHED_RELATIONSHIP_ID)
802 INTO l_released_rowcount
803 FROM WIP_SCHED_RELATIONSHIPS WSR,
804 WIP_DISCRETE_JOBS WDJ
805 WHERE WSR.PARENT_OBJECT_TYPE_ID = 1
806 AND WSR.CHILD_OBJECT_TYPE_ID = 1
807 AND WSR.PARENT_OBJECT_ID = l_parent_object_id
808 AND WSR.PARENT_OBJECT_TYPE_ID = l_parent_object_type_id
809 AND WSR.RELATIONSHIP_TYPE = 1
810 AND WSR.RELATIONSHIP_STATUS = 3
811 AND WDJ.WIP_ENTITY_ID = WSR.CHILD_OBJECT_ID
812 AND WDJ.STATUS_TYPE IN (3,4,5,6,7,12,14,15);
813 -- No Need to Check parent status as Parent will always be released if child is released
814
815 --dbms_output.put_line ('Released Count ='||l_released_rowcount);
816
817
818 IF (l_released_rowcount > 0 ) THEN
819 -- Error between two released work orders
820 x_return_status := FND_API.G_RET_STS_ERROR;
821
822 END IF;
823
824 END IF;
825
826 /* Recursive Call to the validation API */
827 l_stmt_num := 40;
828 EAM_WO_NETWORK_VALIDATE_PVT.Check_Constrained_Children
829 (
830 p_api_version => 1.0,
831 p_parent_object_id => child.child_object_id,
832 p_parent_object_type_id => child.child_object_type_id,
833 x_return_status => l_return_status,
834 x_msg_count => l_msg_count,
835 x_msg_data => l_msg_data
836 );
837
838 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
839 x_return_status := FND_API.G_RET_STS_ERROR;
840
841 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
842 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
843
844 END IF;
845
846
847 END LOOP;
848
849 -- End of API body.
850 -- Standard check of p_commit.
851 IF FND_API.To_Boolean( p_commit ) THEN
852 --dbms_output.put_line('committing');
853 COMMIT WORK;
854 END IF;
855 -- Standard call to get message count and if count is 1, get message info.
856 FND_MSG_PUB.Count_And_Get
857 ( p_count => x_msg_count ,
858 p_data => x_msg_data
859 );
860 EXCEPTION
861 WHEN FND_API.G_EXC_ERROR THEN
862 x_return_status := FND_API.G_RET_STS_ERROR ;
863 FND_MSG_PUB.Count_And_Get
864 ( p_count => x_msg_count ,
865 p_data => x_msg_data
866 );
867
868 RETURN;
869 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
870
871 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
872 FND_MSG_PUB.Count_And_Get
873 (
874 p_count => x_msg_count,
875 p_data => x_msg_data
876 );
877
878 RETURN;
879 WHEN OTHERS THEN
880 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
881 IF FND_MSG_PUB.Check_Msg_Level
882 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
883 THEN
884 FND_MSG_PUB.Add_Exc_Msg
885 ( G_PKG_NAME,
886 l_api_name||'('||l_stmt_num||')'
887 );
888 END IF;
889 FND_MSG_PUB.Count_And_Get
890 ( p_count => x_msg_count,
891 p_data => x_msg_data
892 );
893
894 RETURN;
895 END Check_Constrained_Children;
896
897
898 --fix for 3433757.added procedure validate_status to validate the statuses of parent and child
899 ---------------------------------------------------------------------------------------------
900 -- Valid statuses for parent and child are
901 -- Parent Child
902 -------------------------------------------------------------------------
903 -- Draft Draft,cancelled,on-hold
904 -- Unreleased Draft,Unreleased,cancelled,on-hold
905 -- Released,On-hold Draft,Unreleased,Released,On-hold,Cancelled,Complete,comp-no-chrg,closed,pend-close,failed close
906 -- Cancelled Cancelled,Closed,pend-close,failed close
907 -- Complete,Comp-no-charg,closed, Complete,comp-no-chrg,closed ,Cancelled,pend-close,failed close
908 -- pend-close,failed close
909 ---------------------------------------------------------------------------------------------
910 PROCEDURE Validate_Status
911 (
912 p_work_object_id IN NUMBER,
913 p_work_object_type_id IN NUMBER,
914 x_return_status OUT NOCOPY VARCHAR2
915 )
916 IS
917 l_parent_status NUMBER;
918 l_parent_count NUMBER := 0;
919 l_wo_status NUMBER;
920 l_invalid_child NUMBER:=0;
921 --Added variables l_pending_flag,l_user_defined_status for Bug #5350181.
922 l_pending_flag VARCHAR2(1);
923 l_user_defined_status NUMBER;
924
925 BEGIN
926 x_return_status := FND_API.G_RET_STS_SUCCESS;
927
928 --Bug #5350181 :Changed the query to capture pending flag and user defined status
929 BEGIN
930 SELECT wdj.status_type,ewod.user_defined_status_id,ewod.pending_flag
931 INTO l_wo_status,l_user_defined_status,l_pending_flag
932 FROM wip_discrete_jobs wdj,eam_work_order_details_v ewod
933 WHERE wdj.wip_entity_id=p_work_object_id
934 and ewod.wip_entity_id=wdj.wip_entity_id;
935 EXCEPTION -- added exception for bug 13736177
936 WHEN NO_DATA_FOUND THEN
937 select status_type into l_wo_status
938 from wip_discrete_jobs where wip_entity_id = p_work_object_id;
939 l_user_defined_status := l_wo_status;
940 l_pending_flag := null;
941 END;
942
943 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('workorder status'||l_wo_status); END IF;
944
945 BEGIN
946 SELECT wdj.status_type
947 INTO l_parent_status
948 FROM wip_discrete_jobs wdj,wip_sched_relationships wsr
949 WHERE wsr.child_object_id =p_work_object_id
950 and wsr.child_object_type_id = p_work_object_type_id
951 and wsr.relationship_type = 1
952 and wdj.wip_entity_id = wsr.parent_object_id;
953
954
955
956 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('inside parent status validation :parent status'||l_parent_status); END IF;
957
958 IF( ((l_parent_status=17) and (l_wo_status NOT IN (17,7,6)))
959 OR ((l_parent_status=1) and (l_wo_status NOT IN (17,7,1,6)))
960 OR ((l_parent_status IN (3,6)) and (l_wo_status NOT IN (3,6,17,7,1,12,14,15,4,5)))
961 OR ((l_parent_status=7) and (l_wo_status NOT IN (7,12,14,15)))
962 OR ((l_parent_status IN (4,5,12,14,15)) and (l_wo_status NOT IN (4,5,12,14,15,7)))
963 -- Bug #5350181: A child work order can't be sent for release approval when is parent is not in released status.
964 OR ((l_parent_status NOT IN (3,6))and (l_user_defined_status IN(3) and l_pending_flag='Y'))
965 ) THEN
966 x_return_status:=FND_API.G_RET_STS_ERROR;
967 END IF;
968 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('after parent status validation :'||x_return_status); END IF;
969
970 EXCEPTION
971 WHEN NO_DATA_FOUND THEN
972 null;
973 END;
974
975 l_invalid_child := 0;
976
977 IF(l_wo_status=17) THEN
978 SELECT COUNT(*)
979 INTO l_invalid_child
980 FROM wip_discrete_jobs wdj,wip_sched_relationships wsr
981 WHERE wsr.parent_object_id=p_work_object_id
982 AND wsr.parent_object_type_id= p_work_object_type_id
983 AND wsr.child_object_type_id=p_work_object_type_id
984 AND wsr.child_object_id=wdj.wip_entity_id
985 AND wsr.relationship_type = 1
986 AND wdj.status_type NOT IN (17,7,6);
987 ELSIF(l_wo_status=1) THEN
988 SELECT COUNT(*)
989 INTO l_invalid_child
990 FROM wip_discrete_jobs wdj,wip_sched_relationships wsr
991 WHERE wsr.parent_object_id=p_work_object_id
992 AND wsr.parent_object_type_id= p_work_object_type_id
993 AND wsr.child_object_type_id=p_work_object_type_id
994 AND wsr.child_object_id=wdj.wip_entity_id
995 AND wsr.relationship_type = 1
996 AND wdj.status_type NOT IN (17,7,1,6);
997 ELSIF(l_wo_status IN (3,6)) THEN
998 SELECT COUNT(*)
999 INTO l_invalid_child
1000 FROM wip_discrete_jobs wdj,wip_sched_relationships wsr
1001 WHERE wsr.parent_object_id=p_work_object_id
1002 AND wsr.parent_object_type_id= p_work_object_type_id
1003 AND wsr.child_object_type_id=p_work_object_type_id
1004 AND wsr.child_object_id=wdj.wip_entity_id
1005 AND wsr.relationship_type = 1
1006 AND wdj.status_type NOT IN (3,6,17,7,1,12,14,15,4,5);
1007 ELSIF(l_wo_status=7) THEN
1008 SELECT COUNT(*)
1009 INTO l_invalid_child
1010 FROM wip_discrete_jobs wdj,wip_sched_relationships wsr
1011 WHERE wsr.parent_object_id=p_work_object_id
1012 AND wsr.parent_object_type_id= p_work_object_type_id
1013 AND wsr.child_object_type_id=p_work_object_type_id
1014 AND wsr.child_object_id=wdj.wip_entity_id
1015 AND wsr.relationship_type = 1
1016 AND wdj.status_type NOT IN (7,12,14,15);
1017 ELSIF(l_wo_status IN (4,5,12,14,15)) THEN
1018 SELECT COUNT(*)
1019 INTO l_invalid_child
1020 FROM wip_discrete_jobs wdj,wip_sched_relationships wsr
1021 WHERE wsr.parent_object_id=p_work_object_id
1022 AND wsr.parent_object_type_id= p_work_object_type_id
1023 AND wsr.child_object_type_id=p_work_object_type_id
1024 AND wsr.child_object_id=wdj.wip_entity_id
1025 AND wsr.relationship_type = 1
1026 AND wdj.status_type NOT IN (4,5,12,14,15,7);
1027 END IF;
1028
1029 IF(l_invalid_child<>0) THEN
1030 x_return_status:=FND_API.G_RET_STS_ERROR;
1031 END IF;
1032
1033 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('after child status validation :'||x_return_status); END IF;
1034 END Validate_Status;
1035
1036 -- Added for Detailed Scheduling.Validates the status of entire hierarchy
1037 PROCEDURE Validate_Network_Status
1038 (
1039 p_work_object_id IN NUMBER,
1040 p_work_object_type_id IN NUMBER,
1041 p_wo_relationship_exc_tbl IN OUT NOCOPY EAM_PROCESS_WO_PUB.wo_relationship_exc_tbl_type
1042 )
1043 IS
1044 l_wo_status NUMBER;
1045 l_wo_status_meaning VARCHAR2(80);
1046 l_exception_msg VARCHAR2(1000);
1047 x_return_status VARCHAR2(1);
1048
1049 TYPE wip_entity_id_tbl_type is TABLE OF number INDEX BY BINARY_INTEGER;
1050 TYPE workorder_status_tbl_type is TABLE OF varchar2(1000) INDEX BY BINARY_INTEGER;
1051
1052 l_WipEntityId_tbl wip_entity_id_tbl_type;
1053 l_workorder_status_tbl workorder_status_tbl_type;
1054
1055 CURSOR constrained_children_cur (l_p_object NUMBER, l_p_object_type NUMBER) IS
1056 SELECT WSR.CHILD_OBJECT_ID,
1057 WSR.CHILD_OBJECT_TYPE_ID
1058 FROM WIP_SCHED_RELATIONSHIPS WSR
1059 WHERE WSR.PARENT_OBJECT_ID = l_p_object
1060 AND WSR.PARENT_OBJECT_TYPE_ID = l_p_object_type
1061 AND WSR.RELATIONSHIP_TYPE = 1;
1062
1063 CURSOR get_status(p_work_object_id NUMBER) IS
1064 SELECT work_order_status
1065 FROM eam_work_order_details ewod,eam_wo_statuses_v ewsv
1066 WHERE ewod.wip_entity_id = p_work_object_id
1067 AND ewod.user_defined_status_id = ewsv.status_id;
1068 BEGIN
1069
1070 x_return_status := FND_API.G_RET_STS_SUCCESS;
1071
1072 OPEN get_status(p_work_object_id);
1073 FETCH get_status INTO l_wo_status_meaning ;
1074 CLOSE get_status;
1075
1076 l_WipEntityId_tbl.delete;
1077 l_workorder_status_tbl.delete;
1078
1079 IF(l_wo_status=17) THEN
1080 SELECT wsr.child_object_id,lk.meaning BULK COLLECT INTO
1081 l_WipEntityId_tbl,l_workorder_status_tbl
1082 FROM wip_discrete_jobs wdj,wip_sched_relationships wsr,mfg_lookups lk
1083 WHERE wsr.parent_object_id=p_work_object_id
1084 AND wsr.parent_object_type_id= p_work_object_type_id
1085 AND wsr.child_object_type_id=p_work_object_type_id
1086 AND wsr.child_object_id=wdj.wip_entity_id
1087 AND wsr.relationship_type = 1
1088 AND wdj.status_type NOT IN (17,7,6)
1089 AND lk.lookup_type = 'WIP_JOB_STATUS'
1090 AND lk.lookup_code = wdj.status_type;
1091 ELSIF(l_wo_status=1) THEN
1092 SELECT wsr.child_object_id,lk.meaning BULK COLLECT INTO
1093 l_WipEntityId_tbl,l_workorder_status_tbl
1094 FROM wip_discrete_jobs wdj,wip_sched_relationships wsr,mfg_lookups lk
1095 WHERE wsr.parent_object_id=p_work_object_id
1096 AND wsr.parent_object_type_id= p_work_object_type_id
1097 AND wsr.child_object_type_id=p_work_object_type_id
1098 AND wsr.child_object_id=wdj.wip_entity_id
1099 AND wsr.relationship_type = 1
1100 AND wdj.status_type NOT IN (17,7,1,6)
1101 AND lk.lookup_type = 'WIP_JOB_STATUS'
1102 AND lk.lookup_code = wdj.status_type;
1103 ELSIF(l_wo_status IN (3,6)) THEN
1104 SELECT wsr.child_object_id,lk.meaning BULK COLLECT INTO
1105 l_WipEntityId_tbl,l_workorder_status_tbl
1106 FROM wip_discrete_jobs wdj,wip_sched_relationships wsr,mfg_lookups lk
1107 WHERE wsr.parent_object_id=p_work_object_id
1108 AND wsr.parent_object_type_id= p_work_object_type_id
1109 AND wsr.child_object_type_id=p_work_object_type_id
1110 AND wsr.child_object_id=wdj.wip_entity_id
1111 AND wsr.relationship_type = 1
1112 AND wdj.status_type NOT IN (3,6,17,7,1,12,14,15,4,5)
1113 AND lk.lookup_type = 'WIP_JOB_STATUS'
1114 AND lk.lookup_code = wdj.status_type;
1115 ELSIF(l_wo_status=7) THEN
1116 SELECT wsr.child_object_id,lk.meaning BULK COLLECT INTO
1117 l_WipEntityId_tbl,l_workorder_status_tbl
1118 FROM wip_discrete_jobs wdj,wip_sched_relationships wsr,mfg_lookups lk
1119 WHERE wsr.parent_object_id=p_work_object_id
1120 AND wsr.parent_object_type_id= p_work_object_type_id
1121 AND wsr.child_object_type_id=p_work_object_type_id
1122 AND wsr.child_object_id=wdj.wip_entity_id
1123 AND wsr.relationship_type = 1
1124 AND wdj.status_type NOT IN (7,12,14,15)
1125 AND lk.lookup_type = 'WIP_JOB_STATUS'
1126 AND lk.lookup_code = wdj.status_type;
1127 ELSIF(l_wo_status IN (4,5,12,14,15)) THEN
1128 SELECT wsr.child_object_id,lk.meaning BULK COLLECT INTO
1129 l_WipEntityId_tbl,l_workorder_status_tbl
1130 FROM wip_discrete_jobs wdj,wip_sched_relationships wsr,mfg_lookups lk
1131 WHERE wsr.parent_object_id=p_work_object_id
1132 AND wsr.parent_object_type_id= p_work_object_type_id
1133 AND wsr.child_object_type_id=p_work_object_type_id
1134 AND wsr.child_object_id=wdj.wip_entity_id
1135 AND wsr.relationship_type = 1
1136 AND wdj.status_type NOT IN (4,5,12,14,15,7)
1137 AND lk.lookup_type = 'WIP_JOB_STATUS'
1138 AND lk.lookup_code = wdj.status_type;
1139 END IF;
1140
1141 IF l_WipEntityId_tbl.COUNT > 0 THEN
1142 FOR tbl_counter IN l_WipEntityId_tbl.FIRST..l_WipEntityId_tbl.LAST LOOP
1143 FND_MESSAGE.SET_NAME('EAM','EAM_WO_REL_STATUS_ERROR');
1144 FND_MESSAGE.SET_TOKEN('PARENT_JOB',p_work_object_id);
1145 FND_MESSAGE.SET_TOKEN('PARENT_STATUS',l_wo_status_meaning);
1146 FND_MESSAGE.SET_TOKEN('CHILD_JOB',l_WipEntityId_tbl(tbl_counter));
1147 FND_MESSAGE.SET_TOKEN('CHILD_STATUS',l_workorder_status_tbl(tbl_counter));
1148 l_exception_msg := FND_MESSAGE.GET;
1149
1150 IF p_wo_relationship_exc_tbl.COUNT =0 Then
1151 p_wo_relationship_exc_tbl(1) :=l_exception_msg;
1152 ELSE
1153 p_wo_relationship_exc_tbl(p_wo_relationship_exc_tbl.LAST+1) :=l_exception_msg;
1154 END if;
1155 END LOOP;
1156 END IF;
1157
1158 FOR child IN constrained_children_cur(p_work_object_id, p_work_object_type_id)
1159 LOOP
1160 EAM_WO_NETWORK_VALIDATE_PVT.Validate_Network_Status
1161 (p_work_object_id => child.child_object_id,
1162 p_work_object_type_id => child.child_object_type_id,
1163 p_wo_relationship_exc_tbl => p_wo_relationship_exc_tbl
1164 );
1165 END LOOP;
1166
1167 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN
1168 IF p_wo_relationship_exc_tbl.count >0 THEN
1169 x_return_status:=FND_API.G_RET_STS_ERROR;
1170 END IF;
1171 EAM_ERROR_MESSAGE_PVT.Write_Debug('after Validate_Network_Status status validation :'||x_return_status);
1172 END IF;
1173
1174 END Validate_Network_Status;
1175
1176
1177 END EAM_WO_NETWORK_VALIDATE_PVT;