[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT void_pa.effective_date,
fnd_number.canonical_to_number(chq_or_mag_aa.serial_number) cheque_number,
chq_or_mag_aa.assignment_action_id
FROM pay_assignment_actions chq_or_mag_aa,
pay_action_interlocks,
pay_assignment_actions void_aa,
pay_payroll_actions void_pa
WHERE chq_or_mag_aa.payroll_action_id = p_payroll_action_id
AND ((l_start_cheque is not null
AND l_end_cheque is not null
AND fnd_number.canonical_to_number(chq_or_mag_aa.serial_number)
BETWEEN l_start_cheque AND l_end_cheque)
OR (l_start_cheque is null and l_start_cheque is null))
AND ((l_start_assignment is not null
AND l_end_assignment is not null
AND chq_or_mag_aa.assignment_action_id
BETWEEN l_start_assignment AND l_end_assignment)
OR (l_start_assignment is null and l_end_assignment is null))
AND locked_action_id = chq_or_mag_aa.assignment_action_id
AND locking_action_id = void_aa.assignment_action_id
AND void_pa.payroll_action_id = void_aa.payroll_action_id
AND void_pa.action_type = 'D';
SELECT act.assignment_action_id,
act.assignment_id,
fnd_number.canonical_to_number(act.serial_number) cheque_number,
act.object_version_number,
act.action_status,
act.tax_unit_id,
-- nvl((select distinct 'Y'
-- from pay_ce_reconciled_payments prp
-- where prp.assignment_action_id = act.assignment_action_id)
-- ,'N') recon_exists,
nvl(pos.final_process_date, hr_general.end_of_time) final_process_date
FROM pay_assignment_actions act,
per_assignments_f asg,
per_periods_of_service pos
WHERE act.payroll_action_id = p_payroll_action_id
AND ((l_start_assignment is null and l_end_assignment is null)
OR
(l_start_assignment is not null
and l_end_assignment is not null
and act.assignment_action_id between
l_start_assignment and l_end_assignment))
AND ((l_start_cheque is not null
and l_end_cheque is not null
and fnd_number.canonical_to_number(act.serial_number)
BETWEEN l_start_cheque AND l_end_cheque)
or
(l_start_cheque is null and l_end_cheque is null))
AND act.assignment_id = asg.assignment_id
AND pos.period_of_service_id = asg.period_of_service_id
AND l_effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date ;
SELECT 'Y'
FROM pay_ce_reconciled_payments prp
WHERE prp.assignment_action_id = v_asg_act_id;
SELECT business_group_id,
consolidation_set_id,
payroll_id,
object_version_number,
action_type
INTO bgid,csid,pid,ovn,action_type
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
INSERT INTO pay_payroll_actions
( payroll_action_id,
action_type,
business_group_id,
consolidation_set_id,
payroll_id,
target_payroll_action_id,
action_population_status,
action_status,
effective_date,
comments,
start_cheque_number,
end_cheque_number,
request_id,
object_version_number ) VALUES
( pay_payroll_actions_s.nextval, -- payroll_action_id
'D', -- action_type
bgid, -- business_group_id
csid, -- consolidation_set_id
pid, -- payroll_id
p_payroll_action_id, -- target_payroll_action_id
'C', -- action_population_status
'C', -- action_status
l_effective_date, -- effective_date
p_reason, -- comments
l_start_cheque, -- start_cheque_number
l_end_cheque, -- end_cheque_number
fnd_profile.value('REQUEST_ID'),-- request_id
ovn ); -- object_version_number
INSERT INTO pay_assignment_actions
( assignment_action_id,
assignment_id,
payroll_action_id,
action_status,
serial_number,
object_version_number,
action_sequence,
tax_unit_id ) VALUES
( pay_assignment_actions_s.nextval, -- assignment_action_id
void_row.assignment_id, -- assignment_id
pay_payroll_actions_s.currval, -- payroll_action_id
'C', -- action_status
void_row.cheque_number, -- serial_number
void_row.object_version_number, -- object_version_number
pay_assignment_actions_s.nextval,
void_row.tax_unit_id ); -- action_sequence
INSERT INTO pay_action_interlocks
( locking_action_id, locked_action_id )
VALUES
( pay_assignment_actions_s.currval,void_row.assignment_action_id );