[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_RES_SSWA_PUB
Source
1 PACKAGE BODY jtf_rs_res_sswa_pub AS
2 /* $Header: jtfrssrb.pls 120.1 2005/06/07 23:06:15 baianand ship $ */
3
4 /*****************************************************************************************
5 ******************************************************************************************/
6
7 /* Package variables. */
8
9 G_PKG_NAME VARCHAR2(30) := 'JTF_RS_RES_SSWA_PUB';
10
11 PROCEDURE create_emp_resource
12 (P_API_VERSION IN NUMBER,
13 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
14 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
15 P_SOURCE_FIRST_NAME IN VARCHAR2 DEFAULT NULL,
16 P_SOURCE_LAST_NAME IN VARCHAR2,
17 P_SOURCE_MIDDLE_NAME IN VARCHAR2 DEFAULT NULL,
18 P_EMPLOYEE_NUMBER IN VARCHAR2 DEFAULT NULL,
19 P_SOURCE_SEX IN VARCHAR2,
20 P_SOURCE_TITLE IN VARCHAR2 DEFAULT NULL,
21 P_SOURCE_JOB_ID IN NUMBER DEFAULT NULL,
22 P_SOURCE_EMAIL IN VARCHAR2 DEFAULT NULL,
23 P_SOURCE_START_DATE IN DATE,
24 P_SOURCE_END_DATE IN DATE DEFAULT NULL,
25 P_USER_NAME IN VARCHAR2,
26 P_SOURCE_ADDRESS_ID IN NUMBER DEFAULT NULL,
27 P_SOURCE_OFFICE IN VARCHAR2 DEFAULT NULL,
28 P_SOURCE_MAILSTOP IN VARCHAR2 DEFAULT NULL,
29 P_SOURCE_LOCATION IN VARCHAR2 DEFAULT NULL,
30 P_SOURCE_PHONE IN VARCHAR2 DEFAULT NULL,
31 P_SALESREP_NUMBER IN VARCHAR2,
32 P_SALES_CREDIT_TYPE_ID IN NUMBER,
33 P_SOURCE_MGR_ID IN NUMBER DEFAULT NULL, /* This is the Resource_id of the manager */
34 X_RESOURCE_ID OUT NOCOPY NUMBER,
35 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
36 X_MSG_COUNT OUT NOCOPY NUMBER,
37 X_MSG_DATA OUT NOCOPY VARCHAR2,
38 P_CALLED_FROM IN VARCHAR2 DEFAULT NULL,
39 P_USER_PASSWORD IN OUT NOCOPY VARCHAR2
40 ) IS
41
42 l_api_version constant number := 1.0;
43 l_api_name constant varchar2(30) := 'CREATE_EMP_RESOURCE';
44 l_return_status varchar2(100) := fnd_api.g_ret_sts_success;
45 l_address_id number := to_number(p_source_address_id);
46 l_job_id number := p_source_job_id;
47 l_source_mailstop jtf_rs_resource_extns.source_mailstop%type := p_source_mailstop;
48 l_source_office jtf_rs_resource_extns.source_office%type := p_source_office;
49 l_source_location jtf_rs_resource_extns.source_location%type := p_source_location;
50 l_source_email jtf_rs_resource_extns.source_email%type := p_source_email;
51 l_source_phone jtf_rs_resource_extns.source_phone%type := p_source_phone;
52 l_business_group_id per_all_people_f.business_group_id%type;
53 l_user_name fnd_user.user_name%type := upper(p_user_name);
54 l_user_password fnd_user.encrypted_user_password%type := p_user_password;
55 l_salesrep_number jtf_rs_salesreps.salesrep_number%type := p_salesrep_number;
56 /* Out Parameters for Create Employee*/
57
58 l_person_id number;
59 l_assignment_id number;
60 l_per_object_version_number number;
61 l_asg_object_version_number number;
62 l_per_effective_start_date date;
63 l_per_effective_end_date date;
64 l_full_name varchar2(2000) ;
65 l_employee_number varchar2(2000);
66 l_per_comment_id number;
67 l_assignment_sequence number;
68 l_assignment_number varchar2(250);
69 l_name_combination_warning boolean;
70 l_assign_payroll_warning boolean;
71 l_orig_hire_warning boolean;
72
73 /* Out Parameters for Create Phone*/
74
75 l_object_version_number_phone number;
76 l_phone_id number;
77
78 /* Out Parameters for Create Resource*/
79
80 l_msg_count number;
81 l_msg_data varchar2(2000);
82 l_resource_id number;
83 l_resource_number varchar2(2000);
84
85 /* Out Parameters for Create Resource*/
86
87 l_salesrep_id number;
88
89 /* Cursor Variables to get Addres Deatils */
90
91 cursor address_cur(l_address_id number)
92 is
93 select address_line_1,
94 address_line_2,
95 address_line_3 ,
96 town_or_city ,
97 country,
98 postal_code
99 from hr_locations
100 where location_id = l_address_id;
101
102 l_address_line_1 hr_locations.address_line_1%type;
103 l_address_line_2 hr_locations.address_line_2%type;
104 l_address_line_3 hr_locations.address_line_3%type;
105 l_town_or_city hr_locations.town_or_city%type;
106 l_country hr_locations.country%type;
107 l_postal_code hr_locations.postal_code%type;
108 -- address_rec address_cur%rowtype;
109
110 /* Cursor Variables to get Org Deatils */
111
112 cursor org_details(l_business_group_id number)
113 is
114 select name
115 from hr_all_organization_units
116 where l_business_group_id = organization_id;
117
118 l_org_name varchar2(2000);
119
120 /* Cursor Variables to get Manager Deatils */
121
122 /* cursor mgr_details(p_source_mgr_id number)
123 is
124 select full_name, person_id
125 from per_all_people_f
126 where trunc(sysdate ) between effective_start_date and effective_end_date
127 and person_id = (select source_id from jtf_rs_resource_extns
128 and resource_id = p_source_mgr_id);
129 */
130 cursor mgr_details(p_source_mgr_id number)
131 is
132 select source_name, source_id
133 from jtf_rs_resource_extns
134 where resource_id = p_source_mgr_id;
135
136 l_source_mgr_name varchar2(2000);
137 l_mgr_source_id number;
138
139 /* Cursor Variables to get Fnd User Deatils */
140
141 cursor fnd_user_details(l_user_name varchar2)
142 is
143 select user_id
144 from fnd_user
145 where user_name = l_user_name;
146
147 l_user_id number;
148
149 /* Cursor Variables to insert Job and Manager Deatils */
150
151 cursor job_cur(l_person_id number)
152 is
153 select object_version_number,
154 assignment_id,
155 effective_start_date,
156 effective_end_date,
157 business_group_id,
158 location_id,
159 special_ceiling_step_id,
160 last_update_date,
161 job_id
162 from per_all_assignments_f
163 where person_id = l_person_id
164 and effective_start_date = (select max(effective_start_date)
165 from per_all_assignments_f
166 where person_id = l_person_id
167 and assignment_type = 'E'
168 and primary_flag = 'Y')
169 and effective_end_date = (select max(effective_end_date)
170 from per_all_assignments_f
171 where person_id = l_person_id
172 and assignment_type = 'E'
173 and primary_flag = 'Y')
174 and assignment_type = 'E'
175 and primary_flag = 'Y';
176
177 job_rec job_cur%rowtype;
178
179 l_object_version_number_assg number;
180 l_special_ceiling_step_id number;
181 l_group_name varchar2(2000);
182 l_datetrack_update_mode varchar2(2000) := 'UPDATE';
183 l_effective_start_date date;
184 l_effective_end_date date;
185 l_people_group_id number;
186 l_org_now_no_manager_warning boolean;
187 l_other_manager_warning boolean;
188 l_spp_delete_warning boolean;
189 l_entries_changed_earning varchar2(2000);
190 l_tax_district_changed_earning boolean ;
191 l_soft_coding_keyflex_id number;
192 l_concatenated_segments varchar2(2000);
193 l_comment_id number;
194 l_no_managers_warning boolean;
195
196 /* Cursor Variables to Job Name */
197
198 cursor Job_dtls(l_job_id number)
199 is
200 select name
201 from per_jobs
202 where job_id = l_job_id;
203
204 l_job_name varchar2(240) := null;
205
206 hr_api_error exception;
207 PRAGMA EXCEPTION_INIT(hr_api_error, -20001);
208
209 BEGIN
210 x_return_status := fnd_api.g_ret_sts_success;
211
212 savepoint cr_emp_save;
213
214 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
215 if fnd_api.tO_BOOLEAN(p_init_msg_list)
216 then
217 fnd_msg_pub.Initialize;
218 end if;
219
220 l_employee_number := p_employee_number;
221
222 /* Get Business group Id */
223
224 fnd_profile.get('PER_BUSINESS_GROUP_ID',l_business_group_id);
225
226
227 /* Calling the hr api's to create an Employee in HR database. */
228
229 hr_employee_api.create_employee
230 (p_hire_date => p_source_start_date
231 ,p_business_group_id => l_business_group_id
232 ,p_last_name => p_source_last_name
233 ,p_sex => p_source_sex
234 ,p_email_address => p_source_email
235 ,p_employee_number => l_employee_number
236 ,p_first_name => p_source_first_name
237 ,p_middle_names => p_source_middle_name
238 ,p_title => p_source_title
239 ,p_work_telephone => p_source_phone
240 ,p_internal_location => p_source_location
241 ,p_mailstop => p_source_mailstop
242 ,p_office_number => p_source_office
243 ,p_person_id => l_person_id
244 ,p_assignment_id => l_assignment_id
245 ,p_per_object_version_number => l_per_object_version_number
246 ,p_asg_object_version_number => l_asg_object_version_number
247 ,p_per_effective_start_date => l_per_effective_start_date
248 ,p_per_effective_end_date => l_per_effective_end_date
249 ,p_full_name => l_full_name
250 ,p_per_comment_id => l_per_comment_id
251 ,p_assignment_sequence => l_assignment_sequence
252 ,p_assignment_number => l_assignment_number
253 ,p_name_combination_warning => l_name_combination_warning
254 ,p_assign_payroll_warning => l_assign_payroll_warning
255 ,p_orig_hire_warning => l_orig_hire_warning
256 );
257
258 /* Fetching Manager datails */
259 open mgr_details(p_source_mgr_id);
260 fetch mgr_details into l_source_mgr_name,l_mgr_source_id;
261 close mgr_details;
262
263 if l_mgr_source_id is not null then
264 --update supervisor id
265 open job_cur(l_person_id);
266 fetch job_cur into job_rec;
267 if(job_cur%found) then
268 l_object_version_number_assg := job_rec.object_version_number;
269 l_special_ceiling_step_id := job_rec.special_ceiling_step_id;
270 if(trunc(job_rec.last_update_date)= trunc(sysdate)) then
271 l_datetrack_update_mode := 'CORRECTION';
272 else
273 l_datetrack_update_mode := 'UPDATE';
274 end if;
275
276 hr_assignment_api.update_emp_asg
277 (p_effective_date => trunc(sysdate)
278 ,p_datetrack_update_mode => l_datetrack_update_mode
279 ,p_assignment_id => job_rec.assignment_id
280 ,p_object_version_number => l_object_version_number_assg
281 ,p_supervisor_id => l_mgr_source_id
282 ,p_concatenated_segments => l_concatenated_segments
283 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
284 ,p_comment_id => l_comment_id
285 ,p_effective_start_date => l_effective_start_date
286 ,p_effective_end_date => l_effective_start_date
287 ,p_no_managers_warning => l_no_managers_warning
288 ,p_other_manager_warning => l_other_manager_warning );
289 end if;
290 close job_cur;
291 end if;
292
293 if l_job_id is not null then
294 --update job id
295 open job_cur(l_person_id);
296 fetch job_cur into job_rec;
297 if(job_cur%found) then
298 l_object_version_number_assg := job_rec.object_version_number;
299 l_special_ceiling_step_id := job_rec.special_ceiling_step_id;
300 if(trunc(job_rec.last_update_date)= trunc(sysdate)) then
301 l_datetrack_update_mode := 'CORRECTION';
302 else
303 l_datetrack_update_mode := 'UPDATE';
304 end if;
305
306 hr_assignment_api.update_emp_asg_criteria
307 (p_effective_date => trunc(sysdate)
308 ,p_datetrack_update_mode => l_datetrack_update_mode
309 ,p_assignment_id => job_rec.assignment_id
310 ,p_object_version_number => l_object_version_number_assg
311 ,p_job_id => l_job_id
312 ,p_special_ceiling_step_id => l_special_ceiling_step_id
313 ,p_group_name => l_group_name
314 ,p_effective_start_date => l_effective_start_date
315 ,p_effective_end_date => l_effective_start_date
316 ,p_people_group_id => l_people_group_id
317 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
318 ,p_other_manager_warning => l_other_manager_warning
319 ,p_spp_delete_warning => l_spp_delete_warning
320 ,p_entries_changed_warning => l_entries_changed_earning
321 ,p_tax_district_changed_warning => l_tax_district_changed_earning
322 );
323 end if;
324 close job_cur;
325 end if;
326
327 open Job_dtls(l_job_id);
328 fetch Job_dtls into l_job_name;
329 close Job_dtls;
330
331 -- end if;
332
333 -- generate fnd user password
334
335 jtf_um_password_pvt.generate_password (
336 p_api_version_number => 1.0,
337 x_password => l_user_password,
338 x_return_status => l_return_status,
339 x_msg_count => x_msg_count,
340 x_msg_data => x_msg_data
341 );
342 if not (l_return_status = fnd_api.g_ret_sts_success) THEN
343 raise fnd_api.g_exc_unexpected_error;
344 end if;
345
346 p_user_password := l_user_password;
347
348 fnd_user_pkg.CreateUser (
349 x_user_name => l_user_name
350 ,x_owner => null
351 ,x_unencrypted_password => l_user_password
352 ,x_start_date => p_source_start_date
353 ,x_end_date => p_source_end_date
354 ,x_employee_id => l_person_id
355 ,x_email_address => p_source_email
356 );
357
358 /* Fetching User Id datails */
359 if(l_user_name is NOT NULL )
360 then
361 open fnd_user_details(l_user_name);
362 fetch fnd_user_details into l_user_id;
363 close fnd_user_details;
364
365 /* if l_user_id is NOT NULL
366 then
367 raise
368 end if;
369 */
370
371 end if;
372
373 /* Fetching Location datails */
374 if(l_address_id <> fnd_api.g_miss_num)
375 then
376 open address_cur(l_address_id);
377 fetch address_cur
378 into l_address_line_1,
379 l_address_line_2,
380 l_address_line_3,
381 l_town_or_city,
382 l_country,
383 l_postal_code;
384 close address_cur;
385 else
386 l_address_line_1 := NULL;
387 l_address_line_2 := NULL;
388 l_address_line_3 := NULL;
389 l_town_or_city := NULL;
390 l_country := NULL;
391 l_postal_code := NULL;
392 end if;
393
394 /* Fetching Org datails */
395 open org_details(l_business_group_id);
396 fetch org_details into l_org_name;
397 close org_details;
398
399 -- Calling Create Resource API
400
401 jtf_rs_resource_pub.create_resource
402 ( p_api_version => 1.0
403 ,p_init_msg_list => fnd_api.g_false
404 ,p_commit => fnd_api.g_false
405 ,p_category => 'EMPLOYEE'
406 ,p_source_id => l_person_id
407 ,p_address_id => p_source_address_id
408 ,p_contact_id => null
409 ,p_managing_emp_id => null
410 ,p_start_date_active => p_source_start_date
411 ,p_end_date_active => p_source_end_date
412 ,p_time_zone => null
413 ,p_cost_per_hr => null
414 ,p_primary_language => null
415 ,p_secondary_language => null
416 ,p_support_site_id => null
417 ,p_ies_agent_login => null
418 ,p_server_group_id => null
419 ,p_assigned_to_group_id => null
420 ,p_cost_center => null
421 ,p_charge_to_cost_center => null
422 ,p_comp_currency_code => null
423 ,p_commissionable_flag => null
424 ,p_hold_reason_code => null
425 ,p_hold_payment => null
426 ,p_comp_service_team_id => null
427 ,p_user_id => l_user_id
428 ,p_transaction_number => null
429 ,x_return_status => l_return_status
430 ,x_msg_count => l_msg_count
431 ,x_msg_data => l_msg_data
432 ,x_resource_id => l_resource_id
433 ,x_resource_number => l_resource_number
434 ,p_resource_name => l_full_name
435 ,p_source_name => l_full_name
436 ,p_source_number => l_employee_number
437 ,p_source_job_title => l_job_name
438 ,p_source_email => p_source_email
439 ,p_source_phone => p_source_phone
440 ,p_source_org_id => null
441 ,p_source_org_name => null
442 ,p_source_address1 => l_address_line_1
443 ,p_source_address2 => l_address_line_2
444 ,p_source_address3 => l_address_line_3
445 ,p_source_city => l_town_or_city
446 ,p_source_country => l_country
447 ,p_source_postal_code => l_postal_code
448 ,p_source_address4 => null
449 ,p_source_state => null
450 ,p_source_province => null
451 ,p_source_county => null
452 ,p_source_mgr_id => l_mgr_source_id
453 ,p_source_mgr_name => l_source_mgr_name
454 ,p_source_business_grp_id => l_business_group_id
455 ,p_source_business_grp_name => l_org_name
456 ,p_source_first_name => p_source_first_name
457 ,p_source_last_name => p_source_last_name
458 ,p_source_middle_name => p_source_middle_name
459 ,p_source_category => null
460 ,p_source_status => null
461 ,p_source_office => p_source_office
462 ,p_source_location => p_source_location
463 ,p_source_mailstop => p_source_mailstop
464 ,p_user_name => l_user_name
465 );
466
467 if not (l_return_status = fnd_api.g_ret_sts_success) THEN
468 raise fnd_api.g_exc_unexpected_error;
469 end if;
470
471 x_resource_id := l_resource_id;
472
473 if (p_called_from = 'CRT_BULK_IMPORT') then
474 l_salesrep_number := l_employee_number;
475 end if;
476
477 -- below call to MO_GLOBAL init is added for MOAC changes.
478 -- If we didn't pass a value for org id in create_salesrep API,
479 -- it will call the get_valid_org procedure and get the default operating unit.
480 MO_GLOBAL.init('JTF');
481 jtf_rs_salesreps_pub.create_salesrep (
482 p_api_version => p_api_version
483 ,p_init_msg_list => p_init_msg_list
484 ,p_commit => p_commit
485 ,p_resource_id => l_resource_id
486 ,p_sales_credit_type_id => p_sales_credit_type_id
487 ,p_name => l_full_name
488 ,p_status => 'A'
489 ,p_start_date_active => p_source_start_date
490 ,p_end_date_active => p_source_end_date
491 ,p_gl_id_rev => null
492 ,p_gl_id_freight => null
493 ,p_gl_id_rec => null
494 ,p_set_of_books_id => null
495 ,p_salesrep_number => l_salesrep_number
496 ,p_email_address => null
497 ,p_wh_update_date => null
498 ,p_sales_tax_geocode => null
499 ,p_sales_tax_inside_city_limits => '1'
500 ,x_return_status => l_return_status
501 ,x_msg_count => l_msg_count
502 ,x_msg_data => l_msg_data
503 ,x_salesrep_id => l_salesrep_id
504 );
505
506 if not (l_return_status = fnd_api.g_ret_sts_success) THEN
507 raise fnd_api.g_exc_unexpected_error;
508 end if;
509
510 if (p_called_from <> 'CRT_BULK_IMPORT') then
511
512 -- initiate the workflow to send the password
513 jtf_um_password_pvt.send_password(
514 p_api_version_number => 1.0,
515 p_requester_user_name => l_user_name,
516 p_requester_password => l_user_password,
517 p_first_time_user => 'Y',
518 p_user_verified => 'Y',
519 x_return_status => l_return_status,
520 x_msg_count => x_msg_count,
521 x_msg_data => x_msg_data
522 );
523
524 -- if not (l_return_status = fnd_api.g_ret_sts_success) THEN
525 -- raise fnd_api.g_exc_unexpected_error;
526 -- end if;
527
528 end if;
529
530 if fnd_api.to_boolean(p_commit)
531 then
532 commit work;
533 end if;
534
535 exception
536 WHEN hr_api_error
537 THEN
538 ROLLBACK TO cr_emp_save;
539 x_return_status := fnd_api.g_ret_sts_error;
540 fnd_message.set_name('JTF','JTF_RS_HR_API_ERROR');
541 fnd_message.set_token('P_SQLERRM',substr(SQLERRM,11));
542 FND_MSG_PUB.add;
543 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
544 when fnd_api.g_exc_unexpected_error
545 then
546 rollback to cr_emp_save;
547 x_return_status := fnd_api.g_ret_sts_error;
548 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
549 when others
550 then
551 rollback to cr_emp_save;
552 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
553 fnd_message.set_token('P_SQLCODE',SQLCODE);
554 fnd_message.set_token('P_SQLERRM',SQLERRM);
555 fnd_message.set_token('P_API_NAME',l_api_name);
556 FND_MSG_PUB.add;
557 x_return_status := fnd_api.g_ret_sts_unexp_error;
558 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
559
560 END create_emp_resource;
561
562 PROCEDURE update_resource
563 (P_API_VERSION IN NUMBER,
564 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
565 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
566 P_RESOURCE_ID IN NUMBER,
567 P_RESOURCE_NUMBER IN VARCHAR2,
568 P_RESOURCE_NAME IN VARCHAR2 ,
569 P_SOURCE_NAME IN VARCHAR2 ,
570 P_ADDRESS_ID IN VARCHAR2 ,
571 P_SOURCE_OFFICE IN VARCHAR2 ,
572 P_SOURCE_MAILSTOP IN VARCHAR2 ,
573 P_SOURCE_LOCATION IN VARCHAR2 ,
574 P_SOURCE_PHONE IN VARCHAR2 ,
575 P_SOURCE_EMAIL IN VARCHAR2 ,
576 P_OBJECT_VERSION_NUMBER IN NUMBER,
577 P_APPROVED IN VARCHAR2 DEFAULT 'N',
578 P_SOURCE_JOB_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
579 P_SOURCE_JOB_TITLE IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
580 P_SALESREP_NUMBER IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
581 P_SALES_CREDIT_TYPE_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
582 P_END_DATE_ACTIVE IN DATE DEFAULT FND_API.G_MISS_DATE,
583 P_USER_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
584 P_USER_NAME IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
585 P_MGR_RESOURCE_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
586 P_ORG_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
587 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
588 X_MSG_COUNT OUT NOCOPY NUMBER,
589 X_MSG_DATA OUT NOCOPY VARCHAR2,
590 P_TIME_ZONE IN NUMBER DEFAULT FND_API.G_MISS_NUM,
591 P_COST_PER_HR IN NUMBER DEFAULT FND_API.G_MISS_NUM,
592 P_PRIMARY_LANGUAGE IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
593 P_SECONDARY_LANGUAGE IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
594 P_SUPPORT_SITE_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
595 P_SOURCE_MOBILE_PHONE IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
596 P_SOURCE_PAGER IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
597 ) IS
598
599 l_api_version CONSTANT NUMBER := 1.0;
600 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE';
601 l_return_status VARCHAR2(100) := FND_API.G_RET_STS_SUCCESS;
602 l_job_id number := p_source_job_id;
603 l_job_title varchar2(2000) := p_source_job_title;
604 l_supervisor_id number ;
605
606 cursor old_cur
607 is
608 select RESOURCE_NAME
609 , SOURCE_NAME
610 , SOURCE_NUMBER
611 , SOURCE_MGR_NAME
612 , SOURCE_JOB_TITLE
613 , SOURCE_ADDRESS1
614 , SOURCE_ADDRESS2
615 , SOURCE_ADDRESS3
616 , SOURCE_ADDRESS4
617 , SOURCE_CITY
618 , SOURCE_STATE
619 , SOURCE_PROVINCE
620 , SOURCE_POSTAL_CODE
621 , SOURCE_COUNTY
622 , SOURCE_COUNTRY
623 , SOURCE_PHONE
624 , SOURCE_EMAIL
625 , ADDRESS_ID
626 , SOURCE_OFFICE
627 , SOURCE_MAILSTOP
628 , SOURCE_LOCATION
629 , SOURCE_ID
630 , SOURCE_MGR_ID
631 , START_DATE_ACTIVE
632 , END_DATE_ACTIVE
633 , TIME_ZONE
634 , COST_PER_HR
635 , PRIMARY_LANGUAGE
636 , SECONDARY_LANGUAGE
637 , SUPPORT_SITE_ID
638 , SOURCE_MOBILE_PHONE
639 , SOURCE_PAGER
640 , USER_ID
641 from jtf_rs_resource_extns_vl
642 where resource_id = p_resource_id;
643
644 old_rec old_cur%rowtype;
645
646 cursor salesrep_cur(l_resource_id number,
647 l_org_id number)
648 is
649 select salesrep_id,
650 object_version_number,
651 sales_credit_type_id,
652 salesrep_number
653 from jtf_rs_salesreps
654 where resource_id = l_resource_id
655 and org_id = l_org_id;
656
657 salesrep_rec salesrep_cur%rowtype;
658
659 l_srp_object_version_number number;
660 l_salesrep_id number;
661
662 cursor mgr_user(l_mgr_id number)
663 is
664 select user_id
665 from fnd_user
666 where employee_id = l_mgr_id;
667
668 l_mgr_user_id number;
669
670 cursor user_cur(l_user_id number)
671 is
672 select user_name
673 from fnd_user
674 where user_id = l_user_id;
675
676 l_user_name fnd_user.user_name%type;
677
678 l_u number := 0;
679 l_uwn number := 0;
680 l_uwa number := 0;
681 l_direct_call varchar2(1) := 'Y';
682
683 cursor attr_cur
684 is
685 select attribute_name
686 , attribute_access_level
687 from jtf_rs_table_attributes_b;
688
689
690 attr_rec attr_cur%rowtype;
691
692
693 TYPE ATTR_TYPE is Record
694 (attribute_name varchar2(30),
695 attribute_access_level varchar2(30));
696 type attr_tab_type is table of attr_type index by BINARY_INTEGER;
697
698 l_attr_rec attr_tab_type;
699 i BINARY_INTEGER := 0;
700 l_found BOOLEAN := FALSE;
701 l_object_version_number number := p_object_version_number;
702
703 cursor assg_cur(l_person_id number)
704 is
705 select object_version_number,
706 assignment_id,
707 effective_start_date,
708 effective_end_date,
709 business_group_id,
710 location_id,
711 special_ceiling_step_id,
712 last_update_date,
713 job_id
714 from per_all_assignments_f
715 where person_id = l_person_id
716 and effective_start_date = (select max(effective_start_date)
717 from per_all_assignments_f
718 where person_id = l_person_id
719 and assignment_type = 'E'
720 and primary_flag = 'Y')
721 and effective_end_date = (select max(effective_end_date)
722 from per_all_assignments_f
723 where person_id = l_person_id
724 and assignment_type = 'E'
725 and primary_flag = 'Y')
726 and assignment_type = 'E'
727 and primary_flag = 'Y';
728
729 assg_rec assg_cur%rowtype;
730
731 cursor per_cur(l_person_id number)
732 is
733 select person_id,
734 employee_number,
735 last_update_date,
736 object_version_number
737 from per_all_people_f
738 where person_id = l_person_id
739 and effective_start_date = (select max(effective_start_date)
740 from per_all_people_f
741 where person_id = l_person_id
742 and employee_number is not null )
743 and effective_end_date = (select max(effective_end_date)
744 from per_all_people_f
745 where person_id = l_person_id
746 and employee_number is not null )
747 and employee_number is not null;
748
749 per_rec per_cur%rowtype;
750
751 -- Bug # 2186026
752 -- OK_... fields are set for changes for which no approval needed or just the
753 -- noficiations are required. Whereas APPRV_.. fields are set when
754 -- changes requires approval
755 aprvl_resource_name jtf_rs_resource_extns_vl.resource_name%type := fnd_api.g_miss_char;
756 aprvl_source_name jtf_rs_resource_extns_vl.source_name%type := fnd_api.g_miss_char;
757 aprvl_address_id jtf_rs_resource_extns_vl.address_id%type := fnd_api.g_miss_num;
758 aprvl_source_office jtf_rs_resource_extns_vl.source_office%type := fnd_api.g_miss_char;
759 aprvl_source_mailstop jtf_rs_resource_extns_vl.source_mailstop%type := fnd_api.g_miss_char;
760 aprvl_source_location jtf_rs_resource_extns_vl.source_location%type := fnd_api.g_miss_char;
761 aprvl_source_phone jtf_rs_resource_extns_vl.source_phone%type := fnd_api.g_miss_char;
762 aprvl_source_mobile_phone jtf_rs_resource_extns_vl.source_mobile_phone%type := fnd_api.g_miss_char;
763 aprvl_source_pager jtf_rs_resource_extns_vl.source_pager%type := fnd_api.g_miss_char;
764 aprvl_source_email jtf_rs_resource_extns_vl.source_email%type := fnd_api.g_miss_char;
765 aprvl_time_zone jtf_rs_resource_extns_vl.time_zone%type := fnd_api.g_miss_num;
766 aprvl_support_site_id jtf_rs_resource_extns_vl.support_site_id%type := fnd_api.g_miss_num;
767 aprvl_primary_language jtf_rs_resource_extns_vl.primary_language%type := fnd_api.g_miss_char;
768 aprvl_secondary_language jtf_rs_resource_extns_vl.secondary_language%type := fnd_api.g_miss_char;
769 aprvl_cost_per_hr jtf_rs_resource_extns_vl.cost_per_hr%type := fnd_api.g_miss_num;
770
771 ok_resource_name jtf_rs_resource_extns_vl.resource_name%type := p_resource_name;
772 ok_source_name jtf_rs_resource_extns_vl.source_name%type := p_source_name;
773 ok_address_id jtf_rs_resource_extns_vl.address_id%type := to_number(p_address_id);
774 ok_source_office jtf_rs_resource_extns_vl.source_office%type := p_source_office;
775 ok_source_mailstop jtf_rs_resource_extns_vl.source_mailstop%type := p_source_mailstop;
776 ok_source_location jtf_rs_resource_extns_vl.source_location%type := p_source_location;
777 ok_source_phone jtf_rs_resource_extns_vl.source_phone%type := p_source_phone;
778 ok_source_mobile_phone jtf_rs_resource_extns_vl.source_mobile_phone%type := p_source_mobile_phone;
779 ok_source_pager jtf_rs_resource_extns_vl.source_pager%type := p_source_pager;
780 ok_source_email jtf_rs_resource_extns_vl.source_email%type := p_source_email;
781 ok_time_zone jtf_rs_resource_extns_vl.time_zone%type := p_time_zone;
782 ok_support_site_id jtf_rs_resource_extns_vl.support_site_id%type := p_support_site_id;
783 ok_primary_language jtf_rs_resource_extns_vl.primary_language%type := p_primary_language;
784 ok_secondary_language jtf_rs_resource_extns_vl.secondary_language%type := p_secondary_language;
785 ok_cost_per_hr jtf_rs_resource_extns_vl.cost_per_hr%type := p_cost_per_hr;
786
787 --variables for assg update call
788 l_object_version_number_assg number;
789 l_special_ceiling_step_id number;
790 l_group_name varchar2(2000);
791 l_datetrack_update_mode varchar2(2000) := 'UPDATE';
792 l_effective_start_date date;
793 l_effective_end_date date;
794 l_people_group_id number;
795 l_org_now_no_manager_warning boolean;
796 l_other_manager_warning boolean;
797 l_spp_delete_warning boolean;
798 l_entries_changed_earning varchar2(2000);
799 l_tax_district_changed_earning boolean ;
800 l_cagr_grade_def_id number;
801 l_cagr_concatenated_segments varchar2(2000);
802 l_soft_coding_keyflex_id number;
803 l_concatenated_segments varchar2(2000);
804
805 l_no_managers_warning boolean;
806 --variables for person update
807 l_object_version_number_per number;
808 l_employee_number varchar2(2000);
809 l_full_name varchar2(2000);
810 l_comment_id number;
811 l_name_combination_warning boolean;
812 l_assign_payroll_warning boolean;
813 l_orig_hire_warning boolean;
814
815
816
817 cursor address_cur(l_address_id number)
818 is
819 select ADDRESS_LINE_1,
820 ADDRESS_LINE_2,
821 ADDRESS_LINE_3 ,
822 TOWN_OR_CITY ,
823 COUNTRY,
824 POSTAL_CODE
825 from hr_locations
826 where location_id = l_address_id;
827
828 address_rec address_cur%rowtype;
829
830
831 cursor phone_cur(l_person_id number)
832 is
833 select phone_id,
834 object_version_number
835 from per_phones p1
836 where parent_table = 'PER_ALL_PEOPLE_F'
837 and parent_id = l_person_id
838 and phone_type = 'W1'
839 and date_from = (select max(date_from)
840 from per_phones p2
841 where parent_table = 'PER_ALL_PEOPLE_F'
842 and parent_id = l_person_id
843 and phone_type = 'W1' );
844
845 phone_rec phone_cur%rowtype;
846 l_object_version_number_phone number;
847 l_phone_id number;
848
849
850 cursor mobile_phone_cur(l_person_id number)
851 is
852 select phone_id,
853 object_version_number
854 from per_phones p1
855 where parent_table = 'PER_ALL_PEOPLE_F'
856 and parent_id = l_person_id
857 and phone_type = 'M'
858 and date_from = (select max(date_from)
859 from per_phones p2
860 where parent_table = 'PER_ALL_PEOPLE_F'
861 and parent_id = l_person_id
862 and phone_type = 'M' );
863
864 mobile_phone_rec mobile_phone_cur%rowtype;
865 l_object_ver_num_mobile_ph number;
866 l_mobile_phone_id number;
867
868
869 cursor pager_cur(l_person_id number)
870 is
871 select phone_id,
872 object_version_number
873 from per_phones p1
874 where parent_table = 'PER_ALL_PEOPLE_F'
875 and parent_id = l_person_id
876 and phone_type = 'P'
877 and date_from = (select max(date_from)
878 from per_phones p2
879 where parent_table = 'PER_ALL_PEOPLE_F'
880 and parent_id = l_person_id
881 and phone_type = 'P' );
882
883 pager_rec pager_cur%rowtype;
884 l_object_version_number_pager number;
885 l_pager_id number;
886
887
888 cursor mgr_res_cur(l_resource_id number)
889 is
890 select source_id,
891 source_name
892 from jtf_rs_resource_extns
893 where resource_id = l_resource_id;
894
895 l_mgr_source_name jtf_rs_resource_extns.source_name%type := fnd_api.g_miss_char;
896 l_mgr_source_id jtf_rs_resource_extns.source_id%type := fnd_api.g_miss_num;
897 l_mgr_name jtf_rs_resource_extns_vl.resource_name%type;
898 mgr_res_rec mgr_res_cur%rowtype;
899
900 l_fnd_date date := to_date(to_char(fnd_api.g_miss_date, 'DD-MM-RRRR'), 'DD-MM-RRRR');
901
902 hr_api_error exception;
903 PRAGMA EXCEPTION_INIT(hr_api_error, -20001);
904 BEGIN
905 x_return_status := fnd_api.g_ret_sts_success;
906 --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
907 IF FND_API.To_boolean(P_INIT_MSG_LIST)
908 THEN
909 FND_MSG_PUB.Initialize;
910 END IF;
911 SAVEPOINT sp_save;
912 IF(P_APPROVED='NR')
913 THEN
914 -- if called from the all update screen then work like l_u = 1
915 l_u := 1;
916 END IF;
917 IF(P_APPROVED='N')
918 THEN
919 x_return_status := fnd_api.g_ret_sts_success;
920 --fetch the attributes
921 open attr_cur;
922 fetch attr_cur into attr_rec;
923 while(attr_cur%found)
924 loop
925 i := i + 1;
926 l_attr_rec(i).attribute_name := attr_rec.attribute_name;
927 l_attr_rec(i).attribute_access_level := attr_rec.attribute_access_level;
928 fetch attr_cur into attr_rec;
929 end loop;
930 close attr_cur;
931
932
933
934 open old_cur;
935 fetch old_cur into old_rec;
936 if (old_cur%found)
937 then
938 --resource name
939 if(nvl(ok_resource_name, fnd_api.g_miss_char) <>
940 nvl(old_rec.resource_name, fnd_api.g_miss_char))
941 then
942 i:= 0;
943 l_found := FALSE;
944 for i in 1..l_attr_rec.COUNT
945 loop
946 if(l_attr_rec(i).attribute_name = 'RESOURCE_NAME')
947 then
948 l_found := TRUE;
949 if(l_attr_rec(i).attribute_access_level = 'U')
950 then
951 l_u := 1;
952 elsif(l_attr_rec(i).attribute_access_level = 'UWN')
953 then
954 l_uwn := 1;
955 elsif(l_attr_rec(i).attribute_access_level = 'UWA')
956 then
957 l_uwa := 1;
958 aprvl_resource_name := ok_resource_name;
959 ok_resource_name := fnd_api.g_miss_char;
960 end if;
961
962 end if;
963 if(l_found) then exit; end if;
964 end loop;
965 end if;
966
967 --source name
968 if(nvl(ok_source_name, fnd_api.g_miss_char) <>
969 nvl(old_rec.source_name, fnd_api.g_miss_char))
970 then
971 i:= 0;
972 l_found := FALSE;
973 for i in 1..l_attr_rec.COUNT
974 loop
975 if(l_attr_rec(i).attribute_name = 'RESOURCE_NAME')
976 then
977 l_found := TRUE;
978 if(l_attr_rec(i).attribute_access_level = 'U')
979 then
980 l_u := 1;
981 elsif(l_attr_rec(i).attribute_access_level = 'UWN')
982 then
983 l_uwn := 1;
984 elsif(l_attr_rec(i).attribute_access_level = 'UWA')
985 then
986 l_uwa := 1;
987 aprvl_source_name := ok_source_name;
988 ok_source_name := fnd_api.g_miss_char;
989 end if;
990
991 end if;
992 if(l_found) then exit; end if;
993 end loop;
994 end if;
995
996
997 --address
998
999 if(nvl(ok_address_id, fnd_api.g_miss_num) <>
1000 nvl(old_rec.address_id, fnd_api.g_miss_num))
1001 then
1002 i:= 0;
1003 l_found := FALSE;
1004 for i in 1..l_attr_rec.COUNT
1005 loop
1006 if(l_attr_rec(i).attribute_name = 'SOURCE_ADDRESS')
1007 then
1008 l_found := TRUE;
1009 if(l_attr_rec(i).attribute_access_level = 'U')
1010 then
1011 l_u := 1;
1012 elsif(l_attr_rec(i).attribute_access_level = 'UWN')
1013 then
1014 l_uwn := 1;
1015 elsif(l_attr_rec(i).attribute_access_level = 'UWA')
1016 then
1017 l_uwa := 1;
1018 aprvl_address_id := ok_address_id;
1019 ok_address_id := fnd_api.g_miss_num;
1020 end if;
1021
1022 end if;
1023 if(l_found) then exit; end if;
1024 end loop;
1025 else
1026 ok_address_id := fnd_api.g_miss_num;
1027 end if;
1028
1029
1030 --office
1031
1032 if(nvl(ok_source_office, fnd_api.g_miss_char) <>
1033 nvl(old_rec.source_office, fnd_api.g_miss_char))
1034 then
1035 i:= 0;
1036 l_found := FALSE;
1037 for i in 1..l_attr_rec.COUNT
1038 loop
1039 if(l_attr_rec(i).attribute_name = 'SOURCE_OFFICE')
1040 then
1041 l_found := TRUE;
1042 if(l_attr_rec(i).attribute_access_level = 'U')
1043 then
1044 l_u := 1;
1045 elsif(l_attr_rec(i).attribute_access_level = 'UWN')
1046 then
1047 l_uwn := 1;
1048 elsif(l_attr_rec(i).attribute_access_level = 'UWA')
1049 then
1050 l_uwa := 1;
1051 aprvl_source_office := ok_source_office;
1052 -- not setting ok_source_office as G_MISS as HR API
1053 -- does not understand G_MISS and inserts G_MISS into
1054 -- the database which causes form crash/junk value
1055 -- bug # 3114608
1056 ok_source_office := old_rec.source_office;
1057
1058 end if;
1059
1060 end if;
1061 if(l_found) then exit; end if;
1062 end loop;
1063 end if;
1064
1065 --mailstop
1066
1067 if(nvl(ok_source_mailstop, fnd_api.g_miss_char) <>
1068 nvl(old_rec.source_mailstop, fnd_api.g_miss_char))
1069 then
1070 i:= 0;
1071 l_found := FALSE;
1072 for i in 1..l_attr_rec.COUNT
1073 loop
1074 if(l_attr_rec(i).attribute_name = 'SOURCE_MAILSTOP')
1075 then
1076 l_found := TRUE;
1077 if(l_attr_rec(i).attribute_access_level = 'U')
1078 then
1079 l_u := 1;
1080 elsif(l_attr_rec(i).attribute_access_level = 'UWN')
1081 then
1082 l_uwn := 1;
1083 elsif(l_attr_rec(i).attribute_access_level = 'UWA')
1084 then
1085 l_uwa := 1;
1086 aprvl_source_mailstop := ok_source_mailstop;
1087 -- not setting ok_source_mailstop as G_MISS as HR API
1088 -- does not understand G_MISS and inserts G_MISS into
1089 -- the database which causes form crash/junk value
1090 -- bug # 3114608
1091 ok_source_mailstop := old_rec.source_mailstop;
1092 end if;
1093
1094 end if;
1095 if(l_found) then exit; end if;
1096 end loop;
1097 end if;
1098
1099 --location
1100
1101 if(nvl(ok_source_location, fnd_api.g_miss_char) <>
1102 nvl(old_rec.source_location, fnd_api.g_miss_char))
1103 then
1104 i:= 0;
1105 l_found := FALSE;
1106 for i in 1..l_attr_rec.COUNT
1107 loop
1108 if(l_attr_rec(i).attribute_name = 'SOURCE_LOCATION')
1109 then
1110 l_found := TRUE;
1111 if(l_attr_rec(i).attribute_access_level = 'U')
1112 then
1113 l_u := 1;
1114 elsif(l_attr_rec(i).attribute_access_level = 'UWN')
1115 then
1116 l_uwn := 1;
1117 elsif(l_attr_rec(i).attribute_access_level = 'UWA')
1118 then
1119 l_uwa := 1;
1120 aprvl_source_location := ok_source_location;
1121 -- not setting ok_source_location as G_MISS as HR API
1122 -- does not understand G_MISS and inserts G_MISS into
1123 -- the database which causes form crash/junk value
1124 -- bug # 3114608
1125 ok_source_location := old_rec.source_location;
1126 end if;
1127
1128 end if;
1129 if(l_found) then exit; end if;
1130 end loop;
1131 end if;
1132
1133
1134 --phone
1135 if(nvl(ok_source_phone, fnd_api.g_miss_char) <>
1136 nvl(old_rec.source_phone, fnd_api.g_miss_char))
1137 then
1138 i:= 0;
1139 l_found := FALSE;
1140 for i in 1..l_attr_rec.COUNT
1141 loop
1142 if(l_attr_rec(i).attribute_name = 'SOURCE_PHONE')
1143 then
1144 l_found := TRUE;
1145 if(l_attr_rec(i).attribute_access_level = 'U')
1146 then
1147 l_u := 1;
1148 elsif(l_attr_rec(i).attribute_access_level = 'UWN')
1149 then
1150 l_uwn := 1;
1151 elsif(l_attr_rec(i).attribute_access_level = 'UWA')
1152 then
1153 l_uwa := 1;
1154 aprvl_source_phone := ok_source_phone;
1155 -- not setting ok_source_phone as G_MISS as HR API
1156 -- does not understand G_MISS and inserts G_MISS into
1157 -- the database which causes form crash/junk value
1158 -- bug # 3114608
1159 ok_source_phone := old_rec.source_phone;
1160 end if;
1161
1162 end if;
1163 if(l_found) then exit; end if;
1164 end loop;
1165 end if;
1166
1167 --mobile phone
1168 if(nvl(ok_source_mobile_phone, fnd_api.g_miss_char) <>
1169 nvl(old_rec.source_mobile_phone, fnd_api.g_miss_char))
1170 then
1171 i:= 0;
1172 l_found := FALSE;
1173 for i in 1..l_attr_rec.COUNT
1174 loop
1175 if(l_attr_rec(i).attribute_name = 'SOURCE_MOBILE_PHONE')
1176 then
1177 l_found := TRUE;
1178 if(l_attr_rec(i).attribute_access_level = 'U')
1179 then
1180 l_u := 1;
1181 elsif(l_attr_rec(i).attribute_access_level = 'UWN')
1182 then
1183 l_uwn := 1;
1184 elsif(l_attr_rec(i).attribute_access_level = 'UWA')
1185 then
1186 l_uwa := 1;
1187 aprvl_source_mobile_phone := ok_source_mobile_phone;
1188 -- not setting ok_source_mobile_phone as G_MISS as HR API
1189 -- does not understand G_MISS and inserts G_MISS into
1190 -- the database which causes form crash/junk value
1191 -- bug # 3114608
1192 ok_source_mobile_phone := old_rec.source_mobile_phone;
1193 end if;
1194
1195 end if;
1196 if(l_found) then exit; end if;
1197 end loop;
1198 end if;
1199
1200 --pager
1201 if(nvl(ok_source_pager, fnd_api.g_miss_char) <>
1202 nvl(old_rec.source_pager, fnd_api.g_miss_char))
1203 then
1204 i:= 0;
1205 l_found := FALSE;
1206 for i in 1..l_attr_rec.COUNT
1207 loop
1208 if(l_attr_rec(i).attribute_name = 'SOURCE_PAGER')
1209 then
1210 l_found := TRUE;
1211 if(l_attr_rec(i).attribute_access_level = 'U')
1212 then
1213 l_u := 1;
1214 elsif(l_attr_rec(i).attribute_access_level = 'UWN')
1215 then
1216 l_uwn := 1;
1217 elsif(l_attr_rec(i).attribute_access_level = 'UWA')
1218 then
1219 l_uwa := 1;
1220 aprvl_source_pager := ok_source_pager;
1221 -- not setting ok_source_pager as G_MISS as HR API
1222 -- does not understand G_MISS and inserts G_MISS into
1223 -- the database which causes form crash/junk value
1224 -- bug # 3114608
1225 ok_source_pager := old_rec.source_pager;
1226 end if;
1227
1228 end if;
1229 if(l_found) then exit; end if;
1230 end loop;
1231 end if;
1232
1233 --email
1234 if(nvl(ok_source_email, fnd_api.g_miss_char) <>
1235 nvl(old_rec.source_email, fnd_api.g_miss_char))
1236 then
1237 i:= 0;
1238 l_found := FALSE;
1239 for i in 1..l_attr_rec.COUNT
1240 loop
1241 if(l_attr_rec(i).attribute_name = 'SOURCE_EMAIL')
1242 then
1243 l_found := TRUE;
1244 if(l_attr_rec(i).attribute_access_level = 'U')
1245 then
1246 l_u := 1;
1247 elsif(l_attr_rec(i).attribute_access_level = 'UWN')
1248 then
1249 l_uwn := 1;
1250 elsif(l_attr_rec(i).attribute_access_level = 'UWA')
1251 then
1252 l_uwa := 1;
1253 aprvl_source_email := ok_source_email;
1254 -- not setting ok_source_email as G_MISS as HR API
1255 -- does not understand G_MISS and inserts G_MISS into
1256 -- the database which causes form crash/junk value
1257 -- bug # 3114608
1258 ok_source_email := old_rec.source_email;
1259 end if;
1260
1261 end if;
1262 if(l_found) then exit; end if;
1263 end loop;
1264 end if;
1265
1266 --time zone
1267 if(nvl(ok_time_zone, fnd_api.g_miss_num) <>
1268 nvl(old_rec.time_zone, fnd_api.g_miss_num))
1269 then
1270 i:= 0;
1271 l_found := FALSE;
1272 for i in 1..l_attr_rec.COUNT
1273 loop
1274 if(l_attr_rec(i).attribute_name = 'TIME_ZONE')
1275 then
1276 l_found := TRUE;
1277 if(l_attr_rec(i).attribute_access_level = 'U')
1278 then
1279 l_u := 1;
1280 elsif(l_attr_rec(i).attribute_access_level = 'UWN')
1281 then
1282 l_uwn := 1;
1283 elsif(l_attr_rec(i).attribute_access_level = 'UWA')
1284 then
1285 l_uwa := 1;
1286 aprvl_time_zone := ok_time_zone;
1287 ok_time_zone := fnd_api.g_miss_num;
1288 end if;
1289
1290 end if;
1291 if(l_found) then exit; end if;
1292 end loop;
1293 else
1294 ok_time_zone := fnd_api.g_miss_num;
1295 end if;
1296
1297 --Support site
1298 if(nvl(ok_support_site_id, fnd_api.g_miss_num) <>
1299 nvl(old_rec.support_site_id, fnd_api.g_miss_num))
1300 then
1301 i:= 0;
1302 l_found := FALSE;
1303 for i in 1..l_attr_rec.COUNT
1304 loop
1305 if(l_attr_rec(i).attribute_name = 'SUPPORT_SITE')
1306 then
1307 l_found := TRUE;
1308 if(l_attr_rec(i).attribute_access_level = 'U')
1309 then
1310 l_u := 1;
1311 elsif(l_attr_rec(i).attribute_access_level = 'UWN')
1312 then
1313 l_uwn := 1;
1314 elsif(l_attr_rec(i).attribute_access_level = 'UWA')
1315 then
1316 l_uwa := 1;
1317 aprvl_support_site_id := ok_support_site_id;
1318 ok_support_site_id := fnd_api.g_miss_num;
1319 end if;
1320
1321 end if;
1322 if(l_found) then exit; end if;
1323 end loop;
1324 else
1325 ok_support_site_id := fnd_api.g_miss_num;
1326 end if;
1327
1328 --primary language
1329 if(nvl(ok_primary_language, fnd_api.g_miss_char) <>
1330 nvl(old_rec.primary_language, fnd_api.g_miss_char))
1331 then
1332 i:= 0;
1333 l_found := FALSE;
1334 for i in 1..l_attr_rec.COUNT
1335 loop
1336 if(l_attr_rec(i).attribute_name = 'PRIMARY_LANGUAGE')
1337 then
1338 l_found := TRUE;
1339 if(l_attr_rec(i).attribute_access_level = 'U')
1340 then
1341 l_u := 1;
1342 elsif(l_attr_rec(i).attribute_access_level = 'UWN')
1343 then
1344 l_uwn := 1;
1345 elsif(l_attr_rec(i).attribute_access_level = 'UWA')
1346 then
1347 l_uwa := 1;
1348 aprvl_primary_language := ok_primary_language;
1349 ok_primary_language := fnd_api.g_miss_char;
1350 end if;
1351
1352 end if;
1353 if(l_found) then exit; end if;
1354 end loop;
1355 else
1356 ok_primary_language := fnd_api.g_miss_char;
1357 end if;
1358
1359 --secondary language
1360 if(nvl(ok_secondary_language, fnd_api.g_miss_char) <>
1361 nvl(old_rec.secondary_language, fnd_api.g_miss_char))
1362 then
1363 i:= 0;
1364 l_found := FALSE;
1365 for i in 1..l_attr_rec.COUNT
1366 loop
1367 if(l_attr_rec(i).attribute_name = 'SECONDARY_LANGUAGE')
1368 then
1369 l_found := TRUE;
1370 if(l_attr_rec(i).attribute_access_level = 'U')
1371 then
1372 l_u := 1;
1373 elsif(l_attr_rec(i).attribute_access_level = 'UWN')
1374 then
1375 l_uwn := 1;
1376 elsif(l_attr_rec(i).attribute_access_level = 'UWA')
1377 then
1378 l_uwa := 1;
1379 aprvl_secondary_language := ok_secondary_language;
1380 ok_secondary_language := fnd_api.g_miss_char;
1381 end if;
1382
1383 end if;
1384 if(l_found) then exit; end if;
1385 end loop;
1386 else
1387 ok_secondary_language := fnd_api.g_miss_char;
1388 end if;
1389
1390 --Rate
1391 if(nvl(ok_cost_per_hr, fnd_api.g_miss_num) <>
1392 nvl(old_rec.cost_per_hr, fnd_api.g_miss_num))
1393 then
1394 i:= 0;
1395 l_found := FALSE;
1396 for i in 1..l_attr_rec.COUNT
1397 loop
1398 if(l_attr_rec(i).attribute_name = 'COST_PER_HR')
1399 then
1400 l_found := TRUE;
1401 if(l_attr_rec(i).attribute_access_level = 'U')
1402 then
1403 l_u := 1;
1404 elsif(l_attr_rec(i).attribute_access_level = 'UWN')
1405 then
1406 l_uwn := 1;
1407 elsif(l_attr_rec(i).attribute_access_level = 'UWA')
1408 then
1409 l_uwa := 1;
1410 aprvl_cost_per_hr := ok_cost_per_hr;
1411 ok_cost_per_hr := fnd_api.g_miss_num;
1412 end if;
1413
1414 end if;
1415 if(l_found) then exit; end if;
1416 end loop;
1417 else
1418 ok_cost_per_hr := fnd_api.g_miss_num;
1419 end if;
1420
1421
1422 end if; -- end of old cou
1423 close old_cur;
1424 END IF; -- end of p_approved check
1425 if(p_approved <> 'Y')
1426 then
1427 open old_cur;
1428 fetch old_cur into old_rec;
1429 if (old_cur%found)
1430 then
1431 if p_user_id = fnd_api.g_miss_num
1432 then
1433 --fetch user name (use the user_id of resource_id from table)
1434 open user_cur(old_rec.user_id);
1435 fetch user_cur into l_user_name;
1436 close user_cur;
1437 else
1438 if p_user_id is not null then
1439 --fetch user name (use the user_id from API input parameter)
1440 open user_cur(p_user_id);
1441 fetch user_cur into l_user_name;
1442 close user_cur;
1443 end if;
1444 end if;
1445 end if;--end of old_cur check
1446 close old_cur;
1447 end if; --end of p_approved second check
1448
1449 --if called with P_APPROVED = NR then fetch all relevant details
1450 if(p_approved = 'NR')
1451 then
1452
1453 open mgr_res_cur(p_mgr_resource_id);
1454 fetch mgr_res_cur into mgr_res_rec;
1455 l_mgr_source_id := mgr_res_rec.source_id;
1456 l_mgr_source_name := mgr_res_rec.source_name;
1457 if(nvl(l_mgr_source_id, fnd_api.g_miss_num) = nvl(old_rec.source_mgr_id, fnd_api.g_miss_num))
1458 then
1459 l_supervisor_id := fnd_api.g_miss_num;
1460 else
1461 l_supervisor_id := l_mgr_source_id;
1462 end if;
1463
1464 close mgr_res_cur;
1465
1466 if(nvl(l_job_title, fnd_api.g_miss_char) = nvl(old_rec.source_job_title, fnd_api.g_miss_char))
1467 then
1468 l_job_id := fnd_api.g_miss_num;
1469 l_job_title := fnd_api.g_miss_char;
1470 -- added these checks for invalid job title validation
1471 elsif((l_job_id is null) and (l_job_title is not null))
1472 then
1473 l_job_id := fnd_api.g_miss_num;
1474 l_job_title := fnd_api.g_miss_char;
1475 elsif((l_job_id is not null) and (l_job_title is null))
1476 then
1477 l_job_id := null;
1478 end if;
1479
1480 if(nvl(ok_address_id, fnd_api.g_miss_num) =
1481 nvl(old_rec.address_id, fnd_api.g_miss_num))
1482 then
1483 ok_address_id := fnd_api.g_miss_num;
1484 end if;
1485 else
1486 l_job_id := fnd_api.g_miss_num;
1487 l_supervisor_id := fnd_api.g_miss_num;
1488 l_job_title := fnd_api.g_miss_char;
1489
1490 if(nvl(ok_address_id, fnd_api.g_miss_num) =
1491 nvl(old_rec.address_id, fnd_api.g_miss_num))
1492 then
1493 ok_address_id := fnd_api.g_miss_num;
1494 end if;
1495 end if;
1496
1497 if(l_uwn=1)
1498 then
1499
1500 jtf_rs_resource_wf_pub.start_update_resource_wf
1501 (P_API_VERSION => 1.0,
1502 P_COMMIT => p_commit,
1503 P_RESOURCE_ID => p_resource_id,
1504 P_CATEGORY => 'EMPLOYEE',
1505 P_RESOURCE_NUMBER => null,
1506 P_RESOURCE_NAME => ok_resource_name,
1507 P_ADDRESS_ID => ok_address_id,
1508 P_SOURCE_EMAIL => ok_source_email,
1509 P_SOURCE_PHONE => ok_source_phone,
1510 P_SOURCE_OFFICE => ok_source_office,
1511 P_SOURCE_LOCATION => ok_source_location,
1512 P_SOURCE_MAILSTOP => ok_source_mailstop,
1513 P_TIME_ZONE => ok_time_zone,
1514 P_SUPPORT_SITE_ID => ok_support_site_id,
1515 P_PRIMARY_LANGUAGE => ok_primary_language,
1516 P_SECONDARY_LANGUAGE => ok_secondary_language,
1517 P_COST_PER_HR => ok_cost_per_hr,
1518 P_SOURCE_MOBILE_PHONE => ok_source_mobile_phone,
1519 P_SOURCE_PAGER => ok_source_pager,
1520 P_ATTRIBUTE_ACCESS_LEVEL => 'UWN',
1521 P_OBJECT_VERSION_NUMBER => p_object_version_number,
1522 X_RETURN_STATUS => x_return_status,
1523 X_MSG_COUNT => x_msg_count,
1524 X_MSG_DATA => x_msg_data
1525 );
1526
1527 elsif((l_u = 1) OR (p_approved IN ('A', 'T', 'AE', 'TE') ))
1528 then
1529
1530 --call the hr api's
1531 If (l_supervisor_id <> fnd_api.g_miss_num)
1532 then
1533 -- call the api for per_assigments_f update
1534 open assg_cur(old_rec.source_id);
1535 fetch assg_cur into assg_rec;
1536 if(assg_cur%found)
1537 then
1538 l_object_version_number_assg := assg_rec.object_version_number;
1539 l_special_ceiling_step_id := assg_rec.special_ceiling_step_id;
1540 if(trunc(assg_rec.last_update_date)= trunc(sysdate))
1541 then
1542 l_datetrack_update_mode := 'CORRECTION';
1543 else
1544 l_datetrack_update_mode := 'UPDATE';
1545 end if;
1546 hr_assignment_api.update_emp_asg
1547 (p_effective_date => trunc(sysdate)
1548 ,p_datetrack_update_mode => l_datetrack_update_mode --in ??
1549 ,p_assignment_id => assg_rec.assignment_id
1550 ,p_object_version_number => l_object_version_number_assg
1551 ,p_supervisor_id => l_mgr_source_id
1552 ,p_concatenated_segments => l_concatenated_segments
1553 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
1554 ,p_comment_id => l_comment_id
1555 ,p_effective_start_date => l_effective_start_date
1556 ,p_effective_end_date => l_effective_start_date
1557 ,p_no_managers_warning => l_no_managers_warning
1558 ,p_other_manager_warning => l_other_manager_warning );
1559
1560 end if; --end of assg cur check
1561 close assg_cur;
1562 end if;
1563
1564 if((ok_address_id <> fnd_api.g_miss_num)
1565 OR (ok_address_id is null)
1566 OR (l_job_id <> fnd_api.g_miss_num)
1567 OR (l_job_id is null))
1568 then
1569 --update location and job
1570 open assg_cur(old_rec.source_id);
1571 fetch assg_cur into assg_rec;
1572 if(assg_cur%found)
1573 then
1574 if(l_job_id = fnd_api.g_miss_num)
1575 then
1576 l_job_id := assg_rec.job_id;
1577 end if;
1578 if(ok_address_id = fnd_api.g_miss_num)
1579 then
1580 ok_address_id := assg_rec.location_id;
1581 end if;
1582 l_object_version_number_assg := assg_rec.object_version_number;
1583 l_special_ceiling_step_id := assg_rec.special_ceiling_step_id;
1584 if(trunc(assg_rec.last_update_date)= trunc(sysdate))
1585 then
1586 l_datetrack_update_mode := 'CORRECTION';
1587 else
1588 l_datetrack_update_mode := 'UPDATE';
1589 end if;
1590 hr_assignment_api.update_emp_asg_criteria
1591 (p_effective_date => trunc(sysdate)
1592 ,p_datetrack_update_mode => l_datetrack_update_mode --in ??
1593 ,p_assignment_id => assg_rec.assignment_id
1594 ,p_object_version_number => l_object_version_number_assg
1595 ,p_location_id => ok_address_id
1596 ,p_job_id => l_job_id
1597 ,p_special_ceiling_step_id => l_special_ceiling_step_id
1598 ,p_group_name => l_group_name
1599 ,p_effective_start_date => l_effective_start_date
1600 ,p_effective_end_date => l_effective_start_date
1601 ,p_people_group_id => l_people_group_id
1602 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
1603 ,p_other_manager_warning => l_other_manager_warning
1604 ,p_spp_delete_warning => l_spp_delete_warning
1605 ,p_entries_changed_warning => l_entries_changed_earning
1606 ,p_tax_district_changed_warning => l_tax_district_changed_earning
1607 );
1608
1609 end if;
1610 close assg_cur;
1611 end if;
1612
1613 if((nvl(ok_source_mailstop, fnd_api.g_miss_char) <>
1614 nvl(old_rec.source_mailstop, fnd_api.g_miss_char) )
1615 OR (nvl(ok_source_location, fnd_api.g_miss_char) <>
1616 nvl(old_rec.source_location, fnd_api.g_miss_char) )
1617 OR (nvl(ok_source_email, fnd_api.g_miss_char) <>
1618 nvl(old_rec.source_email, fnd_api.g_miss_char) )
1619 OR (nvl(ok_source_office, fnd_api.g_miss_char) <>
1620 nvl(old_rec.source_office, fnd_api.g_miss_char)))
1621 then
1622 --update per_all_people_f
1623 open per_cur(old_rec.source_id);
1624 fetch per_cur into per_rec;
1625 if(per_cur%found)
1626 then
1627 l_object_version_number_per := per_rec.object_version_number;
1628 l_employee_number := per_rec.employee_number;
1629 if(trunc(per_rec.last_update_date)= trunc(sysdate))
1630 then
1631 l_datetrack_update_mode := 'CORRECTION';
1632 else
1633 l_datetrack_update_mode := 'UPDATE';
1634 end if;
1635
1636 hr_person_api.update_person
1637 (p_effective_date => trunc(sysdate)
1638 ,p_datetrack_update_mode => l_datetrack_update_mode
1639 ,p_person_id => per_rec.person_id
1640 ,p_object_version_number => l_object_version_number_per
1641 ,p_employee_number => l_employee_number
1642 ,p_internal_location => ok_source_location
1643 ,p_mailstop => ok_source_mailstop
1644 ,p_office_number => ok_source_office
1645 ,p_email_address => ok_source_email
1646 ,p_effective_start_date => l_effective_start_date
1647 ,p_effective_end_date => l_effective_end_date
1648 ,p_full_name => l_full_name
1649 ,p_comment_id => l_comment_id
1650 ,p_name_combination_warning => l_name_combination_warning
1651 ,p_assign_payroll_warning => l_assign_payroll_warning
1652 ,p_orig_hire_warning => l_orig_hire_warning);
1653
1654 end if; -- end of per cursor
1655 close per_cur;
1656
1657 end if; -- end of source col change check
1658
1659
1660 --call phone api
1661 if(nvl(ok_source_phone, fnd_api.g_miss_char) <>
1662 nvl(old_rec.source_phone, fnd_api.g_miss_char))
1663 then
1664 --update per_phones
1665 open phone_cur(old_rec.source_id);
1666 fetch phone_cur into phone_rec;
1667 if(phone_cur%found)
1668 then
1669 l_object_version_number_phone := phone_rec.object_version_number;
1670 if(ok_source_phone is not null)
1671 then
1672 hr_phone_api.update_phone(p_phone_id => phone_rec.phone_id,
1673 p_object_version_number => l_object_version_number_phone,
1674 p_effective_date => sysdate,
1675 p_phone_number => ok_source_phone);
1676 else
1677 hr_phone_api.delete_phone
1678 (p_phone_id => phone_rec.phone_id,
1679 p_object_version_number => l_object_version_number_phone
1680 );
1681
1682
1683 end if;
1684 else
1685 hr_phone_api.create_phone
1686 (p_date_from => sysdate,
1687 p_phone_type => 'W1',
1688 p_phone_number => ok_source_phone,
1689 p_parent_id => old_rec.source_id,
1690 p_parent_table => 'PER_ALL_PEOPLE_F',
1691 p_effective_date => sysdate,
1692 p_object_version_number => l_object_version_number_phone,
1693 p_phone_id => l_phone_id);
1694
1695 end if;
1696 close phone_cur;
1697 end if; -- end of source_phone change check
1698
1699
1700 --call mobile phone api
1701 if(nvl(ok_source_mobile_phone, fnd_api.g_miss_char) <>
1702 nvl(old_rec.source_mobile_phone, fnd_api.g_miss_char))
1703 then
1704 --update per_phones
1705 open mobile_phone_cur(old_rec.source_id);
1706 fetch mobile_phone_cur into mobile_phone_rec;
1707 if(mobile_phone_cur%found)
1708 then
1709 l_object_ver_num_mobile_ph := mobile_phone_rec.object_version_number;
1710 if(ok_source_mobile_phone is not null)
1711 then
1712 hr_phone_api.update_phone(p_phone_id => mobile_phone_rec.phone_id,
1713 p_object_version_number => l_object_ver_num_mobile_ph,
1714 p_effective_date => sysdate,
1715 p_phone_number => ok_source_mobile_phone);
1716 else
1717 hr_phone_api.delete_phone
1718 (p_phone_id => mobile_phone_rec.phone_id,
1719 p_object_version_number => l_object_ver_num_mobile_ph
1720 );
1721
1722
1723 end if;
1724 else
1725 hr_phone_api.create_phone
1726 (p_date_from => sysdate,
1727 p_phone_type => 'M',
1728 p_phone_number => ok_source_mobile_phone,
1729 p_parent_id => old_rec.source_id,
1730 p_parent_table => 'PER_ALL_PEOPLE_F',
1731 p_effective_date => sysdate,
1732 p_object_version_number => l_object_ver_num_mobile_ph,
1733 p_phone_id => l_mobile_phone_id);
1734
1735 end if;
1736 close mobile_phone_cur;
1737 end if; -- end of source_mobile_phone change check
1738
1739 --call pager api
1740 if(nvl(ok_source_pager, fnd_api.g_miss_char) <>
1741 nvl(old_rec.source_pager, fnd_api.g_miss_char))
1742 then
1743 --update per_phones
1744 open pager_cur(old_rec.source_id);
1745 fetch pager_cur into pager_rec;
1746 if(pager_cur%found)
1747 then
1748 l_object_version_number_pager := pager_rec.object_version_number;
1749 if(ok_source_pager is not null)
1750 then
1751 hr_phone_api.update_phone(p_phone_id => pager_rec.phone_id,
1752 p_object_version_number => l_object_version_number_pager,
1753 p_effective_date => sysdate,
1754 p_phone_number => ok_source_pager);
1755 else
1756 hr_phone_api.delete_phone
1757 (p_phone_id => pager_rec.phone_id,
1758 p_object_version_number => l_object_version_number_pager
1759 );
1760
1761
1762 end if;
1763 else
1764 hr_phone_api.create_phone
1765 (p_date_from => sysdate,
1766 p_phone_type => 'P',
1767 p_phone_number => ok_source_pager,
1768 p_parent_id => old_rec.source_id,
1769 p_parent_table => 'PER_ALL_PEOPLE_F',
1770 p_effective_date => sysdate,
1771 p_object_version_number => l_object_version_number_pager,
1772 p_phone_id => l_pager_id);
1773
1774 end if;
1775 close pager_cur;
1776 end if; -- end of source_pager change check
1777
1778
1779 --end of hr api calls
1780
1781 --call update resource api
1782
1783 if((ok_address_id <> fnd_api.g_miss_num) OR (ok_address_id is NULL))
1784 then
1785 if (ok_address_id is NULL) then
1786 address_rec := NULL;
1787 else
1788 open address_cur(ok_address_id);
1789 fetch address_cur into address_rec;
1790 close address_cur;
1791 end if;
1792 else
1793 open address_cur(old_rec.address_id);
1794 fetch address_cur into address_rec;
1795 close address_cur;
1796 ok_address_id := old_rec.address_id;
1797 end if;
1798
1799
1800 --call the cascade update api if p_end_date_active < existing end date
1801 IF(nvl(to_date(to_char(p_end_date_active ,'DD-MM-RRRR'), 'DD-MM-RRRR'), l_fnd_date)
1802 < nvl(to_date(to_char(old_rec.end_date_active, 'DD-MM-RRRR'), 'DD-MM-RRRR'), l_fnd_date))
1803 THEN
1804 jtf_rs_resource_utl_pub.end_date_employee
1805 (p_api_version => 1.0
1806 , p_resource_id => p_resource_id
1807 , p_end_date_active => p_end_date_active
1808 , x_object_ver_number => l_object_version_number
1809 , x_return_status => x_return_status
1810 , x_msg_count => x_msg_count
1811 , x_msg_data => x_msg_data);
1812
1813
1814 if(x_return_status <> fnd_api.g_ret_sts_success)
1815 then
1816 raise fnd_api.g_exc_error;
1817 end if;
1818
1819 END IF; -- edn of date check
1820
1821 jtf_rs_resource_pub.update_resource
1822 (p_api_version => 1.0
1823 , p_init_msg_list => fnd_api.g_false
1824 , p_commit => p_commit
1825 , p_resource_id => p_resource_id
1826 , p_resource_number => null
1827 , p_resource_name => ok_resource_name
1828 , p_SOURCE_NAME => ok_source_name
1829 , p_SOURCE_EMAIL => ok_source_email
1830 , p_SOURCE_PHONE => ok_source_phone
1831 , p_address_id => ok_address_id
1832 , p_source_mailstop => ok_source_mailstop
1833 , p_source_office => ok_source_office
1834 , p_source_location => ok_source_location
1835 , p_source_address1 => address_rec.address_line_1
1836 , p_source_address2 => address_rec.address_line_2
1837 , p_source_address3 => address_rec.address_line_3
1838 , p_source_city => address_rec.town_or_city
1839 , p_source_country => address_rec.country
1840 , p_source_postal_code => address_rec.postal_code
1841 , p_object_version_num => l_object_version_number
1842 , p_source_job_title => l_job_title --p_source_job_title
1843 , p_end_date_active => p_end_date_active
1844 , p_source_mgr_id => l_mgr_source_id
1845 , p_source_mgr_name => l_mgr_source_name
1846 , p_user_id => p_user_id
1847 , p_user_name => l_user_name
1848 , p_time_zone => ok_time_zone
1849 , p_cost_per_hr => ok_cost_per_hr
1850 , p_primary_language => ok_primary_language
1851 , p_secondary_language => ok_secondary_language
1852 , p_support_site_id => ok_support_site_id
1853 , p_source_mobile_phone => ok_source_mobile_phone
1854 , p_source_pager => ok_source_pager
1855 , x_return_status => x_return_status
1856 , x_msg_count => x_msg_count
1857 , x_msg_data => x_msg_data);
1858
1859
1860 if(x_return_status = fnd_api.g_ret_sts_success)
1861 then
1862 if((p_org_id is not null)
1863 OR (p_org_id <> fnd_api.g_miss_num))
1864 then
1865 --update srp
1866 open salesrep_cur(p_resource_id, p_org_id);
1867 fetch salesrep_cur into salesrep_rec;
1868 if(salesrep_cur%found)
1869 then
1870 l_srp_object_version_number := salesrep_rec.object_version_number;
1871 jtf_rs_salesreps_pub.update_salesrep
1872 (P_API_VERSION => 1.0,
1873 P_SALESREP_ID => salesrep_rec.salesrep_id,
1874 P_SALES_CREDIT_TYPE_ID => p_sales_credit_type_id,
1875 P_NAME => ok_resource_name,
1876 P_ORG_ID => p_org_id,
1877 p_salesrep_number => p_salesrep_number,
1878 P_OBJECT_VERSION_NUMBER => l_srp_object_version_number,
1879 X_RETURN_STATUS => x_return_status,
1880 X_MSG_COUNT => x_msg_count,
1881 X_MSG_DATA => x_msg_data);
1882 end if;
1883 close salesrep_cur;
1884
1885 elsif(( p_sales_credit_type_id is not null)
1886 AND (p_sales_credit_type_id <> fnd_api.g_miss_num))
1887 then
1888 --create srp
1889 -- below call to MO_GLOBAL init is added for MOAC changes.
1890 -- If we didn't pass a value for org id in create_salesrep API,
1891 -- it will call the get_valid_org procedure and get the default operating unit.
1892 MO_GLOBAL.init('JTF');
1893 jtf_rs_salesreps_pub.create_salesrep
1894 (P_API_VERSION => 1.0,
1895 P_RESOURCE_ID => p_resource_id,
1896 P_SALES_CREDIT_TYPE_ID => p_sales_credit_type_id,
1897 p_salesrep_number => p_salesrep_number,
1898 P_NAME => ok_resource_name,
1899 P_START_DATE_ACTIVE => trunc(old_rec.start_date_active),
1900 P_END_DATE_ACTIVE => trunc(p_end_date_active),
1901 X_RETURN_STATUS => x_return_status,
1902 X_MSG_COUNT => x_msg_count,
1903 X_MSG_DATA => x_msg_data,
1904 X_SALESREP_ID => l_salesrep_id
1905 );
1906
1907 end if;
1908 end if;
1909
1910
1911 end if;
1912
1913
1914 if(l_uwa=1)
1915 then
1916
1917 --call approval api
1918 jtf_rs_resource_wf_pub.start_update_resource_wf
1919 (P_API_VERSION => 1.0,
1920 P_COMMIT => p_commit,
1921 P_RESOURCE_ID => p_resource_id,
1922 P_CATEGORY => 'EMPLOYEE',
1923 P_RESOURCE_NUMBER => null,
1924 P_RESOURCE_NAME => aprvl_resource_name,
1925 P_ADDRESS_ID => aprvl_address_id,
1926 P_SOURCE_EMAIL => aprvl_source_email,
1927 P_SOURCE_PHONE => aprvl_source_phone,
1928 P_SOURCE_OFFICE => aprvl_source_office,
1929 P_SOURCE_LOCATION => aprvl_source_location,
1930 P_SOURCE_MAILSTOP => aprvl_source_mailstop,
1931 P_TIME_ZONE => aprvl_time_zone,
1932 P_SUPPORT_SITE_ID => aprvl_support_site_id,
1933 P_PRIMARY_LANGUAGE => aprvl_primary_language,
1934 P_SECONDARY_LANGUAGE => aprvl_secondary_language,
1935 P_COST_PER_HR => aprvl_cost_per_hr,
1936 P_SOURCE_MOBILE_PHONE => aprvl_source_mobile_phone,
1937 P_SOURCE_PAGER => aprvl_source_pager,
1938 P_ATTRIBUTE_ACCESS_LEVEL => 'UWA',
1939 P_OBJECT_VERSION_NUMBER => p_object_version_number,
1940 X_RETURN_STATUS => l_return_status,
1941 X_MSG_COUNT => x_msg_count,
1942 X_MSG_DATA => x_msg_data
1943 );
1944
1945 end if;
1946
1947 l_mgr_name := old_rec.source_mgr_name;
1948 if(x_return_status=fnd_api.g_ret_sts_success)
1949 then
1950 -- bug # 2185983 success message was showed
1951 -- two times if atleast one field requires
1952 -- notification/apporval and atleast one field has
1953 -- full update
1954 if(l_u=1 and l_uwa=0 and l_uwn=0)
1955 then
1956 fnd_message.set_name ('JTF', 'JTF_RS_UPDATED');
1957 FND_MSG_PUB.add;
1958 else
1959 if(p_approved = 'A')
1960 then
1961 fnd_message.set_name ('JTF', 'JTF_RS_WF_APPROVAL');
1962 fnd_message.set_token('P_MGR',l_mgr_name);
1963 FND_MSG_PUB.add;
1964 elsif(p_approved= 'T')
1965 then
1966 fnd_message.set_name ('JTF', 'JTF_RS_WF_NOTIF');
1967 fnd_message.set_token('P_MGR',l_mgr_name);
1968 FND_MSG_PUB.add;
1969 fnd_message.set_name ('JTF', 'JTF_RS_UPDATED');
1970 FND_MSG_PUB.add;
1971 elsif((p_approved='TE') OR (p_approved = 'AE') OR
1972 (l_u=1 AND l_uwa=1 AND l_uwn=0))
1973 then
1974 fnd_message.set_name ('JTF', 'JTF_RS_UPDATED');
1975 FND_MSG_PUB.add;
1976 end if;
1977 end if;
1978
1979 if((l_u = 0) AND (l_uwn = 0) AND (l_uwa = 0) AND (p_approved = 'N'))
1980 then
1981 fnd_message.set_name ('JTF', 'JTF_RS_NO_CHANGES');
1982 FND_MSG_PUB.add;
1983 elsif((l_uwa= 1) AND (old_rec.source_mgr_id is not null))
1984 then
1985 open mgr_user(old_rec.source_mgr_id);
1986 fetch mgr_user into l_mgr_user_id ;
1987 if(mgr_user%found)
1988 then
1989 fnd_message.set_name ('JTF', 'JTF_RS_WF_APPROVAL');
1990 fnd_message.set_token('P_MGR',l_mgr_name);
1991 FND_MSG_PUB.add;
1992 end if;
1993 close mgr_user;
1994 end if;
1995
1996 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1997 else
1998 ROLLBACK TO sp_save;
1999 end if;
2000
2001 EXCEPTION
2002 WHEN hr_api_error
2003 THEN
2004 ROLLBACK TO sp_save;
2005 x_return_status := fnd_api.g_ret_sts_error;
2006 fnd_message.set_name('JTF','JTF_RS_HR_API_ERROR');
2007 fnd_message.set_token('P_SQLERRM',substr(SQLERRM,11));
2008 FND_MSG_PUB.add;
2009 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2010 WHEN fnd_api.g_exc_unexpected_error
2011 THEN
2012 ROLLBACK TO sp_save;
2013 x_return_status := fnd_api.g_ret_sts_error;
2014 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2015 WHEN fnd_api.g_exc_error
2016 THEN
2017 ROLLBACK TO sp_save;
2018 x_return_status := fnd_api.g_ret_sts_error;
2019 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2020
2021 WHEN OTHERS
2022 THEN
2023 ROLLBACK TO sp_save;
2024 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
2025 fnd_message.set_token('P_SQLCODE',SQLCODE);
2026 fnd_message.set_token('P_SQLERRM',SQLERRM);
2027 fnd_message.set_token('P_API_NAME',l_api_name);
2028 FND_MSG_PUB.add;
2029 x_return_status := fnd_api.g_ret_sts_unexp_error;
2030 FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2031 END update_resource;
2032
2033
2034
2035
2036 END jtf_rs_res_sswa_pub;