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