1 PACKAGE BODY MRP_Validate_Sourcing_Rule AS
2 /* $Header: MRPLSRLB.pls 120.1 2005/06/16 11:42:12 ichoudhu noship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'MRP_Validate_Sourcing_Rule';
7
8 -- Procedure Entity
9
10 PROCEDURE Entity
11 ( x_return_status OUT NOCOPY VARCHAR2
12 , p_Sourcing_Rule_rec IN MRP_Sourcing_Rule_PUB.Sourcing_Rule_Rec_Type
13 , p_old_Sourcing_Rule_rec IN MRP_Sourcing_Rule_PUB.Sourcing_Rule_Rec_Type :=
14 MRP_Sourcing_Rule_PUB.G_MISS_SOURCING_RULE_REC
15 )
16 IS
17 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
18 BEGIN
19
20 -- Check required attributes.
21
22 IF p_Sourcing_Rule_rec.Sourcing_Rule_Id IS NULL
23 THEN
24
25 l_return_status := FND_API.G_RET_STS_ERROR;
26
27 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
28 THEN
29
30 FND_MESSAGE.SET_NAME('MRP','MRP_ATTRIBUTE_REQUIRED');
31 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Sourcing_Rule_Id');
32 FND_MSG_PUB.Add;
33
34 END IF;
35
36 END IF;
37
38 --
39 -- Check rest of required attributes here.
40 --
41
42
43 -- Return Error if a required attribute is missing.
44
45 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
46
47 RAISE FND_API.G_EXC_ERROR;
48
49 END IF;
50
51 --
52 -- Check conditionally required attributes here.
53 --
54
55
56 --
57 -- Validate attribute dependencies here.
58 --
59
60
61 -- Done validating entity
62
63 x_return_status := l_return_status;
64
65 EXCEPTION
66
67 WHEN FND_API.G_EXC_ERROR THEN
68
69 x_return_status := FND_API.G_RET_STS_ERROR;
70
71 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
72
73 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
74
75 WHEN OTHERS THEN
76
77 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
78
79 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
80 THEN
81 FND_MSG_PUB.Add_Exc_Msg
82 ( G_PKG_NAME
83 , 'Entity'
84 );
85 END IF;
86
87 END Entity;
88
89 -- Procedure Entity_Delete
90
91 PROCEDURE Entity_Delete
92 ( x_return_status OUT NOCOPY VARCHAR2
93 , p_Sourcing_Rule_rec IN MRP_Sourcing_Rule_PUB.Sourcing_Rule_Rec_Type
94 )
95 IS
96 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
97 l_count NUMBER;
98 BEGIN
99
100 -- Validate entity delete.
101
102 SELECT count(*)
103 INTO l_count
104 FROM MRP_SR_ASSIGNMENTS
105 WHERE sourcing_rule_id = p_Sourcing_Rule_rec.sourcing_rule_id;
106
107 IF l_count > 0 THEN
108 dbms_output.put_line ('Cannot delete Sourcing Rule');
109 FND_MESSAGE.SET_NAME('MRP','MRP_OPERATION_ERROR');
110 FND_MESSAGE.SET_TOKEN('OPERATION','DELETE');
111 FND_MESSAGE.SET_TOKEN('ENTITY','Sourcing_Rule');
112 FND_MESSAGE.SET_TOKEN('DETAILS','This Sourcing Rule has been assigned');
113 FND_MSG_PUB.Add;
114 l_return_status := FND_API.G_RET_STS_ERROR;
115 END IF;
116
117 -- Done.
118
119 x_return_status := l_return_status;
120
121 EXCEPTION
122
123 WHEN FND_API.G_EXC_ERROR THEN
124
125 x_return_status := FND_API.G_RET_STS_ERROR;
126
127 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
128
129 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
130
131 WHEN OTHERS THEN
132
133 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
134
135 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
136 THEN
137 FND_MSG_PUB.Add_Exc_Msg
138 ( G_PKG_NAME
139 , 'Entity_Delete'
140 );
141 END IF;
142
143 END Entity_Delete;
144
145 -- Procedure Attributes
146
147 PROCEDURE Attributes
148 ( x_return_status OUT NOCOPY VARCHAR2
149 , p_Sourcing_Rule_rec IN MRP_Sourcing_Rule_PUB.Sourcing_Rule_Rec_Type
150 , p_old_Sourcing_Rule_rec IN MRP_Sourcing_Rule_PUB.Sourcing_Rule_Rec_Type :=
151 MRP_Sourcing_Rule_PUB.G_MISS_SOURCING_RULE_REC
152 )
153 IS
154 BEGIN
155
156 x_return_status := FND_API.G_RET_STS_SUCCESS;
157
158 -- Validate Sourcing_Rule attributes
159
160 IF (p_Sourcing_Rule_rec.Sourcing_Rule_Id IS NOT NULL AND
161 ( p_Sourcing_Rule_rec.Sourcing_Rule_Id <>
162 p_old_Sourcing_Rule_rec.Sourcing_Rule_Id OR
163 p_old_Sourcing_Rule_rec.Sourcing_Rule_Id IS NULL ))
164 THEN
165 IF NOT MRP_Validate.Sourcing_Rule
166 ( p_Sourcing_Rule_rec.Sourcing_Rule_Id
167 )
168 THEN
169 dbms_output.put_line ('Error Sourcing_Rule_Id');
170 x_return_status := FND_API.G_RET_STS_ERROR;
171 END IF;
172 END IF;
173
174 IF p_Sourcing_Rule_rec.Attribute1 IS NOT NULL AND
175 ( p_Sourcing_Rule_rec.Attribute1 <>
176 p_old_Sourcing_Rule_rec.Attribute1 OR
177 p_old_Sourcing_Rule_rec.Attribute1 IS NULL )
178 THEN
179 IF NOT Val_Attribute1(p_Sourcing_Rule_rec.Attribute1) THEN
180 x_return_status := FND_API.G_RET_STS_ERROR;
181 END IF;
182 END IF;
183
184 IF p_Sourcing_Rule_rec.Attribute10 IS NOT NULL AND
185 ( p_Sourcing_Rule_rec.Attribute10 <>
186 p_old_Sourcing_Rule_rec.Attribute10 OR
187 p_old_Sourcing_Rule_rec.Attribute10 IS NULL )
188 THEN
189 IF NOT Val_Attribute10(p_Sourcing_Rule_rec.Attribute10) THEN
190 x_return_status := FND_API.G_RET_STS_ERROR;
191 END IF;
192 END IF;
193
194 IF p_Sourcing_Rule_rec.Attribute11 IS NOT NULL AND
195 ( p_Sourcing_Rule_rec.Attribute11 <>
196 p_old_Sourcing_Rule_rec.Attribute11 OR
197 p_old_Sourcing_Rule_rec.Attribute11 IS NULL )
198 THEN
199 IF NOT Val_Attribute11(p_Sourcing_Rule_rec.Attribute11) THEN
200 x_return_status := FND_API.G_RET_STS_ERROR;
201 END IF;
202 END IF;
203
204 IF p_Sourcing_Rule_rec.Attribute12 IS NOT NULL AND
205 ( p_Sourcing_Rule_rec.Attribute12 <>
206 p_old_Sourcing_Rule_rec.Attribute12 OR
207 p_old_Sourcing_Rule_rec.Attribute12 IS NULL )
208 THEN
209 IF NOT Val_Attribute12(p_Sourcing_Rule_rec.Attribute12) THEN
210 x_return_status := FND_API.G_RET_STS_ERROR;
211 END IF;
212 END IF;
213
214 IF p_Sourcing_Rule_rec.Attribute13 IS NOT NULL AND
215 ( p_Sourcing_Rule_rec.Attribute13 <>
216 p_old_Sourcing_Rule_rec.Attribute13 OR
217 p_old_Sourcing_Rule_rec.Attribute13 IS NULL )
218 THEN
219 IF NOT Val_Attribute13(p_Sourcing_Rule_rec.Attribute13) THEN
220 x_return_status := FND_API.G_RET_STS_ERROR;
221 END IF;
222 END IF;
223
224 IF p_Sourcing_Rule_rec.Attribute14 IS NOT NULL AND
225 ( p_Sourcing_Rule_rec.Attribute14 <>
226 p_old_Sourcing_Rule_rec.Attribute14 OR
227 p_old_Sourcing_Rule_rec.Attribute14 IS NULL )
228 THEN
229 IF NOT Val_Attribute14(p_Sourcing_Rule_rec.Attribute14) THEN
230 x_return_status := FND_API.G_RET_STS_ERROR;
231 END IF;
232 END IF;
233
234 IF p_Sourcing_Rule_rec.Attribute15 IS NOT NULL AND
235 ( p_Sourcing_Rule_rec.Attribute15 <>
236 p_old_Sourcing_Rule_rec.Attribute15 OR
237 p_old_Sourcing_Rule_rec.Attribute15 IS NULL )
238 THEN
239 IF NOT Val_Attribute15(p_Sourcing_Rule_rec.Attribute15) THEN
240 x_return_status := FND_API.G_RET_STS_ERROR;
241 END IF;
242 END IF;
243
244 IF p_Sourcing_Rule_rec.Attribute2 IS NOT NULL AND
245 ( p_Sourcing_Rule_rec.Attribute2 <>
246 p_old_Sourcing_Rule_rec.Attribute2 OR
247 p_old_Sourcing_Rule_rec.Attribute2 IS NULL )
248 THEN
249 IF NOT Val_Attribute2(p_Sourcing_Rule_rec.Attribute2) THEN
250 x_return_status := FND_API.G_RET_STS_ERROR;
251 END IF;
252 END IF;
253
254 IF p_Sourcing_Rule_rec.Attribute3 IS NOT NULL AND
255 ( p_Sourcing_Rule_rec.Attribute3 <>
256 p_old_Sourcing_Rule_rec.Attribute3 OR
257 p_old_Sourcing_Rule_rec.Attribute3 IS NULL )
258 THEN
259 IF NOT Val_Attribute3(p_Sourcing_Rule_rec.Attribute3) THEN
260 x_return_status := FND_API.G_RET_STS_ERROR;
261 END IF;
262 END IF;
263
264 IF p_Sourcing_Rule_rec.Attribute4 IS NOT NULL AND
265 ( p_Sourcing_Rule_rec.Attribute4 <>
266 p_old_Sourcing_Rule_rec.Attribute4 OR
267 p_old_Sourcing_Rule_rec.Attribute4 IS NULL )
268 THEN
269 IF NOT Val_Attribute4(p_Sourcing_Rule_rec.Attribute4) THEN
270 x_return_status := FND_API.G_RET_STS_ERROR;
271 END IF;
272 END IF;
273
274 IF p_Sourcing_Rule_rec.Attribute5 IS NOT NULL AND
275 ( p_Sourcing_Rule_rec.Attribute5 <>
276 p_old_Sourcing_Rule_rec.Attribute5 OR
277 p_old_Sourcing_Rule_rec.Attribute5 IS NULL )
278 THEN
279 IF NOT Val_Attribute5(p_Sourcing_Rule_rec.Attribute5) THEN
280 x_return_status := FND_API.G_RET_STS_ERROR;
281 END IF;
282 END IF;
283
284 IF p_Sourcing_Rule_rec.Attribute6 IS NOT NULL AND
285 ( p_Sourcing_Rule_rec.Attribute6 <>
286 p_old_Sourcing_Rule_rec.Attribute6 OR
287 p_old_Sourcing_Rule_rec.Attribute6 IS NULL )
288 THEN
289 IF NOT Val_Attribute6(p_Sourcing_Rule_rec.Attribute6) THEN
290 x_return_status := FND_API.G_RET_STS_ERROR;
291 END IF;
292 END IF;
293
294 IF p_Sourcing_Rule_rec.Attribute7 IS NOT NULL AND
295 ( p_Sourcing_Rule_rec.Attribute7 <>
296 p_old_Sourcing_Rule_rec.Attribute7 OR
297 p_old_Sourcing_Rule_rec.Attribute7 IS NULL )
298 THEN
299 IF NOT Val_Attribute7(p_Sourcing_Rule_rec.Attribute7) THEN
300 x_return_status := FND_API.G_RET_STS_ERROR;
301 END IF;
302 END IF;
303
304 IF p_Sourcing_Rule_rec.Attribute8 IS NOT NULL AND
305 ( p_Sourcing_Rule_rec.Attribute8 <>
306 p_old_Sourcing_Rule_rec.Attribute8 OR
307 p_old_Sourcing_Rule_rec.Attribute8 IS NULL )
308 THEN
309 IF NOT Val_Attribute8(p_Sourcing_Rule_rec.Attribute8) THEN
310 x_return_status := FND_API.G_RET_STS_ERROR;
311 END IF;
312 END IF;
313
314 IF p_Sourcing_Rule_rec.Attribute9 IS NOT NULL AND
315 ( p_Sourcing_Rule_rec.Attribute9 <>
316 p_old_Sourcing_Rule_rec.Attribute9 OR
317 p_old_Sourcing_Rule_rec.Attribute9 IS NULL )
318 THEN
319 IF NOT Val_Attribute9(p_Sourcing_Rule_rec.Attribute9) THEN
320 x_return_status := FND_API.G_RET_STS_ERROR;
321 END IF;
322 END IF;
323
324 IF p_Sourcing_Rule_rec.Attribute_Category IS NOT NULL AND
325 ( p_Sourcing_Rule_rec.Attribute_Category <>
326 p_old_Sourcing_Rule_rec.Attribute_Category OR
327 p_old_Sourcing_Rule_rec.Attribute_Category IS NULL )
328 THEN
329 IF NOT Val_Attribute_Category(p_Sourcing_Rule_rec.Attribute_Category) THEN
330 x_return_status := FND_API.G_RET_STS_ERROR;
331 END IF;
332 END IF;
333
334 IF p_Sourcing_Rule_rec.Created_By IS NOT NULL AND
335 ( p_Sourcing_Rule_rec.Created_By <>
336 p_old_Sourcing_Rule_rec.Created_By OR
337 p_old_Sourcing_Rule_rec.Created_By IS NULL )
338 THEN
339 IF NOT Val_Created_By(p_Sourcing_Rule_rec.Created_By) THEN
340 x_return_status := FND_API.G_RET_STS_ERROR;
341 END IF;
342 END IF;
343
344 IF p_Sourcing_Rule_rec.Creation_Date IS NOT NULL AND
345 ( p_Sourcing_Rule_rec.Creation_Date <>
346 p_old_Sourcing_Rule_rec.Creation_Date OR
347 p_old_Sourcing_Rule_rec.Creation_Date IS NULL )
348 THEN
349 IF NOT Val_Creation_Date(p_Sourcing_Rule_rec.Creation_Date) THEN
350 x_return_status := FND_API.G_RET_STS_ERROR;
351 END IF;
352 END IF;
353
354 IF p_Sourcing_Rule_rec.Description IS NOT NULL AND
355 ( p_Sourcing_Rule_rec.Description <>
356 p_old_Sourcing_Rule_rec.Description OR
357 p_old_Sourcing_Rule_rec.Description IS NULL )
358 THEN
359 IF NOT Val_Description(p_Sourcing_Rule_rec.Description) THEN
360 x_return_status := FND_API.G_RET_STS_ERROR;
361 END IF;
362 END IF;
363
364 IF p_Sourcing_Rule_rec.Last_Updated_By IS NOT NULL AND
365 ( p_Sourcing_Rule_rec.Last_Updated_By <>
366 p_old_Sourcing_Rule_rec.Last_Updated_By OR
367 p_old_Sourcing_Rule_rec.Last_Updated_By IS NULL )
368 THEN
369 IF NOT Val_Last_Updated_By(p_Sourcing_Rule_rec.Last_Updated_By) THEN
370 x_return_status := FND_API.G_RET_STS_ERROR;
371 END IF;
372 END IF;
373
374 IF p_Sourcing_Rule_rec.Last_Update_Date IS NOT NULL AND
375 ( p_Sourcing_Rule_rec.Last_Update_Date <>
376 p_old_Sourcing_Rule_rec.Last_Update_Date OR
377 p_old_Sourcing_Rule_rec.Last_Update_Date IS NULL )
378 THEN
379 IF NOT Val_Last_Update_Date(p_Sourcing_Rule_rec.Last_Update_Date) THEN
380 x_return_status := FND_API.G_RET_STS_ERROR;
381 END IF;
382 END IF;
383
384 IF p_Sourcing_Rule_rec.Last_Update_Login IS NOT NULL AND
385 ( p_Sourcing_Rule_rec.Last_Update_Login <>
386 p_old_Sourcing_Rule_rec.Last_Update_Login OR
387 p_old_Sourcing_Rule_rec.Last_Update_Login IS NULL )
388 THEN
389 IF NOT Val_Last_Update_Login(p_Sourcing_Rule_rec.Last_Update_Login) THEN
390 x_return_status := FND_API.G_RET_STS_ERROR;
391 END IF;
392 END IF;
393
394 IF p_Sourcing_Rule_rec.Organization_Id IS NOT NULL AND
395 ( p_Sourcing_Rule_rec.Organization_Id <>
396 p_old_Sourcing_Rule_rec.Organization_Id OR
397 p_old_Sourcing_Rule_rec.Organization_Id IS NULL )
398 THEN
399 IF (p_Sourcing_Rule_rec.operation = MRP_Globals.G_OPR_UPDATE) AND
400 (p_old_Sourcing_Rule_rec.Organization_Id IS NOT NULL) THEN
401 -- organization_id changed while updating - NOT ALLOWED
402 FND_MESSAGE.SET_NAME('MRP','MRP_ATTRIBUTE_VALUE_ERROR');
403 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Organization_Id');
404 FND_MESSAGE.SET_TOKEN('DETAILS',
405 'Cannot change Organization while Updating');
406 FND_MSG_PUB.Add;
407 x_return_status := FND_API.G_RET_STS_ERROR;
408 ELSIF NOT Val_Organization_Id(p_Sourcing_Rule_rec.Organization_Id) THEN
409 dbms_output.put_line ('Error Organization_Id');
410 x_return_status := FND_API.G_RET_STS_ERROR;
411 END IF;
412 END IF;
413
414 IF p_Sourcing_Rule_rec.Planning_Active IS NOT NULL AND
415 ( p_Sourcing_Rule_rec.Planning_Active <>
416 p_old_Sourcing_Rule_rec.Planning_Active OR
417 p_old_Sourcing_Rule_rec.Planning_Active IS NULL )
418 THEN
419 IF NOT Val_Planning_Active(p_Sourcing_Rule_rec.Planning_Active) THEN
420 x_return_status := FND_API.G_RET_STS_ERROR;
421 END IF;
422 END IF;
423
424 IF p_Sourcing_Rule_rec.Program_Application_Id IS NOT NULL AND
425 ( p_Sourcing_Rule_rec.Program_Application_Id <>
426 p_old_Sourcing_Rule_rec.Program_Application_Id OR
427 p_old_Sourcing_Rule_rec.Program_Application_Id IS NULL )
428 THEN
429 IF NOT Val_Program_Application_Id(p_Sourcing_Rule_rec.Program_Application_Id) THEN
430 x_return_status := FND_API.G_RET_STS_ERROR;
431 END IF;
432 END IF;
433
434 IF p_Sourcing_Rule_rec.Program_Id IS NOT NULL AND
435 ( p_Sourcing_Rule_rec.Program_Id <>
436 p_old_Sourcing_Rule_rec.Program_Id OR
437 p_old_Sourcing_Rule_rec.Program_Id IS NULL )
438 THEN
439 IF NOT Val_Program_Id(p_Sourcing_Rule_rec.Program_Id) THEN
440 x_return_status := FND_API.G_RET_STS_ERROR;
441 END IF;
442 END IF;
443
444 IF p_Sourcing_Rule_rec.Program_Update_Date IS NOT NULL AND
445 ( p_Sourcing_Rule_rec.Program_Update_Date <>
446 p_old_Sourcing_Rule_rec.Program_Update_Date OR
447 p_old_Sourcing_Rule_rec.Program_Update_Date IS NULL )
448 THEN
449 IF NOT Val_Program_Update_Date(p_Sourcing_Rule_rec.Program_Update_Date) THEN
450 x_return_status := FND_API.G_RET_STS_ERROR;
451 END IF;
452 END IF;
453
454 IF p_Sourcing_Rule_rec.Request_Id IS NOT NULL AND
455 ( p_Sourcing_Rule_rec.Request_Id <>
456 p_old_Sourcing_Rule_rec.Request_Id OR
457 p_old_Sourcing_Rule_rec.Request_Id IS NULL )
458 THEN
459 IF NOT Val_Request_Id(p_Sourcing_Rule_rec.Request_Id) THEN
460 x_return_status := FND_API.G_RET_STS_ERROR;
461 END IF;
462 END IF;
463
464 IF p_Sourcing_Rule_rec.Sourcing_Rule_Name IS NOT NULL AND
465 ( p_Sourcing_Rule_rec.Sourcing_Rule_Name <>
466 p_old_Sourcing_Rule_rec.Sourcing_Rule_Name OR
467 p_old_Sourcing_Rule_rec.Sourcing_Rule_Name IS NULL )
468 THEN
469 -- Bug 3015208
470 IF NOT Val_Sourcing_Rule_Name(p_Sourcing_Rule_rec.Sourcing_Rule_Name,
471 p_Sourcing_Rule_rec.organization_id) THEN
472 dbms_output.put_line ('Error Sourcing_Rule_Name');
473 x_return_status := FND_API.G_RET_STS_ERROR;
474 END IF;
475 END IF;
476
477 IF p_Sourcing_Rule_rec.Sourcing_Rule_Type IS NOT NULL AND
478 ( p_Sourcing_Rule_rec.Sourcing_Rule_Type <>
479 p_old_Sourcing_Rule_rec.Sourcing_Rule_Type OR
480 p_old_Sourcing_Rule_rec.Sourcing_Rule_Type IS NULL )
481 THEN
482 IF (p_Sourcing_Rule_rec.operation = MRP_Globals.G_OPR_UPDATE) AND
483 (p_old_Sourcing_Rule_rec.Sourcing_Rule_Type IS NOT NULL) THEN
484 -- sourcing_rule_type changed while updating - NOT ALLOWED
485 FND_MESSAGE.SET_NAME('MRP','MRP_ATTRIBUTE_VALUE_ERROR');
486 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Sourcing_Rule_Type');
487 FND_MESSAGE.SET_TOKEN('DETAILS',
488 'Cannot Change Sourcing Rule Type while updating');
489 FND_MSG_PUB.Add;
490 x_return_status := FND_API.G_RET_STS_ERROR;
491 ELSIF NOT Val_Sourcing_Rule_Type(p_Sourcing_Rule_rec.Sourcing_Rule_Type) THEN
492 dbms_output.put_line ('Error Sourcing_Rule_Type');
493 x_return_status := FND_API.G_RET_STS_ERROR;
494 END IF;
495 END IF;
496
497 IF p_Sourcing_Rule_rec.Status IS NOT NULL AND
498 ( p_Sourcing_Rule_rec.Status <>
499 p_old_Sourcing_Rule_rec.Status OR
500 p_old_Sourcing_Rule_rec.Status IS NULL )
501 THEN
502 IF NOT Val_Status(p_Sourcing_Rule_rec.Status) THEN
503 dbms_output.put_line ('Error Status');
504 x_return_status := FND_API.G_RET_STS_ERROR;
505 END IF;
506 END IF;
507
508 -- Done validating attributes
509
510 EXCEPTION
511
512 WHEN FND_API.G_EXC_ERROR THEN
513
514 x_return_status := FND_API.G_RET_STS_ERROR;
515
516 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
517
518 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
519
520 WHEN OTHERS THEN
521
522 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
523
524 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
525 THEN
526 FND_MSG_PUB.Add_Exc_Msg
527 ( G_PKG_NAME
528 , 'Attributes'
529 );
530 END IF;
531
532 END Attributes;
533
534 -- Entity attribute validation functions.
535
536
537 -- Function Val_Attribute1
538
539 FUNCTION Val_Attribute1
540 ( p_Attribute1 IN VARCHAR2
541 ) RETURN BOOLEAN
542 IS
543 BEGIN
544
545 IF p_Attribute1 IS NULL OR
546 p_Attribute1 = FND_API.G_MISS_CHAR
547 THEN
548 RETURN TRUE;
549 END IF;
550
551 RETURN TRUE;
552
553 END Val_Attribute1;
554
555 -- Function Val_Attribute10
556
557 FUNCTION Val_Attribute10
558 ( p_Attribute10 IN VARCHAR2
559 ) RETURN BOOLEAN
560 IS
561 BEGIN
562
563 IF p_Attribute10 IS NULL OR
564 p_Attribute10 = FND_API.G_MISS_CHAR
565 THEN
566 RETURN TRUE;
567 END IF;
568
569 RETURN TRUE;
570
571 END Val_Attribute10;
572
573 -- Function Val_Attribute11
574
575 FUNCTION Val_Attribute11
576 ( p_Attribute11 IN VARCHAR2
577 ) RETURN BOOLEAN
578 IS
579 BEGIN
580
581 IF p_Attribute11 IS NULL OR
582 p_Attribute11 = FND_API.G_MISS_CHAR
583 THEN
584 RETURN TRUE;
585 END IF;
586
587 RETURN TRUE;
588
589 END Val_Attribute11;
590
591 -- Function Val_Attribute12
592
593 FUNCTION Val_Attribute12
594 ( p_Attribute12 IN VARCHAR2
595 ) RETURN BOOLEAN
596 IS
597 BEGIN
598
599 IF p_Attribute12 IS NULL OR
600 p_Attribute12 = FND_API.G_MISS_CHAR
601 THEN
602 RETURN TRUE;
603 END IF;
604
605 RETURN TRUE;
606
607 END Val_Attribute12;
608
609 -- Function Val_Attribute13
610
611 FUNCTION Val_Attribute13
612 ( p_Attribute13 IN VARCHAR2
613 ) RETURN BOOLEAN
614 IS
615 BEGIN
616
617 IF p_Attribute13 IS NULL OR
618 p_Attribute13 = FND_API.G_MISS_CHAR
619 THEN
620 RETURN TRUE;
621 END IF;
622
623 RETURN TRUE;
624
625 END Val_Attribute13;
626
627 -- Function Val_Attribute14
628
629 FUNCTION Val_Attribute14
630 ( p_Attribute14 IN VARCHAR2
631 ) RETURN BOOLEAN
632 IS
633 BEGIN
634
635 IF p_Attribute14 IS NULL OR
636 p_Attribute14 = FND_API.G_MISS_CHAR
637 THEN
638 RETURN TRUE;
639 END IF;
640
641 RETURN TRUE;
642
643 END Val_Attribute14;
644
645 -- Function Val_Attribute15
646
647 FUNCTION Val_Attribute15
648 ( p_Attribute15 IN VARCHAR2
649 ) RETURN BOOLEAN
650 IS
651 BEGIN
652
653 IF p_Attribute15 IS NULL OR
654 p_Attribute15 = FND_API.G_MISS_CHAR
655 THEN
656 RETURN TRUE;
657 END IF;
658
659 RETURN TRUE;
660
661 END Val_Attribute15;
662
663 -- Function Val_Attribute2
664
665 FUNCTION Val_Attribute2
666 ( p_Attribute2 IN VARCHAR2
667 ) RETURN BOOLEAN
668 IS
669 BEGIN
670
671 IF p_Attribute2 IS NULL OR
672 p_Attribute2 = FND_API.G_MISS_CHAR
673 THEN
674 RETURN TRUE;
675 END IF;
676
677 RETURN TRUE;
678
679 END Val_Attribute2;
680
681 -- Function Val_Attribute3
682
683 FUNCTION Val_Attribute3
684 ( p_Attribute3 IN VARCHAR2
685 ) RETURN BOOLEAN
686 IS
687 BEGIN
688
689 IF p_Attribute3 IS NULL OR
690 p_Attribute3 = FND_API.G_MISS_CHAR
691 THEN
692 RETURN TRUE;
693 END IF;
694
695 RETURN TRUE;
696
697 END Val_Attribute3;
698
699 -- Function Val_Attribute4
700
701 FUNCTION Val_Attribute4
702 ( p_Attribute4 IN VARCHAR2
703 ) RETURN BOOLEAN
704 IS
705 BEGIN
706
707 IF p_Attribute4 IS NULL OR
708 p_Attribute4 = FND_API.G_MISS_CHAR
709 THEN
710 RETURN TRUE;
711 END IF;
712
713 RETURN TRUE;
714
715 END Val_Attribute4;
716
717 -- Function Val_Attribute5
718
719 FUNCTION Val_Attribute5
720 ( p_Attribute5 IN VARCHAR2
721 ) RETURN BOOLEAN
722 IS
723 BEGIN
724
725 IF p_Attribute5 IS NULL OR
726 p_Attribute5 = FND_API.G_MISS_CHAR
727 THEN
728 RETURN TRUE;
729 END IF;
730
731 RETURN TRUE;
732
733 END Val_Attribute5;
734
735 -- Function Val_Attribute6
736
737 FUNCTION Val_Attribute6
738 ( p_Attribute6 IN VARCHAR2
739 ) RETURN BOOLEAN
740 IS
741 BEGIN
742
743 IF p_Attribute6 IS NULL OR
744 p_Attribute6 = FND_API.G_MISS_CHAR
745 THEN
746 RETURN TRUE;
747 END IF;
748
749 RETURN TRUE;
750
751 END Val_Attribute6;
752
753 -- Function Val_Attribute7
754
755 FUNCTION Val_Attribute7
756 ( p_Attribute7 IN VARCHAR2
757 ) RETURN BOOLEAN
758 IS
759 BEGIN
760
761 IF p_Attribute7 IS NULL OR
762 p_Attribute7 = FND_API.G_MISS_CHAR
763 THEN
764 RETURN TRUE;
765 END IF;
766
767 RETURN TRUE;
768
769 END Val_Attribute7;
770
771 -- Function Val_Attribute8
772
773 FUNCTION Val_Attribute8
774 ( p_Attribute8 IN VARCHAR2
775 ) RETURN BOOLEAN
776 IS
777 BEGIN
778
779 IF p_Attribute8 IS NULL OR
780 p_Attribute8 = FND_API.G_MISS_CHAR
781 THEN
782 RETURN TRUE;
783 END IF;
784
785 RETURN TRUE;
786
787 END Val_Attribute8;
788
789 -- Function Val_Attribute9
790
791 FUNCTION Val_Attribute9
792 ( p_Attribute9 IN VARCHAR2
793 ) RETURN BOOLEAN
794 IS
795 BEGIN
796
797 IF p_Attribute9 IS NULL OR
798 p_Attribute9 = FND_API.G_MISS_CHAR
799 THEN
800 RETURN TRUE;
801 END IF;
802
803 RETURN TRUE;
804
805 END Val_Attribute9;
806
807 -- Function Val_Attribute_Category
808
809 FUNCTION Val_Attribute_Category
810 ( p_Attribute_Category IN VARCHAR2
811 ) RETURN BOOLEAN
812 IS
813 BEGIN
814
815 IF p_Attribute_Category IS NULL OR
816 p_Attribute_Category = FND_API.G_MISS_CHAR
817 THEN
818 RETURN TRUE;
819 END IF;
820
821 RETURN TRUE;
822
823 END Val_Attribute_Category;
824
825 -- Function Val_Created_By
826
827 FUNCTION Val_Created_By
828 ( p_Created_By IN NUMBER
829 ) RETURN BOOLEAN
830 IS
831 BEGIN
832
833 IF p_Created_By IS NULL OR
834 p_Created_By = FND_API.G_MISS_NUM
835 THEN
836 RETURN TRUE;
837 END IF;
838
839 RETURN TRUE;
840
841 END Val_Created_By;
842
843 -- Function Val_Creation_Date
844
845 FUNCTION Val_Creation_Date
846 ( p_Creation_Date IN DATE
847 ) RETURN BOOLEAN
848 IS
849 BEGIN
850
851 IF p_Creation_Date IS NULL OR
852 p_Creation_Date = FND_API.G_MISS_DATE
853 THEN
854 RETURN TRUE;
855 END IF;
856
857 RETURN TRUE;
858
859 END Val_Creation_Date;
860
861 -- Function Val_Description
862
863 FUNCTION Val_Description
864 ( p_Description IN VARCHAR2
865 ) RETURN BOOLEAN
866 IS
867 BEGIN
868
869 IF p_Description IS NULL OR
870 p_Description = FND_API.G_MISS_CHAR
871 THEN
872 RETURN TRUE;
873 END IF;
874
875 RETURN TRUE;
876
877 END Val_Description;
878
879 -- Function Val_Last_Updated_By
880
881 FUNCTION Val_Last_Updated_By
882 ( p_Last_Updated_By IN NUMBER
883 ) RETURN BOOLEAN
884 IS
885 BEGIN
886
887 IF p_Last_Updated_By IS NULL OR
888 p_Last_Updated_By = FND_API.G_MISS_NUM
889 THEN
890 RETURN TRUE;
891 END IF;
892
893 RETURN TRUE;
894
895 END Val_Last_Updated_By;
896
897 -- Function Val_Last_Update_Date
898
899 FUNCTION Val_Last_Update_Date
900 ( p_Last_Update_Date IN DATE
901 ) RETURN BOOLEAN
902 IS
903 BEGIN
904
905 IF p_Last_Update_Date IS NULL OR
906 p_Last_Update_Date = FND_API.G_MISS_DATE
907 THEN
908 RETURN TRUE;
909 END IF;
910
911 RETURN TRUE;
912
913 END Val_Last_Update_Date;
914
915 -- Function Val_Last_Update_Login
916
917 FUNCTION Val_Last_Update_Login
918 ( p_Last_Update_Login IN NUMBER
919 ) RETURN BOOLEAN
920 IS
921 BEGIN
922
923 IF p_Last_Update_Login IS NULL OR
924 p_Last_Update_Login = FND_API.G_MISS_NUM
925 THEN
926 RETURN TRUE;
927 END IF;
928
929 RETURN TRUE;
930
931 END Val_Last_Update_Login;
932
933 -- Function Val_Organization_Id
934
935 FUNCTION Val_Organization_Id
936 ( p_Organization_Id IN NUMBER
937 ) RETURN BOOLEAN
938 IS
939
940 l_count number;
941
942 BEGIN
943
944 IF p_Organization_Id IS NULL OR
945 p_Organization_Id = FND_API.G_MISS_NUM
946 THEN
947 RETURN TRUE;
948 ELSE
949 SELECT count(*)
950 INTO l_count
951 FROM MTL_PARAMETERS
952 WHERE organization_id = p_Organization_Id;
953
954 IF l_count = 0 THEN
955 FND_MESSAGE.SET_NAME('MRP','MRP_ATTRIBUTE_VALUE_ERROR');
956 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Organization_Id');
957 FND_MSG_PUB.Add;
958 RETURN FALSE;
959 END IF;
960 END IF;
961
962 RETURN TRUE;
963
964 END Val_Organization_Id;
965
966 -- Function Val_Planning_Active
967
968 FUNCTION Val_Planning_Active
969 ( p_Planning_Active IN NUMBER
970 ) RETURN BOOLEAN
971 IS
972 BEGIN
973
974 IF p_Planning_Active IS NULL OR
975 p_Planning_Active = FND_API.G_MISS_NUM
976 THEN
977 RETURN TRUE;
978 END IF;
979
980 RETURN TRUE;
981
982 END Val_Planning_Active;
983
984 -- Function Val_Program_Application_Id
985
986 FUNCTION Val_Program_Application_Id
987 ( p_Program_Application_Id IN NUMBER
988 ) RETURN BOOLEAN
989 IS
990 BEGIN
991
992 IF p_Program_Application_Id IS NULL OR
993 p_Program_Application_Id = FND_API.G_MISS_NUM
994 THEN
995 RETURN TRUE;
996 END IF;
997
998 RETURN TRUE;
999
1000 END Val_Program_Application_Id;
1001
1002 -- Function Val_Program_Id
1003
1004 FUNCTION Val_Program_Id
1005 ( p_Program_Id IN NUMBER
1006 ) RETURN BOOLEAN
1007 IS
1008 BEGIN
1009
1010 IF p_Program_Id IS NULL OR
1011 p_Program_Id = FND_API.G_MISS_NUM
1012 THEN
1013 RETURN TRUE;
1014 END IF;
1015
1016 RETURN TRUE;
1017
1018 END Val_Program_Id;
1019
1020 -- Function Val_Program_Update_Date
1021
1022 FUNCTION Val_Program_Update_Date
1023 ( p_Program_Update_Date IN DATE
1024 ) RETURN BOOLEAN
1025 IS
1026 BEGIN
1027
1028 IF p_Program_Update_Date IS NULL OR
1029 p_Program_Update_Date = FND_API.G_MISS_DATE
1030 THEN
1031 RETURN TRUE;
1032 END IF;
1033
1034 RETURN TRUE;
1035
1036 END Val_Program_Update_Date;
1037
1038 -- Function Val_Request_Id
1039
1040 FUNCTION Val_Request_Id
1041 ( p_Request_Id IN NUMBER
1042 ) RETURN BOOLEAN
1043 IS
1044 BEGIN
1045
1046 IF p_Request_Id IS NULL OR
1047 p_Request_Id = FND_API.G_MISS_NUM
1048 THEN
1049 RETURN TRUE;
1050 END IF;
1051
1052 RETURN TRUE;
1053
1054 END Val_Request_Id;
1055
1056 -- Function Val_Sourcing_Rule_Name
1057
1058 FUNCTION Val_Sourcing_Rule_Name
1059 ( p_Sourcing_Rule_Name IN VARCHAR2,
1060 p_organization_id IN NUMBER
1061 ) RETURN BOOLEAN
1062 IS
1063
1064 l_count number;
1065
1066 BEGIN
1067
1068 IF p_Sourcing_Rule_Name IS NULL OR
1069 p_Sourcing_Rule_Name = FND_API.G_MISS_CHAR
1070 THEN
1071 FND_MESSAGE.SET_NAME('MRP','MRP_ATTRIBUTE_REQUIRED');
1072 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Sourcing_Rule_Name');
1073 FND_MSG_PUB.Add;
1074 RETURN FALSE;
1075 ELSE
1076 -- make sure that this sourcing rule name does
1077 -- not already exist in the given org
1078 -- bug 3015208
1079 SELECT count(*)
1080 INTO l_count
1081 FROM MRP_SOURCING_RULES
1082 WHERE sourcing_rule_name = p_Sourcing_Rule_Name
1083 AND organization_id = p_organization_id;
1084
1085 IF l_count > 0 THEN
1086 FND_MESSAGE.SET_NAME('MRP','MRP_ATTRIBUTE_VALUE_ERROR');
1087 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Sourcing_Rule_Name');
1088 FND_MESSAGE.SET_TOKEN('DETAILS',
1089 'This Sourcing Rule already exists');
1090 FND_MSG_PUB.Add;
1091 RETURN FALSE;
1092 END IF;
1093
1094 END IF;
1095
1096 RETURN TRUE;
1097
1098 END Val_Sourcing_Rule_Name;
1099
1100 -- Function Val_Sourcing_Rule_Type
1101
1102 FUNCTION Val_Sourcing_Rule_Type
1103 ( p_Sourcing_Rule_Type IN NUMBER
1104 ) RETURN BOOLEAN
1105 IS
1106 BEGIN
1107
1108 IF p_Sourcing_Rule_Type IS NULL OR
1109 p_Sourcing_Rule_Type = FND_API.G_MISS_NUM
1110 THEN
1111 FND_MESSAGE.SET_NAME('MRP','MRP_ATTRIBUTE_REQUIRED');
1112 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Sourcing_Rule_Type');
1113 FND_MSG_PUB.Add;
1114 RETURN FALSE;
1115 ELSE
1116 IF (p_Sourcing_Rule_Type <> 1) AND (p_Sourcing_Rule_Type <> 2) THEN
1117 FND_MESSAGE.SET_NAME('MRP','MRP_ATTRIBUTE_VALUE_ERROR');
1118 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Sourcing_Rule_Type');
1119 FND_MSG_PUB.Add;
1120 RETURN FALSE;
1121 END IF;
1122 END IF;
1123
1124 RETURN TRUE;
1125
1126 END Val_Sourcing_Rule_Type;
1127
1128 -- Function Val_Status
1129
1130 FUNCTION Val_Status
1131 ( p_Status IN NUMBER
1132 ) RETURN BOOLEAN
1133 IS
1134 BEGIN
1135
1136 IF p_Status IS NULL OR
1137 p_Status = FND_API.G_MISS_NUM
1138 THEN
1139 FND_MESSAGE.SET_NAME('MRP','MRP_ATTRIBUTE_REQUIRED');
1140 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Status');
1141 FND_MSG_PUB.Add;
1142 RETURN FALSE;
1143 ELSE
1144 IF p_Status <> 1 THEN
1145 FND_MESSAGE.SET_NAME('MRP','MRP_ATTRIBUTE_VALUE_ERROR');
1146 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Status');
1147 FND_MSG_PUB.Add;
1148 RETURN FALSE;
1149 END IF;
1150 END IF;
1151
1152 RETURN TRUE;
1153
1154 END Val_Status;
1155
1156 END MRP_Validate_Sourcing_Rule;