[Home] [Help]
PACKAGE BODY: APPS.GHR_POSITION_EXTRA_INFO_API
Source
1 Package Body ghr_position_extra_info_api as
2 /* $Header: ghpoiapi.pkb 115.6 2003/01/30 16:32:12 asubrahm ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' ghr_position_extra_info_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |---------------------< create_position_extra_info >---------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_position_extra_info
13 (p_validate in boolean default false
14 ,p_position_id in number
15 ,p_information_type in varchar2
16 ,p_effective_date in date
17 ,p_poei_attribute_category in varchar2 default null
18 ,p_poei_attribute1 in varchar2 default null
19 ,p_poei_attribute2 in varchar2 default null
20 ,p_poei_attribute3 in varchar2 default null
21 ,p_poei_attribute4 in varchar2 default null
22 ,p_poei_attribute5 in varchar2 default null
23 ,p_poei_attribute6 in varchar2 default null
24 ,p_poei_attribute7 in varchar2 default null
25 ,p_poei_attribute8 in varchar2 default null
26 ,p_poei_attribute9 in varchar2 default null
27 ,p_poei_attribute10 in varchar2 default null
28 ,p_poei_attribute11 in varchar2 default null
29 ,p_poei_attribute12 in varchar2 default null
30 ,p_poei_attribute13 in varchar2 default null
31 ,p_poei_attribute14 in varchar2 default null
32 ,p_poei_attribute15 in varchar2 default null
33 ,p_poei_attribute16 in varchar2 default null
34 ,p_poei_attribute17 in varchar2 default null
35 ,p_poei_attribute18 in varchar2 default null
36 ,p_poei_attribute19 in varchar2 default null
37 ,p_poei_attribute20 in varchar2 default null
38 ,p_poei_information_category in varchar2 default null
39 ,p_poei_information1 in varchar2 default null
40 ,p_poei_information2 in varchar2 default null
41 ,p_poei_information3 in varchar2 default null
42 ,p_poei_information4 in varchar2 default null
43 ,p_poei_information5 in varchar2 default null
44 ,p_poei_information6 in varchar2 default null
45 ,p_poei_information7 in varchar2 default null
46 ,p_poei_information8 in varchar2 default null
47 ,p_poei_information9 in varchar2 default null
48 ,p_poei_information10 in varchar2 default null
49 ,p_poei_information11 in varchar2 default null
50 ,p_poei_information12 in varchar2 default null
51 ,p_poei_information13 in varchar2 default null
52 ,p_poei_information14 in varchar2 default null
53 ,p_poei_information15 in varchar2 default null
54 ,p_poei_information16 in varchar2 default null
55 ,p_poei_information17 in varchar2 default null
56 ,p_poei_information18 in varchar2 default null
57 ,p_poei_information19 in varchar2 default null
58 ,p_poei_information20 in varchar2 default null
59 ,p_poei_information21 in varchar2 default null
60 ,p_poei_information22 in varchar2 default null
61 ,p_poei_information23 in varchar2 default null
62 ,p_poei_information24 in varchar2 default null
63 ,p_poei_information25 in varchar2 default null
64 ,p_poei_information26 in varchar2 default null
65 ,p_poei_information27 in varchar2 default null
66 ,p_poei_information28 in varchar2 default null
67 ,p_poei_information29 in varchar2 default null
68 ,p_poei_information30 in varchar2 default null
69 ,p_position_extra_info_id out nocopy number
70 ,p_object_version_number out nocopy number
71 ) is
72 --
73 -- Declare cursors and local variables
74 --
75 l_proc varchar2(72) := g_package||'create_position_extra_info';
76 l_object_version_number per_position_extra_info.object_version_number%type;
77 l_position_extra_info_id per_position_extra_info.position_extra_info_id%type;
78 l_bg_id per_positions.business_group_id%type;
79 --
80
81 cursor c_bg is
82 SELECT business_group_id
83 FROM per_positions
84 WHERE position_id = p_position_id;
85
86 begin
87 hr_utility.set_location('Entering:'|| l_proc, 5);
88 --
89 -- Issue a savepoint if operating in validation only mode.
90 --
91 savepoint ghr_create_position_extra_info;
92 --
93 --
94 --
95 -- set session variables
96 ghr_session.set_session_var_for_core
97 (p_effective_date => p_effective_date
98 );
99
100 -----------------------------------------------------------------------------------------
101 -- To fix issue with position valid grade value set using $PROFILES$.PER_BUSINESS_GROUP_ID
102 -- Note: PER_BUSINESS_GROUP_ID appears to be defaulted to zero so we cannot test it.
103 -----------------------------------------------------------------------------------------
104 --fnd_profile.get('PER_BUSINESS_GROUP_ID',l_bg_id);
105 --IF l_bg_id IS NULL THEN
106 FOR c_bg_id in c_bg LOOP
107 l_bg_id := c_bg_id.business_group_id;
108 END LOOP;
109 fnd_profile.put('PER_BUSINESS_GROUP_ID',l_bg_id);
110 --END IF;
111 --
112 hr_position_extra_info_api.create_position_extra_info
113 (
114 p_position_id => p_position_id ,
115 p_information_type => p_information_type ,
116 p_poei_attribute_category => p_poei_attribute_category ,
117 p_poei_attribute1 => p_poei_attribute1 ,
118 p_poei_attribute2 => p_poei_attribute2 ,
119 p_poei_attribute3 => p_poei_attribute3 ,
120 p_poei_attribute4 => p_poei_attribute4 ,
121 p_poei_attribute5 => p_poei_attribute5 ,
122 p_poei_attribute6 => p_poei_attribute6 ,
123 p_poei_attribute7 => p_poei_attribute7 ,
124 p_poei_attribute8 => p_poei_attribute8 ,
125 p_poei_attribute9 => p_poei_attribute9 ,
126 p_poei_attribute10 => p_poei_attribute10 ,
127 p_poei_attribute11 => p_poei_attribute11 ,
128 p_poei_attribute12 => p_poei_attribute12 ,
129 p_poei_attribute13 => p_poei_attribute13 ,
130 p_poei_attribute14 => p_poei_attribute14 ,
131 p_poei_attribute15 => p_poei_attribute15 ,
132 p_poei_attribute16 => p_poei_attribute16 ,
133 p_poei_attribute17 => p_poei_attribute17 ,
134 p_poei_attribute18 => p_poei_attribute18 ,
135 p_poei_attribute19 => p_poei_attribute19 ,
136 p_poei_attribute20 => p_poei_attribute20 ,
137 p_poei_information_category => p_poei_information_category ,
138 p_poei_information1 => p_poei_information1 ,
139 p_poei_information2 => p_poei_information2 ,
140 p_poei_information3 => p_poei_information3 ,
141 p_poei_information4 => p_poei_information4 ,
142 p_poei_information5 => p_poei_information5 ,
143 p_poei_information6 => p_poei_information6 ,
144 p_poei_information7 => p_poei_information7 ,
145 p_poei_information8 => p_poei_information8 ,
146 p_poei_information9 => p_poei_information9 ,
147 p_poei_information10 => p_poei_information10 ,
148 p_poei_information11 => p_poei_information11 ,
149 p_poei_information12 => p_poei_information12 ,
150 p_poei_information13 => p_poei_information13 ,
151 p_poei_information14 => p_poei_information14 ,
152 p_poei_information15 => p_poei_information15 ,
153 p_poei_information16 => p_poei_information16 ,
154 p_poei_information17 => p_poei_information17 ,
155 p_poei_information18 => p_poei_information18 ,
156 p_poei_information19 => p_poei_information19 ,
157 p_poei_information20 => p_poei_information20 ,
158 p_poei_information21 => p_poei_information21 ,
159 p_poei_information22 => p_poei_information22 ,
160 p_poei_information23 => p_poei_information23 ,
161 p_poei_information24 => p_poei_information24 ,
162 p_poei_information25 => p_poei_information25 ,
163 p_poei_information26 => p_poei_information26 ,
164 p_poei_information27 => p_poei_information27 ,
165 p_poei_information28 => p_poei_information28 ,
166 p_poei_information29 => p_poei_information29 ,
167 p_poei_information30 => p_poei_information30 ,
168 p_position_extra_info_id => l_position_extra_info_id ,
169 p_object_version_number => l_object_version_number
170 );
171 --
172
173 hr_utility.set_location(l_proc, 7);
174 --
175 ghr_history_api.post_update_process;
176
177 -- When in validation only mode raise the Validate_Enabled exception
178 --
179
180 if p_validate then
181 raise hr_api.validate_enabled;
182 end if;
183 --
184 p_object_version_number := l_object_version_number;
185 p_position_extra_info_id := l_position_extra_info_id;
186
187 hr_utility.set_location(' Leaving:'||l_proc, 11);
188 exception
189 when hr_api.validate_enabled then
190 --
191 -- As the Validate_Enabled exception has been raised
192 -- we must rollback to the savepoint
193 --
194 ROLLBACK TO ghr_create_position_extra_info;
195 --
196 -- Only set output warning arguments
197 -- (Any key or derived arguments must be set to null
198 -- when validation only mode is being used.)
199 --
200 p_position_extra_info_id := null;
201 p_object_version_number := null;
202 --
203 hr_utility.set_location(' Leaving:'||l_proc, 12);
204 when others then
205 ROLLBACK TO ghr_create_position_extra_info;
206 --
207 -- Reset IN OUT parameters and set OUT parameters
208 --
209 p_position_extra_info_id := null;
210 p_object_version_number := null;
211 raise;
212 end create_position_extra_info;
213 --
214 -- ----------------------------------------------------------------------------
215 -- |---------------------< update_position_extra_info >---------------------|
216 -- ----------------------------------------------------------------------------
217 --
218 procedure update_position_extra_info
219 (p_validate in boolean default false
220 ,p_position_extra_info_id in number
221 ,p_object_version_number in out nocopy number
222 ,p_effective_date in date
223 ,p_poei_attribute_category in varchar2 default hr_api.g_varchar2
224 ,p_poei_attribute1 in varchar2 default hr_api.g_varchar2
225 ,p_poei_attribute2 in varchar2 default hr_api.g_varchar2
226 ,p_poei_attribute3 in varchar2 default hr_api.g_varchar2
227 ,p_poei_attribute4 in varchar2 default hr_api.g_varchar2
228 ,p_poei_attribute5 in varchar2 default hr_api.g_varchar2
229 ,p_poei_attribute6 in varchar2 default hr_api.g_varchar2
230 ,p_poei_attribute7 in varchar2 default hr_api.g_varchar2
231 ,p_poei_attribute8 in varchar2 default hr_api.g_varchar2
232 ,p_poei_attribute9 in varchar2 default hr_api.g_varchar2
233 ,p_poei_attribute10 in varchar2 default hr_api.g_varchar2
234 ,p_poei_attribute11 in varchar2 default hr_api.g_varchar2
235 ,p_poei_attribute12 in varchar2 default hr_api.g_varchar2
236 ,p_poei_attribute13 in varchar2 default hr_api.g_varchar2
237 ,p_poei_attribute14 in varchar2 default hr_api.g_varchar2
238 ,p_poei_attribute15 in varchar2 default hr_api.g_varchar2
239 ,p_poei_attribute16 in varchar2 default hr_api.g_varchar2
240 ,p_poei_attribute17 in varchar2 default hr_api.g_varchar2
241 ,p_poei_attribute18 in varchar2 default hr_api.g_varchar2
242 ,p_poei_attribute19 in varchar2 default hr_api.g_varchar2
243 ,p_poei_attribute20 in varchar2 default hr_api.g_varchar2
244 ,p_poei_information_category in varchar2 default hr_api.g_varchar2
245 ,p_poei_information1 in varchar2 default hr_api.g_varchar2
246 ,p_poei_information2 in varchar2 default hr_api.g_varchar2
247 ,p_poei_information3 in varchar2 default hr_api.g_varchar2
248 ,p_poei_information4 in varchar2 default hr_api.g_varchar2
249 ,p_poei_information5 in varchar2 default hr_api.g_varchar2
250 ,p_poei_information6 in varchar2 default hr_api.g_varchar2
251 ,p_poei_information7 in varchar2 default hr_api.g_varchar2
252 ,p_poei_information8 in varchar2 default hr_api.g_varchar2
253 ,p_poei_information9 in varchar2 default hr_api.g_varchar2
254 ,p_poei_information10 in varchar2 default hr_api.g_varchar2
255 ,p_poei_information11 in varchar2 default hr_api.g_varchar2
256 ,p_poei_information12 in varchar2 default hr_api.g_varchar2
257 ,p_poei_information13 in varchar2 default hr_api.g_varchar2
258 ,p_poei_information14 in varchar2 default hr_api.g_varchar2
259 ,p_poei_information15 in varchar2 default hr_api.g_varchar2
260 ,p_poei_information16 in varchar2 default hr_api.g_varchar2
261 ,p_poei_information17 in varchar2 default hr_api.g_varchar2
262 ,p_poei_information18 in varchar2 default hr_api.g_varchar2
263 ,p_poei_information19 in varchar2 default hr_api.g_varchar2
264 ,p_poei_information20 in varchar2 default hr_api.g_varchar2
265 ,p_poei_information21 in varchar2 default hr_api.g_varchar2
266 ,p_poei_information22 in varchar2 default hr_api.g_varchar2
267 ,p_poei_information23 in varchar2 default hr_api.g_varchar2
268 ,p_poei_information24 in varchar2 default hr_api.g_varchar2
269 ,p_poei_information25 in varchar2 default hr_api.g_varchar2
270 ,p_poei_information26 in varchar2 default hr_api.g_varchar2
271 ,p_poei_information27 in varchar2 default hr_api.g_varchar2
272 ,p_poei_information28 in varchar2 default hr_api.g_varchar2
273 ,p_poei_information29 in varchar2 default hr_api.g_varchar2
274 ,p_poei_information30 in varchar2 default hr_api.g_varchar2
275 ) is
276 --
277 -- Declare cursors and local variables
278 --
279 l_proc varchar2(72) := g_package||'update_position_extra_info';
280 l_object_version_number per_position_extra_info.object_version_number%TYPE;
281 l_bg_id per_positions.business_group_id%type;
282 --
283
284 cursor c_bg is
285 SELECT pos.business_group_id
286 FROM per_positions pos, per_position_extra_info poi
287 WHERE pos.position_id = poi.position_id
288 AND poi.position_extra_info_id = p_position_extra_info_id;
289
290 --
291 begin
292 hr_utility.set_location('Entering:'|| l_proc, 5);
293 --
294 -- Issue a savepoint if operating in validation only mode.
295 --
296 savepoint ghr_update_position_extra_info;
297 --
298
299 l_object_version_number := p_object_version_number;
300
301 ghr_session.set_session_var_for_core
302 (p_effective_date => p_effective_date
303 );
304
305
306 -----------------------------------------------------------------------------------------
307 -- To fix issue with position valid grade value set using $PROFILE$.PER_BUSINESS_GROUP_ID
308 -- Note: PER_BUSINESS_GROUP_ID appears to be defaulted to zero so we cannot test it.
309 -----------------------------------------------------------------------------------------
310 --fnd_profile.get('PER_BUSINESS_GROUP_ID',l_bg_id);
311 --IF l_bg_id IS NULL THEN
312 FOR c_bg_id in c_bg LOOP
313 l_bg_id := c_bg_id.business_group_id;
314 END LOOP;
315 fnd_profile.put('PER_BUSINESS_GROUP_ID',l_bg_id);
316 --END IF;
317
318
319 hr_position_extra_info_api.update_position_extra_info
320 (
324 p_poei_attribute2 => p_poei_attribute2 ,
321 p_position_extra_info_id => p_position_extra_info_id ,
322 p_poei_attribute_category => p_poei_attribute_category ,
323 p_poei_attribute1 => p_poei_attribute1 ,
325 p_poei_attribute3 => p_poei_attribute3 ,
326 p_poei_attribute4 => p_poei_attribute4 ,
327 p_poei_attribute5 => p_poei_attribute5 ,
328 p_poei_attribute6 => p_poei_attribute6 ,
329 p_poei_attribute7 => p_poei_attribute7 ,
330 p_poei_attribute8 => p_poei_attribute8 ,
331 p_poei_attribute9 => p_poei_attribute9 ,
332 p_poei_attribute10 => p_poei_attribute10 ,
333 p_poei_attribute11 => p_poei_attribute11 ,
334 p_poei_attribute12 => p_poei_attribute12 ,
335 p_poei_attribute13 => p_poei_attribute13 ,
336 p_poei_attribute14 => p_poei_attribute14 ,
337 p_poei_attribute15 => p_poei_attribute15 ,
338 p_poei_attribute16 => p_poei_attribute16 ,
339 p_poei_attribute17 => p_poei_attribute17 ,
340 p_poei_attribute18 => p_poei_attribute18 ,
341 p_poei_attribute19 => p_poei_attribute19 ,
342 p_poei_attribute20 => p_poei_attribute20 ,
343 p_poei_information_category => p_poei_information_category ,
344 p_poei_information1 => p_poei_information1 ,
345 p_poei_information2 => p_poei_information2 ,
346 p_poei_information3 => p_poei_information3 ,
347 p_poei_information4 => p_poei_information4 ,
348 p_poei_information5 => p_poei_information5 ,
349 p_poei_information6 => p_poei_information6 ,
350 p_poei_information7 => p_poei_information7 ,
351 p_poei_information8 => p_poei_information8 ,
352 p_poei_information9 => p_poei_information9 ,
353 p_poei_information10 => p_poei_information10 ,
354 p_poei_information11 => p_poei_information11 ,
355 p_poei_information12 => p_poei_information12 ,
356 p_poei_information13 => p_poei_information13 ,
357 p_poei_information14 => p_poei_information14 ,
358 p_poei_information15 => p_poei_information15 ,
359 p_poei_information16 => p_poei_information16 ,
360 p_poei_information17 => p_poei_information17 ,
361 p_poei_information18 => p_poei_information18 ,
362 p_poei_information19 => p_poei_information19 ,
363 p_poei_information20 => p_poei_information20 ,
364 p_poei_information21 => p_poei_information21 ,
365 p_poei_information22 => p_poei_information22 ,
366 p_poei_information23 => p_poei_information23 ,
367 p_poei_information24 => p_poei_information24 ,
368 p_poei_information25 => p_poei_information25 ,
369 p_poei_information26 => p_poei_information26 ,
370 p_poei_information27 => p_poei_information27 ,
371 p_poei_information28 => p_poei_information28 ,
372 p_poei_information29 => p_poei_information29 ,
373 p_poei_information30 => p_poei_information30 ,
374 p_object_version_number => p_object_version_number
375 );
376 --
377 hr_utility.set_location(l_proc, 7);
378 --
379 ghr_history_api.post_update_process;
380
381 --
382 --
383 if p_validate then
384 raise hr_api.validate_enabled;
385 end if;
386
387 --
388 hr_utility.set_location(' Leaving:'||l_proc, 11);
389 exception
390 when hr_api.validate_enabled then
391 --
392 -- As the Validate_Enabled exception has been raised
393 -- we must rollback to the savepoint
394 --
395 ROLLBACK TO ghr_update_position_extra_info;
396 --
397 -- Only set output warning arguments
398 -- (Any key or derived arguments must be set to null
399 -- when validation only mode is being used.)
400 --
401 p_object_version_number := l_object_version_number;
402 --
403 hr_utility.set_location(' Leaving:'||l_proc, 12);
404 when others then
405 ROLLBACK TO ghr_update_position_extra_info;
406 --
407 -- Reset IN OUT parameters and set OUT parameters
408 --
409 p_object_version_number := l_object_version_number;
410 raise;
411
412 end update_position_extra_info;
413 --
414 --
415 end ghr_position_extra_info_api;