[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.4 2011/04/28 10:12:15 sidsaxen ship $ */
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 --
86 -- Added the following code as a part of Zero Downtime Patching Project.
87 -- Code Starts Here.
88 --
89
90 BEGIN
91 PER_RIC_PKG.chk_integrity (
92 p_entity_name => 'HR_ORG_INFORMATION_TYPES_TL',
93 p_ref_entity => 'HR_ORG_INFORMATION_TYPES',
94 p_ref_column_name => 'ORG_INFORMATION_TYPE',
95 p_ref_col_value_number => NULL,
96 p_ref_col_value_varchar => X_ORG_INFORMATION_TYPE,
97 p_ref_col_value_date => NULL,
98 p_ref_type => 'INS');
99
100 END;
101 --
102 -- Code Ends Here.
103 --
104 insert into HR_ORG_INFORMATION_TYPES_TL (
105 ORG_INFORMATION_TYPE,
106 DISPLAYED_ORG_INFORMATION_TYPE,
107 CREATED_BY,
108 CREATION_DATE,
109 LAST_UPDATED_BY,
110 LAST_UPDATE_DATE,
111 LAST_UPDATE_LOGIN,
112 LANGUAGE,
113 SOURCE_LANG
114 ) select
115 X_ORG_INFORMATION_TYPE,
116 X_DISPLAYED_ORG_INFORMATION_TP,
117 X_CREATED_BY,
118 X_CREATION_DATE,
119 X_LAST_UPDATED_BY,
120 X_LAST_UPDATE_DATE,
121 X_LAST_UPDATE_LOGIN,
122 L.LANGUAGE_CODE,
123 userenv('LANG')
124 from FND_LANGUAGES L
125 where L.INSTALLED_FLAG in ('I', 'B')
126 and not exists
127 (select NULL
128 from HR_ORG_INFORMATION_TYPES_TL T
129 where T.ORG_INFORMATION_TYPE = X_ORG_INFORMATION_TYPE
130 and T.LANGUAGE = L.LANGUAGE_CODE);
131
132 end INSERT_ROW;
133 --
134 procedure LOCK_ROW (
135 X_ORG_INFORMATION_TYPE in VARCHAR2,
136 X_DESTINATION in VARCHAR2,
137 X_LEGISLATION_CODE in VARCHAR2,
138 X_NAVIGATION_METHOD in VARCHAR2,
139 X_FND_APPLICATION_ID in NUMBER,
140 X_DESCRIPTION in VARCHAR2,
141 X_DISPLAYED_ORG_INFORMATION_TP in VARCHAR2
142 ) is
143 cursor CSR_ORG_INFORMATION_TYPE (
144 X_ORG_INFORMATION_TYPE in VARCHAR2
145 ) is
146 select DESTINATION
147 ,LEGISLATION_CODE
148 ,NAVIGATION_METHOD
149 ,FND_APPLICATION_ID
150 ,DESCRIPTION
151 from HR_ORG_INFORMATION_TYPES
152 where ORG_INFORMATION_TYPE = X_ORG_INFORMATION_TYPE
153 for update of ORG_INFORMATION_TYPE nowait;
154 RECINFO CSR_ORG_INFORMATION_TYPE%rowtype;
155
156 cursor CSR_ORG_INFORMATION_TYPE_TL is
157 select DISPLAYED_ORG_INFORMATION_TYPE,
158 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
159 from HR_ORG_INFORMATION_TYPES_TL
160 where ORG_INFORMATION_TYPE = X_ORG_INFORMATION_TYPE
161 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
162 for update of ORG_INFORMATION_TYPE nowait;
163
164 begin
165 open CSR_ORG_INFORMATION_TYPE(X_ORG_INFORMATION_TYPE);
166 fetch CSR_ORG_INFORMATION_TYPE into RECINFO;
167 if (CSR_ORG_INFORMATION_TYPE%notfound) then
168 close CSR_ORG_INFORMATION_TYPE;
169 fnd_message.set_name('FND','FORM_RECORD_DELETED');
170 app_exception.raise_exception;
171 end if;
172 close CSR_ORG_INFORMATION_TYPE;
173 if ( ( (RECINFO.DESTINATION = X_DESTINATION)
174 or (RECINFO.DESTINATION is null and X_DESTINATION is null))
175 and ( (RECINFO.LEGISLATION_CODE = X_LEGISLATION_CODE)
176 or (RECINFO.LEGISLATION_CODE is null and X_LEGISLATION_CODE is null))
177 and ( (RECINFO.NAVIGATION_METHOD = X_NAVIGATION_METHOD)
178 or (RECINFO.NAVIGATION_METHOD is null and X_NAVIGATION_METHOD is null))
179 and ( (RECINFO.NAVIGATION_METHOD = X_NAVIGATION_METHOD)
180 or (RECINFO.NAVIGATION_METHOD is null and X_NAVIGATION_METHOD is null))
181 and ( (RECINFO.DESCRIPTION = X_DESCRIPTION)
182 or (RECINFO.DESCRIPTION is null and X_DESCRIPTION is null))
183 ) then
184 null;
185 else
186 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
187 app_exception.raise_exception;
188 end if;
189
190 for tlinfo in CSR_ORG_INFORMATION_TYPE_TL loop
191 if (tlinfo.BASELANG = 'Y') then
192 if ( ((tlinfo.DISPLAYED_ORG_INFORMATION_TYPE = X_DISPLAYED_ORG_INFORMATION_TP)
193 OR ((tlinfo.DISPLAYED_ORG_INFORMATION_TYPE is null) AND (X_DISPLAYED_ORG_INFORMATION_TP is null)))
194 ) then
195 null;
196 else
197 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
198 app_exception.raise_exception;
199 end if;
200 end if;
201 end loop;
202 return;
203
204 end LOCK_ROW;
205 --
206 procedure UPDATE_ROW (
207 X_ORG_INFORMATION_TYPE in VARCHAR2,
208 X_DESTINATION in VARCHAR2,
209 X_LEGISLATION_CODE in VARCHAR2,
210 X_NAVIGATION_METHOD in VARCHAR2,
211 X_FND_APPLICATION_ID in NUMBER,
212 X_DESCRIPTION in VARCHAR2,
213 X_DISPLAYED_ORG_INFORMATION_TP in VARCHAR2,
214 X_LAST_UPDATE_DATE in DATE,
215 X_LAST_UPDATED_BY in NUMBER,
216 X_LAST_UPDATE_LOGIN in NUMBER
217 ) is
218 begin
219 update HR_ORG_INFORMATION_TYPES set
220 DESTINATION = X_DESTINATION,
221 LEGISLATION_CODE = X_LEGISLATION_CODE,
222 NAVIGATION_METHOD = X_NAVIGATION_METHOD,
223 FND_APPLICATION_ID = X_FND_APPLICATION_ID,
224 DESCRIPTION = X_DESCRIPTION,
225 DISPLAYED_ORG_INFORMATION_TYPE = X_DISPLAYED_ORG_INFORMATION_TP,
226 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
227 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
228 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
229 where ORG_INFORMATION_TYPE = X_ORG_INFORMATION_TYPE;
230 if (sql%notfound) then
231 raise no_data_found;
232 end if;
233
234 update HR_ORG_INFORMATION_TYPES_TL set
235 DISPLAYED_ORG_INFORMATION_TYPE = X_DISPLAYED_ORG_INFORMATION_TP,
236 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
237 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
238 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
239 SOURCE_LANG = userenv('LANG')
240 where ORG_INFORMATION_TYPE = X_ORG_INFORMATION_TYPE
241 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
242
243 if (sql%notfound) then
244 raise no_data_found;
245 end if;
246
247 end UPDATE_ROW;
248 --
249 procedure DELETE_ROW (
250 X_ORG_INFORMATION_TYPE in VARCHAR2
251 ) is
252 begin
253 delete from HR_ORG_INFORMATION_TYPES_TL
254 where ORG_INFORMATION_TYPE = X_ORG_INFORMATION_TYPE;
255
256 if (sql%notfound) then
257 raise no_data_found;
258 end if;
259
260 --
261 -- Added the following code as a part of Zero Downtime Patching Project.
262 -- Code Starts Here.
263 --
264
265 BEGIN
266 PER_RIC_PKG.chk_integrity (
267 p_entity_name => 'HR_ORG_INFORMATION_TYPES',
268 p_ref_entity_info => PER_RIC_PKG.ref_entity_tbl(
269 PER_RIC_PKG.ref_info_rec('HR_ORG_INFORMATION_TYPES_TL', PER_RIC_PKG.column_info_tbl(
270 PER_RIC_PKG.col_info_rec('ORG_INFORMATION_TYPE',X_ORG_INFORMATION_TYPE,NULL,NULL))),
271 PER_RIC_PKG.ref_info_rec('HR_ORG_INFO_TYPES_BY_CLASS', PER_RIC_PKG.column_info_tbl(
272 PER_RIC_PKG.col_info_rec('ORG_INFORMATION_TYPE',X_ORG_INFORMATION_TYPE,NULL,NULL)))
273 ),
274 p_ref_type => 'DEL');
275
276 END;
277 --
278 -- Code Ends Here.
279 --
280
281 delete from HR_ORG_INFORMATION_TYPES
282 where ORG_INFORMATION_TYPE = X_ORG_INFORMATION_TYPE;
283 if (sql%notfound) then
284 raise no_data_found;
285 end if;
286 end DELETE_ROW;
287 --
288 procedure ADD_LANGUAGE
289 is
290 begin
291 delete from HR_ORG_INFORMATION_TYPES_TL T
292 where not exists
293 (select NULL
294 from HR_ORG_INFORMATION_TYPES B
295 where B.ORG_INFORMATION_TYPE = T.ORG_INFORMATION_TYPE
296 );
297
298 update HR_ORG_INFORMATION_TYPES_TL T set (
299 DISPLAYED_ORG_INFORMATION_TYPE
300 ) = (select
301 B.DISPLAYED_ORG_INFORMATION_TYPE
302 from HR_ORG_INFORMATION_TYPES_TL B
303 where B.ORG_INFORMATION_TYPE = T.ORG_INFORMATION_TYPE
304 and B.LANGUAGE = T.SOURCE_LANG)
305 where (
306 T.ORG_INFORMATION_TYPE,
307 T.LANGUAGE
308 ) in (select
309 SUBT.ORG_INFORMATION_TYPE,
310 SUBT.LANGUAGE
311 from HR_ORG_INFORMATION_TYPES_TL SUBB, HR_ORG_INFORMATION_TYPES_TL SUBT
312 where SUBB.ORG_INFORMATION_TYPE = SUBT.ORG_INFORMATION_TYPE
313 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
314 and (SUBB.DISPLAYED_ORG_INFORMATION_TYPE <> SUBT.DISPLAYED_ORG_INFORMATION_TYPE
315 or (SUBB.DISPLAYED_ORG_INFORMATION_TYPE is null and SUBT.DISPLAYED_ORG_INFORMATION_TYPE is not null)
316 or (SUBB.DISPLAYED_ORG_INFORMATION_TYPE is not null and SUBT.DISPLAYED_ORG_INFORMATION_TYPE is null)
317 ));
318
319 insert into HR_ORG_INFORMATION_TYPES_TL (
320 ORG_INFORMATION_TYPE,
321 DISPLAYED_ORG_INFORMATION_TYPE,
322 CREATED_BY,
323 CREATION_DATE,
324 LAST_UPDATED_BY,
325 LAST_UPDATE_DATE,
326 LAST_UPDATE_LOGIN,
327 LANGUAGE,
328 SOURCE_LANG
329 ) select
330 B.ORG_INFORMATION_TYPE,
331 B.DISPLAYED_ORG_INFORMATION_TYPE,
332 B.CREATED_BY,
333 B.CREATION_DATE,
334 B.LAST_UPDATED_BY,
335 B.LAST_UPDATE_DATE,
336 B.LAST_UPDATE_LOGIN,
337 L.LANGUAGE_CODE,
338 B.SOURCE_LANG
339 from HR_ORG_INFORMATION_TYPES_TL B, FND_LANGUAGES L
340 where L.INSTALLED_FLAG in ('I', 'B')
341 and B.LANGUAGE = userenv('LANG')
342 and not exists
343 (select NULL
344 from HR_ORG_INFORMATION_TYPES_TL T
345 where T.ORG_INFORMATION_TYPE = B.ORG_INFORMATION_TYPE
346 and T.LANGUAGE = L.LANGUAGE_CODE);
347 end ADD_LANGUAGE;
348
349 --
350 procedure LOAD_ROW (
351 X_ORG_INFORMATION_TYPE in VARCHAR2,
352 X_DESTINATION in VARCHAR2,
353 X_LEGISLATION_CODE in VARCHAR2,
354 X_NAVIGATION_METHOD in VARCHAR2,
355 X_APPLICATION_SHORT_NAME in VARCHAR2,
356 X_DESCRIPTION in VARCHAR2,
357 X_DISPLAYED_ORG_INFORMATION_TP in VARCHAR2,
358 X_OWNER in VARCHAR2,
359 X_LAST_UPDATE_DATE IN varchar2 default sysdate,
360 X_CUSTOM_MODE IN VARCHAR2 default null
361 ) is
362 X_CREATION_DATE DATE;
363 X_CREATED_BY NUMBER;
364 X_LAST_UPDATED_BY NUMBER;
365 X_LAST_UPDATE_LOGIN NUMBER;
366 X_APPLICATION_ID NUMBER;
367 f_luby number; -- entity owner in file
368 f_ludate date; -- entity update date in file
369 db_luby number; -- entity owner in db
370 db_ludate date; -- entity update date in db
371 begin
372 --This has been commented as LAST_UPDATE_DATE is passed as an parameter
373 /*OWNER_TO_WHO (
374 X_OWNER,
375 X_CREATION_DATE,
376 X_CREATED_BY,
377 X_LAST_UPDATE_DATE,
378 X_LAST_UPDATED_BY,
379 X_LAST_UPDATE_LOGIN
380 );*/
381
382 X_APPLICATION_ID := APPLICATION_ID(X_APPLICATION_SHORT_NAME);
383 -- Translate owner to file_last_updated_by
384 f_luby := fnd_load_util.owner_id(X_OWNER);
385 -- Translate char last_update_date to date
386 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
387 select LAST_UPDATED_BY, LAST_UPDATE_DATE
388 into db_luby, db_ludate
389 from HR_ORG_INFORMATION_TYPES
390 where ORG_INFORMATION_TYPE = X_ORG_INFORMATION_TYPE;
391
392 -- Test for customization and version
393 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
394 db_ludate, X_CUSTOM_MODE)) then
395 -- Update existing row
396 HR_ORG_INFORMATION_TYPES_PKG.Update_Row(
397 X_ORG_INFORMATION_TYPE,
398 X_DESTINATION,
399 X_LEGISLATION_CODE,
400 X_NAVIGATION_METHOD,
401 X_APPLICATION_ID,
402 X_DESCRIPTION,
403 X_DISPLAYED_ORG_INFORMATION_TP,
404 f_ludate,
405 f_luby,
406 0);
407 END IF;
408 exception
409 when no_data_found then
410 -- Record doesn't exist - insert in all cases
411 HR_ORG_INFORMATION_TYPES_PKG.Insert_Row(
412 X_ORG_INFORMATION_TYPE,
413 X_DESTINATION,
414 X_LEGISLATION_CODE,
415 X_NAVIGATION_METHOD,
416 X_APPLICATION_ID,
417 X_DESCRIPTION,
418 X_DISPLAYED_ORG_INFORMATION_TP,
419 f_ludate,
420 f_luby,
421 f_ludate,
422 f_luby,
423 0);
424
425 end LOAD_ROW;
426 --
427 procedure TRANSLATE_ROW (
428 X_ORG_INFORMATION_TYPE in VARCHAR2,
429 X_DESCRIPTION in VARCHAR2,
430 X_DISPLAYED_ORG_INFORMATION_TP in VARCHAR2,
431 X_OWNER in VARCHAR2,
432 X_LAST_UPDATE_DATE in VARCHAR2 default sysdate,
433 X_CUSTOM_MODE IN VARCHAR2 default null
434 ) is
435 X_CREATION_DATE DATE;
436 X_CREATED_BY NUMBER;
437 X_LAST_UPDATED_BY NUMBER;
438 X_LAST_UPDATE_LOGIN NUMBER;
439 f_luby number; -- entity owner in file
440 f_ludate date; -- entity update date in file
441 db_luby number; -- entity owner in db
442 db_ludate date; -- entity update date in db
443 begin
444 --This has been commented as LAST_UPDATE_DATE is passed as an parameter
445 /*OWNER_TO_WHO (
446 X_OWNER,
447 X_CREATION_DATE,
448 X_CREATED_BY,
449 X_LAST_UPDATE_DATE,
450 X_LAST_UPDATED_BY,
451 X_LAST_UPDATE_LOGIN
452 );*/
453 -- Translate owner to file_last_updated_by
454 f_luby := fnd_load_util.owner_id(X_OWNER);
455 -- Translate char last_update_date to date
456 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
457
458 -- added the check as per Bug 5092005 to make sure that only
459 -- the correct row is fetched instead of multiple rows
460
461 select LAST_UPDATED_BY, LAST_UPDATE_DATE
462 into db_luby, db_ludate
463 from HR_ORG_INFORMATION_TYPES_TL
464 where ORG_INFORMATION_TYPE = X_ORG_INFORMATION_TYPE
465 and LANGUAGE=userenv('LANG');
466
467 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
468 db_ludate,X_CUSTOM_MODE)) then
469 -- Update translations for this language
470 -- bug 5235538 nls date issue, changed to LAST_UPDATE_DATE = f_ludate, LAST_UPDATED_BY = f_luby
471
472 update HR_ORG_INFORMATION_TYPES_TL
473 set DISPLAYED_ORG_INFORMATION_TYPE = X_DISPLAYED_ORG_INFORMATION_TP
474 , LAST_UPDATE_DATE = f_ludate
475 , LAST_UPDATED_BY = f_luby
476 , LAST_UPDATE_LOGIN = 0
477 , SOURCE_LANG = userenv('LANG')
478 where ORG_INFORMATION_TYPE = X_ORG_INFORMATION_TYPE
479 and userenv('LANG') in (LANGUAGE,SOURCE_LANG);
480 end if;
481 exception
482 when no_data_found then
483 -- Do not insert missing translations, skip this row
484 null;
485
486 end TRANSLATE_ROW;
487 --
488 end HR_ORG_INFORMATION_TYPES_PKG;