[Home] [Help]
PACKAGE BODY: APPS.PSP_PERIOD_FREQUENCY_API
Source
1 Package Body psp_period_frequency_api as
2 /* $Header: PSPFBAIB.pls 120.0 2005/06/02 15:59 appldev noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' psp_period_frequency_api.';
7 dup_data Exception;
8 --
9 -- ----------------------------------------------------------------------------
10 -- |--------------------------< Create_Period_frequency >--------------------------|
11 -- ----------------------------------------------------------------------------
12 -- {Start Of Comments}
13 --
14 -- Description:
15 --
16 --
17 -- Prerequisites:
18 --
19 --
20 -- In Parameters:
21 -- Name Reqd Type Description
22 --
23 --
24 -- Post Success:
25 --
26 --
27 -- Name Type Description
28 --
29 -- Post Failure:
30 --
31 --
32 -- Access Status:
33 -- Public.
34 --
35 -- {End Of Comments}
36 --
37 procedure Create_Period_frequency
38 ( p_validate in BOOLEAN default false
39 ,p_start_date in date
40 ,p_unit_of_measure in varchar2
41 ,p_period_duration in number
42 ,p_report_type in varchar2 default null
43 ,p_period_frequency in varchar2
44 ,p_language_code in varchar2 default hr_api.userenv_lang
45 ,p_period_frequency_id out nocopy number
46 ,p_object_version_number out nocopy number
47 ,p_api_warning out nocopy varchar2
48 ) is
49 l_proc varchar2(72) := g_package || 'Create_Period_frequency';
50 --l_period_frequency psp_report_period_frequency_tl.period_frequency%TYPE;
51 l_period_frequency_name psp_report_period_frequency_tl.period_frequency%TYPE;
52 l_period_frequency_id psp_report_period_frequency_b.period_frequency_id%TYPE;
53 l_object_version_number psp_report_period_frequency_b.object_version_number%TYPE;
54 l_api_warning varchar2(250);
55 l_language_code varchar2(30) := p_language_code;
56 --
57 -- cursor to check Duplicate Period Frequency
58 --
59 cursor c_period_frequency
60 is
61 select distinct period_frequency
62 from psp_report_period_frequency_v
63 where period_frequency = p_period_frequency;
64 --
65 -- end of cursor
66 --
67 begin
68 hr_utility.set_location('Entering:'|| l_proc, 10);
69 --
70 -- Issue a savepoint
71 --
72 savepoint Create_Period_frequency;
73 --
74 -- Validate the language parameter. l_language_code should be passed
75 -- instead of p_language_code from now on, to allow an IN OUT parameter to
76 -- be passed through.
77 --
78 hr_api.validate_language_code(p_language_code => l_language_code);
79 --
80 open c_period_frequency;
81 fetch c_period_frequency into l_period_frequency_name;
82 close c_period_frequency;
83 if (l_period_frequency_name is not NULL ) then
84 raise dup_data;
85 end if;
86 --
87 -- Call Before Process User Hook
88 --
89 begin
90 hr_utility.set_location('Before Calling User Hook Create_period_Frequency_b',20);
91 PSP_Period_frequency_BK1.Create_Period_frequency_b
92 ( p_start_date => p_start_date
93 ,p_unit_of_measure => p_unit_of_measure
94 ,p_period_duration => p_period_duration
95 ,p_report_type => p_report_type
96 ,p_period_frequency => p_period_frequency
97 );
98 hr_utility.set_location('After Calling User Hook Create_period_Frequency_b',25);
99 exception
100 when hr_api.cannot_find_prog_unit then
101 hr_api.cannot_find_prog_unit_error
102 (p_module_name => 'CREATE_PERIOD_FREQUENCY'
103 ,p_hook_type => 'BP'
104 );
105 end;
106 --
107 -- Validation in addition to Row Handlers
108 --
109 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
110 -- Process Logic - Call the row-handler ins procedure
111 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
112 -- Base Table insert statement
113 psp_pfb_ins.ins
114 (p_start_date => p_start_date
115 ,p_unit_of_measure => p_unit_of_measure
116 ,p_period_duration => p_period_duration
117 ,p_report_type => p_report_type
118 ,p_period_frequency_id => l_period_frequency_id
119 ,p_object_version_number => l_object_version_number
120 );
121 -- Transaltion table insert Statement
122 psp_pft_ins.ins_tl
123 ( p_language_code => l_language_code
124 ,p_period_frequency_id => l_period_frequency_id
125 ,p_period_frequency => p_period_frequency
126 );
127 --
128 -- Call After Process User Hook
129 --
130 begin
131 PSP_Period_frequency_BK1.Create_Period_frequency_a
132 (p_start_date => p_start_date
133 ,p_unit_of_measure => p_unit_of_measure
134 ,p_period_duration => p_period_duration
135 ,p_report_type => p_report_type
136 ,p_period_frequency => p_period_frequency
137 ,p_period_frequency_id => l_period_frequency_id
138 ,p_object_version_number => l_object_version_number
139 ,p_api_warning => l_api_warning
140 );
141 exception
142 when hr_api.cannot_find_prog_unit then
143 hr_api.cannot_find_prog_unit_error
144 (p_module_name => 'CREATE_PERIOD_FREQUENCY'
145 ,p_hook_type => 'AP'
146 );
147 end;
148 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
149 -- When in validation only mode raise the Validate_Enabled exception
150 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
151 If p_validate Then
152 raise hr_api.validate_enabled;
153 End If;
154 --
155 -- Set all IN OUT and OUT parameters with out values
156 --
157 p_period_frequency_id := l_period_frequency_id;
158 p_object_version_number := l_object_version_number;
159 p_api_warning := l_api_warning;
160 exception
161 when dup_data then
162 fnd_message.set_name('PSP','PSP_DUP_PERIOD_FREQUENCY');
163 fnd_message.set_token('PERIODFREQUENCY',p_period_frequency);
164 fnd_message.raise_error;
165 when hr_api.validate_enabled then
166 --
167 -- As the Validate_Enabled exception has been raised
168 -- we must rollback to the savepoint
169 --
170 rollback to Create_Period_frequency;
171 --
172 -- Reset IN OUT parameters and set OUT parameters
173 -- (Any key or derived arguments must be set to null
174 -- when validation only mode is being used.)
175 p_period_frequency_id := null;
176 p_object_version_number := null;
177 p_api_warning := l_api_warning;
178 hr_utility.set_location(' Leaving:'||l_proc, 80);
179 when others then
180 --
181 -- A validation or unexpected error has occured
182 --
183 rollback to Create_Period_frequency;
184 --
185 -- Reset IN OUT parameters and set all
186 -- OUT parameters, including warnings, to null
187 p_period_frequency_id := null;
188 p_object_version_number := null;
189 p_api_warning := l_api_warning;
190 hr_utility.set_location('error is : '||SQLERRM,85);
191 hr_utility.set_location(' Leaving:'||l_proc, 90);
192 end Create_Period_frequency;
193 --
194 -- -------------------------------------------------------------------------------
195 -- |--------------------------< Update_Period_Frequency >--------------------------|
196 -- --------------------------------------------------------------------------------
197 procedure Update_Period_Frequency
198 (p_validate in BOOLEAN default false
199 ,p_start_date in date
200 ,p_unit_of_measure in varchar2
201 ,p_period_duration in number
202 ,p_report_type in varchar2 default null
203 ,p_language_code in varchar2 default hr_api.userenv_lang
204 ,p_period_frequency in varchar2
205 ,p_period_frequency_id in number
206 ,p_object_version_number in out nocopy number
207 ,p_api_warning out nocopy varchar2
208 ) is
209 l_proc varchar2(150) := g_package||'Update_Period_Frequency';
210 l_api_warning varchar2(250);
211 l_object_version_number psp_report_period_frequency_b.object_version_number%TYPE;
212 l_language_code varchar2(30);
213 begin
214 hr_utility.set_location('Entering:'|| l_proc, 10);
215 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
216 -- Issue a savepoint
217 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
218 savepoint Update_Period_Frequency ;
219 l_object_version_number := p_object_version_number;
220 l_language_code := p_language_code;
221 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
222 -- Call Before Process User Hook
223 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
224 begin
225 hr_utility.set_location('Before Calling User Hook Update_Period_Frequency_b',20);
226 PSP_Period_frequency_BK2.Update_Period_Frequency_b
227 ( p_start_date => p_start_date
228 ,p_unit_of_measure => p_unit_of_measure
229 ,p_period_duration => p_period_duration
230 ,p_report_type => p_report_type
231 ,p_period_frequency => p_period_frequency
232 ,p_period_frequency_id => p_period_frequency_id
233 ,p_object_version_number => l_object_version_number
234 );
235 hr_utility.set_location('After Calling User Hook Update_Period_Frequency_b',20);
236 exception
237 when hr_api.cannot_find_prog_unit then
238 hr_utility.set_location('Exception in User Hook Update_Period_Frequency_b',25);
239 hr_api.cannot_find_prog_unit_error
240 (p_module_name => 'Update_Period_Frequency'
241 ,p_hook_type => 'BP'
242 );
243 end;
244 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
245 -- Validation in addition to Row Handlers
246 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
247 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
248 -- Process Logic - Call the row-handler ins procedure
249 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
250 hr_utility.set_location('Before calling row-handler psp_pfb_upd.upd',30);
251 psp_pfb_upd.upd
252 (p_period_frequency_id => p_period_frequency_id
253 ,p_object_version_number => l_object_version_number
254 ,p_start_date => p_start_date
255 ,p_unit_of_measure => p_unit_of_measure
256 ,p_period_duration => p_period_duration
257 ,p_report_type => p_report_type
258 );
259 -- Row Handlers for updating the _Tl table
260 psp_pft_upd.upd_tl
261 ( p_language_code => l_language_code
262 ,p_period_frequency_id => p_period_frequency_id
263 ,p_period_frequency => p_period_frequency
264 );
265 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
266 -- Call After Process User Hook
267 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
268 begin
269 hr_utility.set_location(' Before Calling User Hook : Update_Period_Frequency_a',20);
270 PSP_Period_Frequency_Bk2.Update_Period_Frequency_a
271 (p_start_date => p_start_date
272 ,p_unit_of_measure => p_unit_of_measure
273 ,p_period_duration => p_period_duration
274 ,p_report_type => p_report_type
275 ,p_period_frequency => p_period_frequency
276 ,p_period_frequency_id => p_period_frequency_id
277 ,p_object_version_number => l_object_version_number
278 ,p_api_warning => l_api_warning
279 );
280 hr_utility.set_location(' After Calling User Hook :Update_Period_Frequency_a',20);
281 exception
282 When hr_api.cannot_find_prog_unit Then
283 hr_utility.set_location('Exception in User Hook :Update_Period_Frequency_a',25);
284 hr_api.cannot_find_prog_unit_error
285 (p_module_name => 'Update_Period_Frequency'
286 ,p_hook_type => 'AP'
287 );
288 end;
289 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
290 -- When in validation only mode raise the Validate_Enabled exception
291 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
292 If p_validate Then
293 raise hr_api.validate_enabled;
294 End If;
295 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
296 -- Set all output arguments
297 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
298 p_object_version_number := l_object_version_number;
299 p_api_warning := l_api_warning;
300 hr_utility.set_location(' Leaving:'||l_proc, 70);
301 Exception
302 When hr_api.validate_enabled Then
303 --
304 -- As the Validate_Enabled exception has been raised
305 -- we must rollback to the savepoint
306 --
307 Rollback To Update_Period_Frequency;
308 --
309 -- Only set output warning arguments
310 -- (Any key or derived arguments must be set to null
311 -- when validation only mode is being used.)
312 --
313 p_api_warning := l_api_warning;
314 hr_utility.set_location(' Leaving:'||l_proc, 80);
315 When Others Then
316 --
317 -- A validation or unexpected error has occured
318 --
319 Rollback to Update_Period_Frequency;
320 hr_utility.set_location(' Leaving:'||l_proc, 90);
321 Raise;
322 End Update_Period_Frequency ;
323 --
324 -- -------------------------------------------------------------------------------
325 -- |--------------------------< Delete_Period_Frequency >--------------------------|
326 -- --------------------------------------------------------------------------------
327 procedure Delete_Period_Frequency
328 (p_validate in BOOLEAN default false
329 ,p_period_frequency_id in number
330 ,p_object_version_number in out nocopy number
331 ,p_api_warning out nocopy varchar2
332 ) is
333 l_proc varchar2(150) := g_package||'Delete_Period_Frequency';
334 l_object_version_number psp_report_period_frequency_b.object_version_number%TYPE;
335 l_api_warning varchar2(250);
336 begin
337 hr_utility.set_location('Entering:'|| l_proc, 10);
338 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
339 -- Issue a savepoint
340 savepoint Delete_Period_Frequency;
341
342 -- Truncate the time portion from all IN date parameters
343
344 l_object_version_number := p_object_version_number;
345 -- Call Before Process User Hook
346 Begin
347 hr_utility.set_location('Before Calling User Hook Delete_Period_Frequency_b',20);
348 PSP_Period_frequency_BK3.Delete_Period_Frequency_b
349 ( p_period_frequency_id => p_period_frequency_id
350 ,p_object_version_number => l_object_version_number
351 );
352 hr_utility.set_location('After Calling User Hook Delete_Period_Frequency_b',20);
353 Exception
354 When hr_api.cannot_find_prog_unit Then
355 hr_utility.set_location('Exception in User Hook Delete_Period_Frequency_b',25);
356 hr_api.cannot_find_prog_unit_error
357 (p_module_name => 'Delete_Period_Frequency'
358 ,p_hook_type => 'BP'
359 );
360 End;
361 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
362 -- Process Logic - Call the row-handler del procedure
363 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
364 -- call the procedure to delete from the _tl table
365 hr_utility.set_location('Before calling row-handler psp_pft_del.del_tl',30);
366 psp_pft_del.del_tl( p_period_frequency_id => p_period_frequency_id) ;
367 -- call the procedure to delete from the Base table
368
369 hr_utility.set_location('Before calling row-handler psp_pft_del.del',35);
370 psp_pfb_del.del
371 ( p_period_frequency_id => p_period_frequency_id
372 ,p_object_version_number => l_object_version_number) ;
373 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
374 -- Call After Process User Hook
375 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
376 Begin
377 hr_utility.set_location('Before Calling User Hook Delete_Period_Frequency_a',20);
378 PSP_Period_frequency_BK3.Delete_Period_Frequency_a
379 (p_period_frequency_id => p_period_frequency_id
380 ,p_object_version_number => l_object_version_number
381 ,p_api_warning => l_api_warning
382 );
383 hr_utility.set_location('After Calling User Hook Delete_Period_Frequency_a',20);
384 Exception
385 When hr_api.cannot_find_prog_unit Then
386 hr_utility.set_location('Exception in User Hook Delete_Period_Frequency_a',25);
387 hr_api.cannot_find_prog_unit_error
388 (p_module_name => 'Delete_Period_Frequency'
389 ,p_hook_type => 'AP'
390 );
391 End;
392 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
393 -- When in validation only mode raise the Validate_Enabled exception
394 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
395 If p_validate Then
396 raise hr_api.validate_enabled;
397 End If;
398 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
399 -- Set all output arguments
400 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
401 p_object_version_number := l_object_version_number;
402 hr_utility.set_location(' Leaving:'||l_proc, 70);
403 Exception
404 when dup_data then
405 fnd_message.set_name('PSP','PSP_DUP_PERIOD_FREQUENCY');
406 fnd_message.raise_error;
407 When hr_api.validate_enabled Then
408 --
409 -- As the Validate_Enabled exception has been raised
410 -- we must rollback to the savepoint
411 --
412 Rollback To Delete_Period_Frequency;
413 --
414 -- Only set output warning arguments
415 -- (Any key or derived arguments must be set to null
416 -- when validation only mode is being used.)
417 --
418 hr_utility.set_location(' Leaving:'||l_proc, 80);
419 When Others Then
420 --
421 -- A validation or unexpected error has occured
422 --
423 Rollback to Delete_Period_Frequency;
424 hr_utility.set_location(' Leaving:'||l_proc, 90);
425 Raise;
426 End Delete_Period_Frequency;
427 end PSP_Period_frequency_API;