[Home] [Help]
PACKAGE BODY: APPS.PAY_CE_RECONCILIATION_PKG
Source
1 package body pay_ce_reconciliation_pkg as
2 /* $Header: pycerecn.pkb 120.1 2005/09/30 12:42:32 adkumar noship $ */
3
4 procedure reconcile_payment(p_payment_id number,
5 p_cleared_date date,
6 p_trx_amount number,
7 p_trx_type varchar2,
8 p_last_updated_by number,
9 p_last_update_login number,
10 p_created_by number) is
11
12 l_payment_base_value number;
13 l_rate_type varchar2(30);
14
15 e_payment_voided exception;
16 e_payment_amount_mismatch exception;
17 e_no_matching_payment exception;
18
19 l_pay_currency_code pay_org_payment_methods_f.currency_code%type;
20 l_recon_currency_code pay_org_payment_methods_f.currency_code%type;
21 l_value Pay_pre_payments.value%type;
22 l_base_currency_value pay_pre_payments.base_currency_value%type;
23 l_action_status pay_payroll_actions.action_status%type;
24 l_business_group_id number;
25
26 begin
27
28 hr_utility.set_location('pay_ce_reconciliation_pkg.reconcile_payment', 10);
29
30 --Bug No. 4644827
31 pay_maintain_bank_acct.get_payment_details(
32 p_payment_id => p_payment_id,
33 p_voided_payment => false,
34 p_pay_currency_code => l_pay_currency_code,
35 p_recon_currency_code => l_recon_currency_code,
36 p_value => l_value,
37 p_base_currency_value => l_base_currency_value,
38 p_action_status => l_action_status,
39 p_business_group_Id => l_business_group_id
40 );
41 -- since currency code is mandatory, so i am supposing that it can not be null
42 if (l_pay_currency_code is null) then
43
44 -- we could be trying to reconcile a voided payment, which is fine
45 -- as it will be reconciled against a stop pay line on the bank
46 -- statement. but we still want to keep it seperate as we may
47 -- want to handle it differently in the future.
48
49 pay_maintain_bank_acct.get_payment_details(
50 p_payment_id => p_payment_id,
51 p_voided_payment => true,
52 p_pay_currency_code => l_pay_currency_code,
53 p_recon_currency_code => l_recon_currency_code,
54 p_value => l_value,
55 p_base_currency_value => l_base_currency_value,
56 p_action_status => l_action_status,
57 p_business_group_Id => l_business_group_id
58 );
59 if ((l_pay_currency_code is not null) and not(payment_reconciled(p_payment_id))) then
60
61 if l_pay_currency_code <> l_recon_currency_code then
62
63 l_rate_type := hr_currency_pkg.get_rate_type(l_business_group_id,
64 p_cleared_date,
65 'P');
66
67 l_payment_base_value := hr_currency_pkg.convert_amount(
68 l_pay_currency_code,
69 l_recon_currency_code,
70 p_cleared_date,
71 p_trx_amount,
72 l_rate_type);
73
74
75 else
76
77 l_payment_base_value := p_trx_amount;
78
79 end if;
80
81 if l_value <> p_trx_amount then
82
83 -- the amount on the statement and the amount being reconciled
84 -- didn't match, so something's afoot.
85
86 raise e_payment_amount_mismatch;
87
88 else
89
90 -- everything matches, so mark the payment as reconciled
91
92 update_reconciled_payment(p_payment_id,
93 p_trx_amount,
94 l_payment_base_value,
95 p_trx_type,
96 p_cleared_date,
97 'C');
98
99 end if;
100
101 else
102
103 -- we couldn't find a payment that matches the line on the statement
104
105 raise e_no_matching_payment;
106
107 end if;
108
109 elsif (not(payment_reconciled(p_payment_id))) then
110
111 hr_utility.set_location('pay_ce_reconciliation_pkg.reconcile_payment', 20);
112
113 if l_pay_currency_code <> l_recon_currency_code then
114
115 l_rate_type := hr_currency_pkg.get_rate_type(l_business_group_id,
116 p_cleared_date,
117 'P');
118
119 l_payment_base_value := hr_currency_pkg.convert_amount(
120 l_pay_currency_code,
121 l_recon_currency_code,
122 p_cleared_date,
123 p_trx_amount,
124 l_rate_type);
125
126
127 else
128
129 l_payment_base_value := p_trx_amount;
130
131 end if;
132
133 if l_action_status = 'V' then
134
135 raise e_payment_voided;
136
137 elsif l_value <> p_trx_amount then
138
139 raise e_payment_amount_mismatch;
140
141 else
142
143 update_reconciled_payment(p_payment_id,
144 p_trx_amount,
145 l_payment_base_value,
146 p_trx_type,
147 p_cleared_date,
148 'C');
149 end if;
150
151 else
152
153 raise e_no_matching_payment;
154
155 end if;
156
157 exception
158 when e_payment_voided then
159
160 -- we want to reconcile void payments because they will only be
161 -- reconciled to a stopped payment line. but kept it in an exception
162 -- for now, because it is still an unusual condition.
163
164 update_reconciled_payment(p_payment_id,
165 p_trx_amount,
166 l_payment_base_value,
167 p_trx_type,
168 p_cleared_date,
169 'C');
170
171 when e_payment_amount_mismatch then
172
173 update_reconciled_payment(p_payment_id,
174 null,
175 null,
176 p_trx_type,
177 null,
178 'E');
179
180 when e_no_matching_payment then
181
182 fnd_message.set_name('PAY', 'PAY_52789_NO_MATCHING_PAYMENT');
183 app_exception.raise_exception;
184
185 when others then
186
187 raise;
188
189 end reconcile_payment;
190
191
192 procedure update_reconciled_payment(p_payment_id number,
193 p_trx_amount number,
194 p_base_trx_amount number,
195 p_trx_type varchar2,
196 p_cleared_date date,
197 p_payment_status varchar2) is
198
199 l_cleared_base_amount number;
200
201 begin
202
203 hr_utility.set_location('pay_ce_reconciliation_pkg.update_reconciled_payment', 10);
204
205 insert into pay_ce_reconciled_payments(
206 reconciled_payment_id,
207 assignment_action_id,
208 trx_type,
209 cleared_amount,
210 cleared_date,
211 status_code,
212 cleared_base_amount)
213 values(
214 pay_ce_reconciled_payments_s.nextval,
215 p_payment_id,
216 p_trx_type,
217 p_trx_amount,
218 p_cleared_date,
219 p_payment_status,
220 p_base_trx_amount);
221
222
223 exception
224 when others then
225 app_exception.raise_exception;
226
227 end update_reconciled_payment;
228
229
230 procedure reverse_reconcile(p_payment_id number) is
231
232 l_reconciled_payment_id number;
233 e_payment_not_reconciled exception;
234
235 cursor c_reconciled_payment is
236 select
237 reconciled_payment_id
238 from
239 pay_ce_reconciled_payments
240 where
241 assignment_action_id = p_payment_id;
242
243 begin
244
245 hr_utility.set_location('pay_ce_reconciliation_pkg.reverse_reconcile', 10);
246
247 open c_reconciled_payment;
248 fetch c_reconciled_payment into l_reconciled_payment_id;
249 if c_reconciled_payment%found then
250
251 delete from pay_ce_reconciled_payments
252 where reconciled_payment_id = l_reconciled_payment_id;
253
254 else
255
256 raise e_payment_not_reconciled;
257
258 end if;
259
260 close c_reconciled_payment;
261
262 exception
263 when e_payment_not_reconciled then
264
265 fnd_message.set_name('PAY', 'PAY_52790_PAYMENT_NOT_CLEARED');
266 app_exception.raise_exception;
267
268 when others then
269
270 app_exception.raise_exception;
271
272 end reverse_reconcile;
273
274
275 function payment_reconciled(p_payment_id number) return boolean is
276
277 l_dummy varchar2(1);
278
279 cursor c_payment is
280 select
281 null
282 from
283 pay_ce_reconciled_payments
284 where
285 assignment_action_id = p_payment_id
286 and status_code = 'C';
287
288 begin
289
290 open c_payment;
291 fetch c_payment into l_dummy;
292 if c_payment%found then
293
294 close c_payment;
295 return true;
296
297 else
298
299 close c_payment;
300 return false;
301
302 end if;
303
304 end payment_reconciled;
305
306
307 function payinfo(p_identifier varchar2,
308 p_assignment_action_id number)
309 return varchar2 is
310 --
311 l_effective_date pay_payroll_actions.effective_date%type;
312 l_payment_type_id pay_org_payment_methods_f.payment_type_id%type;
313 l_org_payment_method_id pay_pre_payments.org_payment_method_id%type;
314 l_personal_payment_method_id pay_pre_payments.personal_payment_method_id%type;
315 l_pre_payment_id pay_pre_payments.pre_payment_id%type;
316 l_payroll_action_id pay_assignment_actions.payroll_action_id%type;
317 l_identifier_value varchar2(255);
318 --
319 begin
320 --
321 select PA.effective_date,
322 OP.payment_type_id,
323 PP.org_payment_method_id,
324 PP.personal_payment_method_id,
325 PP.PRE_PAYMENT_ID,
326 AA.payroll_action_id
327 into l_effective_date,
328 l_payment_type_id,
329 l_org_payment_method_id,
330 l_personal_payment_method_id,
331 l_pre_payment_id,
332 l_payroll_action_id
333 from pay_payroll_actions PA,
334 pay_assignment_actions AA,
335 -- pay_action_interlocks INT,
336 pay_pre_payments PP,
337 pay_org_payment_methods_f OP
338 where PA.payroll_action_id = AA.payroll_action_id
339 and AA.assignment_action_id = p_assignment_action_id
340 -- and AA.assignment_action_id = INT.locking_action_id
341 -- and PP.assignment_action_id = INT.locked_action_id
342 and PP.pre_payment_id = AA.pre_payment_id
343 and PP.org_payment_method_id = OP.org_payment_method_id
344 and PA.effective_date between OP.effective_start_date
345 and OP.effective_end_date ;
346 --
347 l_identifier_value := payment_transaction_info(l_effective_date,
348 p_identifier,
349 l_payroll_action_id,
350 l_payment_type_id,
351 l_org_payment_method_id,
352 l_personal_payment_method_id,
353 p_assignment_action_id,
354 l_pre_payment_id,
355 '/');
356 return l_identifier_value;
357 --
358 end payinfo;
359 --
360 function payment_transaction_info(p_effective_date date,
361 p_identifier_name varchar2,
362 p_payroll_action_id number,
363 p_payment_type_id number,
364 p_org_payment_method_id number,
365 p_personal_payment_method_id number,
366 p_assignment_action_id number,
367 p_pre_payment_id number,
368 p_delimiter_string varchar2 default '/') return varchar2 is
369 --
370 l_function_name pay_payment_types.reconciliation_function%TYPE;
371 l_identifier_value varchar2(255);
372 sql_curs number;
373 statem varchar2(2000);
374 rows_processed number;
375 --
376 cursor csr_payment_function is
377 select pyt.reconciliation_function
378 from pay_payment_types pyt
379 where pyt.payment_type_id = p_payment_type_id
380 and pyt.reconciliation_function is not null;
381 --
382 cursor csr_payee_bank_details (p_info_type varchar2) is
383 select pay_ce_support_pkg.bank_segment_value(
384 ppm.external_account_id,
385 p_info_type,
386 nvl(ppt.TERRITORY_CODE,pbg.legislation_code))
387 from per_business_groups_perf pbg,
388 pay_payment_types ppt,
389 pay_personal_payment_methods_f ppm
390 where ppm.personal_payment_method_id = p_personal_payment_method_id
391 and p_effective_date between ppm.effective_start_date
392 and ppm.effective_end_date
393 and ppt.payment_type_id = p_payment_type_id
394 and ppm.business_group_id = pbg.business_group_id;
395 --
396 begin
397 --
398 open csr_payment_function;
399 fetch csr_payment_function into l_function_name;
400 if csr_payment_function%notfound then
401 close csr_payment_function;
402 --
403 if p_identifier_name = 'PAYEE_BANK_ACCOUNT_NAME' then
404 --
405 open csr_payee_bank_details('BANK_ACCOUNT_NAME');
406 fetch csr_payee_bank_details into l_identifier_value;
407 close csr_payee_bank_details;
408 return l_identifier_value;
409 --
410 elsif p_identifier_name = 'PAYEE_BANK_ACCOUNT_NUMBER' then
411 --
412 open csr_payee_bank_details('BANK_ACCOUNT_NUMBER');
413 fetch csr_payee_bank_details into l_identifier_value;
414 close csr_payee_bank_details;
415 return l_identifier_value;
416 --
417 elsif p_identifier_name = 'PAYEE_BANK_BRANCH' then
418 --
419 open csr_payee_bank_details('BANK_BRANCH');
420 fetch csr_payee_bank_details into l_identifier_value;
421 close csr_payee_bank_details;
422 return l_identifier_value;
423 --
424 elsif p_identifier_name = 'PAYEE_BANK_NAME' then
425 --
426 open csr_payee_bank_details('BANK_NAME');
427 fetch csr_payee_bank_details into l_identifier_value;
428 close csr_payee_bank_details;
429 return l_identifier_value;
430 --
431 else
432 return null;
433 end if;
434 --
435 else
436 close csr_payment_function;
437 --
438 if p_identifier_name = 'PAYEE_BANK_ACCOUNT_NAME' then
439 --
440 open csr_payee_bank_details('BANK_ACCOUNT_NAME');
444 --
441 fetch csr_payee_bank_details into l_identifier_value;
442 close csr_payee_bank_details;
443 return l_identifier_value;
445 elsif p_identifier_name = 'PAYEE_BANK_ACCOUNT_NUMBER' then
446 --
447 open csr_payee_bank_details('BANK_ACCOUNT_NUMBER');
448 fetch csr_payee_bank_details into l_identifier_value;
449 close csr_payee_bank_details;
450 return l_identifier_value;
451 --
452 elsif p_identifier_name = 'PAYEE_BANK_BRANCH' then
453 --
454 open csr_payee_bank_details('BANK_BRANCH');
455 fetch csr_payee_bank_details into l_identifier_value;
456 close csr_payee_bank_details;
457 return l_identifier_value;
458 --
459 elsif p_identifier_name = 'PAYEE_BANK_NAME' then
460 --
461 open csr_payee_bank_details('BANK_NAME');
462 fetch csr_payee_bank_details into l_identifier_value;
463 close csr_payee_bank_details;
464 return l_identifier_value;
465 --
466 end if;
467 --
468 begin
469 statem := 'BEGIN
470 select substr('||l_function_name||'(:p_effective_date,
471 :p_identifier_name,
472 :p_payroll_action_id,
473 :p_payment_type_id,
474 :p_org_payment_method_id,
475 :p_personal_payment_method_id,
476 :p_assignment_action_id,
477 :p_pre_payment_id,
478 :p_delimiter_string),1,255)
479 into :identifier_value
480 from dual;
481 END;';
482 --
483 sql_curs := dbms_sql.open_cursor;
484 --
485 dbms_sql.parse(sql_curs,
486 statem,
487 dbms_sql.v7);
488 --
489 dbms_sql.bind_variable(sql_curs, 'p_effective_date', p_effective_date);
490 dbms_sql.bind_variable(sql_curs, 'p_identifier_name', p_identifier_name);
491 dbms_sql.bind_variable(sql_curs, 'p_payroll_action_id', p_payroll_action_id);
492 dbms_sql.bind_variable(sql_curs, 'p_payment_type_id', p_payment_type_id);
493 dbms_sql.bind_variable(sql_curs, 'p_org_payment_method_id', p_org_payment_method_id);
494 dbms_sql.bind_variable(sql_curs, 'p_personal_payment_method_id', p_personal_payment_method_id);
495 dbms_sql.bind_variable(sql_curs, 'p_assignment_action_id', p_assignment_action_id);
496 dbms_sql.bind_variable(sql_curs, 'p_pre_payment_id', p_pre_payment_id);
497 dbms_sql.bind_variable(sql_curs, 'p_delimiter_string', p_delimiter_string);
498 dbms_sql.bind_variable(sql_curs, 'identifier_value', l_identifier_value,255);
499 --
500 rows_processed := dbms_sql.execute(sql_curs);
501 --
502 dbms_sql.variable_value(sql_curs, 'identifier_value', l_identifier_value);
503 --
504 dbms_sql.close_cursor(sql_curs);
505 --
506 exception
507 when others then
508 --
509 if dbms_sql.is_open(sql_curs) then
510 dbms_sql.close_cursor(sql_curs);
511 end if;
512 --
513 l_identifier_value := null;
514 --
515 end;
516 --
517 return l_identifier_value;
518 --
519 end if;
520 --
521 end payment_transaction_info;
522
523
524 end pay_ce_reconciliation_pkg;