[Home] [Help]
PACKAGE BODY: APPS.WIP_WORK_ORDER_PVT
Source
1 PACKAGE BODY WIP_Work_Order_PVT AS
2 /* $Header: WIPVWORB.pls 115.9 2002/12/01 16:32:07 rmahidha ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WIP_Work_Order_PVT';
7
8
9 -- Start of Comments
10 -- API name Lock_Work_Order
11 -- Type Private
12 -- Function
13 --
14 -- Pre-reqs
15 --
16 -- Parameters
17 --
18 -- Version Current version = 1.0
19 -- Initial version = 1.0
20 --
21 -- Notes
22 --
23 -- End of Comments
24
25 PROCEDURE Lock_Work_Order
26 ( p_api_version_number IN NUMBER
27 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
28 , x_return_status OUT NOCOPY VARCHAR2
29 , x_msg_count OUT NOCOPY NUMBER
30 , x_msg_data OUT NOCOPY VARCHAR2
31 , p_Wip_Entities_tbl IN WIP_Work_Order_PUB.Wip_Entities_Tbl_Type :=
32 WIP_Work_Order_PUB.G_MISS_WIP_ENTITIES_TBL
33 , p_FlowSchedule_tbl IN WIP_Work_Order_PUB.Flowschedule_Tbl_Type :=
34 WIP_Work_Order_PUB.G_MISS_FLOWSCHEDULE_TBL
35 , p_DiscreteJob_tbl IN WIP_Work_Order_PUB.Discretejob_Tbl_Type :=
36 WIP_Work_Order_PUB.G_MISS_DISCRETEJOB_TBL
37 , p_RepSchedule_tbl IN WIP_Work_Order_PUB.Repschedule_Tbl_Type :=
38 WIP_Work_Order_PUB.G_MISS_REPSCHEDULE_TBL
39 , x_Wip_Entities_tbl IN OUT NOCOPY WIP_Work_Order_PUB.Wip_Entities_Tbl_Type
40 , x_FlowSchedule_tbl IN OUT NOCOPY WIP_Work_Order_PUB.Flowschedule_Tbl_Type
41 , x_DiscreteJob_tbl IN OUT NOCOPY WIP_Work_Order_PUB.Discretejob_Tbl_Type
42 , x_RepSchedule_tbl IN OUT NOCOPY WIP_Work_Order_PUB.Repschedule_Tbl_Type
43 )
44 IS
45 l_api_version_number CONSTANT NUMBER := 1.0;
46 l_api_name CONSTANT VARCHAR2(30):= 'Lock_Work_Order';
47 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
48 l_Wip_Entities_rec WIP_Work_Order_PUB.Wip_Entities_Rec_Type;
49 l_FlowSchedule_rec WIP_Work_Order_PUB.Flowschedule_Rec_Type;
50 l_DiscreteJob_rec WIP_Work_Order_PUB.Discretejob_Rec_Type;
51 l_RepSchedule_rec WIP_Work_Order_PUB.Repschedule_Rec_Type;
52 BEGIN
53
54 -- Standard call to check for call compatibility
55
56 IF NOT FND_API.Compatible_API_Call
57 ( l_api_version_number
58 , p_api_version_number
59 , l_api_name
60 , G_PKG_NAME
61 )
62 THEN
63 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
64 END IF;
65
66 -- Initialize message list.
67
68 IF FND_API.to_Boolean(p_init_msg_list) THEN
69 FND_MSG_PUB.initialize;
70 END IF;
71
72 -- Set Savepoint
73
74 SAVEPOINT Lock_Work_Order_PVT;
75
76 -- Lock Wip_Entities
77
78 FOR I IN 1..p_Wip_Entities_tbl.COUNT LOOP
79
80 IF p_Wip_Entities_tbl(I).action = WIP_GLOBALS.G_OPR_LOCK THEN
81
82 WIP_Wip_Entities_Util.Lock_Row
83 ( p_Wip_Entities_rec => p_Wip_Entities_tbl(I)
84 , x_Wip_Entities_rec => l_Wip_Entities_rec
85 , x_return_status => l_return_status
86 );
87
88 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
89 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
90 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
91 RAISE FND_API.G_EXC_ERROR;
92 END IF;
93
94 x_Wip_Entities_tbl(I) := l_Wip_Entities_rec;
95
96 END IF;
97
98 END LOOP;
99
100 -- Lock FlowSchedule
101
102 FOR I IN 1..p_FlowSchedule_tbl.COUNT LOOP
103
104 IF p_FlowSchedule_tbl(I).action = WIP_GLOBALS.G_OPR_LOCK THEN
105
106 WIP_Flowschedule_Util.Lock_Row
107 ( p_FlowSchedule_rec => p_FlowSchedule_tbl(I)
108 , x_FlowSchedule_rec => l_FlowSchedule_rec
109 , x_return_status => l_return_status
110 );
111
112 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
113 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
114 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
115 RAISE FND_API.G_EXC_ERROR;
116 END IF;
117
118 x_FlowSchedule_tbl(I) := l_FlowSchedule_rec;
119
120 END IF;
121
122 END LOOP;
123
124 -- Lock DiscreteJob
125
126 FOR I IN 1..p_DiscreteJob_tbl.COUNT LOOP
127
128 IF p_DiscreteJob_tbl(I).action = WIP_GLOBALS.G_OPR_LOCK THEN
129
130 WIP_Discretejob_Util.Lock_Row
131 ( p_DiscreteJob_rec => p_DiscreteJob_tbl(I)
132 , x_DiscreteJob_rec => l_DiscreteJob_rec
133 , x_return_status => l_return_status
134 );
135
136 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
137 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
138 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
139 RAISE FND_API.G_EXC_ERROR;
140 END IF;
141
142 x_DiscreteJob_tbl(I) := l_DiscreteJob_rec;
143
144 END IF;
145
146 END LOOP;
147
148 -- Lock RepSchedule
149
150 FOR I IN 1..p_RepSchedule_tbl.COUNT LOOP
151
152 IF p_RepSchedule_tbl(I).action = WIP_GLOBALS.G_OPR_LOCK THEN
153
154 WIP_Repschedule_Util.Lock_Row
155 ( p_RepSchedule_rec => p_RepSchedule_tbl(I)
156 , x_RepSchedule_rec => l_RepSchedule_rec
157 , x_return_status => l_return_status
158 );
159
160 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
161 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
162 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
163 RAISE FND_API.G_EXC_ERROR;
164 END IF;
165
166 x_RepSchedule_tbl(I) := l_RepSchedule_rec;
167
168 END IF;
169
170 END LOOP;
171
172 -- Set return status
173
174 x_return_status := FND_API.G_RET_STS_SUCCESS;
175
176 -- Get message count and data
177
178 FND_MSG_PUB.Count_And_Get
179 ( p_count => x_msg_count
180 , p_data => x_msg_data
181 );
182
183
184 EXCEPTION
185
186 WHEN FND_API.G_EXC_ERROR THEN
187
188 x_return_status := FND_API.G_RET_STS_ERROR;
189
190 -- Get message count and data
191
192 FND_MSG_PUB.Count_And_Get
193 ( p_count => x_msg_count
194 , p_data => x_msg_data
195 );
196
197 -- Rollback
198
199 ROLLBACK TO Lock_Work_Order_PVT;
200
201 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
202
203 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
204
205 -- Get message count and data
206
207 FND_MSG_PUB.Count_And_Get
208 ( p_count => x_msg_count
209 , p_data => x_msg_data
210 );
211
212 -- Rollback
213
214 ROLLBACK TO Lock_Work_Order_PVT;
215
216 WHEN OTHERS THEN
217
218 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
219
220 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
221 THEN
222 FND_MSG_PUB.Add_Exc_Msg
223 ( G_PKG_NAME
224 , 'Lock_Work_Order'
225 );
226 END IF;
227
228 -- Get message count and data
229
230 FND_MSG_PUB.Count_And_Get
231 ( p_count => x_msg_count
232 , p_data => x_msg_data
233 );
234
235 -- Rollback
236
237 ROLLBACK TO Lock_Work_Order_PVT;
238
239 END Lock_Work_Order;
240
241 -- Start of Comments
242 -- API name Get_Work_Order
243 -- Type Private
244 -- Function
245 --
246 -- Pre-reqs
247 --
248 -- Parameters
249 --
250 -- Version Current version = 1.0
251 -- Initial version = 1.0
252 --
253 -- Notes
254 --
255 -- End of Comments
256
257 PROCEDURE Get_Work_Order
258 ( p_api_version_number IN NUMBER
259 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
260 , x_return_status OUT NOCOPY VARCHAR2
261 , x_msg_count OUT NOCOPY NUMBER
262 , x_msg_data OUT NOCOPY VARCHAR2
263 , p_wip_entity_id IN NUMBER
264 , x_Wip_Entities_tbl OUT NOCOPY WIP_Work_Order_PUB.Wip_Entities_Tbl_Type
265 , x_FlowSchedule_tbl OUT NOCOPY WIP_Work_Order_PUB.Flowschedule_Tbl_Type
266 , x_DiscreteJob_tbl OUT NOCOPY WIP_Work_Order_PUB.Discretejob_Tbl_Type
267 , x_RepSchedule_tbl OUT NOCOPY WIP_Work_Order_PUB.Repschedule_Tbl_Type
268 )
269 IS
270 l_api_version_number CONSTANT NUMBER := 1.0;
271 l_api_name CONSTANT VARCHAR2(30):= 'Get_Work_Order';
272 l_Wip_Entities_rec WIP_Work_Order_PUB.Wip_Entities_Rec_Type;
273 l_Wip_Entities_tbl WIP_Work_Order_PUB.Wip_Entities_Tbl_Type;
274 l_FlowSchedule_tbl WIP_Work_Order_PUB.Flowschedule_Tbl_Type;
275 l_x_FlowSchedule_tbl WIP_Work_Order_PUB.Flowschedule_Tbl_Type;
276 l_DiscreteJob_tbl WIP_Work_Order_PUB.Discretejob_Tbl_Type;
277 l_x_DiscreteJob_tbl WIP_Work_Order_PUB.Discretejob_Tbl_Type;
278 l_RepSchedule_tbl WIP_Work_Order_PUB.Repschedule_Tbl_Type;
279 l_x_RepSchedule_tbl WIP_Work_Order_PUB.Repschedule_Tbl_Type;
280 BEGIN
281
282 -- Standard call to check for call compatibility
283
284 IF NOT FND_API.Compatible_API_Call
285 ( l_api_version_number
286 , p_api_version_number
287 , l_api_name
288 , G_PKG_NAME
289 )
290 THEN
291 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
292 END IF;
293
294 -- Initialize message list.
295
296 IF FND_API.to_Boolean(p_init_msg_list) THEN
297 FND_MSG_PUB.initialize;
298 END IF;
299
300 -- Get Wip_Entities ( parent = Wip_Entities )
301 -- Goofed up !!!!!
302
303 l_Wip_Entities_rec := WIP_Wip_Entities_Util.Query_Row
304 ( p_wip_entity_id => p_wip_entity_id
305 );
306
307 -- Loop over Wip_Entities's children
308
309 FOR I1 IN 1..l_Wip_Entities_tbl.COUNT LOOP
310
311 -- Get FlowSchedule ( parent = Wip_Entities )
312
313 l_FlowSchedule_tbl := WIP_Flowschedule_Util.Query_Rows
314 ( p_wip_entity_id => l_Wip_Entities_tbl(I1).wip_entity_id
315 );
316
317 FOR I2 IN 1..l_FlowSchedule_tbl.COUNT LOOP
318 l_FlowSchedule_tbl(I2).Wip_Entities_Index := I1;
319 l_x_FlowSchedule_tbl
320 (l_x_FlowSchedule_tbl.COUNT + 1) := l_FlowSchedule_tbl(I2);
321 END LOOP;
322
323
324 -- Get DiscreteJob ( parent = Wip_Entities )
325
326 l_DiscreteJob_tbl := WIP_Discretejob_Util.Query_Rows
327 ( p_wip_entity_id => l_Wip_Entities_tbl(I1).wip_entity_id
328 );
329
330 FOR I2 IN 1..l_DiscreteJob_tbl.COUNT LOOP
331 l_DiscreteJob_tbl(I2).Wip_Entities_Index := I1;
332 l_x_DiscreteJob_tbl
333 (l_x_DiscreteJob_tbl.COUNT + 1) := l_DiscreteJob_tbl(I2);
334 END LOOP;
335
336
337 -- Get RepSchedule ( parent = Wip_Entities )
338
339 l_RepSchedule_tbl := WIP_Repschedule_Util.Query_Rows
340 ( p_wip_entity_id => l_Wip_Entities_tbl(I1).wip_entity_id
341 );
342
343 FOR I2 IN 1..l_RepSchedule_tbl.COUNT LOOP
344 l_RepSchedule_tbl(I2).Wip_Entities_Index := I1;
345 l_x_RepSchedule_tbl
346 (l_x_RepSchedule_tbl.COUNT + 1) := l_RepSchedule_tbl(I2);
347 END LOOP;
348
349
350 END LOOP;
351
352
353 -- Load out parameters
354
355 x_Wip_Entities_tbl := l_Wip_Entities_tbl;
356 x_FlowSchedule_tbl := l_x_FlowSchedule_tbl;
357 x_DiscreteJob_tbl := l_x_DiscreteJob_tbl;
358 x_RepSchedule_tbl := l_x_RepSchedule_tbl;
359
360 -- Set return status
361
362 x_return_status := FND_API.G_RET_STS_SUCCESS;
363
364 -- Get message count and data
365
366 FND_MSG_PUB.Count_And_Get
367 ( p_count => x_msg_count
368 , p_data => x_msg_data
369 );
370
371
372 EXCEPTION
373
374 WHEN FND_API.G_EXC_ERROR THEN
375
376 x_return_status := FND_API.G_RET_STS_ERROR;
377
378 -- Get message count and data
379
380 FND_MSG_PUB.Count_And_Get
381 ( p_count => x_msg_count
382 , p_data => x_msg_data
383 );
384
385 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
386
387 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
388
389 -- Get message count and data
390
391 FND_MSG_PUB.Count_And_Get
392 ( p_count => x_msg_count
393 , p_data => x_msg_data
394 );
395
396 WHEN OTHERS THEN
397
398 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
399
400 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
401 THEN
402 FND_MSG_PUB.Add_Exc_Msg
403 ( G_PKG_NAME
404 , 'Get_Work_Order'
405 );
406 END IF;
407
408 -- Get message count and data
409
410 FND_MSG_PUB.Count_And_Get
411 ( p_count => x_msg_count
412 , p_data => x_msg_data
413 );
414
415 END Get_Work_Order;
416
417
418 PROCEDURE Get_Work_Order
419 ( p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
420 , x_return_status OUT NOCOPY VARCHAR2
421 , x_msg_count OUT NOCOPY NUMBER
422 , x_msg_data OUT NOCOPY VARCHAR2
423 , p_wip_entity_id IN NUMBER
424 , x_FlowSchedule_rec OUT NOCOPY WIP_Work_Order_PUB.Flowschedule_Rec_Type
425 , x_DiscreteJob_rec OUT NOCOPY WIP_Work_Order_PUB.Discretejob_Rec_Type
426 , x_RepSchedule_rec OUT NOCOPY WIP_Work_Order_PUB.Repschedule_Rec_Type
427 )
428 IS
429 l_api_name CONSTANT VARCHAR2(30):= 'Get_Work_Order';
430 l_Wip_Entities_rec WIP_Work_Order_PUB.Wip_Entities_Rec_Type:=WIP_Work_Order_PUB.G_MISS_WIP_ENTITIES_REC;
431 l_FlowSchedule_rec WIP_Work_Order_PUB.Flowschedule_Rec_Type:=WIP_Work_Order_PUB.G_MISS_FLOWSCHEDULE_REC;
432 l_DiscreteJob_rec WIP_Work_Order_PUB.Discretejob_Rec_Type:=WIP_Work_Order_PUB.G_MISS_DISCRETEJOB_REC;
433 l_RepSchedule_rec WIP_Work_Order_PUB.Repschedule_Rec_Type:=WIP_Work_Order_PUB.G_MISS_REPSCHEDULE_REC;
434 BEGIN
435
436 -- Initialize message list.
437
438 IF FND_API.to_Boolean(p_init_msg_list) THEN
439 FND_MSG_PUB.initialize;
440 END IF;
441
442 l_Wip_Entities_rec := WIP_Wip_Entities_Util.Query_Row
443 ( p_wip_entity_id => p_wip_entity_id
444 );
445
446 IF l_wip_entities_rec.entity_type = FND_API.G_MISS_NUM THEN
447 RAISE FND_API.G_EXC_ERROR;
448 END IF;
449
450 -- Loop over Wip_Entities's children
451
452 IF l_wip_entities_rec.entity_type = 1 THEN
453 l_DiscreteJob_rec := WIP_Discretejob_Util.Query_Row
454 ( p_wip_entity_id => p_wip_entity_id
455 );
456 ELSIF l_wip_entities_rec.entity_type = 2 THEN
457 l_RepSchedule_rec := WIP_Repschedule_Util.Query_Row
458 ( p_wip_entity_id => p_wip_entity_id
459 );
460 ELSIF l_wip_entities_rec.entity_type = 4 THEN
461 l_FlowSchedule_rec := WIP_Flowschedule_Util.Query_Row
462 ( p_wip_entity_id => p_wip_entity_id
463 );
464 ELSE
465 RAISE FND_API.G_EXC_ERROR;
466 END IF;
467
468 x_FlowSchedule_rec := l_FlowSchedule_rec;
469 x_DiscreteJob_rec := l_DiscreteJob_rec;
470 x_RepSchedule_rec := l_RepSchedule_rec;
471
472 -- Set return status
473
474 x_return_status := FND_API.G_RET_STS_SUCCESS;
475
476 -- Get message count and data
477
478 FND_MSG_PUB.Count_And_Get
479 ( p_count => x_msg_count
480 , p_data => x_msg_data
481 );
482
483
484 EXCEPTION
485
486 WHEN FND_API.G_EXC_ERROR THEN
487
488 x_return_status := FND_API.G_RET_STS_ERROR;
489
490 -- Get message count and data
491
492 FND_MSG_PUB.Count_And_Get
493 ( p_count => x_msg_count
494 , p_data => x_msg_data
495 );
496
497 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
498
499 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
500
501 -- Get message count and data
502
503 FND_MSG_PUB.Count_And_Get
504 ( p_count => x_msg_count
505 , p_data => x_msg_data
506 );
507
508 WHEN OTHERS THEN
509
510 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
511
512 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
513 THEN
514 FND_MSG_PUB.Add_Exc_Msg
515 ( G_PKG_NAME
516 , 'Get_Work_Order'
517 );
518 END IF;
519
520 -- Get message count and data
521
522 FND_MSG_PUB.Count_And_Get
523 ( p_count => x_msg_count
524 , p_data => x_msg_data
525 );
526
527 END Get_Work_Order;
528
529 PROCEDURE Get_Kanban_Details( p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
530 ,p_wip_entity_id IN NUMBER := FND_API.G_MISS_NUM
531 ,kanban_card_id IN OUT NOCOPY NUMBER
532 ,x_kanban_card_number OUT NOCOPY VARCHAR2
533 ,x_subinventory OUT NOCOPY VARCHAR2
534 ,x_locator_id OUT NOCOPY NUMBER
535 ,x_line_id OUT NOCOPY NUMBER
536 ,x_supply_status OUT NOCOPY NUMBER
537 ,x_return_status OUT NOCOPY VARCHAR2
538 ,x_msg_count OUT NOCOPY NUMBER
539 ,x_msg_data OUT NOCOPY VARCHAR2
540 )
541 IS
542 l_FlowSchedule_rec WIP_Work_Order_PUB.Flowschedule_Rec_Type;
543 l_DiscreteJob_rec WIP_Work_Order_PUB.Discretejob_Rec_Type;
544 l_RepSchedule_rec WIP_Work_Order_PUB.Repschedule_Rec_Type;
545 l_kanban_rec INV_Kanban_PVT.Kanban_Card_Rec_Type;
546 l_return_status VARCHAR2(1);
547 l_msg_count NUMBER;
548 l_msg_data VARCHAR2(1000);
549 l_locator_control NUMBER;
550
551 BEGIN
552
553
554 x_kanban_card_number := NULL;
555 x_subinventory := NULL;
556 x_locator_id := NULL;
557 x_line_id := NULL;
558 x_supply_status := NULL;
559
560 -- Initialize message list.
561
562 IF FND_API.to_Boolean(p_init_msg_list) THEN
563 FND_MSG_PUB.initialize;
564 END IF;
565
566 -- Initialize the kanban_card_id only if deriving from wip_entity_id
567
568 IF Nvl(kanban_card_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
569 AND
570 Nvl(p_wip_entity_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
571 THEN
572 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
573 THEN
574 FND_MSG_PUB.Add_Exc_Msg
575 ( G_PKG_NAME
576 , 'Get Kanban Details'
577 , 'Keys are mutually exclusive: wip_entity_id = '|| p_wip_entity_id || ', kanban_card_id = '|| kanban_card_id
578 );
579 END IF;
580 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
581
582 ELSIF Nvl(p_wip_entity_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
583 kanban_card_id := NULL;
584
585 ELSIF Nvl(kanban_card_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
586 l_kanban_rec.kanban_card_id := kanban_card_id;
587
588 ELSE
589 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
590 THEN
591 FND_MSG_PUB.Add_Exc_Msg
592 ( G_PKG_NAME
593 , 'Get Kanban Details'
594 , 'Either wip_entity_id or kanban_card_id has to be provided'
595 );
596 END IF;
597 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
598
599 END IF;
600
601
602 IF Nvl(p_wip_entity_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
603
604 Get_Work_Order(
605 p_init_msg_list => FND_API.G_TRUE
606 ,x_return_status => x_return_status
607 ,x_msg_count => x_msg_count
608 ,x_msg_data => x_msg_data
609 ,p_wip_entity_id => p_wip_entity_id
610 ,x_FlowSchedule_rec => l_FlowSchedule_rec
611 ,x_DiscreteJob_rec => l_DiscreteJob_rec
612 ,x_RepSchedule_rec => l_RepSchedule_rec);
613
614 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
615 RAISE FND_API.G_EXC_ERROR;
616 END IF;
617
618 IF l_FlowSchedule_rec.kanban_card_id <> FND_API.G_MISS_NUM THEN
619 l_kanban_rec.kanban_card_id := l_FlowSchedule_rec.kanban_card_id;
620 ELSIF l_DiscreteJob_rec.kanban_card_id <> FND_API.G_MISS_NUM THEN
621 l_kanban_rec.kanban_card_id := l_DiscreteJob_rec.kanban_card_id;
622 END IF;
623
624 END IF;
625
626
627 IF Nvl(l_kanban_rec.kanban_card_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
628 l_kanban_rec := INV_KanbanCard_PKG.Query_Row(p_kanban_card_id => l_kanban_rec.kanban_card_id);
629 kanban_card_id := l_kanban_rec.kanban_card_id;
630 x_kanban_card_number := l_kanban_rec.kanban_card_number;
631 x_line_id := l_kanban_rec.wip_line_id;
632 x_supply_status := l_kanban_rec.supply_status;
633 x_subinventory := l_kanban_rec.subinventory_name;
634 x_locator_id := l_kanban_rec.locator_id;
635 IF Nvl(x_locator_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
636 Wip_Globals.Get_Locator_Control(l_kanban_rec.organization_id,
637 x_subinventory,
638 l_kanban_rec.inventory_item_id,
639 l_return_status,
640 l_msg_count,
641 l_msg_data,
642 l_locator_control
643 );
644 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
645 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
646 THEN
647 FND_MSG_PUB.Add_Exc_Msg
648 (G_PKG_NAME
649 ,'Get Kanban Details'
650 ,'Get Locator Control was not successful. ' || l_msg_data
651 );
652 END IF;
653 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
654 END IF;
655
656 IF l_locator_control = 1 THEN
657 -- Donot default the locator if there is no locator control.
658 x_locator_id := NULL;
659 END IF;
660 END IF;
661
662 END IF;
663
664
665 x_return_status := FND_API.G_RET_STS_SUCCESS;
666
667 -- Get message count and data
668
669 FND_MSG_PUB.Count_And_Get
670 ( p_count => x_msg_count
671 , p_data => x_msg_data
672 );
673
674
675
676 EXCEPTION
677
678 WHEN FND_API.G_EXC_ERROR THEN
679 x_return_status := FND_API.G_RET_STS_ERROR;
680 -- Get message count and data
681 FND_MSG_PUB.Count_And_Get
682 ( p_count => x_msg_count
683 , p_data => x_msg_data
684 );
685
686 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
687 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
688 -- Get message count and data
689 FND_MSG_PUB.Count_And_Get
690 ( p_count => x_msg_count
691 , p_data => x_msg_data
692 );
693
694 WHEN OTHERS THEN
695 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
696 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
697 THEN
698 FND_MSG_PUB.Add_Exc_Msg
699 ( G_PKG_NAME
700 , 'Get_Kanban_Details'
701 );
702 END IF;
703 -- Get message count and data
704 FND_MSG_PUB.Count_And_Get
705 ( p_count => x_msg_count
706 , p_data => x_msg_data
707 );
708
709 END Get_Kanban_Details;
710
711 PROCEDURE Check_Build_Sequence(p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
712 ,p_wip_entity_id IN NUMBER := FND_API.G_MISS_NUM
713 ,p_organization_id IN NUMBER := FND_API.G_MISS_NUM
714 ,p_build_sequence IN NUMBER := FND_API.G_MISS_NUM
715 ,p_schedule_group_id IN NUMBER := FND_API.G_MISS_NUM
716 ,p_line_id IN NUMBER := FND_API.G_MISS_NUM
717 ,p_FlowSchedule_rec IN WIP_Work_Order_PUB.FlowSchedule_Rec_Type := WIP_Work_Order_PUB.G_MISS_FLOWSCHEDULE_REC
718 ,p_DiscreteJob_rec IN WIP_Work_Order_PUB.Discretejob_Rec_Type := WIP_Work_Order_PUB.G_MISS_DISCRETEJOB_REC
719 ,x_build_seq_valid OUT NOCOPY VARCHAR2
720 ,x_return_status OUT NOCOPY VARCHAR2
721 ,x_msg_count OUT NOCOPY NUMBER
722 ,x_msg_data OUT NOCOPY VARCHAR2)
723 IS
724 l_wip_entity_id NUMBER;
725 l_org_id NUMBER;
726 l_build_seq NUMBER;
727 l_schedule_gp_id NUMBER;
728 l_line_id NUMBER;
729
730 BEGIN
731 x_build_seq_valid := FND_API.G_FALSE;
732 x_msg_count := 0;
733 x_msg_data := NULL;
734
735 -- Initialize message list.
736
737 IF FND_API.to_Boolean(p_init_msg_list) THEN
738 FND_MSG_PUB.initialize;
739 END IF;
740
741 IF p_wip_entity_id <> FND_API.G_MISS_NUM
742 AND p_organization_id <> FND_API.G_MISS_NUM
743 AND (p_build_sequence IS NULL OR p_build_sequence <> FND_API.G_MISS_NUM)
744 AND (p_schedule_group_id IS NULL OR p_schedule_group_id <> FND_API.G_MISS_NUM)
745 AND (p_line_id IS NULL OR p_line_id <> FND_API.G_MISS_NUM)
746 THEN
747 l_wip_entity_id := p_wip_entity_id;
748 l_org_id := p_organization_id;
749 l_build_seq := p_build_sequence;
750 l_schedule_gp_id := p_schedule_group_id;
751 l_line_id := p_line_id;
752 ELSIF p_FlowSchedule_rec.wip_entity_id = FND_API.G_MISS_NUM
753 AND p_DiscreteJob_rec.wip_entity_id <> FND_API.G_MISS_NUM
754 THEN
755 l_wip_entity_id := p_DiscreteJob_rec.wip_entity_id;
756 l_org_id := p_DiscreteJob_rec.organization_id;
757 l_build_seq := p_DiscreteJob_rec.build_sequence;
758 l_schedule_gp_id := p_DiscreteJob_rec.schedule_group_id;
759 l_line_id := p_DiscreteJob_rec.line_id;
760 ELSIF p_FlowSchedule_rec.wip_entity_id <> FND_API.G_MISS_NUM
761 AND p_DiscreteJob_rec.wip_entity_id = FND_API.G_MISS_NUM
762 THEN
763 l_wip_entity_id := p_FlowSchedule_rec.wip_entity_id;
764 l_org_id := p_FlowSchedule_rec.organization_id;
765 l_build_seq := p_FlowSchedule_rec.build_sequence;
766 l_schedule_gp_id := p_FlowSchedule_rec.schedule_group_id;
767 l_line_id := p_FlowSchedule_rec.line_id;
768 ELSIF p_FlowSchedule_rec.wip_entity_id <> FND_API.G_MISS_NUM
769 AND p_DiscreteJob_rec.wip_entity_id <> FND_API.G_MISS_NUM
770 THEN
771 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
772 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
773 THEN
774 FND_MSG_PUB.Add_Exc_Msg
775 ( G_PKG_NAME
776 , 'Check Build Sequence'
777 , 'Mutually exclusive parameters: p_FlowSchedule_rec, p_DiscreteJob_rec '
778 );
779 END IF;
780 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
781
782 ELSE
783 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
784 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
785 THEN
786 FND_MSG_PUB.Add_Exc_Msg
787 ( G_PKG_NAME
788 , 'Check Build Sequence'
789 , 'Insufficient Arguments. '
790 );
791 END IF;
792 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
793
794 END IF;
795 IF l_wip_entity_id = FND_API.G_MISS_NUM
796 OR l_org_id = FND_API.G_MISS_NUM
797 OR l_build_seq = FND_API.G_MISS_NUM
798 OR l_schedule_gp_id = FND_API.G_MISS_NUM
799 OR l_line_id = FND_API.G_MISS_NUM
800 THEN
801 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
802 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
803 THEN
804 FND_MSG_PUB.Add_Exc_Msg
805 ( G_PKG_NAME
806 , 'Check Build Sequence'
807 , 'Insufficient Arguments in p_FlowSchedule_rec or p_DiscreteJob_rec. '
808 );
809 END IF;
810 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
811
812 END IF;
813 IF WIP_Validate.Build_Sequence(p_build_sequence => l_build_seq,
814 p_wip_entity_id => l_wip_entity_id,
815 p_organization_id => l_org_id,
816 p_line_id => l_line_id,
817 p_schedule_group_id => l_schedule_gp_id) THEN
818
819 x_build_seq_valid := FND_API.G_TRUE;
820 END IF;
821 x_return_status := FND_API.G_RET_STS_SUCCESS;
822
823 -- Get message count and data
824
825 FND_MSG_PUB.Count_And_Get
826 ( p_count => x_msg_count
827 , p_data => x_msg_data
828 );
829
830
831
832 EXCEPTION
833
834 WHEN FND_API.G_EXC_ERROR THEN
835 x_return_status := FND_API.G_RET_STS_ERROR;
836 -- Get message count and data
837 FND_MSG_PUB.Count_And_Get
838 ( p_count => x_msg_count
839 , p_data => x_msg_data
840 );
841
842 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
843 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
844 -- Get message count and data
845 FND_MSG_PUB.Count_And_Get
846 ( p_count => x_msg_count
847 , p_data => x_msg_data
848 );
849
850 WHEN OTHERS THEN
851 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
852 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
853 THEN
854 FND_MSG_PUB.Add_Exc_Msg
855 ( G_PKG_NAME
856 , 'Get_Build_Sequence'
857 );
858 END IF;
859 -- Get message count and data
860 FND_MSG_PUB.Count_And_Get
861 ( p_count => x_msg_count
862 , p_data => x_msg_data
863 );
864
865 END Check_Build_Sequence;
866
867 function component_exist (p_wip_entity_id number,
868 p_operation_seq_num number,
869 p_rep_schedule_id number,
870 p_organization_id number) return varchar2 IS
871 x_exists varchar2(1);
872 begin
873
874 SELECT 'Y'
875 INTO x_exists
876 FROM dual
877 WHERE EXISTS (SELECT 1
878 from WIP_REQUIREMENT_OPERATIONS
879 where wip_entity_id = p_wip_entity_id
880 and organization_id = p_organization_id
881 and operation_seq_num = p_operation_seq_num
882 and NVL(repetitive_schedule_id, -1) = NVL(p_rep_schedule_id, -1));
883
884 IF (x_exists<>'Y') THEN
885 return('N');
886 ELSE
887 return('Y');
888 END IF;
889
890 EXCEPTION
891 WHEN OTHERS THEN
892 RETURN('N');
893 end;
894
895
896 END WIP_Work_Order_PVT;