1 package body WMS_WP_WAVE_EXCEPTIONS_PKG as
2 /* $Header: WMSWPTEB.pls 120.1.12010000.1 2009/03/25 09:55:23 shrmitra noship $ */
3 procedure INSERT_ROW (
4 X_EXCEPTION_ID in NUMBER,
5 X_EXCEPTION_ENTITY in VARCHAR2,
6 X_EXCEPTION_STAGE in VARCHAR2,
7 X_EXCEPTION_LEVEL in VARCHAR2,
8 X_EXCEPTION_MSG in VARCHAR2,
9 X_WAVE_HEADER_ID in NUMBER,
10 X_TRIP_ID in NUMBER,
11 X_DELIVERY_ID in NUMBER,
12 X_ORDER_NUMBER in NUMBER,
13 X_ORDER_LINE_ID in NUMBER,
14 X_STATUS in VARCHAR2,
15 X_READY_TO_RELEASE in VARCHAR2,
16 X_BACKORDERED in VARCHAR2,
17 X_CROSSDOCK_PLANNED in VARCHAR2,
18 X_REPLENISHMENT_PLANNED in VARCHAR2,
19 X_TASKED in VARCHAR2,
20 X_PICKED in VARCHAR2,
21 X_PACKED in VARCHAR2,
22 X_STAGED in VARCHAR2,
23 X_LOADED_TO_DOCK in VARCHAR2,
24 X_SHIPPED in VARCHAR2,
25 X_CONCURRENT_REQUEST_ID in NUMBER,
26 X_PROGRAM_ID in NUMBER,
27 X_EXCEPTION_NAME 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_WP_WAVE_EXCEPTIONS_B
35 where EXCEPTION_ID = X_EXCEPTION_ID
36 ;
37
38 -- create a cursor variable here SUDHEER
39 c_var c%rowtype;
40 begin
41 insert into WMS_WP_WAVE_EXCEPTIONS_B (
42 EXCEPTION_ID,
43 EXCEPTION_ENTITY,
44 EXCEPTION_STAGE,
45 EXCEPTION_LEVEL,
46 EXCEPTION_MSG,
47 WAVE_HEADER_ID,
48 TRIP_ID,
49 DELIVERY_ID,
50 ORDER_NUMBER,
51 ORDER_LINE_ID,
52 STATUS,
53 READY_TO_RELEASE,
54 BACKORDERED,
55 CROSSDOCK_PLANNED,
56 REPLENISHMENT_PLANNED,
57 TASKED,
58 PICKED,
59 PACKED,
60 STAGED,
61 LOADED_TO_DOCK,
62 SHIPPED,
63 CONCURRENT_REQUEST_ID,
64 PROGRAM_ID,
65 CREATION_DATE,
66 CREATED_BY,
67 LAST_UPDATE_DATE,
68 LAST_UPDATED_BY,
69 LAST_UPDATE_LOGIN
70 ) values (
71 X_EXCEPTION_ID,
72 X_EXCEPTION_ENTITY,
73 X_EXCEPTION_STAGE,
74 X_EXCEPTION_LEVEL,
75 X_EXCEPTION_MSG,
76 X_WAVE_HEADER_ID,
77 X_TRIP_ID,
78 X_DELIVERY_ID,
79 X_ORDER_NUMBER,
80 X_ORDER_LINE_ID,
81 X_STATUS,
82 X_READY_TO_RELEASE,
83 X_BACKORDERED,
84 X_CROSSDOCK_PLANNED,
85 X_REPLENISHMENT_PLANNED,
86 X_TASKED,
87 X_PICKED,
88 X_PACKED,
89 X_STAGED,
90 X_LOADED_TO_DOCK,
91 X_SHIPPED,
92 X_CONCURRENT_REQUEST_ID,
93 X_PROGRAM_ID,
94 X_CREATION_DATE,
95 X_CREATED_BY,
96 X_LAST_UPDATE_DATE,
97 X_LAST_UPDATED_BY,
98 X_LAST_UPDATE_LOGIN
99 );
100
101 insert into WMS_WP_WAVE_EXCEPTIONS_TL (
102 EXCEPTION_ID,
103 EXCEPTION_NAME,
104 CREATED_BY,
105 CREATION_DATE,
106 LAST_UPDATED_BY,
107 LAST_UPDATE_DATE,
108 LAST_UPDATE_LOGIN,
109 LANGUAGE,
110 SOURCE_LANG
111 ) select
112 X_EXCEPTION_ID,
113 X_EXCEPTION_NAME,
114 X_CREATED_BY,
115 X_CREATION_DATE,
116 X_LAST_UPDATED_BY,
117 X_LAST_UPDATE_DATE,
118 X_LAST_UPDATE_LOGIN,
119 L.LANGUAGE_CODE,
120 userenv('LANG')
121 from FND_LANGUAGES L
122 where L.INSTALLED_FLAG in ('I', 'B')
123 and not exists
124 (select NULL
125 from WMS_WP_WAVE_EXCEPTIONS_TL T
126 where T.EXCEPTION_ID = X_EXCEPTION_ID
127 and T.LANGUAGE = L.LANGUAGE_CODE);
128
129 open c;
130 --fetch c into X_ROWID; -- sudheer
131 fetch c into c_var;
132 if (c%notfound) then
133 close c;
134 raise no_data_found;
135 end if;
136 close c;
137
138 end INSERT_ROW;
139
140 procedure LOCK_ROW (
141 X_EXCEPTION_ID in NUMBER,
142 X_EXCEPTION_ENTITY in VARCHAR2,
143 X_EXCEPTION_STAGE in VARCHAR2,
144 X_EXCEPTION_LEVEL in VARCHAR2,
145 X_EXCEPTION_MSG in VARCHAR2,
146 X_WAVE_HEADER_ID in NUMBER,
147 X_TRIP_ID in NUMBER,
148 X_DELIVERY_ID in NUMBER,
149 X_ORDER_NUMBER in NUMBER,
150 X_ORDER_LINE_ID in NUMBER,
151 X_STATUS in VARCHAR2,
152 X_READY_TO_RELEASE in VARCHAR2,
153 X_BACKORDERED in VARCHAR2,
154 X_CROSSDOCK_PLANNED in VARCHAR2,
155 X_REPLENISHMENT_PLANNED in VARCHAR2,
156 X_TASKED in VARCHAR2,
157 X_PICKED in VARCHAR2,
158 X_PACKED in VARCHAR2,
159 X_STAGED in VARCHAR2,
160 X_LOADED_TO_DOCK in VARCHAR2,
161 X_SHIPPED in VARCHAR2,
162 X_CONCURRENT_REQUEST_ID in NUMBER,
163 X_PROGRAM_ID in NUMBER, -- sudheer
164 X_EXCEPTION_NAME in VARCHAR2
165 ) is
166 cursor c is select
167 EXCEPTION_ENTITY,
168 EXCEPTION_STAGE,
169 EXCEPTION_LEVEL,
170 EXCEPTION_MSG,
171 WAVE_HEADER_ID,
172 TRIP_ID,
173 DELIVERY_ID,
174 ORDER_NUMBER,
175 ORDER_LINE_ID,
176 STATUS,
177 READY_TO_RELEASE,
178 BACKORDERED,
179 CROSSDOCK_PLANNED,
180 REPLENISHMENT_PLANNED,
181 TASKED,
182 PICKED,
183 PACKED,
184 STAGED,
185 LOADED_TO_DOCK,
186 SHIPPED,
187 CONCURRENT_REQUEST_ID,
188 PROGRAM_ID -- sudheer
189 from WMS_WP_WAVE_EXCEPTIONS_B
190 where EXCEPTION_ID = X_EXCEPTION_ID
191 for update of EXCEPTION_ID nowait;
192 recinfo c%rowtype;
193
194 cursor c1 is select
195 EXCEPTION_NAME,
196 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
197 from WMS_WP_WAVE_EXCEPTIONS_TL
198 where EXCEPTION_ID = X_EXCEPTION_ID
199 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
200 for update of EXCEPTION_ID nowait;
201 begin
202 open c;
203 fetch c into recinfo;
204 if (c%notfound) then
205 close c;
206 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
207 app_exception.raise_exception;
208 end if;
209 close c;
210 if ( ((recinfo.EXCEPTION_ENTITY = X_EXCEPTION_ENTITY)
211 OR ((recinfo.EXCEPTION_ENTITY is null) AND (X_EXCEPTION_ENTITY is null)))
212 AND ((recinfo.EXCEPTION_STAGE = X_EXCEPTION_STAGE)
213 OR ((recinfo.EXCEPTION_STAGE is null) AND (X_EXCEPTION_STAGE is null)))
214 AND ((recinfo.EXCEPTION_LEVEL = X_EXCEPTION_LEVEL)
215 OR ((recinfo.EXCEPTION_LEVEL is null) AND (X_EXCEPTION_LEVEL is null)))
216 AND ((recinfo.EXCEPTION_MSG = X_EXCEPTION_MSG)
217 OR ((recinfo.EXCEPTION_MSG is null) AND (X_EXCEPTION_MSG is null)))
218 AND (recinfo.WAVE_HEADER_ID = X_WAVE_HEADER_ID)
219 AND ((recinfo.TRIP_ID = X_TRIP_ID)
220 OR ((recinfo.TRIP_ID is null) AND (X_TRIP_ID is null)))
221 AND ((recinfo.DELIVERY_ID = X_DELIVERY_ID)
222 OR ((recinfo.DELIVERY_ID is null) AND (X_DELIVERY_ID is null)))
223 AND ((recinfo.ORDER_NUMBER = X_ORDER_NUMBER)
224 OR ((recinfo.ORDER_NUMBER is null) AND (X_ORDER_NUMBER is null)))
225 AND ((recinfo.ORDER_LINE_ID = X_ORDER_LINE_ID)
226 OR ((recinfo.ORDER_LINE_ID is null) AND (X_ORDER_LINE_ID is null)))
227 AND ((recinfo.STATUS = X_STATUS)
228 OR ((recinfo.STATUS is null) AND (X_STATUS is null)))
229 AND ((recinfo.READY_TO_RELEASE = X_READY_TO_RELEASE)
230 OR ((recinfo.READY_TO_RELEASE is null) AND (X_READY_TO_RELEASE is null)))
231 AND ((recinfo.BACKORDERED = X_BACKORDERED)
232 OR ((recinfo.BACKORDERED is null) AND (X_BACKORDERED is null)))
233 AND ((recinfo.CROSSDOCK_PLANNED = X_CROSSDOCK_PLANNED)
234 OR ((recinfo.CROSSDOCK_PLANNED is null) AND (X_CROSSDOCK_PLANNED is null)))
235 AND ((recinfo.REPLENISHMENT_PLANNED = X_REPLENISHMENT_PLANNED)
236 OR ((recinfo.REPLENISHMENT_PLANNED is null) AND (X_REPLENISHMENT_PLANNED is null)))
237 AND ((recinfo.TASKED = X_TASKED)
238 OR ((recinfo.TASKED is null) AND (X_TASKED is null)))
239 AND ((recinfo.PICKED = X_PICKED)
240 OR ((recinfo.PICKED is null) AND (X_PICKED is null)))
241 AND ((recinfo.PACKED = X_PACKED)
242 OR ((recinfo.PACKED is null) AND (X_PACKED is null)))
243 AND ((recinfo.STAGED = X_STAGED)
244 OR ((recinfo.STAGED is null) AND (X_STAGED is null)))
245 AND ((recinfo.LOADED_TO_DOCK = X_LOADED_TO_DOCK)
246 OR ((recinfo.LOADED_TO_DOCK is null) AND (X_LOADED_TO_DOCK is null)))
247 AND ((recinfo.SHIPPED = X_SHIPPED)
248 OR ((recinfo.SHIPPED is null) AND (X_SHIPPED is null)))
249 AND ((recinfo.CONCURRENT_REQUEST_ID = X_CONCURRENT_REQUEST_ID)
250 OR ((recinfo.CONCURRENT_REQUEST_ID is null) AND (X_CONCURRENT_REQUEST_ID is null)))
251 AND ((recinfo.PROGRAM_ID = X_PROGRAM_ID) -- sudheer
252 OR ((recinfo.PROGRAM_ID is null) AND (X_PROGRAM_ID is null))) -- sudheer
253
254 ) then
255 null;
256 else
257 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
258 app_exception.raise_exception;
259 end if;
260
261 for tlinfo in c1 loop
262 if (tlinfo.BASELANG = 'Y') then
263 if ( ((tlinfo.EXCEPTION_NAME = X_EXCEPTION_NAME)
264 OR ((tlinfo.EXCEPTION_NAME is null) AND (X_EXCEPTION_NAME is null)))
265 ) then
266 null;
267 else
268 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
269 app_exception.raise_exception;
270 end if;
271 end if;
272 end loop;
273 return;
274 end LOCK_ROW;
275
276 procedure UPDATE_ROW (
277 X_EXCEPTION_ID in NUMBER,
278 X_EXCEPTION_ENTITY in VARCHAR2,
279 X_EXCEPTION_STAGE in VARCHAR2,
280 X_EXCEPTION_LEVEL in VARCHAR2,
281 X_EXCEPTION_MSG in VARCHAR2,
282 X_WAVE_HEADER_ID in NUMBER,
283 X_TRIP_ID in NUMBER,
284 X_DELIVERY_ID in NUMBER,
285 X_ORDER_NUMBER in NUMBER,
286 X_ORDER_LINE_ID in NUMBER,
287 X_STATUS in VARCHAR2,
288 X_READY_TO_RELEASE in VARCHAR2,
289 X_BACKORDERED in VARCHAR2,
290 X_CROSSDOCK_PLANNED in VARCHAR2,
291 X_REPLENISHMENT_PLANNED in VARCHAR2,
292 X_TASKED in VARCHAR2,
293 X_PICKED in VARCHAR2,
294 X_PACKED in VARCHAR2,
295 X_STAGED in VARCHAR2,
296 X_LOADED_TO_DOCK in VARCHAR2,
297 X_SHIPPED in VARCHAR2,
298 X_CONCURRENT_REQUEST_ID in NUMBER,
299 X_PROGRAM_ID in NUMBER, -- added by sudheer.
300 X_EXCEPTION_NAME in VARCHAR2,
301 X_LAST_UPDATE_DATE in DATE,
302 X_LAST_UPDATED_BY in NUMBER,
303 X_LAST_UPDATE_LOGIN in NUMBER
304 ) is
305 begin
306 update WMS_WP_WAVE_EXCEPTIONS_B set
307 EXCEPTION_ENTITY = X_EXCEPTION_ENTITY,
308 EXCEPTION_STAGE = X_EXCEPTION_STAGE,
309 EXCEPTION_LEVEL = X_EXCEPTION_LEVEL,
310 EXCEPTION_MSG = X_EXCEPTION_MSG,
311 WAVE_HEADER_ID = X_WAVE_HEADER_ID,
312 TRIP_ID = X_TRIP_ID,
313 DELIVERY_ID = X_DELIVERY_ID,
314 ORDER_NUMBER = X_ORDER_NUMBER,
315 ORDER_LINE_ID = X_ORDER_LINE_ID,
316 STATUS = X_STATUS,
317 READY_TO_RELEASE = X_READY_TO_RELEASE,
318 BACKORDERED = X_BACKORDERED,
319 CROSSDOCK_PLANNED = X_CROSSDOCK_PLANNED,
320 REPLENISHMENT_PLANNED = X_REPLENISHMENT_PLANNED,
321 TASKED = X_TASKED,
322 PICKED = X_PICKED,
323 PACKED = X_PACKED,
324 STAGED = X_STAGED,
325 LOADED_TO_DOCK = X_LOADED_TO_DOCK,
326 SHIPPED = X_SHIPPED,
327 CONCURRENT_REQUEST_ID = X_CONCURRENT_REQUEST_ID,
328 PROGRAM_ID = X_PROGRAM_ID, -- by SUDHEER
329 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
330 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
331 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
332 where EXCEPTION_ID = X_EXCEPTION_ID;
333
334 if (sql%notfound) then
335 raise no_data_found;
336 end if;
337
338 update WMS_WP_WAVE_EXCEPTIONS_TL set
339 EXCEPTION_NAME = X_EXCEPTION_NAME,
340 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
341 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
342 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
343 SOURCE_LANG = userenv('LANG')
344 where EXCEPTION_ID = X_EXCEPTION_ID
345 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
346
347 if (sql%notfound) then
348 raise no_data_found;
349 end if;
350 end UPDATE_ROW;
351
352 procedure DELETE_ROW (
353 X_EXCEPTION_ID in NUMBER
354 ) is
355 begin
356 delete from WMS_WP_WAVE_EXCEPTIONS_TL
357 where EXCEPTION_ID = X_EXCEPTION_ID;
358
359 if (sql%notfound) then
360 raise no_data_found;
361 end if;
362
363 delete from WMS_WP_WAVE_EXCEPTIONS_B
364 where EXCEPTION_ID = X_EXCEPTION_ID;
365
366 if (sql%notfound) then
367 raise no_data_found;
368 end if;
369 end DELETE_ROW;
370
371 procedure ADD_LANGUAGE
372 is
373 begin
374 delete from WMS_WP_WAVE_EXCEPTIONS_TL T
375 where not exists
376 (select NULL
377 from WMS_WP_WAVE_EXCEPTIONS_B B
378 where B.EXCEPTION_ID = T.EXCEPTION_ID
379 );
380
381 update WMS_WP_WAVE_EXCEPTIONS_TL T set (
382 EXCEPTION_NAME
383 ) = (select
384 B.EXCEPTION_NAME
385 from WMS_WP_WAVE_EXCEPTIONS_TL B
386 where B.EXCEPTION_ID = T.EXCEPTION_ID
387 and B.LANGUAGE = T.SOURCE_LANG)
388 where (
389 T.EXCEPTION_ID,
390 T.LANGUAGE
391 ) in (select
392 SUBT.EXCEPTION_ID,
393 SUBT.LANGUAGE
394 from WMS_WP_WAVE_EXCEPTIONS_TL SUBB, WMS_WP_WAVE_EXCEPTIONS_TL SUBT
395 where SUBB.EXCEPTION_ID = SUBT.EXCEPTION_ID
396 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
397 and (SUBB.EXCEPTION_NAME <> SUBT.EXCEPTION_NAME
398 or (SUBB.EXCEPTION_NAME is null and SUBT.EXCEPTION_NAME is not null)
399 or (SUBB.EXCEPTION_NAME is not null and SUBT.EXCEPTION_NAME is null)
400 ));
401
402 insert into WMS_WP_WAVE_EXCEPTIONS_TL (
403 EXCEPTION_ID,
404 EXCEPTION_NAME,
405 CREATED_BY,
406 CREATION_DATE,
407 LAST_UPDATED_BY,
408 LAST_UPDATE_DATE,
409 LAST_UPDATE_LOGIN,
410 LANGUAGE,
411 SOURCE_LANG
412 ) select /*+ ORDERED */
413 B.EXCEPTION_ID,
414 B.EXCEPTION_NAME,
415 B.CREATED_BY,
416 B.CREATION_DATE,
417 B.LAST_UPDATED_BY,
418 B.LAST_UPDATE_DATE,
419 B.LAST_UPDATE_LOGIN,
420 L.LANGUAGE_CODE,
421 B.SOURCE_LANG
422 from WMS_WP_WAVE_EXCEPTIONS_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_WP_WAVE_EXCEPTIONS_TL T
428 where T.EXCEPTION_ID = B.EXCEPTION_ID
429 and T.LANGUAGE = L.LANGUAGE_CODE);
430 end ADD_LANGUAGE;
431
432 end WMS_WP_WAVE_EXCEPTIONS_PKG;
[Home] [Help]