[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_ROLE_RELATE_PVT
Source
1 PACKAGE BODY jtf_rs_role_relate_pvt AS
2 /* $Header: jtfrsvlb.pls 120.3.12020000.2 2012/07/18 12:50:30 rgokavar ship $ */
3
4 /*****************************************************************************************
5 This is a public API that caller will invoke.
6 It provides procedures for managing resource roles, like
7 create, update and delete resource roles from other modules.
8 Its main procedures are as following:
9 Create Resource Role Relate
10 Update Resource Role Relate
11 Delete Resource Role Relate
12 Calls to these procedures will invoke procedures from jtf_rs_role_relate_pvt
13 to do business validations and to do actual inserts, updates and deletes into tables.
14 ******************************************************************************************/
15 /* Package variables. */
16
17 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_RS_ROLE_RELATE_PVT';
18 G_NAME VARCHAR2(240);
19
20
21 /* private procedure to check that is updating role date for resource then
22 group/team meber roles are still valid */
23 procedure validate_indv_role_date(p_role_relate_id IN NUMBER,
24 p_role_id IN NUMBER ,
25 p_resource_id IN NUMBER,
26 p_old_start_date IN DATE ,
27 p_old_end_date IN DATE ,
28 p_new_start_date IN DATE ,
29 p_new_end_date IN DATE ,
30 p_valid OUT NOCOPY BOOLEAN);
31
32
33 procedure validate_indv_role_date(p_role_relate_id IN NUMBER,
34 p_role_id IN NUMBER ,
35 p_resource_id IN NUMBER,
36 p_old_start_date IN DATE ,
37 p_old_end_date IN DATE ,
38 p_new_start_date IN DATE ,
39 p_new_end_date IN DATE ,
40 p_valid OUT NOCOPY BOOLEAN)
41 is
42
43 CURSOR rsc_cur(ll_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE)
44 IS
45 SELECT rsc.start_date_active,
46 rsc.end_date_active
47 FROM jtf_rs_resource_extns rsc
48 WHERE rsc.resource_id = ll_resource_id;
49
50 rsc_rec rsc_cur%rowtype;
51
52 l_valid boolean := TRUE;
53
54 cursor grp_mem_cur
55 is
56 select rlt.role_relate_id,
57 rlt.start_date_active,
58 rlt.end_date_active
59 from jtf_rs_role_relations rlt,
60 jtf_rs_group_members mem
61 where mem.resource_id = p_resource_id
62 and nvl(mem.delete_flag, 'N') <> 'Y'
63 and rlt.role_resource_id = mem.group_member_id
64 and rlt.role_id = p_role_id --added vide bug#2474811
65 and rlt.role_resource_type = 'RS_GROUP_MEMBER'
66 and nvl(rlt.delete_flag, 'N') <> 'Y'
67 and rlt.start_date_active between p_old_start_date and
68 to_date(to_char(nvl(p_old_end_date, fnd_api.g_miss_date), 'DD-MM-RRRR'), 'DD-MM-RRRR');
69 l_grp_valid BOOLEAN := TRUE;
70
71
72 cursor team_mem_cur
73 is
74 select rlt.role_relate_id,
75 rlt.start_date_active,
76 rlt.end_date_active
77 from jtf_rs_role_relations rlt,
78 jtf_rs_team_members mem
79 where mem.team_resource_id = p_resource_id
80 and mem.resource_type = 'INDIVIDUAL'
81 and nvl(mem.delete_flag, 'N') <> 'Y'
82 and rlt.role_resource_id = mem.team_member_id
83 and rlt.role_id = p_role_id --added vide bug#2474811
84 and rlt.role_resource_type = 'RS_TEAM_MEMBER'
85 and nvl(rlt.delete_flag, 'N') <> 'Y'
86 and rlt.start_date_active between p_old_start_date and
87 to_date(to_char(nvl(p_old_end_date, fnd_api.g_miss_date), 'DD-MM-RRRR'), 'DD-MM-RRRR') ;
88 l_team_valid BOOLEAN := TRUE;
89
90 /* removed the below parameter since it is not used anywhere */
91 -- l_end_date date := to_date(to_char(fnd_api.g_miss_date, 'DD-MM-RRRR'), 'DD-MM-RRRR');
92 begin
93
94 open rsc_cur(p_resource_id);
95 fetch rsc_cur INTO rsc_rec;
96 close rsc_cur;
97 IF((rsc_rec.start_date_active > p_new_start_date)
98 -- changed by sudarsana 11 feb 2002
99 OR (rsc_rec.end_date_active < to_date(to_char(nvl(p_new_end_date, fnd_api.g_miss_date), 'DD-MM-RRRR'), 'DD-MM-RRRR')))
100 THEN
101 fnd_message.set_name ('JTF', 'JTF_RS_RES_DATE_ERR');
102 FND_MSG_PUB.add;
103 l_valid := FALSE;
104 END IF;
105
106 for grp_mem_rec in grp_mem_cur
107 loop
108 if(grp_mem_rec.start_date_active not between p_new_start_date
109 and to_date(to_char(nvl(p_new_end_date, fnd_api.g_miss_date), 'DD-MM-RRRR'), 'DD-MM-RRRR'))
110 then
111 l_grp_valid := FALSE;
112 end if;
113
114 if(to_date(to_char(nvl(grp_mem_rec.end_date_active, fnd_api.g_miss_date), 'DD-MM-RRRR'), 'DD-MM-RRRR') not between p_new_start_date
115 and to_date(to_char(nvl(p_new_end_date, fnd_api.g_miss_date), 'DD-MM-RRRR'), 'DD-MM-RRRR'))
116 then
117 l_grp_valid := FALSE;
118 end if;
119
120 if NOT(l_grp_valid)
121 then
122 fnd_message.set_name ('JTF', 'JTF_RS_RES_UPD_DT_ERR');
123 FND_MSG_PUB.add;
124 exit;
125 end if;
126 end loop; --end of grp_mem_cur
127
128 for team_mem_rec in team_mem_cur
129 loop
130 if(team_mem_rec.start_date_active not between p_new_start_date
131 and to_date(to_char(nvl(p_new_end_date, fnd_api.g_miss_date), 'DD-MM-RRRR'), 'DD-MM-RRRR'))
132 then
133 l_team_valid := FALSE;
134 end if;
135
136 if(to_date(to_char(nvl(team_mem_rec.end_date_active, fnd_api.g_miss_date), 'DD-MM-RRRR'), 'DD-MM-RRRR')
137 not between p_new_start_date and to_date(to_char(nvl(p_new_end_date, fnd_api.g_miss_date), 'DD-MM-RRRR'), 'DD-MM-RRRR'))
138 then
139 l_team_valid := FALSE;
140 end if;
141
142 if NOT(l_team_valid)
143 then
144 fnd_message.set_name ('JTF', 'JTF_RS_RES_UPD_DT_ERR');
145 FND_MSG_PUB.add;
146 exit;
147 end if;
148 end loop; --end of grp_mem_cur
149
150 if NOT (l_grp_valid)
151 OR NOT (l_team_valid)
152 OR NOT (l_valid)
153 then
154 p_valid := FALSE;
155 end if;
156
157 end validate_indv_role_date;
158
159
160
161 /* private procedure to check that role type is active during
162 this role relation dates */
163 procedure validate_role_type(p_role_id IN NUMBER ,
164 p_start_date IN DATE ,
165 p_end_date IN DATE ,
166 p_valid OUT NOCOPY BOOLEAN);
167
168
169 procedure validate_role_type(p_role_id IN NUMBER ,
170 p_start_date IN DATE ,
171 p_end_date IN DATE ,
172 p_valid OUT NOCOPY BOOLEAN)
173 is
174
175 CURSOR get_type_cur(l_role_id JTF_RS_ROLES_B.role_id%TYPE)
176 IS
177 SELECT role_type_code
178 FROM jtf_rs_roles_b
179 WHERE role_id = l_role_id;
180
181 role_type_rec get_type_cur%rowtype;
182
183 cursor chk_role_type_cur(l_role_type FND_LOOKUPS.LOOKUP_CODE%type)
184 is
185 select 'X'
186 from fnd_lookups
187 where lookup_type = 'JTF_RS_ROLE_TYPE'
188 and lookup_code = l_role_type
189 and ENABLED_FLAG = 'Y'
190 and START_DATE_ACTIVE <= p_start_date
191 and (END_DATE_ACTIVE is NULL or
192 (p_end_date is not null and
193 END_DATE_ACTIVE >= p_end_date));
194
195 chk_role_type_rec chk_role_type_cur%rowtype;
196
197 begin
198 p_valid := FALSE;
199 open get_type_cur(p_role_id);
200 fetch get_type_cur INTO role_type_rec;
201 if (get_type_cur%found) then
202 close get_type_cur;
203 open chk_role_type_cur(role_type_rec.role_type_code);
204 fetch chk_role_type_cur INTO chk_role_type_rec;
205 if (chk_role_type_cur%found) then
206 p_valid := TRUE;
207 end if;
208 close chk_role_type_cur;
209 else
210 close get_type_cur;
211 end if;
212 end validate_role_type;
213
214
215 /* Procedure to create the resource roles
216 based on input values passed by calling routines. */
217
218 PROCEDURE create_resource_role_relate
219 (P_API_VERSION IN NUMBER,
220 P_INIT_MSG_LIST IN VARCHAR2,
221 P_COMMIT IN VARCHAR2,
222 P_ROLE_RESOURCE_TYPE IN JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_TYPE%TYPE,
223 P_ROLE_RESOURCE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_ID%TYPE,
224 P_ROLE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_ID%TYPE,
225 P_START_DATE_ACTIVE IN JTF_RS_ROLE_RELATIONS.START_DATE_ACTIVE%TYPE,
226 P_END_DATE_ACTIVE IN JTF_RS_ROLE_RELATIONS.END_DATE_ACTIVE%TYPE,
227 P_ATTRIBUTE1 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE1%TYPE,
228 P_ATTRIBUTE2 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE2%TYPE,
229 P_ATTRIBUTE3 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE3%TYPE,
230 P_ATTRIBUTE4 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE4%TYPE,
231 P_ATTRIBUTE5 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE5%TYPE,
232 P_ATTRIBUTE6 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE6%TYPE,
233 P_ATTRIBUTE7 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE7%TYPE,
234 P_ATTRIBUTE8 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE8%TYPE,
235 P_ATTRIBUTE9 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE9%TYPE,
236 P_ATTRIBUTE10 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE10%TYPE,
237 P_ATTRIBUTE11 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE11%TYPE,
238 P_ATTRIBUTE12 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE12%TYPE,
239 P_ATTRIBUTE13 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE13%TYPE,
240 P_ATTRIBUTE14 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE14%TYPE,
241 P_ATTRIBUTE15 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE15%TYPE,
242 P_ATTRIBUTE_CATEGORY IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE_CATEGORY%TYPE,
243 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
244 X_MSG_COUNT OUT NOCOPY NUMBER,
245 X_MSG_DATA OUT NOCOPY VARCHAR2,
246 X_ROLE_RELATE_ID OUT NOCOPY JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE
247 )IS
248
249 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RESOURCE_ROLE_RELATE';
250 l_api_version CONSTANT NUMBER := 1.0;
251 l_bind_data_id NUMBER;
252
253 /* Moved the initial assignment of below variables to inside begin */
254 l_role_resource_type JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_TYPE%TYPE;
255 l_role_resource_id JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_ID%TYPE;
256 l_role_id JTF_RS_ROLE_RELATIONS.ROLE_ID%TYPE;
257 -- added truncate on 12 feb 2002
258 l_start_date_active JTF_RS_ROLE_RELATIONS.START_DATE_ACTIVE%TYPE;
259 l_end_date_active JTF_RS_ROLE_RELATIONS.START_DATE_ACTIVE%TYPE;
260
261 l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE;
262 l_return_code VARCHAR2(100);
263 l_count NUMBER;
264 l_data VARCHAR2(200);
265
266 l_return_status VARCHAR2(200);
267 l_msg_count NUMBER;
268 l_msg_data VARCHAR2(200);
269 l_rowid VARCHAR2(200);
270
271 l_date_invalid boolean := FALSE;
272
273
274 CURSOR team_mem_cur(l_team_member_id JTF_RS_TEAM_MEMBERS.TEAM_MEMBER_ID%TYPE)
275 IS
276 SELECT resource_type,
277 team_resource_id
278 FROM jtf_rs_team_members
279 WHERE team_member_id = l_team_member_id;
280
281 --Bug 9652619 To fetch Only Active Group members delete_flag condition is added
282 CURSOR grp_mem_cur(l_grp_member_id JTF_RS_GROUP_MEMBERS.GROUP_MEMBER_ID%TYPE)
283 IS
284 SELECT resource_id
285 FROM jtf_rs_group_members
286 WHERE group_member_id = l_grp_member_id
287 AND NVL(delete_flag,'O') <> 'Y';
288
289 l_rsc_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE;
290 l_team_resource_type JTF_RS_TEAM_MEMBERS.RESOURCE_TYPE%TYPE;
291
292 --changed the date comparison in the cursor 07/07/00
293 CURSOR res_role_cur(ll_role_resource_id JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_ID%TYPE,
294 ll_role_id JTF_RS_ROLES_B.ROLE_ID%TYPE,
295 ll_start_date_active JTF_RS_ROLE_RELATIONS.START_DATE_ACTIVE%TYPE,
296 ll_end_date_active JTF_RS_ROLE_RELATIONS.END_DATE_ACTIVE%TYPE )
297 IS
298 SELECT 'X'
299 FROM jtf_rs_role_relations
300 WHERE role_resource_type = 'RS_INDIVIDUAL'
301 AND role_resource_id = ll_role_resource_id
302 AND role_id = ll_role_id
303 AND nvl(delete_flag, '0') <> 'Y'
304 AND to_date(to_char(start_date_active , 'dd-MM-yyyy'),'dd-MM-yyyy') <=
305 to_date(to_char(ll_start_date_active, 'dd-MM-yyyy'),'dd-MM-yyyy')
306 AND ( to_date(to_char(end_date_active, 'dd-MM-yyyy'),'dd-MM-yyyy')
307 >= to_date(to_char(ll_end_date_active, 'dd-MM-yyyy'),'dd-MM-yyyy')
308 OR ( end_date_active IS NULL AND ll_end_date_active IS NULL)
309 OR (end_date_active IS NULL AND ll_end_date_active IS NOT NULL))
310 AND nvl(delete_flag, '0') <> 'Y';
311
312 res_role_rec res_role_cur%rowtype;
313
314 CURSOR grp_role_cur(ll_role_resource_id JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_ID%TYPE,
315 ll_role_id JTF_RS_ROLES_B.ROLE_ID%TYPE,
316 ll_start_date_active JTF_RS_ROLE_RELATIONS.START_DATE_ACTIVE%TYPE,
317 ll_end_date_active JTF_RS_ROLE_RELATIONS.END_DATE_ACTIVE%TYPE )
318 IS
319 SELECT 'X'
320 FROM jtf_rs_role_relations
321 WHERE role_resource_type = 'RS_GROUP'
322 AND role_resource_id = ll_role_resource_id
323 AND role_id = ll_role_id
324 AND start_date_active <= ll_start_date_active
325 AND nvl(delete_flag, '0') <> 'Y'
326 AND ( end_date_active >= ll_end_date_active
327 OR ( end_date_active IS NULL AND ll_end_date_active IS NULL)
328 OR (end_date_active IS NULL AND ll_end_date_active IS NOT NULL))
329 AND nvl(delete_flag, '0') <> 'Y';
330
331 grp_role_rec grp_role_cur%rowtype;
332
333 l_role_valid boolean := FALSE;
334
335 CURSOR check_date_cur(ll_role_resource_type JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_TYPE%TYPE ,
336 ll_role_resource_id JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_ID%TYPE,
337 ll_role_id JTF_RS_ROLE_RELATIONS.ROLE_ID%TYPE)
338 IS
339 SELECT start_date_active,
340 end_date_active
341 FROM jtf_rs_role_relations
342 WHERE role_resource_type = ll_role_resource_type
343 AND role_resource_id = ll_role_resource_id
344 AND role_id = ll_role_id
345 AND nvl(delete_flag, 'N') <> 'Y';
346
347 check_date_rec check_date_cur%rowtype;
348
349 CURSOR group_cur(ll_member_id JTF_RS_GROUP_MEMBERS.GROUP_MEMBER_ID%TYPE)
350 IS
351 SELECT grp.start_date_active,
352 grp.end_date_active
353 FROM jtf_rs_groups_b grp,
354 jtf_rs_group_members mem
355 WHERE mem.group_member_id = ll_member_id
356 AND mem.group_id = grp.group_id
357 AND nvl(mem.delete_flag, '0') <> 'Y';
358
359 group_rec group_cur%rowtype;
360
361 --
362 CURSOR group_dt_cur(l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE)
363 IS
364 SELECT grp.start_date_active,
365 grp.end_date_active
366 FROM jtf_rs_groups_b grp
367 WHERE grp.group_id = l_group_id;
368
369 group_dt_rec group_dt_cur%rowtype;
370
371 CURSOR team_dt_cur(l_team_id JTF_RS_TEAMS_B.TEAM_ID%TYPE)
372 IS
373 SELECT tm.start_date_active,
374 tm.end_date_active
375 FROM jtf_rs_teams_b tm
376 WHERE tm.team_id = l_team_id;
377
378 team_dt_rec team_dt_cur%rowtype;
379
380
381
382 CURSOR team_cur(ll_member_id JTF_RS_TEAM_MEMBERS.TEAM_MEMBER_ID%TYPE)
383 IS
384 SELECT tm.start_date_active,
385 tm.end_date_active
386 FROM jtf_rs_teams_b tm,
387 jtf_rs_team_members mem
388 WHERE mem.team_member_id = ll_member_id
389 AND mem.team_id = tm.team_id;
390
391 team_rec team_cur%rowtype;
392
393
394 CURSOR rsc_cur(ll_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE)
395 IS
396 SELECT rsc.start_date_active,
397 rsc.end_date_active
398 FROM jtf_rs_resource_extns rsc
399 WHERE rsc.resource_id = ll_resource_id;
400
401 rsc_rec rsc_cur%rowtype;
402
403
404 --exclusive flag check cursor
405 CURSOR c_exclusive_group_check_cur(l_member_id JTF_RS_GROUP_MEMBERS.GROUP_MEMBER_ID%TYPE,
406 l_start_date_active DATE,
407 L_end_date_active DATE)
408 IS
409 SELECT 'X'
410 FROM jtf_rs_groups_b G1,
411 jtf_rs_groups_b G2,
412 jtf_rs_group_members GM1,
413 jtf_rs_group_members GM2,
414 jtf_rs_group_usages GU1,
415 jtf_rs_group_usages GU2,
416 jtf_rs_role_relations RR1
417 /* commented the below line to improve the performance. We are not using this table in the select statement. */
418 -- jtf_rs_role_relations RR2
419 WHERE GM2.group_member_id = l_member_id
420 AND G1.group_id = GM1.group_id
421 AND G2.group_id = GM2.group_id
422 AND nvl(GM1.delete_flag, 'N') <> 'Y'
423 AND nvl(GM2.delete_flag, 'N') <> 'Y'
424 AND GM1.resource_id = GM2.resource_id
425 AND GM1.group_member_id = RR1.role_resource_id
426 AND RR1.role_resource_type = 'RS_GROUP_MEMBER'
427 AND nvl(RR1.delete_flag, 'N') <> 'Y'
428 AND not (((nvl(l_end_date_active,RR1.start_date_active + 1) < RR1.start_date_active OR
429 l_start_date_active > RR1.end_date_active) AND
430 RR1.end_date_active IS NOT NULL)
431 OR ( nvl(l_end_date_active,RR1.start_date_active + 1) < RR1.start_date_active AND
432 RR1.end_date_active IS NULL ))
433 AND G2.exclusive_flag = 'Y'
434 AND G1.exclusive_flag = 'Y'
435 AND GU1.group_id = G1.group_id
436 AND GU2.group_id = G2.group_id
437 AND GU1.usage = GU2.usage
438 AND G1.group_id <> G2.group_id;
439
440
441 c_exclusive_group_check_rec c_exclusive_group_check_cur%rowtype;
442
443 l_date Date;
444 l_user_id Number;
445 l_login_id Number;
446
447 cursor get_group_cur(l_role_relate_id number)
448 is
449 select mem.group_id
450 from jtf_rs_group_members mem,
451 jtf_rs_role_relations rel
452 where rel.role_relate_id = l_role_relate_id
453 and rel.role_resource_id = mem.group_member_id;
454
455 l_group_id number;
456
457 cursor get_child_cur(l_group_id number)
458 is
459 select count(*) child_cnt
460 from jtf_rs_grp_relations rel
461 connect by related_group_id = prior group_id
462 and nvl(delete_flag, 'N') <> 'Y'
463 AND ((trunc(rel.start_date_active) <= prior rel.start_date_active
464 AND nvl(rel.end_date_active, prior rel.start_date_active) >=
465 trunc(prior rel.start_date_active)) OR
466 (rel.start_date_active > trunc(prior rel.start_date_active)
467 AND trunc(rel.start_date_active) <= nvl(prior rel.end_date_active,
468 rel.start_date_active)))
469 start with related_group_id = l_group_id
470 and nvl(delete_flag, 'N') <> 'Y';
471
472 l_child_cnt number := 0;
473 l_request number;
474
475 cursor conc_prog_cur
476 is
477 select description
478 from fnd_concurrent_programs_vl
479 where concurrent_program_name = 'JTFRSRMG'
480 and application_id = 690;
481
482 l_role_type_valid boolean := false;
483
484 BEGIN
485
486 l_role_resource_type := p_role_resource_type;
487 l_role_resource_id := p_role_resource_id;
488 l_role_id := p_role_id;
489 l_start_date_active := trunc(p_start_date_active);
490 l_end_date_active := trunc(p_end_date_active);
491
492 --dbms_output.put_line ('Debug Message begin 10');
493 --Standard Start of API SAVEPOINT
494 SAVEPOINT ROLE_RELATE_SP;
495
496 x_return_status := fnd_api.g_ret_sts_success;
497
498 --Standard Call to check API compatibility
499 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
500 THEN
501 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
502 END IF;
503
504 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
505 IF FND_API.To_boolean(P_INIT_MSG_LIST)
506 THEN
507 FND_MSG_PUB.Initialize;
508 END IF;
509
510 --dbms_output.put_line ('Debug Message 10');
511
512
513 --GET USER ID AND SYSDATE
514 l_date := sysdate;
515 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
516 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
517
518
519 -- user hook calls for customer
520 -- Customer pre- processing section - mandatory
521 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'CREATE_RESOURCE_ROLE_RELATE', 'B', 'C' ))
522 then
523 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'CREATE_RESOURCE_ROLE_RELATE', 'B', 'C' ))
524 then
525
526
527 JTF_RS_ROLE_RELATE_CUHK.CREATE_RES_ROLE_RELATE_PRE(P_ROLE_RESOURCE_TYPE => p_role_resource_type,
528 P_ROLE_RESOURCE_ID => p_role_resource_id,
529 P_ROLE_ID => p_role_id,
530 P_START_DATE_ACTIVE => p_start_date_active,
531 P_END_DATE_ACTIVE => p_end_date_active,
532 p_data => L_data,
533 p_count => L_count,
534 P_return_code => l_return_code);
535 if ( l_return_code <> FND_API.G_RET_STS_SUCCESS) then
536 fnd_message.set_name ('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
537 FND_MSG_PUB.add;
538 IF l_return_code = FND_API.G_RET_STS_ERROR THEN
539 RAISE FND_API.G_EXC_ERROR;
540 ELSIF l_return_code = FND_API.G_RET_STS_UNEXP_ERROR THEN
541 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
542 END IF;
543 end if;
544 end if;
545 end if;
546
547 /* Vertial industry pre- processing section - mandatory */
548
549 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'CREATE_RESOURCE_ROLE_RELATE', 'B', 'V' ))
550 then
551 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'CREATE_RESOURCE_ROLE_RELATE', 'B', 'V' ))
552 then
553
554 JTF_RS_ROLE_RELATE_VUHK.CREATE_RES_ROLE_RELATE_PRE(P_ROLE_RESOURCE_TYPE => p_role_resource_type,
555 P_ROLE_RESOURCE_ID => p_role_resource_id,
556 P_ROLE_ID => p_role_id,
557 P_START_DATE_ACTIVE => p_start_date_active,
558 P_END_DATE_ACTIVE => p_end_date_active,
559 p_data => L_data,
560 p_count => L_count,
561 P_return_code => l_return_code);
562 if ( l_return_code <> FND_API.G_RET_STS_SUCCESS) then
563 fnd_message.set_name ('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
564 FND_MSG_PUB.add;
565 IF l_return_code = FND_API.G_RET_STS_ERROR THEN
566 RAISE FND_API.G_EXC_ERROR;
567 ELSIF l_return_code = FND_API.G_RET_STS_UNEXP_ERROR THEN
568 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
569 END IF;
570 end if;
571 end if;
572 end if;
573
574 /* Internal pre- processing section - mandatory */
575
576 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'CREATE_RESOURCE_ROLE_RELATE', 'B', 'I' ))
577 then
578 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'CREATE_RESOURCE_ROLE_RELATE', 'B', 'I' ))
579 then
580
581 JTF_RS_ROLE_RELATE_IUHK.CREATE_RES_ROLE_RELATE_PRE(P_ROLE_RESOURCE_TYPE => p_role_resource_type,
582 P_ROLE_RESOURCE_ID => p_role_resource_id,
583 P_ROLE_ID => p_role_id,
584 P_START_DATE_ACTIVE => p_start_date_active,
585 P_END_DATE_ACTIVE => p_end_date_active,
586 p_data => L_data,
587 p_count => L_count,
588 P_return_code => l_return_code);
589 if ( l_return_code <> FND_API.G_RET_STS_SUCCESS) then
590 fnd_message.set_name ('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
591 FND_MSG_PUB.add;
592 IF l_return_code = FND_API.G_RET_STS_ERROR THEN
593 RAISE FND_API.G_EXC_ERROR;
594 ELSIF l_return_code = FND_API.G_RET_STS_UNEXP_ERROR THEN
595 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
596 END IF;
597 end if;
598 end if;
599 end if;
600
601 --dbms_output.put_line ('Debug Message 11');
602
603
604 -- end of user hook call
605
606 --check start date null
607 IF(l_start_date_active is NULL)
608 THEN
609 fnd_message.set_name ('JTF', 'JTF_RS_DATE_RANGE_ERR');
610 FND_MSG_PUB.add;
611 RAISE fnd_api.g_exc_error;
612 END IF;
613
614
615 --check start date less than end date
616 IF(l_start_date_active > l_end_date_active)
617 THEN
618 fnd_message.set_name ('JTF', 'JTF_RS_DATE_RANGE_ERR');
619 FND_MSG_PUB.add;
620 RAISE fnd_api.g_exc_error;
621 END IF;
622
623 IF(l_role_resource_type = 'RS_TEAM' or
624 l_role_resource_type = 'RS_GROUP' or
625 l_role_resource_type = 'RS_INDIVIDUAL')
626 THEN
627 validate_role_type(l_role_id,
628 l_start_date_active,
629 l_end_date_active,
630 l_role_type_valid);
631
632 if (l_role_type_valid = false) then
633 fnd_message.set_name ('JTF', 'JTF_RS_ROLE_TYPE_INACTIVE');
634 FND_MSG_PUB.add;
635 RAISE fnd_api.g_exc_error;
636 end if;
637 END IF;
638
639 --dbms_output.put_line ('Debug Message 12');
640
641
642 --check whether the start date and end date overlaps any existing start date and end date
643 --for the resource type, resource id and role.
644 open check_date_cur(l_role_resource_type,
645 l_role_resource_id,
646 l_role_id);
647 fetch check_date_cur INTO check_date_rec;
648 While(check_date_cur%found)
649 loop
650 IF((l_start_date_active >= check_date_rec.start_date_active)
651 AND ((l_start_date_active <= check_date_rec.end_date_active)
652 OR (check_date_rec.end_date_active IS NULL)))
653 THEN
654 l_date_invalid := TRUE;
655 END IF;
656
657
658 IF((to_date(to_char(nvl(l_end_date_active, fnd_api.g_miss_date), 'DD-MM-RRRR'), 'DD-MM-RRRR')
659 between check_date_rec.start_date_active and
660 to_date(to_char(nvl(check_date_rec.end_date_active, fnd_api.g_miss_date), 'DD-MM-RRRR'), 'DD-MM-RRRR'))
661 OR (l_end_date_active IS NULL AND
662 check_date_rec.end_date_active IS NULL))
663 THEN
664 l_date_invalid := TRUE;
665 END IF;
666
667 -- added this check as a date range outside of the existing ranges was getting entered
668 if(l_start_date_active < check_date_rec.start_date_active
669 and to_date(to_char(nvl(l_end_date_active, fnd_api.g_miss_date), 'DD-MM-RRRR'), 'DD-MM-RRRR') >
670 to_date(to_char(nvl(check_date_rec.end_date_active, fnd_api.g_miss_date), 'DD-MM-RRRR'), 'DD-MM-RRRR'))
671 THEN
672 l_date_invalid := TRUE;
673 END IF;
674
675 IF(l_date_invalid)
676 THEN
677 exit;
678 END IF;
679 fetch check_date_cur INTO check_date_rec;
680 end loop;
681 CLOSE check_date_cur;
682
683 IF(l_date_invalid)
684 THEN
685 fnd_message.set_name ('JTF', 'JTF_RS_OVERLAP_DATE_ERR');
686 FND_MSG_PUB.add;
687 RAISE fnd_api.g_exc_error;
688 END IF;
689 --end of overlapping date range check
690
691
692 --check whether team member is resource or group
693 IF(l_role_resource_type = 'RS_TEAM_MEMBER')
694 THEN
695 OPEN team_mem_cur(l_role_resource_id);
696 FETCH team_mem_cur INTO l_team_resource_type, l_rsc_id;
697 CLOSE team_mem_cur;
698
699 END IF;
700
701 --dbms_output.put_line ('Debug Message 14');
702 --Bug9652619 When Group Member not found then raise an exception.
703 IF(l_role_resource_type = 'RS_GROUP_MEMBER')
704 THEN
705 OPEN grp_mem_cur(l_role_resource_id);
706 FETCH grp_mem_cur INTO l_rsc_id;
707 IF(grp_mem_cur%notfound)
708 THEN
709 fnd_message.set_name('JTF','JTF_RS_INVALID_GRP_MBR_ID');
710 fnd_message.set_token('P_GRP_MBR_ID',l_role_resource_id);
711 fnd_msg_pub.add;
712 RAISE fnd_api.g_exc_error;
713 END IF;
714 CLOSE grp_mem_cur;
715
716 END IF;
717
718
719 --valid role for the resource if being entered as a group member and team member
720 IF((l_role_resource_type = 'RS_GROUP_MEMBER') OR
721 ((l_role_resource_type = 'RS_TEAM_MEMBER') AND
722 (l_team_resource_type = 'INDIVIDUAL')))
723 THEN
724 --if team member is of type resource or it is group member
725 --then check for valid role and st date , end date for the resource
726 --in role relations
727 open res_role_cur(l_rsc_id,
728 l_role_id ,
729 l_start_date_active ,
730 l_end_date_active );
731 fetch res_role_cur INTO res_role_rec;
732 IF(res_role_cur%found)
733 THEN
734 l_role_valid := TRUE;
735
736 ELSE
737 l_role_valid := FALSE;
738 fnd_message.set_name ('JTF', 'JTF_RS_ROLE_OR_DATE_ERR');
739 FND_MSG_PUB.add;
740 RAISE fnd_api.g_exc_error;
741 END IF;
742 close res_role_cur;
743 ELSIF((l_role_resource_type = 'RS_TEAM_MEMBER') AND
744 (l_team_resource_type = 'GROUP'))
745 THEN
746 --if team member is of type group then check for valid role and st date ,
747 --end date for the group in role relations
748
749 --dbms_output.put_line ('Debug Message 15');
750
751 open grp_role_cur(l_rsc_id,
752 l_role_id ,
753 l_start_date_active ,
754 l_end_date_active );
755 fetch grp_role_cur INTO grp_role_rec;
756 IF(grp_role_cur%found)
757 THEN
758 l_role_valid := TRUE;
759
760 ELSE
761 l_role_valid := FALSE;
762 fnd_message.set_name ('JTF', 'JTF_RS_ROLE_OR_DATE_ERR');
763 FND_MSG_PUB.add;
764 RAISE fnd_api.g_exc_error;
765 END IF;
766 close grp_role_cur;
767 END IF;
768 -- end of valid role for the resource if being entered as a group member and team member
769
770 --dbms_output.put_line ('Debug Message 16');
771
772 --Bug13643390
773 --When comparing TRUNCATED dates system is not properly comparing.
774 --Converted into DD-MM-RRRR and comparing
775
776 --if resource type is group member or team member then check against group and team
777 --start date and end date
778 IF(l_role_resource_type = 'RS_TEAM_MEMBER')
779 THEN
780 open team_cur(l_role_resource_id);
781 fetch team_cur INTO team_rec;
782 close team_cur;
783 -- IF((trunc(team_rec.start_date_active) > trunc(l_start_date_active))
784 IF (( to_date(to_char(nvl(team_rec.start_date_active, fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR') > to_date(to_char(nvl(l_start_date_active, fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR'))
785 OR to_date(to_char(nvl(team_rec.end_date_active, fnd_api.g_miss_date), 'DD-MM-RRRR'), 'DD-MM-RRRR') < to_date(to_char(nvl(l_end_date_active, fnd_api.g_miss_date), 'DD-MM-RRRR'), 'DD-MM-RRRR'))
786 THEN
787 fnd_message.set_name ('JTF', 'JTF_RS_TM_GRP_DT_ERR');
788 FND_MSG_PUB.add;
789 RAISE fnd_api.g_exc_error;
790 END IF;
791
792 IF(team_rec.end_date_active is not null AND l_end_date_active is null)
793 THEN
794 fnd_message.set_name ('JTF', 'JTF_RS_TM_GRP_DT_ERR');
795 FND_MSG_PUB.add;
796 RAISE fnd_api.g_exc_error;
797 END IF;
798
799 --dbms_output.put_line ('Debug Message 17');
800
801
802 ELSIF(l_role_resource_type = 'RS_GROUP_MEMBER')
803 THEN
804 --date validation against group dates
805 open group_cur(l_role_resource_id);
806 fetch group_cur INTO group_rec;
807 close group_cur;
808
809 --IF((trunc(group_rec.start_date_active) > trunc(l_start_date_active))
810 IF (( to_date(to_char(nvl(group_rec.start_date_active, fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR') > to_date(to_char(nvl(l_start_date_active, fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR'))
811 OR to_date(to_char(nvl(group_rec.end_date_active, fnd_api.g_miss_date), 'DD-MM-RRRR'), 'DD-MM-RRRR') < to_date(to_char(nvl(l_end_date_active, fnd_api.g_miss_date), 'DD-MM-RRRR'), 'DD-MM-RRRR'))
812 THEN
813 fnd_message.set_name ('JTF', 'JTF_RS_TM_GRP_DT_ERR');
814 FND_MSG_PUB.add;
815 RAISE fnd_api.g_exc_error;
816 END IF;
817
818
819 IF(group_rec.end_date_active is not null AND l_end_date_active is null)
820 THEN
821 fnd_message.set_name ('JTF', 'JTF_RS_TM_GRP_DT_ERR');
822 FND_MSG_PUB.add;
823 RAISE fnd_api.g_exc_error;
824 END IF;
825
826 --exclusive flag validation
827 open c_exclusive_group_check_cur(l_role_resource_id,
828 l_start_date_active,
829 l_end_date_active);
830
831 fetch c_exclusive_group_check_cur into c_exclusive_group_check_rec;
832 IF(c_exclusive_group_check_cur%FOUND)
833 THEN
834 fnd_message.set_name ('JTF', 'JTF_RS_RES_USAGE_ERR');
835 FND_MSG_PUB.add;
836 RAISE fnd_api.g_exc_error;
837 END IF;
838
839 close c_exclusive_group_check_cur;
840
841 ELSIF(l_role_resource_type = 'RS_INDIVIDUAL')
842 --check against res start and end dates
843 THEN
844 open rsc_cur(l_role_resource_id);
845 fetch rsc_cur INTO rsc_rec;
846 close rsc_cur;
847 IF((rsc_rec.start_date_active > l_start_date_active)
848 -- changed by sudarsana 11 feb 2002
849 OR (rsc_rec.end_date_active < to_date(to_char(nvl(l_end_date_active, fnd_api.g_miss_date), 'DD-MM-RRRR'), 'DD-MM-RRRR')))
850 THEN
851 fnd_message.set_name ('JTF', 'JTF_RS_RES_DATE_ERR');
852 FND_MSG_PUB.add;
853 RAISE fnd_api.g_exc_error;
854 END IF;
855 ELSIF(l_role_resource_type = 'RS_GROUP')
856 --check against group start and end dates
857 THEN
858 open group_dt_cur(l_role_resource_id);
859 fetch group_dt_cur INTO group_dt_rec;
860 close group_dt_cur;
861 IF((group_dt_rec.start_date_active > l_start_date_active)
862 -- changed by nsinghai 20 May 2002 to handle null value of l_end_date_active
863 --OR (group_dt_rec.end_date_active < l_end_date_active))
864 OR (to_date(to_char(nvl(group_dt_rec.end_date_active,fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR')
865 < (to_date(to_char(nvl(l_end_date_active,fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR'))))
866 THEN
867 fnd_message.set_name ('JTF', 'JTF_RS_GRP_DT_ERR');
868 FND_MSG_PUB.add;
869 RAISE fnd_api.g_exc_error;
870 END IF;
871 ELSIF(l_role_resource_type = 'RS_TEAM')
872 --check against team start and end dates
873 THEN
874 open team_dt_cur(l_role_resource_id);
875 fetch team_dt_cur INTO team_dt_rec;
876 close team_dt_cur;
877 IF((team_dt_rec.start_date_active > l_start_date_active)
878 -- changed by nsinghai 20 May 2002 to handle null value of l_end_date_active
879 --OR (team_dt_rec.end_date_active < l_end_date_active))
880 OR (to_date(to_char(nvl(team_dt_rec.end_date_active,fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR')
881 < (to_date(to_char(nvl(l_end_date_active,fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR'))))
882 THEN
883 fnd_message.set_name ('JTF', 'JTF_RS_TEAM_DT_ERR');
884 FND_MSG_PUB.add;
885 RAISE fnd_api.g_exc_error;
886 END IF;
887 END IF;
888
889 --dbms_output.put_line ('Debug Message 19');
890
891 --get the primary key sequence value
892 select jtf_rs_role_relations_s.nextval
893 into l_role_relate_id
894 from dual;
895
896
897 --call audit api for insert
898 jtf_rs_role_relate_aud_pvt.insert_role_relate(
899 P_API_VERSION => 1.0,
900 P_INIT_MSG_LIST => p_init_msg_list,
901 P_COMMIT => null,
902 P_ROLE_RELATE_ID => l_role_relate_id,
903 P_ROLE_RESOURCE_TYPE => l_role_resource_type,
904 P_ROLE_RESOURCE_ID => l_role_resource_id,
905 P_ROLE_ID => l_role_id,
906 P_START_DATE_ACTIVE => l_start_date_active,
907 P_END_DATE_ACTIVE => l_end_date_active,
908 P_OBJECT_VERSION_NUMBER => 1,
909 X_RETURN_STATUS => l_return_status,
910 X_MSG_COUNT => l_msg_count,
911 X_MSG_DATA => l_msg_data );
912
913 IF(l_return_status <> fnd_api.g_ret_sts_success)
914 THEN
915 IF L_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
916 RAISE FND_API.G_EXC_ERROR;
917 ELSIF L_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
918 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
919 END IF;
920 END IF;
921
922 --dbms_output.put_line ('Debug Message 20');
923 --dbms_output.put_line ('Before Calling Table Handler : x_return_status=' ||x_return_status);
924 --dbms_output.put_line ('l_role_relate_id' || l_role_relate_id);
925 --dbms_output.put_line ('l_rowid ' || l_rowid);
926
927 --Date Conversion before Insert_Row
928
929 /* l_start_date_active := to_date(to_char(l_start_date_active, 'DD-MM-YYYY') || ' ' || to_char(sysdate, 'HH24:MI:SS'), 'DD-MM-YYYY HH24:MI:SS');
930 */
931 --dbms_output.put_line ('l_start_date_active' || to_char(l_start_date_active, 'DD-MM-YYYY HH24:MI:SS'));
932
933 --call table handler to insert record in role relations
934 jtf_rs_role_relations_pkg.insert_row(X_ROWID => l_rowid,
935 X_ROLE_RELATE_ID => l_role_relate_id,
936 X_ATTRIBUTE9 => p_attribute9,
937 X_ATTRIBUTE10 => p_attribute10,
938 X_ATTRIBUTE11 => p_attribute11,
939 X_ATTRIBUTE12 => p_attribute12,
940 X_ATTRIBUTE13 => p_attribute13,
941 X_ATTRIBUTE14 => p_attribute14,
942 X_ATTRIBUTE15 => p_attribute15,
943 X_ATTRIBUTE_CATEGORY => p_attribute_category,
944 X_ROLE_RESOURCE_TYPE => l_role_resource_type,
945 X_ROLE_RESOURCE_ID => l_role_resource_id,
946 X_ROLE_ID => l_role_id,
947 X_START_DATE_ACTIVE => l_start_date_active,
948 X_END_DATE_ACTIVE => l_end_date_active,
949 X_DELETE_FLAG => 'N',
950 X_ATTRIBUTE2 => p_attribute2,
951 X_ATTRIBUTE3 => p_attribute3,
952 X_ATTRIBUTE4 => p_attribute4,
953 X_ATTRIBUTE5 => p_attribute5,
954 X_ATTRIBUTE6 => p_attribute6,
955 X_ATTRIBUTE7 => p_attribute7,
956 X_ATTRIBUTE8 => p_attribute8,
957 X_ATTRIBUTE1 => p_attribute1,
958 X_CREATION_DATE => l_date,
959 X_CREATED_BY => l_user_id,
960 X_LAST_UPDATE_DATE => l_date,
961 X_LAST_UPDATED_BY => l_user_id,
962 X_LAST_UPDATE_LOGIN => l_login_id ) ;
963
964 --dbms_output.put_line (' After Calling Table Handler : x_return_status=' ||x_return_status);
965
966 --dbms_output.put_line ('Debug Message 21');
967
968 IF(l_role_resource_type = 'RS_GROUP_MEMBER')
969 THEN
970 -- get the group id of the member
971 open get_group_cur(l_role_relate_id);
972 fetch get_group_cur into l_group_id;
973 close get_group_cur;
974
975 --get no of children for the group
976 BEGIN
977 open get_child_cur(l_group_id);
978 fetch get_child_cur into l_child_cnt;
979 close get_child_cur;
980 EXCEPTION
981 WHEN OTHERS THEN
982 l_child_cnt := 101; -- use concurrent program
983 END;
984
985 if (nvl(l_child_cnt, 0) > 100)
986 then
987 begin
988 insert into jtf_rs_chgd_role_relations
989 (role_relate_id,
990 role_resource_type,
991 role_resource_id,
992 role_id,
993 start_date_active,
994 end_date_active,
995 delete_flag,
996 operation_flag,
997 created_by,
998 creation_date,
999 last_updated_by,
1000 last_update_date,
1001 last_update_login)
1002 values(
1003 l_role_relate_id,
1004 l_role_resource_type,
1005 l_role_resource_id,
1006 l_role_id,
1007 l_start_date_active,
1008 l_end_date_active,
1009 'N',
1010 'I',
1011 l_user_id,
1012 l_date,
1013 l_user_id,
1014 l_date,
1015 l_login_id);
1016
1017 exception
1018 when others then
1019 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1020 fnd_message.set_token('P_SQLCODE',SQLCODE);
1021 fnd_message.set_token('P_SQLERRM',SQLERRM);
1022 fnd_message.set_token('P_API_NAME', l_api_name);
1023 FND_MSG_PUB.add;
1024 RAISE fnd_api.g_exc_unexpected_error;
1025
1026
1027 end;
1028
1029
1030 --call concurrent program
1031
1032 begin
1033 l_request := fnd_request.submit_request(APPLICATION => 'JTF',
1034 PROGRAM => 'JTFRSRMG');
1035
1036 open conc_prog_cur;
1037 fetch conc_prog_cur into g_name;
1038 close conc_prog_cur;
1039
1040 fnd_message.set_name ('JTF', 'JTF_RS_CONC_START');
1041 fnd_message.set_token('P_NAME',g_name);
1042 fnd_message.set_token('P_ID',l_request);
1043 FND_MSG_PUB.add;
1044
1045 exception when others then
1046 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1047 fnd_message.set_token('P_SQLCODE',SQLCODE);
1048 fnd_message.set_token('P_SQLERRM',SQLERRM);
1049 fnd_message.set_token('P_API_NAME', l_api_name);
1050 FND_MSG_PUB.add;
1051 RAISE fnd_api.g_exc_unexpected_error;
1052 end;
1053
1054 else
1055 --call to insert records in jtf_rs_rep_managers
1056 JTF_RS_REP_MGR_DENORM_PVT.INSERT_REP_MANAGER
1057 ( P_API_VERSION => 1.0,
1058 P_INIT_MSG_LIST => p_init_msg_list,
1059 P_COMMIT => null,
1060 P_ROLE_RELATE_ID => l_role_relate_id,
1061 X_RETURN_STATUS => l_return_status,
1062 X_MSG_COUNT => l_msg_count,
1063 X_MSG_DATA => l_msg_data);
1064
1065 IF(l_return_status <> fnd_api.g_ret_sts_success)
1066 THEN
1067 IF L_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1068 RAISE FND_API.G_EXC_ERROR;
1069 ELSIF L_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1070 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1071 END IF;
1072 END IF;
1073 END IF; -- end of count check
1074 END IF;
1075
1076 --dbms_output.put_line ('Debug Message 22');
1077 -- user hook calls for customer
1078 -- Customer post- processing section - mandatory
1079 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'CREATE_RESOURCE_ROLE_RELATE', 'A', 'C' ))
1080 then
1081 JTF_RS_ROLE_RELATE_CUHK.CREATE_RES_ROLE_RELATE_POST(P_ROLE_RELATE_ID => l_role_relate_id,
1082 P_ROLE_RESOURCE_TYPE => p_role_resource_type,
1083 P_ROLE_RESOURCE_ID => p_role_resource_id,
1084 P_ROLE_ID => p_role_id,
1085 P_START_DATE_ACTIVE => p_start_date_active,
1086 P_END_DATE_ACTIVE => p_end_date_active,
1087 p_data => L_data,
1088 p_count => L_count,
1089 P_return_code => l_return_code);
1090 if ( l_return_code <> FND_API.G_RET_STS_SUCCESS) then
1091 fnd_message.set_name ('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
1092 FND_MSG_PUB.add;
1093 IF l_return_code = FND_API.G_RET_STS_ERROR THEN
1094 RAISE FND_API.G_EXC_ERROR;
1095 ELSIF l_return_code = FND_API.G_RET_STS_UNEXP_ERROR THEN
1096 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1097 END IF;
1098 end if;
1099 end if;
1100
1101
1102 --dbms_output.put_line ('Debug Message 23');
1103
1104 /* Verticle industry post- processing section - mandatory */
1105
1106 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'CREATE_RESOURCE_ROLE_RELATE', 'A', 'V' ))
1107 then
1108 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'CREATE_RESOURCE_ROLE_RELATE', 'A', 'V' ))
1109 then
1110 JTF_RS_ROLE_RELATE_VUHK.CREATE_RES_ROLE_RELATE_POST(p_role_relate_id => l_role_relate_id,
1111 P_ROLE_RESOURCE_TYPE => p_role_resource_type,
1112 P_ROLE_RESOURCE_ID => p_role_resource_id,
1113 P_ROLE_ID => p_role_id,
1114 P_START_DATE_ACTIVE => p_start_date_active,
1115 P_END_DATE_ACTIVE => p_end_date_active,
1116 p_data => L_data,
1117 p_count => L_count,
1118 P_return_code => l_return_code);
1119 if ( l_return_code <> FND_API.G_RET_STS_SUCCESS) then
1120 fnd_message.set_name ('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
1121 FND_MSG_PUB.add;
1122 IF l_return_code = FND_API.G_RET_STS_ERROR THEN
1123 RAISE FND_API.G_EXC_ERROR;
1124 ELSIF l_return_code = FND_API.G_RET_STS_UNEXP_ERROR THEN
1125 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1126 END IF;
1127 end if;
1128 end if;
1129 end if;
1130
1131 /* Internal post- processing section - mandatory */
1132
1133 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'CREATE_RESOURCE_ROLE_RELATE', 'A', 'I' ))
1134 then
1135 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'CREATE_RESOURCE_ROLE_RELATE', 'A', 'I' ))
1136 then
1137 JTF_RS_ROLE_RELATE_IUHK.CREATE_RES_ROLE_RELATE_POST(p_role_relate_id => l_role_relate_id,
1138 P_ROLE_RESOURCE_TYPE => p_role_resource_type,
1139 P_ROLE_RESOURCE_ID => p_role_resource_id,
1140 P_ROLE_ID => p_role_id,
1141 P_START_DATE_ACTIVE => p_start_date_active,
1142 P_END_DATE_ACTIVE => p_end_date_active,
1143 p_data => L_data,
1144 p_count => L_count,
1145 P_return_code => l_return_code);
1146 if ( l_return_code <> FND_API.G_RET_STS_SUCCESS) then
1147 fnd_message.set_name ('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
1148 FND_MSG_PUB.add;
1149 IF l_return_code = FND_API.G_RET_STS_ERROR THEN
1150 RAISE FND_API.G_EXC_ERROR;
1151 ELSIF l_return_code = FND_API.G_RET_STS_UNEXP_ERROR THEN
1152 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1153 END IF;
1154 end if;
1155 end if;
1156 end if;
1157
1158 -- end of user hook call
1159
1160 x_role_relate_id := l_role_relate_id;
1161
1162 IF jtf_resource_utl.ok_to_execute(
1163 'JTF_RS_ROLE_RELATE_PVT',
1164 'CREATE_RESOURCE_ROLE_RELATE',
1165 'M',
1166 'M')
1167 THEN
1168 IF jtf_usr_hks.ok_to_execute(
1169 'JTF_RS_ROLE_RELATE_PVT',
1170 'CREATE_RESOURCE_ROLE_RELATE',
1171 'M',
1172 'M')
1173 THEN
1174
1175 IF (jtf_rs_role_relate_cuhk.ok_to_generate_msg(
1176 p_role_relate_id => l_role_relate_id,
1177 x_return_status => x_return_status) )
1178 THEN
1179
1180 /* Get the bind data id for the Business Object Instance */
1181
1182 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
1183
1184
1185 /* Set bind values for the bind variables in the Business Object
1186 SQL */
1187
1188 jtf_usr_hks.load_bind_data(l_bind_data_id, 'role_relate_id',
1189 l_role_relate_id, 'S', 'N');
1190
1191
1192 /* Call the message generation API */
1193
1194 jtf_usr_hks.generate_message(
1195 p_prod_code => 'JTF',
1196 p_bus_obj_code => 'RS_RRL',
1197 p_action_code => 'I', /* I/U/D */
1198 p_bind_data_id => l_bind_data_id,
1199 x_return_code => x_return_status);
1200
1201
1202 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1203 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1204 RAISE FND_API.G_EXC_ERROR;
1205 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1206 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1207 END IF;
1208
1209 END IF;
1210
1211 END IF;
1212
1213 END IF;
1214 END IF;
1215
1216 --standard commit
1217 IF fnd_api.to_boolean (p_commit)
1218 THEN
1219 COMMIT WORK;
1220 END IF;
1221
1222
1223 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1224
1225 /* Calling publish API to raise create resource role relation event. */
1226 /* added by baianand on 04/09/2003 */
1227
1228 begin
1229 jtf_rs_wf_events_pub.create_resource_role_relate
1230 (p_api_version => 1.0
1231 ,p_init_msg_list => fnd_api.g_false
1232 ,p_commit => fnd_api.g_false
1233 ,p_role_relate_id => l_role_relate_id
1234 ,p_role_resource_type => l_role_resource_type
1235 ,p_role_resource_id => l_role_resource_id
1236 ,p_role_id => l_role_id
1237 ,p_start_date_active => l_start_date_active
1238 ,p_end_date_active => l_end_date_active
1239 ,x_return_status => l_return_status
1240 ,x_msg_count => l_msg_count
1241 ,x_msg_data => l_msg_data);
1242
1243 EXCEPTION when others then
1244 null;
1245 end;
1246
1247 /* End of publish API call */
1248
1249 EXCEPTION
1250 WHEN fnd_api.g_exc_error THEN
1251 ROLLBACK TO ROLE_RELATE_SP;
1252 x_return_status := fnd_api.g_ret_sts_error;
1253 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1254 p_data => x_msg_data);
1255 WHEN fnd_api.g_exc_unexpected_error THEN
1256 ROLLBACK TO ROLE_RELATE_SP;
1257 x_return_status := fnd_api.g_ret_sts_unexp_error;
1258 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1259 p_data => x_msg_data);
1260 WHEN OTHERS THEN
1261 ROLLBACK TO ROLE_RELATE_SP;
1262 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1263 fnd_message.set_token('P_SQLCODE',SQLCODE);
1264 fnd_message.set_token('P_SQLERRM',SQLERRM);
1265 fnd_message.set_token('P_API_NAME', l_api_name);
1266 FND_MSG_PUB.add;
1267 x_return_status := fnd_api.g_ret_sts_unexp_error;
1268 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1269 p_data => x_msg_data);
1270
1271 END create_resource_role_relate;
1272
1273
1274
1275 /* Procedure to update the resource roles
1276 based on input values passed by calling routines. */
1277
1278 PROCEDURE update_resource_role_relate
1279 (P_API_VERSION IN NUMBER,
1280 P_INIT_MSG_LIST IN VARCHAR2,
1281 P_COMMIT IN VARCHAR2,
1282 P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
1283 P_START_DATE_ACTIVE IN JTF_RS_ROLE_RELATIONS.START_DATE_ACTIVE%TYPE,
1284 P_END_DATE_ACTIVE IN JTF_RS_ROLE_RELATIONS.END_DATE_ACTIVE%TYPE,
1285 P_OBJECT_VERSION_NUM IN OUT NOCOPY JTF_RS_ROLE_RELATIONS.OBJECT_VERSION_NUMBER%TYPE,
1286 P_ATTRIBUTE1 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE1%TYPE,
1287 P_ATTRIBUTE2 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE2%TYPE,
1288 P_ATTRIBUTE3 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE3%TYPE,
1289 P_ATTRIBUTE4 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE4%TYPE,
1290 P_ATTRIBUTE5 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE5%TYPE,
1291 P_ATTRIBUTE6 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE6%TYPE,
1292 P_ATTRIBUTE7 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE7%TYPE,
1293 P_ATTRIBUTE8 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE8%TYPE,
1294 P_ATTRIBUTE9 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE9%TYPE,
1295 P_ATTRIBUTE10 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE10%TYPE,
1296 P_ATTRIBUTE11 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE11%TYPE,
1297 P_ATTRIBUTE12 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE12%TYPE,
1298 P_ATTRIBUTE13 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE13%TYPE,
1299 P_ATTRIBUTE14 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE14%TYPE,
1300 P_ATTRIBUTE15 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE15%TYPE,
1301 P_ATTRIBUTE_CATEGORY IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE_CATEGORY%TYPE,
1302 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1303 X_MSG_COUNT OUT NOCOPY NUMBER,
1304 X_MSG_DATA OUT NOCOPY VARCHAR2
1305 )IS
1306 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE_ROLE_RELATE';
1307 l_api_version CONSTANT NUMBER :=1.0;
1308 l_bind_data_id number;
1309
1310 l_return_code VARCHAR2(100);
1311 l_count NUMBER;
1312 l_data VARCHAR2(200);
1313
1314
1315 L_ATTRIBUTE1 JTF_RS_ROLE_RELATIONS.ATTRIBUTE1%TYPE;
1316 L_ATTRIBUTE2 JTF_RS_ROLE_RELATIONS.ATTRIBUTE2%TYPE;
1317 L_ATTRIBUTE3 JTF_RS_ROLE_RELATIONS.ATTRIBUTE3%TYPE;
1318 L_ATTRIBUTE4 JTF_RS_ROLE_RELATIONS.ATTRIBUTE4%TYPE;
1319 L_ATTRIBUTE5 JTF_RS_ROLE_RELATIONS.ATTRIBUTE5%TYPE;
1320 L_ATTRIBUTE6 JTF_RS_ROLE_RELATIONS.ATTRIBUTE6%TYPE;
1321 L_ATTRIBUTE7 JTF_RS_ROLE_RELATIONS.ATTRIBUTE7%TYPE;
1322 L_ATTRIBUTE8 JTF_RS_ROLE_RELATIONS.ATTRIBUTE8%TYPE;
1323 L_ATTRIBUTE9 JTF_RS_ROLE_RELATIONS.ATTRIBUTE9%TYPE;
1324 L_ATTRIBUTE10 JTF_RS_ROLE_RELATIONS.ATTRIBUTE10%TYPE;
1325 L_ATTRIBUTE11 JTF_RS_ROLE_RELATIONS.ATTRIBUTE11%TYPE;
1326 L_ATTRIBUTE12 JTF_RS_ROLE_RELATIONS.ATTRIBUTE12%TYPE;
1327 L_ATTRIBUTE13 JTF_RS_ROLE_RELATIONS.ATTRIBUTE13%TYPE;
1328 L_ATTRIBUTE14 JTF_RS_ROLE_RELATIONS.ATTRIBUTE14%TYPE;
1329 L_ATTRIBUTE15 JTF_RS_ROLE_RELATIONS.ATTRIBUTE15%TYPE;
1330 L_ATTRIBUTE_CATEGORY JTF_RS_ROLE_RELATIONS.ATTRIBUTE_CATEGORY%TYPE;
1331
1332
1333 CURSOR role_relate_cur(ll_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)
1334 IS
1335 SELECT role_resource_type,
1336 role_resource_id,
1337 role_id,
1338 start_date_active,
1339 end_date_active,
1340 object_version_number,
1341 delete_flag,
1342 attribute1,
1343 attribute2,
1344 attribute3,
1345 attribute4,
1346 attribute5,
1347 attribute6,
1348 attribute7,
1349 attribute8,
1350 attribute9,
1351 attribute10,
1352 attribute11,
1353 attribute12,
1354 attribute13,
1355 attribute14,
1356 attribute15,
1357 attribute_category
1358 FROM jtf_rs_role_relations
1359 WHERE role_relate_id = ll_role_relate_id
1360 AND nvl(delete_flag, '0') <> 'Y';
1361
1362 role_relate_rec role_relate_cur%rowtype;
1363
1364 l_role_resource_type JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_TYPE%TYPE ;
1365 l_role_resource_id JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_ID%TYPE ;
1366 l_role_id JTF_RS_ROLE_RELATIONS.ROLE_ID%TYPE ;
1367 -- added trunc on 12th feb 2002
1368 /* Moved the initial assignment of below variables to inside begin */
1369 l_start_date_active JTF_RS_ROLE_RELATIONS.START_DATE_ACTIVE%TYPE;
1370 l_end_date_active JTF_RS_ROLE_RELATIONS.START_DATE_ACTIVE%TYPE;
1371 l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE;
1372 l_object_version_number JTF_RS_ROLE_RELATIONS.OBJECT_VERSION_NUMBER%TYPE;
1373 l_delete_flag JTF_RS_ROLE_RELATIONS.DELETE_FLAG%TYPE ;
1374
1375 l_return_status VARCHAR2(200);
1376 l_msg_count NUMBER;
1377 l_msg_data VARCHAR2(200);
1378 l_rowid VARCHAR2(200);
1379
1380 l_date_invalid boolean := FALSE;
1381 l_role_valid boolean := FALSE;
1382 l_date Date;
1383 l_user_id Number;
1384 l_login_id Number;
1385 l_group_id number;
1386 l_child_cnt number := 0;
1387 l_request number;
1388
1389 l_valid boolean := TRUE;
1390
1391 CURSOR team_mem_cur(l_team_member_id JTF_RS_TEAM_MEMBERS.TEAM_MEMBER_ID%TYPE)
1392 IS
1393 SELECT resource_type,
1394 team_resource_id
1395 FROM jtf_rs_team_members
1396 WHERE team_member_id = l_team_member_id
1397 AND nvl(delete_flag, '0') <> 'Y';
1398
1399
1400 CURSOR grp_mem_cur(l_grp_member_id JTF_RS_GROUP_MEMBERS.GROUP_MEMBER_ID%TYPE)
1401 IS
1402 SELECT resource_id
1403 FROM jtf_rs_group_members
1404 WHERE group_member_id = l_grp_member_id
1405 AND nvl(delete_flag, '0') <> 'Y';
1406
1407 l_rsc_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE;
1408 l_team_resource_type JTF_RS_TEAM_MEMBERS.RESOURCE_TYPE%TYPE;
1409
1410 --changed the date comparison in the cursor 07/07/00
1411 CURSOR res_role_cur(ll_role_resource_id JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_ID%TYPE,
1412 ll_role_id JTF_RS_ROLES_B.ROLE_ID%TYPE,
1413 ll_start_date_active JTF_RS_ROLE_RELATIONS.START_DATE_ACTIVE%TYPE,
1414 ll_end_date_active JTF_RS_ROLE_RELATIONS.END_DATE_ACTIVE%TYPE )
1415 IS
1416 SELECT 'X'
1417 FROM jtf_rs_role_relations
1418 WHERE role_resource_type = 'RS_INDIVIDUAL'
1419 AND role_resource_id = ll_role_resource_id
1420 AND role_id = ll_role_id
1421 AND to_date(to_char(start_date_active , 'dd-MM-yyyy'),'dd-MM-yyyy') <=
1422 to_date(to_char(ll_start_date_active, 'dd-MM-yyyy'),'dd-MM-yyyy')
1423 AND ( to_date(to_char(end_date_active, 'dd-MM-yyyy'),'dd-MM-yyyy')
1424 >= to_date(to_char(ll_end_date_active, 'dd-MM-yyyy'),'dd-MM-yyyy')
1425 OR ( end_date_active IS NULL AND ll_end_date_active IS NULL)
1426 OR (end_date_active IS NULL AND ll_end_date_active IS NOT NULL))
1427 AND nvl(delete_flag, '0') <> 'Y';
1428
1429 res_role_rec res_role_cur%rowtype;
1430
1431 CURSOR grp_role_cur(ll_role_resource_id JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_ID%TYPE,
1432 ll_role_id JTF_RS_ROLES_B.ROLE_ID%TYPE,
1433 ll_start_date_active JTF_RS_ROLE_RELATIONS.START_DATE_ACTIVE%TYPE,
1434 ll_end_date_active JTF_RS_ROLE_RELATIONS.END_DATE_ACTIVE%TYPE )
1435 IS
1436 SELECT 'X'
1437 FROM jtf_rs_role_relations
1438 WHERE role_resource_type = 'RS_GROUP'
1439 AND role_resource_id = ll_role_resource_id
1440 AND role_id = ll_role_id
1441 AND start_date_active <= ll_start_date_active
1442 AND ( end_date_active >= ll_end_date_active
1443 OR ( end_date_active IS NULL AND ll_end_date_active IS NULL)
1444 OR (end_date_active IS NULL AND ll_end_date_active IS NOT NULL))
1445 AND nvl(delete_flag, '0') <> 'Y';
1446
1447 grp_role_rec grp_role_cur%rowtype;
1448
1449
1450 CURSOR check_date_cur(ll_role_resource_type JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_TYPE%TYPE ,
1451 ll_role_resource_id JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_ID%TYPE,
1452 ll_role_id JTF_RS_ROLE_RELATIONS.ROLE_ID%TYPE,
1453 ll_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)
1454 IS
1455 SELECT start_date_active,
1456 end_date_active
1457 FROM jtf_rs_role_relations
1458 WHERE role_relate_id <> ll_role_relate_id
1459 AND role_resource_type = ll_role_resource_type
1460 AND role_resource_id = ll_role_resource_id
1461 AND role_id = ll_role_id
1462 AND nvl(delete_flag, 'N') <> 'Y';
1463
1464
1465 check_date_rec check_date_cur%rowtype;
1466
1467 CURSOR group_cur(ll_member_id JTF_RS_GROUP_MEMBERS.GROUP_MEMBER_ID%TYPE)
1468 IS
1469 SELECT grp.start_date_active,
1470 grp.end_date_active
1471 FROM jtf_rs_groups_b grp,
1472 jtf_rs_group_members mem
1473 WHERE mem.group_member_id = ll_member_id
1474 AND mem.group_id = grp.group_id
1475 AND nvl(mem.delete_flag, '0') <> 'Y';
1476
1477 group_rec group_cur%rowtype;
1478
1479
1480 CURSOR team_cur(ll_member_id JTF_RS_TEAM_MEMBERS.TEAM_MEMBER_ID%TYPE)
1481 IS
1482 SELECT tm.start_date_active,
1483 tm.end_date_active
1484 FROM jtf_rs_teams_b tm,
1485 jtf_rs_team_members mem
1486 WHERE mem.team_member_id = ll_member_id
1487 AND mem.team_id = tm.team_id
1488 AND nvl(mem.delete_flag, '0') <> 'Y';
1489
1490 team_rec team_cur%rowtype;
1491
1492 CURSOR rsc_cur(ll_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE)
1493 IS
1494 SELECT rsc.start_date_active,
1495 rsc.end_date_active
1496 FROM jtf_rs_resource_extns rsc
1497 WHERE rsc.resource_id = ll_resource_id;
1498
1499 rsc_rec rsc_cur%rowtype;
1500
1501
1502 CURSOR group_dt_cur(l_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE)
1503 IS
1504 SELECT grp.start_date_active,
1505 grp.end_date_active
1506 FROM jtf_rs_groups_b grp
1507 WHERE grp.group_id = l_group_id;
1508
1509 group_dt_rec group_dt_cur%rowtype;
1510
1511 CURSOR team_dt_cur(l_team_id JTF_RS_TEAMS_B.TEAM_ID%TYPE)
1512 IS
1513 SELECT tm.start_date_active,
1514 tm.end_date_active
1515 FROM jtf_rs_teams_b tm
1516 WHERE tm.team_id = l_team_id;
1517
1518 team_dt_rec team_dt_cur%rowtype;
1519
1520 --exclusive flag check cursor
1521 CURSOR c_exclusive_group_check_cur(l_member_id JTF_RS_GROUP_MEMBERS.GROUP_MEMBER_ID%TYPE,
1522 l_start_date_active DATE,
1523 L_end_date_active DATE)
1524 IS
1525 SELECT 'X'
1526 FROM jtf_rs_groups_b G1,
1527 jtf_rs_groups_b G2,
1528 jtf_rs_group_members GM1,
1529 jtf_rs_group_members GM2,
1530 jtf_rs_group_usages GU1,
1531 jtf_rs_group_usages GU2,
1532 jtf_rs_role_relations RR1
1533 /* commented the below line to improve the performance. We are not using this table in the select statement. */
1534 -- jtf_rs_role_relations RR2
1535 WHERE GM2.group_member_id = l_member_id
1536 AND G1.group_id = GM1.group_id
1537 AND G2.group_id = GM2.group_id
1538 AND nvl(GM1.delete_flag, 'N') <> 'Y'
1539 AND nvl(GM2.delete_flag, 'N') <> 'Y'
1540 AND GM1.resource_id = GM2.resource_id
1541 AND GM1.group_member_id = RR1.role_resource_id
1542 AND RR1.role_resource_type = 'RS_GROUP_MEMBER'
1543 AND nvl(RR1.delete_flag, 'N') <> 'Y'
1544 AND not (((nvl(l_end_date_active,RR1.start_date_active + 1) < RR1.start_date_active OR
1545 l_start_date_active > RR1.end_date_active) AND
1546 RR1.end_date_active IS NOT NULL)
1547 OR ( nvl(l_end_date_active,RR1.start_date_active + 1) < RR1.start_date_active AND
1548 RR1.end_date_active IS NULL ))
1549 AND G2.exclusive_flag = 'Y'
1550 AND G1.exclusive_flag = 'Y'
1551 AND GU1.group_id = G1.group_id
1552 AND GU2.group_id = G2.group_id
1553 AND GU1.usage = GU2.usage
1554 AND G1.group_id <> G2.group_id;
1555
1556
1557 c_exclusive_group_check_rec c_exclusive_group_check_cur%rowtype;
1558
1559 /*changed + 1 logic */
1560 --cursor to check for team member dates for resource
1561 CURSOR res_team_cur(l_resource_id JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_ID%TYPE,
1562 l_start_date_active DATE,
1563 L_end_date_active DATE ,
1564 l_role_id JTF_RS_ROLES_B.ROLE_ID%TYPE )
1565 IS
1566 SELECT 'X'
1567 FROM jtf_rs_team_members mem,
1568 jtf_rs_role_relations rlt
1569 WHERE mem.team_resource_id = l_resource_id
1570 AND mem.resource_type = 'INDIVIDUAL'
1571 AND nvl(mem.delete_flag, 'N') <> 'Y'
1572 AND mem.team_member_id = rlt.role_resource_id
1573 AND rlt.role_resource_type = 'RS_TEAM_MEMBER'
1574 AND nvl(rlt.delete_flag, 'N') <> 'Y'
1575 AND ((l_start_date_active between rlt.start_date_active + 1
1576 and nvl(rlt.end_date_active - 1, l_start_date_active +1))
1577 OR (l_end_date_active between rlt.start_date_active + 1
1578 and nvl(rlt.end_date_active - 1, l_end_date_active - 1)))
1579 AND rlt.role_id = l_role_id;
1580
1581 res_team_rec res_team_cur%rowtype;
1582
1583 /*changed + 1 logic */
1584 --cursor to check for group member dates for resource
1585 CURSOR res_group_cur(l_resource_id JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_ID%TYPE,
1586 l_start_date_active DATE,
1587 L_end_date_active DATE ,
1588 l_role_id JTF_RS_ROLES_B.ROLE_ID%TYPE)
1589 IS
1590 SELECT rlt.role_relate_id
1591 FROM jtf_rs_group_members mem,
1592 jtf_rs_role_relations rlt
1593 WHERE mem.resource_id = l_resource_id
1594 AND nvl(mem.delete_flag, 'N') <> 'Y'
1595 AND mem.group_member_id = rlt.role_resource_id
1596 AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
1597 AND nvl(rlt.delete_flag, 'N') <> 'Y'
1598 AND ((l_start_date_active between rlt.start_date_active+1
1599 and nvl(rlt.end_date_active - 1, l_start_date_active +1))
1600 OR (l_end_date_active between rlt.start_date_active+1
1601 and nvl(rlt.end_date_active - 1, l_end_date_active - 1)))
1602 AND rlt.role_id = l_role_id;
1603
1604 res_group_rec res_group_cur%rowtype;
1605
1606
1607
1608 cursor get_group_cur(l_role_relate_id number)
1609 is
1610 select mem.group_id
1611 from jtf_rs_group_members mem,
1612 jtf_rs_role_relations rel
1613 where rel.role_relate_id = l_role_relate_id
1614 and rel.role_resource_id = mem.group_member_id;
1615
1616
1617 cursor get_child_cur(l_group_id number)
1618 is
1619 select count(*) child_cnt
1620 from jtf_rs_grp_relations rel
1621 connect by related_group_id = prior group_id
1622 and nvl(delete_flag, 'N') <> 'Y'
1623 AND ((trunc(rel.start_date_active) <= prior rel.start_date_active
1624 AND nvl(rel.end_date_active, prior rel.start_date_active) >=
1625 trunc(prior rel.start_date_active)) OR
1626 (rel.start_date_active > trunc(prior rel.start_date_active)
1627 AND trunc(rel.start_date_active) <= nvl(prior rel.end_date_active,
1628 rel.start_date_active)))
1629 start with related_group_id = l_group_id
1630 and nvl(delete_flag, 'N') <> 'Y';
1631
1632
1633 cursor conc_prog_cur
1634 is
1635 select description
1636 from fnd_concurrent_programs_vl
1637 where concurrent_program_name = 'JTFRSRMG'
1638 and application_id = 690;
1639
1640 l_role_type_valid boolean := false;
1641
1642 BEGIN
1643
1644 l_start_date_active := trunc(p_start_date_active);
1645 l_end_date_active := trunc(p_end_date_active);
1646 l_role_relate_id := p_role_relate_id;
1647 l_object_version_number := p_object_version_num;
1648
1649 --Standard Start of API SAVEPOINT
1650 SAVEPOINT ROLE_RELATE_SP;
1651
1652 x_return_status := fnd_api.g_ret_sts_success;
1653
1654 --Standard Call to check API compatibility
1655 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
1656 THEN
1657 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1658 END IF;
1659
1660 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
1661 IF FND_API.To_boolean(P_INIT_MSG_LIST)
1662 THEN
1663 FND_MSG_PUB.Initialize;
1664 END IF;
1665
1666
1667 --GET USER ID AND SYSDATE
1668 l_date := sysdate;
1669 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
1670 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
1671
1672 -- user hook calls for customer
1673 -- Customer pre- processing section - mandatory
1674 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'UPDATE_RESOURCE_ROLE_RELATE', 'B', 'C' ))
1675 then
1676 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'UPDATE_RESOURCE_ROLE_RELATE', 'B', 'C' ))
1677 then
1678 JTF_RS_ROLE_RELATE_CUHK.UPDATE_RES_ROLE_RELATE_PRE(P_ROLE_RELATE_ID => p_role_relate_id,
1679 P_START_DATE_ACTIVE => P_start_date_active,
1680 P_END_DATE_ACTIVE => P_end_date_active,
1681 P_OBJECT_VERSION_NUM => P_OBJECT_VERSION_NUM,
1682 p_data => L_data,
1683 p_count => L_count,
1684 P_return_code => l_return_code);
1685 if ( l_return_code <> FND_API.G_RET_STS_SUCCESS) then
1686 fnd_message.set_name ('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
1687 FND_MSG_PUB.add;
1688 IF l_return_code = FND_API.G_RET_STS_ERROR THEN
1689 RAISE FND_API.G_EXC_ERROR;
1690 ELSIF l_return_code = FND_API.G_RET_STS_UNEXP_ERROR THEN
1691 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1692 END IF;
1693
1694 end if;
1695 end if;
1696 end if;
1697
1698 /* Vertical industry pre- processing section - mandatory */
1699
1700 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'UPDATE_RESOURCE_ROLE_RELATE', 'B', 'V' ))
1701 then
1702 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'UPDATE_RESOURCE_ROLE_RELATE', 'B', 'V' ))
1703 then
1704
1705 JTF_RS_ROLE_RELATE_VUHK.UPDATE_RES_ROLE_RELATE_PRE(P_ROLE_RELATE_ID => p_role_relate_id,
1706 P_START_DATE_ACTIVE => P_start_date_active,
1707 P_END_DATE_ACTIVE => P_end_date_active,
1708 P_OBJECT_VERSION_NUM => P_OBJECT_VERSION_NUM,
1709 p_data => L_data,
1710 p_count => L_count,
1711 P_return_code => l_return_code);
1712 if ( l_return_code <> FND_API.G_RET_STS_SUCCESS) then
1713 fnd_message.set_name ('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
1714 FND_MSG_PUB.add;
1715 IF l_return_code = FND_API.G_RET_STS_ERROR THEN
1716 RAISE FND_API.G_EXC_ERROR;
1717 ELSIF l_return_code = FND_API.G_RET_STS_UNEXP_ERROR THEN
1718 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1719 END IF;
1720
1721 end if;
1722 end if;
1723 end if;
1724
1725 /* Internal pre- processing section - mandatory */
1726
1727 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'UPDATE_RESOURCE_ROLE_RELATE', 'B', 'I' ))
1728 then
1729 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'UPDATE_RESOURCE_ROLE_RELATE', 'B', 'I' ))
1730 then
1731
1732 JTF_RS_ROLE_RELATE_IUHK.UPDATE_RES_ROLE_RELATE_PRE(P_ROLE_RELATE_ID => p_role_relate_id,
1733 P_START_DATE_ACTIVE => P_start_date_active,
1734 P_END_DATE_ACTIVE => P_end_date_active,
1735 P_OBJECT_VERSION_NUM => P_OBJECT_VERSION_NUM,
1736 p_data => L_data,
1737 p_count => L_count,
1738 P_return_code => l_return_code);
1739 if ( l_return_code <> FND_API.G_RET_STS_SUCCESS) then
1740 fnd_message.set_name ('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
1741 FND_MSG_PUB.add;
1742 IF l_return_code = FND_API.G_RET_STS_ERROR THEN
1743 RAISE FND_API.G_EXC_ERROR;
1744 ELSIF l_return_code = FND_API.G_RET_STS_UNEXP_ERROR THEN
1745 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1746 END IF;
1747 end if;
1748 end if;
1749 end if;
1750
1751 -- end of user hook call
1752
1753
1754
1755
1756
1757
1758 --fetch the details for the role relate id
1759 open role_relate_cur(l_role_relate_id);
1760 fetch role_relate_cur into role_relate_rec;
1761 close role_relate_cur;
1762
1763 l_role_resource_type := role_relate_rec.role_resource_type;
1764 l_role_resource_id := role_relate_rec.role_resource_id;
1765 l_role_id := role_relate_rec.role_id;
1766 --Bug8434591
1767 --Typo error attribute4 , attribute6 values are overwritten by
1768 --attribute1 value, corrected the code.
1769 IF(p_start_date_active = FND_API.G_MISS_DATE)
1770 THEN
1771 l_start_date_active := role_relate_rec.start_date_active;
1772 ELSE
1773 l_start_date_active := p_start_date_active;
1774 END IF;
1775 IF(p_end_date_active = FND_API.G_MISS_DATE)
1776 THEN
1777 l_end_date_active := role_relate_rec.end_date_active;
1778 ELSE
1779 l_end_date_active := p_end_date_active;
1780 END IF;
1781 IF(p_attribute1 = FND_API.G_MISS_CHAR)
1782 THEN
1783 l_attribute1 := role_relate_rec.attribute1;
1784 ELSE
1785 l_attribute1 := p_attribute1;
1786 END IF;
1787 IF(p_attribute2= FND_API.G_MISS_CHAR)
1788 THEN
1789 l_attribute2 := role_relate_rec.attribute2;
1790 ELSE
1791 l_attribute2 := p_attribute2;
1792 END IF;
1793 IF(p_attribute3 = FND_API.G_MISS_CHAR)
1794 THEN
1795 l_attribute3 := role_relate_rec.attribute3;
1796 ELSE
1797 l_attribute3 := p_attribute3;
1798 END IF;
1799 IF(p_attribute4 = FND_API.G_MISS_CHAR)
1800 THEN
1801 l_attribute4 := role_relate_rec.attribute4;
1802 ELSE
1803 l_attribute4 := p_attribute4;
1804 END IF;
1805 IF(p_attribute5 = FND_API.G_MISS_CHAR)
1806 THEN
1807 l_attribute5 := role_relate_rec.attribute5;
1808 ELSE
1809 l_attribute5 := p_attribute5;
1810 END IF;
1811 IF(p_attribute6 = FND_API.G_MISS_CHAR)
1812 THEN
1813 l_attribute6 := role_relate_rec.attribute6;
1814 ELSE
1815 l_attribute6 := p_attribute6;
1816 END IF;
1817 IF(p_attribute7 = FND_API.G_MISS_CHAR)
1818 THEN
1819 l_attribute7 := role_relate_rec.attribute7;
1820 ELSE
1821 l_attribute7 := p_attribute7;
1822 END IF;
1823 IF(p_attribute8 = FND_API.G_MISS_CHAR)
1824 THEN
1825 l_attribute8 := role_relate_rec.attribute8;
1826 ELSE
1827 l_attribute8 := p_attribute8;
1828 END IF;
1829 IF(p_attribute9 = FND_API.G_MISS_CHAR)
1830 THEN
1831 l_attribute9 := role_relate_rec.attribute9;
1832 ELSE
1833 l_attribute9 := p_attribute9;
1834 END IF;
1835 IF(p_attribute10 = FND_API.G_MISS_CHAR)
1836 THEN
1837 l_attribute10 := role_relate_rec.attribute10;
1838 ELSE
1839 l_attribute10 := p_attribute10;
1840 END IF;
1841 IF(p_attribute11 = FND_API.G_MISS_CHAR)
1842 THEN
1843 l_attribute11 := role_relate_rec.attribute11;
1844 ELSE
1845 l_attribute11 := p_attribute11;
1846 END IF;
1847 IF(p_attribute12 = FND_API.G_MISS_CHAR)
1848 THEN
1849 l_attribute12 := role_relate_rec.attribute12;
1850 ELSE
1851 l_attribute12 := p_attribute12;
1852 END IF;
1853 IF(p_attribute13 = FND_API.G_MISS_CHAR)
1854 THEN
1855 l_attribute13 := role_relate_rec.attribute13;
1856 ELSE
1857 l_attribute13 := p_attribute13;
1858 END IF;
1859 IF(p_attribute14 = FND_API.G_MISS_CHAR)
1860 THEN
1861 l_attribute14 := role_relate_rec.attribute14;
1862 ELSE
1863 l_attribute14 := p_attribute14;
1864 END IF;
1865 IF(p_attribute15 = FND_API.G_MISS_CHAR)
1866 THEN
1867 l_attribute15 := role_relate_rec.attribute15;
1868 ELSE
1869 l_attribute15 := p_attribute15;
1870 END IF;
1871
1872 IF(p_attribute_category = FND_API.G_MISS_CHAR)
1873 THEN
1874 l_attribute_category := role_relate_rec.attribute_category;
1875 ELSE
1876 l_attribute_category := p_attribute_category;
1877 END IF;
1878
1879 l_delete_flag := role_relate_rec.delete_flag;
1880
1881
1882 IF(l_start_date_active IS NULL)
1883 THEN
1884 l_start_date_active := role_relate_rec.start_date_active;
1885 END IF;
1886
1887
1888 --check start date null
1889 IF(l_start_date_active is NULL)
1890 THEN
1891 fnd_message.set_name ('JTF', 'JTF_RS_DATE_RANGE_ERR');
1892 FND_MSG_PUB.add;
1893 RAISE fnd_api.g_exc_error;
1894 END IF;
1895
1896
1897
1898 --check start date less than end date
1899 IF(l_start_date_active > l_end_date_active)
1900 THEN
1901
1902 fnd_message.set_name ('JTF', 'JTF_RS_DATE_RANGE_ERR');
1903 FND_MSG_PUB.add;
1904 RAISE fnd_api.g_exc_error;
1905 END IF;
1906
1907
1908 IF(l_role_resource_type = 'RS_TEAM' or
1909 l_role_resource_type = 'RS_GROUP' or
1910 l_role_resource_type = 'RS_INDIVIDUAL')
1911 THEN
1912 validate_role_type(l_role_id,
1913 l_start_date_active,
1914 l_end_date_active,
1915 l_role_type_valid);
1916
1917 if (l_role_type_valid = false) then
1918 fnd_message.set_name ('JTF', 'JTF_RS_ROLE_TYPE_INACTIVE');
1919 FND_MSG_PUB.add;
1920 RAISE fnd_api.g_exc_error;
1921 end if;
1922 END IF;
1923
1924
1925 --l_end_date_active := role_relate_rec.end_date_active;
1926
1927
1928 --check whether the start date and end date overlaps any existing start date and end date
1929 --for the resource type, resource id and role.
1930 open check_date_cur(l_role_resource_type,
1931 l_role_resource_id,
1932 l_role_id,
1933 l_role_relate_id);
1934 fetch check_date_cur INTO check_date_rec;
1935 While(check_date_cur%found)
1936 loop
1937
1938 IF((l_start_date_active >= check_date_rec.start_date_active)
1939 AND ((l_start_date_active <= check_date_rec.end_date_active)
1940 OR (check_date_rec.end_date_active IS NULL)))
1941 THEN
1942
1943 l_date_invalid := TRUE;
1944 END IF;
1945
1946 --IF((l_end_date_active between check_date_rec.start_date_active and check_date_rec.end_date_active)
1947 IF((to_date(to_char(nvl(l_end_date_active, fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR')
1948 between check_date_rec.start_date_active and
1949 to_date(to_char(nvl(check_date_rec.end_date_active, fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR'))
1950 OR (l_end_date_active IS NULL AND
1951 check_date_rec.end_date_active IS NULL))
1952 THEN
1953
1954 l_date_invalid := TRUE;
1955 END IF;
1956 -- added this check as a date range outside of the existing ranges was getting entered
1957 if(l_start_date_active < check_date_rec.start_date_active
1958 and to_date(to_char(nvl(l_end_date_active, fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR') >
1959 to_date(to_char(nvl(check_date_rec.end_date_active, fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR'))
1960 THEN
1961 l_date_invalid := TRUE;
1962 END IF;
1963
1964 IF(l_date_invalid)
1965 THEN
1966 exit;
1967 END IF;
1968 fetch check_date_cur INTO check_date_rec;
1969 end loop;
1970 CLOSE check_date_cur;
1971 IF(l_date_invalid)
1972 THEN
1973 fnd_message.set_name ('JTF', 'JTF_RS_DATE_RANGE_ERR');
1974 FND_MSG_PUB.add;
1975 RAISE fnd_api.g_exc_error;
1976 END IF;
1977
1978 --end of overlapping date range check
1979
1980
1981
1982 --valid role for the resource if being entered as a group member and team member
1983 --check whether team member is resource or group
1984 IF(l_role_resource_type = 'RS_TEAM_MEMBER')
1985 THEN
1986 OPEN team_mem_cur(l_role_resource_id);
1987 FETCH team_mem_cur INTO l_team_resource_type, l_rsc_id;
1988 CLOSE team_mem_cur;
1989
1990 END IF;
1991
1992 IF(l_role_resource_type = 'RS_GROUP_MEMBER')
1993 THEN
1994 OPEN grp_mem_cur(l_role_resource_id);
1995 FETCH grp_mem_cur INTO l_rsc_id;
1996 IF(grp_mem_cur%notfound)
1997 THEN
1998 fnd_message.set_name('JTF','JTF_RS_INVALID_GRP_MBR_ID');
1999 fnd_message.set_token('P_GRP_MBR_ID',l_role_resource_id);
2000 fnd_msg_pub.add;
2001 RAISE fnd_api.g_exc_error;
2002 END IF;
2003 CLOSE grp_mem_cur;
2004
2005 END IF;
2006
2007
2008 --valid role for the resource if being entered as a group member and team member
2009 IF((l_role_resource_type = 'RS_GROUP_MEMBER') OR
2010 ((l_role_resource_type = 'RS_TEAM_MEMBER') AND
2011 (l_team_resource_type = 'INDIVIDUAL')))
2012 THEN
2013 --if team member is of type resource or it is group member
2014 --then check for valid role and st date , end date for the resource
2015 --in role relations
2016 open res_role_cur(l_rsc_id,
2017 l_role_id ,
2018 l_start_date_active ,
2019 l_end_date_active );
2020 fetch res_role_cur INTO res_role_rec;
2021 --close res_role_cur;
2022 IF(res_role_cur%found)
2023 THEN
2024 l_role_valid := TRUE;
2025
2026 ELSE
2027
2028 l_role_valid := FALSE;
2029 fnd_message.set_name ('JTF', 'JTF_RS_ROLE_OR_DATE_ERR');
2030 FND_MSG_PUB.add;
2031 RAISE fnd_api.g_exc_error;
2032 END IF;
2033 close res_role_cur;
2034 ELSIF((l_role_resource_type = 'RS_TEAM_MEMBER') AND
2035 (l_team_resource_type = 'GROUP'))
2036 THEN
2037 --if team member is of type group then check for valid role and st date ,
2038 --end date for the group in role relations
2039
2040 open grp_role_cur(l_rsc_id,
2041 l_role_id ,
2042 l_start_date_active ,
2043 l_end_date_active );
2044 fetch grp_role_cur INTO grp_role_rec;
2045 --close grp_role_cur;
2046 IF(grp_role_cur%found)
2047 THEN
2048 l_role_valid := TRUE;
2049
2050 ELSE
2051 l_role_valid := FALSE;
2052 fnd_message.set_name ('JTF', 'JTF_RS_ROLE_OR_DATE_ERR');
2053 FND_MSG_PUB.add;
2054 RAISE fnd_api.g_exc_error;
2055 END IF;
2056 close grp_role_cur;
2057
2058 END IF;
2059 -- end of valid role for the resource if being entered as a group member and team member
2060
2061 --if resource type is group member or team member then check against group and team
2062 --start date and end date
2063 IF(l_role_resource_type = 'RS_TEAM_MEMBER')
2064 THEN
2065 open team_cur(l_role_resource_id);
2066 fetch team_cur INTO team_rec;
2067 close team_cur;
2068 -- IF((trunc(team_rec.start_date_active) > trunc(l_start_date_active))
2069 IF (( to_date(to_char(nvl(team_rec.start_date_active, fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR') > to_date(to_char(nvl(l_start_date_active, fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR'))
2070 OR to_date(to_char(nvl(team_rec.end_date_active, fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR') < to_date(to_char(nvl(l_end_date_active, fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR'))
2071 THEN
2072 fnd_message.set_name ('JTF', 'JTF_RS_TM_GRP_DT_ERR');
2073 FND_MSG_PUB.add;
2074 RAISE fnd_api.g_exc_error;
2075 END IF;
2076
2077 IF(team_rec.end_date_active is not null AND l_end_date_active is null)
2078 THEN
2079 fnd_message.set_name ('JTF', 'JTF_RS_TM_GRP_DT_ERR');
2080 FND_MSG_PUB.add;
2081 RAISE fnd_api.g_exc_error;
2082 END IF;
2083
2084 ELSIF(l_role_resource_type = 'RS_GROUP_MEMBER')
2085 THEN
2086 open group_cur(l_role_resource_id);
2087 fetch group_cur INTO group_rec;
2088 close group_cur;
2089
2090 -- IF((trunc(group_rec.start_date_active) > trunc(l_start_date_active))
2091 IF (( to_date(to_char(nvl(group_rec.start_date_active, fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR') > to_date(to_char(nvl(l_start_date_active, fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR'))
2092 OR to_date(to_char(nvl(group_rec.end_date_active, fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR') < to_date(to_char(nvl(l_end_date_active, fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR'))
2093 THEN
2094
2095 fnd_message.set_name ('JTF', 'JTF_RS_TM_GRP_DT_ERR');
2096 FND_MSG_PUB.add;
2097 RAISE fnd_api.g_exc_error;
2098 END IF;
2099
2100
2101
2102 IF(group_rec.end_date_active is not null AND l_end_date_active is null)
2103 THEN
2104 fnd_message.set_name ('JTF', 'JTF_RS_TM_GRP_DT_ERR');
2105 FND_MSG_PUB.add;
2106 RAISE fnd_api.g_exc_error;
2107 END IF;
2108
2109 --exclusive flag validation
2110 open c_exclusive_group_check_cur(l_role_resource_id,
2111 l_start_date_active,
2112 l_end_date_active);
2113
2114 fetch c_exclusive_group_check_cur into c_exclusive_group_check_rec;
2115 IF(c_exclusive_group_check_cur%FOUND)
2116 THEN
2117 fnd_message.set_name ('JTF', 'JTF_RS_RES_USAGE_ERR');
2118 FND_MSG_PUB.add;
2119 RAISE fnd_api.g_exc_error;
2120 END IF;
2121
2122 close c_exclusive_group_check_cur;
2123 ELSIF(l_role_resource_type = 'RS_INDIVIDUAL')
2124 THEN
2125
2126 open rsc_cur(l_role_resource_id);
2127 fetch rsc_cur INTO rsc_rec;
2128 close rsc_cur;
2129
2130 IF((rsc_rec.start_date_active > l_start_date_active)
2131 --changed by sudarsana 11 feb 2002
2132 OR (rsc_rec.end_date_active < to_date(to_char(nvl(l_end_date_active, fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR')))
2133 -- OR (rsc_rec.end_date_active < l_end_date_active))
2134 THEN
2135 fnd_message.set_name ('JTF', 'JTF_RS_RES_DATE_ERR');
2136 FND_MSG_PUB.add;
2137 RAISE fnd_api.g_exc_error;
2138 END IF;
2139
2140 ELSIF(l_role_resource_type = 'RS_GROUP')
2141 THEN
2142 open group_dt_cur(l_role_resource_id);
2143 fetch group_dt_cur INTO group_dt_rec;
2144 close group_dt_cur;
2145 IF((group_dt_rec.start_date_active > l_start_date_active)
2146 -- changed by nsinghai 20 May 2002 to handle null value of l_end_date_active
2147 --OR (group_dt_rec.end_date_active < l_end_date_active))
2148 OR (to_date(to_char(nvl(group_dt_rec.end_date_active,fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR')
2149 < (to_date(to_char(nvl(l_end_date_active,fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR'))))
2150 THEN
2151 fnd_message.set_name ('JTF', 'JTF_RS_GRP_DT_ERR');
2152 FND_MSG_PUB.add;
2153 RAISE fnd_api.g_exc_error;
2154 END IF;
2155 ELSIF(l_role_resource_type = 'RS_TEAM')
2156 THEN
2157 open team_dt_cur(l_role_resource_id);
2158 fetch team_dt_cur INTO team_dt_rec;
2159 close team_dt_cur;
2160 IF((team_dt_rec.start_date_active > l_start_date_active)
2161 -- changed by nsinghai 20 May 2002 to handle null value of l_end_date_active
2162 --OR (team_dt_rec.end_date_active < l_end_date_active))
2163 OR (to_date(to_char(nvl(team_dt_rec.end_date_active,fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR')
2164 < (to_date(to_char(nvl(l_end_date_active,fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR'))))
2165 THEN
2166 fnd_message.set_name ('JTF', 'JTF_RS_TEAM_DT_ERR');
2167 FND_MSG_PUB.add;
2168 RAISE fnd_api.g_exc_error;
2169 END IF;
2170 END IF;
2171
2172 --if resource type is RS_INDIVIDUAL then check whether the start and end dates do not
2173 --fall within the start and end dates if this resource is a team member or group member
2174 IF(l_role_resource_type = 'RS_INDIVIDUAL')
2175 THEN
2176 open res_team_cur(l_role_resource_id,
2177 l_start_date_active,
2178 l_end_date_active,
2179 l_role_id);
2180 fetch res_team_cur INTO res_team_rec;
2181
2182 If(res_team_cur%found)
2183 THEN
2184
2185 x_return_status := fnd_api.g_ret_sts_error;
2186 fnd_message.set_name ('JTF', 'JTF_RS_RES_MEM_DT_ERR');
2187 FND_MSG_PUB.add;
2188 RAISE fnd_api.g_exc_error;
2189 END IF;
2190 close res_team_cur;
2191
2192 open res_group_cur(l_role_resource_id,
2193 l_start_date_active,
2194 l_end_date_active,
2195 l_role_id);
2196 fetch res_group_cur INTO res_group_rec;
2197
2198 If(res_group_cur%found)
2199 THEN
2200 fnd_message.set_name ('JTF', 'JTF_RS_RES_MEM_DT_ERR');
2201 FND_MSG_PUB.add;
2202 RAISE fnd_api.g_exc_error;
2203 END IF;
2204 close res_group_cur;
2205
2206
2207 -- we also need to check that no group/team member role becomes invalid
2208 -- because of this change
2209 validate_indv_role_date(p_role_relate_id => l_role_relate_id,
2210 p_role_id => l_role_id,
2211 p_resource_id => l_role_resource_id,
2212 p_old_start_date => role_relate_rec.start_date_active,
2213 p_old_end_date => role_relate_rec.end_date_active,
2214 p_new_start_date => l_start_date_active,
2215 p_new_end_date => l_end_date_active,
2216 p_valid => l_valid);
2217
2218 If NOT(l_valid)
2219 THEN
2220 --fnd_message.set_name ('JTF', 'JTF_RS_RES_UPD_DT_ERR');
2221 --FND_MSG_PUB.add;
2222 RAISE fnd_api.g_exc_error;
2223 END IF;
2224
2225 END IF;
2226
2227 --call update table handler
2228 BEGIN
2229
2230 jtf_rs_role_relations_pkg.lock_row(
2231 x_role_relate_id => l_role_relate_id,
2232 x_object_version_number => p_object_version_num
2233 );
2234
2235 EXCEPTION
2236
2237 WHEN OTHERS THEN
2238 fnd_message.set_name('JTF', 'JTF_RS_ROW_LOCK_ERROR');
2239 fnd_msg_pub.add;
2240 RAISE fnd_api.g_exc_error;
2241
2242 END;
2243
2244 l_object_version_number := l_object_version_number +1;
2245
2246 --call audit api for update
2247 jtf_rs_role_relate_aud_pvt.update_role_relate(
2248 P_API_VERSION => 1.0,
2249 P_INIT_MSG_LIST => p_init_msg_list,
2250 P_COMMIT => null,
2251 P_ROLE_RELATE_ID => l_role_relate_id,
2252 P_ROLE_RESOURCE_TYPE => l_role_resource_type,
2253 P_ROLE_RESOURCE_ID => l_role_resource_id,
2254 P_ROLE_ID => l_role_id,
2255 P_START_DATE_ACTIVE => l_start_date_active,
2256 P_END_DATE_ACTIVE => l_end_date_active,
2257 P_OBJECT_VERSION_NUMBER => l_object_version_number,
2258 X_RETURN_STATUS => l_return_status,
2259 X_MSG_COUNT => l_msg_count,
2260 X_MSG_DATA => l_msg_data );
2261
2262 IF(l_return_status <> fnd_api.g_ret_sts_success)
2263 THEN
2264 fnd_message.set_name ('JTF', 'JTF_RS_AUDIT_ERR');
2265 FND_MSG_PUB.add;
2266 IF L_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
2267 RAISE FND_API.G_EXC_ERROR;
2268 ELSIF L_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
2269 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2270 END IF;
2271
2272 END IF;
2273
2274 /* Calling publish API to raise update resource role relation event. */
2275 /* added by baianand on 04/09/2003 */
2276
2277 begin
2278
2279 jtf_rs_wf_events_pub.update_resource_role_relate
2280 (p_api_version => 1.0
2281 ,p_init_msg_list => fnd_api.g_false
2282 ,p_commit => fnd_api.g_false
2283 ,p_role_relate_id => l_role_relate_id
2284 ,p_role_resource_type => l_role_resource_type
2285 ,p_role_resource_id => l_role_resource_id
2286 ,p_role_id => l_role_id
2287 ,p_start_date_active => l_start_date_active
2288 ,p_end_date_active => l_end_date_active
2289 ,x_return_status => l_return_status
2290 ,x_msg_count => l_msg_count
2291 ,x_msg_data => l_msg_data);
2292
2293 EXCEPTION when others then
2294 null;
2295 end;
2296
2297 /* End of publish API call */
2298
2299 jtf_rs_role_relations_pkg.update_row(X_ROLE_RELATE_ID => l_role_relate_id,
2300 X_ATTRIBUTE9 => l_attribute9,
2301 X_ATTRIBUTE10 => l_attribute10,
2302 X_ATTRIBUTE11 => l_attribute11,
2303 X_ATTRIBUTE12 => l_attribute12,
2304 X_ATTRIBUTE13 => l_attribute13,
2305 X_ATTRIBUTE14 => l_attribute14,
2306 X_ATTRIBUTE15 => l_attribute15,
2307 X_ATTRIBUTE_CATEGORY => l_attribute_category,
2308 X_ROLE_RESOURCE_TYPE => l_role_resource_type,
2309 X_ROLE_RESOURCE_ID => l_role_resource_id,
2310 X_ROLE_ID => l_role_id,
2311 X_START_DATE_ACTIVE => l_start_date_active,
2312 X_END_DATE_ACTIVE => l_end_date_active,
2313 X_DELETE_FLAG => l_delete_flag,
2314 X_OBJECT_VERSION_NUMBER => l_object_version_number ,
2315 X_ATTRIBUTE2 => l_attribute2,
2316 X_ATTRIBUTE3 => l_attribute3,
2317 X_ATTRIBUTE4 => l_attribute4,
2318 X_ATTRIBUTE5 => l_attribute5,
2319 X_ATTRIBUTE6 => l_attribute6,
2320 X_ATTRIBUTE7 => l_attribute7,
2321 X_ATTRIBUTE8 => l_attribute8,
2322 X_ATTRIBUTE1 => l_attribute1,
2323 X_LAST_UPDATE_DATE => l_date,
2324 X_LAST_UPDATED_BY => l_user_id,
2325 X_LAST_UPDATE_LOGIN => l_login_id ) ;
2326
2327 P_OBJECT_VERSION_NUM := l_object_version_number;
2328
2329
2330 IF(l_role_resource_type = 'RS_GROUP_MEMBER')
2331 THEN
2332
2333 -- get the group id of the member
2334 open get_group_cur(l_role_relate_id);
2335 fetch get_group_cur into l_group_id;
2336 close get_group_cur;
2337
2338 --get no of children for the group
2339 BEGIN
2340 open get_child_cur(l_group_id);
2341 fetch get_child_cur into l_child_cnt;
2342 close get_child_cur;
2343 EXCEPTION
2344 WHEN OTHERS THEN
2345 l_child_cnt := 101; -- use concurrent program
2346 END;
2347
2348 if (nvl(l_child_cnt, 0) > 100)
2349 then
2350 begin
2351 insert into jtf_rs_chgd_role_relations
2352 (role_relate_id,
2353 role_resource_type,
2354 role_resource_id,
2355 role_id,
2356 start_date_active,
2357 end_date_active,
2358 delete_flag,
2359 operation_flag,
2360 created_by,
2361 creation_date,
2362 last_updated_by,
2363 last_update_date,
2364 last_update_login)
2365 values(
2366 l_role_relate_id,
2367 l_role_resource_type,
2368 l_role_resource_id,
2369 l_role_id,
2370 l_start_date_active,
2371 l_end_date_active,
2372 'N',
2373 'U',
2374 l_user_id,
2375 l_date,
2376 l_user_id,
2377 l_date,
2378 l_login_id);
2379
2380 exception
2381 when others then
2382 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
2383 fnd_message.set_token('P_SQLCODE',SQLCODE);
2384 fnd_message.set_token('P_SQLERRM',SQLERRM);
2385 fnd_message.set_token('P_API_NAME', l_api_name);
2386 FND_MSG_PUB.add;
2387 RAISE fnd_api.g_exc_unexpected_error;
2388
2389
2390 end;
2391
2392
2393 --call concurrent program
2394
2395 begin
2396 l_request := fnd_request.submit_request(APPLICATION => 'JTF',
2397 PROGRAM => 'JTFRSRMG');
2398
2399 open conc_prog_cur;
2400 fetch conc_prog_cur into g_name;
2401 close conc_prog_cur;
2402 fnd_message.set_name ('JTF', 'JTF_RS_CONC_START');
2403 fnd_message.set_token('P_NAME',g_name);
2404 fnd_message.set_token('P_ID',l_request);
2405 FND_MSG_PUB.add;
2406
2407
2408 exception when others then
2409 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
2410 fnd_message.set_token('P_SQLCODE',SQLCODE);
2411 fnd_message.set_token('P_SQLERRM',SQLERRM);
2412 fnd_message.set_token('P_API_NAME', l_api_name);
2413 FND_MSG_PUB.add;
2414 RAISE fnd_api.g_exc_unexpected_error;
2415 end;
2416
2417 else
2418
2419
2420
2421 --call to UPDATE records in jtf_rs_rep_managers
2422 JTF_RS_REP_MGR_DENORM_PVT.UPDATE_REP_MANAGER
2423 ( P_API_VERSION => 1.0,
2424 P_INIT_MSG_LIST => p_init_msg_list,
2425 P_COMMIT => null,
2426 P_ROLE_RELATE_ID => l_role_relate_id,
2427 X_RETURN_STATUS => l_return_status,
2428 X_MSG_COUNT => l_msg_count,
2429 X_MSG_DATA => l_msg_data);
2430
2431 IF(l_return_status <> fnd_api.g_ret_sts_success)
2432 THEN
2433 IF L_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
2434 RAISE FND_API.G_EXC_ERROR;
2435 ELSIF L_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
2436 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2437 END IF;
2438
2439
2440 END IF;
2441 END IF; -- END OF COUNT CHECK
2442 END IF;
2443
2444 -- user hook calls for customer
2445 -- Customer pre- processing section - mandatory
2446 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'UPDATE_RESOURCE_ROLE_RELATE', 'A', 'C' ))
2447 then
2448 JTF_RS_ROLE_RELATE_CUHK.UPDATE_RES_ROLE_RELATE_POST(P_ROLE_RELATE_ID => p_role_relate_id,
2449 P_START_DATE_ACTIVE => P_start_date_active,
2450 P_END_DATE_ACTIVE => P_end_date_active,
2451 P_OBJECT_VERSION_NUM => P_OBJECT_VERSION_NUM,
2452 p_data => L_data,
2453 p_count => L_count,
2454 P_return_code => l_return_code);
2455 if( l_return_code <> FND_API.G_RET_STS_SUCCESS) then
2456 fnd_message.set_name ('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
2457 FND_MSG_PUB.add;
2458 IF l_return_code = FND_API.G_RET_STS_ERROR THEN
2459 RAISE FND_API.G_EXC_ERROR;
2460 ELSIF l_return_code = FND_API.G_RET_STS_UNEXP_ERROR THEN
2461 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2462 END IF;
2463
2464 end if;
2465 end if;
2466
2467 /* Vertical industry post- processing section - mandatory */
2468
2469 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'UPDATE_RESOURCE_ROLE_RELATE', 'A', 'V' ))
2470 then
2471 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'UPDATE_RESOURCE_ROLE_RELATE', 'A', 'V' ))
2472 then
2473
2474 JTF_RS_ROLE_RELATE_VUHK.UPDATE_RES_ROLE_RELATE_POST(P_ROLE_RELATE_ID => p_role_relate_id,
2475 P_START_DATE_ACTIVE => P_start_date_active,
2476 P_END_DATE_ACTIVE => P_end_date_active,
2477 P_OBJECT_VERSION_NUM => P_OBJECT_VERSION_NUM,
2478 p_data => L_data,
2479 p_count => L_count,
2480 P_return_code => l_return_code);
2481 if ( l_return_code <> FND_API.G_RET_STS_SUCCESS) then
2482 fnd_message.set_name ('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
2483 FND_MSG_PUB.add;
2484 IF l_return_code = FND_API.G_RET_STS_ERROR THEN
2485 RAISE FND_API.G_EXC_ERROR;
2486 ELSIF l_return_code = FND_API.G_RET_STS_UNEXP_ERROR THEN
2487 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2488 END IF;
2489
2490 end if;
2491 end if;
2492 end if;
2493
2494
2495 /* Internal post- processing section - mandatory */
2496
2497 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'UPDATE_RESOURCE_ROLE_RELATE', 'A', 'I' ))
2498 then
2499 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'UPDATE_RESOURCE_ROLE_RELATE', 'A', 'I' ))
2500 then
2501
2502 JTF_RS_ROLE_RELATE_IUHK.UPDATE_RES_ROLE_RELATE_POST(P_ROLE_RELATE_ID => p_role_relate_id,
2503 P_START_DATE_ACTIVE => P_start_date_active,
2504 P_END_DATE_ACTIVE => P_end_date_active,
2505 P_OBJECT_VERSION_NUM => P_OBJECT_VERSION_NUM,
2506 p_data => L_data,
2507 p_count => L_count,
2508 P_return_code => l_return_code);
2509 if ( l_return_code <> FND_API.G_RET_STS_SUCCESS)
2510 then
2511 fnd_message.set_name ('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
2512 FND_MSG_PUB.add;
2513 IF l_return_code = FND_API.G_RET_STS_ERROR THEN
2514 RAISE FND_API.G_EXC_ERROR;
2515 ELSIF l_return_code = FND_API.G_RET_STS_UNEXP_ERROR THEN
2516 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2517 END IF;
2518 end if;
2519 end if;
2520 end if;
2521 -- end of user hook call
2522
2523 IF jtf_resource_utl.ok_to_execute(
2524 'JTF_RS_ROLE_RELATE_PVT',
2525 'UPDATE_RESOURCE_ROLE_RELATE',
2526 'M',
2527 'M')
2528 THEN
2529 IF jtf_usr_hks.ok_to_execute(
2530 'JTF_RS_ROLE_RELATE_PVT',
2531 'UPDATE_RESOURCE_ROLE_RELATE',
2532 'M',
2533 'M')
2534 THEN
2535
2536 IF (jtf_rs_role_relate_cuhk.ok_to_generate_msg(
2537 p_role_relate_id => l_role_relate_id,
2538 x_return_status => x_return_status) )
2539 THEN
2540
2541 /* Get the bind data id for the Business Object Instance */
2542
2543 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
2544
2545
2546 /* Set bind values for the bind variables in the Business Object
2547 SQL */
2548
2549 jtf_usr_hks.load_bind_data(l_bind_data_id, 'role_relate_id',
2550 l_role_relate_id, 'S', 'N');
2551
2552
2553 /* Call the message generation API */
2554
2555 jtf_usr_hks.generate_message(
2556 p_prod_code => 'JTF',
2557 p_bus_obj_code => 'RS_RRL',
2558 p_action_code => 'U', /* I/U/D */
2559 p_bind_data_id => l_bind_data_id,
2560 x_return_code => x_return_status);
2561
2562
2563 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
2564 -- x_return_status := fnd_api.g_ret_sts_error;
2565
2566 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
2567 fnd_msg_pub.add;
2568 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
2569 RAISE FND_API.G_EXC_ERROR;
2570 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
2571 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2572 END IF;
2573
2574
2575 END IF;
2576
2577 END IF;
2578
2579 END IF;
2580 END IF;
2581
2582
2583
2584
2585
2586 IF fnd_api.to_boolean (p_commit)
2587 THEN
2588 COMMIT WORK;
2589 END IF;
2590
2591
2592 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2593
2594 EXCEPTION
2595 WHEN fnd_api.g_exc_error THEN
2596 ROLLBACK TO ROLE_RELATE_SP;
2597 x_return_status := fnd_api.g_ret_sts_error;
2598 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
2599 p_data => x_msg_data);
2600 WHEN fnd_api.g_exc_unexpected_error THEN
2601 ROLLBACK TO ROLE_RELATE_SP;
2602 x_return_status := fnd_api.g_ret_sts_unexp_error;
2603 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
2604 p_data => x_msg_data);
2605 WHEN OTHERS THEN
2606 ROLLBACK TO ROLE_RELATE_SP;
2607 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
2608 fnd_message.set_token('P_SQLCODE',SQLCODE);
2609 fnd_message.set_token('P_SQLERRM',SQLERRM);
2610 fnd_message.set_token('P_API_NAME', l_api_name);
2611 FND_MSG_PUB.add;
2612 x_return_status := fnd_api.g_ret_sts_unexp_error;
2613 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
2614 p_data => x_msg_data);
2615
2616 END update_resource_role_relate;
2617
2618
2619 /* Procedure to delete the resource roles. */
2620
2621 PROCEDURE delete_resource_role_relate
2622 (P_API_VERSION IN NUMBER,
2623 P_INIT_MSG_LIST IN VARCHAR2,
2624 P_COMMIT IN VARCHAR2,
2625 P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
2626 P_OBJECT_VERSION_NUM IN JTF_RS_ROLE_RELATIONS.OBJECT_VERSION_NUMBER%TYPE,
2627 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2628 X_MSG_COUNT OUT NOCOPY NUMBER,
2629 X_MSG_DATA OUT NOCOPY VARCHAR2
2630 )IS
2631
2632
2633 CURSOR chk_type_cur(l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE)
2634 IS
2635 SELECT role_resource_type,
2636 role_resource_id,
2637 role_id,
2638 start_date_active,
2639 end_date_active,
2640 object_version_number,
2641 attribute1,
2642 attribute2,
2643 attribute3,
2644 attribute4,
2645 attribute5,
2646 attribute6,
2647 attribute7,
2648 attribute8,
2649 attribute9,
2650 attribute10,
2651 attribute11,
2652 attribute12,
2653 attribute13,
2654 attribute14,
2655 attribute15,
2656 attribute_category
2657 FROM jtf_rs_role_relations
2658 WHERE role_relate_id = l_role_relate_id;
2659
2660
2661 chk_type_rec chk_type_cur%rowtype;
2662
2663 CURSOR chk_grp_cur(l_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
2664 l_role_id JTF_RS_ROLES_B.ROLE_ID%TYPE,
2665 l_start_date_active JTF_RS_ROLE_RELATIONS.START_DATE_ACTIVE%TYPE,
2666 l_end_date_active JTF_RS_ROLE_RELATIONS.END_DATE_ACTIVE%TYPE)
2667 IS
2668 SELECT 'x'
2669 FROM jtf_rs_role_relations rlt,
2670 jtf_rs_group_members mem
2671 WHERE mem.resource_id = l_resource_id
2672 AND rlt.role_resource_id = mem.group_member_id
2673 AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
2674 AND rlt.role_id = l_role_id
2675 --AND nvl(end_date_active, TRUNC(sysdate) + 1) > TRUNC(sysdate)
2676 AND (start_date_active between l_start_date_active and to_date(to_char(nvl(l_end_date_active, fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR')
2677 OR to_date(to_char(nvl(end_date_active, fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR') between l_start_date_active and to_date(to_char(nvl(l_end_date_active, fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR'))
2678 AND nvl(rlt.delete_flag, '0') <> 'Y';
2679
2680 chk_grp_rec chk_grp_cur%rowtype;
2681
2682 CURSOR chk_team_cur(l_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
2683 l_role_id JTF_RS_ROLES_B.ROLE_ID%TYPE,
2684 l_start_date_active JTF_RS_ROLE_RELATIONS.START_DATE_ACTIVE%TYPE,
2685 l_end_date_active JTF_RS_ROLE_RELATIONS.END_DATE_ACTIVE%TYPE)
2686 IS
2687 SELECT 'x'
2688 FROM jtf_rs_role_relations rlt,
2689 jtf_rs_team_members mem
2690 WHERE mem.team_resource_id = l_resource_id
2691 AND mem.resource_type <> 'GROUP'
2692 AND rlt.role_resource_id = mem.team_member_id
2693 AND rlt.role_resource_type = 'RS_TEAM_MEMBER'
2694 AND rlt.role_id = l_role_id
2695 --AND nvl(rlt.end_date_active, TRUNC(sysdate) + 1) > TRUNC(sysdate)
2696 AND (start_date_active between l_start_date_active and to_date(to_char(nvl(l_end_date_active, fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR')
2697 OR to_date(to_char(nvl(end_date_active, fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR') between l_start_date_active and to_date(to_char(nvl(l_end_date_active, fnd_api.g_miss_date),'DD-MM-RRRR'),'DD-MM-RRRR'))
2698 AND nvl(rlt.delete_flag, '0') <> 'Y';
2699
2700
2701 chk_team_rec chk_team_cur%rowtype;
2702
2703
2704 /* Moved the initial assignment of below variable to inside begin */
2705 l_role_relate_id JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE;
2706
2707 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RESOURCE_ROLE_RELATE';
2708 l_api_version CONSTANT NUMBER :=1.0;
2709 l_bind_data_id number;
2710
2711 l_date Date;
2712 l_g_miss_date Date;
2713 l_user_id Number;
2714 l_login_id Number;
2715
2716
2717 l_return_code VARCHAR2(100);
2718 l_count NUMBER;
2719 l_data VARCHAR2(200);
2720
2721 L_ATTRIBUTE1 JTF_RS_ROLE_RELATIONS.ATTRIBUTE1%TYPE;
2722 L_ATTRIBUTE2 JTF_RS_ROLE_RELATIONS.ATTRIBUTE2%TYPE;
2723 L_ATTRIBUTE3 JTF_RS_ROLE_RELATIONS.ATTRIBUTE3%TYPE;
2724 L_ATTRIBUTE4 JTF_RS_ROLE_RELATIONS.ATTRIBUTE4%TYPE;
2725 L_ATTRIBUTE5 JTF_RS_ROLE_RELATIONS.ATTRIBUTE5%TYPE;
2726 L_ATTRIBUTE6 JTF_RS_ROLE_RELATIONS.ATTRIBUTE6%TYPE;
2727 L_ATTRIBUTE7 JTF_RS_ROLE_RELATIONS.ATTRIBUTE7%TYPE;
2728 L_ATTRIBUTE8 JTF_RS_ROLE_RELATIONS.ATTRIBUTE8%TYPE;
2729 L_ATTRIBUTE9 JTF_RS_ROLE_RELATIONS.ATTRIBUTE9%TYPE;
2730 L_ATTRIBUTE10 JTF_RS_ROLE_RELATIONS.ATTRIBUTE10%TYPE;
2731 L_ATTRIBUTE11 JTF_RS_ROLE_RELATIONS.ATTRIBUTE11%TYPE;
2732 L_ATTRIBUTE12 JTF_RS_ROLE_RELATIONS.ATTRIBUTE12%TYPE;
2733 L_ATTRIBUTE13 JTF_RS_ROLE_RELATIONS.ATTRIBUTE13%TYPE;
2734 L_ATTRIBUTE14 JTF_RS_ROLE_RELATIONS.ATTRIBUTE14%TYPE;
2735 L_ATTRIBUTE15 JTF_RS_ROLE_RELATIONS.ATTRIBUTE15%TYPE;
2736 L_ATTRIBUTE_CATEGORY JTF_RS_ROLE_RELATIONS.ATTRIBUTE_CATEGORY%TYPE;
2737
2738 l_return_status VARCHAR2(200);
2739 l_msg_count NUMBER;
2740 l_msg_data VARCHAR2(200);
2741
2742 cursor get_group_cur(l_role_relate_id number)
2743 is
2744 select mem.group_id
2745 from jtf_rs_group_members mem,
2746 jtf_rs_role_relations rel
2747 where rel.role_relate_id = l_role_relate_id
2748 and rel.role_resource_id = mem.group_member_id;
2749
2750 l_group_id number;
2751
2752 cursor get_child_cur(l_group_id number)
2753 is
2754 select count(*) child_cnt
2755 from jtf_rs_grp_relations rel
2756 connect by related_group_id = prior group_id
2757 and nvl(delete_flag, 'N') <> 'Y'
2758 AND ((trunc(rel.start_date_active) <= prior rel.start_date_active
2759 AND nvl(rel.end_date_active, prior rel.start_date_active) >=
2760 trunc(prior rel.start_date_active)) OR
2761 (rel.start_date_active > trunc(prior rel.start_date_active)
2762 AND trunc(rel.start_date_active) <= nvl(prior rel.end_date_active,
2763 rel.start_date_active)))
2764 start with related_group_id = l_group_id
2765 and nvl(delete_flag, 'N') <> 'Y';
2766
2767 l_child_cnt number := 0;
2768 l_request number;
2769
2770
2771 cursor conc_prog_cur
2772 is
2773 select description
2774 from fnd_concurrent_programs_vl
2775 where concurrent_program_name = 'JTFRSRMG'
2776 and application_id = 690;
2777 BEGIN
2778
2779 l_role_relate_id := p_role_relate_id;
2780
2781 --Standard Start of API SAVEPOINT
2782 SAVEPOINT ROLE_RELATE_SP;
2783
2784 x_return_status := fnd_api.g_ret_sts_success;
2785
2786 --Standard Call to check API compatibility
2787 IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
2788 THEN
2789 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2790 END IF;
2791
2792 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
2793 IF FND_API.To_boolean(P_INIT_MSG_LIST)
2794 THEN
2795 FND_MSG_PUB.Initialize;
2796 END IF;
2797
2798 -- user hook calls for customer
2799 -- Customer pre- processing section - mandatory
2800 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'DELETE_RESOURCE_ROLE_RELATE', 'B', 'C' ))
2801 then
2802 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'DELETE_RESOURCE_ROLE_RELATE', 'B', 'C' ))
2803 then
2804 JTF_RS_ROLE_RELATE_CUHK.DELETE_RES_ROLE_RELATE_PRE(P_ROLE_RELATE_ID => p_role_relate_id,
2805 P_OBJECT_VERSION_NUM => p_object_version_num,
2806 p_data => L_data,
2807 p_count => L_count,
2808 P_return_code => l_return_code);
2809 if ( l_return_code <> FND_API.G_RET_STS_SUCCESS) then
2810 fnd_message.set_name ('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
2811 FND_MSG_PUB.add;
2812 IF l_return_code = FND_API.G_RET_STS_ERROR THEN
2813 RAISE FND_API.G_EXC_ERROR;
2814 ELSIF l_return_code = FND_API.G_RET_STS_UNEXP_ERROR THEN
2815 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2816 END IF;
2817
2818 end if;
2819 end if;
2820 end if;
2821
2822 /* Vertical industry pre- processing section - mandatory */
2823
2824 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'DELETE_RESOURCE_ROLE_RELATE', 'B', 'V' ))
2825 then
2826 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'DELETE_RESOURCE_ROLE_RELATE', 'B', 'V' ))
2827 then
2828
2829 JTF_RS_ROLE_RELATE_VUHK.DELETE_RES_ROLE_RELATE_PRE(P_ROLE_RELATE_ID => p_role_relate_id,
2830 P_OBJECT_VERSION_NUM => p_object_version_num,
2831 p_data => L_data,
2832 p_count => L_count,
2833 P_return_code => l_return_code);
2834 if ( l_return_code <> FND_API.G_RET_STS_SUCCESS) then
2835 fnd_message.set_name ('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
2836 FND_MSG_PUB.add;
2837 IF l_return_code = FND_API.G_RET_STS_ERROR THEN
2838 RAISE FND_API.G_EXC_ERROR;
2839 ELSIF l_return_code = FND_API.G_RET_STS_UNEXP_ERROR THEN
2840 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2841 END IF;
2842
2843 end if;
2844 end if;
2845 end if;
2846
2847 /* Internal pre- processing section - mandatory */
2848
2849 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'DELETE_RESOURCE_ROLE_RELATE', 'B', 'I' ))
2850 then
2851 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'DELETE_RESOURCE_ROLE_RELATE', 'B', 'I' ))
2852 then
2853
2854 JTF_RS_ROLE_RELATE_IUHK.DELETE_RES_ROLE_RELATE_PRE(P_ROLE_RELATE_ID => p_role_relate_id,
2855 P_OBJECT_VERSION_NUM => p_object_version_num,
2856 p_data => L_data,
2857 p_count => L_count,
2858 P_return_code => l_return_code);
2859 if ( l_return_code <> FND_API.G_RET_STS_SUCCESS) then
2860 fnd_message.set_name ('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
2861 FND_MSG_PUB.add;
2862 IF l_return_code = FND_API.G_RET_STS_ERROR THEN
2863 RAISE FND_API.G_EXC_ERROR;
2864 ELSIF l_return_code = FND_API.G_RET_STS_UNEXP_ERROR THEN
2865 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2866 END IF;
2867
2868 end if;
2869 end if;
2870 end if;
2871
2872
2873 -- end of user hook call
2874
2875
2876 --check the resource type
2877 --If resource type is individual then check whether this resource with the same role
2878 --is existing as a current team/group member
2879
2880 OPEN chk_type_cur(l_role_relate_id);
2881 FETCH chk_type_cur INTO chk_type_rec;
2882 IF chk_type_cur%FOUND THEN
2883
2884 --assign the attribute1..15 values to the local varialbles
2885 L_ATTRIBUTE1 := chk_type_rec.attribute1;
2886 L_ATTRIBUTE2 := chk_type_rec.attribute2;
2887 L_ATTRIBUTE3 := chk_type_rec.attribute3;
2888 L_ATTRIBUTE4 := chk_type_rec.attribute4;
2889 L_ATTRIBUTE5 := chk_type_rec.attribute5;
2890 L_ATTRIBUTE6 := chk_type_rec.attribute6;
2891 L_ATTRIBUTE7 := chk_type_rec.attribute7;
2892 L_ATTRIBUTE8 := chk_type_rec.attribute8;
2893 L_ATTRIBUTE9 := chk_type_rec.attribute9;
2894 L_ATTRIBUTE10 := chk_type_rec.attribute10;
2895 L_ATTRIBUTE11 := chk_type_rec.attribute11;
2896 L_ATTRIBUTE12 := chk_type_rec.attribute12;
2897 L_ATTRIBUTE13 := chk_type_rec.attribute13;
2898 L_ATTRIBUTE14 := chk_type_rec.attribute14;
2899 L_ATTRIBUTE15 := chk_type_rec.attribute15;
2900 L_ATTRIBUTE_CATEGORY := chk_type_rec.attribute_category;
2901
2902
2903 IF chk_type_rec.role_resource_type = 'RS_INDIVIDUAL' THEN
2904 OPEN chk_team_cur (chk_type_rec.role_resource_id ,
2905 chk_type_rec.role_id,
2906 chk_type_rec.start_date_active,
2907 chk_type_rec.end_date_active);
2908 FETCH chk_team_cur INTO chk_team_rec;
2909 IF(chk_team_cur%FOUND) THEN
2910 fnd_message.set_name ('JTF', 'JTF_RS_MEM_ROLE_EXIST_ERR');
2911 FND_MSG_PUB.add;
2912 CLOSE chk_team_cur;
2913 RAISE fnd_api.g_exc_error;
2914 END IF;
2915 CLOSE chk_team_cur;
2916 OPEN chk_grp_cur (chk_type_rec.role_resource_id ,
2917 chk_type_rec.role_id,
2918 chk_type_rec.start_date_active,
2919 chk_type_rec.end_date_active);
2920 FETCH chk_grp_cur INTO chk_grp_rec;
2921 IF(chk_grp_cur%FOUND) THEN
2922 fnd_message.set_name ('JTF', 'JTF_RS_MEM_ROLE_EXIST_ERR');
2923 FND_MSG_PUB.add;
2924 CLOSE chk_grp_cur;
2925 RAISE fnd_api.g_exc_error;
2926 END IF;
2927 CLOSE chk_grp_cur;
2928 END IF;
2929
2930 END IF; -- end of chk_type_cur
2931 CLOSE chk_type_cur;
2932
2933
2934 --GET USER ID AND SYSDATE
2935 l_date := sysdate;
2936 l_user_id := NVL(FND_PROFILE.Value('USER_ID'), -1);
2937 l_login_id := NVL(FND_PROFILE.Value('LOGIN_ID'), -1);
2938
2939
2940 --call audit api for delete
2941 jtf_rs_role_relate_aud_pvt.delete_role_relate(
2942 P_API_VERSION => 1.0,
2943 P_INIT_MSG_LIST => p_init_msg_list,
2944 P_COMMIT => null,
2945 P_ROLE_RELATE_ID => l_role_relate_id,
2946 X_RETURN_STATUS => l_return_status,
2947 X_MSG_COUNT => l_msg_count,
2948 X_MSG_DATA => l_msg_data );
2949
2950 IF(l_return_status <> fnd_api.g_ret_sts_success)
2951 THEN
2952 --fnd_message.set_name ('JTF', 'JTF_RS_AUDIT_ERR');
2953 --FND_MSG_PUB.add;
2954 IF L_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
2955 RAISE FND_API.G_EXC_ERROR;
2956 ELSIF L_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
2957 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2958 END IF;
2959
2960 END IF;
2961
2962
2963 --call update api to set the delete flag to 'Y'
2964 jtf_rs_role_relations_pkg.update_row(
2965 X_ROLE_RELATE_ID => l_role_relate_id,
2966 X_ATTRIBUTE9 => l_attribute9,
2967 X_ATTRIBUTE10 => l_attribute10,
2968 X_ATTRIBUTE11 => l_attribute11,
2969 X_ATTRIBUTE12 => l_attribute12,
2970 X_ATTRIBUTE13 => l_attribute13,
2971 X_ATTRIBUTE14 => l_attribute14,
2972 X_ATTRIBUTE15 => l_attribute15,
2973 X_ATTRIBUTE_CATEGORY => l_attribute_category,
2974 X_ROLE_RESOURCE_TYPE => chk_type_rec.role_resource_type,
2975 X_ROLE_RESOURCE_ID => chk_type_rec.role_resource_id,
2976 X_ROLE_ID => chk_type_rec.role_id,
2977 X_START_DATE_ACTIVE => chk_type_rec.start_date_active,
2978 X_END_DATE_ACTIVE => chk_type_rec.end_date_active,
2979 X_DELETE_FLAG => 'Y',
2980 X_OBJECT_VERSION_NUMBER => chk_type_rec.object_version_number ,
2981 X_ATTRIBUTE2 => l_attribute2,
2982 X_ATTRIBUTE3 => l_attribute3,
2983 X_ATTRIBUTE4 => l_attribute4,
2984 X_ATTRIBUTE5 => l_attribute5,
2985 X_ATTRIBUTE6 => l_attribute6,
2986 X_ATTRIBUTE7 => l_attribute7,
2987 X_ATTRIBUTE8 => l_attribute8,
2988 X_ATTRIBUTE1 => l_attribute1,
2989 X_LAST_UPDATE_DATE => l_date,
2990 X_LAST_UPDATED_BY => l_user_id,
2991 X_LAST_UPDATE_LOGIN => l_login_id );
2992
2993
2994
2995 IF(chk_type_rec.role_resource_type = 'RS_GROUP_MEMBER')
2996 THEN
2997 -- get the group id of the member
2998 open get_group_cur(l_role_relate_id);
2999 fetch get_group_cur into l_group_id;
3000 close get_group_cur;
3001
3002 --get no of children for the group
3003 BEGIN
3004 open get_child_cur(l_group_id);
3005 fetch get_child_cur into l_child_cnt;
3006 close get_child_cur;
3007 EXCEPTION
3008 WHEN OTHERS THEN
3009 l_child_cnt := 101; -- use concurrent program
3010 END;
3011
3012 if (nvl(l_child_cnt, 0) > 100)
3013 then
3014 begin
3015 insert into jtf_rs_chgd_role_relations
3016 (role_relate_id,
3017 role_resource_type,
3018 role_resource_id,
3019 role_id,
3020 start_date_active,
3021 end_date_active,
3022 delete_flag,
3023 operation_flag,
3024 created_by,
3025 creation_date,
3026 last_updated_by,
3027 last_update_date,
3028 last_update_login)
3029 values(
3030 l_role_relate_id,
3031 chk_type_rec.role_resource_type,
3032 chk_type_rec.role_resource_id,
3033 chk_type_rec.role_id,
3034 chk_type_rec.start_date_active,
3035 chk_type_rec.end_date_active,
3036 'Y',
3037 'D',
3038 l_user_id,
3039 l_date,
3040 l_user_id,
3041 l_date,
3042 l_login_id);
3043
3044 exception
3045 when others then
3046 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
3047 fnd_message.set_token('P_SQLCODE',SQLCODE);
3048 fnd_message.set_token('P_SQLERRM',SQLERRM);
3049 fnd_message.set_token('P_API_NAME', l_api_name);
3050 FND_MSG_PUB.add;
3051 RAISE fnd_api.g_exc_unexpected_error;
3052
3053
3054 end;
3055
3056
3057 --call concurrent program
3058
3059 begin
3060 l_request := fnd_request.submit_request(APPLICATION => 'JTF',
3061 PROGRAM => 'JTFRSRMG');
3062 open conc_prog_cur;
3063 fetch conc_prog_cur into g_name;
3064 close conc_prog_cur;
3065
3066 fnd_message.set_name ('JTF', 'JTF_RS_CONC_START');
3067 fnd_message.set_token('P_NAME',g_name);
3068 fnd_message.set_token('P_ID',l_request);
3069 FND_MSG_PUB.add;
3070
3071 exception when others then
3072 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
3073 fnd_message.set_token('P_SQLCODE',SQLCODE);
3074 fnd_message.set_token('P_SQLERRM',SQLERRM);
3075 fnd_message.set_token('P_API_NAME', l_api_name);
3076 FND_MSG_PUB.add;
3077
3078 RAISE fnd_api.g_exc_unexpected_error;
3079 end;
3080
3081 else
3082
3083
3084 --call to delete records in jtf_rs_rep_managers
3085 JTF_RS_REP_MGR_DENORM_PVT.DELETE_MEMBERS
3086 ( P_API_VERSION => 1.0,
3087 P_INIT_MSG_LIST => p_init_msg_list,
3088 P_COMMIT => null,
3089 P_ROLE_RELATE_ID => l_role_relate_id,
3090 X_RETURN_STATUS => l_return_status,
3091 X_MSG_COUNT => l_msg_count,
3092 X_MSG_DATA => l_msg_data);
3093
3094 IF(l_return_status <> fnd_api.g_ret_sts_success)
3095 THEN
3096 IF L_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3097 RAISE FND_API.G_EXC_ERROR;
3098 ELSIF L_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3099 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3100 END IF;
3101
3102 END IF;
3103 END IF; -- END OF COUNT CHECK
3104 END IF;
3105
3106 -- user hook calls for customer
3107 -- Customer post- processing section - mandatory
3108 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'DELETE_RESOURCE_ROLE_RELATE', 'A', 'C' ))
3109 then
3110 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'DELETE_RESOURCE_ROLE_RELATE', 'A', 'C' ))
3111 then
3112 JTF_RS_ROLE_RELATE_CUHK.DELETE_RES_ROLE_RELATE_POST(P_ROLE_RELATE_ID => p_role_relate_id,
3113 P_OBJECT_VERSION_NUM => p_object_version_num,
3114 p_data => L_data,
3115 p_count => L_count,
3116 P_return_code => l_return_code);
3117 if ( l_return_code <> FND_API.G_RET_STS_SUCCESS) then
3118 fnd_message.set_name ('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
3119 FND_MSG_PUB.add;
3120 IF l_return_code = FND_API.G_RET_STS_ERROR THEN
3121 RAISE FND_API.G_EXC_ERROR;
3122 ELSIF l_return_code = FND_API.G_RET_STS_UNEXP_ERROR THEN
3123 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3124 END IF;
3125 end if;
3126 end if;
3127 end if;
3128
3129 /* Verticle industry post- processing section - mandatory */
3130
3131 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'DELETE_RESOURCE_ROLE_RELATE', 'A', 'V' ))
3132 then
3133 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'DELETE_RESOURCE_ROLE_RELATE', 'A', 'V' ))
3134 then
3135
3136
3137 JTF_RS_ROLE_RELATE_VUHK.DELETE_RES_ROLE_RELATE_POST(P_ROLE_RELATE_ID => p_role_relate_id,
3138 P_OBJECT_VERSION_NUM => p_object_version_num,
3139 p_data => L_data,
3140 p_count => L_count,
3141 P_return_code => l_return_code);
3142 if ( l_return_code <> FND_API.G_RET_STS_SUCCESS) then
3143 fnd_message.set_name ('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
3144 FND_MSG_PUB.add;
3145 IF l_return_code = FND_API.G_RET_STS_ERROR THEN
3146 RAISE FND_API.G_EXC_ERROR;
3147 ELSIF l_return_code = FND_API.G_RET_STS_UNEXP_ERROR THEN
3148 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3149 END IF;
3150 end if;
3151 end if;
3152 end if;
3153
3154
3155 /* Internal post- processing section - mandatory */
3156
3157 if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'DELETE_RESOURCE_ROLE_RELATE', 'A', 'I' ))
3158 then
3159 if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_ROLE_RELATE_PVT', 'DELETE_RESOURCE_ROLE_RELATE', 'A', 'I' ))
3160 then
3161
3162
3163 JTF_RS_ROLE_RELATE_IUHK.DELETE_RES_ROLE_RELATE_POST(P_ROLE_RELATE_ID => p_role_relate_id,
3164 P_OBJECT_VERSION_NUM => p_object_version_num,
3165 p_data => L_data,
3166 p_count => L_count,
3167 P_return_code => l_return_code);
3168 if ( l_return_code <> FND_API.G_RET_STS_SUCCESS) then
3169 fnd_message.set_name ('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
3170 FND_MSG_PUB.add;
3171 IF l_return_code = FND_API.G_RET_STS_ERROR THEN
3172 RAISE FND_API.G_EXC_ERROR;
3173 ELSIF l_return_code = FND_API.G_RET_STS_UNEXP_ERROR THEN
3174 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3175 END IF;
3176 end if;
3177 end if;
3178 end if;
3179
3180 IF jtf_resource_utl.ok_to_execute(
3181 'JTF_RS_ROLE_RELATE_PVT',
3182 'DELETE_RESOURCE_ROLE_RELATE',
3183 'M',
3184 'M')
3185 THEN
3186 IF jtf_usr_hks.ok_to_execute(
3187 'JTF_RS_ROLE_RELATE_PVT',
3188 'DELETE_RESOURCE_ROLE_RELATE',
3189 'M',
3190 'M')
3191 THEN
3192
3193 IF (jtf_rs_role_relate_cuhk.ok_to_generate_msg(
3194 p_role_relate_id => p_role_relate_id,
3195 x_return_status => x_return_status) )
3196 THEN
3197
3198 /* Get the bind data id for the Business Object Instance */
3199
3200 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
3201
3202
3203 /* Set bind values for the bind variables in the Business Object
3204 SQL */
3205
3206 jtf_usr_hks.load_bind_data(l_bind_data_id, 'role_relate_id',
3207 p_role_relate_id, 'S', 'N');
3208
3209
3210 /* Call the message generation API */
3211
3212 jtf_usr_hks.generate_message(
3213 p_prod_code => 'JTF',
3214 p_bus_obj_code => 'RS_RRL',
3215 p_action_code => 'D', /* I/U/D */
3216 p_bind_data_id => l_bind_data_id,
3217 x_return_code => x_return_status);
3218
3219
3220 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3221 --x_return_status := fnd_api.g_ret_sts_error;
3222
3223 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
3224 fnd_msg_pub.add;
3225
3226 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3227 RAISE FND_API.G_EXC_ERROR;
3228 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3229 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3230 END IF;
3231
3232 END IF;
3233
3234 END IF;
3235
3236 END IF;
3237 END IF;
3238
3239
3240 -- end of user hook call
3241
3242 IF fnd_api.to_boolean (p_commit)
3243 THEN
3244 COMMIT WORK;
3245 END IF;
3246
3247 /* Calling publish API to raise delete resource role relation event. */
3248 /* added by baianand on 11/09/2002 */
3249
3250 begin
3251 jtf_rs_wf_events_pub.delete_resource_role_relate
3252 (p_api_version => 1.0
3253 ,p_init_msg_list => fnd_api.g_false
3254 ,p_commit => fnd_api.g_false
3255 ,p_role_relate_id => l_role_relate_id
3256 ,x_return_status => l_return_status
3257 ,x_msg_count => l_msg_count
3258 ,x_msg_data => l_msg_data);
3259
3260 EXCEPTION when others then
3261 null;
3262 end;
3263
3264 /* End of publish API call */
3265
3266 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3267
3268 EXCEPTION
3269 WHEN fnd_api.g_exc_error THEN
3270 ROLLBACK TO ROLE_RELATE_SP;
3271 x_return_status := fnd_api.g_ret_sts_error;
3272 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
3273 p_data => x_msg_data);
3274 WHEN fnd_api.g_exc_unexpected_error THEN
3275 ROLLBACK TO ROLE_RELATE_SP;
3276 x_return_status := fnd_api.g_ret_sts_unexp_error;
3277 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
3278 p_data => x_msg_data);
3279 WHEN OTHERS THEN
3280 ROLLBACK TO ROLE_RELATE_SP;
3281 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
3282 fnd_message.set_token('P_SQLCODE',SQLCODE);
3283 fnd_message.set_token('P_SQLERRM',SQLERRM);
3284 fnd_message.set_token('P_API_NAME', l_api_name);
3285 FND_MSG_PUB.add;
3286 x_return_status := fnd_api.g_ret_sts_unexp_error;
3287 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
3288 p_data => x_msg_data);
3289
3290 END delete_resource_role_relate;
3291
3292 END jtf_rs_role_relate_pvt;