[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_package varchar2(33) := ' UPDATE_PAYROLL_BANK_ACCT.';
Procedure update_payroll_bank_acct(
p_bank_account_id IN number,
p_external_account_id IN NUMBER,
p_org_payment_method_id IN number) IS
cursor csr_chk_payroll_bank_acct is
select cba.payroll_bank_account_id
from ce_bank_acct_uses_all cba
where cba.Bank_account_id = p_bank_account_id
and (-- Check Whether the given CE account is attached to another payroll account.
(cba.payroll_bank_account_id <> p_external_account_id and
cba.payroll_bank_account_id is not null and
cba.org_id in (select business_group_id from pay_org_payment_methods_f
where org_payment_method_id = p_org_payment_method_id))
-- Check whether the given Payroll account is attached to more than one CE account.
or exists (select Bank_account_id
from (select distinct cbb.Bank_account_id
from ce_bank_acct_uses_all cbb
where cbb.payroll_bank_account_id = p_external_account_id
and cbb.org_id in (select business_group_id from pay_org_payment_methods_f
where org_payment_method_id = p_org_payment_method_id))
group by Bank_account_id
having count(*) >1));
-- or EXISTS (SELECT 'X' FROM pay_org_payment_methods_f
-- WHERE ORG_PAYMENT_METHOD_ID = p_org_payment_method_id);
SELECT 'X'
FROM SYS.DUAL
WHERE 1=2;
l_proc varchar2(100) := g_package||'UPDATE_PAYROLL_BANK_ACCT';
update ce_bank_acct_uses_all
set payroll_bank_account_id = p_external_account_id
where Bank_account_id = p_bank_account_id
and pay_use_enable_flag = 'Y'
and org_id in (select business_group_id from pay_org_payment_methods_f
where org_payment_method_id = p_org_payment_method_id);
update ce_bank_acct_uses_all
set payroll_bank_account_id = null
where payroll_bank_account_id = p_external_account_id
AND Bank_account_id <> p_bank_account_id
and org_id in (select business_group_id from pay_org_payment_methods_f
where org_payment_method_id = p_org_payment_method_id);
update ce_bank_acct_uses_all
set payroll_bank_account_id = null
where payroll_bank_account_id = p_external_account_id
and pay_use_enable_flag = 'Y'
and org_id in (select business_group_id from pay_org_payment_methods_f
where org_payment_method_id = p_org_payment_method_id);
select
popm.currency_code pay_currency_code,
cba.currency_code recon_currency_code,
ppp.value,
nvl(ppp.base_currency_value,ppp.value) base_currency_value,
paa.action_status,
popm.business_group_id
from
pay_org_payment_methods_f popm,
ce_bank_accounts cba,
ce_bank_acct_uses_all apb,
pay_pre_payments ppp,
pay_assignment_actions paa,
pay_payroll_actions ppa
where
paa.assignment_action_id = p_payment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('H', 'M', 'E')
and ppp.pre_payment_id = paa.pre_payment_id
and ppp.org_payment_method_id = popm.org_payment_method_id
and cba.bank_account_id = apb.bank_account_id
and popm.external_account_id = apb.payroll_bank_account_id
and apb.org_id = popm.business_group_id;
select
popm.currency_code pay_currency_code,
cba.currency_code recon_currency_code,
ppp.value,
nvl(ppp.base_currency_value,ppp.value) base_currency_value,
paa.action_status,
popm.business_group_id
from
pay_org_payment_methods_f popm,
ce_bank_accounts cba,
ce_bank_acct_uses_all apb,
pay_pre_payments ppp,
pay_assignment_actions paa,
pay_assignment_actions paa1,
pay_payroll_actions ppa,
pay_action_interlocks pai
where
paa.assignment_action_id = p_payment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type = 'D'
and ppp.pre_payment_id = paa1.pre_payment_id
and paa1.assignment_action_id = pai.locked_action_id
and paa.assignment_action_id = pai.locking_action_id
and ppp.org_payment_method_id = popm.org_payment_method_id
and cba.bank_account_id = apb.bank_account_id
and popm.external_account_id = apb.payroll_bank_account_id
and apb.org_id = popm.business_group_id;
select 'Y'
from ce_bank_accounts cba,
ce_bank_acct_uses_all cbau
where cba.bank_account_id = cbau.bank_account_id
and cbau.payroll_bank_account_id = c_external_account_id
and pay_use_allowed_flag = 'Y'
and cbau.org_id = nvl(hr_general.get_business_group_id, cbau.org_id);
select DISTINCT null,null,null,aba.asset_code_combination_id
into p_char_of_accounts_id,p_set_of_books_id,p_name,p_asset_ccid
from ce_bank_accounts aba,
ce_bank_acct_uses_all cbau
where aba.bank_account_id = cbau.bank_account_id
and cbau.payroll_bank_account_id = p_external_account_id;
procedure update_asset_ccid
(
p_assest_ccid in number,
p_set_of_books_id in number,
p_external_account_id in number
)is
begin
-- for R11.5 bank_account_id field will be null
null;
select null
from pay_org_payment_methods_f opm,
ce_bank_accounts cba,
ce_bank_acct_uses_all cbau
where cba.bank_account_id = cbau.bank_account_id
and opm.org_payment_method_id = c_org_payment_method_id
and opm.effective_start_date between c_validation_start_date and c_validation_end_date
and opm.effective_end_date between c_validation_start_date and c_validation_end_date
and pay_use_allowed_flag = 'Y'
and cbau.PAYROLL_BANK_ACCOUNT_ID = opm.external_account_id
and opm.business_group_id = cbau.org_id;
select *
from ce_bank_acct_uses_all aba
where payroll_bank_account_id = p_external_account_id
FOR UPDATE OF BANK_ACCOUNT_ID NOWAIT;
select DISTINCT bank_uses.bank_account_id,
accounts.bank_account_name
from ce_bank_acct_uses_all bank_uses,
ce_bank_accounts accounts
where bank_uses.payroll_bank_account_id = p_external_account_id
and bank_uses.bank_account_id = accounts.bank_account_id
and accounts.PAY_USE_ALLOWED_FLAG = 'Y';