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