[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select TPERIOD.start_date
,TPERIOD.end_date
,PACTION.payroll_action_id
from pay_payroll_actions PACTION
,pay_assignment_actions ASSACTION
,per_time_periods TPERIOD
,per_time_period_types TPTYPE
where ASSACTION.assignment_action_id = p_asg_action_id
and PACTION.payroll_action_id = ASSACTION.payroll_action_id
and TPERIOD.time_period_id = PACTION.time_period_id
and TPTYPE.period_type = TPERIOD.period_type;
select glb.global_value
from ff_globals_f glb
where glb.global_name = p_global_name
and p_process_date between glb.effective_start_date and glb.effective_end_date;
SELECT element_type_id
from pay_element_types_f
where element_name = p_element_name
and legislation_code = 'KR'
and business_group_id is null
and p_date_earned between effective_start_date and effective_end_date
group by element_type_id;
SELECT pee.assignment_id,
pee.element_entry_id,
peev.input_value_id,
pee.effective_start_date court_order_start_date,
pee.entry_information1 interest_from_date1,
pee.entry_information2 interest_to_date1,
pee.entry_information3 interest_base1,
pee.entry_information4 interest_rate1,
pee.entry_information5 interest_from_date2,
pee.entry_information6 interest_to_date2,
pee.entry_information7 interest_base2,
pee.entry_information8 interest_rate2,
pee.entry_information9 interest_from_date3,
pee.entry_information10 interest_to_date3,
pee.entry_information11 interest_base3,
pee.entry_information12 interest_rate3,
pee.entry_information13 interest_from_date4,
pee.entry_information14 interest_to_date4,
pee.entry_information15 interest_base4,
pee.entry_information16 interest_rate4,
pee.entry_information17 interest_from_date5,
pee.entry_information18 interest_to_date5,
pee.entry_information19 interest_base5,
pee.entry_information20 interest_rate5,
pee.entry_information21 previous_case_number,
pee.entry_information23 payout_date,
pee.entry_information24 court_order_origin,
name,
peev.screen_entry_value
from pay_element_entry_values_f peev,
pay_element_entries_f pee,
pay_element_links_f pel,
pay_input_values_f piv
where pel.element_type_id = p_element_type_id
and piv.element_type_id = p_element_type_id
and piv.element_type_id = pel.element_type_id
and p_date_earned between pel.effective_start_date and pel.effective_end_date
and p_date_earned between piv.effective_start_date and piv.effective_end_date
and peev.input_value_id = piv.input_value_id
and pee.element_link_id = pel.element_link_id
and pee.assignment_id = p_assignment_id
and nvl(pee.entry_type, 'E') = 'E'
and p_date_earned between pee.effective_start_date and pee.effective_end_date
and peev.element_entry_id = pee.element_entry_id
and peev.effective_start_date = pee.effective_start_date
and peev.effective_end_date = pee.effective_end_date
-- Bug 2786290
-- Bug 4866417 -- removed the not exists clause introduced for bug 2786290
-- Order by clause changes for bug 268885
order by 1,2,3 desc;
select min(effective_start_date)
from pay_element_entries_f pee
where pee.element_entry_id = p_element_entry_id;
SELECT 'Y'
from pay_run_results prr
,pay_run_result_values prrv
,pay_input_values_f piv
where prr.source_id = p_element_entry_id
and prr.entry_type IN ('I','E')
and prr.status = 'P'
and prrv.run_result_id = prr.run_result_id
and piv.input_value_id = prrv.input_value_id
and piv.element_type_id = p_element_type_id
and piv.name = 'Obligation Release'
and prrv.result_value = 'Y'
and piv.legislation_code = 'KR'
and p_date_earned between piv.effective_start_date and piv.effective_end_date;
SELECT fnd_date.canonical_to_date(prrv.result_value)
from pay_run_results prr
,pay_run_result_values prrv
,pay_element_types_f pet
,pay_input_values_f piv
where prr.source_id = p_element_entry_id
and prr.entry_type IN ('I','E')
and prr.status IN ('P')
and prrv.run_result_id = prr.run_result_id
and piv.input_value_id = prrv.input_value_id
and pet.element_name = 'WG Results'
and pet.legislation_code = 'KR'
and piv.element_type_id = pet.element_type_id
and piv.name = 'Payout Date'
and piv.legislation_code = 'KR'
and piv.business_group_id IS NULL
and p_date_earned between piv.effective_start_date and piv.effective_end_date
order by prr.assignment_action_id desc;
SELECT fnd_date.canonical_to_date(prrv.result_value)
from pay_payroll_actions ppa
,pay_assignment_actions paa
,pay_run_results prr
,pay_run_result_values prrv
,pay_run_result_values prrv1
,pay_input_values_f piv
,pay_input_values_f piv1
where paa.assignment_id = p_assignment_id
and prr.assignment_action_id = paa.assignment_action_id
and paa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type = 'B'
and ppa.effective_date <= p_date_earned
-- following condition is put for performance reasons
and paa.assignment_action_id <= p_assignment_action_id
and prr.status = 'P'
and prr.entry_type = 'B'
and prr.element_type_id = p_element_type_id
and prrv1.run_result_id = prr.run_result_id
and piv1.name = 'Attachment Seq No'
and piv1.legislation_code = 'KR'
and piv1.business_group_id IS NULL
and piv1.element_type_id = p_element_type_id
and piv1.input_value_id = prrv1.input_value_id
and prrv1.result_value = p_attachment_seq_no
and piv.name = 'Payout Date'
and piv.legislation_code = 'KR'
and piv.business_group_id IS NULL
and prrv.run_result_id = prr.run_result_id
and piv.input_value_id = prrv.input_value_id
and piv.element_type_id = p_element_type_id
and p_date_earned between piv.effective_start_date and piv.effective_end_date
order by ppa.effective_date desc;
SELECT pdb.defined_balance_id
from pay_balance_types pbt
,pay_balance_dimensions pbd
,pay_defined_balances pdb
where pbt.balance_name = p_balance_name
and pbt.legislation_code ='KR'
and pbd.database_item_suffix = p_dim_name
and pbd.legislation_code ='KR'
and pdb.balance_type_id = pbt.balance_type_id
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pdb.legislation_code ='KR';
SELECT pee.effective_start_date,pee.entry_information24
from pay_element_entry_values_f peev,
pay_element_entries_f pee,
pay_element_links_f pel,
pay_input_values_f piv
where pel.element_type_id = l_element_type_id
and piv.element_type_id = l_element_type_id
and piv.element_type_id = pel.element_type_id
and p_date_earned between pel.effective_start_date and pel.effective_end_date
and p_date_earned between piv.effective_start_date and piv.effective_end_date
and peev.input_value_id = piv.input_value_id
and pee.element_link_id = pel.element_link_id
and pee.assignment_id = p_assignment_id
and nvl(pee.entry_type, 'E') = 'E'
and p_date_earned between pee.effective_start_date and pee.effective_end_date
and peev.element_entry_id = pee.element_entry_id
and peev.effective_start_date = pee.effective_start_date
and peev.effective_end_date = pee.effective_end_date
and piv.name = 'Case Number'
and peev.screen_entry_value = p_prev_case_num
order by pee.effective_start_date;
g_court_orders.delete;
SELECT 'Y'
from pay_element_entry_values_f peev,
pay_element_entries_f pee,
pay_element_links_f pel,
pay_input_values_f piv,
pay_element_types_f pet,
fnd_sessions ses
where pet.element_name = 'Wage Garnishments'
and pet.legislation_code = 'KR'
and pet.business_group_id IS NULL
and ses.session_id = userenv('sessionid')
and pel.element_type_id = pet.element_type_id
and piv.element_type_id = pel.element_type_id
and piv.name = 'Attachment Seq No'
and pee.element_link_id = pel.element_link_id
and nvl(pee.entry_type, 'E') = 'E'
and pee.assignment_id = p_assignment_id
and peev.input_value_id = piv.input_value_id
and peev.element_entry_id = pee.element_entry_id
and peev.element_entry_id <> p_element_entry_id
and ses.effective_date between pel.effective_start_date and pel.effective_end_date
and ses.effective_date between piv.effective_start_date and piv.effective_end_date
and ses.effective_date between pet.effective_start_date and pet.effective_end_date
and pee.element_entry_id <> p_element_entry_id
and peev.effective_start_date = pee.effective_start_date
and peev.effective_end_date = pee.effective_end_date
and peev.screen_entry_value = p_attachment_seq_no;