[Home] [Help]
PACKAGE BODY: APPS.PAY_ORG_PAYMENT_METHODS_PKG
Source
1 PACKAGE BODY pay_org_payment_methods_pkg AS
2 /* $Header: pyopm01t.pkb 120.6 2006/08/31 12:42:53 pgongada noship $ */
3 g_dummy number(1);
4 g_business_group_id number(15);
5 g_validation_start_date date;
6 g_validation_end_date date;
7 -----------------------------------------------------------------------------
8 --
9 -- Standard Insert procedure
10 --
11 procedure insert_row(
12 p_row_id in out nocopy varchar2,
13 p_org_payment_method_id in out nocopy number,
14 p_effective_start_date date,
15 p_effective_end_date date,
16 p_business_group_id number,
17 p_external_account_id number,
18 p_currency_code varchar2,
19 p_payment_type_id number,
20 p_defined_balance_id number,
21 p_org_payment_method_name varchar2,
22 p_base_opm_name varchar2,
23 p_comment_id number,
24 p_attribute_category varchar2,
25 p_attribute1 varchar2,
26 p_attribute2 varchar2,
27 p_attribute3 varchar2,
28 p_attribute4 varchar2,
29 p_attribute5 varchar2,
30 p_attribute6 varchar2,
31 p_attribute7 varchar2,
32 p_attribute8 varchar2,
33 p_attribute9 varchar2,
34 p_attribute10 varchar2,
35 p_attribute11 varchar2,
36 p_attribute12 varchar2,
37 p_attribute13 varchar2,
38 p_attribute14 varchar2,
39 p_attribute15 varchar2,
40 p_attribute16 varchar2,
41 p_attribute17 varchar2,
42 p_attribute18 varchar2,
43 p_attribute19 varchar2,
44 p_attribute20 varchar2,
45 p_pmeth_information_category varchar2,
46 p_pmeth_information1 varchar2,
47 p_pmeth_information2 varchar2,
48 p_pmeth_information3 varchar2,
49 p_pmeth_information4 varchar2,
50 p_pmeth_information5 varchar2,
51 p_pmeth_information6 varchar2,
52 p_pmeth_information7 varchar2,
53 p_pmeth_information8 varchar2,
54 p_pmeth_information9 varchar2,
55 p_pmeth_information10 varchar2,
56 p_pmeth_information11 varchar2,
57 p_pmeth_information12 varchar2,
58 p_pmeth_information13 varchar2,
59 p_pmeth_information14 varchar2,
60 p_pmeth_information15 varchar2,
61 p_pmeth_information16 varchar2,
62 p_pmeth_information17 varchar2,
63 p_pmeth_information18 varchar2,
64 p_pmeth_information19 varchar2,
65 p_pmeth_information20 varchar2,
66 p_asset_code_combination_id number,
67 p_set_of_books_id number,
68 p_transfer_to_gl_flag varchar2,
69 p_cost_payment varchar2,
70 p_cost_cleared_payment varchar2,
71 p_cost_cleared_void_payment varchar2,
72 p_exclude_manual_payment varchar2,
73 p_gl_set_of_books_id number,
74 p_gl_cash_ac_id number,
75 p_gl_cash_clearing_ac_id number,
76 p_gl_control_ac_id number,
77 p_gl_error_ac_id number,
78 p_default_gl_account varchar2,
79 p_bank_account_id number,
80 p_pay_gl_account_id_out out nocopy number ) is
81 --
82 cursor c1 is
83 select pay_org_payment_methods_s.nextval
84 from sys.dual;
85 cursor c2 is
86 select rowid
87 from pay_org_payment_methods_f
88 where org_payment_method_id = P_ORG_PAYMENT_METHOD_ID
89 and effective_start_date = P_EFFECTIVE_START_DATE
90 and effective_end_date = P_EFFECTIVE_END_DATE;
91 --
92 begin
93 open c1;
94 fetch c1 into P_ORG_PAYMENT_METHOD_ID;
95 close c1;
96 --
97 begin
98 insert into pay_org_payment_methods_f (
99 org_payment_method_id ,
100 effective_start_date ,
101 effective_end_date ,
102 business_group_id ,
103 external_account_id ,
104 currency_code ,
105 payment_type_id ,
106 defined_balance_id ,
107 org_payment_method_name ,
108 comment_id ,
109 attribute_category ,
110 attribute1 ,
111 attribute2 ,
112 attribute3 ,
113 attribute4 ,
114 attribute5 ,
115 attribute6 ,
116 attribute7 ,
117 attribute8 ,
118 attribute9 ,
119 attribute10 ,
120 attribute11 ,
121 attribute12 ,
122 attribute13 ,
123 attribute14 ,
124 attribute15 ,
125 attribute16 ,
126 attribute17 ,
127 attribute18 ,
128 attribute19 ,
129 attribute20 ,
130 pmeth_information_category ,
131 pmeth_information1 ,
132 pmeth_information2 ,
133 pmeth_information3 ,
134 pmeth_information4 ,
135 pmeth_information5 ,
136 pmeth_information6 ,
137 pmeth_information7 ,
138 pmeth_information8 ,
139 pmeth_information9 ,
140 pmeth_information10,
141 pmeth_information11,
142 pmeth_information12,
143 pmeth_information13,
144 pmeth_information14,
145 pmeth_information15,
146 pmeth_information16,
147 pmeth_information17,
148 pmeth_information18,
149 pmeth_information19,
150 pmeth_information20,
151 transfer_to_gl_flag,
152 cost_payment,
153 cost_cleared_payment,
154 cost_cleared_void_payment,
155 exclude_manual_payment )
156 values (
157 p_org_payment_method_id ,
158 p_effective_start_date ,
159 p_effective_end_date ,
160 p_business_group_id ,
161 p_external_account_id ,
162 p_currency_code ,
163 p_payment_type_id ,
164 p_defined_balance_id ,
165 p_base_opm_name ,
166 p_comment_id ,
167 p_attribute_category ,
168 p_attribute1 ,
169 p_attribute2 ,
170 p_attribute3 ,
171 p_attribute4 ,
172 p_attribute5 ,
173 p_attribute6 ,
174 p_attribute7 ,
175 p_attribute8 ,
176 p_attribute9 ,
177 p_attribute10 ,
178 p_attribute11 ,
179 p_attribute12 ,
180 p_attribute13 ,
181 p_attribute14 ,
182 p_attribute15 ,
183 p_attribute16 ,
184 p_attribute17 ,
185 p_attribute18 ,
186 p_attribute19 ,
187 p_attribute20 ,
188 p_pmeth_information_category ,
189 p_pmeth_information1 ,
190 p_pmeth_information2 ,
191 p_pmeth_information3 ,
192 p_pmeth_information4 ,
193 p_pmeth_information5 ,
194 p_pmeth_information6 ,
195 p_pmeth_information7 ,
196 p_pmeth_information8 ,
197 p_pmeth_information9 ,
198 p_pmeth_information10,
199 p_pmeth_information11,
200 p_pmeth_information12,
201 p_pmeth_information13,
202 p_pmeth_information14,
203 p_pmeth_information15,
204 p_pmeth_information16,
205 p_pmeth_information17,
206 p_pmeth_information18,
207 p_pmeth_information19,
208 p_pmeth_information20,
209 p_transfer_to_gl_flag,
210 p_cost_payment,
211 p_cost_cleared_payment,
212 p_cost_cleared_void_payment,
213 p_exclude_manual_payment );
214 --
215 -- **************************************************************************
216 -- insert into MLS table (TL)
217 --
218 insert into PAY_ORG_PAYMENT_METHODS_F_TL (
219 ORG_PAYMENT_METHOD_ID,
220 ORG_PAYMENT_METHOD_NAME,
221 LAST_UPDATE_DATE,
222 CREATION_DATE,
223 LANGUAGE,
224 SOURCE_LANG
225 ) select
226 P_ORG_PAYMENT_METHOD_ID,
227 P_ORG_PAYMENT_METHOD_NAME,
228 sysdate,
229 sysdate,
230 L.LANGUAGE_CODE,
231 userenv('LANG')
232 from FND_LANGUAGES L
233 where L.INSTALLED_FLAG in ('I', 'B')
234 and not exists
235 (select NULL
236 from PAY_ORG_PAYMENT_METHODS_F_TL T
237 where T.ORG_PAYMENT_METHOD_ID = P_ORG_PAYMENT_METHOD_ID
238 and T.LANGUAGE = L.LANGUAGE_CODE);
239
240 --
241 --
242 -- *******************************************************************************
243 --
244 end;
245
246 -- cash management integration: update asset_code_combination_id in
247 -- ap_bank_branches_all for the bank account associated with
248 -- this payment method. only do this if cash management integration
249 -- is active, i.e. both payroll and cash management are installed.
250
251 if pay_ce_support_pkg.pay_and_ce_licensed then
252
253 -- Bug No. 4644827
254 -- for r11.5 the same functionality is done through database trigger. Code
255 -- is for R12
256 if p_bank_account_id is not null AND p_external_account_id IS NOT NULL then
257 pay_maintain_bank_acct.update_payroll_bank_acct(
258 p_bank_account_id => p_bank_account_id,
259 p_external_account_id => p_external_account_id,
260 p_org_payment_method_id => P_ORG_PAYMENT_METHOD_ID);
261 end if;
262 --
263 pay_maintain_bank_acct.update_asset_ccid(
264 p_assest_ccid =>p_asset_code_combination_id,
265 p_set_of_books_id =>p_set_of_books_id,
266 p_external_account_id =>p_external_account_id
267 );
268 end if;
269
270 -- Costing of Payment changes
271
272 PAY_PAYMENT_GL_ACCOUNTS_PKG.INSERT_ROW
273 ( P_PAY_GL_ACCOUNT_ID => p_pay_gl_account_id_out,
274 P_EFFECTIVE_START_DATE => P_EFFECTIVE_START_DATE,
275 P_EFFECTIVE_END_DATE => P_EFFECTIVE_END_DATE,
276 P_SET_OF_BOOKS_ID => P_GL_SET_OF_BOOKS_ID,
277 P_GL_CASH_AC_ID => P_GL_CASH_AC_ID,
278 P_GL_CASH_CLEARING_AC_ID => P_GL_CASH_CLEARING_AC_ID,
279 P_GL_CONTROL_AC_ID => P_GL_CONTROL_AC_ID,
280 P_GL_ERROR_AC_ID => P_GL_ERROR_AC_ID,
281 P_EXTERNAL_ACCOUNT_ID => P_EXTERNAL_ACCOUNT_ID,
282 P_ORG_PAYMENT_METHOD_ID => P_ORG_PAYMENT_METHOD_ID,
283 P_DEFAULT_GL_ACCOUNT => P_DEFAULT_GL_ACCOUNT
284 );
285
286 --
287 open c2;
288 fetch c2 into P_ROW_ID;
289 close c2;
290 --
291 end insert_row;
292 --------------------------------------------------------------------------------
293 PROCEDURE set_translation_globals(p_business_group_id IN NUMBER,
294 p_validation_start_date IN DATE,
295 p_validation_end_date IN DATE) IS
296 BEGIN
297 g_business_group_id := p_business_group_id;
298 g_validation_start_date := p_validation_start_date;
299 g_validation_end_date := p_validation_end_date;
300 END set_translation_globals;
301 -----------------------------------------------------------------------------
302 procedure validate_translation(org_payment_method_id IN NUMBER,
303 language IN VARCHAR2,
304 org_payment_method_name IN VARCHAR2) IS
305 /*
306
307 This procedure fails if a payment method translation is already present in
308 the table for a given language. Otherwise, no action is performed. It is
309 used to ensure uniqueness of translated payment method names.
310
311 */
312
313 --
314 -- This cursor implements the validation we require,
315 -- and expects that the various package globals are set before
316 -- the call to this procedure is made. This is done from the
317 -- user-named trigger 'TRANSLATIONS' in the form
318 --
319 cursor c_translation(p_language IN VARCHAR2,
320 p_org_payment_method_name IN VARCHAR2,
321 p_org_payment_method_id IN NUMBER) IS
322 SELECT 1
323 FROM pay_org_payment_methods_f_tl ptt,
324 pay_org_payment_methods_f ptm
325 WHERE upper(ptt.org_payment_method_name)=upper(p_org_payment_method_name)
326 AND ptt.org_payment_method_id = ptm.org_payment_method_id
327 AND ptt.language = p_language
328 AND (ptm.org_payment_method_id <> p_org_payment_method_id OR p_org_payment_method_id IS NULL)
329 AND (ptm.business_group_id = g_business_group_id OR g_business_group_id IS NULL)
330 AND ((g_validation_start_date between ptm.effective_start_date and
331 ptm.effective_end_date) or
332 (g_validation_end_date between ptm.effective_start_date and
333 ptm.effective_end_date) or
334 (g_validation_start_date IS NULL or g_validation_end_date IS NULL) or
335 ((g_validation_start_date < ptm.effective_start_date) and
336 (g_validation_end_date > ptm.effective_end_date)));
337 l_package_name VARCHAR2(80) := 'PAY_ORG_PAYMENT_METHODS_PKG.VALIDATE_TRANSLATION';
338
339 BEGIN
340 hr_utility.set_location (l_package_name,10);
341 OPEN c_translation(language, org_payment_method_name,org_payment_method_id);
342 hr_utility.set_location (l_package_name,50);
343 FETCH c_translation INTO g_dummy;
344
345 IF c_translation%NOTFOUND THEN
346 hr_utility.set_location (l_package_name,60);
347 CLOSE c_translation;
348 ELSE
349 hr_utility.set_location (l_package_name,70);
350 CLOSE c_translation;
351 fnd_message.set_name('PAY','HR_TRANSLATION_EXISTS');
352 fnd_message.raise_error;
353 END IF;
354 hr_utility.set_location ('Leaving:'||l_package_name,80);
355 END validate_translation;
356 -----------------------------------------------------------------------------
357 --
358 -- Standard delete procedure
359 --
360 procedure delete_row(p_org_payment_method_id NUMBER,
361 p_row_id varchar2,
362 p_dt_delete_mode varchar2,
363 p_effective_date date,
364 p_org_effective_start_date date,
365 p_org_effective_end_date date
366 ) is
367 --
368 begin
369
370 PAY_PAYMENT_GL_ACCOUNTS_PKG.DELETE_ROW
371 (p_org_payment_method_id => p_org_payment_method_id
372 ,p_effective_date => p_effective_date
373 ,p_datetrack_mode => p_dt_delete_mode
374 ,p_org_eff_start_date => p_org_effective_start_date
375 ,p_org_eff_end_date => p_org_effective_end_date
376 );
377
378 delete from pay_org_payment_methods_f o
379 where o.rowid = chartorowid(P_ROW_ID);
380 --
381 -- ********************************************************************************
382 --
383 -- delete from MLS table (TL)
384 --
385 delete from PAY_ORG_PAYMENT_METHODS_F_TL
386 where ORG_PAYMENT_METHOD_ID = P_ORG_PAYMENT_METHOD_ID
387 and not exists
388 (select null
389 from pay_org_payment_methods_f o
390 where o.ORG_PAYMENT_METHOD_ID = P_ORG_PAYMENT_METHOD_ID
391 and o.rowid <> chartorowid(P_ROW_ID));
392 --
393 if sql%notfound then -- trap system errors during deletion
394 hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
395 hr_utility.set_message_token ('PROCEDURE','PAY_ORG_PAYMENT_METHODS_PKG.DELETE_TL_ROW');
396 end if;
397 --
398 -- ********************************************************************************
399 --
400 end delete_row;
401 -----------------------------------------------------------------------------
402 --
403 -- Standard lock procedure
404 --
405 procedure lock_row(
406 p_row_id varchar2,
407 p_org_payment_method_id number,
408 p_effective_start_date date,
409 p_effective_end_date date,
410 p_business_group_id number,
411 p_external_account_id number,
412 p_currency_code varchar2,
413 p_payment_type_id number,
414 p_defined_balance_id number,
415 p_base_opm_name varchar2,
416 p_comment_id number,
417 p_attribute_category varchar2,
418 p_attribute1 varchar2,
419 p_attribute2 varchar2,
420 p_attribute3 varchar2,
421 p_attribute4 varchar2,
422 p_attribute5 varchar2,
423 p_attribute6 varchar2,
424 p_attribute7 varchar2,
425 p_attribute8 varchar2,
426 p_attribute9 varchar2,
427 p_attribute10 varchar2,
428 p_attribute11 varchar2,
429 p_attribute12 varchar2,
430 p_attribute13 varchar2,
431 p_attribute14 varchar2,
432 p_attribute15 varchar2,
433 p_attribute16 varchar2,
434 p_attribute17 varchar2,
435 p_attribute18 varchar2,
436 p_attribute19 varchar2,
437 p_attribute20 varchar2,
438 p_pmeth_information_category varchar2,
439 p_pmeth_information1 varchar2,
440 p_pmeth_information2 varchar2,
441 p_pmeth_information3 varchar2,
442 p_pmeth_information4 varchar2,
443 p_pmeth_information5 varchar2,
444 p_pmeth_information6 varchar2,
445 p_pmeth_information7 varchar2,
446 p_pmeth_information8 varchar2,
447 p_pmeth_information9 varchar2,
448 p_pmeth_information10 varchar2,
449 p_pmeth_information11 varchar2,
450 p_pmeth_information12 varchar2,
451 p_pmeth_information13 varchar2,
452 p_pmeth_information14 varchar2,
453 p_pmeth_information15 varchar2,
454 p_pmeth_information16 varchar2,
455 p_pmeth_information17 varchar2,
456 p_pmeth_information18 varchar2,
457 p_pmeth_information19 varchar2,
458 p_pmeth_information20 varchar2,
459 p_transfer_to_gl_flag varchar2,
460 p_cost_payment varchar2,
461 p_cost_cleared_payment varchar2,
462 p_cost_cleared_void_payment varchar2,
463 p_exclude_manual_payment varchar2,
464 p_pay_gl_account_id number,
465 p_set_of_books_id number,
466 p_gl_cash_ac_id number,
467 p_gl_cash_clearing_ac_id number,
468 p_gl_control_ac_id number,
469 p_gl_error_ac_id number ) is
470 --
471 cursor OPM_CUR is
472 select *
473 from pay_org_payment_methods_f o
474 where o.rowid = chartorowid(P_ROW_ID)
475 FOR UPDATE OF ORG_PAYMENT_METHOD_ID NOWAIT;
476
477 cursor PGA_CUR is
478 select *
479 from pay_payment_gl_accounts_f pga
480 where pga.pay_gl_account_id = p_pay_gl_account_id
481 and p_effective_start_date between pga.effective_start_date and
482 pga.effective_end_date;
483 --
484 -- ***************************************************************************
485 --
486 OPM_REC OPM_CUR%rowtype;
487 PGA_REC PGA_CUR%rowtype;
488 --
489 begin
490 --
491 -- 115.4: ARUNDELL: Removed explicit lock of _TL table, the MLS strategy requires
492 -- that the base table is locked before update of the _TL table can take place,
493 -- which implies it is not necessary to lock both tables.
494 --
495 open OPM_CUR;
496 --
497 fetch OPM_CUR into OPM_REC;
498 --
499 if (OPM_CUR%NOTFOUND) then
500 close OPM_CUR;
501 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
502 hr_utility.set_message_token('PROCEDURE','pay_org_payment_methods_pkg.lock_row');
503 hr_utility.set_message_token('STEP','1');
504 hr_utility.raise_error;
505 end if;
506 close OPM_CUR;
507 --
508 --
509 if p_pay_gl_account_id is not null then
510 open PGA_CUR;
511 fetch PGA_CUR into PGA_REC;
512
513 if (PGA_CUR%NOTFOUND) then
514 close PGA_CUR;
515 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
516 hr_utility.set_message_token('PROCEDURE','pay_org_payment_methods_pkg.lock_row');
517 hr_utility.set_message_token('STEP','2');
518 hr_utility.raise_error;
519 end if;
520
521 close PGA_CUR;
522
523 else
524 pga_rec.set_of_books_id := p_set_of_books_id ;
525 pga_rec.gl_cash_ac_id := p_gl_cash_ac_id ;
526 pga_rec.gl_cash_clearing_ac_id := p_gl_cash_clearing_ac_id ;
527 pga_rec.gl_control_ac_id := p_gl_control_ac_id ;
528 pga_rec.gl_error_ac_id := p_gl_error_ac_id ;
529
530 end if;
531
532 --
533 -- ***************************************************************************
534 --
535 OPM_REC.currency_code := rtrim(OPM_REC.currency_code);
536 OPM_REC.org_payment_method_name := rtrim(OPM_REC.org_payment_method_name);
537 OPM_REC.attribute_category := rtrim(OPM_REC.attribute_category);
538 OPM_REC.attribute1 := rtrim(OPM_REC.attribute1);
539 OPM_REC.attribute2 := rtrim(OPM_REC.attribute2);
540 OPM_REC.attribute3 := rtrim(OPM_REC.attribute3);
541 OPM_REC.attribute4 := rtrim(OPM_REC.attribute4);
542 OPM_REC.attribute5 := rtrim(OPM_REC.attribute5);
543 OPM_REC.attribute6 := rtrim(OPM_REC.attribute6);
544 OPM_REC.attribute7 := rtrim(OPM_REC.attribute7);
545 OPM_REC.attribute8 := rtrim(OPM_REC.attribute8);
546 OPM_REC.attribute9 := rtrim(OPM_REC.attribute9);
547 OPM_REC.attribute10 := rtrim(OPM_REC.attribute10);
548 OPM_REC.attribute11 := rtrim(OPM_REC.attribute11);
549 OPM_REC.attribute12 := rtrim(OPM_REC.attribute12);
550 OPM_REC.attribute13 := rtrim(OPM_REC.attribute13);
551 OPM_REC.attribute14 := rtrim(OPM_REC.attribute14);
552 OPM_REC.attribute15 := rtrim(OPM_REC.attribute15);
553 OPM_REC.attribute16 := rtrim(OPM_REC.attribute16);
554 OPM_REC.attribute17 := rtrim(OPM_REC.attribute17);
555 OPM_REC.attribute18 := rtrim(OPM_REC.attribute18);
556 OPM_REC.attribute19 := rtrim(OPM_REC.attribute19);
557 OPM_REC.attribute20 := rtrim(OPM_REC.attribute20);
558 OPM_REC.pmeth_information_category := rtrim(OPM_REC.pmeth_information_category);
559 OPM_REC.pmeth_information1 := rtrim(OPM_REC.pmeth_information1);
560 OPM_REC.pmeth_information2 := rtrim(OPM_REC.pmeth_information2);
561 OPM_REC.pmeth_information3 := rtrim(OPM_REC.pmeth_information3);
562 OPM_REC.pmeth_information4 := rtrim(OPM_REC.pmeth_information4);
563 OPM_REC.pmeth_information5 := rtrim(OPM_REC.pmeth_information5);
564 OPM_REC.pmeth_information6 := rtrim(OPM_REC.pmeth_information6);
565 OPM_REC.pmeth_information7 := rtrim(OPM_REC.pmeth_information7);
566 OPM_REC.pmeth_information8 := rtrim(OPM_REC.pmeth_information8);
567 OPM_REC.pmeth_information9 := rtrim(OPM_REC.pmeth_information9);
568 OPM_REC.pmeth_information10 := rtrim(OPM_REC.pmeth_information10);
569 OPM_REC.pmeth_information11 := rtrim(OPM_REC.pmeth_information11);
570 OPM_REC.pmeth_information12 := rtrim(OPM_REC.pmeth_information12);
571 OPM_REC.pmeth_information13 := rtrim(OPM_REC.pmeth_information13);
572 OPM_REC.pmeth_information14 := rtrim(OPM_REC.pmeth_information14);
573 OPM_REC.pmeth_information15 := rtrim(OPM_REC.pmeth_information15);
574 OPM_REC.pmeth_information16 := rtrim(OPM_REC.pmeth_information16);
575 OPM_REC.pmeth_information17 := rtrim(OPM_REC.pmeth_information17);
576 OPM_REC.pmeth_information18 := rtrim(OPM_REC.pmeth_information18);
577 OPM_REC.pmeth_information19 := rtrim(OPM_REC.pmeth_information19);
578 OPM_REC.pmeth_information20 := rtrim(OPM_REC.pmeth_information20);
579 OPM_REC.transfer_to_gl_flag := rtrim(OPM_REC.transfer_to_gl_flag);
580 OPM_REC.cost_payment := rtrim(OPM_REC.cost_payment);
581 OPM_REC.cost_cleared_payment := rtrim(OPM_REC.cost_cleared_payment);
582 OPM_REC.cost_cleared_void_payment := rtrim(OPM_REC.cost_cleared_void_payment);
583 OPM_REC.exclude_manual_payment := rtrim(OPM_REC.exclude_manual_payment);
584 --
585 if (((opm_rec.org_payment_method_id = p_org_payment_method_id )
586 or (opm_rec.org_payment_method_id is null
587 and (p_org_payment_method_id is null)))
588 and ((opm_rec.effective_start_date = p_effective_start_date )
589 or (opm_rec.effective_start_date is null
590 and (p_effective_start_date is null)))
591 and ((opm_rec.effective_end_date = p_effective_end_date )
592 or (opm_rec.effective_end_date is null
593 and (p_effective_end_date is null)))
594 and ((opm_rec.business_group_id = p_business_group_id )
595 or (opm_rec.business_group_id is null
596 and (p_business_group_id is null)))
597 and ((opm_rec.external_account_id = p_external_account_id)
598 or (opm_rec.external_account_id is null
599 and (p_external_account_id is null)))
600 and ((opm_rec.currency_code = p_currency_code )
601 or (opm_rec.currency_code is null
602 and (p_currency_code is null)))
603 and ((opm_rec.payment_type_id = p_payment_type_id )
604 or (opm_rec.payment_type_id is null
605 and (p_payment_type_id is null)))
606 and ((opm_rec.defined_balance_id = p_defined_balance_id )
607 or (opm_rec.defined_balance_id is null
608 and (p_defined_balance_id is null)))
609 and ((opm_rec.org_payment_method_name = p_base_opm_name )
610 or (opm_rec.org_payment_method_name is null
611 and (p_base_opm_name is null)))
612 and ((opm_rec.comment_id = p_comment_id )
613 or (opm_rec.comment_id is null
614 and (p_comment_id is null)))
615 and ((opm_rec.attribute_category = p_attribute_category )
616 or (opm_rec.attribute_category is null
617 and (p_attribute_category is null)))
618 and ((opm_rec.attribute1 = p_attribute1 )
619 or (opm_rec.attribute1 is null
620 and (p_attribute1 is null)))
621 and ((opm_rec.attribute2 = p_attribute2 )
622 or (opm_rec.attribute2 is null
623 and (p_attribute2 is null)))
624 and ((opm_rec.attribute3 = p_attribute3 )
625 or (opm_rec.attribute3 is null
626 and (p_attribute3 is null)))
627 and ((opm_rec.attribute4 = p_attribute4 )
628 or (opm_rec.attribute4 is null
629 and (p_attribute4 is null)))
630 and ((opm_rec.attribute5 = p_attribute5 )
631 or (opm_rec.attribute5 is null
632 and (p_attribute5 is null)))
633 and ((opm_rec.attribute6 = p_attribute6 )
634 or (opm_rec.attribute6 is null
635 and (p_attribute6 is null)))
636 and ((opm_rec.attribute7 = p_attribute7 )
637 or (opm_rec.attribute7 is null
638 and (p_attribute7 is null)))
639 and ((opm_rec.attribute8 = p_attribute8 )
640 or (opm_rec.attribute8 is null
641 and (p_attribute8 is null)))
642 and ((opm_rec.attribute9 = p_attribute9 )
643 or (opm_rec.attribute9 is null
644 and (p_attribute9 is null)))
645 and ((opm_rec.attribute10 = p_attribute10 )
646 or (opm_rec.attribute10 is null
647 and (p_attribute10 is null)))
648 and ((opm_rec.attribute11 = p_attribute11 )
649 or (opm_rec.attribute11 is null
650 and (p_attribute11 is null)))
651 and ((opm_rec.attribute12 = p_attribute12 )
652 or (opm_rec.attribute12 is null
653 and (p_attribute12 is null)))
654 and ((opm_rec.attribute13 = p_attribute13 )
655 or (opm_rec.attribute13 is null
656 and (p_attribute13 is null)))
657 and ((opm_rec.attribute14 = p_attribute14 )
658 or (opm_rec.attribute14 is null
659 and (p_attribute14 is null)))
660 and ((opm_rec.attribute15 = p_attribute15 )
661 or (opm_rec.attribute15 is null
662 and (p_attribute15 is null)))
663 and ((opm_rec.attribute16 = p_attribute16 )
664 or (opm_rec.attribute16 is null
665 and (p_attribute16 is null)))
666 and ((opm_rec.attribute17 = p_attribute17 )
667 or (opm_rec.attribute17 is null
668 and (p_attribute17 is null)))
669 and ((opm_rec.attribute18 = p_attribute18 )
670 or (opm_rec.attribute18 is null
671 and (p_attribute18 is null)))
672 and ((opm_rec.attribute19 = p_attribute19 )
673 or (opm_rec.attribute19 is null
674 and (p_attribute19 is null)))
675 and ((opm_rec.attribute20 = p_attribute20 )
676 or (opm_rec.attribute20 is null
677 and (p_attribute20 is null)))
678 and ((opm_rec.pmeth_information_category = p_pmeth_information_category )
679 or (opm_rec.pmeth_information_category is null
680 and (p_pmeth_information_category is null)))
681 and ((opm_rec.pmeth_information1 = p_pmeth_information1 )
682 or (opm_rec.pmeth_information1 is null
683 and (p_pmeth_information1 is null)))
684 and ((opm_rec.pmeth_information2 = p_pmeth_information2 )
685 or (opm_rec.pmeth_information2 is null
686 and (p_pmeth_information2 is null)))
687 and ((opm_rec.pmeth_information3 = p_pmeth_information3 )
688 or (opm_rec.pmeth_information3 is null
689 and (p_pmeth_information3 is null)))
690 and ((opm_rec.pmeth_information4 = p_pmeth_information4 )
691 or (opm_rec.pmeth_information4 is null
692 and (p_pmeth_information4 is null)))
693 and ((opm_rec.pmeth_information5 = p_pmeth_information5 )
694 or (opm_rec.pmeth_information5 is null
695 and (p_pmeth_information5 is null)))
696 and ((opm_rec.pmeth_information6 = p_pmeth_information6 )
697 or (opm_rec.pmeth_information6 is null
698 and (p_pmeth_information6 is null)))
699 and ((opm_rec.pmeth_information7 = p_pmeth_information7 )
700 or (opm_rec.pmeth_information7 is null
701 and (p_pmeth_information7 is null)))
702 and ((opm_rec.pmeth_information8 = p_pmeth_information8 )
703 or (opm_rec.pmeth_information8 is null
704 and (p_pmeth_information8 is null)))
705 and ((opm_rec.pmeth_information9 = p_pmeth_information9 )
706 or (opm_rec.pmeth_information9 is null
707 and (p_pmeth_information9 is null)))
708 and ((opm_rec.pmeth_information10 = p_pmeth_information10)
709 or (opm_rec.pmeth_information10 is null
710 and (p_pmeth_information10 is null)))
711 and ((opm_rec.pmeth_information11 = p_pmeth_information11)
712 or (opm_rec.pmeth_information11 is null
713 and (p_pmeth_information11 is null)))
714 and ((opm_rec.pmeth_information12 = p_pmeth_information12)
715 or (opm_rec.pmeth_information12 is null
716 and (p_pmeth_information12 is null)))
717 and ((opm_rec.pmeth_information13 = p_pmeth_information13)
718 or (opm_rec.pmeth_information13 is null
719 and (p_pmeth_information13 is null)))
720 and ((opm_rec.pmeth_information14 = p_pmeth_information14)
721 or (opm_rec.pmeth_information14 is null
722 and (p_pmeth_information14 is null)))
723 and ((opm_rec.pmeth_information15 = p_pmeth_information15)
724 or (opm_rec.pmeth_information15 is null
725 and (p_pmeth_information15 is null)))
726 and ((opm_rec.pmeth_information16 = p_pmeth_information16)
727 or (opm_rec.pmeth_information16 is null
728 and (p_pmeth_information16 is null)))
729 and ((opm_rec.pmeth_information17 = p_pmeth_information17)
730 or (opm_rec.pmeth_information17 is null
731 and (p_pmeth_information17 is null)))
732 and ((opm_rec.pmeth_information18 = p_pmeth_information18)
733 or (opm_rec.pmeth_information18 is null
734 and (p_pmeth_information18 is null)))
735 and ((opm_rec.pmeth_information19 = p_pmeth_information19)
736 or (opm_rec.pmeth_information19 is null
737 and (p_pmeth_information19 is null)))
738 and ((opm_rec.pmeth_information20 = p_pmeth_information20)
739 or (opm_rec.pmeth_information20 is null
740 and (p_pmeth_information20 is null)))
741 and ((opm_rec.transfer_to_gl_flag = p_transfer_to_gl_flag)
742 or (opm_rec.transfer_to_gl_flag is null
743 and (p_transfer_to_gl_flag is null)))
744 and ((opm_rec.cost_payment = p_cost_payment)
745 or (opm_rec.cost_payment is null
746 and (p_cost_payment is null)))
747 and ((opm_rec.cost_cleared_payment = p_cost_cleared_payment)
748 or (opm_rec.cost_cleared_payment is null
749 and (p_cost_cleared_payment is null)))
750 and ((opm_rec.cost_cleared_void_payment = p_cost_cleared_void_payment)
751 or (opm_rec.cost_cleared_void_payment is null
752 and (p_cost_cleared_void_payment is null)))
753 and ((opm_rec.exclude_manual_payment = p_exclude_manual_payment)
754 or (opm_rec.exclude_manual_payment is null
755 and (p_exclude_manual_payment is null))) )
756 then
757 return; -- Row successfully locked, no clashes
758 end if;
759 --
760 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
761 app_exception.raise_exception;
762 --
763 end lock_row;
764 -----------------------------------------------------------------------------
765 --
766 -- Standard update procedure
767 --
768 procedure update_row(
769 p_row_id varchar2,
770 p_org_payment_method_id number,
771 p_effective_start_date date,
772 p_effective_end_date date,
773 p_business_group_id number,
774 p_external_account_id number,
775 p_currency_code varchar2,
776 p_payment_type_id number,
777 p_defined_balance_id number,
778 p_org_payment_method_name varchar2,
779 p_comment_id number,
780 p_attribute_category varchar2,
781 p_attribute1 varchar2,
782 p_attribute2 varchar2,
783 p_attribute3 varchar2,
784 p_attribute4 varchar2,
785 p_attribute5 varchar2,
786 p_attribute6 varchar2,
787 p_attribute7 varchar2,
788 p_attribute8 varchar2,
789 p_attribute9 varchar2,
790 p_attribute10 varchar2,
791 p_attribute11 varchar2,
792 p_attribute12 varchar2,
793 p_attribute13 varchar2,
794 p_attribute14 varchar2,
795 p_attribute15 varchar2,
796 p_attribute16 varchar2,
797 p_attribute17 varchar2,
798 p_attribute18 varchar2,
799 p_attribute19 varchar2,
800 p_attribute20 varchar2,
801 p_pmeth_information_category varchar2,
802 p_pmeth_information1 varchar2,
803 p_pmeth_information2 varchar2,
804 p_pmeth_information3 varchar2,
805 p_pmeth_information4 varchar2,
806 p_pmeth_information5 varchar2,
807 p_pmeth_information6 varchar2,
808 p_pmeth_information7 varchar2,
809 p_pmeth_information8 varchar2,
810 p_pmeth_information9 varchar2,
811 p_pmeth_information10 varchar2,
812 p_pmeth_information11 varchar2,
813 p_pmeth_information12 varchar2,
814 p_pmeth_information13 varchar2,
815 p_pmeth_information14 varchar2,
816 p_pmeth_information15 varchar2,
817 p_pmeth_information16 varchar2,
818 p_pmeth_information17 varchar2,
819 p_pmeth_information18 varchar2,
820 p_pmeth_information19 varchar2,
821 p_pmeth_information20 varchar2,
822 p_asset_code_combination_id number,
823 p_set_of_books_id number,
824 p_dt_update_mode varchar2,
825 p_base_opm_name varchar2,
826 p_transfer_to_gl_flag varchar2,
827 p_cost_payment varchar2,
828 p_cost_cleared_payment varchar2,
829 p_cost_cleared_void_payment varchar2,
830 p_exclude_manual_payment varchar2,
831 p_gl_set_of_books_id number,
832 p_gl_cash_ac_id number,
833 p_gl_cash_clearing_ac_id number,
834 p_gl_control_ac_id number,
835 p_gl_error_ac_id number,
836 p_default_gl_account varchar2,
837 p_bank_account_id number,
838 p_pay_gl_account_id_out out nocopy number
839 ) is
840 --
841 begin
842
843 -- check whether this should be a DT update or not
844 -- if null then just update the TL table
845 -- acedward 16/05/2000
846
847 if p_dt_update_mode is not null then
848
849 update pay_org_payment_methods_f o
850 set o.org_payment_method_id = P_ORG_PAYMENT_METHOD_ID,
851 o.effective_start_date = P_EFFECTIVE_START_DATE,
852 o.effective_end_date = P_EFFECTIVE_END_DATE,
853 o.business_group_id = P_BUSINESS_GROUP_ID,
854 o.external_account_id = P_EXTERNAL_ACCOUNT_ID,
855 o.currency_code = P_CURRENCY_CODE,
856 o.payment_type_id = P_PAYMENT_TYPE_ID,
857 o.defined_balance_id = P_DEFINED_BALANCE_ID,
858 o.comment_id = P_COMMENT_ID,
859 o.attribute_category = P_ATTRIBUTE_CATEGORY,
860 o.attribute1 = P_ATTRIBUTE1,
861 o.attribute2 = P_ATTRIBUTE2,
862 o.attribute3 = P_ATTRIBUTE3,
863 o.attribute4 = P_ATTRIBUTE4,
864 o.attribute5 = P_ATTRIBUTE5,
865 o.attribute6 = P_ATTRIBUTE6,
866 o.attribute7 = P_ATTRIBUTE7,
867 o.attribute8 = P_ATTRIBUTE8,
868 o.attribute9 = P_ATTRIBUTE9,
869 o.attribute10 = P_ATTRIBUTE10,
870 o.attribute11 = P_ATTRIBUTE11,
871 o.attribute12 = P_ATTRIBUTE12,
872 o.attribute13 = P_ATTRIBUTE13,
873 o.attribute14 = P_ATTRIBUTE14,
874 o.attribute15 = P_ATTRIBUTE15,
875 o.attribute16 = P_ATTRIBUTE16,
876 o.attribute17 = P_ATTRIBUTE17,
877 o.attribute18 = P_ATTRIBUTE18,
878 o.attribute19 = P_ATTRIBUTE19,
879 o.attribute20 = P_ATTRIBUTE20,
880 o.pmeth_information_category = P_PMETH_INFORMATION_CATEGORY,
881 o.pmeth_information1 = P_PMETH_INFORMATION1,
882 o.pmeth_information2 = P_PMETH_INFORMATION2,
883 o.pmeth_information3 = P_PMETH_INFORMATION3,
884 o.pmeth_information4 = P_PMETH_INFORMATION4,
885 o.pmeth_information5 = P_PMETH_INFORMATION5,
886 o.pmeth_information6 = P_PMETH_INFORMATION6,
887 o.pmeth_information7 = P_PMETH_INFORMATION7,
888 o.pmeth_information8 = P_PMETH_INFORMATION8,
889 o.pmeth_information9 = P_PMETH_INFORMATION9,
890 o.pmeth_information10 = P_PMETH_INFORMATION10,
891 o.pmeth_information11 = P_PMETH_INFORMATION11,
892 o.pmeth_information12 = P_PMETH_INFORMATION12,
893 o.pmeth_information13 = P_PMETH_INFORMATION13,
894 o.pmeth_information14 = P_PMETH_INFORMATION14,
895 o.pmeth_information15 = P_PMETH_INFORMATION15,
896 o.pmeth_information16 = P_PMETH_INFORMATION16,
897 o.pmeth_information17 = P_PMETH_INFORMATION17,
898 o.pmeth_information18 = P_PMETH_INFORMATION18,
899 o.pmeth_information19 = P_PMETH_INFORMATION19,
900 o.pmeth_information20 = P_PMETH_INFORMATION20,
901 o.ORG_PAYMENT_METHOD_NAME = p_base_opm_name,
902 o.transfer_to_gl_flag = p_transfer_to_gl_flag ,
903 o.cost_payment = p_cost_payment ,
904 o.cost_cleared_payment = p_cost_cleared_payment ,
905 o.cost_cleared_void_payment = p_cost_cleared_void_payment ,
906 o.exclude_manual_payment = p_exclude_manual_payment
907 where o.rowid = chartorowid(P_ROW_ID);
908 --
909 -- ****************************************************************************************
910 --
911 -- update MLS table (TL)
912 --
913 update PAY_ORG_PAYMENT_METHODS_F_TL set
914 ORG_PAYMENT_METHOD_NAME = P_ORG_PAYMENT_METHOD_NAME,
915 LAST_UPDATE_DATE = sysdate,
916 SOURCE_LANG = userenv('LANG')
917 where ORG_PAYMENT_METHOD_ID = P_ORG_PAYMENT_METHOD_ID
918 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
919 --
920 if (sql%notfound) then -- trap system errors during update
921 hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
922 hr_utility.set_message_token ('PROCEDURE','PAY_ORG_PAYMENT_METHODS_PKG.UPDATE_TL_ROW');
923 end if;
924 --
925 -- ***************************************************************************************
926
927 -- cash management integration: update asset_code_combination_id in
928 -- ap_bank_branches_all for the bank account associated with
929 -- this payment method. only do this if cash management integration
930 -- is active, i.e. both payroll and cash management are installed.
931
932 if pay_ce_support_pkg.pay_and_ce_licensed then
933
934 -- Bug No. 4644827
935 -- for r11.5 the same functionality is done through database trigger. Code
936 -- is for R12
937 if p_external_account_id is not null then
938 pay_maintain_bank_acct.update_payroll_bank_acct(
939 p_bank_account_id => p_bank_account_id,
940 p_external_account_id => p_external_account_id,
941 p_org_payment_method_id => P_ORG_PAYMENT_METHOD_ID);
942 end if;
943 --
944
945 --Bug No. 4644827
946 pay_maintain_bank_acct.update_asset_ccid(
947 p_assest_ccid =>p_asset_code_combination_id,
948 p_set_of_books_id =>p_set_of_books_id,
949 p_external_account_id =>p_external_account_id
950 );
951
952 end if;
953
954 -- Costing of Payment changes
955
956 PAY_PAYMENT_GL_ACCOUNTS_PKG.UPDATE_ROW
957 ( P_EFFECTIVE_START_DATE => p_effective_start_date,
958 P_EFFECTIVE_END_DATE => p_effective_end_date,
959 P_SET_OF_BOOKS_ID => p_gl_set_of_books_id,
960 P_GL_CASH_AC_ID => p_gl_cash_ac_id,
961 P_GL_CASH_CLEARING_AC_ID => p_gl_cash_clearing_ac_id,
962 P_GL_CONTROL_AC_ID => p_gl_control_ac_id,
963 P_GL_ERROR_AC_ID => p_gl_error_ac_id,
964 P_EXTERNAL_ACCOUNT_ID => p_external_account_id,
965 P_ORG_PAYMENT_METHOD_ID => p_org_payment_method_id,
966 P_DT_UPDATE_MODE => p_dt_update_mode,
967 P_DEFAULT_GL_ACCOUNT => p_default_gl_account,
968 P_PAY_GL_ACCOUNT_ID_OUT => p_pay_gl_account_id_out
969 );
970
971 else
972 -- do a non DT update
973 -- update MLS table (TL)
974 --
975 update PAY_ORG_PAYMENT_METHODS_F_TL set
976 ORG_PAYMENT_METHOD_NAME = P_ORG_PAYMENT_METHOD_NAME,
977 LAST_UPDATE_DATE = sysdate,
978 SOURCE_LANG = userenv('LANG')
979 where ORG_PAYMENT_METHOD_ID = P_ORG_PAYMENT_METHOD_ID
980 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
981 --
982 if (sql%notfound) then -- trap system errors during update
983 hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
984 hr_utility.set_message_token ('PROCEDURE','PAY_ORG_PAYMENT_METHODS_PKG.UPDAT
985 E_TL_ROW');
986 end if;
987 --
988
989 end if;
990 --
991
992 --
993 end update_row;
994 --
995 ------------------------------------------------------------------------------------------
996 procedure ADD_LANGUAGE
997 is
998 begin
999 delete from PAY_ORG_PAYMENT_METHODS_F_TL T
1000 where not exists
1001 (select NULL
1002 from PAY_ORG_PAYMENT_METHODS_F B
1003 where B.ORG_PAYMENT_METHOD_ID = T.ORG_PAYMENT_METHOD_ID
1004 );
1005
1006 update PAY_ORG_PAYMENT_METHODS_F_TL T set (
1007 ORG_PAYMENT_METHOD_NAME
1008 ) = (select
1009 B.ORG_PAYMENT_METHOD_NAME
1010 from PAY_ORG_PAYMENT_METHODS_F_TL B
1011 where B.ORG_PAYMENT_METHOD_ID = T.ORG_PAYMENT_METHOD_ID
1012 and B.LANGUAGE = T.SOURCE_LANG)
1013 where (
1014 T.ORG_PAYMENT_METHOD_ID,
1015 T.LANGUAGE
1016 ) in (select
1017 SUBT.ORG_PAYMENT_METHOD_ID,
1018 SUBT.LANGUAGE
1019 from PAY_ORG_PAYMENT_METHODS_F_TL SUBB, PAY_ORG_PAYMENT_METHODS_F_TL SUBT
1020 where SUBB.ORG_PAYMENT_METHOD_ID = SUBT.ORG_PAYMENT_METHOD_ID
1021 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1022 and (SUBB.ORG_PAYMENT_METHOD_NAME <> SUBT.ORG_PAYMENT_METHOD_NAME
1023 ));
1024
1025 insert into PAY_ORG_PAYMENT_METHODS_F_TL (
1026 ORG_PAYMENT_METHOD_ID,
1027 ORG_PAYMENT_METHOD_NAME,
1028 LAST_UPDATE_DATE,
1029 LAST_UPDATED_BY,
1030 LAST_UPDATE_LOGIN,
1031 CREATED_BY,
1032 CREATION_DATE,
1033 LANGUAGE,
1034 SOURCE_LANG
1035 ) select
1036 B.ORG_PAYMENT_METHOD_ID,
1037 B.ORG_PAYMENT_METHOD_NAME,
1038 B.LAST_UPDATE_DATE,
1039 B.LAST_UPDATED_BY,
1040 B.LAST_UPDATE_LOGIN,
1041 B.CREATED_BY,
1042 B.CREATION_DATE,
1043 L.LANGUAGE_CODE,
1044 B.SOURCE_LANG
1045 from PAY_ORG_PAYMENT_METHODS_F_TL B, FND_LANGUAGES L
1046 where L.INSTALLED_FLAG in ('I', 'B')
1047 and B.LANGUAGE = userenv('LANG')
1048 and not exists
1049 (select NULL
1050 from PAY_ORG_PAYMENT_METHODS_F_TL T
1051 where T.ORG_PAYMENT_METHOD_ID = B.ORG_PAYMENT_METHOD_ID
1052 and T.LANGUAGE = L.LANGUAGE_CODE);
1053 end ADD_LANGUAGE;
1054 -------------------------------------------------------------------------
1055 procedure check_end_date(p_end_date varchar2,
1056 p_opm_id number)is
1057 l_dummy varchar2(10);
1058 begin
1059 --
1060 --first check if there are any ppm's based on the opm with an end date
1061 -- greater than the opm's end date.
1062 --
1063 select null
1064 into l_dummy
1065 from pay_personal_payment_methods_f
1066 where org_payment_method_id = p_opm_id
1067 and effective_end_date > fnd_date.canonical_to_date(p_end_date);
1068 if (SQL%FOUND) then
1069 hr_utility.set_message(801, 'HR_6235_PAYM_EXISTING_PPMS');
1070 hr_utility.raise_error;
1071 end if;
1072 -- now check if there are any opmu's using this opm with an end date
1073 --greater than the new end_date
1074 select null
1075 into l_dummy
1076 from pay_org_pay_method_usages_f
1077 where org_payment_method_id = p_opm_id
1078 and effective_end_date > fnd_date.canonical_to_date(p_end_date);
1079 if (SQL%FOUND) then
1080 hr_utility.set_message(801, 'HR_6236_PAYM_USED_AS_DEFAULT');
1081 hr_utility.raise_error;
1082 end if;
1083 exception
1084 when no_data_found then
1085 null;
1086 when others then
1087 null;
1088 end check_end_date;
1089
1090 function chk_dflt_prpy_ppm(opm_id varchar2,
1091 val_start_date varchar2) return boolean is
1092 begin
1093 return(hr_payments.check_default(opm_id,val_start_date)
1094 AND hr_payments.check_prepay(to_number(opm_id),val_start_date)
1095 AND hr_payments.check_ppm(opm_id,val_start_date));
1096 end chk_dflt_prpy_ppm;
1097 -----------------------------------------------------------------------------
1098 procedure unique_chk(O_ORG_PAYMENT_METHOD_NAME in VARCHAR2, O_EFFECTIVE_START_DATE in date,
1099 O_EFFECTIVE_END_DATE in date)
1100 is
1101 result varchar2(255);
1102 Begin
1103 SELECT count(*) INTO result
1104 FROM pay_org_payment_methods_f
1105 WHERE ORG_PAYMENT_METHOD_NAME = O_ORG_PAYMENT_METHOD_NAME
1106 and EFFECTIVE_START_DATE = O_EFFECTIVE_START_DATE
1107 and EFFECTIVE_END_DATE = O_EFFECTIVE_END_DATE
1108 and O_ORG_PAYMENT_METHOD_NAME is not NULL
1109 and O_EFFECTIVE_START_DATE is not NULL
1110 and O_EFFECTIVE_END_DATE is not NULL
1111 and BUSINESS_GROUP_ID is NULL;
1112 --
1113 IF (result>1) THEN
1114 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1115 hr_utility.set_message_token('PROCEDURE','PAY_ORG_PAYMENT_METHODS_PKG.UNIQUE_CHK');
1116 hr_utility.set_message_token('STEP','1');
1117 hr_utility.raise_error;
1118 END IF;
1119 EXCEPTION
1120 when NO_DATA_FOUND then
1121 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1122 hr_utility.set_message_token('PROCEDURE','PAY_ORG_PAYMENT_METHODS_PKG.UNIQUE_CHK');
1123 hr_utility.set_message_token('STEP','1');
1124 hr_utility.raise_error;
1125 end unique_chk;
1126 --------------------------------------------------------------------------------
1127 function payee_type(p_payee_type varchar2,
1128 p_payee_id number,
1129 p_effective_date date) return varchar2 is
1130 --
1131 CURSOR c_person IS
1132 SELECT per.full_name
1133 FROM per_all_people_f per
1134 WHERE per.person_id = p_payee_id
1135 AND p_effective_date BETWEEN per.effective_start_date
1136 AND per.effective_end_date;
1137 CURSOR c_org IS
1138 SELECT org.name
1139 FROM hr_all_organization_units org
1140 WHERE org.organization_id = p_payee_id;
1141 --
1142 l_payee_name per_people_f.full_name%TYPE;
1143 begin
1144 IF p_payee_type = 'P' THEN
1145 OPEN c_person;
1146 FETCH c_person INTO l_payee_name;
1147 CLOSE c_person;
1148 ELSIF p_payee_type = 'O' THEN
1149 OPEN c_org;
1150 FETCH c_org INTO l_payee_name;
1151 CLOSE c_org;
1152 ELSE
1153 l_payee_name := NULL;
1154 END IF;
1155 RETURN l_payee_name;
1156 exception
1157 WHEN no_data_found THEN
1158 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1159 hr_utility.set_message_token('PROCEDURE','PAY_ORG_PAYMENT_METHODS_PKG.PAYEE_TYPE');
1160 hr_utility.set_message_token('STEP','2');
1161 hr_utility.raise_error;
1162 end payee_type;
1163 --------------------------------------------------------------------------------
1164 procedure TRANSLATE_ROW (
1165 X_O_ORG_PAYMENT_METHOD_NAME in varchar2,
1166 X_O_EFFECTIVE_START_DATE in date,
1167 X_O_EFFECTIVE_END_DATE in date,
1168 X_ORG_PAYMENT_METHOD_NAME in varchar2,
1169 X_OWNER in varchar2 ) is
1170 begin
1171 -- unique_chk(X_O_ORG_PAYMENT_METHOD_NAME,X_O_EFFECTIVE_START_DATE,X_O_EFFECTIVE_END_DATE);
1172 --
1173 UPDATE pay_org_payment_methods_f_tl
1174 SET ORG_PAYMENT_METHOD_NAME = nvl(X_ORG_PAYMENT_METHOD_NAME,ORG_PAYMENT_METHOD_NAME),
1175 last_update_date = SYSDATE,
1176 last_updated_by = decode(x_owner,'SEED',1,0),
1177 last_update_login = 0,
1178 source_lang = userenv('LANG')
1179 WHERE userenv('LANG') IN (language,source_lang)
1180 AND ORG_PAYMENT_METHOD_ID in
1181 (select ORG_PAYMENT_METHOD_ID
1182 from pay_org_payment_methods_f
1183 WHERE ORG_PAYMENT_METHOD_NAME = X_O_ORG_PAYMENT_METHOD_NAME
1184 and EFFECTIVE_START_DATE = X_O_EFFECTIVE_START_DATE
1185 and EFFECTIVE_END_DATE = X_O_EFFECTIVE_END_DATE
1186 and X_O_ORG_PAYMENT_METHOD_NAME is not NULL
1187 and X_O_EFFECTIVE_START_DATE is not NULL
1188 and X_O_EFFECTIVE_END_DATE is not NULL
1189 and BUSINESS_GROUP_ID is NULL);
1190 --
1191 if (sql%notfound) then -- trap system errors during update
1192 -- hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
1193 -- hr_utility.set_message_token ('PROCEDURE','PAY_ORG_PAYMENT_METHODS_PKG.TRANSLATE_ROW');
1194 -- hr_utility.set_message_token('STEP','1');
1195 -- hr_utility.raise_error;
1196 null;
1197 end if;
1198 end TRANSLATE_ROW;
1199 --------------------------------------------------------------------------------
1200 procedure lock_aba_row(
1201 p_external_account_id in number,
1202 p_set_of_books_id in number,
1203 p_asset_code_combination_id in number ) is
1204 --
1205 begin
1206 --
1207 if p_external_account_id is not null then
1208
1209 --Bug No. 4644827
1210 pay_maintain_bank_acct.lock_row(
1211 p_external_account_id => p_external_account_id
1212 );
1213
1214 end if;
1215 --
1216 end lock_aba_row;
1217 --------------------------------------------------------------------------------
1218 END pay_org_payment_methods_pkg;