The following lines contain the word 'select', 'insert', 'update' or 'delete':
* select 'T' from jtf_um_usertypes_b
* where usertype_id = p_usertype_id
* and approval_id is not NULL;
CURSOR c_item_key IS SELECT JTF_UM_PSW_WF_S.nextval FROM dual;
select email_address
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 wf_users
where name = l_user_name ;
select 'T' from jtf_um_usertypes_b
where usertype_id = user_type_id
and approval_id is not NULL;
select application_id from fnd_responsibility
where responsibility_id = p_resp_id;
insert into test_temp
values
(debug_msg)
;
* Also, it inserts a user into wf_local_user if a valid username
* and email combination does not
already exist in wf_user.
* Parameters : None
* input parameters
* param requester_user_name
* param requester_email
* (*) required fields
* output parameters
* param x_return_status
* param x_msg_data
* param x_msg_count
* Errors : Expected Errors
* requester_user_name and email is null
* requester_user_name is not a valid user
* requester_email does not correspond to a valid user
* Other Comments :
* FND_USER update : The update of fnd_user table is done using fnd_user_pkg
* procedure as recommended by fnd (bug 1713101)
* DEFAULTING LOGIC
* If only the user name is passed then the email is defaulted using the following logic
* 1. Email address from fnd_users where user_name = p_requester_user_name
* 2. Email from per_all_people_F where person_id = employee_id
* (retrieved from fnd_users using the user_name)
* 3. Email from hz_contact_points where owner_type_id = party_id and
* owner_table = 'HZ_PARTIES' and contact_point_type = 'EMAIL'.
* Party_id here is obtained from the customer id stored in fnd_user
* where user_name = p_requester_user_name.
* In all the above cases the user, employee, party etc. have to be valid.
*
* If only the email address is specified, the user name is determined using a similar logic
* 1. User_name from fnd_user where email_address = p_requester_email_Address
* 2. User_name from fnd_user where employee_id = person_id (retrieved from per_all_people_f
* using the email_address)
* 3. User_name from fnd_user where customer_id = hz_contact_points.owner_type_id and
* owner_table = 'HZ_PARTIES' and contact_point_type = 'EMAIL' and
* contact_point = p_requester_email_Address
*
* If both email and user name are passed, the combination is validated using the above logic.
*/
procedure reset_password(p_api_version_number in number,
p_init_msg_list in varchar2 := FND_API.G_FALSE,
p_commit in varchar2 := FND_API.G_FALSE,
p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
p_requester_user_name in varchar2 := null,
p_requester_email in varchar2 := null,
p_application_id in number := null,
p_responsibility_id in number := null,
x_return_status out NOCOPY varchar2,
x_msg_count out NOCOPY number,
x_msg_data out NOCOPY varchar2
) is
l_api_version_number NUMBER := 1.0;
fnd_user_pkg.UpdateUser (
x_user_name => l_requester_user_name,
x_owner => 'CUST',
x_unencrypted_password => l_password,
x_password_date => null);
update fnd_user set
password_date = null
where user_name = l_requester_user_name;
select svl.subscription_name||decode(reg.status_code,'PENDING', '(*)') name
from jtf_um_subscriptions_vl svl, jtf_um_subscription_reg reg
where svl.subscription_id = reg.subscription_id
and reg.subscription_reg_id = p_subscr_reg_id
and status_code in ('APPROVED', 'PENDING')
and NVL(reg.EFFECTIVE_END_DATE, SYSDATE + 1) > SYSDATE;