[Home] [Help]
PACKAGE BODY: APPS.HR_KI_OTY_LOAD_API
Source
1 Package Body HR_KI_OTY_LOAD_API as
2 /* $Header: hrkiotyl.pkb 120.1 2006/06/27 16:06:30 avarri noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(31) := 'HR_KI_OTY_LOAD_API';
7 --
8
9
10 procedure UPDATE_ROW (
11 X_OPTION_TYPE_ID in NUMBER,
12 X_DISPLAY_TYPE in VARCHAR2,
13 X_OPTION_NAME in VARCHAR2,
14 X_LAST_UPDATE_DATE in DATE,
15 X_LAST_UPDATED_BY in NUMBER,
16 X_LAST_UPDATE_LOGIN in NUMBER,
17 X_OBJECT_VERSION_NUMBER in NUMBER
18
19 ) is
20
21 begin
22
23
24 update HR_KI_OPTION_TYPES
25 set
26 DISPLAY_TYPE = X_DISPLAY_TYPE,
27 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
28 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
29 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
30 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER+1
31 where OPTION_TYPE_ID = X_OPTION_TYPE_ID;
32
33
34 update HR_KI_OPTION_TYPES_TL set
35 OPTION_NAME = X_OPTION_NAME,
36 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
37 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
38 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
39 SOURCE_LANG = userenv('LANG')
40 where OPTION_TYPE_ID = X_OPTION_TYPE_ID
41 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
42
43 if (sql%notfound) then
44
45 insert into HR_KI_OPTION_TYPES_TL (
46 OPTION_TYPE_ID,
47 OPTION_NAME,
48 CREATED_BY,
49 CREATION_DATE,
50 LAST_UPDATED_BY,
51 LAST_UPDATE_DATE,
52 LAST_UPDATE_LOGIN,
53 LANGUAGE,
54 SOURCE_LANG
55 ) select
56 X_OPTION_TYPE_ID,
57 X_OPTION_NAME,
58 1 ,
59 SYSDATE,
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 HR_KI_OPTION_TYPES_TL T
70 where T.OPTION_TYPE_ID = X_OPTION_TYPE_ID
71 and T.LANGUAGE = L.LANGUAGE_CODE);
72
73 end if;
74
75 end UPDATE_ROW;
76
77
78 procedure INSERT_ROW (
79 X_ROWID in out nocopy VARCHAR2,
80 X_OPTION_TYPE_ID in out nocopy NUMBER,
81 X_OPTION_TYPE_KEY in VARCHAR2,
82 X_DISPLAY_TYPE in VARCHAR2,
83 X_OPTION_NAME in varchar2,
84 X_CREATION_DATE in DATE,
85 X_CREATED_BY in NUMBER,
86 X_LAST_UPDATE_DATE in DATE,
87 X_LAST_UPDATED_BY in NUMBER,
88 X_LAST_UPDATE_LOGIN in NUMBER
89 ) is
90
91 cursor C is select ROWID from HR_KI_OPTION_TYPES
92 where option_type_id = X_OPTION_TYPE_ID;
93
94 begin
95
96 select HR_KI_OPTION_TYPES_S.NEXTVAL into X_OPTION_TYPE_ID from sys.dual;
97
98 insert into HR_KI_OPTION_TYPES (
99 OPTION_TYPE_ID,
100 OPTION_TYPE_KEY,
101 DISPLAY_TYPE,
102 CREATION_DATE,
103 CREATED_BY,
104 LAST_UPDATE_DATE,
105 LAST_UPDATED_BY,
106 LAST_UPDATE_LOGIN,
107 OBJECT_VERSION_NUMBER
108 ) values (
109 X_OPTION_TYPE_ID,
110 X_OPTION_TYPE_KEY,
111 X_DISPLAY_TYPE,
112 X_CREATION_DATE,
113 X_CREATED_BY,
114 X_LAST_UPDATE_DATE,
115 X_LAST_UPDATED_BY,
116 X_LAST_UPDATE_LOGIN,
117 1
118 );
119
120
121 insert into HR_KI_OPTION_TYPES_TL (
122 OPTION_TYPE_ID,
123 OPTION_NAME,
124 CREATED_BY,
125 CREATION_DATE,
126 LAST_UPDATED_BY,
127 LAST_UPDATE_DATE,
128 LAST_UPDATE_LOGIN,
129 LANGUAGE,
130 SOURCE_LANG
131 ) select
132 X_OPTION_TYPE_ID,
133 X_OPTION_NAME,
134 X_CREATED_BY,
135 X_CREATION_DATE,
136 X_LAST_UPDATED_BY,
137 X_LAST_UPDATE_DATE,
138 X_LAST_UPDATE_LOGIN,
139 L.LANGUAGE_CODE,
140 userenv('LANG')
141 from FND_LANGUAGES L
142 where L.INSTALLED_FLAG in ('I', 'B')
143 and not exists
144 (select NULL
145 from HR_KI_OPTION_TYPES_TL T
146 where T.OPTION_TYPE_ID = X_OPTION_TYPE_ID
147 and T.LANGUAGE = L.LANGUAGE_CODE);
148
149 open c;
150 fetch c into X_ROWID;
151 if (c%notfound) then
152 close c;
153 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
154 hr_utility.set_message_token('PROCEDURE',
155 'hr_ki_option_types.insert_row');
156 hr_utility.set_message_token('STEP','1');
157 hr_utility.raise_error;
158 end if;
159 close c;
160
161
162 end INSERT_ROW;
163
164 procedure LOAD_ROW
165 (
166 X_OPTION_TYPE_KEY in VARCHAR2,
167 X_DISPLAY_TYPE in VARCHAR2,
168 X_OPTION_NAME in VARCHAR2,
169 X_OWNER in varchar2,
170 X_CUSTOM_MODE in varchar2,
171 X_LAST_UPDATE_DATE in varchar2
172
173 )
174 is
175 l_proc VARCHAR2(31) := 'HR_KI_OTY_LOAD_API.LOAD_ROW';
176 l_rowid rowid;
177 l_created_by HR_KI_OPTION_TYPES.created_by%TYPE := 0;
178 l_creation_date HR_KI_OPTION_TYPES.creation_date%TYPE := SYSDATE;
179 l_last_update_date HR_KI_OPTION_TYPES.last_update_date%TYPE := SYSDATE;
180 l_last_updated_by HR_KI_OPTION_TYPES.last_updated_by%TYPE := 0;
181 l_last_update_login HR_KI_OPTION_TYPES.last_update_login%TYPE := 0;
182 l_option_type_id HR_KI_OPTION_TYPES.option_type_id%TYPE;
183 l_object_version_number HR_KI_OPTION_TYPES.object_version_number%TYPE;
184
185 db_luby number; -- entity owner in db
186 db_ludate date; -- entity update date in db
187
188
189 CURSOR C_APPL IS
190 select option_type_id,object_version_number
191 from HR_KI_OPTION_TYPES
192 where upper(option_type_key) = upper(X_OPTION_TYPE_KEY);
193
194 begin
195 --
196 -- added for 5354277
197 hr_general.g_data_migrator_mode := 'Y';
198 --
199
200 -- Translate owner to file_last_updated_by
201 l_last_updated_by := fnd_load_util.owner_id(X_OWNER);
202 l_created_by := fnd_load_util.owner_id(X_OWNER);
203
204 -- Translate char last_update_date to date
205 l_last_update_date := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD hh24:mi:ss'), sysdate);
206
207
208 -- Update or insert row as appropriate
209
210 OPEN C_APPL;
211 FETCH C_APPL INTO l_option_type_id,l_object_version_number;
212
213
214 if C_APPL%notfound then
215 close C_APPL;
216 INSERT_ROW
217 (X_ROWID => l_rowid
218 ,X_OPTION_TYPE_ID => l_option_type_id
219 ,X_OPTION_TYPE_KEY => X_OPTION_TYPE_KEY
220 ,X_DISPLAY_TYPE => X_DISPLAY_TYPE
221 ,X_OPTION_NAME => X_OPTION_NAME
222 ,X_CREATED_BY => l_created_by
223 ,X_CREATION_DATE => l_creation_date
224 ,X_LAST_UPDATE_DATE => l_last_update_date
225 ,X_LAST_UPDATED_BY => l_last_updated_by
226 ,X_LAST_UPDATE_LOGIN => l_last_update_login
227 );
228
229
230 else
231 close C_APPL;
232 select LAST_UPDATED_BY, LAST_UPDATE_DATE
233 into db_luby, db_ludate
234 from HR_KI_OPTION_TYPES
235 where option_type_id = l_option_type_id;
236
237
238 if (fnd_load_util.upload_test(l_last_updated_by, l_last_update_date, db_luby,
239 db_ludate, X_CUSTOM_MODE)) then
240
241 UPDATE_ROW
242 ( X_OPTION_TYPE_ID => l_option_type_id
243 ,X_OPTION_NAME => X_OPTION_NAME
244 ,X_DISPLAY_TYPE => X_DISPLAY_TYPE
245 ,X_LAST_UPDATE_DATE => l_last_update_date
246 ,X_LAST_UPDATED_BY => l_last_updated_by
247 ,X_LAST_UPDATE_LOGIN => l_last_update_login
248 ,X_OBJECT_VERSION_NUMBER => l_object_version_number
249 );
250
251 end if;
252
253 end if;
254
255 --
256 end LOAD_ROW;
257
258 procedure TRANSLATE_ROW
259 (X_OPTION_TYPE_KEY in varchar2,
260 X_OPTION_NAME in VARCHAR2,
261 X_OWNER in varchar2,
262 X_CUSTOM_MODE in varchar2,
263 X_LAST_UPDATE_DATE in varchar2
264 )
265 is
266 l_option_type_id HR_KI_OPTION_TYPES.option_type_id%TYPE;
267
268 f_luby number; -- entity owner in file
269 f_ludate date; -- entity update date in file
270 db_luby number; -- entity owner in db
271 db_ludate date; -- entity update date in db
272
273
274 begin
275 --
276 -- added for 5354277
277 hr_general.g_data_migrator_mode := 'Y';
278 --
279
280 select option_type_id into l_option_type_id
281 from HR_KI_OPTION_TYPES
282 where upper(option_type_key) = upper(X_OPTION_TYPE_KEY);
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 hh24:mi:ss'), sysdate);
290
291 begin
292 select LAST_UPDATED_BY, LAST_UPDATE_DATE
293 into db_luby, db_ludate
294 from HR_KI_OPTION_TYPES_TL
295 where
296 LANGUAGE = userenv('LANG')
297 and option_type_id = l_option_type_id;
298
299 -- Test for customization and version
300 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
301 db_ludate,X_CUSTOM_MODE)) then
302
303 UPDATE HR_KI_OPTION_TYPES_TL
304 SET
305 OPTION_NAME = X_OPTION_NAME,
306 LAST_UPDATE_DATE = f_ludate ,
307 LAST_UPDATED_BY = f_luby,
308 LAST_UPDATE_LOGIN = 0,
309 SOURCE_LANG = userenv('LANG')
310 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
311 and option_type_id = l_option_type_id;
312
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
320 end TRANSLATE_ROW;
321
322 END HR_KI_OTY_LOAD_API;