[Home] [Help]
PACKAGE BODY: APPS.HR_ORG_INFORMATION_TYPES_PKG
Source
1 package body HR_ORG_INFORMATION_TYPES_PKG as
2 /* $Header: peoit01t.pkb 120.3 2006/05/25 08:14:28 srenukun noship $ */
3 --
4 function APPLICATION_ID (
5 X_APPLICATION_SHORT_NAME in VARCHAR2
6 ) return NUMBER is
7 cursor CSR_APPLICATION (
8 X_APPLICATION_SHORT_NAME in VARCHAR2
9 ) is
10 select APPLICATION_ID
11 from FND_APPLICATION
12 where APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME;
13 X_APPLICATION CSR_APPLICATION%rowtype;
14 begin
15 open CSR_APPLICATION(X_APPLICATION_SHORT_NAME);
16 fetch CSR_APPLICATION into X_APPLICATION;
17 close CSR_APPLICATION;
18 return(X_APPLICATION.APPLICATION_ID);
19 end APPLICATION_ID;
20 --
21 procedure OWNER_TO_WHO (
22 X_OWNER in VARCHAR2,
23 X_CREATION_DATE out nocopy DATE,
24 X_CREATED_BY out nocopy NUMBER,
25 X_LAST_UPDATE_DATE out nocopy DATE,
26 X_LAST_UPDATED_BY out nocopy NUMBER,
27 X_LAST_UPDATE_LOGIN out nocopy NUMBER
28 ) is
29 begin
30 if X_OWNER = 'SEED' then
31 X_CREATED_BY := 1;
32 X_LAST_UPDATED_BY := 1;
33 else
34 X_CREATED_BY := 0;
35 X_LAST_UPDATED_BY := 0;
36 end if;
37 X_CREATION_DATE := sysdate;
38 X_LAST_UPDATE_DATE := sysdate;
39 X_LAST_UPDATE_LOGIN := 0;
40 end OWNER_TO_WHO;
41 --
42 procedure INSERT_ROW (
43 X_ORG_INFORMATION_TYPE in VARCHAR2,
44 X_DESTINATION in VARCHAR2,
45 X_LEGISLATION_CODE in VARCHAR2,
46 X_NAVIGATION_METHOD in VARCHAR2,
47 X_FND_APPLICATION_ID in NUMBER,
48 X_DESCRIPTION in VARCHAR2,
49 X_DISPLAYED_ORG_INFORMATION_TP in VARCHAR2,
50 X_CREATION_DATE in DATE,
51 X_CREATED_BY in NUMBER,
52 X_LAST_UPDATE_DATE in DATE,
53 X_LAST_UPDATED_BY in NUMBER,
54 X_LAST_UPDATE_LOGIN in NUMBER
55 ) is
56 begin
57 insert into HR_ORG_INFORMATION_TYPES (
58 ORG_INFORMATION_TYPE,
59 DESTINATION,
60 LEGISLATION_CODE,
61 NAVIGATION_METHOD,
62 FND_APPLICATION_ID,
63 DESCRIPTION,
64 DISPLAYED_ORG_INFORMATION_TYPE,
65 CREATION_DATE,
66 CREATED_BY,
67 LAST_UPDATE_DATE,
68 LAST_UPDATED_BY,
69 LAST_UPDATE_LOGIN
70 ) values (
71 X_ORG_INFORMATION_TYPE,
72 X_DESTINATION,
73 X_LEGISLATION_CODE,
74 X_NAVIGATION_METHOD,
75 X_FND_APPLICATION_ID,
76 X_DESCRIPTION,
77 X_DISPLAYED_ORG_INFORMATION_TP,
78 X_CREATION_DATE,
79 X_CREATED_BY,
80 X_LAST_UPDATE_DATE,
81 X_LAST_UPDATED_BY,
82 X_LAST_UPDATE_LOGIN
83 );
84
85 insert into HR_ORG_INFORMATION_TYPES_TL (
86 ORG_INFORMATION_TYPE,
87 DISPLAYED_ORG_INFORMATION_TYPE,
88 CREATED_BY,
89 CREATION_DATE,
90 LAST_UPDATED_BY,
91 LAST_UPDATE_DATE,
92 LAST_UPDATE_LOGIN,
93 LANGUAGE,
94 SOURCE_LANG
95 ) select
96 X_ORG_INFORMATION_TYPE,
97 X_DISPLAYED_ORG_INFORMATION_TP,
98 X_CREATED_BY,
99 X_CREATION_DATE,
100 X_LAST_UPDATED_BY,
101 X_LAST_UPDATE_DATE,
102 X_LAST_UPDATE_LOGIN,
103 L.LANGUAGE_CODE,
104 userenv('LANG')
105 from FND_LANGUAGES L
106 where L.INSTALLED_FLAG in ('I', 'B')
107 and not exists
108 (select NULL
109 from HR_ORG_INFORMATION_TYPES_TL T
110 where T.ORG_INFORMATION_TYPE = X_ORG_INFORMATION_TYPE
111 and T.LANGUAGE = L.LANGUAGE_CODE);
112
113 end INSERT_ROW;
114 --
115 procedure LOCK_ROW (
116 X_ORG_INFORMATION_TYPE in VARCHAR2,
117 X_DESTINATION in VARCHAR2,
118 X_LEGISLATION_CODE in VARCHAR2,
119 X_NAVIGATION_METHOD in VARCHAR2,
120 X_FND_APPLICATION_ID in NUMBER,
121 X_DESCRIPTION in VARCHAR2,
122 X_DISPLAYED_ORG_INFORMATION_TP in VARCHAR2
123 ) is
124 cursor CSR_ORG_INFORMATION_TYPE (
125 X_ORG_INFORMATION_TYPE in VARCHAR2
126 ) is
127 select DESTINATION
128 ,LEGISLATION_CODE
129 ,NAVIGATION_METHOD
130 ,FND_APPLICATION_ID
131 ,DESCRIPTION
132 from HR_ORG_INFORMATION_TYPES
133 where ORG_INFORMATION_TYPE = X_ORG_INFORMATION_TYPE
134 for update of ORG_INFORMATION_TYPE nowait;
135 RECINFO CSR_ORG_INFORMATION_TYPE%rowtype;
136
137 cursor CSR_ORG_INFORMATION_TYPE_TL is
138 select DISPLAYED_ORG_INFORMATION_TYPE,
139 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
140 from HR_ORG_INFORMATION_TYPES_TL
141 where ORG_INFORMATION_TYPE = X_ORG_INFORMATION_TYPE
142 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
143 for update of ORG_INFORMATION_TYPE nowait;
144
145 begin
146 open CSR_ORG_INFORMATION_TYPE(X_ORG_INFORMATION_TYPE);
147 fetch CSR_ORG_INFORMATION_TYPE into RECINFO;
148 if (CSR_ORG_INFORMATION_TYPE%notfound) then
149 close CSR_ORG_INFORMATION_TYPE;
150 fnd_message.set_name('FND','FORM_RECORD_DELETED');
151 app_exception.raise_exception;
152 end if;
153 close CSR_ORG_INFORMATION_TYPE;
154 if ( ( (RECINFO.DESTINATION = X_DESTINATION)
155 or (RECINFO.DESTINATION is null and X_DESTINATION is null))
156 and ( (RECINFO.LEGISLATION_CODE = X_LEGISLATION_CODE)
157 or (RECINFO.LEGISLATION_CODE is null and X_LEGISLATION_CODE is null))
158 and ( (RECINFO.NAVIGATION_METHOD = X_NAVIGATION_METHOD)
159 or (RECINFO.NAVIGATION_METHOD is null and X_NAVIGATION_METHOD is null))
160 and ( (RECINFO.NAVIGATION_METHOD = X_NAVIGATION_METHOD)
161 or (RECINFO.NAVIGATION_METHOD is null and X_NAVIGATION_METHOD is null))
162 and ( (RECINFO.DESCRIPTION = X_DESCRIPTION)
163 or (RECINFO.DESCRIPTION is null and X_DESCRIPTION is null))
164 ) then
165 null;
166 else
167 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
168 app_exception.raise_exception;
169 end if;
170
171 for tlinfo in CSR_ORG_INFORMATION_TYPE_TL loop
172 if (tlinfo.BASELANG = 'Y') then
173 if ( ((tlinfo.DISPLAYED_ORG_INFORMATION_TYPE = X_DISPLAYED_ORG_INFORMATION_TP)
174 OR ((tlinfo.DISPLAYED_ORG_INFORMATION_TYPE is null) AND (X_DISPLAYED_ORG_INFORMATION_TP is null)))
175 ) then
176 null;
177 else
178 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
179 app_exception.raise_exception;
180 end if;
181 end if;
182 end loop;
183 return;
184
185 end LOCK_ROW;
186 --
187 procedure UPDATE_ROW (
188 X_ORG_INFORMATION_TYPE in VARCHAR2,
189 X_DESTINATION in VARCHAR2,
190 X_LEGISLATION_CODE in VARCHAR2,
191 X_NAVIGATION_METHOD in VARCHAR2,
192 X_FND_APPLICATION_ID in NUMBER,
193 X_DESCRIPTION in VARCHAR2,
194 X_DISPLAYED_ORG_INFORMATION_TP in VARCHAR2,
195 X_LAST_UPDATE_DATE in DATE,
196 X_LAST_UPDATED_BY in NUMBER,
197 X_LAST_UPDATE_LOGIN in NUMBER
198 ) is
199 begin
200 update HR_ORG_INFORMATION_TYPES set
201 DESTINATION = X_DESTINATION,
202 LEGISLATION_CODE = X_LEGISLATION_CODE,
203 NAVIGATION_METHOD = X_NAVIGATION_METHOD,
204 FND_APPLICATION_ID = X_FND_APPLICATION_ID,
205 DESCRIPTION = X_DESCRIPTION,
206 DISPLAYED_ORG_INFORMATION_TYPE = X_DISPLAYED_ORG_INFORMATION_TP,
207 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
208 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
209 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
210 where ORG_INFORMATION_TYPE = X_ORG_INFORMATION_TYPE;
211 if (sql%notfound) then
212 raise no_data_found;
213 end if;
214
215 update HR_ORG_INFORMATION_TYPES_TL set
216 DISPLAYED_ORG_INFORMATION_TYPE = X_DISPLAYED_ORG_INFORMATION_TP,
217 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
218 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
219 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
220 SOURCE_LANG = userenv('LANG')
221 where ORG_INFORMATION_TYPE = X_ORG_INFORMATION_TYPE
222 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
223
224 if (sql%notfound) then
225 raise no_data_found;
226 end if;
227
228 end UPDATE_ROW;
229 --
230 procedure DELETE_ROW (
231 X_ORG_INFORMATION_TYPE in VARCHAR2
232 ) is
233 begin
234 delete from HR_ORG_INFORMATION_TYPES_TL
235 where ORG_INFORMATION_TYPE = X_ORG_INFORMATION_TYPE;
236
237 if (sql%notfound) then
238 raise no_data_found;
239 end if;
240
241 delete from HR_ORG_INFORMATION_TYPES
242 where ORG_INFORMATION_TYPE = X_ORG_INFORMATION_TYPE;
243 if (sql%notfound) then
244 raise no_data_found;
245 end if;
246 end DELETE_ROW;
247 --
248 procedure ADD_LANGUAGE
249 is
250 begin
251 delete from HR_ORG_INFORMATION_TYPES_TL T
252 where not exists
253 (select NULL
254 from HR_ORG_INFORMATION_TYPES B
255 where B.ORG_INFORMATION_TYPE = T.ORG_INFORMATION_TYPE
256 );
257
258 update HR_ORG_INFORMATION_TYPES_TL T set (
259 DISPLAYED_ORG_INFORMATION_TYPE
260 ) = (select
261 B.DISPLAYED_ORG_INFORMATION_TYPE
262 from HR_ORG_INFORMATION_TYPES_TL B
263 where B.ORG_INFORMATION_TYPE = T.ORG_INFORMATION_TYPE
264 and B.LANGUAGE = T.SOURCE_LANG)
265 where (
266 T.ORG_INFORMATION_TYPE,
267 T.LANGUAGE
268 ) in (select
269 SUBT.ORG_INFORMATION_TYPE,
270 SUBT.LANGUAGE
271 from HR_ORG_INFORMATION_TYPES_TL SUBB, HR_ORG_INFORMATION_TYPES_TL SUBT
272 where SUBB.ORG_INFORMATION_TYPE = SUBT.ORG_INFORMATION_TYPE
273 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
274 and (SUBB.DISPLAYED_ORG_INFORMATION_TYPE <> SUBT.DISPLAYED_ORG_INFORMATION_TYPE
275 or (SUBB.DISPLAYED_ORG_INFORMATION_TYPE is null and SUBT.DISPLAYED_ORG_INFORMATION_TYPE is not null)
276 or (SUBB.DISPLAYED_ORG_INFORMATION_TYPE is not null and SUBT.DISPLAYED_ORG_INFORMATION_TYPE is null)
277 ));
278
279 insert into HR_ORG_INFORMATION_TYPES_TL (
280 ORG_INFORMATION_TYPE,
281 DISPLAYED_ORG_INFORMATION_TYPE,
282 CREATED_BY,
283 CREATION_DATE,
284 LAST_UPDATED_BY,
285 LAST_UPDATE_DATE,
286 LAST_UPDATE_LOGIN,
287 LANGUAGE,
288 SOURCE_LANG
289 ) select
290 B.ORG_INFORMATION_TYPE,
291 B.DISPLAYED_ORG_INFORMATION_TYPE,
292 B.CREATED_BY,
293 B.CREATION_DATE,
294 B.LAST_UPDATED_BY,
295 B.LAST_UPDATE_DATE,
296 B.LAST_UPDATE_LOGIN,
297 L.LANGUAGE_CODE,
298 B.SOURCE_LANG
299 from HR_ORG_INFORMATION_TYPES_TL B, FND_LANGUAGES L
300 where L.INSTALLED_FLAG in ('I', 'B')
301 and B.LANGUAGE = userenv('LANG')
302 and not exists
303 (select NULL
304 from HR_ORG_INFORMATION_TYPES_TL T
305 where T.ORG_INFORMATION_TYPE = B.ORG_INFORMATION_TYPE
306 and T.LANGUAGE = L.LANGUAGE_CODE);
307 end ADD_LANGUAGE;
308
309 --
310 procedure LOAD_ROW (
311 X_ORG_INFORMATION_TYPE in VARCHAR2,
312 X_DESTINATION in VARCHAR2,
313 X_LEGISLATION_CODE in VARCHAR2,
314 X_NAVIGATION_METHOD in VARCHAR2,
315 X_APPLICATION_SHORT_NAME in VARCHAR2,
316 X_DESCRIPTION in VARCHAR2,
317 X_DISPLAYED_ORG_INFORMATION_TP in VARCHAR2,
318 X_OWNER in VARCHAR2,
319 X_LAST_UPDATE_DATE IN varchar2 default sysdate,
320 X_CUSTOM_MODE IN VARCHAR2 default null
321 ) is
322 X_CREATION_DATE DATE;
323 X_CREATED_BY NUMBER;
324 X_LAST_UPDATED_BY NUMBER;
325 X_LAST_UPDATE_LOGIN NUMBER;
326 X_APPLICATION_ID NUMBER;
327 f_luby number; -- entity owner in file
328 f_ludate date; -- entity update date in file
329 db_luby number; -- entity owner in db
330 db_ludate date; -- entity update date in db
331 begin
332 --This has been commented as LAST_UPDATE_DATE is passed as an parameter
333 /*OWNER_TO_WHO (
334 X_OWNER,
335 X_CREATION_DATE,
336 X_CREATED_BY,
337 X_LAST_UPDATE_DATE,
338 X_LAST_UPDATED_BY,
339 X_LAST_UPDATE_LOGIN
340 );*/
341
342 X_APPLICATION_ID := APPLICATION_ID(X_APPLICATION_SHORT_NAME);
343 -- Translate owner to file_last_updated_by
344 f_luby := fnd_load_util.owner_id(X_OWNER);
345 -- Translate char last_update_date to date
346 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
347 select LAST_UPDATED_BY, LAST_UPDATE_DATE
348 into db_luby, db_ludate
349 from HR_ORG_INFORMATION_TYPES
350 where ORG_INFORMATION_TYPE = X_ORG_INFORMATION_TYPE;
351
352 -- Test for customization and version
353 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
354 db_ludate, X_CUSTOM_MODE)) then
355 -- Update existing row
356 HR_ORG_INFORMATION_TYPES_PKG.Update_Row(
357 X_ORG_INFORMATION_TYPE,
358 X_DESTINATION,
359 X_LEGISLATION_CODE,
360 X_NAVIGATION_METHOD,
361 X_APPLICATION_ID,
362 X_DESCRIPTION,
363 X_DISPLAYED_ORG_INFORMATION_TP,
364 f_ludate,
365 f_luby,
366 0);
367 END IF;
368 exception
369 when no_data_found then
370 -- Record doesn't exist - insert in all cases
371 HR_ORG_INFORMATION_TYPES_PKG.Insert_Row(
372 X_ORG_INFORMATION_TYPE,
373 X_DESTINATION,
374 X_LEGISLATION_CODE,
375 X_NAVIGATION_METHOD,
376 X_APPLICATION_ID,
377 X_DESCRIPTION,
378 X_DISPLAYED_ORG_INFORMATION_TP,
379 f_ludate,
380 f_luby,
381 f_ludate,
382 f_luby,
383 0);
384
385 end LOAD_ROW;
386 --
387 procedure TRANSLATE_ROW (
388 X_ORG_INFORMATION_TYPE in VARCHAR2,
389 X_DESCRIPTION in VARCHAR2,
390 X_DISPLAYED_ORG_INFORMATION_TP in VARCHAR2,
391 X_OWNER in VARCHAR2,
392 X_LAST_UPDATE_DATE in VARCHAR2 default sysdate,
393 X_CUSTOM_MODE IN VARCHAR2 default null
394 ) is
395 X_CREATION_DATE DATE;
396 X_CREATED_BY NUMBER;
397 X_LAST_UPDATED_BY NUMBER;
398 X_LAST_UPDATE_LOGIN NUMBER;
399 f_luby number; -- entity owner in file
400 f_ludate date; -- entity update date in file
401 db_luby number; -- entity owner in db
402 db_ludate date; -- entity update date in db
403 begin
404 --This has been commented as LAST_UPDATE_DATE is passed as an parameter
405 /*OWNER_TO_WHO (
406 X_OWNER,
407 X_CREATION_DATE,
408 X_CREATED_BY,
409 X_LAST_UPDATE_DATE,
410 X_LAST_UPDATED_BY,
411 X_LAST_UPDATE_LOGIN
412 );*/
413 -- Translate owner to file_last_updated_by
414 f_luby := fnd_load_util.owner_id(X_OWNER);
415 -- Translate char last_update_date to date
416 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
417
418 -- added the check as per Bug 5092005 to make sure that only
419 -- the correct row is fetched instead of multiple rows
420
421 select LAST_UPDATED_BY, LAST_UPDATE_DATE
422 into db_luby, db_ludate
423 from HR_ORG_INFORMATION_TYPES_TL
424 where ORG_INFORMATION_TYPE = X_ORG_INFORMATION_TYPE
425 and LANGUAGE=userenv('LANG');
426
427 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
428 db_ludate,X_CUSTOM_MODE)) then
429 -- Update translations for this language
430 -- bug 5235538 nls date issue, changed to LAST_UPDATE_DATE = f_ludate, LAST_UPDATED_BY = f_luby
431
432 update HR_ORG_INFORMATION_TYPES_TL
433 set DISPLAYED_ORG_INFORMATION_TYPE = X_DISPLAYED_ORG_INFORMATION_TP
434 , LAST_UPDATE_DATE = f_ludate
435 , LAST_UPDATED_BY = f_luby
436 , LAST_UPDATE_LOGIN = 0
437 , SOURCE_LANG = userenv('LANG')
438 where ORG_INFORMATION_TYPE = X_ORG_INFORMATION_TYPE
439 and userenv('LANG') in (LANGUAGE,SOURCE_LANG);
440 end if;
441 exception
442 when no_data_found then
443 -- Do not insert missing translations, skip this row
444 null;
445
446 end TRANSLATE_ROW;
447 --
448 end HR_ORG_INFORMATION_TYPES_PKG;