[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_GROUPS_AUD_PVT
Source
1 Package Body JTF_RS_GROUPS_AUD_PVT AS
2 /* $Header: jtfrsagb.pls 120.0.12010000.3 2009/05/12 05:09:23 rgokavar ship $ */
3 -- API Name : JTF_RS_GROUPS_AUD_PVT
4 -- Type : Private
5 -- Purpose : Inserts IN the JTF_RS_GROUPS_AUD_VL
6 -- Modification History
7 -- DATE NAME PURPOSE
8 -- 17 Jan 2000 S Choudhury Created
9 -- Notes:
10 --
11 g_pkg_name varchar2(30) := 'JTF_RS_GROUPS_AUD_PVT';
12
13 /*FOR INSERT */
14 PROCEDURE INSERT_GROUP(
15 P_API_VERSION IN NUMBER,
16 P_INIT_MSG_LIST IN VARCHAR2,
17 P_COMMIT IN VARCHAR2,
18 P_GROUP_ID IN NUMBER,
19 P_GROUP_NUMBER IN VARCHAR2,
20 P_EMAIL_ADDRESS IN VARCHAR2,
21 P_START_DATE_ACTIVE IN DATE,
22 P_END_DATE_ACTIVE IN DATE,
23 P_ACCOUNTING_CODE IN VARCHAR2,
24 P_EXCLUSIVE_FLAG IN VARCHAR2,
25 P_GROUP_NAME IN VARCHAR2,
26 P_GROUP_DESC IN VARCHAR2,
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 P_TIME_ZONE IN NUMBER)
32 IS
33
34 l_group_audit_id jtf_rs_groups_aud_b.group_audit_id%type;
35 l_row_id varchar2(24) := null;
36
37 --other variables
38 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_GROUP';
39 l_api_version CONSTANT NUMBER :=1.0;
40 l_date Date := sysdate;
41 l_user_id Number := 1;
42 l_login_id Number := 1;
43
44
45 BEGIN
46
47 --Standard Start of API SAVEPOINT
48 SAVEPOINT GROUP_AUDIT;
49
50 x_return_status := fnd_api.g_ret_sts_success;
51
52 --Standard Call to check API compatibility
53 IF NOT FND_API.Compatible_API_CALL(l_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
54 THEN
55 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
56 END IF;
57
58 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
59 IF FND_API.To_boolean(P_INIT_MSG_LIST)
60 THEN
61 FND_MSG_PUB.Initialize;
62 END IF;
63
64 l_date := sysdate;
65 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
66 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
67
68
69 select jtf_rs_groups_audit_s.nextval
70 into l_group_audit_id
71 from dual;
72
73 /* CALL TABLE HANDLER */
74 JTF_RS_GROUPS_AUD_PKG.INSERT_ROW (
75 X_ROWID => l_row_id,
76 x_group_audit_id => l_group_audit_id,
77 x_group_id => p_group_id,
78 x_new_group_number => p_group_number,
79 x_old_group_number => null,
80 x_new_email_address => p_email_address,
81 x_old_email_address => null,
82 x_new_exclusive_flag => p_exclusive_flag,
83 x_old_exclusive_flag => null,
84 x_new_start_date_active => p_start_date_active,
85 x_old_start_date_active => null ,
86 x_new_end_date_active => p_end_date_active,
87 x_old_end_date_active => null,
88 x_new_accounting_code => p_accounting_code,
89 x_old_accounting_code => null,
90 x_new_object_version_number => p_object_version_number ,
91 x_old_object_version_number => null,
92 x_new_group_name => p_group_name,
93 x_old_group_name => null,
94 x_new_group_desc => p_group_desc,
95 x_old_group_desc => null,
96 x_creation_date => l_date,
97 x_created_by => l_user_id ,
98 x_last_update_date => l_date,
99 x_last_updated_by => l_user_id,
100 x_last_update_login => l_login_id,
101 x_new_time_zone => p_time_zone,
102 x_old_time_zone => null
103 );
104
105
106
107 IF fnd_api.to_boolean (p_commit)
108 THEN
109 COMMIT WORK;
110 END IF;
111
112 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
113
114
115 EXCEPTION
116 WHEN fnd_api.g_exc_unexpected_error
117 THEN
118 ROLLBACK TO group_audit;
119 fnd_message.set_name ('JTF', 'JTF_RS_GROUP_AUDIT_ERR');
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 WHEN OTHERS
124 THEN
125 ROLLBACK TO group_audit;
126 fnd_message.set_name ('JTF', 'JTF_RS_GROUP_AUDIT_ERR');
127 FND_MSG_PUB.add;
128 x_return_status := fnd_api.g_ret_sts_unexp_error;
129 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
130
131 END INSERT_GROUP;
132
133 /* FOR UPDATE */
134
135 PROCEDURE UPDATE_GROUP(
136 P_API_VERSION IN NUMBER,
137 P_INIT_MSG_LIST IN VARCHAR2,
138 P_COMMIT IN VARCHAR2,
139 P_GROUP_ID IN NUMBER,
140 P_GROUP_NUMBER IN VARCHAR2,
141 P_EMAIL_ADDRESS IN VARCHAR2,
142 P_START_DATE_ACTIVE IN DATE,
143 P_END_DATE_ACTIVE IN DATE,
144 P_ACCOUNTING_CODE IN VARCHAR2,
145 P_EXCLUSIVE_FLAG IN VARCHAR2,
146 P_GROUP_NAME IN VARCHAR2,
147 P_GROUP_DESC IN VARCHAR2,
148 P_OBJECT_VERSION_NUMBER IN NUMBER,
149 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
150 X_MSG_COUNT OUT NOCOPY NUMBER,
151 X_MSG_DATA OUT NOCOPY VARCHAR2,
152 P_TIME_ZONE IN NUMBER )
153 IS
154 CURSOR rr_old_cur(l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE)
155 IS
156 SELECT b.group_number ,
157 b.exclusive_flag ,
158 b.start_date_active ,
159 b.end_date_active ,
160 b.accounting_code ,
161 b.object_version_number,
162 b.email_address,
163 b.group_name,
164 b.group_desc,
165 b.time_zone
166 FROM jtf_rs_groups_vl B
167 WHERE b.group_id = l_group_id;
168
169
170
171 --declare variables
172 --old value
173 l_group_number JTF_RS_GROUPS_B.GROUP_NUMBER%TYPE;
174 l_exclusive_flag JTF_RS_GROUPS_B.EXCLUSIVE_FLAG%TYPE;
175 l_start_date_active JTF_RS_GROUPS_B.START_DATE_ACTIVE%TYPE;
176 l_end_date_active JTF_RS_GROUPS_B.END_DATE_ACTIVE%TYPE;
177 l_accounting_code JTF_RS_GROUPS_B.ACCOUNTING_CODE%TYPE;
178 l_object_version_number JTF_RS_GROUPS_B.OBJECT_VERSION_NUMBER%TYPE;
179 l_email_address JTF_RS_GROUPS_B.EMAIL_ADDRESS%TYPE;
180 l_group_name JTF_RS_GROUPS_VL.GROUP_NAME%TYPE := null ;
181 l_group_desc JTF_RS_GROUPS_VL.GROUP_DESC%TYPE := null ;
182 l_time_zone JTF_RS_GROUPS_B.TIME_ZONE%TYPE;
183
184
185
186
187
188 --old values
189 l_group_number_n JTF_RS_GROUPS_B.GROUP_NUMBER%TYPE;
190 l_exclusive_flag_n JTF_RS_GROUPS_B.EXCLUSIVE_FLAG%TYPE;
191 l_start_date_active_n JTF_RS_GROUPS_B.START_DATE_ACTIVE%TYPE;
192 l_end_date_active_n JTF_RS_GROUPS_B.END_DATE_ACTIVE%TYPE;
193 l_accounting_code_n JTF_RS_GROUPS_B.ACCOUNTING_CODE%TYPE;
194 l_object_version_number_n JTF_RS_GROUPS_B.OBJECT_VERSION_NUMBER%TYPE;
195 l_email_address_n JTF_RS_GROUPS_B.EMAIL_ADDRESS%TYPE;
196 l_group_name_n JTF_RS_GROUPS_VL.GROUP_NAME%TYPE := null ;
197 l_group_desc_n JTF_RS_GROUPS_VL.GROUP_DESC%TYPE := null ;
198 l_time_zone_n JTF_RS_GROUPS_B.TIME_ZONE%TYPE;
199
200 rr_old_rec rr_old_cur%rowtype;
201
202 l_group_audit_id jtf_rs_groups_aud_b.group_audit_id%type;
203 l_row_id varchar2(24) := null;
204
205 --other variables
206 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_GROUP';
207 l_api_version CONSTANT NUMBER :=1.0;
208 l_date Date := sysdate;
209 l_user_id Number := 1;
210 l_login_id Number := 1;
211
212
213 BEGIN
214
215 --Standard Start of API SAVEPOINT
216 SAVEPOINT GROUP_AUDIT;
217
218 x_return_status := fnd_api.g_ret_sts_success;
219
220 --Standard Call to check API compatibility
221 IF NOT FND_API.Compatible_API_CALL(l_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
222 THEN
223 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
224 END IF;
225
226 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
227 IF FND_API.To_boolean(P_INIT_MSG_LIST)
228 THEN
229 FND_MSG_PUB.Initialize;
230 END IF;
231
232 l_date := sysdate;
233 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
234 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
235
236
237 open rr_old_cur(p_group_id);
238 FETCH rr_old_cur into rr_old_rec;
239 close rr_old_cur;
240
241 if p_group_number <> nvl(rr_old_rec.group_number, 'x')
242 then
243 l_group_number := rr_old_rec.group_number;
244 l_group_number_n := p_group_number;
245 end if;
246 if p_start_date_active <> rr_old_rec.start_date_active
247 then
248 l_start_date_active := rr_old_rec.start_date_active;
249 l_start_date_active_n := p_start_date_active;
250 end if;
251 if (p_end_date_active <> rr_old_rec.end_date_active) OR
252 /* (p_end_date_active is null AND rr_old_rec.end_date_active <> FND_API.G_MISS_DATE) OR
253 (p_end_date_active is not null AND rr_old_rec.end_date_active = FND_API.G_MISS_DATE) */
254 /* Modified the above date validation to fix bug # 2760129 */
255 (p_end_date_active is null AND rr_old_rec.end_date_active is NOT NULL) OR
256 (p_end_date_active is not null AND rr_old_rec.end_date_active is NULL)
257 then
258 l_end_date_active := rr_old_rec.end_date_active ;
259 l_end_date_active_n := p_end_date_active ;
260 end if;
261 if p_accounting_code <> nvl(rr_old_rec.accounting_code , 'x')
262 then
263 l_accounting_code := rr_old_rec.accounting_code ;
264 l_accounting_code_n:= p_accounting_code ;
265 end if;
266 if p_group_name <> nvl(rr_old_rec.group_name, 'x')
267 then
268 l_group_name := rr_old_rec.group_name ;
269 l_group_name_n := p_group_name;
270 end if;
271 if p_group_desc <> nvl(rr_old_rec.group_desc, 'x')
272 then
273 l_group_desc := rr_old_rec.group_desc ;
274 l_group_desc_n := p_group_desc ;
275 end if;
276 if p_accounting_code <> rr_old_rec.accounting_code
277 then
278 l_accounting_code := rr_old_rec.group_desc ;
279 l_accounting_code_n := p_accounting_code ;
280 end if;
281 if p_object_version_number <> rr_old_rec.object_version_number
282 then
283 l_object_version_number := rr_old_rec.object_version_number ;
284 l_object_version_number_n := p_object_version_number ;
285 end if;
286 if nvl(p_email_address,'r') <> nvl(rr_old_rec.email_address, 'r')
287 then
288 l_email_address := rr_old_rec.email_address ;
289 l_email_address_n := p_email_address;
290 end if;
291 if p_time_zone <> nvl(rr_old_rec.time_zone, -99)
292 then
293 l_time_zone := rr_old_rec.time_zone;
294 l_time_zone_n := p_time_zone;
295 end if;
296
297 select jtf_rs_groups_audit_s.nextval
298 into l_group_audit_id
299 from dual;
300
301 /* CALL TABLE HANDLER */
302 JTF_RS_GROUPS_AUD_PKG.INSERT_ROW (
303 X_ROWID => l_row_id,
304 x_group_audit_id => l_group_audit_id,
305 x_group_id => p_group_id,
306 x_new_group_number => l_group_number_n,
307 x_old_group_number => l_group_number,
308 x_new_email_address => l_email_address_n,
309 x_old_email_address => l_email_address,
310 x_new_exclusive_flag => l_exclusive_flag_n,
311 x_old_exclusive_flag => l_exclusive_flag,
312 X_NEW_START_DATE_ACTIVE => l_start_date_active_n,
313 X_OLD_START_DATE_ACTIVE => l_start_date_active,
314 X_NEW_END_DATE_ACTIVE => l_end_date_active_n,
315 X_OLD_END_DATE_ACTIVE => l_end_date_active,
316 x_new_accounting_code => l_accounting_code_n,
317 x_old_accounting_code => l_accounting_code,
318 x_new_object_version_number => l_object_version_number_n,
319 x_old_object_version_number => l_object_version_number,
320 x_new_group_name => l_group_name_n,
321 x_old_group_name => l_group_name,
322 x_new_group_desc => l_group_desc_n,
323 x_old_group_desc => l_group_desc,
324 x_creation_date => l_date,
325 x_created_by => l_user_id ,
326 x_last_update_date => l_date,
327 x_last_updated_by => l_user_id,
328 x_last_update_login => l_login_id,
329 x_new_time_zone => l_time_zone_n,
330 x_old_time_zone => l_time_zone
331 );
332
333 IF fnd_api.to_boolean (p_commit)
334 THEN
335 COMMIT WORK;
336 END IF;
337
338 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
339
340
341 EXCEPTION
342 WHEN fnd_api.g_exc_unexpected_error
343 THEN
344 ROLLBACK TO group_audit;
345 fnd_message.set_name ('JTF', 'JTF_RS_GROUP_AUDIT_ERR');
346 FND_MSG_PUB.add;
347 x_return_status := fnd_api.g_ret_sts_unexp_error;
348 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
349 WHEN OTHERS
350 THEN
351 ROLLBACK TO group_audit;
352 fnd_message.set_name ('JTF', 'JTF_RS_GROUP_AUDIT_ERR');
353 FND_MSG_PUB.add;
354 x_return_status := fnd_api.g_ret_sts_unexp_error;
355 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
356
357 END UPDATE_GROUP;
358
359
360 PROCEDURE DELETE_GROUP(
361 P_API_VERSION IN NUMBER,
362 P_INIT_MSG_LIST IN VARCHAR2,
363 P_COMMIT IN VARCHAR2,
364 P_GROUP_ID IN NUMBER,
365 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
366 X_MSG_COUNT OUT NOCOPY NUMBER,
367 X_MSG_DATA OUT NOCOPY VARCHAR2 )
368 IS
369 CURSOR rr_old_cur(l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE)
370 IS
371 SELECT b.group_number ,
372 b.exclusive_flag ,
373 b.start_date_active ,
374 b.end_date_active ,
375 b.accounting_code ,
376 b.object_version_number,
377 b.email_address,
378 b.group_name,
379 b.group_desc,
380 b.time_zone
381 FROM jtf_rs_groups_vl B
382 WHERE b.group_id = l_group_id;
383
384
385 --declare variables
386 --old value
387 l_group_number JTF_RS_GROUPS_B.GROUP_NUMBER%TYPE;
388 l_exclusive_flag JTF_RS_GROUPS_B.EXCLUSIVE_FLAG%TYPE;
389 l_start_date_active JTF_RS_GROUPS_B.START_DATE_ACTIVE%TYPE;
390 l_end_date_active JTF_RS_GROUPS_B.END_DATE_ACTIVE%TYPE;
391 l_accounting_code JTF_RS_GROUPS_B.ACCOUNTING_CODE%TYPE;
392 l_object_version_number JTF_RS_GROUPS_B.OBJECT_VERSION_NUMBER%TYPE;
393 l_email_address JTF_RS_GROUPS_B.EMAIL_ADDRESS%TYPE;
394 l_group_name JTF_RS_GROUPS_VL.GROUP_NAME%TYPE := null ;
395 l_group_desc JTF_RS_GROUPS_VL.GROUP_DESC%TYPE := null ;
396 l_time_zone JTF_RS_GROUPS_B.TIME_ZONE%TYPE;
397
398 rr_old_rec rr_old_cur%rowtype;
399
400 l_group_audit_id jtf_rs_groups_aud_b.group_audit_id%type;
401 l_row_id varchar2(24) := null;
402
403 --other variables
404 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_GROUP';
405 l_api_version CONSTANT NUMBER := 1.0;
406 l_date Date := sysdate;
407 l_user_id Number := 1;
408 l_login_id Number := 1;
409
410
411 BEGIN
412
413 --Standard Start of API SAVEPOINT
414 SAVEPOINT GROUP_AUDIT;
415
416 x_return_status := fnd_api.g_ret_sts_success;
417
418 --Standard Call to check API compatibility
419 IF NOT FND_API.Compatible_API_CALL(l_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
420 THEN
421 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
422 END IF;
423
424 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
425 IF FND_API.To_boolean(P_INIT_MSG_LIST)
426 THEN
427 FND_MSG_PUB.Initialize;
428 END IF;
429
430 l_date := sysdate;
431 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
432 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
433
434
435 open rr_old_cur(p_group_id);
436 FETCH rr_old_cur into rr_old_rec;
437 close rr_old_cur;
438
439 l_group_number := rr_old_rec.group_number;
440 l_start_date_active := rr_old_rec.start_date_active;
441 l_end_date_active := rr_old_rec.end_date_active ;
442 l_accounting_code := rr_old_rec.accounting_code ;
443 l_group_name := rr_old_rec.group_name ;
444 l_group_desc := rr_old_rec.group_desc ;
445 l_exclusive_flag := rr_old_rec.group_desc ;
446 l_object_version_number := rr_old_rec.object_version_number;
447 l_email_address := rr_old_rec.email_address;
448 l_time_zone := rr_old_rec.time_zone ;
449
450
451 select jtf_rs_groups_audit_s.nextval
452 into l_group_audit_id
453 from dual;
454
455 /* CALL TABLE HANDLER */
456 JTF_RS_GROUPS_AUD_PKG.INSERT_ROW (
457 X_ROWID => l_row_id,
458 x_group_audit_id => l_group_audit_id,
459 x_group_id => p_group_id,
460 x_new_group_number => null,
461 x_old_group_number => l_group_number,
462 x_new_email_address => null,
463 x_old_email_address => l_email_address,
464 x_new_exclusive_flag => null,
465 x_old_exclusive_flag => l_exclusive_flag,
466 X_NEW_START_DATE_ACTIVE => null,
467 X_OLD_START_DATE_ACTIVE => l_start_date_active,
468 X_NEW_END_DATE_ACTIVE => null,
469 X_OLD_END_DATE_ACTIVE => l_end_date_active,
470 x_new_accounting_code => null,
471 x_old_accounting_code => l_accounting_code,
472 x_new_object_version_number => null,
473 x_old_object_version_number => l_object_version_number,
474 x_new_group_name => null,
475 x_old_group_name => l_group_name,
476 x_new_group_desc => null,
477 x_old_group_desc => l_group_desc,
478 x_creation_date => l_date,
479 x_created_by => l_user_id ,
480 x_last_update_date => l_date,
481 x_last_updated_by => l_user_id,
482 x_last_update_login => l_login_id,
483 x_new_time_zone => null,
484 x_old_time_zone => l_time_zone
485 );
486
487
488
489
490 IF fnd_api.to_boolean (p_commit)
491 THEN
492 COMMIT WORK;
493 END IF;
494
495 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
496
497
498 EXCEPTION
499 WHEN fnd_api.g_exc_unexpected_error
500 THEN
501 ROLLBACK TO group_audit;
502 fnd_message.set_name ('JTF', 'JTF_RS_GROUP_AUDIT_ERR');
503 FND_MSG_PUB.add;
504 x_return_status := fnd_api.g_ret_sts_unexp_error;
505 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
506 WHEN OTHERS
507 THEN
508 ROLLBACK TO group_audit;
509 fnd_message.set_name ('JTF', 'JTF_RS_GROUP_AUDIT_ERR');
510 FND_MSG_PUB.add;
511 x_return_status := fnd_api.g_ret_sts_unexp_error;
512 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
513 END DELETE_GROUP;
514 END; -- Package Body JTF_RS_GROUPS_AUD_PVT