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