[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ppa.legislative_parameters ,
pqp_exppreproc_pkg.get_parameter('TRANSFER_CONC_SET',ppa.legislative_parameters) ,
pqp_exppreproc_pkg.get_parameter('TRANSFER_PAYROLL',ppa.legislative_parameters) ,
pqp_exppreproc_pkg.get_parameter('TRANSFER_GRE',ppa.legislative_parameters),
pqp_exppreproc_pkg.get_parameter('TRANSFER_REPORT',ppa.legislative_parameters),
pqp_exppreproc_pkg.get_parameter('TRANSFER_GROUP',ppa.legislative_parameters),
pqp_exppreproc_pkg.get_parameter('TRANSFER_DATE',ppa.legislative_parameters)
INTO leg_param,
l_consolidation_set_id,
l_payroll_id,
l_tax_unit_id,
l_report_id ,
l_group_name,
l_start_date
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, /* run and quickpay assignment actions */
pay_payroll_actions ppa_run, /* run and quickpay payroll actions */
pay_payroll_actions ppa_gen /* PYUGEN information */
where ppa_gen.payroll_action_id = :payroll_action_id
and ppa_run.action_type in (''R'',''Q'',''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 ppa_run.payroll_action_id = act_run.payroll_action_id
and act_run.action_status = ''C''
and asg.assignment_id = act_run.assignment_id
and ppa_run.effective_date between /* date join btwn run and asg */
asg.effective_start_date
and asg.effective_end_date
and asg.business_group_id +0 = ppa_gen.business_group_id
order by asg.person_id';
SELECT ppa.legislative_parameters,
pqp_exppreproc_pkg.get_parameter('TRANSFER_CONC_SET',ppa.legislative_parameters),
pqp_exppreproc_pkg.get_parameter('TRANSFER_PAYROLL',ppa.legislative_parameters),
pqp_exppreproc_pkg.get_parameter('TRANSFER_GRE',ppa.legislative_parameters) ,
pqp_exppreproc_pkg.get_parameter('TRANSFER_DATE',ppa.legislative_parameters),
pqp_exppreproc_pkg.get_parameter('TRANSFER_JD',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, /* run and quickpay payroll actions */
pay_assignment_actions act_run, /* run and quickpay assignment actions */
pay_payroll_actions ppa_gen, /* PYUGEN information */
per_time_periods ptp
WHERE ppa_gen.payroll_action_id = pactid
--Added by Gattu
AND ptp.payroll_id = nvl(l_payroll_id,
ppa_run.payroll_id)
AND (ppa_run.effective_date BETWEEN ptp.start_date AND
ptp.end_date
OR ppa_run.effective_date = ptp.regular_payment_date )
-- AND (DECODE (off_date ,1,ppa_run.effective_date,ppa_gen.effective_date
-- )
AND ptp.end_date
between /* date join btwn run and pyugen ppa */
ppa_gen.start_date
and ppa_gen.effective_date --)
AND ppa_run.action_type in ('R','Q','V')
AND ppa_run.action_status = 'C'
AND ppa_run.consolidation_set_id = l_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 asg.assignment_id = act_run.assignment_id
AND ppa_run.effective_date between /* date join btwn run and asg */
asg.effective_start_date
and asg.effective_end_date
AND asg.business_group_id = ppa_gen.business_group_id
AND ( asg.soft_coding_keyflex_id IN
(SELECT hsck.soft_coding_keyflex_id
FROM hr_soft_coding_keyflex hsck
WHERE hsck.segment1 = TO_CHAR(l_tax_unit_id)
OR hsck.segment11 = TO_CHAR(l_tax_unit_id) /* Bug 13955510 */
OR hsck.segment12 = TO_CHAR(l_tax_unit_id)
)
OR l_tax_unit_id IS NULL)
AND (l_jd_cd IS NULL OR
l_jd_cd in (select jurisdiction_code
from pay_us_emp_state_tax_rules_f puest
WHERE puest.assignment_id=asg.assignment_id
AND ppa_run.effective_date BETWEEN
puest.effective_start_date AND
puest.effective_end_date)
)
AND asg.person_id between stperson and endperson
GROUP BY asg.assignment_id;
SELECT /*+ LEADING(ppa_gen ppa_run ptp asg act_run) */
MAX(act_run.assignment_action_id),
asg.assignment_id
FROM per_assignments_f asg,
pay_payroll_actions ppa_run, /* run and quickpay payroll actions */
pay_assignment_actions act_run, /* run and quickpay assignment actions */
pay_payroll_actions ppa_gen, /* PYUGEN information */
per_time_periods ptp
WHERE ppa_gen.payroll_action_id = pactid
--Added by Gattu
AND ptp.payroll_id = l_payroll_id
AND (ppa_run.effective_date BETWEEN ptp.start_date AND
ptp.end_date
OR ppa_run.effective_date = ptp.regular_payment_date )
-- AND (DECODE (off_date ,1,ppa_run.effective_date,ppa_gen.effective_date
-- )
AND ptp.end_date
between /* date join btwn run and pyugen ppa */
ppa_gen.start_date
and ppa_gen.effective_date --)
AND ppa_run.action_type in ('R','Q','V')
AND ppa_run.action_status = 'C'
AND ppa_run.consolidation_set_id = l_consolidation_set_id
AND ppa_run.payroll_id = l_payroll_id
AND ppa_run.payroll_action_id = act_run.payroll_action_id
AND act_run.action_status = 'C'
AND asg.assignment_id = act_run.assignment_id
AND ppa_run.effective_date between /* date join btwn run and asg */
asg.effective_start_date
and asg.effective_end_date
AND asg.business_group_id = ppa_gen.business_group_id
AND ( asg.soft_coding_keyflex_id IN
(SELECT hsck.soft_coding_keyflex_id
FROM hr_soft_coding_keyflex hsck
WHERE hsck.segment1 = TO_CHAR(l_tax_unit_id)
OR hsck.segment11 = TO_CHAR(l_tax_unit_id) /* Bug 13955510 */
OR hsck.segment12 = TO_CHAR(l_tax_unit_id)
)
OR l_tax_unit_id IS NULL)
AND (l_jd_cd IS NULL OR
l_jd_cd in (select jurisdiction_code
from pay_us_emp_state_tax_rules_f puest
WHERE puest.assignment_id=asg.assignment_id
AND ppa_run.effective_date BETWEEN
puest.effective_start_date AND
puest.effective_end_date)
)
AND asg.person_id between stperson and endperson
GROUP BY asg.assignment_id;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
-- insert the action record.
hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
-- insert an interlock to this action.
hr_nonrun_asact.insint(lockingactid,lockedactid);
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
-- insert the action record.
hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
-- insert an interlock to this action.
hr_nonrun_asact.insint(lockingactid,lockedactid);
sqlstr := 'select paa1.rowid
from pay_assignment_actions paa1, -- PYUGEN assignment action
pay_payroll_actions ppa1 -- PYUGEN payroll action id
where ppa1.payroll_action_id = :pactid
and paa1.payroll_action_id = ppa1.payroll_action_id
order by paa1.assignment_action_id for update of paa1.assignment_id';
select
pay_core_utils.get_parameter('REMOVE_ACT',
ppa.legislative_parameters)
into l_remove_act
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;