The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pcv.configuration_value_id, pcv.pcv_information1
,pcv.pcv_information2, pcv.pcv_information3
,pcv.pcv_information4, pcv.pcv_information5
,pcv.pcv_information6, pcv.pcv_information7
,pcv.pcv_information8, pcv.pcv_information9
,pcv.pcv_information10, pcv.pcv_information11
,pcv.pcv_information12, pcv.pcv_information13
,pcv.pcv_information14, pcv.pcv_information15
,pcv.pcv_information16, pcv.pcv_information17
,pcv.pcv_information18, pcv.pcv_information19
,pcv.pcv_information20
FROM pqp_configuration_values pcv, pqp_configuration_types pct
WHERE pcv.pcv_information_category = pct.configuration_type
AND pct.configuration_type = p_configuration_type
AND ( ( pcv.business_group_id IS NOT NULL
AND pcv.business_group_id = p_business_group_id
)
OR ( pcv.legislation_code IS NOT NULL
AND pcv.legislation_code = c_legislation_code
AND NOT EXISTS(
SELECT 1
FROM pqp_configuration_values pcv2
WHERE pcv2.pcv_information_category =
pcv.pcv_information_category
AND pcv2.configuration_value_id <>
pcv.configuration_value_id
AND pcv2.business_group_id = p_business_group_id
AND ( ( pct.multiple_occurences_flag = 'Y'
AND ( ( pct.total_unique_columns = 1
AND pcv2.pcv_information1 =
pcv.pcv_information1
)
OR ( pct.total_unique_columns = 2
AND pcv2.pcv_information1 =
pcv.pcv_information1
AND pcv2.pcv_information2 =
pcv.pcv_information2
)
OR ( pct.total_unique_columns = 3
AND pcv2.pcv_information1 =
pcv.pcv_information1
AND pcv2.pcv_information2 =
pcv.pcv_information2
AND pcv2.pcv_information3 =
pcv.pcv_information3
)
OR ( pct.total_unique_columns = 4
AND pcv2.pcv_information1 =
pcv.pcv_information1
AND pcv2.pcv_information2 =
pcv.pcv_information2
AND pcv2.pcv_information3 =
pcv.pcv_information3
AND pcv2.pcv_information4 =
pcv.pcv_information4
)
OR ( pct.total_unique_columns = 5
AND pcv2.pcv_information1 =
pcv.pcv_information1
AND pcv2.pcv_information2 =
pcv.pcv_information2
AND pcv2.pcv_information3 =
pcv.pcv_information3
AND pcv2.pcv_information4 =
pcv.pcv_information4
AND pcv2.pcv_information5 =
pcv.pcv_information5
)
OR ( pct.total_unique_columns = 6
AND pcv2.pcv_information1 =
pcv.pcv_information1
AND pcv2.pcv_information2 =
pcv.pcv_information2
AND pcv2.pcv_information3 =
pcv.pcv_information3
AND pcv2.pcv_information4 =
pcv.pcv_information4
AND pcv2.pcv_information5 =
pcv.pcv_information5
AND pcv2.pcv_information6 =
pcv.pcv_information6
)
OR ( pct.total_unique_columns = 7
AND pcv2.pcv_information1 =
pcv.pcv_information1
AND pcv2.pcv_information2 =
pcv.pcv_information2
AND pcv2.pcv_information3 =
pcv.pcv_information3
AND pcv2.pcv_information4 =
pcv.pcv_information4
AND pcv2.pcv_information5 =
pcv.pcv_information5
AND pcv2.pcv_information6 =
pcv.pcv_information6
AND pcv2.pcv_information7 =
pcv.pcv_information7
)
OR ( pct.total_unique_columns = 8
AND pcv2.pcv_information1 =
pcv.pcv_information1
AND pcv2.pcv_information2 =
pcv.pcv_information2
AND pcv2.pcv_information3 =
pcv.pcv_information3
AND pcv2.pcv_information4 =
pcv.pcv_information4
AND pcv2.pcv_information5 =
pcv.pcv_information5
AND pcv2.pcv_information6 =
pcv.pcv_information6
AND pcv2.pcv_information7 =
pcv.pcv_information7
AND pcv2.pcv_information8 =
pcv.pcv_information8
)
OR ( pct.total_unique_columns = 9
AND pcv2.pcv_information1 =
pcv.pcv_information1
AND pcv2.pcv_information2 =
pcv.pcv_information2
AND pcv2.pcv_information3 =
pcv.pcv_information3
AND pcv2.pcv_information4 =
pcv.pcv_information4
AND pcv2.pcv_information5 =
pcv.pcv_information5
AND pcv2.pcv_information6 =
pcv.pcv_information6
AND pcv2.pcv_information7 =
pcv.pcv_information7
AND pcv2.pcv_information8 =
pcv.pcv_information8
AND pcv2.pcv_information9 =
pcv.pcv_information9
)
OR ( pct.total_unique_columns =
10
AND pcv2.pcv_information1 =
pcv.pcv_information1
AND pcv2.pcv_information2 =
pcv.pcv_information2
AND pcv2.pcv_information3 =
pcv.pcv_information3
AND pcv2.pcv_information4 =
pcv.pcv_information4
AND pcv2.pcv_information5 =
pcv.pcv_information5
AND pcv2.pcv_information6 =
pcv.pcv_information6
AND pcv2.pcv_information7 =
pcv.pcv_information7
AND pcv2.pcv_information8 =
pcv.pcv_information8
AND pcv2.pcv_information9 =
pcv.pcv_information9
AND pcv2.pcv_information10 =
pcv.pcv_information10
)
)
)
OR pct.multiple_occurences_flag = 'N'
))
)
OR ( pcv.business_group_id IS NULL
AND pcv.legislation_code IS NULL
AND NOT EXISTS(
SELECT 1
FROM pqp_configuration_values pcv3
WHERE pcv3.pcv_information_category =
pcv.pcv_information_category
AND pcv3.configuration_value_id <>
pcv.configuration_value_id
AND ( pcv3.business_group_id =
p_business_group_id
OR pcv3.legislation_code = c_legislation_code
)
AND ( ( pct.multiple_occurences_flag = 'Y'
AND ( ( pct.total_unique_columns = 1
AND pcv3.pcv_information1 =
pcv.pcv_information1
)
OR ( pct.total_unique_columns = 2
AND pcv3.pcv_information1 =
pcv.pcv_information1
AND pcv3.pcv_information2 =
pcv.pcv_information2
)
OR ( pct.total_unique_columns = 3
AND pcv3.pcv_information1 =
pcv.pcv_information1
AND pcv3.pcv_information2 =
pcv.pcv_information2
AND pcv3.pcv_information3 =
pcv.pcv_information3
)
OR ( pct.total_unique_columns = 4
AND pcv3.pcv_information1 =
pcv.pcv_information1
AND pcv3.pcv_information2 =
pcv.pcv_information2
AND pcv3.pcv_information3 =
pcv.pcv_information3
AND pcv3.pcv_information4 =
pcv.pcv_information4
)
OR ( pct.total_unique_columns = 5
AND pcv3.pcv_information1 =
pcv.pcv_information1
AND pcv3.pcv_information2 =
pcv.pcv_information2
AND pcv3.pcv_information3 =
pcv.pcv_information3
AND pcv3.pcv_information4 =
pcv.pcv_information4
AND pcv3.pcv_information5 =
pcv.pcv_information5
)
OR ( pct.total_unique_columns = 6
AND pcv3.pcv_information1 =
pcv.pcv_information1
AND pcv3.pcv_information2 =
pcv.pcv_information2
AND pcv3.pcv_information3 =
pcv.pcv_information3
AND pcv3.pcv_information4 =
pcv.pcv_information4
AND pcv3.pcv_information5 =
pcv.pcv_information5
AND pcv3.pcv_information6 =
pcv.pcv_information6
)
OR ( pct.total_unique_columns = 7
AND pcv3.pcv_information1 =
pcv.pcv_information1
AND pcv3.pcv_information2 =
pcv.pcv_information2
AND pcv3.pcv_information3 =
pcv.pcv_information3
AND pcv3.pcv_information4 =
pcv.pcv_information4
AND pcv3.pcv_information5 =
pcv.pcv_information5
AND pcv3.pcv_information6 =
pcv.pcv_information6
AND pcv3.pcv_information7 =
pcv.pcv_information7
)
OR ( pct.total_unique_columns = 8
AND pcv3.pcv_information1 =
pcv.pcv_information1
AND pcv3.pcv_information2 =
pcv.pcv_information2
AND pcv3.pcv_information3 =
pcv.pcv_information3
AND pcv3.pcv_information4 =
pcv.pcv_information4
AND pcv3.pcv_information5 =
pcv.pcv_information5
AND pcv3.pcv_information6 =
pcv.pcv_information6
AND pcv3.pcv_information7 =
pcv.pcv_information7
AND pcv3.pcv_information8 =
pcv.pcv_information8
)
OR ( pct.total_unique_columns = 9
AND pcv3.pcv_information1 =
pcv.pcv_information1
AND pcv3.pcv_information2 =
pcv.pcv_information2
AND pcv3.pcv_information3 =
pcv.pcv_information3
AND pcv3.pcv_information4 =
pcv.pcv_information4
AND pcv3.pcv_information5 =
pcv.pcv_information5
AND pcv3.pcv_information6 =
pcv.pcv_information6
AND pcv3.pcv_information7 =
pcv.pcv_information7
AND pcv3.pcv_information8 =
pcv.pcv_information8
AND pcv3.pcv_information9 =
pcv.pcv_information9
)
OR ( pct.total_unique_columns =
10
AND pcv3.pcv_information1 =
pcv.pcv_information1
AND pcv3.pcv_information2 =
pcv.pcv_information2
AND pcv3.pcv_information3 =
pcv.pcv_information3
AND pcv3.pcv_information4 =
pcv.pcv_information4
AND pcv3.pcv_information5 =
pcv.pcv_information5
AND pcv3.pcv_information6 =
pcv.pcv_information6
AND pcv3.pcv_information7 =
pcv.pcv_information7
AND pcv3.pcv_information8 =
pcv.pcv_information8
AND pcv3.pcv_information9 =
pcv.pcv_information9
AND pcv3.pcv_information10 =
pcv.pcv_information10
)
)
)
OR pct.multiple_occurences_flag = 'N'
))
)
)
AND ( pct.legislation_code = c_legislation_code
OR pct.legislation_code IS NULL
);
l_select VARCHAR2(1000);
l_select :=
'SELECT '
|| p_col_nam
|| ' FROM '
|| p_table
|| ' WHERE '
|| p_key_col
|| ' = '
|| p_key_val
|| p_where;
debug(l_select);
OPEN c_column FOR 'SELECT '
|| p_col_nam
|| ' FROM '
|| p_table
|| ' WHERE '
|| p_key_col
|| ' = '
|| p_key_val
|| p_where;
OPEN c_context FOR 'SELECT attribute_category FROM '
|| p_tab_name
|| ' WHERE '
|| p_key_col
|| '='
|| p_key_val;
OPEN c_rowid FOR ' SELECT rowid FROM '
|| p_tab_name
|| ' WHERE '
|| p_key_col
|| ' = '
|| p_key_val;
OPEN c_view FOR ' SELECT concatenated_segments
FROM '
|| p_view_name
|| ' WHERE row_id = '
|| ''''
|| l_rowid
|| '''';
OPEN c_value FOR ' SELECT '
|| l_col_name
|| ' FROM
pay_element_type_extra_info WHERE
information_type ='
|| ''''
|| p_flex_context
|| ''''
|| ' AND element_type_id ='
|| p_element_type_id;
SELECT element_type_extra_info_id
FROM pay_element_type_extra_info
WHERE element_type_id = p_element_type_id
AND information_type = p_information_type;
g_cached_tbls.DELETE(j);
delete_udt_value
(p_table_name => p_table_name
,p_column_name => p_column_name
,p_error_msg => l_err_msg
);
PROCEDURE delete_udt_value(
p_table_name IN VARCHAR2
,p_column_name IN VARCHAR2 DEFAULT 'ALL'
,p_error_msg OUT NOCOPY VARCHAR2
)
IS
l_proc_name VARCHAR2(60)
:= g_package_name || 'delete_udt_data';
g_udt_rec.DELETE(i);
END delete_udt_value;
SELECT tbls.user_table_id
FROM pay_user_tables tbls
WHERE tbls.user_table_name = p_udt_name
AND (
(business_group_id IS NULL AND legislation_code IS NULL)
OR (legislation_code IS NOT NULL AND legislation_code = 'GB')
OR (
business_group_id IS NOT NULL
AND business_group_id = p_business_group_id
)
);
SELECT user_column_id
,user_column_name
FROM pay_user_columns
WHERE user_table_id = p_user_table_id
AND user_column_name like p_user_column_name
AND (
(business_group_id IS NULL AND legislation_code IS NULL)
OR (legislation_code IS NOT NULL AND legislation_code = 'GB')
OR (
business_group_id IS NOT NULL
AND business_group_id = p_business_group_id
)
)
ORDER BY user_column_id;
SELECT user_row_id
,row_low_range_or_name
,row_high_range
FROM pay_user_rows_f
WHERE user_table_id = p_user_table_id
AND TRUNC(p_effective_date) BETWEEN effective_start_date
AND effective_end_date
AND (
(business_group_id IS NULL AND legislation_code IS NULL)
OR (legislation_code IS NOT NULL AND legislation_code = 'GB')
OR (
business_group_id IS NOT NULL
AND business_group_id = p_business_group_id
)
)
ORDER BY display_sequence;
SELECT VALUE
,effective_start_date
,effective_end_date
FROM pay_user_column_instances_f
WHERE user_column_id = p_user_column_id
AND user_row_id = p_user_row_id
AND (
(business_group_id IS NULL AND legislation_code IS NULL)
OR (legislation_code IS NOT NULL AND legislation_code = 'GB')
OR (
business_group_id IS NOT NULL
AND business_group_id = p_business_group_id
)
);
SELECT udt.range_or_match
FROM pay_user_tables udt
WHERE user_table_name = p_table_name;
PROCEDURE delete_formula(
p_formula_id IN NUMBER
,p_drop_compiled_info IN BOOLEAN
,p_error_code OUT NOCOPY NUMBER
,p_error_message OUT NOCOPY VARCHAR2
)
IS
CURSOR csr_package_names
IS
SELECT object_name
FROM user_objects
WHERE object_name LIKE 'FFP' || TO_CHAR(p_formula_id) || '_%'
AND object_type = 'PACKAGE';
:= g_package_name || 'delete_formula';
SELECT ROWID
INTO l_formula_rowid
FROM ff_formulas_f
WHERE formula_id = p_formula_id;
DELETE FROM ff_compiled_info_f
WHERE formula_id = p_formula_id;
DELETE FROM ff_fdi_usages_f
WHERE formula_id = p_formula_id;
ff_formulas_f_pkg.delete_row(
x_rowid => l_formula_rowid
,x_formula_id => p_formula_id
,x_dt_delete_mode => 'ZAP'
,x_validation_start_date => hr_api.g_sot
,x_validation_end_date => hr_api.g_eot
);
END delete_formula;
l_event_details(l_itr).update_type := l_proration_changes(l_itr);
t_event_details.DELETE;
t_event_details.DELETE;
t_proration_dates.DELETE;
t_proration_change_type.DELETE;
t_proration_dates.DELETE;
t_proration_change_type.DELETE;
l_temp_str := 'SELECT '|| p_column_name ||'
FROM pqp_configuration_values
WHERE ((business_group_id = ' ||p_business_group_id ||'
AND legislation_code IS NULL )
OR (business_group_id IS NULL
AND legislation_code =
'||''''||p_legislation_code ||''''||')
OR (business_group_id IS NULL
AND legislation_code IS NULL))
AND PCV_INFORMATION_CATEGORY =
'|| ''''||p_information_category ||''''||'
' ;
SELECT pei.element_type_extra_info_id
FROM pay_element_type_extra_info pei
WHERE pei.information_type = p_information_type
AND pei.element_type_id = p_element_type_id;
delete_udt_value --have a look at this procedure as well
(p_table_name => p_table_name
,p_error_msg => l_err_msg
);
g_hash_keys.DELETE;
'SELECT SEGMENT1';
l_query := 'SELECT '|| p_segment_column_name ||'
FROM '|| p_entity_name ||'
WHERE '||p_key_column_name||' = '|| p_key_column_value;
SELECT 'Y'
FROM pay_datetracked_events pde
,pay_dated_tables pdt
WHERE event_group_id = p_event_group_id
AND pde.dated_table_id = pdt.dated_table_id
AND (pdt.table_name = 'PAY_ELEMENT_ENTRIES_F'
OR
pdt.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
)
AND ROWNUM < 2;
SELECT element_set_id
FROM pay_event_group_usages
WHERE event_group_id = p_event_group_id;
SELECT distinct pee.element_entry_id
,pde.datetracked_event_id
FROM pay_element_set_members pes
,pay_element_entries_f pee
,pay_datetracked_events pde
WHERE pes.element_set_id = p_element_set_id
AND pee.element_type_id = pes.element_type_id
AND pee.assignment_id = p_assignment_id
AND pde.event_group_id = p_event_group_id
AND (
p_start_date BETWEEN pee.effective_start_date AND pee.effective_end_date
OR
p_end_date BETWEEN pee.effective_start_date AND pee.effective_end_date
OR
pee.effective_start_date BETWEEN p_start_date AND p_end_date
OR
pee.effective_end_date BETWEEN p_start_date AND p_end_date
);
SELECT datetracked_event_id
FROM pay_datetracked_events pde
,pay_dated_tables pdt
WHERE event_group_id = p_event_group_id
AND pde.dated_table_id = pdt.dated_table_id
AND pde.update_type = 'P'
AND pdt.table_name = 'PAY_ELEMENT_ENTRIES_F';
SELECT distinct ppe.surrogate_key
FROM pay_element_set_members pes
,pay_process_events ppe
,pay_event_updates peu
WHERE pes.element_set_id = p_element_set_id
AND ppe.assignment_id = p_assignment_id
AND ppe.noted_value = pes.element_type_id
AND peu.event_update_id = ppe.event_update_id
AND peu.event_type = 'ZAP'
AND ppe.effective_date BETWEEN p_start_date AND p_end_date;
p_element_entries_tab.element_entry_id.DELETE;
p_element_entries_tab.datetracked_event_id.DELETE;
p_element_entries_tab.element_entry_id.DELETE;
p_element_entries_tab.datetracked_event_id.DELETE;