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