[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SUBSTR(legislative_parameters,
INSTR(legislative_parameters,p_token)+(LENGTH(p_token)+1),
INSTR(legislative_parameters,' ',
INSTR(legislative_parameters,p_token))
- (INSTR(legislative_parameters,p_token)+LENGTH(p_token))),
business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_pact_id;
This code returns the select statement that
should be used to generate the ranges.
*/
PROCEDURE range_cursor (pactid IN NUMBER,
sqlstr OUT NOCOPY VARCHAR2)
-- public procedure which archives the payroll information, then returns a
-- varchar2 defining a SQL statement to select all the people that may be
-- eligible for payslip reports.
-- The archiver uses this cursor to split the people into chunks for parallel
-- processing.
IS
--
l_proc CONSTANT VARCHAR2(50):= g_package||'range_cursor';
sqlstr := 'SELECT DISTINCT person_id
FROM per_people_f ppf,
pay_payroll_actions ppa
WHERE ppa.payroll_action_id = :payroll_action_id
AND ppa.business_group_id +0 = ppf.business_group_id
ORDER BY ppf.person_id';
SELECT act.assignment_id assignment_id,
act.assignment_action_id run_action_id,
act1.assignment_action_id prepaid_action_id
FROM pay_payroll_actions ppa,
pay_payroll_actions appa,
pay_payroll_actions appa2,
pay_assignment_actions act,
pay_assignment_actions act1,
pay_action_interlocks pai,
per_all_assignments_f as1
WHERE ppa.payroll_action_id = p_pact_id
AND appa2.consolidation_set_id = p_consolidation_id
AND appa2.effective_date BETWEEN
ppa.start_date AND ppa.effective_date
AND as1.person_id BETWEEN
stperson AND endperson
AND appa.action_type IN ('R','Q') -- Payroll Run or Quickpay Run
AND act.payroll_action_id = appa.payroll_action_id
AND act.source_action_id IS NULL
AND as1.assignment_id = act1.assignment_id
AND ppa.effective_date BETWEEN
as1.effective_start_date AND as1.effective_end_date
AND act.action_status = 'C'
AND act.assignment_action_id = pai.locked_action_id
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status = 'C'
AND act1.payroll_action_id = appa2.payroll_action_id
AND appa2.action_type IN ('P','U') -- Prepayments or Quickpay Prepayments
AND (as1.payroll_id = p_payroll_id OR p_payroll_id IS NULL)
AND NOT EXISTS (SELECT /*+ ORDERED */ NULL
FROM pay_action_interlocks pai1,
pay_assignment_actions act2,
pay_payroll_actions appa3
WHERE pai1.locked_action_id = act.assignment_action_id
AND act2.assignment_action_id = pai1.locking_action_id
AND act2.payroll_action_id = appa3.payroll_action_id
AND appa3.action_type = 'X'
AND appa3.report_type = p_report_type
AND appa3.report_qualifier = p_report_qualifier)
ORDER BY act.assignment_id, act.assignment_action_id
FOR UPDATE OF as1.assignment_id;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
select run_type_id
into l_run_type_id
from pay_payroll_actions
where payroll_action_id = p_payroll_action_id;
select action_sequence
into l_act_seq
from pay_assignment_actions
where assignment_action_id = p_assignment_action_id;
SELECT MAX(paa1.action_sequence)
into l_act_seq
FROM pay_assignment_actions paa1,
pay_assignment_actions paa2,
pay_run_types_f prt1
WHERE prt1.run_type_id = paa1.run_type_id
AND prt1.run_method IN ('N','P')
AND paa1.payroll_action_id = p_payroll_action_id
AND paa1.assignment_id = paa2.assignment_id
AND paa1.source_action_id = paa2.assignment_action_id
AND paa2.assignment_action_id = p_assignment_action_id
AND p_effective_date BETWEEN
prt1.effective_start_date AND prt1.effective_end_date;
SELECT pre.locked_action_id pre_assignment_action_id,
pay.locked_action_id master_assignment_action_id,
assact.assignment_id assignment_id,
assact.payroll_action_id pay_payroll_action_id,
paa.effective_date effective_date,
ppaa.effective_date pre_effective_date,
paa.date_earned date_earned,
paa.time_period_id time_period_id
FROM pay_action_interlocks pre,
pay_action_interlocks pay,
pay_payroll_actions paa,
pay_payroll_actions ppaa,
pay_assignment_actions assact,
pay_assignment_actions passact
WHERE pre.locked_action_id = pay.locking_action_id
AND pre.locking_action_id = p_locking_action_id
AND pre.locked_action_id = passact.assignment_action_id
AND passact.payroll_action_id = ppaa.payroll_action_id
AND ppaa.action_type IN ('P','U')
AND pay.locked_action_id = assact.assignment_action_id
AND assact.payroll_action_id = paa.payroll_action_id
AND assact.source_action_id IS NULL
ORDER BY pay.locked_action_id;
SELECT paa.assignment_action_id child_assignment_action_id,
'S' run_type
FROM pay_assignment_actions paa,
pay_run_types_f prt
WHERE paa.source_action_id = p_master_assignment_action
AND paa.payroll_action_id = p_payroll_action_id
AND paa.assignment_id = p_assignment_id
AND paa.run_type_id = prt.run_type_id
AND prt.run_method = 'S'
AND p_effective_date BETWEEN
prt.effective_start_date AND prt.effective_end_date
UNION
SELECT paa.assignment_action_id child_assignment_action_id,
'NP' run_type
FROM pay_assignment_actions paa
WHERE paa.payroll_action_id = p_payroll_action_id
AND paa.assignment_id = p_assignment_id
AND paa.action_sequence =
pay_core_payslip_utils.get_max_nor_act_seq(p_payroll_action_id,
p_master_assignment_action,
p_effective_date);
SELECT paa.assignment_action_id np_assignment_action_id,
prt.run_method
FROM pay_assignment_actions paa,
pay_run_types_f prt
WHERE paa.source_action_id = p_assignment_action_id
AND paa.payroll_action_id = p_payroll_action_id
AND paa.assignment_id = p_assignment_id
AND paa.run_type_id = prt.run_type_id
AND prt.run_method IN ('N','P')
AND p_effective_date BETWEEN
prt.effective_start_date AND prt.effective_end_date
UNION
SELECT paa.assignment_action_id np_assignment_action_id,
'N'
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_action_id = p_assignment_action_id
AND ppa.payroll_action_id = p_payroll_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.run_type_id is null
AND paa.assignment_id = p_assignment_id;
SELECT paa.chunk_number,
paa.payroll_action_id
INTO l_chunk_number,
l_pactid
FROM pay_assignment_actions paa
WHERE paa.assignment_action_id = p_assactid;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;