[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_tab_event_map_cv.DELETE;
g_tab_abs_types.DELETE;
g_tab_asg_status.DELETE;
g_tab_pen_sch_map_cv.DELETE;
g_tab_pen_ele_ids.DELETE;
g_tab_prs_dfn_cv.DELETE;
g_tab_dated_table.DELETE;
g_tab_lvrsn_map_cv.DELETE;
SELECT input_value_id
FROM pay_input_values_f
WHERE element_type_id = p_element_type_id
AND NAME = p_input_value_name
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT screen_entry_value
FROM pay_element_entry_values_f
WHERE element_entry_id = p_element_entry_id
AND effective_start_date = p_effective_start_date
AND effective_end_date = p_effective_end_date
AND input_value_id = p_input_value_id;
SELECT dfc.descriptive_flex_context_name
FROM pqp_configuration_types pct, fnd_descr_flex_contexts_vl dfc
WHERE pct.configuration_type = p_config_type
AND dfc.descriptive_flex_context_code = pct.configuration_type
AND dfc.application_id = 8303
AND dfc.descriptive_flexfield_name =
'Configuration Value Info DDF'
AND dfc.enabled_flag = 'Y';
SELECT user_status
FROM per_assignment_status_types
WHERE assignment_status_type_id = p_asg_sts_type_id;
SELECT NAME
FROM per_absence_attendance_types
WHERE absence_attendance_type_id = p_absence_type_id;
SELECT lookup_code, meaning
FROM hr_lookups
WHERE lookup_type = 'PQP_PENSERVER_EVENT_DESC'
AND enabled_flag = 'Y'
AND g_effective_date BETWEEN NVL(
start_date_active
,g_effective_date
)
AND NVL(end_date_active
,g_effective_date)
ORDER BY lookup_code;
g_tab_event_desc_lov.DELETE(j);
SELECT DECODE(data_typ_cd, 'F', 'CUTOVER', 'C', 'PERIODIC')
FROM ben_ext_dfn
WHERE ext_dfn_id = p_ext_dfn_id;
SELECT pee.element_entry_id, pee.effective_start_date
,pee.effective_end_date, pel.element_type_id
FROM pay_element_entries_f pee, pay_element_links_f pel
WHERE pee.assignment_id = p_assignment_id
AND pee.entry_type = 'E'
AND pee.element_link_id = pel.element_link_id
AND p_effective_date BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND pel.element_type_id = c_element_type_id
AND p_effective_date BETWEEN pel.effective_start_date
AND pel.effective_end_date
ORDER BY pee.effective_start_date DESC;
SELECT MAX(eff_dt)
FROM ben_ext_rslt
WHERE ext_dfn_id = g_ext_dfn_id
AND business_group_id = g_business_group_id
AND ext_stat_cd = 'A';
SELECT DECODE(per.current_employee_flag, 'Y', pps.date_start, NULL)
FROM per_all_people_f per, per_periods_of_service pps
WHERE per.person_id = p_person_id
AND pps.person_id = p_person_id
AND p_effective_date BETWEEN per.effective_start_date
AND NVL(
per.effective_end_date
,TO_DATE('31/12/4712', 'DD/MM/YYYY')
)
AND p_effective_date BETWEEN pps.date_start
AND NVL(
pps.actual_termination_date
,TO_DATE('31/12/4712', 'DD/MM/YYYY')
);
SELECT person_id, effective_start_date, effective_end_date
,assignment_number, primary_flag, normal_hours
,assignment_status_type_id, employment_category
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date
ORDER BY effective_start_date DESC;
SELECT asg1.assignment_id curr_assignment_id
,asg1.assignment_status_type_id curr_status_type_id
,asg1.effective_start_date curr_effective_start_date
,asg1.effective_end_date curr_effective_end_date
,asg2.assignment_status_type_id prev_status_type_id
,asg2.effective_start_date prev_effective_start_date
,asg2.effective_end_date prev_effective_end_date
FROM per_all_assignments_f asg1, per_all_assignments_f asg2
WHERE asg1.assignment_id = p_assignment_id
AND (
(
asg1.effective_start_date BETWEEN c_effective_start_date
AND c_effective_end_date
)
OR (
asg1.effective_end_date BETWEEN c_effective_start_date
AND c_effective_end_date
)
)
AND asg2.assignment_id = asg1.assignment_id
AND asg2.effective_end_date = asg1.effective_start_date - 1
AND asg2.assignment_status_type_id <>
asg1.assignment_status_type_id
ORDER BY asg1.effective_start_date DESC;
SELECT MIN(effective_start_date)
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND employment_category = c_employment_category;
SELECT absence_attendance_type_id, absence_attendance_id
,date_start, date_end
FROM per_absence_attendances
WHERE person_id = c_person_id
AND (
(
date_start BETWEEN c_effective_start_date
AND c_effective_end_date
)
OR (
( NVL(date_end, c_effective_start_date)
BETWEEN c_effective_start_date
AND c_effective_end_date
)
AND
(date_start <= c_effective_end_date)
)
)
ORDER BY date_start DESC;
SELECT pee.element_entry_id, pee.effective_start_date
,pee.effective_end_date, pel.element_type_id
FROM pay_element_entries_f pee, pay_element_links_f pel
WHERE pee.assignment_id = p_assignment_id
AND pee.entry_type = 'E'
AND pee.element_link_id = pel.element_link_id
AND (
(
pee.effective_start_date BETWEEN c_effective_start_date
AND c_effective_end_date
)
OR (
pee.effective_end_date BETWEEN c_effective_start_date
AND c_effective_end_date
)
)
AND pel.element_type_id = c_element_type_id
AND g_effective_date BETWEEN pel.effective_start_date
AND pel.effective_end_date
ORDER BY pee.effective_start_date DESC;
SELECT pee.element_entry_id, pee.effective_start_date
,pee.effective_end_date
FROM pay_element_entries_f pee, pay_element_links_f pel
WHERE pee.assignment_id = p_assignment_id
AND pee.entry_type = 'E'
AND pee.element_link_id = pel.element_link_id
AND (
(
pee.effective_start_date BETWEEN c_effective_start_date
AND c_effective_end_date
)
OR (
pee.effective_end_date BETWEEN c_effective_start_date
AND c_effective_end_date
)
)
AND pel.element_type_id = c_element_type_id
AND g_effective_date BETWEEN pel.effective_start_date
AND pel.effective_end_date
AND EXISTS(
SELECT 1
FROM pay_element_entry_values_f pev
WHERE pev.element_entry_id = pee.element_entry_id
AND pev.effective_start_date =
pee.effective_start_date
AND pev.effective_end_date = pee.effective_end_date
AND pev.input_value_id = c_input_value_id
AND pev.screen_entry_value IS NOT NULL)
ORDER BY pee.effective_start_date DESC;
SELECT effective_end_date
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND c_effective_date BETWEEN effective_start_date
AND effective_end_date
ORDER BY effective_start_date;
SELECT pps.leaving_reason, pps.actual_termination_date
FROM per_periods_of_service pps
WHERE pps.person_id = c_person_id
AND pps.date_start = (SELECT MAX(date_start)
FROM per_periods_of_service pps1
WHERE pps1.person_id = c_person_id
AND pps1.date_start <= c_effective_date);
SELECT pos.actual_termination_date
FROM per_all_assignments_f asg,
per_periods_of_service pos
WHERE asg.assignment_id = p_assignment_id
AND g_effective_date between asg.effective_start_date AND asg.effective_end_date
AND asg.period_of_service_id = pos.period_of_service_id;
SELECT absence_attendance_id, absence_attendance_type_id
,date_start, date_end
FROM per_absence_attendances
WHERE absence_attendance_id = p_absence_attendance_id;
SELECT actual_termination_date
FROM per_all_assignments_f paf,
per_periods_of_service pos
WHERE paf.assignment_id=p_assignment_id
AND paf.period_of_service_id = pos.period_of_service_id;
IF g_tab_pay_proc_evnts(g_event_counter).update_type = 'P'
THEN
--
NULL;
SELECT absence_attendance_id, absence_attendance_type_id
,date_start, date_end
FROM per_absence_attendances
WHERE absence_attendance_id = c_absence_attendance_id;
SELECT gap.absence_attendance_id, glds.gap_absence_plan_id
,glds.gap_level, glds.date_start, glds.date_end
,glds.summary_type
FROM pqp_gap_absence_plans gap, pqp_gap_duration_summary glds
WHERE glds.gap_absence_plan_id = gap.gap_absence_plan_id
AND glds.gap_duration_summary_id = p_gap_duration_summary_id;
SELECT actual_termination_date
FROM per_all_assignments_f paf,
per_periods_of_service pos
WHERE paf.assignment_id=p_assignment_id
AND paf.period_of_service_id = pos.period_of_service_id;
IF g_tab_pay_proc_evnts(g_event_counter).update_type = 'P'
THEN
--
NULL;
SELECT element_type_id
FROM pay_element_entries_f
WHERE assignment_id = p_assignment_id
AND element_type_id = p_element_type_id
AND effective_start_date = p_pension_change_date + 1;
SELECT element_type_id
FROM pay_element_entries_f
WHERE assignment_id = p_assignment_id
AND element_type_id = p_element_type_id
AND effective_end_date = p_pension_change_date - 1;
SELECT pel.element_type_id, pee.effective_start_date
,pee.effective_end_date, pee.element_entry_id
FROM pay_element_entries_f pee, pay_element_links_f pel
WHERE pee.element_entry_id = c_element_entry_id
AND g_effective_date BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND pel.element_link_id = pee.element_link_id
AND g_effective_date BETWEEN pee.effective_start_date
AND pee.effective_end_date;
SELECT input_value_id, screen_entry_value, element_entry_id
FROM pay_element_entry_values_f
WHERE element_entry_value_id = c_element_entry_value_id
AND g_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT MIN(effective_start_date)
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND employment_category = c_employment_category;
SELECT effective_end_date
FROM per_all_assignments_f paaf,
per_periods_of_service pps,
per_assignment_status_types past
WHERE paaf.assignment_id = p_assignment_id
AND paaf.assignment_status_type_id = past.assignment_status_type_id
and pps.person_id = paaf.person_id
and pps.period_of_service_id = paaf.period_of_service_id
AND past.per_system_status = 'ACTIVE_ASSIGN'
and g_effective_date <> NVL(pps.final_process_date, hr_api.g_eot)
AND g_effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
ORDER BY paaf.effective_start_date;
SELECT MIN(effective_start_date)
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id;
SELECT element_type_id
FROM pay_element_entries_f
WHERE assignment_id = p_assignment_id
AND effective_start_date BETWEEN asg_start_date
AND (ele_start_date-1)
ORDER BY effective_start_date;
IF g_tab_pay_proc_evnts(g_event_counter).update_type = 'P'
THEN
--
NULL;
IF g_tab_pay_proc_evnts(g_event_counter).update_type = 'I'
--Bug 9179022: Added update of EFFECTIVE_START_DATE as a valid event
OR (g_tab_pay_proc_evnts(g_event_counter).update_type = 'U'
AND
g_tab_pay_proc_evnts(g_event_counter).column_name = 'EFFECTIVE_START_DATE')
THEN
-- This is an insert event
-- Check whether this element type id exists in the
-- pension element collection
IF g_debug
THEN
l_proc_step := 30;
ELSIF g_tab_pay_proc_evnts(g_event_counter).update_type
in ('U','E') --115.20 5930973
THEN
-- Date track update
-- Check whether the effective end date of the element entry
-- is not end of time
-- Do this check only if this is a pension element and
-- ensure that this is not because of a termination event
IF g_debug
THEN
l_proc_step := 70;
END IF; -- End if of update_type check ...
SELECT MIN(effective_start_date)
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND employment_category = c_employment_category;
SELECT leaving_reason, actual_termination_date,final_process_date
FROM per_all_assignments_f paf,
per_periods_of_service pos
WHERE paf.assignment_id=p_assignment_id
AND paf.period_of_service_id = pos.period_of_service_id;
SELECT pps.leaving_reason, pps.actual_termination_date
FROM per_periods_of_service pps
WHERE pps.person_id = c_person_id
AND pps.date_start = (SELECT MAX(date_start)
FROM per_periods_of_service pps1
WHERE pps1.person_id = c_person_id
AND pps1.date_start <= c_effective_date);
SELECT element_type_id, element_entry_id
FROM pay_element_entries_f
WHERE assignment_id = p_assignment_id
AND effective_start_date = c_asg_cate_chng_date;
SELECT element_entry_id
FROM pay_element_entries_f
WHERE assignment_id = p_assignment_id
AND element_entry_id = c_element_entry_id
AND effective_end_date = c_asg_cate_chng_date -1;
'update type: '
|| l_tab_pay_proc_evnts(g_event_counter).update_type
);
OR l_tab_pay_proc_evnts(g_event_counter).update_type <>
g_prev_pay_proc_evnts.update_type
OR l_tab_pay_proc_evnts(g_event_counter).surrogate_key <>
g_prev_pay_proc_evnts.surrogate_key
OR l_tab_pay_proc_evnts(g_event_counter).column_name <>
g_prev_pay_proc_evnts.column_name
OR l_tab_pay_proc_evnts(g_event_counter).effective_date <>
g_prev_pay_proc_evnts.effective_date
OR l_tab_pay_proc_evnts(g_event_counter).old_value <>
g_prev_pay_proc_evnts.old_value
OR l_tab_pay_proc_evnts(g_event_counter).new_value <>
g_prev_pay_proc_evnts.new_value
OR l_tab_pay_proc_evnts(g_event_counter).change_values <>
g_prev_pay_proc_evnts.change_values
OR l_tab_pay_proc_evnts(g_event_counter).proration_type <>
g_prev_pay_proc_evnts.proration_type
OR l_tab_pay_proc_evnts(g_event_counter).event_group_id <>
g_prev_pay_proc_evnts.event_group_id
OR l_tab_pay_proc_evnts(g_event_counter).actual_date <>
g_prev_pay_proc_evnts.actual_date
THEN
l_process_flag := 'Y';