[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_update_user_sql VARCHAR2(2000) := NULL;
SELECT table_name
FROM all_tables
WHERE owner = upper(p_schema_name)
AND table_name = 'MDP_MATRIX';
SELECT to_char(user_id)
FROM fnd_user
WHERE user_name = p_user_name;
OR eventName = 'oracle.apps.fnd.wf.ds.userRole.updated'
OR eventName = 'oracle.apps.fnd.user.delete'
OR eventName = 'oracle.apps.fnd.wf.ds.user.updated'
OR eventName = 'oracle.apps.fnd.wf.ds.user.nameChanged')
THEN
x_curr_name := p_event.getValueForParameter('USER_NAME');
' select fu.user_name, decode(sign(fu.end_date - sysdate),-1,2,1),
(SELECT sum(a) FROM (SELECT 1 a
FROM dual
WHERE EXISTS ( SELECT 1
FROM fnd_user_resp_groups_all fug,
fnd_responsibility fr,
fnd_menu_entries fme,
fnd_form_functions fff
WHERE
fug.user_id = :user_id1
AND fug.responsibility_application_id = 722
AND decode(sign(fug.end_date - sysdate), -1, 2, 1) = 1
AND fr.application_id = 722
AND fr.responsibility_id = fug.responsibility_id
AND fme.menu_id = fr.menu_id
AND fme.grant_flag = ''Y''
AND fme.sub_menu_id IS NULL
AND fff.function_id = fme.function_id
AND fff.function_name = ''MSD_DEM_DEMPLANR'')
OR EXISTS ( SELECT 1
FROM fnd_user_resp_groups_all fug,
fnd_responsibility fr,
fnd_menu_entries fme,
fnd_menu_entries sub_fme,
fnd_form_functions fff
WHERE
fug.user_id = :user_id2
AND fug.responsibility_application_id = 724
and decode(sign(fug.end_date - sysdate), -1, 2, 1) = 1
AND fr.application_id = 724
AND fr.responsibility_id = fug.responsibility_id
AND fme.menu_id = fr.menu_id
AND fme.grant_flag = ''Y''
AND fme.sub_menu_id is not null
AND fme.sub_menu_id = sub_fme.menu_id
AND fff.function_id = sub_fme.function_id
AND fff.function_name = ''MSD_SPF_SPFPLANR'' )
UNION ALL
SELECT 2 a
FROM dual
WHERE EXISTS ( SELECT 2
FROM fnd_user_resp_groups_all fug,
fnd_responsibility fr,
fnd_menu_entries fme,
fnd_form_functions fff
WHERE
fug.user_id = :user_id3
AND fug.responsibility_application_id = 722
AND decode(sign(fug.end_date - sysdate), -1, 2, 1) = 1
AND fr.application_id = 722
AND fr.responsibility_id = fug.responsibility_id
AND fme.menu_id = fr.menu_id
AND fme.grant_flag = ''Y''
AND fme.sub_menu_id IS NULL
AND fff.function_id = fme.function_id
AND fff.function_name = ''MSD_DEM_WF_MGR'')
OR EXISTS ( SELECT 2
FROM fnd_user_resp_groups_all fug,
fnd_responsibility fr,
fnd_menu_entries fme,
fnd_menu_entries sub_fme,
fnd_form_functions fff
WHERE
fug.user_id = :user_id4
AND fug.responsibility_application_id = 724
and decode(sign(fug.end_date - sysdate), -1, 2, 1) = 1
AND fr.application_id = 724
AND fr.responsibility_id = fug.responsibility_id
AND fme.menu_id = fr.menu_id
AND fme.grant_flag = ''Y''
AND fme.sub_menu_id is not null
AND fme.sub_menu_id = sub_fme.menu_id
AND fff.function_id = sub_fme.function_id
AND fff.function_name = ''MSD_SPF_WF_MGR''))) user_type,
( select first_name
from per_all_people_f
where ((person_id = fu.employee_id
and fu.employee_id is not null)
or
(party_id = fu.person_party_id
and fu.person_party_id is not null)
or (party_id = fu.supplier_id
and fu.supplier_id is not null)
or (party_id = fu.customer_id
and fu.customer_id is not null))
and rownum <2) first_name,
( select last_name
from per_all_people_f
where ((person_id = fu.employee_id
and fu.employee_id is not null)
or
(party_id = fu.person_party_id
and fu.person_party_id is not null)
or (party_id = fu.supplier_id
and fu.supplier_id is not null)
or (party_id = fu.customer_id
and fu.customer_id is not null))
and rownum <2) last_name,
( select name
from hr_all_organization_units
where business_group_id in
(select pap.business_group_id
from per_all_people_f pap
where (pap.person_id = fu.employee_id
and fu.employee_id is not null)
or
(pap.party_id = fu.person_party_id
and fu.person_party_id is not null)
or (pap.party_id = fu.supplier_id
and fu.supplier_id is not null)
or (pap.party_id = fu.customer_id
and fu.customer_id is not null))
and rownum < 2) company,
( select work_telephone
from per_all_people_f
where ((person_id = fu.employee_id
and fu.employee_id is not null)
or
(party_id = fu.person_party_id
and fu.person_party_id is not null)
or (party_id = fu.supplier_id
and fu.supplier_id is not null)
or (party_id = fu.customer_id
and fu.customer_id is not null))
and rownum <2) phone_num,
fu.fax,
decode(fu.email_address,
null,
(select pap.email_address
from per_all_people_f pap
where ((person_id = fu.employee_id
and fu.employee_id is not null)
or
(party_id = fu.person_party_id
and fu.person_party_id is not null)
or (party_id = fu.supplier_id
and fu.supplier_id is not null)
or (party_id = fu.customer_id
and fu.customer_id is not null))
and pap.email_address is not null
and rownum <2),
fu.email_address) email_address,
1 product
from fnd_user fu
where fu.user_id = :userid5';
IF (eventName = 'oracle.apps.fnd.user.delete')
THEN
p_user_name := x_curr_name;
IF (eventName = 'oracle.apps.fnd.user.delete')
THEN
NULL;
ELSIF ( eventName = 'oracle.apps.fnd.wf.ds.userRole.updated'
OR eventName = 'oracle.apps.fnd.wf.ds.user.updated')
THEN
eventName := 'oracle.apps.fnd.user.resp.delete';
/* UPDATE USER */
x_update_user_sql := 'BEGIN ' || x_schema || '.API_CREATE_ORA_DEM_USER ( ' ||
' ''' || p_user_name || ''' , ' ||
' null , ' || -- Bug#14524761
' ''' || x_user_permission || ''' , ' ||
' ''' || p_user_fname || ''' , ' ||
' ''' || p_user_lname || ''' , ' ||
' ''' || p_user_org_name || ''' , ' ||
' ''' || p_user_wrkphone || ''' , ' ||
' ''' || p_user_fax || ''' , ' ||
' ''' || p_user_email || ''' , ' ||
' ''0'' , ' || -- ' ''' || p_user_language || ''' , ' || --commenting out MLS nallkuma
' null, ' ||
' ''' || x_component_name || ''' , ' ||
' null, ' ||
' ''UPDATE''); END;';
DuserQuery := 'select count(user_name) from ' || x_schema || '.user_id where user_name = '''||p_user_name||'''';
DOlduserQuery := 'select count(user_name) from ' || x_schema || '.user_id where user_name = '''||x_old_name||'''';
/* insert/Update responsibility for user */
If ( eventName = 'oracle.apps.fnd.wf.ds.userRole.created'
OR eventName = 'oracle.apps.fnd.wf.ds.userRole.updated') THEN
/* User does not exist in Demantra... Add the user */
If Duser_cnt = 0 Then
/* invoke API_CREATE_ORA_DEM_USER.(ADD) */
log_debug ('Insert/Update Responsibility - Creating User');
/* User exists in Demantra, Update the responsibility */
Elsif Duser_cnt > 0 Then
/* invoke API_CREATE_ORA_DEM_USER (UPDATE) */
log_debug ('Insert/Update Responsibility - Updating User');
log_debug (x_update_user_sql);
EXECUTE IMMEDIATE x_update_user_sql;
/* Update Existing User */
Elsif (eventName = 'oracle.apps.fnd.wf.ds.user.updated') THEN
/* Effective date has been disabled OR DM responsibilities have been disabled for user....delete user */
If (p_user_valid = 2 OR p_user_resp = 0) Then
/* invoke API_DROP_ORA_DEM_USER(user name) */
log_debug ('User Update - Deleting User');
log_debug ('User Update - Creating User');
/* User exists in Demantra and effective date is enabled....Update user */
Else
/* invoke API_CREATE_ORA_DEM_USER (UPDATE) */
log_debug('User Update - Updating User');
log_debug (x_update_user_sql);
EXECUTE IMMEDIATE x_update_user_sql;
/* Delete existing User */
Elsif (eventName = 'oracle.apps.fnd.user.delete') THEN
/* invoke API_DROP_ORA_DEM_USER(user name) */
log_debug('User Delete - Deleting User');
/* Delete responsibility */
Elsif (eventName = 'oracle.apps.fnd.user.resp.delete') THEN
/* User exists in Demantra */
If (Duser_cnt > 0 AND x_user_permission IS NOT NULL) Then
/* invoke API_CREATE_ORA_DEM_USER (UPDATE) */
log_debug ('Responsibility Delete - Updating user');
log_debug (x_update_user_sql);
EXECUTE IMMEDIATE x_update_user_sql;
log_debug('Responsibility Delete - Deleting user');
x_update_user_sql VARCHAR2(2000) := NULL;
SELECT to_char(user_id)
FROM fnd_user
WHERE user_name = p_user_name;
OR eventName = 'oracle.apps.fnd.wf.ds.userRole.updated'
OR eventName = 'oracle.apps.fnd.user.delete'
OR eventName = 'oracle.apps.fnd.wf.ds.user.updated'
OR eventName = 'oracle.apps.fnd.wf.ds.user.nameChanged')
THEN
x_curr_name := p_event.getValueForParameter('USER_NAME');
' select fu.user_name, decode(sign(fu.end_date - sysdate),-1,2,1),
(SELECT sum(a) FROM (SELECT 1 a
FROM dual
WHERE EXISTS ( SELECT 1
FROM fnd_user_resp_groups_all fug,
fnd_responsibility fr,
fnd_menu_entries fme,
fnd_form_functions fff
WHERE
fug.user_id = :user_id1
AND fug.responsibility_application_id = 722
AND decode(sign(fug.end_date - sysdate), -1, 2, 1) = 1
AND fr.application_id = 722
AND fr.responsibility_id = fug.responsibility_id
AND fme.menu_id = fr.menu_id
AND fme.grant_flag = ''Y''
AND fme.sub_menu_id IS NULL
AND fff.function_id = fme.function_id
AND fff.function_name = ''MSD_DEM_SOP_SOPPLANR'')
UNION ALL
SELECT 2 a
FROM dual
WHERE EXISTS ( SELECT 1
FROM fnd_user_resp_groups_all fug,
fnd_responsibility fr,
fnd_menu_entries fme,
fnd_form_functions fff
WHERE
fug.user_id = :user_id2
AND fug.responsibility_application_id = 722
AND decode(sign(fug.end_date - sysdate), -1, 2, 1) = 1
AND fr.application_id = 722
AND fr.responsibility_id = fug.responsibility_id
AND fme.menu_id = fr.menu_id
AND fme.grant_flag = ''Y''
AND fme.sub_menu_id IS NULL
AND fff.function_id = fme.function_id
AND fff.function_name = ''MSD_DEM_SOP_WF_MGR''))) user_type,
( select first_name
from per_all_people_f
where ((person_id = fu.employee_id
and fu.employee_id is not null)
or
(party_id = fu.person_party_id
and fu.person_party_id is not null)
or (party_id = fu.supplier_id
and fu.supplier_id is not null)
or (party_id = fu.customer_id
and fu.customer_id is not null))
and rownum <2) first_name,
( select last_name
from per_all_people_f
where ((person_id = fu.employee_id
and fu.employee_id is not null)
or
(party_id = fu.person_party_id
and fu.person_party_id is not null)
or (party_id = fu.supplier_id
and fu.supplier_id is not null)
or (party_id = fu.customer_id
and fu.customer_id is not null))
and rownum <2) last_name,
( select name
from hr_all_organization_units
where business_group_id in
(select pap.business_group_id
from per_all_people_f pap
where (pap.person_id = fu.employee_id
and fu.employee_id is not null)
or
(pap.party_id = fu.person_party_id
and fu.person_party_id is not null)
or (pap.party_id = fu.supplier_id
and fu.supplier_id is not null)
or (pap.party_id = fu.customer_id
and fu.customer_id is not null))
and rownum < 2) company,
( select work_telephone
from per_all_people_f
where ((person_id = fu.employee_id
and fu.employee_id is not null)
or
(party_id = fu.person_party_id
and fu.person_party_id is not null)
or (party_id = fu.supplier_id
and fu.supplier_id is not null)
or (party_id = fu.customer_id
and fu.customer_id is not null))
and rownum <2) phone_num,
fu.fax,
decode(fu.email_address,
null,
(select pap.email_address
from per_all_people_f pap
where ((person_id = fu.employee_id
and fu.employee_id is not null)
or
(party_id = fu.person_party_id
and fu.person_party_id is not null)
or (party_id = fu.supplier_id
and fu.supplier_id is not null)
or (party_id = fu.customer_id
and fu.customer_id is not null))
and pap.email_address is not null
and rownum <2),
fu.email_address) email_address,
1 product
from fnd_user fu
where fu.user_id = :userid3';
IF (eventName = 'oracle.apps.fnd.user.delete')
THEN
p_user_name := x_curr_name;
IF (eventName = 'oracle.apps.fnd.user.delete')
THEN
NULL;
ELSIF ( eventName = 'oracle.apps.fnd.wf.ds.userRole.updated'
OR eventName = 'oracle.apps.fnd.wf.ds.user.updated')
THEN
eventName := 'oracle.apps.fnd.user.resp.delete';
/* UPDATE USER */
x_update_user_sql := 'BEGIN ' || x_schema || '.API_CREATE_ORA_DEM_USER ( ' ||
' ''' || p_user_name || ''' , ' ||
' null , ' || -- Bug#14524761
' ''' || x_user_permission || ''' , ' ||
' ''' || p_user_fname || ''' , ' ||
' ''' || p_user_lname || ''' , ' ||
' ''' || p_user_org_name || ''' , ' ||
' ''' || p_user_wrkphone || ''' , ' ||
' ''' || p_user_fax || ''' , ' ||
' ''' || p_user_email || ''' , ' ||
' ''0'' , ' || -- ' ''' || p_user_language || ''' , ' || --commenting out MLS nallkuma
' null, ' ||
' ''' || x_component_name || ''' , ' ||
' null, ' ||
' ''UPDATE''); END;';
DuserQuery := 'select count(user_name) from ' || x_schema || '.user_id where user_name = '''||p_user_name||'''';
DOlduserQuery := 'select count(user_name) from ' || x_schema || '.user_id where user_name = '''||x_old_name||'''';
/* insert/Update responsibility for user */
If ( eventName = 'oracle.apps.fnd.wf.ds.userRole.created'
OR eventName = 'oracle.apps.fnd.wf.ds.userRole.updated') THEN
/* User does not exist in Demantra... Add the user */
If Duser_cnt = 0 Then
/* invoke API_CREATE_ORA_DEM_USER.(ADD) */
log_debug ('Insert/Update Responsibility - Creating User');
/* User exists in Demantra, Update the responsibility */
Elsif Duser_cnt > 0 Then
/* invoke API_CREATE_ORA_DEM_USER (UPDATE) */
log_debug ('Insert/Update Responsibility - Updating User');
log_debug (x_update_user_sql);
EXECUTE IMMEDIATE x_update_user_sql;
/* Update Existing User */
Elsif (eventName = 'oracle.apps.fnd.wf.ds.user.updated') THEN
/* Effective date has been disabled OR DM responsibilities have been disabled for user....delete user */
If (p_user_valid = 2 OR p_user_resp = 0) Then
/* invoke API_DROP_ORA_DEM_USER(user name) */
log_debug ('User Update - Deleting User');
log_debug ('User Update - Creating User');
/* User exists in Demantra and effective date is enabled....Update user */
Else
/* invoke API_CREATE_ORA_DEM_USER (UPDATE) */
log_debug('User Update - Updating User');
log_debug (x_update_user_sql);
EXECUTE IMMEDIATE x_update_user_sql;
/* Delete existing User */
Elsif (eventName = 'oracle.apps.fnd.user.delete') THEN
/* invoke API_DROP_ORA_DEM_USER(user name) */
log_debug('User Delete - Deleting User');
/* Delete responsibility */
Elsif (eventName = 'oracle.apps.fnd.user.resp.delete') THEN
/* User exists in Demantra */
If (Duser_cnt > 0 AND x_user_permission IS NOT NULL) Then
/* invoke API_CREATE_ORA_DEM_USER (UPDATE) */
log_debug ('Responsibility Delete - Updating user');
log_debug (x_update_user_sql);
EXECUTE IMMEDIATE x_update_user_sql;
log_debug('Responsibility Delete - Deleting user');
/* SELECT count(1)
INTO x_is_present
FROM dba_objects
WHERE owner = p_schema
and object_type = 'TABLE'
and object_name = 'LANGUAGES';
/* SELECT LANGUAGE_CODE
INTO x_language
FROM FND_LANGUAGES_VL
WHERE NLS_LANGUAGE = FND_PROFILE.VALUE_SPECIFIC('ICX_LANGUAGE', p_ebs_user_id);
EXECUTE IMMEDIATE 'select lang_id from ' || p_schema || '.languages where lang_code = :1'
INTO x_dem_language_id USING x_language;