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