[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_GROUP_MEMBERS_AUD_PVT
Source
1 Package Body JTF_RS_GROUP_MEMBERS_AUD_PVT AS
2 /* $Header: jtfrsamb.pls 120.0 2005/05/11 08:19:09 appldev ship $ */
3 -- API Name : JTF_RS_GROUP_MEMBERS_AUD_PVT
4 -- Type : Private
5 -- Purpose : Inserts IN the JTF_RS_GROUPS_B_AUD
6 -- Modification History
7 -- DATE NAME PURPOSE
8 -- 20 JAN 2000 S Choudhury Created
9 -- Notes:
10 --
11
12 g_pkg_name varchar2(30) := 'JTF_RS_GROUP_MEMBERS_AUD_PVT';
13
14 /*FOR INSERT */
15 PROCEDURE INSERT_MEMBER(
16 P_API_VERSION IN NUMBER,
17 P_INIT_MSG_LIST IN VARCHAR2,
18 P_COMMIT IN VARCHAR2,
19 P_GROUP_MEMBER_ID IN NUMBER,
20 P_GROUP_ID IN NUMBER,
21 P_RESOURCE_ID IN NUMBER,
22 P_PERSON_ID IN NUMBER,
23 P_OBJECT_VERSION_NUMBER IN NUMBER,
24 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
25 X_MSG_COUNT OUT NOCOPY NUMBER,
26 X_MSG_DATA OUT NOCOPY VARCHAR2)
27 IS
28
29 l_group_member_audit_id jtf_rs_group_members_aud.group_member_audit_id%type;
30 l_row_id varchar2(24) := null;
31
32 --other variables
33 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_MEMBER';
34 l_api_version CONSTANT NUMBER := 1.0;
35 l_date Date ;
36 l_user_id Number;
37 l_login_id Number;
38
39
40 BEGIN
41
42 --Standard Start of API SAVEPOINT
43 SAVEPOINT GROUP_AUDIT;
44
45 x_return_status := fnd_api.g_ret_sts_success;
46
47 --Standard Call to check API compatibility
48 IF NOT FND_API.Compatible_API_CALL(l_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
49 THEN
50 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
51 END IF;
52
53 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
54 IF FND_API.To_boolean(P_INIT_MSG_LIST)
55 THEN
56 FND_MSG_PUB.Initialize;
57 END IF;
58
59 l_date := sysdate;
60 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
61 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
62
63
64
65 select jtf_rs_group_members_aud_s.nextval
66 into l_group_member_audit_id
67 from dual;
68
69
70 /* CALL TABLE HANDLER */
71 JTF_RS_GROUP_MEMBERS_AUD_PKG.INSERT_ROW (
72 X_ROWID => l_row_id,
73 x_group_member_audit_id => l_group_member_audit_id,
74 x_group_member_id => p_group_member_id,
75 x_new_group_id => p_group_id,
76 x_old_group_id => jtf_rs_group_members_pvt.g_moved_fr_group_id,
77 x_new_resource_id => P_resource_id,
78 x_old_resource_id => null,
79 x_new_person_id => P_person_id,
80 x_old_person_id => null,
81 x_new_object_version_number => P_object_version_number,
82 X_OLD_object_version_number => null,
83 X_CREATION_DATE => l_date,
84 X_CREATED_BY => l_user_id ,
85 X_LAST_UPDATE_DATE => l_date,
86 X_LAST_UPDATED_BY => l_user_id,
87 X_LAST_UPDATE_LOGIN => l_login_id
88 );
89
90
91 IF fnd_api.to_boolean (p_commit)
92 THEN
93 COMMIT WORK;
94 END IF;
95
96 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
97
98
99 EXCEPTION
100 WHEN fnd_api.g_exc_unexpected_error
101 THEN
102 ROLLBACK TO group_audit;
103 x_return_status := fnd_api.g_ret_sts_unexp_error;
104 fnd_message.set_name ('JTF', 'JTF_RS_GROUP_MEM_AUD_ERR');
105 FND_MSG_PUB.add;
106 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
107 WHEN OTHERS
108 THEN
109 ROLLBACK TO group_audit;
110 x_return_status := fnd_api.g_ret_sts_unexp_error;
111 fnd_message.set_name ('JTF', 'JTF_RS_GROUP_MEM_AUD_ERR');
112 FND_MSG_PUB.add;
113 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
114
115 END INSERT_MEMBER;
116
117 /* FOR UPDATE */
118 PROCEDURE UPDATE_MEMBER(
119 P_API_VERSION IN NUMBER,
120 P_INIT_MSG_LIST IN VARCHAR2,
121 P_COMMIT IN VARCHAR2,
122 P_GROUP_MEMBER_ID IN NUMBER,
123 P_GROUP_ID IN NUMBER,
124 P_RESOURCE_ID IN NUMBER,
125 P_PERSON_ID IN NUMBER,
126 P_OBJECT_VERSION_NUMBER IN NUMBER,
127 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
128 X_MSG_COUNT OUT NOCOPY NUMBER,
129 X_MSG_DATA OUT NOCOPY VARCHAR2)
130 IS
131 CURSOR rr_old_cur(l_group_member_id JTF_RS_GROUP_MEMBERS.GROUP_MEMBER_ID%TYPE)
132 IS
133 SELECT b.group_id ,
134 b.resource_id ,
135 b.person_id ,
136 b.object_version_number
137 FROM jtf_rs_group_members b
138 WHERE b.group_member_id = l_group_member_id;
139
140
141 --declare variables
142 --old value
143 l_group_id JTF_RS_GROUP_MEMBERS.GROUP_ID%TYPE;
144 l_resource_id JTF_RS_GROUP_MEMBERS.RESOURCE_ID%TYPE;
145 l_person_id JTF_RS_GROUP_MEMBERS.PERSON_ID%TYPE;
146 l_object_version_number JTF_RS_GROUP_MEMBERS.OBJECT_VERSION_NUMBER%TYPE;
147
148
149
150 --old values
151 l_group_id_n JTF_RS_GROUP_MEMBERS.GROUP_ID%TYPE;
152 l_resource_id_n JTF_RS_GROUP_MEMBERS.RESOURCE_ID%TYPE;
153 l_person_id_n JTF_RS_GROUP_MEMBERS.PERSON_ID%TYPE;
154 l_object_version_number_n JTF_RS_GROUP_MEMBERS.OBJECT_VERSION_NUMBER%TYPE;
155
156
157
158
159 rr_old_rec rr_old_cur%rowtype;
160 l_group_member_audit_id jtf_rs_group_members_aud.group_member_audit_id%type;
161 l_row_id varchar2(24) := null;
162
163 --other variables
164 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_MEMBER';
165 l_api_version CONSTANT NUMBER :=1.0;
166 l_date Date ;
167 l_user_id Number ;
168 l_login_id Number ;
169
170
171 BEGIN
172
173 --Standard Start of API SAVEPOINT
174 SAVEPOINT GROUP_AUDIT;
175
176 x_return_status := fnd_api.g_ret_sts_success;
177
178 --Standard Call to check API compatibility
179 IF NOT FND_API.Compatible_API_CALL(l_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
180 THEN
181 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
182 END IF;
183
184 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
185 IF FND_API.To_boolean(P_INIT_MSG_LIST)
186 THEN
187 FND_MSG_PUB.Initialize;
188 END IF;
189
190 l_date := sysdate;
191 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
192 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
193
194
195 open rr_old_cur(p_group_member_id);
196 FETCH rr_old_cur into rr_old_rec;
197 close rr_old_cur;
198
199 if p_group_id <> nvl(rr_old_rec.group_id, 0)
200 then
201 l_group_id := rr_old_rec.group_id;
202 l_group_id_n := p_group_id;
203 end if;
204 if p_resource_id <> nvl(rr_old_rec.resource_id,0)
205 then
206 l_resource_id := rr_old_rec.resource_id;
207 l_resource_id_n:= p_resource_id;
208 end if;
209 if p_person_id <> nvl(rr_old_rec.person_id,0)
210 then
211 l_person_id := rr_old_rec.person_id ;
212 l_person_id_n:= p_person_id;
213 end if;
214 if p_object_version_number <> rr_old_rec.object_version_number
215 then
216 l_object_version_number := rr_old_rec.object_version_number;
217 l_object_version_number_n := p_object_version_number;
218 end if;
219
220
221
222 select jtf_rs_group_members_aud_s.nextval
223 into l_group_member_audit_id
224 from dual;
225
226
227 /* CALL TABLE HANDLER */
228 JTF_RS_GROUP_MEMBERS_AUD_PKG.INSERT_ROW (
229 X_ROWID => l_row_id,
230 x_group_member_audit_id => l_group_member_audit_id,
231 x_group_member_id => p_group_member_id,
232 x_new_group_id => l_group_id_n,
233 x_old_group_id => l_group_id,
234 x_new_resource_id => l_resource_id_n,
235 x_old_resource_id => l_resource_id,
236 x_new_person_id => l_person_id_n,
237 x_old_person_id => l_person_id,
238 X_new_object_version_number => l_object_version_number_n,
239 X_old_object_version_number => l_object_version_number,
240 X_CREATION_DATE => l_date,
241 X_CREATED_BY => l_user_id ,
242 X_LAST_UPDATE_DATE => l_date,
243 X_LAST_UPDATED_BY => l_user_id,
244 X_LAST_UPDATE_LOGIN => l_login_id
245 );
246
247
248 IF fnd_api.to_boolean (p_commit)
249 THEN
250 COMMIT WORK;
251 END IF;
252
253 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
254
255 EXCEPTION
256 WHEN fnd_api.g_exc_unexpected_error
257 THEN
258 ROLLBACK TO group_audit;
259 x_return_status := fnd_api.g_ret_sts_unexp_error;
260 fnd_message.set_name ('JTF', 'JTF_RS_GROUP_MEM_AUD_ERR');
261 FND_MSG_PUB.add;
262 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
263 WHEN OTHERS
264 THEN
265 ROLLBACK TO group_audit;
266 x_return_status := fnd_api.g_ret_sts_unexp_error;
267 fnd_message.set_name ('JTF', 'JTF_RS_GROUP_MEM_AUD_ERR');
268 FND_MSG_PUB.add;
269 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
270
271 END UPDATE_MEMBER;
272
273
274 --FOR DELETE
275
276 PROCEDURE DELETE_MEMBER(
277 P_API_VERSION IN NUMBER,
278 P_INIT_MSG_LIST IN VARCHAR2,
279 P_COMMIT IN VARCHAR2,
280 P_GROUP_MEMBER_ID IN NUMBER,
281 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
282 X_MSG_COUNT OUT NOCOPY NUMBER,
283 X_MSG_DATA OUT NOCOPY VARCHAR2 )
284 IS
285 CURSOR rr_old_cur(l_group_member_id JTF_RS_GROUP_MEMBERS.GROUP_MEMBER_ID%TYPE)
286 IS
287 SELECT b.group_id ,
288 b.resource_id ,
289 b.person_id ,
290 b.object_version_number
291 FROM jtf_rs_group_members b
292 WHERE b.group_member_id = l_group_member_id;
293
294
295 --declare variables
296 --old value
297 l_group_id JTF_RS_GROUP_MEMBERS.GROUP_ID%TYPE;
298 l_resource_id JTF_RS_GROUP_MEMBERS.RESOURCE_ID%TYPE;
299 l_person_id JTF_RS_GROUP_MEMBERS.PERSON_ID%TYPE;
300 l_object_version_number JTF_RS_GROUP_MEMBERS.object_version_number%TYPE;
301
302
303
304
305 rr_old_rec rr_old_cur%rowtype;
306 l_group_member_audit_id jtf_rs_group_members_aud.group_member_audit_id%type;
307 l_row_id varchar2(24) := null;
308
309 --other variables
310 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_MEMBER';
311 l_api_version CONSTANT NUMBER :=1.0;
312 l_date Date := sysdate;
313 l_user_id Number := 1;
314 l_login_id Number := 1;
315
316
317 BEGIN
318
319 --Standard Start of API SAVEPOINT
320 SAVEPOINT GROUP_AUDIT;
321
322 x_return_status := fnd_api.g_ret_sts_success;
323
324 --Standard Call to check API compatibility
325 IF NOT FND_API.Compatible_API_CALL(l_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
326 THEN
327 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
328 END IF;
329
330 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
331 IF FND_API.To_boolean(P_INIT_MSG_LIST)
332 THEN
333 FND_MSG_PUB.Initialize;
334 END IF;
335
336 l_date := sysdate;
337 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
338 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
339
340
341 open rr_old_cur(p_group_member_id);
342 FETCH rr_old_cur into rr_old_rec;
343 close rr_old_cur;
344
345 l_group_id := rr_old_rec.group_id;
346 l_resource_id := rr_old_rec.resource_id;
347 l_person_id := rr_old_rec.person_id ;
348 l_object_version_number:= rr_old_rec.object_version_number;
349
350
351
352
353 select jtf_rs_group_members_aud_s.nextval
354 into l_group_member_audit_id
355 from dual;
356
357
358 /* CALL TABLE HANDLER */
359 JTF_RS_GROUP_MEMBERS_AUD_PKG.INSERT_ROW (
360 X_ROWID => l_row_id,
361 x_group_member_audit_id => l_group_member_audit_id,
362 x_group_member_id => p_group_member_id,
363 x_new_group_id => NULL,
364 x_old_group_id => l_group_id,
365 x_new_resource_id => NULL,
366 x_old_resource_id => l_resource_id,
367 x_new_person_id => NULL,
368 x_old_person_id => l_person_id,
369 X_NEW_object_version_number => NULL,
370 X_old_object_version_number => l_object_version_number,
371 X_CREATION_DATE => l_date,
372 X_CREATED_BY => l_user_id ,
373 X_LAST_UPDATE_DATE => l_date,
374 X_LAST_UPDATED_BY => l_user_id,
375 X_LAST_UPDATE_LOGIN => l_login_id
376 );
377
378
379 IF fnd_api.to_boolean (p_commit)
380 THEN
381 COMMIT WORK;
382 END IF;
383
384 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
385
386
387 EXCEPTION
388 WHEN fnd_api.g_exc_unexpected_error
389 THEN
390 ROLLBACK TO group_audit;
391 x_return_status := fnd_api.g_ret_sts_unexp_error;
392 fnd_message.set_name ('JTF', 'JTF_RS_GROUP_MEM_AUD_ERR');
393 FND_MSG_PUB.add;
394 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
395 WHEN OTHERS
396 THEN
397 ROLLBACK TO group_audit;
398 x_return_status := fnd_api.g_ret_sts_unexp_error;
399 fnd_message.set_name ('JTF', 'JTF_RS_GROUP_MEM_AUD_ERR');
400 FND_MSG_PUB.add;
401 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
402
403 END delete_member;
404 END; -- Package Body JTF_RS_GROUP_MEMBERS_AUD_PVT