[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_ROLE_RELATE_AUD_PVT
Source
1 Package Body JTF_RS_ROLE_RELATE_AUD_PVT AS
2 /* $Header: jtfrsalb.pls 120.0 2005/05/11 08:19:07 appldev ship $ */
3 -- API Name : JTF_RS_ROLE_RELATE_AUD_PVT
4 -- Type : Private
5 -- Purpose : Inserts IN the JTF_RS_ROLE_RELATE_AUD
6 -- Modification History
7 -- DATE NAME PURPOSE
8 -- 20 JAN 2000 S Choudhury Created
9 -- Notes:
10 --
11
12 --DECLARE GLOBAL VARIABLE
13
14 g_pkg_name varchar2(30) := 'JTF_RS_ROLE_RELATE_AUD_PVT ';
15 /* FOR INSERT */
16
17 PROCEDURE INSERT_ROLE_RELATE(
18 P_API_VERSION IN NUMBER,
19 P_INIT_MSG_LIST IN VARCHAR2,
20 P_COMMIT IN VARCHAR2,
21 P_ROLE_RELATE_ID IN NUMBER,
22 P_ROLE_RESOURCE_TYPE IN VARCHAR2,
23 P_ROLE_RESOURCE_ID IN NUMBER,
24 P_ROLE_ID IN NUMBER,
25 P_START_DATE_ACTIVE IN DATE,
26 P_END_DATE_ACTIVE IN DATE,
27 P_OBJECT_VERSION_NUMBER IN NUMBER,
28 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
29 X_MSG_COUNT OUT NOCOPY NUMBER,
30 X_MSG_DATA OUT NOCOPY VARCHAR2 )
31 IS
32
33 l_role_relate_aud_id jtf_rs_role_relate_aud.role_relate_audit_id%type;
34 l_row_id varchar2(24) := null;
35
36 --other variables
37 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_ROLE_RELATE';
38 l_api_version CONSTANT NUMBER :=1.0;
39 l_date Date := sysdate;
40 l_user_id Number;
41 l_login_id Number;
42
43 BEGIN
44
45 --Standard Start of API SAVEPOINT
46 SAVEPOINT ROLE_RELATE_AUDIT;
47
48 x_return_status := fnd_api.g_ret_sts_success;
49
50 --Standard Call to check API compatibility
51 IF NOT FND_API.Compatible_API_CALL(l_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
52 THEN
53 RAISE FND_API.G_EXC_ERROR;
54 END IF;
55
56 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
57 IF FND_API.To_boolean(P_INIT_MSG_LIST)
58 THEN
59 FND_MSG_PUB.Initialize;
60 END IF;
61
62 l_date := sysdate;
63 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
64 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
65
66
67 select jtf_rs_role_relate_aud_s.nextval
68 into l_role_relate_aud_id
69 from dual;
70
71 /* CALL TABLE HANDLER */
72 JTF_RS_ROLE_RELATE_AUD_PKG.INSERT_ROW (
73 X_ROWID => l_row_id,
74 X_ROLE_RELATE_AUDIT_ID => l_role_relate_aud_id,
75 X_ROLE_RELATE_ID => p_role_relate_id,
76 X_NEW_ROLE_RESOURCE_TYPE => p_role_resource_type,
77 X_OLD_ROLE_RESOURCE_TYPE => null,
78 X_NEW_ROLE_RESOURCE_ID => p_role_resource_id,
79 X_OLD_ROLE_RESOURCE_ID => null,
80 X_NEW_ROLE_ID => p_role_id,
81 X_OLD_ROLE_ID => null,
82 X_NEW_START_DATE_ACTIVE => p_start_date_active,
83 X_OLD_START_DATE_ACTIVE => null,
84 X_NEW_END_DATE_ACTIVE => p_end_date_active,
85 X_OLD_END_DATE_ACTIVE => null,
86 X_NEW_OBJECT_VERSION_NUMBER => p_object_version_number,
87 X_OLD_OBJECT_VERSION_NUMBER => null,
88 X_CREATION_DATE => l_date,
89 X_CREATED_BY => l_user_id,
90 X_LAST_UPDATE_DATE => l_date,
91 X_LAST_UPDATED_BY => l_user_id,
92 X_LAST_UPDATE_LOGIN => l_login_id
93 );
94
95 IF fnd_api.to_boolean (p_commit)
96 THEN
97 COMMIT WORK;
98 END IF;
99
100 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
101
102 EXCEPTION
103 WHEN fnd_api.g_exc_unexpected_error
104 THEN
105 ROLLBACK TO role_relate_audit;
106 --x_return_status := fnd_api.g_ret_sts_unexp_error;
107 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
108 WHEN fnd_api.g_exc_error
109 THEN
110 ROLLBACK TO role_relate_audit;
111 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
112
113 WHEN OTHERS
114 THEN
115 ROLLBACK TO role_relate_audit;
116 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
117 fnd_message.set_token('P_SQLCODE',SQLCODE);
118 fnd_message.set_token('P_SQLERRM',SQLERRM);
119 fnd_message.set_token('P_API_NAME',L_API_NAME);
120 FND_MSG_PUB.add;
121 x_return_status := fnd_api.g_ret_sts_unexp_error;
122 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
123
124
125 END INSERT_ROLE_RELATE;
126
127
128
129 /*FOR UPDATE */
130 PROCEDURE UPDATE_ROLE_RELATE(
131 P_API_VERSION IN NUMBER,
132 P_INIT_MSG_LIST IN VARCHAR2,
133 P_COMMIT IN VARCHAR2,
134 P_ROLE_RELATE_ID IN NUMBER,
135 P_ROLE_RESOURCE_TYPE IN VARCHAR2,
136 P_ROLE_RESOURCE_ID IN NUMBER,
137 P_ROLE_ID IN NUMBER,
138 P_START_DATE_ACTIVE IN DATE,
139 P_END_DATE_ACTIVE IN DATE,
140 P_OBJECT_VERSION_NUMBER IN NUMBER,
141 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
142 X_MSG_COUNT OUT NOCOPY NUMBER,
143 X_MSG_DATA OUT NOCOPY VARCHAR2)
144 IS
145 CURSOR rr_old_cur(l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)
146 IS
147 SELECT role_resource_id,
148 role_resource_type,
149 role_id,
150 start_date_active,
151 end_date_active,
152 object_version_number
153 FROM jtf_rs_role_relations
154 WHERE role_relate_id = l_role_relate_id;
155
156
157 --declare variables
158 --old value
159 l_role_resource_id JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_ID%TYPE := null ;
160 l_role_resource_type JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_TYPE%TYPE := NULL;
161 l_role_id JTF_RS_ROLE_RELATIONS.ROLE_ID%TYPE := null ;
162 l_start_date_active JTF_RS_ROLE_RELATIONS.START_DATE_ACTIVE%TYPE := null ;
163 l_end_date_active JTF_RS_ROLE_RELATIONS.END_DATE_ACTIVE%TYPE := null ;
164 l_object_version_number JTF_RS_ROLE_RELATIONS.OBJECT_VERSION_NUMBER%TYPE := NULL;
165
166
167
168 --new values
169 l_ROLE_resource_id_n JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_ID%TYPE := null ;
170 l_role_resource_type_n JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_TYPE%TYPE := NULL;
171 l_role_id_n JTF_RS_ROLE_RELATIONS.ROLE_ID%TYPE := null ;
172 l_start_date_active_n JTF_RS_ROLE_RELATIONS.START_DATE_ACTIVE%TYPE := null ;
173 l_end_date_active_n JTF_RS_ROLE_RELATIONS.END_DATE_ACTIVE%TYPE := null ;
174 l_object_version_number_n JTF_RS_ROLE_RELATIONS.OBJECT_VERSION_NUMBER%TYPE := NULL;
175
176
177
178 rr_old_rec rr_old_cur%rowtype;
179 l_role_relate_aud_id jtf_rs_role_relate_aud.role_relate_audit_id%type;
180 l_row_id varchar2(24) := null;
181
182 --other variables
183 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ROLE_RELATE';
184 l_api_version CONSTANT NUMBER :=1.0;
185 l_date Date := sysdate;
186 l_user_id Number := 1;
187 l_login_id Number := 1;
188
189
190 BEGIN
191
192 --Standard Start of API SAVEPOINT
193 SAVEPOINT ROLE_RELATE_AUDIT;
194
195 x_return_status := fnd_api.g_ret_sts_success;
196
197 --Standard Call to check API compatibility
198 IF NOT FND_API.Compatible_API_CALL(l_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
199 THEN
200 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
201 END IF;
202
203 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
204 IF FND_API.To_boolean(P_INIT_MSG_LIST)
205 THEN
206 FND_MSG_PUB.Initialize;
207 END IF;
208
209 l_date := sysdate;
210 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
211 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
212
213
214 open rr_old_cur(p_role_relate_id);
215 FETCH rr_old_cur into rr_old_rec;
216 close rr_old_cur;
217
218 if p_role_resource_id <> nvl(rr_old_rec.role_resource_id,0)
219 then
220 l_role_resource_id := rr_old_rec.role_resource_id;
221 l_role_resource_id_n := p_role_resource_id;
222 end if;
223 if p_role_resource_type <> nvl(rr_old_rec.role_resource_type,'x')
224 then
225 l_role_resource_type := rr_old_rec.role_resource_type;
226 l_role_resource_type_n := p_role_resource_type;
227 end if;
228 if p_role_id <> nvl(rr_old_rec.role_id, 0)
229 then
230 l_role_id := rr_old_rec.role_id;
231 l_role_id_n:= p_role_id;
232 end if;
233 if p_start_date_active <> rr_old_rec.start_date_active
234 then
235 l_start_date_active := rr_old_rec.start_date_active;
236 l_start_date_active_n := p_start_date_active;
237 end if;
238 if /* (p_end_date_active <> rr_old_rec.end_date_active) OR
239 (p_end_date_active is null AND rr_old_rec.end_date_active <> FND_API.G_MISS_DATE) OR
240 (p_end_date_active is not null AND rr_old_rec.end_date_active = FND_API.G_MISS_DATE) */
241 nvl(p_end_date_active, fnd_api.g_miss_date) <> nvl(rr_old_rec.end_date_active, fnd_api.g_miss_date)
242 then
243 l_end_date_active := rr_old_rec.end_date_active ;
244 l_end_date_active_n := p_end_date_active ;
245 end if;
246 if p_object_version_number <> nvl(rr_old_rec.object_version_number,0)
247 then
248 l_object_version_number := rr_old_rec.object_version_number;
249 l_object_version_number_n := p_object_version_number;
250 end if;
251
252
253 select jtf_rs_role_relate_aud_s.nextval
254 into l_role_relate_aud_id
255 from dual;
256
257 /* CALL TABLE HANDLER */
258 JTF_RS_ROLE_RELATE_AUD_PKG.INSERT_ROW (
259 X_ROWID => l_row_id,
260 X_ROLE_RELATE_AUDIT_ID => l_role_relate_aud_id,
261 X_ROLE_RELATE_ID => p_role_relate_id,
262 X_NEW_ROLE_RESOURCE_TYPE => l_role_resource_type_n,
263 X_OLD_ROLE_RESOURCE_TYPE => l_role_resource_type,
264 X_NEW_ROLE_RESOURCE_ID => l_role_resource_id_n,
265 X_OLD_ROLE_RESOURCE_ID => l_role_resource_id,
266 X_NEW_ROLE_ID => l_role_id_n,
267 X_OLD_ROLE_ID => l_role_id,
268 X_NEW_START_DATE_ACTIVE => l_start_date_active_n,
269 X_OLD_START_DATE_ACTIVE => l_start_date_active,
270 X_NEW_END_DATE_ACTIVE => l_end_date_active_n,
271 X_OLD_END_DATE_ACTIVE => l_end_date_active,
272 X_NEW_OBJECT_VERSION_NUMBER => l_object_version_number_n,
273 X_OLD_OBJECT_VERSION_NUMBER => l_object_version_number,
274 X_CREATION_DATE => l_date,
275 X_CREATED_BY => l_user_id,
276 X_LAST_UPDATE_DATE => l_date,
277 X_LAST_UPDATED_BY => l_user_id,
278 X_LAST_UPDATE_LOGIN => l_login_id
279 );
280
281
282
283
284 IF fnd_api.to_boolean (p_commit)
285 THEN
286 COMMIT WORK;
287 END IF;
288
289 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
290
291
292 EXCEPTION
293 WHEN fnd_api.g_exc_unexpected_error
294 THEN
295 ROLLBACK TO role_relate_audit;
296 x_return_status := fnd_api.g_ret_sts_unexp_error;
297 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
298 WHEN fnd_api.g_exc_error
299 THEN
300 ROLLBACK TO role_relate_audit;
301 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
302
303 WHEN OTHERS
304 THEN
305 ROLLBACK TO role_relate_audit;
306 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
307 fnd_message.set_token('P_SQLCODE',SQLCODE);
308 fnd_message.set_token('P_SQLERRM',SQLERRM);
309 fnd_message.set_token('P_API_NAME',l_api_name);
310 FND_MSG_PUB.add;
311 x_return_status := fnd_api.g_ret_sts_unexp_error;
312 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
313
314
315 END UPDATE_ROLE_RELATE;
316
317
318 --FOR DELETE
319
320 PROCEDURE DELETE_ROLE_RELATE(
321 P_API_VERSION IN NUMBER,
322 P_INIT_MSG_LIST IN VARCHAR2,
323 P_COMMIT IN VARCHAR2,
324 P_ROLE_RELATE_ID IN NUMBER,
325 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
326 X_MSG_COUNT OUT NOCOPY NUMBER,
327 X_MSG_DATA OUT NOCOPY VARCHAR2 )
328 IS
329 CURSOR rr_old_cur(l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)
330 IS
331 SELECT role_resource_id,
332 role_resource_type,
333 role_id,
334 start_date_active,
335 end_date_active,
336 object_version_number
337 FROM jtf_rs_role_relations
338 WHERE role_relate_id = l_role_relate_id;
339
340
341 --declare variables
342 --old value
343 l_role_resource_id JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_ID%TYPE := null ;
344 l_role_resource_type JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_TYPE%TYPE := NULL;
345 l_role_id JTF_RS_ROLE_RELATIONS.ROLE_ID%TYPE := null ;
346 l_start_date_active JTF_RS_ROLE_RELATIONS.START_DATE_ACTIVE%TYPE := null ;
347 l_end_date_active JTF_RS_ROLE_RELATIONS.END_DATE_ACTIVE%TYPE := null ;
348 l_object_version_number JTF_RS_ROLE_RELATIONS.OBJECT_VERSION_NUMBER%TYPE := NULL;
349
350 rr_old_rec rr_old_cur%rowtype;
351 l_role_relate_aud_id jtf_rs_role_relate_aud.role_relate_audit_id%type;
352 l_row_id varchar2(24) := null;
353
354 --other variables
355 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_ROLE_RELATE';
356 l_api_version CONSTANT NUMBER :=1.0;
357 l_date Date := sysdate;
358 l_user_id Number;
359 l_login_id Number;
360
361
362
363
364 BEGIN
365
366 --Standard Start of API SAVEPOINT
367 SAVEPOINT ROLE_RELATE_AUDIT;
368
369 x_return_status := fnd_api.g_ret_sts_success;
370
371 --Standard Call to check API compatibility
372 IF NOT FND_API.Compatible_API_CALL(l_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
373 THEN
374 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
375 END IF;
376
377 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
378 IF FND_API.To_boolean(P_INIT_MSG_LIST)
379 THEN
380 FND_MSG_PUB.Initialize;
381 END IF;
382
383 l_date := sysdate;
384 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
385 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
386
387
388 open rr_old_cur(p_role_relate_id);
389 FETCH rr_old_cur into rr_old_rec;
390 close rr_old_cur;
391
392 l_role_resource_id := rr_old_rec.role_resource_id;
393 l_role_resource_type := rr_old_rec.role_resource_type;
394 l_role_id := rr_old_rec.role_id;
395 l_start_date_active := rr_old_rec.start_date_active;
396 l_end_date_active := rr_old_rec.end_date_active ;
397 l_object_version_number := rr_old_rec.object_version_number;
398
399
400
401
402 select jtf_rs_role_relate_aud_s.nextval
403 into l_role_relate_aud_id
404 from dual;
405
406 /* CALL TABLE HANDLER */
407 JTF_RS_ROLE_RELATE_AUD_PKG.INSERT_ROW (
408 X_ROWID => l_row_id,
409 X_ROLE_RELATE_AUDIT_ID => l_role_relate_aud_id,
410 X_ROLE_RELATE_ID => p_role_relate_id,
411 X_NEW_ROLE_RESOURCE_TYPE => null,
412 X_OLD_ROLE_RESOURCE_TYPE => l_role_resource_type,
413 X_NEW_ROLE_RESOURCE_ID => null,
414 X_OLD_ROLE_RESOURCE_ID => l_role_resource_id,
415 X_NEW_ROLE_ID => null,
416 X_OLD_ROLE_ID => l_role_id,
417 X_NEW_START_DATE_ACTIVE => null,
418 X_OLD_START_DATE_ACTIVE => l_start_date_active,
419 X_NEW_END_DATE_ACTIVE => null,
420 X_OLD_END_DATE_ACTIVE => l_end_date_active,
421 X_NEW_OBJECT_VERSION_NUMBER =>null,
422 X_OLD_OBJECT_VERSION_NUMBER => l_object_version_number,
423 X_CREATION_DATE => l_date,
424 X_CREATED_BY => l_user_id,
425 X_LAST_UPDATE_DATE => l_date,
426 X_LAST_UPDATED_BY => l_user_id,
427 X_LAST_UPDATE_LOGIN => l_login_id
428 );
429
430
431
432
433
434 IF fnd_api.to_boolean (p_commit)
435 THEN
436 COMMIT WORK;
437 END IF;
438
439 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
440
441 EXCEPTION
442 WHEN fnd_api.g_exc_unexpected_error
443 THEN
444 ROLLBACK TO role_relate_audit;
445 x_return_status := fnd_api.g_ret_sts_unexp_error;
446 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
447 WHEN fnd_api.g_exc_error
448 THEN
449 ROLLBACK TO role_relate_audit;
450 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
451
452 WHEN OTHERS
453 THEN
454 ROLLBACK TO role_relate_audit;
455 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
456 fnd_message.set_token('P_SQLCODE',SQLCODE);
457 fnd_message.set_token('P_SQLERRM',SQLERRM);
458 fnd_message.set_token('P_API_NAME',l_api_name);
459 FND_MSG_PUB.add;
460 x_return_status := fnd_api.g_ret_sts_unexp_error;
461 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
462
463
464
465 END DELETE_ROLE_RELATE;
466
467 END; -- Package Body JTF_RS_ROLE_RELATE_AUD_PVT