[Home] [Help]
PACKAGE BODY: APPS.ONT_ITEM_DEF_UTIL
Source
1 PACKAGE BODY ONT_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_ITEM_Def_Util
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package ONT_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_ITEM_Def_Util';
25
26
27 g_database_object_name varchar2(30) :='OE_AK_INVENTORY_ITEMS_V';
28
29
30 FUNCTION Get_Attr_Val_Varchar2
31 ( p_attr_code IN VARCHAR2
32 , p_record IN OE_AK_INVENTORY_ITEMS_V%ROWTYPE
33 ) RETURN VARCHAR2
34 IS
35 BEGIN
36
37 IF p_attr_code =('ACCOUNTING_RULE_ID') THEN
38 IF NVL(p_record.ACCOUNTING_RULE_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
39 RETURN p_record.ACCOUNTING_RULE_ID;
40 ELSE
41 RETURN NULL;
42 END IF;
43 ELSIF p_attr_code =('BUILD_IN_WIP_FLAG') THEN
44 IF NVL(p_record.BUILD_IN_WIP_FLAG, FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR THEN
45 RETURN p_record.BUILD_IN_WIP_FLAG;
46 ELSE
47 RETURN NULL;
48 END IF;
49 ELSIF p_attr_code =('CHARGE_PERIODICITY_CODE') THEN
50 IF NVL(p_record.CHARGE_PERIODICITY_CODE, FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR THEN
51 RETURN p_record.CHARGE_PERIODICITY_CODE;
52 ELSE
53 RETURN NULL;
54 END IF;
55 ELSIF p_attr_code =('COVERAGE_SCHEDULE_ID') THEN
56 IF NVL(p_record.TEMPLATE, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
57 RETURN p_record.TEMPLATE;
58 ELSE
59 RETURN NULL;
60 END IF;
61 ELSIF p_attr_code =('DEFAULT_SHIPPING_ORG') THEN
62 IF NVL(p_record.DEFAULT_SHIPPING_ORG, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
63 RETURN p_record.DEFAULT_SHIPPING_ORG;
64 ELSE
65 RETURN NULL;
66 END IF;
67 ELSIF p_attr_code =('INVENTORY_ITEM_ID') THEN
68 IF NVL(p_record.INVENTORY_ITEM_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
69 RETURN p_record.INVENTORY_ITEM_ID;
70 ELSE
71 RETURN NULL;
72 END IF;
73 ELSIF p_attr_code =('INVOICING_RULE_ID') THEN
74 IF NVL(p_record.INVOICING_RULE_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
75 RETURN p_record.INVOICING_RULE_ID;
76 ELSE
77 RETURN NULL;
78 END IF;
79 ELSIF p_attr_code =('ORGANIZATION_ID') THEN
80 IF NVL(p_record.ORGANIZATION_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
81 RETURN p_record.ORGANIZATION_ID;
82 ELSE
83 RETURN NULL;
84 END IF;
85 ELSIF p_attr_code =('OVER_RETURN_TOLERANCE') THEN
86 IF NVL(p_record.OVER_RETURN_TOLERANCE, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
87 RETURN p_record.OVER_RETURN_TOLERANCE;
88 ELSE
89 RETURN NULL;
90 END IF;
91 ELSIF p_attr_code =('OVER_SHIPMENT_TOLERANCE') THEN
92 IF NVL(p_record.OVER_SHIPMENT_TOLERANCE, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
93 RETURN p_record.OVER_SHIPMENT_TOLERANCE;
94 ELSE
95 RETURN NULL;
96 END IF;
97 ELSIF p_attr_code =('PAYMENT_TERM_ID') THEN
98 IF NVL(p_record.PAYMENT_TERM_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
99 RETURN p_record.PAYMENT_TERM_ID;
100 ELSE
101 RETURN NULL;
102 END IF;
103 ELSIF p_attr_code =('PICK_COMPONENTS_FLAG') THEN
104 IF NVL(p_record.PICK_COMPONENTS_FLAG, FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR THEN
105 RETURN p_record.PICK_COMPONENTS_FLAG;
106 ELSE
107 RETURN NULL;
108 END IF;
109 ELSIF p_attr_code =('PRIMARY_UOM_CODE') THEN
110 IF NVL(p_record.PRIMARY_UOM_CODE, FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR THEN
111 RETURN p_record.PRIMARY_UOM_CODE;
112 ELSE
113 RETURN NULL;
114 END IF;
115 ELSIF p_attr_code =('REPLENISH_TO_ORDER_FLAG') THEN
116 IF NVL(p_record.REPLENISH_TO_ORDER_FLAG, FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR THEN
117 RETURN p_record.REPLENISH_TO_ORDER_FLAG;
118 ELSE
119 RETURN NULL;
120 END IF;
121 ELSIF p_attr_code =('SERVICE_DURATION') THEN
122 IF NVL(p_record.SERVICE_DURATION, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
123 RETURN p_record.SERVICE_DURATION;
124 ELSE
125 RETURN NULL;
126 END IF;
127 ELSIF p_attr_code =('SERVICE_PERIOD') THEN
128 IF NVL(p_record.SERVICE_DURATION_PERIOD_CODE, FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR THEN
129 RETURN p_record.SERVICE_DURATION_PERIOD_CODE;
130 ELSE
131 RETURN NULL;
132 END IF;
133 ELSIF p_attr_code =('SHIP_MODEL_COMPLETE_FLAG') THEN
134 IF NVL(p_record.SHIP_MODEL_COMPLETE_FLAG, FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR THEN
135 RETURN p_record.SHIP_MODEL_COMPLETE_FLAG;
136 ELSE
137 RETURN NULL;
138 END IF;
139 ELSIF p_attr_code =('SOURCE_TYPE_CODE') THEN
140 IF NVL(p_record.SOURCE_TYPE_CODE, FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR THEN
141 RETURN p_record.SOURCE_TYPE_CODE;
142 ELSE
143 RETURN NULL;
144 END IF;
145 ELSIF p_attr_code =('UNDER_RETURN_TOLERANCE') THEN
146 IF NVL(p_record.UNDER_RETURN_TOLERANCE, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
147 RETURN p_record.UNDER_RETURN_TOLERANCE;
148 ELSE
149 RETURN NULL;
150 END IF;
151 ELSIF p_attr_code =('UNDER_SHIPMENT_TOLERANCE') THEN
152 IF NVL(p_record.UNDER_SHIPMENT_TOLERANCE, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
153 RETURN p_record.UNDER_SHIPMENT_TOLERANCE;
154 ELSE
155 RETURN NULL;
156 END IF;
157 ELSE
158 RETURN NULL;
159 END IF;
160 END Get_Attr_Val_Varchar2;
161
162
163 FUNCTION Get_Attr_Val_Date
164 ( p_attr_code IN VARCHAR2
165 , p_record IN OE_AK_INVENTORY_ITEMS_V%ROWTYPE
166 ) RETURN DATE
167 IS
168 BEGIN
169
170 IF p_attr_code =('ACCOUNTING_RULE_ID') THEN
171 IF NVL(p_record.ACCOUNTING_RULE_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
172 RETURN to_date(p_record.ACCOUNTING_RULE_ID,'RRRR/MM/DD HH24:MI:SS');
173 ELSE
174 RETURN NULL;
175 END IF;
176 ELSIF p_attr_code =('BUILD_IN_WIP_FLAG') THEN
177 IF NVL(p_record.BUILD_IN_WIP_FLAG, FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR THEN
178 RETURN to_date(p_record.BUILD_IN_WIP_FLAG,'RRRR/MM/DD HH24:MI:SS');
179 ELSE
180 RETURN NULL;
181 END IF;
182 ELSIF p_attr_code =('CHARGE_PERIODICITY_CODE') THEN
183 IF NVL(p_record.CHARGE_PERIODICITY_CODE, FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR THEN
184 RETURN to_date(p_record.CHARGE_PERIODICITY_CODE,'RRRR/MM/DD HH24:MI:SS');
185 ELSE
186 RETURN NULL;
187 END IF;
188 ELSIF p_attr_code =('COVERAGE_SCHEDULE_ID') THEN
189 IF NVL(p_record.TEMPLATE, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
190 RETURN to_date(p_record.TEMPLATE,'RRRR/MM/DD HH24:MI:SS');
191 ELSE
192 RETURN NULL;
193 END IF;
194 ELSIF p_attr_code =('DEFAULT_SHIPPING_ORG') THEN
195 IF NVL(p_record.DEFAULT_SHIPPING_ORG, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
196 RETURN to_date(p_record.DEFAULT_SHIPPING_ORG,'RRRR/MM/DD HH24:MI:SS');
197 ELSE
198 RETURN NULL;
199 END IF;
200 ELSIF p_attr_code =('INVENTORY_ITEM_ID') THEN
201 IF NVL(p_record.INVENTORY_ITEM_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
202 RETURN to_date(p_record.INVENTORY_ITEM_ID,'RRRR/MM/DD HH24:MI:SS');
203 ELSE
204 RETURN NULL;
205 END IF;
206 ELSIF p_attr_code =('INVOICING_RULE_ID') THEN
207 IF NVL(p_record.INVOICING_RULE_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
208 RETURN to_date(p_record.INVOICING_RULE_ID,'RRRR/MM/DD HH24:MI:SS');
209 ELSE
210 RETURN NULL;
211 END IF;
212 ELSIF p_attr_code =('ORGANIZATION_ID') THEN
213 IF NVL(p_record.ORGANIZATION_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
214 RETURN to_date(p_record.ORGANIZATION_ID,'RRRR/MM/DD HH24:MI:SS');
215 ELSE
216 RETURN NULL;
217 END IF;
218 ELSIF p_attr_code =('OVER_RETURN_TOLERANCE') THEN
219 IF NVL(p_record.OVER_RETURN_TOLERANCE, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
220 RETURN to_date(p_record.OVER_RETURN_TOLERANCE,'RRRR/MM/DD HH24:MI:SS');
221 ELSE
222 RETURN NULL;
223 END IF;
224 ELSIF p_attr_code =('OVER_SHIPMENT_TOLERANCE') THEN
225 IF NVL(p_record.OVER_SHIPMENT_TOLERANCE, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
226 RETURN to_date(p_record.OVER_SHIPMENT_TOLERANCE,'RRRR/MM/DD HH24:MI:SS');
227 ELSE
228 RETURN NULL;
229 END IF;
230 ELSIF p_attr_code =('PAYMENT_TERM_ID') THEN
231 IF NVL(p_record.PAYMENT_TERM_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
232 RETURN to_date(p_record.PAYMENT_TERM_ID,'RRRR/MM/DD HH24:MI:SS');
233 ELSE
234 RETURN NULL;
235 END IF;
236 ELSIF p_attr_code =('PICK_COMPONENTS_FLAG') THEN
237 IF NVL(p_record.PICK_COMPONENTS_FLAG, FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR THEN
238 RETURN to_date(p_record.PICK_COMPONENTS_FLAG,'RRRR/MM/DD HH24:MI:SS');
239 ELSE
240 RETURN NULL;
241 END IF;
242 ELSIF p_attr_code =('PRIMARY_UOM_CODE') THEN
243 IF NVL(p_record.PRIMARY_UOM_CODE, FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR THEN
244 RETURN to_date(p_record.PRIMARY_UOM_CODE,'RRRR/MM/DD HH24:MI:SS');
245 ELSE
246 RETURN NULL;
247 END IF;
248 ELSIF p_attr_code =('REPLENISH_TO_ORDER_FLAG') THEN
249 IF NVL(p_record.REPLENISH_TO_ORDER_FLAG, FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR THEN
250 RETURN to_date(p_record.REPLENISH_TO_ORDER_FLAG,'RRRR/MM/DD HH24:MI:SS');
251 ELSE
252 RETURN NULL;
253 END IF;
254 ELSIF p_attr_code =('SERVICE_DURATION') THEN
255 IF NVL(p_record.SERVICE_DURATION, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
256 RETURN to_date(p_record.SERVICE_DURATION,'RRRR/MM/DD HH24:MI:SS');
257 ELSE
258 RETURN NULL;
259 END IF;
260 ELSIF p_attr_code =('SERVICE_PERIOD') THEN
261 IF NVL(p_record.SERVICE_DURATION_PERIOD_CODE, FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR THEN
262 RETURN to_date(p_record.SERVICE_DURATION_PERIOD_CODE,'RRRR/MM/DD HH24:MI:SS');
263 ELSE
264 RETURN NULL;
265 END IF;
266 ELSIF p_attr_code =('SHIP_MODEL_COMPLETE_FLAG') THEN
267 IF NVL(p_record.SHIP_MODEL_COMPLETE_FLAG, FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR THEN
268 RETURN to_date(p_record.SHIP_MODEL_COMPLETE_FLAG,'RRRR/MM/DD HH24:MI:SS');
269 ELSE
270 RETURN NULL;
271 END IF;
272 ELSIF p_attr_code =('SOURCE_TYPE_CODE') THEN
273 IF NVL(p_record.SOURCE_TYPE_CODE, FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR THEN
274 RETURN to_date(p_record.SOURCE_TYPE_CODE,'RRRR/MM/DD HH24:MI:SS');
275 ELSE
276 RETURN NULL;
277 END IF;
278 ELSIF p_attr_code =('UNDER_RETURN_TOLERANCE') THEN
279 IF NVL(p_record.UNDER_RETURN_TOLERANCE, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
280 RETURN to_date(p_record.UNDER_RETURN_TOLERANCE,'RRRR/MM/DD HH24:MI:SS');
281 ELSE
282 RETURN NULL;
283 END IF;
284 ELSIF p_attr_code =('UNDER_SHIPMENT_TOLERANCE') THEN
285 IF NVL(p_record.UNDER_SHIPMENT_TOLERANCE, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
286 RETURN to_date(p_record.UNDER_SHIPMENT_TOLERANCE,'RRRR/MM/DD HH24:MI:SS');
287 ELSE
288 RETURN NULL;
289 END IF;
290 ELSE
291 RETURN NULL;
292 END IF;
293
294 END Get_Attr_Val_Date;
295
296
297 PROCEDURE Clear_ITEM_Cache
298 IS
299 BEGIN
300 g_cached_record.INVENTORY_ITEM_ID := null;
301 g_cached_record.ORGANIZATION_ID := null;
302 END Clear_ITEM_Cache;
303
304
305 FUNCTION Sync_ITEM_Cache
306 ( p_INVENTORY_ITEM_ID IN NUMBER
307 , p_ORGANIZATION_ID IN NUMBER
308
309
310 ) RETURN NUMBER
311 IS
312 CURSOR cache IS
313 SELECT * FROM OE_AK_INVENTORY_ITEMS_V
314 WHERE INVENTORY_ITEM_ID = p_INVENTORY_ITEM_ID
315 AND ORGANIZATION_ID = p_ORGANIZATION_ID
316 ;
317 BEGIN
318
319 IF (NVL(p_INVENTORY_ITEM_ID,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
320 OR (NVL(p_ORGANIZATION_ID,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
321 THEN
322 RETURN 0 ;
323 ELSIF (NVL(g_cached_record.INVENTORY_ITEM_ID,FND_API.G_MISS_NUM) <> p_INVENTORY_ITEM_ID)
324 OR (NVL(g_cached_record.ORGANIZATION_ID,FND_API.G_MISS_NUM) <> p_ORGANIZATION_ID)
325 THEN
326 Clear_ITEM_Cache;
327 Open cache;
328 FETCH cache into g_cached_record;
329 IF cache%NOTFOUND THEN
330 RETURN 0;
331 END IF;
332 Close cache;
333 RETURN 1 ;
334 END IF;
335
336 RETURN 1 ;
337 EXCEPTION
338 WHEN OTHERS THEN
339 RETURN 0 ;
340 END Sync_ITEM_Cache;
341
342
343 END ONT_ITEM_Def_Util;