[Home] [Help]
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
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.
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'
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
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);
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;
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,
191: nvl(ppp.base_currency_value,ppp.value) base_currency_value,
192: paa.action_status,
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,
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