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