[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select user_name
from fnd_user
where upper(email_address) = p_requester_email
and (nvl(end_date, sysdate + 1) > sysdate or
to_char(END_DATE) = to_char(FND_API.G_MISS_DATE))
union
-- from HR tables
select fnd.user_name
from fnd_user fnd, per_all_people_f per
where per.person_id = fnd.employee_id
and per.effective_end_date > sysdate
and (nvl(fnd.end_date, sysdate+1) > sysdate or
to_char(fnd.END_DATE) = to_char(FND_API.G_MISS_DATE))
and upper(per.email_address) = p_requester_email
union
-- from TCA tables
select fnd.user_name
from hz_contact_points hcp, fnd_user fnd
where hcp.owner_table_id = fnd.customer_id
and hcp.owner_table_name = 'HZ_PARTIES'
and hcp.contact_point_type = 'EMAIL'
and upper(hcp.email_address) = p_requester_email;
select name
from wf_users
where name = l_user_name
and upper(email_address) = l_email;
select email_address, customer_id, employee_id
from fnd_user
where user_name = l_user_name
and (nvl(end_date, sysdate + 1) > sysdate or
to_char(END_DATE) = to_char(FND_API.G_MISS_DATE));
select email_address
from per_all_people_f
where person_id = p_employee_id
and effective_end_date > sysdate;
select hzp.party_type
from hz_parties hzp
where hzp.party_id = p_customer_id;
select subject_id
from hz_party_relationships
where party_id = p_customer_id
and party_relationship_type = 'EMPLOYEE_OF'
and nvl(end_date, sysdate+1) > sysdate;
select EMAIL_ADDRESS
from hz_contact_points
where owner_table_id = p_party_id
and owner_table_name = 'HZ_PARTIES'
and contact_point_type = 'EMAIL';
select name
from wf_users
where name = l_user_name
and email_address = l_email;
select user_name
from fnd_user
where upper(email_address) = p_requester_email
and (nvl(end_date, sysdate + 1) > sysdate or
to_char(END_DATE) = to_char(FND_API.G_MISS_DATE));
select fnd.user_name
from fnd_user fnd, per_all_people_f per
where per.person_id = fnd.employee_id
and per.effective_end_date > sysdate
and (nvl(fnd.end_date, sysdate+1) > sysdate or
to_char(fnd.END_DATE) = to_char(FND_API.G_MISS_DATE))
and upper(per.email_address) = p_requester_email;
select fnd.user_name
from hz_contact_points hcp, fnd_user fnd
where hcp.owner_table_id = fnd.customer_id
and hcp.owner_table_name = 'HZ_PARTIES'
and hcp.contact_point_type = 'EMAIL'
and upper(hcp.email_address) = p_requester_email;
select user_name
from fnd_user
where customer_id = l_party_id
and (nvl(end_date, sysdate+1) > sysdate or
to_char(END_DATE) = to_char(FND_API.G_MISS_DATE));
select name
from wf_users
where name = l_user_name
and upper(email_address) = l_email;
cursor c_update_email(l_user_name in varchar2) is
select email_address
from fnd_user
where user_name = l_user_name;
* database, just update input attributes.
* Parameters :
* input parameters
* @param
* p_username
* description: The adhoc username.
* required : Y
* p_display_name
* description: The adhoc display name.
* required : N
* default : null
* p_language
* description: The value of the database NLS_LANGUAGE initialization
* parameter that specifies the default language-dependent
* behavior of the user's notification session. If null,
* the procedure resolves this to the language setting of
* your current session.
* required : N
* default : null
* p_territory
* description: The value of the database NLS_TERRITORY initialization
* parameter that specifies the default territory-dependant
* date and numeric formatting used in the user's
* notification session. If null, the procedure resolves
* this to the territory setting of your current session.
* required : N
* default : null
* p_description
* description: Description for the user.
* required : N
* default : null
* p_notification_preference
* description: Indicate how this user prefers to receive notifications:
* 'MAILTEXT', 'MAILHTML', 'MAILATTH', 'QUERY' or 'SUMMARY'.
* If null, the procedure sets the notification preference
* to 'MAILHTML'.
* required : N
* default : 'MAILTEXT'
* p_email_address
* description: Electronic mail address for this user.
* required : Y
* p_fax
* description: Fax number for the user
* required : N
* default : null
* p_status
* description: The availability of the user to participate in a
* workflow process. The possible statuses are 'ACTIVE',
* 'EXTLEAVE', 'INACTIVE', and 'TMPLEAVE'. If null, the
* procedure sets the status to 'ACTIVE'.
* required : N
* default : 'ACTIVE'
* p_expiration_date
* description: The date at which the user is no longer valid in the
* directory service. If null, the procedure defaults the
* expiration date to sysdate.
* required : N
* default : sysdate
* output parameters
* @return
* Errors :
* Other Comments :
*/
PROCEDURE GetAdHocUser (p_api_version_number in number,
p_init_msg_list in varchar2 default FND_API.G_FALSE,
p_commit in varchar2 default FND_API.G_FALSE,
p_validation_level in number default FND_API.G_VALID_LEVEL_FULL,
p_username in varchar2,
p_display_name in varchar2 default null,
p_language in varchar2 default null,
p_territory in varchar2 default null,
p_description in varchar2 default null,
p_notification_preference in varchar2 default 'MAILTEXT',
p_email_address in varchar2,
p_fax in varchar2 default null,
p_status in varchar2 default 'ACTIVE',
p_expiration_date in date default sysdate,
x_return_status out NOCOPY varchar2,
x_msg_data out NOCOPY varchar2,
x_msg_count out NOCOPY varchar2) is
l_api_version_number NUMBER := 1.0;
select JTF_UM_EMAIL_NOTIFICATION_S.NEXTVAL
from dual;
select profile_option_id,
application_id
from fnd_profile_options
where profile_option_name = l_name_z
and start_date_active <= sysdate
and nvl(end_date_active, sysdate) >= sysdate;
select profile_option_value
from fnd_profile_option_values
where profile_option_id = pid
and application_id = aid
and level_id = lid
and level_value = lval;
select profile_option_value
from fnd_profile_option_values
where profile_option_id = pid
and application_id = aid
and level_id = 10003
and level_value = lval
and level_value_application_id = laid
;
select application_id from fnd_responsibility
where responsibility_id = p_resp_id;
select user_id from fnd_user
where user_name = p_username;
select responsibility_id from fnd_responsibility
where application_id = 690
and responsibility_key = 'JTF_PENDING_APPROVAL';
select fnd.responsibility_id, fnd.application_id
from jtf_um_usertype_reg reg,
jtf_um_usertype_resp resp,
fnd_responsibility fnd
where reg.user_id = p_user_id
and reg.usertype_id = resp.usertype_id
and resp.responsibility_key = fnd.responsibility_key
and resp.application_id = fnd.application_id;
cursor find_user_id is select user_id from fnd_user where
user_id = p_user_id and (nvl(end_date, sysdate+1) > sysdate or
to_char(END_DATE) = to_char(FND_API.G_MISS_DATE));
cursor find_subscription_id is select subscription_id from jtf_um_subscriptions_b where
subscription_id = p_subscription_id and nvl(effective_end_date, sysdate+1) > sysdate;
CURSOR VALIDATE_ROLE IS SELECT JTF_AUTH_PRINCIPAL_ID FROM JTF_AUTH_PRINCIPALS_B
WHERE JTF_AUTH_PRINCIPAL_ID = p_auth_principal_id AND IS_USER_FLAG = 0;
CURSOR CHECK_ROLE_ASSIGNMENT IS SELECT JTF_AUTH_PRINCIPAL_MAPPING_ID FROM JTF_AUTH_PRINCIPAL_MAPS
WHERE JTF_AUTH_PARENT_PRINCIPAL_ID = p_auth_principal_id
AND JTF_AUTH_PRINCIPAL_ID IN
(SELECT JTF_AUTH_PRINCIPAL_ID FROM JTF_AUTH_PRINCIPALS_B ROLE, FND_USER FU
WHERE FU.USER_NAME = ROLE.PRINCIPAL_NAME AND FU.USER_ID = p_user_id);
CURSOR VALIDATE_ROLE_NAME IS SELECT JTF_AUTH_PRINCIPAL_ID FROM JTF_AUTH_PRINCIPALS_B
WHERE PRINCIPAL_NAME = p_principal_name AND IS_USER_FLAG = 0;
CURSOR FIND_ROLE_NAME IS SELECT PRINCIPAL_NAME FROM JTF_AUTH_PRINCIPALS_B
WHERE JTF_AUTH_PRINCIPAL_ID = p_role_id;
CURSOR GET_ID IS SELECT USER_ID FROM FND_USER WHERE USER_NAME = p_user_name;
CURSOR GET_ID IS SELECT USERTYPE_ID FROM JTF_UM_USERTYPE_REG
WHERE USER_ID = p_user_id
AND NVL(EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE
AND EFFECTIVE_START_DATE < SYSDATE;
SELECT hzp.party_type
FROM hz_parties hzp
WHERE hzp.party_id = p_party_id;
select territory_code into l_territory_code
from fnd_territories
where nls_territory = l_nls_territory
and OBSOLETE_FLAG = 'N'
and rownum = 1;