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