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