[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_RESOURCE_UTL_PUB
Source
1 PACKAGE BODY jtf_rs_resource_utl_pub AS
2 /* $Header: jtfrspnb.pls 120.2 2006/05/11 12:02:55 haradhak ship $ */
3
4 /*****************************************************************************************
5 ******************************************************************************************/
6
7 PROCEDURE end_date_employee
8 (P_API_VERSION IN NUMBER,
9 P_INIT_MSG_LIST IN VARCHAR2,
10 P_COMMIT IN VARCHAR2,
11 P_RESOURCE_ID IN NUMBER,
12 P_END_DATE_ACTIVE IN DATE,
13 X_OBJECT_VER_NUMBER IN OUT NOCOPY NUMBER,
14 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
15 X_MSG_COUNT OUT NOCOPY NUMBER,
16 X_MSG_DATA OUT NOCOPY VARCHAR2 )
17 IS
18 l_api_version CONSTANT NUMBER := 1.0;
19 l_api_name CONSTANT VARCHAR2(30) := 'END_DATE_EMPLOYEE';
20 L_RETURN_STATUS VARCHAR2(2);
21 L_MSG_COUNT NUMBER;
22 L_MSG_DATA VARCHAR2(2000);
23 l_resource_id NUMBER;
24
25 l_fnd_date date;
26 l_end_date_active date;
27 end_date_active date;
28 l_object_version_num_res number;
29
30 l_updated_by number;
31 CURSOR term_res_cur(l_resource_id number)
32 IS
33 SELECT rsc.resource_id
34 , rsc.resource_number
35 , rsc.source_id
36 , rsc.object_version_number
37 , rsc.start_date_active
38 , rsc.end_date_active
39 FROM jtf_rs_resource_extns rsc
40 WHERE rsc.resource_id = l_resource_id;
41
42 term_res_rec term_res_cur%rowtype;
43
44
45 --cursor to get group member roles for the resource
46 CURSOR res_role_cur(l_role_resource_id JTF_RS_ROLE_RELATIONS.ROLE_RESOURCE_ID%TYPE)
47 IS
48 SELECT rlt.role_relate_id
49 , rlt.start_date_active
50 , rlt.end_date_active
51 , rlt.object_version_number
52 FROM jtf_rs_role_relations rlt
53 WHERE rlt.role_resource_id = l_role_resource_id
54 AND rlt.role_resource_type = 'RS_INDIVIDUAL'
55 AND nvl(rlt.delete_flag, 'N') <> 'Y'
56 -- AND nvl(rlt.end_date_active,trunc(sysdate)) >= trunc(sysdate);
57 AND nvl(rlt.end_date_active, l_fnd_date) > p_end_date_active;
58
59 res_role_rec res_role_cur%rowtype;
60
61
62 --cursor to get salesreps
63 CURSOR res_srp_cur(l_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE)
64 IS
65 SELECT res.salesrep_id
66 , res.org_id
67 , res.start_date_active
68 , res.end_date_active
69 , res.object_version_number
70 , res.sales_credit_type_id
71 FROM jtf_rs_salesreps res
72 WHERE res.resource_id = l_resource_id;
73
74 res_srp_rec res_srp_cur%rowtype;
75
76 --cursor to get salesrep territories
77 CURSOR res_srp_terr_cur(l_salesrep_id JTF_RS_SALESREPS.SALESREP_ID%TYPE)
78 IS
79 SELECT terr.salesrep_id
80 ,terr.territory_id
81 ,terr.salesrep_territory_id
82 ,terr.start_date_active
83 ,terr.end_date_active
84 ,terr.object_version_number
85 FROM ra_salesrep_territories terr
86 WHERE terr.salesrep_id = l_salesrep_id;
87
88 res_srp_terr_rec res_srp_terr_cur%rowtype;
89
90 --cursor to get overlap salesrep territories
91 CURSOR res_srp_terr_dup_cur(c_start_date_active ra_salesrep_territories.start_date_active%type,
92 c_end_date_active ra_salesrep_territories.end_date_active%type,
93 c_salesrep_id ra_salesrep_territories.salesrep_id%type,
94 c_territory_id ra_salesrep_territories.territory_id%type,
95 c_salesrep_territory_id ra_salesrep_territories.salesrep_territory_id%type)
96 IS
97 SELECT salesrep_territory_id
98 FROM ra_salesrep_territories
99 WHERE salesrep_id = c_salesrep_id
100 AND territory_id = c_territory_id
101 and salesrep_territory_id <> c_salesrep_territory_id
102 AND (c_start_date_active between start_date_active and (nvl(end_date_active, l_fnd_date))
103 OR (nvl(c_end_date_active, l_fnd_date) between start_date_active and nvl(end_date_active, l_fnd_date))
104 OR (c_start_date_active < start_date_active and nvl(c_end_date_active, l_fnd_date) > nvl(end_date_active, l_fnd_date))
105 );
106
107 res_srp_terr_dup_rec res_srp_terr_dup_cur%rowtype;
108
109 --cursor to get team memebr roles for the resource
110 CURSOR res_team_cur(l_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE)
111 IS
112 SELECT rlt.role_relate_id
113 , rlt.start_date_active
114 , rlt.end_date_active
115 , rlt.object_version_number
116 FROM jtf_rs_role_relations rlt
117 , jtf_rs_team_members mem
118 WHERE mem.team_resource_id = l_resource_id
119 AND mem.resource_type = 'INDIVIDUAL'
120 AND nvl(mem.delete_flag, 'N') <> 'Y'
121 AND rlt.role_resource_id = mem.team_member_id
122 AND rlt.role_resource_type = 'RS_TEAM_MEMBER'
123 AND nvl(rlt.delete_flag ,'N') <> 'Y'
124 -- AND nvl(rlt.end_date_active,trunc(sysdate)) >= trunc(sysdate);
125 AND nvl(rlt.end_date_active, l_fnd_date) > p_end_date_active;
126
127 res_team_rec res_team_cur%rowtype;
128
129 --cursor to get roles for the resource
130 CURSOR res_group_cur(l_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE)
131 IS
132 SELECT rlt.role_relate_id
133 , rlt.start_date_active
134 , rlt.end_date_active
135 , rlt.object_version_number
136 FROM jtf_rs_role_relations rlt
137 , jtf_rs_group_members mem
138 WHERE mem.resource_id = l_resource_id
139 AND nvl(mem.delete_flag, 'N') <> 'Y'
140 AND rlt.role_resource_id = mem.group_member_id
141 AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
142 AND nvl(rlt.delete_flag ,'N') <> 'Y'
143 -- AND nvl(rlt.end_date_active,trunc(sysdate)) >= trunc(sysdate);
144 AND nvl(rlt.end_date_active, l_fnd_date) > p_end_date_active;
145
146
147 res_group_rec res_group_cur%rowtype;
148
149 i NUMBER;
150 l_value varchar2(2000);
151 l_count number;
152
153 BEGIN
154
155 l_fnd_date := to_date(to_char(fnd_api.g_miss_date, 'DD-MM-RRRR'), 'DD-MM-RRRR');
156 end_date_active := to_date(to_char(p_end_date_active, 'DD-MM-RRRR'), 'DD-MM-RRRR');
157 l_updated_by := jtf_resource_utl.updated_by;
158
159 --Standard Start of API SAVEPOINT
160 SAVEPOINT res_save;
161
162 l_return_status := fnd_api.g_ret_sts_success;
163 l_count := 0;
164 IF FND_API.To_boolean(P_INIT_MSG_LIST)
165 THEN
166 FND_MSG_PUB.Initialize;
167 END IF;
168 open term_res_cur(p_resource_id);
169 fetch term_res_cur into term_res_rec;
170 if(term_res_cur%found)
171 then
172
173 IF(nvl(trunc(term_res_rec.end_date_active), l_fnd_date) >
174 nvl(trunc(p_end_date_active), l_fnd_date))
175 THEN
176
177 --get all team member roles to be terminated
178 open res_team_cur(p_resource_id);
179 fetch res_team_cur INTO res_team_rec;
180 WHILE(res_team_cur%FOUND)
181 LOOP
182
183 l_return_status := fnd_api.g_ret_sts_success;
184 l_end_date_active := to_date(to_char(res_team_rec.end_date_active, 'DD-MM-RRRR'), 'DD-MM-RRRR');
185 --if start date > sysdate -1 then delete the role
186 IF(trunc(res_team_rec.start_date_active) > trunc(p_end_date_active) )
187 THEN
188 --call delete role relate api
189 jtf_rs_role_relate_pub.delete_resource_role_relate
190 ( P_API_VERSION => 1.0,
191 P_ROLE_RELATE_ID => res_team_rec.role_relate_id,
192 P_OBJECT_VERSION_NUM => res_team_rec.object_version_number,
193 X_RETURN_STATUS => l_return_status,
194 X_MSG_COUNT => l_msg_count,
195 X_MSG_DATA => l_msg_data);
196
197 ELSIF(nvl(l_end_date_active, l_fnd_date)
198 >= nvl(end_date_active, l_fnd_date))
199 THEN
200 --update end date with p_end_date_active -1 call update role relate api
201 jtf_rs_role_relate_pub.update_resource_role_relate
202 ( P_API_VERSION => 1.0,
203 P_ROLE_RELATE_ID => res_team_rec.role_relate_id,
204 P_END_DATE_ACTIVE => trunc(p_end_date_active) ,
205 P_OBJECT_VERSION_NUM => res_team_rec.object_version_number,
206 X_RETURN_STATUS => l_return_status,
207 X_MSG_COUNT => l_msg_count,
208 X_MSG_DATA => l_msg_data);
209
210 END IF; -- end of start_date check
211
212 if ( l_return_status <> fnd_api.g_ret_sts_success)
213 then
214 raise fnd_api.g_exc_error;
215 END IF;
216 FETCH res_team_cur INTO res_team_rec;
217 END LOOP; -- end of res_team_cur
218 CLOSE res_team_cur;
219
220 --get all group member roles to be terminated
221 open res_group_cur(p_resource_id);
222 fetch res_group_cur INTO res_group_rec;
223 WHILE(res_group_cur%FOUND)
224 LOOP
225 l_end_date_active := to_date(to_char(res_group_rec.end_date_active, 'DD-MM-RRRR'), 'DD-MM-RRRR');
226 IF(trunc(res_group_rec.start_date_active) > trunc(p_end_date_active))
227 THEN
228 --call delete role relate api
229 jtf_rs_role_relate_pub.delete_resource_role_relate
230 ( P_API_VERSION => 1.0,
231 P_ROLE_RELATE_ID => res_group_rec.role_relate_id,
232 P_OBJECT_VERSION_NUM => res_group_rec.object_version_number,
233 X_RETURN_STATUS => l_return_status,
234 X_MSG_COUNT => l_msg_count,
235 X_MSG_DATA => l_msg_data);
236
237 ELSIF(nvl(l_end_date_active, l_fnd_date)
238 >= nvl(end_date_active, l_fnd_date))
239 THEN
240 --update end date with p_end_date_active -1 call update role relate api
241 jtf_rs_role_relate_pub.update_resource_role_relate
242 ( P_API_VERSION => 1.0,
243 P_ROLE_RELATE_ID => res_group_rec.role_relate_id,
244 P_END_DATE_ACTIVE => trunc(p_end_date_active) ,
245 P_OBJECT_VERSION_NUM => res_group_rec.object_version_number,
246 X_RETURN_STATUS => l_return_status,
247 X_MSG_COUNT => l_msg_count,
248 X_MSG_DATA => l_msg_data);
249
250 END IF; -- end of start_date check
251 if ( l_return_status <> fnd_api.g_ret_sts_success)
252 then
253 raise fnd_api.g_exc_error;
254 END IF;
255 FETCH res_group_cur INTO res_group_rec;
256 END LOOP; -- end of res_group_cur
257 CLOSE res_group_cur;
258
259 --terminate the roles for the resource
260 open res_role_cur(p_resource_id);
261 fetch res_role_cur INTO res_role_rec;
262 WHILE(res_role_cur%FOUND)
263 LOOP
264 l_end_date_active := to_date(to_char(res_role_rec.end_date_active, 'DD-MM-RRRR'), 'DD-MM-RRRR');
265 --if start date > sysdate -1 then delete the role
266 IF(trunc(res_role_rec.start_date_active) > trunc(p_end_date_active))
267 THEN
268 --call delete role relate api
269 jtf_rs_role_relate_pub.delete_resource_role_relate
270 ( P_API_VERSION => 1.0,
271 P_ROLE_RELATE_ID => res_role_rec.role_relate_id,
272 P_OBJECT_VERSION_NUM => res_role_rec.object_version_number,
273 X_RETURN_STATUS => l_return_status,
274 X_MSG_COUNT => l_msg_count,
275 X_MSG_DATA => l_msg_data ) ;
276 ELSIF(nvl(l_end_date_active, l_fnd_date)
277 >= nvl(end_date_active, l_fnd_date))
278 THEN
279 --update end date with sysdate -1 call update role relate api
280 jtf_rs_role_relate_pub.update_resource_role_relate
281 ( P_API_VERSION => 1.0,
282 P_ROLE_RELATE_ID => res_role_rec.role_relate_id,
283 P_END_DATE_ACTIVE => trunc(p_end_date_active) ,
284 P_OBJECT_VERSION_NUM => res_role_rec.object_version_number,
285 X_RETURN_STATUS => l_return_status,
286 X_MSG_COUNT => l_msg_count,
287 X_MSG_DATA => l_msg_data ) ;
288
289 END IF; -- end of start_date check
290
291
292 if ( l_return_status <> fnd_api.g_ret_sts_success)
293 then
294 raise fnd_api.g_exc_error;
295 END IF;
296
297
298
299 FETCH res_role_cur INTO res_role_rec;
300 END LOOP; -- end of res_role_cur
301 CLOSE res_role_cur;
302
303 --terminate the salesrep for the resource
304 open res_srp_cur(p_resource_id);
305 fetch res_srp_cur INTO res_srp_rec;
306
307 WHILE(res_srp_cur%FOUND)
308 LOOP
309
310 --terminate the salesrep territories for the resource
311 open res_srp_terr_cur(res_srp_rec.salesrep_id);
312 fetch res_srp_terr_cur INTO res_srp_terr_rec;
313
314 WHILE(res_srp_terr_cur%FOUND)
315 LOOP
316 IF(res_srp_terr_rec.start_date_active > trunc(p_end_date_active)) THEN
317 open res_srp_terr_dup_cur(trunc(p_end_date_active - 1),
318 trunc(p_end_date_active),
319 res_srp_rec.salesrep_id,
320 res_srp_terr_rec.territory_id,
321 res_srp_terr_rec.salesrep_territory_id);
322 fetch res_srp_terr_dup_cur INTO res_srp_terr_dup_rec;
323 IF res_srp_terr_dup_cur%FOUND THEN
324 fnd_message.set_name ('JTF','JTF_RS_DUP_TERR');
325 fnd_msg_pub.add;
326 CLOSE res_srp_terr_dup_cur;
327 raise fnd_api.g_exc_error;
328 END IF;
329 CLOSE res_srp_terr_dup_cur;
330
331 update ra_salesrep_territories
332 set start_date_active = trunc(p_end_date_active - 1),
333 end_date_active = trunc(p_end_date_active),
334 object_version_number = object_version_number + 1,
335 last_update_date = sysdate,
336 last_updated_by = l_updated_by
337 where salesrep_territory_id = res_srp_terr_rec.salesrep_territory_id;
338 ELSIF(nvl(res_srp_terr_rec.end_date_active, l_fnd_date) >= nvl(p_end_date_active, l_fnd_date)) THEN
339 update ra_salesrep_territories
340 set end_date_active = trunc(p_end_date_active),
341 object_version_number = object_version_number + 1,
342 last_update_date = sysdate,
343 last_updated_by = l_updated_by
344 where salesrep_territory_id = res_srp_terr_REC.SALEsrep_territory_id;
345 END IF; -- end of start_date check
346
347 FETCH res_srp_terr_cur INTO res_srp_terr_rec;
348 END LOOP; -- end of res_srp_terr_cur
349 CLOSE res_srp_terr_cur;
350
351 l_end_date_active := to_date(to_char(res_srp_rec.end_date_active, 'DD-MM-RRRR'), 'DD-MM-RRRR');
352 IF(res_srp_rec.start_date_active > trunc(p_end_date_active))
353 THEN
354 --update to sydate - 2 and sysdate -1
355 jtf_rs_salesreps_pub.update_salesrep
356 ( P_API_VERSION => 1.0,
357 P_SALESREP_ID => res_srp_rec.salesrep_id,
361 P_END_DATE_ACTIVE => trunc(p_end_date_active ) ,
358 P_ORG_ID => res_srp_rec.org_id,
359 P_SALES_CREDIT_TYPE_ID => res_srp_rec.sales_credit_type_id,
360 P_START_DATE_ACTIVE => trunc(p_end_date_active - 1) ,
362 P_OBJECT_VERSION_NUMBER => res_srp_rec.object_version_number,
363 X_RETURN_STATUS => l_return_status,
364 X_MSG_COUNT => l_msg_count,
365 X_MSG_DATA => l_msg_data ) ;
366
367
368 ELSIF(nvl(l_end_date_active, l_fnd_date)
369 >= nvl(end_date_active, l_fnd_date))
370 THEN
371
372 --update end date with sysdate -1 call update role relate api
373 jtf_rs_salesreps_pub.update_salesrep
374 ( P_API_VERSION => 1.0,
375 P_SALESREP_ID => res_srp_rec.salesrep_id,
376 P_ORG_ID => res_srp_rec.org_id,
377 P_SALES_CREDIT_TYPE_ID => res_srp_rec.sales_credit_type_id,
378 P_END_DATE_ACTIVE => trunc(p_end_date_active ) ,
379 P_OBJECT_VERSION_NUMBER => res_srp_rec.object_version_number,
380 X_RETURN_STATUS => l_return_status,
381 X_MSG_COUNT => l_msg_count,
382 X_MSG_DATA => l_msg_data ) ;
383 END IF; -- end of start_date check
384 if ( l_return_status <> fnd_api.g_ret_sts_success)
385 then
386 raise fnd_api.g_exc_error;
387 END IF;
388
389
390
391 FETCH res_srp_cur INTO res_srp_rec;
392 END LOOP; -- end of res_srp_cur
393 CLOSE res_srp_cur;
394
395
396 END IF; -- end of terminate employee
397 ---------------------------------------------------
398 l_object_version_num_res := term_res_rec.object_version_number;
399
400 IF(term_res_rec.start_date_active >= trunc(p_end_date_active + 1))
401 THEN
402
403 --for future dated resources terminate it anyway
404 jtf_rs_resource_pub.update_resource
405 (P_API_VERSION => 1,
406 P_INIT_MSG_LIST => fnd_api.g_true,
407 P_COMMIT => fnd_api.g_false,
408 P_RESOURCE_ID => term_res_rec.resource_id,
409 P_RESOURCE_NUMBER => term_res_rec.resource_number,
410 P_START_DATE_ACTIVE => trunc(p_end_date_active - 1) ,
411 P_END_DATE_ACTIVE => trunc(p_end_date_active) ,
412 P_OBJECT_VERSION_NUM => l_object_version_num_res,
413 X_RETURN_STATUS => l_return_status,
414 X_MSG_COUNT => l_msg_count,
415 X_MSG_DATA => l_msg_data) ;
416
417 ELSE
418
419 --put end_date to p_end_date_active
420 jtf_rs_resource_pub.update_resource
421 ( P_API_VERSION => 1,
422 P_INIT_MSG_LIST => fnd_api.g_true,
423 P_COMMIT => fnd_api.g_false,
424 P_RESOURCE_ID => term_res_rec.resource_id,
425 P_RESOURCE_NUMBER => term_res_rec.resource_number,
426 P_END_DATE_ACTIVE => trunc(p_end_date_active) ,
427 P_OBJECT_VERSION_NUM => l_object_version_num_res,
428 X_RETURN_STATUS => l_return_status,
429 X_MSG_COUNT => l_msg_count,
430 X_MSG_DATA => l_msg_data) ;
431
432 END IF; -- end of terminate employee
433
434 x_object_ver_number := l_object_version_num_res;
435
436 if ( l_return_status <> fnd_api.g_ret_sts_success)
437 then
438 raise fnd_api.g_exc_error;
439 END IF;
440 --------------------------------------------------
441
442 end if; -- end of term_res_cur
443
444 close term_res_cur;
445
446 FND_MSG_PUB.count_and_get (p_count => l_msg_count, p_data => l_msg_data);
447
448 EXCEPTION
449 WHEN fnd_api.g_exc_unexpected_error
450 THEN
451 ROLLBACK TO res_save;
452 x_return_status := fnd_api.g_ret_sts_error;
453 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
454 WHEN fnd_api.g_exc_error
455 THEN
456 ROLLBACK TO res_save;
457 x_return_status := fnd_api.g_ret_sts_error;
458 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
459
460 WHEN OTHERS
461 THEN
462 ROLLBACK TO res_save;
463 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
464 fnd_message.set_token('P_SQLCODE',SQLCODE);
465 fnd_message.set_token('P_SQLERRM',SQLERRM);
466 fnd_message.set_token('P_API_NAME',l_api_name);
467 FND_MSG_PUB.add;
468 x_return_status := fnd_api.g_ret_sts_unexp_error;
469 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
470 END end_date_employee;
471
472 PROCEDURE add_message
473 (P_API_VERSION IN NUMBER,
474 P_MESSAGE_CODE IN VARCHAR2,
475 P_TOKEN1_NAME IN VARCHAR2,
476 P_TOKEN1_VALUE IN VARCHAR2,
477 P_TOKEN2_NAME IN VARCHAR2,
481 X_MSG_DATA OUT NOCOPY VARCHAR2
478 P_TOKEN2_VALUE IN VARCHAR2,
479 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
480 X_MSG_COUNT OUT NOCOPY NUMBER,
482 )
483 AS
484 l_api_name VARCHAR2(30);
485 BEGIN
486 l_api_name := 'ADD_MESSAGE';
487 x_return_status := fnd_api.g_ret_sts_success;
488 FND_MSG_PUB.Initialize;
489 if(P_MESSAGE_CODE is not null)
490 then
491 fnd_message.set_name ('JTF', p_message_code);
492 if((P_TOKEN1_NAME is not null) OR (P_TOKEN1_NAME <> fnd_api.g_miss_char))
493 then
494 fnd_message.set_token (p_token1_name, p_token1_value);
495 end if;
496 if((P_TOKEN2_NAME is not null) OR (P_TOKEN2_NAME <> fnd_api.g_miss_char))
497 then
498 fnd_message.set_token (p_token2_name, p_token2_value);
499 end if;
500 FND_MSG_PUB.add;
501 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
502 end if;
503
504 EXCEPTION
505 WHEN OTHERS
506 THEN
507 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
508 fnd_message.set_token('P_SQLCODE',SQLCODE);
509 fnd_message.set_token('P_SQLERRM',SQLERRM);
510 fnd_message.set_token('P_API_NAME',l_api_name);
511 FND_MSG_PUB.add;
512 x_return_status := fnd_api.g_ret_sts_unexp_error;
513 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
514
515 END add_message ;
516
517 /* Function to check if user has resource update access */
518
519 Function Validate_Update_Access( p_resource_id number,
520 p_resource_user_id number default null
521 ) Return varchar2
522 IS
523 l_profile_value VARCHAR2(10);
524 l_user_id number;
525 l_resource_user_id number;
526
527 BEGIN
528
529 l_profile_value := nvl(FND_PROFILE.VALUE('JTF_RS_EMP_RES_UPD_ACCESS'),'SELF');
530 l_user_id := nvl(FND_PROFILE.VALUE('USER_ID'),-1);
531
532 IF (l_profile_value = 'SELF') THEN
533 IF (p_resource_user_id IS NULL) THEN
534 BEGIN
535 SELECT nvl(user_id,-99)
536 INTO l_resource_user_id
537 FROM jtf_rs_resource_extns
538 WHERE resource_id = p_resource_id;
539 EXCEPTION WHEN NO_DATA_FOUND THEN
540 l_resource_user_id := -99;
541 WHEN OTHERS THEN
542 l_resource_user_id := -98;
543 END;
544 ELSE
545 l_resource_user_id := p_resource_user_id;
546 END IF;
547
548 IF l_resource_user_id = l_user_id THEN
549 Return 'SELF';
550 ELSE
551 Return 'OTHERS';
552 END IF;
553
554 ELSIF (l_profile_value = 'ANY') THEN
555 Return 'ANY';
556 ELSE
557 Return 'OTHERS';
558 END IF;
559
560 END Validate_Update_Access;
561
562 /* Function to check if logged in user has access to Update Group Membership/Hierarchy */
563
564 FUNCTION Group_Update_Access( p_group_id IN number default null) RETURN VARCHAR2
565 IS
566
567 l_profile_value VARCHAR2(10);
568 l_user_id number;
569 l_resource_id number := 0;
570 l_mgr number := 0;
571
572 CURSOR parent_grp_cur(l_group_id number) IS
573 SELECT parent_group_id
574 FROM jtf_rs_groups_denorm
575 WHERE group_id = l_group_id
576 AND group_id <> parent_group_id
577 AND trunc(sysdate) between start_date_active and nvl(end_date_active, trunc(sysdate));
578
579 BEGIN
580
581 l_profile_value := nvl(FND_PROFILE.VALUE('JTF_RS_GROUP_UPD_ACCESS'),'NONE');
582 l_user_id := nvl(FND_PROFILE.VALUE('USER_ID'),-1);
583 -- l_profile_value := 'NONE';
584 -- l_user_id := 1351;
585
586 IF (l_profile_value = 'ALL') THEN
587 RETURN 'SU';
588 ELSIF ((l_profile_value = 'NONE') AND (p_group_id IS NOT NULL)) THEN
589 BEGIN
590 SELECT resource_id
591 INTO l_resource_id
592 FROM jtf_rs_resource_extns
593 WHERE user_id = l_user_id
594 AND rownum < 2;
595 EXCEPTION
596 WHEN NO_DATA_FOUND THEN
597 l_resource_id := 0;
598 RETURN('RO');
599 WHEN OTHERS THEN
600 l_resource_id := 0;
601 RETURN('RO');
602 END;
603
604 IF ((l_resource_id <> 0)) THEN
605 -- Check if user is active Manager/Admin of any acive parent group
606 FOR parent_grp_rec IN parent_grp_cur(p_group_id) LOOP
607 EXIT WHEN l_mgr = 1;
608 BEGIN
609 SELECT '1'
610 INTO l_mgr
611 FROM jtf_rs_roles_b c,
612 jtf_rs_role_relations b,
613 jtf_rs_group_members a
614 WHERE a.group_id = parent_grp_rec.parent_group_id
615 AND a.resource_id = l_resource_id
616 AND nvl(a.delete_flag, 'N') <> 'Y'
617 AND b.role_resource_id = a.group_member_id
618 AND trunc(sysdate) between b.start_date_active and nvl(b.end_date_active, trunc(sysdate))
619 AND b.role_resource_type = 'RS_GROUP_MEMBER'
620 AND nvl(b.delete_flag, 'N') <> 'Y'
624 AND rownum < 2 ;
621 AND c.role_id = b.role_id
622 AND 'Y' in (c.manager_flag, c.admin_flag)
623 AND c.active_flag = 'Y'
625 EXCEPTION
626 WHEN NO_DATA_FOUND THEN
627 l_mgr := 0;
628 WHEN OTHERS THEN
629 l_mgr := 0;
630 END;
631 END LOOP;
632
633 IF (l_mgr = 1) THEN
634 RETURN('FA');
635 ELSE
636 -- Check if user is active Manager/Admin of group being queried
637 BEGIN
638 SELECT '2'
639 INTO l_mgr
640 FROM jtf_rs_roles_b c,
641 jtf_rs_role_relations b,
642 jtf_rs_group_members a
643 WHERE a.group_id = p_group_id
644 AND a.resource_id = l_resource_id
645 AND nvl(a.delete_flag, 'N') <> 'Y'
646 AND b.role_resource_id = a.group_member_id
647 AND trunc(sysdate) between b.start_date_active and nvl(b.end_date_active, trunc(sysdate))
648 AND b.role_resource_type = 'RS_GROUP_MEMBER'
649 AND nvl(b.delete_flag, 'N') <> 'Y'
650 AND c.role_id = b.role_id
651 AND 'Y' in (c.manager_flag, c.admin_flag)
652 AND c.active_flag = 'Y'
653 AND rownum < 2 ;
654
655 IF (l_mgr = 2) THEN
656 RETURN('NPU');
657 ELSIF (l_mgr = 0) THEN
658 RETURN('RO');
659 END IF;
660
661 EXCEPTION
662 WHEN NO_DATA_FOUND THEN
663 l_mgr := 0;
664 RETURN('RO');
665 WHEN OTHERS THEN
666 l_mgr := 0;
667 RETURN('RO');
668 END;
669 END IF; -- End of l_mgr value check
670 ELSE -- Resource id is invalid (0)
671 RETURN('RO');
672 END IF; -- End of l_resource_id value check
673 ELSE -- Profile value is NONE but p_group_id is NULL
674 RETURN('RO');
675 END IF; -- End of profile value check
676
677 END Group_Update_Access;
678
679
680 /* Function to check if logged in user has access to Update role */
681
682 FUNCTION Role_Update_Access RETURN VARCHAR2
683 IS
684
685 l_profile_value VARCHAR2(10);
686
687 BEGIN
688
689 l_profile_value := nvl(FND_PROFILE.VALUE('JTF_RS_ROLE_UPD_ACCESS'),'NONE');
690 if l_profile_value = 'ALL' then
691 return 'FA';
692 else
693 return 'RO';
694 end if;
695
696 END Role_Update_Access;
697
698
699
700 /* Function to check if user is HR manager for this resource */
701
702 Function Is_HR_Manager( p_resource_id number
703 ) Return varchar2
704 IS
705 l_user_id number;
706
707 cursor mgr_usr_ids(p_res_id number) is
708 select user_id
709 from jtf_rs_resource_extns connect by
710 source_id = prior source_mgr_id
711 start with resource_id = p_res_id;
712
713 BEGIN
714
715 l_user_id := nvl(FND_PROFILE.VALUE('USER_ID'),-1);
716
717 for mgr_usr_id_rec in mgr_usr_ids(p_resource_id) loop
718 if (l_user_id = mgr_usr_id_rec.user_id) then
719 return 'Y';
720 end if;
721 end loop;
722
723 return 'N';
724 END Is_HR_Manager;
725
726 PROCEDURE end_date_group
727 (P_API_VERSION IN NUMBER,
728 P_INIT_MSG_LIST IN VARCHAR2,
729 P_COMMIT IN VARCHAR2,
730 P_GROUP_ID IN NUMBER,
731 P_END_DATE_ACTIVE IN DATE,
732 X_OBJECT_VER_NUMBER IN OUT NOCOPY NUMBER,
733 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
734 X_MSG_COUNT OUT NOCOPY NUMBER,
735 X_MSG_DATA OUT NOCOPY VARCHAR2 )
736 IS
737 l_api_version CONSTANT NUMBER := 1.0;
738 l_api_name CONSTANT VARCHAR2(30) := 'END_DATE_GROUP';
739 l_return_status VARCHAR2(2);
740 l_msg_count NUMBER;
741 l_msg_data VARCHAR2(2000);
742 l_group_id NUMBER;
743
744 l_fnd_date date;
745 l_end_date_active date;
746 end_date_active date;
747 l_object_version_num_grp number;
748
749 --cursor to get details about group needs to end date
750 CURSOR term_grp_cur(c_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE) IS
751 SELECT grp.group_id,
752 grp.group_number,
753 grp.object_version_number,
754 grp.start_date_active,
755 grp.end_date_active
756 FROM jtf_rs_groups_b grp
757 WHERE grp.group_id = c_group_id;
758
759 term_grp_rec term_grp_cur%rowtype;
760
761 --cursor to get all active member roles
762 CURSOR group_mem_roles_cur(c_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE,
763 c_group_end_date JTF_RS_GROUPS_B.END_DATE_ACTIVE%TYPE) IS
764 SELECT rlt.role_relate_id,
765 rlt.start_date_active,
766 rlt.end_date_active,
767 rlt.object_version_number
768 FROM jtf_rs_role_relations rlt,
769 jtf_rs_group_members mem
770 WHERE mem.group_id = c_group_id
771 AND nvl(mem.delete_flag, 'N') <> 'Y'
775 AND nvl(rlt.end_date_active, l_fnd_date) > c_group_end_date
772 AND rlt.role_resource_id = mem.group_member_id
773 AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
774 AND nvl(rlt.delete_flag ,'N') <> 'Y'
776 UNION ALL
777 SELECT rlt2.role_relate_id,
778 rlt2.start_date_active,
779 rlt2.end_date_active,
780 rlt2.object_version_number
781 FROM jtf_rs_role_relations rlt2
782 WHERE rlt2.role_resource_id = c_group_id
783 AND rlt2.role_resource_type = 'RS_GROUP'
784 AND nvl(rlt2.delete_flag ,'N') <> 'Y'
785 AND NVL(rlt2.end_date_active, l_fnd_date) > c_group_end_date;
786
787 group_mem_roles_rec group_mem_roles_cur%rowtype;
788
789 --cursor to get all active parent or child group relations
790 CURSOR grp_relations_cur(c_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE,
791 c_group_end_date JTF_RS_GROUPS_B.END_DATE_ACTIVE%TYPE) IS
792 SELECT group_relate_id,
793 group_id,
794 related_group_id,
795 start_date_active,
796 end_date_active,
797 object_version_number
798 FROM jtf_rs_grp_relations
799 WHERE nvl(delete_flag, 'N') <> 'Y'
800 AND group_id = c_group_id
801 AND nvl(end_date_active, l_fnd_date) > c_group_end_date
802 UNION ALL
803 SELECT group_relate_id,
804 group_id,
805 related_group_id,
806 start_date_active,
807 end_date_active,
808 object_version_number
809 FROM jtf_rs_grp_relations
810 WHERE nvl(delete_flag, 'N') <> 'Y'
811 AND related_group_id = c_group_id
812 AND nvl(end_date_active, l_fnd_date) > c_group_end_date;
813
814 grp_relations_rec grp_relations_cur%rowtype;
815
816 BEGIN
817
818 l_fnd_date := to_date(to_char(fnd_api.g_miss_date, 'DD-MM-RRRR'), 'DD-MM-RRRR');
819 end_date_active := to_date(to_char(p_end_date_active, 'DD-MM-RRRR'), 'DD-MM-RRRR');
820
821 --Standard Start of API SAVEPOINT
822 SAVEPOINT group_mem_roles_save;
823
824 l_return_status := fnd_api.g_ret_sts_success;
825
826 IF FND_API.To_boolean(P_INIT_MSG_LIST)
827 THEN
828 FND_MSG_PUB.Initialize;
829 END IF;
830
831 OPEN term_grp_cur(p_group_id);
832 FETCH term_grp_cur into term_grp_rec;
833 IF (term_grp_cur%found) THEN
834
835 -- If condition to check whether the new group end_date is before old end_date or old end_date is null.
836 IF(nvl(trunc(term_grp_rec.end_date_active), l_fnd_date) > nvl(trunc(p_end_date_active), l_fnd_date)) THEN
837
838 --get all group member roles to be terminated
839 open group_mem_roles_cur(p_group_id,p_end_date_active);
840 fetch group_mem_roles_cur INTO group_mem_roles_rec;
841 WHILE(group_mem_roles_cur%FOUND)
842 LOOP
843 l_end_date_active := to_date(to_char(group_mem_roles_rec.end_date_active, 'DD-MM-RRRR'), 'DD-MM-RRRR');
844 IF(trunc(group_mem_roles_rec.start_date_active) > trunc(p_end_date_active))
845 THEN
846 --call delete role relate api
847 jtf_rs_role_relate_pub.delete_resource_role_relate
848 ( P_API_VERSION => 1.0,
849 P_ROLE_RELATE_ID => group_mem_roles_rec.role_relate_id,
850 P_OBJECT_VERSION_NUM => group_mem_roles_rec.object_version_number,
851 X_RETURN_STATUS => l_return_status,
852 X_MSG_COUNT => l_msg_count,
853 X_MSG_DATA => l_msg_data);
854
855 ELSIF(nvl(l_end_date_active, l_fnd_date)
856 >= nvl(end_date_active, l_fnd_date))
857 THEN
858 --update end date with p_end_date_active call update role relate api
859 jtf_rs_role_relate_pub.update_resource_role_relate
860 ( P_API_VERSION => 1.0,
861 P_ROLE_RELATE_ID => group_mem_roles_rec.role_relate_id,
862 P_END_DATE_ACTIVE => trunc(p_end_date_active) ,
863 P_OBJECT_VERSION_NUM => group_mem_roles_rec.object_version_number,
864 X_RETURN_STATUS => l_return_status,
865 X_MSG_COUNT => l_msg_count,
866 X_MSG_DATA => l_msg_data);
867
868 END IF; -- end of start_date check
869 if ( l_return_status <> fnd_api.g_ret_sts_success)
870 then
871 raise fnd_api.g_exc_error;
872 END IF;
873 FETCH group_mem_roles_cur INTO group_mem_roles_rec;
874 END LOOP; -- end of group_mem_roles_cur
875 CLOSE group_mem_roles_cur;
876
877 --get all group relations to be terminated
878 open grp_relations_cur(p_group_id,p_end_date_active);
879 fetch grp_relations_cur INTO grp_relations_rec;
880 WHILE(grp_relations_cur%FOUND)
881 LOOP
882 l_end_date_active := to_date(to_char(grp_relations_rec.end_date_active, 'DD-MM-RRRR'), 'DD-MM-RRRR');
883 IF(trunc(grp_relations_rec.start_date_active) > trunc(p_end_date_active))
884 THEN
885 --call delete group relate api
886 jtf_rs_group_relate_pvt.delete_resource_group_relate
887 ( P_API_VERSION => 1.0,
891 X_MSG_COUNT => l_msg_count,
888 P_GROUP_RELATE_ID => grp_relations_rec.group_relate_id,
889 P_OBJECT_VERSION_NUM => grp_relations_rec.object_version_number,
890 X_RETURN_STATUS => l_return_status,
892 X_MSG_DATA => l_msg_data);
893
894 ELSIF(nvl(l_end_date_active, l_fnd_date)
895 >= nvl(end_date_active, l_fnd_date))
896 THEN
897 --update end date with p_end_date_active call update group relate api
898 jtf_rs_group_relate_pvt.update_resource_group_relate
899 ( P_API_VERSION => 1.0,
900 P_GROUP_RELATE_ID => grp_relations_rec.group_relate_id,
901 P_END_DATE_ACTIVE => trunc(p_end_date_active) ,
902 P_OBJECT_VERSION_NUM => grp_relations_rec.object_version_number,
903 X_RETURN_STATUS => l_return_status,
904 X_MSG_COUNT => l_msg_count,
905 X_MSG_DATA => l_msg_data);
906
907 END IF; -- end of start_date check
908 if ( l_return_status <> fnd_api.g_ret_sts_success)
909 then
910 raise fnd_api.g_exc_error;
911 END IF;
912 FETCH grp_relations_cur INTO grp_relations_rec;
913 END LOOP; -- end of grp_relations_cur
914 CLOSE grp_relations_cur;
915
916 END IF; -- end of If condition to check whether the new group end_date is before old end date or old end_date is null.
917 ---------------------------------------------------
918 l_object_version_num_grp := term_grp_rec.object_version_number;
919
920 IF(term_grp_rec.start_date_active >= trunc(p_end_date_active + 1)) THEN
921
922 --for future dated groups terminate it anyway
923 jtf_rs_groups_pub.update_resource_group
924 (P_API_VERSION => 1.0,
925 P_INIT_MSG_LIST => fnd_api.g_true,
926 P_COMMIT => fnd_api.g_false,
927 P_GROUP_ID => term_grp_rec.group_id,
928 P_GROUP_NUMBER => term_grp_rec.group_number,
929 P_START_DATE_ACTIVE => trunc(p_end_date_active - 1) ,
930 P_END_DATE_ACTIVE => trunc(p_end_date_active) ,
931 P_OBJECT_VERSION_NUM => l_object_version_num_grp,
932 X_RETURN_STATUS => l_return_status,
933 X_MSG_COUNT => l_msg_count,
934 X_MSG_DATA => l_msg_data) ;
935
936 ELSE
937
938 --put end_date to p_end_date_active
939 jtf_rs_groups_pub.update_resource_group
940 (P_API_VERSION => 1.0,
941 P_INIT_MSG_LIST => fnd_api.g_true,
942 P_COMMIT => fnd_api.g_false,
943 P_GROUP_ID => term_grp_rec.group_id,
944 P_GROUP_NUMBER => term_grp_rec.group_number,
945 P_END_DATE_ACTIVE => trunc(p_end_date_active),
946 P_OBJECT_VERSION_NUM => l_object_version_num_grp,
947 X_RETURN_STATUS => l_return_status,
948 X_MSG_COUNT => l_msg_count,
949 X_MSG_DATA => l_msg_data) ;
950
951 END IF; -- end of terminate group
952
953 x_object_ver_number := l_object_version_num_grp;
954
955 if ( l_return_status <> fnd_api.g_ret_sts_success) then
956 raise fnd_api.g_exc_error;
957 END IF;
958
959 end if; -- end of term_grp_cur
960 close term_grp_cur;
961
962 FND_MSG_PUB.count_and_get (p_count => l_msg_count, p_data => l_msg_data);
963
964 EXCEPTION
965 WHEN fnd_api.g_exc_unexpected_error
966 THEN
967 ROLLBACK TO group_mem_roles_save;
968 x_return_status := fnd_api.g_ret_sts_error;
969 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
970 WHEN fnd_api.g_exc_error
971 THEN
972 ROLLBACK TO group_mem_roles_save;
973 x_return_status := fnd_api.g_ret_sts_error;
974 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
975
976 WHEN OTHERS
977 THEN
978 ROLLBACK TO group_mem_roles_save;
979 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
980 fnd_message.set_token('P_SQLCODE',SQLCODE);
981 fnd_message.set_token('P_SQLERRM',SQLERRM);
982 fnd_message.set_token('P_API_NAME',l_api_name);
983 FND_MSG_PUB.add;
984 x_return_status := fnd_api.g_ret_sts_unexp_error;
985 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
986
987 END end_date_group;
988
989 /* returns 'Y' for Yes and 'N' for No */
990 FUNCTION TAX_VENDOR_EXTENSION return VARCHAR2
991 IS
992 val boolean;
993 BEGIN
994 val := zx_r11i_tax_partner_pkg.TAX_VENDOR_EXTENSION;
995 if (val = true) then
996 return 'Y';
997 else
998 return 'N';
999 end if;
1000 END TAX_VENDOR_EXTENSION;
1001
1002 /* returns 'Y' for Yes and 'N' for No */
1003 FUNCTION IS_GEOCODE_VALID(p_geocode IN VARCHAR2) return VARCHAR2
1004 IS
1005 val boolean;
1006 BEGIN
1007 val := zx_r11i_tax_partner_pkg.IS_GEOCODE_VALID(p_geocode);
1008 if (val = true) then
1009 return 'Y';
1010 else
1011 return 'N';
1012 end if;
1013 END IS_GEOCODE_VALID;
1014
1015 /* returns 'Y' for Yes and 'N' for No */
1016 FUNCTION IS_CITY_LIMIT_VALID(p_city_limit IN VARCHAR2) return VARCHAR2
1017 IS
1018 val boolean;
1019 BEGIN
1020 val := zx_r11i_tax_partner_pkg.IS_CITY_LIMIT_VALID(p_city_limit);
1021 if (val = true) then
1022 return 'Y';
1023 else
1024 return 'N';
1025 end if;
1026 END IS_CITY_LIMIT_VALID;
1027
1028 END jtf_rs_resource_utl_pub;