[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select start_date,
end_date,
status,
period_name as DISPLAY_PERIOD
/* commented for the bug 12388097 */
-- period_name||
-- ' ('||
-- fnd_date.date_to_displaydate (start_date)|| /* COMMENTED FOR BUG 11830805 */
-- fnd_date.date_to_displaydate (start_date, calendar_aware=>2)|| /* FOR BUG 11830805 */
-- ' - '||
-- fnd_date.date_to_displaydate (end_date)|| /* COMMENTED FOR BUG 11830805 */
-- fnd_date.date_to_displaydate (end_date, calendar_aware=>2)|| /* FOR BUG 11830805 */
-- ')' DISPLAY_PERIOD
from per_time_periods
where payroll_id = p_payroll_id
and p_effective_date between start_date and end_date;
select cost_allocation_structure
from per_business_groups_perf
where business_group_id = p_business_group_id;
select payroll_id
from per_assignments_f
where assignment_id = p_assignment_id
and p_effective_date between effective_start_date
and effective_end_date;
select value
from pay_restriction_values
where restriction_code = 'ELEMENT_TYPE'
and customized_restriction_id = p_customized_restriction_id;
select fnd_number.canonical_to_number( value )
from pay_restriction_values
where restriction_code = 'ELEMENT_SET'
and customized_restriction_id = p_customized_restriction_id;
select value
from pay_restriction_values
where restriction_code = 'ENTRY_TYPE'
and customized_restriction_id = p_customized_restriction_id;
select 'Y'
from pay_run_results prr,
pay_element_entries_f pee
where pee.element_entry_id = p_element_entry_id
and p_effective_date between pee.effective_start_date
and pee.effective_end_date
and prr.source_id = decode(pee.entry_type,
'A', decode (adjust_ee_source,
'T', pee.target_entry_id,
pee.element_entry_id),
'R', decode (adjust_ee_source,
'T', pee.target_entry_id,
pee.element_entry_id),
pee.element_entry_id)
and prr.entry_type = pee.entry_type
and prr.source_type = 'E'
and prr.status <> 'U'
-- change 115.9
and NOT EXISTS
(SELECT 1
FROM PAY_RUN_RESULTS sub_rr
WHERE sub_rr.source_id = prr.run_result_id
and sub_rr.source_type in ('R', 'V'))
;
select 'Y'
from pay_element_entries_f oee,
pay_element_entries_f ree
where oee.element_entry_id = p_element_entry_id
and p_effective_date between oee.effective_start_date
and oee.effective_end_date
and ree.assignment_id = oee.assignment_id
and ree.source_id = oee.element_entry_id
and ree.entry_type = 'E'
and ree.creator_type = 'EE';
select 'Y'
from pay_run_results RESULT,
pay_assignment_actions ASGT_ACTION,
pay_payroll_actions PAY_ACTION,
per_time_periods PERIOD
where result.source_id = nvl (p_original_entry_id, p_element_entry_id)
and result.status <> 'U'
and result.source_type = 'E'
and result.assignment_action_id = asgt_action.assignment_action_id
and asgt_action.payroll_action_id = pay_action.payroll_action_id
and pay_action.payroll_id = period.payroll_id
and pay_action.date_earned between period.start_date and period.end_date
and p_effective_date between period.start_date and period.end_date
-- change 115.12
and NOT EXISTS
(SELECT 1
FROM PAY_RUN_RESULTS rev_result
WHERE rev_result.source_id = result.run_result_id
and rev_result.source_type in ('R', 'V'));
select /*+ ORDERED INDEX(ree PAY_ELEMENT_ENTRIES_F_N50)*/
'Y'
from pay_element_entries_f oee,
pay_element_entries_f ree,
pay_assignment_actions paa,
pay_payroll_actions pac,
per_time_periods period
where oee.element_entry_id = p_element_entry_id
and p_effective_date between oee.effective_start_date
and oee.effective_end_date
and p_effective_date between period.start_date and period.end_date
and pac.payroll_id = period.payroll_id
and pac.date_earned between period.start_date and period.end_date
and ree.assignment_id = oee.assignment_id
and ree.source_id = oee.element_entry_id
and ree.entry_type in ('D','E')
and ree.creator_type = 'EE'
and paa.assignment_action_id = ree.source_asg_action_id
and pac.payroll_action_id = paa.payroll_action_id
and pac.effective_date between oee.effective_start_date
and oee.effective_end_date;
select /*+ INDEX(paf PER_ASSIGNMENTS_F_PK)*/ plr.rule_mode
into adjust_ee_source
from pay_legislation_rules plr,
per_business_groups pbg,
per_assignments_f paf,
pay_element_entries_f pee
where pee.element_entry_id = p_element_entry_id
and p_effective_date between pee.effective_start_date
and pee.effective_end_date
and paf.assignment_id = pee.assignment_id
and p_effective_date between paf.effective_start_date
and paf.effective_end_date
and paf.business_group_id = pbg.business_group_id
and pbg.legislation_code = plr.legislation_code
and plr.rule_type = 'ADJUSTMENT_EE_SOURCE';
PROCEDURE delete_entry_caches IS
BEGIN
-- no need to delete the g_element_link_id_tab structure as it is always deleted
-- at the end of population
g_entry_type_start_tab.DELETE;
g_entry_type_stop_tab.DELETE;
g_entry_type_tab.DELETE;
END delete_entry_caches;
SELECT DISTINCT
pee.element_link_id,
pee.entry_type
FROM pay_element_entries_f pee
WHERE pee.assignment_id = p_assignment_id
AND p_effective_date
BETWEEN pee.effective_start_date and pee.effective_end_date
ORDER BY 1,2;
delete_entry_caches;
g_element_link_id_tab.DELETE;
delete_entry_caches;
select 'Y'
from pay_quickpay_inclusions incl
where p_use_qpay_excl_model = 'N'
and incl.assignment_action_id = p_asgt_act_id
and incl.element_entry_id = p_ee_id
union all
/*
* QuickPay Exclusions model
*/
select 'Y'
from dual
where p_use_qpay_excl_model = 'Y'
and not exists
(select ''
from pay_quickpay_exclusions excl
where excl.assignment_action_id = p_asgt_act_id
and excl.element_entry_id = p_ee_id
);
select ppm.payee_type,
ppm.payee_id,
opm_tl.org_payment_method_name
||' : '||pay_type_tl.payment_type_name PAYMENT_TYPE
from pay_personal_payment_methods_f PPM,
pay_org_payment_methods_f_tl OPM_TL,
pay_org_payment_methods_f OPM,
pay_payment_types_tl PAY_TYPE_TL,
pay_payment_types PAY_TYPE
where personal_payment_method_id = p_personal_payment_method_id
and ppm.org_payment_method_id = opm.org_payment_method_id
and opm_tl.org_payment_method_id = opm.org_payment_method_id
and USERENV('LANG') = opm_tl.language
and pay_type.payment_type_id = opm.payment_type_id
and pay_type_tl.payment_type_id = pay_type.payment_type_id
and userenv('LANG') = pay_type_tl.language
and p_effective_date between opm.effective_start_date
and opm.effective_end_date
and p_effective_date between ppm.effective_start_date
and ppm.effective_end_date;
select name
from hr_all_organization_units
where organization_id = l_payee_id;
select full_name
from per_all_people_f
where person_id = l_payee_id
and p_effective_date between effective_start_date
and effective_end_date;
procedure update_original_if_MIX
(
-- used by entry.insert_row to nulify creator_type
-- for MIX entry when creating additional entries or overrides
--
p_assignment_id number,
p_element_type_id number,
p_effective_start_date date,
p_session_date date
) is
--
l_element_entry_id number;
select peef.element_entry_id, peef.creator_type
from pay_element_entries_f peef,
pay_element_links_f pelf,
pay_element_types_f petf
where petf.element_type_id = p_element_type_id
and pelf.element_type_id = petf.element_type_id
and peef.element_link_id = pelf.element_link_id
and peef.assignment_id = p_assignment_id
and p_effective_start_date between peef.effective_start_date
and peef.effective_end_date
and p_effective_start_date between pelf.effective_start_date
and pelf.effective_end_date
and p_effective_start_date between petf.effective_start_date
and petf.effective_end_date;
hr_entry_api.update_element_entry
(
p_dt_update_mode =>'CORRECTION',
p_session_date =>p_session_date,
p_creator_type => 'F',
p_creator_id => null,
p_element_entry_id =>l_element_entry_id
);
end update_original_if_MIX;
select employee_contribution,
employer_contribution
--
from ben_benefit_contributions_f
--
where p_effective_date between effective_start_date
and effective_end_date
and element_type_id = p_element_type_id
and business_group_id = p_business_group_id
and coverage_type = v_coverage_type;
select entry.element_entry_value_id,
entry.screen_entry_value,
entry.input_value_id,
type_tl.name,
type.uom,
type.hot_default_flag,
type.mandatory_flag,
decode (type.hot_default_flag,
'N', link.warning_or_error,
nvl (link.warning_or_error,
type.warning_or_error)) WARNING_OR_ERROR,
type.lookup_type,
type.value_set_id,
type.formula_id,
decode(type.hot_default_flag,'N',link.min_value,
nvl(link.min_value,type.min_value)) MIN_VALUE,
decode(type.hot_default_flag,'N',link.max_value,
nvl(link.max_value,type.max_value)) MAX_VALUE,
decode (type.hot_default_flag,
'N', link.default_value,
nvl (link.default_value,
type.default_value)) DEFAULT_VALUE
from pay_element_entry_values_f ENTRY,
pay_link_input_values_f LINK,
pay_input_values_f_tl TYPE_TL,
pay_input_values_f TYPE
where entry.element_entry_id = p_element_entry_id
and link.element_link_id = p_element_link_id
and link.input_value_id = entry.input_value_id
and type.input_value_id = entry.input_value_id
and type_tl.input_value_id = type.input_value_id
and userenv('LANG') = type_tl.language
and p_effective_date between link.effective_start_date
and link.effective_end_date
-- Bugfix 4438706
-- Fetch the entry values that match the effective start and end
-- dates of the entry, not the ones as at the effective date (could
-- be the wrong values if form is running in QuickPay mode).
-- and p_effective_date between entry.effective_start_date
-- and entry.effective_end_date
and entry.effective_start_date = p_ee_effective_start_date
and entry.effective_end_date = p_ee_effective_end_date
and p_effective_date between type.effective_start_date
and type.effective_end_date
order by type.display_sequence, type_tl.name;
select PAY_ACT.date_earned
from pay_entry_process_details ENTRY_PROC,
pay_assignment_actions ASGT_ACT,
pay_payroll_actions PAY_ACT
where ENTRY_PROC.element_entry_id = ee_id
and ENTRY_PROC.source_asg_action_id = ASGT_ACT.assignment_action_id
and ASGT_ACT.payroll_action_id = PAY_ACT.payroll_action_id;