[Home] [Help]
PACKAGE BODY: APPS.PAY_PAYMENT_GL_ACCOUNTS_PKG
Source
1 package body PAY_PAYMENT_GL_ACCOUNTS_PKG as
2 /* $Header: pypga01t.pkb 120.0 2005/09/29 10:51 tvankayl noship $ */
3
4 procedure INSERT_ROW (
5 P_PAY_GL_ACCOUNT_ID OUT NOCOPY NUMBER,
6 P_EFFECTIVE_START_DATE in DATE,
7 P_EFFECTIVE_END_DATE in DATE,
8 P_SET_OF_BOOKS_ID in NUMBER,
9 P_GL_CASH_AC_ID in NUMBER,
10 P_GL_CASH_CLEARING_AC_ID in NUMBER,
11 P_GL_CONTROL_AC_ID in NUMBER,
12 P_GL_ERROR_AC_ID in NUMBER,
13 P_EXTERNAL_ACCOUNT_ID in NUMBER,
14 P_ORG_PAYMENT_METHOD_ID in NUMBER,
15 P_DEFAULT_GL_ACCOUNT in VARCHAR2
16 ) is
17 --
18 cursor C is select ROWID from PAY_PAYMENT_GL_ACCOUNTS_F
19 where PAY_GL_ACCOUNT_ID = P_PAY_GL_ACCOUNT_ID
20 ;
21 --
22 l_def_gl_acct_id pay_payment_gl_accounts_f.pay_gl_account_id%type;
23 l_external_account_id pay_org_payment_methods_f.external_account_id%type;
24 l_org_payment_method_id pay_org_payment_methods_f.org_payment_method_id%type;
25 l_effective_start_date date;
26 l_effective_end_date date;
27 l_ovn number;
28 l_esd_out date;
29 l_eed_out date;
30 l_proc varchar2(100) := 'PAY_PAYMENT_GL_ACCOUNTS_PKG.INSERT_ROW';
31 --
32 begin
33 --
34
35 hr_utility.set_location('Entering: '|| l_proc, 10);
36 hr_utility.set_location('Org Payment Method Id: ' || p_org_payment_method_id, 20);
37
38 hr_api.mandatory_arg_error
39 (p_api_name => l_proc
40 ,p_argument => 'ORG_PAYMENT_METHOD_ID'
41 ,p_argument_value => p_org_payment_method_id
42 );
43
44 if p_external_account_id is null and p_default_gl_account = 'Y' then
45
46 fnd_message.set_name('PAY', 'PAY_33459_INV_DEF_BANK_COMB');
47 fnd_message.raise_error;
48
49 end if;
50
51 hr_utility.set_location(l_proc, 30);
52
53 l_external_account_id := p_external_account_id;
54 l_org_payment_method_id := p_org_payment_method_id;
55 l_effective_start_date := p_effective_start_date;
56 l_effective_end_date := p_effective_end_date;
57
58 if p_default_gl_account = 'Y' then
59
60 -- Check if default GL accounts for this bank already exist.
61
62 -- If default flag is 'Y' then external_account_id must be passed.
63
64 hr_utility.set_location(l_proc, 40);
65
66 l_def_gl_acct_id := default_gl_accounts
67 ( p_external_account_id => p_external_account_id );
68
69 if l_def_gl_acct_id is not null then
70
71 -- Default GL Accounts for the bank exist, validate the data passed in
72 -- and update the default GL Accounts.
73
74 hr_utility.set_location(l_proc, 50);
75
76 pay_pga_bus.chk_set_of_books_id
77 (p_set_of_books_id => p_set_of_books_id
78 );
79 --
80 pay_pga_bus.chk_gl_account_id
81 (p_gl_account_id => p_gl_cash_ac_id
82 );
83 --
84 pay_pga_bus.chk_gl_account_id
85 (p_gl_account_id => p_gl_cash_clearing_ac_id
86 );
87 --
88 pay_pga_bus.chk_gl_account_id
89 (p_gl_account_id => p_gl_control_ac_id
90 );
91 --
92 pay_pga_bus.chk_gl_account_id
93 (p_gl_account_id => p_gl_error_ac_id
94 );
95 --
96
97 update pay_payment_gl_accounts_f
98 set
99 set_of_books_id = p_set_of_books_id,
100 gl_cash_ac_id = p_gl_cash_ac_id,
101 gl_cash_clearing_ac_id = p_gl_cash_clearing_ac_id,
102 gl_control_ac_id = p_gl_control_ac_id,
103 gl_error_ac_id = p_gl_error_ac_id
104 where
105 pay_gl_account_id = l_def_gl_acct_id and
106 external_account_id = p_external_account_id ;
107
108 p_pay_gl_account_id := l_def_gl_acct_id;
109
110 return;
111
112 else
113
114 -- Default GL Accounts for the bank do not exist
115 -- Create the Default GL Accounts Now.
116
117 hr_utility.set_location(l_proc, 60);
118
119 l_org_payment_method_id := null;
120 l_effective_start_date := hr_general.start_of_time;
121 l_effective_end_date := hr_general.end_of_time;
122
123 end if;
124
125 else
126
127 -- Default flag is 'N'.
128 -- GL Accounts for the Org Payment method has to be created.
129
130 hr_utility.set_location(l_proc, 70);
131
132 l_external_account_id := null;
133
134 end if;
135
136 hr_utility.set_location(l_proc, 80);
137
138 pay_pga_ins.ins
139 (p_effective_date => trunc(l_effective_start_date)
140 ,p_set_of_books_id => p_set_of_books_id
141 ,p_gl_cash_ac_id => p_gl_cash_ac_id
142 ,p_gl_cash_clearing_ac_id => p_gl_cash_clearing_ac_id
143 ,p_gl_control_ac_id => p_gl_control_ac_id
144 ,p_gl_error_ac_id => p_gl_error_ac_id
145 ,p_external_account_id => l_external_account_id
146 ,p_org_payment_method_id => l_org_payment_method_id
147 ,p_pay_gl_account_id => p_pay_gl_account_id
148 ,p_object_version_number => l_ovn
149 ,p_effective_start_date => l_esd_out
150 ,p_effective_end_date => l_eed_out
151 );
152
153 hr_utility.set_location(l_proc, 90);
154
155 if l_esd_out <> l_effective_start_date or
156 l_eed_out <> l_effective_end_date then
157
158 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
159 hr_utility.set_message_token('PROCEDURE','PAY_PAYMENT_GL_ACCOUNTS_PKG.INSERT_ROW');
160 hr_utility.set_message_token('STEP','10');
161 hr_utility.raise_error;
162
163 end if;
164
165 hr_utility.set_location('Leaving: '|| l_proc, 100);
166 --
167 end INSERT_ROW;
168
169 procedure UPDATE_ROW (
170 P_EFFECTIVE_START_DATE in DATE,
171 P_EFFECTIVE_END_DATE in DATE,
172 P_SET_OF_BOOKS_ID in NUMBER,
173 P_GL_CASH_AC_ID in NUMBER,
174 P_GL_CASH_CLEARING_AC_ID in NUMBER,
175 P_GL_CONTROL_AC_ID in NUMBER,
176 P_GL_ERROR_AC_ID in NUMBER,
177 P_EXTERNAL_ACCOUNT_ID in NUMBER,
178 P_ORG_PAYMENT_METHOD_ID in NUMBER,
179 P_DT_UPDATE_MODE IN VARCHAR2,
180 P_DEFAULT_GL_ACCOUNT in VARCHAR2,
181 P_PAY_GL_ACCOUNT_ID_OUT out nocopy number
182 ) is
183 --
184 l_ovn number;
185 l_ovn_seq number;
186 l_esd_out date;
187 l_eed_out date;
188 l_opm_gl_acct_id pay_payment_gl_accounts_f.pay_gl_account_id%type;
189 l_def_gl_acct_id pay_payment_gl_accounts_f.pay_gl_account_id%type;
190 l_pay_gl_account_id pay_payment_gl_accounts_f.pay_gl_account_id%type;
191 l_def_gl_set_of_books_id pay_payment_gl_accounts_f.set_of_books_id%type;
192 l_def_gl_cash_ac_id pay_payment_gl_accounts_f.gl_cash_ac_id%type;
193 l_def_gl_cash_clearing_ac_id pay_payment_gl_accounts_f.gl_cash_clearing_ac_id%type;
194 l_def_gl_control_ac_id pay_payment_gl_accounts_f.gl_control_ac_id%type;
195 l_def_gl_error_ac_id pay_payment_gl_accounts_f.gl_error_ac_id%type;
196 l_seq_id pay_payment_gl_accounts_f.pay_gl_account_id%type;
197 l_proc varchar2(100) := 'PAY_PAYMENT_GL_ACCOUNTS_PKG.UPDATE_ROW';
198 --
199 cursor csr_ovn (p_gl_acct_id number, p_effective_date date) is
200 select object_version_number
201 from pay_payment_gl_accounts_f
202 where pay_gl_account_id = p_gl_acct_id
203 and p_effective_date between
204 effective_start_date and effective_end_date;
205 --
206 cursor csr_def_gl_accounts (p_ext_gl_id number, p_effective_date date) is
207 select gl_cash_ac_id, gl_cash_clearing_ac_id, gl_control_ac_id, gl_error_ac_id, set_of_books_id
208 from pay_payment_gl_accounts_f
209 where external_account_id = p_ext_gl_id
210 and p_effective_date between effective_start_date and effective_end_date;
211 --
212 cursor csr_next_seq is
213 select pay_payment_gl_accounts_s.nextval
214 from dual;
215 --
216 cursor csr_opm_rows (p_org_payment_method_id number) is
217 select effective_start_date, effective_end_date, external_account_id
218 from pay_org_payment_methods_f
219 where org_payment_method_id = p_org_payment_method_id;
220 --
221 begin
222 --
223
224 hr_utility.set_location('Entering: '|| l_proc, 10);
225 hr_utility.set_location('Org Payment Method Id: '|| p_org_payment_method_id, 20);
226
227 hr_api.mandatory_arg_error
228 (p_api_name => l_proc
229 ,p_argument => 'ORG_PAYMENT_METHOD_ID'
230 ,p_argument_value => p_org_payment_method_id
231 );
232
233 if p_external_account_id is null and p_default_gl_account = 'Y' then
234
235 fnd_message.set_name('PAY', 'PAY_33459_INV_DEF_BANK_COMB');
236 fnd_message.raise_error;
237
238 end if;
239
240 hr_utility.set_location(l_proc, 30);
241
242 l_opm_gl_acct_id := opm_gl_accounts
243 ( p_org_payment_method_id => p_org_payment_method_id );
244
245 l_def_gl_acct_id := default_gl_accounts
246 ( p_external_account_id => p_external_account_id );
247
248 hr_utility.set_location(l_proc || ' OPM GL ACCOUNT ID: ' || l_opm_gl_acct_id, 40);
249 hr_utility.set_location(l_proc || ' DEFAULT GL ACCOUNT ID: ' || l_def_gl_acct_id, 50);
250
251 if p_default_gl_account = 'Y' then
252
253 -- When Default flag is 'Y', GL accounts for the OPM should not exist.
254
255 hr_utility.set_location(l_proc, 60);
256
257 if l_opm_gl_acct_id is not null then
258
259 fnd_message.set_name('PAY', 'PAY_33460_OPM_GL_ACT_EXISTS');
260 fnd_message.raise_error;
261
262 end if;
263
264 hr_utility.set_location(l_proc, 70);
265
266 if l_def_gl_acct_id is not null then
267
268 -- When the default GL Accounts for the bank already exists
269 -- we need to only validate the data and update the record.
270
271 hr_utility.set_location(l_proc, 80);
272
273 pay_pga_bus.chk_set_of_books_id
274 (p_set_of_books_id => p_set_of_books_id
275 );
276 --
277 pay_pga_bus.chk_gl_account_id
278 (p_gl_account_id => p_gl_cash_ac_id
279 );
280 --
281 pay_pga_bus.chk_gl_account_id
282 (p_gl_account_id => p_gl_cash_clearing_ac_id
283 );
284 --
285 pay_pga_bus.chk_gl_account_id
286 (p_gl_account_id => p_gl_control_ac_id
287 );
288 --
289 pay_pga_bus.chk_gl_account_id
290 (p_gl_account_id => p_gl_error_ac_id
291 );
292 --
293
294 update pay_payment_gl_accounts_f
295 set
296 set_of_books_id = p_set_of_books_id,
297 gl_cash_ac_id = p_gl_cash_ac_id,
298 gl_cash_clearing_ac_id = p_gl_cash_clearing_ac_id,
299 gl_control_ac_id = p_gl_control_ac_id,
300 gl_error_ac_id = p_gl_error_ac_id
301 where
302 pay_gl_account_id = l_def_gl_acct_id and
303 external_account_id = p_external_account_id ;
304
305 --
306 p_pay_gl_account_id_out := l_def_gl_acct_id;
307
308 else
309
310 -- Default GL Accounts for the bank do not exist
311 -- Create the Default GL Accounts Now.
312
313 hr_utility.set_location(l_proc, 90);
314
315 pay_pga_ins.ins
316 (p_effective_date => trunc(hr_general.start_of_time)
317 ,p_set_of_books_id => p_set_of_books_id
318 ,p_gl_cash_ac_id => p_gl_cash_ac_id
319 ,p_gl_cash_clearing_ac_id => p_gl_cash_clearing_ac_id
320 ,p_gl_control_ac_id => p_gl_control_ac_id
321 ,p_gl_error_ac_id => p_gl_error_ac_id
322 ,p_external_account_id => p_external_account_id
323 ,p_org_payment_method_id => null
324 ,p_pay_gl_account_id => l_pay_gl_account_id
325 ,p_object_version_number => l_ovn
326 ,p_effective_start_date => l_esd_out
327 ,p_effective_end_date => l_eed_out
328 );
329
330 p_pay_gl_account_id_out := l_pay_gl_account_id;
331
332 hr_utility.set_location(l_proc, 100);
333
334 if l_esd_out <> hr_general.start_of_time and
335 l_eed_out <> hr_general.end_of_time then
336
337 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
338 hr_utility.set_message_token('PROCEDURE','PAY_PAYMENT_GL_ACCOUNTS_PKG.UPDATE_ROW');
339 hr_utility.set_message_token('STEP','10');
340 hr_utility.raise_error;
341
342 end if;
343
344 end if;
345
346 else
347
348 -- Default flag is 'N'. Check if the GL accounts for the OPM exist.
349
350 hr_utility.set_location(l_proc, 110);
351
352 if l_opm_gl_acct_id is not null then
353
354 -- GL Accounts for the OPM exist. Update the record on the effective date.
355
356 hr_utility.set_location(l_proc, 120);
357
358 open csr_ovn (p_gl_acct_id => l_opm_gl_acct_id,
359 p_effective_date => p_effective_start_date);
360 fetch csr_ovn into l_ovn;
361 close csr_ovn;
362
363 pay_pga_upd.upd
364 (p_effective_date => trunc(p_effective_start_date)
365 ,p_datetrack_mode => p_dt_update_mode
366 ,p_pay_gl_account_id => l_opm_gl_acct_id
367 ,p_object_version_number => l_ovn
368 ,p_set_of_books_id => p_set_of_books_id
372 ,p_gl_error_ac_id => p_gl_error_ac_id
369 ,p_gl_cash_ac_id => p_gl_cash_ac_id
370 ,p_gl_cash_clearing_ac_id => p_gl_cash_clearing_ac_id
371 ,p_gl_control_ac_id => p_gl_control_ac_id
373 ,p_effective_start_date => l_esd_out
374 ,p_effective_end_date => l_eed_out
375 );
376
377 p_pay_gl_account_id_out := l_opm_gl_acct_id;
378
379 hr_utility.set_location(l_proc, 130);
380
381 if l_esd_out <> p_effective_start_date or
382 l_eed_out <> p_effective_end_date then
383
384 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
385 hr_utility.set_message_token('PROCEDURE','PAY_PAYMENT_GL_ACCOUNTS_PKG.UPDATE_ROW');
386 hr_utility.set_message_token('STEP','20');
387 hr_utility.raise_error;
388
389 end if;
390
391 else
392
393 -- Default flag is 'N' but the GL Accounts for the bank details do not exist
394 -- The only when this can happen is the user has unchecked the default flag
395 -- to have GL accounts specifically for this OPM instead of sharing the
396 -- GL accounts of its bank.
397
398 open csr_next_seq;
399 fetch csr_next_seq into l_seq_id;
400 close csr_next_seq;
401
402 l_ovn_seq := 1;
403
404 hr_utility.set_location(l_proc, 140);
405
406 for l_rec in csr_opm_rows (p_org_payment_method_id => p_org_payment_method_id ) loop
407
408 hr_utility.trace('Inserting Row for OPM: ' || p_org_payment_method_id || ' ' || l_rec.effective_start_date || ' ' || l_rec.effective_end_date );
409
410 l_def_gl_cash_ac_id := null;
411 l_def_gl_cash_clearing_ac_id := null;
412 l_def_gl_control_ac_id := null;
413 l_def_gl_error_ac_id := null;
414 l_def_gl_set_of_books_id := null;
415
416 if l_rec.external_account_id is not null then
417
418 -- The OPM shared the GL Accounts of its bank earlier, but now it is being
419 -- updated to have specific GL Accounts for itself
420 -- For the period with effective_date, the GL accounts used by the
421 -- OPM will be the values passed to the UPDATE_ROW procedure
422 -- For other periods, the GL Accounts of the bank the OPM used in that period
423 -- will be populated.
424
425 hr_utility.set_location(l_proc, 141);
426
427 open csr_def_gl_accounts(p_ext_gl_id => l_rec.external_account_id,
428 p_effective_date => l_rec.effective_start_date);
429 fetch csr_def_gl_accounts into l_def_gl_cash_ac_id, l_def_gl_cash_clearing_ac_id,
430 l_def_gl_control_ac_id, l_def_gl_error_ac_id, l_def_gl_set_of_books_id;
431 close csr_def_gl_accounts;
432
433 end if;
434
435 hr_utility.set_location(l_proc, 142);
436
437 insert into PAY_PAYMENT_GL_ACCOUNTS_F
438 (
439 PAY_GL_ACCOUNT_ID,
440 EFFECTIVE_START_DATE,
441 EFFECTIVE_END_DATE,
442 SET_OF_BOOKS_ID,
443 GL_CASH_AC_ID,
444 GL_CASH_CLEARING_AC_ID,
445 GL_CONTROL_AC_ID,
446 GL_ERROR_AC_ID,
447 EXTERNAL_ACCOUNT_ID,
448 ORG_PAYMENT_METHOD_ID,
449 OBJECT_VERSION_NUMBER
450 )
451 values
452 (
453 l_seq_id,
454 trunc(l_rec.effective_start_date),
455 trunc(l_rec.effective_end_date),
456 l_def_gl_set_of_books_id,
457 l_def_gl_cash_ac_id,
458 l_def_gl_cash_clearing_ac_id,
459 l_def_gl_control_ac_id,
460 l_def_gl_error_ac_id,
461 null,
462 p_org_payment_method_id,
463 l_ovn_seq
464 );
465
466 l_ovn_seq := l_ovn_seq + 1;
467
468 p_pay_gl_account_id_out := l_seq_id;
469
470 hr_utility.set_location(l_proc, 143);
471
472 end loop;
473
474 hr_utility.set_location(l_proc, 150);
475
476 open csr_ovn (p_gl_acct_id => l_seq_id,
477 p_effective_date => p_effective_start_date);
478 fetch csr_ovn into l_ovn;
479 close csr_ovn;
480
481 pay_pga_upd.upd
482 (p_effective_date => trunc(p_effective_start_date)
483 ,p_datetrack_mode => 'CORRECTION'
484 ,p_pay_gl_account_id => l_seq_id
485 ,p_object_version_number => l_ovn
486 ,p_set_of_books_id => p_set_of_books_id
487 ,p_gl_cash_ac_id => p_gl_cash_ac_id
488 ,p_gl_cash_clearing_ac_id => p_gl_cash_clearing_ac_id
489 ,p_gl_control_ac_id => p_gl_control_ac_id
490 ,p_gl_error_ac_id => p_gl_error_ac_id
491 ,p_effective_start_date => l_esd_out
492 ,p_effective_end_date => l_eed_out
493 );
494
495 hr_utility.set_location(l_proc, 160);
496
497 if l_esd_out <> p_effective_start_date or
498 l_eed_out <> p_effective_end_date then
499
500 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
501 hr_utility.set_message_token('PROCEDURE','PAY_PAYMENT_GL_ACCOUNTS_PKG.UPDATE_ROW');
502 hr_utility.set_message_token('STEP','30');
503 hr_utility.raise_error;
504
505 end if;
506
507 end if;
508
509 end if;
510
514
511 hr_utility.set_location('Leaving: '|| l_proc, 170);
512 --
513 end UPDATE_ROW;
515 procedure DELETE_ROW (
516 p_org_payment_method_id in NUMBER
517 ,p_effective_date in DATE
518 ,p_datetrack_mode in VARCHAR2
519 ,p_org_eff_start_date in DATE
520 ,p_org_eff_end_date in DATE )
521 is
522 --
523 cursor csr_opm_gl_rows is
524 select pay_gl_account_id, object_version_number
525 from pay_payment_gl_accounts_f
526 where org_payment_method_id = p_org_payment_method_id
527 and p_effective_date between effective_start_date and effective_end_date ;
528 --
529 l_pay_gl_account_id pay_payment_gl_accounts_f.pay_gl_account_id%type;
530 l_ovn pay_payment_gl_accounts_f.object_version_number%type;
531 l_esd_out date;
532 l_eed_out date;
533 l_proc varchar2(100) := 'PAY_PAYMENT_GL_ACCOUNTS_PKG.DELETE_ROW';
534 --
535 begin
536 --
537 hr_utility.set_location('Entering: '|| l_proc, 10);
538 hr_utility.set_location('Org Payment Method Id: '|| p_org_payment_method_id, 20);
539
540 open csr_opm_gl_rows;
541 fetch csr_opm_gl_rows into l_pay_gl_account_id, l_ovn;
542
543 if csr_opm_gl_rows%found then
544
545 hr_utility.set_location(l_proc, 30);
546
547 pay_pga_del.del
548 (p_effective_date => trunc(p_effective_date)
549 ,p_datetrack_mode => p_datetrack_mode
550 ,p_pay_gl_account_id => l_pay_gl_account_id
551 ,p_object_version_number => l_ovn
552 ,p_effective_start_date => l_esd_out
553 ,p_effective_end_date => l_eed_out
554 );
555
556 hr_utility.set_location(l_proc, 40);
557
558 end if;
559
560 close csr_opm_gl_rows;
561
562 hr_utility.set_location('Leaving: '|| l_proc, 50);
563 --
564 end DELETE_ROW;
565
566 function DEFAULT_GL_ACCOUNTS (
567 P_EXTERNAL_ACCOUNT_ID in NUMBER)
568 RETURN NUMBER is
569 --
570 cursor csr_default_gl_accounts is
571 select pga.pay_gl_account_id
572 from pay_payment_gl_accounts_f pga
573 where pga.external_account_id = p_external_account_id;
574 --
575 l_def_gl_acct_id pay_payment_gl_accounts_f.pay_gl_account_id%type;
576 l_proc varchar2(100) := 'PAY_PAYMENT_GL_ACCOUNTS_PKG.DEFAULT_GL_ACCOUNTS';
577 --
578 BEGIN
579 --
580 hr_utility.set_location('Entering: '|| l_proc, 10);
581 hr_utility.set_location('External Account Id: '|| l_proc, 20);
582
583 if p_external_account_id is not null then
584
585 hr_utility.set_location(l_proc, 30);
586 open csr_default_gl_accounts;
587 fetch csr_default_gl_accounts into l_def_gl_acct_id;
588
589 if csr_default_gl_accounts%ROWCOUNT > 1 then
590
591 close csr_default_gl_accounts;
592
593 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
594 hr_utility.set_message_token('PROCEDURE','PAY_PAYMENT_GL_ACCOUNTS_PKG.DEFAULT_GL_ACCOUNTS');
595 hr_utility.set_message_token('STEP','10');
596 hr_utility.raise_error;
597
598 end if;
599
600 close csr_default_gl_accounts;
601
602 else
603 hr_utility.set_location(l_proc, 40);
604 l_def_gl_acct_id := null;
605 end if;
606
607 hr_utility.set_location('Leaving: '|| l_proc, 50);
608
609 return l_def_gl_acct_id;
610 --
611 END DEFAULT_GL_ACCOUNTS;
612
613 function OPM_GL_ACCOUNTS (
614 P_ORG_PAYMENT_METHOD_ID in NUMBER)
615 RETURN NUMBER is
616 --
617 cursor csr_opm_gl_accounts is
618 select distinct pga.pay_gl_account_id
619 from pay_payment_gl_accounts_f pga
620 where pga.org_payment_method_id = p_org_payment_method_id;
621 --
622 l_opm_gl_acct_id pay_payment_gl_accounts_f.pay_gl_account_id%type;
623 l_proc varchar2(100) := 'PAY_PAYMENT_GL_ACCOUNTS_PKG.OPM_GL_ACCOUNTS';
624 --
625 BEGIN
626 --
627 hr_utility.set_location('Entering: '|| l_proc, 10);
628 hr_utility.set_location('Org Payment Method Id: '|| p_org_payment_method_id, 20);
629
630 if p_org_payment_method_id is not null then
631
632 hr_utility.set_location(l_proc, 30);
633 open csr_opm_gl_accounts;
634 fetch csr_opm_gl_accounts into l_opm_gl_acct_id;
635
636 if csr_opm_gl_accounts%ROWCOUNT > 1 then
637
638 close csr_opm_gl_accounts;
639
640 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
641 hr_utility.set_message_token('PROCEDURE','PAY_PAYMENT_GL_ACCOUNTS_PKG.OPM_GL_ACCOUNTS');
642 hr_utility.set_message_token('STEP','10');
643 hr_utility.raise_error;
644
645 end if;
646
647 close csr_opm_gl_accounts;
648
649 else
650 hr_utility.set_location(l_proc, 40);
651 l_opm_gl_acct_id := null;
652 end if;
653
654 hr_utility.set_location('Leaving: '|| l_proc, 50);
655 return l_opm_gl_acct_id;
656 --
657 END OPM_GL_ACCOUNTS;
658
659 procedure GET_GL_ACCOUNTS
660 ( p_pay_gl_account_id in number,
661 p_effective_date in date,
662 p_set_of_books_id out nocopy number,
663 p_set_of_books_name out nocopy varchar2,
664 p_gl_account_flex_num out nocopy number,
665 p_gl_cash_ac_id out nocopy number,
666 p_gl_cash_clearing_ac_id out nocopy number,
667 p_gl_control_ac_id out nocopy number,
668 p_gl_error_ac_id out nocopy number
669 ) is
670 --
671 cursor csr_gl_accounts is
672 select gl_cash_ac_id, gl_cash_clearing_ac_id, gl_control_ac_id, gl_error_ac_id, set_of_books_id
673 from pay_payment_gl_accounts_f
674 where pay_gl_account_id = p_pay_gl_account_id
678 select name, chart_of_accounts_id
675 and p_effective_date between effective_start_date and effective_end_date;
676 --
677 cursor csr_set_of_books_name is
679 from gl_sets_of_books
680 where set_of_books_id = p_set_of_books_id;
681 --
682 l_proc varchar2(100) := 'PAY_PAYMENT_GL_ACCOUNTS_PKG.GET_GL_ACCOUNTS';
683 --
684 BEGIN
685 --
686 hr_utility.set_location('Entering: '|| l_proc, 10);
687
691
688 if p_pay_gl_account_id is not null then
689
690 hr_utility.set_location(l_proc, 20);
692 open csr_gl_accounts;
693 fetch csr_gl_accounts into p_gl_cash_ac_id, p_gl_cash_clearing_ac_id, p_gl_control_ac_id,
694 p_gl_error_ac_id , p_set_of_books_id ;
695 close csr_gl_accounts;
696
697 if p_set_of_books_id is not null then
698
699 hr_utility.set_location(l_proc, 30);
700
701 open csr_set_of_books_name;
702 fetch csr_set_of_books_name into p_set_of_books_name, p_gl_account_flex_num ;
703 close csr_set_of_books_name;
704
705 end if;
706
707 hr_utility.set_location(l_proc, 40);
708
709 end if;
710 hr_utility.set_location('Leaving: '|| l_proc, 50);
711 --
712 END GET_GL_ACCOUNTS;
713
714
715 end PAY_PAYMENT_GL_ACCOUNTS_PKG;
716