[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_CONC_RES_PUB
Source
1 PACKAGE BODY jtf_rs_conc_res_pub AS
2 /* $Header: jtfrsbrb.pls 120.6.12010000.2 2008/10/22 05:52:03 rgokavar ship $ */
3
4 /*****************************************************************************************
5 This is a concurrent program to fetch all employees from the employee database for creating
6 resources in resource manager. All employees who are valid on sysdate and are not already
7 existing in resource manager will be fetched
8
9 This program also updates the party and partner information from TCA model
10
11 ******************************************************************************************/
12
13 /* Package variables. */
14
15 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_RS_CONC_RES_PUB';
16 g_run_date DATE;
17
18 /* Function to get the start date of the primary job assignment */
19
20 FUNCTION get_asg_start_date(p_job_id NUMBER, p_person_id NUMBER, p_assignment_id NUMBER, p_assignment_type VARCHAR2)
21 RETURN DATE
22 IS
23 l_asg_start_date DATE;
24 l_asg_start_date_temp DATE;
25 l_job_id NUMBER;
26 l_job_id_temp NUMBER;
27
28 CURSOR c_get_start_date(c_job_id IN NUMBER, c_person_id IN NUMBER, c_assignment_id NUMBER, c_assignment_type VARCHAR2) IS
29 select job_id, effective_start_date
30 from per_all_assignments_f
31 where person_id = c_person_id
32 -- and assignment_type = 'E'
33 and assignment_type = c_assignment_type
34 and primary_flag = 'Y'
35 -- and job_id = c_job_id
36 and assignment_id = c_assignment_id
37 and trunc(effective_start_date) <= g_run_date
38 order by effective_start_date desc;
39
40 BEGIN
41
42 l_job_id := p_job_id;
43 l_job_id_temp := p_job_id;
44
45 OPEN c_get_start_date(p_job_id,p_person_id,p_assignment_id,p_assignment_type);
46 LOOP
47 FETCH c_get_start_date INTO l_job_id_temp, l_asg_start_date_temp;
48 if (l_job_id = l_job_id_temp) then
49 l_asg_start_date := l_asg_start_date_temp;
50 else
51 EXIT;
52 end if;
53 EXIT WHEN c_get_start_date%NOTFOUND;
54 END LOOP;
55 CLOSE c_get_start_date;
56
57 RETURN l_asg_start_date;
58
59 END;
60
61 /* Enh 3947611 2-dec-2004:Function to get the start date of the primary job assignment for future dated employees */
62
63 FUNCTION get_ftr_asg_start_date(p_job_id NUMBER, p_person_id NUMBER, p_assignment_id NUMBER, p_assignment_type VARCHAR2)
64 RETURN DATE
65 IS
66 l_asg_start_date DATE;
67 l_asg_start_date_temp DATE;
68 l_job_id NUMBER;
69 l_job_id_temp NUMBER;
70
71 CURSOR c_get_start_date(c_job_id IN NUMBER, c_person_id IN NUMBER, c_assignment_id NUMBER, c_assignment_type VARCHAR2) IS
72 select job_id, effective_start_date
73 from per_all_assignments_f
74 where person_id = c_person_id
75 -- and assignment_type = 'E'
76 and assignment_type = c_assignment_type
77 and primary_flag = 'Y'
78 -- and job_id = c_job_id
79 and assignment_id = c_assignment_id
80 --and trunc(effective_start_date) <= trunc(sysdate)
81 order by effective_start_date desc;
82
83 BEGIN
84
85 l_job_id := p_job_id;
86 l_job_id_temp := p_job_id;
87
88 OPEN c_get_start_date(p_job_id,p_person_id,p_assignment_id,p_assignment_type);
89 LOOP
90 FETCH c_get_start_date INTO l_job_id_temp, l_asg_start_date_temp;
91 if (l_job_id = l_job_id_temp) then
92 l_asg_start_date := l_asg_start_date_temp;
93 else
94 EXIT;
95 end if;
96 EXIT WHEN c_get_start_date%NOTFOUND;
97 END LOOP;
98 CLOSE c_get_start_date;
99
100 RETURN l_asg_start_date;
101
102 END;
103
104
105 /* Function to get the end date of the primary job assignment */
106
107 FUNCTION get_asg_end_date(p_job_id NUMBER, p_person_id NUMBER, p_assignment_id NUMBER, p_assignment_type VARCHAR2)
108 RETURN DATE
109 IS
110 l_asg_end_date DATE;
111
112 CURSOR c_get_end_date(c_job_id IN NUMBER, c_person_id IN NUMBER, c_assignment_id NUMBER, c_assignment_type VARCHAR2) IS
113 select effective_end_date
114 from per_all_assignments_f
115 where person_id = c_person_id
116 -- and assignment_type = 'E'
117 and assignment_type = c_assignment_type
118 and primary_flag = 'Y'
119 and job_id = c_job_id
120 and assignment_id = c_assignment_id
121 and trunc(effective_start_date) <= g_run_date
122 order by effective_start_date desc;
123
124 BEGIN
125
126 OPEN c_get_end_date(p_job_id,p_person_id,p_assignment_id,p_assignment_type);
127 FETCH c_get_end_date INTO l_asg_end_date;
128 CLOSE c_get_end_date;
129
130 RETURN l_asg_end_date;
131
132 END;
133
134 PROCEDURE UPDATE_EMP_PHONE;
135
136 --procedure to group together all the synchronizing procedures
137 PROCEDURE synchronize_employee
138 (ERRBUF OUT NOCOPY VARCHAR2,
139 RETCODE OUT NOCOPY VARCHAR2,
140 P_OVERWRITE_NAME IN VARCHAR2,
141 P_GET_NEW_EMP IN VARCHAR2,
142 P_DUMMY_1 IN VARCHAR2,
143 P_CREATE_SRP IN VARCHAR2,
144 P_DUMMY_2 IN VARCHAR2,
145 P_SALES_CREDIT_TYPE IN VARCHAR2,
146 P_CHECK_JOB_ROLE_MAP IN VARCHAR2
147 )
148
149 IS
150
151 /* Moved the initial assignment of below variables to inside begin */
152 set_err_var VARCHAR2(1);
153 l_prof_srp_num fnd_profile_option_values.profile_option_value%TYPE;
154
155 BEGIN
156
157 set_err_var := 'N';
158 l_prof_srp_num := FND_PROFILE.VALUE('JTF_RS_MODE_OF_SRP_NUM_CREATION');
159 g_run_date := nvl(to_date(FND_PROFILE.VALUE('JTF_RS_SYNC_RUN_DATE'),'MM/DD/YYYY'),trunc(sysdate));
160
161 SAVEPOINT CONC_EMPLOYEE_SP;
162
163 -- Adding Code for Enh to Create Salesperson and Roles (Job-Role Map) Automatically
164 IF (p_get_new_emp ='Y') THEN
165 IF (p_create_srp = 'Y') THEN
166 IF (p_sales_credit_type IS NULL) THEN
167 fnd_message.set_name ('JTF','JTF_RS_CP_PM_SLS_CRDT_TYP_NULL');
168 fnd_file.put_line(fnd_file.log, fnd_message.get);
169 fnd_file.new_line(fnd_file.log,1);
170 set_err_var := 'Y';
171 END IF;
172 IF (l_prof_srp_num = 'MANUALLY_ENTERED' OR l_prof_srp_num IS NULL) THEN
173 fnd_message.set_name('JTF', 'JTF_RS_PROF_SRP_NUM_CONC_ERR');
174 fnd_message.set_token('P_PROFILE_VALUE', l_prof_srp_num);
175 fnd_file.put_line(fnd_file.log, fnd_message.get);
176 fnd_file.new_line(fnd_file.log,1);
177 set_err_var := 'Y';
178 END IF;
179 END IF;
180 END IF;
181
182 IF (set_err_var='Y') THEN
183 raise fnd_api.g_exc_error;
184 END IF;
185
186 -- End Code for Enh
187
188 --call terminate api
189 jtf_rs_conc_res_pub.terminate_employee;
190
191
192 --call update api
193 jtf_rs_conc_res_pub.update_employee
194 (P_OVERWRITE_NAME => P_OVERWRITE_NAME);
195
196 -- call update terminated api
197 -- jtf_rs_conc_res_pub.update_terminated_employee;
198
199 -- call update phones for employees
200 -- update_emp_phone;
201
202 --call create api
203 IF(p_get_new_emp = 'Y')
204 THEN
205 jtf_rs_conc_res_pub.create_employee
206 (P_CREATE_SRP => P_CREATE_SRP,
207 P_SALES_CREDIT_TYPE => P_SALES_CREDIT_TYPE,
208 P_CHECK_JOB_ROLE_MAP => P_CHECK_JOB_ROLE_MAP);
209 END IF;
210
211 commit;
212
213 EXCEPTION
214 WHEN fnd_api.g_exc_error
215 THEN
216 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
217 ROLLBACK TO CONC_EMPLOYEE_SP;
218 WHEN fnd_api.g_exc_unexpected_error
219 THEN
220 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
221 ROLLBACK TO CONC_EMPLOYEE_SP;
222 --FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
223 WHEN OTHERS
224 THEN
225 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
226 --ROLLBACK TO CONC_EMPLOYEE_SP;
227 --FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
228
229 END synchronize_employee;
230
231
232 /* Procedure to create the resource */
233
234 PROCEDURE create_employee
235 (P_CREATE_SRP IN VARCHAR2,
236 P_SALES_CREDIT_TYPE IN VARCHAR2,
237 P_CHECK_JOB_ROLE_MAP IN VARCHAR2
238 )
239
240 IS
241
242 l_api_version CONSTANT NUMBER := 1.0;
243 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_EMPLOYEE';
244 l_return_status VARCHAR2(2);
245 l_msg_count NUMBER;
246 l_msg_data VARCHAR2(2000);
247 l_resource_id jtf_rs_resource_extns.resource_id%TYPE;
248 l_resource_number jtf_rs_resource_extns.resource_number%TYPE;
249 l_source_number jtf_rs_resource_extns.source_number%TYPE;
250 l_assignment_type per_all_assignments_f.assignment_type%TYPE;
251 -- l_msg_index_out number;
252 -- l_msg_data1 varchar2(5000);
253
254 -- Variables and Cursors added for Enh/Bug 2828368
255
256 l_salesperson_number jtf_rs_salesreps.salesrep_number%TYPE;
257 l_role_id jtf_rs_roles_vl.role_id%TYPE;
258 l_role_name jtf_rs_roles_vl.role_name%TYPE;
259 /* Moved the initial assignment of below variable to inside begin */
260 l_sales_credit_type oe_sales_credit_types.name%TYPE;
261 l_sales_credit_type_id oe_sales_credit_types.sales_credit_type_id%TYPE;
262
263 /* Moved the initial assignment of below variable to inside begin */
264 l_prof_srp_num fnd_profile_option_values.profile_option_value%TYPE;
265 l_prof_get_emp_org fnd_profile_option_values.profile_option_value%TYPE;
266 l_prof_org fnd_profile_option_values.profile_option_value%TYPE;
267
268 r_return_status VARCHAR2(2);
269 r_msg_count NUMBER;
270 r_msg_data VARCHAR2(2000);
271
272 s_return_status VARCHAR2(2);
273 s_msg_count NUMBER;
274 s_msg_data VARCHAR2(2000);
275
276 l_salesrep_id jtf_rs_salesreps.salesrep_id%TYPE;
277 m_salesrep_id jtf_rs_salesreps.salesrep_id%TYPE;
278 l_role_relate_id jtf_rs_role_relations.role_relate_id%TYPE;
279
280 CURSOR c_salesrep_number (l_salesrep_number jtf_rs_salesreps.salesrep_number%TYPE) IS
281 SELECT salesrep_id FROM jtf_rs_salesreps
282 WHERE salesrep_number = l_salesrep_number;
283
284 CURSOR c_job_roles (l_job_id jtf_rs_job_roles.job_id%TYPE) IS
285 SELECT role_id
286 FROM jtf_rs_job_roles
287 WHERE job_id = l_job_id;
288
289 CURSOR c_role_name (l_role_id jtf_rs_roles_vl.role_id%TYPE) IS
290 SELECT role_name
291 FROM jtf_rs_roles_vl
292 WHERE role_id = l_role_id;
293
294 -- Added the below cursor to get the role type for a role id.
295 CURSOR c_get_role_type (l_role_id jtf_rs_roles_vl.role_id%TYPE) IS
296 SELECT role_type_code
297 FROM jtf_rs_roles_b
298 WHERE role_id = l_role_id;
299
300 l_role_type_code jtf_rs_roles_b.role_type_code%TYPE;
301
302 CURSOR c_sales_credit_type_id (l_sales_credit_type oe_sales_credit_types.name%TYPE) IS
303 SELECT sales_credit_type_id
304 FROM oe_sales_credit_types
305 WHERE name = l_sales_credit_type;
306
307 -- End of Variables and Cursors Added
308
309
310 CURSOR fetch_emp_cur
311 IS
312 SELECT ppl.person_id person_id
313 , ppl.employee_number employee_number
314 , ppl.npw_number npw_number
315 , ppl.known_as known_as
316 , ppl.full_name full_name
317 , ppl.first_name first_name
318 , ppl.last_name last_name
319 , ppl.middle_names middle_name
320 , ppl.work_telephone phone
321 , ppl.email_address email
322 , job.job_id job_id
323 , job.name job_title
324 , ppl.effective_start_date effective_start_date
325 , ppl.effective_end_date effective_end_date
326 , ppl.business_group_id org_id
327 , org.name org_name
328 , loc.address_line_1 address1
329 , loc.address_line_2 address2
330 , loc.address_line_3 address3
331 , null address4
332 , loc.town_or_city city
333 , null county
334 , loc.country country
335 , loc.postal_code postal_code
336 , null state
337 , null province
338 , asg.supervisor_id mgr_id
339 , emp.full_name mgr_name
340 , ppl.office_number office
341 , ppl.internal_location location
342 , ppl.mailstop mailstop
343 , asg.location_id address_id
344 , asg.assignment_id assignment_id
345 , asg.assignment_type assignment_type
346 FROM per_all_people_f ppl
347 , hr_all_organization_units org
348 , per_all_assignments_f asg
349 , per_jobs job
350 --, hr_locations loc (SQL rep perf bug 4956611 Nishant 28-Mar-2006)
351 , hr_locations_all loc
352 --, per_employees_current_x emp
353 , per_all_people_f emp
354 -- WHERE NOT EXISTS (select source_id
355 WHERE NOT EXISTS (select 1
356 from jtf_rs_resource_extns rsc
357 where category = 'EMPLOYEE'
358 and ppl.person_id = rsc.source_id)
359 and ppl.business_group_id = org.organization_id
360 and ppl.person_id = asg.person_id
361 and trunc(sysdate ) between ppl.effective_start_date and ppl.effective_end_date
362 -- and ppl.employee_number is not null
363 and (ppl.current_employee_flag = 'Y' OR ppl.current_npw_flag = 'Y')
364 and asg.primary_flag = 'Y'
365 -- and asg.assignment_type = 'E'
366 and (asg.assignment_type = 'E' OR asg.assignment_type = 'C')
367 and trunc(sysdate ) between asg.effective_start_date and asg.effective_end_date
368 and asg.job_id = job.job_id(+)
369 and asg.location_id = loc.location_id (+)
370 --and asg.supervisor_id = emp.employee_id(+)
371 and asg.supervisor_id = emp.person_id(+)
372 and trunc(sysdate ) between emp.effective_start_date(+) and emp.effective_end_date(+);
373
374 -- Enh 3947611 2-dec-2004:future dated employee cursor
375 CURSOR fetch_future_emp_cur
376 IS
377 SELECT ppl.person_id person_id
378 , ppl.employee_number employee_number
379 , ppl.npw_number npw_number
380 , ppl.known_as known_as
381 , ppl.full_name full_name
382 , ppl.first_name first_name
383 , ppl.last_name last_name
384 , ppl.middle_names middle_name
385 , ppl.work_telephone phone
386 , ppl.email_address email
387 , job.job_id job_id
388 , job.name job_title
389 , ppl.effective_start_date effective_start_date
390 , ppl.effective_end_date effective_end_date
391 , ppl.business_group_id org_id
392 , org.name org_name
393 , loc.address_line_1 address1
394 , loc.address_line_2 address2
395 , loc.address_line_3 address3
396 , null address4
397 , loc.town_or_city city
398 , null county
399 , loc.country country
400 , loc.postal_code postal_code
401 , null state
402 , null province
403 , asg.supervisor_id mgr_id
404 , emp.full_name mgr_name
405 , ppl.office_number office
406 , ppl.internal_location location
407 , ppl.mailstop mailstop
408 , asg.location_id address_id
409 , asg.assignment_id assignment_id
410 , asg.assignment_type assignment_type
411 FROM per_all_people_f ppl
412 , hr_all_organization_units org
413 , per_all_assignments_f asg
414 , per_jobs job
415 --, hr_locations loc (SQL rep perf bug 4956611 Nishant 28-Mar-2006)
416 , hr_locations_all loc
417 --, per_employees_current_x emp
418 , per_all_people_f emp
419 -- WHERE NOT EXISTS (select source_id
420 WHERE NOT EXISTS (select 1
421 from jtf_rs_resource_extns rsc
422 where category = 'EMPLOYEE'
423 and ppl.person_id = rsc.source_id)
424 and ppl.business_group_id = org.organization_id
425 and ppl.person_id = asg.person_id
426 and trunc(sysdate) <= ppl.effective_end_date
427 and ppl.effective_start_date > trunc(sysdate )
428 and (ppl.current_employee_flag = 'Y' OR ppl.current_npw_flag = 'Y')
429 and asg.primary_flag = 'Y'
430 and (asg.assignment_type = 'E' OR asg.assignment_type = 'C')
431 -- and trunc(sysdate ) <= asg.effective_end_date -- (not required as start_date >= sysdate is already checked)
432 and asg.effective_start_date = (select min(asg1.effective_start_date)
433 from per_all_assignments_f asg1
434 where ppl.person_id = asg1.person_id
435 and asg1.effective_start_date >= trunc(sysdate)
436 and asg.primary_flag = 'Y')
437 and asg.job_id = job.job_id(+)
438 and asg.location_id = loc.location_id (+)
439 and asg.supervisor_id = emp.person_id(+);
440 -- and trunc(sysdate ) <= emp.effective_end_date(+);
441
442
443 CURSOR fetch_user_cur(l_employee_id NUMBER)
444 IS
445 SELECT user_id,
446 user_name
447 FROM fnd_user
448 WHERE employee_id = l_employee_id;
449
450 cursor fetch_phone(l_person_id number)
451 is
452 select phone_number
453 from per_phones p1
454 where parent_table = 'PER_ALL_PEOPLE_F'
455 and parent_id = l_person_id
456 and phone_type = 'W1'
457 and trunc(sysdate) between date_from and nvl(date_to, to_date('31/12/4712', 'DD/MM/YYYY'));
458
459 -- Enh 3947611 2-dec-2004:for future dated emp phone
460 cursor fetch_future_phone(l_person_id number)
461 is
462 select phone_number
463 from per_phones p1
464 where parent_table = 'PER_ALL_PEOPLE_F'
465 and parent_id = l_person_id
466 and phone_type = 'W1'
467 and date_from = (select max(date_from)
468 from per_phones p2
469 where parent_table = 'PER_ALL_PEOPLE_F'
470 and parent_id = l_person_id
471 and phone_type = 'W1');
472
473
474 -- to get the active start date and end_date of the employee
475 -- changed the ORDER BY from desc to asc. Fix for bug # 3300436
476 CURSOR c_active_emp_dates (l_person_id IN NUMBER) IS
477 SELECT date_start, actual_termination_date
478 FROM per_periods_of_service
479 WHERE person_id = l_person_id
480 ORDER BY date_start asc;
481
482 -- CURSOR c_active_npw_dates (l_person_id IN NUMBER) IS
483 -- SELECT date_start, actual_termination_date
484 -- FROM per_periods_of_placement
485 -- WHERE person_id = l_person_id
486 -- ORDER BY date_start asc;
487
488 -- Added the below cursor to validate the operating unit belongs to the current business_group_id
489 CURSOR op_units_cur(c_business_group_id number, c_org_id number) IS
490 select organization_id
491 from hr_operating_units
492 where business_group_id = c_business_group_id
493 and organization_id = c_org_id;
494
495 l_organization_id number;
496 l_salesrep_extsts varchar2(2);
497 l_create_salesrep varchar2(2);
498 l_org_id number;
499
500 TYPE Getenddate IS REF CURSOR;
501 get_enddate_date Getenddate;
502
503 l_query varchar2(300);
504
505 l_table_not_found EXCEPTION; --exception to handle table not found;
506 PRAGMA EXCEPTION_INIT(l_table_not_found, -00942 );
507
508 l_user_id NUMBER;
509 l_user_name VARCHAR2(2000);
510 i NUMBER;
511 l_value varchar2(2000);
512 l_count number;
513 l_phone per_phones.phone_number%type;
514
515 l_active_emp_start_date DATE;
516 l_active_emp_end_date DATE;
517 l_asg_start_date date;
518
519 BEGIN
520
521 l_sales_credit_type := P_SALES_CREDIT_TYPE;
522 l_prof_srp_num := FND_PROFILE.VALUE('JTF_RS_MODE_OF_SRP_NUM_CREATION');
523 l_prof_get_emp_org := FND_PROFILE.VALUE('JTF_RS_SYNC_EMP_BUSINESS_GROUP');
524 l_prof_org := FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');
525
526 l_query := 'SELECT date_start, actual_termination_date '||
527 'FROM per_periods_of_placement ' ||
528 'WHERE person_id = :b_person_id '||
529 'ORDER BY date_start asc';
530
531
532 --Standard Start of API SAVEPOINT
533 SAVEPOINT CREATE_EMPLOYEE_SP;
534
535 l_return_status := fnd_api.g_ret_sts_success;
536
537 -- Fetch Sales Credit Type if you want to create Salesperson
538 IF (p_create_srp='Y') THEN
539 OPEN c_sales_credit_type_id (l_sales_credit_type);
540 FETCH c_sales_credit_type_id INTO l_sales_credit_type_id;
541 CLOSE c_sales_credit_type_id;
542
543 MO_GLOBAL.init('JTF');
544 l_org_id := MO_UTILS.get_default_org_id;
545 END IF;
546
547 -- start of import of employees
548 l_count := 1;
549 For fetch_emp_rec IN fetch_emp_cur
550 loop
551
552 -- Start of business profile validation
553 if (l_prof_get_emp_org = 'ALL' or l_prof_org = fetch_emp_rec.org_id) then
554
555 -- Adding Code for Enh
556
557 BEGIN
558
559 SAVEPOINT create_resource_sp;
560
561 l_role_id := NULL;
562
563 IF (fetch_emp_rec.job_id IS NOT NULL) THEN
564 OPEN c_job_roles (fetch_emp_rec.job_id);
565 FETCH c_job_roles into l_role_id;
566 CLOSE c_job_roles;
567 END IF;
568
569 IF ((P_CHECK_JOB_ROLE_MAP = 'N') OR
570 (P_CHECK_JOB_ROLE_MAP = 'Y' AND l_role_id IS NOT NULL)) THEN
571
572 -- End Adding Code
573
574 --fetch the user_id for the employee
575 l_user_id := null;
576 l_user_name := null;
577 open fetch_user_cur(fetch_emp_rec.person_id);
578 fetch fetch_user_cur into l_user_id, l_user_name;
579 close fetch_user_cur;
580
581 --fetch phone number
582 l_phone := null;
583 open fetch_phone(fetch_emp_rec.person_id);
584 fetch fetch_phone into l_phone;
585 close fetch_phone;
586
587 l_return_status := fnd_api.g_ret_sts_success;
588 r_return_status := fnd_api.g_ret_sts_success;
589 s_return_status := fnd_api.g_ret_sts_success;
590
591 --fetch active emp dates
592 l_active_emp_start_date := NULL;
593 l_active_emp_end_date := NULL;
594
595 if fetch_emp_rec.assignment_type = 'E' then
596 l_source_number := fetch_emp_rec.employee_number;
597 l_assignment_type := 'E';
598
599 OPEN c_active_emp_dates (fetch_emp_rec.person_id);
600 FETCH c_active_emp_dates INTO l_active_emp_start_date,l_active_emp_end_date;
601 IF c_active_emp_dates%NOTFOUND THEN
602 l_active_emp_start_date := fetch_emp_rec.effective_start_date;
603 l_active_emp_end_date := fetch_emp_rec.effective_end_date;
604 END IF;
605 CLOSE c_active_emp_dates;
606 else
607 l_source_number := fetch_emp_rec.npw_number;
608 l_assignment_type := 'C';
609
610 -- OPEN c_active_npw_dates (fetch_emp_rec.person_id);
611 -- FETCH c_active_npw_dates INTO l_active_emp_start_date,l_active_emp_end_date;
612 -- IF c_active_npw_dates%NOTFOUND THEN
613 -- l_active_emp_start_date := fetch_emp_rec.effective_start_date;
614 -- l_active_emp_end_date := fetch_emp_rec.effective_end_date;
615 -- END IF;
616 -- CLOSE c_active_npw_dates;
617
618 begin
619 OPEN get_enddate_date FOR l_query USING fetch_emp_rec.person_id;
620 FETCH get_enddate_date into l_active_emp_start_date, l_active_emp_end_date;
621 IF get_enddate_date%NOTFOUND THEN
622 l_active_emp_start_date := fetch_emp_rec.effective_start_date;
623 l_active_emp_end_date := fetch_emp_rec.effective_end_date;
624 END IF;
625 CLOSE get_enddate_date;
626 exception
627 when l_table_not_found then
628 l_active_emp_start_date := fetch_emp_rec.effective_start_date;
629 l_active_emp_end_date := fetch_emp_rec.effective_end_date;
630 end;
631 end if;
632
633 -- call the public api for create resource
634 /* Fix for bug # 3300436. commented P_END_DATE_ACTIVE */
635 jtf_rs_resource_pub.create_resource
636 ( P_API_VERSION => 1,
637 P_INIT_MSG_LIST => fnd_api.g_true,
638 P_COMMIT => fnd_api.g_false,
639 P_CATEGORY => 'EMPLOYEE',
640 P_SOURCE_ID => fetch_emp_rec.person_id,
641 P_ADDRESS_ID => fetch_emp_rec.address_id,
642 P_CONTACT_ID => null,
643 P_MANAGING_EMP_ID => null,
644 P_START_DATE_ACTIVE => l_active_emp_start_date,
645 -- P_END_DATE_ACTIVE => l_active_emp_end_date,
646 P_TIME_ZONE => null,
647 P_COST_PER_HR => null,
648 P_PRIMARY_LANGUAGE => null,
649 P_SECONDARY_LANGUAGE => null,
650 P_SUPPORT_SITE_ID => null,
651 P_IES_AGENT_LOGIN => null,
652 P_SERVER_GROUP_ID => null,
653 P_ASSIGNED_TO_GROUP_ID => null,
654 P_COST_CENTER => null,
655 P_CHARGE_TO_COST_CENTER => null,
656 P_COMP_CURRENCY_CODE => null,
657 P_COMMISSIONABLE_FLAG => null,
658 P_HOLD_REASON_CODE => null,
659 P_HOLD_PAYMENT => null,
660 P_COMP_SERVICE_TEAM_ID => null,
661 P_USER_ID => l_user_id,
662 P_TRANSACTION_NUMBER => null,
663 X_RETURN_STATUS => l_return_status,
664 X_MSG_COUNT => l_msg_count,
665 X_MSG_DATA => l_msg_data,
666 X_RESOURCE_ID => l_resource_id,
667 X_RESOURCE_NUMBER => l_resource_number,
668 --P_RESOURCE_NAME => fetch_emp_rec.full_name ,
669 P_RESOURCE_NAME => nvl(fetch_emp_rec.known_as,fetch_emp_rec.full_name) ,
670 P_SOURCE_NAME => fetch_emp_rec.full_name,
671 P_SOURCE_NUMBER => l_source_number,
672 P_SOURCE_JOB_TITLE => fetch_emp_rec.job_title,
673 P_SOURCE_EMAIL => fetch_emp_rec.email,
674 P_SOURCE_PHONE => l_phone,
675 P_SOURCE_ORG_ID => null,
676 P_SOURCE_ORG_NAME => null,
677 P_SOURCE_ADDRESS1 => fetch_emp_rec.address1,
678 P_SOURCE_ADDRESS2 => fetch_emp_rec.address2,
679 P_SOURCE_ADDRESS3 => fetch_emp_rec.address3,
680 P_SOURCE_ADDRESS4 => fetch_emp_rec.address4,
681 P_SOURCE_CITY => fetch_emp_rec.city,
682 P_SOURCE_POSTAL_CODE => fetch_emp_rec.postal_code,
683 P_SOURCE_STATE => fetch_emp_rec.state,
684 P_SOURCE_PROVINCE => fetch_emp_rec.province,
685 P_SOURCE_COUNTY => fetch_emp_rec.county,
686 P_SOURCE_COUNTRY => fetch_emp_rec.country,
687 P_SOURCE_MGR_ID => fetch_emp_rec.mgr_id,
688 P_SOURCE_MGR_NAME => fetch_emp_rec.mgr_name,
689 P_SOURCE_BUSINESS_GRP_ID => fetch_emp_rec.org_id,
690 P_SOURCE_BUSINESS_GRP_NAME => fetch_emp_rec.org_name,
691 P_SOURCE_FIRST_NAME => fetch_emp_rec.first_name,
692 P_SOURCE_LAST_NAME => fetch_emp_rec.last_name,
693 P_SOURCE_MIDDLE_NAME => fetch_emp_rec.middle_name,
694 P_SOURCE_CATEGORY => null,
695 P_SOURCE_STATUS => null,
696 P_SOURCE_OFFICE => fetch_emp_rec.office,
697 P_SOURCE_LOCATION => fetch_emp_rec.location,
698 P_SOURCE_MAILSTOP => fetch_emp_rec.mailstop,
699 P_USER_NAME => l_user_name
700 );
701
702 -- dbms_output.put_line('name '||fetch_emp_rec.full_name);
703 IF ( l_return_status <> fnd_api.g_ret_sts_success) THEN
704 fnd_message.set_name('JTF', 'JTF_RS_CONC_CREATE_EMP_ERR');
705 fnd_message.set_token('P_EMPLOYEE_NAME', fetch_emp_rec.full_name);
706 fnd_file.put_line(fnd_file.log, fnd_message.get);
707 FOR i IN 1..l_msg_count
708 LOOP
709 fnd_file.put_line(fnd_file.log, (fnd_msg_pub.get(i,
710 p_encoded => fnd_api.g_false)));
711 END LOOP;
712 fnd_msg_pub.delete_msg;
713 fnd_file.new_line(fnd_file.log,1);
714 raise fnd_api.g_exc_error;
715 END IF;
716
717 -- Code added by repuri (04/09/03) for Enh/Bug # 2828368
718
719 l_organization_id := NULL;
720 l_salesrep_extsts := 'N';
721 l_create_salesrep := 'N';
722
723 IF (P_CREATE_SRP = 'Y') THEN
724
725 -- To get the current operating unit. if l_org_id is NULL, then it is a single org
726 -- select to_number(decode(substrb(userenv('CLIENT_INFO'),1,1),' ',null,substrb(userenv('CLIENT_INFO'),1,10)))
727 -- into l_org_id
728 -- from dual;
729
730 if l_org_id is NOT NULL then
731 OPEN op_units_cur(fetch_emp_rec.org_id,l_org_id);
732 FETCH op_units_cur INTO l_organization_id;
733 IF op_units_cur%FOUND THEN
734 l_create_salesrep := 'Y';
735 END IF;
736 CLOSE op_units_cur;
737 -- else
738 -- l_create_salesrep := 'Y';
739 end if;
740
741 if l_create_salesrep = 'Y' then
742
743 l_salesperson_number := NULL;
744 IF (l_prof_srp_num = 'EMPLOYEE_NUMBER') THEN
745 l_salesperson_number := l_source_number;
746 ELSIF (l_prof_srp_num = 'SEQUENCE_GENERATED') THEN
747 LOOP
748 SELECT jtf_rs_salesrep_number_s.nextval
749 INTO l_salesperson_number
750 FROM DUAL;
751
752 OPEN c_salesrep_number(l_salesperson_number);
753 FETCH c_salesrep_number INTO m_salesrep_id;
754 EXIT WHEN c_salesrep_number%NOTFOUND;
755 CLOSE c_salesrep_number;
756 END LOOP;
757 CLOSE c_salesrep_number;
758 END IF;
759
760
761 IF (l_salesperson_number IS NOT NULL AND l_sales_credit_type_id IS NOT NULL) THEN
762 --Create a Salesperson for the Resource Created above
763 JTF_RS_SALESREPS_PUB.CREATE_SALESREP (
764 p_api_version => 1.0,
765 p_init_msg_list => fnd_api.g_true,
766 p_commit => fnd_api.g_false,
767 p_resource_id => l_resource_id,
768 p_sales_credit_type_id => l_sales_credit_type_id,
769 p_salesrep_number => l_salesperson_number,
770 p_name => fetch_emp_rec.full_name,
771 p_status => 'A',
772 p_start_date_active => l_active_emp_start_date,
773 p_end_date_active => null,
774 p_org_id => l_org_id,
775 p_gl_id_rev => null,
776 p_gl_id_freight => null,
777 p_gl_id_rec => null,
778 p_set_of_books_id => null,
779 p_email_address => null,
780 p_wh_update_date => null,
781 p_sales_tax_geocode => null,
782 p_sales_tax_inside_city_limits => null,
783 x_return_status => s_return_status,
784 x_msg_count => s_msg_count,
785 x_msg_data => s_msg_data,
786 x_salesrep_id => l_salesrep_id
787 );
788 IF(s_return_status <> 'S') THEN
789 fnd_message.set_name('JTF', 'JTF_RS_CP_SYN_EMP_CRT_SRP_ERR');
790 fnd_message.set_token('P_EMPLOYEE_NAME', fetch_emp_rec.full_name);
791 fnd_file.put_line(fnd_file.log, fnd_message.get);
792 FOR i IN 1..s_msg_count LOOP
793 fnd_file.put_line(fnd_file.log, (fnd_msg_pub.get(i,
794 p_encoded => fnd_api.g_false)));
795 END LOOP;
796 fnd_msg_pub.delete_msg;
797 fnd_file.new_line(fnd_file.log,1);
798 raise fnd_api.g_exc_error;
799 END IF;
800 l_salesrep_extsts := 'Y';
801 END IF;
802 END IF;
803
804 END IF;
805
806 /* added the below line to get the job assignment start date */
807 /* also changed the value for p_start_date_active from sysdate to l_asg_start_date */
808 l_asg_start_date := get_asg_start_date(fetch_emp_rec.job_id,fetch_emp_rec.person_id,fetch_emp_rec.assignment_id,l_assignment_type);
809
810 FOR i IN c_job_roles(fetch_emp_rec.job_id) LOOP
811
812 l_role_type_code := NULL;
813 OPEN c_get_role_type(i.role_id);
814 FETCH c_get_role_type INTO l_role_type_code;
815 CLOSE c_get_role_type;
816
817 -- Check for Sales Comp Roles
818 -- Will create the SALES_COMP roles only if the resource is a salesperson.
819 if not (l_salesrep_extsts = 'N' and l_role_type_code = 'SALES_COMP') then
820
821 JTF_RS_ROLE_RELATE_PVT.CREATE_RESOURCE_ROLE_RELATE(
822 p_api_version => 1.0,
823 p_init_msg_list => fnd_api.g_true,
824 p_commit => fnd_api.g_false,
825 p_role_resource_type => 'RS_INDIVIDUAL',
826 p_role_resource_id => l_resource_id,
827 p_role_id => i.role_id,
828 p_start_date_active => l_asg_start_date,
829 x_return_status => r_return_status,
830 x_msg_count => r_msg_count,
831 x_msg_data => r_msg_data,
832 x_role_relate_id => l_role_relate_id
833 );
834 IF ( r_return_status <> fnd_api.g_ret_sts_success) THEN
835 l_role_name := NULL;
836 OPEN c_role_name (i.role_id);
837 FETCH c_role_name INTO l_role_name;
838 CLOSE c_role_name;
839 fnd_message.set_name('JTF', 'JTF_RS_CP_SYN_EMP_CRT_RES_ROLE');
840 fnd_message.set_token('P_EMPLOYEE_NAME', fetch_emp_rec.full_name);
841 fnd_message.set_token('P_ROLE_NAME',l_role_name);
842 fnd_file.put_line(fnd_file.log, fnd_message.get);
843 FOR i IN 1..r_msg_count LOOP
844 fnd_file.put_line(fnd_file.log, (fnd_msg_pub.get(i,
845 p_encoded => fnd_api.g_false)));
846 END LOOP;
847 fnd_msg_pub.delete_msg;
848 fnd_file.new_line(fnd_file.log,1);
849 raise fnd_api.g_exc_error;
850 END IF;
851 end if; -- End of Check for Sales Comp Roles
852 END LOOP;
853
854 -- End Add Code for Enh
855
856 l_count := l_count + 1;
857 IF (l_count > 1000) THEN
858 commit;
859 l_count := 1;
860 END IF;
861
862 END IF;
863
864 EXCEPTION
865 WHEN fnd_api.g_exc_error
866 THEN
867 fnd_file.put_line(fnd_file.log, SQLCODE||':'||SQLERRM);
868 ROLLBACK TO create_resource_sp;
869 WHEN fnd_api.g_exc_unexpected_error
870 THEN
871 fnd_file.put_line(fnd_file.log, SQLCODE||':'||SQLERRM);
872 ROLLBACK TO create_resource_sp;
873 WHEN OTHERS
874 THEN
875 fnd_file.put_line(fnd_file.log, SQLCODE||':'||SQLERRM);
876 ROLLBACK TO create_resource_sp;
877
878 END;
879
880 end if; -- End of business profile validation
881
882 END LOOP; -- fetch employee cursor end
883
884 --Enh 3947611 2-dec-2004: create resource for future dated emplooyees
885 --
886 -- start of import of employees
887 l_count := 1;
888 For fetch_emp_rec IN fetch_future_emp_cur
889 loop
890 -- Start of business profile validation
891 if (l_prof_get_emp_org = 'ALL' or l_prof_org = fetch_emp_rec.org_id) then
892 -- Adding Code for Enh
893
894 BEGIN
895
896 SAVEPOINT create_resource_sp;
897
898 l_role_id := NULL;
899
900 IF (fetch_emp_rec.job_id IS NOT NULL) THEN
901 OPEN c_job_roles (fetch_emp_rec.job_id);
902 FETCH c_job_roles into l_role_id;
903 CLOSE c_job_roles;
904 END IF;
905
906 IF ((P_CHECK_JOB_ROLE_MAP = 'N') OR
907 (P_CHECK_JOB_ROLE_MAP = 'Y' AND l_role_id IS NOT NULL)) THEN
908
909
910 -- End Adding Code
911
912 --fetch the user_id for the employee
913 l_user_id := null;
914 l_user_name := null;
915 open fetch_user_cur(fetch_emp_rec.person_id);
916 fetch fetch_user_cur into l_user_id, l_user_name;
917 close fetch_user_cur;
918
919 --fetch phone number
920 l_phone := null;
921 open fetch_future_phone(fetch_emp_rec.person_id);
922 fetch fetch_future_phone into l_phone;
923 close fetch_future_phone;
924
925
926
927 l_return_status := fnd_api.g_ret_sts_success;
928 r_return_status := fnd_api.g_ret_sts_success;
929 s_return_status := fnd_api.g_ret_sts_success;
930
931 --fetch active emp dates
932 l_active_emp_start_date := NULL;
933 l_active_emp_end_date := NULL;
934
935 if fetch_emp_rec.assignment_type = 'E' then
936 l_source_number := fetch_emp_rec.employee_number;
937 l_assignment_type := 'E';
938
939 OPEN c_active_emp_dates (fetch_emp_rec.person_id);
940 FETCH c_active_emp_dates INTO l_active_emp_start_date,l_active_emp_end_date;
941 IF c_active_emp_dates%NOTFOUND THEN
942 l_active_emp_start_date := fetch_emp_rec.effective_start_date;
943 l_active_emp_end_date := fetch_emp_rec.effective_end_date;
944 END IF;
945 CLOSE c_active_emp_dates;
946 else
947 l_source_number := fetch_emp_rec.npw_number;
948 l_assignment_type := 'C';
949
950 begin
951 OPEN get_enddate_date FOR l_query USING fetch_emp_rec.person_id;
952 FETCH get_enddate_date into l_active_emp_start_date, l_active_emp_end_date;
953 IF get_enddate_date%NOTFOUND THEN
954 l_active_emp_start_date := fetch_emp_rec.effective_start_date;
955 l_active_emp_end_date := fetch_emp_rec.effective_end_date;
956 END IF;
957 CLOSE get_enddate_date;
958 exception
959 when l_table_not_found then
960 l_active_emp_start_date := fetch_emp_rec.effective_start_date;
961 l_active_emp_end_date := fetch_emp_rec.effective_end_date;
962 end;
963 end if;
964
965 -- call the public api for create resource
966 /* Fix for bug # 3300436. commented P_END_DATE_ACTIVE */
967 jtf_rs_resource_pub.create_resource
968 ( P_API_VERSION => 1,
969 P_INIT_MSG_LIST => fnd_api.g_true,
970 P_COMMIT => fnd_api.g_false,
971 P_CATEGORY => 'EMPLOYEE',
972 P_SOURCE_ID => fetch_emp_rec.person_id,
973 P_ADDRESS_ID => fetch_emp_rec.address_id,
974 P_CONTACT_ID => null,
975 P_MANAGING_EMP_ID => null,
976 P_START_DATE_ACTIVE => l_active_emp_start_date,
977 -- P_END_DATE_ACTIVE => l_active_emp_end_date,
978 P_TIME_ZONE => null,
979 P_COST_PER_HR => null,
980 P_PRIMARY_LANGUAGE => null,
981 P_SECONDARY_LANGUAGE => null,
982 P_SUPPORT_SITE_ID => null,
983 P_IES_AGENT_LOGIN => null,
984 P_SERVER_GROUP_ID => null,
985 P_ASSIGNED_TO_GROUP_ID => null,
986 P_COST_CENTER => null,
987 P_CHARGE_TO_COST_CENTER => null,
988 P_COMP_CURRENCY_CODE => null,
989 P_COMMISSIONABLE_FLAG => null,
990 P_HOLD_REASON_CODE => null,
991 P_HOLD_PAYMENT => null,
992 P_COMP_SERVICE_TEAM_ID => null,
993 P_USER_ID => l_user_id,
994 P_TRANSACTION_NUMBER => null,
995 X_RETURN_STATUS => l_return_status,
996 X_MSG_COUNT => l_msg_count,
997 X_MSG_DATA => l_msg_data,
998 X_RESOURCE_ID => l_resource_id,
999 X_RESOURCE_NUMBER => l_resource_number,
1000 --P_RESOURCE_NAME => fetch_emp_rec.full_name ,
1001 P_RESOURCE_NAME => nvl(fetch_emp_rec.known_as,fetch_emp_rec.full_name) ,
1002 P_SOURCE_NAME => fetch_emp_rec.full_name,
1003 P_SOURCE_NUMBER => l_source_number,
1004 P_SOURCE_JOB_TITLE => fetch_emp_rec.job_title,
1005 P_SOURCE_EMAIL => fetch_emp_rec.email,
1006 P_SOURCE_PHONE => l_phone,
1007 P_SOURCE_ORG_ID => null,
1008 P_SOURCE_ORG_NAME => null,
1009 P_SOURCE_ADDRESS1 => fetch_emp_rec.address1,
1010 P_SOURCE_ADDRESS2 => fetch_emp_rec.address2,
1011 P_SOURCE_ADDRESS3 => fetch_emp_rec.address3,
1012 P_SOURCE_ADDRESS4 => fetch_emp_rec.address4,
1013 P_SOURCE_CITY => fetch_emp_rec.city,
1014 P_SOURCE_POSTAL_CODE => fetch_emp_rec.postal_code,
1015 P_SOURCE_STATE => fetch_emp_rec.state,
1016 P_SOURCE_PROVINCE => fetch_emp_rec.province,
1017 P_SOURCE_COUNTY => fetch_emp_rec.county,
1018 P_SOURCE_COUNTRY => fetch_emp_rec.country,
1019 P_SOURCE_MGR_ID => fetch_emp_rec.mgr_id,
1020 P_SOURCE_MGR_NAME => fetch_emp_rec.mgr_name,
1021 P_SOURCE_BUSINESS_GRP_ID => fetch_emp_rec.org_id,
1022 P_SOURCE_BUSINESS_GRP_NAME => fetch_emp_rec.org_name,
1023 P_SOURCE_FIRST_NAME => fetch_emp_rec.first_name,
1024 P_SOURCE_LAST_NAME => fetch_emp_rec.last_name,
1025 P_SOURCE_MIDDLE_NAME => fetch_emp_rec.middle_name,
1026 P_SOURCE_CATEGORY => null,
1027 P_SOURCE_STATUS => null,
1028 P_SOURCE_OFFICE => fetch_emp_rec.office,
1029 P_SOURCE_LOCATION => fetch_emp_rec.location,
1030 P_SOURCE_MAILSTOP => fetch_emp_rec.mailstop,
1031 P_USER_NAME => l_user_name
1032 );
1033
1034 -- dbms_output.put_line('name '||fetch_emp_rec.full_name);
1035 IF ( l_return_status <> fnd_api.g_ret_sts_success) THEN
1036
1037 fnd_message.set_name('JTF', 'JTF_RS_CONC_CREATE_EMP_ERR');
1038 fnd_message.set_token('P_EMPLOYEE_NAME', fetch_emp_rec.full_name);
1039 fnd_file.put_line(fnd_file.log, fnd_message.get);
1040 FOR i IN 1..l_msg_count
1041 LOOP
1042 fnd_file.put_line(fnd_file.log, (fnd_msg_pub.get(i,
1043 p_encoded => fnd_api.g_false)));
1044 END LOOP;
1045 fnd_msg_pub.delete_msg;
1046 fnd_file.new_line(fnd_file.log,1);
1047 raise fnd_api.g_exc_error;
1048 END IF;
1049
1050 -- Code added by repuri (04/09/03) for Enh/Bug # 2828368
1051
1052 l_organization_id := NULL;
1053 l_salesrep_extsts := 'N';
1054
1055 IF (P_CREATE_SRP = 'Y') THEN
1056 OPEN op_units_cur(fetch_emp_rec.org_id,l_org_id);
1057 FETCH op_units_cur INTO l_organization_id;
1058 IF op_units_cur%FOUND THEN
1059
1060 l_salesperson_number := NULL;
1061 IF (l_prof_srp_num = 'EMPLOYEE_NUMBER') THEN
1062 l_salesperson_number := l_source_number;
1063 ELSIF (l_prof_srp_num = 'SEQUENCE_GENERATED') THEN
1064 LOOP
1065 SELECT jtf_rs_salesrep_number_s.nextval
1066 INTO l_salesperson_number
1067 FROM DUAL;
1068
1069 OPEN c_salesrep_number(l_salesperson_number);
1070 FETCH c_salesrep_number INTO m_salesrep_id;
1071 EXIT WHEN c_salesrep_number%NOTFOUND;
1072 CLOSE c_salesrep_number;
1073 END LOOP;
1074 CLOSE c_salesrep_number;
1075 END IF;
1076
1077
1078 IF (l_salesperson_number IS NOT NULL AND l_sales_credit_type_id IS NOT NULL) THEN
1079 --Create a Salesperson for the Resource Created above
1080 JTF_RS_SALESREPS_PUB.CREATE_SALESREP (
1081 p_api_version => 1.0,
1082 p_init_msg_list => fnd_api.g_true,
1083 p_commit => fnd_api.g_false,
1084 p_resource_id => l_resource_id,
1085 p_sales_credit_type_id => l_sales_credit_type_id,
1086 p_salesrep_number => l_salesperson_number,
1087 p_name => fetch_emp_rec.full_name,
1088 p_status => 'A',
1089 -- Enh 3947611 2-dec-2004:changed this date for future dated employees
1090 p_start_date_active => l_active_emp_start_date, --trunc(sysdate),
1091 p_end_date_active => null,
1092 p_org_id => l_org_id,
1093 p_gl_id_rev => null,
1094 p_gl_id_freight => null,
1095 p_gl_id_rec => null,
1096 p_set_of_books_id => null,
1097 p_email_address => null,
1098 p_wh_update_date => null,
1099 p_sales_tax_geocode => null,
1100 p_sales_tax_inside_city_limits => null,
1101 x_return_status => s_return_status,
1102 x_msg_count => s_msg_count,
1103 x_msg_data => s_msg_data,
1104 x_salesrep_id => l_salesrep_id
1105 );
1106 IF(s_return_status <> 'S') THEN
1107 fnd_message.set_name('JTF', 'JTF_RS_CP_SYN_EMP_CRT_SRP_ERR');
1108 fnd_message.set_token('P_EMPLOYEE_NAME', fetch_emp_rec.full_name);
1109 fnd_file.put_line(fnd_file.log, fnd_message.get);
1110 FOR i IN 1..s_msg_count LOOP
1111 fnd_file.put_line(fnd_file.log, (fnd_msg_pub.get(i,
1112 p_encoded => fnd_api.g_false)));
1113 END LOOP;
1114 fnd_msg_pub.delete_msg;
1115 fnd_file.new_line(fnd_file.log,1);
1116 raise fnd_api.g_exc_error;
1117 END IF;
1118 l_salesrep_extsts := 'Y';
1119 END IF;
1120 END IF;
1121 CLOSE op_units_cur;
1122
1123 END IF;
1124 /*added the below line to get the job assignment start date */
1125 /* Enh 3947611 2-dec-2004: using new function for future dated*/
1126 /* also changed the value for p_start_date_active from sysdate to l_asg_start_date */
1127 l_asg_start_date := get_ftr_asg_start_date(fetch_emp_rec.job_id,fetch_emp_rec.person_id,fetch_emp_rec.assignment_id,l_assignment_type);
1128
1129 FOR i IN c_job_roles(fetch_emp_rec.job_id) LOOP
1130 l_role_type_code := NULL;
1131 OPEN c_get_role_type(i.role_id);
1132 FETCH c_get_role_type INTO l_role_type_code;
1133 CLOSE c_get_role_type;
1134
1135 -- Check for Sales Comp Roles
1136 -- Will create the SALES_COMP roles only if the resource is a salesperson.
1137 if not (l_salesrep_extsts = 'N' and l_role_type_code = 'SALES_COMP') then
1138
1139 JTF_RS_ROLE_RELATE_PVT.CREATE_RESOURCE_ROLE_RELATE(
1140 p_api_version => 1.0,
1141 p_init_msg_list => fnd_api.g_true,
1142 p_commit => fnd_api.g_false,
1143 p_role_resource_type => 'RS_INDIVIDUAL',
1144 p_role_resource_id => l_resource_id,
1145 p_role_id => i.role_id,
1146 p_start_date_active => l_asg_start_date,
1147 x_return_status => r_return_status,
1148 x_msg_count => r_msg_count,
1149 x_msg_data => r_msg_data,
1150 x_role_relate_id => l_role_relate_id
1151 );
1152 IF ( r_return_status <> fnd_api.g_ret_sts_success) THEN
1153 l_role_name := NULL;
1154 OPEN c_role_name (i.role_id);
1155 FETCH c_role_name INTO l_role_name;
1156 CLOSE c_role_name;
1157
1158 fnd_message.set_name('JTF', 'JTF_RS_CP_SYN_EMP_CRT_RES_ROLE');
1159 fnd_message.set_token('P_EMPLOYEE_NAME', fetch_emp_rec.full_name);
1160 fnd_message.set_token('P_ROLE_NAME',l_role_name);
1161 fnd_file.put_line(fnd_file.log, fnd_message.get);
1162 FOR i IN 1..r_msg_count LOOP
1163 fnd_file.put_line(fnd_file.log, (fnd_msg_pub.get(i,
1164 p_encoded => fnd_api.g_false)));
1165 END LOOP;
1166
1167 fnd_msg_pub.delete_msg;
1168 fnd_file.new_line(fnd_file.log,1);
1169 raise fnd_api.g_exc_error;
1170 END IF;
1171 end if; -- End of Check for Sales Comp Roles
1172 END LOOP;
1173
1174 -- End Add Code for Enh
1175
1176 l_count := l_count + 1;
1177 IF (l_count > 1000) THEN
1178 commit;
1179 l_count := 1;
1180 END IF;
1181
1182 END IF;
1183
1184 EXCEPTION
1185 WHEN fnd_api.g_exc_error
1186 THEN
1187 fnd_file.put_line(fnd_file.log, SQLCODE||':'||SQLERRM);
1188 ROLLBACK TO create_resource_sp;
1189 WHEN fnd_api.g_exc_unexpected_error
1190 THEN
1191 fnd_file.put_line(fnd_file.log, SQLCODE||':'||SQLERRM);
1192 ROLLBACK TO create_resource_sp;
1193 WHEN OTHERS
1194 THEN
1195 fnd_file.put_line(fnd_file.log, SQLCODE||':'||SQLERRM);
1196 ROLLBACK TO create_resource_sp;
1197
1198 END;
1199
1200 end if; -- End of business profile validation
1201
1202
1203 END LOOP; -- fetch employee cursor end
1204
1205
1206 FND_MSG_PUB.count_and_get (p_count => l_msg_count, p_data => l_msg_data);
1207
1208 EXCEPTION
1209 WHEN fnd_api.g_exc_error
1210 THEN
1211 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
1212 ROLLBACK TO CREATE_EMPLOYEE_SP;
1213 WHEN fnd_api.g_exc_unexpected_error
1214 THEN
1215 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
1216 ROLLBACK TO CREATE_EMPLOYEE_SP;
1217 WHEN OTHERS
1218 THEN
1219 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
1220 ROLLBACK TO CREATE_EMPLOYEE_SP;
1221 fnd_file.put_line(fnd_file.log, sqlerrm);
1222
1223 END create_employee;
1224
1225
1226 PROCEDURE terminate_employee
1227 IS
1228 l_api_version CONSTANT NUMBER := 1.0;
1229 l_api_name CONSTANT VARCHAR2(30) := 'TERMINATE_EMPLOYEE';
1230 L_RETURN_STATUS VARCHAR2(2);
1231 L_MSG_COUNT NUMBER;
1232 L_MSG_DATA VARCHAR2(2000);
1233 l_resource_id NUMBER;
1234
1235 CURSOR term_emp_cur (l_active_days IN NUMBER)
1236 IS
1237 SELECT rsc.resource_id
1238 -- , rsc.resource_number
1239 , rsc.resource_name
1240 , rsc.source_id
1241 , rsc.object_version_number
1242 -- , rsc.start_date_active
1243 -- , rsc.end_date_active
1244 FROM jtf_rs_resource_extns_vl rsc
1245 WHERE rsc.category = 'EMPLOYEE'
1246 /* AND not exists (select employee_id
1247 from per_employees_current_x emp
1248 where emp.employee_id = rsc.source_id
1249 and rsc.category = 'EMPLOYEE')*/
1250 /*
1251 AND not exists (select ppl.person_id
1252 from per_all_people_f ppl,
1253 per_all_assignments_f asg,
1254 per_periods_of_service ser
1255 where ppl.person_id = rsc.source_id
1256 -- and rsc.category = 'EMPLOYEE'
1257 and trunc(sysdate) between ppl.effective_start_date and ppl.effective_end_date
1258 and ppl.employee_number is not null
1259 and ppl.person_id = asg.person_id
1260 and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
1261 and asg.primary_flag = 'Y'
1262 and asg.assignment_type = 'E'
1263 and asg.period_of_service_id = ser.period_of_service_id
1264 and (ser.actual_termination_date >= trunc(sysdate) or ser.actual_termination_date is NULL))
1265 */
1266 AND not exists (select ppl.person_id
1267 from per_all_people_f ppl
1268 where ppl.person_id = rsc.source_id
1269 --Enh 3947611 2-dec-2004: changed the date check so that future dated employees are not terminated
1270 --and ( trunc(sysdate) between trunc(ppl.effective_start_date) and trunc(ppl.effective_end_date)
1271 and trunc(sysdate) <= trunc(ppl.effective_end_date )
1272 and (ppl.current_employee_flag = 'Y' OR ppl.current_npw_flag = 'Y'))
1273 AND trunc( nvl(end_date_active, sysdate+l_active_days)) >= trunc(sysdate+l_active_days);
1274
1275 -- Added this cursor (and related code) for the Enh #2660474
1276 -- to get the actual termination date of an employee
1277 /* Added the last 2 lines in the cursor to fix bug # 3300436
1278 The first record from this query will give the correct record from per_periods_of_service
1279 if there are more records in this table. */
1280 CURSOR c_term_emp_date (l_person_id IN NUMBER) IS
1281 SELECT actual_termination_date
1282 FROM per_periods_of_service
1283 WHERE person_id = l_person_id
1284 AND trunc(date_start) <= trunc(sysdate)
1285 ORDER BY period_of_service_id desc;
1286
1287 /* To get the actual termination date of a worker
1288 The first record from this query will give the correct record from per_periods_of_placement
1289 if there are more records in this table. */
1290 -- CURSOR c_term_cwk_date (l_person_id IN NUMBER) IS
1291 -- SELECT actual_termination_date
1292 -- FROM per_periods_of_placement
1293 -- WHERE person_id = l_person_id
1294 -- AND trunc(date_start) <= trunc(sysdate)
1295 -- ORDER BY period_of_placement_id desc;
1296
1297 TYPE Getenddate IS REF CURSOR;
1298 get_enddate_date Getenddate;
1299
1300 l_query varchar2(300);
1301
1302 l_table_not_found EXCEPTION; --exception to handle table not found;
1303 PRAGMA EXCEPTION_INIT(l_table_not_found, -00942 );
1304
1305 i NUMBER;
1306 l_count NUMBER;
1307
1308 l_emp_end_date DATE;
1309 l_cwk_end_date DATE;
1310 l_date DATE;
1311 l_active_days NUMBER;
1312
1313 BEGIN
1314
1315 l_query := 'SELECT actual_termination_date '||
1316 'FROM per_periods_of_placement ' ||
1317 'WHERE person_id = :b_person_id '||
1318 'AND trunc(date_start) <= trunc(sysdate) '||
1319 'ORDER BY period_of_placement_id desc';
1320
1321 --Standard Start of API SAVEPOINT
1322 SAVEPOINT TERMINATE_EMPLOYEE_SP;
1323
1324 l_return_status := fnd_api.g_ret_sts_success;
1325 l_count := 0;
1326
1327 FND_PROFILE.GET('JTF_RS_RESOURCE_ACTIVE_DAYS',l_active_days);
1328
1329 IF l_active_days IS NULL THEN
1330 l_active_days := 0;
1331 END IF;
1332
1333 For term_emp_rec IN term_emp_cur (l_active_days)
1334 loop
1335
1336 BEGIN
1337
1338 SAVEPOINT TERMINATE_EMPLOYEE_IN_SP;
1339
1340 l_return_status := fnd_api.g_ret_sts_success;
1341
1342 -- Additional Code for the Enh #2660474
1343 IF term_emp_rec.source_id IS NOT NULL THEN
1344
1345 -- Changed the logic to derive the end date. Person can be a Employee or CWK.
1346 -- Fix for bug # 3455951, 3072464
1347 OPEN c_term_emp_date (term_emp_rec.source_id);
1348 FETCH c_term_emp_date into l_emp_end_date;
1349 IF c_term_emp_date%NOTFOUND THEN
1350 l_emp_end_date := NULL;
1351 END IF;
1352 CLOSE c_term_emp_date;
1353
1354 -- OPEN c_term_cwk_date (term_emp_rec.source_id);
1355 -- FETCH c_term_cwk_date into l_cwk_end_date;
1356 -- IF c_term_cwk_date%FOUND THEN
1357 begin
1358 OPEN get_enddate_date FOR l_query USING term_emp_rec.source_id;
1359 FETCH get_enddate_date into l_cwk_end_date;
1360 IF get_enddate_date%FOUND THEN
1361 IF l_emp_end_date IS NULL THEN
1362 l_date := l_cwk_end_date;
1363 ELSE -- This person was an Employee as well as CWK
1364 IF l_cwk_end_date IS NULL THEN -- this is just to address if there is any bad data.
1365 l_date := l_emp_end_date;
1366 ELSE
1367 l_date := greatest(l_emp_end_date,l_cwk_end_date);
1368 END IF;
1369 END IF;
1370 ELSE -- this shows the person was never a CWK.
1371 l_date := l_emp_end_date;
1372 END IF;
1373 -- CLOSE c_term_cwk_date;
1374 CLOSE get_enddate_date;
1375 exception
1376 when l_table_not_found then
1377 l_date := l_emp_end_date;
1378 end;
1379
1380 END IF;
1381
1382 ------------------------------------------------------------------------------
1383 -- We assume that per_periods_of_service will always have a value
1384 -- for termination_date column, if an employee is terminated.
1385 -- But for any bad data (if there is no value), we initialize it to sysdate
1386
1387 IF l_date IS NULL THEN
1388 l_date := trunc(sysdate-1);
1389 END IF;
1390
1391 jtf_rs_resource_utl_pub.end_date_employee
1392 (p_api_version => 1.0
1393 , p_resource_id => term_emp_rec.resource_id
1394 , p_end_date_active => (l_date+l_active_days)
1395 , x_object_ver_number => term_emp_rec.object_version_number
1396 , x_return_status => l_return_status
1397 , x_msg_count => l_msg_count
1398 , x_msg_data => l_msg_data);
1399
1400 IF ( l_return_status <> fnd_api.g_ret_sts_success) THEN
1401 fnd_message.set_name('JTF', 'JTF_RS_CP_SYN_EMP_TER_RES');
1402 fnd_message.set_token('P_RESOURCE_NAME', term_emp_rec.resource_name);
1403 fnd_file.put_line(fnd_file.log, fnd_message.get);
1404 FOR i IN 1..l_msg_count LOOP
1405 fnd_file.put_line(fnd_file.log, (fnd_msg_pub.get(i,
1406 p_encoded => fnd_api.g_false)));
1407 END LOOP;
1408 fnd_msg_pub.delete_msg;
1409 fnd_file.new_line(fnd_file.log,1);
1410 raise fnd_api.g_exc_error;
1411 END IF;
1412
1413 -------------------------------------------------------------------------------
1414
1415 l_count := l_count + 1;
1416 if (l_count > 1000) then
1417 commit;
1418 l_count := 1;
1419 end if;
1420
1421 EXCEPTION
1422 WHEN fnd_api.g_exc_error THEN
1423 fnd_file.put_line(fnd_file.log, SQLCODE||':'||SQLERRM);
1424 ROLLBACK TO TERMINATE_EMPLOYEE_IN_SP;
1425 WHEN fnd_api.g_exc_unexpected_error THEN
1426 fnd_file.put_line(fnd_file.log, SQLCODE||':'||SQLERRM);
1427 ROLLBACK TO TERMINATE_EMPLOYEE_IN_SP;
1428 WHEN OTHERS THEN
1429 fnd_file.put_line(fnd_file.log, SQLCODE||':'||SQLERRM);
1430 ROLLBACK TO TERMINATE_EMPLOYEE_IN_SP;
1431 END;
1432
1433 end loop; -- end of term_emp_cur
1434
1435 FND_MSG_PUB.count_and_get (p_count => l_msg_count, p_data => l_msg_data);
1436
1437 EXCEPTION
1438 WHEN fnd_api.g_exc_error
1439 THEN
1440 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
1441 ROLLBACK TO TERMINATE_EMPLOYEE_SP;
1442 WHEN OTHERS
1443 THEN
1444 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
1445 ROLLBACK TO TERMINATE_EMPLOYEE_SP;
1446 -- fnd_file.put_line(fnd_file.log, sqlerrm);
1447
1448 END terminate_employee;
1449
1450
1451 PROCEDURE update_employee
1452 (P_OVERWRITE_NAME IN VARCHAR2 )
1453 IS
1454 l_api_version CONSTANT NUMBER := 1.0;
1455 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_EMPLOYEE';
1456 l_return_status VARCHAR2(2);
1457 l_msg_count NUMBER;
1458 l_msg_data VARCHAR2(2000);
1459 /* Moved the initial assignment of below variable to inside begin */
1460 l_sysdate date;
1461
1462 -------------------------------------------------------------
1463
1464 CURSOR c_resource IS
1465 SELECT resource_id,
1466 source_id,
1467 source_number,
1468 source_job_id,
1469 start_date_active,
1470 end_date_active
1471 FROM jtf_rs_resource_extns
1472 WHERE category = 'EMPLOYEE';
1473
1474 CURSOR c_check_rehire_rev_emp(p_person_id IN NUMBER) IS
1475 SELECT 'Y'
1476 FROM jtf_rs_resource_extns rsc
1477 WHERE rsc.category = 'EMPLOYEE'
1478 AND rsc.source_id = p_person_id
1479 AND l_sysdate not between trunc(rsc.start_date_active) and trunc(nvl(rsc.end_date_active,sysdate))
1480 -- AND exists ( select cur.employee_id from PER_EMPLOYEES_CURRENT_X cur where cur.employee_id = rsc.source_id);
1481 AND exists ( select ppl.person_id
1482 from per_all_people_f ppl
1483 where ppl.person_id = p_person_id
1484 and trunc(sysdate) between ppl.effective_start_date and ppl.effective_end_date
1485 and (ppl.current_employee_flag = 'Y' OR ppl.current_npw_flag = 'Y'));
1486
1487 CURSOR c_salesreps (c_resource_id jtf_rs_resource_extns.resource_id%TYPE) IS
1488 SELECT salesrep_id
1489 FROM jtf_rs_salesreps
1490 WHERE resource_id = c_resource_id;
1491
1492 l_salesreps_rec c_salesreps%ROWTYPE;
1493
1494 CURSOR c_ppf(p_person_id IN NUMBER) IS
1495 SELECT employee_number,
1496 full_name,
1497 known_as,
1498 first_name,
1499 middle_names,
1500 last_name,
1501 email_address,
1502 business_group_id,
1503 office_number,
1504 internal_location,
1505 mailstop,
1506 npw_number,
1507 party_id,
1508 current_employee_flag
1509 FROM per_all_people_f
1510 WHERE person_id = p_person_id
1511 and g_run_date between effective_start_date and effective_end_date;
1512
1513 l_ppf_rec c_ppf%ROWTYPE;
1514 l_source_number jtf_rs_resource_extns.source_number%TYPE;
1515 l_assignment_type per_all_assignments_f.assignment_type%TYPE;
1516
1517 CURSOR c_org(p_org_id IN NUMBER) IS
1518 SELECT name
1519 FROM hr_all_organization_units
1520 WHERE organization_id = p_org_id;
1521
1522 CURSOR c_asg(p_person_id IN NUMBER) IS
1523 SELECT supervisor_id,
1524 location_id,
1525 job_id,
1526 assignment_id
1527 FROM per_all_assignments_f
1528 WHERE person_id = p_person_id
1529 AND primary_flag = 'Y'
1530 -- AND assignment_type = 'E'
1531 AND assignment_type in ('E','C')
1532 AND g_run_date between effective_start_date and effective_end_date;
1533
1534 l_asg_rec c_asg%ROWTYPE;
1535
1536 CURSOR c_job(p_job_id IN NUMBER) IS
1537 SELECT name
1538 FROM per_jobs
1539 WHERE job_id = p_job_id;
1540
1541 CURSOR c_loc(p_loc_id IN NUMBER) IS
1542 SELECT address_line_1,
1543 address_line_2,
1544 address_line_3,
1545 town_or_city,
1546 country,
1547 postal_code
1548 FROM hr_locations
1549 WHERE location_id = p_loc_id;
1550
1551 l_loc_rec c_loc%ROWTYPE;
1552
1553 CURSOR c_mgr(p_person_id IN NUMBER) IS
1554 SELECT full_name
1555 FROM per_all_people_f
1556 WHERE person_id = p_person_id
1557 and g_run_date between effective_start_date and effective_end_date;
1558
1559 CURSOR c_phone(p_person_id IN NUMBER) IS
1560 SELECT phone_number
1561 FROM per_phones
1562 WHERE parent_table = 'PER_ALL_PEOPLE_F'
1563 AND parent_id = p_person_id
1564 AND phone_type = 'W1'
1565 AND g_run_date between date_from and nvl(date_to, to_date('31/12/4712', 'DD/MM/YYYY'));
1566
1567
1568 CURSOR c_mobile_phone(p_person_id IN NUMBER) IS
1569 SELECT phone_number
1570 FROM per_phones
1571 WHERE parent_table = 'PER_ALL_PEOPLE_F'
1572 AND parent_id = p_person_id
1573 AND phone_type = 'M'
1574 AND g_run_date between date_from and nvl(date_to, to_date('31/12/4712', 'DD/MM/YYYY'));
1575
1576 CURSOR c_pager(p_person_id IN NUMBER) IS
1577 SELECT phone_number
1578 FROM per_phones
1579 WHERE parent_table = 'PER_ALL_PEOPLE_F'
1580 AND parent_id = p_person_id
1581 AND phone_type = 'P'
1582 AND g_run_date between date_from and nvl(date_to, to_date('31/12/4712', 'DD/MM/YYYY'));
1583
1584 --Mugsrin
1585 --ER # 2669284. This cursor selects old values from jtf_rs_resource_extns table.
1586 --Then old values are compared with new values to find out
1587 --whether a row has to be updated and also to insert null for unmodified columns.
1588 CURSOR rr_old_cur(l_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE)
1589 IS
1590 SELECT
1591 source_number,
1592 source_name,
1593 source_first_name,
1594 source_middle_name,
1595 source_last_name,
1596 source_phone,
1597 source_email,
1598 source_job_id,
1599 source_job_title,
1600 address_id,
1601 source_address1,
1602 source_address2,
1603 source_address3,
1604 source_city,
1605 source_country,
1606 source_postal_code,
1607 source_mgr_id,
1608 source_mgr_name,
1609 source_business_grp_id,
1610 source_business_grp_name,
1611 source_office,
1612 source_location,
1613 source_mailstop,
1614 source_mobile_phone,
1615 source_pager,
1616 resource_name,
1617 end_date_active,
1618 person_party_id
1619 FROM jtf_rs_resource_extns_vl
1620 WHERE resource_id = l_resource_id;
1621
1622 rr_old_rec rr_old_cur%rowtype;
1623
1624 --ER # 2669284. Resource old values
1625 l_source_number_o jtf_rs_resource_extns.source_number%type;
1626 l_source_name_o jtf_rs_resource_extns.source_name%type;
1627 l_source_first_name_o jtf_rs_resource_extns.source_first_name%type;
1628 l_source_middle_name_o jtf_rs_resource_extns.source_middle_name%type;
1629 l_source_last_name_o jtf_rs_resource_extns.source_last_name%type;
1630 l_source_phone_o jtf_rs_resource_extns.source_phone%type;
1631 l_source_email_o jtf_rs_resource_extns.source_email%type;
1632 l_source_job_id_o jtf_rs_resource_extns.source_job_id%type;
1633 l_source_job_title_o jtf_rs_resource_extns.source_job_title%type;
1634 l_address_id_o jtf_rs_resource_extns.address_id%type ;
1635 l_source_address1_o jtf_rs_resource_extns.source_address1%type;
1636 l_source_address2_o jtf_rs_resource_extns.source_address2%type;
1637 l_source_address3_o jtf_rs_resource_extns.source_address3%type;
1638 l_source_city_o jtf_rs_resource_extns.source_city%type;
1639 l_source_country_o jtf_rs_resource_extns.source_country%type;
1640 l_source_postal_code_o jtf_rs_resource_extns.source_postal_code%type;
1641 l_source_mgr_id_o jtf_rs_resource_extns.source_mgr_id%type;
1642 l_source_mgr_name_o jtf_rs_resource_extns.source_mgr_name%type;
1643 l_source_business_grp_id_o jtf_rs_resource_extns.source_business_grp_id%type;
1644 l_source_business_grp_name_o jtf_rs_resource_extns.source_business_grp_name%type;
1645 l_source_office_o jtf_rs_resource_extns.source_office%type;
1646 l_source_location_o jtf_rs_resource_extns.source_location%type;
1647 l_source_mailstop_o jtf_rs_resource_extns.source_mailstop%type;
1648 l_source_mobile_phone_o jtf_rs_resource_extns.source_mobile_phone%type;
1649 l_source_pager_o jtf_rs_resource_extns.source_pager%type;
1650 l_resource_name_o jtf_rs_resource_extns_vl.resource_name%type;
1651 l_end_date_active_o jtf_rs_resource_extns_vl.end_date_active%type;
1652 l_party_id_o jtf_rs_resource_extns_vl.person_party_id%type;
1653
1654 --ER # 2669284. Resource new values
1655 l_source_number_n jtf_rs_resource_extns.source_number%type;
1656 l_source_name_n jtf_rs_resource_extns.source_name%type;
1657 l_source_first_name_n jtf_rs_resource_extns.source_first_name%type;
1658 l_source_middle_name_n jtf_rs_resource_extns.source_middle_name%type;
1659 l_source_last_name_n jtf_rs_resource_extns.source_last_name%type;
1660 l_source_phone_n jtf_rs_resource_extns.source_phone%type;
1661 l_source_email_n jtf_rs_resource_extns.source_email%type;
1662 l_source_job_id_n jtf_rs_resource_extns.source_job_id%type;
1663 l_source_job_title_n jtf_rs_resource_extns.source_job_title%type;
1664 l_address_id_n jtf_rs_resource_extns.address_id%type ;
1665 l_source_address1_n jtf_rs_resource_extns.source_address1%type;
1666 l_source_address2_n jtf_rs_resource_extns.source_address2%type;
1667 l_source_address3_n jtf_rs_resource_extns.source_address3%type;
1668 l_source_city_n jtf_rs_resource_extns.source_city%type;
1669 l_source_country_n jtf_rs_resource_extns.source_country%type;
1670 l_source_postal_code_n jtf_rs_resource_extns.source_postal_code%type;
1671 l_source_mgr_id_n jtf_rs_resource_extns.source_mgr_id%type;
1672 l_source_mgr_name_n jtf_rs_resource_extns.source_mgr_name%type;
1673 l_source_business_grp_id_n jtf_rs_resource_extns.source_business_grp_id%type;
1674 l_source_business_grp_name_n jtf_rs_resource_extns.source_business_grp_name%type;
1675 l_source_office_n jtf_rs_resource_extns.source_office%type;
1676 l_source_location_n jtf_rs_resource_extns.source_location%type;
1677 l_source_mailstop_n jtf_rs_resource_extns.source_mailstop%type;
1678 l_source_mobile_phone_n jtf_rs_resource_extns.source_mobile_phone%type;
1679 l_source_pager_n jtf_rs_resource_extns.source_pager%type;
1680 l_resource_name_n jtf_rs_resource_extns_vl.resource_name%type;
1681 l_end_date_active_n jtf_rs_resource_extns_vl.end_date_active%type;
1682 l_party_id_n jtf_rs_resource_extns_vl.person_party_id%type;
1683
1684 l_resource_extn_aud_id jtf_rs_resource_extn_aud.resource_audit_id%type;
1685 l_row_id varchar2(24) := null;
1686 /* Moved the initial assignment of below variables to inside begin */
1687 l_is_to_be_updated varchar2(1);
1688 l_update_extn varchar2(1);
1689 l_rehire_rev_emp_flag varchar2(1);
1690 l_activate_salesreps varchar2(1);
1691
1692 l_phone VARCHAR2(240);
1693 l_mobile_phone VARCHAR2(240);
1694 l_pager VARCHAR2(240);
1695 l_mgr_name VARCHAR2(240);
1696 l_job_name VARCHAR2(240);
1697 l_org_name VARCHAR2(240);
1698 l_end_date_active jtf_rs_resource_extns_vl.end_date_active%type;
1699 -- l_sysdate DATE := SYSDATE;
1700 /* Moved the initial assignment of below variables to inside begin */
1701 l_user_id NUMBER;
1702 l_login NUMBER;
1703
1704 l_job_id NUMBER;
1705 l_party_id NUMBER;
1706 -- l_role_relate_id NUMBER;
1707 l_role_name jtf_rs_roles_vl.role_name%TYPE;
1708 l_role_id NUMBER;
1709
1710 l_new_job_title VARCHAR2(240);
1711 l_new_job_id NUMBER;
1712 l_new_job_assignment_id NUMBER;
1713 l_active_days NUMBER;
1714
1715 r_return_status VARCHAR2(2);
1716 r_msg_count NUMBER;
1717 r_msg_data VARCHAR2(2000);
1718
1719 l_derived_job_id NUMBER;
1720
1721 CURSOR c_derive_job_id (l_job_name VARCHAR2, l_business_group_id NUMBER) IS
1722 SELECT job_id
1723 FROM per_jobs
1724 WHERE NAME = l_job_name
1725 AND business_group_id = l_business_group_id;
1726
1727 PROCEDURE update_job_roles
1728 (p_old_job_id IN NUMBER,
1729 p_new_job_title IN VARCHAR2,
1730 p_new_job_id IN NUMBER,
1731 p_new_job_assignment_id IN NUMBER,
1732 p_person_id IN NUMBER,
1733 p_resource_id IN NUMBER,
1734 p_assignment_type IN VARCHAR2
1735 )
1736 IS
1737
1738 l_api_version CONSTANT NUMBER := 1.0;
1739 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_JOB_ROLES';
1740 l_return_status VARCHAR2(2);
1741 l_msg_count NUMBER;
1742 l_msg_data VARCHAR2(2000);
1743 -- l_old_job_id NUMBER;
1744 l_role_relate_id NUMBER;
1745 -- l_sysdate date := trunc(sysdate);
1746
1747 l_g_miss_date date;
1748
1749 CURSOR c_job_roles (l_job_id IN NUMBER) IS
1750 SELECT role_id
1751 FROM jtf_rs_job_roles
1752 WHERE job_id = l_job_id;
1753
1754 l_job_roles_rec c_job_roles%ROWTYPE;
1755
1756 -- Added the below cursor to get the role type for a role id.
1757 CURSOR c_get_role_type (l_role_id jtf_rs_roles_vl.role_id%TYPE) IS
1758 SELECT role_type_code
1759 FROM jtf_rs_roles_b
1760 WHERE role_id = l_role_id;
1761
1762 l_role_type_code jtf_rs_roles_b.role_type_code%TYPE;
1763
1764 -- Added the below cursor to check the salesrep exists for the resource_id. If the salesrep does not esists
1765 -- we will not create the sales compensation roles.
1766
1767 -- removed the code to check the org_id condition.
1768 -- As per bug # 4391220, JTF_RS_ROLE_RELATE_VUHK has been changed to be MOAC compliant
1769 -- The new validation check that a Resource it associated with at least one
1770 -- Salesrep record in JTF_RS_SALESREPS.
1771 CURSOR c_salesrep_check(c_resource_id number) IS
1772 select 'Y'
1773 from jtf_rs_salesreps
1774 where resource_id = c_resource_id;
1775 -- and nvl(org_id,-99) = nvl(c_org_id,-99);
1776
1777 l_salesrep_extsts varchar2(2);
1778 -- l_org_id number;
1779
1780 -- CURSOR c_job_id (l_job_name IN per_jobs.name%TYPE) IS
1781 -- SELECT job_id
1782 -- FROM per_jobs
1783 -- WHERE name = l_job_name;
1784
1785 CURSOR c_job_role_relations (l_role_id IN NUMBER, l_resource_id IN NUMBER) IS
1786 SELECT rr.role_relate_id, rr.object_version_number
1787 FROM jtf_rs_role_relations rr
1788 WHERE rr.role_resource_type = 'RS_INDIVIDUAL'
1789 AND rr.role_resource_id = l_resource_id
1790 AND rr.role_id = l_role_id
1791 AND nvl(rr.delete_flag,'N') <> 'Y'
1792 AND g_run_date between trunc(start_date_active) and trunc(nvl(end_date_active,g_run_date));
1793
1794 l_job_role_relations_rec c_job_role_relations%ROWTYPE;
1795
1796 /* The below cursor will get all the roles that has an end_date greater than or equal to the start date passed
1797 and also the role exists in job roles table. Bug # 3300409 */
1798
1799 CURSOR c_res_roles_enddate (l_resource_id IN NUMBER, l_new_role_st_dt DATE) IS
1800 SELECT rr.role_relate_id, rr.object_version_number, rr.role_id
1801 FROM jtf_rs_role_relations rr
1802 WHERE exists (select null
1803 from jtf_rs_job_roles jr
1804 where jr.role_id = rr.role_id
1805 and jr.job_id not in (nvl(p_old_job_id,-99)))
1806 AND rr.role_resource_type = 'RS_INDIVIDUAL'
1807 AND rr.role_resource_id = l_resource_id
1808 AND nvl(rr.delete_flag,'N') <> 'Y'
1809 AND trunc(nvl(end_date_active,g_run_date)) >= trunc(l_new_role_st_dt);
1810
1811 CURSOR c_role_name (l_role_id IN jtf_rs_roles_vl.role_id%TYPE) IS
1812 SELECT role_name
1813 FROM jtf_rs_roles_vl
1814 WHERE role_id = l_role_id;
1815
1816 CURSOR c_get_assignment_id(p_job_id IN NUMBER, p_person_id IN NUMBER) IS
1817 SELECT assignment_id
1818 FROM PER_ALL_ASSIGNMENTS_F
1819 WHERE job_id = p_job_id
1820 AND primary_flag = 'Y'
1821 -- AND assignment_type = 'E'
1822 AND assignment_type = p_assignment_type
1823 AND person_id = p_person_id
1824 ORDER BY assignment_id desc;
1825
1826 l_asg_start_date date;
1827 l_asg_end_date date;
1828 l_asg_new_end_date date;
1829 l_assignment_id number;
1830 /* Moved the initial assignment of below variable to inside begin */
1831 l_res_roles_enddated varchar2(1);
1832
1833 BEGIN
1834
1835 l_g_miss_date := trunc(to_date('31-12-4712','DD-MM-YYYY'));
1836 l_res_roles_enddated := 'N';
1837 -- l_old_job_id := NULL;
1838
1839 -- To get the current operating unit. if l_org_id is NULL, then it is a single org
1840 -- select to_number(decode(substrb(userenv('CLIENT_INFO'),1,1),' ',null,substrb(userenv('CLIENT_INFO'),1,10)))
1841 -- into l_org_id
1842 -- from dual;
1843
1844 l_salesrep_extsts := 'N';
1845 -- if l_org_id is NOT NULL then
1846 OPEN c_salesrep_check(p_resource_id);
1847 FETCH c_salesrep_check INTO l_salesrep_extsts;
1848 CLOSE c_salesrep_check;
1849 -- end if;
1850
1851 IF (NVL (p_new_job_id, -99) <> NVL (p_old_job_id, -99)) THEN /*Job changed*/
1852 -- IF (NVL (p_new_job_title, 'jobnamenullerror') <> NVL (p_old_job_title, 'jobnamenullerror')) THEN /*Job changed*/
1853 -- IF l_job_name is NULL then /* Value to null */
1854
1855 OPEN c_job_roles(p_new_job_id);
1856 FETCH c_job_roles INTO l_job_roles_rec;
1857 IF c_job_roles%FOUND THEN
1858 l_asg_start_date := get_asg_start_date(p_new_job_id,p_person_id,p_new_job_assignment_id,p_assignment_type);
1859 ELSE
1860 l_asg_start_date := NULL;
1861 END IF;
1862 CLOSE c_job_roles;
1863
1864 -- OPEN c_job_id(p_old_job_title);
1865 -- FETCH c_job_id INTO l_old_job_id;
1866 -- IF c_job_id%FOUND THEN
1867
1868 OPEN c_job_roles(p_old_job_id);
1869 FETCH c_job_roles INTO l_job_roles_rec;
1870 IF c_job_roles%FOUND THEN
1871
1872 OPEN c_get_assignment_id(p_old_job_id,p_person_id);
1873 FETCH c_get_assignment_id INTO l_assignment_id;
1874 CLOSE c_get_assignment_id;
1875
1876
1877 l_asg_end_date := get_asg_end_date(p_old_job_id,p_person_id,l_assignment_id,p_assignment_type) + l_active_days;
1878
1879 IF (l_asg_start_date is NOT NULL AND l_asg_start_date <= l_asg_end_date) then
1880 -- l_asg_start_date is not null implies there is a new job that has a job role mapping.
1881 -- So This code will get executed only if the old job end date + profile overlaps with new job's start date
1882 l_asg_end_date := l_asg_start_date - 1;
1883 END IF;
1884
1885 LOOP
1886 OPEN c_job_role_relations(l_job_roles_rec.role_id, p_resource_id);
1887 FETCH c_job_role_relations INTO l_job_role_relations_rec;
1888 IF c_job_role_relations%FOUND THEN
1889 JTF_RS_ROLE_RELATE_PVT.UPDATE_RESOURCE_ROLE_RELATE(
1890 p_api_version => 1.0,
1891 p_init_msg_list => fnd_api.g_true,
1892 p_commit => fnd_api.g_false,
1893 p_role_relate_id => l_job_role_relations_rec.role_relate_id,
1894 p_end_date_active => l_asg_end_date,
1895 p_object_version_num => l_job_role_relations_rec.object_version_number,
1896 x_return_status => r_return_status,
1897 x_msg_count => r_msg_count,
1898 x_msg_data => r_msg_data
1899 );
1900 IF ( r_return_status <> fnd_api.g_ret_sts_success) THEN
1901 l_role_name := NULL;
1902 OPEN c_role_name (l_job_roles_rec.role_id);
1903 FETCH c_role_name INTO l_role_name;
1904 CLOSE c_role_name;
1905 fnd_message.set_name('JTF', 'JTF_RS_CP_SYN_EMP_UPD_ROL_ERR');
1906 fnd_message.set_token('P_EMPLOYEE_NAME', l_ppf_rec.full_name);
1907 fnd_message.set_token('P_JOB_TITLE', p_new_job_title);
1908 fnd_message.set_token('P_ROLE_NAME', l_role_name);
1909 fnd_file.put_line(fnd_file.log, fnd_message.get);
1910 for i in 1..r_msg_count loop
1911 fnd_file.put_line(fnd_file.log, (fnd_msg_pub.get(i,p_encoded => fnd_api.g_false)));
1912 end loop;
1913 CLOSE c_job_roles;
1914 -- CLOSE c_job_id;
1915 fnd_msg_pub.delete_msg;
1916 fnd_file.new_line(fnd_file.log,1);
1917 raise fnd_api.g_exc_error;
1918 END IF;
1919 END IF;
1920 CLOSE c_job_role_relations;
1921 FETCH c_job_roles INTO l_job_roles_rec;
1922 EXIT WHEN c_job_roles%NOTFOUND;
1923 END LOOP;
1924 END IF;
1925 CLOSE c_job_roles;
1926 -- END IF;
1927 -- CLOSE c_job_id;
1928 -- ELSIF p_old_job_title is NULL then /* null to value */
1929
1930 OPEN c_job_roles(p_new_job_id);
1931 FETCH c_job_roles INTO l_job_roles_rec;
1932 IF c_job_roles%FOUND THEN
1933
1934 /* l_asg_start_date is already derived above */
1935 -- l_asg_start_date := get_asg_start_date(p_new_job_id,p_person_id,p_new_job_assignment_id,p_assignment_type);
1936 l_res_roles_enddated := 'N'; /* Initializing the flag to N for every resource. */
1937
1938 LOOP
1939 OPEN c_job_role_relations(l_job_roles_rec.role_id,p_resource_id);
1940 FETCH c_job_role_relations INTO l_job_role_relations_rec;
1941 IF c_job_role_relations%NOTFOUND THEN /* no active resource role */
1942
1943 /* Below IF loop is added to Fix bug # 3300409 */
1944 /* This loop will execute only once for a particular resource */
1945 IF l_res_roles_enddated = 'N' THEN
1946 /* This cursor will get all the roles that has an end_date greater than or equal to the start date
1947 of the new role needs to be created */
1948 FOR i in c_res_roles_enddate(p_resource_id,l_asg_start_date) LOOP
1949 JTF_RS_ROLE_RELATE_PVT.UPDATE_RESOURCE_ROLE_RELATE(
1950 p_api_version => 1.0,
1951 p_init_msg_list => fnd_api.g_true,
1952 p_commit => fnd_api.g_false,
1953 p_role_relate_id => i.role_relate_id,
1954 p_end_date_active => l_asg_start_date-1,
1955 p_object_version_num => i.object_version_number,
1956 x_return_status => r_return_status,
1957 x_msg_count => r_msg_count,
1958 x_msg_data => r_msg_data
1959 );
1960 IF ( r_return_status <> fnd_api.g_ret_sts_success) THEN
1961 l_role_name := NULL;
1962 OPEN c_role_name (i.role_id);
1963 FETCH c_role_name INTO l_role_name;
1964 CLOSE c_role_name;
1965 fnd_message.set_name('JTF', 'JTF_RS_CP_SYN_EMP_UPD_ROL_ERR');
1966 fnd_message.set_token('P_EMPLOYEE_NAME', l_ppf_rec.full_name);
1967 fnd_message.set_token('P_JOB_TITLE', p_new_job_title);
1968 fnd_message.set_token('P_ROLE_NAME', l_role_name);
1969 fnd_file.put_line(fnd_file.log, fnd_message.get);
1970 for j in 1..r_msg_count loop
1971 fnd_file.put_line(fnd_file.log, (fnd_msg_pub.get(j,p_encoded => fnd_api.g_false)));
1972 end loop;
1973 fnd_msg_pub.delete_msg;
1974 fnd_file.new_line(fnd_file.log,1);
1975 raise fnd_api.g_exc_error;
1976 END IF;
1977 END LOOP;
1978 l_res_roles_enddated := 'Y';
1979 END IF;
1980 /* End of fix for bug # 3300409 */
1981
1982 -- Below cursor will retrive the role_type_code for a role id
1983 l_role_type_code := NULL;
1984 OPEN c_get_role_type(l_job_roles_rec.role_id);
1985 FETCH c_get_role_type INTO l_role_type_code;
1986 CLOSE c_get_role_type;
1987
1988 -- Check for Sales Comp Roles and salesrep exists
1989 -- Will create the SALES_COMP roles only if the resource is a salesperson.
1990 if not (l_salesrep_extsts = 'N' and l_role_type_code = 'SALES_COMP') then
1991
1992 -- To get the end date of the new job
1993 l_asg_new_end_date := get_asg_end_date(p_new_job_id,p_person_id,p_new_job_assignment_id,p_assignment_type);
1994
1995 -- If the end date of the new job is g_miss_date, replace the date with NULL.
1996 -- Else add profile dayes to the end date
1997 if (l_asg_new_end_date = l_g_miss_date) then
1998 l_asg_new_end_date := NULL;
1999 else
2000 l_asg_new_end_date := l_asg_new_end_date + l_active_days;
2001 end if;
2002
2003 JTF_RS_ROLE_RELATE_PVT.CREATE_RESOURCE_ROLE_RELATE(
2004 p_api_version => 1.0,
2005 p_init_msg_list => fnd_api.g_true,
2006 p_commit => fnd_api.g_false,
2007 p_role_resource_type => 'RS_INDIVIDUAL',
2008 p_role_resource_id => p_resource_id,
2009 p_role_id => l_job_roles_rec.role_id,
2010 p_start_date_active => l_asg_start_date,
2011 p_end_date_active => l_asg_new_end_date,
2012 x_return_status => r_return_status,
2013 x_msg_count => r_msg_count,
2014 x_msg_data => r_msg_data,
2015 x_role_relate_id => l_role_relate_id
2016 );
2017 IF ( r_return_status <> fnd_api.g_ret_sts_success) THEN
2018 l_role_name := NULL;
2019 OPEN c_role_name (l_job_roles_rec.role_id);
2020 FETCH c_role_name INTO l_role_name;
2021 CLOSE c_role_name;
2022 fnd_message.set_name('JTF', 'JTF_RS_CP_SYN_EMP_CRT_ROL_ERR');
2023 fnd_message.set_token('P_EMPLOYEE_NAME', l_ppf_rec.full_name);
2024 fnd_message.set_token('P_JOB_TITLE', p_new_job_title);
2025 fnd_message.set_token('P_ROLE_NAME', l_role_name);
2026 fnd_file.put_line(fnd_file.log, fnd_message.get);
2027 for i in 1..r_msg_count loop
2028 fnd_file.put_line(fnd_file.log, (fnd_msg_pub.get(i,p_encoded => fnd_api.g_false)));
2029 end loop;
2030 fnd_msg_pub.delete_msg;
2031 fnd_file.new_line(fnd_file.log,1);
2032 raise fnd_api.g_exc_error;
2033 END IF;
2034 end if; -- Check for Sales Comp Roles and salesrep exists
2035 END IF;
2036 CLOSE c_job_role_relations;
2037 FETCH c_job_roles INTO l_job_roles_rec;
2038 EXIT WHEN c_job_roles%NOTFOUND;
2039 END LOOP;
2040 END IF;
2041 CLOSE c_job_roles;
2042 ELSE /*Job not changed*/
2043
2044 OPEN c_job_roles(p_new_job_id);
2045 FETCH c_job_roles INTO l_job_roles_rec;
2046 IF c_job_roles%FOUND THEN /* all the roles from job roles */
2047
2048 l_asg_start_date := get_asg_start_date(p_new_job_id,p_person_id,p_new_job_assignment_id,p_assignment_type);
2049 l_res_roles_enddated := 'N'; /* Initializing the flag to N for every resource. */
2050
2051 LOOP
2052 OPEN c_job_role_relations(l_job_roles_rec.role_id,p_resource_id);
2053 FETCH c_job_role_relations INTO l_job_role_relations_rec;
2054 IF c_job_role_relations%NOTFOUND THEN /* no active resource role */
2055
2056 /* Below IF loop is added to Fix bug # 3300409 */
2057 /* This loop will execute only once for a particular resource */
2058 IF l_res_roles_enddated = 'N' THEN
2059 /* This cursor will get all the roles that has an end_date greater than or equal to the start date
2060 of the new role needs to be created */
2061 FOR i in c_res_roles_enddate(p_resource_id,l_asg_start_date) LOOP
2062 JTF_RS_ROLE_RELATE_PVT.UPDATE_RESOURCE_ROLE_RELATE(
2063 p_api_version => 1.0,
2064 p_init_msg_list => fnd_api.g_true,
2065 p_commit => fnd_api.g_false,
2066 p_role_relate_id => i.role_relate_id,
2067 p_end_date_active => l_asg_start_date-1,
2068 p_object_version_num => i.object_version_number,
2069 x_return_status => r_return_status,
2070 x_msg_count => r_msg_count,
2071 x_msg_data => r_msg_data
2072 );
2073 IF ( r_return_status <> fnd_api.g_ret_sts_success) THEN
2074 l_role_name := NULL;
2075 OPEN c_role_name (i.role_id);
2076 FETCH c_role_name INTO l_role_name;
2077 CLOSE c_role_name;
2078 fnd_message.set_name('JTF', 'JTF_RS_CP_SYN_EMP_UPD_ROL_ERR');
2079 fnd_message.set_token('P_EMPLOYEE_NAME', l_ppf_rec.full_name);
2080 fnd_message.set_token('P_JOB_TITLE', p_new_job_title);
2081 fnd_message.set_token('P_ROLE_NAME', l_role_name);
2082 fnd_file.put_line(fnd_file.log, fnd_message.get);
2083 for j in 1..r_msg_count loop
2084 fnd_file.put_line(fnd_file.log, (fnd_msg_pub.get(j,p_encoded => fnd_api.g_false)));
2085 end loop;
2086 fnd_msg_pub.delete_msg;
2087 fnd_file.new_line(fnd_file.log,1);
2088 raise fnd_api.g_exc_error;
2089 END IF;
2090 END LOOP;
2091 l_res_roles_enddated := 'Y';
2092 END IF;
2093 /* End of fix for bug # 3300409*/
2094
2095 -- Below cursor will retrive the role_type_code for a role id
2096 l_role_type_code := NULL;
2097 OPEN c_get_role_type(l_job_roles_rec.role_id);
2098 FETCH c_get_role_type INTO l_role_type_code;
2099 CLOSE c_get_role_type;
2100
2101 -- Check for Sales Comp Roles and salesrep exists
2102 -- Will create the SALES_COMP roles only if the resource is a salesperson.
2103 if not (l_salesrep_extsts = 'N' and l_role_type_code = 'SALES_COMP') then
2104
2105 -- To get the end date of the new job
2106 l_asg_new_end_date := get_asg_end_date(p_new_job_id,p_person_id,p_new_job_assignment_id,p_assignment_type);
2107
2108 -- If the end date of the new job is g_miss_date, replace the date with NULL.
2109 -- Else add profile dayes to the end date
2110 if (l_asg_new_end_date = l_g_miss_date) then
2111 l_asg_new_end_date := NULL;
2112 else
2113 l_asg_new_end_date := l_asg_new_end_date + l_active_days;
2114 end if;
2115
2116 JTF_RS_ROLE_RELATE_PVT.CREATE_RESOURCE_ROLE_RELATE(
2117 p_api_version => 1.0,
2118 p_init_msg_list => fnd_api.g_true,
2119 p_commit => fnd_api.g_false,
2120 p_role_resource_type => 'RS_INDIVIDUAL',
2121 p_role_resource_id => p_resource_id,
2122 p_role_id => l_job_roles_rec.role_id,
2123 p_start_date_active => l_asg_start_date,
2124 p_end_date_active => l_asg_new_end_date,
2125 x_return_status => r_return_status,
2126 x_msg_count => r_msg_count,
2127 x_msg_data => r_msg_data,
2128 x_role_relate_id => l_role_relate_id
2129 );
2130 IF ( r_return_status <> fnd_api.g_ret_sts_success) THEN
2131 l_role_name := NULL;
2132 OPEN c_role_name (l_job_roles_rec.role_id);
2133 FETCH c_role_name INTO l_role_name;
2134 CLOSE c_role_name;
2135 fnd_message.set_name('JTF', 'JTF_RS_CP_SYN_EMP_CRT_ROL_ERR');
2136 fnd_message.set_token('P_EMPLOYEE_NAME', l_ppf_rec.full_name);
2137 fnd_message.set_token('P_JOB_TITLE', p_new_job_title);
2138 fnd_message.set_token('P_ROLE_NAME', l_role_name);
2139 fnd_file.put_line(fnd_file.log, fnd_message.get);
2140 for i in 1..r_msg_count loop
2141 fnd_file.put_line(fnd_file.log, (fnd_msg_pub.get(i,p_encoded => fnd_api.g_false)));
2142 end loop;
2143 fnd_msg_pub.delete_msg;
2144 fnd_file.new_line(fnd_file.log,1);
2145 raise fnd_api.g_exc_error;
2146 END IF;
2147 end if; -- Check for Sales Comp Roles and salesrep exists
2148 END IF; /* no active resource role */
2149 CLOSE c_job_role_relations;
2150 FETCH c_job_roles INTO l_job_roles_rec;
2151 EXIT WHEN c_job_roles%NOTFOUND;
2152 END LOOP; /* all the roles from job roles */
2153 END IF;
2154 CLOSE c_job_roles;
2155 END IF; /*Job changed*/
2156
2157 IF c_job_roles%ISOPEN THEN
2158 CLOSE c_job_roles;
2159 END IF;
2160
2161 -- IF c_job_id%ISOPEN THEN
2162 -- CLOSE c_job_id;
2163 -- END IF;
2164
2165 IF c_job_role_relations%ISOPEN THEN
2166 CLOSE c_job_role_relations;
2167 END IF;
2168
2169 IF c_role_name%ISOPEN THEN
2170 CLOSE c_role_name;
2171 END IF;
2172
2173 IF c_get_assignment_id%ISOPEN THEN
2174 CLOSE c_get_assignment_id;
2175 END IF;
2176
2177 END update_job_roles;
2178
2179 BEGIN
2180
2181 l_sysdate := trunc(sysdate);
2182 l_is_to_be_updated := 'N';
2183 l_update_extn := 'N';
2184 l_rehire_rev_emp_flag := 'N';
2185 l_activate_salesreps := 'N';
2186 l_user_id := nvl(FND_GLOBAL.USER_ID,-1);
2187 l_login := nvl(FND_GLOBAL.LOGIN_ID,-1);
2188
2189 --Standard Start of API SAVEPOINT
2190
2191 SAVEPOINT UPDATE_EMPLOYEE_SP;
2192
2193 l_return_status := fnd_api.g_ret_sts_success;
2194
2195 FND_PROFILE.GET('JTF_RS_RESOURCE_ACTIVE_DAYS',l_active_days);
2196
2197 FOR l_c_resource IN c_resource LOOP
2198 BEGIN
2199 SAVEPOINT update_resource_sp;
2200
2201 l_rehire_rev_emp_flag := 'N';
2202 l_activate_salesreps := 'N';
2203
2204 OPEN c_ppf(l_c_resource.source_id);
2205 FETCH c_ppf INTO l_ppf_rec;
2206 IF c_ppf%FOUND THEN
2207
2208 if l_ppf_rec.current_employee_flag = 'Y' then
2209 l_source_number := l_ppf_rec.employee_number;
2210 l_assignment_type := 'E';
2211 else
2212 l_source_number := l_ppf_rec.npw_number;
2213 l_assignment_type := 'C';
2214 end if;
2215
2216 OPEN c_org(l_ppf_rec.business_group_id);
2217 FETCH c_org INTO l_org_name;
2218 IF c_org%NOTFOUND THEN
2219 l_org_name := NULL;
2220 END IF;
2221 CLOSE c_org;
2222
2223 OPEN c_phone(l_c_resource.source_id);
2224 FETCH c_phone INTO l_phone;
2225 IF c_phone%NOTFOUND THEN
2226 l_phone := NULL;
2227 END IF;
2228 CLOSE c_phone;
2229
2230 OPEN c_mobile_phone(l_c_resource.source_id);
2231 FETCH c_mobile_phone INTO l_mobile_phone;
2232 IF c_mobile_phone%NOTFOUND THEN
2233 l_mobile_phone := NULL;
2234 END IF;
2235 CLOSE c_mobile_phone;
2236
2237 OPEN c_pager(l_c_resource.source_id);
2238 FETCH c_pager INTO l_pager;
2239 IF c_pager%NOTFOUND THEN
2240 l_pager := NULL;
2241 END IF;
2242 CLOSE c_pager;
2243
2244 l_new_job_title := NULL;
2245 l_new_job_id := NULL;
2246 l_new_job_assignment_id := NULL;
2247
2248 OPEN c_asg(l_c_resource.source_id);
2249 FETCH c_asg INTO l_asg_rec;
2250 IF c_asg%FOUND THEN
2251
2252 IF l_asg_rec.job_id is NOT NULL then
2253 OPEN c_job(l_asg_rec.job_id);
2254 FETCH c_job INTO l_job_name;
2255 IF c_job%NOTFOUND THEN
2256 l_job_name := NULL;
2257
2258 END IF;
2259 CLOSE c_job;
2260 ELSE
2261 l_job_name := NULL;
2262 END IF;
2263
2264 l_new_job_title := l_job_name;
2265 l_new_job_id := l_asg_rec.job_id;
2266 l_new_job_assignment_id := l_asg_rec.assignment_id;
2267
2268 IF l_asg_rec.location_id is NOT NULL then
2269 OPEN c_loc(l_asg_rec.location_id);
2270 FETCH c_loc INTO l_loc_rec;
2271 IF c_loc%NOTFOUND THEN
2272 l_loc_rec := NULL;
2273 END IF;
2274 CLOSE c_loc;
2275 ELSE
2276 l_loc_rec := NULL;
2277 END IF;
2278
2279 IF l_asg_rec.supervisor_id is NOT NULL then
2280 OPEN c_mgr(l_asg_rec.supervisor_id);
2281 FETCH c_mgr INTO l_mgr_name;
2282 IF c_mgr%NOTFOUND THEN
2283 l_mgr_name := NULL;
2284 END IF;
2285 CLOSE c_mgr;
2286 ELSE
2287 l_mgr_name := NULL;
2288 END IF;
2289
2290 ELSE
2291 l_asg_rec := NULL;
2292 l_job_name := NULL;
2293 l_loc_rec := NULL;
2294 l_mgr_name := NULL;
2295 END IF;
2296 CLOSE c_asg;
2297
2298 OPEN c_check_rehire_rev_emp(l_c_resource.source_id);
2299 FETCH c_check_rehire_rev_emp INTO l_rehire_rev_emp_flag;
2300 IF l_rehire_rev_emp_flag = 'Y' THEN
2301 l_end_date_active := NULL;
2302 ELSE
2303 l_end_date_active := l_c_resource.end_date_active;
2304 END IF;
2305 CLOSE c_check_rehire_rev_emp;
2306
2307 OPEN c_salesreps(l_c_resource.resource_id);
2308 FETCH c_salesreps INTO l_salesreps_rec;
2309 IF c_salesreps%FOUND THEN
2310 l_activate_salesreps := 'Y';
2311 ELSE
2312 l_activate_salesreps := 'N';
2313 END IF;
2314 CLOSE c_salesreps;
2315
2316 ELSE
2317 l_ppf_rec := NULL;
2318 l_org_name:= NULL;
2319 l_phone := NULL;
2320 l_mobile_phone := NULL;
2321 l_pager := NULL;
2322 l_asg_rec := NULL;
2323 l_job_name := NULL;
2324 l_new_job_id := NULL;
2325 l_loc_rec := NULL;
2326 l_mgr_name := NULL;
2327 l_end_date_active := l_c_resource.end_date_active;
2328 l_source_number := l_c_resource.source_number;
2329 END IF;
2330 CLOSE c_ppf;
2331
2332 l_is_to_be_updated := 'N';
2333 l_update_extn := 'N';
2334
2335 --ER # 2669284. Compare old and new values and update l_is_to_be_updated variable to 'Y'
2336 --if any change has occured.
2337 open rr_old_cur(l_c_resource.resource_id);
2338 FETCH rr_old_cur into rr_old_rec;
2339 close rr_old_cur;
2340
2341 --ER # 2669284. Resource old values
2342 l_source_number_o := null;
2343 l_source_name_o := null;
2344 l_source_first_name_o := null;
2345 l_source_middle_name_o := null;
2346 l_source_last_name_o := null;
2347 l_source_phone_o := null;
2348 l_source_email_o := null;
2349 l_source_job_id_o := null;
2350 l_source_job_title_o := null;
2351 l_address_id_o := null;
2352 l_source_address1_o := null;
2353 l_source_address2_o := null;
2354 l_source_address3_o := null;
2355 l_source_city_o := null;
2356 l_source_country_o := null;
2357 l_source_postal_code_o := null;
2358 l_source_mgr_id_o := null;
2359 l_source_mgr_name_o := null;
2360 l_source_business_grp_id_o := null;
2361 l_source_business_grp_name_o := null;
2362 l_source_office_o := null;
2363 l_source_location_o := null;
2364 l_source_mailstop_o := null;
2365 l_source_mobile_phone_o := null;
2366 l_source_pager_o := null;
2367 l_resource_name_o := null;
2368 l_end_date_active_o := null;
2369
2370 --ER # 2669284. Resource new values
2371 l_source_number_n := null;
2372 l_source_name_n := null;
2373 l_source_first_name_n := null;
2374 l_source_middle_name_n := null;
2375 l_source_last_name_n := null;
2376 l_source_phone_n := null;
2377 l_source_email_n := null;
2378 l_source_job_id_n := null;
2379 l_source_job_title_n := null;
2380 l_address_id_n := null;
2381 l_source_address1_n := null;
2382 l_source_address2_n := null;
2383 l_source_address3_n := null;
2384 l_source_city_n := null;
2385 l_source_country_n := null;
2386 l_source_postal_code_n := null;
2387 l_source_mgr_id_n := null;
2388 l_source_mgr_name_n := null;
2389 l_source_business_grp_id_n := null;
2390 l_source_business_grp_name_n := null;
2391 l_source_office_n := null;
2392 l_source_location_n := null;
2393 l_source_mailstop_n := null;
2394 l_source_mobile_phone_n := null;
2395 l_source_pager_n := null;
2396 l_resource_name_n := null;
2397 l_end_date_active_n := null;
2398
2399 if nvl(l_source_number, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_number, fnd_api.g_miss_char)
2400 then
2401 l_source_number_o := rr_old_rec.source_number;
2402 l_source_number_n := l_source_number;
2403 l_is_to_be_updated := 'Y';
2404
2405 end if;
2406
2407 if nvl(l_ppf_rec.full_name, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_name, fnd_api.g_miss_char)
2408 then
2409 l_source_name_o := rr_old_rec.source_name;
2410 l_source_name_n := l_ppf_rec.full_name;
2411 l_is_to_be_updated := 'Y';
2412 end if;
2413
2414 if nvl(l_ppf_rec.first_name, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_first_name, fnd_api.g_miss_char)
2415 then
2416 l_source_first_name_o := rr_old_rec.source_first_name;
2417 l_source_first_name_n := l_ppf_rec.first_name;
2418 l_is_to_be_updated := 'Y';
2419 end if;
2420
2421 if nvl(l_ppf_rec.middle_names, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_middle_name, fnd_api.g_miss_char)
2422 then
2423 l_source_middle_name_o := rr_old_rec.source_middle_name;
2424 l_source_middle_name_n := l_ppf_rec.middle_names;
2425 l_is_to_be_updated := 'Y';
2426 end if;
2427
2428 if nvl(l_ppf_rec.last_name, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_last_name, fnd_api.g_miss_char)
2429 then
2430 l_source_last_name_o := rr_old_rec.source_last_name;
2431 l_source_last_name_n := l_ppf_rec.last_name;
2432 l_is_to_be_updated := 'Y';
2433 end if;
2434
2435 if nvl(l_phone, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_phone, fnd_api.g_miss_char)
2436 then
2437 l_source_phone_o := rr_old_rec.source_phone;
2438 l_source_phone_n := l_phone;
2439 l_is_to_be_updated := 'Y';
2440 end if;
2441
2442 if nvl(l_ppf_rec.email_address, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_email, fnd_api.g_miss_char)
2443 then
2444 l_source_email_o := rr_old_rec.source_email;
2445 l_source_email_n := l_ppf_rec.email_address;
2446 l_is_to_be_updated := 'Y';
2447 end if;
2448
2449
2450 if nvl(l_asg_rec.location_id, -1) <> nvl(rr_old_rec.address_id, -1)
2451 then
2452 l_address_id_o := rr_old_rec.address_id;
2453 l_address_id_n := l_asg_rec.location_id ;
2454 l_is_to_be_updated := 'Y';
2455 end if;
2456
2457 if nvl(l_ppf_rec.party_id, fnd_api.g_miss_num) <> nvl(rr_old_rec.person_party_id, fnd_api.g_miss_num)
2458 then
2459 l_party_id_o := rr_old_rec.person_party_id;
2460 l_party_id_n := l_ppf_rec.party_id;
2461 l_is_to_be_updated := 'Y';
2462 end if;
2463
2464 if nvl(l_new_job_id, fnd_api.g_miss_num) <> nvl(rr_old_rec.source_job_id, fnd_api.g_miss_num)
2465 then
2466 l_source_job_id_o := rr_old_rec.source_job_id;
2467 l_source_job_id_n := l_new_job_id;
2468 l_is_to_be_updated := 'Y';
2469 end if;
2470
2471 if nvl(l_job_name, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_job_title, fnd_api.g_miss_char)
2472 then
2473 l_source_job_title_o := rr_old_rec.source_job_title;
2474 l_source_job_title_n := l_job_name;
2475 l_is_to_be_updated := 'Y';
2476 end if;
2477
2478 if nvl(l_loc_rec.address_line_1, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_address1, fnd_api.g_miss_char)
2479 then
2480 l_source_address1_o := rr_old_rec.source_address1;
2481 l_source_address1_n := l_loc_rec.address_line_1;
2482 l_is_to_be_updated := 'Y';
2483 end if;
2484
2485 if nvl(l_loc_rec.address_line_2, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_address2, fnd_api.g_miss_char)
2486 then
2487 l_source_address2_o := rr_old_rec.source_address2;
2488 l_source_address2_n := l_loc_rec.address_line_1;
2489 l_is_to_be_updated := 'Y';
2490 end if;
2491
2492 if nvl(l_loc_rec.address_line_3, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_address3, fnd_api.g_miss_char)
2493 then
2494 l_source_address3_o := rr_old_rec.source_address3;
2495 l_source_address3_n := l_loc_rec.address_line_3;
2496 l_is_to_be_updated := 'Y';
2497 end if;
2498
2499 if nvl(l_loc_rec.town_or_city, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_city, fnd_api.g_miss_char)
2500 then
2501 l_source_city_o := rr_old_rec.source_city;
2502 l_source_city_n := l_loc_rec.town_or_city;
2503 l_is_to_be_updated := 'Y';
2504 end if;
2505
2506 if nvl(l_loc_rec.postal_code, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_postal_code, fnd_api.g_miss_char)
2507 then
2508 l_source_postal_code_o := rr_old_rec.source_postal_code;
2509 l_source_postal_code_n := l_loc_rec.postal_code;
2510 l_is_to_be_updated := 'Y';
2511 end if;
2512
2513 if nvl(l_loc_rec.country, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_country, fnd_api.g_miss_char)
2514 then
2515 l_source_country_o := rr_old_rec.source_country;
2516 l_source_country_n := l_loc_rec.country;
2517 l_is_to_be_updated := 'Y';
2518 end if;
2519
2520 if nvl(l_asg_rec.supervisor_id, fnd_api.g_miss_num) <> nvl(rr_old_rec.source_mgr_id, fnd_api.g_miss_num)
2521 then
2522 l_source_mgr_id_o := rr_old_rec.source_mgr_id;
2523 l_source_mgr_id_n := l_asg_rec.supervisor_id;
2524 l_is_to_be_updated := 'Y';
2525 end if;
2526
2527 if nvl(l_mgr_name, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_mgr_name, fnd_api.g_miss_char)
2528 then
2529 l_source_mgr_name_o := rr_old_rec.source_mgr_name;
2530 l_source_mgr_name_n := l_mgr_name;
2531 l_is_to_be_updated := 'Y';
2532 end if;
2533
2534 if nvl(l_ppf_rec.business_group_id, fnd_api.g_miss_num) <> nvl(rr_old_rec.source_business_grp_id, fnd_api.g_miss_num)
2535 then
2536 l_source_business_grp_id_o := rr_old_rec.source_business_grp_id;
2537 l_source_business_grp_id_n := l_ppf_rec.business_group_id;
2538 l_is_to_be_updated := 'Y';
2539 end if;
2540
2541 if nvl(l_org_name, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_business_grp_name, fnd_api.g_miss_char)
2542 then
2543 l_source_business_grp_name_o := rr_old_rec.source_business_grp_name;
2544 l_source_business_grp_name_n := l_org_name;
2545 l_is_to_be_updated := 'Y';
2546 end if;
2547
2548 if nvl(l_ppf_rec.office_number, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_office, fnd_api.g_miss_char)
2549 then
2550 l_source_office_o := rr_old_rec.source_office;
2551 l_source_office_n := l_ppf_rec.office_number;
2552 l_is_to_be_updated := 'Y';
2553 end if;
2554
2555 if nvl(l_ppf_rec.internal_location, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_location, fnd_api.g_miss_char)
2556 then
2557 l_source_location_o := rr_old_rec.source_location;
2558 l_source_location_n := l_ppf_rec.internal_location;
2559 l_is_to_be_updated := 'Y';
2560 end if;
2561
2562 if nvl(l_ppf_rec.mailstop, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_mailstop, fnd_api.g_miss_char)
2563 then
2564 l_source_mailstop_o := rr_old_rec.source_mailstop;
2565 l_source_mailstop_n := l_ppf_rec.mailstop;
2566 l_is_to_be_updated := 'Y';
2567 end if;
2568
2569 if nvl(l_mobile_phone, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_mobile_phone, fnd_api.g_miss_char)
2570 then
2571 l_source_mobile_phone_o := rr_old_rec.source_mobile_phone;
2572 l_source_mobile_phone_n := l_mobile_phone;
2573 l_is_to_be_updated := 'Y';
2574 end if;
2575
2576 if nvl(l_pager, fnd_api.g_miss_char) <> nvl(rr_old_rec.source_pager, fnd_api.g_miss_char)
2577 then
2578 l_source_pager_o := rr_old_rec.source_pager;
2579 l_source_pager_n := l_pager;
2580 l_is_to_be_updated := 'Y';
2581 end if;
2582
2583 if (p_overwrite_name = 'ALIAS' AND l_ppf_rec.known_as IS NOT NULL) THEN
2584 l_resource_name_n := l_ppf_rec.known_as;
2585 elsif (p_overwrite_name = 'FULL_NAME' AND l_ppf_rec.full_name IS NOT NULL) THEN
2586 l_resource_name_n := l_ppf_rec.full_name;
2587 end if;
2588
2589 if nvl(l_end_date_active, fnd_api.g_miss_date) <> nvl(rr_old_rec.end_date_active, fnd_api.g_miss_date)
2590 then
2591 l_end_date_active_o := rr_old_rec.end_date_active;
2592 l_end_date_active_n := l_end_date_active;
2593 l_is_to_be_updated := 'Y';
2594 end if;
2595
2596 if (l_is_to_be_updated = 'Y') then
2597 l_update_extn := 'Y';
2598 end if;
2599
2600 if (l_resource_name_n is not null) then
2601 l_resource_name_o := rr_old_rec.resource_name;
2602 if ( nvl(l_resource_name_o, fnd_api.g_miss_char) <> l_resource_name_n) then
2603 if (l_update_extn = 'N') then
2604 l_is_to_be_updated := 'Y';
2605 end if;
2606 else
2607 l_resource_name_n := null;
2608 l_resource_name_o := null;
2609 end if;
2610 end if;
2611
2612 --ER # 2669284 . If any change has ben encountered, then audit that resource record and also
2613 --update the jtf_rs_resource_extns.
2614 if (l_is_to_be_updated = 'Y') then
2615
2616 ---fnd_file.put_line(fnd_file.log,'resource id:' || l_c_resource.resource_id);
2617 select jtf_rs_resource_extn_aud_s.nextval
2618 into l_resource_extn_aud_id
2619 from dual;
2620
2621 JTF_RS_RESOURCE_EXTN_AUD_PKG.INSERT_ROW
2622 (
2623 X_ROWID => l_row_id ,
2624 x_resource_audit_id => l_resource_extn_aud_id ,
2625 x_resource_id => l_c_resource.resource_id ,
2626 x_new_category => null ,
2627 x_old_category => null ,
2628 x_new_resource_number => null ,
2629 x_old_resource_number => null ,
2630 x_new_source_id => null ,
2631 x_old_source_id => null ,
2632 x_new_address_id => l_address_id_n ,
2633 x_old_address_id => l_address_id_o ,
2634 x_new_contact_id => null ,
2635 x_old_contact_id => null ,
2636 x_new_managing_employee_id => null ,
2637 x_old_managing_employee_id => null ,
2638 x_new_start_date_active => null ,
2639 x_old_start_date_active => null ,
2640 x_new_end_date_active => l_end_date_active_n ,
2641 x_old_end_date_active => l_end_date_active_o ,
2642 x_new_time_zone => null ,
2643 x_old_time_zone => null ,
2644 x_new_cost_per_hr => null ,
2645 x_old_cost_per_hr => null ,
2646 x_new_primary_language => null ,
2647 x_old_primary_language => null ,
2648 x_new_secondary_language => null ,
2649 x_old_secondary_language => null ,
2650 x_new_support_site_id => null ,
2651 x_old_support_site_id => null ,
2652 x_new_ies_agent_login => null ,
2653 x_old_ies_agent_login => null ,
2654 x_new_server_group_id => null ,
2655 x_old_server_group_id => null ,
2656 x_new_assigned_to_group_id => null ,
2657 x_old_assigned_to_group_id => null ,
2658 x_new_cost_center => null ,
2659 x_old_cost_center => null ,
2660 x_new_charge_to_cost_center => null ,
2661 x_old_charge_to_cost_center => null ,
2662 x_new_compensation_currency_co => null ,
2663 x_old_compensation_currency_co => null ,
2664 x_new_commissionable_flag => null ,
2665 x_old_commissionable_flag => null ,
2666 x_new_hold_reason_code => null ,
2667 x_old_hold_reason_code => null ,
2668 x_new_hold_payment => null ,
2669 x_old_hold_payment => null ,
2670 x_new_comp_service_team_id => null ,
2671 x_old_comp_service_team_id => null ,
2672 x_new_transaction_number => null ,
2673 x_old_transaction_number => null ,
2674 x_new_object_version_number => null ,
2675 x_old_object_version_number => null ,
2676 x_new_user_id => null ,
2677 x_old_user_id => null ,
2678 x_new_resource_name => l_resource_name_n ,
2679 x_old_resource_name => l_resource_name_o ,
2680 x_new_source_name => l_source_name_n,
2681 x_old_source_name => l_source_name_o,
2682 x_new_source_number => l_source_number_n,
2683 x_old_source_number => l_source_number_o,
2684 x_new_source_job_id => l_source_job_id_n,
2685 x_old_source_job_id => l_source_job_id_o,
2686 x_new_source_job_title => l_source_job_title_n,
2687 x_old_source_job_title => l_source_job_title_o,
2688 x_new_source_email => l_source_email_n,
2689 x_old_source_email => l_source_email_o,
2690 x_new_source_phone => l_source_phone_n,
2691 x_old_source_phone => l_source_phone_o,
2692 x_new_source_org_id => null,
2693 x_old_source_org_id => null,
2694 x_new_source_org_name => null,
2695 x_old_source_org_name => null,
2696 x_new_source_address1 => l_source_address1_n,
2697 x_old_source_address1 => l_source_address1_o,
2698 x_new_source_address2 => l_source_address2_n,
2699 x_old_source_address2 => l_source_address2_o,
2700 x_new_source_address3 => l_source_address3_n,
2701 x_old_source_address3 => l_source_address3_o,
2702 x_new_source_address4 => null,
2703 x_old_source_address4 => null,
2704 x_new_source_city => l_source_city_n,
2705 x_old_source_city => l_source_city_o,
2706 x_new_source_postal_code => l_source_postal_code_n,
2707 x_old_source_postal_code => l_source_postal_code_o,
2708 x_new_source_state => null,
2709 x_old_source_state => null,
2710 x_new_source_province => null,
2711 x_old_source_province => null,
2712 x_new_source_county => null,
2713 x_old_source_county => null,
2714 x_new_source_country => l_source_country_n,
2715 x_old_source_country => l_source_country_o,
2716 x_new_source_mgr_id => l_source_mgr_id_n,
2717 x_old_source_mgr_id => l_source_mgr_id_o,
2718 x_new_source_mgr_name => l_source_mgr_name_n,
2719 x_old_source_mgr_name => l_source_mgr_name_o,
2720 x_new_source_business_grp_id => l_source_business_grp_id_n,
2721 x_old_source_business_grp_id => l_source_business_grp_id_o,
2722 x_new_source_business_grp_name => l_source_business_grp_name_n,
2723 x_old_source_business_grp_name => l_source_business_grp_name_o,
2724 x_new_source_first_name => l_source_first_name_n,
2725 x_old_source_first_name => l_source_first_name_o,
2726 x_new_source_middle_name => l_source_middle_name_n,
2727 x_old_source_middle_name => l_source_middle_name_o,
2728 x_new_source_last_name => l_source_last_name_n,
2729 x_old_source_last_name => l_source_last_name_o,
2730 x_new_source_category => null,
2731 x_old_source_category => null,
2732 x_new_source_status => null,
2733 x_old_source_status => null,
2734 x_new_source_office => l_source_office_n,
2735 x_old_source_office => l_source_office_o,
2736 x_new_source_location => l_source_location_n,
2737 x_old_source_location => l_source_location_o,
2738 x_new_source_mailstop => l_source_mailstop_n,
2739 x_old_source_mailstop => l_source_mailstop_o,
2740 x_new_user_name => null,
2741 x_old_user_name => null,
2742 x_new_party_id => l_party_id_n,
2743 x_old_party_id => l_party_id_o,
2744 x_new_source_mobile_phone => l_source_mobile_phone_n,
2745 x_old_source_mobile_phone => l_source_mobile_phone_o,
2746 x_new_source_pager => l_source_pager_n,
2747 x_old_source_pager => l_source_pager_o,
2748 x_creation_date => l_sysdate,
2749 x_created_by => l_user_id,
2750 x_last_update_date => l_sysdate,
2751 x_last_updated_by => l_user_id,
2752 x_last_update_login => l_login );
2753
2754 if (l_update_extn = 'Y') then
2755
2756 UPDATE jtf_rs_resource_extns
2757 SET last_update_date = l_sysdate,
2758 last_update_login = l_login,
2759 last_updated_by = l_user_id,
2760 source_number = l_source_number,
2761 source_name = l_ppf_rec.full_name,
2762 source_first_name = l_ppf_rec.first_name,
2763 source_last_name = l_ppf_rec.last_name,
2764 source_middle_name = l_ppf_rec.middle_names,
2765 source_phone = l_phone,
2766 source_email = l_ppf_rec.email_address,
2767 source_job_id = l_new_job_id,
2768 source_job_title = l_job_name,
2769 address_id = l_asg_rec.location_id,
2770 source_address1 = l_loc_rec.address_line_1,
2771 source_address2 = l_loc_rec.address_line_2,
2772 source_address3 = l_loc_rec.address_line_3,
2773 source_city = l_loc_rec.town_or_city,
2774 source_country = l_loc_rec.country,
2775 source_postal_code = l_loc_rec.postal_code,
2776 source_mgr_id = l_asg_rec.supervisor_id,
2777 source_mgr_name = l_mgr_name,
2778 source_business_grp_id = l_ppf_rec.business_group_id,
2779 source_business_grp_name = l_org_name,
2780 source_office = l_ppf_rec.office_number,
2781 source_location = l_ppf_rec.internal_location,
2782 source_mailstop = l_ppf_rec.mailstop,
2783 source_mobile_phone = l_mobile_phone,
2784 source_pager = l_pager,
2785 person_party_id = l_ppf_rec.party_id,
2786 end_date_active = l_end_date_active
2787 WHERE RESOURCE_ID = l_c_resource.resource_id;
2788 end if;
2789
2790 IF (p_overwrite_name = 'ALIAS') THEN
2791 IF l_resource_name_n IS NOT NULL THEN
2792 update jtf_rs_resource_extns_tl
2793 SET last_update_date = l_sysdate,
2794 last_update_login = l_login,
2795 last_updated_by = l_user_id,
2796 resource_name = l_resource_name_n,
2797 source_lang = userenv('LANG')
2798 where resource_id = l_c_resource.resource_id
2799 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
2800
2801 END IF;
2802 ELSIF (p_overwrite_name = 'FULL_NAME') THEN
2803 IF (l_resource_name_n IS NOT NULL) THEN
2804 update jtf_rs_resource_extns_tl
2805 SET last_update_date = l_sysdate,
2806 last_update_login = l_login,
2807 last_updated_by = l_user_id,
2808 resource_name = l_resource_name_n,
2809 source_lang = userenv('LANG')
2810 where resource_id = l_c_resource.resource_id
2811 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
2812 END IF;
2813 END IF;
2814
2815 IF ((l_rehire_rev_emp_flag = 'Y') and (l_activate_salesreps = 'Y')) then
2816 UPDATE jtf_rs_salesreps
2817 SET end_date_active = NULL,
2818 last_update_date = l_sysdate,
2819 last_update_login = l_login,
2820 last_updated_by = l_user_id
2821 WHERE resource_id = l_c_resource.resource_id;
2822 END IF;
2823
2824 END IF;
2825
2826 --- In the end update job roles for that resource
2827 IF g_run_date between trunc(l_c_resource.start_date_active) and trunc(nvl(l_end_date_active-l_active_days,g_run_date)) then /*active resource*/
2828
2829 -- Bug 5590723 (Handle NULL values in source_job_id column when source_job_title is populated)
2830 -- otherwise NULL value gets passed to update_job_roles procedure.
2831 l_derived_job_id := rr_old_rec.source_job_id;
2832
2833 IF (rr_old_rec.source_job_id IS NULL) THEN
2834 IF (rr_old_rec.source_job_title IS NOT NULL AND
2835 rr_old_rec.source_business_grp_id IS NOT NULL
2836 )
2837 THEN
2838 OPEN c_derive_job_id (rr_old_rec.source_job_title, rr_old_rec.source_business_grp_id);
2839 FETCH c_derive_job_id INTO l_derived_job_id;
2840 CLOSE c_derive_job_id;
2841 END IF;
2842 END IF;
2843
2844 update_job_roles(
2845 p_old_job_id => l_derived_job_id, --l_c_resource.source_job_id,
2846 p_new_job_title => l_new_job_title,
2847 p_new_job_id => l_new_job_id,
2848 p_new_job_assignment_id => l_new_job_assignment_id,
2849 p_person_id => l_c_resource.source_id,
2850 p_resource_id => l_c_resource.resource_id,
2851 p_assignment_type => l_assignment_type
2852 );
2853
2854 END IF;
2855
2856 EXCEPTION
2857 WHEN fnd_api.g_exc_error
2858 THEN
2859 -- fnd_file.put_line(fnd_file.log, sqlerrm);
2860 ROLLBACK TO update_resource_sp;
2861 WHEN fnd_api.g_exc_unexpected_error
2862 THEN
2863 -- fnd_file.put_line(fnd_file.log, sqlerrm);
2864 ROLLBACK TO update_resource_sp;
2865 WHEN OTHERS
2866 THEN
2867 fnd_file.put_line(fnd_file.log, sqlerrm);
2868 ROLLBACK TO update_resource_sp;
2869 END;
2870
2871 END LOOP;
2872
2873 EXCEPTION
2874 WHEN fnd_api.g_exc_unexpected_error
2875 THEN
2876
2877 IF c_resource%ISOPEN THEN
2878 CLOSE c_resource;
2879 END IF;
2880
2881 IF c_check_rehire_rev_emp%ISOPEN THEN
2882 CLOSE c_check_rehire_rev_emp;
2883 END IF;
2884
2885 IF c_salesreps%ISOPEN THEN
2886 CLOSE c_salesreps;
2887 END IF;
2888
2889 IF c_ppf%ISOPEN THEN
2890 CLOSE c_ppf;
2891 END IF;
2892
2893 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
2894 ROLLBACK TO UPDATE_EMPLOYEE_SP;
2895 WHEN OTHERS
2896 THEN
2897
2898 IF c_resource%ISOPEN THEN
2899 CLOSE c_resource;
2900 END IF;
2901
2902 IF c_check_rehire_rev_emp%ISOPEN THEN
2903 CLOSE c_check_rehire_rev_emp;
2904 END IF;
2905
2906 IF c_salesreps%ISOPEN THEN
2907 CLOSE c_salesreps;
2908 END IF;
2909
2910 IF c_ppf%ISOPEN THEN
2911 CLOSE c_ppf;
2912 END IF;
2913
2914 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
2915 ROLLBACK TO UPDATE_EMPLOYEE_SP;
2916
2917 END update_employee;
2918
2919 ----------------------------------------------------------------------
2920
2921 PROCEDURE update_terminated_employee
2922 IS
2923 cursor term_res_cur
2924 is
2925 SELECT RESOURCE_ID, SOURCE_ID
2926 FROM JTF_RS_RESOURCE_EXTNS RES
2927 WHERE res.category = 'EMPLOYEE'
2928 AND not exists (select ppl.person_id
2929 from per_all_people_f ppl,
2930 per_all_assignments_f asg
2931 where ppl.person_id = res.source_id
2932 and res.category = 'EMPLOYEE'
2933 and trunc(sysdate) between ppl.effective_start_date and ppl.effective_end_date
2934 and ppl.employee_number is not null
2935 and ppl.person_id = asg.person_id
2936 and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
2937 and asg.primary_flag = 'Y')
2938 /*AND NOT EXISTS (SELECT EMPLOYEE_ID
2939 FROM PER_EMPLOYEES_CURRENT_X
2940 WHERE EMPLOYEE_ID = RES.SOURCE_ID)*/
2941 union
2942 SELECT RESOURCE_ID, SOURCE_ID
2943 FROM JTF_RS_RESOURCE_EXTNS RES
2944 WHERE res.category = 'EMPLOYEE'
2945 AND (res.end_date_active IS NOT NULL AND res.end_date_active < sysdate);
2946
2947 term_res_rec term_res_cur%rowtype;
2948
2949
2950 l_too_many_rows_query EXCEPTION; --exception to handle too many rows returned by inner query;
2951 PRAGMA EXCEPTION_INIT(l_too_many_rows_query, -1427 );
2952
2953
2954 CURSOR fetch_emp_cur(l_resource_id NUMBER)
2955 IS
2956 SELECT res.resource_id resource_id
2957 , res.resource_number resource_number
2958 , res.object_version_number object_version_num
2959 , ppl.person_id person_id
2960 , ppl.employee_number employee_number
2961 , ppl.full_name full_name
2962 , ppl.first_name first_name
2963 , ppl.middle_names middle_name
2964 , ppl.last_name last_name
2965 , ppl.work_telephone phone
2966 , ppl.email_address email
2967 , ppl.business_group_id org_id
2968 , org.name org_name
2969 , ppl.office_number office
2970 , ppl.internal_location location
2971 , ppl.mailstop mailstop
2972 , ppl.party_id party_id
2973 FROM jtf_rs_resource_extns res
2974 , per_all_people_f ppl
2975 , hr_all_organization_units org
2976 WHERE res.resource_id = l_resource_id
2977 AND res.source_id = ppl.person_id
2978 and ppl.effective_start_date = (select max(effective_start_date)
2979 from per_all_people_f where person_id = ppl.person_id)
2980 and ppl.effective_end_date = (select max(effective_end_date)
2981 from per_all_people_f where person_id = ppl.person_id)
2982 and ppl.business_group_id = org.organization_id;
2983
2984 emp_rec fetch_emp_cur%rowtype;
2985
2986 BEGIN
2987
2988 open term_res_cur;
2989 fetch term_res_cur into term_res_rec;
2990 while(term_res_cur%found)
2991 loop
2992
2993 begin
2994 UPDATE jtf_rs_resource_extns RES
2995 SET RES.LAST_UPDATE_DATE = sysdate,
2996 RES.LAST_UPDATED_BY = fnd_global.user_id,
2997 (RES.SOURCE_NUMBER ,
2998 RES.SOURCE_NAME ,
2999 RES.SOURCE_PHONE ,
3000 RES.SOURCE_EMAIL ,
3001 --RES.SOURCE_JOB_TITLE ,
3002 --RES.SOURCE_ORG_ID ,
3003 --RES.SOURCE_ORG_NAME ,
3004 --RES.SOURCE_ADDRESS1,
3005 --RES.SOURCE_ADDRESS2 ,
3006 --RES.SOURCE_ADDRESS3 ,
3007 --RES.SOURCE_ADDRESS4 ,
3008 --RES.SOURCE_CITY ,
3009 --RES.SOURCE_POSTAL_CODE ,
3010 --RES.SOURCE_STATE ,
3011 --RES.SOURCE_PROVINCE ,
3012 --RES.SOURCE_COUNTY ,
3013 --RES.SOURCE_COUNTRY ,
3014 --RES.SOURCE_MGR_ID ,
3015 --RES.SOURCE_MGR_NAME ,
3016 RES.SOURCE_BUSINESS_GRP_ID ,
3017 RES.SOURCE_BUSINESS_GRP_NAME,
3018 RES.SOURCE_FIRST_NAME,
3019 RES.SOURCE_MIDDLE_NAME,
3020 RES.SOURCE_LAST_NAME,
3021 RES.SOURCE_OFFICE,
3022 RES.SOURCE_LOCATION,
3023 RES.PERSON_PARTY_ID,
3024 RES.SOURCE_MAILSTOP)
3025 = ( SELECT
3026 ppl.employee_number employee_number
3027 , ppl.full_name full_name
3028 , ppl.work_telephone phone
3029 , ppl.email_address email
3030 --, job.name job_title
3031 --, NULL
3032 --, NULL
3033 --, loc.address_line_1 address1
3034 --, loc.address_line_2 address2
3035 --, loc.address_line_3 address3
3036 --, null address4
3037 --, loc.town_or_city city
3038 --, loc.postal_code postal_code
3039 --, null state
3040 --, null province
3041 --, null county
3042 --, loc.country country
3043 --, asg.supervisor_id mgr_id
3044 --, emp.full_name mgr_name
3045 , ppl.business_group_id org_id
3046 , org.name org_name
3047 , ppl.first_name
3048 , ppl.middle_names
3049 , ppl.last_name
3050 , ppl.office_number
3051 , ppl.internal_location
3052 , ppl.party_id
3053 , ppl.mailstop
3054 FROM per_all_people_f ppl
3055 , hr_all_organization_units org
3056 --, per_all_assignments_f asg
3057 --, per_jobs job
3058 --, hr_locations loc
3059 --, per_employees_current_x emp
3060 WHERE res.source_id = ppl.person_id
3061 /* AND NOT EXISTS (SELECT EMPLOYEE_ID
3062 FROM PER_EMPLOYEES_CURRENT_X
3063 WHERE EMPLOYEE_ID = RES.SOURCE_ID) */
3064 AND ppl.effective_start_date = (select max(effective_start_date)
3065 from per_all_people_f
3066 where person_id = ppl.person_id)
3067 and ppl.business_group_id = org.organization_id
3068 --and ppl.person_id = asg.person_id
3069 --and asg.primary_flag = 'Y'
3070 --and asg.assignment_type = 'E'
3071 /*and asg.effective_start_date = (select max(effective_start_date)
3072 from per_all_assignments_f
3073 where person_id = ppl.person_id
3074 and primary_flag = 'Y'
3075 and assignment_type = 'E'
3076 ) */
3077 --and asg.job_id = job.job_id(+)
3078 --and asg.location_id = loc.location_id (+)
3079 --and asg.supervisor_id = emp.employee_id(+)
3080 )
3081 WHERE res.resource_id = term_res_rec.resource_id ;
3082
3083 EXCEPTION
3084 when l_too_many_rows_query then
3085 open fetch_emp_cur(term_res_rec.resource_id);
3086 fetch fetch_emp_cur into emp_rec;
3087 close fetch_emp_cur;
3088 UPDATE jtf_rs_resource_extns RES
3089 SET RES.LAST_UPDATE_DATE = sysdate,
3090 RES.LAST_UPDATED_BY = fnd_global.user_id,
3091 RES.SOURCE_NUMBER = emp_rec.employee_number ,
3092 RES.SOURCE_NAME = emp_rec.full_name,
3093 RES.SOURCE_PHONE = emp_rec.phone,
3094 RES.SOURCE_EMAIL = emp_rec.email,
3095 --RES.SOURCE_JOB_TITLE = emp_rec.job_title ,
3096 RES.SOURCE_BUSINESS_GRP_ID = emp_rec.org_id ,
3097 RES.SOURCE_BUSINESS_GRP_NAME = emp_rec.org_name,
3098 RES.SOURCE_FIRST_NAME = emp_rec.first_name,
3099 RES.SOURCE_LAST_NAME = emp_rec.last_name,
3100 RES.SOURCE_MIDDLE_NAME = emp_rec.middle_name,
3101 RES.SOURCE_OFFICE = emp_rec.office,
3102 RES.SOURCE_LOCATION = emp_rec.location,
3103 RES.person_party_id = emp_rec.party_id,
3104 RES.SOURCE_MAILSTOP = emp_rec.mailstop
3105 WHERE RES.resource_id = emp_rec.resource_id;
3106 WHEN OTHERS
3107 THEN
3108 fnd_message.set_name('JTF', 'JTF_RS_UPD_TERM_RES_ERR');
3109 fnd_message.set_token('P_PERSON_ID',term_res_rec.source_id );
3110 fnd_file.put_line(fnd_file.log, fnd_message.get);
3111
3112 end;
3113 fetch term_res_cur into term_res_rec;
3114 end loop;
3115 close term_res_cur;
3116 commit;
3117
3118 EXCEPTION
3119 WHEN fnd_api.g_exc_unexpected_error
3120 THEN
3121 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
3122 WHEN OTHERS
3123 THEN
3124 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
3125 fnd_file.put_line(fnd_file.log, sqlerrm);
3126 END update_terminated_employee;
3127
3128
3129
3130 PROCEDURE update_emp_phone
3131 IS
3132 BEGIN
3133
3134 update jtf_rs_resource_extns res
3135 SET RES.LAST_UPDATE_DATE = sysdate,
3136 RES.LAST_UPDATED_BY = fnd_global.user_id,
3137 RES.SOURCE_PHONE
3138 = (select per.phone_number
3139 from per_phones per
3140 where parent_table = 'PER_ALL_PEOPLE_F'
3141 and parent_id = res.source_id
3142 and phone_type = 'W1'
3143 AND trunc(sysdate) between date_from and nvl(date_to, to_date('31/12/4712', 'DD/MM/YYYY')))
3144 WHERE RES.CATEGORY = 'EMPLOYEE';
3145
3146
3147 commit;
3148 EXCEPTION
3149 WHEN fnd_api.g_exc_unexpected_error
3150 THEN
3151 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
3152 WHEN OTHERS
3153 THEN
3154 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
3155 fnd_file.put_line(fnd_file.log, sqlerrm);
3156
3157 END update_emp_phone;
3158
3159 PROCEDURE synchronize_party
3160 (ERRBUF OUT NOCOPY VARCHAR2,
3161 RETCODE OUT NOCOPY VARCHAR2,
3162 P_OVERWRITE_NAME IN VARCHAR2
3163 )
3164
3165 IS
3166
3167
3168 BEGIN
3169 --Terminate all partner resource, whose relationship is terminated in hz_relationships
3170 jtf_rs_conc_res_pub.terminate_partner_rel;
3171
3172 jtf_rs_conc_res_pub.update_party
3173 (p_overwrite_name => P_OVERWRITE_NAME);
3174 COMMIT;
3175
3176 EXCEPTION
3177 WHEN fnd_api.g_exc_unexpected_error
3178 THEN
3179 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
3180 WHEN OTHERS
3181 THEN
3182 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
3183
3184 END synchronize_party;
3185
3186 PROCEDURE terminate_partner_rel IS
3187
3188 l_api_version CONSTANT NUMBER := 1.0;
3189 l_api_name CONSTANT VARCHAR2(30) := 'TERMINATE_PARTNER_REL';
3190 L_RETURN_STATUS VARCHAR2(2);
3191 L_MSG_COUNT NUMBER;
3192 L_MSG_DATA VARCHAR2(2000);
3193
3194 CURSOR term_part_rel_cur (l_active_days IN NUMBER) IS
3195 SELECT rsc.resource_id
3196 , rsc.object_version_number
3197 , hpr.end_date
3198 , hpr.status
3199 FROM jtf_rs_resource_extns rsc
3200 ,hz_relationships hpr
3201 WHERE rsc.category = 'PARTNER'
3202 AND rsc.source_id = hpr.party_id
3203 AND hpr.directional_flag = 'F'
3204 --
3205 AND hpr.RELATIONSHIP_CODE IN
3206 ('PARTNER_OF', 'VAD_OF', 'VAD_VENDOR_OF',
3207 'THIRD_PARTY_FOR', 'INDIRECTLY_MANAGES_CUSTOMER', 'CUSTOMER_INDIRECTLY_MANAGED_BY','POM_THIRDPARTY_AUTHOR_FOR')
3208 AND hpr.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3209 AND hpr.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3210 --
3211 -- AND hpr.relationship_code in ('PARTNER_OF','VAD_OF','CUSTOMER_INDIRECTLY_MANAGED_BY','POM_THIRDPARTY_AUTHOR_FOR')
3212 AND ((hpr.status = 'I')
3213 OR
3214 (nvl(trunc(hpr.end_date),trunc(sysdate)) < trunc(sysdate)))
3215 AND nvl(trunc(end_date_active),trunc(sysdate+1+l_active_days)) > trunc(sysdate+l_active_days);
3216
3217 l_count NUMBER;
3218 l_active_days NUMBER;
3219
3220 BEGIN
3221
3222 --Standard Start of API SAVEPOINT
3223 SAVEPOINT TERMINATE_PARTNER_REL_SP;
3224
3225 l_return_status := fnd_api.g_ret_sts_success;
3226 l_count := 0;
3227
3228 FND_PROFILE.GET('JTF_RS_RESOURCE_ACTIVE_DAYS',l_active_days);
3229
3230 IF l_active_days IS NULL THEN
3231 l_active_days := 0;
3232 END IF;
3233
3234 FOR term_part_rel_rec IN term_part_rel_cur (l_active_days) LOOP
3235
3236 l_return_status := fnd_api.g_ret_sts_success;
3237
3238 IF (trunc(nvl(term_part_rel_rec.end_date,sysdate)) < trunc(sysdate)) THEN
3239 jtf_rs_resource_utl_pub.end_date_employee
3240 (p_api_version => 1.0
3241 , p_resource_id => term_part_rel_rec.resource_id
3242 , p_end_date_active => trunc(term_part_rel_rec.end_date)+l_active_days
3243 , x_object_ver_number => term_part_rel_rec.object_version_number
3244 , x_return_status => l_return_status
3245 , x_msg_count => l_msg_count
3246 , x_msg_data => l_msg_data);
3247 ELSIF (term_part_rel_rec.status = 'I') THEN
3248 jtf_rs_resource_utl_pub.end_date_employee
3249 (p_api_version => 1.0
3250 , p_resource_id => term_part_rel_rec.resource_id
3251 , p_end_date_active => trunc(sysdate-1)+l_active_days
3252 , x_object_ver_number => term_part_rel_rec.object_version_number
3253 , x_return_status => l_return_status
3254 , x_msg_count => l_msg_count
3255 , x_msg_data => l_msg_data);
3256 END IF;
3257
3258 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
3259 RAISE fnd_api.g_exc_error;
3260 END IF;
3261
3262 l_count := l_count + 1;
3263 IF (l_count > 1000) THEN
3264 COMMIT;
3265 l_count := 1;
3266 END IF;
3267
3268 END LOOP; -- end of term_part_cur
3269
3270 FND_MSG_PUB.count_and_get (p_count => l_msg_count, p_data => l_msg_data);
3271
3272 EXCEPTION
3273 WHEN fnd_api.g_exc_error
3274 THEN
3275 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
3276 ROLLBACK TO TERMINATE_PARTNER_REL_SP;
3277
3278 WHEN OTHERS
3279 THEN
3280 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
3281 ROLLBACK TO TERMINATE_PARTNER_REL_SP;
3282 fnd_file.put_line(fnd_file.log, sqlerrm);
3283
3284 END terminate_partner_rel;
3285
3286
3287 PROCEDURE update_party
3288 (P_OVERWRITE_NAME IN VARCHAR2 )
3289 IS
3290 l_too_many_rows_query EXCEPTION; --exception to handle too many rows returned by inner query;
3291 PRAGMA EXCEPTION_INIT(l_too_many_rows_query, -1427 );
3292
3293
3294 CURSOR c_res
3295 IS
3296 SELECT resource_id
3297 , source_id
3298 , address_id
3299 , category
3300 FROM jtf_rs_resource_extns
3301 WHERE category in ('PARTY', 'PARTNER');
3302
3303
3304 CURSOR c_party(l_party_id in number)
3305 IS
3306 SELECT PARTY.PARTY_NUMBER,
3307 PARTY.PARTY_NAME,
3308 PARTY.EMAIL_ADDRESS,
3309 CT_POINT1.PHONE_AREA_CODE||CT_POINT1.PHONE_NUMBER PHONE,
3310 TO_NUMBER(NULL) ORG_ID,
3311 NULL ORG_NAME,
3312 PARTY.PERSON_FIRST_NAME FIRST_NAME,
3313 PARTY.PERSON_MIDDLE_NAME MIDDLE_NAME,
3314 PARTY.PERSON_LAST_NAME LAST_NAME
3315 FROM
3316 HZ_PARTIES PARTY,
3317 HZ_CONTACT_POINTS CT_POINT1
3318 WHERE PARTY.PARTY_ID = l_party_id
3319 AND CT_POINT1.OWNER_TABLE_NAME (+)= 'HZ_PARTIES'
3320 AND CT_POINT1.OWNER_TABLE_ID (+)= PARTY.PARTY_ID
3321 AND CT_POINT1.PRIMARY_FLAG (+)= 'Y'
3322 AND CT_POINT1.STATUS (+)= 'A'
3323 AND CT_POINT1.CONTACT_POINT_TYPE (+)= 'PHONE';
3324
3325
3326
3327 CURSOR c_partner(l_party_id in number)
3328 IS
3329 SELECT PARTY.PARTY_NUMBER,
3330 PARTY.PARTY_NAME,
3331 PARTY.EMAIL_ADDRESS,
3332 CT_POINT1.PHONE_AREA_CODE||CT_POINT1.PHONE_NUMBER PHONE,
3333 REL.OBJECT_ID ORG_ID,
3334 PARTY.PARTY_NAME ORG_NAME,
3335 PARTY.PERSON_FIRST_NAME FIRST_NAME,
3336 PARTY.PERSON_MIDDLE_NAME MIDDLE_NAME,
3337 PARTY.PERSON_LAST_NAME LAST_NAME
3338 FROM
3339 HZ_PARTIES PARTY,
3340 HZ_PARTIES PARTY2,
3341 HZ_PARTIES PARTY3,
3342 HZ_CONTACT_POINTS CT_POINT1,
3343 HZ_RELATIONSHIPS REL
3344 WHERE PARTY.PARTY_ID = l_party_id
3345 AND (
3346 (
3347 PARTY.PARTY_TYPE = 'ORGANIZATION'
3348 AND
3349 PARTY.PARTY_ID = REL.SUBJECT_ID
3350 )
3351 OR
3352 (
3353 PARTY.PARTY_TYPE = 'PARTY_RELATIONSHIP'
3354 AND
3355 PARTY.PARTY_ID = REL.PARTY_ID
3356 )
3357 )
3358 --
3359 --
3360 AND REL.RELATIONSHIP_CODE IN
3361 ('PARTNER_OF', 'VAD_OF', 'VAD_VENDOR_OF',
3362 'THIRD_PARTY_FOR', 'INDIRECTLY_MANAGES_CUSTOMER', 'CUSTOMER_INDIRECTLY_MANAGED_BY','POM_THIRDPARTY_AUTHOR_FOR')
3363 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3364 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3365 AND REL.DIRECTIONAL_FLAG = 'F'
3366 AND REL.STATUS = 'A'
3367 --
3368 AND REL.SUBJECT_ID = PARTY2.PARTY_ID
3369 AND (PARTY2.PARTY_TYPE = 'PERSON'
3370 OR PARTY2.PARTY_TYPE = 'ORGANIZATION')
3371 AND REL.OBJECT_ID = PARTY3.PARTY_ID
3372 AND PARTY3.PARTY_TYPE = 'ORGANIZATION'
3373 AND CT_POINT1.OWNER_TABLE_NAME (+)= 'HZ_PARTIES'
3374 AND CT_POINT1.OWNER_TABLE_ID (+)= PARTY.PARTY_ID
3375 AND CT_POINT1.PRIMARY_FLAG (+)= 'Y'
3376 AND CT_POINT1.STATUS (+)= 'A'
3377 AND CT_POINT1.CONTACT_POINT_TYPE (+)= 'PHONE'
3378 ORDER BY PARTY.LAST_UPDATE_DATE DESC ; -- so that we can pick up latest record.
3379
3380 r_party c_partner%rowtype;
3381
3382 CURSOR c_address(l_address_id in number)
3383 IS
3384 SELECT LOC.ADDRESS1
3385 ,LOC.ADDRESS2
3386 ,LOC.ADDRESS3
3387 ,LOC.ADDRESS4
3388 ,LOC.CITY
3389 ,LOC.POSTAL_CODE
3390 ,LOC.STATE
3391 ,LOC.PROVINCE
3392 ,LOC.COUNTY
3393 ,LOC.COUNTRY
3394 FROM HZ_PARTY_SITES PARTY_SITE
3395 , HZ_LOCATIONS LOC
3396 WHERE PARTY_SITE.PARTY_SITE_ID = l_address_id
3397 AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID;
3398
3399 r_address c_address%rowtype;
3400
3401 /* Moved the initial assignment of below variables to inside begin */
3402 l_sysdate DATE;
3403 l_user_id NUMBER;
3404 l_login NUMBER;
3405
3406 BEGIN
3407
3408 l_sysdate := sysdate;
3409 l_user_id := nvl(FND_GLOBAL.USER_ID,-1);
3410 l_login := nvl(FND_GLOBAL.LOGIN_ID,-1);
3411
3412 -- first populate the address id where address id is null
3413 BEGIN
3414 UPDATE JTF_RS_RESOURCE_EXTNS RES
3415 SET RES.LAST_UPDATE_DATE = l_sysdate,
3416 RES.LAST_UPDATED_BY = l_user_id,
3417 RES.LAST_UPDATE_LOGIN = l_login,
3418 (RES.ADDRESS_ID)
3419 = ( SELECT prt.party_site_id
3420 FROM hz_party_sites prt
3421 WHERE prt.party_id = RES.source_id
3422 AND prt.identifying_address_flag = 'Y'
3423 -- added status flag check 20 nov 2001
3424 AND prt.status = 'A')
3425 WHERE RES.CATEGORY = 'PARTY'
3426 AND RES.ADDRESS_ID IS NULL;
3427
3428
3429 EXCEPTION
3430 WHEN L_TOO_MANY_ROWS_QUERY
3431 THEN
3432 null;
3433 WHEN OTHERS
3434 THEN
3435 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
3436 fnd_file.put_line(fnd_file.log, sqlerrm);
3437 END;
3438
3439
3440
3441 -- first populate the address id with primary address id for PARTNER
3442 BEGIN
3443 UPDATE JTF_RS_RESOURCE_EXTNS RES
3444 SET RES.LAST_UPDATE_DATE = l_sysdate,
3445 RES.LAST_UPDATED_BY = l_user_id,
3446 RES.LAST_UPDATE_LOGIN = l_login,
3447 (RES.ADDRESS_ID)
3448 = ( SELECT prt.party_site_id
3449 FROM hz_party_sites prt
3450 WHERE prt.party_id = RES.source_id
3451 AND prt.identifying_address_flag = 'Y'
3452 -- added status flag check 20 nov 2001
3453 AND prt.status = 'A')
3454 WHERE RES.CATEGORY = 'PARTNER'
3455 AND exists (select 'A'
3456 from hz_parties par
3457 where par.party_id = res.source_id
3458 and par.party_type = 'PARTY_RELATIONSHIP');
3459
3460 EXCEPTION
3461 WHEN L_TOO_MANY_ROWS_QUERY
3462 THEN
3463 null;
3464 WHEN OTHERS
3465 THEN
3466 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
3467 fnd_file.put_line(fnd_file.log, sqlerrm);
3468
3469 END;
3470
3471 for r_res in c_res
3472 loop
3473
3474 -- Bug 7497021 : Sudhir Gokavarapu (21-OCT-2008)
3475 -- Initialize record r_party to NULL
3476 r_party := NULL;
3477 IF(r_res.category = 'PARTY')
3478 THEN
3479 open c_party(r_res.source_id);
3480 fetch c_party into r_party;
3481 close c_party;
3482 ELSIF(r_res.category = 'PARTNER')
3483 THEN
3484 open c_partner(r_res.source_id);
3485 fetch c_partner into r_party;
3486 close c_partner;
3487 END IF;
3488 IF(r_party.party_number is not null)
3489 THEN
3490 open c_address(r_res.address_id);
3491 fetch c_address into r_address;
3492 close c_address;
3493
3494 UPDATE JTF_RS_RESOURCE_EXTNS RES
3495 SET RES.LAST_UPDATE_DATE = l_sysdate,
3496 RES.LAST_UPDATED_BY = l_user_id,
3497 RES.LAST_UPDATE_LOGIN = l_login,
3498 RES.SOURCE_NUMBER = r_party.party_number,
3499 RES.SOURCE_NAME = r_party.party_name,
3500 RES.SOURCE_EMAIL = r_party.email_address ,
3501 RES.SOURCE_ADDRESS1 = r_address.address1,
3502 RES.SOURCE_ADDRESS2 = r_address.address2,
3503 RES.SOURCE_ADDRESS3 = r_address.address3,
3504 RES.SOURCE_ADDRESS4 = r_address.address4,
3505 RES.SOURCE_CITY = r_address.city,
3506 RES.SOURCE_POSTAL_CODE = r_address.postal_code ,
3507 RES.SOURCE_STATE = r_address.state,
3508 RES.SOURCE_PROVINCE = r_address.province,
3509 RES.SOURCE_COUNTY = r_address.county,
3510 RES.SOURCE_COUNTRY = r_address.country,
3511 RES.SOURCE_PHONE = r_party.phone,
3512 RES.SOURCE_ORG_ID = r_party.org_id,
3513 RES.SOURCE_ORG_NAME = r_party.org_name,
3514 RES.SOURCE_FIRST_NAME = r_party.first_name,
3515 RES.SOURCE_MIDDLE_NAME = r_party.middle_name,
3516 RES.SOURCE_LAST_NAME = r_party.last_name
3517 WHERE RES.RESOURCE_ID = r_res.resource_id;
3518
3519
3520 IF(p_overwrite_name = 'Y')
3521 THEN
3522 update jtf_rs_resource_extns_tl res
3523 SET RES.LAST_UPDATE_DATE = l_sysdate,
3524 RES.LAST_UPDATED_BY = l_user_id,
3525 RES.LAST_UPDATE_LOGIN = l_login,
3526 resource_name = r_party.party_name,
3527 SOURCE_LANG = userenv('LANG')
3528 where resource_id = r_res.resource_id
3529 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
3530 END IF;
3531 END IF;
3532
3533 end loop;
3534
3535 COMMIT;
3536
3537
3538 EXCEPTION
3539 WHEN OTHERS
3540 THEN
3541 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
3542 fnd_file.put_line(fnd_file.log, sqlerrm);
3543
3544 END update_party;
3545
3546
3547
3548 PROCEDURE synchronize_supp_contact
3549 (ERRBUF OUT NOCOPY VARCHAR2,
3550 RETCODE OUT NOCOPY VARCHAR2,
3551 P_OVERWRITE_NAME IN VARCHAR2
3552 )IS
3553
3554 BEGIN
3555
3556 -- Call Terminate Supplier Contact Procedure
3557 jtf_rs_conc_res_pub.terminate_supplier_contact;
3558
3559 -- Call Update Supplier Contact Procedure
3560 jtf_rs_conc_res_pub.update_supp_contact
3561 (p_overwrite_name => P_OVERWRITE_NAME);
3562 COMMIT;
3563
3564 EXCEPTION
3565 WHEN fnd_api.g_exc_unexpected_error
3566 THEN
3567 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
3568 WHEN OTHERS
3569 THEN
3570 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
3571 END synchronize_supp_contact;
3572
3573
3574 PROCEDURE update_supp_contact
3575 (P_OVERWRITE_NAME IN VARCHAR2 )
3576 is
3577
3578 CURSOR c_res
3579 IS
3580 SELECT resource_id,
3581 source_id
3582 FROM jtf_rs_resource_extns res
3583 WHERE res.category = 'SUPPLIER_CONTACT';
3584
3585
3586
3587 CURSOR c_supp(l_supplier_contact_id in NUMBER)
3588 IS
3589 SELECT POV.SEGMENT1 SOURCE_NUMBER,
3590 POC.LAST_NAME || ' , ' || POC.MIDDLE_NAME ||' '|| POC.FIRST_NAME|| ' - '|| POV.VENDOR_NAME SOURCE_NAME ,
3591 NULL EMAIL,
3592 POS.ADDRESS_LINE1 ADDRESS1 ,
3593 POS.ADDRESS_LINE2 ADDRESS2 ,
3594 POS.ADDRESS_LINE3 ADDRESS3 ,
3595 NULL ADDRESS4 ,
3596 POS.CITY CITY ,
3597 POS.ZIP POSTAL_CODE ,
3598 POS.STATE STATE ,
3599 POS.PROVINCE PROVINCE,
3600 POS.COUNTY COUNTY ,
3601 POS.COUNTRY COUNTRY ,
3602 POC.AREA_CODE || ' ' ||POC.PHONE PHONE,
3603 POS.ORG_ID ORG_ID,
3604 -- ORG.NAME ORG_NAME,
3605 POC.FIRST_NAME FIRST_NAME,
3606 POC.MIDDLE_NAME MIDDLE_NAME,
3607 POC.LAST_NAME LAST_NAME
3608 FROM PO_VENDOR_CONTACTS POC,
3609 PO_VENDOR_SITES_ALL POS,
3610 PO_VENDORS POV
3611 -- HR_OPERATING_UNITS ORG
3612 WHERE POC.VENDOR_CONTACT_ID = l_supplier_contact_id
3613 AND POC.VENDOR_SITE_ID = POS.VENDOR_SITE_ID
3614 AND POS.VENDOR_ID = POV.VENDOR_ID;
3615 -- AND POS.ORG_ID = ORG.ORGANIZATION_ID;
3616
3617 r_supp c_supp%rowtype;
3618
3619 --Added this Cursor (and some related code changes) as a fix for bug #2586720, single org sync issue
3620 CURSOR c_org_name (l_org_id IN NUMBER) IS
3621 SELECT name
3622 FROM hr_operating_units
3623 WHERE organization_id = l_org_id;
3624
3625 /* Moved the initial assignment of below variables to inside begin */
3626 l_sysdate DATE;
3627 l_user_id NUMBER;
3628 l_login NUMBER;
3629
3630 l_org_name HR_OPERATING_UNITS.NAME%TYPE := NULL;
3631
3632 begin
3633
3634 l_sysdate := sysdate;
3635 l_user_id := nvl(FND_GLOBAL.USER_ID,-1);
3636 l_login := nvl(FND_GLOBAL.LOGIN_ID,-1);
3637
3638 -- synchronize supp cont
3639
3640 FOR r_res in c_res LOOP
3641
3642 OPEN c_supp(r_res.source_id);
3643 FETCH c_supp into r_supp;
3644 IF(c_supp%found)
3645 THEN
3646
3647 l_org_name := NULL;
3648
3649 IF r_supp.org_id IS NOT NULL THEN
3650 OPEN c_org_name (r_supp.org_id);
3651 FETCH c_org_name into l_org_name;
3652 CLOSE c_org_name;
3653 END IF;
3654
3655 UPDATE JTF_RS_RESOURCE_EXTNS RES
3656 SET RES.LAST_UPDATE_DATE = l_sysdate,
3657 RES.LAST_UPDATED_BY = l_user_id,
3658 RES.LAST_UPDATE_LOGIN = l_login,
3659 RES.SOURCE_NUMBER = r_supp.source_number,
3660 RES.SOURCE_NAME = r_supp.source_name,
3661 RES.SOURCE_EMAIL = r_supp.email ,
3662 RES.SOURCE_ADDRESS1 = r_supp.address1,
3663 RES.SOURCE_ADDRESS2 = r_supp.address2,
3664 RES.SOURCE_ADDRESS3 = r_supp.address3,
3665 RES.SOURCE_ADDRESS4 = r_supp.address4,
3666 RES.SOURCE_CITY = r_supp.city,
3667 RES.SOURCE_POSTAL_CODE = r_supp.postal_code ,
3668 RES.SOURCE_STATE = r_supp.state,
3669 RES.SOURCE_PROVINCE = r_supp.province,
3670 RES.SOURCE_COUNTY = r_supp.county,
3671 RES.SOURCE_COUNTRY = r_supp.country,
3672 RES.SOURCE_PHONE = r_supp.phone,
3673 RES.SOURCE_ORG_ID = r_supp.org_id,
3674 RES.SOURCE_ORG_NAME = l_org_name,
3675 RES.SOURCE_FIRST_NAME = r_supp.first_name,
3676 RES.SOURCE_MIDDLE_NAME = r_supp.middle_name,
3677 RES.SOURCE_LAST_NAME = r_supp.last_name
3678 WHERE RES.RESOURCE_ID = r_res.resource_id;
3679
3680
3681 IF(p_overwrite_name = 'Y')
3682 THEN
3683 update jtf_rs_resource_extns_tl res
3684 SET RES.LAST_UPDATE_DATE = l_sysdate,
3685 RES.LAST_UPDATED_BY = l_user_id,
3686 RES.LAST_UPDATE_LOGIN = l_login,
3687 SOURCE_LANG = userenv('LANG'),
3688 resource_name = r_supp.source_name
3689 where resource_id = r_res.resource_id
3690 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
3691 END IF;
3692 END IF;
3693 CLOSE c_supp;
3694
3695 END LOOP;
3696 COMMIT;
3697
3698 EXCEPTION
3699 WHEN fnd_api.g_exc_unexpected_error
3700 THEN
3701 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
3702 WHEN OTHERS
3703 THEN
3704 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
3705 end update_supp_contact;
3706
3707
3708 PROCEDURE terminate_supplier_contact IS
3709
3710 l_api_version CONSTANT NUMBER := 1.0;
3711 l_api_name CONSTANT VARCHAR2(30) := 'TERMINATE_SUPPLIER_CONTACT';
3712 L_RETURN_STATUS VARCHAR2(2);
3713 L_MSG_COUNT NUMBER;
3714 L_MSG_DATA VARCHAR2(2000);
3715
3716 CURSOR term_supp_cont_cur (l_active_days IN NUMBER) IS
3717 SELECT rsc.resource_id
3718 , rsc.object_version_number
3719 , pvc.inactive_date
3720 FROM jtf_rs_resource_extns rsc
3721 ,po_vendor_contacts pvc
3722 WHERE rsc.category = 'SUPPLIER_CONTACT'
3723 AND rsc.source_id = pvc.vendor_contact_id
3724 AND trunc(nvl(inactive_date, sysdate)) < trunc(sysdate)
3725 AND trunc(nvl(end_date_active,inactive_date+1+l_active_days)) > trunc(inactive_date+l_active_days);
3726
3727 l_active_days NUMBER;
3728 l_count NUMBER;
3729
3730 BEGIN
3731
3732 --Standard Start of API SAVEPOINT
3733 SAVEPOINT TERMINATE_SUPPLIER_CONTACT_SP;
3734
3735 l_return_status := fnd_api.g_ret_sts_success;
3736 l_count := 0;
3737
3738 FND_PROFILE.GET('JTF_RS_RESOURCE_ACTIVE_DAYS',l_active_days);
3739
3740 IF l_active_days IS NULL THEN
3741 l_active_days := 0;
3742 END IF;
3743
3744 FOR term_supp_cont_rec IN term_supp_cont_cur (l_active_days) LOOP
3745
3746 l_return_status := fnd_api.g_ret_sts_success;
3747
3748 jtf_rs_resource_utl_pub.end_date_employee
3749 (p_api_version => 1.0
3750 , p_resource_id => term_supp_cont_rec.resource_id
3751 , p_end_date_active => trunc(term_supp_cont_rec.inactive_date)+l_active_days
3752 , x_object_ver_number => term_supp_cont_rec.object_version_number
3753 , x_return_status => l_return_status
3754 , x_msg_count => l_msg_count
3755 , x_msg_data => l_msg_data);
3756
3757 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
3758 RAISE fnd_api.g_exc_error;
3759 END IF;
3760
3761 l_count := l_count + 1;
3762 IF (l_count > 1000) THEN
3763 COMMIT;
3764 l_count := 1;
3765 END IF;
3766
3767 END LOOP; -- end of term_part_cur
3768
3769 FND_MSG_PUB.count_and_get (p_count => l_msg_count, p_data => l_msg_data);
3770
3771 EXCEPTION
3772 WHEN fnd_api.g_exc_error
3773 THEN
3774 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
3775 ROLLBACK TO TERMINATE_SUPPLIER_CONTACT_SP;
3776
3777 WHEN OTHERS
3778 THEN
3779 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
3780 ROLLBACK TO TERMINATE_SUPPLIER_CONTACT_SP;
3781 fnd_file.put_line(fnd_file.log, sqlerrm);
3782
3783 END terminate_supplier_contact;
3784
3785
3786 PROCEDURE update_username
3787 IS
3788 BEGIN
3789 UPDATE jtf_rs_resource_extns res
3790 SET user_name = (SELECT user_name
3791 FROM fnd_user fu
3792 WHERE res.user_id = fu.user_id)
3793 WHERE user_id IS NOT NULL;
3794 COMMIT;
3795 EXCEPTION
3796 WHEN fnd_api.g_exc_unexpected_error
3797 THEN
3798 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
3799 WHEN OTHERS
3800 THEN
3801 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
3802 fnd_file.put_line(fnd_file.log, sqlerrm);
3803 END update_username;
3804
3805
3806 PROCEDURE update_userid
3807 IS
3808
3809 l_RETURN_STATUS VARCHAR2(2);
3810 l_MSG_COUNT NUMBER;
3811 l_MSG_DATA VARCHAR2(2000);
3812
3813
3814 CURSOR c_invalid_userid IS
3815 SELECT
3816 RESOURCE_ID,
3817 CATEGORY,
3818 RESOURCE_NUMBER,
3819 SOURCE_ID,
3820 OBJECT_VERSION_NUMBER,
3821 USER_ID,
3822 SOURCE_NAME,
3823 USER_NAME
3824 FROM JTF_RS_RESOURCE_EXTNS_VL jres
3825 WHERE CATEGORY in ('EMPLOYEE','PARTY','PARTNER','SUPPLIER_CONTACT')
3826 AND jres.USER_ID is not null
3827 AND NOT EXISTS
3828 ( SELECT 'x'
3829 FROM fnd_user f
3830 WHERE f.user_id = jres.user_id
3831 AND decode(category,'EMPLOYEE', employee_id,'SUPPLIER_CONTACT',supplier_id,customer_id) = jres.source_id
3832 );
3833
3834 --to update effective FND_USER user_id during the period for the resource
3835 CURSOR c_invalid_active_userid IS
3836 SELECT
3837 RESOURCE_ID,
3838 CATEGORY,
3839 RESOURCE_NUMBER,
3840 SOURCE_ID,
3841 OBJECT_VERSION_NUMBER,
3842 USER_ID,
3843 SOURCE_NAME,
3844 USER_NAME
3845 FROM JTF_RS_RESOURCE_EXTNS_VL jres
3846 WHERE CATEGORY in ('EMPLOYEE','PARTY','PARTNER','SUPPLIER_CONTACT')
3847 AND USER_ID is not null
3848 AND trunc(nvl(END_DATE_ACTIVE,SYSDATE)) >= trunc(SYSDATE)
3849 AND NOT EXISTS
3850 ( SELECT 'x'
3851 FROM fnd_user f
3852 WHERE f.user_id = jres.user_id
3853 AND decode(category,'EMPLOYEE', employee_id,'SUPPLIER_CONTACT',supplier_id,customer_id) = jres.source_id
3854 AND trunc(sysdate) between trunc(start_date) and trunc(nvl(end_date,sysdate))
3855 );
3856
3857 /* Modified the below cursor due to performance resons. Bug # 3121399
3858 After this change, the cost is reduced from 114,186 to 3713 in CRMAPDEV */
3859 /* CURSOR c_null_userid IS
3860 SELECT
3861 RESOURCE_ID,
3862 CATEGORY,
3863 RESOURCE_NUMBER,
3864 SOURCE_ID,
3865 OBJECT_VERSION_NUMBER,
3866 USER_ID,
3867 SOURCE_NAME,
3868 USER_NAME
3869 FROM JTF_RS_RESOURCE_EXTNS_VL jres
3870 WHERE CATEGORY in ('EMPLOYEE','PARTY','PARTNER','SUPPLIER_CONTACT')
3871 AND trunc(nvl(END_DATE_ACTIVE,SYSDATE)) >= trunc(SYSDATE)
3872 AND jres.USER_ID is null
3873 AND exists (select 'x'
3874 from fnd_user f
3875 where jres.source_id = decode(jres.category,'EMPLOYEE',
3876 employee_id,'SUPPLIER_CONTACT',supplier_id,customer_id)
3877 );
3878 */
3879 CURSOR c_null_userid IS
3880 SELECT
3881 RESOURCE_ID,
3882 CATEGORY,
3883 RESOURCE_NUMBER,
3884 SOURCE_ID,
3885 OBJECT_VERSION_NUMBER,
3886 USER_ID,
3887 SOURCE_NAME,
3888 USER_NAME
3889 FROM JTF_RS_RESOURCE_EXTNS_VL jres
3890 WHERE CATEGORY = 'EMPLOYEE'
3891 AND trunc(nvl(END_DATE_ACTIVE,SYSDATE)) >= trunc(SYSDATE)
3892 AND jres.USER_ID is null
3893 AND exists (select 'x'
3894 from fnd_user f
3895 where jres.source_id = employee_id)
3896 union all
3897 SELECT
3898 RESOURCE_ID,
3899 CATEGORY,
3900 RESOURCE_NUMBER,
3901 SOURCE_ID,
3902 OBJECT_VERSION_NUMBER,
3903 USER_ID,
3904 SOURCE_NAME,
3905 USER_NAME
3906 FROM JTF_RS_RESOURCE_EXTNS_VL jres
3907 WHERE CATEGORY = 'SUPPLIER_CONTACT'
3908 AND trunc(nvl(END_DATE_ACTIVE,SYSDATE)) >= trunc(SYSDATE)
3909 AND jres.USER_ID is null
3910 AND exists (select 'x'
3911 from fnd_user f
3912 where jres.source_id = supplier_id)
3913 union all
3914 SELECT
3915 RESOURCE_ID,
3916 CATEGORY,
3917 RESOURCE_NUMBER,
3918 SOURCE_ID,
3919 OBJECT_VERSION_NUMBER,
3920 USER_ID,
3921 SOURCE_NAME,
3922 USER_NAME
3923 FROM JTF_RS_RESOURCE_EXTNS_VL jres
3924 WHERE CATEGORY in ('PARTY','PARTNER')
3925 AND trunc(nvl(END_DATE_ACTIVE,SYSDATE)) >= trunc(SYSDATE)
3926 AND jres.USER_ID is null
3927 AND exists (select 'x'
3928 from fnd_user f
3929 where jres.source_id = customer_id);
3930
3931 CURSOR c_party_partner(p_source_id IN jtf_rs_resource_extns.source_id%TYPE) IS
3932 SELECT user_id,
3933 user_name
3934 FROM fnd_user
3935 WHERE customer_id = p_source_id
3936 AND trunc(sysdate) BETWEEN trunc(start_date) AND trunc(nvl(end_date,sysdate));
3937
3938 CURSOR c_supp_contact(p_source_id IN jtf_rs_resource_extns.source_id%TYPE) is
3939 SELECT user_id,
3940 user_name
3941 FROM fnd_user
3942 WHERE supplier_id = p_source_id
3943 AND trunc(sysdate) BETWEEN trunc(start_date) AND trunc(nvl(end_date,sysdate));
3944
3945 CURSOR c_emp(p_source_id IN jtf_rs_resource_extns.source_id%TYPE) is
3946 SELECT user_id,
3947 user_name
3948 FROM fnd_user
3949 WHERE employee_id = p_source_id
3950 AND trunc(sysdate) BETWEEN trunc(start_date) AND trunc(nvl(end_date,sysdate));
3951
3952 l_user_id fnd_user.user_id%type := NULL;
3953 l_user_name fnd_user.user_name%type := NULL;
3954 l_count number;
3955 i number;
3956
3957 BEGIN
3958 SAVEPOINT UPDATE_USERID_SP;
3959 l_return_status := fnd_api.g_ret_sts_success;
3960 l_count := 1;
3961
3962 FOR l_c_invalid_userid IN c_invalid_userid
3963 LOOP
3964 l_user_id := NULL;
3965 l_user_name := NULL;
3966
3967 IF l_c_invalid_userid.category ='EMPLOYEE' then
3968 OPEN c_emp(l_c_invalid_userid.source_id);
3969 FETCH c_emp INTO l_user_id, l_user_name;
3970 CLOSE c_emp;
3971 ELSIF l_c_invalid_userid.category ='SUPPLIER_CONTACT' then
3972 OPEN c_supp_contact(l_c_invalid_userid.source_id);
3973 FETCH c_supp_contact INTO l_user_id, l_user_name;
3974 CLOSE c_supp_contact;
3975 ELSE
3976 OPEN c_party_partner(l_c_invalid_userid.source_id);
3977 FETCH c_party_partner INTO l_user_id, l_user_name;
3978 CLOSE c_party_partner;
3979 END IF;
3980
3981 l_return_status := fnd_api.g_ret_sts_success;
3982
3983 jtf_rs_resource_pub.update_resource
3984 (p_api_version => 1.0,
3985 p_init_msg_list => FND_API.G_TRUE,
3986 p_resource_id => l_c_invalid_userid.resource_id,
3987 p_resource_number => l_c_invalid_userid.resource_number,
3988 p_user_id => l_user_id,
3989 p_source_name => l_c_invalid_userid.source_name,
3990 p_object_version_num => l_c_invalid_userid.object_version_number,
3991 p_user_name => l_user_name,
3992 x_return_status => l_return_status,
3993 x_msg_count => l_msg_count,
3994 x_msg_data => l_msg_data
3995 ) ;
3996
3997 IF ( l_return_status <> fnd_api.g_ret_sts_success)
3998 THEN
3999 fnd_message.set_name('JTF', 'JTF_RS_CONC_UPDATE_USERID_ERR');
4000 fnd_message.set_token('P_SOURCE_ID', l_c_invalid_userid.source_id);
4001 fnd_file.put_line(fnd_file.log, fnd_message.get);
4002 FOR i IN 1..l_msg_count
4003 LOOP
4004 fnd_file.put_line(fnd_file.log, (fnd_msg_pub.get(i,
4005 p_encoded => fnd_api.g_false)));
4006 END LOOP;
4007 END IF;
4008
4009 l_count := l_count + 1;
4010 IF (l_count > 1000)
4011 THEN
4012 COMMIT;
4013 l_count := 1;
4014 END IF;
4015 END LOOP;
4016
4017 FOR l_c_invalid_active_userid IN c_invalid_active_userid
4018 LOOP
4019 l_user_id := NULL;
4020 l_user_name := NULL;
4021
4022 IF l_c_invalid_active_userid.category ='EMPLOYEE' then
4023 OPEN c_emp(l_c_invalid_active_userid.source_id);
4024 FETCH c_emp INTO l_user_id, l_user_name;
4025 CLOSE c_emp;
4026 ELSIF l_c_invalid_active_userid.category ='SUPPLIER_CONTACT' then
4027 OPEN c_supp_contact(l_c_invalid_active_userid.source_id);
4028 FETCH c_supp_contact INTO l_user_id, l_user_name;
4029 CLOSE c_supp_contact;
4030 ELSE
4031 OPEN c_party_partner(l_c_invalid_active_userid.source_id);
4032 FETCH c_party_partner INTO l_user_id, l_user_name;
4033 CLOSE c_party_partner;
4034 END IF;
4035
4036 l_return_status := fnd_api.g_ret_sts_success;
4037
4038 jtf_rs_resource_pub.update_resource
4039 (p_api_version => 1.0,
4040 p_init_msg_list => FND_API.G_TRUE,
4041 p_resource_id => l_c_invalid_active_userid.resource_id,
4042 p_resource_number => l_c_invalid_active_userid.resource_number,
4043 p_user_id => l_user_id,
4044 p_source_name => l_c_invalid_active_userid.source_name,
4045 p_object_version_num => l_c_invalid_active_userid.object_version_number,
4046 p_user_name => l_user_name,
4047 x_return_status => l_return_status,
4048 x_msg_count => l_msg_count,
4049 x_msg_data => l_msg_data
4050 ) ;
4051
4052 IF ( l_return_status <> fnd_api.g_ret_sts_success)
4053 THEN
4054 fnd_message.set_name('JTF', 'JTF_RS_CONC_UPDATE_USERID_ERR');
4055 fnd_message.set_token('P_SOURCE_ID', l_c_invalid_active_userid.source_id);
4056 fnd_file.put_line(fnd_file.log, fnd_message.get);
4057 FOR i IN 1..l_msg_count
4058 LOOP
4059 fnd_file.put_line(fnd_file.log, (fnd_msg_pub.get(i,
4060 p_encoded => fnd_api.g_false)));
4061 END LOOP;
4062 END IF;
4063
4064 l_count := l_count + 1;
4065 IF (l_count > 1000)
4066 THEN
4067 COMMIT;
4068 l_count := 1;
4069 END IF;
4070 END LOOP;
4071
4072 FOR l_c_null_userid IN c_null_userid
4073 LOOP
4074 l_user_id := NULL;
4075 l_user_name := NULL;
4076
4077 IF l_c_null_userid.category ='EMPLOYEE' then
4078 OPEN c_emp(l_c_null_userid.source_id);
4079 FETCH c_emp INTO l_user_id, l_user_name;
4080 CLOSE c_emp;
4081 ELSIF l_c_null_userid.category ='SUPPLIER_CONTACT' then
4082 OPEN c_supp_contact(l_c_null_userid.source_id);
4083 FETCH c_supp_contact INTO l_user_id, l_user_name;
4084 CLOSE c_supp_contact;
4085 ELSE
4086 OPEN c_party_partner(l_c_null_userid.source_id);
4087 FETCH c_party_partner INTO l_user_id, l_user_name;
4088 CLOSE c_party_partner;
4089 END IF;
4090
4091 l_return_status := fnd_api.g_ret_sts_success;
4092
4093 jtf_rs_resource_pub.update_resource
4094 (p_api_version => 1.0,
4095 p_init_msg_list => FND_API.G_TRUE,
4096 p_resource_id => l_c_null_userid.resource_id,
4097 p_resource_number => l_c_null_userid.resource_number,
4098 p_user_id => l_user_id,
4099 p_source_name => l_c_null_userid.source_name,
4100 p_object_version_num => l_c_null_userid.object_version_number,
4101 p_user_name => l_user_name,
4102 x_return_status => l_return_status,
4103 x_msg_count => l_msg_count,
4104 x_msg_data => l_msg_data
4105 ) ;
4106 IF ( l_return_status <> fnd_api.g_ret_sts_success)
4107 THEN
4108 fnd_message.set_name('JTF', 'JTF_RS_CONC_UPDATE_USERID_ERR');
4109 fnd_message.set_token('P_SOURCE_ID', l_c_null_userid.source_id);
4110 fnd_file.put_line(fnd_file.log, fnd_message.get);
4111 FOR i IN 1..l_msg_count
4112 LOOP
4113 fnd_file.put_line(fnd_file.log, (fnd_msg_pub.get(i,
4114 p_encoded => fnd_api.g_false)));
4115 END LOOP;
4116 END IF;
4117
4118 l_count := l_count + 1;
4119 IF (l_count > 1000)
4120 THEN
4121 COMMIT;
4122 l_count := 1;
4123 END IF;
4124 END LOOP;
4125
4126 FND_MSG_PUB.count_and_get (p_count => l_msg_count, p_data => l_msg_data);
4127 COMMIT;
4128
4129 EXCEPTION
4130 WHEN fnd_api.g_exc_unexpected_error
4131 THEN
4132 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
4133 ROLLBACK TO UPDATE_USERID_SP;
4134 WHEN OTHERS
4135 THEN
4136 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
4137 ROLLBACK TO UPDATE_USERID_SP;
4138 fnd_file.put_line(fnd_file.log, sqlerrm);
4139 END update_userid;
4140
4141
4142 PROCEDURE synchronize_user_name
4143 (ERRBUF OUT NOCOPY VARCHAR2,
4144 RETCODE OUT NOCOPY VARCHAR2,
4145 P_SYNCHRONIZE IN VARCHAR2 DEFAULT 'Both'
4146 )
4147 IS
4148 BEGIN
4149
4150 IF (P_SYNCHRONIZE = 'User Name' OR P_SYNCHRONIZE = 'Both')
4151 THEN
4152 jtf_rs_conc_res_pub.update_username;
4153 END IF;
4154
4155 IF (P_SYNCHRONIZE = 'User Identifier' OR P_SYNCHRONIZE = 'Both')
4156 THEN
4157 jtf_rs_conc_res_pub.update_userid;
4158 END IF;
4159
4160 COMMIT;
4161 EXCEPTION
4162 WHEN fnd_api.g_exc_unexpected_error
4163 THEN
4164 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
4165 WHEN OTHERS
4166 THEN
4167 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
4168 END synchronize_user_name;
4169
4170 END jtf_rs_conc_res_pub;