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