[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_GROUP_RELATE_PVT
Source
1 PACKAGE BODY jtf_rs_group_relate_pvt AS
2 /* $Header: jtfrsvfb.pls 120.0 2005/05/11 08:22:58 appldev ship $ */
3
4 /*****************************************************************************************
5 This is a private API that caller will invoke.
6 It provides procedures for managing resource group relations, like
7 create, update and delete resource group relations from other modules.
8 Its main procedures are as following:
9 Create Resource Group Relate
10 Update Resource Group Relate
11 Delete Resource Group Relate
12 All bUsiness logic validations are done through this API
13 ******************************************************************************************/
14
15 G_PKG_NAME VARCHAR2(30) := 'JTF_RS_GROUP_RELATE_PVT';
16 G_NAME VARCHAR2(200);
17
18 /* Procedure to create the resource group relation
19 based on input values passed by calling routines. */
20
21 PROCEDURE create_resource_group_relate
22 (P_API_VERSION IN NUMBER,
23 P_INIT_MSG_LIST IN VARCHAR2,
24 P_COMMIT IN VARCHAR2,
25 P_GROUP_ID IN JTF_RS_GROUP_MEMBERS.GROUP_ID%TYPE,
26 P_RELATED_GROUP_ID IN JTF_RS_GROUP_MEMBERS.GROUP_ID%TYPE,
27 P_RELATION_TYPE IN JTF_RS_GRP_RELATIONS.RELATION_TYPE%TYPE,
28 P_START_DATE_ACTIVE IN JTF_RS_GRP_RELATIONS.START_DATE_ACTIVE%TYPE,
29 P_END_DATE_ACTIVE IN JTF_RS_GRP_RELATIONS.END_DATE_ACTIVE%TYPE,
30 P_ATTRIBUTE1 IN JTF_RS_GRP_RELATIONS.ATTRIBUTE1%TYPE,
31 P_ATTRIBUTE2 IN JTF_RS_GRP_RELATIONS.ATTRIBUTE2%TYPE,
32 P_ATTRIBUTE3 IN JTF_RS_GRP_RELATIONS.ATTRIBUTE3%TYPE,
33 P_ATTRIBUTE4 IN JTF_RS_GRP_RELATIONS.ATTRIBUTE4%TYPE,
34 P_ATTRIBUTE5 IN JTF_RS_GRP_RELATIONS.ATTRIBUTE5%TYPE,
35 P_ATTRIBUTE6 IN JTF_RS_GRP_RELATIONS.ATTRIBUTE6%TYPE,
36 P_ATTRIBUTE7 IN JTF_RS_GRP_RELATIONS.ATTRIBUTE7%TYPE,
37 P_ATTRIBUTE8 IN JTF_RS_GRP_RELATIONS.ATTRIBUTE8%TYPE,
38 P_ATTRIBUTE9 IN JTF_RS_GRP_RELATIONS.ATTRIBUTE9%TYPE,
39 P_ATTRIBUTE10 IN JTF_RS_GRP_RELATIONS.ATTRIBUTE10%TYPE,
40 P_ATTRIBUTE11 IN JTF_RS_GRP_RELATIONS.ATTRIBUTE11%TYPE,
41 P_ATTRIBUTE12 IN JTF_RS_GRP_RELATIONS.ATTRIBUTE12%TYPE,
42 P_ATTRIBUTE13 IN JTF_RS_GRP_RELATIONS.ATTRIBUTE13%TYPE,
43 P_ATTRIBUTE14 IN JTF_RS_GRP_RELATIONS.ATTRIBUTE14%TYPE,
44 P_ATTRIBUTE15 IN JTF_RS_GRP_RELATIONS.ATTRIBUTE15%TYPE,
45 P_ATTRIBUTE_CATEGORY IN JTF_RS_GRP_RELATIONS.ATTRIBUTE_CATEGORY%TYPE,
46 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
47 X_MSG_COUNT OUT NOCOPY NUMBER,
48 X_MSG_DATA OUT NOCOPY VARCHAR2,
49 X_GROUP_RELATE_ID OUT NOCOPY JTF_RS_GRP_RELATIONS.GROUP_RELATE_ID%TYPE
50 )
51 IS
52 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RESOURCE_GROUP_RELATE';
53 l_api_version CONSTANT NUMBER :=1.0;
54 l_bind_data_id number;
55
56 l_return_status VARCHAR2(200);
57 l_msg_count NUMBER;
58 l_msg_data VARCHAR2(200);
59 l_rowid VARCHAR2(200);
60
61 l_return_code VARCHAR2(100);
62 l_count NUMBER;
63 l_data VARCHAR2(200);
64 l_date Date;
65 l_user_id Number;
66 l_login_id Number;
67
68 l_GROUP_ID JTF_RS_GRP_RELATIONS.GROUP_ID%TYPE := p_group_id;
69 l_RELATED_GROUP_ID JTF_RS_GRP_RELATIONS.GROUP_ID%TYPE := p_related_group_id;
70 l_RELATION_TYPE JTF_RS_GRP_RELATIONS.RELATION_TYPE%TYPE := p_relation_type;
71 l_start_date_active JTF_RS_GRP_RELATIONS.START_DATE_ACTIVE%TYPE := p_start_date_active;
72 l_end_date_active JTF_RS_GRP_RELATIONS.END_DATE_ACTIVE%TYPE := p_end_date_active;
73 l_temp_end_date_active JTF_RS_GRP_RELATIONS.END_DATE_ACTIVE%TYPE ;
74 l_group_relate_id JTF_RS_GRP_RELATIONS.GROUP_RELATE_ID%TYPE;
75
76 l_g_miss_date date := to_date(to_char(fnd_api.g_miss_date,'DD-MM-RRRR'),'DD-MM-RRRR') ;
77
78 --CHECK THIS CURSOR FOR VALIDITY OF DATES
79
80 CURSOR check_overlap_cur(l_group_id JTF_RS_GRP_RELATIONS.GROUP_ID%TYPE,
81 l_start_date_active JTF_RS_GRP_RELATIONS.START_DATE_ACTIVE%TYPE,
82 l_end_date_active JTF_RS_GRP_RELATIONS.START_DATE_ACTIVE%TYPE)
83 IS
84 SELECT 'X'
85 FROM jtf_rs_grp_relations rel
86 WHERE rel.group_id = l_group_id
87 AND NVL(rel.delete_flag,'N') <> 'Y'
88 AND rel.relation_type = 'PARENT_GROUP'
89 AND ((l_start_date_active between rel.start_date_active and
90 nvl(rel.end_date_active,l_start_date_active+1))
91 OR (l_end_date_active between rel.start_date_active
92 and nvl(rel.end_date_active,l_end_date_active))
93 OR ((l_start_date_active <= rel.start_date_active)
94 AND (l_end_date_active >= rel.end_date_active
95 OR l_end_date_active IS NULL )));
96 --OR rel.end_date_active IS NULL))) ;
97
98 /*((l_end_date_active >= rel.start_date_active
99 AND l_start_date_active <= rel.end_date_active
100 AND rel.end_date_active is not null)
101 OR ( l_end_date_active >= rel.start_date_active
102 AND rel.end_date_active IS NULL));*/
103
104 check_overlap_rec check_overlap_cur%ROWTYPE;
105
106 CURSOR check_group_dt_cur(l_group_id JTF_RS_GRP_RELATIONS.GROUP_ID%TYPE ,
107 l_start_date_active JTF_RS_GRP_RELATIONS.START_DATE_ACTIVE%TYPE,
108 l_end_date_active JTF_RS_GRP_RELATIONS.START_DATE_ACTIVE%TYPE)
109 IS
110 SELECT 'X'
111 FROM jtf_rs_groups_b grp
112 WHERE grp.group_id = l_group_id
113 -- changed by nsinghai 20 May 2002 to handle null value of l_end_date_active
114 -- AND l_start_date_active >= grp.start_date_active
115 -- AND ((grp.end_date_active IS NULL)
116 -- OR (grp.end_date_active >= nvl(l_end_date_active,grp.end_date_active)));
117 AND trunc(l_start_date_active) between trunc(grp.start_date_active)
118 and nvl(trunc(grp.end_date_active),l_g_miss_date)
119 AND nvl(trunc(l_end_date_active),l_g_miss_date)
120 between trunc(l_start_date_active)
121 and nvl(trunc(grp.end_date_active),l_g_miss_date);
122
123 check_group_dt_rec check_group_dt_cur%rowtype;
124
125
126 CURSOR check_dates_cur(l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE ,
127 l_related_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE )
128 IS
129 SELECT 'X'
130 FROM jtf_rs_groups_b g1
131 ,jtf_rs_groups_b g2
132 WHERE g1.group_id = l_group_id
133 AND g2.group_id = l_related_group_id
134 AND ((g1.start_date_active <= g2.end_date_active and g2.end_date_active <> NULL)
135 or ((g2.end_date_active IS NULL) AND (g1.end_date_active <> NULL) AND
136 (g1.end_date_active >= g2.start_date_active))
137 OR((g2.end_date_active IS NULL) AND (g1.end_date_active IS NULL)));
138
139 check_dates_rec check_dates_cur%rowtype;
140
141 --cursor for cyclic dependency check
142 cursor dep_cur(L_GROUP_ID number,
143 l_related_group_id number,
144 l_start_date_active date,
145 l_end_date_active date)
146 is
147 select 'x'
148 from jtf_rs_groups_denorm
149 where parent_group_id = l_group_id
150 and group_id = l_related_group_id
151 and ( ( (l_start_date_active >= start_date_active)
152 AND ((l_start_date_active <= end_date_active)
153 OR (end_date_active IS NULL))
154 )
155 OR (
156 (l_end_date_active between start_date_active and nvl(end_date_active,l_g_miss_date))
157 OR ((nvl(l_end_date_active,start_date_active) >= start_date_active)
158 AND (end_date_active IS NULL))
159 -- OR (nvl(l_end_date_active,sysdate) <= end_date_active)
160 )
161 OR (
162 (l_start_date_active <= start_date_active)
163 AND
164 (nvl(l_end_date_active,l_g_miss_date) >= nvl(end_date_active,l_g_miss_date))
165 )
166 );
167
168
169 dep_rec dep_cur%rowtype;
170
171
172 CURSOR seq_cur
173 IS
174 SELECT jtf_rs_grp_relations_s.nextval
175 FROM dual;
176
177
178 CURSOR parent_count_cur(l_group_id number)
179 IS
180 SELECT count(*) par_count
181 from jtf_rs_grp_relations rel
182 where rel.relation_type = 'PARENT_GROUP'
183 connect by rel.group_id = prior related_group_id
184 and nvl(delete_flag, 'N') <> 'Y'
185 and rel.related_group_id <> l_group_id
186 AND ((trunc(rel.start_date_active) <= prior rel.start_date_active
187 AND nvl(rel.end_date_active, prior rel.start_date_active) >=
188 trunc(prior rel.start_date_active)) OR
189 (rel.start_date_active > trunc(prior rel.start_date_active)
190 AND trunc(rel.start_date_active) <= nvl(prior rel.end_date_active,
191 rel.start_date_active)))
192 start with rel.group_id = l_group_id
193 and nvl(rel.delete_flag,'N') <> 'Y';
194
195
196 CURSOR child_count_cur(l_group_id number)
197 IS
198 SELECT count(*) par_count
199 from jtf_rs_grp_relations rel
200 where rel.relation_type = 'PARENT_GROUP'
201 connect by rel.related_group_id = prior group_id
202 and nvl(delete_flag, 'N') <> 'Y'
203 and rel.group_id <> l_group_id
204 AND ((trunc(rel.start_date_active) <= prior rel.start_date_active
205 AND nvl(rel.end_date_active, prior rel.start_date_active) >=
206 trunc(prior rel.start_date_active)) OR
207 (rel.start_date_active > trunc(prior rel.start_date_active)
208 AND trunc(rel.start_date_active) <= nvl(prior rel.end_date_active,
209 rel.start_date_active)))
210 start with rel.related_group_id = l_group_id
211 and nvl(rel.delete_flag,'N') <> 'Y';
212
213 l_parent number;
214 l_child number;
215 l_request number;
216
217 cursor conc_prog_cur
218 is
219 select description
220 from fnd_concurrent_programs_vl
221 where concurrent_program_name = 'JTFRSDEN'
222 and application_id = 690;
223
224
225 BEGIN
226 --Standard Start of API SAVEPOINT
227 SAVEPOINT GROUP_RELATE_SP;
228
229 x_return_status := fnd_api.g_ret_sts_success;
230 l_return_status := fnd_api.g_ret_sts_success;
231
232 --Standard Call to check API compatibility
233 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
234 THEN
235 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
236 END IF;
237
238 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
239 IF FND_API.To_boolean(P_INIT_MSG_LIST)
240 THEN
241 FND_MSG_PUB.Initialize;
242 END IF;
243
244 --GET USER ID AND SYSDATE
245 l_date := sysdate;
246 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
247 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
248
249 -- user hook calls for customer
250 -- Customer pre- processing section - mandatory
251 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'CREATE_RESOURCE_GROUP_RELATE', 'B', 'C' ))
252 then
253 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'CREATE_RESOURCE_GROUP_RELATE', 'B', 'C' ))
254 then
255
256
257 JTF_RS_GROUP_RELATE_CUHK.CREATE_RES_GROUP_RELATE_PRE(P_GROUP_ID => p_group_id,
258 P_RELATED_GROUP_ID => p_related_group_id,
259 P_RELATION_TYPE => p_relation_type,
260 P_START_DATE_ACTIVE => p_start_date_active,
261 P_END_DATE_ACTIVE => p_end_date_active,
262 p_data => L_data,
263 p_count => L_count,
264 P_return_code => l_return_code);
265 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
266 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
267
268 x_return_status := fnd_api.g_ret_sts_error;
269 fnd_message.set_name ('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
270 FND_MSG_PUB.add;
271 RAISE fnd_api.g_exc_error;
272 end if;
273 end if;
274 end if;
275
276 /* Vertical industry pre- processing section - mandatory */
277
278 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'CREATE_RESOURCE_GROUP_RELATE', 'B', 'V' ))
279 then
280 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'CREATE_RESOURCE_GROUP_RELATE', 'B', 'V' ))
281 then
282
283 JTF_RS_GROUP_RELATE_VUHK.CREATE_RES_GROUP_RELATE_PRE(P_GROUP_ID => p_group_id,
284 P_RELATED_GROUP_ID => p_related_group_id,
285 P_RELATION_TYPE => p_relation_type,
286 P_START_DATE_ACTIVE => p_start_date_active,
287 P_END_DATE_ACTIVE => p_end_date_active,
288 p_data => L_data,
289 p_count => L_count,
290 P_return_code => l_return_code);
291 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
292 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
293 x_return_status := fnd_api.g_ret_sts_error;
294 fnd_message.set_name ('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
295 FND_MSG_PUB.add;
296 RAISE fnd_api.g_exc_error;
297 end if;
298 end if;
299 end if;
300
301 /* Internal pre- processing section - mandatory */
302
303 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'CREATE_RESOURCE_GROUP_RELATE', 'B', 'I' ))
304 then
305 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'CREATE_RESOURCE_GROUP_RELATE', 'B', 'I' ))
306 then
307
308 JTF_RS_GROUP_RELATE_IUHK.CREATE_RES_GROUP_RELATE_PRE(P_GROUP_ID => p_group_id,
309 P_RELATED_GROUP_ID => p_related_group_id,
310 P_RELATION_TYPE => p_relation_type,
311 P_START_DATE_ACTIVE => p_start_date_active,
312 P_END_DATE_ACTIVE => p_end_date_active,
313 p_data => L_data,
314 p_count => L_count,
315 P_return_code => l_return_code);
316 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
317 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
318 x_return_status := fnd_api.g_ret_sts_error;
319 fnd_message.set_name ('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
320 FND_MSG_PUB.add;
321 RAISE fnd_api.g_exc_error;
322 end if;
323 end if;
324 end if;
325
326 -- end of user hook call
327
328 l_start_date_active := trunc(l_start_date_active);
329 l_end_date_active := trunc(l_end_date_active);
330 --call default date validation utl
331 JTF_RESOURCE_UTL.VALIDATE_INPUT_DATES(l_start_date_active,
332 l_end_date_active,
333 l_return_status);
334
335 IF(l_return_status <> fnd_api.g_ret_sts_success)
336 THEN
337 x_return_status := fnd_api.g_ret_sts_error;
338 RAISE fnd_api.g_exc_error;
339 END IF;
340
341
342
343 --check whether the same set of child and parent group have overlapping records for the same
344 --time period if relation_type = PARENT_GROUP
345 IF (l_relation_type='PARENT_GROUP')
346 THEN
347 IF (l_end_date_active is null) THEN
348 l_temp_end_date_active := to_date(to_char(fnd_api.g_miss_date,'dd-MM-RRRR'),'dd-MM-RRRR');
349 --l_temp_end_date_active := to_date('31-DEC-4712','dd-MM-RRRR');
350 ELSE l_temp_end_date_active := l_end_date_active;
351 END IF;
352 OPEN check_overlap_cur(l_group_id,
353 l_start_date_active,
354 l_temp_end_date_active);
355
356 FETCH check_overlap_cur INTO check_overlap_rec;
357
358 IF(check_overlap_cur%FOUND)
359 THEN
360 x_return_status := fnd_api.g_ret_sts_error;
361 fnd_message.set_name ('JTF', 'JTF_RS_GRP_REL_OVERLAP');
362 FND_MSG_PUB.add;
363 RAISE fnd_api.g_exc_error;
364 END IF;
365 CLOSE check_overlap_cur;
366
367 END IF;
368
369 --dates within child group dates
370 OPEN check_group_dt_cur(l_group_id,
371 l_start_date_active,
372 l_end_date_active);
373 FETCH check_group_dt_cur INTO check_group_dt_rec;
374 IF(check_group_dt_cur%NOTFOUND)
375 THEN
376 x_return_status := fnd_api.g_ret_sts_error;
377 fnd_message.set_name ('JTF', 'JTF_RS_CHILD_GRP_DT_ERR');
378 FND_MSG_PUB.add;
379 RAISE fnd_api.g_exc_error;
380
381 END IF;
382 CLOSE check_group_dt_cur;
383
384 --dates within related group dates
385 OPEN check_group_dt_cur(l_related_group_id,
386 l_start_date_active,
387 l_end_date_active);
388 FETCH check_group_dt_cur INTO check_group_dt_rec;
389 IF(check_group_dt_cur%NOTFOUND)
390 THEN
391 x_return_status := fnd_api.g_ret_sts_error;
392 fnd_message.set_name ('JTF', 'JTF_RS_RELATED_GRP_DT_ERR');
393 FND_MSG_PUB.add;
394 RAISE fnd_api.g_exc_error;
395
396 END IF;
397 CLOSE check_group_dt_cur;
398
399 --check that child group dates are within the related group dates
400 /* OPEN check_dates_cur(l_group_id,
401 l_related_group_id);
402 FETCH check_dates_cur INTO check_dates_rec;
403
404 IF(check_dates_cur%NOTFOUND)
405 THEN
406 x_return_status := fnd_api.g_ret_sts_error;
407 fnd_message.set_name ('JTF', 'JTF_RS_CHILD_REL_DT_ERR');
408 FND_MSG_PUB.add;
409 RAISE fnd_api.g_exc_error;
410
411 END IF;
412 CLOSE check_dates_cur;*/
413
414 --check for cyclic dependency
415 open dep_cur(p_group_id,
416 p_related_group_id,
417 p_start_date_active ,
418 p_end_date_active );
419 fetch dep_cur into dep_rec;
420 if(dep_cur%found)
421 then
422 x_return_status := fnd_api.g_ret_sts_error;
423 fnd_message.set_name ('JTF', 'JTF_RS_CYCLIC_DEP_ERR');
424 FND_MSG_PUB.add;
425 RAISE fnd_api.g_exc_error;
426 end if;
427 close dep_cur;
428
429
430
431 --call to generate the next id
432 OPEN seq_cur;
433 FETCH seq_cur INTO l_group_relate_id;
434 CLOSE seq_cur;
435
436 --call audit api
437
438 JTF_RS_GROUP_RELATE_AUD_PVT.insert_group_relate(
439 P_API_VERSION => 1.0,
440 P_INIT_MSG_LIST => p_init_msg_list,
441 P_COMMIT => null,
442 P_GROUP_RELATE_ID => l_group_relate_id,
443 P_GROUP_ID => l_group_id,
444 P_RELATED_GROUP_ID => l_related_group_id,
445 P_RELATION_TYPE => l_relation_type,
446 P_START_DATE_ACTIVE => l_start_date_active,
447 P_END_DATE_ACTIVE => l_end_date_active,
448 P_OBJECT_VERSION_NUMBER => 1.0,
449 X_RETURN_STATUS => l_return_status,
450 X_MSG_COUNT => l_msg_count,
451 X_MSG_DATA => l_msg_data);
452
453 IF(l_return_status <> fnd_api.g_ret_sts_success)
454 THEN
455 x_return_status := fnd_api.g_ret_sts_error;
456 fnd_message.set_name ('JTF', 'JTF_RS_GRP_RELATE_AUDIT_ERR');
457 FND_MSG_PUB.add;
458 RAISE fnd_api.g_exc_error;
459
460 END IF;
461
462
463 --call table handler to insert record
464 jtf_rs_grp_relations_pkg.insert_row(
465 X_ROWID => l_rowid,
466 X_GROUP_RELATE_ID => l_group_relate_id,
467 X_GROUP_ID => l_group_id,
468 X_RELATED_GROUP_ID => l_related_group_id,
469 X_RELATION_TYPE => l_relation_type,
470 X_START_DATE_ACTIVE => l_start_date_active,
471 X_END_DATE_ACTIVE => l_end_date_active,
472 X_DELETE_FLAG => 'N',
473 X_ATTRIBUTE2 => p_attribute2,
474 X_ATTRIBUTE3 => p_attribute3,
475 X_ATTRIBUTE4 => p_attribute4,
476 X_ATTRIBUTE5 => p_attribute5,
477 X_ATTRIBUTE6 => p_attribute6,
478 X_ATTRIBUTE7 => p_attribute7,
479 X_ATTRIBUTE8 => p_attribute8,
480 X_ATTRIBUTE9 => p_attribute9,
481 X_ATTRIBUTE10 => p_attribute10,
482 X_ATTRIBUTE11 => p_attribute11,
483 X_ATTRIBUTE12 => p_attribute12,
484 X_ATTRIBUTE13 => p_attribute13,
485 X_ATTRIBUTE14 => p_attribute14,
486 X_ATTRIBUTE15 => p_attribute15,
487 X_ATTRIBUTE_CATEGORY => p_attribute_category,
488 X_ATTRIBUTE1 => p_attribute1,
489 X_CREATION_DATE => l_date,
490 X_CREATED_BY => l_user_id,
491 X_LAST_UPDATE_DATE => l_date,
492 X_LAST_UPDATED_BY => l_user_id,
493 X_LAST_UPDATE_LOGIN => l_login_id); --call to insert records in jtf_rs_groups_denorm
494 IF(l_relation_type = 'PARENT_GROUP')
495 THEN
496
497 l_parent := 0;
498 l_child := 0;
499 BEGIN
500 OPEN parent_count_cur(l_group_id);
501 FETCH parent_count_cur INTO l_parent;
502 CLOSE parent_count_cur;
503
504 OPEN child_count_cur(l_group_id);
505 FETCH child_count_cur INTO l_child;
506 CLOSE child_count_cur;
507 EXCEPTION
508 WHEN OTHERS THEN
509 -- use concurrent program
510 l_parent := 10;
511 l_child := 10;
512 END;
513
514 IF (l_parent < 1) then l_parent := 1; end if;
515 IF (l_child < 1) then l_child := 1; end if;
516
517 IF(l_parent * l_child > 50)
518 THEN
519 begin
520 insert into jtf_rs_chgd_grp_relations
521 (GROUP_RELATE_ID,
522 GROUP_ID ,
523 RELATED_GROUP_ID,
524 RELATION_TYPE ,
525 START_DATE_ACTIVE,
526 END_DATE_ACTIVE,
527 OPERATION_FLAG,
528 CREATED_BY ,
529 CREATION_DATE ,
530 LAST_UPDATED_BY ,
531 LAST_UPDATE_DATE,
532 LAST_UPDATE_LOGIN)
533 values(l_group_relate_id,
534 p_group_id,
535 p_related_group_id,
536 p_relation_type,
537 p_start_date_active,
538 p_end_date_active,
539 'I',
540 l_user_id,
541 l_date,
542 l_user_id,
543 l_date,
544 l_login_id);
545
546
547 --call concurrent program
548
549 begin
550 l_request := fnd_request.submit_request(APPLICATION => 'JTF',
551 PROGRAM => 'JTFRSDEN');
552 open conc_prog_cur;
553 fetch conc_prog_cur into g_name;
554 close conc_prog_cur;
555
556 fnd_message.set_name ('JTF', 'JTF_RS_CONC_START');
557 fnd_message.set_token('P_NAME',g_name);
558 fnd_message.set_token('P_ID',l_request);
559 FND_MSG_PUB.add;
560 exception when others then
561 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
562 fnd_message.set_token('P_SQLCODE',SQLCODE);
563 fnd_message.set_token('P_SQLERRM',SQLERRM);
564 fnd_message.set_token('P_API_NAME', l_api_name);
565 FND_MSG_PUB.add;
566 x_return_status := fnd_api.g_ret_sts_unexp_error;
567 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
568 RAISE fnd_api.g_exc_unexpected_error;
569 end;
570 exception when others then
571
572 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
573 fnd_message.set_token('P_SQLCODE',SQLCODE);
574 fnd_message.set_token('P_SQLERRM',SQLERRM);
575 fnd_message.set_token('P_API_NAME', l_api_name);
576 FND_MSG_PUB.add;
577 x_return_status := fnd_api.g_ret_sts_unexp_error;
578 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
579 RAISE fnd_api.g_exc_unexpected_error;
580
581 end;
582
583 ELSE
584
585 JTF_RS_GROUP_DENORM_PVT.INSERT_GROUPS
586 ( P_API_VERSION => 1.0,
587 P_INIT_MSG_LIST => p_init_msg_list,
588 P_COMMIT => null,
589 P_GROUP_ID => l_group_id,
590 X_RETURN_STATUS => l_return_status,
591 X_MSG_COUNT => l_msg_count,
592 X_MSG_DATA => l_msg_data);
593
594 IF(l_return_status <> fnd_api.g_ret_sts_success)
595 THEN
596 x_return_status := fnd_api.g_ret_sts_error;
597 fnd_message.set_name ('JTF', 'JTF_RS_GRP_DENORM_ERR');
598 FND_MSG_PUB.add;
599 RAISE fnd_api.g_exc_error;
600
601 END IF;
602
603
604 --call to insert records in jtf_rs_rep_managers
605 -- this call has moved to groups denorm
606 /* JTF_RS_REP_MGR_DENORM_PVT.INSERT_GRP_RELATIONS
607 ( P_API_VERSION => 1.0,
608 P_INIT_MSG_LIST => p_init_msg_list,
609 P_COMMIT => null,
610 P_GROUP_RELATE_ID => l_group_relate_id,
611 X_RETURN_STATUS => l_return_status,
612 X_MSG_COUNT => l_msg_count,
613 X_MSG_DATA => l_msg_data);
614
615 IF(l_return_status <> fnd_api.g_ret_sts_success)
616 THEN
617
618 x_return_status := fnd_api.g_ret_sts_error;
619 fnd_message.set_name ('JTF', 'JTF_RS_REP_MGR_ERR');
620 FND_MSG_PUB.add;
621 RAISE fnd_api.g_exc_error;
622
623 END IF; */
624 END IF; -- end of count check
625 END IF;
626
627
628 -- user hook calls for customer
629 -- Customer post- processing section - mandatory
630 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'CREATE_RESOURCE_GROUP_RELATE', 'A', 'C' ))
631 then
632 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'CREATE_RESOURCE_GROUP_RELATE', 'A', 'C' ))
633 then
634 JTF_RS_GROUP_RELATE_CUHK.CREATE_RES_GROUP_RELATE_POST(P_GROUP_RELATE_ID => l_group_relate_id,
635 P_GROUP_ID => p_group_id,
636 P_RELATED_GROUP_ID => p_related_group_id,
637 P_RELATION_TYPE => p_relation_type,
638 P_START_DATE_ACTIVE => p_start_date_active,
639 P_END_DATE_ACTIVE => p_end_date_active,
640 p_data => L_data,
641 p_count => L_count,
642 P_return_code => l_return_code);
643 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
644 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
645 x_return_status := fnd_api.g_ret_sts_error;
646 fnd_message.set_name ('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
647 FND_MSG_PUB.add;
648 RAISE fnd_api.g_exc_error;
649 end if;
650 end if;
651 end if;
652
653 /* Vertical industry post- processing section - mandatory */
654
655 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'CREATE_RESOURCE_GROUP_RELATE', 'A', 'V' ))
656 then
657 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'CREATE_RESOURCE_GROUP_RELATE', 'A', 'V' ))
658 then
659
660 JTF_RS_GROUP_RELATE_VUHK.CREATE_RES_GROUP_RELATE_POST(P_GROUP_RELATE_ID => l_group_relate_id,
661 P_GROUP_ID => p_group_id,
662 P_RELATED_GROUP_ID => p_related_group_id,
663 P_RELATION_TYPE => p_relation_type,
664 P_START_DATE_ACTIVE => p_start_date_active,
665 P_END_DATE_ACTIVE => p_end_date_active,
666 p_data => l_msg_data,
667 p_count => L_count,
668 P_return_code => l_return_code);
669 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
670 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
671 x_return_status := fnd_api.g_ret_sts_error;
672 fnd_message.set_name ('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
673 FND_MSG_PUB.add;
674 RAISE fnd_api.g_exc_error;
675 end if;
676 end if;
677 end if;
678
679 /* Internal post- processing section - mandatory */
680
681 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'CREATE_RESOURCE_GROUP_RELATE', 'A', 'I' ))
682 then
683 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'CREATE_RESOURCE_GROUP_RELATE', 'A', 'I' ))
684 then
685
686 JTF_RS_GROUP_RELATE_IUHK.CREATE_RES_GROUP_RELATE_POST(P_GROUP_RELATE_ID => l_group_relate_id,
687 P_GROUP_ID => p_group_id,
688 P_RELATED_GROUP_ID => p_related_group_id,
689 P_RELATION_TYPE => p_relation_type,
690 P_START_DATE_ACTIVE => p_start_date_active,
691 P_END_DATE_ACTIVE => p_end_date_active,
692 p_data => l_msg_data,
693 p_count => L_count,
694 P_return_code => l_return_code);
695 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
696 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
697 x_return_status := fnd_api.g_ret_sts_error;
698 fnd_message.set_name ('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
699 FND_MSG_PUB.add;
700 RAISE fnd_api.g_exc_error;
701 end if;
702 end if;
703 end if;
704
705 -- end of user hook call
706
707
708
709
710 x_group_relate_id := l_group_relate_id;
711
712 IF jtf_resource_utl.ok_to_execute(
713 'JTF_RS_GROUP_RELATE_PVT',
714 'CREATE_RESOURCE_GROUP_RELATE',
715 'M',
716 'M')
717 THEN
718 IF jtf_usr_hks.ok_to_execute(
719 'JTF_RS_GROUP_RELATE_PVT',
720 'CREATE_RESOURCE_GROUP_RELATE',
721 'M',
722 'M')
723 THEN
724
725 IF (jtf_rs_group_relate_cuhk.ok_to_generate_msg(
726 p_group_relate_id => l_group_relate_id,
727 x_return_status => x_return_status) )
728 THEN
729
730 /* Get the bind data id for the Business Object Instance */
731
732 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
733
734
735 /* Set bind values for the bind variables in the Business Object
736 SQL */
737
738 jtf_usr_hks.load_bind_data(l_bind_data_id, 'group_relate_id',
739 l_group_relate_id, 'S', 'N');
740
741
742 /* Call the message generation API */
743
744 jtf_usr_hks.generate_message(
745 p_prod_code => 'JTF',
746 p_bus_obj_code => 'RS_GRL',
747 p_action_code => 'I', /* I/U/D */
748 p_bind_data_id => l_bind_data_id,
749 x_return_code => x_return_status);
750
751
752 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
753 x_return_status := fnd_api.g_ret_sts_error;
754 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
755 fnd_msg_pub.add;
756 RAISE fnd_api.g_exc_error;
757 END IF;
758 END IF;
759 END IF;
760 END IF;
761
762
763
764 --standard commit
765 IF fnd_api.to_boolean (p_commit)
766 THEN
767 COMMIT WORK;
768 END IF;
769
770
771 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
772
773 EXCEPTION
774 WHEN fnd_api.g_exc_unexpected_error
775 THEN
776 ROLLBACK TO group_relate_sp;
777 --fnd_message.set_name ('JTF', 'JTF_RS_GROUP_DENORM_ERR');
778 --FND_MSG_PUB.add;
779 --x_return_status := fnd_api.g_ret_sts_unexp_error;
780 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
781 WHEN fnd_api.g_exc_error
782 THEN
783 ROLLBACK TO group_relate_sp;
784 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
785
786 WHEN OTHERS
787 THEN
788 ROLLBACK TO group_relate_sp;
789 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
790 fnd_message.set_token('P_SQLCODE',SQLCODE);
791 fnd_message.set_token('P_SQLERRM',SQLERRM);
792 fnd_message.set_token('P_API_NAME',l_api_name);
793 FND_MSG_PUB.add;
794 x_return_status := fnd_api.g_ret_sts_unexp_error;
795 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
796
797
798 END create_resource_group_relate;
799
800
801 /* Procedure to update the resource group relation
802 based on input values passed by calling routines. */
803
804 PROCEDURE update_resource_group_relate
805 (P_API_VERSION IN NUMBER,
806 P_INIT_MSG_LIST IN VARCHAR2,
807 P_COMMIT IN VARCHAR2,
808 P_GROUP_RELATE_ID IN JTF_RS_GRP_RELATIONS.GROUP_RELATE_ID%TYPE,
809 P_START_DATE_ACTIVE IN JTF_RS_GRP_RELATIONS.START_DATE_ACTIVE%TYPE,
810 P_END_DATE_ACTIVE IN JTF_RS_GRP_RELATIONS.END_DATE_ACTIVE%TYPE,
811 P_OBJECT_VERSION_NUM IN OUT NOCOPY JTF_RS_GROUPS_VL.OBJECT_VERSION_NUMBER%TYPE,
812 P_ATTRIBUTE1 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE1%TYPE,
813 P_ATTRIBUTE2 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE2%TYPE,
814 P_ATTRIBUTE3 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE3%TYPE,
815 P_ATTRIBUTE4 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE4%TYPE,
816 P_ATTRIBUTE5 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE5%TYPE,
817 P_ATTRIBUTE6 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE6%TYPE,
818 P_ATTRIBUTE7 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE7%TYPE,
819 P_ATTRIBUTE8 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE8%TYPE,
820 P_ATTRIBUTE9 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE9%TYPE,
821 P_ATTRIBUTE10 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE10%TYPE,
822 P_ATTRIBUTE11 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE11%TYPE,
823 P_ATTRIBUTE12 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE12%TYPE,
824 P_ATTRIBUTE13 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE13%TYPE,
825 P_ATTRIBUTE14 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE14%TYPE,
826 P_ATTRIBUTE15 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE15%TYPE,
827 P_ATTRIBUTE_CATEGORY IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE_CATEGORY%TYPE,
828 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
829 X_MSG_COUNT OUT NOCOPY NUMBER,
830 X_MSG_DATA OUT NOCOPY VARCHAR2
831 )
832 IS
833 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE_GROUP_RELATE';
834 l_api_version CONSTANT NUMBER :=1.0;
835 l_bind_data_id number;
836
837 l_return_status VARCHAR2(200);
838 l_msg_count NUMBER;
839 l_msg_data VARCHAR2(200);
840 l_rowid VARCHAR2(200);
841
842 l_return_code VARCHAR2(100);
843 l_count NUMBER;
844 l_data VARCHAR2(200);
845 l_date Date;
846 l_user_id Number;
847 l_login_id Number;
848
849
850 l_GROUP_ID JTF_RS_GRP_RELATIONS.GROUP_ID%TYPE;
851 l_RELATED_GROUP_ID JTF_RS_GRP_RELATIONS.GROUP_ID%TYPE ;
852 l_RELATION_TYPE JTF_RS_GRP_RELATIONS.RELATION_TYPE%TYPE ;
853 l_start_date_active JTF_RS_GRP_RELATIONS.START_DATE_ACTIVE%TYPE ;
854 l_end_date_active JTF_RS_GRP_RELATIONS.END_DATE_ACTIVE%TYPE ;
855 l_temp_end_date_active JTF_RS_GRP_RELATIONS.END_DATE_ACTIVE%TYPE ;
856 l_object_version_number JTF_RS_GRP_RELATIONS.OBJECT_VERSION_NUMBER%TYPE;
857 l_delete_flag JTF_RS_GRP_RELATIONS.DELETE_FLAG%TYPE;
858
859 l_group_relate_id JTF_RS_GRP_RELATIONS.GROUP_RELATE_ID%TYPE := p_group_relate_id;
860
861 l_g_miss_date date := to_date(to_char(fnd_api.g_miss_date,'DD-MM-RRRR'),'DD-MM-RRRR') ;
862
863 L_ATTRIBUTE1 JTF_RS_GRP_RELATIONS.ATTRIBUTE1%TYPE;
864 L_ATTRIBUTE2 JTF_RS_GRP_RELATIONS.ATTRIBUTE2%TYPE;
865 L_ATTRIBUTE3 JTF_RS_GRP_RELATIONS.ATTRIBUTE3%TYPE;
866 L_ATTRIBUTE4 JTF_RS_GRP_RELATIONS.ATTRIBUTE4%TYPE;
867 L_ATTRIBUTE5 JTF_RS_GRP_RELATIONS.ATTRIBUTE5%TYPE;
868 L_ATTRIBUTE6 JTF_RS_GRP_RELATIONS.ATTRIBUTE6%TYPE;
869 L_ATTRIBUTE7 JTF_RS_GRP_RELATIONS.ATTRIBUTE7%TYPE;
870 L_ATTRIBUTE8 JTF_RS_GRP_RELATIONS.ATTRIBUTE8%TYPE;
871 L_ATTRIBUTE9 JTF_RS_GRP_RELATIONS.ATTRIBUTE9%TYPE;
872 L_ATTRIBUTE10 JTF_RS_GRP_RELATIONS.ATTRIBUTE10%TYPE;
873 L_ATTRIBUTE11 JTF_RS_GRP_RELATIONS.ATTRIBUTE11%TYPE;
874 L_ATTRIBUTE12 JTF_RS_GRP_RELATIONS.ATTRIBUTE12%TYPE;
875 L_ATTRIBUTE13 JTF_RS_GRP_RELATIONS.ATTRIBUTE13%TYPE;
876 L_ATTRIBUTE14 JTF_RS_GRP_RELATIONS.ATTRIBUTE14%TYPE;
877 L_ATTRIBUTE15 JTF_RS_GRP_RELATIONS.ATTRIBUTE15%TYPE;
878 L_ATTRIBUTE_CATEGORY JTF_RS_GRP_RELATIONS.ATTRIBUTE_CATEGORY%TYPE;
879
880
881
882 CURSOR grp_rel_cur(l_group_relate_id JTF_RS_GRP_RELATIONS.GROUP_RELATE_ID%TYPE )
883 IS
884 SELECT group_id,
885 related_group_id,
886 start_date_active,
887 end_date_active,
888 relation_type,
889 object_version_number,
890 delete_flag,
891 attribute1,
892 attribute2,
893 attribute3,
894 attribute4,
895 attribute5,
896 attribute6,
897 attribute7,
898 attribute8,
899 attribute9,
900 attribute10,
901 attribute11,
902 attribute12,
903 attribute13,
904 attribute14,
905 attribute15,
906 attribute_category
907 FROM jtf_rs_grp_relations
908 WHERE group_relate_id = l_group_relate_id;
909
910 grp_rel_rec grp_rel_cur%rowtype;
911
912
913
914 CURSOR check_overlap_cur(l_group_id JTF_RS_GRP_RELATIONS.GROUP_ID%TYPE ,
915 l_start_date_active JTF_RS_GRP_RELATIONS.START_DATE_ACTIVE%TYPE,
916 l_end_date_active JTF_RS_GRP_RELATIONS.START_DATE_ACTIVE%TYPE,
917 l_group_relate_id JTF_RS_GRP_RELATIONS.GROUP_RELATE_ID%TYPE)
918 IS
919 SELECT 'X'
920 FROM jtf_rs_grp_relations rel
921 WHERE rel.group_relate_id <> l_group_relate_id
922 AND rel.group_id = l_group_id
923 AND NVL(rel.delete_flag,'N') <> 'Y'
924 AND rel.relation_type = 'PARENT_GROUP'
925 AND rel.group_relate_id <> p_group_relate_id
926 AND ((l_start_date_active between rel.start_date_active and
927 nvl(rel.end_date_active,l_start_date_active+1))
928 OR (l_end_date_active between rel.start_date_active
929 and nvl(rel.end_date_active,l_end_date_active))
930 OR ((l_start_date_active <= rel.start_date_active)
931 AND (l_end_date_active >= rel.end_date_active
932 OR l_end_date_active IS NULL )));
933 -- OR rel.end_date_active IS NULL)));
934
935 check_overlap_rec check_overlap_cur%ROWTYPE;
936
937 CURSOR check_group_dt_cur(l_group_id JTF_RS_GRP_RELATIONS.GROUP_ID%TYPE ,
938 l_start_date_active JTF_RS_GRP_RELATIONS.START_DATE_ACTIVE%TYPE,
939 l_end_date_active JTF_RS_GRP_RELATIONS.START_DATE_ACTIVE%TYPE)
940 IS
941 SELECT 'X'
942 FROM jtf_rs_groups_b grp
943 WHERE grp.group_id = l_group_id
944 -- changed by nsinghai 20 May 2002 to handle null value of l_end_date_active
945 -- AND l_start_date_active >= grp.start_date_active
946 -- AND ((grp.end_date_active IS NULL)
947 -- OR (grp.end_date_active >= nvl(l_end_date_active,grp.end_date_active)));
948 AND trunc(l_start_date_active) between trunc(grp.start_date_active)
949 and nvl(trunc(grp.end_date_active),l_g_miss_date)
950 AND nvl(trunc(l_end_date_active),l_g_miss_date)
951 between trunc(l_start_date_active)
952 and nvl(trunc(grp.end_date_active),l_g_miss_date);
953
954 check_group_dt_rec check_group_dt_cur%rowtype;
955
956
957 CURSOR check_dates_cur(l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE ,
958 l_related_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE )
959 IS
960 SELECT 'X'
961 FROM jtf_rs_groups_b g1
962 ,jtf_rs_groups_b g2
963 WHERE g1.group_id = l_group_id
964 AND g2.group_id = l_related_group_id
965 AND ((g1.start_date_active <= g2.end_date_active and g2.end_date_active <> NULL)
966 or ((g2.end_date_active IS NULL) AND (g1.end_date_active <> NULL) AND
967 (g1.end_date_active >= g2.start_date_active))
968 OR((g2.end_date_active IS NULL) AND (g1.end_date_active IS NULL)));
969
970 check_dates_rec check_dates_cur%rowtype;
971
972
973 --cursor for cyclic dependency check
974 cursor dep_cur(L_GROUP_ID number,
975 l_related_group_id number,
976 l_start_date_active date,
977 l_end_date_active date)
978 is
979 select 'x'
980 from jtf_rs_groups_denorm
981 where parent_group_id = l_group_id
982 and group_id = l_related_group_id
983 and ( ( (l_start_date_active >= start_date_active)
984 AND ((l_start_date_active <= end_date_active)
985 OR (end_date_active IS NULL))
986 )
987 OR (
988 (l_end_date_active between start_date_active and nvl(end_date_active,l_g_miss_date))
989 OR ((nvl(l_end_date_active,start_date_active) >= start_date_active)
990 AND (end_date_active IS NULL))
991 -- OR (nvl(l_end_date_active,sysdate) <= end_date_active)
992 )
993 OR (
994 (l_start_date_active <= start_date_active)
995 AND
996 (nvl(l_end_date_active,l_g_miss_date) >= nvl(end_date_active,l_g_miss_date))
997 )
998 );
999
1000 dep_rec dep_cur%rowtype;
1001
1002 CURSOR parent_count_cur(l_group_id number)
1003 IS
1004 SELECT count(*) par_count
1005 from jtf_rs_grp_relations rel
1006 where rel.relation_type = 'PARENT_GROUP'
1007 connect by rel.group_id = prior related_group_id
1008 and nvl(delete_flag, 'N') <> 'Y'
1009 and rel.related_group_id <> l_group_id
1010 AND ((trunc(rel.start_date_active) <= prior rel.start_date_active
1011 AND nvl(rel.end_date_active, prior rel.start_date_active) >=
1012 trunc(prior rel.start_date_active)) OR
1013 (rel.start_date_active > trunc(prior rel.start_date_active)
1014 AND trunc(rel.start_date_active) <= nvl(prior rel.end_date_active,
1015 rel.start_date_active)))
1016 start with rel.group_id = l_group_id
1017 and nvl(rel.delete_flag,'N') <> 'Y';
1018
1019
1020 CURSOR child_count_cur(l_group_id number)
1021 IS
1022 SELECT count(*) par_count
1023 from jtf_rs_grp_relations rel
1024 where rel.relation_type = 'PARENT_GROUP'
1025 connect by rel.related_group_id = prior group_id
1026 and nvl(delete_flag, 'N') <> 'Y'
1027 and rel.group_id <> l_group_id
1028 AND ((trunc(rel.start_date_active) <= prior rel.start_date_active
1029 AND nvl(rel.end_date_active, prior rel.start_date_active) >=
1030 trunc(prior rel.start_date_active)) OR
1031 (rel.start_date_active > trunc(prior rel.start_date_active)
1032 AND trunc(rel.start_date_active) <= nvl(prior rel.end_date_active,
1033 rel.start_date_active)))
1034 start with rel.related_group_id = l_group_id
1035 and nvl(rel.delete_flag,'N') <> 'Y';
1036
1037 l_parent number;
1038 l_child number;
1039 l_request number;
1040
1041
1042
1043 cursor conc_prog_cur
1044 is
1045 select description
1046 from fnd_concurrent_programs_vl
1047 where concurrent_program_name = 'JTFRSDEN'
1048 and application_id = 690;
1049
1050
1051
1052 BEGIN
1053 --Standard Start of API SAVEPOINT
1054 SAVEPOINT GROUP_RELATE_SP;
1055
1056 x_return_status := fnd_api.g_ret_sts_success;
1057
1058 --Standard Call to check API compatibility
1059 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
1060 THEN
1061 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1062 END IF;
1063
1064 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
1065 IF FND_API.To_boolean(P_INIT_MSG_LIST)
1066 THEN
1067 FND_MSG_PUB.Initialize;
1068 END IF;
1069
1070 --GET USER ID AND SYSDATE
1071 l_date := sysdate;
1072 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
1073 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
1074
1075 -- user hook calls for customer
1076 -- Customer pre- processing section - mandatory
1077 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'UPDATE_RESOURCE_GROUP_RELATE', 'B', 'C' ))
1078 then
1079 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'UPDATE_RESOURCE_GROUP_RELATE', 'B', 'C' ))
1080 then
1081 JTF_RS_GROUP_RELATE_CUHK.UPDATE_RES_GROUP_RELATE_PRE(P_GROUP_RELATE_ID => p_group_relate_id,
1082 P_START_DATE_ACTIVE => p_start_date_active,
1083 P_END_DATE_ACTIVE => p_end_date_active,
1084 P_OBJECT_VERSION_NUM => p_object_version_num,
1085 p_data => L_data,
1086 p_count => L_count,
1087 P_return_code => l_return_code);
1088 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
1089 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
1090 x_return_status := fnd_api.g_ret_sts_error;
1091 fnd_message.set_name ('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
1092 FND_MSG_PUB.add;
1093 RAISE fnd_api.g_exc_error;
1094 end if;
1095 end if;
1096 end if;
1097
1098 /* Vertical industry pre- processing section - mandatory */
1099
1100 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'UPDATE_RESOURCE_GROUP_RELATE', 'B', 'V' ))
1101 then
1102 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'UPDATE_RESOURCE_GROUP_RELATE', 'B', 'V' ))
1103 then
1104
1105 JTF_RS_GROUP_RELATE_VUHK.UPDATE_RES_GROUP_RELATE_PRE(P_GROUP_RELATE_ID => p_group_relate_id,
1106 P_START_DATE_ACTIVE => p_start_date_active,
1107 P_END_DATE_ACTIVE => p_end_date_active,
1108 P_OBJECT_VERSION_NUM => p_object_version_num,
1109 p_data => l_data,
1110 p_count => l_count,
1111 P_return_code => l_return_code);
1112 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
1113 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
1114 x_return_status := fnd_api.g_ret_sts_error;
1115 fnd_message.set_name ('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
1116 FND_MSG_PUB.add;
1117 RAISE fnd_api.g_exc_error;
1118 end if;
1119 end if;
1120 end if;
1121
1122 /* Internal pre- processing section - mandatory */
1123
1124 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'UPDATE_RESOURCE_GROUP_RELATE', 'B', 'I' ))
1125 then
1126 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'UPDATE_RESOURCE_GROUP_RELATE', 'B', 'I' ))
1127 then
1128
1129 JTF_RS_GROUP_RELATE_IUHK.UPDATE_RES_GROUP_RELATE_PRE(P_GROUP_RELATE_ID => p_group_relate_id,
1130 P_START_DATE_ACTIVE => p_start_date_active,
1131 P_END_DATE_ACTIVE => p_end_date_active,
1132 P_OBJECT_VERSION_NUM => p_object_version_num,
1133 p_data => l_data,
1134 p_count => l_count,
1135 P_return_code => l_return_code);
1136 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
1137 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
1138 x_return_status := fnd_api.g_ret_sts_error;
1139 fnd_message.set_name ('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
1140 FND_MSG_PUB.add;
1141 RAISE fnd_api.g_exc_error;
1142 end if;
1143 end if;
1144 end if;
1145
1146 -- end of user hook call
1147
1148
1149 --assign the values to the local variables
1150 OPEN grp_rel_cur(l_group_relate_id);
1151 FETCH grp_rel_cur INTO grp_rel_rec;
1152 CLOSE grp_rel_cur;
1153
1154 l_group_id := grp_rel_rec.group_id;
1155
1156 l_related_group_id := grp_rel_rec.related_group_id;
1157 l_delete_flag := grp_rel_rec.delete_flag;
1158 l_object_version_number := grp_rel_rec.object_version_number;
1159 l_relation_type := grp_rel_rec.relation_type;
1160
1161
1162 IF(p_start_date_active = FND_API.G_MISS_DATE)
1163 THEN
1164 l_start_date_active := grp_rel_rec.start_date_active;
1165 ELSE
1166 l_start_date_active := p_start_date_active;
1167 END IF;
1168 IF(p_end_date_active = FND_API.G_MISS_DATE)
1169 THEN
1170 l_end_date_active := grp_rel_rec.end_date_active;
1171 ELSE
1172 l_end_date_active := p_end_date_active;
1173 END IF;
1174 IF(p_attribute1 = FND_API.G_MISS_CHAR)
1175 THEN
1176 l_attribute1 := grp_rel_rec.attribute1;
1177 ELSE
1178 l_attribute1 := p_attribute1;
1179 END IF;
1180 IF(p_attribute2= FND_API.G_MISS_CHAR)
1181 THEN
1182 l_attribute2 := grp_rel_rec.attribute2;
1183 ELSE
1184 l_attribute2 := p_attribute2;
1185 END IF;
1186 IF(p_attribute3 = FND_API.G_MISS_CHAR)
1187 THEN
1188 l_attribute3 := grp_rel_rec.attribute3;
1189 ELSE
1190 l_attribute3 := p_attribute3;
1191 END IF;
1192 IF(p_attribute4 = FND_API.G_MISS_CHAR)
1193 THEN
1194 l_attribute4 := grp_rel_rec.attribute1;
1195 ELSE
1196 l_attribute4 := p_attribute4;
1197 END IF;
1198 IF(p_attribute5 = FND_API.G_MISS_CHAR)
1199 THEN
1200 l_attribute5 := grp_rel_rec.attribute5;
1201 ELSE
1202 l_attribute5 := p_attribute5;
1203 END IF;
1204 IF(p_attribute6 = FND_API.G_MISS_CHAR)
1205 THEN
1206 l_attribute6 := grp_rel_rec.attribute1;
1207 ELSE
1208 l_attribute6 := p_attribute6;
1209 END IF;
1210 IF(p_attribute7 = FND_API.G_MISS_CHAR)
1211 THEN
1212 l_attribute7 := grp_rel_rec.attribute7;
1213 ELSE
1214 l_attribute7 := p_attribute7;
1215 END IF;
1216 IF(p_attribute8 = FND_API.G_MISS_CHAR)
1217 THEN
1218 l_attribute8 := grp_rel_rec.attribute8;
1219 ELSE
1220 l_attribute8 := p_attribute8;
1221 END IF;
1222 IF(p_attribute9 = FND_API.G_MISS_CHAR)
1223 THEN
1224 l_attribute9 := grp_rel_rec.attribute9;
1225 ELSE
1226 l_attribute9 := p_attribute9;
1227 END IF;
1228 IF(p_attribute10 = FND_API.G_MISS_CHAR)
1229 THEN
1230 l_attribute10 := grp_rel_rec.attribute10;
1231 ELSE
1232 l_attribute10 := p_attribute10;
1233 END IF;
1234 IF(p_attribute11 = FND_API.G_MISS_CHAR)
1235 THEN
1236 l_attribute11 := grp_rel_rec.attribute11;
1237 ELSE
1238 l_attribute11 := p_attribute11;
1239 END IF;
1240 IF(p_attribute12 = FND_API.G_MISS_CHAR)
1241 THEN
1242 l_attribute12 := grp_rel_rec.attribute12;
1243 ELSE
1244 l_attribute12 := p_attribute12;
1245 END IF;
1246 IF(p_attribute13 = FND_API.G_MISS_CHAR)
1247 THEN
1248 l_attribute13 := grp_rel_rec.attribute13;
1249 ELSE
1250 l_attribute13 := p_attribute13;
1251 END IF;
1252 IF(p_attribute14 = FND_API.G_MISS_CHAR)
1253 THEN
1254 l_attribute14 := grp_rel_rec.attribute14;
1255 ELSE
1256 l_attribute14 := p_attribute14;
1257 END IF;
1258 IF(p_attribute15 = FND_API.G_MISS_CHAR)
1259 THEN
1260 l_attribute15 := grp_rel_rec.attribute15;
1261 ELSE
1262 l_attribute15 := p_attribute15;
1263 END IF;
1264
1265 IF(p_attribute_category = FND_API.G_MISS_CHAR)
1266 THEN
1267 l_attribute_category := grp_rel_rec.attribute_category;
1268 ELSE
1269 l_attribute_category := p_attribute_category;
1270 END IF;
1271
1272 -- do the validations
1273 l_start_date_active := trunc(l_start_date_active);
1274 l_end_date_active := trunc(l_end_date_active);
1275
1276 JTF_RESOURCE_UTL.VALIDATE_INPUT_DATES(l_start_date_active,
1277 l_end_date_active,
1278 l_return_status);
1279
1280 IF(l_return_status <> fnd_api.g_ret_sts_success)
1281 THEN
1282 x_return_status := fnd_api.g_ret_sts_error;
1283 RAISE fnd_api.g_exc_error;
1284 END IF;
1285
1286
1287
1288 --check whether the same set of child and parent group have overlapping records for the same
1289 --time period if relation_type = PARENT_GROUP
1290 IF (l_relation_type='PARENT_GROUP')
1291 THEN
1292 IF (l_end_date_active is null) THEN
1293 l_temp_end_date_active := to_date(to_char(fnd_api.g_miss_date,'dd-MM-RRRR'),'dd-MM-RRRR');
1294 --l_temp_end_date_active := to_date('31-DEC-4712','dd-MM-RRRR');
1295 ELSE l_temp_end_date_active := l_end_date_active;
1296 END IF;
1297
1298 OPEN check_overlap_cur(l_group_id,
1299 l_start_date_active,
1300 l_end_date_active,
1301 l_group_relate_id);
1302
1303 FETCH check_overlap_cur INTO check_overlap_rec;
1304
1305 IF(check_overlap_cur%FOUND)
1306 THEN
1307 x_return_status := fnd_api.g_ret_sts_error;
1308 fnd_message.set_name ('JTF', 'JTF_RS_GRP_REL_OVERLAP');
1309 FND_MSG_PUB.add;
1310 RAISE fnd_api.g_exc_error;
1311 END IF;
1312 CLOSE check_overlap_cur;
1313
1314 END IF;
1315
1316
1317 --check for cyclic dependency
1318 IF (l_relation_type='PARENT_GROUP')
1319 THEN
1320 open dep_cur(l_group_id,
1321 l_related_group_id,
1322 l_start_date_active ,
1323 l_end_date_active );
1324 fetch dep_cur into dep_rec;
1325 if(dep_cur%found)
1326 then
1327 x_return_status := fnd_api.g_ret_sts_error;
1328 fnd_message.set_name ('JTF', 'JTF_RS_CYCLIC_DEP_ERR');
1329 FND_MSG_PUB.add;
1330 RAISE fnd_api.g_exc_error;
1331 end if;
1332 close dep_cur;
1333 END IF;
1334
1335
1336 --dates within child group dates
1337 OPEN check_group_dt_cur(l_group_id,
1338 l_start_date_active,
1339 l_end_date_active);
1340
1341 FETCH check_group_dt_cur INTO check_group_dt_rec;
1342 IF(check_group_dt_cur%NOTFOUND)
1343 THEN
1344 x_return_status := fnd_api.g_ret_sts_error;
1345 fnd_message.set_name ('JTF', 'JTF_RS_CHILD_GRP_DT_ERR');
1346 FND_MSG_PUB.add;
1347 RAISE fnd_api.g_exc_error;
1348
1349 END IF;
1350 CLOSE check_group_dt_cur;
1351
1352 --dates within related group dates
1353 OPEN check_group_dt_cur(l_related_group_id,
1354 l_start_date_active,
1355 l_end_date_active);
1356 FETCH check_group_dt_cur INTO check_group_dt_rec;
1357 IF(check_group_dt_cur%NOTFOUND)
1358 THEN
1359 x_return_status := fnd_api.g_ret_sts_error;
1360 fnd_message.set_name ('JTF', 'JTF_RS_RELATED_GRP_DT_ERR');
1361 FND_MSG_PUB.add;
1362 RAISE fnd_api.g_exc_error;
1363
1364 END IF;
1365 CLOSE check_group_dt_cur;
1366
1367 --check that child group dates are within the related group dates
1368 /* OPEN check_dates_cur(l_group_id,
1369 l_related_group_id);
1370 FETCH check_dates_cur INTO check_dates_rec;
1371 IF(check_dates_cur%NOTFOUND)
1372 THEN
1373 x_return_status := fnd_api.g_ret_sts_error;
1374 fnd_message.set_name ('JTF', 'JTF_RS_CHILD_REL_DT_ERR');
1375 FND_MSG_PUB.add;
1376 RAISE fnd_api.g_exc_error;
1377
1378 END IF;
1379 CLOSE check_dates_cur;*/
1380
1381
1382
1383 --call lock row for updation
1384 BEGIN
1385
1386 jtf_rs_grp_relations_pkg.lock_row(
1387 x_group_relate_id => l_group_relate_id,
1388 x_object_version_number => p_object_version_num
1389 );
1390
1391
1392 EXCEPTION
1393
1394 WHEN OTHERS THEN
1395 x_return_status := fnd_api.g_ret_sts_error;
1396 fnd_message.set_name('JTF', 'JTF_RS_ROW_LOCK_ERROR');
1397 fnd_msg_pub.add;
1398 RAISE fnd_api.g_exc_error;
1399
1400 END;
1401
1402
1403 l_object_version_number := p_object_version_num +1;
1404
1405 --call update table handler
1406 --call audit api
1407
1408 JTF_RS_GROUP_RELATE_AUD_PVT.update_group_relate(
1409 P_API_VERSION => 1.0,
1410 P_INIT_MSG_LIST => p_init_msg_list,
1411 P_COMMIT => null,
1412 P_GROUP_RELATE_ID => l_group_relate_id,
1413 P_GROUP_ID => l_group_id,
1414 P_RELATED_GROUP_ID => l_related_group_id,
1415 P_RELATION_TYPE => l_relation_type,
1416 P_START_DATE_ACTIVE => l_start_date_active,
1417 P_END_DATE_ACTIVE => l_end_date_active,
1418 P_OBJECT_VERSION_NUMBER => l_object_version_number,
1419 X_RETURN_STATUS => l_return_status,
1420 X_MSG_COUNT => l_msg_count,
1421 X_MSG_DATA => l_msg_data);
1422
1423 IF(l_return_status <> fnd_api.g_ret_sts_success)
1424 THEN
1425 x_return_status := fnd_api.g_ret_sts_error;
1426 fnd_message.set_name ('JTF', 'JTF_RS_GRP_RELATE_AUDIT_ERR');
1427 FND_MSG_PUB.add;
1428 RAISE fnd_api.g_exc_error;
1429
1430 END IF;
1431
1432
1433
1434
1435
1436 jtf_rs_grp_relations_pkg.update_row(
1437 X_GROUP_RELATE_ID => l_group_relate_id,
1438 X_GROUP_ID => l_group_id,
1439 X_RELATED_GROUP_ID => l_related_group_id,
1440 X_RELATION_TYPE => l_relation_type,
1441 X_START_DATE_ACTIVE => l_start_date_active,
1442 X_END_DATE_ACTIVE => l_end_date_active,
1443 X_DELETE_FLAG => l_delete_flag,
1444 X_OBJECT_VERSION_NUMBER => l_object_version_number,
1445 X_ATTRIBUTE2 => l_attribute2,
1446 X_ATTRIBUTE3 => l_attribute3,
1447 X_ATTRIBUTE4 => l_attribute4,
1448 X_ATTRIBUTE5 => l_attribute5,
1449 X_ATTRIBUTE6 => l_attribute6,
1450 X_ATTRIBUTE7 => l_attribute7,
1451 X_ATTRIBUTE8 => l_attribute8,
1452 X_ATTRIBUTE9 => l_attribute9,
1453 X_ATTRIBUTE10 => l_attribute10,
1454 X_ATTRIBUTE11 => l_attribute11,
1455 X_ATTRIBUTE12 => l_attribute12,
1456 X_ATTRIBUTE13 => l_attribute13,
1457 X_ATTRIBUTE14 => l_attribute14,
1458 X_ATTRIBUTE15 => l_attribute15,
1459 X_ATTRIBUTE_CATEGORY => l_attribute_category,
1460 X_ATTRIBUTE1 => l_attribute1,
1461 X_LAST_UPDATE_DATE => l_date,
1462 X_LAST_UPDATED_BY => l_user_id,
1463 X_LAST_UPDATE_LOGIN => l_login_id);
1464
1465
1466 p_object_version_num := l_object_version_number;
1467
1468
1469
1470 --call to insert records in jtf_rs_groups_denorm FOR UPDATION EFFECT
1471 IF(l_relation_type = 'PARENT_GROUP')
1472 THEN
1473 IF (l_start_date_active <> grp_rel_rec.start_date_active
1474 OR nvl(l_end_date_active, fnd_api.g_miss_date) <>
1475 nvl(grp_rel_rec.end_date_active, fnd_api.g_miss_date)
1476 OR l_relation_type <> grp_rel_rec.relation_type)
1477 THEN
1478 l_parent := 0;
1479 l_child := 0;
1480 BEGIN
1481 OPEN parent_count_cur(l_group_id);
1482 FETCH parent_count_cur INTO l_parent;
1483 CLOSE parent_count_cur;
1484
1485 OPEN child_count_cur(l_group_id);
1486 FETCH child_count_cur INTO l_child;
1487 CLOSE child_count_cur;
1488 EXCEPTION
1489 WHEN OTHERS THEN
1490 -- use concurrent program
1491 l_parent := 10;
1492 l_child := 10;
1493 END;
1494
1495 IF (l_parent < 1) then l_parent := 1; end if;
1496 IF (l_child < 1) then l_child := 1; end if;
1497 IF(l_parent * l_child > 50)
1498 THEN
1499 begin
1500 insert into jtf_rs_chgd_grp_relations
1501 (GROUP_RELATE_ID,
1502 GROUP_ID ,
1503 RELATED_GROUP_ID,
1504 RELATION_TYPE ,
1505 START_DATE_ACTIVE,
1506 END_DATE_ACTIVE,
1507 OPERATION_FLAG,
1508 CREATED_BY ,
1509 CREATION_DATE ,
1510 LAST_UPDATED_BY ,
1511 LAST_UPDATE_DATE,
1512 LAST_UPDATE_LOGIN)
1513 values(p_group_relate_id,
1514 l_group_id,
1515 l_related_group_id,
1516 l_relation_type,
1517 l_start_date_active,
1518 l_end_date_active,
1519 'U',
1520 l_user_id,
1521 l_date,
1522 l_user_id,
1523 l_date,
1524 l_login_id);
1525
1526 exception when others then
1527 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1528 fnd_message.set_token('P_SQLCODE',SQLCODE);
1529 fnd_message.set_token('P_SQLERRM',SQLERRM);
1530 fnd_message.set_token('P_API_NAME', l_api_name);
1531 FND_MSG_PUB.add;
1532 x_return_status := fnd_api.g_ret_sts_unexp_error;
1533 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1534 RAISE fnd_api.g_exc_unexpected_error;
1535
1536 end;
1537
1538
1539 begin
1540
1541 l_request := fnd_request.submit_request(APPLICATION => 'JTF',
1542 PROGRAM => 'JTFRSDEN');
1543 open conc_prog_cur;
1544 fetch conc_prog_cur into g_name;
1545 close conc_prog_cur;
1546
1547 fnd_message.set_name ('JTF', 'JTF_RS_CONC_START');
1548 fnd_message.set_token('P_NAME',g_name);
1549 fnd_message.set_token('P_ID',l_request);
1550 FND_MSG_PUB.add;
1551
1552 exception when others then
1553
1554 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1555 fnd_message.set_token('P_SQLCODE',SQLCODE);
1556 fnd_message.set_token('P_SQLERRM',SQLERRM);
1557 fnd_message.set_token('P_API_NAME', l_api_name);
1558 FND_MSG_PUB.add;
1559 x_return_status := fnd_api.g_ret_sts_unexp_error;
1560 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1561 RAISE fnd_api.g_exc_unexpected_error;
1562
1563 end;
1564 ELSE
1565 JTF_RS_GROUP_DENORM_PVT.UPDATE_GROUPS
1566 ( P_API_VERSION => 1.0,
1567 P_INIT_MSG_LIST => p_init_msg_list,
1568 P_COMMIT => null,
1569 P_GROUP_ID => l_group_id,
1570 X_RETURN_STATUS => l_return_status,
1571 X_MSG_COUNT => l_msg_count,
1572 X_MSG_DATA => l_msg_data);
1573
1574 IF(l_return_status <> fnd_api.g_ret_sts_success)
1575 THEN
1576 x_return_status := fnd_api.g_ret_sts_error;
1577 fnd_message.set_name ('JTF', 'JTF_RS_GRP_DENORM_ERR');
1578 FND_MSG_PUB.add;
1579 RAISE fnd_api.g_exc_error;
1580
1581 END IF;
1582 END IF; --end of count check
1583 else
1584 null;
1585 END IF; -- check of anything has changed at all
1586 END IF;
1587
1588
1589
1590 --end of update
1591 -- user hook calls for customer
1592 -- Customer post- processing section - mandatory
1593 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'UPDATE_RESOURCE_GROUP_RELATE', 'A', 'C' ))
1594 then
1595 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'UPDATE_RESOURCE_GROUP_RELATE', 'A', 'C' ))
1596 then
1597 JTF_RS_GROUP_RELATE_CUHK.UPDATE_RES_GROUP_RELATE_POST(P_GROUP_RELATE_ID => p_group_relate_id,
1598 P_START_DATE_ACTIVE => p_start_date_active,
1599 P_END_DATE_ACTIVE => p_end_date_active,
1600 P_OBJECT_VERSION_NUM => p_object_version_num,
1601 p_data => L_data,
1602 p_count => l_count,
1603 P_return_code => l_return_code);
1604 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
1605 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
1606 x_return_status := fnd_api.g_ret_sts_error;
1607 fnd_message.set_name ('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
1608 FND_MSG_PUB.add;
1609 RAISE fnd_api.g_exc_error;
1610 end if;
1611 end if;
1612 end if;
1613
1614 /* Vertical industry post- processing section - mandatory */
1615
1616 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'UPDATE_RESOURCE_GROUP_RELATE', 'A', 'V' ))
1617 then
1618 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'UPDATE_RESOURCE_GROUP_RELATE', 'A', 'V' ))
1619 then
1620 JTF_RS_GROUP_RELATE_VUHK.UPDATE_RES_GROUP_RELATE_POST(P_GROUP_RELATE_ID => p_group_relate_id,
1621 P_START_DATE_ACTIVE => p_start_date_active,
1622 P_END_DATE_ACTIVE => p_end_date_active,
1623 P_OBJECT_VERSION_NUM => p_object_version_num,
1624 p_data => L_data,
1625 p_count => L_count,
1626 P_return_code => l_return_code);
1627 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
1628 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
1629 x_return_status := fnd_api.g_ret_sts_error;
1630 fnd_message.set_name ('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
1631 FND_MSG_PUB.add;
1632 RAISE fnd_api.g_exc_error;
1633 end if;
1634 end if;
1635 end if;
1636
1637 /* Internal post- processing section - mandatory */
1638
1639 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'UPDATE_RESOURCE_GROUP_RELATE', 'A', 'I' ))
1640 then
1641 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'UPDATE_RESOURCE_GROUP_RELATE', 'A', 'I' ))
1642 then
1643 JTF_RS_GROUP_RELATE_IUHK.UPDATE_RES_GROUP_RELATE_POST(P_GROUP_RELATE_ID => p_group_relate_id,
1644 P_START_DATE_ACTIVE => p_start_date_active,
1645 P_END_DATE_ACTIVE => p_end_date_active,
1646 P_OBJECT_VERSION_NUM => p_object_version_num,
1647 p_data => L_data,
1648 p_count => L_count,
1649 P_return_code => l_return_code);
1650 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
1651 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
1652 x_return_status := fnd_api.g_ret_sts_error;
1653 fnd_message.set_name ('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
1654 FND_MSG_PUB.add;
1655 RAISE fnd_api.g_exc_error;
1656 end if;
1657 end if;
1658 end if;
1659
1660 -- end of user hook call
1661 IF jtf_resource_utl.ok_to_execute(
1662 'JTF_RS_GROUP_RELATE_PVT',
1663 'UPDATE_RESOURCE_GROUP_RELATE',
1664 'M',
1665 'M')
1666 THEN
1667 IF jtf_usr_hks.ok_to_execute(
1668 'JTF_RS_GROUP_RELATE_PVT',
1669 'UPDATE_RESOURCE_GROUP_RELATE',
1670 'M',
1671 'M')
1672 THEN
1673
1674 IF (jtf_rs_group_relate_cuhk.ok_to_generate_msg(
1675 p_group_relate_id => l_group_relate_id,
1676 x_return_status => x_return_status) )
1677 THEN
1678
1679 /* Get the bind data id for the Business Object Instance */
1680
1681 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
1682
1683
1684 /* Set bind values for the bind variables in the Business Object
1685 SQL */
1686
1687 jtf_usr_hks.load_bind_data(l_bind_data_id, 'group_relate_id',
1688 l_group_relate_id, 'S', 'N');
1689
1690
1691 /* Call the message generation API */
1692
1693 jtf_usr_hks.generate_message(
1694 p_prod_code => 'JTF',
1695 p_bus_obj_code => 'RS_GRL',
1696 p_action_code => 'U', /* I/U/D */
1697 p_bind_data_id => l_bind_data_id,
1698 x_return_code => x_return_status);
1699
1700
1701 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1702 x_return_status := fnd_api.g_ret_sts_error;
1703 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
1704 fnd_msg_pub.add;
1705 RAISE fnd_api.g_exc_error;
1706 END IF;
1707 END IF;
1708 END IF;
1709 END IF;
1710
1711
1712
1713 --standard commit
1714 IF fnd_api.to_boolean (p_commit)
1715 THEN
1716 COMMIT WORK;
1717 END IF;
1718
1719
1720 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1721
1722
1723 EXCEPTION
1724 WHEN fnd_api.g_exc_unexpected_error
1725 THEN
1726 ROLLBACK TO group_relate_sp;
1727 --fnd_message.set_name ('JTF', 'JTF_RS_GROUP_DENORM_ERR');
1728 --FND_MSG_PUB.add;
1729 --x_return_status := fnd_api.g_ret_sts_unexp_error;
1730 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1731 WHEN fnd_api.g_exc_error
1732 THEN
1733 ROLLBACK TO group_relate_sp;
1734 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1735
1736 WHEN OTHERS
1737 THEN
1738 ROLLBACK TO group_relate_sp;
1739 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1740 fnd_message.set_token('P_SQLCODE',SQLCODE);
1741 fnd_message.set_token('P_SQLERRM',SQLERRM);
1742 fnd_message.set_token('P_API_NAME',l_api_name);
1743 FND_MSG_PUB.add;
1744 x_return_status := fnd_api.g_ret_sts_unexp_error;
1745 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1746
1747 END update_resource_group_relate;
1748
1749
1750 /* Procedure to delete the resource group relation. */
1751
1752 PROCEDURE delete_resource_group_relate
1753 (P_API_VERSION IN NUMBER,
1754 P_INIT_MSG_LIST IN VARCHAR2,
1755 P_COMMIT IN VARCHAR2,
1756 P_GROUP_RELATE_ID IN JTF_RS_GRP_RELATIONS.GROUP_RELATE_ID%TYPE,
1757 P_OBJECT_VERSION_NUM IN JTF_RS_GROUPS_VL.OBJECT_VERSION_NUMBER%TYPE,
1758 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1759 X_MSG_COUNT OUT NOCOPY NUMBER,
1760 X_MSG_DATA OUT NOCOPY VARCHAR2
1761 )
1762 IS
1763 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RESOURCE_GROUP_RELATE';
1764 l_api_version CONSTANT NUMBER :=1.0;
1765 l_bind_data_id number;
1766
1767 l_return_status VARCHAR2(200);
1768 l_msg_count NUMBER;
1769 l_msg_data VARCHAR2(200);
1770 l_rowid VARCHAR2(200);
1771
1772 l_return_code VARCHAR2(100);
1773 l_count NUMBER;
1774 l_data VARCHAR2(200);
1775 l_date Date;
1776 l_user_id Number;
1777 l_login_id Number;
1778
1779
1780
1781 CURSOR grp_rel_cur(l_group_relate_id JTF_RS_GRP_RELATIONS.GROUP_RELATE_ID%TYPE )
1782 IS
1783 SELECT group_id,
1784 related_group_id,
1785 start_date_active,
1786 end_date_active,
1787 relation_type,
1788 object_version_number,
1789 delete_flag,
1790 attribute1,
1791 attribute2,
1792 attribute3,
1793 attribute4,
1794 attribute5,
1795 attribute6,
1796 attribute7,
1797 attribute8,
1798 attribute9,
1799 attribute10,
1800 attribute11,
1801 attribute12,
1802 attribute13,
1803 attribute14,
1804 attribute15,
1805 attribute_category
1806 FROM jtf_rs_grp_relations
1807 WHERE group_relate_id = l_group_relate_id;
1808
1809 grp_rel_rec grp_rel_cur%rowtype;
1810
1811 CURSOR parent_count_cur(l_group_id number)
1812 IS
1813 SELECT count(*) par_count
1814 from jtf_rs_grp_relations rel
1815 where rel.relation_type = 'PARENT_GROUP'
1816 connect by rel.group_id = prior related_group_id
1817 and nvl(delete_flag, 'N') <> 'Y'
1818 and rel.related_group_id <> l_group_id
1819 AND ((trunc(rel.start_date_active) <= prior rel.start_date_active
1820 AND nvl(rel.end_date_active, prior rel.start_date_active) >=
1821 trunc(prior rel.start_date_active)) OR
1822 (rel.start_date_active > trunc(prior rel.start_date_active)
1823 AND trunc(rel.start_date_active) <= nvl(prior rel.end_date_active,
1824 rel.start_date_active)))
1825 start with rel.group_id = l_group_id
1826 and nvl(rel.delete_flag,'N') <> 'Y';
1827
1828
1829 CURSOR child_count_cur(l_group_id number)
1830 IS
1831 SELECT count(*) child_count
1832 from jtf_rs_grp_relations rel
1833 where rel.relation_type = 'PARENT_GROUP'
1834 connect by rel.related_group_id = prior group_id
1835 and nvl(delete_flag, 'N') <> 'Y'
1836 and rel.group_id <> l_group_id
1837 AND ((trunc(rel.start_date_active) <= prior rel.start_date_active
1838 AND nvl(rel.end_date_active, prior rel.start_date_active) >=
1839 trunc(prior rel.start_date_active)) OR
1840 (rel.start_date_active > trunc(prior rel.start_date_active)
1841 AND trunc(rel.start_date_active) <= nvl(prior rel.end_date_active,
1842 rel.start_date_active)))
1843 start with rel.related_group_id = l_group_id
1844 and nvl(rel.delete_flag,'N') <> 'Y';
1845
1846 l_parent number;
1847 l_child number;
1848 l_request number;
1849
1850 cursor conc_prog_cur
1851 is
1852 select description
1853 from fnd_concurrent_programs_vl
1854 where concurrent_program_name = 'JTFRSDEN'
1855 and application_id = 690;
1856
1857
1858 BEGIN
1859 --Standard Start of API SAVEPOINT
1860 SAVEPOINT GROUP_RELATE_SP;
1861
1862 x_return_status := fnd_api.g_ret_sts_success;
1863
1864 --Standard Call to check API compatibility
1865 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
1866 THEN
1867 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1868 END IF;
1869
1870 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
1871 IF FND_API.To_boolean(P_INIT_MSG_LIST)
1872 THEN
1873 FND_MSG_PUB.Initialize;
1874 END IF;
1875
1876 --GET USER ID AND SYSDATE
1877 l_date := sysdate;
1878 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
1879 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
1880
1881 -- user hook calls for customer
1882 -- Customer pre- processing section - mandatory
1883 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'DELETE_RESOURCE_GROUP_RELATE', 'B', 'C' ))
1884 then
1885 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'DELETE_RESOURCE_GROUP_RELATE', 'B', 'C' ))
1886 then
1887 JTF_RS_GROUP_RELATE_CUHK.DELETE_RES_GROUP_RELATE_PRE(P_GROUP_RELATE_ID => p_group_relate_id,
1888 P_OBJECT_VERSION_NUM => p_object_version_num,
1889 p_data => L_data,
1890 p_count => L_count,
1891 P_return_code => l_return_code);
1892 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
1893 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
1894 x_return_status := fnd_api.g_ret_sts_error;
1895 fnd_message.set_name ('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
1896 FND_MSG_PUB.add;
1897 RAISE fnd_api.g_exc_error;
1898 end if;
1899 end if;
1900 end if;
1901
1902 /* Vertical industry pre- processing section - mandatory */
1903
1904 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'DELETE_RESOURCE_GROUP_RELATE', 'B', 'V' ))
1905 then
1906 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'DELETE_RESOURCE_GROUP_RELATE', 'B', 'V' ))
1907 then
1908
1909
1910 JTF_RS_GROUP_RELATE_VUHK.DELETE_RES_GROUP_RELATE_PRE(P_GROUP_RELATE_ID => p_group_relate_id,
1911 P_OBJECT_VERSION_NUM => p_object_version_num,
1912 p_data => l_data,
1913 p_count => l_count,
1914 P_return_code => l_return_code);
1915 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
1916 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
1917 x_return_status := fnd_api.g_ret_sts_error;
1918 fnd_message.set_name ('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
1919 FND_MSG_PUB.add;
1920 RAISE fnd_api.g_exc_error;
1921 end if;
1922 end if;
1923 end if;
1924
1925
1926
1927 /* Internal pre- processing section - mandatory */
1928
1929 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'DELETE_RESOURCE_GROUP_RELATE', 'B', 'I' ))
1930 then
1931 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'DELETE_RESOURCE_GROUP_RELATE', 'B', 'I' ))
1932 then
1933
1934
1935 JTF_RS_GROUP_RELATE_IUHK.DELETE_RES_GROUP_RELATE_PRE(P_GROUP_RELATE_ID => p_group_relate_id,
1936 P_OBJECT_VERSION_NUM => p_object_version_num,
1937 p_data => l_data,
1938 p_count => l_count,
1939 P_return_code => l_return_code);
1940 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
1941 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
1942 x_return_status := fnd_api.g_ret_sts_error;
1943 fnd_message.set_name ('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
1944 FND_MSG_PUB.add;
1945 RAISE fnd_api.g_exc_error;
1946 end if;
1947 end if;
1948 end if;
1949 -- end of user hook call
1950
1951
1952 OPEN grp_rel_cur(p_group_relate_id);
1953 FETCH grp_rel_cur INTO grp_rel_rec;
1954 CLOSE grp_rel_cur;
1955
1956
1957 --call audit api
1958
1959 JTF_RS_GROUP_RELATE_AUD_PVT.delete_group_relate(
1960 P_API_VERSION => 1.0,
1961 P_INIT_MSG_LIST => p_init_msg_list,
1962 P_COMMIT => null,
1963 P_GROUP_RELATE_ID => p_group_relate_id,
1964 X_RETURN_STATUS => l_return_status,
1965 X_MSG_COUNT => l_msg_count,
1966 X_MSG_DATA => l_msg_data);
1967
1968 IF(l_return_status <> fnd_api.g_ret_sts_success)
1969 THEN
1970 x_return_status := fnd_api.g_ret_sts_error;
1971 fnd_message.set_name ('JTF', 'JTF_RS_GRP_RELATE_AUDIT_ERR');
1972 FND_MSG_PUB.add;
1973 RAISE fnd_api.g_exc_error;
1974
1975 END IF;
1976
1977
1978
1979 --call lock row for updation
1980 BEGIN
1981
1982 jtf_rs_grp_relations_pkg.lock_row(
1983 x_group_relate_id => p_group_relate_id,
1984 x_object_version_number => p_object_version_num
1985 );
1986
1987
1988 EXCEPTION
1989
1990 WHEN OTHERS THEN
1991 x_return_status := fnd_api.g_ret_sts_error;
1992 fnd_message.set_name('JTF', 'JTF_RS_ROW_LOCK_ERROR');
1993 fnd_msg_pub.add;
1994 RAISE fnd_api.g_exc_error;
1995
1996 END;
1997
1998
1999 --fetch table handler to update the delete flag to 'Y'
2000 jtf_rs_grp_relations_pkg.update_row(
2001 X_GROUP_RELATE_ID => p_group_relate_id,
2002 X_GROUP_ID => grp_rel_rec.group_id,
2003 X_RELATED_GROUP_ID => grp_rel_rec.related_group_id,
2004 X_RELATION_TYPE => grp_rel_rec.relation_type,
2005 X_START_DATE_ACTIVE => grp_rel_rec.start_date_active,
2006 X_END_DATE_ACTIVE => grp_rel_rec.end_date_active,
2007 X_DELETE_FLAG => 'Y',
2008 X_OBJECT_VERSION_NUMBER => grp_rel_rec.object_version_number,
2009 X_ATTRIBUTE2 => grp_rel_rec.attribute2,
2010 X_ATTRIBUTE3 => grp_rel_rec.attribute3,
2011 X_ATTRIBUTE4 => grp_rel_rec.attribute4,
2012 X_ATTRIBUTE5 => grp_rel_rec.attribute5,
2013 X_ATTRIBUTE6 => grp_rel_rec.attribute6,
2014 X_ATTRIBUTE7 => grp_rel_rec.attribute7,
2015 X_ATTRIBUTE8 => grp_rel_rec.attribute8,
2016 X_ATTRIBUTE9 => grp_rel_rec.attribute9,
2017 X_ATTRIBUTE10 => grp_rel_rec.attribute10,
2018 X_ATTRIBUTE11 => grp_rel_rec.attribute11,
2019 X_ATTRIBUTE12 => grp_rel_rec.attribute12,
2020 X_ATTRIBUTE13 => grp_rel_rec.attribute13,
2021 X_ATTRIBUTE14 => grp_rel_rec.attribute14,
2022 X_ATTRIBUTE15 => grp_rel_rec.attribute15,
2023 X_ATTRIBUTE_CATEGORY => grp_rel_rec.attribute_category,
2024 X_ATTRIBUTE1 => grp_rel_rec.attribute1,
2025 X_LAST_UPDATE_DATE => l_date,
2026 X_LAST_UPDATED_BY => l_user_id,
2027 X_LAST_UPDATE_LOGIN => l_login_id);
2028
2029
2030
2031
2032 --call to delete records in jtf_rs_groups_denorm
2033 IF(grp_rel_rec.relation_type = 'PARENT_GROUP')
2034 THEN
2035 l_parent := 0;
2036 l_child := 0;
2037 BEGIN
2038 OPEN parent_count_cur(grp_rel_rec.related_group_id);
2039 FETCH parent_count_cur INTO l_parent;
2040 CLOSE parent_count_cur;
2041
2042 OPEN child_count_cur(grp_rel_rec.group_id);
2043 FETCH child_count_cur INTO l_child;
2044 CLOSE child_count_cur;
2045 EXCEPTION
2046 WHEN OTHERS THEN
2047 -- use concurrent program
2048 l_parent := 10;
2049 l_child := 10;
2050 END;
2051
2052 IF (l_parent < 1) then l_parent := 1; end if;
2053 IF (l_child < 1) then l_child := 1; end if;
2054 IF(l_parent * l_child > 50)
2055 THEN
2056 begin
2057 insert into jtf_rs_chgd_grp_relations
2058 (GROUP_RELATE_ID,
2059 GROUP_ID ,
2060 RELATED_GROUP_ID,
2061 RELATION_TYPE ,
2062 START_DATE_ACTIVE,
2063 END_DATE_ACTIVE,
2064 OPERATION_FLAG,
2065 CREATED_BY ,
2066 CREATION_DATE ,
2067 LAST_UPDATED_BY ,
2068 LAST_UPDATE_DATE,
2069 LAST_UPDATE_LOGIN)
2070 values(p_group_relate_id,
2071 grp_rel_rec.group_id,
2072 grp_rel_rec.related_group_id,
2073 grp_rel_rec.relation_type,
2074 grp_rel_rec.start_date_active,
2075 grp_rel_rec.end_date_active,
2076 'D',
2077 l_user_id,
2078 l_date,
2079 l_user_id,
2080 l_date,
2081 l_login_id);
2082 exception when others then
2083 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
2084 fnd_message.set_token('P_SQLCODE',SQLCODE);
2085 fnd_message.set_token('P_SQLERRM',SQLERRM);
2086 fnd_message.set_token('P_API_NAME', l_api_name);
2087 FND_MSG_PUB.add;
2088 x_return_status := fnd_api.g_ret_sts_unexp_error;
2089 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2090 RAISE fnd_api.g_exc_unexpected_error;
2091
2092 end;
2093 begin
2094 l_request := fnd_request.submit_request(APPLICATION => 'JTF',
2095 PROGRAM => 'JTFRSDEN');
2096
2097
2098 open conc_prog_cur;
2099 fetch conc_prog_cur into g_name;
2100 close conc_prog_cur;
2101
2102 fnd_message.set_name ('JTF', 'JTF_RS_CONC_START');
2103 fnd_message.set_token('P_NAME',g_name);
2104 fnd_message.set_token('P_ID',l_request);
2105 FND_MSG_PUB.add;
2106
2107 exception when others then
2108 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
2109 fnd_message.set_token('P_SQLCODE',SQLCODE);
2110 fnd_message.set_token('P_SQLERRM',SQLERRM);
2111 fnd_message.set_token('P_API_NAME', l_api_name);
2112 FND_MSG_PUB.add;
2113 x_return_status := fnd_api.g_ret_sts_unexp_error;
2114 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2115 RAISE fnd_api.g_exc_unexpected_error;
2116
2117 end;
2118 ELSE
2119 /* JTF_RS_GROUP_DENORM_PVT.DELETE_GROUPS
2120 ( P_API_VERSION => 1.0,
2121 P_INIT_MSG_LIST => p_init_msg_list,
2122 P_COMMIT => null,
2123 P_GROUP_ID => grp_rel_rec.group_id,
2124 X_RETURN_STATUS => l_return_status,
2125 X_MSG_COUNT => l_msg_count,
2126 X_MSG_DATA => l_msg_data); */
2127
2128 JTF_RS_GROUP_DENORM_PVT.DELETE_GRP_RELATIONS
2129 ( P_API_VERSION => 1.0,
2130 P_INIT_MSG_LIST => p_init_msg_list,
2131 P_COMMIT => null,
2132 P_group_relate_id => p_group_relate_id,
2133 P_GROUP_ID => grp_rel_rec.group_id,
2134 P_RELATED_GROUP_ID => grp_rel_rec.related_group_id,
2135 X_RETURN_STATUS => l_return_status,
2136 X_MSG_COUNT => l_msg_count,
2137 X_MSG_DATA => l_msg_data);
2138
2139 IF(l_return_status <> fnd_api.g_ret_sts_success)
2140 THEN
2141 x_return_status := fnd_api.g_ret_sts_error;
2142 fnd_message.set_name ('JTF', 'JTF_RS_GRP_DENORM_ERR');
2143 FND_MSG_PUB.add;
2144 RAISE fnd_api.g_exc_error;
2145
2146 END IF;
2147 END IF; -- end of count
2148 END IF;
2149
2150
2151 -- user hook calls for customer
2152 -- Customer post- processing section - mandatory
2153 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'DELETE_RESOURCE_GROUP_RELATE', 'A', 'C' ))
2154 then
2155 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'DELETE_RESOURCE_GROUP_RELATE', 'A', 'C' ))
2156 then
2157 JTF_RS_GROUP_RELATE_CUHK.DELETE_RES_GROUP_RELATE_POST(P_GROUP_RELATE_ID => p_group_relate_id,
2158 P_OBJECT_VERSION_NUM => p_object_version_num,
2159 p_data => L_data,
2160 p_count => l_count,
2161 P_return_code => l_return_code);
2162 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
2163 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
2164 x_return_status := fnd_api.g_ret_sts_error;
2165 fnd_message.set_name ('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
2166 FND_MSG_PUB.add;
2167 RAISE fnd_api.g_exc_error;
2168 end if;
2169 end if;
2170 end if;
2171
2172 /* Vertical industry post- processing section - mandatory */
2173
2174 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'DELETE_RESOURCE_GROUP_RELATE', 'A', 'V' ))
2175 then
2176 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'DELETE_RESOURCE_GROUP_RELATE', 'A', 'V' ))
2177 then
2178
2179 JTF_RS_GROUP_RELATE_VUHK.DELETE_RES_GROUP_RELATE_POST(P_GROUP_RELATE_ID => p_group_relate_id,
2180 P_OBJECT_VERSION_NUM => p_object_version_num,
2181 p_data => L_data,
2182 p_count => L_count,
2183 P_return_code => l_return_code);
2184 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
2185 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
2186 x_return_status := fnd_api.g_ret_sts_error;
2187 fnd_message.set_name ('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
2188 FND_MSG_PUB.add;
2189 RAISE fnd_api.g_exc_error;
2190 end if;
2191 end if;
2192 end if;
2193
2194 /* Internal industry post- processing section - mandatory */
2195
2196 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'DELETE_RESOURCE_GROUP_RELATE', 'A', 'I' ))
2197 then
2198 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'DELETE_RESOURCE_GROUP_RELATE', 'A', 'I' ))
2199 then
2200
2201 JTF_RS_GROUP_RELATE_IUHK.DELETE_RES_GROUP_RELATE_POST(P_GROUP_RELATE_ID => p_group_relate_id,
2202 P_OBJECT_VERSION_NUM => p_object_version_num,
2203 p_data => L_data,
2204 p_count => L_count,
2205 P_return_code => l_return_code);
2206 if ( l_return_code = FND_API.G_RET_STS_ERROR) OR
2207 (l_return_code = FND_API.G_RET_STS_UNEXP_ERROR ) then
2208 x_return_status := fnd_api.g_ret_sts_error;
2209 fnd_message.set_name ('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
2210 FND_MSG_PUB.add;
2211 RAISE fnd_api.g_exc_error;
2212 end if;
2213 end if;
2214 end if;
2215
2216 -- end of user hook call
2217 IF jtf_resource_utl.ok_to_execute(
2218 'JTF_RS_GROUP_RELATE_PVT',
2219 'DELETE_RESOURCE_GROUP_RELATE',
2220 'M',
2221 'M')
2222 THEN
2223 IF jtf_usr_hks.ok_to_execute(
2224 'JTF_RS_GROUP_RELATE_PVT',
2225 'DELETE_RESOURCE_GROUP_RELATE',
2226 'M',
2227 'M')
2228 THEN
2229
2230 IF (jtf_rs_group_relate_cuhk.ok_to_generate_msg(
2231 p_group_relate_id => p_group_relate_id,
2232 x_return_status => x_return_status) )
2233 THEN
2234
2235 /* Get the bind data id for the Business Object Instance */
2236
2237 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
2238
2239
2240 /* Set bind values for the bind variables in the Business Object
2241 SQL */
2242
2243 jtf_usr_hks.load_bind_data(l_bind_data_id, 'group_relate_id',
2244 p_group_relate_id, 'S', 'N');
2245
2246
2247 /* Call the message generation API */
2248
2249 jtf_usr_hks.generate_message(
2250 p_prod_code => 'JTF',
2251 p_bus_obj_code => 'RS_GRL',
2252 p_action_code => 'I', /* I/U/D */
2253 p_bind_data_id => l_bind_data_id,
2254 x_return_code => x_return_status);
2255
2256
2257 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
2258 x_return_status := fnd_api.g_ret_sts_error;
2259 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
2260 fnd_msg_pub.add;
2261 RAISE fnd_api.g_exc_error;
2262 END IF;
2263 END IF;
2264 END IF;
2265 END IF;
2266
2267
2268 --standard commit
2269 IF fnd_api.to_boolean (p_commit)
2270 THEN
2271 COMMIT WORK;
2272 END IF;
2273
2274
2275 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2276
2277
2278 EXCEPTION
2279 WHEN fnd_api.g_exc_unexpected_error
2280 THEN
2281 -- ROLLBACK TO group_relate_sp;
2282 --fnd_message.set_name ('JTF', 'JTF_RS_GROUP_DENORM_ERR');
2283 --FND_MSG_PUB.add;
2284 --x_return_status := fnd_api.g_ret_sts_unexp_error;
2285 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2286 WHEN fnd_api.g_exc_error
2287 THEN
2288 -- ROLLBACK TO group_relate_sp;
2289 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2290
2291 WHEN OTHERS
2292 THEN
2293 -- ROLLBACK TO group_relate_sp;
2294 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
2295 fnd_message.set_token('P_SQLCODE',SQLCODE);
2296 fnd_message.set_token('P_SQLERRM',SQLERRM);
2297 fnd_message.set_token('P_API_NAME',l_api_name);
2298 FND_MSG_PUB.add;
2299 x_return_status := fnd_api.g_ret_sts_unexp_error;
2300 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2301
2302
2303 END delete_resource_group_relate;
2304 END jtf_rs_group_relate_pvt;