[Home] [Help]
PACKAGE BODY: APPS.HR_KI_OPT_LOAD_API
Source
1 Package Body HR_KI_OPT_LOAD_API as
2 /* $Header: hrkioptl.pkb 120.3 2008/03/20 07:44:12 avarri ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'HR_KI_OPT_LOAD_API';
7 --
8 procedure UPDATE_ROW (
9
10 X_OPTION_ID in number
11 ,X_VALUE in varchar2
12 ,X_ENCRYPTED in varchar2
13 ,X_LAST_UPDATE_DATE in DATE
14 ,X_LAST_UPDATED_BY in NUMBER
15 ,X_LAST_UPDATE_LOGIN in NUMBER
16 ,X_OBJECT_VERSION_NUMBER in NUMBER
17
18 ) is
19
20 l_integration_id number;
21 l_option_type_id number;
22 l_option_level_id varchar2(50);
23 l_value varchar2(1000);
24
25 begin
26
27 select integration_id,option_type_id,option_level_id
28 into l_integration_id,l_option_type_id,l_option_level_id
29 from hr_ki_options
30 where OPTION_ID = X_OPTION_ID;
31
32 if X_ENCRYPTED = 'Y' then
33 l_value := l_integration_id || '#'
34 || l_option_type_id || '#'
35 || l_option_level_id || '#'
36 || X_OPTION_ID;
37 FND_VAULT.PUT('KI',l_value,X_VALUE);
38 else
39 l_value := X_VALUE;
40 end if;
41
42 update HR_KI_OPTIONS
43 set
44 VALUE = l_value ,
45 ENCRYPTED = X_ENCRYPTED,
46 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
47 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
48 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
49 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER+1
50 where OPTION_ID = X_OPTION_ID;
51
52
53
54
55 end UPDATE_ROW;
56
57
58 procedure INSERT_ROW (
59
60 X_ROWID in out nocopy VARCHAR2,
61 X_OPTION_ID in out nocopy NUMBER,
62 X_OPTION_TYPE_ID in number,
63 X_OPTION_LEVEL in number,
64 X_OPTION_LEVEL_ID in varchar2,
65 X_INTEGRATION_ID in number,
66 X_VALUE in varchar2,
67 X_ENCRYPTED in varchar2,
68 X_CREATION_DATE in DATE,
69 X_CREATED_BY in NUMBER,
70 X_LAST_UPDATE_DATE in DATE,
71 X_LAST_UPDATED_BY in NUMBER,
72 X_LAST_UPDATE_LOGIN in NUMBER
73
74 ) is
75 cursor C is select ROWID from HR_KI_OPTIONS
76 where option_id = X_OPTION_ID;
77
78 l_value varchar2(1000);
79
80 begin
81 select HR_KI_OPTIONS_S.NEXTVAL into X_OPTION_ID from sys.dual;
82 if X_ENCRYPTED = 'Y' then
83 l_value := X_INTEGRATION_ID || '#'
84 || X_OPTION_TYPE_ID || '#'
85 || X_OPTION_LEVEL_ID || '#'
86 || X_OPTION_ID;
87 FND_VAULT.PUT('KI',l_value,X_VALUE);
88
89 else
90 l_value := X_VALUE;
91 end if;
92
93 insert into HR_KI_OPTIONS (
94 OPTION_ID,
95 OPTION_TYPE_ID,
96 OPTION_LEVEL,
97 OPTION_LEVEL_ID,
98 INTEGRATION_ID,
99 VALUE,
100 ENCRYPTED,
101 CREATION_DATE,
102 CREATED_BY,
103 LAST_UPDATE_DATE,
104 LAST_UPDATED_BY,
105 LAST_UPDATE_LOGIN,
106 OBJECT_VERSION_NUMBER
107 ) values (
108 X_OPTION_ID,
109 X_OPTION_TYPE_ID,
110 X_OPTION_LEVEL,
111 X_OPTION_LEVEL_ID,
112 X_INTEGRATION_ID,
113 l_value,
114 X_ENCRYPTED,
115 X_CREATION_DATE,
116 X_CREATED_BY,
117 X_LAST_UPDATE_DATE,
118 X_LAST_UPDATED_BY,
119 X_LAST_UPDATE_LOGIN,
120 1
121 );
122
123 open c;
124 fetch c into X_ROWID;
125 if (c%notfound) then
126 close c;
127 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
128 hr_utility.set_message_token('PROCEDURE',
129 'hr_ki_options.insert_row');
130 hr_utility.set_message_token('STEP','1');
131 hr_utility.raise_error;
132 end if;
133 close c;
134
135
136
137
138 end INSERT_ROW;
139
140 procedure validate_row(
141 X_OPTION_TYPE_KEY in varchar2,
142 X_OPTION_LEVEL in number,
143 X_OPTION_LEVEL_KEY in varchar2,
144 X_INTEGRATION_KEY in varchar2,
145 X_INTEGRATION_ID in out nocopy number,
146 X_OPTION_TYPE_ID in out nocopy number,
147 X_OPTION_LEVEL_ID in out nocopy varchar2
148 )
149 is
150 CURSOR CUR_VALIDATE_INT IS
151 select integration_id
152 from
153 hr_ki_integrations
154 where
155 upper(integration_key) = upper(X_INTEGRATION_KEY);
156
157 CURSOR CUR_VALIDATE_OTY IS
158 select option_type_id
159 from
160 hr_ki_option_types
161 where
162 upper(option_type_key) = upper(X_OPTION_TYPE_KEY);
163
164 CURSOR CUR_VALIDATE_LEVEL_APP IS
165 select application_id from fnd_application
166 where
167 application_short_name = X_OPTION_LEVEL_KEY;
168
169 CURSOR CUR_VALIDATE_LEVEL_RESP IS
170 select responsibility_id||'#'||application_id from fnd_responsibility
171 where
172 responsibility_key = X_OPTION_LEVEL_KEY;
173
174 CURSOR CUR_VALIDATE_LEVEL_USR IS
175 select user_id from fnd_user
176 where
177 user_name = X_OPTION_LEVEL_KEY;
178
179
180 begin
181
182 OPEN CUR_VALIDATE_INT;
183 FETCH CUR_VALIDATE_INT INTO X_INTEGRATION_ID;
184 if (CUR_VALIDATE_INT%notfound) then
185 close CUR_VALIDATE_INT;
186 fnd_message.set_name('PER','PER_449955_OPT_INT_ID_ABSENT');
187 fnd_message.raise_error;
188 end if;
189 close CUR_VALIDATE_INT;
190
191 OPEN CUR_VALIDATE_OTY;
192 FETCH CUR_VALIDATE_OTY INTO X_OPTION_TYPE_ID;
193 if (CUR_VALIDATE_OTY%notfound) then
194 close CUR_VALIDATE_OTY;
195 fnd_message.set_name('PER','PER_449953_OPT_OP_TY_ID_ABSENT');
196 fnd_message.raise_error;
197 end if;
198 close CUR_VALIDATE_OTY;
199
200 if X_OPTION_LEVEL=100 then
201
202 X_OPTION_LEVEL_ID :=null;
203
204 elsif X_OPTION_LEVEL=80 then
205 OPEN CUR_VALIDATE_LEVEL_APP;
206 FETCH CUR_VALIDATE_LEVEL_APP INTO X_OPTION_LEVEL_ID;
207 if (CUR_VALIDATE_LEVEL_APP%notfound) then
208 close CUR_VALIDATE_LEVEL_APP;
209 fnd_message.set_name('PER','PER_449958_OPT_OP_APP_ID_ERR');
210 fnd_message.raise_error;
211 end if;
212 close CUR_VALIDATE_LEVEL_APP;
213
214 elsif X_OPTION_LEVEL=60 then
215 OPEN CUR_VALIDATE_LEVEL_RESP;
216 FETCH CUR_VALIDATE_LEVEL_RESP INTO X_OPTION_LEVEL_ID;
217 if (CUR_VALIDATE_LEVEL_RESP%notfound) then
218 close CUR_VALIDATE_LEVEL_RESP;
219 fnd_message.set_name('PER','PER_449959_OPT_OP_RESP_ID_ERR');
220 fnd_message.raise_error;
221 end if;
222 close CUR_VALIDATE_LEVEL_RESP;
223
224 elsif X_OPTION_LEVEL=20 then
225 OPEN CUR_VALIDATE_LEVEL_USR;
226 FETCH CUR_VALIDATE_LEVEL_USR INTO X_OPTION_LEVEL_ID;
227 if (CUR_VALIDATE_LEVEL_USR%notfound) then
228 close CUR_VALIDATE_LEVEL_USR;
229 fnd_message.set_name('PER','PER_449960_OPT_OP_US_ID_ERR');
230 fnd_message.raise_error;
231 end if;
232 close CUR_VALIDATE_LEVEL_USR;
233
234 end if;
235
236 end validate_row;
237
238 procedure LOAD_ROW
239 (
240 X_OPTION_TYPE_KEY in VARCHAR2,
241 X_OPTION_LEVEL in VARCHAR2,
242 X_OPTION_LEVEL_KEY in VARCHAR2,
243 X_INTEGRATION_KEY in VARCHAR2,
244 X_VALUE in VARCHAR2,
245 X_ENCRYPTED in VARCHAR2,
246 X_OWNER in VARCHAR2,
247 X_CUSTOM_MODE in VARCHAR2,
248 X_LAST_UPDATE_DATE in VARCHAR2
249 )
250 is
251 l_proc VARCHAR2(31) := 'HR_KI_OPTIONS_API.LOAD_ROW';
252 l_rowid rowid;
253 l_created_by HR_KI_OPTIONS.created_by%TYPE := 0;
254 l_creation_date HR_KI_OPTIONS.creation_date%TYPE := SYSDATE;
255 l_last_update_date HR_KI_OPTIONS.last_update_date%TYPE := SYSDATE;
256 l_last_updated_by HR_KI_OPTIONS.last_updated_by%TYPE := 0;
257 l_last_update_login HR_KI_OPTIONS.last_update_login%TYPE := 0;
258 l_option_id HR_KI_OPTIONS.option_id%TYPE;
259 l_option_level_id HR_KI_OPTIONS.option_level_id%TYPE;
260 l_option_type_id HR_KI_OPTION_TYPES.option_type_id%TYPE;
261 l_integration_id HR_KI_INTEGRATIONS.integration_id%TYPE;
262 l_object_version_number HR_KI_OPTIONS.object_version_number%TYPE;
263
264 db_luby number; -- entity owner in db
265 db_ludate date; -- entity update date in db
266
267 CURSOR CUR_VALIDATE_SITE IS
268 select
269 distinct opt.option_id,opt.object_version_number
270 from HR_KI_OPTIONS opt,HR_KI_OPTION_TYPES oty,
271 hr_ki_integrations int
272 where
273 opt.option_type_id=oty.option_type_id
274 and int.integration_id=opt.integration_id
275 and upper(int.integration_key) = upper(X_INTEGRATION_KEY)
276 and upper(oty.option_type_key)=upper(X_OPTION_TYPE_KEY)
277 and opt.option_level=to_number(X_OPTION_LEVEL)
278 and opt.option_level_id is null;
279
280 CURSOR CUR_VALIDATE_ROLE IS
281 select
282 distinct opt.option_id,opt.object_version_number
283 from HR_KI_OPTIONS opt,HR_KI_OPTION_TYPES oty,
284 hr_ki_integrations int
285 where
286 opt.option_type_id=oty.option_type_id
287 and int.integration_id=opt.integration_id
288 and upper(int.integration_key) = upper(X_INTEGRATION_KEY)
289 and upper(oty.option_type_key) =upper(X_OPTION_TYPE_KEY)
290 and opt.option_level=to_number(X_OPTION_LEVEL);
291
292
293 CURSOR CUR_VALIDATE_USER IS
294 select
295 distinct opt.option_id,opt.object_version_number
296 from HR_KI_OPTIONS opt,HR_KI_OPTION_TYPES oty,
297 hr_ki_integrations int,
298 fnd_user usr
299 where
300 opt.option_type_id=oty.option_type_id
301 and int.integration_id=opt.integration_id
302 and upper(int.integration_key) = upper(X_INTEGRATION_KEY)
303 and upper(oty.option_type_key) =upper(X_OPTION_TYPE_KEY)
304 and opt.option_level=to_number(X_OPTION_LEVEL)
305 and usr.user_name=X_OPTION_LEVEL_KEY
306 and to_char(usr.user_id)=opt.OPTION_LEVEL_ID;
307
308 CURSOR CUR_VALIDATE_RESP IS
309 select
310 distinct opt.option_id,opt.object_version_number
311 from HR_KI_OPTIONS opt,HR_KI_OPTION_TYPES oty,
312 hr_ki_integrations int,
313 fnd_responsibility resp
314 where
315 opt.option_type_id=oty.option_type_id
316 and int.integration_id=opt.integration_id
317 and upper(int.integration_key) = upper(X_INTEGRATION_KEY)
318 and upper(oty.option_type_key) = upper(X_OPTION_TYPE_KEY)
319 and opt.option_level=to_number(X_OPTION_LEVEL)
320 and resp.responsibility_key= X_OPTION_LEVEL_KEY
321 and resp.responsibility_id =
322 (substr(option_level_id, 0, instr(option_level_id, '#') - 1))
323 and resp.application_id =
324 (substr(option_level_id, instr(option_level_id, '#') + 1));
325
326 CURSOR CUR_VALIDATE_APP IS
327 select
328 distinct opt.option_id,opt.object_version_number
329 from HR_KI_OPTIONS opt,HR_KI_OPTION_TYPES oty,
330 hr_ki_integrations int,
331 fnd_application app
332 where
333 opt.option_type_id=oty.option_type_id
337 and opt.option_level=to_number(X_OPTION_LEVEL)
334 and int.integration_id=opt.integration_id
335 and upper(int.integration_key) = upper(X_INTEGRATION_KEY)
336 and upper(oty.option_type_key) =upper(X_OPTION_TYPE_KEY)
338 and app.application_short_name=X_OPTION_LEVEL_KEY
339 and to_char(app.application_id)=opt.OPTION_LEVEL_ID;
340
341 begin
342 --
343 -- added for 5354277
344 hr_general.g_data_migrator_mode := 'Y';
345 --
346
347 -- Translate owner to file_last_updated_by
348 l_last_updated_by := fnd_load_util.owner_id(X_OWNER);
349 l_created_by := fnd_load_util.owner_id(X_OWNER);
350
351 -- Translate char last_update_date to date
352 l_last_update_date := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD hh24:mi:ss'), sysdate);
353
354
355 -- Update or insert row as appropriate
356 if X_OPTION_LEVEL =100 then
357 OPEN CUR_VALIDATE_SITE;
358 FETCH CUR_VALIDATE_SITE INTO l_option_id,l_object_version_number;
359 close CUR_VALIDATE_SITE;
360
361 elsif X_OPTION_LEVEL =80 then
362 OPEN CUR_VALIDATE_APP;
363 FETCH CUR_VALIDATE_APP INTO l_option_id,l_object_version_number;
364 close CUR_VALIDATE_APP;
365
366 elsif X_OPTION_LEVEL =60 then
367 OPEN CUR_VALIDATE_RESP;
368 FETCH CUR_VALIDATE_RESP INTO l_option_id,l_object_version_number;
369 close CUR_VALIDATE_RESP;
370
371 elsif X_OPTION_LEVEL =40 then
372 OPEN CUR_VALIDATE_ROLE;
373 FETCH CUR_VALIDATE_ROLE INTO l_option_id,l_object_version_number;
374 close CUR_VALIDATE_ROLE;
375
376 elsif X_OPTION_LEVEL =20 then
377 OPEN CUR_VALIDATE_USER;
378 FETCH CUR_VALIDATE_USER INTO l_option_id,l_object_version_number;
379 close CUR_VALIDATE_USER;
380
381 end if;
382
383 select LAST_UPDATED_BY, LAST_UPDATE_DATE
384 into db_luby, db_ludate
385 from HR_KI_OPTIONS
386 where option_id = l_option_id;
387
388
389 if (fnd_load_util.upload_test(l_last_updated_by, l_last_update_date, db_luby,
390 db_ludate, X_CUSTOM_MODE)) then
391
392 UPDATE_ROW
393 (
394 X_OPTION_ID => l_option_id
395 ,X_VALUE => X_VALUE
396 ,X_ENCRYPTED => X_ENCRYPTED
397 ,X_LAST_UPDATE_DATE => l_last_update_date
398 ,X_LAST_UPDATED_BY => l_last_updated_by
399 ,X_LAST_UPDATE_LOGIN => l_last_update_login
400 ,X_OBJECT_VERSION_NUMBER => l_object_version_number
401 );
402
403 end if;
404 exception
405 when no_data_found then
406
407
408 validate_row
409 (
410 X_OPTION_TYPE_KEY => X_OPTION_TYPE_KEY,
411 X_OPTION_LEVEL => to_number(X_OPTION_LEVEL),
412 X_OPTION_LEVEL_KEY => X_OPTION_LEVEL_KEY,
413 X_INTEGRATION_KEY => X_INTEGRATION_KEY,
414 X_INTEGRATION_ID => l_integration_id,
415 X_OPTION_TYPE_ID => l_option_type_id,
416 X_OPTION_LEVEL_ID => l_option_level_id
417 );
418
419
420 INSERT_ROW
421 (X_ROWID => l_rowid
422 ,X_OPTION_ID => l_option_id
423 ,X_OPTION_TYPE_ID => l_option_type_id
424 ,X_OPTION_LEVEL => to_number(X_OPTION_LEVEL)
425 ,X_OPTION_LEVEL_ID => l_option_level_id
426 ,X_INTEGRATION_ID => l_integration_id
427 ,X_VALUE => X_VALUE
428 ,X_ENCRYPTED => X_ENCRYPTED
429 ,X_CREATED_BY => l_created_by
430 ,X_CREATION_DATE => l_creation_date
431 ,X_LAST_UPDATE_DATE => l_last_update_date
432 ,X_LAST_UPDATED_BY => l_last_updated_by
433 ,X_LAST_UPDATE_LOGIN => l_last_update_login
434 );
435
436 --
437 end LOAD_ROW;
438
439 END HR_KI_OPT_LOAD_API;