The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_sqlstr := 'SELECT DISTINCT person_id
FROM per_all_people_f ppf,
pay_payroll_actions ppa
WHERE ppa.payroll_action_id = :payroll_action_id
AND ppa.business_group_id = ppf.business_group_id
ORDER BY ppf.person_id';
SELECT act.assignment_action_id,
act.assignment_id,
ppp.pre_payment_id
FROM pay_assignment_actions act,
per_all_assignments_f asg,
pay_payroll_actions pa2,
pay_payroll_actions pa1,
pay_pre_payments ppp,
pay_org_payment_methods_f OPM,
per_all_people_f pap,
hr_soft_coding_keyflex hsk,
pay_payment_types ppt
WHERE pa1.payroll_action_id = p_payroll_action_id
AND pa2.payroll_id = NVL(p_payroll_id,pa2.payroll_id)
AND pa2.effective_date <= pa1.effective_date
AND pa2.action_type IN ('U','P') -- Prepayments or Quickpay Prepayments
AND act.payroll_action_id = pa2.payroll_action_id
AND act.action_status = 'C'
AND asg.assignment_id = act.assignment_id
AND pa1.business_group_id = asg.business_group_id
AND pa1.effective_date between asg.effective_start_date and asg.effective_end_date
AND pa1.effective_date between pap.effective_start_date and pap.effective_end_date
AND pa1.effective_date between opm.effective_start_date and opm.effective_end_date
AND pap.person_id = asg.person_id
AND pap.person_id between p_start_person_id and p_end_person_id
AND ppp.assignment_action_id = act.assignment_action_id
AND ppp.org_payment_method_id = opm.org_payment_method_id
AND ppt.payment_type_id= opm.payment_type_id
AND ( ppt.payment_type_name like 'NO Money Order' or ppt.category in ('MT'))
AND pap.person_id = NVL(p_person_id,pap.person_id)
AND (ppt.category in ('MT') or exists ( select '1'
FROM per_addresses pad
WHERE pad.person_id = asg.person_id
and pad.PRIMARY_FLAG ='Y')
)
AND (p_assignment_set_id IS NULL
OR EXISTS ( SELECT ''
FROM hr_assignment_set_amendments hr_asg
WHERE hr_asg.assignment_set_id = p_assignment_set_id
AND hr_asg.assignment_id = asg.assignment_id
))
AND NOT EXISTS (SELECT /*+ ORDERED */ NULL
FROM pay_action_interlocks pai1,
pay_assignment_actions act2,
pay_payroll_actions appa
WHERE pai1.locked_action_id = act.assignment_action_id
AND act2.assignment_action_id = pai1.locking_action_id
AND act2.payroll_action_id = appa.payroll_action_id
-- AND appa.action_type = 'X'
AND appa.action_type = 'M'
AND appa.report_type = 'NO_PP')
and hsk.SOFT_CODING_KEYFLEX_ID = asg.SOFT_CODING_KEYFLEX_ID
and hsk.enabled_flag = 'Y'
and hsk.segment2 in (
select hoi2.org_information1
from HR_ORGANIZATION_UNITS o1
, HR_ORGANIZATION_INFORMATION hoi1
, HR_ORGANIZATION_INFORMATION hoi2
WHERE o1.business_group_id = pa1.business_group_id
and hoi1.organization_id = o1.organization_id
and hoi1.organization_id = to_number(PAY_NO_PAYPROC_UTILITY.get_parameter
(pa1.payroll_action_id,'LEGAL_EMPLOYER'))
and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
and hoi1.organization_id = hoi2.organization_id
and hoi2.ORG_INFORMATION_CONTEXT='NO_LOCAL_UNITS'
) ;
SELECT pay_assignment_actions_s.nextval
INTO l_locking_action_id
FROM dual;
select pop.pmeth_information1
from pay_org_payment_methods_f pop,
pay_payment_types ppt,
pay_org_pay_method_usages_f ppu,
pay_payroll_actions ppa
where ppt.payment_type_id=pop.payment_type_id
and ppt.category in ('MT')
and ppu.payroll_id=p_payroll_id
and pop.org_payment_method_id=ppu.org_payment_method_id
and ppa.effective_date between pop.effective_start_date and pop.effective_end_date
and ppa.payroll_action_id=p_payroll_action_id
and rownum<2;
update pay_payroll_actions set action_type='M' where payroll_action_id=p_payroll_action_id;
select pea.segment6
from pay_external_accounts pea,
pay_personal_payment_methods_f ppp
where
ppp.personal_payment_method_id=p_per_pay_method_id
and ppp.external_account_id=pea.external_account_id;
select ADDRESS_LINE1,ADDRESS_LINE2,POSTAL_CODE --,TOWN_OR_CITY
FROM per_addresses pad
,per_all_assignments_f paf
,per_all_people_f pef
WHERE paf.assignment_id=p_assignment_id
and paf.person_id = pef.person_id
and pad.person_id= pef.person_id
and pad.PRIMARY_FLAG ='Y';
select pea.segment6
from pay_external_accounts pea,
pay_personal_payment_methods_f ppp
where
ppp.personal_payment_method_id=p_per_pay_method_id
and ppp.external_account_id=pea.external_account_id;
select o.name
from HR_ORGANIZATION_UNITS o
WHERE o.business_group_id = p_business_group_id
and o.organization_id = to_number(p_legal_emp_id);
FUNCTION update_seq_values (
p_payroll_id IN number
,p_ah_seq IN varchar2
,p_seq_control IN VARCHAR2) return varchar2 IS
l_org_pay_method_id NUMBER;
select pop.ORG_PAYMENT_METHOD_ID
into l_org_pay_method_id
from pay_org_payment_methods_f pop,
pay_payment_types ppt,
pay_org_pay_method_usages_f ppu
where ppt.payment_type_id=pop.payment_type_id
and ppt.category in ('MT')
and ppu.payroll_id=p_payroll_id
and pop.org_payment_method_id=ppu.org_payment_method_id
and rownum<2;
update pay_org_payment_methods_f
set pmeth_information3=l_ah_seq , pmeth_information4=l_seq_control
where ORG_PAYMENT_METHOD_ID=l_org_pay_method_id;
end update_seq_values;
SELECT ppf.employee_number
FROM per_all_people_f ppf,
per_all_assignments_f paf,
pay_assignment_actions paa
WHERE ppf.person_id=paf.person_id
AND paf.assignment_id=paa.assignment_id
AND paa.assignment_action_id=v_asg_act_id;
select meaning
from hr_lookups
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code;