1 package body WMS_ZONES_PKG as
2 /* $Header: WMSGIZNB.pls 120.0 2005/05/25 09:01:45 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_ZONE_ID in NUMBER,
6 X_ATTRIBUTE_CATEGORY in VARCHAR2,
7 X_ATTRIBUTE1 in VARCHAR2,
8 X_ATTRIBUTE2 in VARCHAR2,
9 X_ATTRIBUTE3 in VARCHAR2,
10 X_ATTRIBUTE4 in VARCHAR2,
11 X_ATTRIBUTE5 in VARCHAR2,
12 X_ATTRIBUTE6 in VARCHAR2,
13 X_ATTRIBUTE7 in VARCHAR2,
14 X_ATTRIBUTE8 in VARCHAR2,
15 X_ATTRIBUTE9 in VARCHAR2,
16 X_ATTRIBUTE10 in VARCHAR2,
17 X_ATTRIBUTE11 in VARCHAR2,
18 X_ATTRIBUTE12 in VARCHAR2,
19 X_ATTRIBUTE13 in VARCHAR2,
20 X_ATTRIBUTE14 in VARCHAR2,
21 X_ATTRIBUTE15 in VARCHAR2,
22 X_ORGANIZATION_ID in NUMBER,
23 X_DISABLE_DATE in DATE,
24 X_ENABLED_FLAG in VARCHAR2,
25 X_LABOR_ENABLED IN VARCHAR2,
26 X_ZONE_NAME in VARCHAR2,
27 X_DESCRIPTION in VARCHAR2,
28 X_CREATION_DATE in DATE,
29 X_CREATED_BY in NUMBER,
30 X_LAST_UPDATE_DATE in DATE,
31 X_LAST_UPDATED_BY in NUMBER,
32 X_LAST_UPDATE_LOGIN in NUMBER
33 ) is
34 cursor C is select ROWID from WMS_ZONES_B
35 where ZONE_ID = X_ZONE_ID
36 ;
37 begin
38 insert into WMS_ZONES_B (
39 ATTRIBUTE_CATEGORY,
40 ATTRIBUTE1,
41 ATTRIBUTE2,
42 ATTRIBUTE3,
43 ATTRIBUTE4,
44 ATTRIBUTE5,
45 ATTRIBUTE6,
46 ATTRIBUTE7,
47 ATTRIBUTE8,
48 ATTRIBUTE9,
49 ATTRIBUTE10,
50 ATTRIBUTE11,
51 ATTRIBUTE12,
52 ATTRIBUTE13,
53 ATTRIBUTE14,
54 ATTRIBUTE15,
55 ZONE_ID,
56 ORGANIZATION_ID,
57 DISABLE_DATE,
58 ENABLED_FLAG,
59 LABOR_ENABLED,
60 CREATION_DATE,
61 CREATED_BY,
62 LAST_UPDATE_DATE,
63 LAST_UPDATED_BY,
64 LAST_UPDATE_LOGIN
65 ) values (
66 X_ATTRIBUTE_CATEGORY,
67 X_ATTRIBUTE1,
68 X_ATTRIBUTE2,
69 X_ATTRIBUTE3,
70 X_ATTRIBUTE4,
71 X_ATTRIBUTE5,
72 X_ATTRIBUTE6,
73 X_ATTRIBUTE7,
74 X_ATTRIBUTE8,
75 X_ATTRIBUTE9,
76 X_ATTRIBUTE10,
77 X_ATTRIBUTE11,
78 X_ATTRIBUTE12,
79 X_ATTRIBUTE13,
80 X_ATTRIBUTE14,
81 X_ATTRIBUTE15,
82 X_ZONE_ID,
83 X_ORGANIZATION_ID,
84 X_DISABLE_DATE,
85 X_ENABLED_FLAG,
86 X_LABOR_ENABLED,
87 X_CREATION_DATE,
88 X_CREATED_BY,
89 X_LAST_UPDATE_DATE,
90 X_LAST_UPDATED_BY,
91 X_LAST_UPDATE_LOGIN
92 );
93
94 insert into WMS_ZONES_TL (
95 ZONE_ID,
96 ZONE_NAME,
97 DESCRIPTION,
98 LAST_UPDATE_DATE,
99 LAST_UPDATED_BY,
100 CREATION_DATE,
101 CREATED_BY,
102 LAST_UPDATE_LOGIN,
103 LANGUAGE,
104 SOURCE_LANG
105 ) select
106 X_ZONE_ID,
107 X_ZONE_NAME,
108 X_DESCRIPTION,
109 X_LAST_UPDATE_DATE,
110 X_LAST_UPDATED_BY,
111 X_CREATION_DATE,
112 X_CREATED_BY,
113 X_LAST_UPDATE_LOGIN,
114 L.LANGUAGE_CODE,
115 userenv('LANG')
116 from FND_LANGUAGES L
117 where L.INSTALLED_FLAG in ('I', 'B')
118 and not exists
119 (select NULL
120 from WMS_ZONES_TL T
121 where T.ZONE_ID = X_ZONE_ID
122 and T.LANGUAGE = L.LANGUAGE_CODE);
123
124 open c;
125 fetch c into X_ROWID;
126 if (c%notfound) then
127 close c;
128 raise no_data_found;
129 end if;
130 close c;
131
132 end INSERT_ROW;
133
134 procedure LOCK_ROW (
135 X_ZONE_ID in NUMBER,
136 X_ATTRIBUTE_CATEGORY in VARCHAR2,
137 X_ATTRIBUTE1 in VARCHAR2,
138 X_ATTRIBUTE2 in VARCHAR2,
139 X_ATTRIBUTE3 in VARCHAR2,
140 X_ATTRIBUTE4 in VARCHAR2,
141 X_ATTRIBUTE5 in VARCHAR2,
142 X_ATTRIBUTE6 in VARCHAR2,
143 X_ATTRIBUTE7 in VARCHAR2,
144 X_ATTRIBUTE8 in VARCHAR2,
145 X_ATTRIBUTE9 in VARCHAR2,
146 X_ATTRIBUTE10 in VARCHAR2,
147 X_ATTRIBUTE11 in VARCHAR2,
148 X_ATTRIBUTE12 in VARCHAR2,
149 X_ATTRIBUTE13 in VARCHAR2,
150 X_ATTRIBUTE14 in VARCHAR2,
151 X_ATTRIBUTE15 in VARCHAR2,
152 X_ORGANIZATION_ID in NUMBER,
153 X_DISABLE_DATE in DATE,
154 X_ENABLED_FLAG in VARCHAR2,
155 X_ZONE_NAME in VARCHAR2,
156 X_DESCRIPTION in VARCHAR2
157 ) is
158 cursor c is select
159 ATTRIBUTE_CATEGORY,
160 ATTRIBUTE1,
161 ATTRIBUTE2,
162 ATTRIBUTE3,
163 ATTRIBUTE4,
164 ATTRIBUTE5,
165 ATTRIBUTE6,
166 ATTRIBUTE7,
167 ATTRIBUTE8,
168 ATTRIBUTE9,
169 ATTRIBUTE10,
170 ATTRIBUTE11,
171 ATTRIBUTE12,
172 ATTRIBUTE13,
173 ATTRIBUTE14,
174 ATTRIBUTE15,
175 ORGANIZATION_ID,
176 DISABLE_DATE,
177 ENABLED_FLAG
178 from WMS_ZONES_B
179 where ZONE_ID = X_ZONE_ID
180 for update of ZONE_ID nowait;
181 recinfo c%rowtype;
182
183 cursor c1 is select
184 ZONE_NAME,
185 DESCRIPTION,
186 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
187 from WMS_ZONES_TL
188 where ZONE_ID = X_ZONE_ID
189 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
190 for update of ZONE_ID nowait;
191 begin
192 open c;
193 fetch c into recinfo;
194 if (c%notfound) then
195 close c;
196 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
197 app_exception.raise_exception;
198 end if;
199 close c;
200 if ( ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
201 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
202 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
203 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
204 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
205 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
206 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
207 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
208 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
209 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
210 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
211 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
212 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
213 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
214 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
215 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
216 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
217 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
218 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
219 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
220 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
221 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
222 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
223 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
224 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
225 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
226 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
227 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
228 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
229 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
230 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
231 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
232 AND (recinfo.ORGANIZATION_ID = X_ORGANIZATION_ID)
233 AND ((recinfo.DISABLE_DATE = X_DISABLE_DATE)
234 OR ((recinfo.DISABLE_DATE is null) AND (X_DISABLE_DATE is null)))
235 AND ((recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
236 OR ((recinfo.ENABLED_FLAG is null) AND (X_ENABLED_FLAG is null)))
237 ) then
238 null;
239 else
240 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
241 app_exception.raise_exception;
242 end if;
243
244 for tlinfo in c1 loop
245 if (tlinfo.BASELANG = 'Y') then
246 if ( (tlinfo.ZONE_NAME = X_ZONE_NAME)
247 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
248 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
249 ) then
250 null;
251 else
252 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
253 app_exception.raise_exception;
254 end if;
255 end if;
256 end loop;
257 return;
258 end LOCK_ROW;
259
260 procedure UPDATE_ROW (
261 X_ZONE_ID in NUMBER,
262 X_ATTRIBUTE_CATEGORY in VARCHAR2,
263 X_ATTRIBUTE1 in VARCHAR2,
264 X_ATTRIBUTE2 in VARCHAR2,
265 X_ATTRIBUTE3 in VARCHAR2,
266 X_ATTRIBUTE4 in VARCHAR2,
267 X_ATTRIBUTE5 in VARCHAR2,
268 X_ATTRIBUTE6 in VARCHAR2,
269 X_ATTRIBUTE7 in VARCHAR2,
270 X_ATTRIBUTE8 in VARCHAR2,
271 X_ATTRIBUTE9 in VARCHAR2,
272 X_ATTRIBUTE10 in VARCHAR2,
273 X_ATTRIBUTE11 in VARCHAR2,
274 X_ATTRIBUTE12 in VARCHAR2,
275 X_ATTRIBUTE13 in VARCHAR2,
276 X_ATTRIBUTE14 in VARCHAR2,
277 X_ATTRIBUTE15 in VARCHAR2,
278 X_ORGANIZATION_ID in NUMBER,
279 X_DISABLE_DATE in DATE,
280 X_ENABLED_FLAG in VARCHAR2,
281 X_LABOR_ENABLED IN VARCHAR2,
282 X_ZONE_NAME in VARCHAR2,
283 X_DESCRIPTION in VARCHAR2,
284 X_LAST_UPDATE_DATE in DATE,
285 X_LAST_UPDATED_BY in NUMBER,
286 X_LAST_UPDATE_LOGIN in NUMBER
287 ) is
288 begin
289 update WMS_ZONES_B set
290 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
291 ATTRIBUTE1 = X_ATTRIBUTE1,
292 ATTRIBUTE2 = X_ATTRIBUTE2,
293 ATTRIBUTE3 = X_ATTRIBUTE3,
294 ATTRIBUTE4 = X_ATTRIBUTE4,
295 ATTRIBUTE5 = X_ATTRIBUTE5,
296 ATTRIBUTE6 = X_ATTRIBUTE6,
297 ATTRIBUTE7 = X_ATTRIBUTE7,
298 ATTRIBUTE8 = X_ATTRIBUTE8,
299 ATTRIBUTE9 = X_ATTRIBUTE9,
300 ATTRIBUTE10 = X_ATTRIBUTE10,
301 ATTRIBUTE11 = X_ATTRIBUTE11,
302 ATTRIBUTE12 = X_ATTRIBUTE12,
303 ATTRIBUTE13 = X_ATTRIBUTE13,
304 ATTRIBUTE14 = X_ATTRIBUTE14,
305 ATTRIBUTE15 = X_ATTRIBUTE15,
306 ORGANIZATION_ID = X_ORGANIZATION_ID,
307 DISABLE_DATE = X_DISABLE_DATE,
308 ENABLED_FLAG = X_ENABLED_FLAG,
309 LABOR_ENABLED = X_LABOR_ENABLED,
310 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
311 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
312 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
313 where ZONE_ID = X_ZONE_ID;
314
315 if (sql%notfound) then
316 raise no_data_found;
317 end if;
318
319 update WMS_ZONES_TL set
320 ZONE_NAME = X_ZONE_NAME,
321 DESCRIPTION = X_DESCRIPTION,
322 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
323 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
324 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
325 SOURCE_LANG = userenv('LANG')
326 where ZONE_ID = X_ZONE_ID
327 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
328
329 if (sql%notfound) then
330 raise no_data_found;
331 end if;
332 end UPDATE_ROW;
333
334 procedure DELETE_ROW (
335 X_ZONE_ID in NUMBER
336 ) is
337 begin
338 delete from WMS_ZONES_TL
339 where ZONE_ID = X_ZONE_ID;
340
341 if (sql%notfound) then
342 raise no_data_found;
343 end if;
344
345 delete from WMS_ZONES_B
346 where ZONE_ID = X_ZONE_ID;
347
348 if (sql%notfound) then
349 raise no_data_found;
350 end if;
351 end DELETE_ROW;
352
353 procedure ADD_LANGUAGE
354 is
355 begin
356 delete from WMS_ZONES_TL T
357 where not exists
358 (select NULL
359 from WMS_ZONES_B B
360 where B.ZONE_ID = T.ZONE_ID
361 );
362
363 update WMS_ZONES_TL T set (
364 ZONE_NAME,
365 DESCRIPTION
366 ) = (select
367 B.ZONE_NAME,
368 B.DESCRIPTION
369 from WMS_ZONES_TL B
370 where B.ZONE_ID = T.ZONE_ID
371 and B.LANGUAGE = T.SOURCE_LANG)
372 where (
373 T.ZONE_ID,
374 T.LANGUAGE
375 ) in (select
376 SUBT.ZONE_ID,
377 SUBT.LANGUAGE
378 from WMS_ZONES_TL SUBB, WMS_ZONES_TL SUBT
379 where SUBB.ZONE_ID = SUBT.ZONE_ID
380 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
381 and (SUBB.ZONE_NAME <> SUBT.ZONE_NAME
382 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
383 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
384 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
385 ));
386
387 insert into WMS_ZONES_TL (
388 ZONE_ID,
389 ZONE_NAME,
390 DESCRIPTION,
391 LAST_UPDATE_DATE,
392 LAST_UPDATED_BY,
393 CREATION_DATE,
394 CREATED_BY,
395 LAST_UPDATE_LOGIN,
396 LANGUAGE,
397 SOURCE_LANG
398 ) select /*+ ORDERED */
399 B.ZONE_ID,
400 B.ZONE_NAME,
401 B.DESCRIPTION,
402 B.LAST_UPDATE_DATE,
403 B.LAST_UPDATED_BY,
404 B.CREATION_DATE,
405 B.CREATED_BY,
406 B.LAST_UPDATE_LOGIN,
407 L.LANGUAGE_CODE,
408 B.SOURCE_LANG
409 from WMS_ZONES_TL B, FND_LANGUAGES L
410 where L.INSTALLED_FLAG in ('I', 'B')
411 and B.LANGUAGE = userenv('LANG')
412 and not exists
413 (select NULL
414 from WMS_ZONES_TL T
415 where T.ZONE_ID = B.ZONE_ID
416 and T.LANGUAGE = L.LANGUAGE_CODE);
417 end ADD_LANGUAGE;
418
419 end WMS_ZONES_PKG;
[Home] [Help]