[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.12010000.3 2009/12/31 10:52:03 rgokavar 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,
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) ,
361 P_END_DATE_ACTIVE => trunc(p_end_date_active ) ,
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 --Bug9009376
442 --When No Data Found against Resource Id then
443 --raising an error.
444 else -- else block of term_res_cur
445 fnd_message.set_name('JTF', 'JTF_RS_INVALID_RESOURCE');
446 fnd_message.set_token('P_RESOURCE_ID', p_resource_id);
447 fnd_msg_pub.add;
448
449 RAISE fnd_api.g_exc_error;
450
451 end if; -- end of term_res_cur
452
453 close term_res_cur;
454
455 FND_MSG_PUB.count_and_get (p_count => l_msg_count, p_data => l_msg_data);
456 --Bug#8915500
457 --Status in local varaible assigned to Out parameter.
458 x_return_status := l_return_status;
459
460 EXCEPTION
461 WHEN fnd_api.g_exc_unexpected_error
462 THEN
463 ROLLBACK TO res_save;
464 x_return_status := fnd_api.g_ret_sts_error;
465 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
466 WHEN fnd_api.g_exc_error
467 THEN
468 ROLLBACK TO res_save;
469 x_return_status := fnd_api.g_ret_sts_error;
470 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
471
472 WHEN OTHERS
473 THEN
474 ROLLBACK TO res_save;
475 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
476 fnd_message.set_token('P_SQLCODE',SQLCODE);
477 fnd_message.set_token('P_SQLERRM',SQLERRM);
478 fnd_message.set_token('P_API_NAME',l_api_name);
479 FND_MSG_PUB.add;
480 x_return_status := fnd_api.g_ret_sts_unexp_error;
481 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
482 END end_date_employee;
483
484 PROCEDURE add_message
485 (P_API_VERSION IN NUMBER,
486 P_MESSAGE_CODE IN VARCHAR2,
487 P_TOKEN1_NAME IN VARCHAR2,
488 P_TOKEN1_VALUE IN VARCHAR2,
489 P_TOKEN2_NAME IN VARCHAR2,
490 P_TOKEN2_VALUE IN VARCHAR2,
491 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
492 X_MSG_COUNT OUT NOCOPY NUMBER,
493 X_MSG_DATA OUT NOCOPY VARCHAR2
494 )
495 AS
496 l_api_name VARCHAR2(30);
497 BEGIN
498 l_api_name := 'ADD_MESSAGE';
499 x_return_status := fnd_api.g_ret_sts_success;
500 FND_MSG_PUB.Initialize;
501 if(P_MESSAGE_CODE is not null)
502 then
503 fnd_message.set_name ('JTF', p_message_code);
504 if((P_TOKEN1_NAME is not null) OR (P_TOKEN1_NAME <> fnd_api.g_miss_char))
505 then
506 fnd_message.set_token (p_token1_name, p_token1_value);
507 end if;
508 if((P_TOKEN2_NAME is not null) OR (P_TOKEN2_NAME <> fnd_api.g_miss_char))
509 then
510 fnd_message.set_token (p_token2_name, p_token2_value);
511 end if;
512 FND_MSG_PUB.add;
513 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
514 end if;
515
516 EXCEPTION
517 WHEN OTHERS
518 THEN
519 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
520 fnd_message.set_token('P_SQLCODE',SQLCODE);
521 fnd_message.set_token('P_SQLERRM',SQLERRM);
522 fnd_message.set_token('P_API_NAME',l_api_name);
523 FND_MSG_PUB.add;
524 x_return_status := fnd_api.g_ret_sts_unexp_error;
525 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
526
527 END add_message ;
528
529 /* Function to check if user has resource update access */
530
531 Function Validate_Update_Access( p_resource_id number,
532 p_resource_user_id number default null
533 ) Return varchar2
534 IS
535 l_profile_value VARCHAR2(10);
536 l_user_id number;
537 l_resource_user_id number;
538
539 BEGIN
540
541 l_profile_value := nvl(FND_PROFILE.VALUE('JTF_RS_EMP_RES_UPD_ACCESS'),'SELF');
542 l_user_id := nvl(FND_PROFILE.VALUE('USER_ID'),-1);
543
544 IF (l_profile_value = 'SELF') THEN
545 IF (p_resource_user_id IS NULL) THEN
546 BEGIN
547 SELECT nvl(user_id,-99)
548 INTO l_resource_user_id
549 FROM jtf_rs_resource_extns
550 WHERE resource_id = p_resource_id;
551 EXCEPTION WHEN NO_DATA_FOUND THEN
552 l_resource_user_id := -99;
553 WHEN OTHERS THEN
554 l_resource_user_id := -98;
555 END;
556 ELSE
557 l_resource_user_id := p_resource_user_id;
558 END IF;
559
560 IF l_resource_user_id = l_user_id THEN
561 Return 'SELF';
562 ELSE
563 Return 'OTHERS';
564 END IF;
565
566 ELSIF (l_profile_value = 'ANY') THEN
567 Return 'ANY';
568 ELSE
569 Return 'OTHERS';
570 END IF;
571
572 END Validate_Update_Access;
573
574 /* Function to check if logged in user has access to Update Group Membership/Hierarchy */
575
576 FUNCTION Group_Update_Access( p_group_id IN number default null) RETURN VARCHAR2
577 IS
578
579 l_profile_value VARCHAR2(10);
580 l_user_id number;
581 l_resource_id number := 0;
582 l_mgr number := 0;
583
584 CURSOR parent_grp_cur(l_group_id number) IS
585 SELECT parent_group_id
586 FROM jtf_rs_groups_denorm
587 WHERE group_id = l_group_id
588 AND group_id <> parent_group_id
589 AND trunc(sysdate) between start_date_active and nvl(end_date_active, trunc(sysdate));
590
591 BEGIN
592
593 l_profile_value := nvl(FND_PROFILE.VALUE('JTF_RS_GROUP_UPD_ACCESS'),'NONE');
594 l_user_id := nvl(FND_PROFILE.VALUE('USER_ID'),-1);
595 -- l_profile_value := 'NONE';
596 -- l_user_id := 1351;
597
598 IF (l_profile_value = 'ALL') THEN
599 RETURN 'SU';
600 ELSIF ((l_profile_value = 'NONE') AND (p_group_id IS NOT NULL)) THEN
601 BEGIN
602 SELECT resource_id
603 INTO l_resource_id
604 FROM jtf_rs_resource_extns
605 WHERE user_id = l_user_id
606 AND rownum < 2;
607 EXCEPTION
608 WHEN NO_DATA_FOUND THEN
609 l_resource_id := 0;
610 RETURN('RO');
611 WHEN OTHERS THEN
612 l_resource_id := 0;
613 RETURN('RO');
614 END;
615
616 IF ((l_resource_id <> 0)) THEN
617 -- Check if user is active Manager/Admin of any acive parent group
618 FOR parent_grp_rec IN parent_grp_cur(p_group_id) LOOP
619 EXIT WHEN l_mgr = 1;
620 BEGIN
621 SELECT '1'
622 INTO l_mgr
623 FROM jtf_rs_roles_b c,
624 jtf_rs_role_relations b,
625 jtf_rs_group_members a
626 WHERE a.group_id = parent_grp_rec.parent_group_id
627 AND a.resource_id = l_resource_id
628 AND nvl(a.delete_flag, 'N') <> 'Y'
629 AND b.role_resource_id = a.group_member_id
630 AND trunc(sysdate) between b.start_date_active and nvl(b.end_date_active, trunc(sysdate))
631 AND b.role_resource_type = 'RS_GROUP_MEMBER'
632 AND nvl(b.delete_flag, 'N') <> 'Y'
633 AND c.role_id = b.role_id
634 AND 'Y' in (c.manager_flag, c.admin_flag)
635 AND c.active_flag = 'Y'
636 AND rownum < 2 ;
637 EXCEPTION
638 WHEN NO_DATA_FOUND THEN
639 l_mgr := 0;
640 WHEN OTHERS THEN
641 l_mgr := 0;
642 END;
643 END LOOP;
644
645 IF (l_mgr = 1) THEN
646 RETURN('FA');
647 ELSE
648 -- Check if user is active Manager/Admin of group being queried
649 BEGIN
650 SELECT '2'
651 INTO l_mgr
652 FROM jtf_rs_roles_b c,
653 jtf_rs_role_relations b,
654 jtf_rs_group_members a
655 WHERE a.group_id = p_group_id
656 AND a.resource_id = l_resource_id
657 AND nvl(a.delete_flag, 'N') <> 'Y'
658 AND b.role_resource_id = a.group_member_id
659 AND trunc(sysdate) between b.start_date_active and nvl(b.end_date_active, trunc(sysdate))
660 AND b.role_resource_type = 'RS_GROUP_MEMBER'
661 AND nvl(b.delete_flag, 'N') <> 'Y'
662 AND c.role_id = b.role_id
663 AND 'Y' in (c.manager_flag, c.admin_flag)
664 AND c.active_flag = 'Y'
665 AND rownum < 2 ;
666
667 IF (l_mgr = 2) THEN
668 RETURN('NPU');
669 ELSIF (l_mgr = 0) THEN
670 RETURN('RO');
671 END IF;
672
673 EXCEPTION
674 WHEN NO_DATA_FOUND THEN
675 l_mgr := 0;
676 RETURN('RO');
677 WHEN OTHERS THEN
678 l_mgr := 0;
679 RETURN('RO');
680 END;
681 END IF; -- End of l_mgr value check
682 ELSE -- Resource id is invalid (0)
683 RETURN('RO');
684 END IF; -- End of l_resource_id value check
685 ELSE -- Profile value is NONE but p_group_id is NULL
686 RETURN('RO');
687 END IF; -- End of profile value check
688
689 END Group_Update_Access;
690
691
692 /* Function to check if logged in user has access to Update role */
693
694 FUNCTION Role_Update_Access RETURN VARCHAR2
695 IS
696
697 l_profile_value VARCHAR2(10);
698
699 BEGIN
700
701 l_profile_value := nvl(FND_PROFILE.VALUE('JTF_RS_ROLE_UPD_ACCESS'),'NONE');
702 if l_profile_value = 'ALL' then
703 return 'FA';
704 else
705 return 'RO';
706 end if;
707
708 END Role_Update_Access;
709
710
711
712 /* Function to check if user is HR manager for this resource */
713
714 Function Is_HR_Manager( p_resource_id number
715 ) Return varchar2
716 IS
717 l_user_id number;
718
719 cursor mgr_usr_ids(p_res_id number) is
720 select user_id
721 from jtf_rs_resource_extns connect by
722 source_id = prior source_mgr_id
723 start with resource_id = p_res_id;
724
725 BEGIN
726
727 l_user_id := nvl(FND_PROFILE.VALUE('USER_ID'),-1);
728
729 for mgr_usr_id_rec in mgr_usr_ids(p_resource_id) loop
730 if (l_user_id = mgr_usr_id_rec.user_id) then
731 return 'Y';
732 end if;
733 end loop;
734
735 return 'N';
736 END Is_HR_Manager;
737
738 PROCEDURE end_date_group
739 (P_API_VERSION IN NUMBER,
740 P_INIT_MSG_LIST IN VARCHAR2,
741 P_COMMIT IN VARCHAR2,
742 P_GROUP_ID IN NUMBER,
743 P_END_DATE_ACTIVE IN DATE,
744 X_OBJECT_VER_NUMBER IN OUT NOCOPY NUMBER,
745 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
746 X_MSG_COUNT OUT NOCOPY NUMBER,
747 X_MSG_DATA OUT NOCOPY VARCHAR2 )
748 IS
749 l_api_version CONSTANT NUMBER := 1.0;
750 l_api_name CONSTANT VARCHAR2(30) := 'END_DATE_GROUP';
751 l_return_status VARCHAR2(2);
752 l_msg_count NUMBER;
753 l_msg_data VARCHAR2(2000);
754 l_group_id NUMBER;
755
756 l_fnd_date date;
757 l_end_date_active date;
758 end_date_active date;
759 l_object_version_num_grp number;
760
761 --cursor to get details about group needs to end date
762 CURSOR term_grp_cur(c_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE) IS
763 SELECT grp.group_id,
764 grp.group_number,
765 grp.object_version_number,
766 grp.start_date_active,
767 grp.end_date_active
768 FROM jtf_rs_groups_b grp
769 WHERE grp.group_id = c_group_id;
770
771 term_grp_rec term_grp_cur%rowtype;
772
773 --cursor to get all active member roles
774 CURSOR group_mem_roles_cur(c_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE,
775 c_group_end_date JTF_RS_GROUPS_B.END_DATE_ACTIVE%TYPE) IS
776 SELECT rlt.role_relate_id,
777 rlt.start_date_active,
778 rlt.end_date_active,
779 rlt.object_version_number
780 FROM jtf_rs_role_relations rlt,
781 jtf_rs_group_members mem
782 WHERE mem.group_id = c_group_id
783 AND nvl(mem.delete_flag, 'N') <> 'Y'
784 AND rlt.role_resource_id = mem.group_member_id
785 AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
786 AND nvl(rlt.delete_flag ,'N') <> 'Y'
787 AND nvl(rlt.end_date_active, l_fnd_date) > c_group_end_date
788 UNION ALL
789 SELECT rlt2.role_relate_id,
790 rlt2.start_date_active,
791 rlt2.end_date_active,
792 rlt2.object_version_number
793 FROM jtf_rs_role_relations rlt2
794 WHERE rlt2.role_resource_id = c_group_id
795 AND rlt2.role_resource_type = 'RS_GROUP'
796 AND nvl(rlt2.delete_flag ,'N') <> 'Y'
797 AND NVL(rlt2.end_date_active, l_fnd_date) > c_group_end_date;
798
799 group_mem_roles_rec group_mem_roles_cur%rowtype;
800
801 --cursor to get all active parent or child group relations
802 CURSOR grp_relations_cur(c_group_id JTF_RS_GROUPS_B.GROUP_ID%TYPE,
803 c_group_end_date JTF_RS_GROUPS_B.END_DATE_ACTIVE%TYPE) IS
804 SELECT group_relate_id,
805 group_id,
806 related_group_id,
807 start_date_active,
808 end_date_active,
809 object_version_number
810 FROM jtf_rs_grp_relations
811 WHERE nvl(delete_flag, 'N') <> 'Y'
812 AND group_id = c_group_id
813 AND nvl(end_date_active, l_fnd_date) > c_group_end_date
814 UNION ALL
815 SELECT group_relate_id,
816 group_id,
817 related_group_id,
818 start_date_active,
819 end_date_active,
820 object_version_number
821 FROM jtf_rs_grp_relations
822 WHERE nvl(delete_flag, 'N') <> 'Y'
823 AND related_group_id = c_group_id
824 AND nvl(end_date_active, l_fnd_date) > c_group_end_date;
825
826 grp_relations_rec grp_relations_cur%rowtype;
827
828 BEGIN
829
830 l_fnd_date := to_date(to_char(fnd_api.g_miss_date, 'DD-MM-RRRR'), 'DD-MM-RRRR');
831 end_date_active := to_date(to_char(p_end_date_active, 'DD-MM-RRRR'), 'DD-MM-RRRR');
832
833 --Standard Start of API SAVEPOINT
834 SAVEPOINT group_mem_roles_save;
835
836 l_return_status := fnd_api.g_ret_sts_success;
837
838 IF FND_API.To_boolean(P_INIT_MSG_LIST)
839 THEN
840 FND_MSG_PUB.Initialize;
841 END IF;
842
843 OPEN term_grp_cur(p_group_id);
844 FETCH term_grp_cur into term_grp_rec;
845 IF (term_grp_cur%found) THEN
846
847 -- If condition to check whether the new group end_date is before old end_date or old end_date is null.
848 IF(nvl(trunc(term_grp_rec.end_date_active), l_fnd_date) > nvl(trunc(p_end_date_active), l_fnd_date)) THEN
849
850 --get all group member roles to be terminated
851 open group_mem_roles_cur(p_group_id,p_end_date_active);
852 fetch group_mem_roles_cur INTO group_mem_roles_rec;
853 WHILE(group_mem_roles_cur%FOUND)
854 LOOP
855 l_end_date_active := to_date(to_char(group_mem_roles_rec.end_date_active, 'DD-MM-RRRR'), 'DD-MM-RRRR');
856 IF(trunc(group_mem_roles_rec.start_date_active) > trunc(p_end_date_active))
857 THEN
858 --call delete role relate api
859 jtf_rs_role_relate_pub.delete_resource_role_relate
860 ( P_API_VERSION => 1.0,
861 P_ROLE_RELATE_ID => group_mem_roles_rec.role_relate_id,
862 P_OBJECT_VERSION_NUM => group_mem_roles_rec.object_version_number,
863 X_RETURN_STATUS => l_return_status,
864 X_MSG_COUNT => l_msg_count,
865 X_MSG_DATA => l_msg_data);
866
867 ELSIF(nvl(l_end_date_active, l_fnd_date)
868 >= nvl(end_date_active, l_fnd_date))
869 THEN
870 --update end date with p_end_date_active call update role relate api
871 jtf_rs_role_relate_pub.update_resource_role_relate
872 ( P_API_VERSION => 1.0,
873 P_ROLE_RELATE_ID => group_mem_roles_rec.role_relate_id,
874 P_END_DATE_ACTIVE => trunc(p_end_date_active) ,
875 P_OBJECT_VERSION_NUM => group_mem_roles_rec.object_version_number,
876 X_RETURN_STATUS => l_return_status,
877 X_MSG_COUNT => l_msg_count,
878 X_MSG_DATA => l_msg_data);
879
880 END IF; -- end of start_date check
881 if ( l_return_status <> fnd_api.g_ret_sts_success)
882 then
883 raise fnd_api.g_exc_error;
884 END IF;
885 FETCH group_mem_roles_cur INTO group_mem_roles_rec;
886 END LOOP; -- end of group_mem_roles_cur
887 CLOSE group_mem_roles_cur;
888
889 --get all group relations to be terminated
890 open grp_relations_cur(p_group_id,p_end_date_active);
891 fetch grp_relations_cur INTO grp_relations_rec;
892 WHILE(grp_relations_cur%FOUND)
893 LOOP
894 l_end_date_active := to_date(to_char(grp_relations_rec.end_date_active, 'DD-MM-RRRR'), 'DD-MM-RRRR');
895 IF(trunc(grp_relations_rec.start_date_active) > trunc(p_end_date_active))
896 THEN
897 --call delete group relate api
898 jtf_rs_group_relate_pvt.delete_resource_group_relate
899 ( P_API_VERSION => 1.0,
900 P_GROUP_RELATE_ID => grp_relations_rec.group_relate_id,
901 P_OBJECT_VERSION_NUM => grp_relations_rec.object_version_number,
902 X_RETURN_STATUS => l_return_status,
903 X_MSG_COUNT => l_msg_count,
904 X_MSG_DATA => l_msg_data);
905
906 ELSIF(nvl(l_end_date_active, l_fnd_date)
907 >= nvl(end_date_active, l_fnd_date))
908 THEN
909 --update end date with p_end_date_active call update group relate api
910 jtf_rs_group_relate_pvt.update_resource_group_relate
911 ( P_API_VERSION => 1.0,
912 P_GROUP_RELATE_ID => grp_relations_rec.group_relate_id,
913 P_END_DATE_ACTIVE => trunc(p_end_date_active) ,
914 P_OBJECT_VERSION_NUM => grp_relations_rec.object_version_number,
915 X_RETURN_STATUS => l_return_status,
916 X_MSG_COUNT => l_msg_count,
917 X_MSG_DATA => l_msg_data);
918
919 END IF; -- end of start_date check
920 if ( l_return_status <> fnd_api.g_ret_sts_success)
921 then
922 raise fnd_api.g_exc_error;
923 END IF;
924 FETCH grp_relations_cur INTO grp_relations_rec;
925 END LOOP; -- end of grp_relations_cur
926 CLOSE grp_relations_cur;
927
928 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.
929 ---------------------------------------------------
930 l_object_version_num_grp := term_grp_rec.object_version_number;
931
932 IF(term_grp_rec.start_date_active >= trunc(p_end_date_active + 1)) THEN
933
934 --for future dated groups terminate it anyway
935 jtf_rs_groups_pub.update_resource_group
936 (P_API_VERSION => 1.0,
937 P_INIT_MSG_LIST => fnd_api.g_true,
938 P_COMMIT => fnd_api.g_false,
939 P_GROUP_ID => term_grp_rec.group_id,
940 P_GROUP_NUMBER => term_grp_rec.group_number,
941 P_START_DATE_ACTIVE => trunc(p_end_date_active - 1) ,
942 P_END_DATE_ACTIVE => trunc(p_end_date_active) ,
943 P_OBJECT_VERSION_NUM => l_object_version_num_grp,
944 X_RETURN_STATUS => l_return_status,
945 X_MSG_COUNT => l_msg_count,
946 X_MSG_DATA => l_msg_data) ;
947
948 ELSE
949
950 --put end_date to p_end_date_active
951 jtf_rs_groups_pub.update_resource_group
952 (P_API_VERSION => 1.0,
953 P_INIT_MSG_LIST => fnd_api.g_true,
954 P_COMMIT => fnd_api.g_false,
955 P_GROUP_ID => term_grp_rec.group_id,
956 P_GROUP_NUMBER => term_grp_rec.group_number,
957 P_END_DATE_ACTIVE => trunc(p_end_date_active),
958 P_OBJECT_VERSION_NUM => l_object_version_num_grp,
959 X_RETURN_STATUS => l_return_status,
960 X_MSG_COUNT => l_msg_count,
961 X_MSG_DATA => l_msg_data) ;
962
963 END IF; -- end of terminate group
964
965 x_object_ver_number := l_object_version_num_grp;
966
967 if ( l_return_status <> fnd_api.g_ret_sts_success) then
968 raise fnd_api.g_exc_error;
969 END IF;
970
971 end if; -- end of term_grp_cur
972 close term_grp_cur;
973
974 FND_MSG_PUB.count_and_get (p_count => l_msg_count, p_data => l_msg_data);
975
976 EXCEPTION
977 WHEN fnd_api.g_exc_unexpected_error
978 THEN
979 ROLLBACK TO group_mem_roles_save;
980 x_return_status := fnd_api.g_ret_sts_error;
981 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
982 WHEN fnd_api.g_exc_error
983 THEN
984 ROLLBACK TO group_mem_roles_save;
985 x_return_status := fnd_api.g_ret_sts_error;
986 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
987
988 WHEN OTHERS
989 THEN
990 ROLLBACK TO group_mem_roles_save;
991 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
992 fnd_message.set_token('P_SQLCODE',SQLCODE);
993 fnd_message.set_token('P_SQLERRM',SQLERRM);
994 fnd_message.set_token('P_API_NAME',l_api_name);
995 FND_MSG_PUB.add;
996 x_return_status := fnd_api.g_ret_sts_unexp_error;
997 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
998
999 END end_date_group;
1000
1001 /* returns 'Y' for Yes and 'N' for No */
1002 FUNCTION TAX_VENDOR_EXTENSION return VARCHAR2
1003 IS
1004 val boolean;
1005 BEGIN
1006 val := zx_r11i_tax_partner_pkg.TAX_VENDOR_EXTENSION;
1007 if (val = true) then
1008 return 'Y';
1009 else
1010 return 'N';
1011 end if;
1012 END TAX_VENDOR_EXTENSION;
1013
1014 /* returns 'Y' for Yes and 'N' for No */
1015 FUNCTION IS_GEOCODE_VALID(p_geocode IN VARCHAR2) return VARCHAR2
1016 IS
1017 val boolean;
1018 BEGIN
1019 val := zx_r11i_tax_partner_pkg.IS_GEOCODE_VALID(p_geocode);
1020 if (val = true) then
1021 return 'Y';
1022 else
1023 return 'N';
1024 end if;
1025 END IS_GEOCODE_VALID;
1026
1027 /* returns 'Y' for Yes and 'N' for No */
1028 FUNCTION IS_CITY_LIMIT_VALID(p_city_limit IN VARCHAR2) return VARCHAR2
1029 IS
1030 val boolean;
1031 BEGIN
1032 val := zx_r11i_tax_partner_pkg.IS_CITY_LIMIT_VALID(p_city_limit);
1033 if (val = true) then
1034 return 'Y';
1035 else
1036 return 'N';
1037 end if;
1038 END IS_CITY_LIMIT_VALID;
1039
1040 END jtf_rs_resource_utl_pub;