[Home] [Help]
PACKAGE BODY: APPS.AMW_OPINION_VALUES_PKG
Source
1 package body AMW_OPINION_VALUES_PKG as
2 /* $Header: amwtopvb.pls 115.10 2004/03/26 22:41:58 cpetriuc noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_OPINION_VALUE_ID in NUMBER,
7 X_OPINION_VALUE_CODE in VARCHAR2,
8 X_OPINION_COMPONENT_ID in NUMBER,
9 X_END_DATE in DATE,
10 X_ATTACHMENT_ID in NUMBER,
11 X_IMAGE_FILE_NAME in VARCHAR2,
12 X_DISPLAY_ORDER in NUMBER,
13 X_SECURITY_GROUP_ID in NUMBER,
14 X_OBJECT_VERSION_NUMBER in NUMBER,
15 X_OPINION_VALUE_NAME in VARCHAR2,
16 X_CREATION_DATE in DATE,
17 X_CREATED_BY in NUMBER,
18 X_LAST_UPDATE_DATE in DATE,
19 X_LAST_UPDATED_BY in NUMBER,
20 X_LAST_UPDATE_LOGIN in NUMBER
21 ) is
22 cursor C is select ROWID from AMW_OPINION_VALUES_B
23 where OPINION_VALUE_ID = X_OPINION_VALUE_ID
24 ;
25 begin
26 insert into AMW_OPINION_VALUES_B (
27 OPINION_VALUE_ID,
28 OPINION_VALUE_CODE,
29 OPINION_COMPONENT_ID,
30 END_DATE,
31 ATTACHMENT_ID,
32 IMAGE_FILE_NAME,
33 DISPLAY_ORDER,
34 SECURITY_GROUP_ID,
35 OBJECT_VERSION_NUMBER,
36 CREATION_DATE,
37 CREATED_BY,
38 LAST_UPDATE_DATE,
39 LAST_UPDATED_BY,
40 LAST_UPDATE_LOGIN
41 ) values (
42 X_OPINION_VALUE_ID,
43 X_OPINION_VALUE_CODE,
44 X_OPINION_COMPONENT_ID,
45 X_END_DATE,
46 X_ATTACHMENT_ID,
47 X_IMAGE_FILE_NAME,
48 X_DISPLAY_ORDER,
49 X_SECURITY_GROUP_ID,
50 X_OBJECT_VERSION_NUMBER,
51 X_CREATION_DATE,
52 X_CREATED_BY,
53 X_LAST_UPDATE_DATE,
54 X_LAST_UPDATED_BY,
55 X_LAST_UPDATE_LOGIN
56 );
57
58 insert into AMW_OPINION_VALUES_TL (
59 OPINION_VALUE_ID,
60 OPINION_VALUE_NAME,
61 CREATED_BY,
62 CREATION_DATE,
63 LAST_UPDATED_BY,
64 LAST_UPDATE_DATE,
65 LAST_UPDATE_LOGIN,
66 SECURITY_GROUP_ID,
67 OBJECT_VERSION_NUMBER,
68 LANGUAGE,
69 SOURCE_LANG
70 ) select
71 X_OPINION_VALUE_ID,
72 X_OPINION_VALUE_NAME,
73 X_CREATED_BY,
74 X_CREATION_DATE,
75 X_LAST_UPDATED_BY,
76 X_LAST_UPDATE_DATE,
77 X_LAST_UPDATE_LOGIN,
78 X_SECURITY_GROUP_ID,
79 X_OBJECT_VERSION_NUMBER,
80 L.LANGUAGE_CODE,
81 userenv('LANG')
82 from FND_LANGUAGES L
83 where L.INSTALLED_FLAG in ('I', 'B')
84 and not exists
85 (select NULL
86 from AMW_OPINION_VALUES_TL T
87 where T.OPINION_VALUE_ID = X_OPINION_VALUE_ID
88 and T.LANGUAGE = L.LANGUAGE_CODE);
89
90 open c;
91 fetch c into X_ROWID;
92 if (c%notfound) then
93 close c;
94 raise no_data_found;
95 end if;
96 close c;
97
98 end INSERT_ROW;
99
100 procedure LOCK_ROW (
101 X_OPINION_VALUE_ID in NUMBER,
102 X_OPINION_VALUE_CODE in VARCHAR2,
103 X_OPINION_COMPONENT_ID in NUMBER,
104 X_END_DATE in DATE,
105 X_ATTACHMENT_ID in NUMBER,
106 X_IMAGE_FILE_NAME in VARCHAR2,
107 X_DISPLAY_ORDER in NUMBER,
108 X_SECURITY_GROUP_ID in NUMBER,
109 X_OBJECT_VERSION_NUMBER in NUMBER,
110 X_OPINION_VALUE_NAME in VARCHAR2
111 ) is
112 cursor c is select
113 OPINION_VALUE_CODE,
114 OPINION_COMPONENT_ID,
115 END_DATE,
116 ATTACHMENT_ID,
117 IMAGE_FILE_NAME,
118 DISPLAY_ORDER,
119 SECURITY_GROUP_ID,
120 OBJECT_VERSION_NUMBER
121 from AMW_OPINION_VALUES_B
122 where OPINION_VALUE_ID = X_OPINION_VALUE_ID
123 for update of OPINION_VALUE_ID nowait;
124 recinfo c%rowtype;
125
126 cursor c1 is select
127 OPINION_VALUE_NAME,
128 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
129 from AMW_OPINION_VALUES_TL
130 where OPINION_VALUE_ID = X_OPINION_VALUE_ID
131 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
132 for update of OPINION_VALUE_ID nowait;
133 begin
134 open c;
135 fetch c into recinfo;
136 if (c%notfound) then
137 close c;
138 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
139 app_exception.raise_exception;
140 end if;
141 close c;
142 if ( (recinfo.OPINION_VALUE_CODE = X_OPINION_VALUE_CODE)
143 AND (recinfo.OPINION_COMPONENT_ID = X_OPINION_COMPONENT_ID)
144 AND ((recinfo.END_DATE = X_END_DATE)
145 OR ((recinfo.END_DATE is null) AND (X_END_DATE is null)))
146 AND ((recinfo.ATTACHMENT_ID = X_ATTACHMENT_ID)
147 OR ((recinfo.ATTACHMENT_ID is null) AND (X_ATTACHMENT_ID is null)))
148 AND ((recinfo.IMAGE_FILE_NAME = X_IMAGE_FILE_NAME)
149 OR ((recinfo.IMAGE_FILE_NAME is null) AND (X_IMAGE_FILE_NAME is null)))
150 AND ((recinfo.DISPLAY_ORDER = X_DISPLAY_ORDER)
151 OR ((recinfo.DISPLAY_ORDER is null) AND (X_DISPLAY_ORDER is null)))
152 AND ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
153 OR ((recinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
154 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
155 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
156 ) then
157 null;
158 else
159 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
160 app_exception.raise_exception;
161 end if;
162
163 for tlinfo in c1 loop
164 if (tlinfo.BASELANG = 'Y') then
165 if ( ((tlinfo.OPINION_VALUE_NAME = X_OPINION_VALUE_NAME)
166 OR ((tlinfo.OPINION_VALUE_NAME is null) AND (X_OPINION_VALUE_NAME is null)))
167 ) then
168 null;
169 else
170 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
171 app_exception.raise_exception;
172 end if;
173 end if;
174 end loop;
175 return;
176 end LOCK_ROW;
177
178 procedure UPDATE_ROW (
179 X_OPINION_VALUE_ID in NUMBER,
180 X_OPINION_VALUE_CODE in VARCHAR2,
181 X_OPINION_COMPONENT_ID in NUMBER,
182 X_END_DATE in DATE,
183 X_ATTACHMENT_ID in NUMBER,
184 X_IMAGE_FILE_NAME in VARCHAR2,
185 X_DISPLAY_ORDER in NUMBER,
186 X_SECURITY_GROUP_ID in NUMBER,
187 X_OBJECT_VERSION_NUMBER in NUMBER,
188 X_OPINION_VALUE_NAME in VARCHAR2,
189 X_LAST_UPDATE_DATE in DATE,
190 X_LAST_UPDATED_BY in NUMBER,
191 X_LAST_UPDATE_LOGIN in NUMBER
192 ) is
193 begin
194 update AMW_OPINION_VALUES_B set
195 OPINION_VALUE_CODE = X_OPINION_VALUE_CODE,
196 OPINION_COMPONENT_ID = X_OPINION_COMPONENT_ID,
197 END_DATE = X_END_DATE,
198 ATTACHMENT_ID = X_ATTACHMENT_ID,
199 IMAGE_FILE_NAME = X_IMAGE_FILE_NAME,
200 DISPLAY_ORDER = X_DISPLAY_ORDER,
201 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
202 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
203 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
204 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
205 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
206 where OPINION_VALUE_ID = X_OPINION_VALUE_ID;
207
208 if (sql%notfound) then
209 raise no_data_found;
210 end if;
211
212 update AMW_OPINION_VALUES_TL set
213 OPINION_VALUE_NAME = X_OPINION_VALUE_NAME,
214 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
215 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
216 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
217 SOURCE_LANG = userenv('LANG')
218 where OPINION_VALUE_ID = X_OPINION_VALUE_ID
219 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
220
221 if (sql%notfound) then
222 raise no_data_found;
223 end if;
224 end UPDATE_ROW;
225
226 procedure DELETE_ROW (
227 X_OPINION_VALUE_ID in NUMBER
228 ) is
229 begin
230 delete from AMW_OPINION_VALUES_TL
231 where OPINION_VALUE_ID = X_OPINION_VALUE_ID;
232
233 if (sql%notfound) then
234 raise no_data_found;
235 end if;
236
237 delete from AMW_OPINION_VALUES_B
238 where OPINION_VALUE_ID = X_OPINION_VALUE_ID;
239
240 if (sql%notfound) then
241 raise no_data_found;
242 end if;
243 end DELETE_ROW;
244
245 procedure ADD_LANGUAGE
246 is
247 begin
248 delete from AMW_OPINION_VALUES_TL T
249 where not exists
250 (select NULL
251 from AMW_OPINION_VALUES_B B
252 where B.OPINION_VALUE_ID = T.OPINION_VALUE_ID
253 );
254
255 update AMW_OPINION_VALUES_TL T set (
256 OPINION_VALUE_NAME
257 ) = (select
258 B.OPINION_VALUE_NAME
259 from AMW_OPINION_VALUES_TL B
260 where B.OPINION_VALUE_ID = T.OPINION_VALUE_ID
261 and B.LANGUAGE = T.SOURCE_LANG)
262 where (
263 T.OPINION_VALUE_ID,
264 T.LANGUAGE
265 ) in (select
266 SUBT.OPINION_VALUE_ID,
267 SUBT.LANGUAGE
268 from AMW_OPINION_VALUES_TL SUBB, AMW_OPINION_VALUES_TL SUBT
269 where SUBB.OPINION_VALUE_ID = SUBT.OPINION_VALUE_ID
270 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
271 and (SUBB.OPINION_VALUE_NAME <> SUBT.OPINION_VALUE_NAME
272 or (SUBB.OPINION_VALUE_NAME is null and SUBT.OPINION_VALUE_NAME is not null)
273 or (SUBB.OPINION_VALUE_NAME is not null and SUBT.OPINION_VALUE_NAME is null)
274 ));
275
276 insert into AMW_OPINION_VALUES_TL (
277 OPINION_VALUE_ID,
278 OPINION_VALUE_NAME,
279 CREATED_BY,
280 CREATION_DATE,
281 LAST_UPDATED_BY,
282 LAST_UPDATE_DATE,
283 LAST_UPDATE_LOGIN,
284 SECURITY_GROUP_ID,
285 OBJECT_VERSION_NUMBER,
286 LANGUAGE,
287 SOURCE_LANG
288 ) select
289 B.OPINION_VALUE_ID,
290 B.OPINION_VALUE_NAME,
291 B.CREATED_BY,
292 B.CREATION_DATE,
293 B.LAST_UPDATED_BY,
294 B.LAST_UPDATE_DATE,
295 B.LAST_UPDATE_LOGIN,
296 B.SECURITY_GROUP_ID,
297 B.OBJECT_VERSION_NUMBER,
298 L.LANGUAGE_CODE,
299 B.SOURCE_LANG
300 from AMW_OPINION_VALUES_TL B, FND_LANGUAGES L
301 where L.INSTALLED_FLAG in ('I', 'B')
302 and B.LANGUAGE = userenv('LANG')
303 and not exists
304 (select NULL
305 from AMW_OPINION_VALUES_TL T
306 where T.OPINION_VALUE_ID = B.OPINION_VALUE_ID
307 and T.LANGUAGE = L.LANGUAGE_CODE);
308 end ADD_LANGUAGE;
309
313 X_OPINION_VALUE_CODE in VARCHAR2,
310 procedure LOAD_ROW(
311 X_OPINION_VALUE_ID in NUMBER,
312 X_OPINION_VALUE_NAME in VARCHAR2,
314 X_OPINION_COMPONENT_ID in NUMBER,
315 X_END_DATE in VARCHAR2,
316 X_ATTACHMENT_ID in NUMBER,
317 X_IMAGE_FILE_NAME in VARCHAR2,
318 X_DISPLAY_ORDER in NUMBER,
319 X_LAST_UPDATE_DATE in VARCHAR2,
320 X_OWNER in VARCHAR2,
321 X_CUSTOM_MODE in VARCHAR2) is
322
323 f_luby number; -- entity owner in file
324 f_ludate date; -- entity update date in file
325 db_luby number; -- entity owner in db
326 db_ludate date; -- entity update date in db
327
328 row_id rowid;
329
330 begin
331 -- Translate owner to file_last_updated_by
332 f_luby := fnd_load_util.owner_id(X_OWNER);
333
334 -- Translate char last_update_date to date
335 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
336
337 select last_updated_by, last_update_date into db_luby, db_ludate
338 from AMW_OPINION_VALUES_B
339 where opinion_value_id = X_OPINION_VALUE_ID;
340
341 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate, X_CUSTOM_MODE))
342 then AMW_OPINION_VALUES_PKG.UPDATE_ROW(
343 X_OPINION_VALUE_ID => X_OPINION_VALUE_ID,
344 X_OPINION_VALUE_CODE => X_OPINION_VALUE_CODE,
345 X_OPINION_COMPONENT_ID => X_OPINION_COMPONENT_ID,
346 X_END_DATE => to_date(X_END_DATE, 'YYYY/MM/DD'),
347 X_ATTACHMENT_ID => X_ATTACHMENT_ID,
348 X_IMAGE_FILE_NAME => X_IMAGE_FILE_NAME,
349 X_DISPLAY_ORDER => X_DISPLAY_ORDER,
350 X_SECURITY_GROUP_ID => null,
351 X_OBJECT_VERSION_NUMBER => 1,
352 X_OPINION_VALUE_NAME => X_OPINION_VALUE_NAME,
353 X_LAST_UPDATE_DATE => f_ludate,
354 X_LAST_UPDATED_BY => f_luby,
355 X_LAST_UPDATE_LOGIN => 0);
356 end if;
357 exception when NO_DATA_FOUND
358 then AMW_OPINION_VALUES_PKG.INSERT_ROW(
359 X_ROWID => row_id,
360 X_OPINION_VALUE_ID => X_OPINION_VALUE_ID,
361 X_OPINION_VALUE_CODE => X_OPINION_VALUE_CODE,
362 X_OPINION_COMPONENT_ID => X_OPINION_COMPONENT_ID,
363 X_END_DATE => to_date(X_END_DATE, 'YYYY/MM/DD'),
364 X_ATTACHMENT_ID => X_ATTACHMENT_ID,
365 X_IMAGE_FILE_NAME => X_IMAGE_FILE_NAME,
366 X_DISPLAY_ORDER => X_DISPLAY_ORDER,
367 X_SECURITY_GROUP_ID => null,
368 X_OBJECT_VERSION_NUMBER => 1,
369 X_OPINION_VALUE_NAME => X_OPINION_VALUE_NAME,
370 X_CREATION_DATE => f_ludate,
371 X_CREATED_BY => f_luby,
372 X_LAST_UPDATE_DATE => f_ludate,
373 X_LAST_UPDATED_BY => f_luby,
374 X_LAST_UPDATE_LOGIN => 0);
375 end LOAD_ROW;
376
377 procedure TRANSLATE_ROW(
378 X_OPINION_VALUE_ID in NUMBER,
379 X_OPINION_VALUE_NAME in VARCHAR2,
380 X_LAST_UPDATE_DATE in VARCHAR2,
381 X_OWNER in VARCHAR2,
382 X_CUSTOM_MODE in VARCHAR2) is
383
384 f_luby number; -- entity owner in file
385 f_ludate date; -- entity update date in file
386 db_luby number; -- entity owner in db
387 db_ludate date; -- entity update date in db
388
389 begin
390 -- Translate owner to file_last_updated_by
391 f_luby := fnd_load_util.owner_id(X_OWNER);
392
393 -- Translate char last_update_date to date
394 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
395
396 select last_updated_by, last_update_date into db_luby, db_ludate
397 from AMW_OPINION_VALUES_TL
398 where opinion_value_id = X_OPINION_VALUE_ID and language = userenv('LANG');
399
400 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate, X_CUSTOM_MODE))
401 then update AMW_OPINION_VALUES_TL set
402 opinion_value_name = nvl(X_OPINION_VALUE_NAME, opinion_value_name),
403 source_lang = userenv('LANG'),
404 last_update_date = f_ludate,
405 last_updated_by = f_luby,
406 last_update_login = 0
407 where opinion_value_id = X_OPINION_VALUE_ID and userenv('LANG') in (language, source_lang);
408 end if;
409 end TRANSLATE_ROW;
410
411 end AMW_OPINION_VALUES_PKG;