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