[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Y'
FROM user_views
WHERE view_name = UPPER(X_VIEW_NAME);
SELECT 'Y',SUBSTR(X_PRODUCT_CODES,p_start,3)
FROM hr_legislation_installations
WHERE legislation_code = X_LEGISLATION_CODE
AND application_short_name = SUBSTR(X_PRODUCT_CODES,p_start,3);
SELECT user_table_id
FROM pay_user_tables
WHERE user_table_name = X_USER_TABLE_NAME
AND business_group_id IS NOT NULL;
INSERT INTO hr_stu_exceptions(TABLE_NAME,SURROGATE_ID, EXCEPTION_TEXT,TRUE_KEY)
VALUES(X_USER_TABLE_NAME,l_user_table_id,'User Table: '|| X_USER_TABLE_NAME ||' already exists at BG level.',NULL);
hr_startup_data_api_support.delete_owner_definitions;
SELECT user_table_id
,object_version_number
INTO l_user_table_id
,l_object_version_number
FROM pay_user_tables
WHERE user_table_name = X_USER_TABLE_NAME
AND (
legislation_code = X_LEGISLATION_CODE
OR
legislation_code IS NULL
)
AND business_group_id IS NULL;
pay_user_table_api.update_user_table
(p_validate => FALSE
,p_user_table_id => l_user_table_id
,p_effective_date => SYSDATE
,p_user_table_name => X_USER_TABLE_NAME
,p_user_row_title => X_USER_ROW_TITLE
,p_object_version_number => l_object_version_number
);
SELECT user_table_id, user_key_units
FROM pay_user_tables
WHERE user_table_name = X_USER_TABLE_NAME
AND (
legislation_code = X_LEGISLATION_CODE
OR
legislation_code IS NULL
)
AND business_group_id IS NULL;
SELECT val.user_column_instance_id column_instance_id,
val.object_version_number object_version_number
FROM pay_user_tables put,
pay_user_rows_f pur,
pay_user_columns puc,
pay_user_column_instances_f val
WHERE val.user_row_id = pur.user_row_id
AND val.user_row_id = p_user_row_id
AND val.user_column_id = puc.user_column_id
AND pur.user_table_id = put.user_table_id
AND puc.user_table_id = put.user_table_id
-- bug 9234524 convert to user key units when matching
and decode
(put.user_key_units,
'D', to_char(fnd_date.canonical_to_date(X_ROW_LOW_RANGE_OR_NAME)),
'N', to_char(fnd_number.canonical_to_number(X_ROW_LOW_RANGE_OR_NAME)),
'T', upper (X_ROW_LOW_RANGE_OR_NAME),
null) =
decode
(put.user_key_units,
'D', to_char(fnd_date.canonical_to_date(pur.row_low_range_or_name)),
'N', to_char(fnd_number.canonical_to_number(pur.row_low_range_or_name)),
'T', upper (pur.row_low_range_or_name),
null)
and ( NVL(pur.row_high_range,'NULL') = NVL(X_ROW_HIGH_RANGE,'NULL')
OR decode
(put.user_key_units,
'D', to_char(fnd_date.canonical_to_date(X_ROW_HIGH_RANGE)),
'N', to_char(fnd_number.canonical_to_number(X_ROW_HIGH_RANGE)),
'T', upper (X_ROW_HIGH_RANGE),
null) =
decode
(put.user_key_units,
'D', to_char(fnd_date.canonical_to_date(pur.row_high_range)),
'N', to_char(fnd_number.canonical_to_number(pur.row_high_range)),
'T', upper (pur.row_high_range),
null))
-- end bug 9234524
AND put.user_table_name = X_USER_TABLE_NAME
AND(
(
put.legislation_code IS NULL
AND val.legislation_code IS NULL
AND pur.legislation_code IS NULL
)
OR
(
put.legislation_code = X_LEGISLATION_CODE
-- AND val.legislation_code = X_LEGISLATION_CODE /* Bug 16484589 */
AND pur.legislation_code = X_LEGISLATION_CODE
)
)
AND fnd_date.canonical_to_date(X_EFFECTIVE_END_DATE) < val.effective_end_date;
SELECT user_row_id
,object_version_number
,effective_start_date
,effective_end_date
INTO l_user_row_id
,l_object_version_number
,l_effective_start_date
,l_effective_end_date
FROM pay_user_rows_f pur
-- bug 9234524 convert to user key units when matching
WHERE decode
(l_user_key_units,
'D', to_char(fnd_date.canonical_to_date(X_ROW_LOW_RANGE_OR_NAME)),
'N', to_char(fnd_number.canonical_to_number(X_ROW_LOW_RANGE_OR_NAME)),
'T', upper (X_ROW_LOW_RANGE_OR_NAME),
null) =
decode
(l_user_key_units,
'D', to_char(fnd_date.canonical_to_date(pur.row_low_range_or_name)),
'N', to_char(fnd_number.canonical_to_number(pur.row_low_range_or_name)),
'T', upper (pur.row_low_range_or_name),
null)
and ( NVL(pur.row_high_range,'NULL') = NVL(X_ROW_HIGH_RANGE,'NULL')
OR decode
(l_user_key_units,
'D', to_char(fnd_date.canonical_to_date(X_ROW_HIGH_RANGE)),
'N', to_char(fnd_number.canonical_to_number(X_ROW_HIGH_RANGE)),
'T', upper (X_ROW_HIGH_RANGE),
null) =
decode
(l_user_key_units,
'D', to_char(fnd_date.canonical_to_date(pur.row_high_range)),
'N', to_char(fnd_number.canonical_to_number(pur.row_high_range)),
'T', upper (pur.row_high_range),
null))
-- end bug 9234524 changes
AND (
legislation_code = X_LEGISLATION_CODE
OR
legislation_code IS NULL
)
AND business_group_id IS NULL
AND user_table_id = l_user_table_id
AND fnd_date.canonical_to_date(X_EFFECTIVE_START_DATE) BETWEEN effective_start_date AND effective_end_date;
pay_user_row_api.update_user_row
(p_validate => FALSE
,p_effective_date => fnd_date.canonical_to_date(X_EFFECTIVE_START_DATE)
,p_datetrack_update_mode => hr_api.g_update
,p_user_row_id => l_user_row_id
,p_display_sequence => l_display_sequence
,p_object_version_number => l_object_version_number
,p_row_low_range_or_name => X_ROW_LOW_RANGE_OR_NAME
,p_base_row_low_range_or_name => X_ROW_LOW_RANGE_OR_NAME
,p_disable_range_overlap_check => TRUE
,p_disable_units_check => FALSE
,p_row_high_range => X_ROW_HIGH_RANGE
,p_effective_start_date => l_start_date
,p_effective_end_date => l_end_date
);
else l_datetrack_mode := hr_api.g_delete;
pay_user_column_instance_api.delete_user_column_instance
(p_validate => FALSE
,p_effective_date => l_effective_date
,p_user_column_instance_id => c_rec.column_instance_id
,p_datetrack_update_mode => l_datetrack_mode
,p_object_version_number => c_rec.object_version_number
,p_effective_start_date => l_start_date
,p_effective_end_date => l_end_date
);
pay_user_row_api.delete_user_row
(p_validate => FALSE
,p_effective_date => l_effective_date
,p_datetrack_update_mode => l_datetrack_mode
,p_user_row_id => l_user_row_id
,p_object_version_number => l_object_version_number
,p_disable_range_overlap_check => FALSE
,p_effective_start_date => l_start_date
,p_effective_end_date => l_end_date
);
pay_user_row_api.delete_user_row
(p_validate => FALSE
,p_effective_date => fnd_date.canonical_to_date(X_EFFECTIVE_END_DATE)
,p_datetrack_update_mode => hr_api.g_delete
,p_user_row_id => l_user_row_id
,p_object_version_number => l_object_version_number
,p_disable_range_overlap_check => FALSE
,p_effective_start_date => l_start_date
,p_effective_end_date => l_end_date
);
SELECT user_table_id
FROM pay_user_tables
WHERE user_table_name = X_USER_TABLE_NAME
AND (
legislation_code = X_LEGISLATION_CODE
OR
legislation_code IS NULL
);
SELECT ff.formula_id
FROM ff_formula_types fft
,ff_formulas_f ff
WHERE fft.formula_type_name = 'User Table Validation'
AND fft.formula_type_id = ff.formula_type_id
AND ff.formula_name = X_FORMULA_NAME
AND (
X_FORMULA_LEG_CODE IS NULL
OR
ff.legislation_code = X_FORMULA_LEG_CODE
);
SELECT user_column_id
,object_version_number
INTO l_user_column_id
,l_object_version_number
FROM pay_user_columns
WHERE user_column_name = X_USER_COLUMN_NAME
AND (
legislation_code = X_LEGISLATION_CODE
OR
legislation_code IS NULL
)
AND user_table_id = l_user_table_id
AND business_group_id IS NULL;
pay_user_column_api.update_user_column
(p_validate => FALSE
,p_user_column_id => l_user_column_id
,p_user_column_name => X_USER_COLUMN_NAME
,p_formula_id => l_formula_id
,p_object_version_number => l_object_version_number
,p_formula_warning => l_warning
);
SELECT pur.user_row_id,
puc.user_column_id,
pur.effective_start_date
FROM pay_user_tables put,
pay_user_rows_f pur,
pay_user_columns puc
WHERE pur.user_table_id = put.user_table_id
AND puc.user_table_id = put.user_table_id
-- bug 9234524 convert to user key units when matching
AND decode
(put.user_key_units,
'D', to_char(fnd_date.canonical_to_date(X_ROW_LOW_RANGE_OR_NAME)),
'N', to_char(fnd_number.canonical_to_number(X_ROW_LOW_RANGE_OR_NAME)),
'T', upper (X_ROW_LOW_RANGE_OR_NAME),
null) =
decode
(put.user_key_units,
'D', to_char(fnd_date.canonical_to_date(pur.row_low_range_or_name)),
'N', to_char(fnd_number.canonical_to_number(pur.row_low_range_or_name)),
'T', upper (pur.row_low_range_or_name),
null)
AND ( NVL(pur.row_high_range,'NULL') = NVL(X_ROW_HIGH_RANGE,'NULL')
OR decode
(put.user_key_units,
'D', to_char(fnd_date.canonical_to_date(X_ROW_HIGH_RANGE)),
'N', to_char(fnd_number.canonical_to_number(X_ROW_HIGH_RANGE)),
'T', upper (X_ROW_HIGH_RANGE),
null) =
decode
(put.user_key_units,
'D', to_char(fnd_date.canonical_to_date(pur.row_high_range)),
'N', to_char(fnd_number.canonical_to_number(pur.row_high_range)),
'T', upper (pur.row_high_range),
null))
-- bug 9234524 changes end
AND puc.user_column_name = X_USER_COLUMN_NAME
AND put.user_table_name = X_USER_TABLE_NAME
AND put.legislation_code = X_LEGISLATION_CODE
AND pur.legislation_code = X_LEGISLATION_CODE
AND puc.legislation_code = X_LEGISLATION_CODE
AND fnd_date.canonical_to_date(X_EFFECTIVE_START_DATE)
BETWEEN pur.effective_start_date AND pur.effective_end_date ;
SELECT put.user_table_id,
pur.user_row_id,
puc.user_column_id,
val.user_column_instance_id,
val.object_version_number,
val.effective_start_date,
val.effective_end_date
INTO l_user_table_id,
l_user_row_id,
l_user_column_id,
l_user_col_instance_id,
l_object_version_number,
l_effective_start_date,
l_effective_end_date
FROM pay_user_tables put,
pay_user_rows_f pur,
pay_user_columns puc,
pay_user_column_instances_f val
WHERE val.user_row_id = pur.user_row_id
AND val.user_column_id = puc.user_column_id
AND pur.user_table_id = put.user_table_id
AND puc.user_column_name = X_USER_COLUMN_NAME
-- bug 9234524 convert to user key units when matching
AND decode
(put.user_key_units,
'D', to_char(fnd_date.canonical_to_date(X_ROW_LOW_RANGE_OR_NAME)),
'N', to_char(fnd_number.canonical_to_number(X_ROW_LOW_RANGE_OR_NAME)),
'T', upper (X_ROW_LOW_RANGE_OR_NAME),
null) =
decode
(put.user_key_units,
'D', to_char(fnd_date.canonical_to_date(pur.row_low_range_or_name)),
'N', to_char(fnd_number.canonical_to_number(pur.row_low_range_or_name)),
'T', upper (pur.row_low_range_or_name),
null)
AND ( NVL(pur.row_high_range,'NULL') = NVL(X_ROW_HIGH_RANGE,'NULL')
OR decode
(put.user_key_units,
'D', to_char(fnd_date.canonical_to_date(X_ROW_HIGH_RANGE)),
'N', to_char(fnd_number.canonical_to_number(X_ROW_HIGH_RANGE)),
'T', upper (X_ROW_HIGH_RANGE),
null) =
decode
(put.user_key_units,
'D', to_char(fnd_date.canonical_to_date(pur.row_high_range)),
'N', to_char(fnd_number.canonical_to_number(pur.row_high_range)),
'T', upper (pur.row_high_range),
null))
-- bug 9234524 changes end
AND put.user_table_name = X_USER_TABLE_NAME
AND(
(
put.legislation_code IS NULL
AND val.legislation_code IS NULL
AND pur.legislation_code IS NULL
)
OR
(
put.legislation_code = X_LEGISLATION_CODE
AND val.legislation_code = X_LEGISLATION_CODE
AND pur.legislation_code = X_LEGISLATION_CODE
)
)
AND fnd_date.canonical_to_date(X_EFFECTIVE_START_DATE)
BETWEEN val.effective_start_date AND val.effective_end_date
AND fnd_date.canonical_to_date(X_EFFECTIVE_START_DATE)
BETWEEN pur.effective_start_date AND pur.effective_end_date;
pay_user_column_instance_api.update_user_column_instance
(p_validate => FALSE
,p_effective_date => fnd_date.canonical_to_date(X_EFFECTIVE_START_DATE)
,p_user_column_instance_id => l_user_col_instance_id
,p_datetrack_update_mode => hr_api.g_update
,p_value => X_VALUE
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_start_date
,p_effective_end_date => l_end_date
);
else l_datetrack_mode := hr_api.g_delete;
pay_user_column_instance_api.delete_user_column_instance
(p_validate => FALSE
,p_effective_date => l_effective_date
,p_user_column_instance_id => l_user_col_instance_id
,p_datetrack_update_mode => l_datetrack_mode
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_start_date
,p_effective_end_date => l_end_date
);
pay_user_column_instance_api.delete_user_column_instance
(p_validate => FALSE
,p_effective_date => fnd_date.canonical_to_date(X_EFFECTIVE_END_DATE)
,p_user_column_instance_id => l_user_col_instance_id
,p_datetrack_update_mode => hr_api.g_delete
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_start_date
,p_effective_end_date => l_end_date
);