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