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