[Home] [Help]
PACKAGE BODY: APPS.HR_KI_HRC_LOAD_API
Source
1 Package Body HR_KI_HRC_LOAD_API as
2 /* $Header: hrkihrcl.pkb 120.1 2006/06/27 16:03:09 avarri noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(31) := 'HR_KI_HRC_LOAD_API';
7 --
8
9
10 procedure UPDATE_ROW (
11 X_HIERARCHY_ID in NUMBER,
12 X_PARENT_HIERARCHY_ID in NUMBER,
13 X_NAME in VARCHAR2,
14 X_DESCRIPTION in VARCHAR2,
15 X_LAST_UPDATE_DATE in DATE,
16 X_LAST_UPDATED_BY in NUMBER,
17 X_LAST_UPDATE_LOGIN in NUMBER,
18 X_OBJECT_VERSION_NUMBER in NUMBER
19
20 ) is
21
22 begin
23
24
25 update HR_KI_HIERARCHIES
26 set
27 PARENT_HIERARCHY_ID = X_PARENT_HIERARCHY_ID,
28 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
29 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
30 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
31 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER+1
32 where HIERARCHY_ID = X_HIERARCHY_ID;
33
34
35 update HR_KI_HIERARCHIES_TL set
36 NAME = X_NAME,
37 DESCRIPTION = X_DESCRIPTION,
38 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
39 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
40 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
41 SOURCE_LANG = userenv('LANG')
42 where HIERARCHY_ID = X_HIERARCHY_ID
43 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
44
45 if (sql%notfound) then
46
47 insert into HR_KI_HIERARCHIES_TL (
48 HIERARCHY_ID,
49 NAME,
50 DESCRIPTION,
51 CREATED_BY,
52 CREATION_DATE,
53 LAST_UPDATED_BY,
54 LAST_UPDATE_DATE,
55 LAST_UPDATE_LOGIN,
56 LANGUAGE,
57 SOURCE_LANG
58 ) select
59 X_HIERARCHY_ID,
60 X_NAME,
61 X_DESCRIPTION,
62 1 ,
63 SYSDATE,
64 X_LAST_UPDATED_BY,
65 X_LAST_UPDATE_DATE,
66 X_LAST_UPDATE_LOGIN,
67 L.LANGUAGE_CODE,
68 userenv('LANG')
69 from FND_LANGUAGES L
70 where L.INSTALLED_FLAG in ('I', 'B')
71 and not exists
72 (select NULL
73 from HR_KI_HIERARCHIES_TL T
74 where T.hierarchy_id = X_hierarchy_id
75 and T.LANGUAGE = L.LANGUAGE_CODE);
76
77 end if;
78
79 end UPDATE_ROW;
80
81
82 procedure INSERT_ROW (
83 X_ROWID in out nocopy VARCHAR2,
84 X_HIERARCHY_ID in out nocopy NUMBER,
85 X_HIERARCHY_KEY in VARCHAR2,
86 X_PARENT_HIERARCHY_ID in NUMBER,
87 X_NAME in varchar2,
88 X_DESCRIPTION in VARCHAR2,
89 X_CREATED_BY in NUMBER,
90 X_CREATION_DATE in DATE,
91 X_LAST_UPDATE_DATE in DATE,
92 X_LAST_UPDATED_BY in NUMBER,
93 X_LAST_UPDATE_LOGIN in NUMBER
94
95 ) is
96
97 cursor C is select ROWID from HR_KI_HIERARCHIES
98 where hierarchy_id = X_HIERARCHY_ID;
99
100 begin
101
102 select HR_KI_HIERARCHIES_S.NEXTVAL into X_HIERARCHY_ID from sys.dual;
103
104 insert into HR_KI_HIERARCHIES (
105 HIERARCHY_ID,
106 HIERARCHY_KEY,
107 PARENT_HIERARCHY_ID,
108 CREATION_DATE,
109 CREATED_BY,
110 LAST_UPDATE_DATE,
111 LAST_UPDATED_BY,
112 LAST_UPDATE_LOGIN,
113 OBJECT_VERSION_NUMBER
114 ) values (
115 X_HIERARCHY_ID,
116 X_HIERARCHY_KEY,
117 X_PARENT_HIERARCHY_ID,
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
126
127 insert into HR_KI_HIERARCHIES_TL (
128 HIERARCHY_ID,
129 NAME,
130 DESCRIPTION,
131 CREATED_BY,
132 CREATION_DATE,
133 LAST_UPDATED_BY,
134 LAST_UPDATE_DATE,
135 LAST_UPDATE_LOGIN,
136 LANGUAGE,
137 SOURCE_LANG
138 ) select
139 X_HIERARCHY_ID,
140 X_NAME,
141 X_DESCRIPTION,
142 X_CREATED_BY,
143 X_CREATION_DATE,
144 X_LAST_UPDATED_BY,
145 X_LAST_UPDATE_DATE,
146 X_LAST_UPDATE_LOGIN,
147 L.LANGUAGE_CODE,
148 userenv('LANG')
149 from FND_LANGUAGES L
150 where L.INSTALLED_FLAG in ('I', 'B')
151 and not exists
152 (select NULL
153 from HR_KI_HIERARCHIES_TL T
154 where T.hierarchy_id = X_hierarchy_id
155 and T.LANGUAGE = L.LANGUAGE_CODE);
156
157 open c;
158 fetch c into X_ROWID;
159 if (c%notfound) then
160 close c;
161 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
162 hr_utility.set_message_token('PROCEDURE',
163 'HR_KI_HIERARCHIES.insert_row');
164 hr_utility.set_message_token('STEP','1');
165 hr_utility.raise_error;
166 end if;
167 close c;
168
169
170 end INSERT_ROW;
171
172 procedure validate_parent_key
173 (
174 X_PARENT_HIERARCHY_KEY in VARCHAR2,
175 X_PARENT_HIERARCHY_ID in out nocopy number
176 )
177 is
178
179 l_proc VARCHAR2(61) := 'HR_KI_HRC_LOAD_API.VALIDATE_PARENT_KEY';
180 l_hierarchy_id HR_KI_HIERARCHIES.hierarchy_id%TYPE;
181
182 CURSOR C_VAL IS
183 select hierarchy_id
184 from HR_KI_HIERARCHIES
185 where upper(hierarchy_key) = upper(X_PARENT_HIERARCHY_KEY);
186
187 begin
188
189 --For Global hierarchy_node parent_hierarchy_key will be null
190 --Hence at the time of downloading,we will be
191 --selecting fnd_load_util.null_value for Global Node,Here we need
192 --to ignore validation for this value
193
194 if (X_PARENT_HIERARCHY_KEY <> substrb(fnd_load_util.null_value,1,30) ) then
195 open C_VAL;
196 fetch C_VAL into X_PARENT_HIERARCHY_ID;
197
198 If C_VAL%NOTFOUND then
199 close C_VAL;
200 fnd_message.set_name( 'PER','PER_449916_HRC_PARNT_ID_ABSNT');
201 fnd_message.raise_error;
202 End If;
203
204 close C_VAL;
205 end if;
206
207
208 end validate_parent_key;
209
210 procedure LOAD_ROW
211 (
212 X_HIERARCHY_KEY in VARCHAR2,
213 X_PARENT_HIERARCHY_KEY in VARCHAR2,
214 X_NAME in VARCHAR2,
215 X_DESCRIPTION in VARCHAR2,
216 X_LAST_UPDATE_DATE in VARCHAR2,
217 X_CUSTOM_MODE in VARCHAR2,
218 X_OWNER in VARCHAR2
219
220 )
221 is
222 l_proc VARCHAR2(31) := 'HR_KI_HRC_LOAD_API.LOAD_ROW';
223 l_rowid rowid;
224 l_created_by HR_KI_HIERARCHIES.created_by%TYPE := 0;
225 l_creation_date HR_KI_HIERARCHIES.creation_date%TYPE := SYSDATE;
226 l_last_update_date HR_KI_HIERARCHIES.last_update_date%TYPE := SYSDATE;
227 l_last_updated_by HR_KI_HIERARCHIES.last_updated_by%TYPE := 0;
228 l_last_update_login HR_KI_HIERARCHIES.last_update_login%TYPE := 0;
229 l_hierarchy_id HR_KI_HIERARCHIES.hierarchy_id%TYPE;
230 l_parent_hierarchy_id HR_KI_HIERARCHIES.parent_hierarchy_id%TYPE;
231 l_object_version_number HR_KI_HIERARCHIES.object_version_number%TYPE;
232
233 db_luby number; -- entity owner in db
234 db_ludate date; -- entity update date in db
235
236
237
238 CURSOR C_APPL IS
239 select hierarchy_id,object_version_number
240 from HR_KI_HIERARCHIES
241 where upper(hierarchy_key) = upper(X_HIERARCHY_KEY);
242
243 begin
244 --
245 -- added for 5354277
246 hr_general.g_data_migrator_mode := 'Y';
247 --
248 --validate parent_hierarchy_key
249 validate_parent_key(
250 X_PARENT_HIERARCHY_KEY => X_PARENT_HIERARCHY_KEY
251 ,X_PARENT_HIERARCHY_ID => l_parent_hierarchy_id
252 );
253
254 -- Translate owner to file_last_updated_by
255 l_last_updated_by := fnd_load_util.owner_id(X_OWNER);
256 l_created_by := fnd_load_util.owner_id(X_OWNER);
257
258 -- Translate char last_update_date to date
259 l_last_update_date := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD hh24:mi:ss'), sysdate);
260
261
262 -- Update or insert row as appropriate
263
264 OPEN C_APPL;
265 FETCH C_APPL INTO l_hierarchy_id,l_object_version_number;
266
267
268 if C_APPL%notfound then
269 close C_APPL;
270 INSERT_ROW
271 (
272 X_ROWID => l_rowid
273 ,X_HIERARCHY_ID => l_hierarchy_id
274 ,X_HIERARCHY_KEY => X_HIERARCHY_KEY
275 ,X_PARENT_HIERARCHY_ID => l_parent_hierarchy_id
276 ,X_NAME => X_NAME
277 ,X_DESCRIPTION => X_DESCRIPTION
278 ,X_CREATED_BY => l_created_by
279 ,X_CREATION_DATE => l_creation_date
280 ,X_LAST_UPDATE_DATE => l_last_update_date
281 ,X_LAST_UPDATED_BY => l_last_updated_by
282 ,X_LAST_UPDATE_LOGIN => l_last_update_login
283 );
284
285
286 else
287 close C_APPL;
288 --start of update part
289 select LAST_UPDATED_BY, LAST_UPDATE_DATE
290 into db_luby, db_ludate
291 from HR_KI_HIERARCHIES
292 where hierarchy_id = l_hierarchy_id;
293
294
295 if (fnd_load_util.upload_test(l_last_updated_by, l_last_update_date, db_luby,
296 db_ludate, X_CUSTOM_MODE)) then
297
298 UPDATE_ROW
299 (
300 X_HIERARCHY_ID => l_hierarchy_id
301 ,X_PARENT_HIERARCHY_ID => l_parent_hierarchy_id
302 ,X_NAME => X_NAME
303 ,X_DESCRIPTION => X_DESCRIPTION
304 ,X_LAST_UPDATE_DATE => l_last_update_date
305 ,X_LAST_UPDATED_BY => l_last_updated_by
306 ,X_LAST_UPDATE_LOGIN => l_last_update_login
307 ,X_OBJECT_VERSION_NUMBER => l_object_version_number
308 );
309
310 end if;
311
312 end if;
313
314 --
315 end LOAD_ROW;
316
317 procedure TRANSLATE_ROW
318 (
319 X_HIERARCHY_KEY in varchar2,
320 X_NAME in VARCHAR2,
321 X_DESCRIPTION in VARCHAR2,
322 X_OWNER in varchar2,
323 X_CUSTOM_MODE in varchar2,
324 X_LAST_UPDATE_DATE in varchar2
325 )
326 is
327 l_hierarchy_id HR_KI_HIERARCHIES.hierarchy_id%TYPE;
328
329 f_luby number; -- entity owner in file
330 f_ludate date; -- entity update date in file
331 db_luby number; -- entity owner in db
332 db_ludate date; -- entity update date in db
333
334
335 begin
336 --
337 -- added for 5354277
338 hr_general.g_data_migrator_mode := 'Y';
339 --
340 select hierarchy_id into l_hierarchy_id
341 from HR_KI_HIERARCHIES
342 where upper(hierarchy_key) = upper(X_HIERARCHY_KEY);
343
344
345 -- Translate owner to file_last_updated_by
346 f_luby := fnd_load_util.owner_id(X_OWNER);
347
348 -- Translate char last_update_date to date
349 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD hh24:mi:ss'), sysdate);
350
351 begin
352 select LAST_UPDATED_BY, LAST_UPDATE_DATE
353 into db_luby, db_ludate
354 from HR_KI_HIERARCHIES_TL
355 where
356 LANGUAGE = userenv('LANG')
357 and hierarchy_id = l_hierarchy_id;
358
359 -- Test for customization and version
360 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
361 db_ludate,X_CUSTOM_MODE)) then
362
363 UPDATE HR_KI_HIERARCHIES_TL
364 SET
365 NAME = X_NAME,
366 DESCRIPTION = X_DESCRIPTION,
367 LAST_UPDATE_DATE = f_ludate ,
368 LAST_UPDATED_BY = f_luby,
369 LAST_UPDATE_LOGIN = 0,
370 SOURCE_LANG = userenv('LANG')
371 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
372 and hierarchy_id = l_hierarchy_id;
373
374 end if;
375 exception
376 when no_data_found then
377 -- Do not insert missing translations, skip this row
378 null;
379 end;
380
381 end TRANSLATE_ROW;
382
383 END HR_KI_HRC_LOAD_API;