[Home] [Help]
PACKAGE BODY: APPS.OE_DEFAULT_TOLERANCE
Source
1 PACKAGE BODY OE_Default_Tolerance AS
2 /* $Header: OEXDCISB.pls 120.0 2005/06/01 00:36:45 appldev noship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Default_Tolerance';
7
8 -- Start of Comments
9 -- API name OE_Default_Tolerance
10 -- Type Public
11 -- Version Current version = 1.0
12 -- Initial version = 1.0
13
14 FUNCTION Under_Ship_Tol_From_Item
15 (
16 p_database_object_name IN VARCHAR2
17 ,p_attribute_code IN VARCHAR2
18 )
19 RETURN VARCHAR2
20 IS
21 l_inventory_item_id NUMBER;
22 l_under_shipment_tolerance NUMBER;
23 l_under_return_tolerance NUMBER;
24 BEGIN
25 l_inventory_item_id := ONT_Line_Def_Hdlr.g_record.inventory_item_id;
26 select under_shipment_tolerance, under_return_tolerance into l_under_shipment_tolerance, l_under_return_tolerance
27 from oe_cust_item_settings
28 where internal_item_id = l_inventory_item_id
29 and customer_id is null
30 and site_use_id is null;
31 if p_database_object_name = 'SHIP' then
32 return to_char(l_under_shipment_tolerance);
33 elsif p_database_object_name = 'RETURN' then
34 return to_char(l_under_return_tolerance);
35 else
36 return null;
37 end if;
38
39 EXCEPTION
40
41 WHEN NO_DATA_FOUND THEN
42
43 return null;
44
45 WHEN FND_API.G_EXC_ERROR THEN
46
47 RAISE FND_API.G_EXC_ERROR;
48
49 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
50
51 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
52
53 WHEN OTHERS THEN
54
55 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
56
57 END Under_Ship_Tol_From_Item ;
58
59 FUNCTION Under_Ship_Tol_From_Customer
60 (
61 p_database_object_name IN VARCHAR2
62 ,p_attribute_code IN VARCHAR2
63 )
64 RETURN VARCHAR2
65 IS
66 l_customer_id NUMBER;
67 l_under_shipment_tolerance NUMBER;
68 l_under_return_tolerance NUMBER;
69 BEGIN
70 l_customer_id := ONT_Line_Def_Hdlr.g_record.sold_to_org_id;
71 select under_shipment_tolerance, under_return_tolerance into l_under_shipment_tolerance, l_under_return_tolerance
72 from oe_cust_item_settings
73 where customer_id = l_customer_id
74 and internal_item_id is null
75 and site_use_id is null;
76 if p_database_object_name = 'SHIP' then
77 return to_char(l_under_shipment_tolerance);
78 elsif p_database_object_name = 'RETURN' then
79 return to_char(l_under_return_tolerance);
80 else
81 return null;
82 end if;
83
84 EXCEPTION
85
86 WHEN NO_DATA_FOUND THEN
87
88 return null;
89
90 WHEN FND_API.G_EXC_ERROR THEN
91
92 RAISE FND_API.G_EXC_ERROR;
93
94 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
95
96 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
97
98 WHEN OTHERS THEN
99
100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
101
102 END Under_Ship_Tol_From_Customer ;
103
104 FUNCTION Under_Ship_Tol_From_Site
105 (
106 p_database_object_name IN VARCHAR2
107 ,p_attribute_code IN VARCHAR2
108 )
109 RETURN VARCHAR2
110 IS
111 l_site_use_id NUMBER;
112 l_under_shipment_tolerance NUMBER;
113 l_under_return_tolerance NUMBER;
114 BEGIN
115 if p_attribute_code = 'BILL_TO' then
116 l_site_use_id := ONT_Line_Def_Hdlr.g_record.invoice_to_org_id;
117 elsif p_attribute_code = 'SHIP_TO' then
118 l_site_use_id := ONT_Line_Def_Hdlr.g_record.ship_to_org_id;
119 else
120 return null;
121 end if;
122
123 select under_shipment_tolerance, under_return_tolerance into l_under_shipment_tolerance, l_under_return_tolerance
124 from oe_cust_item_settings
125 where site_use_id = l_site_use_id
126 and customer_id is null
127 and internal_item_id is null;
128 if p_database_object_name = 'SHIP' then
129 return to_char(l_under_shipment_tolerance);
130 elsif p_database_object_name = 'RETURN' then
131 return to_char(l_under_return_tolerance);
132 else
133 return null;
134 end if;
135
136 EXCEPTION
137
138 WHEN NO_DATA_FOUND THEN
139
140 return null;
141
142 WHEN FND_API.G_EXC_ERROR THEN
143
144 RAISE FND_API.G_EXC_ERROR;
145
146 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
147
148 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
149
150 WHEN OTHERS THEN
151
152 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
153
154 END Under_Ship_Tol_From_Site;
155
156 FUNCTION Under_Ship_Tol_From_Cust_Item
157 (
158 p_database_object_name IN VARCHAR2
159 ,p_attribute_code IN VARCHAR2
160 )
161 RETURN VARCHAR2
162 IS
163 l_customer_id NUMBER;
164 l_internal_item_id NUMBER;
165 l_under_shipment_tolerance NUMBER;
166 l_under_return_tolerance NUMBER;
167 BEGIN
168 l_internal_item_id := ONT_Line_Def_Hdlr.g_record.inventory_item_id;
169 l_customer_id := ONT_Line_Def_Hdlr.g_record.sold_to_org_id;
170 select under_shipment_tolerance, under_return_tolerance into l_under_shipment_tolerance, l_under_return_tolerance
171 from oe_cust_item_settings
172 where internal_item_id = l_internal_item_id
173 and customer_id = l_customer_id
174 and site_use_id is null;
175 if p_database_object_name = 'SHIP' then
176 return to_char(l_under_shipment_tolerance);
177 elsif p_database_object_name = 'RETURN' then
178 return to_char(l_under_return_tolerance);
179 else
180 return null;
181 end if;
182
183 EXCEPTION
184
185 WHEN NO_DATA_FOUND THEN
186
187 return null;
188
189 WHEN FND_API.G_EXC_ERROR THEN
190
191 RAISE FND_API.G_EXC_ERROR;
192
193 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
194
195 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
196
197 WHEN OTHERS THEN
198
199 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
200
201 END Under_Ship_Tol_From_Cust_Item ;
202
203 FUNCTION Under_Ship_Tol_From_Site_Item
204 (
205 p_database_object_name IN VARCHAR2
206 ,p_attribute_code IN VARCHAR2
207 )
208 RETURN VARCHAR2
209 IS
210 l_site_use_id NUMBER;
211 l_internal_item_id NUMBER;
212 l_under_shipment_tolerance NUMBER;
213 l_under_return_tolerance NUMBER;
214 BEGIN
215 l_internal_item_id := ONT_Line_Def_Hdlr.g_record.inventory_item_id;
216 if p_attribute_code = 'BILL_TO' then
217 l_site_use_id := ONT_Line_Def_Hdlr.g_record.invoice_to_org_id;
218 elsif p_attribute_code = 'SHIP_TO' then
219 l_site_use_id := ONT_Line_Def_Hdlr.g_record.ship_to_org_id;
220 else
221 return null;
222 end if;
223 select under_shipment_tolerance, under_return_tolerance into l_under_shipment_tolerance, l_under_return_tolerance
224 from oe_cust_item_settings
225 where internal_item_id = l_internal_item_id
226 and site_use_id = l_site_use_id;
227 if p_database_object_name = 'SHIP' then
228 return to_char(l_under_shipment_tolerance);
229 elsif p_database_object_name = 'RETURN' then
230 return to_char(l_under_return_tolerance);
231 else
232 return null;
233 end if;
234
235 EXCEPTION
236
237 WHEN NO_DATA_FOUND THEN
238
239 return null;
240
241 WHEN FND_API.G_EXC_ERROR THEN
242
243 RAISE FND_API.G_EXC_ERROR;
244
245 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
246
247 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
248
249 WHEN OTHERS THEN
250
251 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
252
253 END Under_Ship_Tol_From_Site_Item ;
254
255 FUNCTION Over_Ship_Tol_From_Item
256 (
257 p_database_object_name IN VARCHAR2
258 ,p_attribute_code IN VARCHAR2
259 )
260 RETURN VARCHAR2
261 IS
262 l_inventory_item_id NUMBER;
263 l_Over_shipment_tolerance NUMBER;
264 l_Over_return_tolerance NUMBER;
265 BEGIN
266 l_inventory_item_id := ONT_Line_Def_Hdlr.g_record.inventory_item_id;
267 select Over_shipment_tolerance, Over_return_tolerance into l_Over_shipment_tolerance, l_Over_return_tolerance
268 from oe_cust_item_settings
269 where internal_item_id = l_inventory_item_id
270 and customer_id is null
271 and site_use_id is null;
272 if p_database_object_name = 'SHIP' then
273 return to_char(l_Over_shipment_tolerance);
274 elsif p_database_object_name = 'RETURN' then
275 return to_char(l_Over_return_tolerance);
276 else
277 return null;
278 end if;
279
280 EXCEPTION
281
282 WHEN NO_DATA_FOUND THEN
283
284 return null;
285
286 WHEN FND_API.G_EXC_ERROR THEN
287
288 RAISE FND_API.G_EXC_ERROR;
289
290 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
291
292 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
293
294 WHEN OTHERS THEN
295
296 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
297
298 END Over_Ship_Tol_From_Item ;
299
300 FUNCTION Over_Ship_Tol_From_Customer
301 (
302 p_database_object_name IN VARCHAR2
303 ,p_attribute_code IN VARCHAR2
304 )
305 RETURN VARCHAR2
306 IS
307 l_customer_id NUMBER;
308 l_Over_shipment_tolerance NUMBER;
309 l_Over_return_tolerance NUMBER;
310 BEGIN
311 l_customer_id := ONT_Line_Def_Hdlr.g_record.sold_to_org_id;
312 select Over_shipment_tolerance, Over_return_tolerance into l_Over_shipment_tolerance, l_Over_return_tolerance
313 from oe_cust_item_settings
314 where customer_id = l_customer_id
315 and internal_item_id is null
316 and site_use_id is null;
317 if p_database_object_name = 'SHIP' then
318 return to_char(l_Over_shipment_tolerance);
319 elsif p_database_object_name = 'RETURN' then
320 return to_char(l_Over_return_tolerance);
321 else
322 return null;
323 end if;
324
325 EXCEPTION
326
327 WHEN NO_DATA_FOUND THEN
328
329 return null;
330
331 WHEN FND_API.G_EXC_ERROR THEN
332
333 RAISE FND_API.G_EXC_ERROR;
334
335 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
336
337 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
338
339 WHEN OTHERS THEN
340
341 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
342
343 END Over_Ship_Tol_From_Customer ;
344
345 FUNCTION Over_Ship_Tol_From_Site
346 (
347 p_database_object_name IN VARCHAR2
348 ,p_attribute_code IN VARCHAR2
349 )
350 RETURN VARCHAR2
351 IS
352 l_site_use_id NUMBER;
353 l_Over_shipment_tolerance NUMBER;
354 l_Over_return_tolerance NUMBER;
355 BEGIN
356 if p_attribute_code = 'BILL_TO' then
357 l_site_use_id := ONT_Line_Def_Hdlr.g_record.invoice_to_org_id;
358 elsif p_attribute_code = 'SHIP_TO' then
359 l_site_use_id := ONT_Line_Def_Hdlr.g_record.ship_to_org_id;
360 else
361 return null;
362 end if;
363
364 select Over_shipment_tolerance, Over_return_tolerance into l_Over_shipment_tolerance, l_Over_return_tolerance
365 from oe_cust_item_settings
366 where site_use_id = l_site_use_id
367 and customer_id is null
368 and internal_item_id is null;
369 if p_database_object_name = 'SHIP' then
370 return to_char(l_Over_shipment_tolerance);
371 elsif p_database_object_name = 'RETURN' then
372 return to_char(l_Over_return_tolerance);
373 else
374 return null;
375 end if;
376
377 EXCEPTION
378
379 WHEN NO_DATA_FOUND THEN
380
381 return null;
382
383 WHEN FND_API.G_EXC_ERROR THEN
384
385 RAISE FND_API.G_EXC_ERROR;
386
387 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
388
389 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
390
391 WHEN OTHERS THEN
392
393 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
394
395 END Over_Ship_Tol_From_Site;
396
397 FUNCTION Over_Ship_Tol_From_Cust_Item
398 (
399 p_database_object_name IN VARCHAR2
400 ,p_attribute_code IN VARCHAR2
401 )
402 RETURN VARCHAR2
403 IS
404 l_customer_id NUMBER;
405 l_internal_item_id NUMBER;
406 l_Over_shipment_tolerance NUMBER;
407 l_Over_return_tolerance NUMBER;
408 BEGIN
409 l_internal_item_id := ONT_Line_Def_Hdlr.g_record.inventory_item_id;
410 l_customer_id := ONT_Line_Def_Hdlr.g_record.sold_to_org_id;
411 select Over_shipment_tolerance, Over_return_tolerance into l_Over_shipment_tolerance, l_Over_return_tolerance
412 from oe_cust_item_settings
413 where internal_item_id = l_internal_item_id
414 and customer_id = l_customer_id
415 and site_use_id is null;
416 if p_database_object_name = 'SHIP' then
417 return to_char(l_Over_shipment_tolerance);
418 elsif p_database_object_name = 'RETURN' then
419 return to_char(l_Over_return_tolerance);
420 else
421 return null;
422 end if;
423
424 EXCEPTION
425
426 WHEN NO_DATA_FOUND THEN
427
428 return null;
429
430 WHEN FND_API.G_EXC_ERROR THEN
431
432 RAISE FND_API.G_EXC_ERROR;
433
434 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
435
436 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
437
438 WHEN OTHERS THEN
439
440 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
441
442 END Over_Ship_Tol_From_Cust_Item ;
443
444 FUNCTION Over_Ship_Tol_From_Site_Item
445 (
446 p_database_object_name IN VARCHAR2
447 ,p_attribute_code IN VARCHAR2
448 )
449 RETURN VARCHAR2
450 IS
451 l_site_use_id NUMBER;
452 l_internal_item_id NUMBER;
453 l_Over_shipment_tolerance NUMBER;
454 l_Over_return_tolerance NUMBER;
455 BEGIN
456 l_internal_item_id := ONT_Line_Def_Hdlr.g_record.inventory_item_id;
457 if p_attribute_code = 'BILL_TO' then
458 l_site_use_id := ONT_Line_Def_Hdlr.g_record.invoice_to_org_id;
459 elsif p_attribute_code = 'SHIP_TO' then
460 l_site_use_id := ONT_Line_Def_Hdlr.g_record.ship_to_org_id;
461 else
462 return null;
463 end if;
464 select Over_shipment_tolerance, Over_return_tolerance into l_Over_shipment_tolerance, l_Over_return_tolerance
465 from oe_cust_item_settings
466 where internal_item_id = l_internal_item_id
467 and site_use_id = l_site_use_id;
468 if p_database_object_name = 'SHIP' then
469 return to_char(l_Over_shipment_tolerance);
470 elsif p_database_object_name = 'RETURN' then
471 return to_char(l_Over_return_tolerance);
472 else
473 return null;
474 end if;
475
476 EXCEPTION
477
478 WHEN NO_DATA_FOUND THEN
479
480 return null;
481
482 WHEN FND_API.G_EXC_ERROR THEN
483
484 RAISE FND_API.G_EXC_ERROR;
485
486 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
487
488 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
489
490 WHEN OTHERS THEN
491
492 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
493
494 END Over_Ship_Tol_From_Site_Item ;
495
496 END OE_Default_Tolerance;