[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ipv.input_value_id INTO l_input_value_id
FROM PAY_INPUT_VALUES_F ipv,
PAY_ELEMENT_TYPES_F ele
WHERE ele.element_name = 'Tax Credit'
AND ipv.name = p_name
AND ele.element_type_id = ipv.element_type_id
AND p_effective_date between ele.effective_start_date
and ele.effective_end_date
AND p_effective_date between ipv.effective_start_date
and ipv.effective_end_date;
select effective_date
from fnd_sessions
where session_id = userenv('sessionid');
select element_type_id
from pay_element_types
where element_name = 'Tax Credit';
Select 'Y'
FROM pay_element_entry_values_f eev1,
pay_element_entry_values_f eev2,
pay_element_entry_values_f eev3,
pay_element_entries_f ent,
pay_input_values_f ipv1,
pay_input_values_f ipv2,
pay_input_values_f ipv3,
pay_element_links_f lnk,
pay_element_types_f ele
where ele.element_name = p_ele
and ele.element_type_id = lnk.element_type_id
and ent.element_link_id = lnk.element_link_id
and ent.assignment_id = p_asg
and ipv1.element_type_id = ele.element_type_id
and ipv2.element_type_id = ele.element_type_id
and ipv3.element_type_id = ele.element_type_id
and ipv1.name = 'Start Date'
and ipv2.name = 'End Date'
and ipv3.name = 'Stop Date'
and ipv1.input_value_id = eev1.input_value_id
and ipv2.input_value_id = eev2.input_value_id
and ipv3.input_value_id = eev3.input_value_id
and eev1.element_entry_id = ent.element_entry_id
and eev2.element_entry_id = ent.element_entry_id
and eev3.element_entry_id = ent.element_entry_id
and (ent.element_entry_id <> p_element_entry_id
or p_element_entry_id is null)
and eev1.screen_entry_value <= fnd_date.date_to_canonical(p_start_date)
and (eev3.screen_entry_value >= fnd_date.date_to_canonical(p_start_date)
OR (eev3.screen_entry_value is NULL
and (eev2.screen_entry_value >= fnd_date.date_to_canonical(p_start_date)
OR eev2.screen_entry_value is NULL )))
and p_start_date between
eev1.effective_start_date and eev1.effective_end_date
and p_start_date between
eev2.effective_start_date and eev2.effective_end_date
and p_start_date between
eev3.effective_start_date and eev3.effective_end_date
and p_start_date between
ent.effective_start_date and ent.effective_end_date
and p_start_date between
lnk.effective_start_date and lnk.effective_end_date
and p_start_date between
ele.effective_start_date and ele.effective_end_date
and p_start_date between
ipv1.effective_start_date and ipv1.effective_end_date
and p_start_date between
ipv2.effective_start_date and ipv2.effective_end_date
and p_start_date between
ipv3.effective_start_date and ipv3.effective_end_date;
Select 'Y'
FROM pay_element_entry_values_f eev1,
pay_element_entry_values_f eev2,
pay_element_entries_f ent,
pay_input_values_f ipv1,
pay_input_values_f ipv2,
pay_element_links_f lnk,
pay_element_types_f ele
where ele.element_name = p_ele
and ele.element_type_id = lnk.element_type_id
and ent.element_link_id = lnk.element_link_id
and ent.assignment_id = p_asg
and ipv1.element_type_id = ele.element_type_id
and ipv2.element_type_id = ele.element_type_id
and ipv1.name = 'Start Date'
and ipv2.name = 'End Date'
and ipv1.input_value_id = eev1.input_value_id
and ipv2.input_value_id = eev2.input_value_id
and eev1.element_entry_id = ent.element_entry_id
and eev2.element_entry_id = ent.element_entry_id
and (ent.element_entry_id <> p_element_entry_id
or p_element_entry_id is null)
and eev1.screen_entry_value <= fnd_date.date_to_canonical(p_start_date)
and (eev2.screen_entry_value >= fnd_date.date_to_canonical(p_start_date)
OR (eev2.screen_entry_value is NULL));
Select 'Y'
FROM pay_element_entry_values_f eev1,
pay_element_entry_values_f eev2,
pay_element_entry_values_f eev3,
pay_element_entries_f ent,
pay_input_values_f ipv1,
pay_input_values_f ipv2,
pay_input_values_f ipv3,
pay_element_links_f lnk,
pay_element_types_f ele
where ele.element_name = p_ele
and ele.element_type_id = lnk.element_type_id
and ent.element_link_id = lnk.element_link_id
and ent.assignment_id = p_asg
and ipv1.element_type_id = ele.element_type_id
and ipv2.element_type_id = ele.element_type_id
and ipv3.element_type_id = ele.element_type_id
and ipv1.name = 'Start Date'
and ipv2.name = 'End Date'
and ipv3.name = 'Stop Date'
and ipv1.input_value_id = eev1.input_value_id
and ipv2.input_value_id = eev2.input_value_id
and ipv3.input_value_id = eev3.input_value_id
and eev1.element_entry_id = ent.element_entry_id
and eev2.element_entry_id = ent.element_entry_id
and eev3.element_entry_id = ent.element_entry_id
and (ent.element_entry_id <> p_element_entry_id
or p_element_entry_id is null)
and eev1.screen_entry_value >= fnd_date.date_to_canonical(p_start_date)
and (eev3.screen_entry_value >= fnd_date.date_to_canonical(p_start_date)
OR (eev3.screen_entry_value is NULL
and (eev2.screen_entry_value >= fnd_date.date_to_canonical(p_start_date)
OR eev2.screen_entry_value is NULL )));
Select 'Y'
FROM pay_element_entry_values_f eev1,
pay_element_entry_values_f eev2,
pay_element_entries_f ent,
pay_input_values_f ipv1,
pay_input_values_f ipv2,
pay_element_links_f lnk,
pay_element_types_f ele
where ele.element_name = p_ele
and ele.element_type_id = lnk.element_type_id
and ent.element_link_id = lnk.element_link_id
and ent.assignment_id = p_asg
and ipv1.element_type_id = ele.element_type_id
and ipv2.element_type_id = ele.element_type_id
and ipv1.name = 'Start Date'
and ipv2.name = 'End Date'
and ipv1.input_value_id = eev1.input_value_id
and ipv2.input_value_id = eev2.input_value_id
and eev1.element_entry_id = ent.element_entry_id
and eev2.element_entry_id = ent.element_entry_id
and (ent.element_entry_id <> p_element_entry_id
or p_element_entry_id is null)
and eev1.screen_entry_value >= fnd_date.date_to_canonical(p_start_date)
and (eev2.screen_entry_value >= fnd_date.date_to_canonical(p_start_date)
OR (eev2.screen_entry_value is NULL));
select 'Y'
from per_time_periods ptp,
pay_payroll_actions ppa,
pay_assignment_actions paa,
per_all_assignments_f asg
where asg.assignment_id = p_asg
and asg.payroll_id = ppa.payroll_id
and paa.assignment_id = asg.assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ptp.time_period_id = ppa.time_period_id
and ppa.action_type = 'R'
and ppa.action_status='C'
and ptp.end_date > p_start_date;
select actual_termination_date,
last_standard_process_date,
final_process_date
from per_periods_of_service pos,
fnd_sessions ses,
per_all_assignments_f asg
where asg.assignment_id = p_asg
and ses.session_id = userenv('sessionid')
and ses.effective_date between asg.effective_start_date and asg.effective_end_date
and asg.person_id = pos.person_id
and ses.effective_date between pos.date_start and pos.last_standard_process_date
and pos.actual_termination_date is not null;
select count(*)
from per_time_periods ptp,
per_all_assignments_f asg
where asg.assignment_id = p_asg
and asg.payroll_id = ptp.payroll_id
and ptp.end_date <= p_termination_date
and ptp.end_date >= p_start_date;
Select 'Y'
FROM pay_element_entry_values_f eev1,
pay_element_entry_values_f eev2,
pay_element_entry_values_f eev3,
pay_element_entries_f ent,
pay_input_values_f ipv1,
pay_input_values_f ipv2,
pay_input_values_f ipv3,
pay_element_links_f lnk,
pay_element_types_f ele
where ele.element_name = p_ele
and ele.element_type_id = lnk.element_type_id
and ent.element_link_id = lnk.element_link_id
and ent.assignment_id = p_asg
and ipv1.element_type_id = ele.element_type_id
and ipv2.element_type_id = ele.element_type_id
and ipv3.element_type_id = ele.element_type_id
and ipv1.name = 'Start Date'
and ipv2.name = 'End Date'
and ipv3.name = 'Stop Date'
and ipv1.input_value_id = eev1.input_value_id
and ipv2.input_value_id = eev2.input_value_id
and ipv3.input_value_id = eev3.input_value_id
and eev1.element_entry_id = ent.element_entry_id
and eev2.element_entry_id = ent.element_entry_id
and eev3.element_entry_id = ent.element_entry_id
and (ent.element_entry_id <> p_element_entry_id
or p_element_entry_id is null)
and eev1.screen_entry_value <= fnd_date.date_to_canonical(p_end_date)
and (eev3.screen_entry_value >= fnd_date.date_to_canonical(p_start_date)
OR (eev3.screen_entry_value is NULL
and (eev2.screen_entry_value >= fnd_date.date_to_canonical(p_start_date)
OR eev2.screen_entry_value is NULL )))
and p_start_date between
eev1.effective_start_date and eev1.effective_end_date
and p_start_date between
eev2.effective_start_date and eev2.effective_end_date
and p_start_date between
eev3.effective_start_date and eev3.effective_end_date
and p_start_date between
ent.effective_start_date and ent.effective_end_date
and p_start_date between
lnk.effective_start_date and lnk.effective_end_date
and p_start_date between
ele.effective_start_date and ele.effective_end_date
and p_start_date between
ipv1.effective_start_date and ipv1.effective_end_date
and p_start_date between
ipv2.effective_start_date and ipv2.effective_end_date
and p_start_date between
ipv3.effective_start_date and ipv3.effective_end_date;
select 'Y'
from per_time_periods ptp,
pay_payroll_actions ppa,
per_all_assignments_f asg
where asg.assignment_id = p_asg
and asg.payroll_id = ppa.payroll_id
and ptp.time_period_id = ppa.time_period_id
and ppa.action_type = 'R'
and ppa.action_status='C'
and ptp.end_date > p_end_date;
PROCEDURE Check_Delete_Possible(
p_datetrack_mode in VARCHAR2,
p_effective_date in DATE,
p_assignment_id in PAY_ELEMENT_ENTRIES_F.assignment_id%TYPE,
p_start_date in DATE,
p_end_date in DATE,
p_message out nocopy VARCHAR2) is
cursor c_purge_allowed(p_asg in PAY_ELEMENT_ENTRIES_F.assignment_id%TYPE) is
select max(effective_date)
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_all_assignments_f asg
where asg.assignment_id = p_asg
and asg.assignment_id = paa.assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and asg.payroll_id = ppa.payroll_id
and ppa.action_type = 'R'
and ppa.action_status='C';
select /*+ ORDERED
INDEX(ptp PER_TIME_PERIODS_PK) */ 'Y'
from per_all_assignments_f asg,
pay_payroll_actions ppa,
per_time_periods ptp
where asg.assignment_id = p_asg
and asg.payroll_id = ppa.payroll_id
and ptp.time_period_id = ppa.time_period_id
and ppa.action_type = 'R'
and ppa.action_status='C'
and ptp.end_date > p_date;
elsif p_datetrack_mode = 'DELETE' then
open c_end_date_allowed(p_assignment_id, p_effective_date);
End Check_Delete_Possible;
select 'Y'
from pay_payroll_actions ppa,
per_all_assignments_f asg
where asg.assignment_id = p_asg
and asg.payroll_id = ppa.payroll_id
and ppa.action_type = 'R'
and ppa.action_status='C'
and ppa.effective_date > p_date;
select balance_type_id
from pay_balance_types
where balance_name = 'Tax Credit';
select prr.assignment_action_id,
prr.source_id
from pay_run_results prr,
pay_element_types_f ele
where prr.assignment_action_id in (
SELECT to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16))
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp,
fnd_sessions ses
WHERE paa.assignment_id = p_asg
AND ses.session_id = userenv('sessionid')
AND ppa.payroll_action_id = paa.payroll_action_id
AND ses.effective_date between ptp.start_date and ptp.end_date
AND ppa.time_period_id = ptp.time_period_id
AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B'))
AND prr.element_type_id = ele.element_type_id
AND ele.element_name = 'Tax Credit'
AND prr.source_id = p_ent_id;
select prr.assignment_action_id,
prr.source_id
from pay_run_results prr,
pay_element_types_f ele
where prr.assignment_action_id in (
select to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16))
from pay_assignment_actions paa,
pay_payroll_actions ppa,
fnd_sessions ses,
per_time_periods ptp,
per_all_assignments per
where paa.assignment_id = p_asg
and ses.session_id = userenv('sessionid')
and ptp.payroll_id = ppa.payroll_id
and ses.effective_date between ptp.start_date and ptp.end_date
and ppa.effective_date between ptp.start_date and ptp.end_date
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
and ppa.payroll_id = per.payroll_id
and paa.assignment_id = per.assignment_id
and ses.effective_date between per.effective_start_date and per.effective_end_date)
and prr.element_type_id = ele.element_type_id
and ele.element_name = 'Tax Credit'
and prr.source_id = p_ent;
PROCEDURE Delete_Tax_Credit(
p_datetrack_mode in VARCHAR2
,p_element_entry_id in NUMBER
,p_effective_date in DATE
,p_object_version_number in NUMBER) IS
l_object_version_number NUMBER;
l_delete_warning BOOLEAN;
py_element_entry_api.delete_element_entry(
p_validate => FALSE,
p_datetrack_delete_mode => p_datetrack_mode,
p_effective_date => p_effective_date,
p_element_entry_id => p_element_entry_id,
p_object_version_number => l_object_version_number,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_delete_warning => l_delete_warning
);
END Delete_Tax_Credit;
PROCEDURE Update_Tax_Credit(
p_datetrack_update_mode in varchar2
,p_effective_date in date
,p_business_group_id in number
,p_element_entry_id in number
,p_object_version_number in out nocopy number
,p_reference in VARCHAR2
,p_start_date in VARCHAR2
,p_end_date in VARCHAR2
,p_daily_amount in VARCHAR2
,p_total_amount in VARCHAR2
,p_stop_date in VARCHAR2
,p_reference_ipv_id in NUMBER
,p_start_date_ipv_id in NUMBER
,p_end_date_ipv_id in NUMBER
,p_daily_amount_ipv_id in NUMBER
,p_total_amount_ipv_id in NUMBER
,p_stop_date_ipv_id in NUMBER
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date) is
l_update_warning BOOLEAN;
py_element_entry_api.update_element_entry(
p_validate => FALSE,
p_datetrack_update_mode => p_datetrack_update_mode,
p_effective_date => p_effective_date,
p_business_group_id => p_business_group_id,
p_element_entry_id => p_element_entry_id,
p_object_version_number => p_object_version_number,
P_INPUT_VALUE_ID1 =>Get_Input_Value_Id('Reference',
p_effective_date),
P_INPUT_VALUE_ID2 =>Get_Input_Value_Id('Start Date',
p_effective_date),
P_INPUT_VALUE_ID3 =>Get_Input_Value_Id('End Date',
p_effective_date),
P_INPUT_VALUE_ID4 =>Get_Input_Value_Id('Daily Amount',
p_effective_date),
P_INPUT_VALUE_ID5 =>Get_Input_Value_Id('Total Amount',
p_effective_date),
P_INPUT_VALUE_ID6 =>Get_Input_Value_Id('Stop Date',
p_effective_date),
P_ENTRY_VALUE1 =>p_reference,
P_ENTRY_VALUE2 =>p_start_date,
P_ENTRY_VALUE3 =>p_end_date,
P_ENTRY_VALUE4 =>p_daily_amount,
P_ENTRY_VALUE5 =>p_total_amount,
P_ENTRY_VALUE6 =>p_stop_date,
P_EFFECTIVE_START_DATE =>p_effective_start_date,
P_EFFECTIVE_END_DATE =>p_effective_end_date,
P_UPDATE_WARNING =>l_update_warning);
END Update_Tax_Credit;