[Home] [Help]
PACKAGE BODY: APPS.HR_KI_TIS_LOAD_API
Source
1 Package Body HR_KI_TIS_LOAD_API as
2 /* $Header: hrkitisl.pkb 120.3 2008/02/26 14:03:53 avarri ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(31) := 'HR_KI_TIS_LOAD_API';
7 --
8 -- The below procedure inserts the row into HR_KI_TOPIC_INTEGRATIONS
9 --
10 procedure INSERT_ROW (
11 X_ROWID in out nocopy VARCHAR2,
12 X_TOPIC_INTEGRATIONS_ID in out nocopy NUMBER,
13 X_TOPIC_ID in NUMBER,
14 X_INTEGRATION_ID in NUMBER,
15 X_PARAM_NAME1 in VARCHAR2,
16 X_PARAM_VALUE1 in VARCHAR2,
17 X_PARAM_NAME2 in VARCHAR2,
18 X_PARAM_VALUE2 in VARCHAR2,
19 X_PARAM_NAME3 in VARCHAR2,
20 X_PARAM_VALUE3 in VARCHAR2,
21 X_PARAM_NAME4 in VARCHAR2,
22 X_PARAM_VALUE4 in VARCHAR2,
23 X_PARAM_NAME5 in VARCHAR2,
24 X_PARAM_VALUE5 in VARCHAR2,
25 X_PARAM_NAME6 in VARCHAR2,
26 X_PARAM_VALUE6 in VARCHAR2,
27 X_PARAM_NAME7 in VARCHAR2,
28 X_PARAM_VALUE7 in VARCHAR2,
29 X_PARAM_NAME8 in VARCHAR2,
30 X_PARAM_VALUE8 in VARCHAR2,
31 X_PARAM_NAME9 in VARCHAR2,
32 X_PARAM_VALUE9 in VARCHAR2,
33 X_PARAM_NAME10 in VARCHAR2,
34 X_PARAM_VALUE10 in VARCHAR2,
35 X_CREATED_BY in NUMBER,
36 X_CREATION_DATE in DATE,
37 X_LAST_UPDATE_DATE in DATE,
38 X_LAST_UPDATED_BY in NUMBER,
39 X_LAST_UPDATE_LOGIN in NUMBER
40 ) is
41 L_LOCK_HANDLE varchar2(500);
42 L_RETURN_VALUE number;
43 --
44 cursor C is
45 SELECT ROWID
46 FROM HR_KI_TOPIC_INTEGRATIONS
47 WHERE topic_integrations_id = x_topic_integrations_id;
48 --
49 begin
50 --
51 DBMS_LOCK.ALLOCATE_UNIQUE
52 (LOCKNAME =>'HR_KI_TOPIC_INTEGRATIONS.'||X_TOPIC_INTEGRATIONS_ID
53 ,LOCKHANDLE => L_LOCK_HANDLE
54 );
55 L_RETURN_VALUE := DBMS_LOCK.REQUEST
56 (LOCKHANDLE => L_LOCK_HANDLE
57 ,TIMEOUT => 0
58 ,RELEASE_ON_COMMIT => true);
59
60 if L_RETURN_VALUE = 0 then
61 select HR_KI_TOPIC_INTEGRATIONS_S.NEXTVAL into x_topic_integrations_id
62 from sys.dual;
63 --
64 insert into HR_KI_TOPIC_INTEGRATIONS (
65 TOPIC_INTEGRATIONS_ID,
66 TOPIC_ID,
67 INTEGRATION_ID,
68 PARAM_NAME1,
69 PARAM_VALUE1,
70 PARAM_NAME2,
71 PARAM_VALUE2,
72 PARAM_NAME3,
73 PARAM_VALUE3,
74 PARAM_NAME4,
75 PARAM_VALUE4,
76 PARAM_NAME5,
77 PARAM_VALUE5,
78 PARAM_NAME6,
79 PARAM_VALUE6,
80 PARAM_NAME7,
81 PARAM_VALUE7,
82 PARAM_NAME8,
83 PARAM_VALUE8,
84 PARAM_NAME9,
85 PARAM_VALUE9,
86 PARAM_NAME10,
87 PARAM_VALUE10,
88 CREATION_DATE,
89 CREATED_BY,
90 LAST_UPDATE_DATE,
91 LAST_UPDATED_BY,
92 LAST_UPDATE_LOGIN,
93 OBJECT_VERSION_NUMBER
94 ) values (
95 X_TOPIC_INTEGRATIONS_ID,
96 X_TOPIC_ID,
97 X_INTEGRATION_ID,
98 X_PARAM_NAME1,
99 X_PARAM_VALUE1,
100 X_PARAM_NAME2,
101 X_PARAM_VALUE2,
102 X_PARAM_NAME3,
103 X_PARAM_VALUE3,
104 X_PARAM_NAME4,
105 X_PARAM_VALUE4,
106 X_PARAM_NAME5,
107 X_PARAM_VALUE5,
108 X_PARAM_NAME6,
109 X_PARAM_VALUE6,
110 X_PARAM_NAME7,
111 X_PARAM_VALUE7,
112 X_PARAM_NAME8,
113 X_PARAM_VALUE8,
114 X_PARAM_NAME9,
115 X_PARAM_VALUE9,
116 X_PARAM_NAME10,
117 X_PARAM_VALUE10,
118 X_CREATION_DATE,
119 X_CREATED_BY,
120 X_LAST_UPDATE_DATE,
121 X_LAST_UPDATED_BY,
122 X_LAST_UPDATE_LOGIN,
123 1
124 );
125 end if;
126 --
127 open c;
128 fetch c into X_ROWID;
129 if (c%notfound) then
130 close c;
131 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
132 hr_utility.set_message_token('PROCEDURE',
133 'HR_KI_TOPIC_INTEGRATIONS.insert_row');
134 hr_utility.set_message_token('STEP','1');
135 hr_utility.raise_error;
136 end if;
137 close c;
138 --
139 end INSERT_ROW;
140 --
141 -- The below procure updates the existing row in HR_KI_TOPIC_INTEGRATIONS
142 --
143 procedure UPDATE_ROW
144 (X_TOPIC_INTEGRATIONS_ID in NUMBER,
145 X_TOPIC_ID in NUMBER,
146 X_INTEGRATION_ID in NUMBER,
147 X_PARAM_NAME1 in VARCHAR2,
148 X_PARAM_VALUE1 in VARCHAR2,
149 X_PARAM_NAME2 in VARCHAR2,
150 X_PARAM_VALUE2 in VARCHAR2,
151 X_PARAM_NAME3 in VARCHAR2,
152 X_PARAM_VALUE3 in VARCHAR2,
153 X_PARAM_NAME4 in VARCHAR2,
154 X_PARAM_VALUE4 in VARCHAR2,
155 X_PARAM_NAME5 in VARCHAR2,
156 X_PARAM_VALUE5 in VARCHAR2,
157 X_PARAM_NAME6 in VARCHAR2,
158 X_PARAM_VALUE6 in VARCHAR2,
159 X_PARAM_NAME7 in VARCHAR2,
160 X_PARAM_VALUE7 in VARCHAR2,
161 X_PARAM_NAME8 in VARCHAR2,
162 X_PARAM_VALUE8 in VARCHAR2,
163 X_PARAM_NAME9 in VARCHAR2,
164 X_PARAM_VALUE9 in VARCHAR2,
165 X_PARAM_NAME10 in VARCHAR2,
166 X_PARAM_VALUE10 in VARCHAR2,
167 X_CREATED_BY in NUMBER,
168 X_CREATION_DATE in DATE,
169 X_LAST_UPDATE_DATE in DATE,
170 X_LAST_UPDATED_BY in NUMBER,
171 X_LAST_UPDATE_LOGIN in NUMBER,
172 X_OBJECT_VERSION_NUMBER in NUMBER
173 ) as
174 L_LOCK_HANDLE varchar2(500);
175 L_RETURN_VALUE number;
176 --
177 begin
178 DBMS_LOCK.ALLOCATE_UNIQUE
179 (LOCKNAME =>'HR_KI_TOPIC_INTEGRATIONS.'||X_TOPIC_INTEGRATIONS_ID
180 ,LOCKHANDLE => L_LOCK_HANDLE
181 );
182 L_RETURN_VALUE := DBMS_LOCK.REQUEST
183 (LOCKHANDLE => L_LOCK_HANDLE
184 ,TIMEOUT => 0
185 ,RELEASE_ON_COMMIT => true);
186 --
187 if L_RETURN_VALUE = 0 then
188 UPDATE HR_KI_TOPIC_INTEGRATIONS TIS
189 SET tis.topic_id = X_TOPIC_ID
190 ,tis.integration_id = X_INTEGRATION_ID
191 ,tis.PARAM_NAME1 = X_PARAM_NAME1
192 ,tis.PARAM_VALUE1 = X_PARAM_VALUE1
193 ,tis.PARAM_NAME2 = X_PARAM_NAME2
194 ,tis.PARAM_VALUE2 = X_PARAM_VALUE2
195 ,tis.PARAM_NAME3 = X_PARAM_NAME3
196 ,tis.PARAM_VALUE3 = X_PARAM_VALUE3
197 ,tis.PARAM_NAME4 = X_PARAM_NAME4
198 ,tis.PARAM_VALUE4 = X_PARAM_VALUE4
199 ,tis.PARAM_NAME5 = X_PARAM_NAME5
200 ,tis.PARAM_VALUE5 = X_PARAM_VALUE5
201 ,tis.PARAM_NAME6 = X_PARAM_NAME6
202 ,tis.PARAM_VALUE6 = X_PARAM_VALUE6
203 ,tis.PARAM_NAME7 = X_PARAM_NAME7
204 ,tis.PARAM_VALUE7 = X_PARAM_VALUE7
205 ,tis.PARAM_NAME8 = X_PARAM_NAME8
206 ,tis.PARAM_VALUE8 = X_PARAM_VALUE8
207 ,tis.PARAM_NAME9 = X_PARAM_NAME9
208 ,tis.PARAM_VALUE9 = X_PARAM_VALUE9
209 ,tis.PARAM_NAME10 = X_PARAM_NAME10
210 ,tis.PARAM_VALUE10 = X_PARAM_VALUE10
211 ,tis.CREATED_BY = X_CREATED_BY
212 ,tis.CREATION_DATE = X_CREATION_DATE
213 ,tis.LAST_UPDATE_DATE = X_LAST_UPDATE_DATE
214 ,tis.LAST_UPDATED_BY = X_LAST_UPDATED_BY
215 ,tis.LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
216 ,tis.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
217 WHERE tis.TOPIC_INTEGRATIONS_ID = X_TOPIC_INTEGRATIONS_ID;
218 end if;
219 end UPDATE_ROW;
220 --
221 -- This procedure validates the topic key and integration key.
222 --
223 procedure validate_keys
224 (
225 X_TOPIC_KEY in VARCHAR2,
226 X_INTEGRATION_KEY in VARCHAR2,
227 X_TOPIC_ID in out nocopy number,
228 X_INTEGRATION_ID in out nocopy number
229 )
230 is
231 --
232 l_proc VARCHAR2(35) := 'HR_KI_TIS_LOAD_API.VALIDATE_KEYS';
233 --
234 CURSOR C_VAL_TPC IS
235 select topic_id
236 from HR_KI_TOPICS
237 where upper(topic_key) = upper(X_TOPIC_KEY);
238 --
239 CURSOR C_VAL_INT IS
240 select integration_id
241 from HR_KI_INTEGRATIONS
242 where upper(integration_key) = upper(X_INTEGRATION_KEY);
243 --
244 begin
245 open C_VAL_TPC;
246 fetch C_VAL_TPC into x_topic_id;
247 --
248 If C_VAL_TPC%NOTFOUND then
249 close C_VAL_TPC;
250 fnd_message.set_name( 'PER','PER_449963_TIS_TOPIC_ID_ABSENT');
251 fnd_message.raise_error;
252 End If;
253 close C_VAL_TPC;
254 --
255 open C_VAL_INT;
256 fetch C_VAL_INT into x_integration_id;
257 If C_VAL_INT%NOTFOUND then
258 close C_VAL_INT;
259 fnd_message.set_name( 'PER','PER_449962_TIS_INT_ID_ABSENT');
260 fnd_message.raise_error;
261 End If;
262 close C_VAL_INT;
263 end validate_keys;
264 --
265 -- The below procedure loads the topic integrations row into database.
266 --
267 procedure LOAD_ROW
268 (
269 X_TOPIC_KEY in VARCHAR2,
270 X_INTEGRATION_KEY in VARCHAR2,
271 X_PARAM_NAME1 in VARCHAR2,
272 X_PARAM_VALUE1 in VARCHAR2,
273 X_PARAM_NAME2 in VARCHAR2,
274 X_PARAM_VALUE2 in VARCHAR2,
275 X_PARAM_NAME3 in VARCHAR2,
276 X_PARAM_VALUE3 in VARCHAR2,
277 X_PARAM_NAME4 in VARCHAR2,
278 X_PARAM_VALUE4 in VARCHAR2,
279 X_PARAM_NAME5 in VARCHAR2,
280 X_PARAM_VALUE5 in VARCHAR2,
281 X_PARAM_NAME6 in VARCHAR2,
282 X_PARAM_VALUE6 in VARCHAR2,
283 X_PARAM_NAME7 in VARCHAR2,
284 X_PARAM_VALUE7 in VARCHAR2,
285 X_PARAM_NAME8 in VARCHAR2,
286 X_PARAM_VALUE8 in VARCHAR2,
287 X_PARAM_NAME9 in VARCHAR2,
288 X_PARAM_VALUE9 in VARCHAR2,
289 X_PARAM_NAME10 in VARCHAR2,
290 X_PARAM_VALUE10 in VARCHAR2,
291 X_LAST_UPDATE_DATE in VARCHAR2,
292 X_CUSTOM_MODE in VARCHAR2,
293 X_OWNER in VARCHAR2
294 )
295 is
296 l_proc VARCHAR2(31) := 'HR_KI_TIS_LOAD_API.LOAD_ROW';
297 l_rowid rowid;
298 l_created_by HR_KI_TOPIC_INTEGRATIONS.created_by%TYPE := 0;
299 l_creation_date HR_KI_TOPIC_INTEGRATIONS.creation_date%TYPE := SYSDATE;
300 l_last_update_date HR_KI_TOPIC_INTEGRATIONS.last_update_date%TYPE := SYSDATE;
301 l_last_updated_by HR_KI_TOPIC_INTEGRATIONS.last_updated_by%TYPE := 0;
302 l_last_update_login HR_KI_TOPIC_INTEGRATIONS.last_update_login%TYPE := 0;
303 l_topic_integrations_id HR_KI_TOPIC_INTEGRATIONS.topic_integrations_id%TYPE;
304 l_object_version_number HR_KI_TOPIC_INTEGRATIONS.object_version_number%TYPE;
305 l_topic_id HR_KI_TOPICS.topic_id%TYPE;
306 l_integration_id HR_KI_INTEGRATIONS.integration_id%TYPE;
307 db_luby number; -- entity owner in db
308 db_ludate date; -- entity update date in db
309 --
310 CURSOR C_APPL IS
311 select tpi.topic_integrations_id,
312 nvl(tpi.object_version_number,1)
313 from hr_ki_topic_integrations tpi,
314 hr_ki_topics top,
315 hr_ki_integrations int
316 where tpi.topic_id = top.topic_id
317 and tpi.integration_id = int.integration_id
318 and top.topic_key = X_TOPIC_KEY
319 and int.integration_key = X_INTEGRATION_KEY;
320 --
321 X_CURRENT_OWNER NUMBER;
322 X_CURRENT_LAST_UPDATE_DATE HR_KI_TOPIC_INTEGRATIONS.last_update_date%TYPE;
323 --
324 begin
325 --
326 -- added for 5354277
327 hr_general.g_data_migrator_mode := 'Y';
328 --
329 -- validate parent_hierarchy_key
330 --
331 validate_keys(
332 X_TOPIC_KEY => X_TOPIC_KEY
333 ,X_INTEGRATION_KEY => X_INTEGRATION_KEY
334 ,X_TOPIC_ID => l_topic_id
335 ,X_INTEGRATION_ID => l_integration_id
336 );
337 -- Translate owner to file_last_updated_by
338 l_last_updated_by := fnd_load_util.owner_id(X_OWNER);
339 l_created_by := fnd_load_util.owner_id(X_OWNER);
340 -- Translate char last_update_date to date
341 l_last_update_date := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD hh24:mi:ss'), sysdate);
342 --
343 -- Update or insert row as appropriate
344 --
345 OPEN C_APPL;
346 FETCH C_APPL INTO l_topic_integrations_id,l_object_version_number;
347 --
348 if C_APPL%notfound then
349 close C_APPL;
350 -- Row does not exists in the database.
351 INSERT_ROW
352 (X_ROWID => l_rowid
353 ,X_TOPIC_INTEGRATIONS_ID => l_topic_integrations_id
354 ,X_TOPIC_ID => l_topic_id
355 ,X_INTEGRATION_ID => l_integration_id
356 ,X_PARAM_NAME1 => X_PARAM_NAME1
357 ,X_PARAM_VALUE1 => X_PARAM_VALUE1
358 ,X_PARAM_NAME2 => X_PARAM_NAME2
359 ,X_PARAM_VALUE2 => X_PARAM_VALUE2
360 ,X_PARAM_NAME3 => X_PARAM_NAME3
361 ,X_PARAM_VALUE3 => X_PARAM_VALUE3
362 ,X_PARAM_NAME4 => X_PARAM_NAME4
363 ,X_PARAM_VALUE4 => X_PARAM_VALUE4
364 ,X_PARAM_NAME5 => X_PARAM_NAME5
365 ,X_PARAM_VALUE5 => X_PARAM_VALUE5
366 ,X_PARAM_NAME6 => X_PARAM_NAME6
367 ,X_PARAM_VALUE6 => X_PARAM_VALUE6
368 ,X_PARAM_NAME7 => X_PARAM_NAME7
369 ,X_PARAM_VALUE7 => X_PARAM_VALUE7
370 ,X_PARAM_NAME8 => X_PARAM_NAME8
371 ,X_PARAM_VALUE8 => X_PARAM_VALUE8
372 ,X_PARAM_NAME9 => X_PARAM_NAME9
373 ,X_PARAM_VALUE9 => X_PARAM_VALUE9
374 ,X_PARAM_NAME10 => X_PARAM_NAME10
375 ,X_PARAM_VALUE10 => X_PARAM_VALUE10
376 ,X_CREATED_BY => l_created_by
377 ,X_CREATION_DATE => l_creation_date
378 ,X_LAST_UPDATE_DATE => l_last_update_date
379 ,X_LAST_UPDATED_BY => l_last_updated_by
380 ,X_LAST_UPDATE_LOGIN => l_last_update_login
381 );
382 else
383 close C_APPL;
384 SELECT tis.LAST_UPDATED_BY,
385 tis.LAST_UPDATE_DATE
386 INTO X_CURRENT_OWNER,
387 X_CURRENT_LAST_UPDATE_DATE
388 FROM HR_KI_TOPIC_INTEGRATIONS tis
389 WHERE tis.topic_id = l_topic_id
390 AND tis.integration_id = l_integration_id;
391 --
392 if (FND_LOAD_UTIL.UPLOAD_TEST
393 (P_FILE_ID => l_last_updated_by
394 ,P_FILE_LUD => l_last_update_date
395 ,P_DB_ID => X_CURRENT_OWNER
396 ,P_DB_LUD => to_date(X_CURRENT_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS')
397 ,P_CUSTOM_MODE => X_CUSTOM_MODE
398 )
399 ) then
400 UPDATE_ROW
401 (X_TOPIC_INTEGRATIONS_ID => l_topic_integrations_id
402 ,X_TOPIC_ID => l_topic_id
403 ,X_INTEGRATION_ID => l_integration_id
404 ,X_PARAM_NAME1 => X_PARAM_NAME1
405 ,X_PARAM_VALUE1 => X_PARAM_VALUE1
406 ,X_PARAM_NAME2 => X_PARAM_NAME2
407 ,X_PARAM_VALUE2 => X_PARAM_VALUE2
408 ,X_PARAM_NAME3 => X_PARAM_NAME3
409 ,X_PARAM_VALUE3 => X_PARAM_VALUE3
410 ,X_PARAM_NAME4 => X_PARAM_NAME4
411 ,X_PARAM_VALUE4 => X_PARAM_VALUE4
412 ,X_PARAM_NAME5 => X_PARAM_NAME5
413 ,X_PARAM_VALUE5 => X_PARAM_VALUE5
414 ,X_PARAM_NAME6 => X_PARAM_NAME6
415 ,X_PARAM_VALUE6 => X_PARAM_VALUE6
416 ,X_PARAM_NAME7 => X_PARAM_NAME7
417 ,X_PARAM_VALUE7 => X_PARAM_VALUE7
418 ,X_PARAM_NAME8 => X_PARAM_NAME8
419 ,X_PARAM_VALUE8 => X_PARAM_VALUE8
420 ,X_PARAM_NAME9 => X_PARAM_NAME9
421 ,X_PARAM_VALUE9 => X_PARAM_VALUE9
422 ,X_PARAM_NAME10 => X_PARAM_NAME10
423 ,X_PARAM_VALUE10 => X_PARAM_VALUE10
424 ,X_CREATED_BY => l_created_by
425 ,X_CREATION_DATE => l_creation_date
426 ,X_LAST_UPDATE_DATE => l_last_update_date
427 ,X_LAST_UPDATED_BY => l_last_updated_by
428 ,X_LAST_UPDATE_LOGIN => l_last_update_login
429 ,X_OBJECT_VERSION_NUMBER => l_object_version_number + 1
430 );
431 end if;
432 end if;
433 --
434 end LOAD_ROW;
435 END HR_KI_TIS_LOAD_API;