[Home] [Help]
PACKAGE BODY: APPS.GHR_DUTY_STATION_API
Source
1 Package Body ghr_duty_station_api as
2 /* $Header: ghdutapi.pkb 115.0 2003/06/19 08:42:19 vnarasim noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'ghr_duty_station_api.';
7
8 --
9 -- ----------------------------------------------------------------------------
10 -- |--------------------------< create_duty_station> >--------------------------|
11 -- ----------------------------------------------------------------------------
12 --
13
14 --Assumption : Create_duty_station is manipulated as create/update based on the existence of the record
15 --only where OVN = 1, else according to this logic a new rec. will be created.
16
17 procedure create_duty_station
18 (p_validate in boolean default false
19 ,p_duty_station_id OUT nocopy number
20 ,p_effective_start_date out nocopy date
21 ,p_effective_end_date out nocopy date
22 ,p_locality_pay_area_id in number
23 ,p_leo_pay_area_code in varchar2 default null
24 ,p_name in varchar2 default null
25 ,p_duty_station_code in varchar2
26 ,p_msa_code in varchar2 default null
27 ,p_cmsa_code in varchar2 default null
28 ,p_state_or_country_code in varchar2
29 ,p_county_code in varchar2 default null
30 ,p_is_duty_station in varchar2 default 'Y'
31 ,p_effective_date in date
32 ,p_object_version_number out nocopy number
33 ) is
34 --
35 l_duty_station_id ghr_duty_stations_f.duty_station_id%TYPE;
36 l_proc varchar2(72) := g_package||'create_duty_stations';
37 l_object_version_number ghr_duty_stations_f.object_version_number%TYPE;
38 l_effective_start_date date;
39 l_effective_end_date date;
40 --
41 begin
42 --
43 hr_utility.set_location('Entering:'|| l_proc, 10);
44 --
45 -- Issue a savepoint if operating in validation only mode
46 --
47 savepoint create_duty_stations;
48 --
49 hr_utility.set_location(l_proc, 20);
50 --
51 -- Process Logic
52 --
53 -- Truncate the time portion from all IN date parameters
54 --
55 l_effective_start_date := trunc(p_effective_start_date);
56 l_effective_end_date := trunc(p_effective_end_date);
57
58 --
59 begin
60 --
61 -- Start of API User Hook for the before hook of create_noac_remarks
62 --
63 ghr_duty_station_bk1.create_duty_station_b(
64 p_duty_station_id => p_duty_station_id
65 ,p_effective_start_date => p_effective_start_date
66 ,p_effective_end_date => p_effective_end_date
67 ,p_locality_pay_area_id => p_locality_pay_area_id
68 ,p_leo_pay_area_code => p_leo_pay_area_code
69 ,p_name => p_name
70 ,p_duty_station_code => p_duty_station_code
71 ,p_msa_code => p_msa_code
72 ,p_cmsa_code => p_cmsa_code
73 ,p_state_or_country_code => p_state_or_country_code
74 ,p_county_code => p_county_code
75 ,p_is_duty_station => p_is_duty_station
76 ,p_effective_date => trunc(p_effective_date)
77 ,p_object_version_number => p_object_version_number
78 );
79 exception
80 when hr_api.cannot_find_prog_unit then
81 hr_api.cannot_find_prog_unit_error
82 (
83 p_module_name => 'CREATE_duty_stations'
84 ,p_hook_type => 'BP'
85 );
86 --
87 -- End of API User Hook for the before hook of create_noac_remarks
88 --
89 end;
90 --
91 ghr_dut_ins.ins
92 (
93 p_duty_station_id => l_duty_station_id
94 ,p_effective_start_date => l_effective_start_date
95 ,p_effective_end_date => l_effective_end_date
96 ,p_locality_pay_area_id => p_locality_pay_area_id
97 ,p_leo_pay_area_code => p_leo_pay_area_code
98 ,p_name => p_name
99 ,p_duty_station_code => p_duty_station_code
100 ,p_msa_code => p_msa_code
101 ,p_cmsa_code => p_cmsa_code
102 ,p_state_or_country_code => p_state_or_country_code
103 ,p_county_code => p_county_code
104 ,p_is_duty_station => p_is_duty_station
105 , p_object_version_number => p_object_version_number
106 ,p_effective_date => trunc(p_effective_date)
107 );
108 --
109 begin
110 --
111 -- Start of API User Hook for the after hook of create_noac_remarks
112 --
113 ghr_duty_station_bk1.create_duty_station_a(
114 p_duty_station_id => l_duty_station_id
115 ,p_effective_start_date => l_effective_start_date
116 ,p_effective_end_date => l_effective_end_date
117 ,p_locality_pay_area_id => p_locality_pay_area_id
118 ,p_leo_pay_area_code => p_leo_pay_area_code
119 ,p_name => p_name
120 ,p_duty_station_code => p_duty_station_code
121 ,p_msa_code => p_msa_code
122 ,p_cmsa_code => p_cmsa_code
123 ,p_state_or_country_code => p_state_or_country_code
124 ,p_county_code => p_county_code
125 ,p_is_duty_station => p_is_duty_station
126 ,p_effective_date => trunc(p_effective_date)
127 ,p_object_version_number => l_object_version_number
128 );
129 exception
130 when hr_api.cannot_find_prog_unit then
131 hr_api.cannot_find_prog_unit_error
132 (p_module_name => 'CREATE_duty_stations'
136 -- End of API User Hook for the after hook of create_noac_remarks
133 ,p_hook_type => 'AP'
134 );
135 --
137 --
138 end;
139 --
140 hr_utility.set_location(l_proc, 60);
141 --
142 -- When in validation only mode raise the Validate_Enabled exception
143 --
144 if p_validate then
145 raise hr_api.validate_enabled;
146 end if;
147 --
148 -- Set all output arguments
149 --
150 p_duty_station_id := l_duty_station_id;
151 p_effective_start_date := l_effective_start_date;
152 p_effective_end_date := l_effective_end_date;
153 p_object_version_number := l_object_version_number;
154 --
155 hr_utility.set_location(' Leaving:'||l_proc, 70);
156 --
157 exception
158 --
159 when hr_api.validate_enabled then
160 --
161 -- As the Validate_Enabled exception has been raised
162 -- we must rollback to the savepoint
163 --
164 ROLLBACK TO create_duty_stations;
165 --
166 -- Only set output warning arguments
167 -- (Any key or derived arguments must be set to null
168 -- when validation only mode is being used.)
169 --
170 p_duty_station_id := l_duty_station_id;
171 p_object_version_number := l_object_version_number;
172 hr_utility.set_location(' Leaving:'||l_proc, 80);
173 --
174 when others then
175 --
176 -- A validation or unexpected error has occured
177 --
178 ROLLBACK TO create_duty_stations;
179 raise;
180
181 hr_utility.set_location(' Leaving:'||l_proc, 12);
182
183 end create_duty_station;
184 --
185
186 -- ----------------------------------------------------------------------------
187 -- |--------------------------<update_duty_station> >--------------------------|
188 -- ----------------------------------------------------------------------------
189 --
190 procedure update_duty_station
191 (p_validate in boolean default false
192 ,p_duty_station_id in number
193 ,p_effective_start_date out nocopy date
194 ,p_effective_end_date out nocopy date
195 ,p_locality_pay_area_id in number
199 ,p_msa_code in varchar2
196 ,p_leo_pay_area_code in varchar2 default hr_api.g_varchar2
197 ,p_name in varchar2 default hr_api.g_varchar2
198 ,p_duty_station_code in varchar2
200 ,p_cmsa_code in varchar2
201 ,p_state_or_country_code in varchar2
202 ,p_county_code in varchar2
203 ,p_is_duty_station in varchar2 default hr_api.g_varchar2
204 ,p_effective_date in date
205 ,p_datetrack_update_mode in varchar2
206 ,p_object_version_number IN out nocopy number
207 )
208
209
210
211 is
215 l_object_version_number ghr_duty_stations_f.object_version_number%TYPE;
212 l_proc varchar2(72) := g_package || 'update_duty_stations';
213 l_effective_start_date date;
214 l_effective_end_date date;
216
217 begin
218
219 hr_utility.set_location('Entering:'|| l_proc, 5);
220 --
221 savepoint update_duty_stations;
222 --
223 -- Call Before Process User Hook
224 --
225 begin
226 ghr_duty_station_bk2.update_duty_station_b
227 ( p_duty_station_id => p_duty_station_id
228 ,p_effective_start_date => p_effective_start_date
229 ,p_effective_end_date => p_effective_end_date
230 ,p_locality_pay_area_id => p_locality_pay_area_id
231 ,p_leo_pay_area_code => p_leo_pay_area_code
232 ,p_name => p_name
233 ,p_duty_station_code => p_duty_station_code
234 ,p_is_duty_station => p_is_duty_station
235 ,p_effective_date => p_effective_date
236 ,p_datetrack_update_mode => p_datetrack_update_mode
237 ,p_object_version_number => p_object_version_number
238 );
239
240 exception
241 when hr_api.cannot_find_prog_unit then
242 hr_api.cannot_find_prog_unit_error
243 (p_module_name => 'update_duty_stations',
244 p_hook_type => 'BP'
245 );
246 end;
247 --
248 -- End of Before Process User Hook call
249 --
250 --
251 -- Store the original ovn in case we rollback when p_validate is true
252 --
253 -- l_object_version_number := p_object_version_number;
254
255 hr_utility.set_location(l_proc, 6);
256 ghr_dut_upd.upd (
257 p_duty_station_id => p_duty_station_id
258 ,p_effective_start_date => l_effective_start_date
259 ,p_effective_end_date => l_effective_end_date
260 ,p_locality_pay_area_id => p_locality_pay_area_id
261 ,p_leo_pay_area_code => p_leo_pay_area_code
262 ,p_name => p_name
263 ,p_duty_station_code => p_duty_station_code
264 ,p_msa_code => p_msa_code
265 ,p_cmsa_code => p_cmsa_code
266 ,p_state_or_country_code => p_state_or_country_code
267 ,p_county_code => p_county_code
268 ,p_is_duty_station => p_is_duty_station
269 , p_object_version_number => p_object_version_number
270 ,p_effective_date => p_effective_date
271 ,p_datetrack_update_mode => p_datetrack_update_mode
272 );
273
274
275 --
276 --
277 -- Call After Process User Hook
278 --
279 begin
280 ghr_duty_station_bk2.update_duty_station_a(
281 p_duty_station_id => p_duty_station_id
282 ,p_effective_start_date => l_effective_start_date
283 ,p_effective_end_date => l_effective_end_date
284 ,p_locality_pay_area_id => p_locality_pay_area_id
285 ,p_leo_pay_area_code => p_leo_pay_area_code
286 ,p_name => p_name
287 ,p_duty_station_code => p_duty_station_code
288 ,p_is_duty_station => p_is_duty_station
289 ,p_effective_date => p_effective_date
290 ,p_datetrack_update_mode => p_datetrack_update_mode
291 ,p_object_version_number => l_object_version_number
292 );
293
297 (p_module_name => 'update_duty_stations',
294 exception
295 when hr_api.cannot_find_prog_unit then
296 hr_api.cannot_find_prog_unit_error
298 p_hook_type => 'AP'
299 );
300 end;
301 --
302 -- End of After Process User Hook call
303 --
304 if p_validate then
305 raise hr_api.validate_enabled;
306 end if;
307 --
308 -- Set all output arguments
309 p_effective_start_date := l_effective_start_date;
310 p_effective_end_date := l_effective_end_date;
311 p_object_version_number := l_object_version_number;
312 --
313 hr_utility.set_location(' Leaving:'||l_proc, 11);
314 exception
315 when hr_api.validate_enabled then
316 --
317 -- As the Validate_Enabled exception has been raised
318 -- we must rollback to the savepoint
319 --
320 ROLLBACK TO update_duty_stations;
321 --
322 -- Only set output warning arguments
323 -- (Any key or derived arguments must be set to null
324 -- when validation only mode is being used.)
325 --
326 p_object_version_number := l_object_version_number;
327 When Others then
328 ROLLBACK TO update_duty_stations;
329 raise;
330
331 hr_utility.set_location(' Leaving:'||l_proc, 12);
332 end update_duty_station;
333
334 --
335 --
336 -- ----------------------------------------------------------------------------
337 -- |-----------------------< delete_duty_station >------------------------|
338 -- ----------------------------------------------------------------------------
339 --
340 procedure delete_duty_station
341 (p_validate in boolean default false
342 ,p_duty_station_id in number
343 ,p_effective_start_date out nocopy date
344 ,p_effective_end_date out nocopy date
345 ,p_object_version_number in number
346 ,p_effective_date in date
347 ,p_datetrack_mode in varchar2
348 ) is
349 --
350 -- Declare cursors and local variables
351 --
352 l_proc varchar2(72) := g_package||'delete_duty_station';
353 l_exists boolean := false;
354 l_effective_start_date date;
355 l_effective_end_date date;
356 --
357
358 begin
359 hr_utility.set_location('Entering:'|| l_proc, 5);
360 --
361 --
362 savepoint delete_duty_station;
363 --
364 --
365 -- Call Before Process User Hook
366 --
367 IF p_validate then
368 hr_utility.set_location('1.p_validate is true',10);
369 else
370 hr_utility.set_location('1.p_validate is false',20);
371 End If;
372
373 begin
374 ghr_duty_station_bk3.delete_duty_station_b (
375 p_duty_station_id => p_duty_station_id
376 ,p_object_version_number => p_object_version_number
377 );
378 IF p_validate then
379 hr_utility.set_location('2.p_validate is true',10);
380 else
381 hr_utility.set_location('2.p_validate is false',20);
382 End If;
383 exception
384 when hr_api.cannot_find_prog_unit then
385 hr_api.cannot_find_prog_unit_error
386 (p_module_name => 'delete_ghr_duty_station'
387 ,p_hook_type => 'BP'
388 );
389 end;
390 --
391 -- End of Before Process User Hook call
392 --
393 hr_utility.set_location(l_proc, 7);
394
395 --
396 -- Process Logic - Delete duty_station details if the specific duty_station_id is not required
397 -- for the first_noa_id specified for the pa_request_id
398 -- and for the second_noa_id
399 IF p_validate then
400 hr_utility.set_location('3.p_validate is true',10);
401 else
402 hr_utility.set_location('3.p_validate is false',20);
403 End If;
404
405 ghr_dut_del.del
406 (p_duty_station_id => p_duty_station_id
407 ,p_effective_start_date => l_effective_start_date
408 ,p_effective_end_date => l_effective_end_date
409 , p_object_version_number => p_object_version_number
410 ,p_effective_date => p_effective_date
411 ,p_datetrack_mode => p_datetrack_mode
412 );
413
414 IF p_validate then
415 hr_utility.set_location('4.p_validate is true',10);
416 else
417 hr_utility.set_location('4.p_validate is false',20);
418 End If;
419
420 --
421 hr_utility.set_location(l_proc, 8);
422 --
423 --
424 -- Call After Process User Hook
425 --
426 begin
427 IF p_validate then
428 hr_utility.set_location('5.p_validate is true',10);
429 else
430 hr_utility.set_location('5.p_validate is false',20);
431 End If;
432 ghr_duty_station_bk3.delete_duty_station_a (
433 p_duty_station_id => p_duty_station_id
434 ,p_object_version_number => p_object_version_number
435 );
436 IF p_validate then
437 hr_utility.set_location('6.p_validate is true',10);
438 else
439 hr_utility.set_location('6.p_validate is false',20);
440 End If;
441 exception
442 when hr_api.cannot_find_prog_unit then
443 hr_api.cannot_find_prog_unit_error
444 (p_module_name => 'delete_ghr_duty_station'
445 ,p_hook_type => 'AP'
446 );
447 end;
448 --
449 -- End of After Process User Hook call
450 --
451 -- When in validation only mode raise the Validate_Enabled exception
452 --
453 if p_validate then
454 hr_utility.set_location('inside validate',10);
455 raise hr_api.validate_enabled;
456 end if;
460 hr_utility.set_location(' Leaving:'||l_proc, 11);
457 P_effective_start_date := l_effective_start_date;
458 p_effective_end_date := l_effective_end_date;
459 --
461 exception
462 when hr_api.validate_enabled then
463 --
464 -- As the Validate_Enabled exception has been raised
465 -- we must rollback to the savepoint
466 --
467 hr_utility.set_location('inside validate exception',20);
468 ROLLBACK TO delete_duty_station;
469 p_effective_start_date := l_effective_start_date;
470 p_effective_end_date := l_effective_end_date;
471 hr_utility.set_location(' Leaving:'||l_proc, 25);
472 --
473 When Others then
474 ROLLBACK TO delete_duty_station;
475 raise;
476 hr_utility.set_location(' Leaving:'||l_proc, 12);
477 end delete_duty_station;
478 --
479 --
480 end ghr_duty_station_api;