[Home] [Help]
PACKAGE BODY: APPS.CSF_RES_GROUP_ROLE_PVT
Source
1 package body CSF_RES_GROUP_ROLE_PVT as
2 /* $Header: csfvrsgb.pls 120.0 2010/10/14 06:54:55 dkailash noship $ */
3
4 -- Start of Comments
5 -- Package name : CSF_RES_GROUP_ROLE_PVT
6 -- Purpose : For Group and Role Creation and Deletion
7 -- History :
8 -- End of Comments
9
10 g_pkg_name CONSTANT VARCHAR2(30) := 'CSF_RES_GROUP_ROLE_PVT';
11 g_file_name CONSTANT VARCHAR2(12) := 'csfvrsgb.pls';
12 g_debug VARCHAR2(1) := NVL(fnd_profile.value('AFLOG_ENABLED'), 'N');
13 g_debug_level NUMBER := NVL(fnd_profile.value_specific('AFLOG_LEVEL'), fnd_log.level_event);
14
15 PROCEDURE INSERT_GROUP_ROLES(P_API_VERSION IN NUMBER,
16 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
17 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
18 P_ROLE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_ID%TYPE,
19 P_GROUP_ID IN JTF_RS_GROUP_MEMBERS.GROUP_ID%TYPE,
20 P_RESOURCE_ID IN JTF_RS_GROUP_MEMBERS.RESOURCE_ID%TYPE,
21 P_ROLE_RESOURCE_TYPE IN JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_TYPE%TYPE,
22 P_START_DATE_ACTIVE IN JTF_RS_ROLE_RELATIONS.START_DATE_ACTIVE%TYPE,
23 P_END_DATE_ACTIVE IN JTF_RS_ROLE_RELATIONS.END_DATE_ACTIVE%TYPE DEFAULT NULL,
24 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
25 X_MSG_COUNT OUT NOCOPY NUMBER,
26 X_MSG_DATA OUT NOCOPY VARCHAR2,
27 X_GROUP_MEMBER_ID OUT NOCOPY NUMBER,
28 X_ROLE_RELATE_ID OUT NOCOPY NUMBER
32 l_role_id JTF_RS_ROLE_RELATIONS.ROLE_ID%TYPE := P_ROLE_ID;
29 )
30 IS
31 l_api_version CONSTANT NUMBER := 1.0;
33 l_group_id JTF_RS_GROUP_MEMBERS.GROUP_ID%TYPE := P_GROUP_ID;
34 l_resource_id JTF_RS_GROUP_MEMBERS.RESOURCE_ID%TYPE := P_RESOURCE_ID;
35 l_role_resource_type JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_TYPE%TYPE := P_ROLE_RESOURCE_TYPE;
36 l_start_date_active JTF_RS_ROLE_RELATIONS.START_DATE_ACTIVE%TYPE := P_START_DATE_ACTIVE;
37 l_end_date_active JTF_RS_ROLE_RELATIONS.END_DATE_ACTIVE%TYPE := P_END_DATE_ACTIVE;
38 l_group_member_id_exists JTF_RS_GROUP_MEMBERS.GROUP_MEMBER_ID%TYPE;
39 l_role_resource_id NUMBER;
40 l_error_code NUMBER;
41 l_error_msg VARCHAR2(255);
42 l_msg_data VARCHAR2(255);
43
44
45 CURSOR c_group_member_id_exists( l_group_id JTF_RS_GROUP_MEMBERS.GROUP_ID%TYPE,
46 l_resource_id IN JTF_RS_GROUP_MEMBERS.RESOURCE_ID%TYPE )
47 IS
48 select group_member_id
49 from jtf_rs_group_members
50 where group_id = l_group_id
51 and resource_id = l_resource_id
52 and nvl(delete_flag, 'N') <> 'Y';
53
54 BEGIN
55
56 Debug_Message('Entering INSERT_GROUP_ROLES', 'CSF_RES_GROUP_ROLE_PVT.INSERT_GROUP_ROLES', fnd_log.level_procedure);
57
58 --Check for and existing group member exists for the concerned group and resource
59 OPEN c_group_member_id_exists(l_group_id,l_resource_id);
60 FETCH c_group_member_id_exists INTO l_group_member_id_exists;
61
62 IF c_group_member_id_exists%NOTFOUND THEN
63 Debug_Message('group member does not exist calling jtf_rs_group_members_pub.create_resource_group_members', 'CSF_RES_GROUP_ROLE_PVT.INSERT_GROUP_ROLES', fnd_log.level_procedure);
64 jtf_rs_group_members_pub.create_resource_group_members
65 (P_API_VERSION => l_api_version,
66 P_INIT_MSG_LIST => P_INIT_MSG_LIST,
67 P_COMMIT => P_COMMIT,
68 P_GROUP_ID => l_group_id,
69 P_GROUP_NUMBER => null,
70 P_RESOURCE_ID => l_resource_id,
71 P_RESOURCE_NUMBER => null,
72 X_RETURN_STATUS => X_RETURN_STATUS,
73 X_MSG_COUNT => X_MSG_COUNT,
74 X_MSG_DATA => X_MSG_DATA,
75 X_GROUP_MEMBER_ID => X_GROUP_MEMBER_ID
76 );
77 IF (X_RETURN_STATUS = fnd_api.g_ret_sts_success) THEN
78 Debug_Message('jtf_rs_group_members_pub.create_resource_group_members Successful,X_GROUP_MEMBER_ID'||X_GROUP_MEMBER_ID, 'CSF_RES_GROUP_ROLE_PVT.INSERT_GROUP_ROLES', fnd_log.level_procedure);
79 l_role_resource_id := X_GROUP_MEMBER_ID;
80 END IF;
81 ELSE
82 Debug_Message('Group Member Id Exists,value='||l_group_member_id_exists, 'CSF_RES_GROUP_ROLE_PVT.INSERT_GROUP_ROLES', fnd_log.level_procedure);
83 l_role_resource_id := l_group_member_id_exists;
84 END IF;
85
86 IF (l_role_resource_id IS NOT NULL and l_role_id IS NOT NULL) THEN
87 Debug_Message('l_role_resource_id='||l_role_resource_id, 'CSF_RES_GROUP_ROLE_PVT.INSERT_GROUP_ROLES', fnd_log.level_procedure);
88 Debug_Message('l_role_id = '||l_role_id, 'CSF_RES_GROUP_ROLE_PVT.INSERT_GROUP_ROLES', fnd_log.level_procedure);
89 Debug_Message('Calling jtf_rs_role_relate_pub.create_resource_role_relate', 'CSF_RES_GROUP_ROLE_PVT.INSERT_GROUP_ROLES', fnd_log.level_procedure);
90 jtf_rs_role_relate_pub.create_resource_role_relate
91 (P_API_VERSION => l_api_version,
92 P_INIT_MSG_LIST => P_INIT_MSG_LIST,
93 P_COMMIT => P_COMMIT,
94 P_ROLE_RESOURCE_TYPE => l_role_resource_type,
95 P_ROLE_RESOURCE_ID => l_role_resource_id,
96 P_ROLE_ID => l_role_id,
97 P_ROLE_CODE => null,
98 P_START_DATE_ACTIVE => l_start_date_active,
99 P_END_DATE_ACTIVE => l_end_date_active,
100 X_RETURN_STATUS => X_RETURN_STATUS,
101 X_MSG_COUNT => X_MSG_COUNT,
102 X_MSG_DATA => X_MSG_DATA,
103 X_ROLE_RELATE_ID => X_ROLE_RELATE_ID
104 );
105 IF (X_RETURN_STATUS = fnd_api.g_ret_sts_success) THEN
106 Debug_Message('jtf_rs_role_relate_pub.create_resource_role_relate Successful,X_ROLE_RELATE_ID='||X_ROLE_RELATE_ID, 'CSF_RES_GROUP_ROLE_PVT.INSERT_GROUP_ROLES', fnd_log.level_procedure);
107 END IF;
108 END IF;
109
110 IF c_group_member_id_exists%ISOPEN THEN
111 CLOSE c_group_member_id_exists;
112 END IF;
113
114 -- Exception Handling
115 EXCEPTION
116 WHEN OTHERS THEN
117 l_error_code := SQLCODE;
118 l_error_msg := SQLERRM;
119 l_msg_data := 'Unexpected Error -'||l_error_code||' : '||l_error_msg;
120 Debug_Message('In Exception Block of INSERT_GROUP_ROLES'||l_msg_data, 'CSF_RES_GROUP_ROLE_PVT.INSERT_GROUP_ROLES', fnd_log.level_procedure);
121 END INSERT_GROUP_ROLES;
122
123 PROCEDURE DELETE_GROUP_ROLES(P_API_VERSION IN NUMBER,
124 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
125 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
126 P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
127 P_OBJECT_VERSION_NUM1 IN JTF_RS_ROLE_RELATIONS.OBJECT_VERSION_NUMBER%TYPE,
128 P_GROUP_MEMBER_ID IN JTF_RS_GROUP_MEMBERS.GROUP_MEMBER_ID%TYPE,
129 P_GROUP_ID IN JTF_RS_GROUP_MEMBERS.GROUP_ID%TYPE,
130 P_RESOURCE_ID IN JTF_RS_GROUP_MEMBERS.RESOURCE_ID%TYPE,
131 P_OBJECT_VERSION_NUM IN JTF_RS_GROUP_MEMBERS.OBJECT_VERSION_NUMBER%TYPE,
132 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
133 X_MSG_COUNT OUT NOCOPY NUMBER,
134 X_MSG_DATA OUT NOCOPY VARCHAR2
135 )
136 IS
137 l_api_version CONSTANT NUMBER := 1.0;
138 l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE := P_ROLE_RELATE_ID;
139 l_object_version_number1 JTF_RS_ROLE_RELATIONS.OBJECT_VERSION_NUMBER%TYPE := P_OBJECT_VERSION_NUM1;
140 l_group_member_id JTF_RS_GROUP_MEMBERS.GROUP_MEMBER_ID%TYPE := P_GROUP_MEMBER_ID;
141 l_group_id JTF_RS_GROUP_MEMBERS.GROUP_ID%TYPE := P_GROUP_ID;
142 l_resource_id JTF_RS_GROUP_MEMBERS.RESOURCE_ID%TYPE := P_RESOURCE_ID;
143 l_object_version_number JTF_RS_GROUP_MEMBERS.OBJECT_VERSION_NUMBER%TYPE := P_OBJECT_VERSION_NUM;
144 l_check_res_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE;
145 l_error_code NUMBER;
146 l_error_msg VARCHAR2(255);
147 l_msg_data VARCHAR2(255);
148 l_role_deleted BOOLEAN := FALSE;
149
150 CURSOR c_csf_res_role_relation_id( l_resource_role_id IN JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_ID%TYPE ) IS
151 select role_relate_id
152 from jtf_rs_role_relations
153 where role_resource_id = l_resource_role_id
154 and role_resource_type = 'RS_GROUP_MEMBER'
155 and nvl(delete_flag, 'N') <> 'Y';
156
157 BEGIN
158 --Call Role Delete API First
159 IF l_role_relate_id IS NOT NULL THEN
160 Debug_Message('Calling jtf_rs_role_relate_pub.delete_resource_role_relate,l_role_relate_id'||l_role_relate_id, 'CSF_RES_GROUP_ROLE_PVT.DELETE_GROUP_ROLES', fnd_log.level_procedure);
161 jtf_rs_role_relate_pub.delete_resource_role_relate
162 (P_API_VERSION => l_api_version,
163 P_INIT_MSG_LIST => P_INIT_MSG_LIST,
164 P_COMMIT => P_COMMIT,
165 P_ROLE_RELATE_ID => l_role_relate_id,
166 P_OBJECT_VERSION_NUM => l_object_version_number1,
167 X_RETURN_STATUS => X_RETURN_STATUS,
168 X_MSG_COUNT => X_MSG_COUNT,
169 X_MSG_DATA => X_MSG_DATA
170 );
171 IF (X_RETURN_STATUS = fnd_api.g_ret_sts_success) THEN
172 Debug_Message('Call to jtf_rs_role_relate_pub.delete_resource_role_relate Successful', 'CSF_RES_GROUP_ROLE_PVT.DELETE_GROUP_ROLES', fnd_log.level_procedure);
173 l_role_deleted := TRUE;
174 END IF;
175 ELSE
176 Debug_Message('No Roles Associated setting l_role_deleted to true', 'CSF_RES_GROUP_ROLE_PVT.DELETE_GROUP_ROLES', fnd_log.level_procedure);
177 --No Role Present set l_role_deleted to true for group deletion to occur
178 l_role_deleted := TRUE;
179 END IF;
180
181 IF (l_role_deleted) THEN
182 OPEN c_csf_res_role_relation_id(l_group_member_id);
183 FETCH c_csf_res_role_relation_id INTO l_check_res_role_relate_id;
184
185 IF c_csf_res_role_relation_id%NOTFOUND THEN
186 Debug_Message('No more Roles left in the Group, calling jtf_rs_group_members_pub.delete_resource_group_members', 'CSF_RES_GROUP_ROLE_PVT.DELETE_GROUP_ROLES', fnd_log.level_procedure);
187 jtf_rs_group_members_pub.delete_resource_group_members
188 (P_API_VERSION => l_api_version,
189 P_INIT_MSG_LIST => P_INIT_MSG_LIST,
190 P_COMMIT => P_COMMIT,
191 P_GROUP_ID => l_group_id,
192 P_GROUP_NUMBER => null,
193 P_RESOURCE_ID => l_resource_id,
194 P_RESOURCE_NUMBER => null,
195 P_OBJECT_VERSION_NUM => l_object_version_number,
196 X_RETURN_STATUS => X_RETURN_STATUS,
197 X_MSG_COUNT => X_MSG_COUNT,
198 X_MSG_DATA => X_MSG_DATA
199 );
200 IF (X_RETURN_STATUS = fnd_api.g_ret_sts_success) THEN
201 Debug_Message('Call to jtf_rs_group_members_pub.delete_resource_group_members Successful', 'CSF_RES_GROUP_ROLE_PVT.DELETE_GROUP_ROLES', fnd_log.level_procedure);
202 END IF;
203 END IF;
204 END IF;
205
206 IF c_csf_res_role_relation_id%ISOPEN THEN
207 CLOSE c_csf_res_role_relation_id;
208 END IF;
209
210 -- Exception Handling
211 EXCEPTION
212 WHEN OTHERS THEN
213 l_error_code := SQLCODE;
214 l_error_msg := SQLERRM;
215 l_msg_data := 'Unexpected Error -'||l_error_code||' : '||l_error_msg;
216 Debug_Message('In Exception Block of DELETE_GROUP_ROLES'||l_msg_data, 'CSF_RES_GROUP_ROLE_PVT.DELETE_GROUP_ROLES', fnd_log.level_procedure);
217 END DELETE_GROUP_ROLES;
218
219 PROCEDURE Debug_Message(p_message VARCHAR2, p_module VARCHAR2, p_level NUMBER)
220 IS
221 BEGIN
222 IF g_debug = 'Y' AND p_level >= g_debug_level THEN
223 IF ( p_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
224 THEN
225 fnd_log.string(p_level, 'csf.plsql.csfvrsgb.' || p_module, p_message);
226 END IF;
227 END IF;
228 END Debug_Message;
229
230 end CSF_RES_GROUP_ROLE_PVT;