[Home] [Help]
PACKAGE BODY: APPS.ONT_TOL_CUST_ITEM_DEF_UTIL
Source
1 PACKAGE BODY ONT_TOL_CUST_ITEM_Def_Util AS
2 /* $Header: OEXDFWKB.pls 115.0 13-AUG-13 23:23:31 appldev ship $ */
3
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- ONT_TOL_CUST_ITEM_Def_Util
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package ONT_TOL_CUST_ITEM_Def_Util
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 13-AUG-13 Created
21 --
22
23 -- Global constant holding the package name
24 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ONT_TOL_CUST_ITEM_Def_Util';
25
26
27 g_database_object_name varchar2(30) :='OE_AK_TOL_CUST_ITEMS_V';
28
29
30 FUNCTION Get_Attr_Val_Varchar2
31 ( p_attr_code IN VARCHAR2
32 , p_record IN OE_AK_TOL_CUST_ITEMS_V%ROWTYPE
33 ) RETURN VARCHAR2
34 IS
35 BEGIN
36
37 IF p_attr_code =('CUSTOMER_ID') THEN
38 IF NVL(p_record.CUSTOMER_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
39 RETURN p_record.CUSTOMER_ID;
40 ELSE
41 RETURN NULL;
42 END IF;
43 ELSIF p_attr_code =('INTERNAL_ITEM_ID') THEN
44 IF NVL(p_record.INTERNAL_ITEM_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
45 RETURN p_record.INTERNAL_ITEM_ID;
46 ELSE
47 RETURN NULL;
48 END IF;
49 ELSIF p_attr_code =('INVENTORY_ORG_ID') THEN
50 IF NVL(p_record.INVENTORY_ORG_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
51 RETURN p_record.INVENTORY_ORG_ID;
52 ELSE
53 RETURN NULL;
54 END IF;
55 ELSIF p_attr_code =('OVER_RETURN_TOLERANCE') THEN
56 IF NVL(p_record.OVER_RETURN_TOLERANCE, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
57 RETURN p_record.OVER_RETURN_TOLERANCE;
58 ELSE
59 RETURN NULL;
60 END IF;
61 ELSIF p_attr_code =('OVER_SHIPMENT_TOLERANCE') THEN
62 IF NVL(p_record.OVER_SHIPMENT_TOLERANCE, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
63 RETURN p_record.OVER_SHIPMENT_TOLERANCE;
64 ELSE
65 RETURN NULL;
66 END IF;
67 ELSIF p_attr_code =('SOLD_TO_ORG_ID') THEN
68 IF NVL(p_record.CUSTOMER_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
69 RETURN p_record.CUSTOMER_ID;
70 ELSE
71 RETURN NULL;
72 END IF;
73 ELSIF p_attr_code =('UNDER_RETURN_TOLERANCE') THEN
74 IF NVL(p_record.UNDER_RETURN_TOLERANCE, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
75 RETURN p_record.UNDER_RETURN_TOLERANCE;
76 ELSE
77 RETURN NULL;
78 END IF;
79 ELSIF p_attr_code =('UNDER_SHIPMENT_TOLERANCE') THEN
80 IF NVL(p_record.UNDER_SHIPMENT_TOLERANCE, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
81 RETURN p_record.UNDER_SHIPMENT_TOLERANCE;
82 ELSE
83 RETURN NULL;
84 END IF;
85 ELSE
86 RETURN NULL;
87 END IF;
88 END Get_Attr_Val_Varchar2;
89
90
91 FUNCTION Get_Attr_Val_Date
92 ( p_attr_code IN VARCHAR2
93 , p_record IN OE_AK_TOL_CUST_ITEMS_V%ROWTYPE
94 ) RETURN DATE
95 IS
96 BEGIN
97
98 IF p_attr_code =('CUSTOMER_ID') THEN
99 IF NVL(p_record.CUSTOMER_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
100 RETURN to_date(p_record.CUSTOMER_ID,'RRRR/MM/DD HH24:MI:SS');
101 ELSE
102 RETURN NULL;
103 END IF;
104 ELSIF p_attr_code =('INTERNAL_ITEM_ID') THEN
105 IF NVL(p_record.INTERNAL_ITEM_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
106 RETURN to_date(p_record.INTERNAL_ITEM_ID,'RRRR/MM/DD HH24:MI:SS');
107 ELSE
108 RETURN NULL;
109 END IF;
110 ELSIF p_attr_code =('INVENTORY_ORG_ID') THEN
111 IF NVL(p_record.INVENTORY_ORG_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
112 RETURN to_date(p_record.INVENTORY_ORG_ID,'RRRR/MM/DD HH24:MI:SS');
113 ELSE
114 RETURN NULL;
115 END IF;
116 ELSIF p_attr_code =('OVER_RETURN_TOLERANCE') THEN
117 IF NVL(p_record.OVER_RETURN_TOLERANCE, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
118 RETURN to_date(p_record.OVER_RETURN_TOLERANCE,'RRRR/MM/DD HH24:MI:SS');
119 ELSE
120 RETURN NULL;
121 END IF;
122 ELSIF p_attr_code =('OVER_SHIPMENT_TOLERANCE') THEN
123 IF NVL(p_record.OVER_SHIPMENT_TOLERANCE, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
124 RETURN to_date(p_record.OVER_SHIPMENT_TOLERANCE,'RRRR/MM/DD HH24:MI:SS');
125 ELSE
126 RETURN NULL;
127 END IF;
128 ELSIF p_attr_code =('SOLD_TO_ORG_ID') THEN
129 IF NVL(p_record.CUSTOMER_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
130 RETURN to_date(p_record.CUSTOMER_ID,'RRRR/MM/DD HH24:MI:SS');
131 ELSE
132 RETURN NULL;
133 END IF;
134 ELSIF p_attr_code =('UNDER_RETURN_TOLERANCE') THEN
135 IF NVL(p_record.UNDER_RETURN_TOLERANCE, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
136 RETURN to_date(p_record.UNDER_RETURN_TOLERANCE,'RRRR/MM/DD HH24:MI:SS');
137 ELSE
138 RETURN NULL;
139 END IF;
140 ELSIF p_attr_code =('UNDER_SHIPMENT_TOLERANCE') THEN
141 IF NVL(p_record.UNDER_SHIPMENT_TOLERANCE, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
142 RETURN to_date(p_record.UNDER_SHIPMENT_TOLERANCE,'RRRR/MM/DD HH24:MI:SS');
143 ELSE
144 RETURN NULL;
145 END IF;
146 ELSE
147 RETURN NULL;
148 END IF;
149
150 END Get_Attr_Val_Date;
151
152
153 PROCEDURE Clear_TOL_CUST_ITEM_Cache
154 IS
155 BEGIN
156 g_cached_record.CUSTOMER_ID := null;
157 g_cached_record.INTERNAL_ITEM_ID := null;
158 END Clear_TOL_CUST_ITEM_Cache;
159
160
161 FUNCTION Sync_TOL_CUST_ITEM_Cache
162 ( p_CUSTOMER_ID IN NUMBER
163 , p_INTERNAL_ITEM_ID IN NUMBER
164
165
166 ) RETURN NUMBER
167 IS
168 CURSOR cache IS
169 SELECT * FROM OE_AK_TOL_CUST_ITEMS_V
170 WHERE CUSTOMER_ID = p_CUSTOMER_ID
171 AND INTERNAL_ITEM_ID = p_INTERNAL_ITEM_ID
172 ;
173 BEGIN
174
175 IF (NVL(p_CUSTOMER_ID,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
176 OR (NVL(p_INTERNAL_ITEM_ID,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
177 THEN
178 RETURN 0 ;
179 ELSIF (NVL(g_cached_record.CUSTOMER_ID,FND_API.G_MISS_NUM) <> p_CUSTOMER_ID)
180 OR (NVL(g_cached_record.INTERNAL_ITEM_ID,FND_API.G_MISS_NUM) <> p_INTERNAL_ITEM_ID)
181 THEN
182 Clear_TOL_CUST_ITEM_Cache;
183 Open cache;
184 FETCH cache into g_cached_record;
185 IF cache%NOTFOUND THEN
186 RETURN 0;
187 END IF;
188 Close cache;
189 RETURN 1 ;
190 END IF;
191
192 RETURN 1 ;
193 EXCEPTION
194 WHEN OTHERS THEN
195 RETURN 0 ;
196 END Sync_TOL_CUST_ITEM_Cache;
197
198
199 END ONT_TOL_CUST_ITEM_Def_Util;