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