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