[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_GROUP_RELATE_AUD_PVT
Source
1 Package Body JTF_RS_GROUP_RELATE_AUD_PVT AS
2 /* $Header: jtfrsafb.pls 120.0 2005/05/11 08:19:05 appldev ship $ */
3 -- API Name : JTF_RS_GROUP_RELATE_AUD_PVT
4 -- Type : Private
5 -- Purpose : Inserts IN the JTF_RS_group_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_GROUP_RELATE_AUD_PVT ';
15 /* FOR INSERT */
16
17 PROCEDURE INSERT_GROUP_RELATE(
18 P_API_VERSION IN NUMBER,
19 P_INIT_MSG_LIST IN VARCHAR2,
20 P_COMMIT IN VARCHAR2,
21 P_GROUP_RELATE_ID IN JTF_RS_GRP_RELATIONS.GROUP_RELATE_ID %TYPE,
22 P_GROUP_ID IN JTF_RS_GRP_RELATIONS.GROUP_ID %TYPE,
23 P_RELATED_GROUP_ID IN JTF_RS_GRP_RELATIONS.RELATED_GROUP_ID%TYPE,
24 P_RELATION_TYPE IN JTF_RS_GRP_RELATIONS.RELATION_TYPE%TYPE,
25 P_START_DATE_ACTIVE IN JTF_RS_GRP_RELATIONS.START_DATE_ACTIVE%TYPE,
26 P_END_DATE_ACTIVE IN JTF_RS_GRP_RELATIONS.END_DATE_ACTIVE%TYPE,
27 P_OBJECT_VERSION_NUMBER IN JTF_RS_GRP_RELATIONS.OBJECT_VERSION_NUMBER%TYPE,
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_relate_aud_id jtf_rs_grp_relate_aud.group_relate_audit_id%type;
34 l_row_id varchar2(24) := null;
35
36 --other variables
37 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_GROUP_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 GROUP_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_UNEXPECTED_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_grp_relate_aud_s.nextval
68 into l_group_relate_aud_id
69 from dual;
70
71 /* CALL TABLE HANDLER */
72 JTF_RS_GRP_RELATE_AUD_PKG.INSERT_ROW (
73 X_ROWID => l_row_id,
74 X_group_RELATE_AUDIT_ID => l_group_relate_aud_id,
75 X_group_RELATE_ID => p_group_relate_id,
76 X_NEW_GROUP_ID => p_group_id,
77 X_OLD_GROUP_ID => null,
78 X_NEW_RELATED_GROUP_ID => p_related_group_id,
79 X_OLD_RELATED_GROUP_ID => null,
80 x_NEW_RELATION_TYPE => p_relation_type,
81 x_OLD_RELATION_TYPE => 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 group_relate_audit;
106 fnd_message.set_name ('JTF', 'JTF_RS_GRP_RELATE_AUDIT_ERR');
107 FND_MSG_PUB.add;
108 x_return_status := fnd_api.g_ret_sts_unexp_error;
109 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
110 WHEN OTHERS
111 THEN
112 ROLLBACK TO group_relate_audit;
113 fnd_message.set_name ('JTF', 'JTF_RS_GRP_RELATE_AUDIT_ERR');
114 FND_MSG_PUB.add;
115 x_return_status := fnd_api.g_ret_sts_unexp_error;
116 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
117
118
119 END INSERT_GROUP_RELATE;
120
121
122
123 /*FOR UPDATE */
124 PROCEDURE UPDATE_group_RELATE(
125 P_API_VERSION IN NUMBER,
126 P_INIT_MSG_LIST IN VARCHAR2,
127 P_COMMIT IN VARCHAR2,
128 P_GROUP_RELATE_ID IN JTF_RS_GRP_RELATIONS.GROUP_RELATE_ID %TYPE,
129 P_GROUP_ID IN JTF_RS_GRP_RELATIONS.GROUP_ID %TYPE,
130 P_RELATED_GROUP_ID IN JTF_RS_GRP_RELATIONS.RELATED_GROUP_ID%TYPE,
131 P_RELATION_TYPE IN JTF_RS_GRP_RELATIONS.RELATION_TYPE%TYPE,
132 P_START_DATE_ACTIVE IN JTF_RS_GRP_RELATIONS.START_DATE_ACTIVE%TYPE,
133 P_END_DATE_ACTIVE IN JTF_RS_GRP_RELATIONS.END_DATE_ACTIVE%TYPE,
134 P_OBJECT_VERSION_NUMBER IN JTF_RS_GRP_RELATIONS.OBJECT_VERSION_NUMBER%TYPE,
135 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
136 X_MSG_COUNT OUT NOCOPY NUMBER,
137 X_MSG_DATA OUT NOCOPY VARCHAR2 )
138 IS
139 CURSOR rr_old_cur(l_group_relate_id JTF_RS_grp_RELATIONS.group_RELATE_ID%TYPE)
140 IS
141 SELECT group_id,
142 related_group_id,
143 relation_type,
144 start_date_active,
145 end_date_active,
146 object_version_number
147 FROM jtf_rs_grp_relations
148 WHERE group_relate_id = l_group_relate_id;
149
150
151 --declare variables
152 --old value
153 l_group_relate_id jtf_rs_grp_relations.group_relate_id %type := null;
154 l_group_id jtf_rs_grp_relations.group_id %type := null;
155 l_related_group_id jtf_rs_grp_relations.related_group_id%type := null;
156 l_relation_type jtf_rs_grp_relations.relation_type%type;
157 l_start_date_active jtf_rs_grp_relations.start_date_active%type := null;
158 l_end_date_active jtf_rs_grp_relations.end_date_active%type := null;
159 l_object_version_number jtf_rs_grp_relations.object_version_number%type := null;
160
161
162
163
164 --new values
165 l_group_relate_id_n jtf_rs_grp_relations.group_relate_id %type := null;
166 l_group_id_n jtf_rs_grp_relations.group_id %type := null;
167 l_related_group_id_n jtf_rs_grp_relations.related_group_id%type := null;
168 l_relation_type_n jtf_rs_grp_relations.relation_type%type;
169 l_start_date_active_n jtf_rs_grp_relations.start_date_active%type := null;
170 l_end_date_active_n jtf_rs_grp_relations.end_date_active%type := null;
171 l_object_version_number_n jtf_rs_grp_relations.object_version_number%type := null;
172
173
174
175
176 rr_old_rec rr_old_cur%rowtype;
177 l_group_relate_aud_id jtf_rs_grp_relate_aud.group_relate_audit_id%type;
178 l_row_id varchar2(24) := null;
179
180 --other variables
181 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_GROUP_RELATE';
182 l_api_version CONSTANT NUMBER :=1.0;
183 l_date Date := sysdate;
184 l_user_id Number := 1;
185 l_login_id Number := 1;
186
187
188 BEGIN
189
190 --Standard Start of API SAVEPOINT
191 SAVEPOINT group_RELATE_AUDIT;
192
193 x_return_status := fnd_api.g_ret_sts_success;
194
195 --Standard Call to check API compatibility
196 IF NOT FND_API.Compatible_API_CALL(l_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
197 THEN
198 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
199 END IF;
200
201 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
202 IF FND_API.To_boolean(P_INIT_MSG_LIST)
203 THEN
204 FND_MSG_PUB.Initialize;
205 END IF;
206
207 l_date := sysdate;
208 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
209 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
210
211
212 open rr_old_cur(p_group_relate_id);
213 FETCH rr_old_cur into rr_old_rec;
214 close rr_old_cur;
215
216 if p_group_id <> nvl(rr_old_rec.group_id,0)
217 then
218 l_group_id := rr_old_rec.group_Id;
219 l_group_id_n := p_group_id;
220 end if;
221 if p_related_group_id <> nvl(rr_old_rec.related_group_id, -1)
222 then
223 l_related_group_id := rr_old_rec.related_group_id;
224 l_related_group_id_n := p_related_group_id;
225 end if;
226 if p_relation_type <> rr_old_rec.relation_type
227 then
228 l_relation_type := rr_old_rec.relation_type ;
229 l_relation_type_n:= p_relation_type;
230 end if;
231 if p_start_date_active <> rr_old_rec.start_date_active
232 then
233 l_start_date_active := rr_old_rec.start_date_active;
234 l_start_date_active_n := p_start_date_active;
235 end if;
236 if (p_end_date_active <> rr_old_rec.end_date_active) OR
237 /* (p_end_date_active is null AND rr_old_rec.end_date_active <> FND_API.G_MISS_DATE) OR
238 (p_end_date_active is not null AND rr_old_rec.end_date_active = FND_API.G_MISS_DATE) */
239 /* Modified the above date validation to fix bug # 2760129 */
240 (p_end_date_active is null AND rr_old_rec.end_date_active is NOT NULL) OR
241 (p_end_date_active is not null AND rr_old_rec.end_date_active is NULL)
242
243 then
244 l_end_date_active := rr_old_rec.end_date_active ;
245 l_end_date_active_n := p_end_date_active ;
246 end if;
247 if p_object_version_number <> nvl(rr_old_rec.object_version_number,0)
248 then
249 l_object_version_number := rr_old_rec.object_version_number;
250 l_object_version_number_n := p_object_version_number;
251 end if;
252
253
254 select jtf_rs_grp_relate_aud_s.nextval
255 into l_group_relate_aud_id
256 from dual;
257
258 /* CALL TABLE HANDLER */
259 JTF_RS_GRP_RELATE_AUD_PKG.INSERT_ROW (
260 X_ROWID => l_row_id,
261 X_group_RELATE_AUDIT_ID => l_group_relate_aud_id,
262 X_group_RELATE_ID => p_group_relate_id,
263 X_NEW_group_ID => l_group_id_n,
264 X_OLD_group_ID => l_group_id,
265 X_NEW_RELATED_GROUP_ID => l_related_group_id_n,
266 X_OLD_RELATED_GROUP_ID => l_related_group_id,
267 x_NEW_RELATION_TYPE => l_relation_type_n,
268 x_OLD_RELATION_TYPE => l_relation_type,
269 X_NEW_START_DATE_ACTIVE => l_start_date_active_n,
270 X_OLD_START_DATE_ACTIVE => l_start_date_active,
271 X_NEW_END_DATE_ACTIVE => l_end_date_active_n,
272 X_OLD_END_DATE_ACTIVE => l_end_date_active,
273 X_NEW_OBJECT_VERSION_NUMBER => l_object_version_number_n,
274 X_OLD_OBJECT_VERSION_NUMBER => l_object_version_number,
275 X_CREATION_DATE => l_date,
276 X_CREATED_BY => l_user_id,
277 X_LAST_UPDATE_DATE => l_date,
278 X_LAST_UPDATED_BY => l_user_id,
279 X_LAST_UPDATE_LOGIN => l_login_id
280 );
281
282
283
284
285 IF fnd_api.to_boolean (p_commit)
286 THEN
287 COMMIT WORK;
288 END IF;
289
290 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
291
292
293 EXCEPTION
294 WHEN fnd_api.g_exc_unexpected_error
295 THEN
296 ROLLBACK TO group_relate_audit;
297 fnd_message.set_name ('JTF', 'JTF_RS_GRP_RELATE_AUDIT_ERR');
298 FND_MSG_PUB.add;
299 x_return_status := fnd_api.g_ret_sts_unexp_error;
300 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
301 WHEN OTHERS
302 THEN
303 ROLLBACK TO group_relate_audit;
304 fnd_message.set_name ('JTF', 'JTF_RS_GRP_RELATE_AUDIT_ERR');
305 FND_MSG_PUB.add;
306 x_return_status := fnd_api.g_ret_sts_unexp_error;
307 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
308
309
310 END UPDATE_group_RELATE;
311
312
313 --FOR DELETE
314
315 PROCEDURE DELETE_group_RELATE(
316 P_API_VERSION IN NUMBER,
317 P_INIT_MSG_LIST IN VARCHAR2,
318 P_COMMIT IN VARCHAR2,
319 P_GROUP_RELATE_ID IN NUMBER,
320 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
321 X_MSG_COUNT OUT NOCOPY NUMBER,
322 X_MSG_DATA OUT NOCOPY VARCHAR2 )
323 IS
324 CURSOR rr_old_cur(l_group_relate_id JTF_RS_GRP_RELATIONS.group_RELATE_ID%TYPE)
325 IS
326 SELECT group_id,
327 related_group_id,
328 relation_type,
329 start_date_active,
330 end_date_active,
331 object_version_number
332 FROM jtf_rs_grp_relations
333 WHERE group_relate_id = l_group_relate_id;
334
335
336 --declare variables
337 --old value
338 l_group_relate_id jtf_rs_grp_relations.group_relate_id %type := null;
339 l_group_id jtf_rs_grp_relations.group_id %type := null;
340 l_related_group_id jtf_rs_grp_relations.related_group_id%type := null;
341 l_relation_type jtf_rs_grp_relations.relation_type%type;
342 l_start_date_active jtf_rs_grp_relations.start_date_active%type := null;
343 l_end_date_active jtf_rs_grp_relations.end_date_active%type := null;
344 l_object_version_number jtf_rs_grp_relations.object_version_number%type := null;
345
346
347 rr_old_rec rr_old_cur%rowtype;
348 l_group_relate_aud_id jtf_rs_grp_relate_aud.group_relate_audit_id%type;
349 l_row_id varchar2(24) := null;
350
351 --other variables
352 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_GROUP_RELATE';
353 l_api_version CONSTANT NUMBER :=1.0;
354 l_date Date := sysdate;
355 l_user_id Number;
356 l_login_id Number;
357
358
359
360
361 BEGIN
362
363 --Standard Start of API SAVEPOINT
364 SAVEPOINT group_RELATE_AUDIT;
365
366 x_return_status := fnd_api.g_ret_sts_success;
367
368 --Standard Call to check API compatibility
369 IF NOT FND_API.Compatible_API_CALL(l_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
370 THEN
371 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
372 END IF;
373
374 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
375 IF FND_API.To_boolean(P_INIT_MSG_LIST)
376 THEN
377 FND_MSG_PUB.Initialize;
378 END IF;
379
380 l_date := sysdate;
381 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
382 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
383
384
385 open rr_old_cur(p_group_relate_id);
386 FETCH rr_old_cur into rr_old_rec;
387 close rr_old_cur;
388
389 l_group_id := rr_old_rec.group_id;
390 l_related_group_id := rr_old_rec.related_group_id;
391 l_relation_type := rr_old_rec.relation_type;
392 l_start_date_active := rr_old_rec.start_date_active;
393 l_end_date_active := rr_old_rec.end_date_active ;
394 l_object_version_number := rr_old_rec.object_version_number;
395
396
397
398
399 select jtf_rs_grp_relate_aud_s.nextval
400 into l_group_relate_aud_id
401 from dual;
402
403 /* CALL TABLE HANDLER */
404 JTF_RS_GRP_RELATE_AUD_PKG.INSERT_ROW (
405 X_ROWID => l_row_id,
406 X_group_RELATE_AUDIT_ID => l_group_relate_aud_id,
407 X_group_RELATE_ID => p_group_relate_id,
408 X_NEW_group_ID =>null,
409 X_OLD_group_ID => l_group_id,
410 X_NEW_RELATED_GROUP_ID => null,
411 X_OLD_RELATED_GROUP_ID => l_related_group_id,
412 x_NEW_RELATION_TYPE => null,
413 x_OLD_RELATION_TYPE => l_relation_type,
414 X_NEW_START_DATE_ACTIVE =>null,
415 X_OLD_START_DATE_ACTIVE => l_start_date_active,
416 X_NEW_END_DATE_ACTIVE => null,
417 X_OLD_END_DATE_ACTIVE => l_end_date_active,
418 X_NEW_OBJECT_VERSION_NUMBER => null,
419 X_OLD_OBJECT_VERSION_NUMBER => l_object_version_number,
420 X_CREATION_DATE => l_date,
421 X_CREATED_BY => l_user_id,
422 X_LAST_UPDATE_DATE => l_date,
423 X_LAST_UPDATED_BY => l_user_id,
424 X_LAST_UPDATE_LOGIN => l_login_id
425 );
426
427
428
429
430
431 IF fnd_api.to_boolean (p_commit)
432 THEN
433 COMMIT WORK;
434 END IF;
435
436 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
437
438 EXCEPTION
439 WHEN fnd_api.g_exc_unexpected_error
440 THEN
441 ROLLBACK TO group_relate_audit;
442 fnd_message.set_name ('JTF', 'JTF_RS_GRP_RELATE_AUDIT_ERR');
443 FND_MSG_PUB.add;
444 x_return_status := fnd_api.g_ret_sts_unexp_error;
445 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
446 WHEN OTHERS
447 THEN
448 ROLLBACK TO group_relate_audit;
449 fnd_message.set_name ('JTF', 'JTF_RS_GRP_RELATE_AUDIT_ERR');
450 FND_MSG_PUB.add;
451 x_return_status := fnd_api.g_ret_sts_unexp_error;
452 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
453
454
455 END DELETE_group_RELATE;
456
457 END; -- Package Body JTF_RS_group_RELATE_AUD_PVT