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