The following lines contain the word 'select', 'insert', 'update' or 'delete':
* Procedure : UPDATE_AUTH_PRINCIPAL_ID
* Type : Private
* Pre_reqs :
* Description : Updates the existing UM records with the old_auth_principal_id to
* the new_auth_principal_id
* Parameters
* input parameters : old_auth_principal_id number
* new_auth_principal_id number
* Other Comments :
*/
procedure UPDATE_AUTH_PRINCIPAL_ID(old_auth_principal_id in number,
new_auth_principal_id in number ) IS
BEGIN
update jtf_um_subscriptions_b
set AUTH_DELEGATION_ROLE_ID = new_auth_principal_id
where AUTH_DELEGATION_ROLE_ID = old_auth_principal_id;
* role cannot be deleted. If the principal does not exist anywhere in the usertype/enrollments,
* returns "S" in the parameter x_return_status
* Parameters
* input parameters : auth_principal_name varchar2
* output parameters : x_return_status varchar2
* Errors : If the principal exists in UM, sends appropriate message back as part of
* error stack
* Other Comments :
*/
procedure IS_AUTH_PRINCIPAL_REFERRED(
auth_principal_name in varchar2,
-- x_if_referred_flag out NOCOPY varchar2,
x_return_status out NOCOPY varchar2,
x_msg_count out NOCOPY number,
x_msg_data out NOCOPY varchar2
) IS
l_api_name VARCHAR2(50) := 'IS_AUTH_PRINCIPAL_REFERRED';
select count(*) into l_count
from jtf_um_usertype_role a, jtf_um_usertypes_b b
where a.principal_name = auth_principal_name
and a.usertype_id = b.usertype_id
and a.effective_end_date is null
and b.effective_end_date is null;
select usertype_key into l_usertype_key
from jtf_um_usertypes_b a, jtf_um_usertype_role b
where b.principal_name = auth_principal_name
and a.usertype_id = b.usertype_id
and a.effective_end_date is null
and b.effective_end_date is null
and rownum = 1;
select count(*) into l_count
from jtf_um_subscription_role a, jtf_um_subscriptions_b b
where a.principal_name = auth_principal_name
and a.subscription_id = b.subscription_id
and a.effective_end_date is null
and b.effective_end_date is null;
select subscription_key into l_enrollment_key
from jtf_um_subscriptions_b a, jtf_um_subscription_role b
where b.principal_name = auth_principal_name
and a.subscription_id = b.subscription_id
and a.effective_end_date is null
and b.effective_end_date is null
and rownum = 1;
select count(*) into l_count
from jtf_auth_principals_b a, jtf_um_subscriptions_b b
where a.principal_name = auth_principal_name
and a.JTF_AUTH_PRINCIPAL_ID = b.AUTH_DELEGATION_ROLE_ID
and b.effective_end_date is null;
select subscription_key into l_enrollment_key
from jtf_um_subscriptions_b a, jtf_auth_principals_b b
where b.principal_name = auth_principal_name
and b.JTF_AUTH_PRINCIPAL_ID = a.AUTH_DELEGATION_ROLE_ID
and a.effective_end_date is null
and rownum = 1;