[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select u.creator_id
from ff_user_entities u,
ff_database_items d
where d.user_name = l_bal_name
and u.user_entity_id = d.user_entity_id
and u.legislation_code = 'KW'
and u.business_group_id is null
and u.creator_type = 'B';
SELECT distinct asg.person_id
FROM per_assignments_f asg
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_periods_of_service pos
,per_people_f pef
WHERE asg.assignment_id = paa.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND pos.period_of_service_id = asg.period_of_service_id
AND ppa.action_type in ('R','Q')
AND ppa.action_status = 'C'
AND paa.action_status = 'C'
AND trunc(ppa.date_earned,'MM') = TRUNC(l_effective_date, 'MM')
AND trunc(NVL(pos.actual_termination_date, to_date('31-12-4712','DD-MM-YYYY')),'MM') <> TRUNC(l_effective_date, 'MM')
AND trunc(pos.date_start, 'MM') = trunc(l_effective_date, 'MM')
AND trunc(l_effective_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
AND trunc(l_effective_date, 'MM') between trunc(pef.effective_start_date,'MM') and pef.effective_end_date
AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND hscl.segment1 = to_char(l_employer_id)
AND pef.person_id = asg.person_id
AND pef.nationality = l_nationality;
SELECT distinct asg.person_id
FROM per_assignments_f asg
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_people_f pef
WHERE asg.assignment_id = paa.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type in ('R','Q')
AND ppa.action_status = 'C'
AND paa.action_status = 'C'
AND trunc(ppa.date_earned,'MM') = TRUNC(l_effective_date, 'MM')
AND trunc(l_effective_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
AND trunc(l_effective_date, 'MM') between trunc(pef.effective_start_date,'MM') and pef.effective_end_date
AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND hscl.segment1 = to_char(l_employer_id)
AND pef.person_id = asg.person_id
AND pef.nationality = l_nationality;
SELECT distinct asg.person_id
FROM per_assignments_f asg
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_periods_of_service pos
,per_people_f pef
WHERE asg.assignment_id = paa.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND pos.period_of_service_id = asg.period_of_service_id
AND ppa.action_type in ('R','Q')
AND ppa.action_status = 'C'
AND paa.action_status = 'C'
AND trunc(ppa.date_earned,'MM') = TRUNC(l_effective_date, 'MM')
AND trunc(pos.date_start, 'MM') <> trunc(l_effective_date, 'MM')
AND trunc(NVL(pos.actual_termination_date, to_date('31-12-4712','DD-MM-YYYY')),'MM') <> TRUNC(l_effective_date, 'MM')
AND trunc(l_effective_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
AND trunc(l_effective_date, 'MM') between trunc(pef.effective_start_date,'MM') and pef.effective_end_date
AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND hscl.segment1 = to_char(l_employer_id)
AND pef.person_id = asg.person_id
AND pef.nationality = l_nationality;
select paa.assignment_action_id from per_assignments_f asg
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_periods_of_service pos
,per_people_f pef
where rownum < 2
and asg.assignment_id = paa.assignment_id
and asg.person_id = p_person_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R','Q')
and ppa.action_status = 'C'
and paa.action_status = 'C'
and trunc(ppa.date_earned,'MM') = trunc(p_date, 'MM')
and trunc(p_date,'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
and trunc(p_date,'MM') between trunc(pef.effective_start_date,'MM') and pef.effective_end_date
and hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
and hscl.segment1 = to_char(p_org_id)
and pef.person_id = asg.person_id
and pef.nationality = l_nationality
order by asg.person_id;
select paa.assignment_action_id from per_assignments_f asg
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_periods_of_service pos
,per_people_f pef
where rownum < 2
and asg.assignment_id = paa.assignment_id
and asg.person_id = p_person_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R','Q')
and ppa.action_status = 'C'
and paa.action_status = 'C'
and trunc(ppa.date_earned,'MM') = trunc(p_prev_month_date, 'MM')
and trunc(p_prev_month_date,'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
and trunc(p_prev_month_date,'MM') between trunc(pef.effective_start_date,'MM') and pef.effective_end_date
and hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
and hscl.segment1 = to_char(p_org_id)
and pef.person_id = asg.person_id
and pef.nationality = l_nationality
order by asg.person_id;
SELECT distinct asg.person_id
FROM per_assignments_f asg
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_periods_of_service pos
,per_people_f pef
WHERE asg.assignment_id = paa.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND pos.period_of_service_id = asg.period_of_service_id
AND ppa.action_type in ('R','Q')
AND ppa.action_status = 'C'
AND paa.action_status = 'C'
AND trunc(ppa.date_earned,'MM') = TRUNC(l_effective_date, 'MM')
AND trunc(NVL(pos.actual_termination_date, to_date('31-12-4712','DD-MM-YYYY')),'MM') = TRUNC(l_effective_date, 'MM')
AND trunc(l_effective_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
AND trunc(l_effective_date, 'MM') between trunc(pef.effective_start_date,'MM') and pef.effective_end_date
AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND hscl.segment1 = to_char(l_employer_id)
AND pef.person_id = asg.person_id
AND pef.nationality = l_nationality;
SELECT rrv.RESULT_VALUE val,pee.entry_information3 type,fnd_date.canonical_to_date(pee.entry_information5) start_d
,fnd_date.canonical_to_date(pee.entry_information6) end_d ,pet.element_type_id , pet.effective_start_date
FROM pay_element_types_f pet,
pay_element_entries_f pee,
pay_run_results prr,
pay_run_result_values rrv,
pay_input_values_f piv
WHERE rrv.RUN_RESULT_ID = prr.RUN_RESULT_ID
AND prr.assignment_action_id = l_assignment_action_id
AND prr.ELEMENT_ENTRY_ID = pee.ELEMENT_ENTRY_ID
AND piv.element_type_id = pet.element_type_id
AND piv.name = 'Pay Value'
AND rrv.input_value_id = piv.input_value_id
AND pee.assignment_id = l_assignment_id
AND TRUNC(l_effective_date,'MM') between trunc(pee.effective_start_date,'MM') and nvl(pee.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
AND TRUNC(l_effective_date,'MM') between trunc(piv.effective_start_date,'MM') and nvl(piv.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
AND pee.element_type_id = pet.element_type_id
AND pee.entry_information3 is not null
AND rrv.result_value is not null
AND TRUNC(l_effective_date,'MM') between trunc(pet.effective_start_date,'MM') and nvl(pet.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'));
select pev.screen_entry_value
from pay_element_types_f pet,
pay_element_entries_f pee,
pay_element_entry_values_f pev
where pet.element_type_id = l_type_id
AND trunc(pet.effective_start_date,'MM') = trunc(l_start_date,'MM')
AND pee.element_type_id = pet.element_type_id
AND pee.assignment_id = l_assignment_id
AND trunc(l_start_date,'MM') between pee.effective_start_date and pee.effective_end_date
AND pee.element_entry_id = pev.element_entry_id
AND trunc(l_start_date,'MM') between pev.effective_start_date and pev.effective_end_date;
SELECT i.value
FROM pay_user_column_instances_f i,
pay_user_rows_f r,
pay_user_columns c,
pay_user_tables t
WHERE UPPER(t.user_table_name) = UPPER('KW_DEDUCTION_MAPPING')
AND t.legislation_code = 'KW'
AND t.user_table_id = r.user_table_id
AND t.user_table_id = c.user_table_id
AND UPPER(c.user_column_name) = UPPER('DEDUCTION_TYPE')
AND c.legislation_code = 'KW'
AND r.row_low_range_or_name = p_row
AND r.user_row_id = i.user_row_id
AND c.user_column_id = i.user_column_id
AND l_date BETWEEN r.effective_start_date AND r.effective_end_date
AND l_date BETWEEN i.effective_start_date AND i.effective_end_date;
select paa.assignment_action_id
from per_assignments_f asg
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_periods_of_service pos
where rownum < 2
and asg.assignment_id = paa.assignment_id
and asg.person_id = l_person_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R','Q')
and ppa.action_status = 'C'
and paa.action_status = 'C'
and trunc(ppa.date_earned,'MM') = trunc(l_date, 'MM')
and trunc(l_date,'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
and hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
and hscl.segment1 = to_char(l_employer_id)
order by asg.person_id;
Select start_date
From per_people_f
Where person_id = l_person_id
And l_effective_date between effective_start_date and effective_end_date;
SELECT ORG_INFORMATION1
FROM HR_ORGANIZATION_INFORMATION
WHERE Organization_id = l_employer_id
AND org_information_context = 'KW_SI_DETAILS';