1 PACKAGE BODY MRP_Validate_Shipping_Org AS
2 /* $Header: MRPLSHOB.pls 120.1 2005/06/16 11:02:44 ichoudhu noship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'MRP_Validate_Shipping_Org';
7
8 -- Procedure Entity
9
10 PROCEDURE Entity
11 ( x_return_status OUT NOCOPY VARCHAR2
12 , p_Shipping_Org_rec IN MRP_Sourcing_Rule_PUB.Shipping_Org_Rec_Type
13 , p_old_Shipping_Org_rec IN MRP_Sourcing_Rule_PUB.Shipping_Org_Rec_Type :=
14 MRP_Sourcing_Rule_PUB.G_MISS_SHIPPING_ORG_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_Shipping_Org_rec.Sr_Source_Id IS NULL
23 THEN
24
28 THEN
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)
29
30 FND_MESSAGE.SET_NAME('MRP','MRP_ATTRIBUTE_REQUIRED');
31 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Sr_Source_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 IF (p_Shipping_Org_rec.source_type = 1 OR
55 p_Shipping_Org_rec.source_type = 2) AND
56 p_Shipping_Org_rec.source_organization_id IS NULL THEN
57 dbms_output.put_line ('Error -source_organization_id required');
58 FND_MESSAGE.SET_NAME('MRP','MRP_ATTRIBUTE_REQUIRED');
59 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Source_Organization_Id');
60 FND_MSG_PUB.Add;
61 l_return_status := FND_API.G_RET_STS_ERROR;
62 END IF;
63
64 IF p_Shipping_Org_rec.source_type = 3 AND
65 p_Shipping_Org_rec.vendor_id IS NULL THEN
66 dbms_output.put_line ('Error - vendor_id required');
67 FND_MESSAGE.SET_NAME('MRP','MRP_ATTRIBUTE_REQUIRED');
68 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Vendor_Id');
69 FND_MSG_PUB.Add;
70 l_return_status := FND_API.G_RET_STS_ERROR;
71 END IF;
72
73 --
74 -- Validate attribute dependencies here.
75 --
76
77 IF p_Shipping_Org_rec.source_type = 2 AND
78 p_Shipping_Org_rec.ship_method <> NULL THEN
79 dbms_output.put_line ('error - ship_method shd be null');
80 FND_MESSAGE.SET_NAME('MRP','MRP_ATTRIBUTE_VALUE_ERROR');
81 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Ship_Method');
82 FND_MESSAGE.SET_TOKEN('DETAILS',
83 'Ship method should be NULL for this Source type');
84 FND_MSG_PUB.Add;
85 l_return_status := FND_API.G_RET_STS_ERROR;
86 END IF;
87
88 IF p_Shipping_Org_rec.vendor_site_id IS NOT NULL AND
89 p_Shipping_Org_rec.vendor_id IS NULL THEN
90 dbms_output.put_line ('error - vendor_id not specified');
91 FND_MESSAGE.SET_NAME('MRP','MRP_ATTRIBUTE_REQUIRED');
92 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Vendor_Id');
93 FND_MSG_PUB.Add;
94 l_return_status := FND_API.G_RET_STS_ERROR;
95 END IF;
96
97 -- Done validating entity
98
99 x_return_status := l_return_status;
100
101 EXCEPTION
102
103 WHEN FND_API.G_EXC_ERROR THEN
104
105 x_return_status := FND_API.G_RET_STS_ERROR;
106
107 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
108
109 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
110
111 WHEN OTHERS THEN
112
113 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
114
115 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
116 THEN
117 FND_MSG_PUB.Add_Exc_Msg
118 ( G_PKG_NAME
119 , 'Entity'
120 );
121 END IF;
122
123 END Entity;
124
125 -- Procedure Entity_Delete
126
127 PROCEDURE Entity_Delete
128 ( x_return_status OUT NOCOPY VARCHAR2
129 , p_Shipping_Org_rec IN MRP_Sourcing_Rule_PUB.Shipping_Org_Rec_Type
130 )
131 IS
132 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
133 l_count NUMBER;
134 BEGIN
135
136 -- Validate entity delete.
137
138 -- Check to see if this is the only shipping Org that is
139 -- associated with the receiving org. If yes do not allow delete
140
141 SELECT count(*)
142 INTO l_count
143 FROM MRP_SR_SOURCE_ORG
144 WHERE sr_receipt_id = p_Shipping_Org_rec.sr_receipt_id
145 AND sr_source_id <> p_Shipping_Org_rec.sr_source_id;
146
147 IF l_count = 0 THEN
148 dbms_output.put_line ('This is the only shp org for the rec org');
149 FND_MESSAGE.SET_NAME('MRP','MRP_OPERATION_ERROR');
150 FND_MESSAGE.SET_TOKEN('OPERATION','DELETE');
151 FND_MESSAGE.SET_TOKEN('ENTITY','Shipping_Org');
152 FND_MESSAGE.SET_TOKEN('DETAILS', 'There is only one Source ' ||
153 'Organization for this Receiving Organization');
154 FND_MSG_PUB.Add;
155 l_return_status := FND_API.G_RET_STS_ERROR;
156 END IF;
157
158
159 -- Done.
160
161 x_return_status := l_return_status;
162
163 EXCEPTION
164
165 WHEN FND_API.G_EXC_ERROR THEN
166
167 x_return_status := FND_API.G_RET_STS_ERROR;
168
169 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
170
171 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
172
173 WHEN OTHERS THEN
174
175 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
176
177 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
178 THEN
179 FND_MSG_PUB.Add_Exc_Msg
180 ( G_PKG_NAME
181 , 'Entity_Delete'
182 );
183 END IF;
184
185 END Entity_Delete;
186
187 -- Procedure Attributes
188
189 PROCEDURE Attributes
190 ( x_return_status OUT NOCOPY VARCHAR2
194 )
191 , p_Shipping_Org_rec IN MRP_Sourcing_Rule_PUB.Shipping_Org_Rec_Type
192 , p_old_Shipping_Org_rec IN MRP_Sourcing_Rule_PUB.Shipping_Org_Rec_Type :=
193 MRP_Sourcing_Rule_PUB.G_MISS_SHIPPING_ORG_REC
195 IS
196 BEGIN
197
198 x_return_status := FND_API.G_RET_STS_SUCCESS;
199
200 -- Validate Shipping_Org attributes
201
202 IF (p_Shipping_Org_rec.Sr_Source_Id IS NOT NULL AND
203 ( p_Shipping_Org_rec.Sr_Source_Id <>
204 p_old_Shipping_Org_rec.Sr_Source_Id OR
205 p_old_Shipping_Org_rec.Sr_Source_Id IS NULL ))
206 THEN
207 IF NOT MRP_Validate.Shipping_Org
208 ( p_Shipping_Org_rec.Sr_Source_Id
209 )
210 THEN
211 x_return_status := FND_API.G_RET_STS_ERROR;
212 END IF;
213 END IF;
214
215 IF p_Shipping_Org_rec.Allocation_Percent IS NOT NULL AND
216 ( p_Shipping_Org_rec.Allocation_Percent <>
217 p_old_Shipping_Org_rec.Allocation_Percent OR
218 p_old_Shipping_Org_rec.Allocation_Percent IS NULL )
219 THEN
220 IF NOT Val_Allocation_Percent(p_Shipping_Org_rec.Allocation_Percent) THEN
221 x_return_status := FND_API.G_RET_STS_ERROR;
222 END IF;
223 END IF;
224
225 IF p_Shipping_Org_rec.Attribute1 IS NOT NULL AND
226 ( p_Shipping_Org_rec.Attribute1 <>
227 p_old_Shipping_Org_rec.Attribute1 OR
228 p_old_Shipping_Org_rec.Attribute1 IS NULL )
229 THEN
230 IF NOT Val_Attribute1(p_Shipping_Org_rec.Attribute1) THEN
231 x_return_status := FND_API.G_RET_STS_ERROR;
232 END IF;
233 END IF;
234
235 IF p_Shipping_Org_rec.Attribute10 IS NOT NULL AND
236 ( p_Shipping_Org_rec.Attribute10 <>
237 p_old_Shipping_Org_rec.Attribute10 OR
238 p_old_Shipping_Org_rec.Attribute10 IS NULL )
239 THEN
240 IF NOT Val_Attribute10(p_Shipping_Org_rec.Attribute10) THEN
241 x_return_status := FND_API.G_RET_STS_ERROR;
242 END IF;
243 END IF;
244
245 IF p_Shipping_Org_rec.Attribute11 IS NOT NULL AND
246 ( p_Shipping_Org_rec.Attribute11 <>
247 p_old_Shipping_Org_rec.Attribute11 OR
248 p_old_Shipping_Org_rec.Attribute11 IS NULL )
249 THEN
250 IF NOT Val_Attribute11(p_Shipping_Org_rec.Attribute11) THEN
251 x_return_status := FND_API.G_RET_STS_ERROR;
252 END IF;
253 END IF;
254
255 IF p_Shipping_Org_rec.Attribute12 IS NOT NULL AND
256 ( p_Shipping_Org_rec.Attribute12 <>
257 p_old_Shipping_Org_rec.Attribute12 OR
258 p_old_Shipping_Org_rec.Attribute12 IS NULL )
259 THEN
260 IF NOT Val_Attribute12(p_Shipping_Org_rec.Attribute12) THEN
261 x_return_status := FND_API.G_RET_STS_ERROR;
262 END IF;
263 END IF;
264
265 IF p_Shipping_Org_rec.Attribute13 IS NOT NULL AND
266 ( p_Shipping_Org_rec.Attribute13 <>
267 p_old_Shipping_Org_rec.Attribute13 OR
268 p_old_Shipping_Org_rec.Attribute13 IS NULL )
269 THEN
270 IF NOT Val_Attribute13(p_Shipping_Org_rec.Attribute13) THEN
271 x_return_status := FND_API.G_RET_STS_ERROR;
272 END IF;
273 END IF;
274
275 IF p_Shipping_Org_rec.Attribute14 IS NOT NULL AND
276 ( p_Shipping_Org_rec.Attribute14 <>
277 p_old_Shipping_Org_rec.Attribute14 OR
278 p_old_Shipping_Org_rec.Attribute14 IS NULL )
279 THEN
280 IF NOT Val_Attribute14(p_Shipping_Org_rec.Attribute14) THEN
281 x_return_status := FND_API.G_RET_STS_ERROR;
282 END IF;
283 END IF;
284
285 IF p_Shipping_Org_rec.Attribute15 IS NOT NULL AND
286 ( p_Shipping_Org_rec.Attribute15 <>
287 p_old_Shipping_Org_rec.Attribute15 OR
288 p_old_Shipping_Org_rec.Attribute15 IS NULL )
289 THEN
290 IF NOT Val_Attribute15(p_Shipping_Org_rec.Attribute15) THEN
291 x_return_status := FND_API.G_RET_STS_ERROR;
292 END IF;
293 END IF;
294
295 IF p_Shipping_Org_rec.Attribute2 IS NOT NULL AND
296 ( p_Shipping_Org_rec.Attribute2 <>
297 p_old_Shipping_Org_rec.Attribute2 OR
298 p_old_Shipping_Org_rec.Attribute2 IS NULL )
299 THEN
300 IF NOT Val_Attribute2(p_Shipping_Org_rec.Attribute2) THEN
301 x_return_status := FND_API.G_RET_STS_ERROR;
302 END IF;
303 END IF;
304
305 IF p_Shipping_Org_rec.Attribute3 IS NOT NULL AND
306 ( p_Shipping_Org_rec.Attribute3 <>
307 p_old_Shipping_Org_rec.Attribute3 OR
308 p_old_Shipping_Org_rec.Attribute3 IS NULL )
309 THEN
310 IF NOT Val_Attribute3(p_Shipping_Org_rec.Attribute3) THEN
311 x_return_status := FND_API.G_RET_STS_ERROR;
312 END IF;
313 END IF;
314
315 IF p_Shipping_Org_rec.Attribute4 IS NOT NULL AND
316 ( p_Shipping_Org_rec.Attribute4 <>
317 p_old_Shipping_Org_rec.Attribute4 OR
318 p_old_Shipping_Org_rec.Attribute4 IS NULL )
319 THEN
320 IF NOT Val_Attribute4(p_Shipping_Org_rec.Attribute4) THEN
321 x_return_status := FND_API.G_RET_STS_ERROR;
322 END IF;
323 END IF;
324
325 IF p_Shipping_Org_rec.Attribute5 IS NOT NULL AND
326 ( p_Shipping_Org_rec.Attribute5 <>
327 p_old_Shipping_Org_rec.Attribute5 OR
328 p_old_Shipping_Org_rec.Attribute5 IS NULL )
329 THEN
330 IF NOT Val_Attribute5(p_Shipping_Org_rec.Attribute5) THEN
334
331 x_return_status := FND_API.G_RET_STS_ERROR;
332 END IF;
333 END IF;
335 IF p_Shipping_Org_rec.Attribute6 IS NOT NULL AND
336 ( p_Shipping_Org_rec.Attribute6 <>
337 p_old_Shipping_Org_rec.Attribute6 OR
338 p_old_Shipping_Org_rec.Attribute6 IS NULL )
339 THEN
340 IF NOT Val_Attribute6(p_Shipping_Org_rec.Attribute6) THEN
341 x_return_status := FND_API.G_RET_STS_ERROR;
342 END IF;
343 END IF;
344
345 IF p_Shipping_Org_rec.Attribute7 IS NOT NULL AND
346 ( p_Shipping_Org_rec.Attribute7 <>
347 p_old_Shipping_Org_rec.Attribute7 OR
348 p_old_Shipping_Org_rec.Attribute7 IS NULL )
349 THEN
350 IF NOT Val_Attribute7(p_Shipping_Org_rec.Attribute7) THEN
351 x_return_status := FND_API.G_RET_STS_ERROR;
352 END IF;
353 END IF;
354
355 IF p_Shipping_Org_rec.Attribute8 IS NOT NULL AND
356 ( p_Shipping_Org_rec.Attribute8 <>
357 p_old_Shipping_Org_rec.Attribute8 OR
358 p_old_Shipping_Org_rec.Attribute8 IS NULL )
359 THEN
360 IF NOT Val_Attribute8(p_Shipping_Org_rec.Attribute8) THEN
361 x_return_status := FND_API.G_RET_STS_ERROR;
362 END IF;
363 END IF;
364
365 IF p_Shipping_Org_rec.Attribute9 IS NOT NULL AND
366 ( p_Shipping_Org_rec.Attribute9 <>
367 p_old_Shipping_Org_rec.Attribute9 OR
368 p_old_Shipping_Org_rec.Attribute9 IS NULL )
369 THEN
370 IF NOT Val_Attribute9(p_Shipping_Org_rec.Attribute9) THEN
371 x_return_status := FND_API.G_RET_STS_ERROR;
372 END IF;
373 END IF;
374
375 IF p_Shipping_Org_rec.Attribute_Category IS NOT NULL AND
376 ( p_Shipping_Org_rec.Attribute_Category <>
377 p_old_Shipping_Org_rec.Attribute_Category OR
378 p_old_Shipping_Org_rec.Attribute_Category IS NULL )
379 THEN
380 IF NOT Val_Attribute_Category(p_Shipping_Org_rec.Attribute_Category) THEN
381 x_return_status := FND_API.G_RET_STS_ERROR;
382 END IF;
383 END IF;
384
385 IF p_Shipping_Org_rec.Created_By IS NOT NULL AND
386 ( p_Shipping_Org_rec.Created_By <>
387 p_old_Shipping_Org_rec.Created_By OR
388 p_old_Shipping_Org_rec.Created_By IS NULL )
389 THEN
390 IF NOT Val_Created_By(p_Shipping_Org_rec.Created_By) THEN
391 x_return_status := FND_API.G_RET_STS_ERROR;
392 END IF;
393 END IF;
394
395 IF p_Shipping_Org_rec.Creation_Date IS NOT NULL AND
396 ( p_Shipping_Org_rec.Creation_Date <>
397 p_old_Shipping_Org_rec.Creation_Date OR
398 p_old_Shipping_Org_rec.Creation_Date IS NULL )
399 THEN
400 IF NOT Val_Creation_Date(p_Shipping_Org_rec.Creation_Date) THEN
401 x_return_status := FND_API.G_RET_STS_ERROR;
402 END IF;
403 END IF;
404
405 IF p_Shipping_Org_rec.Last_Updated_By IS NOT NULL AND
406 ( p_Shipping_Org_rec.Last_Updated_By <>
407 p_old_Shipping_Org_rec.Last_Updated_By OR
408 p_old_Shipping_Org_rec.Last_Updated_By IS NULL )
409 THEN
410 IF NOT Val_Last_Updated_By(p_Shipping_Org_rec.Last_Updated_By) THEN
411 x_return_status := FND_API.G_RET_STS_ERROR;
412 END IF;
413 END IF;
414
415 IF p_Shipping_Org_rec.Last_Update_Date IS NOT NULL AND
416 ( p_Shipping_Org_rec.Last_Update_Date <>
417 p_old_Shipping_Org_rec.Last_Update_Date OR
418 p_old_Shipping_Org_rec.Last_Update_Date IS NULL )
419 THEN
420 IF NOT Val_Last_Update_Date(p_Shipping_Org_rec.Last_Update_Date) THEN
421 x_return_status := FND_API.G_RET_STS_ERROR;
422 END IF;
423 END IF;
424
425 IF p_Shipping_Org_rec.Last_Update_Login IS NOT NULL AND
426 ( p_Shipping_Org_rec.Last_Update_Login <>
427 p_old_Shipping_Org_rec.Last_Update_Login OR
428 p_old_Shipping_Org_rec.Last_Update_Login IS NULL )
429 THEN
430 IF NOT Val_Last_Update_Login(p_Shipping_Org_rec.Last_Update_Login) THEN
431 x_return_status := FND_API.G_RET_STS_ERROR;
432 END IF;
433 END IF;
434
435 IF p_Shipping_Org_rec.Program_Application_Id IS NOT NULL AND
436 ( p_Shipping_Org_rec.Program_Application_Id <>
437 p_old_Shipping_Org_rec.Program_Application_Id OR
438 p_old_Shipping_Org_rec.Program_Application_Id IS NULL )
439 THEN
440 IF NOT Val_Program_Application_Id(p_Shipping_Org_rec.Program_Application_Id) THEN
441 x_return_status := FND_API.G_RET_STS_ERROR;
442 END IF;
443 END IF;
444
445 IF p_Shipping_Org_rec.Program_Id IS NOT NULL AND
446 ( p_Shipping_Org_rec.Program_Id <>
447 p_old_Shipping_Org_rec.Program_Id OR
448 p_old_Shipping_Org_rec.Program_Id IS NULL )
449 THEN
450 IF NOT Val_Program_Id(p_Shipping_Org_rec.Program_Id) THEN
451 x_return_status := FND_API.G_RET_STS_ERROR;
452 END IF;
453 END IF;
454
455 IF p_Shipping_Org_rec.Program_Update_Date IS NOT NULL AND
456 ( p_Shipping_Org_rec.Program_Update_Date <>
457 p_old_Shipping_Org_rec.Program_Update_Date OR
458 p_old_Shipping_Org_rec.Program_Update_Date IS NULL )
459 THEN
460 IF NOT Val_Program_Update_Date(p_Shipping_Org_rec.Program_Update_Date) THEN
461 x_return_status := FND_API.G_RET_STS_ERROR;
462 END IF;
463 END IF;
464
465 IF p_Shipping_Org_rec.Rank IS NOT NULL AND
466 ( p_Shipping_Org_rec.Rank <>
470 IF NOT Val_Rank(p_Shipping_Org_rec.Rank) THEN
467 p_old_Shipping_Org_rec.Rank OR
468 p_old_Shipping_Org_rec.Rank IS NULL )
469 THEN
471 x_return_status := FND_API.G_RET_STS_ERROR;
472 END IF;
473 END IF;
474
475 IF p_Shipping_Org_rec.Request_Id IS NOT NULL AND
476 ( p_Shipping_Org_rec.Request_Id <>
477 p_old_Shipping_Org_rec.Request_Id OR
478 p_old_Shipping_Org_rec.Request_Id IS NULL )
479 THEN
480 IF NOT Val_Request_Id(p_Shipping_Org_rec.Request_Id) THEN
481 x_return_status := FND_API.G_RET_STS_ERROR;
482 END IF;
483 END IF;
484
485 IF p_Shipping_Org_rec.Secondary_Inventory IS NOT NULL AND
486 ( p_Shipping_Org_rec.Secondary_Inventory <>
487 p_old_Shipping_Org_rec.Secondary_Inventory OR
488 p_old_Shipping_Org_rec.Secondary_Inventory IS NULL )
489 THEN
490 IF NOT Val_Secondary_Inventory(p_Shipping_Org_rec.Secondary_Inventory) THEN
491 x_return_status := FND_API.G_RET_STS_ERROR;
492 END IF;
493 END IF;
494
495 IF p_Shipping_Org_rec.Ship_Method IS NOT NULL AND
496 ( p_Shipping_Org_rec.Ship_Method <>
497 p_old_Shipping_Org_rec.Ship_Method OR
498 p_old_Shipping_Org_rec.Ship_Method IS NULL )
499 THEN
500 IF NOT Val_Ship_Method( p_Shipping_Org_rec.sr_receipt_id,
501 p_Shipping_Org_rec.source_organization_id,
502 p_Shipping_Org_rec.Ship_Method) THEN
503 x_return_status := FND_API.G_RET_STS_ERROR;
504 END IF;
505 END IF;
506
507 IF p_Shipping_Org_rec.Source_Organization_Id IS NOT NULL AND
508 ( p_Shipping_Org_rec.Source_Organization_Id <>
509 p_old_Shipping_Org_rec.Source_Organization_Id OR
510 p_old_Shipping_Org_rec.Source_Organization_Id IS NULL )
511 THEN
512 IF NOT Val_Source_Organization_Id(p_Shipping_Org_rec.Source_Organization_Id) THEN
513 x_return_status := FND_API.G_RET_STS_ERROR;
514 END IF;
515 END IF;
516
517 IF p_Shipping_Org_rec.Source_Type IS NOT NULL AND
518 ( p_Shipping_Org_rec.Source_Type <>
519 p_old_Shipping_Org_rec.Source_Type OR
520 p_old_Shipping_Org_rec.Source_Type IS NULL )
521 THEN
522 IF NOT Val_Source_Type(p_Shipping_Org_rec.Source_Type) THEN
523 x_return_status := FND_API.G_RET_STS_ERROR;
524 END IF;
525 END IF;
526
527 IF p_Shipping_Org_rec.Sr_Receipt_Id IS NOT NULL AND
528 ( p_Shipping_Org_rec.Sr_Receipt_Id <>
529 p_old_Shipping_Org_rec.Sr_Receipt_Id OR
530 p_old_Shipping_Org_rec.Sr_Receipt_Id IS NULL )
531 THEN
532 IF NOT Val_Sr_Receipt_Id(p_Shipping_Org_rec.Sr_Receipt_Id) THEN
533 x_return_status := FND_API.G_RET_STS_ERROR;
534 END IF;
535 END IF;
536
537 IF p_Shipping_Org_rec.Vendor_Id IS NOT NULL AND
538 ( p_Shipping_Org_rec.Vendor_Id <>
539 p_old_Shipping_Org_rec.Vendor_Id OR
540 p_old_Shipping_Org_rec.Vendor_Id IS NULL )
541 THEN
542 IF NOT Val_Vendor_Id(p_Shipping_Org_rec.Vendor_Id) THEN
543 x_return_status := FND_API.G_RET_STS_ERROR;
544 END IF;
545 END IF;
546
547 IF p_Shipping_Org_rec.Vendor_Site_Id IS NOT NULL AND
548 ( p_Shipping_Org_rec.Vendor_Site_Id <>
549 p_old_Shipping_Org_rec.Vendor_Site_Id OR
550 p_old_Shipping_Org_rec.Vendor_Site_Id IS NULL )
551 THEN
552 IF NOT Val_Vendor_Site_Id(p_Shipping_Org_rec.Vendor_Id,
553 p_Shipping_Org_rec.Vendor_Site_Id) THEN
554 x_return_status := FND_API.G_RET_STS_ERROR;
555 END IF;
556 END IF;
557
558 -- Done validating attributes
559
560 EXCEPTION
561
562 WHEN FND_API.G_EXC_ERROR THEN
563
564 x_return_status := FND_API.G_RET_STS_ERROR;
565
566 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
567
568 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
569
570 WHEN OTHERS THEN
571
572 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
573
574 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
575 THEN
576 FND_MSG_PUB.Add_Exc_Msg
577 ( G_PKG_NAME
578 , 'Attributes'
579 );
580 END IF;
581
582 END Attributes;
583
584 -- Entity attribute validation functions.
585
586
587 -- Function Val_Allocation_Percent
588
589 FUNCTION Val_Allocation_Percent
590 ( p_Allocation_Percent IN NUMBER
591 ) RETURN BOOLEAN
592 IS
593 BEGIN
594
595 IF p_Allocation_Percent IS NULL OR
596 p_Allocation_Percent = FND_API.G_MISS_NUM
597 THEN
598 dbms_output.put_line ('Val_Allocation_Percent Error ');
599 FND_MESSAGE.SET_NAME('MRP','MRP_ATTRIBUTE_REQUIRED');
600 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Allocation_Percent');
601 FND_MSG_PUB.Add;
602 RETURN FALSE;
603 ELSIF p_Allocation_Percent > 100 THEN
604 dbms_output.put_line ('Val_Allocation_Percent Error ');
605 FND_MESSAGE.SET_NAME('MRP','MRP_ATTRIBUTE_VALUE_ERROR');
606 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Allocation_Percent');
607 FND_MESSAGE.SET_TOKEN('DETAILS',
608 'Allocation_Percent cannot be greater than 100');
609 FND_MSG_PUB.Add;
610 RETURN FALSE;
611 END IF;
612
613 RETURN TRUE;
614
615 END Val_Allocation_Percent;
616
617 -- Function Val_Attribute1
618
619 FUNCTION Val_Attribute1
620 ( p_Attribute1 IN VARCHAR2
621 ) RETURN BOOLEAN
622 IS
623 BEGIN
624
625 IF p_Attribute1 IS NULL OR
626 p_Attribute1 = FND_API.G_MISS_CHAR
627 THEN
628 RETURN TRUE;
629 END IF;
630
631 RETURN TRUE;
632
633 END Val_Attribute1;
634
635 -- Function Val_Attribute10
636
637 FUNCTION Val_Attribute10
638 ( p_Attribute10 IN VARCHAR2
639 ) RETURN BOOLEAN
640 IS
641 BEGIN
642
643 IF p_Attribute10 IS NULL OR
644 p_Attribute10 = FND_API.G_MISS_CHAR
645 THEN
646 RETURN TRUE;
647 END IF;
648
649 RETURN TRUE;
650
651 END Val_Attribute10;
652
653 -- Function Val_Attribute11
654
655 FUNCTION Val_Attribute11
656 ( p_Attribute11 IN VARCHAR2
657 ) RETURN BOOLEAN
658 IS
659 BEGIN
660
661 IF p_Attribute11 IS NULL OR
662 p_Attribute11 = FND_API.G_MISS_CHAR
663 THEN
664 RETURN TRUE;
665 END IF;
666
667 RETURN TRUE;
668
669 END Val_Attribute11;
670
671 -- Function Val_Attribute12
672
673 FUNCTION Val_Attribute12
674 ( p_Attribute12 IN VARCHAR2
675 ) RETURN BOOLEAN
676 IS
677 BEGIN
678
679 IF p_Attribute12 IS NULL OR
680 p_Attribute12 = FND_API.G_MISS_CHAR
681 THEN
682 RETURN TRUE;
683 END IF;
684
685 RETURN TRUE;
686
687 END Val_Attribute12;
688
689 -- Function Val_Attribute13
690
691 FUNCTION Val_Attribute13
692 ( p_Attribute13 IN VARCHAR2
693 ) RETURN BOOLEAN
694 IS
695 BEGIN
696
697 IF p_Attribute13 IS NULL OR
698 p_Attribute13 = FND_API.G_MISS_CHAR
699 THEN
700 RETURN TRUE;
701 END IF;
702
703 RETURN TRUE;
704
705 END Val_Attribute13;
706
707 -- Function Val_Attribute14
708
709 FUNCTION Val_Attribute14
710 ( p_Attribute14 IN VARCHAR2
711 ) RETURN BOOLEAN
712 IS
713 BEGIN
714
715 IF p_Attribute14 IS NULL OR
716 p_Attribute14 = FND_API.G_MISS_CHAR
717 THEN
718 RETURN TRUE;
719 END IF;
720
721 RETURN TRUE;
722
723 END Val_Attribute14;
724
725 -- Function Val_Attribute15
726
727 FUNCTION Val_Attribute15
728 ( p_Attribute15 IN VARCHAR2
729 ) RETURN BOOLEAN
730 IS
731 BEGIN
732
733 IF p_Attribute15 IS NULL OR
734 p_Attribute15 = FND_API.G_MISS_CHAR
735 THEN
736 RETURN TRUE;
737 END IF;
738
739 RETURN TRUE;
740
741 END Val_Attribute15;
742
743 -- Function Val_Attribute2
744
745 FUNCTION Val_Attribute2
746 ( p_Attribute2 IN VARCHAR2
747 ) RETURN BOOLEAN
748 IS
749 BEGIN
750
751 IF p_Attribute2 IS NULL OR
752 p_Attribute2 = FND_API.G_MISS_CHAR
753 THEN
754 RETURN TRUE;
755 END IF;
756
757 RETURN TRUE;
758
759 END Val_Attribute2;
760
761 -- Function Val_Attribute3
762
763 FUNCTION Val_Attribute3
764 ( p_Attribute3 IN VARCHAR2
765 ) RETURN BOOLEAN
766 IS
767 BEGIN
768
769 IF p_Attribute3 IS NULL OR
770 p_Attribute3 = FND_API.G_MISS_CHAR
771 THEN
772 RETURN TRUE;
773 END IF;
774
775 RETURN TRUE;
776
777 END Val_Attribute3;
778
779 -- Function Val_Attribute4
780
781 FUNCTION Val_Attribute4
782 ( p_Attribute4 IN VARCHAR2
783 ) RETURN BOOLEAN
784 IS
785 BEGIN
786
787 IF p_Attribute4 IS NULL OR
788 p_Attribute4 = FND_API.G_MISS_CHAR
789 THEN
790 RETURN TRUE;
791 END IF;
792
793 RETURN TRUE;
794
795 END Val_Attribute4;
796
797 -- Function Val_Attribute5
798
799 FUNCTION Val_Attribute5
800 ( p_Attribute5 IN VARCHAR2
801 ) RETURN BOOLEAN
802 IS
803 BEGIN
804
805 IF p_Attribute5 IS NULL OR
806 p_Attribute5 = FND_API.G_MISS_CHAR
807 THEN
808 RETURN TRUE;
809 END IF;
810
811 RETURN TRUE;
812
813 END Val_Attribute5;
814
815 -- Function Val_Attribute6
816
817 FUNCTION Val_Attribute6
818 ( p_Attribute6 IN VARCHAR2
819 ) RETURN BOOLEAN
820 IS
821 BEGIN
822
823 IF p_Attribute6 IS NULL OR
824 p_Attribute6 = FND_API.G_MISS_CHAR
825 THEN
826 RETURN TRUE;
827 END IF;
828
829 RETURN TRUE;
830
831 END Val_Attribute6;
832
833 -- Function Val_Attribute7
834
835 FUNCTION Val_Attribute7
836 ( p_Attribute7 IN VARCHAR2
837 ) RETURN BOOLEAN
838 IS
839 BEGIN
840
841 IF p_Attribute7 IS NULL OR
842 p_Attribute7 = FND_API.G_MISS_CHAR
843 THEN
844 RETURN TRUE;
845 END IF;
846
847 RETURN TRUE;
848
849 END Val_Attribute7;
850
851 -- Function Val_Attribute8
852
853 FUNCTION Val_Attribute8
854 ( p_Attribute8 IN VARCHAR2
855 ) RETURN BOOLEAN
856 IS
857 BEGIN
858
859 IF p_Attribute8 IS NULL OR
860 p_Attribute8 = FND_API.G_MISS_CHAR
861 THEN
862 RETURN TRUE;
863 END IF;
864
865 RETURN TRUE;
866
867 END Val_Attribute8;
868
869 -- Function Val_Attribute9
870
871 FUNCTION Val_Attribute9
872 ( p_Attribute9 IN VARCHAR2
873 ) RETURN BOOLEAN
874 IS
875 BEGIN
876
877 IF p_Attribute9 IS NULL OR
878 p_Attribute9 = FND_API.G_MISS_CHAR
879 THEN
880 RETURN TRUE;
881 END IF;
882
883 RETURN TRUE;
884
885 END Val_Attribute9;
886
887 -- Function Val_Attribute_Category
888
889 FUNCTION Val_Attribute_Category
890 ( p_Attribute_Category IN VARCHAR2
891 ) RETURN BOOLEAN
892 IS
893 BEGIN
894
895 IF p_Attribute_Category IS NULL OR
896 p_Attribute_Category = FND_API.G_MISS_CHAR
897 THEN
898 RETURN TRUE;
899 END IF;
900
901 RETURN TRUE;
902
903 END Val_Attribute_Category;
904
905 -- Function Val_Created_By
906
907 FUNCTION Val_Created_By
908 ( p_Created_By IN NUMBER
909 ) RETURN BOOLEAN
910 IS
911 BEGIN
912
913 IF p_Created_By IS NULL OR
914 p_Created_By = FND_API.G_MISS_NUM
915 THEN
916 RETURN TRUE;
917 END IF;
918
919 RETURN TRUE;
920
921 END Val_Created_By;
922
923 -- Function Val_Creation_Date
924
925 FUNCTION Val_Creation_Date
926 ( p_Creation_Date IN DATE
927 ) RETURN BOOLEAN
928 IS
929 BEGIN
930
931 IF p_Creation_Date IS NULL OR
932 p_Creation_Date = FND_API.G_MISS_DATE
933 THEN
934 RETURN TRUE;
935 END IF;
936
937 RETURN TRUE;
938
939 END Val_Creation_Date;
940
941 -- Function Val_Last_Updated_By
942
943 FUNCTION Val_Last_Updated_By
944 ( p_Last_Updated_By IN NUMBER
945 ) RETURN BOOLEAN
946 IS
947 BEGIN
948
949 IF p_Last_Updated_By IS NULL OR
950 p_Last_Updated_By = FND_API.G_MISS_NUM
951 THEN
952 RETURN TRUE;
953 END IF;
954
955 RETURN TRUE;
956
957 END Val_Last_Updated_By;
958
959 -- Function Val_Last_Update_Date
960
961 FUNCTION Val_Last_Update_Date
962 ( p_Last_Update_Date IN DATE
963 ) RETURN BOOLEAN
964 IS
965 BEGIN
966
967 IF p_Last_Update_Date IS NULL OR
968 p_Last_Update_Date = FND_API.G_MISS_DATE
969 THEN
970 RETURN TRUE;
971 END IF;
972
973 RETURN TRUE;
974
975 END Val_Last_Update_Date;
976
977 -- Function Val_Last_Update_Login
978
979 FUNCTION Val_Last_Update_Login
980 ( p_Last_Update_Login IN NUMBER
981 ) RETURN BOOLEAN
982 IS
983 BEGIN
984
985 IF p_Last_Update_Login IS NULL OR
986 p_Last_Update_Login = FND_API.G_MISS_NUM
987 THEN
988 RETURN TRUE;
989 END IF;
990
991 RETURN TRUE;
992
993 END Val_Last_Update_Login;
994
995 -- Function Val_Program_Application_Id
996
997 FUNCTION Val_Program_Application_Id
998 ( p_Program_Application_Id IN NUMBER
999 ) RETURN BOOLEAN
1000 IS
1001 BEGIN
1002
1003 IF p_Program_Application_Id IS NULL OR
1004 p_Program_Application_Id = FND_API.G_MISS_NUM
1005 THEN
1006 RETURN TRUE;
1007 END IF;
1008
1009 RETURN TRUE;
1010
1011 END Val_Program_Application_Id;
1012
1013 -- Function Val_Program_Id
1014
1015 FUNCTION Val_Program_Id
1016 ( p_Program_Id IN NUMBER
1017 ) RETURN BOOLEAN
1018 IS
1019 BEGIN
1020
1021 IF p_Program_Id IS NULL OR
1022 p_Program_Id = FND_API.G_MISS_NUM
1023 THEN
1024 RETURN TRUE;
1025 END IF;
1026
1027 RETURN TRUE;
1028
1029 END Val_Program_Id;
1030
1031 -- Function Val_Program_Update_Date
1032
1033 FUNCTION Val_Program_Update_Date
1034 ( p_Program_Update_Date IN DATE
1035 ) RETURN BOOLEAN
1036 IS
1037 BEGIN
1038
1039 IF p_Program_Update_Date IS NULL OR
1040 p_Program_Update_Date = FND_API.G_MISS_DATE
1041 THEN
1042 RETURN TRUE;
1043 END IF;
1044
1045 RETURN TRUE;
1046
1047 END Val_Program_Update_Date;
1048
1049 -- Function Val_Rank
1050
1051 FUNCTION Val_Rank
1052 ( p_Rank IN NUMBER
1053 ) RETURN BOOLEAN
1054 IS
1055 BEGIN
1056
1057 IF p_Rank IS NULL OR
1058 p_Rank = FND_API.G_MISS_NUM
1059 THEN
1060 RETURN TRUE;
1061 END IF;
1062
1063 RETURN TRUE;
1064
1065 END Val_Rank;
1066
1067 -- Function Val_Request_Id
1068
1069 FUNCTION Val_Request_Id
1070 ( p_Request_Id IN NUMBER
1071 ) RETURN BOOLEAN
1072 IS
1073 BEGIN
1074
1075 IF p_Request_Id IS NULL OR
1076 p_Request_Id = FND_API.G_MISS_NUM
1077 THEN
1078 RETURN TRUE;
1079 END IF;
1080
1081 RETURN TRUE;
1082
1083 END Val_Request_Id;
1084
1085 -- Function Val_Secondary_Inventory
1086
1087 FUNCTION Val_Secondary_Inventory
1088 ( p_Secondary_Inventory IN VARCHAR2
1089 ) RETURN BOOLEAN
1090 IS
1091 BEGIN
1092
1093 IF p_Secondary_Inventory IS NULL OR
1094 p_Secondary_Inventory = FND_API.G_MISS_CHAR
1095 THEN
1096 RETURN TRUE;
1097 END IF;
1098
1099 RETURN TRUE;
1100
1101 END Val_Secondary_Inventory;
1102
1103 -- Function Val_Ship_Method
1104
1105 FUNCTION Val_Ship_Method
1106 ( p_sr_receipt_id IN NUMBER
1107 , p_source_organization_id IN NUMBER
1108 , p_Ship_Method IN VARCHAR2
1109 ) RETURN BOOLEAN
1110 IS
1111
1112 l_count NUMBER;
1113
1114 BEGIN
1115
1116 IF p_Ship_Method IS NULL OR
1117 p_Ship_Method = FND_API.G_MISS_CHAR
1118 THEN
1119 RETURN TRUE;
1120 ELSE
1121
1122 SELECT count(*)
1123 INTO l_count
1124 FROM MTL_INTERORG_SHIP_METHODS
1125 WHERE ship_method = p_Ship_Method
1126 AND from_organization_id = p_source_organization_id
1127 AND to_organization_id in (
1128 SELECT receipt_organization_id
1129 FROM mrp_sr_receipt_org
1130 WHERE sr_receipt_id = p_sr_receipt_id);
1131
1132 IF l_count = 0 THEN
1133 dbms_output.put_line ('Val_Ship_Method Error');
1134 FND_MESSAGE.SET_NAME('MRP','MRP_ATTRIBUTE_VALUE_ERROR');
1135 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Ship_Method');
1136 FND_MSG_PUB.Add;
1137 RETURN FALSE;
1138 END IF;
1139
1140 END IF;
1141
1142 RETURN TRUE;
1143
1144 END Val_Ship_Method;
1145
1146 -- Function Val_Source_Organization_Id
1147
1148 FUNCTION Val_Source_Organization_Id
1149 ( p_Source_Organization_Id IN NUMBER
1150 ) RETURN BOOLEAN
1151 IS
1152
1153 l_count NUMBER;
1154
1155 BEGIN
1156
1157 IF p_Source_Organization_Id IS NULL OR
1158 p_Source_Organization_Id = FND_API.G_MISS_NUM
1159 THEN
1160 RETURN TRUE;
1161 ELSE
1162 SELECT count(*)
1163 INTO l_count
1164 FROM MTL_PARAMETERS
1165 WHERE organization_id = p_Source_Organization_Id;
1166
1167 IF l_count = 0 THEN
1168 dbms_output.put_line ('Val_Source_Organization_Id Error');
1169 FND_MESSAGE.SET_NAME('MRP','MRP_ATTRIBUTE_VALUE_ERROR');
1170 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Source_Organization_Id');
1171 FND_MSG_PUB.Add;
1172 RETURN FALSE;
1173 END IF;
1174 END IF;
1175
1176 RETURN TRUE;
1177
1178 END Val_Source_Organization_Id;
1179
1180 -- Function Val_Source_Type
1181
1182 FUNCTION Val_Source_Type
1183 ( p_Source_Type IN NUMBER
1184 ) RETURN BOOLEAN
1185 IS
1186 BEGIN
1187
1188 IF p_Source_Type IS NULL OR
1189 p_Source_Type = FND_API.G_MISS_NUM
1190 THEN
1191 dbms_output.put_line ('Val_Source_Type Error ');
1192 FND_MESSAGE.SET_NAME('MRP','MRP_ATTRIBUTE_REQUIRED');
1193 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Source_Type');
1194 FND_MSG_PUB.Add;
1195 RETURN FALSE;
1196 ELSIF (p_Source_Type <> 1) AND (p_Source_Type <> 2) AND
1197 (p_Source_Type <> 3) THEN
1198 FND_MESSAGE.SET_NAME('MRP','MRP_ATTRIBUTE_VALUE_ERROR');
1199 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Source_Type');
1200 FND_MSG_PUB.Add;
1201 dbms_output.put_line ('Val_Source_Type Error ');
1202 RETURN FALSE;
1203 END IF;
1204
1205 RETURN TRUE;
1206
1207 END Val_Source_Type;
1208
1209 -- Function Val_Sr_Receipt_Id
1210
1211 FUNCTION Val_Sr_Receipt_Id
1212 ( p_Sr_Receipt_Id IN NUMBER
1213 ) RETURN BOOLEAN
1214 IS
1215 BEGIN
1216
1217 IF p_Sr_Receipt_Id IS NULL OR
1218 p_Sr_Receipt_Id = FND_API.G_MISS_NUM
1219 THEN
1220 RETURN TRUE;
1221 END IF;
1222
1223 RETURN TRUE;
1224
1225 END Val_Sr_Receipt_Id;
1226
1227 -- Function Val_Vendor_Id
1228
1229 FUNCTION Val_Vendor_Id
1230 ( p_Vendor_Id IN NUMBER
1231 ) RETURN BOOLEAN
1232 IS
1233
1234 l_count NUMBER;
1235
1236 BEGIN
1237
1238 IF p_Vendor_Id IS NULL OR
1239 p_Vendor_Id = FND_API.G_MISS_NUM
1240 THEN
1241 RETURN TRUE;
1242 ELSE
1243 SELECT count(*)
1244 INTO l_count
1245 FROM po_vendors
1246 WHERE vendor_id = p_Vendor_Id;
1247
1248 IF l_count = 0 THEN
1249 dbms_output.put_line ('Val_Vendor_Id Error');
1250 FND_MESSAGE.SET_NAME('MRP','MRP_ATTRIBUTE_VALUE_ERROR');
1251 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Vendor_Id');
1252 FND_MSG_PUB.Add;
1253 RETURN FALSE;
1254 END IF;
1255 END IF;
1256
1257 RETURN TRUE;
1258
1259 END Val_Vendor_Id;
1260
1261 -- Function Val_Vendor_Site_Id
1262
1263 FUNCTION Val_Vendor_Site_Id
1264 ( p_vendor_id IN NUMBER
1265 , p_Vendor_Site_Id IN NUMBER
1266 ) RETURN BOOLEAN
1267 IS
1268
1269 l_count NUMBER;
1270
1271 BEGIN
1272
1273 IF p_Vendor_Site_Id IS NULL OR
1274 p_Vendor_Site_Id = FND_API.G_MISS_NUM
1275 THEN
1276 RETURN TRUE;
1277 ELSE
1278 SELECT count(*)
1279 INTO l_count
1280 FROM po_vendor_sites_all
1281 WHERE vendor_id = p_vendor_id
1282 AND vendor_site_id = p_vendor_site_id;
1283
1284 IF l_count = 0 THEN
1285 dbms_output.put_line ('Val_Vendor_Site_Id Error');
1286 FND_MESSAGE.SET_NAME('MRP','MRP_ATTRIBUTE_VALUE_ERROR');
1287 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Vendor_Site_Id');
1288 FND_MSG_PUB.Add;
1289 RETURN FALSE;
1290 END IF;
1291 END IF;
1292
1293 RETURN TRUE;
1294
1295 END Val_Vendor_Site_Id;
1296
1297 END MRP_Validate_Shipping_Org;
[Home] [Help]