1 PACKAGE BODY PAY_MAINTAIN_BANK_ACCT as
2 /* $Header: pymntbnk.pkb 120.6.12020000.2 2012/09/20 09:25:58 pparate ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' UPDATE_PAYROLL_BANK_ACCT.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |------------------------< UPDATE_PAYROLL_BANK_ACCT >----------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 -- This procedure stamps the payroll_bank_account_id of ce_bank_uses_all in
13 -- Cash Management with the user specified payroll account id (p_external_account_id).
14 -- This stamping happens only if the cash management account is not already
15 -- stamped with other payroll account.
16 -- Below are the scenarios taken care in this procedure
17
18 -- 1) Scenario 1: If the Cash management A/C i.e,. p_bank_account_id is not
19 -- stamped then it stamps with the payroll account id
20 --
21 -- 2) Scenario 2: If the Cash Management A/C is stamped and the user is trying
22 -- to update with another payroll A/C, it raises the error PAY_34070_PAY_CE_MAP_ERR
23 -- when the given payroll A/C is already stamped with another Cash Management A/C.
24 --
25 -- 3) Scenario 3: If the user is trying to create/update an OPM with the bank details,
26 -- which are already used by another OPM, then it allows but without stamping.
27 --
28 -- 4) Scenario 4: Ensure that there is only one-to-one correspondence with the
29 -- Cash Management A/C and Payroll A/C.
30
31 Procedure update_payroll_bank_acct(
32 p_bank_account_id IN number,
33 p_external_account_id IN NUMBER,
34 p_org_payment_method_id IN number) IS
35
36 cursor csr_chk_payroll_bank_acct is
37 select cba.payroll_bank_account_id
38 from ce_bank_acct_uses_all cba
39 where cba.Bank_account_id = p_bank_account_id
40 and (-- Check Whether the given CE account is attached to another payroll account.
41 (cba.payroll_bank_account_id <> p_external_account_id and
42 cba.payroll_bank_account_id is not null and
43 cba.org_id in (select business_group_id from pay_org_payment_methods_f
44 where org_payment_method_id = p_org_payment_method_id))
45 -- Check whether the given Payroll account is attached to more than one CE account.
46 or exists (select Bank_account_id
47 from (select distinct cbb.Bank_account_id
48 from ce_bank_acct_uses_all cbb
49 where cbb.payroll_bank_account_id = p_external_account_id
50 and cbb.org_id in (select business_group_id from pay_org_payment_methods_f
51 where org_payment_method_id = p_org_payment_method_id))
52 group by Bank_account_id
53 having count(*) >1));
54 -- Need to check whether the payment is reconcilled.
55 -- If so then we should not allow the change of the bank account.
56 -- Following needs to be changed later.
57 -- or EXISTS (SELECT 'X' FROM pay_org_payment_methods_f
58 -- WHERE ORG_PAYMENT_METHOD_ID = p_org_payment_method_id);
59
60 CURSOR csr_chk_pre_payments_exists IS
61 SELECT 'X'
62 FROM SYS.DUAL
63 WHERE 1=2;
64 -- Need to change the following as well.
65 -- FROM pay_pre_payments
66 -- WHERE ORG_PAYMENT_METHOD_ID = p_org_payment_method_id;
67
68 l_payroll_bank_account_id pay_org_payment_methods_f.external_account_id%TYPE;
69 l_dummy varchar2(2);
70 l_proc varchar2(100) := g_package||'UPDATE_PAYROLL_BANK_ACCT';
71
72 begin
73
74 --
75 -- Only Update ce bank tables if both ce and pay are installed
76 --
77 hr_utility.set_location('Entering : '||l_proc, 10);
78 if pay_ce_support_pkg.pay_and_ce_licensed then
79
80 if p_bank_account_id is not null then
81
82 open csr_chk_payroll_bank_acct;
83 fetch csr_chk_payroll_bank_acct into l_payroll_bank_account_id;
84 if (csr_chk_payroll_bank_acct% NOTFOUND) THEN
85
86 hr_utility.set_location(l_proc||'Stamping has to be done', 20);
87 hr_utility.set_location(l_proc||'Stamping ....',30);
88 update ce_bank_acct_uses_all
89 set payroll_bank_account_id = p_external_account_id
90 where Bank_account_id = p_bank_account_id
91 and pay_use_enable_flag = 'Y'
92 and org_id in (select business_group_id from pay_org_payment_methods_f
93 where org_payment_method_id = p_org_payment_method_id);
94 close csr_chk_payroll_bank_acct;
95
96 hr_utility.set_location(l_proc||'Nullifying earlier stamping ....',40);
97 update ce_bank_acct_uses_all
98 set payroll_bank_account_id = null
99 where payroll_bank_account_id = p_external_account_id
100 AND Bank_account_id <> p_bank_account_id
101 and org_id in (select business_group_id from pay_org_payment_methods_f
102 where org_payment_method_id = p_org_payment_method_id);
103
104 else
105 hr_utility.set_location(l_proc||'No need of stamping ....',50);
106 close csr_chk_payroll_bank_acct;
107 -- Raising error, if the requested updation is going to break the one-to-one relationship
108 if (nvl(l_payroll_bank_account_id, -1) <> p_external_account_id) THEN
109 hr_utility.set_location(l_proc||'Raising an error ....',60);
110 fnd_message.set_name('PAY', 'PAY_34070_PAY_CE_MAP_ERR');
111 fnd_message.raise_error;
112 end if;
113 END if;
114 ELSE
115 OPEN csr_chk_pre_payments_exists;
116 FETCH csr_chk_pre_payments_exists INTO l_dummy;
117 IF (csr_chk_pre_payments_exists % NOTFOUND) THEN
118 update ce_bank_acct_uses_all
119 set payroll_bank_account_id = null
120 where payroll_bank_account_id = p_external_account_id
121 and pay_use_enable_flag = 'Y'
122 and org_id in (select business_group_id from pay_org_payment_methods_f
123 where org_payment_method_id = p_org_payment_method_id);
124 ELSE
125 fnd_message.set_name('PAY', 'HR_6226_PAYM_PPS_EXIST');
126 fnd_message.raise_error;
127 END IF;
128 CLOSE csr_chk_pre_payments_exists;
129 end if;
130
131 end if;
132
133 exception
134 when others then
135
136 -- anything else, we want to know about.
137
138 raise;
139 hr_utility.set_location('Leaving : '||l_proc, 10);
140 end;
141
142 procedure remove_redundant_bank_detail
143 is
144 begin
145 null;
146 end;
147
148 procedure get_payment_details
149 (
150 p_payment_id in number,
151 p_voided_payment in boolean,
152 p_pay_currency_code out nocopy varchar2,
153 p_recon_currency_code out nocopy varchar2,
154 p_value out nocopy varchar2,
155 p_base_currency_value out nocopy number,
156 p_action_status out nocopy varchar2,
157 p_business_group_Id out nocopy number
158 )is
159
160 cursor c_payment_details is
161 select
162 popm.currency_code pay_currency_code,
163 cba.currency_code recon_currency_code,
164 ppp.value,
165 nvl(ppp.base_currency_value,ppp.value) base_currency_value,
166 paa.action_status,
167 popm.business_group_id
168 from
169 pay_org_payment_methods_f popm,
170 ce_bank_accounts cba,
171 ce_bank_acct_uses_all apb,
172 pay_pre_payments ppp,
173 pay_assignment_actions paa,
174 pay_payroll_actions ppa
175 where
176 paa.assignment_action_id = p_payment_id
177 and ppa.payroll_action_id = paa.payroll_action_id
178 and ppa.action_type in ('H', 'M', 'E')
179 and ppp.pre_payment_id = paa.pre_payment_id
180 and ppp.org_payment_method_id = popm.org_payment_method_id
181 and cba.bank_account_id = apb.bank_account_id
182 and popm.external_account_id = apb.payroll_bank_account_id
183 and apb.org_id = popm.business_group_id;
184
185
186 cursor c_voided_payment is
187 select
188 popm.currency_code pay_currency_code,
192 paa.action_status,
189 cba.currency_code recon_currency_code,
190 ppp.value,
191 nvl(ppp.base_currency_value,ppp.value) base_currency_value,
193 popm.business_group_id
194 from
195 pay_org_payment_methods_f popm,
196 ce_bank_accounts cba,
197 ce_bank_acct_uses_all apb,
198 pay_pre_payments ppp,
199 pay_assignment_actions paa,
200 pay_assignment_actions paa1,
201 pay_payroll_actions ppa,
202 pay_action_interlocks pai
203 where
204 paa.assignment_action_id = p_payment_id
205 and ppa.payroll_action_id = paa.payroll_action_id
206 and ppa.action_type = 'D'
207 and ppp.pre_payment_id = paa1.pre_payment_id
208 and paa1.assignment_action_id = pai.locked_action_id
209 and paa.assignment_action_id = pai.locking_action_id
210 and ppp.org_payment_method_id = popm.org_payment_method_id
211 and cba.bank_account_id = apb.bank_account_id
212 and popm.external_account_id = apb.payroll_bank_account_id
213 and apb.org_id = popm.business_group_id;
214
215
216 begin
217 if p_voided_payment = false then
218 open c_payment_details;
219 fetch c_payment_details into p_pay_currency_code,p_recon_currency_code ,
220 p_value,p_base_currency_value ,p_action_status,p_business_group_Id;
221 close c_payment_details;
222 else
223 open c_voided_payment;
224 fetch c_voided_payment into p_pay_currency_code,p_recon_currency_code ,
225 p_value,p_base_currency_value ,p_action_status,p_business_group_Id;
226 close c_voided_payment;
227 end if;
228
229 end get_payment_details;
230
231
232
233 function chk_bank_row_exists
234 (
235 p_external_account_id in number
236 ) return varchar2
237 is
238 l_exists varchar2(1);
239
240 cursor csr_row_exists(c_external_account_id number) is
241 select 'Y'
242 from ce_bank_accounts cba,
243 ce_bank_acct_uses_all cbau
244 where cba.bank_account_id = cbau.bank_account_id
245 and cbau.payroll_bank_account_id = c_external_account_id
246 and pay_use_allowed_flag = 'Y'
247 and cbau.org_id = nvl(hr_general.get_business_group_id, cbau.org_id);
248 begin
249 l_exists := 'N';
250 open csr_row_exists(p_external_account_id);
251 fetch csr_row_exists into l_exists;
252 close csr_row_exists;
253
254 return l_exists;
255
256 end chk_bank_row_exists;
257
258
259
260 procedure get_chart_of_accts_and_sob
261 (
262 p_external_account_id in number,
263 p_char_of_accounts_id out nocopy number,
264 p_set_of_books_id out nocopy number,
265 p_name out nocopy varchar2,
266 p_asset_ccid out nocopy number
267 )is
268
269 begin
270
271 select DISTINCT null,null,null,aba.asset_code_combination_id
272 into p_char_of_accounts_id,p_set_of_books_id,p_name,p_asset_ccid
273 from ce_bank_accounts aba,
274 ce_bank_acct_uses_all cbau
275 where aba.bank_account_id = cbau.bank_account_id
276 and cbau.payroll_bank_account_id = p_external_account_id;
277
278 end get_chart_of_accts_and_sob;
279
280
281 procedure update_asset_ccid
282 (
283 p_assest_ccid in number,
284 p_set_of_books_id in number,
285 p_external_account_id in number
286 )is
287 begin
288 -- for R11.5 bank_account_id field will be null
289 null;
290 end;
291
292
293 Function get_sob_id
294 (
295 p_org_payment_method_id in number
296 ) return number is
297
298 begin
299 return -1;
300 end get_sob_id;
301
302 function chk_account_exists
303 (
304 p_org_payment_method_id in number,
305 p_validation_start_date in date,
306 p_validation_end_date in date
307 )return boolean
308 is
309 l_exists varchar2(1);
310 cursor csr_ap_details(c_org_payment_method_id number,
311 c_validation_start_date date,
312 c_validation_end_date date) is
313 select null
314 from pay_org_payment_methods_f opm,
315 ce_bank_accounts cba,
316 ce_bank_acct_uses_all cbau
317 where cba.bank_account_id = cbau.bank_account_id
318 and opm.org_payment_method_id = c_org_payment_method_id
319 and opm.effective_start_date between c_validation_start_date and c_validation_end_date
320 and opm.effective_end_date between c_validation_start_date and c_validation_end_date
321 and pay_use_allowed_flag = 'Y'
322 and cbau.PAYROLL_BANK_ACCOUNT_ID = opm.external_account_id
323 and opm.business_group_id = cbau.org_id;
324
325
326 begin
327 l_exists := 'N';
328 open csr_ap_details(p_org_payment_method_id,
329 p_validation_start_date,
330 p_validation_end_date);
331 fetch csr_ap_details into l_exists;
332 close csr_ap_details;
333
334 if l_exists = 'Y' then
335 return true;
336 else
337 return false;
338 end if;
339 end chk_account_exists;
340
341 procedure lock_row
342 (
343 p_external_account_id in number
344 )
345 is
349 from ce_bank_acct_uses_all aba
346
347 cursor ABA_CUR is
348 select *
350 where payroll_bank_account_id = p_external_account_id
351 FOR UPDATE OF BANK_ACCOUNT_ID NOWAIT;
352 l_rec ABA_CUR%rowtype;
353 --
354 begin
355 --
356 if p_external_account_id is not null then
357
358 open ABA_CUR;
359 fetch ABA_CUR into l_rec;
360 close ABA_CUR;
361
362 end if;
363 end lock_row;
364
365 -- ----------------------------------------------------------------------------
366 -- |---------------------------< GET_BANK_DETAILS >---------------------------|
367 -- ----------------------------------------------------------------------------
368 --
369 -- This procedure is used to get the bank detials of the existing OPMs.
370 --
371 procedure get_bank_details
372 (
373 p_external_account_id in number,
374 p_bank_account_id out nocopy number,
375 p_bank_account_name out nocopy varchar2
376 ) is
377 --
378 cursor csr_get_bank_details is
379 select DISTINCT bank_uses.bank_account_id,
380 accounts.bank_account_name
381 from ce_bank_acct_uses_all bank_uses,
382 ce_bank_accounts accounts
383 where bank_uses.payroll_bank_account_id = p_external_account_id
384 and bank_uses.bank_account_id = accounts.bank_account_id
385 and accounts.PAY_USE_ALLOWED_FLAG = 'Y';
386 --
387 begin
388 open csr_get_bank_details;
389 fetch csr_get_bank_details into p_bank_account_id, p_bank_account_name;
390 if (csr_get_bank_details%notfound) then
391 close csr_get_bank_details;
392 p_bank_account_id := null;
393 p_bank_account_name := null;
394 end if;
395 if (csr_get_bank_details%isopen) then
396 close csr_get_bank_details;
397 end if;
398
399 end get_bank_details;
400
401 end PAY_MAINTAIN_BANK_ACCT;
[Home] [Help]