[Home] [Help]
PACKAGE BODY: APPS.PA_HR_UPDATE_PA_ENTITIES
Source
1 PACKAGE BODY pa_hr_update_pa_entities AS
2 /* $Header: PAHRUPDB.pls 120.6.12010000.2 2008/11/13 03:46:09 amehrotr ship $ */
3
4 -- Global variable for debugging. Bug 4352236.
5 G_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
6
7 --------------------------------------------------------------------------------------------------------------
8 -- This procedure prints the text which is being passed as the input
9 -- Input parameters
10 -- Parameters Type Required Description
11 -- p_log_msg VARCHAR2 YES It stores text which you want to print on screen
12 -- Out parameters
13 ----------------------------------------------------------------------------------------------------------------
14 PROCEDURE log_message (p_log_msg IN VARCHAR2)
15 IS
16 -- P_DEBUG_MODE varchar2(1); -- Bug 4352236 - use global variable G_DEBUG_MODE
17 BEGIN
18 -- P_DEBUG_MODE := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
19 IF (G_DEBUG_MODE ='Y') THEN
20 pa_debug.write('PA_HR_UPDATE_PA_ENTITIES', 'log: ' || p_log_msg, 3);
21 END IF;
22 END log_message;
23
24
25 -- -------------------------------------------------------------------------------------
26 -- Global Constants
27 -- -------------------------------------------------------------------------------------
28 -- G_USER_ID CONSTANT NUMBER := FND_GLOBAL.user_id;
29 -- G_LOGIN_ID CONSTANT NUMBER := FND_GLOBAL.login_id;
30
31 PROCEDURE update_project_entities ( p_calling_mode in varchar2,
32 p_table_name in varchar2,
33 p_person_id in number DEFAULT NULL,
34 p_start_date_old in date DEFAULT NULL,
35 p_start_date_new in date DEFAULT NULL,
36 p_end_date_old in date DEFAULT NULL,
37 p_end_date_new in date DEFAULT NULL,
38 p_org_id_old in number DEFAULT NULL,
39 p_org_id_new in number DEFAULT NULL,
40 p_job_id_old in number DEFAULT NULL,
41 p_job_id_new in number DEFAULT NULL,
42 p_from_job_group_id in number DEFAULT NULL,
43 p_to_job_group_id in number DEFAULT NULL,
44 p_job_level_old in number DEFAULT NULL,
45 p_job_level_new in number DEFAULT NULL,
46 p_supervisor_old in number DEFAULT NULL,
47 p_supervisor_new in number DEFAULT NULL,
48 p_primary_flag_old in varchar2 DEFAULT NULL,
49 p_primary_flag_new in varchar2 DEFAULT NULL,
50 p_org_info1_old in varchar2 DEFAULT NULL,
51 p_org_info1_new in varchar2 DEFAULT NULL,
52 p_jei_information2_old in varchar2 DEFAULT NULL,
53 p_jei_information2_new in varchar2 DEFAULT NULL,
54 p_jei_information3_old in varchar2 DEFAULT NULL,
55 p_jei_information3_new in varchar2 DEFAULT NULL,
56 p_jei_information4_old in varchar2 DEFAULT NULL,
57 p_jei_information4_new in varchar2 DEFAULT NULL,
58 p_jei_information6_old in varchar2 DEFAULT NULL,
59 p_jei_information6_new in varchar2 DEFAULT NULL,
60 p_grade_id_old in number DEFAULT NULL,
61 p_grade_id_new in number DEFAULT NULL,
62 p_full_name_old in varchar2 DEFAULT NULL,
63 p_full_name_new in varchar2 DEFAULT NULL,
64 p_country_old in varchar2 DEFAULT NULL,
65 p_country_new in varchar2 DEFAULT NULL,
66 p_city_old in varchar2 DEFAULT NULL,
67 p_city_new in varchar2 DEFAULT NULL,
68 p_region2_old in varchar2 DEFAULT NULL,
69 p_region2_new in varchar2 DEFAULT NULL,
70 p_org_struct_element_id in number DEFAULT NULL,
71 p_organization_id_parent in number DEFAULT NULL,
72 p_organization_id_child in number DEFAULT NULL,
73 p_org_structure_version_id in number DEFAULT NULL,
74 p_inactive_date_old in date DEFAULT NULL,
75 p_inactive_date_new in date DEFAULT NULL,
76 p_from_job_id_old in number DEFAULT NULL,
77 p_from_job_id_new in number DEFAULT NULL,
78 p_to_job_id_old in number DEFAULT NULL,
79 p_to_job_id_new in number DEFAULT NULL,
80 p_org_info_context in varchar2 DEFAULT NULL,
81 x_return_status out NOCOPY varchar2, --File.Sql.39 bug 4440895
82 x_error_message_code out NOCOPY varchar2) --File.Sql.39 bug 4440895
83 is
84 --
85 --
86 --
87
88 ItemType varchar2(30) := 'PAXWFHRU'; -- Identifies the workflow that will be executed.
89 ItemKey number ;
90 l_process VARCHAR2(30);
91
92 l_org_struct_element_id NUMBER;
93 l_organization_id_child NUMBER;
94 l_organization_id_parent NUMBER;
95 l_org_structure_version_id NUMBER;
96
97 l_msg_count NUMBER;
98 l_msg_data VARCHAR(2000);
99 l_return_status VARCHAR2(1);
100 -- l_api_version_number NUMBER := 1.0;
101 l_data VARCHAR2(2000);
102 l_msg_index_out NUMBER;
103 l_save_thresh NUMBER ;
104
105 l_err_code NUMBER := 0;
106 l_err_stage VARCHAR2(2000);
107 l_err_stack VARCHAR2(2000);
108 --
109 --
110 begin
111 --
112 -- bug 2840328:PA HR UPDATE TRIGGERS SHOULD NOT CREATE WF PROCESS IF NO RELEVANT CHANGES
113 -- We should not start workflow when no attribute of interest to PA has not been
114 -- changed on per_all_people_f, PER_ALL_ASSIGNMENTS_F.etc.
115 IF ( nvl(p_start_date_old,FND_API.G_MISS_DATE) = nvl(p_start_date_new,FND_API.G_MISS_DATE) AND
116 nvl(p_end_date_old,FND_API.G_MISS_DATE) = nvl(p_end_date_new,FND_API.G_MISS_DATE) AND
117 nvl(p_org_id_old,FND_API.G_MISS_NUM) = nvl(p_org_id_new,FND_API.G_MISS_NUM) AND
118 nvl(p_job_id_old,FND_API.G_MISS_NUM) = nvl(p_job_id_new,FND_API.G_MISS_NUM) AND
119 nvl(p_job_level_old,FND_API.G_MISS_NUM) = nvl(p_job_level_new,FND_API.G_MISS_NUM) AND
120 nvl(p_supervisor_old,FND_API.G_MISS_NUM) = nvl(p_supervisor_new,FND_API.G_MISS_NUM) AND
121 nvl(p_primary_flag_old,FND_API.G_MISS_CHAR) = nvl(p_primary_flag_new,FND_API.G_MISS_CHAR) AND
122 nvl(p_org_info1_old,FND_API.G_MISS_CHAR) = nvl(p_org_info1_new,FND_API.G_MISS_CHAR) AND
123 nvl(p_jei_information2_old,FND_API.G_MISS_CHAR) = nvl(p_jei_information2_new,FND_API.G_MISS_CHAR) AND
124 nvl(p_jei_information3_old,FND_API.G_MISS_CHAR) = nvl(p_jei_information3_new,FND_API.G_MISS_CHAR) AND
125 nvl(p_jei_information4_old,FND_API.G_MISS_CHAR) = nvl(p_jei_information4_new,FND_API.G_MISS_CHAR) AND
126 nvl(p_jei_information6_old,FND_API.G_MISS_CHAR) = nvl(p_jei_information6_new,FND_API.G_MISS_CHAR) AND
127 nvl(p_grade_id_old,FND_API.G_MISS_NUM) = nvl(p_grade_id_new,FND_API.G_MISS_NUM) AND
128 nvl(p_full_name_old,FND_API.G_MISS_CHAR) = nvl(p_full_name_new,FND_API.G_MISS_CHAR) AND
129 nvl(p_country_old,FND_API.G_MISS_CHAR) = nvl(p_country_new,FND_API.G_MISS_CHAR) AND
130 nvl(p_city_old,FND_API.G_MISS_CHAR) = nvl(p_city_new,FND_API.G_MISS_CHAR) AND
131 nvl(p_region2_old,FND_API.G_MISS_CHAR) = nvl(p_region2_new,FND_API.G_MISS_CHAR) AND
132 nvl(p_inactive_date_old,FND_API.G_MISS_DATE) = nvl(p_inactive_date_new,FND_API.G_MISS_DATE) AND
133 nvl(p_from_job_id_old,FND_API.G_MISS_NUM) = nvl(p_from_job_id_new,FND_API.G_MISS_NUM) AND
134 nvl(p_to_job_id_old,FND_API.G_MISS_NUM) = nvl(p_to_job_id_new,FND_API.G_MISS_NUM) ) THEN
135 return;
136
137 -- When the trigger on PER_JOB_EXTRA_INFO is fired, we won't launch the WF in following cases
138 -- because we don't need to update pa_resources_denorm.
139 -- If 'Include in Utilization' Falg=N, we don't need to pull thos corresponding resources. So
140 -- there won't be any resources to update..
141 ELSIF p_table_name= 'PER_JOB_EXTRA_INFO' THEN
142 IF ( p_calling_mode='INSERT' AND (p_jei_information3_new='N' OR p_jei_information3_new IS NULL)) THEN
143 return;
144 END IF;
145 END IF;
146
147 --
148 -- Get a unique identifier for this specific workflow
149 --
150
151 SELECT pa_workflow_itemkey_s.nextval
152 INTO itemkey
153 FROM dual;
154 --
155 -- Since this workflow needs to be executed in the background we need
156 -- to change the threshold. So we save the current threshold which
157 -- will be used later on to change it back to the current threshold.
158 --
159
160 l_save_thresh := wf_engine.threshold ;
161
162
163 IF wf_engine.threshold < 0 THEN
164 wf_engine.threshold := l_save_thresh ;
165 END IF;
166
167
168 --
169 -- Set the threshold to bellow 0 so that the process will be created
170 -- in the background
171 --
172
173 wf_engine.threshold := -1 ;
174
175
176 IF p_table_name = 'PER_ALL_ASSIGNMENTS_F' THEN
177 l_process := 'PROCESS_ASSIGNMENT_CHANGES' ;
178
179 ELSIF p_table_name = 'PER_ORG_STRUCTURE_ELEMENTS' THEN
180
181 l_process := 'PROCESS_ORG_STRUCT_UPD';
182
183
184 ELSIF p_table_name = 'PER_JOB_EXTRA_INFO' THEN
185
186 l_process := 'PROCESS_JOB_BILL_UPD' ;
187
188 ELSIF p_table_name = 'PER_ALL_PEOPLE_F' THEN
189
190 l_process := 'PROCESS_FULL_NAME_UPD';
191
192 ELSIF p_table_name = 'HR_ORGANIZATION_INFORMATION' THEN
193
194 l_process := 'PROCESS_ORG_INFO_UPD' ;
195
196 ELSIF p_table_name = 'PER_VALID_GRADES' THEN
197
198 l_process := 'PROCESS_VALID_GRADE_UPD';
199
200 ELSIF p_table_name = 'PER_GRADES' THEN
201
202 l_process := 'PROCESS_GRADE_UPD' ;
203
204 ELSIF p_table_name = 'PER_ADDRESSES' THEN
205
206 l_process := 'PROCESS_ADDRESS_UPD' ;
207
208 ELSIF p_table_name = 'PA_ALL_ORGANIZATIONS' THEN
209
210 l_process := 'PROCESS_PA_ALL_ORG_UPD' ;
211
212 ELSIF p_table_name = 'PA_JOB_RELATIONSHIPS' THEN
213
214 l_process := 'PROCESS_JOB_REL_UPD' ;
215
216 END IF ;
217
218 --
219 -- Create the appropriate process
220 --
221
222 wf_engine.CreateProcess( ItemType => ItemType,
223 ItemKey => ItemKey,
224 process => l_process );
225
226 --
227 -- Initialize workflow item attributes with the parameter values
228 --
229
230 wf_engine.SetItemAttrText ( itemtype => itemtype,
231 itemkey => itemkey,
232 aname => 'PROJECT_RESOURCE_ADMINISTRATOR',
233 avalue => 'PASYSADMIN');
234
235 wf_engine.SetItemAttrText ( itemtype => itemtype,
236 itemkey => itemkey,
237 aname => 'CALLING_MODE',
238 avalue => p_calling_mode);
239
240 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
241 itemkey => itemkey,
242 aname => 'ORG_STRUCTURE_ELEMENT_ID',
243 avalue => p_org_struct_element_id);
244
245 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
246 itemkey => itemkey,
247 aname => 'ORGANIZATION_ID_PARENT',
248 avalue => p_organization_id_parent);
249
250 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
251 itemkey => itemkey,
252 aname => 'ORGANIZATION_ID_CHILD',
253 avalue => p_organization_id_child);
254
255 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
256 itemkey => itemkey,
257 aname => 'ORG_STRUCTURE_VERSION_ID',
258 avalue => p_org_structure_version_id);
259
260 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
261 itemkey => itemkey,
262 aname => 'PERSON_ID',
263 avalue => p_person_id);
264
265 wf_engine.SetItemAttrDate ( itemtype => itemtype,
266 itemkey => itemkey,
267 aname => 'START_DATE_OLD',
268 avalue => p_start_date_old);
269
270 wf_engine.SetItemAttrDate ( itemtype => itemtype,
271 itemkey => itemkey,
272 aname => 'START_DATE_NEW',
273 avalue => p_start_date_new);
274
275 wf_engine.SetItemAttrDate ( itemtype => itemtype,
276 itemkey => itemkey,
277 aname => 'END_DATE_OLD',
278 avalue => p_end_date_old);
279
280 wf_engine.SetItemAttrDate ( itemtype => itemtype,
281 itemkey => itemkey,
282 aname => 'END_DATE_NEW',
283 avalue => p_end_date_new);
284
285 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
286 itemkey => itemkey,
287 aname => 'ORG_ID_OLD',
288 avalue => p_org_id_old);
289
290 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
291 itemkey => itemkey,
292 aname => 'ORG_ID_NEW',
293 avalue => p_org_id_new);
294
295 -- Bug 4575004 - removed the setting of attribute SEQUENCE_NEW and
296 -- SEQUENCE_OLD since those attributes are not used and have been
297 -- removed from the workflow.
298
299 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
300 itemkey => itemkey,
301 aname => 'JOB_ID_OLD',
302 avalue => p_job_id_old);
303
304 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
305 itemkey => itemkey,
306 aname => 'JOB_ID_NEW',
307 avalue => p_job_id_new);
308
309 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
310 itemkey => itemkey,
311 aname => 'FROM_JOB_GROUP_ID',
312 avalue => p_from_job_group_id);
313
314 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
315 itemkey => itemkey,
316 aname => 'TO_JOB_GROUP_ID',
317 avalue => p_to_job_group_id);
318
319 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
320 itemkey => itemkey,
321 aname => 'SUPERVISOR_OLD',
322 avalue => p_supervisor_old);
323
324 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
325 itemkey => itemkey,
326 aname => 'SUPERVISOR_NEW',
327 avalue => p_supervisor_new);
328
329 wf_engine.SetItemAttrText ( itemtype => itemtype,
330 itemkey => itemkey,
331 aname => 'PRIMARY_FLAG_OLD',
332 avalue => p_primary_flag_old);
333
334 wf_engine.SetItemAttrText ( itemtype => itemtype,
335 itemkey => itemkey,
336 aname => 'PRIMARY_FLAG_NEW',
337 avalue => p_primary_flag_new);
338
339 wf_engine.SetItemAttrText ( itemtype => itemtype,
340 itemkey => itemkey,
341 aname => 'ORG_INFO1_OLD',
342 avalue => p_org_info1_old);
343
344 wf_engine.SetItemAttrText ( itemtype => itemtype,
345 itemkey => itemkey,
346 aname => 'ORG_INFO1_NEW',
347 avalue => p_org_info1_new);
348
349 wf_engine.SetItemAttrText ( itemtype => itemtype,
350 itemkey => itemkey,
351 aname => 'JEI_INFORMATION2_OLD',
352 avalue => p_jei_information2_old);
353
354 wf_engine.SetItemAttrText ( itemtype => itemtype,
355 itemkey => itemkey,
356 aname => 'JEI_INFORMATION2_NEW',
357 avalue => p_jei_information2_new);
358
359 wf_engine.SetItemAttrText ( itemtype => itemtype,
360 itemkey => itemkey,
361 aname => 'JEI_INFORMATION3_OLD',
362 avalue => p_jei_information3_old);
363
364 wf_engine.SetItemAttrText ( itemtype => itemtype,
365 itemkey => itemkey,
366 aname => 'JEI_INFORMATION3_NEW',
367 avalue => p_jei_information3_new);
368
369 wf_engine.SetItemAttrText ( itemtype => itemtype,
370 itemkey => itemkey,
371 aname => 'JEI_INFORMATION4_OLD',
372 avalue => p_jei_information4_old);
373
374 wf_engine.SetItemAttrText ( itemtype => itemtype,
375 itemkey => itemkey,
376 aname => 'JEI_INFORMATION4_NEW',
377 avalue => p_jei_information4_new);
378 wf_engine.SetItemAttrText ( itemtype => itemtype,
379 itemkey => itemkey,
380 aname => 'JEI_INFORMATION6_OLD',
381 avalue => p_jei_information6_old);
382
383 wf_engine.SetItemAttrText ( itemtype => itemtype,
384 itemkey => itemkey,
385 aname => 'JEI_INFORMATION6_NEW',
386 avalue => p_jei_information6_new);
387
388 wf_engine.SetItemAttrText ( itemtype => itemtype,
389 itemkey => itemkey,
390 aname => 'FULL_NAME_OLD',
391 avalue => p_full_name_old);
392
393 wf_engine.SetItemAttrText ( itemtype => itemtype,
394 itemkey => itemkey,
395 aname => 'FULL_NAME_NEW',
396 avalue => p_full_name_new);
397
398 -- Bug 4575004 - removed the setting of attribute GRADE_ID_OLD and
399 -- GRADE_ID_NEW since those attributes are not used and have been
400 -- removed from the workflow.
401
402 wf_engine.SetItemAttrText ( itemtype => itemtype,
403 itemkey => itemkey,
404 aname => 'COUNTRY_OLD',
405 avalue => p_country_old);
406
407 wf_engine.SetItemAttrText ( itemtype => itemtype,
408 itemkey => itemkey,
409 aname => 'COUNTRY_NEW',
410 avalue => p_country_new);
411
412 wf_engine.SetItemAttrText ( itemtype => itemtype,
413 itemkey => itemkey,
414 aname => 'CITY_OLD',
415 avalue => p_city_old);
416
417 wf_engine.SetItemAttrText ( itemtype => itemtype,
418 itemkey => itemkey,
419 aname => 'CITY_NEW',
420 avalue => p_city_new);
421
422 wf_engine.SetItemAttrText ( itemtype => itemtype,
423 itemkey => itemkey,
424 aname => 'REGION2_OLD',
425 avalue => p_region2_old);
426
427 wf_engine.SetItemAttrText ( itemtype => itemtype,
428 itemkey => itemkey,
429 aname => 'REGION2_NEW',
430 avalue => p_region2_new);
431
432 wf_engine.SetItemAttrDate ( itemtype => itemtype,
433 itemkey => itemkey,
434 aname => 'INACTIVE_DATE_OLD',
435 avalue => p_inactive_date_old);
436
437 wf_engine.SetItemAttrDate ( itemtype => itemtype,
438 itemkey => itemkey,
439 aname => 'INACTIVE_DATE_NEW',
440 avalue => p_inactive_date_new);
441
442 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
443 itemkey => itemkey,
444 aname => 'FROM_JOB_ID_OLD',
445 avalue => p_from_job_id_old);
446
447 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
448 itemkey => itemkey,
449 aname => 'FROM_JOB_ID_NEW',
450 avalue => p_from_job_id_new);
451
452 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
453 itemkey => itemkey,
454 aname => 'TO_JOB_ID_OLD',
455 avalue => p_to_job_id_old);
456
457 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
458 itemkey => itemkey,
459 aname => 'TO_JOB_ID_NEW',
460 avalue => p_to_job_id_new);
461
462 wf_engine.SetItemAttrText ( itemtype => itemtype,
463 itemkey => itemkey,
464 aname => 'ORG_INFO_CONTEXT',
465 avalue => p_org_info_context);
466
467
468 wf_engine.StartProcess( itemtype => itemtype,
469 itemkey => itemkey );
470
471
472 -- Insert to PA tables wf process information.
473 -- This is required for displaying notifications on PA pages.
474
475 BEGIN
476
477 PA_WORKFLOW_UTILS.Insert_WF_Processes
478 (p_wf_type_code => 'HR_CHANGE_MGMT'
479 ,p_item_type => itemtype
480 ,p_item_key => itemkey
481 ,p_entity_key1 => to_char(p_person_id)
482 ,p_entity_key2 => to_char(p_person_id)
483 ,p_description => NULL
484 ,p_err_code => l_err_code
485 ,p_err_stage => l_err_stage
486 ,p_err_stack => l_err_stack
487 );
488
489 EXCEPTION
490 WHEN OTHERS THEN
491 null;
492 END;
493
494 --
495 wf_engine.threshold := l_save_thresh ;
496 exception
497 when others then
498 null;
499
500 END update_project_entities;
501
502 PROCEDURE org_struct_element_change
503 (itemtype IN VARCHAR2
504 , itemkey IN VARCHAR2
505 , actid IN NUMBER
506 , funcmode IN VARCHAR2
507 , resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
508 ) IS
509
510
511
512 l_org_struct_element_id NUMBER;
513 l_organization_id_child NUMBER;
514 l_organization_id_parent NUMBER;
515 l_org_structure_version_id NUMBER;
516
517 l_msg_count NUMBER;
518 l_msg_data VARCHAR(2000);
519 l_return_status VARCHAR2(1);
520 l_api_version_number NUMBER := 1.0;
521 l_data VARCHAR2(2000);
522 l_msg_index_out NUMBER;
523
524 v_err_code NUMBER;
525 v_err_stage VARCHAR2(300);
526 v_err_stack VARCHAR2(300);
527 l_savepoint BOOLEAN;
528
529 --
530 --
531 begin
532
533
534 --
535 -- Get the workflow attribute values
536 --
537
538 l_org_struct_element_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
539 itemkey => itemkey,
540 aname => 'ORG_STRUCTURE_ELEMENT_ID' );
541
542 l_organization_id_parent := wf_engine.GetItemAttrNumber( itemtype => itemtype,
543 itemkey => itemkey,
544 aname => 'ORGANIZATION_ID_PARENT' );
545
546 l_organization_id_child := wf_engine.GetItemAttrNumber(itemtype => itemtype,
547 itemkey => itemkey,
548 aname => 'ORGANIZATION_ID_CHILD' );
549
550 l_org_structure_version_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
551 itemkey => itemkey,
552 aname => 'ORG_STRUCTURE_VERSION_ID' );
553
554 wf_engine.SetItemAttrText ( itemtype => itemtype,
555 itemkey => itemkey,
556 aname => 'ORGANIZATION_NAME',
557 avalue => pa_hr_update_api.get_org_name(l_organization_id_child));
558
559 wf_engine.SetItemAttrText ( itemtype => itemtype,
560 itemkey => itemkey,
561 aname => 'CHILD_ORGANIZATION_NAME',
562 avalue => pa_hr_update_api.get_org_name(l_organization_id_parent));
563
564 --
565 -- Call the api to populate the hierarchy denorm table
566 --
567 SAVEPOINT l_org_struct_element_change ;
568 l_savepoint := true;
569 pa_org_utils.populate_hierarchy_denorm(p_org_version_id => l_org_structure_version_id,
570 p_organization_id_child => l_organization_id_child,
571 p_organization_id_parent => l_organization_id_parent,
572 x_err_code => v_err_code,
573 x_err_stage => v_err_stage,
574 x_err_stack => v_err_stack);
575
576 IF nvl(v_err_code, 0) = 0 THEN
577
578 resultout := wf_engine.eng_completed||':'||'S';
579 ELSE
580
581 --
582 -- Set any error messages
583 --
584 l_savepoint := false;
585 rollback to l_org_struct_element_change ;
586 set_nf_error_msg_attr(p_item_type => itemtype,
587 p_item_key => itemkey,
588 p_msg_count => l_msg_count,
589 p_msg_data => l_msg_data);
590 resultout := wf_engine.eng_completed||':'||'F';
591
592 END IF;
593
594 --
595 EXCEPTION
596 /*
597 WHEN FND_API.G_EXC_ERROR
598 THEN
599 wf_core.context('pa_hr_update_pa_objects',
600 'start_date_change',
601 itemtype,
602 itemkey,
603 to_char(actid),
604 funcmode);
605 if (l_savepoint) then
606 rollback to l_org_struct_element_change ;
607 End if;
608
609 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
610 THEN
611 wf_core.context('pa_hr_update_pa_objects',
612 'start_date_change',
613 itemtype,
614 itemkey,
615 to_char(actid),
616 funcmode);
617 If (l_savepoint) then
618 rollback to l_org_struct_element_change ;
619 End if;
620
621 */
622 WHEN OTHERS THEN
623 wf_core.context('pa_forecast_test',
624 'start_date_change',
625 itemtype,
626 itemkey,
627 to_char(actid),
628 funcmode);
629 If (l_savepoint) then
630 rollback to l_org_struct_element_change ;
631 End if;
632 If l_msg_data is NULL and nvl(l_msg_count,0) = 0 then
633
634 wf_engine.SetItemAttrText
635 ( itemtype => itemtype
636 , itemkey => itemkey
637 , aname => 'ERROR_MSG1'
638 , avalue => SQLCODE||SQLERRM
639 );
640 Else
641 set_nf_error_msg_attr(p_item_type => itemtype,
642 p_item_key => itemkey,
643 p_msg_count => l_msg_count,
644 p_msg_data => l_msg_data);
645
646 End if;
647
648 END org_struct_element_change;
649
650
651
652 PROCEDURE Job_Bill_Change
653 (itemtype IN VARCHAR2
654 , itemkey IN VARCHAR2
655 , actid IN NUMBER
656 , funcmode IN VARCHAR2
657 , resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
658 ) IS
659
660 l_job_id NUMBER;
661 l_jei_information2_old VARCHAR2(150);
662 l_jei_information2_new VARCHAR2(150);
663 l_jei_information3_old VARCHAR2(150);
664 l_jei_information3_new VARCHAR2(150);
665 l_jei_information4_old VARCHAR2(150);
666 l_jei_information4_new VARCHAR2(150);
667 l_jei_information6_old VARCHAR2(150);
668 l_jei_information6_new VARCHAR2(150);
669 l_calling_mode VARCHAR2(10);
670
671 l_msg_count NUMBER;
672 l_msg_data VARCHAR(2000);
673 l_return_status VARCHAR2(1);
674 l_api_version_number NUMBER := 1.0;
675 l_data VARCHAR2(2000);
676 l_msg_index_out NUMBER;
677 l_savepoint BOOLEAN;
678
679 begin
680 --
681 -- Get the workflow attribute values
682 --
683 l_calling_mode := wf_engine.GetItemAttrText( itemtype => itemtype,
684 itemkey => itemkey,
685 aname => 'CALLING_MODE' );
686 l_job_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
687 itemkey => itemkey,
688 aname => 'JOB_ID_NEW' );
689 l_jei_information2_old := wf_engine.GetItemAttrText( itemtype => itemtype,
690 itemkey => itemkey,
691 aname => 'JEI_INFORMATION2_OLD' );
692 l_jei_information2_new := wf_engine.GetItemAttrText( itemtype => itemtype,
693 itemkey => itemkey,
694 aname => 'JEI_INFORMATION2_NEW' );
695 l_jei_information3_old := wf_engine.GetItemAttrText( itemtype => itemtype,
696 itemkey => itemkey,
697 aname => 'JEI_INFORMATION3_OLD' );
698 l_jei_information3_new := wf_engine.GetItemAttrText(itemtype => itemtype,
699 itemkey => itemkey,
700 aname => 'JEI_INFORMATION3_NEW' );
701 l_jei_information4_old := wf_engine.GetItemAttrText( itemtype => itemtype,
702 itemkey => itemkey,
703 aname => 'JEI_INFORMATION4_OLD' );
704 l_jei_information4_new := wf_engine.GetItemAttrText(itemtype => itemtype,
705 itemkey => itemkey,
706 aname => 'JEI_INFORMATION4_NEW' );
707 l_jei_information6_old := wf_engine.GetItemAttrText( itemtype => itemtype,
708 itemkey => itemkey,
709 aname => 'JEI_INFORMATION6_OLD' );
710 l_jei_information6_new := wf_engine.GetItemAttrText(itemtype => itemtype,
711 itemkey => itemkey,
712 aname => 'JEI_INFORMATION6_NEW' );
713
714 wf_engine.SetItemAttrText ( itemtype => itemtype,
715 itemkey => itemkey,
716 aname => 'JOB_NAME',
717 avalue => pa_hr_update_api.get_job_name(l_job_id));
718
719 log_message('before calling per_job_extra_billability');
720 --
721 -- Call api to process job billability
722 --
723 SAVEPOINT l_job_billability_change ;
724 l_savepoint := true;
725 pa_hr_update_api.per_job_extra_billability
726 (p_calling_mode =>l_calling_mode
727 ,P_job_id =>l_job_id
728 ,P_billable_flag_new =>l_jei_information2_new
729 ,P_billable_flag_old =>l_jei_information2_old
730 ,P_utilize_flag_new =>l_jei_information3_new
731 ,P_utilize_flag_old =>l_jei_information3_old
732 ,P_job_level_new =>l_jei_information4_new
733 ,P_job_level_old =>l_jei_information4_old
734 ,p_schedulable_flag_new =>l_jei_information6_new
735 ,p_schedulable_flag_old =>l_jei_information6_old
736 ,x_return_status =>l_return_status
737 ,x_msg_data =>l_msg_data
738 ,x_msg_count =>l_msg_count );
739
740 log_message('after calling per_job_extra_billability, l_return_status: '
741 ||l_return_status);
742 IF l_return_status = 'S' THEN
743
744 resultout := wf_engine.eng_completed||':'||'S';
745 ELSIF l_return_status = 'E' THEN
746 --
747 -- Set any error messages
748 --
749 l_savepoint := false;
750 rollback to l_job_billability_change ;
751 set_nf_error_msg_attr(p_item_type => itemtype,
752 p_item_key => itemkey,
753 p_msg_count => l_msg_count,
754 p_msg_data => l_msg_data);
755 resultout := wf_engine.eng_completed||':'||'F';
756
757 ELSE
758 --
759 -- Set any error messages
760 --
761 l_savepoint := false;
762 rollback to l_job_billability_change ;
763 set_nf_error_msg_attr(p_item_type => itemtype,
764 p_item_key => itemkey,
765 p_msg_count => fnd_msg_pub.count_msg,
766 p_msg_data => l_msg_data);
767
768 wf_engine.SetItemAttrText
769 ( itemtype => itemtype
770 , itemkey => itemkey
771 , aname => 'ERROR_MSG1'
772 , avalue => l_msg_data
773 );
774
775 END IF;
776 log_message('l_return_status: '||l_return_status);
777
778 --
779 EXCEPTION
780 /* WHEN FND_API.G_EXC_ERROR
781 THEN
782 wf_core.context('pa_hr_update_pa_objects',
783 'start_date_change',
784 itemtype,
785 itemkey,
786 to_char(actid),
787 funcmode);
788 If (l_savepoint) then
789 rollback to l_job_billability_change ;
790 End if;
791 set_nf_error_msg_attr(p_item_type => itemtype,
792 p_item_key => itemkey,
793 p_msg_count => l_msg_count,
794 p_msg_data => l_msg_data);
795
796 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
797 THEN
798 wf_core.context('pa_hr_update_pa_objects',
799 'start_date_change',
800 itemtype,
801 itemkey,
802 to_char(actid),
803 funcmode);
804 If (l_savepoint) then
805 rollback to l_job_billability_change ;
806 End if;
807 wf_engine.SetItemAttrText
808 ( itemtype => itemtype
809 , itemkey => itemkey
810 , aname => 'ERROR_MSG1'
811 , avalue => SQLCODE||SQLERRM
812 );
813
814 resultout := wf_engine.eng_completed||':'||'F';
815 */
816 WHEN OTHERS THEN
817 log_message('Execption OTHERS, '||SQLERRM ||', '|| SQLCODE);
818 wf_core.context('pa_forecast_test',
819 'start_date_change',
820 itemtype,
821 itemkey,
822 to_char(actid),
823 funcmode);
824 If (l_savepoint) then
825 log_message('before rollback to l_job_billability_change');
826 rollback to l_job_billability_change ;
827 log_message('after rollback to l_job_billability_change');
828 End if;
829 If l_msg_data is NULL and nvl(l_msg_count,0) = 0 then
830 log_message('l_msg_data is NULL');
831 wf_engine.SetItemAttrText
832 ( itemtype => itemtype
833 , itemkey => itemkey
834 , aname => 'ERROR_MSG1'
835 , avalue => SQLCODE||SQLERRM
836 );
837 Else
838 log_message('l_msg_data is not NULL');
839 set_nf_error_msg_attr(p_item_type => itemtype,
840 p_item_key => itemkey,
841 p_msg_count => l_msg_count,
842 p_msg_data => l_msg_data);
843
844 End if;
845
846 resultout := wf_engine.eng_completed||':'||'F';
847 log_message('resultout: '||resultout);
848 --RAISE;
849
850 END Job_Bill_Change;
851
852 PROCEDURE Full_Name_Change
853 (itemtype IN VARCHAR2
854 , itemkey IN VARCHAR2
855 , actid IN NUMBER
856 , funcmode IN VARCHAR2
857 , resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
858 ) IS
859
860
861
862 l_calling_mode VARCHAR2(10);
863 l_person_id NUMBER;
864 l_person_name VARCHAR2(240);
865 l_full_name_old VARCHAR2(240);
866 l_full_name_new VARCHAR2(240);
867
868 l_msg_count NUMBER;
869 l_msg_data VARCHAR(2000);
870 l_return_status VARCHAR2(1);
871 l_api_version_number NUMBER := 1.0;
872 l_data VARCHAR2(2000);
873 l_msg_index_out NUMBER;
874 l_savepoint Boolean;
875 v_err_code NUMBER;
876 v_err_stage VARCHAR2(300);
877 v_err_stack VARCHAR2(300);
878
879 --
880 --
881 begin
882
883 --
884 -- Get the workflow attribute values
885 --
886
887 l_calling_mode := wf_engine.GetItemAttrText( itemtype => itemtype,
888 itemkey => itemkey,
889 aname => 'CALLING_MODE' );
890
891 l_person_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
892 itemkey => itemkey,
893 aname => 'PERSON_ID' );
894
895 l_full_name_old := wf_engine.GetItemAttrText(itemtype => itemtype,
896 itemkey => itemkey,
897 aname => 'FULL_NAME_OLD');
898
899 l_full_name_new := wf_engine.GetItemAttrText(itemtype => itemtype,
900 itemkey => itemkey,
901 aname => 'FULL_NAME_NEW');
902
903 -- Removing the setting of the FULL_NAME_NEW attribute - no need
904
905 --
906 -- Call the api to update full name
907 --
908
909 SAVEPOINT l_full_name_change ;
910 l_savepoint := true;
911 PA_HR_UPDATE_API.update_name ( p_person_id => l_person_id
912 ,p_old_name => l_full_name_old
913 ,p_new_name => l_full_name_new
914 ,x_return_status => l_return_status
915 ,x_msg_count => l_msg_count
916 ,x_msg_data => l_msg_data);
917
918 IF l_return_status = 'S' THEN
919
920 resultout := wf_engine.eng_completed||':'||'S';
921 ELSIF l_return_status = 'E' THEN
922 --
923 -- Set any error messages
924 --
925 l_savepoint := false;
926 rollback to l_full_name_change ;
927 set_nf_error_msg_attr(p_item_type => itemtype,
928 p_item_key => itemkey,
929 p_msg_count => l_msg_count,
930 p_msg_data => l_msg_data);
931 resultout := wf_engine.eng_completed||':'||'F';
932
933 ELSE
934 l_savepoint := false;
935 rollback to l_full_name_change ;
936 set_nf_error_msg_attr(p_item_type => itemtype,
937 p_item_key => itemkey,
938 p_msg_count => fnd_msg_pub.count_msg,
939 p_msg_data => l_msg_data);
940
941 wf_engine.SetItemAttrText
942 ( itemtype => itemtype
943 , itemkey => itemkey
944 , aname => 'ERROR_MSG1'
945 , avalue => l_msg_data
946 );
947
948 END IF;
949
950
951 --
952 EXCEPTION
953 /* WHEN FND_API.G_EXC_ERROR
954 THEN
955 wf_core.context('pa_hr_update_pa_entities',
956 'Full_Name_Change',
957 itemtype,
958 itemkey,
959 to_char(actid),
960 funcmode);
961 If (l_savepoint) then
962 rollback to l_full_name_change ;
963 End if;
964 set_nf_error_msg_attr(p_item_type => itemtype,
965 p_item_key => itemkey,
966 p_msg_count => l_msg_count,
967 p_msg_data => l_msg_data);
968 resultout := wf_engine.eng_completed||':'||'F';
969
970 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
971 THEN
972 wf_core.context('pa_hr_update_pa_entities',
973 'Full_Name_Change',
974 itemtype,
975 itemkey,
976 to_char(actid),
977 funcmode);
978 If (l_savepoint) then
979 rollback to l_full_name_change ;
980 End if;
981 wf_engine.SetItemAttrText
982 ( itemtype => itemtype
983 , itemkey => itemkey
984 , aname => 'ERROR_MSG1'
985 , avalue => SQLERRM
986 );
987
988 resultout := wf_engine.eng_completed||':'||'F';
989 */
990 WHEN OTHERS THEN
991 wf_core.context('pa_hr_update_pa_entities',
992 'Full_Name_Change',
993 itemtype,
994 itemkey,
995 to_char(actid),
996 funcmode);
997 If (l_savepoint) then
998 rollback to l_full_name_change ;
999 End if;
1000 If l_msg_data is NULL and nvl(l_msg_count,0) = 0 then
1001
1002 wf_engine.SetItemAttrText
1003 ( itemtype => itemtype
1004 , itemkey => itemkey
1005 , aname => 'ERROR_MSG1'
1006 , avalue => SQLCODE||SQLERRM
1007 );
1008 Else
1009 set_nf_error_msg_attr(p_item_type => itemtype,
1010 p_item_key => itemkey,
1011 p_msg_count => l_msg_count,
1012 p_msg_data => l_msg_data);
1013
1014 End if;
1015
1016 resultout := wf_engine.eng_completed||':'||'F';
1017
1018 END Full_Name_Change;
1019
1020 PROCEDURE Default_OU_Change
1021 (itemtype IN VARCHAR2
1022 , itemkey IN VARCHAR2
1023 , actid IN NUMBER
1024 , funcmode IN VARCHAR2
1025 , resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1026 ) IS
1027
1028
1029
1030 l_calling_mode VARCHAR2(10);
1031 l_org_id NUMBER;
1032 l_org_info1_old VARCHAR2(150);
1033 l_org_info1_new VARCHAR2(150);
1034 l_org_info_context VARCHAR2(40);
1035
1036 l_msg_count NUMBER;
1037 l_msg_data VARCHAR(2000);
1038 l_api_version_number NUMBER := 1.0;
1039 l_data VARCHAR2(2000);
1040 l_msg_index_out NUMBER;
1041
1042 l_return_status VARCHAR2(1);
1043 l_error_message_code VARCHAR2(1000);
1044 v_err_code NUMBER;
1045 v_err_stage VARCHAR2(300);
1046 v_err_stack VARCHAR2(300);
1047 l_savepoint Boolean;
1048 --
1049 --
1050 begin
1051
1052
1053 --get the unique identifier for this specific workflow
1054 --
1055 -- Initialize workflow item attributes
1056 --
1057
1058 l_calling_mode := wf_engine.GetItemAttrText( itemtype => itemtype,
1059 itemkey => itemkey,
1060 aname => 'CALLING_MODE' );
1061
1062 l_org_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1063 itemkey => itemkey,
1064 aname => 'ORG_ID_NEW' );
1065
1066 l_org_info1_old := wf_engine.GetItemAttrText( itemtype => itemtype,
1067 itemkey => itemkey,
1068 aname => 'ORG_INFO1_OLD' );
1069
1070 l_org_info1_new := wf_engine.GetItemAttrText(itemtype => itemtype,
1071 itemkey => itemkey,
1072 aname => 'ORG_INFO1_NEW' );
1073
1074 l_org_info_context := wf_engine.GetItemAttrText(itemtype => itemtype,
1075 itemkey => itemkey,
1076 aname => 'ORG_INFO_CONTEXT' );
1077
1078 wf_engine.SetItemAttrText ( itemtype => itemtype,
1079 itemkey => itemkey,
1080 aname => 'ORGANIZATION_NAME',
1081 avalue => pa_hr_update_api.get_org_name(l_org_id));
1082
1083 SAVEPOINT l_ou_change ;
1084 l_savepoint := true;
1085
1086 IF l_org_info_context = 'Exp Organization Defaults' THEN
1087
1088 pa_hr_update_api.default_ou_change
1089 ( p_calling_mode => l_calling_mode,
1090 p_organization_id => l_org_id,
1091 p_default_ou_new => l_org_info1_new,
1092 p_default_ou_old => l_org_info1_old,
1093 x_return_status => l_return_status,
1094 x_msg_count => l_msg_count,
1095 x_msg_data => l_msg_data
1096 ) ;
1097
1098 ELSIF l_org_info_context = 'Project Resource Job Group' THEN
1099
1100 pa_hr_update_api.proj_res_job_group_change
1101 ( p_calling_mode => l_calling_mode,
1102 p_organization_id => l_org_id,
1103 p_proj_job_group_new => l_org_info1_new,
1104 p_proj_job_group_old => l_org_info1_old,
1105 x_return_status => l_return_status,
1106 x_msg_count => l_msg_count,
1107 x_msg_data => l_msg_data
1108 ) ;
1109
1110 END IF;
1111
1112 IF l_return_status = 'S' THEN
1113
1114 resultout := wf_engine.eng_completed||':'||'S';
1115 ELSIF l_return_status = 'E' THEN
1116 l_savepoint := false;
1117 rollback to l_ou_change ;
1118 set_nf_error_msg_attr(p_item_type => itemtype,
1119 p_item_key => itemkey,
1120 p_msg_count => l_msg_count,
1121 p_msg_data => l_msg_data);
1122 resultout := wf_engine.eng_completed||':'||'F';
1123
1124 ELSE
1125 l_savepoint := false;
1126 rollback to l_ou_change ;
1127 set_nf_error_msg_attr(p_item_type => itemtype,
1128 p_item_key => itemkey,
1129 p_msg_count => fnd_msg_pub.count_msg,
1130 p_msg_data => l_msg_data);
1131
1132 wf_engine.SetItemAttrText
1133 ( itemtype => itemtype
1134 , itemkey => itemkey
1135 , aname => 'ERROR_MSG1'
1136 , avalue => l_msg_data
1137 );
1138
1139 END IF;
1140
1141 --
1142 EXCEPTION
1143 /* WHEN FND_API.G_EXC_ERROR
1144 THEN
1145 If (l_savepoint) then
1146 rollback to l_ou_change ;
1147 End if;
1148 -- wf_core.context('pa_hr_update_pa_entities',
1149 -- 'Full_Name_Change',
1150 -- itemtype,
1151 -- itemkey,
1152 -- to_char(actid),
1153 -- funcmode);
1154 set_nf_error_msg_attr(p_item_type => itemtype,
1155 p_item_key => itemkey,
1156 p_msg_count => l_msg_count,
1157 p_msg_data => l_msg_data);
1158
1159 resultout := wf_engine.eng_completed||':'||'F';
1160 -- RAISE;
1161
1162 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1163 THEN
1164
1165 If (l_savepoint) then
1166 rollback to l_ou_change ;
1167 End if;
1168 -- wf_core.context('pa_hr_update_pa_entities',
1169 -- 'Full_Name_Change',
1170 -- itemtype,
1171 -- itemkey,
1172 -- to_char(actid),
1173 -- funcmode);
1174
1175 wf_engine.SetItemAttrText
1176 ( itemtype => itemtype
1177 , itemkey => itemkey
1178 , aname => 'ERROR_MSG1'
1179 , avalue => SQLCODE||SQLERRM
1180 );
1181
1182
1183
1184 resultout := wf_engine.eng_completed||':'||'F';
1185 --RAISE ;
1186 */
1187 WHEN OTHERS THEN
1188 If (l_savepoint) then
1189 rollback to l_ou_change ;
1190 End if;
1191 -- wf_core.context('pa_hr_update_pa_entities',
1192 -- 'Full_Name_Change',
1193 -- itemtype,
1194 -- itemkey,
1195 -- to_char(actid),
1196 -- funcmode);
1197 If l_msg_data is NULL and nvl(l_msg_count,0) = 0 then
1198
1199 wf_engine.SetItemAttrText
1200 ( itemtype => itemtype
1201 , itemkey => itemkey
1202 , aname => 'ERROR_MSG1'
1203 , avalue => SQLCODE||SQLERRM
1204 );
1205 Else
1206 set_nf_error_msg_attr(p_item_type => itemtype,
1207 p_item_key => itemkey,
1208 p_msg_count => l_msg_count,
1209 p_msg_data => l_msg_data);
1210
1211 End if;
1212
1213 resultout := wf_engine.eng_completed||':'||'F';
1214 -- RAISE ;
1215
1216 END Default_OU_Change;
1217
1218 -- This procedure will not get called anymore because the triggers
1219 -- on per_valid_grades and per_grades have been removed
1220 PROCEDURE Valid_Grade_Change
1221 (itemtype IN VARCHAR2
1222 , itemkey IN VARCHAR2
1223 , actid IN NUMBER
1224 , funcmode IN VARCHAR2
1225 , resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1226 ) IS
1227
1228
1229
1230 l_calling_mode VARCHAR2(10);
1231 l_org_id NUMBER;
1232 l_org_info1_old VARCHAR2(150);
1233 l_org_info1_new VARCHAR2(150);
1234 l_org_info_context VARCHAR2(40);
1235 l_grade_id_old NUMBER;
1236 l_grade_id_new NUMBER;
1237 l_job_id_old NUMBER;
1238 l_job_id_new NUMBER;
1239 l_from_job_id_old NUMBER;
1240 l_from_job_id_new NUMBER;
1241 l_to_job_id_old NUMBER;
1242 l_to_job_id_new NUMBER;
1243 l_from_job_group_id NUMBER;
1244 l_to_job_group_id NUMBER;
1245
1246
1247 l_msg_count NUMBER;
1248 l_msg_data VARCHAR(2000);
1249 l_return_status VARCHAR2(1);
1250 l_api_version_number NUMBER := 1.0;
1251 l_data VARCHAR2(2000);
1252 l_msg_index_out NUMBER;
1253
1254 v_err_code NUMBER;
1255 v_err_stage VARCHAR2(300);
1256 v_err_stack VARCHAR2(300);
1257 l_savepoint Boolean;
1258 --
1259 --
1260 begin
1261
1262
1263 --get the unique identifier for this specific workflow
1264 --
1265 -- Initialize workflow item attributes
1266 --
1267
1268 l_calling_mode := wf_engine.GetItemAttrText( itemtype => itemtype,
1269 itemkey => itemkey,
1270 aname => 'CALLING_MODE' );
1271
1272 /*
1273 l_grade_id_old := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1274 itemkey => itemkey,
1275 aname => 'GRADE_ID_OLD' );
1276
1277 l_grade_id_new := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1278 itemkey => itemkey,
1279 aname => 'GRADE_ID_NEW' );
1280
1281 */
1282 l_job_id_old := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1283 itemkey => itemkey,
1284 aname => 'JOB_ID_OLD' );
1285
1286 l_job_id_new := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1287 itemkey => itemkey,
1288 aname => 'JOB_ID_NEW' );
1289
1290 wf_engine.SetItemAttrText ( itemtype => itemtype,
1291 itemkey => itemkey,
1292 aname => 'JOB_NAME',
1293 avalue => pa_hr_update_api.get_job_name(l_job_id_new));
1294
1295 /*
1296 wf_engine.SetItemAttrText ( itemtype => itemtype,
1297 itemkey => itemkey,
1298 aname => 'GRADE_NAME',
1299 avalue => pa_hr_update_api.get_grade_name(l_grade_id_new));
1300 */
1301
1302 SAVEPOINT l_valid_grade_change ;
1303 l_savepoint := true;
1304 PA_HR_UPDATE_API.update_job_levels( P_calling_mode => l_calling_mode
1305 ,P_per_valid_grade_job_id => l_job_id_New
1306 ,P_per_valid_grade_id_old => l_grade_id_old
1307 ,P_per_valid_grade_id_new => l_grade_id_New
1308 ,x_return_status => l_return_status
1309 ,x_msg_count => l_msg_count
1310 ,x_msg_data => l_msg_data);
1311
1312
1313 IF l_return_status = 'S' THEN
1314
1315 resultout := wf_engine.eng_completed||':'||'S';
1316 ELSIF l_return_status = 'E' THEN
1317 l_savepoint := false;
1318 rollback to l_valid_grade_change ;
1319 set_nf_error_msg_attr(p_item_type => itemtype,
1320 p_item_key => itemkey,
1321 p_msg_count => l_msg_count,
1322 p_msg_data => l_msg_data);
1323 resultout := wf_engine.eng_completed||':'||'F';
1324
1325 ELSE
1326 l_savepoint := false;
1327 rollback to l_valid_grade_change ;
1328 set_nf_error_msg_attr(p_item_type => itemtype,
1329 p_item_key => itemkey,
1330 p_msg_count => fnd_msg_pub.count_msg,
1331 p_msg_data => l_msg_data);
1332
1333 wf_engine.SetItemAttrText
1334 ( itemtype => itemtype
1335 , itemkey => itemkey
1336 , aname => 'ERROR_MSG1'
1337 , avalue => l_msg_data
1338 );
1339
1340 END IF;
1341
1342
1343 --
1344 EXCEPTION
1345 /* WHEN FND_API.G_EXC_ERROR
1346 THEN
1347 If (l_savepoint) then
1348 rollback to l_valid_grade_change ;
1349 End if;
1350 wf_core.context('pa_hr_update_pa_entities',
1351 'Valid_Grade_Change',
1352 itemtype,
1353 itemkey,
1354 to_char(actid),
1355 funcmode);
1356 set_nf_error_msg_attr(p_item_type => itemtype,
1357 p_item_key => itemkey,
1358 p_msg_count => l_msg_count,
1359 p_msg_data => l_msg_data);
1360 resultout := wf_engine.eng_completed||':'||'F';
1361
1362 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1363 THEN
1364 If (l_savepoint) then
1365 rollback to l_valid_grade_change ;
1366 End if;
1367 wf_core.context('pa_hr_update_pa_entities',
1368 'Valid_Grade_Change',
1369 itemtype,
1370 itemkey,
1371 to_char(actid),
1372 funcmode);
1373 wf_engine.SetItemAttrText
1374 ( itemtype => itemtype
1375 , itemkey => itemkey
1376 , aname => 'ERROR_MSG1'
1377 , avalue => SQLERRM
1378 );
1379 resultout := wf_engine.eng_completed||':'||'F';
1380 */
1381 WHEN OTHERS THEN
1382 If (l_savepoint) then
1383 rollback to l_valid_grade_change ;
1384 End if;
1385 wf_core.context('pa_hr_update_pa_entities',
1386 'Valid_Grade_Change',
1387 itemtype,
1388 itemkey,
1389 to_char(actid),
1390 funcmode);
1391 If l_msg_data is NULL and nvl(l_msg_count,0) = 0 then
1392
1393 wf_engine.SetItemAttrText
1394 ( itemtype => itemtype
1395 , itemkey => itemkey
1396 , aname => 'ERROR_MSG1'
1397 , avalue => SQLCODE||SQLERRM
1398 );
1399 Else
1400 set_nf_error_msg_attr(p_item_type => itemtype,
1401 p_item_key => itemkey,
1402 p_msg_count => l_msg_count,
1403 p_msg_data => l_msg_data);
1404
1405 End if;
1406
1407 resultout := wf_engine.eng_completed||':'||'F';
1408
1409 END Valid_Grade_Change;
1410
1411 -- This procedure will not get called anymore because the triggers
1412 -- on per_valid_grades and per_grades have been removed
1413 PROCEDURE Job_Level_Change
1414 (itemtype IN VARCHAR2
1415 , itemkey IN VARCHAR2
1416 , actid IN NUMBER
1417 , funcmode IN VARCHAR2
1418 , resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1419 ) IS
1420
1421
1422
1423 l_calling_mode VARCHAR2(10);
1424 l_grade_id NUMBER;
1425 l_job_level_old NUMBER;
1426 l_job_level_new NUMBER;
1427 l_org_info_context VARCHAR2(40);
1428
1429 l_msg_count NUMBER;
1430 l_msg_data VARCHAR(2000);
1431 l_return_status VARCHAR2(1);
1432 l_api_version_number NUMBER := 1.0;
1433 l_data VARCHAR2(2000);
1434 l_msg_index_out NUMBER;
1435
1436 v_err_code NUMBER;
1437 v_err_stage VARCHAR2(300);
1438 v_err_stack VARCHAR2(300);
1439 l_savepoint Boolean;
1440 --
1441 --
1442 begin
1443
1444
1445
1446 --get the unique identifier for this specific workflow
1447 --
1448 -- Initialize workflow item attributes
1449 --
1450
1451 l_calling_mode := wf_engine.GetItemAttrText( itemtype => itemtype,
1452 itemkey => itemkey,
1453 aname => 'CALLING_MODE' );
1454
1455 /*
1456 l_grade_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1457 itemkey => itemkey,
1458 aname => 'GRADE_ID_NEW' );
1459
1460 l_job_level_old := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1461 itemkey => itemkey,
1462 aname => 'SEQUENCE_OLD' );
1463
1464 l_job_level_new := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1465 itemkey => itemkey,
1466 aname => 'SEQUENCE_NEW' );
1467
1468 wf_engine.SetItemAttrText ( itemtype => itemtype,
1469 itemkey => itemkey,
1470 aname => 'GRADE_NAME',
1471 avalue => pa_hr_update_api.get_grade_name(l_grade_id));
1472 */
1473
1474 SAVEPOINT l_job_level_change ;
1475 l_savepoint := true;
1476 PA_HR_UPDATE_API.update_job_levels( P_calling_mode => l_calling_mode
1477 ,P_per_grades_grade_id => l_grade_id
1478 ,P_per_grades_sequence_old => l_job_level_old
1479 ,P_per_grades_sequence_new => l_job_level_new
1480 ,x_return_status => l_return_status
1481 ,x_msg_count => l_msg_count
1482 ,x_msg_data => l_msg_data);
1483
1484 IF l_return_status = 'S' THEN
1485
1486 resultout := wf_engine.eng_completed||':'||'S';
1487 ELSIF l_return_status = 'E' THEN
1488 l_savepoint := false;
1489 rollback to l_job_level_change ;
1490 set_nf_error_msg_attr(p_item_type => itemtype,
1491 p_item_key => itemkey,
1492 p_msg_count => l_msg_count,
1493 p_msg_data => l_msg_data);
1494 resultout := wf_engine.eng_completed||':'||'F';
1495
1496 ELSE
1497 l_savepoint := false;
1498 rollback to l_job_level_change ;
1499 set_nf_error_msg_attr(p_item_type => itemtype,
1500 p_item_key => itemkey,
1501 p_msg_count => fnd_msg_pub.count_msg,
1502 p_msg_data => l_msg_data);
1503
1504 wf_engine.SetItemAttrText
1505 ( itemtype => itemtype
1506 , itemkey => itemkey
1507 , aname => 'ERROR_MSG1'
1508 , avalue => l_msg_data
1509 );
1510
1511 END IF;
1512
1513
1514 EXCEPTION
1515 /* WHEN FND_API.G_EXC_ERROR
1516 THEN
1517 If (l_savepoint) then
1518 rollback to l_job_level_change ;
1519 End if;
1520 wf_core.context('pa_hr_update_pa_entities',
1521 'Job_Change',
1522 itemtype,
1523 itemkey,
1524 to_char(actid),
1525 funcmode);
1526
1527 set_nf_error_msg_attr(p_item_type => itemtype,
1528 p_item_key => itemkey,
1529 p_msg_count => l_msg_count,
1530 p_msg_data => l_msg_data);
1531
1532 resultout := wf_engine.eng_completed||':'||'F';
1533
1534 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1535 THEN
1536 If (l_savepoint) then
1537 rollback to l_job_level_change ;
1538 End if;
1539 wf_core.context('pa_hr_update_pa_entities',
1540 'Job_Change',
1541 itemtype,
1542 itemkey,
1543 to_char(actid),
1544 funcmode);
1545
1546 wf_engine.SetItemAttrText
1547 ( itemtype => itemtype
1548 , itemkey => itemkey
1549 , aname => 'ERROR_MSG1'
1550 , avalue => SQLERRM
1551 );
1552
1553 resultout := wf_engine.eng_completed||':'||'F';
1554 */
1555 WHEN OTHERS THEN
1556 If (l_savepoint) then
1557 rollback to l_job_level_change ;
1558 End if;
1559 wf_core.context('pa_hr_update_pa_entities',
1560 'Job_Change',
1561 itemtype,
1562 itemkey,
1563 to_char(actid),
1564 funcmode);
1565
1566 If l_msg_data is NULL and nvl(l_msg_count,0) = 0 then
1567
1568 wf_engine.SetItemAttrText
1569 ( itemtype => itemtype
1570 , itemkey => itemkey
1571 , aname => 'ERROR_MSG1'
1572 , avalue => SQLCODE||SQLERRM
1573 );
1574 Else
1575 set_nf_error_msg_attr(p_item_type => itemtype,
1576 p_item_key => itemkey,
1577 p_msg_count => l_msg_count,
1578 p_msg_data => l_msg_data);
1579
1580 End if;
1581
1582 resultout := wf_engine.eng_completed||':'||'F';
1583
1584 END Job_Level_Change;
1585
1586 PROCEDURE Address_Change
1587 (itemtype IN VARCHAR2
1588 , itemkey IN VARCHAR2
1589 , actid IN NUMBER
1590 , funcmode IN VARCHAR2
1591 , resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1592 ) IS
1593
1594
1595
1596 l_calling_mode VARCHAR2(10);
1597 l_person_id NUMBER;
1598 l_person_name VARCHAR2(240);
1599 l_country_old VARCHAR2(60);
1600 l_country_new VARCHAR2(60);
1601 l_city_old VARCHAR2(30);
1602 l_city_new VARCHAR2(30);
1603 l_region2_old VARCHAR2(70);
1604 l_region2_new VARCHAR2(70);
1605 l_date_from_old DATE;
1606 l_date_from_new DATE;
1607 l_date_to_old DATE;
1608 l_date_to_new DATE;
1609 l_addr_prim_flag_old VARCHAR2(20);
1610 l_addr_prim_flag_new VARCHAR2(20);
1611 l_org_info_context VARCHAR2(40);
1612
1613 l_msg_count NUMBER;
1614 l_msg_data VARCHAR(2000);
1615 l_return_status VARCHAR2(1);
1616 l_api_version_number NUMBER := 1.0;
1617 l_data VARCHAR2(2000);
1618 l_msg_index_out NUMBER;
1619
1620 v_err_code NUMBER;
1621 v_err_stage VARCHAR2(300);
1622 v_err_stack VARCHAR2(300);
1623 l_savepoint Boolean;
1624 --
1625 --
1626 begin
1627
1628 --get the unique identifier for this specific workflow
1629 --
1630 -- Initialize workflow item attributes
1631 --
1632
1633 l_calling_mode := wf_engine.GetItemAttrText( itemtype => itemtype,
1634 itemkey => itemkey,
1635 aname => 'CALLING_MODE' );
1636
1637 l_person_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1638 itemkey => itemkey,
1639 aname => 'PERSON_ID' );
1640
1641 l_country_old := wf_engine.GetItemAttrText( itemtype => itemtype,
1642 itemkey => itemkey,
1643 aname => 'COUNTRY_OLD' );
1644
1645 l_country_new := wf_engine.GetItemAttrText(itemtype => itemtype,
1646 itemkey => itemkey,
1647 aname => 'COUNTRY_NEW' );
1648
1649 l_city_old := wf_engine.GetItemAttrText( itemtype => itemtype,
1650 itemkey => itemkey,
1651 aname => 'CITY_OLD' );
1652
1653 l_city_new := wf_engine.GetItemAttrText(itemtype => itemtype,
1654 itemkey => itemkey,
1655 aname => 'CITY_NEW' );
1656
1657 l_region2_old := wf_engine.GetItemAttrText( itemtype => itemtype,
1658 itemkey => itemkey,
1659 aname => 'REGION2_OLD' );
1660
1661 l_region2_new := wf_engine.GetItemAttrText(itemtype => itemtype,
1662 itemkey => itemkey,
1663 aname => 'REGION2_NEW' );
1664
1665 l_date_from_old := wf_engine.GetItemAttrDate(itemtype => itemtype,
1666 itemkey => itemkey,
1667 aname => 'START_DATE_OLD');
1668
1669 l_date_from_new := wf_engine.GetItemAttrDate(itemtype => itemtype,
1670 itemkey => itemkey,
1671 aname => 'START_DATE_NEW');
1672
1673 l_date_to_old := wf_engine.GetItemAttrDate(itemtype => itemtype,
1674 itemkey => itemkey,
1675 aname => 'END_DATE_OLD');
1676
1677 l_date_to_new := wf_engine.GetItemAttrDate(itemtype => itemtype,
1678 itemkey => itemkey,
1679 aname => 'END_DATE_NEW');
1680
1681 l_addr_prim_flag_old := wf_engine.GetItemAttrText(itemtype => itemtype,
1682 itemkey => itemkey,
1683 aname => 'PRIMARY_FLAG_OLD');
1684
1685 l_addr_prim_flag_new := wf_engine.GetItemAttrText(itemtype => itemtype,
1686 itemkey => itemkey,
1687 aname => 'PRIMARY_FLAG_NEW');
1688
1689 -- Changed for bug 4354854 - performance improvement
1690 pa_resource_utils.get_person_name(p_person_id => l_person_id,
1691 x_person_name => l_person_name,
1692 x_return_status => l_return_status);
1693
1694 IF l_return_status = 'E' THEN
1695 --
1696 l_savepoint := false;
1697 rollback to l_address_change ;
1698 resultout := wf_engine.eng_completed||':'||'F';
1699 END IF;
1700
1701 wf_engine.SetItemAttrText(itemtype => itemtype,
1702 itemkey => itemkey,
1703 aname => 'FULL_NAME_NEW',
1704 avalue => l_person_name);
1705
1706
1707 SAVEPOINT l_address_change ;
1708 l_savepoint := true;
1709
1710 --dbms_output.put_line('Calling address Update');
1711 pa_hr_update_api.address_change( p_calling_mode => l_calling_mode
1712 , p_person_id => l_person_id
1713 , p_country_old => l_country_old
1714 , p_country_new => l_country_new
1715 , p_city_old => l_city_old
1716 , p_city_new => l_city_new
1717 , p_region2_old => l_region2_old
1718 , p_region2_new => l_region2_new
1719 , p_date_from_old => l_date_from_old
1720 , p_date_from_new => l_date_from_new
1721 , p_date_to_old => l_date_to_old
1722 , p_date_to_new => l_date_to_new
1723 , p_addr_prim_flag_old => l_addr_prim_flag_old
1724 , p_addr_prim_flag_new => l_addr_prim_flag_new
1725 , x_return_status => l_return_status
1726 , x_msg_count => l_msg_count
1727 , x_msg_data => l_msg_data);
1728
1729 IF l_return_status = 'S' THEN
1730
1731 --dbms_output.put_line('Address Update Success');
1732
1733 resultout := wf_engine.eng_completed||':'||'S';
1734 ELSIF l_return_status = 'E' THEN
1735 l_savepoint := false;
1736 rollback to l_address_change ;
1737 set_nf_error_msg_attr(p_item_type => itemtype,
1738 p_item_key => itemkey,
1739 p_msg_count => l_msg_count,
1740 p_msg_data => l_msg_data);
1741 resultout := wf_engine.eng_completed||':'||'F';
1742
1743 ELSE
1744 l_savepoint := false;
1745 rollback to l_address_change ;
1746 set_nf_error_msg_attr(p_item_type => itemtype,
1747 p_item_key => itemkey,
1748 p_msg_count => fnd_msg_pub.count_msg,
1749 p_msg_data => l_msg_data);
1750
1751 wf_engine.SetItemAttrText
1752 ( itemtype => itemtype
1753 , itemkey => itemkey
1754 , aname => 'ERROR_MSG1'
1755 , avalue => l_msg_data
1756 );
1757
1758 END IF;
1759
1760
1761
1762 EXCEPTION
1763 /* WHEN FND_API.G_EXC_ERROR
1764 THEN
1765 If (l_savepoint) then
1766 rollback to l_address_change ;
1767 end if;
1768 wf_core.context('pa_hr_update_pa_entities',
1769 'Address_Change',
1770 itemtype,
1771 itemkey,
1772 to_char(actid),
1773 funcmode);
1774
1775
1776 set_nf_error_msg_attr(p_item_type => itemtype,
1777 p_item_key => itemkey,
1778 p_msg_count => l_msg_count,
1779 p_msg_data => l_msg_data);
1780
1781 resultout := wf_engine.eng_completed||':'||'F';
1782
1783 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1784 THEN
1785 If (l_savepoint) then
1786 rollback to l_address_change ;
1787 End if;
1788 wf_core.context('pa_hr_update_pa_entities',
1789 'Address_Change',
1790 itemtype,
1791 itemkey,
1792 to_char(actid),
1793 funcmode);
1794
1795 wf_engine.SetItemAttrText
1796 ( itemtype => itemtype
1797 , itemkey => itemkey
1798 , aname => 'ERROR_MSG1'
1799 , avalue => SQLCODE||SQLERRM
1800 );
1801
1802 resultout := wf_engine.eng_completed||':'||'F';
1803 */
1804 WHEN OTHERS THEN
1805 If (l_savepoint) then
1806 rollback to l_address_change ;
1807 null;
1808 End if;
1809 wf_core.context('pa_hr_update_pa_entities',
1810 'Address_Change',
1811 itemtype,
1812 itemkey,
1813 to_char(actid),
1814 funcmode);
1815
1816 If l_msg_data is NULL and nvl(l_msg_count,0) = 0 then
1817
1818 wf_engine.SetItemAttrText
1819 ( itemtype => itemtype
1820 , itemkey => itemkey
1821 , aname => 'ERROR_MSG1'
1822 , avalue => SQLCODE||SQLERRM
1823 );
1824 Else
1825 set_nf_error_msg_attr(p_item_type => itemtype,
1826 p_item_key => itemkey,
1827 p_msg_count => l_msg_count,
1828 p_msg_data => l_msg_data);
1829
1830 End if;
1831
1832 resultout := wf_engine.eng_completed||':'||'F';
1833
1834 END Address_Change;
1835
1836 PROCEDURE Project_Organization_Change
1837 (itemtype IN VARCHAR2
1838 , itemkey IN VARCHAR2
1839 , actid IN NUMBER
1840 , funcmode IN VARCHAR2
1841 , resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1842 ) IS
1843
1844
1845
1846 l_calling_mode VARCHAR2(10);
1847 l_org_id NUMBER;
1848 l_org_info1_old VARCHAR2(150);
1849 l_org_info1_new VARCHAR2(150);
1850 l_org_info_context VARCHAR2(40);
1851
1852 l_msg_count NUMBER;
1853 l_msg_data VARCHAR(2000);
1854 l_return_status VARCHAR2(1);
1855 l_api_version_number NUMBER := 1.0;
1856 l_data VARCHAR2(2000);
1857 l_msg_index_out NUMBER;
1858
1859 v_err_code NUMBER;
1860 v_err_stage VARCHAR2(300);
1861 v_err_stack VARCHAR2(300);
1862
1863 l_inactive_date_old DATE;
1864 l_inactive_date_new DATE;
1865 l_savepoint Boolean;
1866 --
1867 --
1868 begin
1869
1870
1871 --get the unique identifier for this specific workflow
1872 --
1873 -- Initialize workflow item attributes
1874 --
1875
1876 l_calling_mode := wf_engine.GetItemAttrText( itemtype => itemtype,
1877 itemkey => itemkey,
1878 aname => 'CALLING_MODE' );
1879
1880
1881
1882 l_org_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1883 itemkey => itemkey,
1884 aname => 'ORG_ID_NEW' );
1885
1886 l_org_info1_new := wf_engine.GetItemAttrText(itemtype => itemtype,
1887 itemkey => itemkey,
1888 aname => 'ORG_INFO1_NEW' );
1889
1890 l_inactive_date_old := wf_engine.GetItemAttrDate( itemtype => itemtype,
1891 itemkey => itemkey,
1892 aname => 'INACTIVE_DATE_OLD' );
1893
1894 l_inactive_date_new := wf_engine.GetItemAttrDate( itemtype => itemtype,
1895 itemkey => itemkey,
1896 aname => 'INACTIVE_DATE_NEW' );
1897
1898 wf_engine.SetItemAttrText ( itemtype => itemtype,
1899 itemkey => itemkey,
1900 aname => 'ORGANIZATION_NAME',
1901 avalue => pa_hr_update_api.get_org_name(l_org_id));
1902
1903
1904 IF l_calling_mode = 'UPDATE' THEN
1905
1906 --dbms_output.put_line('Calling Mode is Update');
1907
1908 SAVEPOINT l_project_org_change ;
1909 l_savepoint := true;
1910
1911 --The following code checks to see if the organization of the resource belongs
1912 --to some other expenditure hierarchy. The resource must be end_dated / inactivated
1913 --only if he does not belong to any expenditure hierarchy
1914
1915 if(pa_hr_update_api.belongs_ExpOrg(l_org_id) = 'N') then
1916 -- dbms_output.put_line('Making resources inactive');
1917
1918 PA_HR_UPDATE_API.make_resource_inactive( p_calling_mode => l_calling_mode
1919 ,P_Organization_id => l_org_id
1920 ,P_Default_OU => l_org_info1_new
1921 ,P_inactive_date => l_inactive_date_new
1922 ,x_return_status => l_return_status
1923 ,x_msg_count => l_msg_count
1924 ,x_msg_data => l_msg_data);
1925 else
1926 --dbms_output.put_line('Pulling resources');
1927 PA_HR_UPDATE_API.pull_resources( P_Organization_id => l_org_id
1928 ,x_return_status => l_return_status
1929 ,x_msg_count => l_msg_count
1930 ,x_msg_data => l_msg_data);
1931 end if;
1932
1933 ELSIF l_calling_mode = 'INSERT' THEN
1934 --dbms_output.put_line('Calling mode is Insert');
1935
1936 SAVEPOINT l_project_org_change ;
1937 l_savepoint := true;
1938 pa_hr_update_api.default_ou_change( p_calling_mode => l_calling_mode,
1939 p_organization_id => l_org_id,
1940 p_default_ou_new => l_org_info1_new,
1941 p_default_ou_old => l_org_info1_old,
1942 x_return_status => l_return_status,
1943 x_msg_count => l_msg_count,
1944 x_msg_data => l_msg_data
1945 ) ;
1946 END IF;
1947
1948
1949 IF l_return_status = 'S' THEN
1950
1951 resultout := wf_engine.eng_completed||':'||'S';
1952 ELSIF l_return_status = 'E' THEN
1953 l_savepoint := false;
1954 rollback to l_project_org_change ;
1955 set_nf_error_msg_attr(p_item_type => itemtype,
1956 p_item_key => itemkey,
1957 p_msg_count => l_msg_count,
1958 p_msg_data => l_msg_data);
1959 resultout := wf_engine.eng_completed||':'||'F';
1960
1961 ELSE
1962 l_savepoint := false;
1963 rollback to l_project_org_change ;
1964 set_nf_error_msg_attr(p_item_type => itemtype,
1965 p_item_key => itemkey,
1966 p_msg_count => fnd_msg_pub.count_msg,
1967 p_msg_data => l_msg_data);
1968
1969 wf_engine.SetItemAttrText
1970 ( itemtype => itemtype
1971 , itemkey => itemkey
1972 , aname => 'ERROR_MSG1'
1973 , avalue => l_msg_data
1974 );
1975
1976 END IF;
1977
1978 --
1979 EXCEPTION
1980 /* WHEN FND_API.G_EXC_ERROR
1981 THEN
1982 If (l_savepoint) then
1983 rollback to l_project_org_change ;
1984 end if;
1985 wf_core.context('pa_hr_update_pa_entities',
1986 'Address_Change',
1987 itemtype,
1988 itemkey,
1989 to_char(actid),
1990 funcmode);
1991
1992 set_nf_error_msg_attr(p_item_type => itemtype,
1993 p_item_key => itemkey,
1994 p_msg_count => l_msg_count,
1995 p_msg_data => l_msg_data);
1996
1997 resultout := wf_engine.eng_completed||':'||'F';
1998 --RAISE;
1999
2000 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
2001 THEN
2002 If (l_savepoint) then
2003 rollback to l_project_org_change ;
2004 End if;
2005 wf_core.context('pa_hr_update_pa_entities',
2006 'Project_Organization_Change',
2007 itemtype,
2008 itemkey,
2009 to_char(actid),
2010 funcmode);
2011
2012 wf_engine.SetItemAttrText
2013 ( itemtype => itemtype
2014 , itemkey => itemkey
2015 , aname => 'ERROR_MSG1'
2016 , avalue => SQLERRM
2017 );
2018
2019 resultout := wf_engine.eng_completed||':'||'F';
2020 --RAISE;
2021 */
2022 WHEN OTHERS THEN
2023
2024 If (l_savepoint) then
2025 rollback to l_project_org_change ;
2026 End if;
2027
2028 wf_core.context('pa_hr_update_pa_entities',
2029 'Project_Organization_Change',
2030 itemtype,
2031 itemkey,
2032 to_char(actid),
2033 funcmode);
2034
2035 If l_msg_data is NULL and nvl(l_msg_count,0) = 0 then
2036
2037 wf_engine.SetItemAttrText
2038 ( itemtype => itemtype
2039 , itemkey => itemkey
2040 , aname => 'ERROR_MSG1'
2041 , avalue => SQLCODE||SQLERRM
2042 );
2043 Else
2044 set_nf_error_msg_attr(p_item_type => itemtype,
2045 p_item_key => itemkey,
2046 p_msg_count => l_msg_count,
2047 p_msg_data => l_msg_data);
2048
2049 End if;
2050
2051 resultout := wf_engine.eng_completed||':'||'F';
2052 --RAISE;
2053
2054 END Project_Organization_Change;
2055
2056 PROCEDURE Job_Rel_Change
2057 (itemtype IN VARCHAR2
2058 , itemkey IN VARCHAR2
2059 , actid IN NUMBER
2060 , funcmode IN VARCHAR2
2061 , resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2062 ) IS
2063
2064
2065
2066 l_calling_mode VARCHAR2(10);
2067
2068 l_msg_count NUMBER;
2069 l_msg_data VARCHAR(2000);
2070 l_return_status VARCHAR2(1);
2071 l_api_version_number NUMBER := 1.0;
2072 l_data VARCHAR2(2000);
2073 l_msg_index_out NUMBER;
2074
2075 v_err_code NUMBER;
2076 v_err_stage VARCHAR2(300);
2077 v_err_stack VARCHAR2(300);
2078
2079 l_from_job_id_old NUMBER;
2080 l_from_job_id_new NUMBER;
2081 l_to_job_id_old NUMBER;
2082 l_to_job_id_new NUMBER;
2083 l_from_job_group_id NUMBER;
2084 l_to_job_group_id NUMBER;
2085 l_savepoint Boolean;
2086 --
2087 --
2088 begin
2089
2090
2091 --get the unique identifier for this specific workflow
2092 --
2093 -- Initialize workflow item attributes
2094 --
2095
2096 l_calling_mode := wf_engine.GetItemAttrText( itemtype => itemtype,
2097 itemkey => itemkey,
2098 aname => 'CALLING_MODE' );
2099
2100 l_from_job_id_old := wf_engine.GetItemAttrNumber( itemtype => itemtype,
2101 itemkey => itemkey,
2102 aname => 'FROM_JOB_ID_OLD' );
2103
2104 l_from_job_id_new := wf_engine.GetItemAttrNumber(itemtype => itemtype,
2105 itemkey => itemkey,
2106 aname => 'FROM_JOB_ID_NEW' );
2107
2108 l_to_job_id_old := wf_engine.GetItemAttrNumber( itemtype => itemtype,
2109 itemkey => itemkey,
2110 aname => 'TO_JOB_ID_OLD' );
2111
2112 l_to_job_id_new := wf_engine.GetItemAttrNumber(itemtype => itemtype,
2113 itemkey => itemkey,
2114 aname => 'TO_JOB_ID_NEW' );
2115
2116 l_from_job_group_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
2117 itemkey => itemkey,
2118 aname => 'FROM_JOB_GROUP_ID' );
2119
2120 l_to_job_group_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
2121 itemkey => itemkey,
2122 aname => 'TO_JOB_GROUP_ID' );
2123
2124
2125 wf_engine.SetItemAttrText ( itemtype => itemtype,
2126 itemkey => itemkey,
2127 aname => 'JOB_NAME',
2128 avalue => pa_hr_update_api.get_job_name(l_from_job_id_new));
2129
2130 wf_engine.SetItemAttrText ( itemtype => itemtype,
2131 itemkey => itemkey,
2132 aname => 'TO_JOB_NAME',
2133 avalue => pa_hr_update_api.get_job_name(l_to_job_id_new));
2134
2135 SAVEPOINT l_job_rel_change ;
2136 l_savepoint := true;
2137 PA_HR_UPDATE_API.update_job_levels( P_calling_mode => l_calling_mode
2138 ,P_from_job_id_old => l_from_job_id_old
2139 ,P_from_job_id_new => l_from_job_id_new
2140 ,P_to_job_id_old => l_to_job_id_old
2141 ,P_to_job_id_new => l_to_job_id_new
2142 ,P_from_job_group_id => l_from_job_group_id
2143 ,P_to_job_group_id => l_to_job_group_id
2144 ,x_return_status => l_return_status
2145 ,x_msg_count => l_msg_count
2146 ,x_msg_data => l_msg_data);
2147
2148
2149 IF l_return_status = 'S' THEN
2150
2151 resultout := wf_engine.eng_completed||':'||'S';
2152 ELSIF l_return_status = 'E' THEN
2153 l_savepoint := false;
2154 rollback to l_job_rel_change ;
2155 set_nf_error_msg_attr(p_item_type => itemtype,
2156 p_item_key => itemkey,
2157 p_msg_count => l_msg_count,
2158 p_msg_data => l_msg_data);
2159 resultout := wf_engine.eng_completed||':'||'F';
2160
2161 ELSE
2162 l_savepoint := false;
2163 rollback to l_job_rel_change ;
2164 set_nf_error_msg_attr(p_item_type => itemtype,
2165 p_item_key => itemkey,
2166 p_msg_count => fnd_msg_pub.count_msg,
2167 p_msg_data => l_msg_data);
2168
2169 wf_engine.SetItemAttrText
2170 ( itemtype => itemtype
2171 , itemkey => itemkey
2172 , aname => 'ERROR_MSG1'
2173 , avalue => l_msg_data
2174 );
2175
2176 END IF;
2177
2178 --
2179 EXCEPTION
2180 /* WHEN FND_API.G_EXC_ERROR
2181 THEN
2182 If (l_savepoint) then
2183 rollback to l_job_rel_change ;
2184 End if;
2185 wf_core.context('pa_hr_update_pa_entities',
2186 'Address_Change',
2187 itemtype,
2188 itemkey,
2189 to_char(actid),
2190 funcmode);
2191
2192 set_nf_error_msg_attr(p_item_type => itemtype,
2193 p_item_key => itemkey,
2194 p_msg_count => l_msg_count,
2195 p_msg_data => l_msg_data);
2196
2197 resultout := wf_engine.eng_completed||':'||'F';
2198
2199 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
2200 THEN
2201 If (l_savepoint) then
2202 rollback to l_job_rel_change ;
2203 End if;
2204 wf_core.context('pa_hr_update_pa_entities',
2205 'Address_Change',
2206 itemtype,
2207 itemkey,
2208 to_char(actid),
2209 funcmode);
2210
2211 wf_engine.SetItemAttrText
2212 ( itemtype => itemtype
2213 , itemkey => itemkey
2214 , aname => 'ERROR_MSG1'
2215 , avalue => SQLCODE||SQLERRM
2216 );
2217
2218 resultout := wf_engine.eng_completed||':'||'F';
2219 */
2220 WHEN OTHERS THEN
2221 If (l_savepoint) then
2222 rollback to l_job_rel_change ;
2223 End if;
2224
2225 wf_core.context('pa_hr_update_pa_entities',
2226 'Address_Change',
2227 itemtype,
2228 itemkey,
2229 to_char(actid),
2230 funcmode);
2231
2232 If l_msg_data is NULL and nvl(l_msg_count,0) = 0 then
2233
2234 wf_engine.SetItemAttrText
2235 ( itemtype => itemtype
2236 , itemkey => itemkey
2237 , aname => 'ERROR_MSG1'
2238 , avalue => SQLCODE||SQLERRM
2239 );
2240 Else
2241 set_nf_error_msg_attr(p_item_type => itemtype,
2242 p_item_key => itemkey,
2243 p_msg_count => l_msg_count,
2244 p_msg_data => l_msg_data);
2245
2246 End if;
2247
2248 resultout := wf_engine.eng_completed||':'||'F';
2249
2250 END Job_Rel_Change;
2251
2252 PROCEDURE assignment_change
2253 (itemtype IN VARCHAR2
2254 , itemkey IN VARCHAR2
2255 , actid IN NUMBER
2256 , funcmode IN VARCHAR2
2257 , resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2258 ) IS
2259
2260
2261 l_calling_mode VARCHAR2(10);
2262 l_person_id NUMBER;
2263 l_person_name VARCHAR2(240);
2264 l_start_date_old DATE;
2265 l_start_date_new DATE;
2266 l_end_date_old DATE;
2267 l_end_date_new DATE;
2268 l_org_id_old NUMBER;
2269 l_org_id_new NUMBER;
2270 l_job_id_old NUMBER;
2271 l_job_id_new NUMBER;
2272 l_supervisor_old NUMBER;
2273 l_supervisor_new NUMBER;
2274 l_primary_flag_old VARCHAR2(1);
2275 l_primary_flag_new VARCHAR2(1);
2276 l_resource_id NUMBER;
2277 l_res_asgn_exists VARCHAR2(1);
2278
2279 l_res_id PA_RESOURCES.RESOURCE_ID%TYPE ; -- for bug 5683340
2280 l_invol_term VARCHAR2(1) ; -- for bug 5683340
2281
2282 v_return_status varchar2(2000);
2283 v_msg_count number;
2284 v_resource_id number;
2285 v_msg_data varchar2(300);
2286 v_error_message_code varchar2(2000);
2287 l_msg_count NUMBER;
2288 l_message_counter NUMBER := 0 ;
2289 l_msg_data VARCHAR(2000);
2290 l_message_data VARCHAR(2000);
2291 l_return_status VARCHAR2(1);
2292 l_final_return_status VARCHAR2(1) := 'S';
2293 l_api_version_number NUMBER := 1.0;
2294 l_data VARCHAR2(2000);
2295 l_msg_index_out NUMBER;
2296 l_savepoint Boolean;
2297 --
2298 --
2299 begin
2300
2301
2302 --get the unique identifier for this specific workflow
2303 --
2304 -- Initialize workflow item attributes
2305 --
2306
2307 l_final_return_status := FND_API.G_RET_STS_SUCCESS ;
2308
2309 l_calling_mode := wf_engine.GetItemAttrText( itemtype => itemtype,
2310 itemkey => itemkey,
2311 aname => 'CALLING_MODE' );
2312
2313 l_person_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
2314 itemkey => itemkey,
2315 aname => 'PERSON_ID' );
2316 l_start_date_old := wf_engine.GetItemAttrDate( itemtype => itemtype,
2317 itemkey => itemkey,
2318 aname => 'START_DATE_OLD' );
2319 l_start_date_new := wf_engine.GetItemAttrDate(itemtype => itemtype,
2320 itemkey => itemkey,
2321 aname => 'START_DATE_NEW' );
2322 l_end_date_old := wf_engine.GetItemAttrDate( itemtype => itemtype,
2323 itemkey => itemkey,
2324 aname => 'END_DATE_OLD' );
2325 l_end_date_new := wf_engine.GetItemAttrDate(itemtype => itemtype,
2326 itemkey => itemkey,
2327 aname => 'END_DATE_NEW' );
2328 l_org_id_old := wf_engine.GetItemAttrNumber( itemtype => itemtype,
2329 itemkey => itemkey,
2330 aname => 'ORG_ID_OLD' );
2331 l_org_id_new := wf_engine.GetItemAttrNumber(itemtype => itemtype,
2332 itemkey => itemkey,
2333 aname => 'ORG_ID_NEW' );
2334 l_job_id_old := wf_engine.GetItemAttrNumber( itemtype => itemtype,
2335 itemkey => itemkey,
2336 aname => 'JOB_ID_OLD' );
2337 l_job_id_new := wf_engine.GetItemAttrNumber(itemtype => itemtype,
2338 itemkey => itemkey,
2339 aname => 'JOB_ID_NEW' );
2340 l_supervisor_old := wf_engine.GetItemAttrNumber( itemtype => itemtype,
2341 itemkey => itemkey,
2342 aname => 'SUPERVISOR_OLD' );
2343 l_supervisor_new := wf_engine.GetItemAttrNumber(itemtype => itemtype,
2344 itemkey => itemkey,
2345 aname => 'SUPERVISOR_NEW' );
2346 l_primary_flag_old := wf_engine.GetItemAttrText( itemtype => itemtype,
2347 itemkey => itemkey,
2348 aname => 'PRIMARY_FLAG_OLD' );
2349 l_primary_flag_new := wf_engine.GetItemAttrText(itemtype => itemtype,
2350 itemkey => itemkey,
2351 aname => 'PRIMARY_FLAG_NEW' );
2352
2353
2354 -- Changed for bug 4354854 - performance improvement
2355 pa_resource_utils.get_person_name(p_person_id => l_person_id,
2356 x_person_name => l_person_name,
2357 x_return_status => l_return_status);
2358
2359 IF l_return_status = 'E' THEN
2360 --
2361 l_savepoint := false;
2362 rollback to l_address_change ;
2363 resultout := wf_engine.eng_completed||':'||'F';
2364 END IF;
2365
2366 wf_engine.SetItemAttrText (itemtype => itemtype,
2367 itemkey => itemkey,
2368 aname => 'FULL_NAME_NEW',
2369 avalue => l_person_name);
2370
2371 --Start of addition for bug 3957522
2372 if l_calling_mode = 'DELETE' then
2373 pa_hr_update_api.Delete_PA_Resource_Denorm (
2374 p_person_id => l_person_id
2375 ,p_old_start_date => l_start_date_old
2376 ,p_old_end_date => l_end_date_old
2377 ,x_return_status => l_return_status
2378 ,x_msg_count => l_msg_count
2379 ,x_msg_data => l_msg_data);
2380
2381
2382 /*Call added for bug 5683340*/
2383 pa_resource_utils.init_fte_sync_wf( p_person_id => l_person_id,
2384 x_invol_term => l_invol_term,
2385 x_return_status => l_return_status,
2386 x_msg_data => l_msg_data ,
2387 x_msg_count => l_msg_count
2388 );
2389
2390 l_message_counter := l_message_counter + nvl(l_msg_count,0);
2391 IF l_return_status = 'U' THEN
2392 app_exception.raise_exception;
2393 ELSIF l_return_status = 'E' THEN
2394 l_final_return_status := l_return_status;
2395 END IF;
2396
2397 /*IF - ELSIF block added for bug 5683340*/
2398 IF ((l_invol_term = 'N') AND (l_return_status = 'S')) THEN
2399
2400 l_res_id := pa_resource_utils.get_resource_id(l_person_id);
2401
2402 PA_TIMELINE_PVT.Create_Timeline (
2403 p_start_resource_name => NULL,
2404 p_end_resource_name => NULL,
2405 p_resource_id => l_res_id,
2406 p_start_date => NULL,
2407 p_end_date => NULL,
2408 x_return_status => l_return_status,
2409 x_msg_count => l_msg_count,
2410 x_msg_data => l_msg_data);
2411
2412 l_message_counter := l_message_counter + nvl(l_msg_count,0);
2413 if l_return_status = 'U' then
2414
2415 app_exception.raise_exception;
2416
2417 elsif l_return_status = 'E' then
2418
2419 l_final_return_status := l_return_status;
2420 end if;
2421
2422 END IF ; --IF ((l_invol_term = 'N') AND (l_return_status = 'S')) bug 5683340
2423
2424 -- End of addition for bug 3957522.
2425 elsif l_calling_mode = 'INSERT' then
2426
2427 if ( l_primary_flag_new = 'Y' and pa_hr_update_api.belongs_ExpOrg(l_org_id_new) = 'Y') then
2428 -- Added new if condition for Bug 7423251
2429 IF (pa_hr_update_api.check_job_utilization (p_job_id => l_job_id_new
2430 ,p_person_id => null
2431 ,p_date => null)) = 'Y' then
2432 -- call the work flow to update pa objects
2433 SAVEPOINT l_assignment_change;
2434 l_savepoint := true;
2435 pa_r_project_resources_pub.create_resource (
2436 p_api_version => 1.0
2437 ,p_init_msg_list => NULL
2438 ,p_commit => FND_API.G_FALSE
2439 ,p_validate_only => NULL
2440 ,p_max_msg_count => NULL
2441 ,p_internal => 'Y'
2442 ,p_person_id => l_person_id
2443 ,p_individual => 'Y'
2444 ,p_resource_type => 'EMPLOYEE'
2445 ,x_return_status => l_return_status
2446 ,x_msg_count => l_msg_count
2447 ,x_msg_data => l_msg_data
2448 ,x_resource_id => l_resource_id);
2449
2450
2451 -- call this procedure to update the forecast data for
2452 -- assigned time ONLY for this resource
2453 -- pass null to start date and end date
2454 -- this is called only if create_resource is a success
2455 if (l_return_status = 'S' and l_resource_id is not null) then
2456 PA_FORECASTITEM_PVT.Create_Forecast_Item(
2457 p_person_id => l_person_id
2458 ,p_start_date => null
2459 ,p_end_date => null
2460 ,p_process_mode => 'GENERATE_ASGMT'
2461 ,x_return_status => l_return_status
2462 ,x_msg_count => l_msg_count
2463 ,x_msg_data => l_msg_data
2464 ) ;
2465 end if;
2466 end if;
2467
2468
2469 l_message_counter := l_message_counter + nvl(l_msg_count,0);
2470
2471 if l_return_status = 'U' then
2472 app_exception.raise_exception;
2473 elsif l_return_status = 'E' then
2474 l_final_return_status := l_return_status;
2475 end if;
2476
2477 end if;
2478
2479 elsif l_calling_mode = 'UPDATE' then
2480 SAVEPOINT l_assignment_change;
2481 l_savepoint := true;
2482
2483 if ( l_primary_flag_new = 'Y' and l_primary_flag_old = 'N'
2484 and pa_hr_update_api.belongs_ExpOrg(l_org_id_new) = 'Y') then
2485 --dbms_output.put_line('Calling ind pull 1');
2486
2487 -- call the work flow to update pa objects
2488 pa_r_project_resources_pub.create_resource (
2489 p_api_version => 1.0
2490 ,p_init_msg_list => NULL
2491 ,p_commit => FND_API.G_FALSE
2492 ,p_validate_only => NULL
2493 ,p_max_msg_count => NULL
2494 ,p_internal => 'Y'
2495 ,p_person_id => l_person_id
2496 ,p_individual => 'Y'
2497 ,p_resource_type => 'EMPLOYEE'
2498 ,x_return_status => l_return_status
2499 ,x_msg_count => l_msg_count
2500 ,x_msg_data => l_msg_data
2501 ,x_resource_id => l_resource_id);
2502
2503 -- call this procedure to update the forecast data for
2504 -- assigned time ONLY for this resource
2505 -- pass null to start date and end date
2506 -- this is called only if create_resource is a success
2507 if (l_return_status = 'S' and l_resource_id is not null) then
2508 PA_FORECASTITEM_PVT.Create_Forecast_Item(
2509 p_person_id => l_person_id
2510 ,p_start_date => null
2511 ,p_end_date => null
2512 ,p_process_mode => 'GENERATE_ASGMT'
2513 ,x_return_status => l_return_status
2514 ,x_msg_count => l_msg_count
2515 ,x_msg_data => l_msg_data
2516 ) ;
2517 end if;
2518
2519
2520 l_message_counter := l_message_counter + nvl(l_msg_count,0);
2521
2522 if l_return_status = 'U' then
2523
2524 app_exception.raise_exception;
2525
2526 elsif l_return_status = 'E' then
2527
2528 l_final_return_status := l_return_status;
2529 end if;
2530
2531 elsif nvl(l_primary_flag_old,'N') <> nvl(l_primary_flag_new,'N') then
2532
2533 --dbms_output.put_line('Primary flags differ');
2534
2535 if (l_primary_flag_new = 'Y' and l_primary_flag_old = 'N'
2536 and pa_hr_update_api.belongs_ExpOrg(l_org_id_new) = 'N') then
2537
2538 /*If the assignments organization does not belong to expenditure hierarchy
2539 then the corresponding assignment in pa_resources_denorm must be
2540 end dated
2541 */
2542 pa_hr_update_api.Update_EndDate(
2543 p_person_id => l_person_id,
2544 p_old_start_date => l_start_date_old,
2545 p_new_start_date => l_start_date_new,
2546 p_old_end_date => l_end_date_old,
2547 p_new_end_date => sysdate,
2548 x_return_status => l_return_status,
2549 x_msg_data => l_msg_data,
2550 x_msg_count => l_msg_count);
2551 else
2552
2553 -- for all other cases, still need to call this API which will call
2554 -- forecast items
2555 -- call the work flow to update pa objects
2556 pa_hr_update_api.Update_PrimaryFlag (
2557 p_person_id => l_person_id
2558 ,p_old_start_date => l_start_date_old
2559 ,p_new_start_date => l_start_date_new
2560 ,p_old_end_date => l_end_date_old
2561 ,p_new_end_date => l_end_date_new
2562 ,x_return_status => l_return_status
2563 ,x_msg_count => l_msg_count
2564 ,x_msg_data => l_msg_data);
2565
2566 end if;
2567
2568 l_message_counter := l_message_counter + nvl(l_msg_count,0);
2569 if l_return_status = 'U' then
2570
2571 app_exception.raise_exception;
2572
2573 elsif l_return_status = 'E' then
2574
2575 l_final_return_status := l_return_status;
2576 end if;
2577
2578 else --after primary flag
2579
2580 --dbms_output.put_line('Primary flags are same');
2581
2582 -- The code below checks if record exists for update in
2583 -- pa_resources_denorm. This check is required because a new
2584 -- assignment creation in HR only updates the default HR
2585 -- assignment. So the new assignment will not exist in PRM
2586
2587 -- Bug 4352255 - performance improvement - change to where exists
2588 -- instead of count. And move it to here from above.
2589 -- Bug 4668272 - Handle NO_DATA_FOUND exception.
2590
2591 l_res_asgn_exists := 'N';
2592
2593 BEGIN
2594 SELECT 'Y'
2595 INTO l_res_asgn_exists
2596 FROM dual
2597 WHERE EXISTS (SELECT 'Y'
2598 FROM pa_resources_denorm
2599 WHERE person_id = l_person_id
2600 AND resource_effective_start_date =
2601 l_start_date_new);
2602 EXCEPTION WHEN NO_DATA_FOUND THEN
2603 l_res_asgn_exists := 'N';
2604 END;
2605 -- END -- Bug 4668272 - added exception handler
2606
2607 -- Bug 4668272 change l_res_asgn_exists = 'Y' to 'N'
2608 /* if (l_res_asgn_exists = 'N' AND
2609 pa_hr_update_api.belongs_ExpOrg(l_org_id_new) = 'Y') THEN commented and changed as below for bug 5665503 */
2610
2611 if ( l_res_asgn_exists = 'N' OR (trunc( l_start_date_old) <> trunc( l_start_date_new)) )
2612 AND ( pa_hr_update_api.belongs_ExpOrg(l_org_id_new) = 'Y')
2613 THEN
2614
2615 --dbms_output.put_line('Calling ind pull 2 as record does not exist or start date is changed');
2616
2617 pa_r_project_resources_pub.create_resource (
2618 p_api_version => 1.0
2619 ,p_init_msg_list => NULL
2620 ,p_commit => FND_API.G_FALSE
2621 ,p_validate_only => NULL
2622 ,p_max_msg_count => NULL
2623 ,p_internal => 'Y'
2624 ,p_person_id => l_person_id
2625 ,p_individual => 'Y'
2626 ,p_resource_type => 'EMPLOYEE'
2627 ,x_return_status => l_return_status
2628 ,x_msg_count => l_msg_count
2629 ,x_msg_data => l_msg_data
2630 ,x_resource_id => l_resource_id);
2631
2632 -- call this procedure to update the forecast data for
2633 -- assigned time ONLY for this resource
2634 -- pass null to start date and end date
2635 -- this is called only if create_resource is a success
2636 if (l_return_status = 'S' and l_resource_id is not null) then
2637 PA_FORECASTITEM_PVT.Create_Forecast_Item(
2638 p_person_id => l_person_id
2639 ,p_start_date => null
2640 ,p_end_date => null
2641 ,p_process_mode => 'GENERATE_ASGMT'
2642 ,x_return_status => l_return_status
2643 ,x_msg_count => l_msg_count
2644 ,x_msg_data => l_msg_data
2645 ) ;
2646 end if;
2647
2648
2649 l_message_counter := l_message_counter + nvl(l_msg_count,0);
2650
2651 if l_return_status = 'U' then
2652
2653 app_exception.raise_exception;
2654
2655 elsif l_return_status = 'E' then
2656
2657 l_final_return_status := l_return_status;
2658 end if;
2659
2660 else
2661 if ( trunc( l_end_date_old) <> trunc( l_end_date_new) ) THEN
2662
2663 --dbms_output.put_line('Date Change');
2664
2665 -- call the work flow to update pa objects
2666 pa_hr_update_api.Update_EndDate (
2667 p_person_id => l_person_id
2668 ,p_old_start_date => l_start_date_old
2669 ,p_new_start_date => l_start_date_new
2670 ,p_old_end_date => l_end_date_old
2671 ,p_new_end_date => l_end_date_new
2672 ,x_return_status => l_return_status
2673 ,x_msg_count => l_msg_count
2674 ,x_msg_data => l_msg_data);
2675
2676 l_message_counter := l_message_counter + nvl(l_msg_count,0);
2677 if l_return_status = 'U' then
2678
2679 app_exception.raise_exception;
2680
2681 elsif l_return_status = 'E' then
2682
2683 l_final_return_status := l_return_status;
2684 end if;
2685 end if;
2686
2687 if (nvl(l_org_id_old,-1) <> nvl(l_org_id_new,-1) ) THEN
2688
2689 --dbms_output.put_line('Organization changed');
2690
2691 -- call the work flow to update pa objects
2692 pa_hr_update_api.Update_Org (
2693 p_person_id => l_person_id
2694 ,p_old_org_id => l_org_id_old
2695 ,p_new_org_id => l_org_id_new
2696 ,p_old_start_date => l_start_date_old
2697 ,p_new_start_date => l_start_date_new
2698 ,p_old_end_date => l_end_date_old
2699 ,p_new_end_date => l_end_date_new
2700 ,x_return_status => l_return_status
2701 ,x_msg_count => l_msg_count
2702 ,x_msg_data => l_msg_data);
2703
2704 l_message_counter := l_message_counter + nvl(l_msg_count,0);
2705 if l_return_status = 'U' then
2706
2707 app_exception.raise_exception;
2708
2709 elsif l_return_status = 'E' then
2710
2711 l_final_return_status := l_return_status;
2712 end if;
2713
2714 end if;
2715
2716 if (nvl(l_job_id_old,-1) <> nvl(l_job_id_new,-1) ) THEN
2717
2718 --dbms_output.put_line('Job changed');
2719
2720 -- call the work flow to update pa objects
2721 pa_hr_update_api.Update_Job (
2722 p_person_id => l_person_id
2723 ,p_old_job => l_job_id_old
2724 ,p_new_job => l_job_id_new
2725 ,p_new_start_date => l_start_date_new
2726 ,p_new_end_date => l_end_date_new
2727 ,x_return_status => l_return_status
2728 ,x_msg_count => l_msg_count
2729 ,x_msg_data => l_msg_data);
2730
2731 l_message_counter := l_message_counter + nvl(l_msg_count,0);
2732 if l_return_status = 'U' then
2733
2734 app_exception.raise_exception;
2735
2736 elsif l_return_status = 'E' then
2737
2738 l_final_return_status := l_return_status;
2739 end if;
2740
2741 end if;
2742
2743 if (nvl(l_supervisor_old,-1) <> nvl(l_supervisor_new,-1) ) THEN
2744
2745 --dbms_output.put_line('Supervisor changed');
2746
2747 -- call the work flow to update pa objects
2748 pa_hr_update_api.Update_Supervisor (
2749 p_person_id => l_person_id
2750 ,p_old_supervisor => l_supervisor_old
2751 ,p_new_supervisor => l_supervisor_new
2752 ,p_new_start_date => l_start_date_new
2753 ,p_new_end_date => l_end_date_new
2754 ,x_return_status => l_return_status
2755 ,x_msg_count => l_msg_count
2756 ,x_msg_data => l_msg_data);
2757
2758 l_message_counter := l_message_counter + nvl(l_msg_count,0);
2759 if l_return_status = 'U' then
2760
2761 app_exception.raise_exception;
2762
2763 elsif l_return_status = 'E' then
2764
2765 l_final_return_status := l_return_status;
2766 end if;
2767
2768 end if;
2769
2770 end if; --end after l_res_asgn_exists check
2771 end if;--end after primary flag
2772 end if; --end update
2773
2774
2775 IF l_final_return_status = 'S' THEN
2776
2777 resultout := wf_engine.eng_completed||':'||'S';
2778 ELSIF l_return_status = 'E' THEN
2779 l_savepoint := false;
2780 rollback to l_assignment_change ;
2781 set_nf_error_msg_attr(p_item_type => itemtype,
2782 p_item_key => itemkey,
2783 p_msg_count => l_msg_count,
2784 p_msg_data => l_msg_data);
2785 resultout := wf_engine.eng_completed||':'||'F';
2786
2787 ELSE
2788 l_savepoint := false;
2789 rollback to l_assignment_change ;
2790 set_nf_error_msg_attr(p_item_type => itemtype,
2791 p_item_key => itemkey,
2792 p_msg_count => fnd_msg_pub.count_msg,
2793 p_msg_data => l_msg_data);
2794
2795 wf_engine.SetItemAttrText
2796 ( itemtype => itemtype
2797 , itemkey => itemkey
2798 , aname => 'ERROR_MSG1'
2799 , avalue => l_msg_data
2800 );
2801
2802 resultout := wf_engine.eng_completed||':'||'F';
2803 END IF;
2804
2805 EXCEPTION
2806 WHEN FND_API.G_EXC_ERROR THEN
2807 If (l_savepoint) then
2808 rollback to l_assignment_change ;
2809 end if;
2810
2811 wf_core.context ('pa_hr_update_pa_objects',
2812 'start_date_change',
2813 itemtype,
2814 itemkey,
2815 to_char(actid),
2816 funcmode);
2817
2818 If l_msg_data is NULL and nvl(l_msg_count,0) = 0 then
2819
2820 wf_engine.SetItemAttrText
2821 ( itemtype => itemtype
2822 , itemkey => itemkey
2823 , aname => 'ERROR_MSG1'
2824 , avalue => SQLCODE||SQLERRM
2825 );
2826 Else
2827 set_nf_error_msg_attr(p_item_type => itemtype,
2828 p_item_key => itemkey,
2829 p_msg_count => l_msg_count,
2830 p_msg_data => l_msg_data);
2831
2832 End if;
2833
2834 resultout := wf_engine.eng_completed||':'||'F';
2835 --RAISE;
2836
2837 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2838 If (l_savepoint) then
2839 rollback to l_assignment_change ;
2840 End if;
2841
2842 wf_core.context('pa_hr_update_pa_objects',
2843 'start_date_change',
2844 itemtype,
2845 itemkey,
2846 to_char(actid),
2847 funcmode);
2848 If l_msg_data is NULL and nvl(l_msg_count,0) = 0 then
2849
2850 wf_engine.SetItemAttrText
2851 ( itemtype => itemtype
2852 , itemkey => itemkey
2853 , aname => 'ERROR_MSG1'
2854 , avalue => SQLCODE||SQLERRM
2855 );
2856 Else
2857 set_nf_error_msg_attr(p_item_type => itemtype,
2858 p_item_key => itemkey,
2859 p_msg_count => l_msg_count,
2860 p_msg_data => l_msg_data);
2861
2862 End if;
2863
2864 resultout := wf_engine.eng_completed||':'||'U';
2865 --RAISE;
2866
2867 WHEN OTHERS THEN
2868 If (l_savepoint) then
2869 rollback to l_assignment_change ;
2870 End if;
2871 /* wf_core.context ('pa_forecast_test',
2872 'start_date_change',
2873 itemtype,
2874 itemkey,
2875 to_char(actid),
2876 funcmode);
2877 */
2878 If l_msg_data is NULL and nvl(l_msg_count,0) = 0 then
2879
2880 wf_engine.SetItemAttrText
2881 ( itemtype => itemtype
2882 , itemkey => itemkey
2883 , aname => 'ERROR_MSG1'
2884 , avalue => SQLCODE||SQLERRM
2885 );
2886 Else
2887 set_nf_error_msg_attr(p_item_type => itemtype,
2888 p_item_key => itemkey,
2889 p_msg_count => l_msg_count,
2890 p_msg_data => l_msg_data);
2891
2892 End if;
2893
2894 resultout := wf_engine.eng_completed||':'||'U';
2895 --RAISE;
2896
2897 END assignment_change;
2898
2899 PROCEDURE set_nf_error_msg_attr (p_item_type IN VARCHAR2,
2900 p_item_key IN VARCHAR2,
2901 p_msg_count IN NUMBER,
2902 p_msg_data IN VARCHAR2 ) IS
2903
2904 l_msg_index_out NUMBER ;
2905 l_msg_data VARCHAR2(2000);
2906 l_data VARCHAR2(2000);
2907 l_item_attr_name VARCHAR2(30);
2908 BEGIN
2909 IF nvl(p_msg_count,0) = 0 and p_msg_data is NULL THEN
2910 RETURN;
2911 Elsif nvl(p_msg_count,0) = 0 and p_msg_data is NOT NULL then
2912 l_data := FND_MESSAGE.get_string('PA',p_msg_data);
2913 wf_engine.SetItemAttrText
2914 ( itemtype => p_item_type
2915 , itemkey => p_item_key
2916 , aname => 'ERROR_MSG1'
2917 , avalue => l_data
2918 );
2919
2920
2921 RETURN;
2922 END IF;
2923
2924 IF p_msg_count = 1 THEN
2925 /* -- this is commented as p_msg_data is string or code cannot be determined
2926 -- so fnd_message.get_string is used instead of fnd_message.set encoded
2927 -- IF p_msg_data IS NOT NULL THEN
2928 -- FND_MESSAGE.SET_ENCODED (p_msg_data);
2929 -- l_data := FND_MESSAGE.GET;
2930 */
2931
2932 -- Added to fix the Bug 1563218
2933 If p_msg_count = 1 and p_msg_data is not NULL then
2934 l_data := FND_MESSAGE.get_string('PA',p_msg_data);
2935 End if;
2936
2937 wf_engine.SetItemAttrText
2938 ( itemtype => p_item_type
2939 , itemkey => p_item_key
2940 , aname => 'ERROR_MSG1'
2941 , avalue => l_data
2942 );
2943
2944 RETURN ;
2945 END IF;
2946
2947 IF p_msg_count > 1 THEN
2948 FOR i in 1..p_msg_count
2949 LOOP
2950 IF i > 5 THEN
2951 EXIT;
2952 END IF;
2953
2954 pa_interface_utils_pub.get_messages
2955 (p_encoded => FND_API.G_FALSE,
2956 p_msg_index => i,
2957 p_msg_count => p_msg_count ,
2958 p_msg_data => p_msg_data ,
2959 p_data => l_data,
2960 p_msg_index_out => l_msg_index_out );
2961
2962 -- Added to fix the Bug 1563218
2963 if l_data is NULL then
2964 l_data := FND_MESSAGE.get_string('PA',p_msg_data);
2965 end if;
2966
2967 l_item_attr_name := 'ERROR_MSG'||i;
2968 wf_engine.SetItemAttrText
2969 ( itemtype => p_item_type
2970 , itemkey => p_item_key
2971 , aname => l_item_attr_name
2972 , avalue => l_data
2973 );
2974 END LOOP;
2975 END IF;
2976
2977 EXCEPTION
2978 WHEN OTHERS THEN
2979 RAISE;
2980 END set_nf_error_msg_attr;
2981
2982 --
2983 -- PROCEDURE
2984 -- create_fte_sync_wf
2985 -- PURPOSE
2986 -- This procedure creates a wf process for termination of employee/contingent worker
2987 --
2988 -- HISTORY
2989 -- 27-MAR-207 kjai Created for Bug 5683340
2990 PROCEDURE create_fte_sync_wf
2991 (p_person_id IN PA_EMPLOYEES.PERSON_ID%TYPE
2992 ,p_wait_days IN NUMBER
2993 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2994 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2995 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2996 )
2997 IS
2998
2999 l_msg_index_out NUMBER;
3000 l_save_thresh NUMBER ;
3001
3002 l_item_type VARCHAR2(8):='PAXWFHRU';
3003
3004 l_item_key NUMBER ;
3005
3006 l_person_name VARCHAR2(240);
3007 l_return_status VARCHAR2(1);
3008
3009 l_err_code NUMBER := 0;
3010 l_err_stage VARCHAR2(2000);
3011 l_err_stack VARCHAR2(2000);
3012
3013 BEGIN
3014
3015 x_return_status := FND_API.G_RET_STS_SUCCESS;
3016
3017 -- Get a unique identifier for this specific workflow
3018 --
3019 SELECT pa_workflow_itemkey_s.nextval
3020 INTO l_item_key
3021 FROM dual;
3022
3023 -- Since this workflow needs to be executed in Non deferred mode,
3024 -- we need to change the threshold. So we save the current threshold which
3025 -- will be used later on to change it back to the current threshold.
3026 --
3027 l_save_thresh := wf_engine.threshold ;
3028
3029 --
3030 -- Set the threshold to 50 so that the process will not be deferred
3031 --
3032 wf_engine.threshold := 50 ;
3033
3034
3035 -- Create the appropriate process
3036 --
3037 wf_engine.CreateProcess( ItemType => l_item_type,
3038 ItemKey => l_item_key,
3039 process => 'TIMEOUT_TERMINATION_PROCESS' );
3040
3041
3042
3043 -- Initialize workflow item attributes with the parameter values
3044 --
3045 wf_engine.SetItemAttrText(itemtype => l_item_type,
3046 itemkey => l_item_key,
3047 aname => 'PROJECT_RESOURCE_ADMINISTRATOR',
3048 avalue => 'PASYSADMIN');
3049
3050 wf_engine.SetItemAttrNumber(itemtype => l_item_type,
3051 itemkey => l_item_key,
3052 aname => 'PERSON_ID',
3053 avalue => p_person_id);
3054
3055 wf_engine.SetItemAttrNumber(itemtype => l_item_type,
3056 itemkey => l_item_key,
3057 aname => 'WAIT_DAYS',
3058 avalue => p_wait_days);
3059
3060 --call the api to get person name
3061 pa_resource_utils.get_person_name(p_person_id => p_person_id,
3062 x_person_name => l_person_name,
3063 x_return_status => l_return_status);
3064
3065
3066 IF l_return_status = 'S' THEN
3067 --Set the wf name attribute for display of name , if any error occurs
3068 wf_engine.SetItemAttrText (itemtype => l_item_type,
3069 itemkey => l_item_key,
3070 aname => 'FULL_NAME_NEW',
3071 avalue => l_person_name);
3072
3073 END IF ;
3074
3075
3076
3077 -- Starting the work flow process and calling work flow api internaly
3078 --
3079 WF_ENGINE.StartProcess( itemtype => l_item_type,
3080 itemkey => l_item_key);
3081
3082 -- Insert to PA tables wf process information.
3083 -- This is required for displaying notifications on PA pages.
3084
3085 BEGIN
3086
3087 PA_WORKFLOW_UTILS.Insert_WF_Processes
3088 (p_wf_type_code => 'HR_CHANGE_MGMT'
3089 ,p_item_type => l_item_type
3090 ,p_item_key => l_item_key
3091 ,p_entity_key1 => to_char(p_person_id)
3092 ,p_entity_key2 => to_char(p_person_id)
3093 ,p_description => NULL
3094 ,p_err_code => l_err_code
3095 ,p_err_stage => l_err_stage
3096 ,p_err_stack => l_err_stack
3097 );
3098
3099 EXCEPTION
3100 WHEN OTHERS THEN
3101 null;
3102 END;
3103
3104
3105
3106 --Setting the original value
3107 wf_engine.threshold := l_save_thresh;
3108
3109
3110 EXCEPTION
3111 WHEN OTHERS THEN
3112
3113 x_return_status := FND_API.G_RET_STS_ERROR;
3114 x_msg_count := 1;
3115 x_msg_data := substr(SQLERRM,1,240);
3116 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_HR_UPDATE_PA_ENTITIES',
3117 p_procedure_name => 'create_fte_sync_wf');
3118 If x_msg_count = 1 THEN
3119 pa_interface_utils_pub.get_messages
3120 (p_encoded => FND_API.G_TRUE,
3121 p_msg_index => 1,
3122 p_msg_count => x_msg_count,
3123 p_msg_data => x_msg_data,
3124 p_data => x_msg_data,
3125 p_msg_index_out => l_msg_index_out );
3126 End If;
3127 RAISE ;
3128
3129 END create_fte_sync_wf;
3130
3131
3132 --
3133 -- PROCEDURE
3134 -- start_fte_sync_wf
3135 -- PURPOSE
3136 -- This procedure starts wf process for termination of employee/contingent worker
3137 --
3138 -- HISTORY
3139 -- 27-MAR-207 kjai Created for Bug 5683340
3140 PROCEDURE start_fte_sync_wf
3141 (itemtype IN VARCHAR2
3142 , itemkey IN VARCHAR2
3143 , actid IN NUMBER
3144 , funcmode IN VARCHAR2
3145 , resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3146 ) IS
3147
3148 l_person_id NUMBER;
3149
3150 l_msg_count NUMBER;
3151 l_msg_data VARCHAR(2000);
3152 l_return_status VARCHAR2(1);
3153 l_api_version_number NUMBER := 1.0;
3154 l_data VARCHAR2(2000);
3155 l_msg_index_out NUMBER;
3156
3157 l_resource_effective_end_date DATE ;
3158
3159 l_resource_id pa_resources.resource_id%TYPE ;
3160
3161 l_invol_term VARCHAR2(1);
3162
3163 l_savepoint BOOLEAN;
3164
3165 BEGIN
3166
3167 -- Get the workflow attribute values
3168 --
3169 l_person_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
3170 itemkey => itemkey,
3171 aname => 'PERSON_ID' );
3172
3173
3174 SAVEPOINT l_termination_change ;
3175 l_savepoint := true;
3176
3177 log_message('before calling PA_R_PROJECT_RESOURCES_PUB.CREATE_RESOURCE');
3178 PA_R_PROJECT_RESOURCES_PUB.CREATE_RESOURCE( p_api_version => 1.0,
3179 p_internal => 'Y',
3180 p_individual => 'Y',
3181 P_PERSON_ID => l_person_id,
3182 p_scheduled_member_flag => 'Y',
3183 P_PULL_TERM_RES => 'Y',
3184 x_return_status => l_return_status,
3185 x_msg_data => l_msg_data,
3186 x_msg_count => l_msg_count,
3187 x_resource_id => l_resource_id);
3188 log_message('after calling PA_R_PROJECT_RESOURCES_PUB.CREATE_RESOURCE, l_return_status: '||l_return_status);
3189
3190
3191
3192 IF l_return_status = 'S' THEN
3193 SELECT max(resource_effective_end_date)
3194 INTO l_resource_effective_end_date
3195 FROM pa_resources_denorm
3196 WHERE person_id = l_person_id;
3197
3198 log_message('before calling PA_HR_UPDATE_API.withdraw_cand_nominations, l_resource_effective_end_date: '||l_resource_effective_end_date);
3199 PA_HR_UPDATE_API.withdraw_cand_nominations
3200 ( p_person_id => l_person_id,
3201 p_effective_date => l_resource_effective_end_date,
3202 x_return_status => l_return_status,
3203 x_msg_data => l_msg_data,
3204 x_msg_count => l_msg_count);
3205 log_message('after calling PA_HR_UPDATE_API.withdraw_cand_nominations, l_return_status: '||l_return_status);
3206 END IF ;
3207
3208
3209 IF l_return_status = 'S' THEN
3210 log_message('before calling pa_resource_utils.set_fte_flag');
3211 pa_resource_utils.set_fte_flag(p_person_id => l_person_id,
3212 p_future_term_wf_flag => NULL,
3213 x_msg_data => l_msg_data,
3214 x_return_status => l_return_status,
3215 x_msg_count => l_msg_count) ;
3216 log_message('after calling pa_resource_utils.set_fte_flag, l_return_status: '||l_return_status);
3217 END IF ;
3218
3219
3220 IF l_return_status = 'S' THEN
3221 resultout := wf_engine.eng_completed||':'||'S';
3222 ELSIF l_return_status = 'E' THEN
3223
3224 -- Set any error messages
3225 --
3226 l_savepoint := false;
3227 ROLLBACK to l_termination_change ;
3228 set_nf_error_msg_attr(p_item_type => itemtype,
3229 p_item_key => itemkey,
3230 p_msg_count => l_msg_count,
3231 p_msg_data => l_msg_data);
3232 resultout := wf_engine.eng_completed||':'||'E';
3233
3234 ELSE
3235 --
3236 -- Set any error messages
3237 --
3238 l_savepoint := false;
3239 ROLLBACK to l_termination_change ;
3240 set_nf_error_msg_attr(p_item_type => itemtype,
3241 p_item_key => itemkey,
3242 p_msg_count => fnd_msg_pub.count_msg,
3243 p_msg_data => l_msg_data);
3244
3245 resultout := wf_engine.eng_completed||':'||'E';
3246
3247 END IF;
3248
3249 EXCEPTION
3250 WHEN OTHERS THEN
3251 log_message('Execption OTHERS, '||SQLERRM ||', '|| SQLCODE);
3252 wf_core.context('pa_hr_update_pa_entities',
3253 'Start_fte_sync_wf',
3254 itemtype,
3255 itemkey,
3256 to_char(actid),
3257 funcmode);
3258 IF (l_savepoint) THEN
3259 log_message('before rollback to l_termination_change');
3260 ROLLBACK to l_termination_change ;
3261 log_message('after rollback to l_termination_change');
3262 END IF;
3263
3264 IF l_msg_data is NULL and nvl(l_msg_count,0) = 0 then
3265 log_message('l_msg_data is NULL');
3266 wf_engine.SetItemAttrText( itemtype => itemtype
3267 , itemkey => itemkey
3268 , aname => 'ERROR_MSG1'
3269 , avalue => SQLCODE||SQLERRM
3270 );
3271 ELSE log_message('l_msg_data is not NULL');
3272 set_nf_error_msg_attr(p_item_type => itemtype,
3273 p_item_key => itemkey,
3274 p_msg_count => l_msg_count,
3275 p_msg_data => l_msg_data);
3276
3277 END IF ;
3278
3279 resultout := wf_engine.eng_completed||':'||'U';
3280 log_message('resultout: '||resultout);
3281 --RAISE;
3282
3283 END start_fte_sync_wf;
3284
3285 END ;