[Home] [Help]
PACKAGE BODY: APPS.PER_BF_BALANCE_AMOUNTS_API
Source
1 Package Body PER_BF_BALANCE_AMOUNTS_API as
2 /* $Header: pebbaapi.pkb 115.10 2002/11/29 14:31:02 apholt noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' PER_BF_BALANCE_AMOUNTS_API.';
7 --
8 Procedure chk_currency_code (p_currency_code in varchar2
9 ,p_balance_type_id in varchar2
10 ,p_effective_date in DATE)
11 --
12 IS
13 -- Cursor to retrieve the currency code and input_value_id from bbt
14 CURSOR csr_bbt_currency IS
15 SELECT input_value_id, currency, uom
16 FROM per_bf_balance_types
17 WHERE balance_type_id = p_balance_type_id;
18 --
19 -- Cursor to retrieve the currency code from element type.
20 CURSOR csr_et_currency_code (p_input_value_id NUMBER )IS
21 SELECT et.input_currency_code
22 , iv.uom
23 FROM pay_element_types_f et
24 , pay_input_values_f iv
25 WHERE iv.input_value_id = p_input_value_id
26 AND iv.element_type_id = et.element_type_id
27 AND p_effective_date BETWEEN
28 iv.effective_start_date AND iv.effective_end_date
29 AND p_effective_date BETWEEN
30 et.effective_start_date AND et.effective_end_date;
31 --
32 l_input_value_id NUMBER;
33 l_currency VARCHAR2(20);
34 l_uom VARCHAR2(20);
35 BEGIN
36 --
37 OPEN csr_bbt_currency;
38 FETCH csr_bbt_currency INTO l_input_value_id, l_currency, l_uom;
39 CLOSE csr_bbt_currency;
40 --
41 IF l_input_value_id IS NOT NULL THEN
42 -- The balance type is attached to an element which will contain
43 -- the currency, and the input_value will contain the UOM.
44 --
45 OPEN csr_et_currency_code(l_input_value_id);
46 FETCH csr_et_currency_code INTO l_currency , l_uom;
47 CLOSE csr_et_currency_code;
48 --
49 END IF;
50 --
51 -- Now check if the UOM is money, and if so check that the currency is
52 -- the same.
53 IF l_uom = 'M' THEN
54 -- The UOM is Money, so need to ensure the currency is the same
55 --
56 IF p_currency_code IS NULL THEN
57 -- Need to error, as if the type is money, as currency is required
58 hr_utility.set_message(800,'HR_50401_NEED_CURR_CODE');
59 hr_utility.raise_error;
60 END IF;
61 IF l_currency <> p_currency_code THEN
62 -- The currency being passed in is different than the currency
63 -- that the business group is expecting, so error!
64 --
65 hr_utility.set_message(800,'HR_50402_WRONG_CURR_CODE');
66 hr_utility.raise_error;
67 --
68 END IF;
69 END IF;
70 --
71 END chk_currency_code;
72 -- ----------------------------------------------------------------------------
73 -- |---------------------< <create_balance_amount> >--------------------------|
74 -- ----------------------------------------------------------------------------
75 --
76 procedure create_balance_amount
77 (p_validate in boolean default false
78 ,p_effective_date in date
79 ,p_business_group_id in number
80 ,p_balance_type_id in number
81 ,p_assignment_id in number
82 ,p_payroll_run_id in number
83 ,p_ytd_amount in number default null
84 ,p_fytd_amount in number default null
85 ,p_ptd_amount in number default null
86 ,p_mtd_amount in number default null
87 ,p_qtd_amount in number default null
88 ,p_run_amount in number default null
89 ,p_currency_code in varchar2 default null
90 ,p_bba_attribute_category in varchar2 default null
91 ,p_bba_attribute1 in varchar2 default null
92 ,p_bba_attribute2 in varchar2 default null
93 ,p_bba_attribute3 in varchar2 default null
94 ,p_bba_attribute4 in varchar2 default null
95 ,p_bba_attribute5 in varchar2 default null
96 ,p_bba_attribute6 in varchar2 default null
97 ,p_bba_attribute7 in varchar2 default null
98 ,p_bba_attribute8 in varchar2 default null
99 ,p_bba_attribute9 in varchar2 default null
100 ,p_bba_attribute10 in varchar2 default null
101 ,p_bba_attribute11 in varchar2 default null
102 ,p_bba_attribute12 in varchar2 default null
103 ,p_bba_attribute13 in varchar2 default null
104 ,p_bba_attribute14 in varchar2 default null
105 ,p_bba_attribute15 in varchar2 default null
106 ,p_bba_attribute16 in varchar2 default null
107 ,p_bba_attribute17 in varchar2 default null
108 ,p_bba_attribute18 in varchar2 default null
109 ,p_bba_attribute19 in varchar2 default null
110 ,p_bba_attribute20 in varchar2 default null
111 ,p_bba_attribute21 in varchar2 default null
112 ,p_bba_attribute22 in varchar2 default null
113 ,p_bba_attribute23 in varchar2 default null
114 ,p_bba_attribute24 in varchar2 default null
115 ,p_bba_attribute25 in varchar2 default null
116 ,p_bba_attribute26 in varchar2 default null
117 ,p_bba_attribute27 in varchar2 default null
118 ,p_bba_attribute28 in varchar2 default null
119 ,p_bba_attribute29 in varchar2 default null
120 ,p_bba_attribute30 in varchar2 default null
121 ,p_processed_assignment_id out nocopy number
122 ,p_processed_assignment_ovn out nocopy number
123 ,p_balance_amount_id out nocopy number
124 ,p_balance_amount_ovn out nocopy number
125 ) is
126 --
127 -- Declare cursors and local variables
128 --
129 -- Cursor to retrieve the ID and OVN of the row in
130 -- PER_BF_PROCESSED_ASSIGNMENTS should one exist.
131 --
132 CURSOR csr_get_pa_id_ovn IS
133 SELECT processed_assignment_id, object_version_number
134 FROM per_bf_processed_assignments
135 WHERE payroll_run_id = p_payroll_run_id
136 AND assignment_id = p_assignment_id;
137 --
138 -- Two tables are being used in this API, so two sets of OUT parameters will
139 -- be created.
140 --
141 l_balance_amount_id PER_BF_BALANCE_AMOUNTS.balance_amount_id%TYPE;
142 l_balance_amount_ovn
143 PER_BF_BALANCE_AMOUNTS.object_version_number%TYPE;
144 l_processed_assignment_id
145 PER_BF_PROCESSED_ASSIGNMENTS.processed_assignment_id%TYPE;
146 l_processed_assignment_ovn
147 PER_BF_PROCESSED_ASSIGNMENTS.object_version_number%TYPE;
148 --
149 l_proc varchar2(72) := g_package||'create_balance_amount';
150 begin
151 hr_utility.set_location('Entering:'|| l_proc, 10);
152 --
153 -- Issue a savepoint
154 --
155 savepoint create_balance_amount;
156 hr_utility.set_location(l_proc, 20);
157 --
158 -- Truncate the time portion from all IN date parameters
159 --
160 --
161 --
162 -- Call Before Process User Hook
163 --
164 begin
165 PER_BF_BALANCE_AMOUNTS_BK1.CREATE_BALANCE_AMOUNT_B
166 (p_effective_date => p_effective_date
167 ,p_business_group_id => p_business_group_id
168 ,p_balance_type_id => p_balance_type_id
169 ,p_assignment_id => p_assignment_id
170 ,p_payroll_run_id => p_payroll_run_id
171 ,p_ytd_amount => p_ytd_amount
172 ,p_fytd_amount => p_fytd_amount
173 ,p_ptd_amount => p_ptd_amount
174 ,p_mtd_amount => p_mtd_amount
175 ,p_qtd_amount => p_qtd_amount
176 ,p_run_amount => p_run_amount
177 ,p_currency_code => p_currency_code
178 ,p_bba_attribute_category => p_bba_attribute_category
179 ,p_bba_attribute1 => p_bba_attribute1
180 ,p_bba_attribute2 => p_bba_attribute2
181 ,p_bba_attribute3 => p_bba_attribute3
182 ,p_bba_attribute4 => p_bba_attribute4
183 ,p_bba_attribute5 => p_bba_attribute5
184 ,p_bba_attribute6 => p_bba_attribute6
185 ,p_bba_attribute7 => p_bba_attribute7
186 ,p_bba_attribute8 => p_bba_attribute8
187 ,p_bba_attribute9 => p_bba_attribute9
188 ,p_bba_attribute10 => p_bba_attribute10
189 ,p_bba_attribute11 => p_bba_attribute11
190 ,p_bba_attribute12 => p_bba_attribute12
191 ,p_bba_attribute13 => p_bba_attribute13
192 ,p_bba_attribute14 => p_bba_attribute14
193 ,p_bba_attribute15 => p_bba_attribute15
194 ,p_bba_attribute16 => p_bba_attribute16
195 ,p_bba_attribute17 => p_bba_attribute17
196 ,p_bba_attribute18 => p_bba_attribute18
197 ,p_bba_attribute19 => p_bba_attribute19
198 ,p_bba_attribute20 => p_bba_attribute20
199 ,p_bba_attribute21 => p_bba_attribute21
200 ,p_bba_attribute22 => p_bba_attribute22
201 ,p_bba_attribute23 => p_bba_attribute23
202 ,p_bba_attribute24 => p_bba_attribute24
203 ,p_bba_attribute25 => p_bba_attribute25
204 ,p_bba_attribute26 => p_bba_attribute26
205 ,p_bba_attribute27 => p_bba_attribute27
206 ,p_bba_attribute28 => p_bba_attribute28
207 ,p_bba_attribute29 => p_bba_attribute29
208 ,p_bba_attribute30 => p_bba_attribute30
209 );
210 --
211 exception
212 when hr_api.cannot_find_prog_unit then
213 hr_api.cannot_find_prog_unit_error
214 (p_module_name => 'CREATE_BALANCE_AMOUNT'
215 ,p_hook_type => 'BP'
216 );
217 end;
218 hr_utility.set_location(l_proc, 30);
219 --
220 -- Validation in addition to Row Handlers
221 --
222 -- check to ensure that the currency code of the values being passed in
223 -- (if the values are monetary) is what is being expected.
224 chk_currency_code (p_currency_code => p_currency_code
225 ,p_balance_type_id => p_balance_type_id
226 ,p_effective_date => p_effective_date);
227 --
228 OPEN csr_get_pa_id_ovn ;
229 FETCH csr_get_pa_id_ovn INTO l_processed_assignment_id
230 , l_processed_assignment_ovn;
231 --
232 IF csr_get_pa_id_ovn%NOTFOUND THEN
233 --
234 hr_utility.set_location(l_proc, 35);
235 --
236 -- This is the first balance to be associated with this assignment and
237 -- payroll run, so a new row needs to be created in the table
238 -- PER_BF_PROCESSED_ASSIGNMENTS.
239 --
240 CLOSE csr_get_pa_id_ovn ;
241 --
242 per_bpa_ins.ins
243 (p_effective_date => p_effective_date
244 ,p_payroll_run_id => p_payroll_run_id
245 ,p_assignment_id => p_assignment_id
246 ,p_processed_assignment_id => l_processed_assignment_id
247 ,p_object_version_number => l_processed_assignment_ovn
248 );
249 --
250 ELSE
251 --
252 hr_utility.set_location(l_proc, 36);
253 --
254 -- A row exists in PER_BF_PROCESSED_ASSIGNMENTS for this payroll run and
255 -- assignment combination, so no need to do anything as the values
256 -- for the processed_assignment_id and object_version number are
257 -- already in local variables.
258 --
259 CLOSE csr_get_pa_id_ovn ;
260 --
261 END IF;
262 --
263 --
264 hr_utility.set_location(l_proc, 40);
265 --
266 -- Process Logic
267 --
268 per_bba_ins.ins
269 (
270 p_effective_date => p_effective_date
271 ,p_balance_type_id => p_balance_type_id
272 ,p_processed_assignment_id => l_processed_assignment_id
273 ,p_business_group_id => p_business_group_id
274 ,p_ytd_amount => p_ytd_amount
275 ,p_fytd_amount => p_fytd_amount
276 ,p_ptd_amount => p_ptd_amount
277 ,p_mtd_amount => p_mtd_amount
278 ,p_qtd_amount => p_qtd_amount
279 ,p_run_amount => p_run_amount
280 ,p_bba_attribute_category => p_bba_attribute_category
281 ,p_bba_attribute1 => p_bba_attribute1
282 ,p_bba_attribute2 => p_bba_attribute2
283 ,p_bba_attribute3 => p_bba_attribute3
284 ,p_bba_attribute4 => p_bba_attribute4
285 ,p_bba_attribute5 => p_bba_attribute5
286 ,p_bba_attribute6 => p_bba_attribute6
287 ,p_bba_attribute7 => p_bba_attribute7
288 ,p_bba_attribute8 => p_bba_attribute8
289 ,p_bba_attribute9 => p_bba_attribute9
290 ,p_bba_attribute10 => p_bba_attribute10
291 ,p_bba_attribute11 => p_bba_attribute11
292 ,p_bba_attribute12 => p_bba_attribute12
293 ,p_bba_attribute13 => p_bba_attribute13
294 ,p_bba_attribute14 => p_bba_attribute14
295 ,p_bba_attribute15 => p_bba_attribute15
296 ,p_bba_attribute16 => p_bba_attribute16
297 ,p_bba_attribute17 => p_bba_attribute17
298 ,p_bba_attribute18 => p_bba_attribute18
299 ,p_bba_attribute19 => p_bba_attribute19
300 ,p_bba_attribute20 => p_bba_attribute20
301 ,p_bba_attribute21 => p_bba_attribute21
302 ,p_bba_attribute22 => p_bba_attribute22
303 ,p_bba_attribute23 => p_bba_attribute23
304 ,p_bba_attribute24 => p_bba_attribute24
305 ,p_bba_attribute25 => p_bba_attribute25
306 ,p_bba_attribute26 => p_bba_attribute26
307 ,p_bba_attribute27 => p_bba_attribute27
308 ,p_bba_attribute28 => p_bba_attribute28
309 ,p_bba_attribute29 => p_bba_attribute29
310 ,p_bba_attribute30 => p_bba_attribute30
311 ,p_balance_amount_id => l_balance_amount_id
312 ,p_object_version_number => l_balance_amount_ovn
313
314 );
315 --
316 --
317 hr_utility.set_location(l_proc, 50);
318 --
319 -- Call After Process User Hook
320 --
321 begin
322 PER_BF_BALANCE_AMOUNTS_BK1.CREATE_BALANCE_AMOUNT_A
323 (p_effective_date => p_effective_date
324 ,p_business_group_id => p_business_group_id
325 ,p_balance_type_id => p_balance_type_id
326 ,p_assignment_id => p_assignment_id
327 ,p_payroll_run_id => p_payroll_run_id
328 ,p_ytd_amount => p_ytd_amount
329 ,p_fytd_amount => p_fytd_amount
330 ,p_ptd_amount => p_ptd_amount
331 ,p_mtd_amount => p_mtd_amount
332 ,p_qtd_amount => p_qtd_amount
333 ,p_run_amount => p_run_amount
334 ,p_currency_code => p_currency_code
335 ,p_bba_attribute_category => p_bba_attribute_category
336 ,p_bba_attribute1 => p_bba_attribute1
337 ,p_bba_attribute2 => p_bba_attribute2
338 ,p_bba_attribute3 => p_bba_attribute3
339 ,p_bba_attribute4 => p_bba_attribute4
340 ,p_bba_attribute5 => p_bba_attribute5
341 ,p_bba_attribute6 => p_bba_attribute6
342 ,p_bba_attribute7 => p_bba_attribute7
343 ,p_bba_attribute8 => p_bba_attribute8
344 ,p_bba_attribute9 => p_bba_attribute9
345 ,p_bba_attribute10 => p_bba_attribute10
346 ,p_bba_attribute11 => p_bba_attribute11
347 ,p_bba_attribute12 => p_bba_attribute12
348 ,p_bba_attribute13 => p_bba_attribute13
349 ,p_bba_attribute14 => p_bba_attribute14
350 ,p_bba_attribute15 => p_bba_attribute15
351 ,p_bba_attribute16 => p_bba_attribute16
352 ,p_bba_attribute17 => p_bba_attribute17
353 ,p_bba_attribute18 => p_bba_attribute18
354 ,p_bba_attribute19 => p_bba_attribute19
355 ,p_bba_attribute20 => p_bba_attribute20
356 ,p_bba_attribute21 => p_bba_attribute21
357 ,p_bba_attribute22 => p_bba_attribute22
358 ,p_bba_attribute23 => p_bba_attribute23
359 ,p_bba_attribute24 => p_bba_attribute24
360 ,p_bba_attribute25 => p_bba_attribute25
361 ,p_bba_attribute26 => p_bba_attribute26
362 ,p_bba_attribute27 => p_bba_attribute27
363 ,p_bba_attribute28 => p_bba_attribute28
364 ,p_bba_attribute29 => p_bba_attribute29
365 ,p_bba_attribute30 => p_bba_attribute30
366 ,p_balance_amount_id => l_balance_amount_id
367 ,p_balance_amount_ovn => l_balance_amount_ovn
368 ,p_processed_assignment_id => l_processed_assignment_id
369 ,p_processed_assignment_ovn => l_processed_assignment_ovn
370 );
371 exception
372 when hr_api.cannot_find_prog_unit then
373 hr_api.cannot_find_prog_unit_error
374 (p_module_name => 'CREATE_BALANCE_AMOUNT'
375 ,p_hook_type => 'AP'
376 );
377 end;
378 hr_utility.set_location(l_proc, 60);
379 --
380 -- When in validation only mode raise the Validate_Enabled exception
381 --
382 if p_validate then
383 raise hr_api.validate_enabled;
384 end if;
385 --
386 -- Set all output arguments
387 --
388 p_balance_amount_id := l_balance_amount_id;
389 p_balance_amount_ovn := l_balance_amount_ovn;
390 p_processed_assignment_id := l_processed_assignment_id;
391 p_processed_assignment_ovn:= l_processed_assignment_ovn;
392 --
393 hr_utility.set_location(' Leaving:'||l_proc, 70);
394 exception
395 when hr_api.validate_enabled then
396 --
397 -- As the Validate_Enabled exception has been raised
398 -- we must rollback to the savepoint
399 --
400 rollback to create_balance_amount;
401 --
402 -- Only set output warning arguments
403 -- (Any key or derived arguments must be set to null
404 -- when validation only mode is being used.)
405 --
406 p_balance_amount_id := null;
407 p_balance_amount_ovn := null;
408 p_processed_assignment_id := null;
409 p_processed_assignment_ovn:= null;
410 --
411 hr_utility.set_location(' Leaving:'||l_proc, 80);
412 when others then
413 --
414 -- A validation or unexpected error has occured
415 --
416 rollback to create_balance_amount;
417 --set out variables
418 --
419 p_balance_amount_id := null;
420 p_balance_amount_ovn := null;
421 p_processed_assignment_id := null;
422 p_processed_assignment_ovn:= null;
423 hr_utility.set_location(' Leaving:'||l_proc, 90);
424 raise;
425 end CREATE_BALANCE_AMOUNT;
426 --
427 --
428 -- ----------------------------------------------------------------------------
429 -- |---------------------< <update_balance_amount> >--------------------------|
430 -- ----------------------------------------------------------------------------
431 --
432 procedure update_balance_amount
433 (p_validate in boolean default false
434 ,p_effective_date in date
435 ,p_ytd_amount in number default hr_api.g_number
436 ,p_fytd_amount in number default hr_api.g_number
437 ,p_ptd_amount in number default hr_api.g_number
438 ,p_mtd_amount in number default hr_api.g_number
439 ,p_qtd_amount in number default hr_api.g_number
440 ,p_run_amount in number default hr_api.g_number
441 ,p_currency_code in varchar2 default hr_api.g_varchar2
442 ,p_bba_attribute_category in varchar2 default hr_api.g_varchar2
443 ,p_bba_attribute1 in varchar2 default hr_api.g_varchar2
444 ,p_bba_attribute2 in varchar2 default hr_api.g_varchar2
445 ,p_bba_attribute3 in varchar2 default hr_api.g_varchar2
446 ,p_bba_attribute4 in varchar2 default hr_api.g_varchar2
447 ,p_bba_attribute5 in varchar2 default hr_api.g_varchar2
448 ,p_bba_attribute6 in varchar2 default hr_api.g_varchar2
449 ,p_bba_attribute7 in varchar2 default hr_api.g_varchar2
450 ,p_bba_attribute8 in varchar2 default hr_api.g_varchar2
451 ,p_bba_attribute9 in varchar2 default hr_api.g_varchar2
452 ,p_bba_attribute10 in varchar2 default hr_api.g_varchar2
453 ,p_bba_attribute11 in varchar2 default hr_api.g_varchar2
454 ,p_bba_attribute12 in varchar2 default hr_api.g_varchar2
455 ,p_bba_attribute13 in varchar2 default hr_api.g_varchar2
456 ,p_bba_attribute14 in varchar2 default hr_api.g_varchar2
457 ,p_bba_attribute15 in varchar2 default hr_api.g_varchar2
458 ,p_bba_attribute16 in varchar2 default hr_api.g_varchar2
459 ,p_bba_attribute17 in varchar2 default hr_api.g_varchar2
460 ,p_bba_attribute18 in varchar2 default hr_api.g_varchar2
461 ,p_bba_attribute19 in varchar2 default hr_api.g_varchar2
462 ,p_bba_attribute20 in varchar2 default hr_api.g_varchar2
463 ,p_bba_attribute21 in varchar2 default hr_api.g_varchar2
464 ,p_bba_attribute22 in varchar2 default hr_api.g_varchar2
465 ,p_bba_attribute23 in varchar2 default hr_api.g_varchar2
466 ,p_bba_attribute24 in varchar2 default hr_api.g_varchar2
467 ,p_bba_attribute25 in varchar2 default hr_api.g_varchar2
468 ,p_bba_attribute26 in varchar2 default hr_api.g_varchar2
469 ,p_bba_attribute27 in varchar2 default hr_api.g_varchar2
470 ,p_bba_attribute28 in varchar2 default hr_api.g_varchar2
471 ,p_bba_attribute29 in varchar2 default hr_api.g_varchar2
472 ,p_bba_attribute30 in varchar2 default hr_api.g_varchar2
473 ,p_balance_amount_id in number
474 ,p_balance_amount_ovn in out nocopy number
475 ) is
476 --
477 -- Declare cursors and local variables
478 --
479 -- Cursor to get the balance_type_id
480 CURSOR csr_get_bbt IS
481 SELECT balance_type_id
482 FROM per_bf_balance_amounts
483 WHERE balance_amount_id = p_balance_amount_id;
484 --
485 l_balance_type_id NUMBER;
486 --
487 l_balance_amount_ovn
488 PER_BF_BALANCE_AMOUNTS.object_version_number%TYPE;
489 --
490 l_proc varchar2(72) := g_package||'update_balance_amount';
491 begin
492 hr_utility.set_location('Entering:'|| l_proc, 10);
493 --
494 -- Issue a savepoint
495 --
496 savepoint update_balance_amount;
497 hr_utility.set_location(l_proc, 20);
498 --
499 -- Truncate the time portion from all IN date parameters
500 --
501 --
502 --
503 -- Call Before Process User Hook
504 --
505 begin
506 PER_BF_BALANCE_AMOUNTS_BK2.UPDATE_BALANCE_AMOUNT_B
507 (p_effective_date => p_effective_date
508 ,p_ytd_amount => p_ytd_amount
509 ,p_fytd_amount => p_fytd_amount
510 ,p_ptd_amount => p_ptd_amount
511 ,p_mtd_amount => p_mtd_amount
512 ,p_qtd_amount => p_qtd_amount
513 ,p_run_amount => p_run_amount
514 ,p_currency_code => p_currency_code
515 ,p_bba_attribute_category => p_bba_attribute_category
516 ,p_bba_attribute1 => p_bba_attribute1
517 ,p_bba_attribute2 => p_bba_attribute2
518 ,p_bba_attribute3 => p_bba_attribute3
519 ,p_bba_attribute4 => p_bba_attribute4
520 ,p_bba_attribute5 => p_bba_attribute5
521 ,p_bba_attribute6 => p_bba_attribute6
522 ,p_bba_attribute7 => p_bba_attribute7
523 ,p_bba_attribute8 => p_bba_attribute8
524 ,p_bba_attribute9 => p_bba_attribute9
525 ,p_bba_attribute10 => p_bba_attribute10
526 ,p_bba_attribute11 => p_bba_attribute11
527 ,p_bba_attribute12 => p_bba_attribute12
528 ,p_bba_attribute13 => p_bba_attribute13
529 ,p_bba_attribute14 => p_bba_attribute14
530 ,p_bba_attribute15 => p_bba_attribute15
531 ,p_bba_attribute16 => p_bba_attribute16
532 ,p_bba_attribute17 => p_bba_attribute17
533 ,p_bba_attribute18 => p_bba_attribute18
534 ,p_bba_attribute19 => p_bba_attribute19
535 ,p_bba_attribute20 => p_bba_attribute20
536 ,p_bba_attribute21 => p_bba_attribute21
537 ,p_bba_attribute22 => p_bba_attribute22
538 ,p_bba_attribute23 => p_bba_attribute23
539 ,p_bba_attribute24 => p_bba_attribute24
540 ,p_bba_attribute25 => p_bba_attribute25
541 ,p_bba_attribute26 => p_bba_attribute26
542 ,p_bba_attribute27 => p_bba_attribute27
543 ,p_bba_attribute28 => p_bba_attribute28
544 ,p_bba_attribute29 => p_bba_attribute29
545 ,p_bba_attribute30 => p_bba_attribute30
546 ,p_balance_amount_id => p_balance_amount_id
547 ,p_balance_amount_ovn => p_balance_amount_ovn
548 );
549 --
550 exception
551 when hr_api.cannot_find_prog_unit then
552 hr_api.cannot_find_prog_unit_error
553 (p_module_name => 'UPDATE_BALANCE_AMOUNT'
554 ,p_hook_type => 'BP'
555 );
556 end;
557 hr_utility.set_location(l_proc, 30);
558 --
559 -- Validation in addition to Row Handlers
560 --
561 OPEN csr_get_bbt;
562 FETCH csr_get_bbt INTO l_balance_type_id;
563 CLOSE csr_get_bbt;
564 --
565 -- check to ensure that the currency code of the values being passed in
566 -- (if the values are monetary) is what is being expected.
567 chk_currency_code (p_currency_code => p_currency_code
568 ,p_balance_type_id => l_balance_type_id
569 ,p_effective_date => p_effective_date);
570 --
571 --
572 hr_utility.set_location(l_proc, 40);
573 --
574 -- Process Logic
575 --
576 l_balance_amount_ovn := p_balance_amount_ovn;
577 --
578 per_bba_upd.upd
579 (
580 p_effective_date => p_effective_date
581 ,p_ytd_amount => p_ytd_amount
582 ,p_fytd_amount => p_fytd_amount
583 ,p_ptd_amount => p_ptd_amount
584 ,p_mtd_amount => p_mtd_amount
585 ,p_qtd_amount => p_qtd_amount
586 ,p_run_amount => p_run_amount
587 ,p_bba_attribute_category => p_bba_attribute_category
588 ,p_bba_attribute1 => p_bba_attribute1
589 ,p_bba_attribute2 => p_bba_attribute2
590 ,p_bba_attribute3 => p_bba_attribute3
591 ,p_bba_attribute4 => p_bba_attribute4
592 ,p_bba_attribute5 => p_bba_attribute5
593 ,p_bba_attribute6 => p_bba_attribute6
594 ,p_bba_attribute7 => p_bba_attribute7
595 ,p_bba_attribute8 => p_bba_attribute8
596 ,p_bba_attribute9 => p_bba_attribute9
597 ,p_bba_attribute10 => p_bba_attribute10
598 ,p_bba_attribute11 => p_bba_attribute11
599 ,p_bba_attribute12 => p_bba_attribute12
600 ,p_bba_attribute13 => p_bba_attribute13
601 ,p_bba_attribute14 => p_bba_attribute14
602 ,p_bba_attribute15 => p_bba_attribute15
603 ,p_bba_attribute16 => p_bba_attribute16
604 ,p_bba_attribute17 => p_bba_attribute17
605 ,p_bba_attribute18 => p_bba_attribute18
606 ,p_bba_attribute19 => p_bba_attribute19
607 ,p_bba_attribute20 => p_bba_attribute20
608 ,p_bba_attribute21 => p_bba_attribute21
609 ,p_bba_attribute22 => p_bba_attribute22
610 ,p_bba_attribute23 => p_bba_attribute23
611 ,p_bba_attribute24 => p_bba_attribute24
612 ,p_bba_attribute25 => p_bba_attribute25
613 ,p_bba_attribute26 => p_bba_attribute26
614 ,p_bba_attribute27 => p_bba_attribute27
615 ,p_bba_attribute28 => p_bba_attribute28
616 ,p_bba_attribute29 => p_bba_attribute29
617 ,p_bba_attribute30 => p_bba_attribute30
618 ,p_balance_amount_id => p_balance_amount_id
619 ,p_object_version_number => l_balance_amount_ovn
620 );
621 --
622 --
623 hr_utility.set_location(l_proc, 50);
624 --
625 -- Call After Process User Hook
626 --
627 begin
628 PER_BF_BALANCE_AMOUNTS_BK2.UPDATE_BALANCE_AMOUNT_A
629 (p_effective_date => p_effective_date
630 ,p_ytd_amount => p_ytd_amount
631 ,p_fytd_amount => p_fytd_amount
632 ,p_ptd_amount => p_ptd_amount
633 ,p_mtd_amount => p_mtd_amount
634 ,p_qtd_amount => p_qtd_amount
635 ,p_run_amount => p_run_amount
636 ,p_currency_code => p_currency_code
637 ,p_bba_attribute_category => p_bba_attribute_category
638 ,p_bba_attribute1 => p_bba_attribute1
639 ,p_bba_attribute2 => p_bba_attribute2
640 ,p_bba_attribute3 => p_bba_attribute3
641 ,p_bba_attribute4 => p_bba_attribute4
642 ,p_bba_attribute5 => p_bba_attribute5
643 ,p_bba_attribute6 => p_bba_attribute6
644 ,p_bba_attribute7 => p_bba_attribute7
645 ,p_bba_attribute8 => p_bba_attribute8
646 ,p_bba_attribute9 => p_bba_attribute9
647 ,p_bba_attribute10 => p_bba_attribute10
648 ,p_bba_attribute11 => p_bba_attribute11
649 ,p_bba_attribute12 => p_bba_attribute12
650 ,p_bba_attribute13 => p_bba_attribute13
651 ,p_bba_attribute14 => p_bba_attribute14
652 ,p_bba_attribute15 => p_bba_attribute15
653 ,p_bba_attribute16 => p_bba_attribute16
654 ,p_bba_attribute17 => p_bba_attribute17
655 ,p_bba_attribute18 => p_bba_attribute18
656 ,p_bba_attribute19 => p_bba_attribute19
657 ,p_bba_attribute20 => p_bba_attribute20
658 ,p_bba_attribute21 => p_bba_attribute21
659 ,p_bba_attribute22 => p_bba_attribute22
660 ,p_bba_attribute23 => p_bba_attribute23
661 ,p_bba_attribute24 => p_bba_attribute24
662 ,p_bba_attribute25 => p_bba_attribute25
663 ,p_bba_attribute26 => p_bba_attribute26
664 ,p_bba_attribute27 => p_bba_attribute27
665 ,p_bba_attribute28 => p_bba_attribute28
666 ,p_bba_attribute29 => p_bba_attribute29
667 ,p_bba_attribute30 => p_bba_attribute30
668 ,p_balance_amount_id => p_balance_amount_id
669 ,p_balance_amount_ovn => l_balance_amount_ovn
670 );
671 exception
672 when hr_api.cannot_find_prog_unit then
673 hr_api.cannot_find_prog_unit_error
674 (p_module_name => 'UPDATE_BALANCE_AMOUNT'
675 ,p_hook_type => 'AP'
676 );
677 end;
678 hr_utility.set_location(l_proc, 60);
679 --
680 -- When in validation only mode raise the Validate_Enabled exception
681 --
682 if p_validate then
683 raise hr_api.validate_enabled;
684 end if;
685 --
686 -- Set all output arguments
687 --
688 p_balance_amount_ovn := l_balance_amount_ovn;
689 --
690 hr_utility.set_location(' Leaving:'||l_proc, 70);
691 exception
692 when hr_api.validate_enabled then
693 --
694 -- As the Validate_Enabled exception has been raised
695 -- we must rollback to the savepoint
696 --
697 rollback to update_balance_amount;
698 --
699 -- Only set output warning arguments
700 -- (Any key or derived arguments must be set to null
701 -- when validation only mode is being used.)
702 --
703 p_balance_amount_ovn := null;
704 --
705 hr_utility.set_location(' Leaving:'||l_proc, 80);
706 when others then
707 --
708 -- A validation or unexpected error has occured
709 --
710 rollback to update_balance_amount;
711 -- set out variable
712 p_balance_amount_ovn := null;
713 hr_utility.set_location(' Leaving:'||l_proc, 90);
714 raise;
715 end UPDATE_BALANCE_AMOUNT;
716 --
717 -- ----------------------------------------------------------------------------
718 -- |---------------------< <delete_balance_amount> >--------------------------|
719 -- ----------------------------------------------------------------------------
720 --
721 procedure delete_balance_amount
722 (p_validate in boolean default false
723 ,p_balance_amount_id in number
724 ,p_balance_amount_ovn in number
725 ) is
726 --
727 -- Declare cursors and local variables
728 --
729 --
730 CURSOR csr_get_pa_id IS
731 SELECT pa.processed_assignment_id, pa.object_version_number
732 FROM PER_BF_BALANCE_AMOUNTS ba
733 ,PER_BF_PROCESSED_ASSIGNMENTS pa
734 WHERE ba.balance_amount_id = p_balance_amount_id
735 AND ba.processed_assignment_id = pa.processed_assignment_id;
736 --
737 CURSOR csr_chk_pa_for_del
738 (p_processed_assignment_id NUMBER) IS
739 SELECT 1
740 FROM per_bf_balance_amounts
741 WHERE processed_assignment_id = p_processed_assignment_id
742 AND balance_amount_id <> p_balance_amount_id
743 UNION
744 SELECT 1
745 FROM per_bf_payment_details
746 WHERE processed_assignment_id = p_processed_assignment_id;
747 --
748 l_processed_assignment_id NUMBER;
749 l_processed_assignment_ovn NUMBER;
750 l_temp VARCHAR2(1);
751 l_delete_pa_row BOOLEAN;
752 l_proc varchar2(72) := g_package||'delete_balance_amount';
753 begin
754 hr_utility.set_location('Entering:'|| l_proc, 10);
755 --
756 -- Issue a savepoint
757 --
758 savepoint delete_balance_amount;
759 hr_utility.set_location(l_proc, 20);
760 --
761 -- Truncate the time portion from all IN date parameters
762 --
763 --
764 --
765 -- Call Before Process User Hook
766 --
767 begin
768 PER_BF_BALANCE_AMOUNTS_BK3.DELETE_BALANCE_AMOUNT_B
769 (
770 p_balance_amount_id => p_balance_amount_id
771 ,p_balance_amount_ovn => p_balance_amount_ovn
772 );
773 --
774 exception
775 when hr_api.cannot_find_prog_unit then
776 hr_api.cannot_find_prog_unit_error
777 (p_module_name => 'DELETE_BALANCE_AMOUNT'
778 ,p_hook_type => 'BP'
779 );
780 end;
781 hr_utility.set_location(l_proc, 30);
782 --
783 -- Validation in addition to Row Handlers
784 --
785 --
786 hr_utility.set_location(l_proc, 40);
787 --
788 -- Process Logic
789 --
790 --
791 -- If no other entity uses the processed_assignment row that the FK
792 -- in PER_BF_BALANCE_AMOUNTS references, then the row will be removed
793 -- from PER_BF_PROCESSED_ASSIGNMENTS.
794 --
795 -- Obtain the processed assignment ID related to this row.
796 --
797 OPEN csr_get_pa_id;
798 FETCH csr_get_pa_id INTO l_processed_assignment_id
799 , l_processed_assignment_ovn;
800 CLOSE csr_get_pa_id;
801 --
802 -- Check whether the processed assignment is used elsewhere.
803 --
804 OPEN csr_chk_pa_for_del
805 (p_processed_assignment_id => l_processed_assignment_id);
806 FETCH csr_chk_pa_for_del INTO l_temp;
807 --
808 IF csr_chk_pa_for_del%NOTFOUND THEN
809 --
810 CLOSE csr_chk_pa_for_del;
811 --
812 -- The row can be deleted from PER_BF_PROCESSED_ASSIGNMENTS
813 -- so set a boolean and it will be removed later.
814 --
815 l_delete_pa_row := TRUE;
816 --
817 ELSE
818 --
819 CLOSE csr_chk_pa_for_del;
820 --
821 l_delete_pa_row := FALSE;
822 --
823 END IF;
824 --
825 -- Delete the row in per_bf_balance_amounts
826 --
827 PER_BBA_DEL.DEL
828 (
829 p_balance_amount_id => p_balance_amount_id
830 ,p_object_version_number => p_balance_amount_ovn
831 );
832 --
833 --
834 IF l_delete_pa_row THEN
835 --
836 -- The processed_assignment row is to be removed, so
837 -- remove it
838 --
839 per_bpa_del.del
840 ( p_processed_assignment_id => l_processed_assignment_id
841 , p_object_version_number => l_processed_assignment_ovn
842 );
843 --
844 END IF;
845 --
846 hr_utility.set_location(l_proc, 50);
847 --
848 -- Call After Process User Hook
849 --
850 begin
851 PER_BF_BALANCE_AMOUNTS_BK3.DELETE_BALANCE_AMOUNT_A
852 (
853 p_balance_amount_id => p_balance_amount_id
854 ,p_balance_amount_ovn => p_balance_amount_ovn
855 );
856 exception
857 when hr_api.cannot_find_prog_unit then
858 hr_api.cannot_find_prog_unit_error
859 (p_module_name => 'DELETE_BALANCE_AMOUNT'
860 ,p_hook_type => 'AP'
861 );
862 end;
863 hr_utility.set_location(l_proc, 60);
864 --
865 -- When in validation only mode raise the Validate_Enabled exception
866 --
867 if p_validate then
868 raise hr_api.validate_enabled;
869 end if;
870 --
871 -- Set all output arguments
872 --
873 hr_utility.set_location(' Leaving:'||l_proc, 70);
874 exception
875 when hr_api.validate_enabled then
876 --
877 -- As the Validate_Enabled exception has been raised
878 -- we must rollback to the savepoint
879 --
880 rollback to delete_balance_amount;
881 --
882 -- Only set output warning arguments
883 -- (Any key or derived arguments must be set to null
884 -- when validation only mode is being used.)
885 --
886 hr_utility.set_location(' Leaving:'||l_proc, 80);
887 when others then
888 --
889 -- A validation or unexpected error has occured
890 --
891 rollback to delete_balance_amount;
892 hr_utility.set_location(' Leaving:'||l_proc, 90);
893 raise;
894 end DELETE_BALANCE_AMOUNT;
895 --
896 end PER_BF_BALANCE_AMOUNTS_API;