[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ppa.legislative_parameters,
pqp_ustiaa_pkg.get_parameter('TRANSFER_CONC_SET',ppa.legislative_parameters),
pqp_ustiaa_pkg.get_parameter('TRANSFER_PAYROLL',ppa.legislative_parameters),
pqp_ustiaa_pkg.get_parameter('TRANSFER_GRE',ppa.legislative_parameters)
INTO leg_param,
l_consolidation_set_id,
l_payroll_id,
l_tax_unit_id
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = pactid;
sqlstr := 'select distinct asg.person_id
from per_assignments_f asg,
pay_assignment_actions act_run,
pay_payroll_actions ppa_run,
pay_payroll_actions ppa_gen
where ppa_gen.payroll_action_id = :payroll_action_id
and ppa_run.action_type in (''R'',''Q'',''V'',''B'')
and ppa_run.action_status = ''C''
'||l_consolidation_set_text||'
'||l_payroll_text||'
and ppa_run.payroll_action_id = act_run.payroll_action_id
'||l_tax_unit_text||'
and asg.assignment_id = act_run.assignment_id
and ppa_run.effective_date between asg.effective_start_date
and asg.effective_end_date
and asg.business_group_id = ppa_gen.business_group_id
order by asg.person_id';
SELECT ppa.legislative_parameters,
pqp_ustiaa_pkg.get_parameter('TRANSFER_CONC_SET',ppa.legislative_parameters),
pqp_ustiaa_pkg.get_parameter('TRANSFER_PAYROLL',ppa.legislative_parameters),
pqp_ustiaa_pkg.get_parameter('TRANSFER_GRE',ppa.legislative_parameters)
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = pactid;
SELECT
ppa_run.action_type ,
act_run.assignment_action_id,
asg.assignment_id ,
act_run.tax_unit_id
FROM per_assignments_f asg,
pay_payroll_actions ppa_run,
pay_assignment_actions act_run,
pay_payroll_actions ppa_gen
WHERE
ppa_gen.payroll_action_id = pactid
AND ppa_run.effective_date BETWEEN ppa_gen.start_date
AND ppa_gen.effective_date
AND ppa_run.action_type IN ('R','Q','V','B')
AND ppa_run.action_status = 'C'
AND ppa_run.consolidation_set_id = NVL(l_consolidation_set_id,
ppa_run.consolidation_set_id)
AND ppa_run.payroll_id = NVL(l_payroll_id,
ppa_run.payroll_id)
AND ppa_run.payroll_action_id = act_run.payroll_action_id
AND act_run.action_status = 'C'
AND act_run.tax_unit_id = NVL(l_tax_unit_id,
act_run.tax_unit_id)
AND asg.assignment_id = act_run.assignment_id
AND ppa_run.effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.business_group_id = ppa_gen.business_group_id
AND asg.person_id BETWEEN stperson
AND endperson
order by asg.assignment_id, act_run.assignment_action_id
FOR UPDATE OF asg.assignment_id;
SELECT TO_NUMBER(ue.creator_id) creator_id,
di.user_name
FROM ff_user_entities ue,
ff_database_items di
WHERE di.user_name IN ( 'GROSS_EARNINGS_ASG_GRE_RUN',
'PAYMENTS_ASG_GRE_RUN' )
AND ue.user_entity_id = di.user_entity_id
AND ue.creator_type = 'B'
AND NVL(ue.legislation_code,'US') = 'US';
SELECT pay_assignment_actions_s.nextval
INTO lockingactid
FROM dual;
sqlstr := 'select paa1.rowid
from pay_assignment_actions paa1,
pay_payroll_actions ppa1
where ppa1.payroll_action_id = :pactid
and paa1.payroll_action_id = ppa1.payroll_action_id
order by paa1.assignment_id,paa1.assignment_action_id
for update of paa1.assignment_id';
SELECT ppa.legislative_parameters,
pqp_ustiaa_pkg.get_parameter('TRANSFER_CONC_SET',ppa.legislative_parameters),
pqp_ustiaa_pkg.get_parameter('TRANSFER_PAYROLL',ppa.legislative_parameters),
pqp_ustiaa_pkg.get_parameter('TRANSFER_GRE',ppa.legislative_parameters)
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = pactid;
SELECT
max(act_run.assignment_action_id),
asg.assignment_id
FROM per_assignments_f asg,
pay_payroll_actions ppa_run,
pay_assignment_actions act_run,
pay_payroll_actions ppa_gen
WHERE
ppa_gen.payroll_action_id = pactid
AND ppa_run.effective_date BETWEEN ppa_gen.start_date
AND ppa_gen.effective_date
AND ppa_run.action_type IN ('R','Q','V','B')
AND ppa_run.action_status = 'C'
AND ppa_run.consolidation_set_id = NVL(l_consolidation_set_id,
ppa_run.consolidation_set_id)
AND ppa_run.payroll_id = NVL(l_payroll_id,
ppa_run.payroll_id)
AND ppa_run.payroll_action_id = act_run.payroll_action_id
AND act_run.action_status = 'C'
AND act_run.tax_unit_id = NVL(l_tax_unit_id,
act_run.tax_unit_id)
AND asg.assignment_id = act_run.assignment_id
AND ppa_run.effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.business_group_id = ppa_gen.business_group_id
AND asg.person_id BETWEEN stperson
AND endperson
AND EXISTS (
select NULL
FROM pay_run_results rr,
pay_element_types_f e,
pay_element_type_extra_info ei
WHERE rr.assignment_action_id = act_run.assignment_action_id
AND rr.element_type_id = e.element_type_id
AND e.element_type_id = ei.element_type_id
AND ei.information_type = 'US_TIAA_CREF_CONT_TYPES'
AND ppa_run.effective_date BETWEEN e.effective_start_date AND e.effective_end_date
)
group by asg.assignment_id;
SELECT TO_NUMBER(ue.creator_id) creator_id,
di.user_name
FROM ff_user_entities ue,
ff_database_items di
WHERE di.user_name IN ( 'GROSS_EARNINGS_ASG_GRE_RUN',
'PAYMENTS_ASG_GRE_RUN' )
AND ue.user_entity_id = di.user_entity_id
AND ue.creator_type = 'B'
AND NVL(ue.legislation_code,'US') = 'US';
SELECT pay_assignment_actions_s.nextval
INTO lockingactid
FROM dual;
SELECT db.defined_balance_id
FROM pay_defined_balances db,
pay_balance_dimensions bd
WHERE bd.dimension_name IN ('Assignment-Level Current Run' ,'Assignment Default Run')
AND bd.balance_dimension_id = db.balance_dimension_id
AND db.balance_type_id = p_bal_type_id;
SELECT paei.aei_information2,
pasg.payroll_id
FROM per_assignment_extra_info paei ,
per_assignments_f pasg
WHERE pasg.assignment_id = p_asg_id
AND pasg.assignment_id = paei.assignment_id(+)
AND paei.information_type(+) = 'PQP_US_TIAA_CREF_CODES'
AND p_date BETWEEN pasg.effective_start_date
AND pasg.effective_end_date;
SELECT prl.prl_information14
FROM pay_payrolls_f prl
WHERE prl.payroll_id = p_payroll_id
AND prl.prl_information_category = 'US'
AND p_date BETWEEN prl.effective_start_date
AND prl.effective_end_date;
SELECT org_information2
FROM hr_organization_information
WHERE org_information_context = 'PQP_US_TIAA_CREF_CODES'
AND organization_id = p_tax_unit_id;
select pei.aei_information1,pei.aei_information2
from per_assignment_extra_info pei
where pei.assignment_id = p_asg_id
and pei.aei_information_category = 'PAY_US_TIAA_CREF_CONT_TYPE'
and pei.aei_information3 = p_ele_entry_id;
SELECT count(*)
FROM pay_run_results rr,
pay_element_types_f e,
pay_element_type_extra_info ei
WHERE rr.assignment_action_id = p_asgact_id
AND rr.element_type_id = e.element_type_id
AND e.element_type_id = ei.element_type_id
AND ei.information_type = 'US_TIAA_CREF_CONT_TYPES'
AND p_date BETWEEN e.effective_start_date AND e.effective_end_date
AND rr.entry_type not in ('A','R')
and rr.rowid > (SELECT min(rr1.rowid)
FROM pay_run_results rr1,
pay_element_types_f e1,
pay_element_type_extra_info ei1
WHERE rr1.assignment_action_id = rr.assignment_action_id
AND rr1.element_type_id = e1.element_type_id
AND e1.element_type_id = ei1.element_type_id
AND ei1.information_type = 'US_TIAA_CREF_CONT_TYPES'
AND e1.element_information10 = e.element_information10
AND p_date BETWEEN e1.effective_start_date AND e1.effective_end_date
AND rr1.entry_type not in ('A','R'));
select ppa.start_date,
ppa.effective_date,
bg.name
from per_business_groups bg,
pay_payroll_actions ppa
where ppa.payroll_action_id = pactid
and ppa.business_group_id = bg.business_group_id;
select papf.last_name last_name,
papf.first_name first_name,
papf.middle_names middle_name,
papf.national_identifier ssn,
papf.sex gender,
papf.date_of_birth dob,
padd.address_line1 addr1,
padd.address_line2 addr2,
padd.address_line3 addr3,
padd.country country,
padd.town_or_city city,
padd.region_2 state,
padd.postal_code postal_code,
paf.assignment_number
from per_all_assignments_f paf
, per_all_people_f papf
, per_addresses padd
where paf.assignment_id = p_asg_id
and paf.person_id = papf.person_id
and padd.person_id = papf.person_id
and padd.primary_flag = 'Y'
and p_date between paf.effective_start_date and paf.effective_end_date
and p_date between papf.effective_start_date and papf.effective_end_date
and p_date between padd.date_from and nvl(padd.date_to,p_date);
select paa.assignment_id asg_id,
paa.assignment_action_id act_id,
ppa_run.effective_date pay_date,
paa.tax_unit_id tax_unit_id
from pay_payroll_actions ppa_gen,
pay_assignment_actions paa,
pay_payroll_actions ppa_run,
pay_temp_object_actions poa
where poa.object_action_id = p_assactid
and poa.payroll_action_id = ppa_gen.payroll_action_id
and poa.object_id = paa.assignment_id
and paa.payroll_action_id = ppa_run.payroll_action_id
and (paa.source_action_id is not null
or (paa.source_action_id is null and ppa_run.action_type in ('B','V')))
and ppa_run.effective_date between ppa_gen.start_date and ppa_gen.effective_date
and ppa_run.action_type in ('R','Q','B','V')
and ppa_run.action_status = 'C'
AND ppa_run.consolidation_set_id = NVL(l_consolidation_set_id,
ppa_run.consolidation_set_id)
AND ppa_run.payroll_id = NVL(l_payroll_id,
ppa_run.payroll_id)
AND paa.tax_unit_id = NVL(l_tax_unit_id,
paa.tax_unit_id)
AND EXISTS (
select NULL
from pay_element_entries_f ee,
pay_element_types_f e,
pay_element_type_extra_info ei
where ee.assignment_id = paa.assignment_id
and ee.element_type_id = e.element_type_id
and ei.element_type_id = e.element_type_id
and ei.information_type = 'US_TIAA_CREF_CONT_TYPES'
and ppa_run.effective_date between e.effective_start_date and e.effective_end_date
and ppa_run.effective_date between ee.effective_start_date and ee.effective_end_date
);
SELECT rr.element_entry_id ele_entry_id,
ei.eei_information1 cont_source,
ei.eei_information2 cont_sub_plan,
ei.eei_information3 plan_no,
e.element_information10 bal_type_id,
e.element_name
FROM pay_run_results rr,
pay_element_types_f e,
pay_element_type_extra_info ei
WHERE rr.assignment_action_id = p_asgact_id
AND rr.element_type_id = e.element_type_id
AND e.element_type_id = ei.element_type_id
AND ei.information_type = 'US_TIAA_CREF_CONT_TYPES'
AND p_date BETWEEN e.effective_start_date AND e.effective_end_date
AND rr.entry_type not in ('A','R');
SELECT prl.prl_information4,
prl.period_type
FROM per_all_assignments_f paf,
pay_payrolls_f prl
WHERE paf.assignment_id = p_asg_id
AND prl.payroll_id = paf.payroll_id
AND prl.prl_information_category = 'US'
AND p_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND p_date BETWEEN prl.effective_start_date
AND prl.effective_end_date;
SELECT ppa.legislative_parameters,
pqp_ustiaa_pkg.get_parameter('TRANSFER_CONC_SET',ppa.legislative_parameters),
pqp_ustiaa_pkg.get_parameter('TRANSFER_PAYROLL',ppa.legislative_parameters),
pqp_ustiaa_pkg.get_parameter('TRANSFER_GRE',ppa.legislative_parameters)
FROM pay_payroll_actions ppa,
pay_temp_object_actions poa
WHERE poa.object_action_id = asg_actid
and ppa.payroll_action_id = poa.payroll_action_id;