[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_tab_clas_pen_bal_dtls.DELETE;
g_tab_clap_pen_bal_dtls.DELETE;
g_tab_prem_pen_bal_dtls.DELETE;
g_tab_part_pen_bal_dtls.DELETE;
g_tab_pen_sch_map_cv.DELETE;
g_tab_pen_ele_ids.DELETE;
g_tab_prs_dfn_cv.DELETE;
g_tab_eei_info.DELETE;
g_tab_avc_pen_bal_dtls.DELETE;
g_tab_ni_cont_out_bals.DELETE;
g_tab_nuvos_pen_bal_dtls.DELETE; -- For Nuvos
SELECT petl.element_name
FROM pay_element_types_f pet, pay_element_types_f_tl petl
WHERE petl.element_type_id = pet.element_type_id
AND petl.LANGUAGE = USERENV('LANG')
AND pet.element_type_id = p_element_type_id
AND p_effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date;
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 template_id
FROM pay_element_templates
WHERE template_name = p_template_name
AND template_type = p_template_type
AND (
(
p_business_group_id IS NOT NULL
AND business_group_id = p_business_group_id
)
OR (
business_group_id IS NULL AND p_business_group_id IS NULL
)
);
SELECT *
FROM pay_element_type_extra_info
WHERE element_type_id = p_element_type_id
AND information_type = p_information_type;
SELECT aei_information1
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND information_type = p_information_type;
SELECT /*+ leading(paa) */
DISTINCT (ptp.end_date) end_date
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,per_time_periods ptp
WHERE ptp.time_period_id = ppa.time_period_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.effective_date BETWEEN p_effective_start_date AND p_effective_end_date
AND ppa.action_type IN('R', 'Q', 'I', 'V', 'B')
AND NVL(ppa.business_group_id, g_business_group_id) = g_business_group_id
AND paa.assignment_id = p_assignment_id
ORDER BY ptp.end_date;
SELECT balance_type_id
FROM pay_balance_types
WHERE balance_name = p_balance_name
AND (
(business_group_id = p_business_group_id)
OR (
business_group_id IS NULL
AND (
legislation_code IS NULL
OR legislation_code = p_legislation_code
)
)
);
SELECT balance_dimension_id
FROM pay_balance_dimensions
WHERE dimension_name = p_dimension_name
AND (
(business_group_id = p_business_group_id)
OR (
business_group_id IS NULL
AND (
legislation_code IS NULL
OR legislation_code = p_legislation_code
)
)
);
SELECT defined_balance_id
FROM pay_defined_balances
WHERE balance_type_id = p_balance_type_id
AND balance_dimension_id = p_balance_dimension_id;
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 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
pee.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_start_date BETWEEN pee.effective_start_date
AND pee.effective_end_date
OR p_effective_end_date BETWEEN pee.effective_start_date
AND pee.effective_end_date
OR pee.effective_start_date BETWEEN p_effective_start_date
AND p_effective_end_date
OR pee.effective_end_date BETWEEN p_effective_start_date
AND p_effective_end_date
)
AND pee.element_type_id = c_element_type_id
/*AND pel.element_type_id = c_element_type_id
AND (
p_effective_start_date BETWEEN pel.effective_start_date
AND pel.effective_end_date
OR p_effective_end_date BETWEEN pel.effective_start_date
AND pel.effective_end_date
OR pel.effective_start_date BETWEEN p_effective_start_date
AND p_effective_end_date
OR pel.effective_end_date BETWEEN p_effective_start_date
AND p_effective_end_date
)*/
ORDER BY pee.effective_start_date DESC;
SELECT *
FROM pay_element_type_extra_info
WHERE information_type = p_information_type
AND eei_information16 = p_associated_ocp_ele_id
AND eei_information12 IS NULL;
SELECT pec.classification_name
FROM pay_element_types_f petf, pay_element_classifications pec
WHERE petf.element_type_id = p_element_type_id
AND pec.classification_id = petf.classification_id;
SELECT screen_entry_value, effective_start_date, effective_end_date
FROM pay_element_entry_values_f
WHERE element_entry_id = c_element_entry_id
AND (
effective_start_date BETWEEN c_effective_start_date
AND c_effective_end_date
OR effective_end_date BETWEEN c_effective_start_date
AND c_effective_end_date
OR c_effective_start_date BETWEEN effective_start_date
AND effective_end_date
OR c_effective_end_date BETWEEN effective_start_date
AND effective_end_date
)
AND input_value_id = c_input_value_id;
SELECT actual_termination_date
FROM per_periods_of_service pps
WHERE pps.person_id = g_person_dtl.person_id
AND pps.date_start =
(SELECT MAX(pps1.date_start) -- this gets most recent
FROM per_periods_of_service pps1
WHERE pps1.person_id = g_person_dtl.person_id
AND pps1.date_start <= c_effective_date)
AND pps.actual_termination_date <=
last_day(add_months(g_effective_end_date, -1)); -- Bug: 6801704