[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select legislative_parameters,
business_group_id,
nvl(to_date( pay_core_utils.get_parameter('REPORT_DATE',
l_legparam)
,'DD/MM/YYYYHH24:MI:SS'),
effective_date)
into l_legparam,
l_bus_grp,
l_report_date
from pay_payroll_actions
where payroll_action_id = pactid;
select assignment_set_id
into l_asg_set_id
from hr_assignment_sets
where assignment_set_name = p_asg_set_name
and payroll_id = p_payroll;
SELECT 'X'
FROM hr_assignment_sets
WHERE UPPER(assignment_set_name) = UPPER(p_asg_set);
select number_of_copies,
printer,
print_style,
save_output_flag
into l_copies_buffer,
l_printer_buffer,
l_style_buffer,
l_save_result
from fnd_concurrent_requests
where request_id = to_number(c_req_id);
update fnd_concurrent_requests
set output_file_type = 'XML'
where request_id = l_req_id;
SELECT hr_assignment_sets_s.nextval
FROM dual;
hr_assignment_sets_pkg.insert_row(
p_rowid => l_rowid
, p_assignment_set_id => l_asg_set_id
, p_business_group_id => p_business_group_id
, p_payroll_id => p_payroll_id
, p_assignment_set_name => p_asg_set_name
, p_formula_id => null);
SELECT astTL.user_status
, ppf.full_name
INTO l_asg_status
, l_person_name
FROM per_assignments_f paf
, per_assignment_status_types ast
, per_assignment_status_types_tl astTL
, per_people_f ppf
WHERE paf.assignment_id = p_assignment_id
AND paf.business_group_id = p_business_group_id
AND paf.person_id = ppf.person_id
AND ppf.business_group_id = p_business_group_id
AND paf.assignment_status_type_id = ast.assignment_status_type_id
AND (ast.business_group_id = p_business_group_id
OR (ast.business_group_id IS NULL
AND ast.legislation_code = p_legislation_code)
OR (ast.business_group_id IS NULL
AND ast.legislation_code IS NULL))
AND ast.assignment_status_type_id = astTL.assignment_status_type_id
AND astTL.language = userenv('LANG')
AND p_report_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND p_report_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date;
SELECT petTL.element_name element
FROM pay_element_types_f_tl petTL
, pay_element_types_f pet
, pay_element_links_f pel
, pay_element_entries_f pef
WHERE pef.element_entry_id = p_element_entry_id
AND pef.element_link_id = pel.element_link_id
AND pel.business_group_id = p_business_group_id
AND pel.element_type_id = pet.element_type_id
AND pet.element_type_id = petTL.element_type_id
AND petTL.language = userenv('LANG')
AND (pet.business_group_id = p_business_group_id
OR (pet.business_group_id IS NULL
AND pet.legislation_code = p_legislation_code)
OR (pet.business_group_id IS NULL
AND pet.legislation_code IS NULL))
AND p_report_date BETWEEN pef.effective_start_date
AND pef.effective_end_date
AND p_report_date BETWEEN pel.effective_start_date
AND pel.effective_end_date
AND p_report_date BETWEEN pet.effective_start_date
AND pet.effective_end_date;
SELECT petTL.element_name
FROM pay_element_types_f_tl petTL
, pay_element_types_f pet
, pay_run_results prr
WHERE prr.source_id = p_element_entry_id
AND prr.source_type = 'E'
AND prr.element_type_id = pet.element_type_id
AND pet.element_type_id = petTL.element_type_id
AND petTL.language = userenv('LANG')
AND (pet.business_group_id = p_business_group_id
OR (pet.business_group_id IS NULL
AND pet.legislation_code = p_legislation_code)
OR (pet.business_group_id IS NULL
AND pet.legislation_code IS NULL))
AND p_report_date BETWEEN pet.effective_start_date
AND pet.effective_end_date;
SELECT /*+ ORDERED INDEX(PDE PAY_DATETRACKED_EVENTS_UK1)
INDEX(PPA PAY_PAYROLL_ACTIONS_PK)
USE_NL(PDE)*/
DISTINCT
prr.source_id entry,
pde.datetracked_event_id
FROM pay_assignment_actions paa
, pay_payroll_actions ppa
, pay_run_results prr
, pay_datetracked_events pde
WHERE prr.source_type = 'E'
AND prr.assignment_action_id = paa.assignment_action_id
AND paa.assignment_id = p_asg
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.business_group_id = p_business_group_id
AND ppa.action_type in ('R', 'Q', 'B', 'V')
AND pde.event_group_id = p_event_group_id
AND ppa.date_earned IS NOT NULL
AND (ppa.date_earned BETWEEN p_min AND p_max
OR ppa.effective_date BETWEEN p_min AND p_max)
/* Make sure that the Entry is not a Retropay Entry */
AND NOT EXISTS (select ''
from pay_element_entries_f pee
where pee.element_entry_id = prr.source_id
and nvl(pee.creator_type, 'F') in ('EE', 'RR', 'PR', 'NR')
)
UNION
SELECT /*+ ORDERED INDEX(PDE PAY_DATETRACKED_EVENTS_UK1)
INDEX(PET PAY_ELEMENT_TYPES_F_PK)
INDEX(PAF PER_ASSIGNMENTS_F_PK)
USE_NL(PDE PAF)*/
DISTINCT
pee.element_entry_id entry,
pde.datetracked_event_id
FROM pay_element_entries_f pee
, pay_datetracked_events pde
WHERE pee.assignment_id = p_asg
AND pde.event_group_id = p_event_group_id
/* Make sure that the Entry is not a Retropay Entry */
AND nvl(pee.creator_type, 'F') not in ('EE', 'RR', 'PR', 'NR')
AND pee.effective_start_date <= p_max
AND pee.effective_end_date >= p_min
AND exists (select /*+ ORDERED INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
INDEX(ppa PAY_PAYROLL_ACTIONS_PK)
USE_NL(paa ppa) */
''
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
where ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = pee.assignment_id
and paa.action_status not in ('E', 'M', 'U')
and ppa.action_type in ('R', 'Q', 'B', 'V')
and (ppa.date_earned BETWEEN p_min AND p_max
OR ppa.effective_date BETWEEN p_min AND p_max)
and ppa.payroll_id = ptp.payroll_id
and ppa.date_earned between ptp.start_date
and ptp.end_date
and pee.effective_start_date <= ptp.end_date
and pee.effective_end_date >= ptp.start_date
)
ORDER BY 2;
SELECT /*+ ORDERED INDEX(PDE PAY_DATETRACKED_EVENTS_UK1)
INDEX(PPA PAY_PAYROLL_ACTIONS_PK)
USE_NL(PDE)*/
DISTINCT
prr.source_id entry,
prr.element_type_id type,
pde.datetracked_event_id,
p_event_group_id
FROM pay_assignment_actions paa
, pay_payroll_actions ppa
, pay_run_results prr
, pay_datetracked_events pde
WHERE prr.source_type = 'E'
AND prr.assignment_action_id = paa.assignment_action_id
AND prr.element_type_id = prr.element_type_id
AND paa.assignment_id = cp_asg
AND paa.payroll_action_id = ppa.payroll_action_id
-- Only bring back a row if an event group is
-- supplied to the process or a recalc one
-- is on the element
AND pde.event_group_id = p_event_group_id
AND ppa.business_group_id = p_business_group_id
AND ppa.action_type in ('R', 'Q', 'B', 'V')
AND ppa.date_earned IS NOT NULL
/* Make sure that the Entry is not a Retropay Entry */
AND NOT EXISTS (select ''
from pay_element_entries_f pee
where pee.element_entry_id = prr.source_id
and nvl(pee.creator_type, 'F') in ('EE', 'RR', 'PR', 'NR')
)
AND (ppa.date_earned BETWEEN cp_min_ed AND cp_max_ed
OR ppa.effective_date BETWEEN cp_min_ed AND cp_max_ed)
UNION
SELECT /*+ ORDERED INDEX(PDE PAY_DATETRACKED_EVENTS_UK1)
USE_NL(PDE)*/
DISTINCT
pee.element_entry_id entry,
pee.element_type_id type,
pde.datetracked_event_id,
p_event_group_id
FROM pay_element_entries_f pee
, pay_datetracked_events pde
WHERE pee.assignment_id = cp_asg
-- Only bring back a row if an event group is
-- supplied to the process or a recalc one
-- is on the element
AND pde.event_group_id = p_event_group_id
/* Make sure that the Entry is not a Retropay Entry */
AND nvl(pee.creator_type, 'F') not in ('EE', 'RR', 'PR', 'NR')
AND pee.effective_start_date <= cp_max_ed
AND pee.effective_end_date >= cp_min_ed
AND exists (select ''
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
where ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = pee.assignment_id
and paa.action_status not in ('E', 'M', 'U')
and ppa.action_type in ('R', 'Q', 'B', 'V')
and (ppa.date_earned BETWEEN cp_min_ed AND cp_max_ed
OR ppa.effective_date BETWEEN cp_min_ed AND cp_max_ed)
and ppa.payroll_id = ptp.payroll_id
and ppa.date_earned between ptp.start_date
and ptp.end_date
and pee.effective_start_date <= ptp.end_date
and pee.effective_end_date >= ptp.start_date
)
ORDER BY 1, 2;
SELECT DISTINCT
prr.source_id entry,
pet.element_type_id type,
pde.datetracked_event_id,
nvl(pet.recalc_event_group_id, -1) event_group_id
FROM pay_assignment_actions paa
, pay_payroll_actions ppa
, pay_run_results prr
, pay_element_types_f pet
, pay_datetracked_events pde
WHERE prr.source_type = 'E'
AND prr.assignment_action_id = paa.assignment_action_id
AND prr.element_type_id = pet.element_type_id
AND paa.assignment_id = cp_asg
AND paa.payroll_action_id = ppa.payroll_action_id
-- Only bring back a row if an event group is
-- supplied to the process or a recalc one
-- is on the element
AND pde.event_group_id = nvl(pet.recalc_event_group_id, -1)
AND ppa.business_group_id = p_business_group_id
AND ppa.action_type in ('R', 'Q', 'B', 'V')
AND ppa.date_earned IS NOT NULL
/* Make sure that the Entry is not a Retropay Entry */
AND NOT EXISTS (select ''
from pay_element_entries_f pee
where pee.element_entry_id = prr.source_id
and nvl(pee.creator_type, 'F') in ('EE', 'RR', 'PR', 'NR')
)
AND (ppa.date_earned BETWEEN cp_min_ed AND cp_max_ed
OR ppa.effective_date BETWEEN cp_min_ed AND cp_max_ed)
UNION
SELECT /*+ ORDERED INDEX(PDE PAY_DATETRACKED_EVENTS_UK1)
INDEX(PET PAY_ELEMENT_TYPES_F_PK)
USE_NL(PDE PET)*/
DISTINCT
pee.element_entry_id entry,
pet.element_type_id type,
pde.datetracked_event_id,
nvl(pet.recalc_event_group_id, -1) event_group_id
FROM pay_element_entries_f pee
, pay_element_types_f pet
, pay_datetracked_events pde
WHERE pee.assignment_id = cp_asg
AND pee.element_type_id = pet.element_type_id
-- Only bring back a row if an event group is
-- supplied to the process or a recalc one
-- is on the element
AND pde.event_group_id = nvl(pet.recalc_event_group_id, -1)
/* Make sure that the Entry is not a Retropay Entry */
AND nvl(pee.creator_type, 'F') not in ('EE', 'RR', 'PR', 'NR')
AND pee.effective_start_date <= cp_max_ed
AND pee.effective_end_date >= cp_min_ed
AND exists (select ''
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
where ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = pee.assignment_id
and paa.action_status not in ('E', 'M', 'U')
and ppa.action_type in ('R', 'Q', 'B', 'V')
and (ppa.date_earned BETWEEN cp_min_ed AND cp_max_ed
OR ppa.effective_date BETWEEN cp_min_ed AND cp_max_ed)
and ppa.payroll_id = ptp.payroll_id
and ppa.date_earned between ptp.start_date
and ptp.end_date
and pee.effective_start_date <= ptp.end_date
and pee.effective_end_date >= ptp.start_date
)
ORDER BY 1, 2;
SELECT 'X'
FROM hr_assignment_set_amendments
WHERE assignment_id = p_assignment_id
AND assignment_set_id = p_asg_set_id
AND include_or_exclude = 'I';
hr_assignment_set_amds_pkg.insert_row(
p_rowid => l_rowid
, p_assignment_id => p_assignment_id
, p_assignment_set_id => p_asg_set_id
, p_include_or_exclude => 'I');
Procedure retro_table_insert(
p_assignment_id IN NUMBER
, p_element_entry_id IN NUMBER
, p_date_processed IN DATE
, p_date_earned IN DATE
, p_change_type IN VARCHAR2
, p_asg_set_id IN NUMBER) is
--
Begin
--
INSERT INTO pay_retro_notif_reports
( report_id
, payroll_id
, report_date
, assignment_id
, element_entry_id
, event_group_id
, date_processed
, date_earned
, change_type
, assignment_set_id
, business_group_id
)
VALUES
( p_payroll_act_id
, p_payroll_id
, p_report_date
, p_assignment_id
, p_element_entry_id
, p_event_group
, p_date_processed
, p_date_earned
, p_change_type
, p_asg_set_id
, p_business_group_id
);
End retro_table_insert;
l_detailed_output.delete;
select /*+ INDEX(ppe PAY_PROCESS_EVENTS_N3) use_nl(ppe peu pdt)
ORDERED */
min(decode(peu.event_type,
'U', decode(peu.column_name,
pdt.end_date_name, ppe.effective_date +1,
ppe.effective_date
),
ppe.effective_date)
)
into l_min_eff_date
from pay_process_events ppe,
pay_event_updates peu,
pay_dated_tables pdt
where ppe.assignment_id = p_assignment_id
and ppe.creation_date between p_min_creation_date
and p_time_processing_started
and peu.event_update_id = ppe.event_update_id
and peu.dated_table_id = pdt.dated_table_id;
select /*+ INDEX(ppe PAY_PROCESS_EVENTS_N3) use_nl(ppe peu pdt)
ORDERED */
min(decode(peu.event_type,
'U', decode(peu.column_name,
pdt.end_date_name, ppe.effective_date +1,
ppe.effective_date
),
ppe.effective_date)
)
into l_min_grp_eff_date
from pay_process_events ppe,
pay_event_updates peu,
pay_dated_tables pdt
where ppe.assignment_id is null
and ppe.creation_date between p_min_creation_date
and p_time_processing_started
and peu.event_update_id = ppe.event_update_id
and peu.dated_table_id = pdt.dated_table_id;
select min_date,
payroll_id
into l_min_ear_date_ptp,
l_payroll_id_ear_date
from (
select min(date_earned) min_date,
ppa.payroll_id payroll_id
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_id = p_assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('Q', 'R', 'B', 'V')
group by payroll_id
order by min_date asc) a
where rownum=1;
select start_date
into l_min_run_ear_date
from per_time_periods
where l_min_ear_date_ptp between start_date and end_date
and payroll_id = l_payroll_id_ear_date;
select min_date,
payroll_id
into l_min_eff_date_ptp,
l_payroll_id_eff_date
from (
select min(effective_date) min_date,
ppa.payroll_id payroll_id
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_id = p_assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('Q', 'R', 'B', 'V')
group by payroll_id
order by min_date asc) a
where rownum=1;
select start_date
into l_min_run_eff_date
from per_time_periods
where l_min_eff_date_ptp between start_date and end_date
and payroll_id = l_payroll_id_eff_date;
select max(ppa.date_earned),
max(ppa.effective_date)
into l_max_ppa_de_date,
l_max_ppa_eff_date
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_id = p_assignment_id
and paa.action_status not in ('U', 'M', 'E')
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('R', 'Q');
retro_table_insert(
p_assignment_id => p_assignment_id,
p_element_entry_id => l_detailed_output(cnt).element_entry_id,
p_date_processed => l_detailed_output(cnt).effective_date,
p_date_earned => NULL,
p_change_type => l_detailed_output(cnt).update_type,
p_asg_set_id => p_asg_set_id);
hr_utility.trace('>DP >update type = '||l_detailed_output(cnt).update_type);
hr_utility.trace('>DP >update type = '||l_detailed_output(cnt).update_type);
l_detailed_output.delete;
retro_table_insert(
p_assignment_id => p_assignment_id,
p_element_entry_id => l_detailed_output(cnt).element_entry_id,
p_date_processed => null,
p_date_earned => l_detailed_output(cnt).effective_date,
p_change_type => l_detailed_output(cnt).update_type,
p_asg_set_id => p_asg_set_id);
select min(ppa.effective_date)
into l_reprocess_date
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = p_assignment_id
and ppa.date_earned >=
l_detailed_output(cnt).effective_date
and ppa.action_type in ('R','Q');
hr_utility.trace('>DE >update type = '||l_detailed_output(cnt).update_type);
hr_utility.trace('>DE >update type = '||l_detailed_output(cnt).update_type);
update pay_retro_assignments
set reprocess_date = (select min(reprocess_date)
from pay_retro_entries
where retro_assignment_id = l_ret_asg_id),
start_date = p_min_creation_date
where retro_assignment_id = l_ret_asg_id;
l_ele_type_id.delete;
l_ret_comp_id.delete;
select start_date
into l_start_date
from pay_retro_assignments
where assignment_id = p_assignment_id
and retro_assignment_action_id is null
and superseding_retro_asg_id is null;
select min(creation_date)
into l_min_creation_date
from pay_process_events
where assignment_id = p_assignment_id
and nvl(retroactive_status, 'P') <> 'C';
select assignment_id
into l_asg_id
from pay_assignment_actions
where assignment_action_id = p_assactid;
SELECT distinct assignment_id asg
FROM pay_process_events ppe
WHERE business_group_id = p_bg_id
and assignment_id = nvl(p_asg_id, assignment_id)
and creation_date between p_start_date
and p_end_date
and assignment_id is not null
and rownum < p_rownum;
sqlstr := 'select distinct asg.person_id
from
per_assignments_f asg,
pay_payroll_actions pa1
where pa1.payroll_action_id = :payroll_action_id
and asg.payroll_id =
pay_core_utils.get_parameter(''PAYROLL_ID'',
pa1.legislative_parameters)
and pa1.effective_date between asg.effective_start_date
and asg.effective_end_date
order by asg.person_id';
select legislative_parameters,
business_group_id
into l_legparam,
l_bus_grp
from pay_payroll_actions
where payroll_action_id = pactid;
select /*+ ordered
INDEX(paf PER_ASSIGNMENTS_N12)
USE_NL(pos paf) */
paf.assignment_id
from
per_periods_of_service pos,
per_assignments_f paf,
pay_payroll_actions ppa
where ppa.payroll_action_id = pactid
and paf.payroll_id =
pay_core_utils.get_parameter('PAYROLL_ID',
ppa.legislative_parameters)
and pos.period_of_service_id = paf.period_of_service_id
and pos.person_id = paf.person_id
and pos.person_id between stperson and endperson
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
order by paf.assignment_id
for update of paf.assignment_id, pos.period_of_service_id;
SELECT report_type
FROM pay_payroll_actions
WHERE payroll_action_id = pactid;
select /*+ ordered
INDEX(paf PER_ASSIGNMENTS_N12)
USE_NL(pos paf) */
paf.assignment_id
FROM pay_population_ranges ppr,
per_periods_of_service pos,
per_assignments_f paf,
pay_payroll_actions ppa
where ppa.payroll_action_id = pactid
and paf.payroll_id = pay_core_utils.get_parameter('PAYROLL_ID', ppa.legislative_parameters)
and pos.period_of_service_id = paf.period_of_service_id
and pos.person_id = paf.person_id
AND ppa.payroll_action_id = ppr.payroll_action_id
AND ppr.chunk_number = chunk
and pos.person_id = ppr.person_id
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
order by paf.assignment_id
for update of paf.assignment_id, pos.period_of_service_id;
SELECT pay_assignment_actions_s.nextval
INTO lockingactid
FROM dual;
SELECT pay_assignment_actions_s.nextval
INTO lockingactid
FROM dual;
SELECT 'X'
FROM hr_assignment_set_amendments
WHERE assignment_set_id = p_asg_set_id;
DELETE FROM hr_assignment_sets
WHERE assignment_set_id = p_asg_set_id;
select pay_core_utils.get_parameter('REMOVE_ACT',
pa1.legislative_parameters),
pay_core_utils.get_parameter('GEN_REPORT',
pa1.legislative_parameters)
into remove_act,
l_generate_report
from pay_payroll_actions pa1
where pa1.payroll_action_id = pactid;