[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select end_date from per_time_periods where
payroll_id = p_payroll_id
and p_date_earned between start_date and end_date;
select paaf1.effective_end_date,nvl(paaf1.payroll_id,-1)
from per_all_assignments_f paaf1,
per_assignment_status_types past1,
per_all_assignments_f paaf2,
per_assignment_status_types past2,
pay_all_payrolls_f papf,
per_time_periods ptp
where past1.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
and paaf1.assignment_status_type_id=past1.assignment_status_type_id
and paaf1.assignment_id=p_assignment_id
and past2.per_system_status ='TERM_ASSIGN'
and paaf2.assignment_id=p_assignment_id
and paaf2.assignment_status_type_id=past2.assignment_status_type_id
and paaf1.effective_end_date+1=paaf2.effective_start_date
and paaf2.effective_start_date >ptp.start_date --not >=
and paaf2.effective_start_date<=ptp.end_date
and papf.payroll_id= paaf2.payroll_id
and p_date_earned between papf.effective_start_date and papf.effective_end_date
and papf.payroll_id=ptp.payroll_id
and p_date_earned between ptp.start_date and ptp.end_date;
select kyflx.segment3,past.per_system_status,paaf.person_id
from hr_soft_coding_keyflex kyflx,
per_assignment_status_types past,
per_all_assignments_f paaf
where paaf.assignment_id=p_assignment_id
and r_date between paaf.effective_start_date and paaf.effective_end_date
and paaf.soft_coding_keyflex_id=kyflx.soft_coding_keyflex_id
and paaf.assignment_status_type_id=past.assignment_status_type_id ;
select EMP_SOCIAL_SECURITY_INFO,
old_age_contribution,
pension_contribution,
sickness_contribution,
work_injury_contribution,
labor_contribution,
unemployment_contribution,
health_contribution
from pay_pl_sii_details_f
where per_or_asg_id=r_per_or_asg_id
and contract_category=r_contract_category
and r_sii_date between effective_start_date and effective_end_date ;
cursor csr_level is select ci.value from
pay_user_column_instances_f ci,
pay_user_columns c,
pay_user_rows_f r,
pay_user_tables t
where r.user_table_id = t.user_table_id
and c.user_table_id = t.user_table_id
and ci.user_row_id = r.user_row_id
and ci.user_column_id = c.user_column_id
and t.legislation_code = 'PL'
and c.user_column_name = 'Level'
and t.user_table_name = 'PL_NORMAL_TAX'
and p_date_earned between ci.effective_start_date and ci.effective_end_date
and round(p_taxable_base,2) between r.row_low_range_or_name and r.row_high_range;
cursor csr_rate_of_tax(p_level number) is select ci.value
from
pay_user_column_instances_f ci,
pay_user_column_instances_f cilvl,
pay_user_rows_f r,
pay_user_rows_f rlvl,
pay_user_columns c,
pay_user_columns clvl,
pay_user_tables t
where c.user_table_id = t.user_table_id
and r.user_table_id = t.user_table_id
and ci.user_row_id = r.user_row_id
and ci.user_column_id = c.user_column_id
and clvl.user_table_id = t.user_table_id
and rlvl.user_table_id = t.user_table_id
and cilvl.user_row_id = rlvl.user_row_id
and cilvl.user_column_id = clvl.user_column_id
and t.legislation_code = 'PL'
and c.user_column_name = 'Standard'
and t.user_table_name = 'PL_NORMAL_TAX'
and r.row_low_range_or_name = rlvl.row_low_range_or_name
and p_date_earned between ci.effective_start_date and ci.effective_end_date
and p_date_earned between cilvl.effective_start_date and cilvl.effective_end_date
and cilvl.value = p_level;
select end_date
from per_time_periods
where payroll_id = p_payroll_id
and p_date_earned between start_date and end_date;
select paaf1.effective_end_date terminated_date,nvl(paaf1.payroll_id,-1) payroll_id
from per_all_assignments_f paaf1,
per_assignment_status_types past1,
per_all_assignments_f paaf2,
per_assignment_status_types past2,
pay_all_payrolls_f papf,
per_time_periods ptp
where past1.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
and paaf1.assignment_status_type_id=past1.assignment_status_type_id
and paaf1.assignment_id=p_assignment_id
and past2.per_system_status ='TERM_ASSIGN'
and paaf2.assignment_id=p_assignment_id
and paaf2.assignment_status_type_id=past2.assignment_status_type_id
and paaf1.effective_end_date+1=paaf2.effective_start_date
and paaf2.effective_start_date >ptp.start_date --not >=
and paaf2.effective_start_date<=ptp.end_date
and papf.payroll_id= paaf2.payroll_id
and p_date_earned between papf.effective_start_date and papf.effective_end_date
and papf.payroll_id=ptp.payroll_id
and p_date_earned between ptp.start_date and ptp.end_date;
select 'Y' payroll_run,paa.assignment_action_id ,ppa.date_earned
from pay_payroll_actions ppa,
pay_Assignment_actions paa,
per_time_periods ptp,
pay_all_payrolls_f papf,
pay_run_results prr,
pay_element_types_f petf
where paa.source_action_id is not null
and paa.assignment_id=r_assignment_id
and paa.action_status='C'
and ppa.action_type in ('R','Q')
and ppa.payroll_action_id=paa.payroll_action_id
and ppa.date_earned between ptp.start_date and ptp.end_date
and ptp.payroll_id = r_payroll_id
and papf.payroll_id=ptp.payroll_id
and r_date between ptp.start_date and ptp.end_date
and ppa.date_earned<=r_less_than_date
and prr.assignment_action_id=paa.assignment_action_id
and petf.legislation_code='PL'
and petf.element_name ='Tax'
and r_date between petf.effective_start_date and petf.effective_end_date
and prr.status='P'
and prr.element_type_id=petf.element_type_id;
select pivf.name,result_value,petf.element_name
from pay_run_results prr,
pay_run_result_values prrv,
pay_element_types_f petf,
pay_input_values_f pivf
where prr.assignment_action_id=r_assignment_action_id
and petf.legislation_code='PL'
and pivf.legislation_code='PL'
and petf.element_name =r_element
and petf.element_type_id=pivf.element_type_id
and p_date_earned between pivf.effective_start_date and pivf.effective_end_date
and p_date_earned between petf.effective_start_date and petf.effective_end_date
and prr.status='P'
and prr.element_type_id=petf.element_type_id
and prr.run_result_id=prrv.run_result_id
and pivf.input_value_id=prrv.input_value_id;
select kyflx.segment3,kyflx.segment4,past.per_system_status,paaf.person_id
from hr_soft_coding_keyflex kyflx,
per_assignment_status_types past,
per_all_assignments_f paaf
where paaf.assignment_id=p_assignment_id
and r_date between paaf.effective_start_date and paaf.effective_end_date
and paaf.soft_coding_keyflex_id=kyflx.soft_coding_keyflex_id
and paaf.assignment_status_type_id=past.assignment_status_type_id ;
select assignment_id
from per_all_assignments_f paaf,hr_soft_coding_keyflex kyflex
where paaf.person_id=r_person_id
and payroll_id= p_payroll_id
and kyflex.segment3='NORMAL'
and p_date_earned between paaf.effective_start_date and paaf.effective_end_date
and kyflex.soft_coding_keyflex_id=paaf.soft_coding_keyflex_id;
select 'Y'
from per_all_assignments_f paaf
,hr_soft_coding_keyflex kyflex
,per_assignment_status_types past
where paaf.person_id=r_person_id
and paaf.assignment_id<>p_assignment_id
and p_date_earned between paaf.effective_start_date and paaf.effective_end_date
and kyflex.segment3='NORMAL'
and kyflex.soft_coding_keyflex_id=paaf.soft_coding_keyflex_id;
select TAX_REDUCTION,TAX_CALC_WITH_SPOUSE_CHILD,INCOME_REDUCTION,
INCOME_REDUCTION_AMOUNT,RATE_OF_TAX,EMP_SOCIAL_SECURITY_INFO
from pay_pl_paye_details_f pppdf,
pay_pl_sii_details_f ppsdf
where pppdf.per_or_asg_id =r_per_or_asg_id
and r_date between pppdf.effective_start_date and pppdf.effective_end_date
and pppdf.contract_category=r_contract_category
and ppsdf.per_or_asg_id =r_per_or_asg_id
and ppsdf.contract_category=r_contract_category
and r_date between ppsdf.effective_start_date and ppsdf.effective_end_date;