1 PACKAGE BODY JTF_UM_ROLE_VERIFICATION as
2 /* $Header: JTFUMRVB.pls 115.3 2002/11/21 22:58:10 kching ship $ */
3 -- Start of Comments
4 -- Package name : JTF_UM_ROLE_VERIFICATION
5 -- Purpose : verify if given role exists in UM and updating principal_id.
6 -- History :
7
8
9 -- package level variables
10 G_PKG_NAME CONSTANT VARCHAR2(30):= 'JTF_UM_ROLE_VERIFICATION';
11
12 /**
13 * Procedure : UPDATE_AUTH_PRINCIPAL_ID
14 * Type : Private
15 * Pre_reqs :
16 * Description : Updates the existing UM records with the old_auth_principal_id to
17 * the new_auth_principal_id
18 * Parameters
19 * input parameters : old_auth_principal_id number
20 * new_auth_principal_id number
21 * Other Comments :
22 */
23 procedure UPDATE_AUTH_PRINCIPAL_ID(old_auth_principal_id in number,
24 new_auth_principal_id in number ) IS
25 BEGIN
26 update jtf_um_subscriptions_b
27 set AUTH_DELEGATION_ROLE_ID = new_auth_principal_id
28 where AUTH_DELEGATION_ROLE_ID = old_auth_principal_id;
29 END;
30
31 /**
32 * Procedure : IS_AUTH_PRINCIPAL_REFERRED
33 * Type : Private
34 * Pre_reqs :
35 * Description : Looks for existence of input auth_principal_id or auth_principal_name in
36 * UM tables and if so, returns "E" in x_return_status with appropriate message that the
37 * role cannot be deleted. If the principal does not exist anywhere in the usertype/enrollments,
38 * returns "S" in the parameter x_return_status
39 * Parameters
40 * input parameters : auth_principal_name varchar2
41 * output parameters : x_return_status varchar2
42 * Errors : If the principal exists in UM, sends appropriate message back as part of
43 * error stack
44 * Other Comments :
45 */
46 procedure IS_AUTH_PRINCIPAL_REFERRED(
47 auth_principal_name in varchar2,
48 -- x_if_referred_flag out NOCOPY varchar2,
49 x_return_status out NOCOPY varchar2,
50 x_msg_count out NOCOPY number,
51 x_msg_data out NOCOPY varchar2
52 ) IS
53 l_api_name VARCHAR2(50) := 'IS_AUTH_PRINCIPAL_REFERRED';
54 l_count NUMBER := 0;
55 l_usertype_key VARCHAR2(30);
56 l_enrollment_key VARCHAR2(30);
57
58 BEGIN
59 -- Initialize API return status to success
60 x_return_status := FND_API.G_RET_STS_SUCCESS;
61
62 -- Initialize the flag to value 'N' which means that the principal
63 -- passed in is not referred by any of UM columns
64 -- x_if_referred_flag := 'N';
65
66 -- Standard Start of API savepoint
67 SAVEPOINT IS_AUTH_PRINCIPAL_REFERRED;
68
69 -- Initialize message list -- if p_init_msg_list is set to TRUE.
70 FND_MSG_PUB.initialize;
71
72 -- Validate required fields for not null values
73 if (auth_principal_name is null) then
74 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
75 FND_MESSAGE.Set_Name('JTF', 'JTA_UM_REQUIRED_FIELD');
76 --FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME, FALSE);
77 FND_MESSAGE.Set_Token('API_NAME', 'IS_AUTH_PRINCIPAL_REFERRED', FALSE);
78 FND_MESSAGE.Set_Token('FIELD', 'auth_principal_name', FALSE);
79 FND_MSG_PUB.ADD;
80 END IF;
81 RAISE FND_API.G_EXC_ERROR;
82 end if;
83
84 -- business logic starts
85
86 -- verify to see if the auth_principal_name passed as input is assigned to any usertype
87 select count(*) into l_count
88 from jtf_um_usertype_role a, jtf_um_usertypes_b b
89 where a.principal_name = auth_principal_name
90 and a.usertype_id = b.usertype_id
91 and a.effective_end_date is null
92 and b.effective_end_date is null;
93
94 IF( l_count <> 0 ) THEN
95 -- set the flag that this principal_name is being referred
96 -- x_if_referred_flag := 'Y';
97
98 -- need to find usertype where the role is used
99 select usertype_key into l_usertype_key
100 from jtf_um_usertypes_b a, jtf_um_usertype_role b
101 where b.principal_name = auth_principal_name
102 and a.usertype_id = b.usertype_id
103 and a.effective_end_date is null
104 and b.effective_end_date is null
105 and rownum = 1;
106
107 -- throw exception for the usertype found
108 FND_MESSAGE.Set_Name('JTF', 'JTA_UM_USERTYPE_ROLE');
109 FND_MESSAGE.Set_Token('PRINCIPAL_NAME', auth_principal_name , FALSE);
110 FND_MESSAGE.Set_Token('USERTYPE_NAME', l_usertype_key , FALSE);
111
112 FND_MSG_PUB.ADD;
113 RAISE FND_API.G_EXC_ERROR;
114 END IF;
115
116 -- verify to see if the auth_principal_name passed as input is assigned to any enrollment
117 select count(*) into l_count
118 from jtf_um_subscription_role a, jtf_um_subscriptions_b b
119 where a.principal_name = auth_principal_name
120 and a.subscription_id = b.subscription_id
121 and a.effective_end_date is null
122 and b.effective_end_date is null;
123
124
125 IF( l_count <> 0 ) THEN
126 -- set the flag that this principal_name is being referred
127 -- x_if_referred_flag := 'Y';
128
129 -- need to find usertype where the role is used
130 select subscription_key into l_enrollment_key
131 from jtf_um_subscriptions_b a, jtf_um_subscription_role b
132 where b.principal_name = auth_principal_name
133 and a.subscription_id = b.subscription_id
134 and a.effective_end_date is null
135 and b.effective_end_date is null
136 and rownum = 1;
137
138 -- throw exception for the enrollment found
139 FND_MESSAGE.Set_Name('JTF', 'JTA_UM_ENROLLMENT_ROLE');
140 FND_MESSAGE.Set_Token('PRINCIPAL_NAME', auth_principal_name , FALSE);
141 FND_MESSAGE.Set_Token('ENROLLMENT_NAME', l_enrollment_key , FALSE);
142
143 FND_MSG_PUB.ADD;
144 RAISE FND_API.G_EXC_ERROR;
145 END IF;
146
147 -- verify to see if the auth_principal_name passed as input is assigned to any enrollment as delegation role
148 select count(*) into l_count
149 from jtf_auth_principals_b a, jtf_um_subscriptions_b b
150 where a.principal_name = auth_principal_name
151 and a.JTF_AUTH_PRINCIPAL_ID = b.AUTH_DELEGATION_ROLE_ID
152 and b.effective_end_date is null;
153
154
155 IF( l_count <> 0 ) THEN
156
157 -- need to find enrollment where the role is used
158 select subscription_key into l_enrollment_key
159 from jtf_um_subscriptions_b a, jtf_auth_principals_b b
160 where b.principal_name = auth_principal_name
161 and b.JTF_AUTH_PRINCIPAL_ID = a.AUTH_DELEGATION_ROLE_ID
162 and a.effective_end_date is null
163 and rownum = 1;
164
165 -- throw exception for the enrollment found
166 FND_MESSAGE.Set_Name('JTF', 'JTA_UM_ENROLLMENT_ROLE');
167 FND_MESSAGE.Set_Token('PRINCIPAL_NAME', auth_principal_name , FALSE);
168 FND_MESSAGE.Set_Token('ENROLLMENT_NAME', l_enrollment_key , FALSE);
169
170 FND_MSG_PUB.ADD;
171 RAISE FND_API.G_EXC_ERROR;
172 END IF;
173 --
174
175 -- business logic ends
176
177 -- Standard call to get message count and if count is 1, get message info.
178 FND_MSG_PUB.Count_And_Get(
179 p_count => x_msg_count,
180 p_data => x_msg_data);
181
182 EXCEPTION
183 WHEN FND_API.G_EXC_ERROR THEN
184 JTF_DEBUG_PUB.HANDLE_EXCEPTIONS(
185 P_API_NAME => L_API_NAME
186 ,P_PKG_NAME => G_PKG_NAME
187 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
188 ,P_SQLCODE => SQLCODE
189 ,P_SQLERRM => SQLERRM
190 ,X_MSG_COUNT => X_MSG_COUNT
191 ,X_MSG_DATA => X_MSG_DATA
192 ,X_RETURN_STATUS => X_RETURN_STATUS);
193
194 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
195 JTF_DEBUG_PUB.HANDLE_EXCEPTIONS(
196 P_API_NAME => L_API_NAME
197 ,P_PKG_NAME => G_PKG_NAME
198 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
199 ,P_SQLCODE => SQLCODE
200 ,P_SQLERRM => SQLERRM
201 ,X_MSG_COUNT => X_MSG_COUNT
202 ,X_MSG_DATA => X_MSG_DATA
203 ,X_RETURN_STATUS => X_RETURN_STATUS);
204
205 WHEN OTHERS THEN
206 JTF_DEBUG_PUB.HANDLE_EXCEPTIONS(
207 P_API_NAME => L_API_NAME
208 ,P_PKG_NAME => G_PKG_NAME
209 ,P_EXCEPTION_LEVEL => JTF_DEBUG_PUB.G_EXC_OTHERS
210 ,P_SQLCODE => SQLCODE
211 ,P_SQLERRM => SQLERRM
212 ,X_MSG_COUNT => X_MSG_COUNT
213 ,X_MSG_DATA => X_MSG_DATA
214 ,X_RETURN_STATUS => X_RETURN_STATUS);
215
216 END;
217
218 END;
[Home] [Help]