1 PACKAGE BODY MRP_Validate_Flow_Schedule AS
2 /* $Header: MRPLSCNB.pls 120.0 2005/05/27 11:12:39 appldev noship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'MRP_Validate_Flow_Schedule';
7
8 -- Procedure Entity
9
10 PROCEDURE Entity
11 ( x_return_status OUT NOCOPY VARCHAR2
12 , p_flow_schedule_rec IN MRP_Flow_Schedule_PVT.Flow_Schedule_PVT_Rec_Type
13 , p_old_flow_schedule_rec IN MRP_Flow_Schedule_PVT.Flow_Schedule_PVT_Rec_Type
14 )
15 IS
16 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
17 l_dummy VARCHAR2(10) := NULL;
18 l_see_eng_items NUMBER := 0;
19 l_schedule_group_id NUMBER := NULL;
20 --bug 3906891: use l_flow_schedule_rec instead of p_flow_schedule_rec
21 l_flow_schedule_rec MRP_Flow_Schedule_PVT.Flow_Schedule_PVT_Rec_Type := MRP_Flow_Schedule_Util.Convert_Miss_To_Null (p_flow_schedule_rec);
22
23 BEGIN
24
25 -- Check required (primary key) attributes.
26
27 IF l_flow_schedule_rec.organization_id IS NULL
28 THEN
29
30 l_return_status := FND_API.G_RET_STS_ERROR;
31
32 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
33 THEN
34
35 FND_MESSAGE.SET_NAME('MRP','MRP_ORG_ID_REQUIRED');
36 FND_MSG_PUB.Add;
37
38 END IF;
39
40 END IF;
41
42 IF l_flow_schedule_rec.wip_entity_id IS NULL
43 THEN
44
45 l_return_status := FND_API.G_RET_STS_ERROR;
46
47 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
48 THEN
49
50 FND_MESSAGE.SET_NAME('MRP','MRP_WIP_ENTITY_ID_REQUIRED');
51 FND_MSG_PUB.Add;
52
53 END IF;
54
55 END IF;
56
57 --
58 -- Check rest of required attributes here.
59 --
60
61 IF l_flow_schedule_rec.scheduled_flag IS NULL
62 THEN
63
64 l_return_status := FND_API.G_RET_STS_ERROR;
65
66 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
67 THEN
68
69 FND_MESSAGE.SET_NAME('MRP','MRP_SCH_FLAG_REQUIRED');
70 FND_MSG_PUB.Add;
71
72 END IF;
73
74 END IF;
75
76 IF l_flow_schedule_rec.primary_item_id IS NULL
77 THEN
78
79 l_return_status := FND_API.G_RET_STS_ERROR;
80
81 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
82 THEN
83
84 FND_MESSAGE.SET_NAME('MRP','MRP_ITEM_ID_REQUIRED');
85 FND_MSG_PUB.Add;
86
87 END IF;
88
89 END IF;
90
91 IF l_flow_schedule_rec.class_code IS NULL
92 THEN
93
94 l_return_status := FND_API.G_RET_STS_ERROR;
95
96 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
97 THEN
98
99 FND_MESSAGE.SET_NAME('MRP','MRP_CLASS_CODE_REQUIRED');
100 FND_MSG_PUB.Add;
101
102 END IF;
103
104 END IF;
105
106 IF l_flow_schedule_rec.scheduled_completion_date IS NULL
107 THEN
108
109 l_return_status := FND_API.G_RET_STS_ERROR;
110
111 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
112 THEN
113
114 FND_MESSAGE.SET_NAME('MRP','MRP_SCH_COMP_DATE_REQUIRED');
115 FND_MSG_PUB.Add;
116
117 END IF;
118
119 END IF;
120
121 IF l_flow_schedule_rec.planned_quantity IS NULL
122 THEN
123
124 l_return_status := FND_API.G_RET_STS_ERROR;
125
126 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
127 THEN
128
129 FND_MESSAGE.SET_NAME('MRP','MRP_PLANNED_QTY_REQUIRED');
130 FND_MSG_PUB.Add;
131
132 END IF;
133
134 END IF;
135
136 IF l_flow_schedule_rec.quantity_completed IS NULL
137 THEN
138
139 l_return_status := FND_API.G_RET_STS_ERROR;
140
141 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
142 THEN
143
144 FND_MESSAGE.SET_NAME('MRP','MRP_QTY_COMP_REQUIRED');
145 FND_MSG_PUB.Add;
146
147 END IF;
148
149 END IF;
150
151 IF l_flow_schedule_rec.scheduled_start_date IS NULL
152 THEN
153
154 l_return_status := FND_API.G_RET_STS_ERROR;
155
156 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
157 THEN
158
159 FND_MESSAGE.SET_NAME('MRP','MRP_SCH_START_DATE_REQUIRED');
160 FND_MSG_PUB.Add;
161
162 END IF;
163
164 END IF;
165
166 IF l_flow_schedule_rec.status IS NULL
167 THEN
168
169 l_return_status := FND_API.G_RET_STS_ERROR;
170
171 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
172 THEN
173
174 FND_MESSAGE.SET_NAME('MRP','MRP_STATUS_REQUIRED');
175 FND_MSG_PUB.Add;
176
177 END IF;
178
179 END IF;
180
181 IF l_flow_schedule_rec.schedule_number IS NULL
182 THEN
183
184 l_return_status := FND_API.G_RET_STS_ERROR;
185
186 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
187 THEN
188
189 FND_MESSAGE.SET_NAME('MRP','MRP_SCH_NUMBER_REQUIRED');
190 FND_MSG_PUB.Add;
191
192 END IF;
193
194 END IF;
195
196 -- Return Error if a required attribute is missing.
197
198 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
199
200 RAISE FND_API.G_EXC_ERROR;
201
202 END IF;
203
204 --
205 -- Check conditionally required attributes here.
206 --
207
208
209 --
210 -- Validate attribute dependencies here.
211 --
212
213 -- Validate Alternate_Bom_Designator
214 IF l_flow_schedule_rec.alternate_bom_designator <>
215 p_old_flow_schedule_rec.alternate_bom_designator
216 THEN
217 BEGIN
218
219 SELECT 'VALID'
220 INTO l_dummy
221 FROM bom_bill_alternates_v
222 WHERE assembly_item_id = l_flow_schedule_rec.primary_item_id
223 AND organization_id = l_flow_schedule_rec.organization_id
224 AND alternate_bom_designator =
225 l_flow_schedule_rec.alternate_bom_designator;
226
227 EXCEPTION
228
229 WHEN NO_DATA_FOUND THEN
230
231 l_return_status := FND_API.G_RET_STS_ERROR;
232
233 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
234 THEN
235
236 FND_MESSAGE.SET_NAME('MRP','MRP_INVALID_ALT_BOM_DESIG');
237 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
238 l_flow_schedule_rec.alternate_bom_designator);
239 FND_MSG_PUB.Add;
240
241 END IF;
242
243 WHEN OTHERS THEN
244
245 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
246
247 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
248 THEN
249
250 FND_MSG_PUB.Add_Exc_Msg
251 ( G_PKG_NAME
252 , 'Record Validation - Alternate Bom Designator'
253 );
254 END IF;
255
256 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
257
258 END;
259
260 END IF;
261
262 -- Validate Alternate_Routing_Designator
263 IF l_flow_schedule_rec.alternate_routing_desig <>
264 p_old_flow_schedule_rec.alternate_routing_desig
265 THEN
266 BEGIN
267
268 SELECT 'VALID'
269 INTO l_dummy
270 FROM bom_routing_alternates_v
271 WHERE assembly_item_id = l_flow_schedule_rec.primary_item_id
272 AND organization_id = l_flow_schedule_rec.organization_id
273 AND NVL(cfm_routing_flag,2) = 2
274 AND alternate_routing_designator =
275 l_flow_schedule_rec.alternate_routing_desig;
276
277 EXCEPTION
278
279 WHEN NO_DATA_FOUND THEN
280
281 l_return_status := FND_API.G_RET_STS_ERROR;
282
283 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
284 THEN
285
286 FND_MESSAGE.SET_NAME('MRP','MRP_INVALID_ALT_RTG_DESIG');
287 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
288 l_flow_schedule_rec.alternate_routing_desig);
289 FND_MSG_PUB.Add;
290
291 END IF;
292
293 WHEN OTHERS THEN
294
295 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
296
297 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
298 THEN
299
300 FND_MSG_PUB.Add_Exc_Msg
301 ( G_PKG_NAME
302 , 'Record Validation - Alternate Routing Designator'
303 );
304 END IF;
305
306 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
307
308 END;
309
310 END IF;
311
312 -- Validate Bom_Revision and Bom_Revision_Date
313 IF l_flow_schedule_rec.bom_revision <> p_old_flow_schedule_rec.bom_revision
314 OR l_flow_schedule_rec.bom_revision_date <>
315 p_old_flow_schedule_rec.bom_revision_date
316 THEN
317 BEGIN
318
319 SELECT 'VALID'
320 INTO l_dummy
321 FROM bom_bill_no_hold_revisions_v
322 WHERE inventory_item_id = l_flow_schedule_rec.primary_item_id
323 AND organization_id = l_flow_schedule_rec.organization_id
324 AND revision = l_flow_schedule_rec.bom_revision
325 AND l_flow_schedule_rec.bom_revision_date >= --fix bug#3170105
326 effectivity_date;
327
328 EXCEPTION
329
330 WHEN NO_DATA_FOUND THEN
331
332 l_return_status := FND_API.G_RET_STS_ERROR;
333
334 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
335 THEN
336
337 FND_MESSAGE.SET_NAME('MRP','MRP_INVALID_BOM_REV');
338 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
339 l_flow_schedule_rec.bom_revision);
340 FND_MSG_PUB.Add;
341
342 END IF;
343
344 WHEN OTHERS THEN
345
346 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
347
348 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
349 THEN
350
351 FND_MSG_PUB.Add_Exc_Msg
352 ( G_PKG_NAME
353 , 'Record Validation - Bom Revison'
354 );
355 END IF;
356
357 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
358
359 END;
360
361 END IF;
362
363 -- Validate Build Sequence and Schedule Group uniqueness
364 IF l_flow_schedule_rec.build_sequence <>
365 p_old_flow_schedule_rec.build_sequence
366 OR p_old_flow_schedule_rec.build_sequence IS NULL
367 OR l_flow_schedule_rec.schedule_group_id <>
368 p_old_flow_schedule_rec.schedule_group_id
369 OR l_flow_schedule_rec.line_id <>
370 p_old_flow_schedule_rec.line_id
371 OR l_flow_schedule_rec.primary_item_id <>
372 p_old_flow_schedule_rec.primary_item_id
373 THEN
374
375 BEGIN
376
377 IF l_flow_schedule_rec.schedule_group_id = FND_API.G_MISS_NUM
378 THEN
379 l_schedule_group_id := NULL;
380 ELSE
381 l_schedule_group_id := l_flow_schedule_rec.schedule_group_id;
382 END IF;
383
384 SELECT 'VALID'
385 INTO l_dummy
386 FROM dual
387 WHERE l_flow_schedule_rec.build_sequence NOT IN
388 (SELECT build_sequence
389 FROM wip_flow_schedules
390 WHERE NVL(schedule_group_id,-1) = NVL(l_schedule_group_id,-1)
391 AND line_id = l_flow_schedule_rec.line_id
392 AND scheduled_completion_date --fix bug#3170105
393 BETWEEN l_flow_schedule_rec.scheduled_completion_date
394 AND l_flow_schedule_rec.scheduled_completion_date+1-1/(24*60*60)
395 AND organization_id = l_flow_schedule_rec.organization_id
396 AND build_sequence = l_flow_schedule_rec.build_sequence);
397
398 EXCEPTION
399
400 WHEN NO_DATA_FOUND THEN
401
402 l_return_status := FND_API.G_RET_STS_ERROR;
403
404 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
405 THEN
406
407 FND_MESSAGE.SET_NAME('MRP','MRP_INVALID_BUILD_SEQ');
408 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
409 l_flow_schedule_rec.build_sequence);
410 FND_MSG_PUB.Add;
411
412 END IF;
413
414 WHEN OTHERS THEN
415
416 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
417
418 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
419 THEN
420
421 FND_MSG_PUB.Add_Exc_Msg
422 ( G_PKG_NAME
423 , 'Record Validation - Build Sequence'
424 );
425 END IF;
426
427 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
428
429 END;
430
431 END IF;
432
433 -- Validate Class_Code
434 IF l_flow_schedule_rec.class_code <> p_old_flow_schedule_rec.class_code
435 THEN
436 BEGIN
437
438 SELECT 'VALID'
439 INTO l_dummy
440 FROM mtl_parameters param,
441 cst_cg_wip_acct_classes_v ccwac
442 WHERE ccwac.organization_id = l_flow_schedule_rec.organization_id
443 AND ccwac.organization_id = param.organization_id
444 AND ( l_flow_schedule_rec.project_id is null OR
445 param.primary_cost_method = 1 OR
446 ( param.primary_cost_method = 2 AND
447 l_flow_schedule_rec.project_id is not null
448 AND ccwac.cost_group_id =
449 ( SELECT costing_group_id
450 FROM mrp_project_parameters
451 WHERE organization_id = l_flow_schedule_rec.organization_id
452 AND project_id = l_flow_schedule_rec.project_id)))
453 AND ccwac.class_code = l_flow_schedule_rec.class_code;
454
455 EXCEPTION
456
457 WHEN NO_DATA_FOUND THEN
458
459 l_return_status := FND_API.G_RET_STS_ERROR;
460
461 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
462 THEN
463
464 FND_MESSAGE.SET_NAME('MRP','MRP_INVALID_CLASS_CODE');
465 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
466 l_flow_schedule_rec.class_code);
467 FND_MSG_PUB.Add;
468
469 END IF;
470
471 WHEN OTHERS THEN
472
473 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
474
475 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
476 THEN
477
478 FND_MSG_PUB.Add_Exc_Msg
479 ( G_PKG_NAME
480 , 'Record Validation - Class Code'
481 );
482 END IF;
483
484 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
485
486 END;
487
488 END IF;
489
490 -- Validate Completion_Locator_Id
491 IF l_flow_schedule_rec.completion_locator_id <>
492 p_old_flow_schedule_rec.completion_locator_id
493 THEN
494 BEGIN
495
496 SELECT 'VALID'
497 INTO l_dummy
498 FROM mtl_item_locations
499 WHERE organization_id = l_flow_schedule_rec.organization_id
500 AND subinventory_code = l_flow_schedule_rec.completion_subinventory
501 AND (disable_date > sysdate or disable_date is null)
502 AND inventory_location_id =
503 l_flow_schedule_rec.completion_locator_id;
504
505 EXCEPTION
506
507 WHEN NO_DATA_FOUND THEN
508
509 l_return_status := FND_API.G_RET_STS_ERROR;
510
511 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
512 THEN
513
514 FND_MESSAGE.SET_NAME('MRP','MRP_INVALID_COMP_LOC_ID');
515 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
516 l_flow_schedule_rec.completion_locator_id);
517 FND_MSG_PUB.Add;
518
519 END IF;
520
521 WHEN OTHERS THEN
522
523 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
524
525 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
526 THEN
527
528 FND_MSG_PUB.Add_Exc_Msg
529 ( G_PKG_NAME
530 , 'Record Validation - Completion Locator Id'
531 );
532 END IF;
533
534 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
535
536 END;
537
538 END IF;
539
540 -- Validate Completion_Subinventory
541 IF l_flow_schedule_rec.completion_subinventory <>
542 p_old_flow_schedule_rec.completion_subinventory
543 THEN
544 BEGIN
545
546 SELECT 'VALID'
547 INTO l_dummy
548 FROM mtl_subinventories_val_v msvv, mtl_sub_ast_trk_val_v msatvv
549 WHERE msvv.organization_id = l_flow_schedule_rec.organization_id
550 AND msvv.organization_id = msatvv.organization_id
551 AND msvv.secondary_inventory_name = msatvv.secondary_inventory_name
552 AND msvv.secondary_inventory_name =
553 l_flow_schedule_rec.completion_subinventory;
554
555 EXCEPTION
556
557 WHEN NO_DATA_FOUND THEN
558
559 l_return_status := FND_API.G_RET_STS_ERROR;
560
561 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
562 THEN
563
564 FND_MESSAGE.SET_NAME('MRP','MRP_INVALID_COMP_SUBINV');
565 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
566 l_flow_schedule_rec.completion_subinventory);
567 FND_MSG_PUB.Add;
568
569 END IF;
570
571 WHEN OTHERS THEN
572
573 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
574
575 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
576 THEN
577
578 FND_MSG_PUB.Add_Exc_Msg
579 ( G_PKG_NAME
580 , 'Record Validation - Completion Subinventory'
581 );
582 END IF;
583
584 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
585
586 END;
587
588 END IF;
589
590 -- Validate Demand_Source_Line, Demand_Source_Delivery,
591 -- and Demand_Source_Header_Id
592 IF (l_flow_schedule_rec.demand_source_line <>
593 p_old_flow_schedule_rec.demand_source_line OR
594 l_flow_schedule_rec.demand_source_delivery <>
595 p_old_flow_schedule_rec.demand_source_delivery) AND /* Bug 3539807 - Added the AND-ed condn. */
596 (nvl(l_flow_schedule_rec.demand_source_line,FND_API.G_MISS_CHAR)<>
597 FND_API.G_MISS_CHAR OR
598 nvl(l_flow_schedule_rec.demand_source_delivery,FND_API.G_MISS_CHAR)<>
599 FND_API.G_MISS_CHAR)
600 THEN
601 BEGIN
602
603 SELECT 'VALID'
604 INTO l_dummy
605 FROM wip_sales_order_lines_v
606 WHERE organization_id = l_flow_schedule_rec.organization_id
607 AND inventory_item_id = NVL(l_flow_schedule_rec.primary_item_id,
608 inventory_item_id)
609 AND NVL(demand_class,'@@@') =
610 NVL(l_flow_schedule_rec.demand_class,'@@@')
611 AND demand_source_header_id =
612 l_flow_schedule_rec.demand_source_header_id
613 AND demand_source_line = l_flow_schedule_rec.demand_source_line
614 AND demand_source_delivery =
615 l_flow_schedule_rec.demand_source_delivery;
616
617 EXCEPTION
618
619 WHEN NO_DATA_FOUND THEN
620
621 l_return_status := FND_API.G_RET_STS_ERROR;
622
623 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
624 THEN
625
626 FND_MESSAGE.SET_NAME('MRP','MRP_INVALID_DEMAND_SOURCE');
627 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
628 l_flow_schedule_rec.demand_source_header_id);
629 FND_MSG_PUB.Add;
630
631 END IF;
632
633 WHEN OTHERS THEN
634
635 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
636
637 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
638 THEN
639
640 FND_MSG_PUB.Add_Exc_Msg
641 ( G_PKG_NAME
642 , 'Record Validation - Demand Sources'
643 );
644 END IF;
645
646 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
647
648 END;
649
650 END IF;
651
652 -- Validate Line_Id
653 IF l_flow_schedule_rec.line_id <> p_old_flow_schedule_rec.line_id
654 THEN
655 BEGIN
656
657 SELECT 'VALID'
658 INTO l_dummy
659 FROM wip_lines
660 WHERE organization_id = l_flow_schedule_rec.organization_id
661 AND line_id = l_flow_schedule_rec.line_id;
662
663 EXCEPTION
664
665 WHEN NO_DATA_FOUND THEN
666
667 l_return_status := FND_API.G_RET_STS_ERROR;
668
669 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
670 THEN
671
672 FND_MESSAGE.SET_NAME('MRP','MRP_INVALID_LINE_ID');
673 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
674 l_flow_schedule_rec.line_id);
675 FND_MSG_PUB.Add;
676
677 END IF;
678
679 WHEN OTHERS THEN
680
681 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
682
683 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
684 THEN
685
686 FND_MSG_PUB.Add_Exc_Msg
687 ( G_PKG_NAME
688 , 'Record Validation - Line Id'
689 );
690 END IF;
691
692 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
693
694 END;
695
696 END IF;
697
698 -- Validate Primary_Item_Id
699 IF l_flow_schedule_rec.primary_item_id <>
700 p_old_flow_schedule_rec.primary_item_id
701 THEN
702 BEGIN
703
704 l_see_eng_items := FND_PROFILE.Value('WIP_SEE_ENG_ITEMS');
705
706 SELECT 'VALID'
707 INTO l_dummy
708 FROM mtl_system_items
709 WHERE organization_id = l_flow_schedule_rec.organization_id
710 AND inventory_item_id = l_flow_schedule_rec.primary_item_id
711 AND build_in_wip_flag = 'Y'
712 AND pick_components_flag = 'N'
713 AND (l_see_eng_items = 1
714 OR (l_see_eng_items = 2 AND eng_item_flag = 'N') );
715
716 EXCEPTION
717
718 WHEN NO_DATA_FOUND THEN
719
720 l_return_status := FND_API.G_RET_STS_ERROR;
721
722 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
723 THEN
724
725 FND_MESSAGE.SET_NAME('MRP','MRP_INVALID_ITEM');
726 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
727 l_flow_schedule_rec.primary_item_id);
728 FND_MSG_PUB.Add;
729
730 END IF;
731
732 WHEN OTHERS THEN
733
734 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
735
736 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
737 THEN
738
739 FND_MSG_PUB.Add_Exc_Msg
740 ( G_PKG_NAME
741 , 'Record Validation - Primary Item Id'
742 );
743 END IF;
744
745 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
746
747 END;
748
749 END IF;
750
751 -- Validate Routing_Revision and Routing_Revision_Date
752 IF l_flow_schedule_rec.routing_revision <>
753 p_old_flow_schedule_rec.routing_revision OR
754 l_flow_schedule_rec.routing_revision_date <>
755 p_old_flow_schedule_rec.routing_revision_date
756 THEN
757 BEGIN
758
759 SELECT 'VALID'
760 INTO l_dummy
761 FROM mtl_routing_rev_highdate_v
762 WHERE organization_id = l_flow_schedule_rec.organization_id
763 AND inventory_item_id = l_flow_schedule_rec.primary_item_id
764 AND process_revision = l_flow_schedule_rec.routing_revision
765 AND l_flow_schedule_rec.routing_revision_date >= --fix bug#3170105
766 effectivity_date;
767
768 EXCEPTION
769
770 WHEN NO_DATA_FOUND THEN
771
772 l_return_status := FND_API.G_RET_STS_ERROR;
773
774 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
775 THEN
776
777 FND_MESSAGE.SET_NAME('MRP','MRP_INVALID_RTG_REV');
778 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
779 l_flow_schedule_rec.routing_revision);
780 FND_MSG_PUB.Add;
781
782 END IF;
783
784 WHEN OTHERS THEN
785
786 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
787
788 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
789 THEN
790
791 FND_MSG_PUB.Add_Exc_Msg
792 ( G_PKG_NAME
793 , 'Record Validation - Routing Revision'
794 );
795 END IF;
796
797 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
798
799 END;
800
801 END IF;
802
803 -- Validate Schedule_Group_Id
804 IF l_flow_schedule_rec.schedule_group_id <>
805 p_old_flow_schedule_rec.schedule_group_id
806 THEN
807 BEGIN
808
809 SELECT 'VALID'
810 INTO l_dummy
811 FROM wip_schedule_groups
812 WHERE organization_id = l_flow_schedule_rec.organization_id
813 AND schedule_group_id = l_flow_schedule_rec.schedule_group_id;
814
815 EXCEPTION
816
817 WHEN NO_DATA_FOUND THEN
818
819 l_return_status := FND_API.G_RET_STS_ERROR;
820
821 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
822 THEN
823
824 FND_MESSAGE.SET_NAME('MRP','MRP_INVALID_SCH_GRP');
825 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
826 l_flow_schedule_rec.schedule_group_id);
827 FND_MSG_PUB.Add;
828
829 END IF;
830
831 WHEN OTHERS THEN
832
833 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
834
835 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
836 THEN
837
838 FND_MSG_PUB.Add_Exc_Msg
839 ( G_PKG_NAME
840 , 'Record Validation - Schedule Group Id'
841 );
842 END IF;
843
844 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
845
846 END;
847
848 END IF;
849
850 -- Validate Task_Id
851 IF l_flow_schedule_rec.task_id <>
852 p_old_flow_schedule_rec.task_id
853 THEN
854 BEGIN
855
856 SELECT 'VALID'
857 INTO l_dummy
858 FROM mtl_task_v
859 WHERE project_id = l_flow_schedule_rec.project_id
860 AND task_id = l_flow_schedule_rec.task_id;
861
862 EXCEPTION
863
864 WHEN NO_DATA_FOUND THEN
865
866 l_return_status := FND_API.G_RET_STS_ERROR;
867
868 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
869 THEN
870
871 FND_MESSAGE.SET_NAME('MRP','MRP_INVALID_TASK');
872 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
873 l_flow_schedule_rec.task_id);
874 FND_MSG_PUB.Add;
875
876 END IF;
877
878 WHEN OTHERS THEN
879
880 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
881
882 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
883 THEN
884
885 FND_MSG_PUB.Add_Exc_Msg
886 ( G_PKG_NAME
887 , 'Record Validation - Task Id'
888 );
889 END IF;
890
891 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
892
893 END;
894
895 END IF;
896
897 -- Done validating entity
898
899 x_return_status := l_return_status;
900
901 EXCEPTION
902
903 WHEN FND_API.G_EXC_ERROR THEN
904
905 x_return_status := FND_API.G_RET_STS_ERROR;
906
907 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
908
909 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
910
911 WHEN OTHERS THEN
912
913 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
914
915 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
916 THEN
917 FND_MSG_PUB.Add_Exc_Msg
918 ( G_PKG_NAME
919 , 'Entity'
920 );
921 END IF;
922
923 END Entity;
924
925 -- Procedure Attributes
926
927 PROCEDURE Attributes
928 ( x_return_status OUT NOCOPY VARCHAR2
929 , p_flow_schedule_rec IN MRP_Flow_Schedule_PVT.Flow_Schedule_PVT_Rec_Type
930 , p_old_flow_schedule_rec IN MRP_Flow_Schedule_PVT.Flow_Schedule_PVT_Rec_Type
931 )
932 IS
933 --bug 3906891: use l_flow_schedule_rec instead of p_flow_schedule_rec
934 l_flow_schedule_rec MRP_Flow_Schedule_PVT.Flow_Schedule_PVT_Rec_Type := MRP_Flow_Schedule_Util.Convert_Miss_To_Null (p_flow_schedule_rec);
935 BEGIN
936
937 x_return_status := FND_API.G_RET_STS_SUCCESS;
938
939 -- Validate flow_schedule attributes
940
941 IF l_flow_schedule_rec.alternate_bom_designator IS NOT NULL AND
942 ( l_flow_schedule_rec.alternate_bom_designator <>
943 p_old_flow_schedule_rec.alternate_bom_designator OR
944 p_old_flow_schedule_rec.alternate_bom_designator IS NULL )
945 THEN
946 IF NOT MRP_Validate.Alternate_Bom_Designator(l_flow_schedule_rec.alternate_bom_designator) THEN
947 x_return_status := FND_API.G_RET_STS_ERROR;
948 END IF;
949 END IF;
950
951 IF l_flow_schedule_rec.alternate_routing_desig IS NOT NULL AND
952 ( l_flow_schedule_rec.alternate_routing_desig <>
953 p_old_flow_schedule_rec.alternate_routing_desig OR
954 p_old_flow_schedule_rec.alternate_routing_desig IS NULL )
955 THEN
956 IF NOT MRP_Validate.Alternate_Routing_Desig(l_flow_schedule_rec.alternate_routing_desig) THEN
957 x_return_status := FND_API.G_RET_STS_ERROR;
958 END IF;
959 END IF;
960
961 IF l_flow_schedule_rec.bom_revision IS NOT NULL AND
962 ( l_flow_schedule_rec.bom_revision <>
963 p_old_flow_schedule_rec.bom_revision OR
964 p_old_flow_schedule_rec.bom_revision IS NULL )
965 THEN
966 IF NOT MRP_Validate.Bom_Revision(l_flow_schedule_rec.bom_revision) THEN
967 x_return_status := FND_API.G_RET_STS_ERROR;
968 END IF;
969 END IF;
970
971 IF l_flow_schedule_rec.bom_revision_date IS NOT NULL AND
972 ( l_flow_schedule_rec.bom_revision_date <>
973 p_old_flow_schedule_rec.bom_revision_date OR
974 p_old_flow_schedule_rec.bom_revision_date IS NULL )
975 THEN
976 IF NOT MRP_Validate.Bom_Revision_Date(l_flow_schedule_rec.bom_revision_date) THEN
977 x_return_status := FND_API.G_RET_STS_ERROR;
978 END IF;
979 END IF;
980
981 IF l_flow_schedule_rec.build_sequence IS NOT NULL AND
982 ( l_flow_schedule_rec.build_sequence <>
983 p_old_flow_schedule_rec.build_sequence OR
984 p_old_flow_schedule_rec.build_sequence IS NULL )
985 THEN
986 IF NOT MRP_Validate.Build_Sequence(l_flow_schedule_rec.build_sequence) THEN
987 x_return_status := FND_API.G_RET_STS_ERROR;
988 END IF;
989 END IF;
990
991 IF l_flow_schedule_rec.class_code IS NOT NULL AND
992 ( l_flow_schedule_rec.class_code <>
993 p_old_flow_schedule_rec.class_code OR
994 p_old_flow_schedule_rec.class_code IS NULL )
995 THEN
996 IF NOT MRP_Validate.Class(l_flow_schedule_rec.class_code) THEN
997 x_return_status := FND_API.G_RET_STS_ERROR;
998 END IF;
999 END IF;
1000
1001 IF l_flow_schedule_rec.completion_locator_id IS NOT NULL AND
1002 ( l_flow_schedule_rec.completion_locator_id <>
1003 p_old_flow_schedule_rec.completion_locator_id OR
1004 p_old_flow_schedule_rec.completion_locator_id IS NULL )
1005 THEN
1006 IF NOT MRP_Validate.Completion_Locator(l_flow_schedule_rec.completion_locator_id) THEN
1007 x_return_status := FND_API.G_RET_STS_ERROR;
1008 END IF;
1009 END IF;
1010
1011 IF l_flow_schedule_rec.completion_subinventory IS NOT NULL AND
1012 ( l_flow_schedule_rec.completion_subinventory <>
1013 p_old_flow_schedule_rec.completion_subinventory OR
1014 p_old_flow_schedule_rec.completion_subinventory IS NULL )
1015 THEN
1016 IF NOT MRP_Validate.Completion_Subinventory(l_flow_schedule_rec.completion_subinventory) THEN
1017 x_return_status := FND_API.G_RET_STS_ERROR;
1018 END IF;
1019 END IF;
1020
1021 IF l_flow_schedule_rec.created_by IS NOT NULL AND
1022 ( l_flow_schedule_rec.created_by <>
1023 p_old_flow_schedule_rec.created_by OR
1024 p_old_flow_schedule_rec.created_by IS NULL )
1025 THEN
1026 IF NOT MRP_Validate.Created_By(l_flow_schedule_rec.created_by) THEN
1027 x_return_status := FND_API.G_RET_STS_ERROR;
1028 END IF;
1029 END IF;
1030
1031 IF l_flow_schedule_rec.creation_date IS NOT NULL AND
1032 ( l_flow_schedule_rec.creation_date <>
1033 p_old_flow_schedule_rec.creation_date OR
1034 p_old_flow_schedule_rec.creation_date IS NULL )
1035 THEN
1036 IF NOT MRP_Validate.Creation_Date(l_flow_schedule_rec.creation_date) THEN
1037 x_return_status := FND_API.G_RET_STS_ERROR;
1038 END IF;
1039 END IF;
1040
1041 IF l_flow_schedule_rec.date_closed IS NOT NULL AND
1042 ( l_flow_schedule_rec.date_closed <>
1043 p_old_flow_schedule_rec.date_closed OR
1044 p_old_flow_schedule_rec.date_closed IS NULL )
1045 THEN
1046 IF NOT MRP_Validate.Date_Closed(l_flow_schedule_rec.date_closed) THEN
1047 x_return_status := FND_API.G_RET_STS_ERROR;
1048 END IF;
1049 END IF;
1050
1051 IF l_flow_schedule_rec.demand_class IS NOT NULL AND
1052 ( l_flow_schedule_rec.demand_class <>
1053 p_old_flow_schedule_rec.demand_class OR
1054 p_old_flow_schedule_rec.demand_class IS NULL )
1055 THEN
1056 IF NOT MRP_Validate.Demand_Class(l_flow_schedule_rec.demand_class) THEN
1057 x_return_status := FND_API.G_RET_STS_ERROR;
1058 END IF;
1059 END IF;
1060
1061 IF l_flow_schedule_rec.demand_source_delivery IS NOT NULL AND
1062 ( l_flow_schedule_rec.demand_source_delivery <>
1063 p_old_flow_schedule_rec.demand_source_delivery OR
1064 p_old_flow_schedule_rec.demand_source_delivery IS NULL )
1065 THEN
1066 IF NOT MRP_Validate.Demand_Source_Delivery(l_flow_schedule_rec.demand_source_delivery) THEN
1067 x_return_status := FND_API.G_RET_STS_ERROR;
1068 END IF;
1069 END IF;
1070
1071 IF l_flow_schedule_rec.demand_source_header_id IS NOT NULL AND
1072 ( l_flow_schedule_rec.demand_source_header_id <>
1073 p_old_flow_schedule_rec.demand_source_header_id OR
1074 p_old_flow_schedule_rec.demand_source_header_id IS NULL )
1075 THEN
1076 IF NOT MRP_Validate.Demand_Source_Header(l_flow_schedule_rec.demand_source_header_id) THEN
1077 x_return_status := FND_API.G_RET_STS_ERROR;
1078 END IF;
1079 END IF;
1080
1081 IF l_flow_schedule_rec.demand_source_line IS NOT NULL AND
1082 ( l_flow_schedule_rec.demand_source_line <>
1083 p_old_flow_schedule_rec.demand_source_line OR
1084 p_old_flow_schedule_rec.demand_source_line IS NULL )
1085 THEN
1086 IF NOT MRP_Validate.Demand_Source_Line(l_flow_schedule_rec.demand_source_line) THEN
1087 x_return_status := FND_API.G_RET_STS_ERROR;
1088 END IF;
1089 END IF;
1090
1091 IF l_flow_schedule_rec.demand_source_type IS NOT NULL AND
1092 ( l_flow_schedule_rec.demand_source_type <>
1093 p_old_flow_schedule_rec.demand_source_type OR
1094 p_old_flow_schedule_rec.demand_source_type IS NULL )
1095 THEN
1096 IF NOT MRP_Validate.Demand_Source_Type(l_flow_schedule_rec.demand_source_type) THEN
1097 x_return_status := FND_API.G_RET_STS_ERROR;
1098 END IF;
1099 END IF;
1100
1101 IF l_flow_schedule_rec.last_updated_by IS NOT NULL AND
1102 ( l_flow_schedule_rec.last_updated_by <>
1103 p_old_flow_schedule_rec.last_updated_by OR
1104 p_old_flow_schedule_rec.last_updated_by IS NULL )
1105 THEN
1106 IF NOT MRP_Validate.Last_Updated_By(l_flow_schedule_rec.last_updated_by) THEN
1107 x_return_status := FND_API.G_RET_STS_ERROR;
1108 END IF;
1109 END IF;
1110
1111 IF l_flow_schedule_rec.last_update_date IS NOT NULL AND
1112 ( l_flow_schedule_rec.last_update_date <>
1113 p_old_flow_schedule_rec.last_update_date OR
1114 p_old_flow_schedule_rec.last_update_date IS NULL )
1115 THEN
1116 IF NOT MRP_Validate.Last_Update_Date(l_flow_schedule_rec.last_update_date) THEN
1117 x_return_status := FND_API.G_RET_STS_ERROR;
1118 END IF;
1119 END IF;
1120
1121 IF l_flow_schedule_rec.last_update_login IS NOT NULL AND
1122 ( l_flow_schedule_rec.last_update_login <>
1123 p_old_flow_schedule_rec.last_update_login OR
1124 p_old_flow_schedule_rec.last_update_login IS NULL )
1125 THEN
1126 IF NOT MRP_Validate.Last_Update_Login(l_flow_schedule_rec.last_update_login) THEN
1127 x_return_status := FND_API.G_RET_STS_ERROR;
1128 END IF;
1129 END IF;
1130
1131 IF l_flow_schedule_rec.line_id IS NOT NULL AND
1132 ( l_flow_schedule_rec.line_id <>
1133 p_old_flow_schedule_rec.line_id OR
1134 p_old_flow_schedule_rec.line_id IS NULL )
1135 THEN
1136 IF NOT MRP_Validate.Line(l_flow_schedule_rec.line_id) THEN
1137 x_return_status := FND_API.G_RET_STS_ERROR;
1138 END IF;
1139 END IF;
1140
1141 IF l_flow_schedule_rec.material_account IS NOT NULL AND
1142 ( l_flow_schedule_rec.material_account <>
1143 p_old_flow_schedule_rec.material_account OR
1144 p_old_flow_schedule_rec.material_account IS NULL )
1145 THEN
1146 IF NOT MRP_Validate.Material_Account(l_flow_schedule_rec.material_account) THEN
1147 x_return_status := FND_API.G_RET_STS_ERROR;
1148 END IF;
1149 END IF;
1150
1151 IF l_flow_schedule_rec.material_overhead_account IS NOT NULL AND
1152 ( l_flow_schedule_rec.material_overhead_account <>
1153 p_old_flow_schedule_rec.material_overhead_account OR
1154 p_old_flow_schedule_rec.material_overhead_account IS NULL )
1155 THEN
1156 IF NOT MRP_Validate.Material_Overhead_Account(l_flow_schedule_rec.material_overhead_account) THEN
1157 x_return_status := FND_API.G_RET_STS_ERROR;
1158 END IF;
1159 END IF;
1160
1161 IF l_flow_schedule_rec.material_variance_account IS NOT NULL AND
1162 ( l_flow_schedule_rec.material_variance_account <>
1163 p_old_flow_schedule_rec.material_variance_account OR
1164 p_old_flow_schedule_rec.material_variance_account IS NULL )
1165 THEN
1166 IF NOT MRP_Validate.Material_Variance_Account(l_flow_schedule_rec.material_variance_account) THEN
1167 x_return_status := FND_API.G_RET_STS_ERROR;
1168 END IF;
1169 END IF;
1170
1171 IF l_flow_schedule_rec.mps_net_quantity IS NOT NULL AND
1172 ( l_flow_schedule_rec.mps_net_quantity <>
1173 p_old_flow_schedule_rec.mps_net_quantity OR
1174 p_old_flow_schedule_rec.mps_net_quantity IS NULL )
1175 THEN
1176 IF NOT MRP_Validate.Mps_Net_Quantity(l_flow_schedule_rec.mps_net_quantity) THEN
1177 x_return_status := FND_API.G_RET_STS_ERROR;
1178 END IF;
1179 END IF;
1180
1181 IF l_flow_schedule_rec.mps_scheduled_comp_date IS NOT NULL AND
1182 ( l_flow_schedule_rec.mps_scheduled_comp_date <>
1183 p_old_flow_schedule_rec.mps_scheduled_comp_date OR
1184 p_old_flow_schedule_rec.mps_scheduled_comp_date IS NULL )
1185 THEN
1186 IF NOT MRP_Validate.Mps_Scheduled_Comp_Date(l_flow_schedule_rec.mps_scheduled_comp_date) THEN
1187 x_return_status := FND_API.G_RET_STS_ERROR;
1188 END IF;
1189 END IF;
1190
1191 IF l_flow_schedule_rec.organization_id IS NOT NULL AND
1192 ( l_flow_schedule_rec.organization_id <>
1193 p_old_flow_schedule_rec.organization_id OR
1194 p_old_flow_schedule_rec.organization_id IS NULL )
1195 THEN
1196 IF NOT MRP_Validate.Organization(l_flow_schedule_rec.organization_id) THEN
1197 x_return_status := FND_API.G_RET_STS_ERROR;
1198 END IF;
1199 END IF;
1200
1201 IF l_flow_schedule_rec.outside_processing_acct IS NOT NULL AND
1202 ( l_flow_schedule_rec.outside_processing_acct <>
1203 p_old_flow_schedule_rec.outside_processing_acct OR
1204 p_old_flow_schedule_rec.outside_processing_acct IS NULL )
1205 THEN
1206 IF NOT MRP_Validate.Outside_Processing_Acct(l_flow_schedule_rec.outside_processing_acct) THEN
1207 x_return_status := FND_API.G_RET_STS_ERROR;
1208 END IF;
1209 END IF;
1210
1211 IF l_flow_schedule_rec.outside_proc_var_acct IS NOT NULL AND
1212 ( l_flow_schedule_rec.outside_proc_var_acct <>
1213 p_old_flow_schedule_rec.outside_proc_var_acct OR
1214 p_old_flow_schedule_rec.outside_proc_var_acct IS NULL )
1215 THEN
1216 IF NOT MRP_Validate.Outside_Proc_Var_Acct(l_flow_schedule_rec.outside_proc_var_acct) THEN
1217 x_return_status := FND_API.G_RET_STS_ERROR;
1218 END IF;
1219 END IF;
1220
1221 IF l_flow_schedule_rec.overhead_account IS NOT NULL AND
1222 ( l_flow_schedule_rec.overhead_account <>
1223 p_old_flow_schedule_rec.overhead_account OR
1224 p_old_flow_schedule_rec.overhead_account IS NULL )
1225 THEN
1226 IF NOT MRP_Validate.Overhead_Account(l_flow_schedule_rec.overhead_account) THEN
1227 x_return_status := FND_API.G_RET_STS_ERROR;
1228 END IF;
1229 END IF;
1230
1231 IF l_flow_schedule_rec.overhead_variance_account IS NOT NULL AND
1232 ( l_flow_schedule_rec.overhead_variance_account <>
1233 p_old_flow_schedule_rec.overhead_variance_account OR
1234 p_old_flow_schedule_rec.overhead_variance_account IS NULL )
1235 THEN
1236 IF NOT MRP_Validate.Overhead_Variance_Account(l_flow_schedule_rec.overhead_variance_account) THEN
1237 x_return_status := FND_API.G_RET_STS_ERROR;
1238 END IF;
1239 END IF;
1240
1241 IF l_flow_schedule_rec.planned_quantity IS NOT NULL AND
1242 ( l_flow_schedule_rec.planned_quantity <>
1243 p_old_flow_schedule_rec.planned_quantity OR
1244 p_old_flow_schedule_rec.planned_quantity IS NULL )
1245 THEN
1246 IF NOT MRP_Validate.Planned_Quantity(l_flow_schedule_rec.planned_quantity) THEN
1247 x_return_status := FND_API.G_RET_STS_ERROR;
1248 END IF;
1249 END IF;
1250
1251 IF l_flow_schedule_rec.primary_item_id IS NOT NULL AND
1252 ( l_flow_schedule_rec.primary_item_id <>
1253 p_old_flow_schedule_rec.primary_item_id OR
1254 p_old_flow_schedule_rec.primary_item_id IS NULL )
1255 THEN
1256 IF NOT MRP_Validate.Primary_Item(l_flow_schedule_rec.primary_item_id) THEN
1257 x_return_status := FND_API.G_RET_STS_ERROR;
1258 END IF;
1259 END IF;
1260
1261 IF l_flow_schedule_rec.program_application_id IS NOT NULL AND
1262 ( l_flow_schedule_rec.program_application_id <>
1263 p_old_flow_schedule_rec.program_application_id OR
1264 p_old_flow_schedule_rec.program_application_id IS NULL )
1265 THEN
1266 IF NOT MRP_Validate.Program_Application(l_flow_schedule_rec.program_application_id) THEN
1267 x_return_status := FND_API.G_RET_STS_ERROR;
1268 END IF;
1269 END IF;
1270
1271 IF l_flow_schedule_rec.program_id IS NOT NULL AND
1272 ( l_flow_schedule_rec.program_id <>
1273 p_old_flow_schedule_rec.program_id OR
1274 p_old_flow_schedule_rec.program_id IS NULL )
1275 THEN
1276 IF NOT MRP_Validate.Program(l_flow_schedule_rec.program_id) THEN
1277 x_return_status := FND_API.G_RET_STS_ERROR;
1278 END IF;
1279 END IF;
1280
1281 IF l_flow_schedule_rec.program_update_date IS NOT NULL AND
1282 ( l_flow_schedule_rec.program_update_date <>
1283 p_old_flow_schedule_rec.program_update_date OR
1284 p_old_flow_schedule_rec.program_update_date IS NULL )
1285 THEN
1286 IF NOT MRP_Validate.Program_Update_Date(l_flow_schedule_rec.program_update_date) THEN
1287 x_return_status := FND_API.G_RET_STS_ERROR;
1288 END IF;
1289 END IF;
1290
1291 IF l_flow_schedule_rec.project_id IS NOT NULL AND
1292 ( l_flow_schedule_rec.project_id <>
1293 p_old_flow_schedule_rec.project_id OR
1294 p_old_flow_schedule_rec.project_id IS NULL )
1295 THEN
1296 IF NOT MRP_Validate.Project(l_flow_schedule_rec.project_id) THEN
1297 x_return_status := FND_API.G_RET_STS_ERROR;
1298 END IF;
1299 END IF;
1300
1301 IF l_flow_schedule_rec.quantity_completed IS NOT NULL AND
1302 ( l_flow_schedule_rec.quantity_completed <>
1303 p_old_flow_schedule_rec.quantity_completed OR
1304 p_old_flow_schedule_rec.quantity_completed IS NULL )
1305 THEN
1306 IF NOT MRP_Validate.Quantity_Completed(l_flow_schedule_rec.quantity_completed) THEN
1307 x_return_status := FND_API.G_RET_STS_ERROR;
1308 END IF;
1309 END IF;
1310
1311 IF l_flow_schedule_rec.request_id IS NOT NULL AND
1312 ( l_flow_schedule_rec.request_id <>
1313 p_old_flow_schedule_rec.request_id OR
1314 p_old_flow_schedule_rec.request_id IS NULL )
1315 THEN
1316 IF NOT MRP_Validate.Request(l_flow_schedule_rec.request_id) THEN
1317 x_return_status := FND_API.G_RET_STS_ERROR;
1318 END IF;
1319 END IF;
1320
1321 IF l_flow_schedule_rec.resource_account IS NOT NULL AND
1322 ( l_flow_schedule_rec.resource_account <>
1323 p_old_flow_schedule_rec.resource_account OR
1324 p_old_flow_schedule_rec.resource_account IS NULL )
1325 THEN
1326 IF NOT MRP_Validate.Resource_Account(l_flow_schedule_rec.resource_account) THEN
1327 x_return_status := FND_API.G_RET_STS_ERROR;
1328 END IF;
1329 END IF;
1330
1331 IF l_flow_schedule_rec.resource_variance_account IS NOT NULL AND
1332 ( l_flow_schedule_rec.resource_variance_account <>
1333 p_old_flow_schedule_rec.resource_variance_account OR
1334 p_old_flow_schedule_rec.resource_variance_account IS NULL )
1335 THEN
1336 IF NOT MRP_Validate.Resource_Variance_Account(l_flow_schedule_rec.resource_variance_account) THEN
1337 x_return_status := FND_API.G_RET_STS_ERROR;
1338 END IF;
1339 END IF;
1340
1341 IF l_flow_schedule_rec.routing_revision IS NOT NULL AND
1342 ( l_flow_schedule_rec.routing_revision <>
1343 p_old_flow_schedule_rec.routing_revision OR
1344 p_old_flow_schedule_rec.routing_revision IS NULL )
1345 THEN
1346 IF NOT MRP_Validate.Routing_Revision(l_flow_schedule_rec.routing_revision) THEN
1347 x_return_status := FND_API.G_RET_STS_ERROR;
1348 END IF;
1349 END IF;
1350
1351 IF l_flow_schedule_rec.routing_revision_date IS NOT NULL AND
1352 ( l_flow_schedule_rec.routing_revision_date <>
1353 p_old_flow_schedule_rec.routing_revision_date OR
1354 p_old_flow_schedule_rec.routing_revision_date IS NULL )
1355 THEN
1356 IF NOT MRP_Validate.Routing_Revision_Date(l_flow_schedule_rec.routing_revision_date) THEN
1357 x_return_status := FND_API.G_RET_STS_ERROR;
1358 END IF;
1359 END IF;
1360
1361 IF l_flow_schedule_rec.scheduled_completion_date IS NOT NULL AND
1362 ( l_flow_schedule_rec.scheduled_completion_date <>
1363 p_old_flow_schedule_rec.scheduled_completion_date OR
1364 p_old_flow_schedule_rec.scheduled_completion_date IS NULL )
1365 THEN
1366 IF NOT MRP_Validate.Scheduled_Completion_Date(l_flow_schedule_rec.scheduled_completion_date) THEN
1367 x_return_status := FND_API.G_RET_STS_ERROR;
1368 END IF;
1369 END IF;
1370
1371 IF l_flow_schedule_rec.scheduled_flag IS NOT NULL AND
1372 ( l_flow_schedule_rec.scheduled_flag <>
1373 p_old_flow_schedule_rec.scheduled_flag OR
1374 p_old_flow_schedule_rec.scheduled_flag IS NULL )
1375 THEN
1376 IF NOT MRP_Validate.Scheduled(l_flow_schedule_rec.scheduled_flag) THEN
1377 x_return_status := FND_API.G_RET_STS_ERROR;
1378 END IF;
1379 END IF;
1380
1381 IF l_flow_schedule_rec.scheduled_start_date IS NOT NULL AND
1382 ( l_flow_schedule_rec.scheduled_start_date <>
1383 p_old_flow_schedule_rec.scheduled_start_date OR
1384 p_old_flow_schedule_rec.scheduled_start_date IS NULL )
1385 THEN
1386 IF NOT MRP_Validate.Scheduled_Start_Date(l_flow_schedule_rec.scheduled_start_date) THEN
1387 x_return_status := FND_API.G_RET_STS_ERROR;
1388 END IF;
1389 END IF;
1390
1391 IF l_flow_schedule_rec.schedule_group_id IS NOT NULL AND
1392 ( l_flow_schedule_rec.schedule_group_id <>
1393 p_old_flow_schedule_rec.schedule_group_id OR
1394 p_old_flow_schedule_rec.schedule_group_id IS NULL )
1395 THEN
1396 IF NOT MRP_Validate.Schedule_Group(l_flow_schedule_rec.schedule_group_id) THEN
1397 x_return_status := FND_API.G_RET_STS_ERROR;
1398 END IF;
1399 END IF;
1400
1401 IF l_flow_schedule_rec.schedule_number IS NOT NULL AND
1402 ( l_flow_schedule_rec.schedule_number <>
1403 p_old_flow_schedule_rec.schedule_number OR
1404 p_old_flow_schedule_rec.schedule_number IS NULL )
1405 THEN
1406 IF NOT MRP_Validate.Schedule_Number(l_flow_schedule_rec.schedule_number) THEN
1407 x_return_status := FND_API.G_RET_STS_ERROR;
1408 END IF;
1409 END IF;
1410
1411 IF l_flow_schedule_rec.status IS NOT NULL AND
1412 ( l_flow_schedule_rec.status <>
1413 p_old_flow_schedule_rec.status OR
1414 p_old_flow_schedule_rec.status IS NULL )
1415 THEN
1416 IF NOT MRP_Validate.Status(l_flow_schedule_rec.status) THEN
1417 x_return_status := FND_API.G_RET_STS_ERROR;
1418 END IF;
1419 END IF;
1420
1421 IF l_flow_schedule_rec.std_cost_adjustment_acct IS NOT NULL AND
1422 ( l_flow_schedule_rec.std_cost_adjustment_acct <>
1423 p_old_flow_schedule_rec.std_cost_adjustment_acct OR
1424 p_old_flow_schedule_rec.std_cost_adjustment_acct IS NULL )
1425 THEN
1426 IF NOT MRP_Validate.Std_Cost_Adjustment_Acct(l_flow_schedule_rec.std_cost_adjustment_acct) THEN
1427 x_return_status := FND_API.G_RET_STS_ERROR;
1428 END IF;
1429 END IF;
1430
1431 IF l_flow_schedule_rec.task_id IS NOT NULL AND
1432 ( l_flow_schedule_rec.task_id <>
1433 p_old_flow_schedule_rec.task_id OR
1434 p_old_flow_schedule_rec.task_id IS NULL )
1435 THEN
1436 IF NOT MRP_Validate.Task(l_flow_schedule_rec.task_id) THEN
1437 x_return_status := FND_API.G_RET_STS_ERROR;
1438 END IF;
1439 END IF;
1440
1441 IF l_flow_schedule_rec.wip_entity_id IS NOT NULL AND
1442 ( l_flow_schedule_rec.wip_entity_id <>
1443 p_old_flow_schedule_rec.wip_entity_id OR
1444 p_old_flow_schedule_rec.wip_entity_id IS NULL )
1445 THEN
1446 IF NOT MRP_Validate.Wip_Entity(l_flow_schedule_rec.wip_entity_id) THEN
1447 x_return_status := FND_API.G_RET_STS_ERROR;
1448 END IF;
1449 END IF;
1450
1451 IF l_flow_schedule_rec.end_item_unit_number IS NOT NULL AND
1452 ( l_flow_schedule_rec.end_item_unit_number <>
1453 p_old_flow_schedule_rec.end_item_unit_number OR
1454 p_old_flow_schedule_rec.end_item_unit_number IS NULL )
1455 THEN
1456 IF NOT MRP_Validate.End_Item_Unit_Number(l_flow_schedule_rec.end_item_unit_number) THEN
1457 x_return_status := FND_API.G_RET_STS_ERROR;
1458 END IF;
1459 END IF;
1460
1461 IF l_flow_schedule_rec.quantity_scrapped IS NOT NULL AND
1462 ( l_flow_schedule_rec.quantity_scrapped <>
1463 p_old_flow_schedule_rec.quantity_scrapped OR
1464 p_old_flow_schedule_rec.quantity_scrapped IS NULL )
1465 THEN
1466 IF NOT MRP_Validate.Quantity_Scrapped(l_flow_schedule_rec.quantity_scrapped) THEN
1467 x_return_status := FND_API.G_RET_STS_ERROR;
1468 END IF;
1469 END IF;
1470
1471 IF (l_flow_schedule_rec.attribute1 IS NOT NULL AND
1472 ( l_flow_schedule_rec.attribute1 <>
1473 p_old_flow_schedule_rec.attribute1 OR
1474 p_old_flow_schedule_rec.attribute1 IS NULL ))
1475 OR (l_flow_schedule_rec.attribute10 IS NOT NULL AND
1476 ( l_flow_schedule_rec.attribute10 <>
1477 p_old_flow_schedule_rec.attribute10 OR
1478 p_old_flow_schedule_rec.attribute10 IS NULL ))
1479 OR (l_flow_schedule_rec.attribute11 IS NOT NULL AND
1480 ( l_flow_schedule_rec.attribute11 <>
1481 p_old_flow_schedule_rec.attribute11 OR
1482 p_old_flow_schedule_rec.attribute11 IS NULL ))
1483 OR (l_flow_schedule_rec.attribute12 IS NOT NULL AND
1484 ( l_flow_schedule_rec.attribute12 <>
1485 p_old_flow_schedule_rec.attribute12 OR
1486 p_old_flow_schedule_rec.attribute12 IS NULL ))
1487 OR (l_flow_schedule_rec.attribute13 IS NOT NULL AND
1488 ( l_flow_schedule_rec.attribute13 <>
1489 p_old_flow_schedule_rec.attribute13 OR
1490 p_old_flow_schedule_rec.attribute13 IS NULL ))
1491 OR (l_flow_schedule_rec.attribute14 IS NOT NULL AND
1492 ( l_flow_schedule_rec.attribute14 <>
1493 p_old_flow_schedule_rec.attribute14 OR
1494 p_old_flow_schedule_rec.attribute14 IS NULL ))
1495 OR (l_flow_schedule_rec.attribute15 IS NOT NULL AND
1496 ( l_flow_schedule_rec.attribute15 <>
1497 p_old_flow_schedule_rec.attribute15 OR
1498 p_old_flow_schedule_rec.attribute15 IS NULL ))
1499 OR (l_flow_schedule_rec.attribute2 IS NOT NULL AND
1500 ( l_flow_schedule_rec.attribute2 <>
1501 p_old_flow_schedule_rec.attribute2 OR
1502 p_old_flow_schedule_rec.attribute2 IS NULL ))
1503 OR (l_flow_schedule_rec.attribute3 IS NOT NULL AND
1504 ( l_flow_schedule_rec.attribute3 <>
1505 p_old_flow_schedule_rec.attribute3 OR
1506 p_old_flow_schedule_rec.attribute3 IS NULL ))
1507 OR (l_flow_schedule_rec.attribute4 IS NOT NULL AND
1508 ( l_flow_schedule_rec.attribute4 <>
1509 p_old_flow_schedule_rec.attribute4 OR
1510 p_old_flow_schedule_rec.attribute4 IS NULL ))
1511 OR (l_flow_schedule_rec.attribute5 IS NOT NULL AND
1512 ( l_flow_schedule_rec.attribute5 <>
1513 p_old_flow_schedule_rec.attribute5 OR
1514 p_old_flow_schedule_rec.attribute5 IS NULL ))
1515 OR (l_flow_schedule_rec.attribute6 IS NOT NULL AND
1516 ( l_flow_schedule_rec.attribute6 <>
1517 p_old_flow_schedule_rec.attribute6 OR
1518 p_old_flow_schedule_rec.attribute6 IS NULL ))
1519 OR (l_flow_schedule_rec.attribute7 IS NOT NULL AND
1520 ( l_flow_schedule_rec.attribute7 <>
1521 p_old_flow_schedule_rec.attribute7 OR
1522 p_old_flow_schedule_rec.attribute7 IS NULL ))
1523 OR (l_flow_schedule_rec.attribute8 IS NOT NULL AND
1524 ( l_flow_schedule_rec.attribute8 <>
1525 p_old_flow_schedule_rec.attribute8 OR
1526 p_old_flow_schedule_rec.attribute8 IS NULL ))
1527 OR (l_flow_schedule_rec.attribute9 IS NOT NULL AND
1528 ( l_flow_schedule_rec.attribute9 <>
1529 p_old_flow_schedule_rec.attribute9 OR
1530 p_old_flow_schedule_rec.attribute9 IS NULL ))
1531 OR (l_flow_schedule_rec.attribute_category IS NOT NULL AND
1532 ( l_flow_schedule_rec.attribute_category <>
1533 p_old_flow_schedule_rec.attribute_category OR
1534 p_old_flow_schedule_rec.attribute_category IS NULL ))
1535 THEN
1536
1537 -- These calls are temporarily commented out
1538
1539 /*
1540 FND_FLEX_DESC_VAL.Set_Column_Value
1541 ( column_name => 'ATTRIBUTE1'
1542 , column_value => l_flow_schedule_rec.attribute1
1543 );
1544 FND_FLEX_DESC_VAL.Set_Column_Value
1545 ( column_name => 'ATTRIBUTE10'
1546 , column_value => l_flow_schedule_rec.attribute10
1547 );
1548 FND_FLEX_DESC_VAL.Set_Column_Value
1549 ( column_name => 'ATTRIBUTE11'
1550 , column_value => l_flow_schedule_rec.attribute11
1551 );
1552 FND_FLEX_DESC_VAL.Set_Column_Value
1553 ( column_name => 'ATTRIBUTE12'
1554 , column_value => l_flow_schedule_rec.attribute12
1555 );
1556 FND_FLEX_DESC_VAL.Set_Column_Value
1557 ( column_name => 'ATTRIBUTE13'
1558 , column_value => l_flow_schedule_rec.attribute13
1559 );
1560 FND_FLEX_DESC_VAL.Set_Column_Value
1561 ( column_name => 'ATTRIBUTE14'
1562 , column_value => l_flow_schedule_rec.attribute14
1563 );
1564 FND_FLEX_DESC_VAL.Set_Column_Value
1565 ( column_name => 'ATTRIBUTE15'
1566 , column_value => l_flow_schedule_rec.attribute15
1567 );
1568 FND_FLEX_DESC_VAL.Set_Column_Value
1569 ( column_name => 'ATTRIBUTE2'
1570 , column_value => l_flow_schedule_rec.attribute2
1571 );
1572 FND_FLEX_DESC_VAL.Set_Column_Value
1573 ( column_name => 'ATTRIBUTE3'
1574 , column_value => l_flow_schedule_rec.attribute3
1575 );
1576 FND_FLEX_DESC_VAL.Set_Column_Value
1577 ( column_name => 'ATTRIBUTE4'
1578 , column_value => l_flow_schedule_rec.attribute4
1579 );
1580 FND_FLEX_DESC_VAL.Set_Column_Value
1581 ( column_name => 'ATTRIBUTE5'
1582 , column_value => l_flow_schedule_rec.attribute5
1583 );
1584 FND_FLEX_DESC_VAL.Set_Column_Value
1585 ( column_name => 'ATTRIBUTE6'
1586 , column_value => l_flow_schedule_rec.attribute6
1587 );
1588 FND_FLEX_DESC_VAL.Set_Column_Value
1589 ( column_name => 'ATTRIBUTE7'
1590 , column_value => l_flow_schedule_rec.attribute7
1591 );
1592 FND_FLEX_DESC_VAL.Set_Column_Value
1593 ( column_name => 'ATTRIBUTE8'
1594 , column_value => l_flow_schedule_rec.attribute8
1595 );
1596 FND_FLEX_DESC_VAL.Set_Column_Value
1597 ( column_name => 'ATTRIBUTE9'
1598 , column_value => l_flow_schedule_rec.attribute9
1599 );
1600 FND_FLEX_DESC_VAL.Set_Column_Value
1601 ( column_name => 'ATTRIBUTE_CATEGORY'
1602 , column_value => l_flow_schedule_rec.attribute_category
1603 );
1604 */
1605
1606 -- Validate descriptive flexfield.
1607
1608 IF NOT MRP_Validate.Desc_Flex( 'WIP_FLOW_SCHEDULE' ) THEN
1609 x_return_status := FND_API.G_RET_STS_ERROR;
1610 END IF;
1611
1612 END IF;
1613
1614 -- Done validating attributes
1615
1616 EXCEPTION
1617
1618 WHEN FND_API.G_EXC_ERROR THEN
1619
1620 x_return_status := FND_API.G_RET_STS_ERROR;
1621
1622 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1623
1624 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1625
1626 WHEN OTHERS THEN
1627
1628 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1629
1630 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1631 THEN
1632 FND_MSG_PUB.Add_Exc_Msg
1633 ( G_PKG_NAME
1634 , 'Attributes'
1635 );
1636 END IF;
1637
1638 END Attributes;
1639
1640 -- Procedure Entity_Delete
1641
1642 PROCEDURE Entity_Delete
1643 ( x_return_status OUT NOCOPY VARCHAR2
1644 , p_flow_schedule_rec IN MRP_Flow_Schedule_PVT.Flow_Schedule_PVT_Rec_Type
1645 )
1646 IS
1647 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1648 BEGIN
1649
1650 -- Validate entity delete.
1651
1652 NULL;
1653
1654 -- Done.
1655
1656 x_return_status := l_return_status;
1657
1658 EXCEPTION
1659
1660 WHEN FND_API.G_EXC_ERROR THEN
1661
1662 x_return_status := FND_API.G_RET_STS_ERROR;
1663
1664 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1665
1666 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1667
1668 WHEN OTHERS THEN
1669
1670 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1671
1672 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1673 THEN
1674 FND_MSG_PUB.Add_Exc_Msg
1675 ( G_PKG_NAME
1676 , 'Entity_Delete'
1677 );
1678 END IF;
1679
1680 END Entity_Delete;
1681
1682 END MRP_Validate_Flow_Schedule;
[Home] [Help]