[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT user_name, user_id
FROM fnd_user
WHERE user_name = upper(p_user_name);
l_last_updated_by number default null;
l_last_update_login number default null;
SELECT count(1)
INTO l_count
FROM per_all_people_f
WHERE person_id = p_employee_id;
l_last_updated_by := fnd_global.user_id;
IF l_last_updated_by IS NULL
THEN
l_last_updated_by := -1;
l_last_update_login := fnd_global.login_id;
IF l_last_update_login IS NULL
THEN
l_last_update_login := -1;
SELECT application_short_name
FROM fnd_application
WHERE application_id = c_app_id;
SELECT 1
FROM sys.dual
WHERE NOT EXISTS
(SELECT 1
FROM fnd_responsibility
WHERE responsibility_key = p_resp_key
AND application_id = p_resp_app_id);
SELECT 1
FROM sys.dual
WHERE NOT EXISTS
(SELECT 1
FROM fnd_responsibility_vl
WHERE responsibility_name = p_resp_name
AND application_id = p_resp_app_id);
SELECT data_group_id
FROM fnd_data_groups_standard_view
WHERE data_group_name = p_data_group_name;
SELECT menu_id
FROM fnd_menus
WHERE menu_name = p_menu_name;
SELECT request_group_id
FROM fnd_request_groups
WHERE request_group_name = p_request_group_name
AND application_id = p_request_group_app_id;
SELECT fnd_responsibility_s.nextval
FROM sys.dual;
SELECT user_id
FROM fnd_user
WHERE user_id = p_user_id;
SELECT responsibility_id
FROM fnd_responsibility
WHERE responsibility_id = p_responsibility_id;
SELECT application_id
FROM fnd_application
WHERE application_id = p_application_id;
SELECT user_id
FROM fnd_user_resp_groups
WHERE user_id = p_user_id
AND responsibility_application_id = p_application_id
AND responsibility_id = p_responsibility_id
AND security_group_id = p_sec_group_id;
' before fnd_user_resp_groups_api.insert_assignment', 30);
fnd_user_resp_groups_api.insert_assignment
(user_id => p_user_id
,responsibility_id => p_responsibility_id
,responsibility_application_id => p_application_id
,security_group_id => p_sec_group_id
,start_date => p_start_date
,end_date => p_end_date
,description => p_description
);
SELECT user_id
FROM fnd_user
WHERE user_id = p_user_id;
SELECT security_group_id
FROM fnd_security_groups
WHERE security_group_id = p_sec_group_id;
SELECT security_profile_id
,business_group_id
FROM per_security_profiles
WHERE security_profile_id = p_sec_profile_id;
SELECT responsibility_id
FROM fnd_responsibility
WHERE responsibility_key = p_resp_key
AND application_id = p_resp_app_id;
CURSOR lc_get_update_flag
IS
SELECT resp_update_allowed_flag
,user_update_allowed_flag
,sql_validation
FROM fnd_profile_options
WHERE profile_option_name = p_profile_opt_name;
l_resp_update_allowed_flag fnd_profile_options.resp_update_allowed_flag%type
default null;
l_user_update_allowed_flag fnd_profile_options.user_update_allowed_flag%type
default null;
OPEN lc_get_update_flag;
FETCH lc_get_update_flag INTO l_resp_update_allowed_flag
,l_user_update_allowed_flag
,l_sql_validation;
IF lc_get_update_flag%NOTFOUND THEN
CLOSE lc_get_update_flag;
CLOSE lc_get_update_flag;
l_resp_update_allowed_flag <> 'Y'
THEN
fnd_message.set_name('FND', 'PROFILES- CANT UPDATE');
l_user_update_allowed_flag <> 'Y'
THEN
fnd_message.set_name('FND', 'PROFILES- CANT UPDATE');
SELECT lookup_code
,meaning
FROM fnd_common_lookups
WHERE lookup_type = p_lookup_type;
SELECT lookup_code
,meaning
FROM fnd_lookups
WHERE lookup_type = p_lookup_type;
SELECT lookup_code
,meaning
FROM hr_lookups
WHERE lookup_type = p_lookup_type;
SELECT lookup_code
,meaning
FROM fnd_lookup_values
WHERE lookup_type = p_lookup_type;
SELECT bst.name booking_status
,bst.booking_status_type_id
,org.name org_name
FROM ota_booking_status_types bst
,hr_organization_units org
WHERE bst.business_group_id = org.organization_id
AND bst.type in ('A', 'P')
ORDER BY org.name;
SELECT name
,business_group_id
FROM per_business_groups;
SELECT initcap(NLS_LANGUAGE)
,language_code
FROM fnd_languages
ORDER BY 1;
SELECT s.security_profile_name
,s.security_profile_id
,s.business_group_id
,o.name
FROM per_security_profiles s
,hr_all_organization_units o
WHERE o.business_group_id = s.business_group_id
AND o.organization_id = o.business_group_id
ORDER BY s.security_profile_id;
SELECT pay.payroll_name
,pay.payroll_id
,per.name
FROM pay_payrolls_f pay
,per_business_groups per
WHERE pay.business_group_id = per.business_group_id
AND sysdate between effective_start_date and effective_end_date
ORDER BY pay.payroll_id;
SELECT BST.NAME visible_option_value , BST.BOOKING_STATUS_TYPE_ID profile_option_value
from ota_booking_status_types bst,
hr_all_organization_units org
where bst.business_group_id = org.organization_id
and bst.type = 'C' order by org.name, bst.name;
SELECT L.RESPONSIBILITY_NAME visible_option_value ,TO_CHAR(L.RESPONSIBILITY_ID)|| TO_CHAR(L.APPLICATION_ID) profile_option_value
FROM FND_RESPONSIBILITY_TL L,
FND_RESPONSIBILITY R
WHERE R.RESPONSIBILITY_ID = L.RESPONSIBILITY_ID
AND R.APPLICATION_ID = L.APPLICATION_ID
AND L.LANGUAGE = USERENV('LANG')
AND R.APPLICATION_ID = 805;
SELECT HAO.NAME visible_option_value,HAO.ORGANIZATION_ID profile_option_value
FROM HR_ALL_ORGANIZATION_UNITS HAO,
HR_ALL_ORGANIZATION_UNITS_TL HAOTL
WHERE HAO.ORGANIZATION_ID = HAOTL.ORGANIZATION_ID
AND HAOTL.LANGUAGE = USERENV('LANG')
AND SYSDATE BETWEEN HAO.DATE_FROM
AND NVL(HAO.DATE_TO,SYSDATE)
ORDER BY HAO.NAME;
SELECT NAME visible_option_value, BUSINESS_GROUP_ID profile_option_value
FROM PER_BUSINESS_GROUPS;
SELECT P.FULL_NAME visible_option_value,P.PERSON_ID profile_option_value
FROM PER_ALL_PEOPLE_F P,HR_ALL_ORGANIZATION_UNITS O,HR_ALL_ORGANIZATION_UNITS BG,
PER_ALL_ASSIGNMENTS_F A
WHERE P.PERSON_ID = A.PERSON_ID AND
O.ORGANIZATION_ID = A.ORGANIZATION_ID AND
O.BUSINESS_GROUP_ID = BG.ORGANIZATION_ID AND
A.PRIMARY_FLAG = 'Y' AND
(TRUNC(SYSDATE) BETWEEN
P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE) AND
(TRUNC(SYSDATE) BETWEEN
A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE);
SELECT ORG.NAME visible_option_value, ORG.ORGANIZATION_ID profile_option_value
FROM HR_ALL_ORGANIZATION_UNITS ORG,HR_ORGANIZATION_INFORMATION ORI,HR_ALL_ORGANIZATION_UNITS BG
WHERE ORG.ORGANIZATION_ID = ORI.ORGANIZATION_ID
AND ORG.BUSINESS_GROUP_ID = BG.ORGANIZATION_ID
AND ORI.ORG_INFORMATION_CONTEXT = 'CLASS'
AND ORI.ORG_INFORMATION1 ='OTA_TC'
AND ORI.ORG_INFORMATION2= 'Y';
SELECT PPF.PAYROLL_NAME visible_option_value ,PPF.PAYROLL_ID profile_option_value
FROM PAY_PAYROLLS_F PPF
WHERE SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND NVL( PPF.PAYROLL_TYPE, 'PAYROLL' ) <> 'BENEFIT'
ORDER BY PPF.PAYROLL_NAME;
SELECT OST.NAME visible_option_value,
OST.ORGANIZATION_STRUCTURE_ID profile_option_value
FROM PER_ORGANIZATION_STRUCTURES_V OST,
PER_BUSINESS_GROUPS ORG
WHERE OST.BUSINESS_GROUP_ID = ORG.BUSINESS_GROUP_ID
ORDER BY OST.NAME;
SELECT user_menu_name visible_option_value , menu_name profile_option_value
FROM fnd_menus_vl
ORDER BY user_menu_name;
SELECT user_menu_name visible_option_value , menu_name profile_option_value
FROM fnd_menus_vl
ORDER BY user_menu_name;
Select name , currency_code profile_option_value
FROM fnd_currencies_vl WHERE enabled_flag='Y';
SELECT
/*+ INDEX(tad OTA_ACTIVITY_DEFINITIONS_FK1) */
TAD.NAME visible_option_value ,TAD.ACTIVITY_ID profile_option_value
from ota_activity_definitions tad,
hr_all_organization_units org
where tad.business_group_id = org.organization_id
order by org.name,tad.name;
SELECT BST.NAME visible_option_value ,BST.BOOKING_STATUS_TYPE_ID profile_option_value
from ota_booking_status_types bst,
hr_all_organization_units org
where bst.business_group_id = org.organization_id
and bst.type in ('A') order by org.name,bst.name;
SELECT TERRITORY_SHORT_NAME visible_option_value, TERRITORY_CODE profile_option_value
FROM FND_TERRITORIES_VL
ORDER BY TERRITORY_SHORT_NAME;
SELECT DESCRIPTION visible_option_value, NLS_LANGUAGE profile_option_value
FROM FND_LANGUAGES_VL WHERE INSTALLED_FLAG IN ('B','I')
ORDER BY DESCRIPTION;
SELECT fpv.profile_option_id
,fpv.profile_option_value
,fp.profile_option_name
FROM fnd_profile_options fp
,fnd_profile_option_values fpv
WHERE fpv.profile_option_id = fp.profile_option_id
AND fpv.level_id = 10003
AND fpv.level_value = p_template_resp_id
AND fpv.level_value_application_id = p_template_resp_app_id;
SELECT application_id, responsibility_id
FROM fnd_responsibility
WHERE responsibility_key = p_resp_key;
SELECT action_id, rule_type
FROM fnd_resp_functions
WHERE application_id = p_app_id
AND responsibility_id = p_resp_id;
SELECT function_name
FROM fnd_form_functions
WHERE function_id = p_func_id;
SELECT menu_name
FROM fnd_menus
WHERE menu_id = p_menu_id;
l_out_func_sec_excl_tbl(l_index).delete_flag := 'N';
l_out_func_sec_excl_tbl(l_index).delete_flag := 'N';
,p_delete_flag in varchar2 default 'N')
IS
--
CURSOR lc_get_resp_id
IS
SELECT responsibility_id
FROM fnd_responsibility
WHERE responsibility_key = p_resp_key;
SELECT function_id
FROM fnd_form_functions
WHERE function_name = p_rule_name;
SELECT menu_id
FROM fnd_menus
WHERE menu_name = p_rule_name;
,delete_flag => p_delete_flag);
PROCEDURE update_fnd_user
(p_user_id in number
,p_old_password in varchar2 default hr_api.g_varchar2
,p_new_password in varchar2 default hr_api.g_varchar2
,p_end_date in date default hr_api.g_date
,p_email_address in varchar2 default hr_api.g_varchar2
,p_fax in varchar2 default hr_api.g_varchar2
,p_known_as in varchar2 default hr_api.g_varchar2
,p_language in varchar2 default hr_api.g_varchar2
,p_host_port in varchar2 default hr_api.g_varchar2
,p_employee_id in number default hr_api.g_number
,p_customer_id in number default hr_api.g_number
,p_supplier_id in number default hr_api.g_number
) IS
--
CURSOR lc_get_user_data
IS
SELECT *
FROM fnd_user
WHERE user_id = p_user_Id;
l_proc varchar2(72) := g_package||'update_fnd_user';
l_last_updated_by number default null;
l_last_update_login number default null;
SELECT count(1)
INTO l_count
FROM per_all_people_f
WHERE person_id = p_employee_id;
l_last_updated_by := fnd_global.user_id;
IF l_last_updated_by IS NULL
THEN
l_last_updated_by := -1;
l_last_update_login := fnd_global.login_id;
IF l_last_update_login IS NULL
THEN
l_last_update_login := -1;
hr_utility.set_location (l_proc || ' before fnd_user_pkg.UpdateUser', 30);
select user_name into l_user_name from fnd_user
where user_id = p_user_id;
fnd_user_pkg.UpdateUser (
x_user_name => l_user_name,
x_owner => '',
x_unencrypted_password =>l_new_password,
x_description => l_description,
-- x_last_logon_date => sysdate, -- for BUG 7116804
x_end_date => l_end_date,
x_employee_id => l_employee_id,
x_email_address => l_email_address,
x_fax => l_fax,
x_customer_id => l_customer_id,
x_supplier_id => l_supplier_id
);
END update_fnd_user;
PROCEDURE update_fnd_user_resp_groups
(p_user_id in number
,p_responsibility_id in number
,p_resp_application_id in number
,p_security_group_id in fnd_user_resp_groups.security_group_id%type
,p_start_date in date default hr_api.g_date
,p_end_date in date default hr_api.g_date
,p_description in varchar2 default hr_api.g_varchar2
) IS
--
CURSOR lc_get_user_id IS
SELECT user_id
FROM fnd_user
WHERE user_id = p_user_id;
SELECT responsibility_id
,responsibility_key
FROM fnd_responsibility
WHERE responsibility_id = p_responsibility_id;
SELECT application_id
FROM fnd_application
WHERE application_id = p_resp_application_id;
SELECT count(*)
FROM fnd_user_resp_groups
WHERE user_id = p_user_id
AND responsibility_id = p_responsibility_id
AND responsibility_application_id = p_resp_application_id
AND security_group_id = p_security_group_id;
SELECT responsibility_application_id
,responsibility_id
,start_date
,end_date
,description
FROM fnd_user_resp_groups
WHERE user_id = p_user_id
AND responsibility_id = p_responsibility_id
AND responsibility_application_id = p_resp_application_id
AND security_group_id = p_security_group_id;
SELECT responsibility_application_id
,responsibility_id
,start_date
,end_date
,description
FROM fnd_user_resp_groups_direct
WHERE user_id = p_user_id
AND responsibility_id = p_responsibility_id
AND responsibility_application_id = p_resp_application_id
AND security_group_id = p_security_group_id;
l_proc varchar2(72) := g_package||'update_fnd_user_resp_groups';
' before fnd_user_resp_groups_api.update_assignment', 30);
fnd_user_resp_groups_api.update_assignment
(user_id => p_user_id
,responsibility_id => p_responsibility_id
,responsibility_application_id => p_resp_application_id
,security_group_id =>p_security_group_id -- Fix 2978610
,start_date => l_start_date
,end_date => l_end_date
,description => l_description
);
END update_fnd_user_resp_groups;
PROCEDURE update_sec_profile_asg
(p_sec_profile_asg_id in
per_sec_profile_assignments.sec_profile_assignment_id%type default null
,p_user_id in fnd_user.user_id%type default null
,p_responsibility_id in per_sec_profile_assignments.responsibility_id%type
default null
,p_resp_app_id in
per_sec_profile_assignments.responsibility_application_id%type default null
,p_security_group_id in fnd_user_resp_groups.security_group_id%type
default null
,p_start_date in per_sec_profile_assignments.start_date%type
default null
,p_end_date in per_sec_profile_assignments.end_date%type
default null
,p_object_version_number in
per_sec_profile_assignments.object_version_number%type default null
) IS
--
--
CURSOR lc_get_user_id IS
SELECT user_id
FROM fnd_user
WHERE user_id = p_user_id;
SELECT sec_profile_assignment_id
,security_group_id
,business_group_id
FROM per_sec_profile_assignments
WHERE user_id = p_user_id
AND responsibility_id = p_responsibility_id
AND responsibility_application_id = p_resp_app_id
AND security_group_id = p_security_group_id;
SELECT responsibility_key
FROM fnd_responsibility
WHERE responsibility_id = p_responsibility_id
AND application_id = p_resp_app_id;
l_proc varchar2(72) := g_package|| 'update_sec_profile_asg';
END update_sec_profile_asg;