[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select p.rowid
from per_all_people_f p
where p.person_id = p_person_id
and p_effective_date between p.effective_start_date and
p.effective_end_date;
select s.person_id
from per_periods_of_service s
where s.person_id = p_person_id
and p_effective_date between s.date_start and
nvl(s.actual_termination_date,hr_general.end_of_time);
select /*+ ORDERED USE_NL(RUN_VALUE, RUN_RESULT, FEED, BALANCE) */
nvl (sum (nvl (run_value.result_value, 0) * feed.scale),0) EARNINGS
from pay_assignment_actions ASG_ACTION,
pay_payroll_actions PAY_ACTION,
per_time_periods PERIOD,
pay_balance_types BALANCE,
pay_balance_feeds_f FEED,
pay_run_results RUN_RESULT,
pay_run_result_values RUN_VALUE
--
-- where the tables join via primary/foreign keys
where pay_action.payroll_action_id = asg_action.payroll_action_id
and run_result.assignment_action_id=asg_action.assignment_action_id
and run_result.run_result_id = run_value.run_result_id
and run_value.input_value_id = feed.input_value_id
and feed.balance_type_id = balance.balance_type_id
and period.time_period_id = pay_action.time_period_id
and period.regular_payment_date between feed.effective_start_date and feed.effective_end_date
--
-- and the earnings are for the specified assignment
and asg_action.assignment_id = L_ASSIGNMENT_ID
--
-- and the run result has been processed
and run_result.status in ('P','PA') --like 'P%'
--
and balance.balance_name = p_balance_name
and period.end_date between L_START_OF_RELEVANT_PERIOD and L_END_OF_RELEVANT_PERIOD;
select distinct
asg.assignment_id
from per_all_assignments_f ASG
where asg.period_of_service_id = L_PERIOD_OF_SERVICE_ID
and asg.payroll_id is not null
--6791913 begin - To treat adoption in the same way as Maternity
--and (( p_absence_category = 'M'
and (( p_absence_category in ('M','GB_ADO')
--6791913 end
and effective_end_date >= (p_effective_date - 68))
or -- p_absence = 'S'
effective_end_date >= (p_effective_date - 62)
);
select count(*) from per_periods_of_service
where person_id = p_person_id;
select nvl(max(actual_termination_date),to_date('01/01/01','DD/MM/YY'))
from per_periods_of_service
where person_id = p_person_id
and actual_termination_date is not null;
select max (period.end_date)
from per_time_periods PERIOD
where period.payroll_id = L_PAYROLL_ID
and period.regular_payment_date <= P_EFFECTIVE_DATE +6;
select max(ptp.end_date)
from per_time_periods ptp
where ptp.payroll_id in
( select papf.payroll_id
from pay_all_payrolls_f papf,
per_all_assignments_f paf,
per_all_people_f ppf,
per_time_periods ptp
where ppf.person_id = paf.person_id
and papf.payroll_id = paf.payroll_id
and paf.payroll_id = ptp.payroll_id
and ptp.regular_payment_date <=
P_EFFECTIVE_DATE + 6
and paf.assignment_id = l_assignment_id)
and ptp.regular_payment_date <= P_EFFECTIVE_DATE + 6 ;
select max (period.end_date)
from per_time_periods PERIOD
where period.payroll_id = L_PAYROLL_ID
and period.regular_payment_date <= P_EFFECTIVE_DATE;
select max(ptp.end_date)
from per_time_periods ptp
where ptp.payroll_id in
( select papf.payroll_id
from pay_all_payrolls_f papf,
per_all_assignments_f paf,
per_all_people_f ppf,
per_time_periods ptp
where ppf.person_id = paf.person_id
and papf.payroll_id = paf.payroll_id
and paf.payroll_id = ptp.payroll_id
and ptp.regular_payment_date <=
P_EFFECTIVE_DATE
and paf.assignment_id = l_assignment_id)
and ptp.regular_payment_date <= P_EFFECTIVE_DATE ;
select max (period.end_date) +1
from per_time_periods PERIOD
where period.payroll_id = L_PAYROLL_ID
and period.end_date <= L_END_OF_RELEVANT_PERIOD - 56;
select ppf.payroll_id
from pay_all_payrolls_f ppf
, per_all_assignments_f paf
where ppf.payroll_id = paf.payroll_id
and paf.assignment_id = l_assignment_id
and l_start_of_relevant_period between
paf.effective_start_date and paf.effective_end_date;
select payroll_id
from per_all_assignments_f paf
where paf.assignment_id = L_ASSIGNMENT_ID
and P_EFFECTIVE_DATE between
paf.effective_start_date and paf.effective_end_date;
select 1
from per_all_assignments_f
where assignment_id = L_ASSIGNMENT_ID
and l_start_of_relevant_period between effective_start_date and effective_end_date;
select min(effective_start_date)
from per_all_assignments_f
where assignment_id = L_ASSIGNMENT_ID;
select past.per_system_status
from per_all_assignments_f paaf, per_assignment_status_types past
where paaf.assignment_id = L_ASSIGNMENT_ID
and paaf.effective_start_date <= P_EFFECTIVE_DATE
and paaf.assignment_status_type_id = past.assignment_status_type_id
order by paaf.effective_start_date desc;
select period_type.number_per_fiscal_year fiscal_year
from pay_all_payrolls_f PAYROLL,
per_all_assignments_f ASSIGNMENT,
per_time_period_types PERIOD_TYPE
where assignment.assignment_id = l_assignment_id
and assignment.effective_start_date <= l_end_of_relevant_period
and assignment.effective_end_date >= l_start_of_relevant_period
and payroll.payroll_id = assignment.payroll_id
and payroll.period_type = period_type.period_type
and payroll.effective_start_date <= l_end_of_relevant_period
and payroll.effective_end_date >= l_start_of_relevant_period;
select service.period_of_service_id,
service.date_start
from per_periods_of_service SERVICE
where person_id = p_person_id
and p_effective_date between service.date_start
and nvl (service.actual_termination_date,
hr_general.end_of_time);
select 1
from per_all_people_f
where per_information2 = 'Y' -- Director_flag
and person_id = p_person_id
and p_effective_date between effective_start_date
and effective_end_date;
select peev.screen_entry_value
from per_all_assignments_f paf
, pay_element_entry_values_f peev
, pay_element_entries_f pee
, pay_element_types_f pet
, pay_element_links_f pel
where pee.element_entry_id = peev.element_entry_id
and pee.assignment_id = paf.assignment_id
and pet.element_type_id = pel.element_type_id
and pel.element_link_id = pee.element_link_id
and paf.assignment_id = l_assignment_id
and pet.element_name = 'Average Earnings Period'
and peev.effective_start_date between paf.effective_start_date
and paf.effective_end_date
and peev.effective_start_date between pee.effective_start_date
and pee.effective_end_date
and peev.effective_start_date between pet.effective_start_date
and pet.effective_end_date
and peev.effective_start_date between pel.effective_start_date
and pel.effective_end_date
and peev.effective_start_date
between greatest(l_start_of_relevant_period, l_hire_date)
and l_end_of_relevant_period
and peev.effective_end_date
between greatest(l_start_of_relevant_period, l_hire_date)
and l_end_of_relevant_period;
select period_type.number_per_fiscal_year fiscal_year
from pay_all_payrolls_f PAYROLL,
per_all_assignments_f ASSIGNMENT,
per_time_period_types PERIOD_TYPE
where assignment.assignment_id = l_assignment_id
and payroll.payroll_id = assignment.payroll_id
and payroll.period_type = period_type.period_type
and payroll.effective_start_date <= l_end_of_relevant_period
and payroll.effective_end_date >= l_start_of_relevant_period
and period_type.number_per_fiscal_year <> 12;
Procedure insert_validate(p_rec in out nocopy ssp_ern_shd.g_rec_type) is
--
l_proc varchar2(72) := g_package||'insert_validate';
End insert_validate;
Procedure update_validate(p_rec in out nocopy ssp_ern_shd.g_rec_type) is
--
l_proc varchar2(72) := g_package||'update_validate';
End update_validate;
Procedure delete_validate(p_rec in ssp_ern_shd.g_rec_type) is
--
l_proc varchar2(72) := g_package||'delete_validate';
End delete_validate;