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