[Home] [Help]
PACKAGE BODY: APPS.CSM_GROUP_DOWNLOAD_PVT
Source
1 PACKAGE BODY CSM_GROUP_DOWNLOAD_PVT AS
2 /* $Header: csmegrpb.pls 120.4 2008/02/29 08:50:40 anaraman noship $ */
3
4 -- The same table is used as both base and access table.
5
6
7 g_grp_acc_table_name CONSTANT VARCHAR2(30) := 'CSM_GROUPS';
8 g_grp_table_name CONSTANT VARCHAR2(30) := 'CSM_GROUPS';
9 g_grp_seq_name CONSTANT VARCHAR2(30) := 'CSM_GROUPS_S' ;
10 g_grp_pk1_name CONSTANT VARCHAR2(30) := 'GROUP_ID';
11 g_grp_item CONSTANT VARCHAR2(30) := 'CSM_GROUPS';
12 g_grp_pubi_name CONSTANT CSM_ACC_PKG.t_publication_item_list :=
13 CSM_ACC_PKG.t_publication_item_list('CSM_GROUPS');
14
15 -- This procedure will insert current group record in csm_groups
16 -- during user-creation.
17 PROCEDURE INSERT_MY_GROUP (p_user_id NUMBER
18 , x_return_status OUT NOCOPY VARCHAR2
19 , x_error_message OUT NOCOPY VARCHAR2)
20 IS
21 --CURSOR declarations
22 CURSOR c_group_ins(b_user_id NUMBER)
23 IS
24 SELECT GROUP_ID, OWNER_ID
25 FROM ASG_USER usr
26 WHERE usr.USER_ID = b_user_id
27 AND usr.ENABLED = 'Y'
28 AND NOT EXISTS
29 (
30 SELECT 1
31 FROM CSM_GROUPS acc
32 WHERE acc.USER_ID = usr.OWNER_ID
33 AND acc.GROUP_TYPE ='C'
34 );
35
36 --variable declarations
37 l_sqlerrno VARCHAR2(20);
38 l_sqlerrmsg VARCHAR2(2000);
39 l_message VARCHAR2(3000);
40 l_group_id CSM_GROUPS.GROUP_ID%TYPE := NULL;
41 l_owner_id CSM_GROUPS.GROUP_OWNER_ID%TYPE := NULL;
42 BEGIN
43
44 CSM_UTIL_PKG.LOG('Entering CSM_GROUP_DOWNLOAD_PVT.INSERT_MY_GROUP Proc ',
45 'CSM_GROUP_DOWNLOAD_PVT.INSERT_MY_GROUP',FND_LOG.LEVEL_STATEMENT);
46 OPEN c_group_ins (p_user_id);
47 FETCH c_group_ins INTO l_group_id,l_owner_id;
48 CLOSE c_group_ins;
49
50 IF l_group_id IS NOT NULL AND l_owner_id IS NOT NULL THEN
51 CSM_ACC_PKG.Insert_Acc
52 (P_PUBLICATION_ITEM_NAMES => g_grp_pubi_name
53 ,P_ACC_TABLE_NAME => g_grp_acc_table_name
54 ,P_SEQ_NAME => g_grp_seq_name
55 ,P_PK1_NAME => g_grp_pk1_name
56 ,P_PK1_NUM_VALUE => l_group_id
57 ,P_USER_ID => l_owner_id
58 );
59
60 UPDATE CSM_GROUPS
61 SET GROUP_TYPE ='C',
62 GROUP_OWNER_ID=l_owner_id
63 WHERE GROUP_ID = l_group_id
64 AND USER_ID = l_owner_id;
65
66 END IF;
67
68 x_return_status := 'SUCCESS';
69 x_error_message := 'Group Data for Current Group successfully Inserted';
70
71 CSM_UTIL_PKG.LOG('Leaving CSM_GROUP_DOWNLOAD_PVT.INSERT_MY_GROUP Proc ',
72 'CSM_GROUP_DOWNLOAD_PVT.INSERT_MY_GROUP',FND_LOG.LEVEL_STATEMENT);
73
74 EXCEPTION
75 WHEN others THEN
76 l_sqlerrno := to_char(SQLCODE);
77 l_sqlerrmsg := substr(SQLERRM, 1,2000);
78 x_return_status := 'ERROR';
79 x_error_message := l_sqlerrmsg;
80 l_message := 'Exception in CSM_GROUP_DOWNLOAD_PVT.INSERT_MY_GROUP Procedure :' || l_sqlerrno || ':' || l_sqlerrmsg;
81 CSM_UTIL_PKG.LOG(l_message, 'CSM_GROUP_DOWNLOAD_PVT.INSERT_MY_GROUP',FND_LOG.LEVEL_EXCEPTION);
82 END INSERT_MY_GROUP;
83
84
85 -- The Group which was downloaded for the user has to be deleted from the
86 -- CSM_GROUPS table only if there are no Mobile Users from the Group.
87
88 PROCEDURE DELETE_MY_GROUP ( p_user_id NUMBER
89 , x_return_status OUT NOCOPY VARCHAR2
90 , x_error_message OUT NOCOPY VARCHAR2)
91 IS
92 --CURSOR declarations
93 CURSOR c_group_del(b_user_id NUMBER)
94 IS
95 SELECT acc.group_id
96 FROM CSM_GROUPS acc
97 WHERE acc.USER_ID = b_user_id
98 AND acc.GROUP_TYPE = 'C';
99
100 --variable declarations
101 l_group_id CSM_GROUPS.GROUP_ID%TYPE := NULL;
102 l_sqlerrno VARCHAR2(20);
103 l_sqlerrmsg VARCHAR2(2000);
104 l_message VARCHAR2(3000);
105 l_access_id CSM_GROUPS.ACCESS_ID%TYPE;
106
107 BEGIN
108 CSM_UTIL_PKG.LOG('Entering CSM_GROUP_DOWNLOAD_PVT.DELETE_MY_GROUP Proc ',
109 'CSM_GROUP_DOWNLOAD_PVT.DELETE_MY_GROUP',FND_LOG.LEVEL_STATEMENT);
110 OPEN c_group_del(p_user_id);
111 FETCH c_group_del INTO l_group_id;
112 CLOSE c_group_del;
113
114 IF l_group_id IS NOT NULL THEN
115
116 CSM_ACC_PKG.Delete_Acc
117 (P_PUBLICATION_ITEM_NAMES => g_grp_pubi_name
118 ,P_ACC_TABLE_NAME => g_grp_acc_table_name
119 ,P_PK1_NAME => g_grp_pk1_name
120 ,P_PK1_NUM_VALUE => l_group_id
121 ,P_USER_ID => p_user_id
122 );
123 END IF;
124
125 x_return_status := 'SUCCESS';
126 x_error_message := 'Group Data successfully Deleted';
127 CSM_UTIL_PKG.LOG('Leaving CSM_GROUP_DOWNLOAD_PVT.DELETE_MY_GROUP Proc ',
128 'CSM_GROUP_DOWNLOAD_PVT.DELETE_MY_GROUP',FND_LOG.LEVEL_STATEMENT);
129
130 EXCEPTION
131 WHEN others THEN
132 l_sqlerrno := to_char(SQLCODE);
133 l_sqlerrmsg := substr(SQLERRM, 1,2000);
134 x_return_status := 'ERROR';
135 x_error_message := 'l_sqlerrmsg';
136 l_message := 'Exception in CSM_GROUP_DOWNLOAD_PVT.DELETE_MY_GROUP Procedure : for accessid '
137 || l_access_id ||': with error ' || l_sqlerrno || ':' || l_sqlerrmsg;
138 CSM_UTIL_PKG.LOG(l_message, 'CSM_GROUP_DOWNLOAD_PVT.DELETE_MY_GROUP',FND_LOG.LEVEL_EXCEPTION);
139 END DELETE_MY_GROUP;
140
141 PROCEDURE INSERT_GROUP_ACC (p_user_id NUMBER
142 , p_group_id NUMBER
143 , p_owner_id NUMBER
144 , p_group_type VARCHAR2
145 , x_return_status OUT NOCOPY VARCHAR2
146 , x_error_message OUT NOCOPY VARCHAR2)
147 IS
148 --CURSOR declarations
149 CURSOR c_rel_group_ins(b_group_id IN NUMBER)
150 IS
151 SELECT 1
152 FROM JTF_RS_GROUPS_B jrgb
153 WHERE jrgb.GROUP_ID = b_group_id
154 AND NVL(jrgb.END_DATE_ACTIVE,SYSDATE) >= TRUNC(SYSDATE);
155
156 --variable declarations
157 l_sqlerrno VARCHAR2(20);
158 l_sqlerrmsg VARCHAR2(2000);
159 l_message VARCHAR2(3000);
160 l_chk_rel_grp NUMBER;
161
162 BEGIN
163
164 CSM_UTIL_PKG.LOG('Entering CSM_GROUP_DOWNLOAD_PVT.INSERT_GROUP_ACC Proc ',
165 'CSM_GROUP_DOWNLOAD.INSERT_GROUP_ACC',FND_LOG.LEVEL_STATEMENT);
166
167 OPEN c_rel_group_ins(p_group_id);
168 FETCH c_rel_group_ins INTO l_chk_rel_grp;
169
170 IF (c_rel_group_ins%NOTFOUND) THEN
171 x_error_message := 'the related group id -'||p_group_id|| ' does not exists in JTF_RS_GROUPS_B base table';
172 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
173 END IF;
174
175 CLOSE c_rel_group_ins;
176
177 IF p_user_id IS NOT NULL AND p_group_id IS NOT NULL THEN
178 CSM_ACC_PKG.Insert_Acc
179 (P_PUBLICATION_ITEM_NAMES => g_grp_pubi_name
180 ,P_ACC_TABLE_NAME => g_grp_acc_table_name
181 ,P_SEQ_NAME => g_grp_seq_name
182 ,P_PK1_NAME => g_grp_pk1_name
183 ,P_PK1_NUM_VALUE => p_group_id
184 ,P_USER_ID => p_user_id
185 );
186
187 UPDATE CSM_GROUPS
188 SET GROUP_TYPE = p_group_type,
189 GROUP_OWNER_ID = p_owner_id
190 WHERE GROUP_ID = p_group_id
191 AND USER_ID = p_user_id;
192
193 END IF;
194
195 IF (c_rel_group_ins%ISOPEN) THEN
196
197 CLOSE c_rel_group_ins;
198
199 END IF;
200
201 x_return_status := 'SUCCESS';
202 x_error_message := 'Group Data successfully Inserted';
203
204 CSM_UTIL_PKG.LOG('Leaving CSM_GROUP_DOWNLOAD_PVT.INSERT_GROUP_ACC Proc ',
205 'CSM_GROUP_DOWNLOAD_PVT.INSERT_GROUP_ACC',FND_LOG.LEVEL_STATEMENT);
206
207 EXCEPTION
208 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
209 x_return_status := 'ERROR';
210 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_GROUP_DOWNLOAD_PVT.INSERT_GROUP_ACC',FND_LOG.LEVEL_EXCEPTION);
211 WHEN others THEN
212 l_sqlerrno := to_char(SQLCODE);
213 l_sqlerrmsg := substr(SQLERRM, 1,2000);
214 x_return_status := 'ERROR';
215 x_error_message := l_sqlerrmsg;
216 l_message := 'Exception in CSM_GROUP_DOWNLOAD_PVT.INSERT_GROUP_ACC Procedure :' || l_sqlerrno || ':' || l_sqlerrmsg;
217 CSM_UTIL_PKG.LOG(l_message, 'CSM_GROUP_DOWNLOAD_PVT.INSERT_GROUP_ACC',FND_LOG.LEVEL_EXCEPTION);
218 END INSERT_GROUP_ACC;
219
220
221 --The Group which was downloaded for the user has to be deleted from the
222 --CSM_GROUPS table only if there are no Mobile Users from the Group.
223
224 PROCEDURE DELETE_GROUP_ACC ( p_user_id NUMBER
225 , p_group_id NUMBER
226 , x_return_status OUT NOCOPY VARCHAR2
227 , x_error_message OUT NOCOPY VARCHAR2)
228 IS
229
230 --variable declarations
231 l_group_id CSM_GROUPS.GROUP_ID%TYPE;
232 l_sqlerrno VARCHAR2(20);
233 l_sqlerrmsg VARCHAR2(2000);
234 l_message VARCHAR2(3000);
235 l_access_id CSM_GROUPS.ACCESS_ID%TYPE;
236
237 BEGIN
238 CSM_UTIL_PKG.LOG('Entering CSM_GROUP_DOWNLOAD_PVT.DELETE_GROUP_ACC Proc ',
239 'CSM_GROUP_DOWNLOAD_PVT.DELETE_GROUP_ACC',FND_LOG.LEVEL_STATEMENT);
240
241 IF p_user_id IS NOT NULL AND p_group_id IS NOT NULL THEN
242
243 CSM_ACC_PKG.Delete_Acc
244 (P_PUBLICATION_ITEM_NAMES => g_grp_pubi_name
245 ,P_ACC_TABLE_NAME => g_grp_acc_table_name
246 ,P_PK1_NAME => g_grp_pk1_name
247 ,P_PK1_NUM_VALUE => p_group_id
248 ,P_USER_ID => p_user_id
249 );
250 END IF;
251
252 x_return_status := 'SUCCESS';
253 x_error_message := 'Group Data successfully Deleted';
254 CSM_UTIL_PKG.LOG('Leaving CSM_GROUP_DOWNLOAD_PVT.DELETE_GROUP_ACC Proc ',
255 'CSM_GROUP_DOWNLOAD_PVT.DELETE_GROUP_ACC',FND_LOG.LEVEL_STATEMENT);
256
257 EXCEPTION
258 WHEN others THEN
259 l_sqlerrno := to_char(SQLCODE);
260 l_sqlerrmsg := substr(SQLERRM, 1,2000);
261 x_return_status := 'ERROR';
262 x_error_message := 'l_sqlerrmsg';
263 l_message := 'Exception in CSM_GROUP_DOWNLOAD_PVT.DELETE_GROUP_ACC Procedure : for accessid '
264 || l_access_id ||': with error ' || l_sqlerrno || ':' || l_sqlerrmsg;
265 CSM_UTIL_PKG.LOG(l_message, 'CSM_GROUP_DOWNLOAD_PVT.DELETE_GROUP_ACC',
266 FND_LOG.LEVEL_EXCEPTION);
267 END DELETE_GROUP_ACC;
268
269 END CSM_GROUP_DOWNLOAD_PVT;