[Home] [Help]
PACKAGE BODY: APPS.WMS_LABEL_FIELD_VARIABLES_PKG
Source
1 package body WMS_LABEL_FIELD_VARIABLES_PKG as
2 /* $Header: WMSLBFVB.pls 120.2 2005/06/22 09:17:48 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2, -- NOCOPY added as a part of Bug# 4380449
5 X_LABEL_FORMAT_ID in NUMBER,
6 X_LABEL_FIELD_ID in NUMBER,
7 X_FIELD_VARIABLE_NAME in VARCHAR2,
8 X_FIELD_VARIABLE_DESCRIPTION in VARCHAR2,
9 X_LAST_UPDATE_DATE in DATE,
10 X_LAST_UPDATED_BY in NUMBER,
11 X_LAST_UPDATE_LOGIN in NUMBER,
12 X_CREATED_BY in NUMBER,
13 X_CREATION_DATE in DATE,
14 X_REQUEST_ID in NUMBER,
15 X_PROGRAM_APPLICATION_ID in NUMBER,
16 X_PROGRAM_ID in NUMBER,
17 X_PROGRAM_UPDATE_DATE in DATE,
18 X_ATTRIBUTE_CATEGORY in VARCHAR2,
19 X_ATTRIBUTE1 in VARCHAR2,
20 X_ATTRIBUTE2 in VARCHAR2,
21 X_ATTRIBUTE3 in VARCHAR2,
22 X_ATTRIBUTE4 in VARCHAR2,
23 X_ATTRIBUTE5 in VARCHAR2,
24 X_ATTRIBUTE6 in VARCHAR2,
25 X_ATTRIBUTE7 in VARCHAR2,
26 X_ATTRIBUTE8 in VARCHAR2,
27 X_ATTRIBUTE9 in VARCHAR2,
28 X_ATTRIBUTE10 in VARCHAR2,
29 X_ATTRIBUTE11 in VARCHAR2,
30 X_ATTRIBUTE12 in VARCHAR2,
31 X_ATTRIBUTE13 in VARCHAR2,
32 X_ATTRIBUTE14 in VARCHAR2,
33 X_ATTRIBUTE15 in VARCHAR2
34 ) is
35 cursor C is select ROWID from WMS_LABEL_FIELD_VARIABLES
36 where LABEL_FORMAT_ID = X_LABEL_FORMAT_ID
37 AND LABEL_FIELD_ID = X_LABEL_FIELD_ID
38 ;
39 begin
40 insert into WMS_LABEL_FIELD_VARIABLES (
41 LABEL_FORMAT_ID,
42 LABEL_FIELD_ID,
43 FIELD_VARIABLE_NAME,
44 FIELD_VARIABLE_DESCRIPTION,
45 LAST_UPDATE_DATE ,
46 LAST_UPDATED_BY ,
47 LAST_UPDATE_LOGIN,
48 CREATED_BY ,
49 CREATION_DATE ,
50 REQUEST_ID ,
51 PROGRAM_APPLICATION_ID,
52 PROGRAM_ID ,
53 PROGRAM_UPDATE_DATE,
54 ATTRIBUTE_CATEGORY,
55 ATTRIBUTE1 ,
56 ATTRIBUTE2 ,
57 ATTRIBUTE3 ,
58 ATTRIBUTE4 ,
59 ATTRIBUTE5 ,
60 ATTRIBUTE6,
61 ATTRIBUTE7,
62 ATTRIBUTE8,
63 ATTRIBUTE9,
64 ATTRIBUTE10,
65 ATTRIBUTE11,
66 ATTRIBUTE12,
67 ATTRIBUTE13,
68 ATTRIBUTE14,
69 ATTRIBUTE15
70 ) values (
71 X_LABEL_FORMAT_ID ,
72 X_LABEL_FIELD_ID,
73 X_FIELD_VARIABLE_NAME,
74 X_FIELD_VARIABLE_DESCRIPTION,
75 X_LAST_UPDATE_DATE ,
76 X_LAST_UPDATED_BY ,
77 X_LAST_UPDATE_LOGIN,
78 X_CREATED_BY ,
79 X_CREATION_DATE ,
80 X_REQUEST_ID ,
81 X_PROGRAM_APPLICATION_ID,
82 X_PROGRAM_ID ,
83 X_PROGRAM_UPDATE_DATE,
84 X_ATTRIBUTE_CATEGORY,
85 X_ATTRIBUTE1 ,
86 X_ATTRIBUTE2 ,
87 X_ATTRIBUTE3 ,
88 X_ATTRIBUTE4 ,
89 X_ATTRIBUTE5 ,
90 X_ATTRIBUTE6,
91 X_ATTRIBUTE7,
92 X_ATTRIBUTE8,
93 X_ATTRIBUTE9,
94 X_ATTRIBUTE10,
95 X_ATTRIBUTE11,
96 X_ATTRIBUTE12,
97 X_ATTRIBUTE13,
98 X_ATTRIBUTE14,
99 X_ATTRIBUTE15
100 );
101
102 open c;
103 fetch c into X_ROWID;
104 if (c%notfound) then
105 close c;
106 raise no_data_found;
107 end if;
108 close c;
109
110 end INSERT_ROW;
111
112 procedure LOCK_ROW (
113 X_LABEL_FORMAT_ID in NUMBER,
114 X_LABEL_FIELD_ID in NUMBER,
115 X_FIELD_VARIABLE_NAME in VARCHAR2,
116 X_FIELD_VARIABLE_DESCRIPTION in VARCHAR2,
117 X_LAST_UPDATE_DATE in DATE,
118 X_LAST_UPDATED_BY in NUMBER,
119 X_LAST_UPDATE_LOGIN in NUMBER,
120 X_CREATED_BY in NUMBER,
121 X_CREATION_DATE in DATE,
122 X_REQUEST_ID in NUMBER,
123 X_PROGRAM_APPLICATION_ID in NUMBER,
124 X_PROGRAM_ID in NUMBER,
125 X_PROGRAM_UPDATE_DATE in DATE,
126 X_ATTRIBUTE_CATEGORY in VARCHAR2,
127 X_ATTRIBUTE1 in VARCHAR2,
128 X_ATTRIBUTE2 in VARCHAR2,
129 X_ATTRIBUTE3 in VARCHAR2,
130 X_ATTRIBUTE4 in VARCHAR2,
131 X_ATTRIBUTE5 in VARCHAR2,
132 X_ATTRIBUTE6 in VARCHAR2,
133 X_ATTRIBUTE7 in VARCHAR2,
134 X_ATTRIBUTE8 in VARCHAR2,
135 X_ATTRIBUTE9 in VARCHAR2,
136 X_ATTRIBUTE10 in VARCHAR2,
137 X_ATTRIBUTE11 in VARCHAR2,
138 X_ATTRIBUTE12 in VARCHAR2,
139 X_ATTRIBUTE13 in VARCHAR2,
140 X_ATTRIBUTE14 in VARCHAR2,
141 X_ATTRIBUTE15 in VARCHAR2
142 ) is
143 cursor c is select
144 FIELD_VARIABLE_NAME,
145 FIELD_VARIABLE_DESCRIPTION,
146 ATTRIBUTE_CATEGORY,
147 ATTRIBUTE1,
148 ATTRIBUTE2,
149 ATTRIBUTE3,
150 ATTRIBUTE4,
151 ATTRIBUTE5,
152 ATTRIBUTE6,
153 ATTRIBUTE7,
154 ATTRIBUTE8,
155 ATTRIBUTE9,
156 ATTRIBUTE10,
157 ATTRIBUTE11,
158 ATTRIBUTE12,
159 ATTRIBUTE13,
160 ATTRIBUTE14,
161 ATTRIBUTE15
162 from WMS_LABEL_FIELD_VARIABLES
163 where LABEL_FORMAT_ID = X_LABEL_FORMAT_ID
164 AND LABEL_FIELD_ID = X_LABEL_FIELD_ID
165 for update of LABEL_FORMAT_ID nowait;
166 recinfo c%rowtype;
167
168 begin
169 open c;
170 fetch c into recinfo;
171 if (c%notfound) then
172 close c;
173 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
174 app_exception.raise_exception;
175 end if;
176 close c;
177 if ( ((recinfo.FIELD_VARIABLE_NAME = X_FIELD_VARIABLE_NAME)
178 OR ((recinfo.FIELD_VARIABLE_NAME is null) AND (X_FIELD_VARIABLE_NAME is null)))
179 AND ((recinfo.FIELD_VARIABLE_DESCRIPTION = X_FIELD_VARIABLE_DESCRIPTION)
180 OR ((recinfo.FIELD_VARIABLE_DESCRIPTION is null) AND (X_FIELD_VARIABLE_DESCRIPTION is null)))
181 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
182 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
183 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
184 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
185 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
186 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
187 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
188 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
189 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
190 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
191 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
192 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
193 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
194 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
195 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
196 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
197 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
198 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
199 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
200 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
201 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
202 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
203 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
204 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
205 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
206 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
207 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
208 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
209 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
210 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
211 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
212 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
213 ) then
214 null;
215 else
216 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
217 app_exception.raise_exception;
218 end if;
219
220 return;
221 end LOCK_ROW;
222
223 procedure UPDATE_ROW (
224 X_LABEL_FORMAT_ID in NUMBER,
225 X_LABEL_FIELD_ID in NUMBER,
226 X_FIELD_VARIABLE_NAME in VARCHAR2,
227 X_FIELD_VARIABLE_DESCRIPTION in VARCHAR2,
228 X_LAST_UPDATE_DATE in DATE,
229 X_LAST_UPDATED_BY in NUMBER,
230 X_LAST_UPDATE_LOGIN in NUMBER,
231 X_CREATED_BY in NUMBER,
232 X_CREATION_DATE in DATE,
233 X_REQUEST_ID in NUMBER,
234 X_PROGRAM_APPLICATION_ID in NUMBER,
235 X_PROGRAM_ID in NUMBER,
236 X_PROGRAM_UPDATE_DATE in DATE,
237 X_ATTRIBUTE_CATEGORY in VARCHAR2,
238 X_ATTRIBUTE1 in VARCHAR2,
239 X_ATTRIBUTE2 in VARCHAR2,
240 X_ATTRIBUTE3 in VARCHAR2,
241 X_ATTRIBUTE4 in VARCHAR2,
242 X_ATTRIBUTE5 in VARCHAR2,
243 X_ATTRIBUTE6 in VARCHAR2,
244 X_ATTRIBUTE7 in VARCHAR2,
245 X_ATTRIBUTE8 in VARCHAR2,
246 X_ATTRIBUTE9 in VARCHAR2,
247 X_ATTRIBUTE10 in VARCHAR2,
248 X_ATTRIBUTE11 in VARCHAR2,
249 X_ATTRIBUTE12 in VARCHAR2,
250 X_ATTRIBUTE13 in VARCHAR2,
251 X_ATTRIBUTE14 in VARCHAR2,
252 X_ATTRIBUTE15 in VARCHAR2
253 ) is
254 begin
255 update WMS_LABEL_FIELD_VARIABLES
256 set FIELD_VARIABLE_NAME = X_FIELD_VARIABLE_NAME,
257 FIELD_VARIABLE_DESCRIPTION = X_FIELD_VARIABLE_DESCRIPTION,
258 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
259 ATTRIBUTE1 = X_ATTRIBUTE1,
260 ATTRIBUTE2 = X_ATTRIBUTE2,
261 ATTRIBUTE3 = X_ATTRIBUTE3,
262 ATTRIBUTE4 = X_ATTRIBUTE4,
263 ATTRIBUTE5 = X_ATTRIBUTE5,
264 ATTRIBUTE6 = X_ATTRIBUTE6,
265 ATTRIBUTE7 = X_ATTRIBUTE7,
266 ATTRIBUTE8 = X_ATTRIBUTE8,
267 ATTRIBUTE9 = X_ATTRIBUTE9,
268 ATTRIBUTE10 = X_ATTRIBUTE10,
269 ATTRIBUTE11 = X_ATTRIBUTE11,
270 ATTRIBUTE12 = X_ATTRIBUTE12,
271 ATTRIBUTE13 = X_ATTRIBUTE13,
272 ATTRIBUTE14 = X_ATTRIBUTE14,
273 ATTRIBUTE15 = X_ATTRIBUTE15,
274 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
275 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
276 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
277 CREATED_BY = X_CREATED_BY,
278 CREATION_DATE = X_CREATION_DATE
279 where LABEL_FORMAT_ID = X_LABEL_FORMAT_ID
280 AND FIELD_VARIABLE_NAME = X_FIELD_VARIABLE_NAME;
281
282 if (sql%notfound) then
283 raise no_data_found;
284 end if;
285
286 end UPDATE_ROW;
287
288 procedure DELETE_ROW (
289 X_ROWID IN VARCHAR2
290 ) is
291 begin
292 delete from WMS_LABEL_FIELD_VARIABLES
293 where ROWID = X_ROWID;
294
295 if (sql%notfound) then
296 raise no_data_found;
297 end if;
298
299 end DELETE_ROW;
300
301 procedure ADD_LANGUAGE
302 is
303 begin
304 null;
305 end ADD_LANGUAGE;
306
307 procedure TRANSLATE_ROW (
308 X_LABEL_FORMAT_ID in VARCHAR2 ,
309 X_LABEL_FIELD_ID in VARCHAR2 ,
310 X_OWNER in VARCHAR2 ,
311 X_LABEL_FORMAT_NAME in VARCHAR2 ,
312 X_FORMAT_DESCRIPTION in VARCHAR2
313 ) IS
314 BEGIN
315 NULL;
316 END translate_row;
317
318 PROCEDURE LOAD_ROW (
319 X_LABEL_FORMAT_ID in NUMBER,
320 X_OWNER in VARCHAR2,
321 X_LABEL_FIELD_ID in NUMBER,
322 X_FIELD_VARIABLE_NAME in VARCHAR2,
323 X_FIELD_VARIABLE_DESCRIPTION in VARCHAR2,
324 X_ATTRIBUTE_CATEGORY in VARCHAR2,
325 X_ATTRIBUTE1 in VARCHAR2,
326 X_ATTRIBUTE2 in VARCHAR2,
327 X_ATTRIBUTE3 in VARCHAR2,
328 X_ATTRIBUTE4 in VARCHAR2,
329 X_ATTRIBUTE5 in VARCHAR2,
330 X_ATTRIBUTE6 in VARCHAR2,
331 X_ATTRIBUTE7 in VARCHAR2,
332 X_ATTRIBUTE8 in VARCHAR2,
333 X_ATTRIBUTE9 in VARCHAR2,
334 X_ATTRIBUTE10 in VARCHAR2,
335 X_ATTRIBUTE11 in VARCHAR2,
336 X_ATTRIBUTE12 in VARCHAR2,
337 X_ATTRIBUTE13 in VARCHAR2,
338 X_ATTRIBUTE14 in VARCHAR2,
339 X_ATTRIBUTE15 in VARCHAR2
340
341 ) IS
342 BEGIN
343 DECLARE
344 l_label_format_id NUMBER;
345 l_label_field_id NUMBER;
346 l_user_id NUMBER := 0;
347 l_row_id VARCHAR2(64);
348 l_sysdate DATE;
349 BEGIN
350 IF (x_owner = 'SEED') THEN
351 l_user_id := 1;
352 END IF;
353 --
354 SELECT Sysdate INTO l_sysdate FROM dual;
355 l_label_format_id := fnd_number.canonical_to_number(x_label_format_id);
356 l_label_field_id := fnd_number.canonical_to_number(x_label_field_id);
357
358 wms_label_field_variables_pkg.update_row
359 (
360 x_label_format_id => l_label_format_id
361 ,x_label_field_id => l_label_field_id
362 ,x_field_variable_name => x_field_variable_name
363 ,x_field_variable_description => x_field_variable_description
364 ,x_last_update_date => l_sysdate
365 ,x_last_updated_by => l_user_id
366 ,x_last_update_login => 0
367 ,x_created_by => l_user_id
368 ,x_creation_date => l_sysdate
369 ,x_request_id => null
370 ,x_program_application_id => null
371 ,x_program_id => null
372 ,x_program_update_date => null
373 ,x_attribute_category => x_attribute_category
374 ,x_attribute1 => x_attribute1
375 ,x_attribute2 => x_attribute2
376 ,x_attribute3 => x_attribute3
377 ,x_attribute4 => x_attribute4
378 ,x_attribute5 => x_attribute5
379 ,x_attribute6 => x_attribute6
380 ,x_attribute7 => x_attribute7
381 ,x_attribute8 => x_attribute8
382 ,x_attribute9 => x_attribute9
383 ,x_attribute10 => x_attribute10
384 ,x_attribute11 => x_attribute11
385 ,x_attribute12 => x_attribute12
386 ,x_attribute13 => x_attribute13
387 ,x_attribute14 => x_attribute14
388 ,x_attribute15 => x_attribute15
389 );
390 EXCEPTION
391 WHEN no_data_found THEN
392 wms_label_field_variables_pkg.insert_row
393 (
394 x_rowid => l_row_id
395 , x_label_format_id => l_label_format_id
396 ,x_label_field_id => l_label_field_id
397 ,x_field_variable_name => x_field_variable_name
398 ,x_field_variable_description => x_field_variable_description
399 ,x_last_update_date => l_sysdate
400 ,x_last_updated_by => l_user_id
401 ,x_last_update_login => 0
402 ,x_created_by => l_user_id
403 ,x_creation_date => l_sysdate
404 ,x_request_id => null
405 ,x_program_application_id => null
406 ,x_program_id => null
407 ,x_program_update_date => null
408 ,x_attribute_category => x_attribute_category
409 ,x_attribute1 => x_attribute1
410 ,x_attribute2 => x_attribute2
411 ,x_attribute3 => x_attribute3
412 ,x_attribute4 => x_attribute4
413 ,x_attribute5 => x_attribute5
414 ,x_attribute6 => x_attribute6
415 ,x_attribute7 => x_attribute7
416 ,x_attribute8 => x_attribute8
417 ,x_attribute9 => x_attribute9
418 ,x_attribute10 => x_attribute10
419 ,x_attribute11 => x_attribute11
420 ,x_attribute12 => x_attribute12
421 ,x_attribute13 => x_attribute13
422 ,x_attribute14 => x_attribute14
423 ,x_attribute15 => x_attribute15
424 );
425 END;
426 END load_row;
427 end WMS_LABEL_FIELD_VARIABLES_PKG;