[Home] [Help]
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 = p_payroll_id
-- and asg.payroll_id = p_payroll_id
AND pa2.consolidation_set_id = PAY_NO_PAYPROC_UTILITY.get_parameter(pa1.payroll_action_id,'CONSOLIDATION_SET_ID')
AND pa2.effective_date between fnd_date.canonical_to_date(nvl(PAY_NO_PAYPROC_UTILITY.get_parameter(pa1.payroll_action_id,'START_DATE'),
fnd_date.date_to_canonical(to_date('01/01/1001','DD/MM/YYYY'))))
and 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 IN ('C','S') -- 10229512
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 opm.pmeth_information_category <> 'NO_THIRD_PARTY_PAYMENT'
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 hr_asg.INCLUDE_OR_EXCLUDE = 'I'
))
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 ( p_le_id is null or exists ( select 1 from hr_soft_coding_keyflex hsk
where 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 = p_le_id
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
and p_date_earned between ppp.effective_start_date and ppp.effective_end_date;
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
and p_date_earned between ppp.effective_start_date and ppp.effective_end_date;
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 pop.pmeth_information_category <> 'NO_THIRD_PARTY_PAYMENT'
and ppt.payment_type_id=pop.payment_type_id
and (ppt.category in ('MT') or ppt.payment_type_name like 'NO Money Order')
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
WHERE ppf.person_id=paf.person_id
AND paf.assignment_id=v_act_id;
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;
select
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(convert(p_input_string,'UTF8'),
utl_raw.cast_to_varchar2(hextoraw('C386')),'AE'
),
utl_raw.cast_to_varchar2(hextoraw('C3A6')),'ae'
),
utl_raw.cast_to_varchar2(hextoraw('C398')),'O'
),
utl_raw.cast_to_varchar2(hextoraw('C3B8')),'o'
),
utl_raw.cast_to_varchar2(hextoraw('C385')),'AA'
),
utl_raw.cast_to_varchar2(hextoraw('C3A5')),'aa'
),
utl_raw.cast_to_varchar2(hextoraw('C389')),'E'
),
utl_raw.cast_to_varchar2(hextoraw('C3A9')),'e'
),
utl_raw.cast_to_varchar2(hextoraw('C384')),'A'
),
utl_raw.cast_to_varchar2(hextoraw('C3A4')),'a'
),
utl_raw.cast_to_varchar2(hextoraw('C396')),'O'
),
utl_raw.cast_to_varchar2(hextoraw('C3B6')),'o'
),
utl_raw.cast_to_varchar2(hextoraw('C388')),'E'
),
utl_raw.cast_to_varchar2(hextoraw('C3A8')),'e'
),
utl_raw.cast_to_varchar2(hextoraw('C381')),'A'
),
utl_raw.cast_to_varchar2(hextoraw('C3A1')),'a'
)
from dual;