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