1 PACKAGE BODY BEVENT_DEVICE_PKG AS
2 /* $Header: WMSDEVBB.pls 120.0 2005/05/25 09:00:00 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID IN OUT NOCOPY VARCHAR2,
5 X_BUSINESS_EVENT_ID in NUMBER,
6 X_DEVICE_ID in NUMBER,
7 X_LEVEL_TYPE in NUMBER,
8 X_LEVEL_VALUE in NUMBER,
9 X_ORGANIZATION_ID in NUMBER,
10 X_SUBINVENTORY_CODE in VARCHAR2,
11 X_AUTO_ENABLED_FLAG in VARCHAR2,
12 X_COMMENTS in VARCHAR2,
13 X_ENABLED_FLAG in VARCHAR2,
14 X_CREATION_DATE in DATE,
15 X_CREATED_BY in NUMBER,
16 X_LAST_UPDATE_DATE in DATE,
17 X_LAST_UPDATED_BY in NUMBER,
18 X_LAST_UPDATE_LOGIN in NUMBER,
19 X_ATTRIBUTE_CATEGORY in VARCHAR2,
20 X_ATTRIBUTE1 in VARCHAR2,
21 X_ATTRIBUTE2 in VARCHAR2,
22 X_ATTRIBUTE3 in VARCHAR2,
23 X_ATTRIBUTE4 in VARCHAR2,
24 X_ATTRIBUTE5 in VARCHAR2,
25 X_ATTRIBUTE6 in VARCHAR2,
26 X_ATTRIBUTE7 in VARCHAR2,
27 X_ATTRIBUTE8 in VARCHAR2,
28 X_ATTRIBUTE9 in VARCHAR2,
29 X_ATTRIBUTE10 in VARCHAR2,
30 X_ATTRIBUTE11 in VARCHAR2,
31 X_ATTRIBUTE12 in VARCHAR2,
32 X_ATTRIBUTE13 in VARCHAR2,
33 X_ATTRIBUTE14 in VARCHAR2,
34 X_ATTRIBUTE15 in VARCHAR2,
35 x_verification_required IN VARCHAR2
36 ) is
37 cursor get_row is
38 select ROWID
39 from wms_bus_event_devices
40 where BUSINESS_EVENT_ID = X_BUSINESS_EVENT_ID
41 and DEVICE_ID = X_DEVICE_ID
42 and LEVEL_VALUE = X_LEVEL_VALUE
43 and ORGANIZATION_ID = X_ORGANIZATION_ID;
44 begin
45 insert into wms_bus_event_devices (
46 BUSINESS_EVENT_ID,
47 DEVICE_ID,
48 LEVEL_TYPE,
49 LEVEL_VALUE,
50 ORGANIZATION_ID,
51 SUBINVENTORY_CODE,
52 AUTO_ENABLED_FLAG,
53 COMMENTS,
54 ENABLED_FLAG,
55 CREATION_DATE,
56 CREATED_BY,
57 LAST_UPDATE_DATE,
58 LAST_UPDATED_BY,
59 LAST_UPDATE_LOGIN,
60 ATTRIBUTE_CATEGORY,
61 ATTRIBUTE1,
62 ATTRIBUTE2,
63 ATTRIBUTE3,
64 ATTRIBUTE4,
65 ATTRIBUTE5,
66 ATTRIBUTE6,
67 ATTRIBUTE7,
68 ATTRIBUTE8,
69 ATTRIBUTE9,
70 ATTRIBUTE10,
71 ATTRIBUTE11,
72 ATTRIBUTE12,
73 ATTRIBUTE13,
74 ATTRIBUTE14,
75 attribute15,
76 VERIFICATION_REQUIRED)
77 values (
78 X_BUSINESS_EVENT_ID,
79 X_DEVICE_ID,
80 X_LEVEL_TYPE,
81 X_LEVEL_VALUE,
82 X_ORGANIZATION_ID,
83 X_SUBINVENTORY_CODE,
84 X_AUTO_ENABLED_FLAG,
85 X_COMMENTS,
86 X_ENABLED_FLAG,
87 X_CREATION_DATE,
88 X_CREATED_BY,
89 X_LAST_UPDATE_DATE,
90 X_LAST_UPDATED_BY,
91 X_LAST_UPDATE_LOGIN,
92 X_ATTRIBUTE_CATEGORY,
93 X_ATTRIBUTE1,
94 X_ATTRIBUTE2,
95 X_ATTRIBUTE3,
96 X_ATTRIBUTE4,
97 X_ATTRIBUTE5,
98 X_ATTRIBUTE6,
99 X_ATTRIBUTE7,
100 X_ATTRIBUTE8,
101 X_ATTRIBUTE9,
102 X_ATTRIBUTE10,
103 X_ATTRIBUTE11,
104 X_ATTRIBUTE12,
105 X_ATTRIBUTE13,
106 X_ATTRIBUTE14,
107 x_attribute15,
108 X_VERIFICATION_REQUIRED );
109 open get_row;
110 fetch get_row into X_ROWID;
111 if (get_row%notfound) then
112 close get_row;
113 raise no_data_found;
114 end if;
115 close get_row;
116
117 end insert_row;
118
119 /*****************************************************************/
120
121 procedure LOCK_ROW (
122 X_ROWID IN OUT NOCOPY VARCHAR2,
123 X_BUSINESS_EVENT_ID in NUMBER,
124 X_DEVICE_ID in NUMBER,
125 X_LEVEL_TYPE in NUMBER,
126 X_LEVEL_VALUE in NUMBER,
127 X_ORGANIZATION_ID in NUMBER,
128 X_SUBINVENTORY_CODE in VARCHAR2,
129 X_AUTO_ENABLED_FLAG in VARCHAR2,
130 X_COMMENTS in VARCHAR2,
131 X_ENABLED_FLAG in VARCHAR2,
132 X_ATTRIBUTE_CATEGORY in VARCHAR2,
133 X_ATTRIBUTE1 in VARCHAR2,
134 X_ATTRIBUTE2 in VARCHAR2,
135 X_ATTRIBUTE3 in VARCHAR2,
136 X_ATTRIBUTE4 in VARCHAR2,
137 X_ATTRIBUTE5 in VARCHAR2,
138 X_ATTRIBUTE6 in VARCHAR2,
139 X_ATTRIBUTE7 in VARCHAR2,
140 X_ATTRIBUTE8 in VARCHAR2,
141 X_ATTRIBUTE9 in VARCHAR2,
142 X_ATTRIBUTE10 in VARCHAR2,
143 X_ATTRIBUTE11 in VARCHAR2,
144 X_ATTRIBUTE12 in VARCHAR2,
145 X_ATTRIBUTE13 in VARCHAR2,
146 X_ATTRIBUTE14 in VARCHAR2,
147 X_ATTRIBUTE15 in VARCHAR2,
148 x_verification_required IN VARCHAR2
149 )
150 is
151 cursor get_lock is
152 select
153 BUSINESS_EVENT_ID,
154 DEVICE_ID,
155 LEVEL_TYPE,
156 LEVEL_VALUE,
157 ORGANIZATION_ID,
158 SUBINVENTORY_CODE,
159 AUTO_ENABLED_FLAG,
160 COMMENTS,
161 ENABLED_FLAG,
162 ATTRIBUTE_CATEGORY,
163 ATTRIBUTE1,
164 ATTRIBUTE2,
165 ATTRIBUTE3,
166 ATTRIBUTE4,
167 ATTRIBUTE5,
168 ATTRIBUTE6,
169 ATTRIBUTE7,
170 ATTRIBUTE8,
171 ATTRIBUTE9,
172 ATTRIBUTE10,
173 ATTRIBUTE11,
174 ATTRIBUTE12,
175 ATTRIBUTE13,
176 ATTRIBUTE14,
177 attribute15,
178 VERIFICATION_REQUIRED
179 from wms_bus_event_devices
180 where ROWID = X_ROWID
181 for update of DEVICE_ID nowait;
182 recinfo get_lock%rowtype;
183
184 begin
185 open get_lock;
186 fetch get_lock into recinfo;
187 if (get_lock%notfound) then
188 close get_lock;
189 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
190 app_exception.raise_exception;
191 end if;
192 close get_lock;
193
194 if ((recinfo.BUSINESS_EVENT_ID = X_BUSINESS_EVENT_ID)
195 AND (recinfo.DEVICE_ID = X_DEVICE_ID)
196 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
197 AND (recinfo.LEVEL_TYPE = X_LEVEL_TYPE)
198
199 AND ((recinfo.LEVEL_VALUE = X_LEVEL_VALUE)
200 OR ((recinfo.LEVEL_VALUE is null) AND (X_LEVEL_VALUE is null)))
201 AND ((recinfo.ORGANIZATION_ID = X_ORGANIZATION_ID)
202 OR ((recinfo.ORGANIZATION_ID is null) AND (X_ORGANIZATION_ID is null)))
203 AND ((recinfo.SUBINVENTORY_CODE = X_SUBINVENTORY_CODE)
204 OR ((recinfo.SUBINVENTORY_CODE is null) AND (X_SUBINVENTORY_CODE is null)))
205 AND ((recinfo.AUTO_ENABLED_FLAG = X_AUTO_ENABLED_FLAG)
206 OR ((recinfo.AUTO_ENABLED_FLAG is null) AND (X_AUTO_ENABLED_FLAG is null)))
207 AND ((recinfo.VERIFICATION_REQUIRED = X_VERIFICATION_REQUIRED)
208 OR ((recinfo.VERIFICATION_REQUIRED is null) AND (X_VERIFICATION_REQUIRED is null)))
209 AND ((recinfo.COMMENTS = X_COMMENTS)
210 OR ((recinfo.COMMENTS is null) AND (X_COMMENTS is null)))
211 AND ((recinfo.attribute_category = X_attribute_category)
212 OR ((recinfo.attribute_category is null) AND (X_attribute_category is null)))
213 AND ((recinfo.attribute1 = X_attribute1)
214 OR ((recinfo.attribute1 is null) AND (X_attribute1 is null)))
215 AND ((recinfo.attribute2 = X_attribute2)
216 OR ((recinfo.attribute2 is null) AND (X_attribute2 is null)))
217 AND ((recinfo.attribute3 = X_attribute3)
218 OR ((recinfo.attribute3 is null) AND (X_attribute3 is null)))
219 AND ((recinfo.attribute4 = X_attribute4)
220 OR ((recinfo.attribute4 is null) AND (X_attribute4 is null)))
221 AND ((recinfo.attribute5 = X_attribute5)
222 OR ((recinfo.attribute5 is null) AND (X_attribute5 is null)))
223 AND ((recinfo.attribute6 = X_attribute6)
224 OR ((recinfo.attribute6 is null) AND (X_attribute6 is null)))
225 AND ((recinfo.attribute7 = X_attribute7)
226 OR ((recinfo.attribute7 is null) AND (X_attribute7 is null)))
227 AND ((recinfo.attribute8 = X_attribute8)
228 OR ((recinfo.attribute8 is null) AND (X_attribute8 is null)))
229 AND ((recinfo.attribute9 = X_attribute9)
230 OR ((recinfo.attribute9 is null) AND (X_attribute9 is null)))
231 AND ((recinfo.attribute10 = X_attribute10)
232 OR ((recinfo.attribute10 is null) AND (X_attribute10 is null)))
233 AND ((recinfo.attribute11 = X_attribute11)
234 OR ((recinfo.attribute11 is null) AND (X_attribute11 is null)))
235 AND ((recinfo.attribute12 = X_attribute12)
236 OR ((recinfo.attribute12 is null) AND (X_attribute12 is null)))
237 AND ((recinfo.attribute13 = X_attribute13)
238 OR ((recinfo.attribute13 is null) AND (X_attribute13 is null)))
239 AND ((recinfo.attribute14 = X_attribute14)
240 OR ((recinfo.attribute14 is null) AND (X_attribute14 is null)))
241 AND ((recinfo.attribute15 = X_attribute15)
242 OR ((recinfo.attribute15 is null) AND (X_attribute15 is null)))
243 )
244 then
245 return;
246 else
247 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
248 app_exception.raise_exception;
249 end if;
250
251 end LOCK_ROW;
252
253 /*******************************************************************************/
254
255 procedure UPDATE_ROW (
256 X_BUSINESS_EVENT_ID in NUMBER,
257 X_DEVICE_ID in NUMBER,
258 X_LEVEL_TYPE in NUMBER,
259 X_LEVEL_VALUE in NUMBER,
260 X_ORGANIZATION_ID in NUMBER,
261 X_SUBINVENTORY_CODE in VARCHAR2,
262 X_AUTO_ENABLED_FLAG in VARCHAR2,
263 X_COMMENTS in VARCHAR2,
264 X_ENABLED_FLAG in VARCHAR2,
265 X_LAST_UPDATE_DATE in DATE,
266 X_LAST_UPDATED_BY in NUMBER,
267 X_LAST_UPDATE_LOGIN in NUMBER,
268 X_ATTRIBUTE_CATEGORY in VARCHAR2,
269 X_ATTRIBUTE1 in VARCHAR2,
270 X_ATTRIBUTE2 in VARCHAR2,
271 X_ATTRIBUTE3 in VARCHAR2,
272 X_ATTRIBUTE4 in VARCHAR2,
273 X_ATTRIBUTE5 in VARCHAR2,
274 X_ATTRIBUTE6 in VARCHAR2,
275 X_ATTRIBUTE7 in VARCHAR2,
276 X_ATTRIBUTE8 in VARCHAR2,
277 X_ATTRIBUTE9 in VARCHAR2,
278 X_ATTRIBUTE10 in VARCHAR2,
279 X_ATTRIBUTE11 in VARCHAR2,
280 X_ATTRIBUTE12 in VARCHAR2,
281 X_ATTRIBUTE13 in VARCHAR2,
282 X_ATTRIBUTE14 in VARCHAR2,
283 X_ATTRIBUTE15 in VARCHAR2,
284 x_verification_required IN VARCHAR2
285 ) is
286 begin
287 update wms_bus_event_devices
288 set
289 LEVEL_TYPE = X_LEVEL_TYPE,
290 LEVEL_VALUE = X_LEVEL_VALUE,
291 ORGANIZATION_ID = X_ORGANIZATION_ID,
292 SUBINVENTORY_CODE = X_SUBINVENTORY_CODE,
293 AUTO_ENABLED_FLAG = X_AUTO_ENABLED_FLAG,
294 COMMENTS = X_COMMENTS,
295 ENABLED_FLAG = X_ENABLED_FLAG,
296 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
297 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
298 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
299 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
300 ATTRIBUTE1 = X_ATTRIBUTE1,
301 ATTRIBUTE2 = X_ATTRIBUTE2,
302 ATTRIBUTE3 = X_ATTRIBUTE3,
303 ATTRIBUTE4 = X_ATTRIBUTE4,
304 ATTRIBUTE5 = X_ATTRIBUTE5,
305 ATTRIBUTE6 = X_ATTRIBUTE6,
306 ATTRIBUTE7 = X_ATTRIBUTE7,
307 ATTRIBUTE8 = X_ATTRIBUTE8,
308 ATTRIBUTE9 = X_ATTRIBUTE9,
309 ATTRIBUTE10 = X_ATTRIBUTE10,
310 ATTRIBUTE11 = X_ATTRIBUTE11,
311 ATTRIBUTE12 = X_ATTRIBUTE12,
312 ATTRIBUTE13 = X_ATTRIBUTE13,
313 ATTRIBUTE14 = X_ATTRIBUTE14,
314 ATTRIBUTE15 = x_attribute15,
315 verification_required = x_VERIFICATION_REQUIRED
316 where BUSINESS_EVENT_ID = X_BUSINESS_EVENT_ID
317 and DEVICE_ID = X_DEVICE_ID
318 and ORGANIZATION_ID = X_ORGANIZATION_ID;
319 -- and LEVEL_TYPE = X_LEVEL_TYPE;
320 -- and LEVEL_VALUE = nvl(X_LEVEL_VALUE, LEVEL_VALUE)
321 -- and SUBINVENTORY_CODE = nvl(X_SUBINVENTORY_CODE, SUBINVENTORY_CODE);
322
323 if (sql%notfound) then
324 raise no_data_found;
325 end if;
326
327 end UPDATE_ROW;
328
329 /********************************************************************************/
330
331 procedure DELETE_ROW (
332 X_BUSINESS_EVENT_ID in NUMBER,
333 X_DEVICE_ID in NUMBER,
334 X_ORGANIZATION_ID in NUMBER,
335 X_LEVEL_TYPE in number,
336 X_LEVEL_VALUE in number,
337 X_SUBINVENTORY_CODE in varchar)
338 is
339 begin
340 delete from wms_bus_event_devices
341 where BUSINESS_EVENT_ID = X_BUSINESS_EVENT_ID
342 and DEVICE_ID = X_DEVICE_ID
343 and ORGANIZATION_ID = X_ORGANIZATION_ID
344 and LEVEL_TYPE = X_LEVEL_TYPE
345 and LEVEL_VALUE = X_LEVEL_VALUE
346 and nvl(SUBINVENTORY_CODE,'aaa') = nvl(X_SUBINVENTORY_CODE,'aaa');
347
348
349 if (sql%notfound) then
350 raise no_data_found;
351 end if;
352
353 end DELETE_ROW;
354
355 /*******************************************************************************/
356
357 end BEVENT_DEVICE_PKG;
[Home] [Help]