[Home] [Help]
PACKAGE BODY: APPS.HR_AMEUTIL_SS
Source
1 PACKAGE BODY HR_AMEUTIL_SS AS
2 /* $Header: hrameutlss.pkb 120.13 2011/08/10 08:55:43 asatulur ship $ */
3
4 -- Package Variables
5 --
6 g_package constant varchar2(14) := 'hr_ameutil_ss.';
7 g_debug constant boolean := hr_utility.debug_enabled;
8
9
10
11
12 -------------------------------------------------------------------------------
13 --------- function get_item_type --------------------------------------------
14
15 ---------- private function to get item type for current transaction ---------
16 -------------------------------------------------------------------------------
17 function get_item_type
18 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
19 return varchar2 is
20 c_item_type varchar2(50);
21
22 begin
23
24 begin
25 if g_debug then
26 hr_utility.set_location('querying hr_api_transactions.item_type for p_transaction_id:'||p_transaction_id, 2);
27 end if;
28 select t.item_type
29 into c_item_type
30 from hr_api_transactions t
31 where transaction_id=get_item_type.p_transaction_id;
32 exception
33 when no_data_found then
34 -- get the data from the steps
35 if g_debug then
36 hr_utility.set_location('querying hr_api_transaction_steps.item_type for p_transaction_id:'||p_transaction_id, 2);
37 end if;
38 select ts.item_type
39 into get_item_type.c_item_type
40 from hr_api_transaction_steps ts
41 where ts.transaction_id=get_item_type.p_transaction_id
42 and ts.item_type is not null and rownum <=1;
43 end;
44
45 return c_item_type;
46 EXCEPTION
47 WHEN OTHERS THEN
48 WF_CORE.CONTEXT(g_package,'.get_item_type',p_transaction_id);
49 RAISE;
50
51 end get_item_type;
52
53
54
55 -------------------------------------------------------------------------------
56 --------- function get_item_key --------------------------------------------
57 ---------- private function to get item key for current transaction ---------
58 -------------------------------------------------------------------------------
59
60 function get_item_key
61 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
62 return varchar2 is
63 c_item_key varchar2(50);
64
65 begin
66
67 begin
68 if g_debug then
69 hr_utility.set_location('querying hr_api_transactions.item_type for p_transaction_id:'||p_transaction_id, 2);
70 end if;
71 select t.item_key
72 into get_item_key.c_item_key
73 from hr_api_transactions t
74 where transaction_id=get_item_key.p_transaction_id;
75 exception
76 when no_data_found then
77 -- get the data from the steps
78 if g_debug then
79 hr_utility.set_location('querying hr_api_transaction_steps.item_type for p_transaction_id:'||p_transaction_id, 2);
80 end if;
81 select ts.item_key
82 into get_item_key.c_item_key
83 from hr_api_transaction_steps ts
84 where ts.transaction_id=get_item_key.p_transaction_id
85 and ts.item_type is not null and rownum <=1;
86 end;
87
88 return get_item_key.c_item_key;
89 EXCEPTION
90 WHEN OTHERS THEN
91 WF_CORE.CONTEXT(g_package,'.get_item_key',p_transaction_id);
92 RAISE;
93
94 end get_item_key;
95
96 function get_process_name
97 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
98 return varchar2 is
99
100 c_process_name varchar2(100);
101 c_item_type varchar2(50);
102 c_item_key varchar2(100);
103
104 begin
105
106 c_item_type := get_item_type(p_transaction_id);
107 c_item_key := get_item_key(p_transaction_id);
108
109 select t.process_name
110 into get_process_name.c_process_name
111 from hr_api_transactions t
112 where transaction_id=get_process_name.p_transaction_id;
113
114 return c_process_name;
115 EXCEPTION
116 WHEN OTHERS THEN
117 WF_CORE.CONTEXT(g_package,'.get_process_name',c_item_type,c_item_key);
118 RAISE;
119 end get_process_name ;
120
121 ------------------------------------------------------------------
122 -- Name: get_transaction_category
123 -- Desc: Derive the category of transaction
124 -- Params: transaction_step_id
125 -- Returns: varchar2
126 ------------------------------------------------------------------
127 function get_transaction_category (
128 p_transaction_step_id IN hr_api_transaction_steps.transaction_step_id%TYPE)
129 return varchar2 is
130
131 -- local variables
132 lv_procedure_name constant varchar2(24) := 'get_transaction_category';
133 l_transaction_category varchar2(50);
134 l_category_undefined constant varchar2(5) :='OTHER';
135
136 BEGIN
137 hr_utility.set_location(lv_procedure_name,1);
138 if(hr_utility.debug_enabled) then
139 -- write debug statements
140 hr_utility.set_location('Entered'||lv_procedure_name||'with step_id:'||p_transaction_step_id, 2);
141 end if;
142
143 BEGIN
144 select decode(hats.api_name,
145 'BEN_PROCESS_COMPENSATION_W.PROCESS_API','BENEFITS',
146 'HR_APPLY_FOR_JOB_APP_WEB.PROCESS_API','BENEFITS',
147 'HR_ASSIGNMENT_COMMON_SAVE_WEB.PROCESS_API',l_category_undefined,
148 'HR_BASIC_DETAILS_WEB.PROCESS_API',l_category_undefined,
149 'HR_CAED_SS.PROCESS_API',l_category_undefined,
150 'HR_CCMGR_SS.PROCESS_API',l_category_undefined,
151 'HR_COMP_PROFILE_SS.PROCESS_API',l_category_undefined,
152 'HR_COMP_REVIEW_WEB_SS.PROCESS_API',l_category_undefined,
153 'HR_EMP_ADDRESS_WEB.PROCESS_API',l_category_undefined,
154 'HR_EMP_CONTACT_WEB.PROCESS_API',l_category_undefined,
155 'HR_EMP_MARITAL_WEB.PROCESS_API',l_category_undefined,
156 'HR_LOA_SS.PROCESS_API',l_category_undefined,
157 'HR_PAY_RATE_SS.PROCESS_API','SALARY',
158 'HR_PAY_RATE_SS.PROCESS_API_JAVA','SALARY',
159 'PER_SSHR_CHANGE_PAY.PROCESS_API','SALARY',
160 'PER_SSHR_CHANGE_PAY.PROCESS_API_JAVA','SALARY',
161 'HR_PERCMPTNCE_REVIEW_WEB.PROCESS_API',l_category_undefined,
162 'HR_PROCESS_ADDRESS_SS.PROCESS_API',l_category_undefined,
163 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API','ASSIGNMENT',
164 'HR_PROCESS_CONTACT_SS.PROCESS_API',l_category_undefined,
165 'HR_PROCESS_CONTACT_SS.PROCESS_CREATE_CONTACT_API',l_category_undefined,
166 'HR_PROCESS_EIT_SS.PROCESS_API',l_category_undefined,
167 'HR_PROCESS_PERSON_SS.PROCESS_API',l_category_undefined,
168 'HR_PROCESS_PHONE_NUMBERS_SS.PROCESS_API',l_category_undefined,
169 'HR_PROCESS_SIT_SS.PROCESS_API',l_category_undefined,
170 'HR_PROF_UTIL_WEB.PROCESS_API',l_category_undefined,
171 'HR_QUA_AWARDS_UTIL_SS.PROCESS_API',l_category_undefined,
172 'HR_SALARY_WEB.PROCESS_API','SALARY',
173 'HR_SALARY_WEB.process_API','SALARY',
174 'HR_SIT_WEB.PROCESS_API',l_category_undefined,
175 'HR_SUPERVISOR_SS.PROCESS_API','TRANSFER',
176 'HR_SUPERVISOR_WEB.PROCESS_API','TRANSFER',
177 'HR_SUPERVISOR_WEB.process_API','TRANSFER',
178 'HR_TERMINATION_SS.PROCESS_API','TERMINATION',
179 'HR_TERMINATION_SS.PROCESS_SAVE','TERMINATION',
180 'HR_TERMINATION_WEB.PROCESS_API','TERMINATION',
181 'PAY_PPMV4_SS.PROCESS_API','PAYROLL',
182 'PAY_US_OTF_UTIL_WEB.UPDATE_W4_INFO','PAYROLL',
183 'PAY_US_WEB_W4.UPDATE_W4_INFO','PAYROLL',
184 'PQH_PROCESS_ACADEMIC_RANK.PROCESS_API',l_category_undefined,
185 'PQH_PROCESS_EMP_REVIEW.PROCESS_API',l_category_undefined,
186 'PQH_PROCESS_TENURE_STATUS.PROCESS_API',l_category_undefined,
187 l_category_undefined)
188 into l_transaction_category
189 from hr_api_transaction_steps hats
190 where hats.transaction_step_id = p_transaction_step_id;
191
192 END;
193
194 if(hr_utility.debug_enabled) then
195 -- write debug statements
196 hr_utility.set_location('Leaving '||lv_procedure_name||'with p_transaction_step_id:'||p_transaction_step_id, 10);
197 end if;
198
199 return l_transaction_category;
200 EXCEPTION
201 WHEN OTHERS THEN
202 raise;
203 end;
204
205 Function isHrHelpDeskAgent
206 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
207 return varchar2 IS
208
209 p_item_key hr_api_transactions.item_key%TYPE;
210 p_item_type hr_api_transactions.item_type%TYPE;
211 p_hrhd_val varchar2(1);
212
213 Begin
214 p_item_type := get_item_type(p_transaction_id);
215 p_item_key := get_item_key(p_transaction_id);
216
217 SELECT NVL(text_value,'N') into p_hrhd_val
218 FROM wf_item_attribute_values
219 where item_type= p_item_type and item_key = p_item_key
220 and NAME = 'IS_HR_HELPDESK_AGENT';
221
222 return p_hrhd_val;
223
224 exception
225 when others then
226 return 'N';
227
228 End isHrHelpDeskAgent;
229
230
231 FUNCTION get_requestor_person_id
232 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
233 return number is
234 -- local variables
235 lv_procedure_name constant varchar2(23) := 'get_requestor_person_id';
236 ln_requestor_person_id number;
237 lv_transaction_ref_table hr_api_transactions.transaction_ref_table%type;
238 lv_transaction_ref_id hr_api_transactions.transaction_ref_id%type;
239
240 p_hrhd varchar2(1);
241
242 BEGIN
243 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
244 if(hr_utility.debug_enabled) then
245 -- write debug statements
246 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
247 end if;
248
249 p_hrhd := isHrHelpDeskAgent(p_transaction_id);
250
251 if p_hrhd = 'Y' then
252
253 -- get the selected person_id from hr_api_transactions
254 -- this would be for HR helpdesk approvals.
255 begin
256 select selected_person_id
257 into ln_requestor_person_id
258 from hr_api_transactions
259 where transaction_id=p_transaction_id;
260 exception
261 when others then
262 raise;
263 end;
264
265 else
266
267 -- get the creator person_id from hr_api_transactions
268 -- this would be the default for all SSHR approvals.
269
270 begin
271 select creator_person_id
272 into ln_requestor_person_id
273 from hr_api_transactions
274 where transaction_id=p_transaction_id;
275 exception
276 when others then
277 raise;
278 end;
279
280 end if;
281
282 -- if the transaction is for appraisal we need go through
283 -- Main Appraiser chain for approvals.
284 begin
285 select transaction_ref_table,transaction_ref_id
286 into lv_transaction_ref_table,lv_transaction_ref_id
287 from hr_api_transactions
288 where transaction_id=p_transaction_id;
289
290 if(lv_transaction_ref_table='PER_APPRAISALS') then
291 begin
292 select main_appraiser_id
293 into ln_requestor_person_id
294 from per_appraisals
295 where appraisal_id=lv_transaction_ref_id;
296 exception
297 when others then
298 -- do not raise, return
299 null;
300 end;
301 end if;
302 exception
303 when others then
304 hr_utility.trace(' exception in checking the hr_api_transactions.transaction_ref_table:'||
305 lv_transaction_ref_table||' : ' || sqlerrm);
306 -- just log the message no need to raise it
307 end;
308
309 if(hr_utility.debug_enabled) then
310 -- write debug statements
311 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
312 end if;
313
314 return fnd_number.number_to_canonical(ln_requestor_person_id);
315 EXCEPTION
316
317 WHEN OTHERS THEN
318 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
319 RAISE;
320
321 END get_requestor_person_id;
322
323 function get_sel_person_assignment_id
324 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
325 return number is
326 c_assignment_id number;
327 c_item_type varchar2(50);
328 c_item_key varchar2(100);
329
330 begin
331
332 c_item_type := get_item_type(p_transaction_id);
333 c_item_key := get_item_key(p_transaction_id);
334
335 if (c_item_key is not null) then
336 c_assignment_id := wf_engine.GetItemAttrNumber(itemtype => c_item_type ,
337 itemkey => c_item_key,
338 aname => 'CURRENT_ASSIGNMENT_ID',
339 ignore_notfound => true);
340
341 else
342 select assignment_id into c_assignment_id from hr_api_transactions
343 where transaction_id = p_transaction_id;
344 end if;
345
346 return fnd_number.number_to_canonical(c_assignment_id);
347 EXCEPTION
348 WHEN OTHERS THEN
349 WF_CORE.CONTEXT(g_package,'.get_sel_person_assignment_id',c_item_type,c_item_key);
350 RAISE;
351
352
353 end get_sel_person_assignment_id ;
354
355
356 FUNCTION get_payrate_step_id
357 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
358 return number is
359 -- local variables
360 lv_procedure_name constant varchar2(19) := 'get_payrate_step_id';
361 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
362 BEGIN
363 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
364 if(hr_utility.debug_enabled) then
365 -- write debug statements
366 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
367 end if;
368 begin
369 select transaction_step_id
370 into ln_step_id
371 from hr_api_transaction_steps
372 where hr_api_transaction_steps.transaction_id=p_transaction_id
373 -- and hr_api_transaction_steps.api_name='HR_PAY_RATE_SS.PROCESS_API';
374 and hr_api_transaction_steps.api_name in ('PER_SSHR_CHANGE_PAY.PROCESS_API', 'HR_PAY_RATE_SS.PROCESS_API');
375 exception
376 when no_data_found then
377 return null;
378 when others then
379 raise;
380 end;
381 if(hr_utility.debug_enabled) then
382 -- write debug statements
383 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
384 end if;
385 return fnd_number.number_to_canonical(ln_step_id);
386 EXCEPTION
387
388 WHEN OTHERS THEN
389 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
390 RAISE;
391
392 END get_payrate_step_id;
393
394
395 FUNCTION get_assignment_step_id
396 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
397 return number is
398 -- local variables
399 lv_procedure_name constant varchar2(22) := 'get_assignment_step_id';
400 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
401 BEGIN
402 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
403 if(hr_utility.debug_enabled) then
404 -- write debug statements
405 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
406 end if;
407 begin
408 select transaction_step_id
409 into ln_step_id
410 from hr_api_transaction_steps
411 where hr_api_transaction_steps.transaction_id=p_transaction_id
412 and hr_api_transaction_steps.api_name='HR_PROCESS_ASSIGNMENT_SS.PROCESS_API';
413 return ln_step_id;
414 exception
415 when no_data_found then
416 return null;
417 when others then
418 raise;
419 end;
420 if(hr_utility.debug_enabled) then
421 -- write debug statements
422 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
423 end if;
424
425 return fnd_number.number_to_canonical(ln_step_id);
426 EXCEPTION
427
428 WHEN OTHERS THEN
429 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
430 RAISE;
431
432 END get_assignment_step_id;
433
434
435 FUNCTION get_supeversior_Chg_step_id
436 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
437 return number is
438 -- local variables
439 lv_procedure_name constant varchar2(27) := 'get_supeversior_Chg_step_id';
440 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
441 BEGIN
442 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
443 if(hr_utility.debug_enabled) then
444 -- write debug statements
445 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
446 end if;
447 begin
448 select transaction_step_id
449 into ln_step_id
450 from hr_api_transaction_steps
451 where hr_api_transaction_steps.transaction_id=p_transaction_id
452 and hr_api_transaction_steps.api_name='HR_SUPERVISOR_SS.PROCESS_API';
453 exception
454 when no_data_found then
455 return null;
456 when others then
457 raise;
458 end;
459 if(hr_utility.debug_enabled) then
460 -- write debug statements
461 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
462 end if;
463 return fnd_number.number_to_canonical(ln_step_id);
464 EXCEPTION
465
466 WHEN OTHERS THEN
467 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
468 RAISE;
469
470 END get_supeversior_Chg_step_id;
471
472
473 FUNCTION get_loa_step_id
474 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
475 return number is
476 -- local variables
477 lv_procedure_name constant varchar2(15) default 'get_loa_step_id';
478 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
479 BEGIN
480 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
481 if(hr_utility.debug_enabled) then
482 -- write debug statements
483 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
484 end if;
485 begin
486 select transaction_step_id
487 into ln_step_id
488 from hr_api_transaction_steps
489 where hr_api_transaction_steps.transaction_id=p_transaction_id
490 and hr_api_transaction_steps.api_name='HR_PERSON_ABSENCE_SWI.PROCESS_API';
491 exception
492 when no_data_found then
493 return null;
494 when others then
495 raise;
496 end;
497 if(hr_utility.debug_enabled) then
498 -- write debug statements
499 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
500 end if;
501 return fnd_number.number_to_canonical(ln_step_id);
502 EXCEPTION
503
504 WHEN OTHERS THEN
505 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
506 RAISE;
507
508 END get_loa_step_id;
509
510
511 FUNCTION get_termination_step_id
512 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
513 return number is
514 -- local variables
515 lv_procedure_name constant varchar2(23) := 'get_termination_step_id';
516 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
517 BEGIN
518 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
519 if(hr_utility.debug_enabled) then
520 -- write debug statements
521 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
522 end if;
523 begin
524 select transaction_step_id
525 into ln_step_id
526 from hr_api_transaction_steps
527 where hr_api_transaction_steps.transaction_id=p_transaction_id
528 and hr_api_transaction_steps.api_name='HR_TERMINATION_SS.PROCESS_API';
529 exception
530 when no_data_found then
531 return null;
532 when others then
533 raise;
534 end;
535 if(hr_utility.debug_enabled) then
536 -- write debug statements
537 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
538 end if;
539 return fnd_number.number_to_canonical(ln_step_id);
540 EXCEPTION
541
542 WHEN OTHERS THEN
543 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
544 RAISE;
545
546 END get_termination_step_id;
547
548 FUNCTION isChangePay
549 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
550 return varchar2 is
551 -- local variables
552 lv_procedure_name constant varchar2(11) := 'isChangePay';
553 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
554 lv_status varchar2(10);
555 BEGIN
556 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
557 if(hr_utility.debug_enabled) then
558 -- write debug statements
559 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
560 end if;
561
562 ln_step_id :=get_payrate_step_id(p_transaction_id);
563
564 if(ln_step_id is not null) then
565 lv_status := ame_util.booleanAttributeTrue;
566 else
567 lv_status := ame_util.booleanAttributeFalse;
568 end if;
569
570 if(hr_utility.debug_enabled) then
571 -- write debug statements
572 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
573 end if;
574 return lv_status;
575 EXCEPTION
576
577 WHEN OTHERS THEN
578 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
579 RAISE;
580
581 END isChangePay;
582
583
584 FUNCTION isAssignmentChange
585 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
586 return varchar2 is
587 -- local variables
588 lv_procedure_name constant varchar2(18) := 'isAssignmentChange';
589 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
590 lv_status varchar2(10);
591 BEGIN
592 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
593 if(hr_utility.debug_enabled) then
594 -- write debug statements
595 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
596 end if;
597
598 ln_step_id :=get_assignment_step_id(p_transaction_id);
599 if(ln_step_id is not null) then
600 lv_status := ame_util.booleanAttributeTrue;
601 else
602 lv_status := ame_util.booleanAttributeFalse;
603 end if;
604
605 if(hr_utility.debug_enabled) then
606 -- write debug statements
607 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
608 end if;
609 return lv_status;
610 EXCEPTION
611
612 WHEN OTHERS THEN
613 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
614 RAISE;
615
616 END isAssignmentChange;
617
618
619 FUNCTION isSupervisorChange
620 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
621 return varchar2 is
622 -- local variables
623 lv_procedure_name constant varchar2(18) := 'isSupervisorChange';
624 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
625 lv_status varchar2(10);
626 BEGIN
627 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
628 if(hr_utility.debug_enabled) then
629 -- write debug statements
630 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
631 end if;
632
633 ln_step_id := get_supeversior_Chg_step_id(p_transaction_id);
634 if(ln_step_id is not null) then
635 lv_status := ame_util.booleanAttributeTrue;
636 else
637 lv_status := ame_util.booleanAttributeFalse;
638 end if;
639
640 if(hr_utility.debug_enabled) then
641 -- write debug statements
642 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
643 end if;
644
645 return lv_status;
646 EXCEPTION
647
648 WHEN OTHERS THEN
649 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
650 RAISE;
651
652 END isSupervisorChange;
653
654
655
656 FUNCTION isLOAChange
657 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
658 return varchar2 is
659 -- local variables
660 lv_procedure_name constant varchar2(11) := 'isLOAChange';
661 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
662 lv_status varchar2(10);
663 BEGIN
664 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
665 if(hr_utility.debug_enabled) then
666 -- write debug statements
667 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
668 end if;
669
670 ln_step_id := get_loa_step_id(p_transaction_id);
671 if(ln_step_id is not null) then
672 lv_status := ame_util.booleanAttributeTrue;
673 else
674 lv_status := ame_util.booleanAttributeFalse;
675 end if;
676
677 if(hr_utility.debug_enabled) then
678 -- write debug statements
679 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
680 end if;
681
682 return lv_status;
683 EXCEPTION
684
685 WHEN OTHERS THEN
686 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
687 RAISE;
688
689 END isLOAChange;
690
691
692
693 FUNCTION isTermination
694 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
695 return varchar2 is
696 -- local variables
697 lv_procedure_name constant varchar2(18) := 'isAssignmentChange';
698 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
699 lv_status varchar2(10);
700 BEGIN
701 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
702 if(hr_utility.debug_enabled) then
703 -- write debug statements
704 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
705 end if;
706
707 ln_step_id :=get_termination_step_id(p_transaction_id);
708 if(ln_step_id is not null) then
709 lv_status := ame_util.booleanAttributeTrue;
710 else
711 lv_status := ame_util.booleanAttributeFalse;
712 end if;
713
714 if(hr_utility.debug_enabled) then
715 -- write debug statements
716 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
717 end if;
718 return lv_status;
719 EXCEPTION
720
721 WHEN OTHERS THEN
722 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
723 RAISE;
724
725 END isTermination;
726
727
728 FUNCTION get_salary_percent_change
729 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
730 return number is
731 -- local variables
732 lv_procedure_name constant varchar2(25) := 'get_salary_percent_change';
733 ln_salary_percent_change number default null;
734 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
735 ln_no_of_components NUMBER ;
736 lv_param_name hr_api_transaction_values.varchar2_value%type;
737 p_sum_percentage per_pay_transactions.change_percentage%type;
738 BEGIN
739 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
740 if(hr_utility.debug_enabled) then
741 -- write debug statements
742 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
743 end if;
744
745 -- get payrate step id
746 ln_step_id := get_payrate_step_id(p_transaction_id);
747
748 if(ln_step_id is null) then
749 ln_salary_percent_change := null;
750 else
751 -- fix for bug 4148680
752 if (is_new_change_pay (ln_step_id)= ame_util.booleanAttributeFalse)then
753 if(hr_transaction_api.get_varchar2_value(ln_step_id,'p_multiple_components')='Y') then
754 -- get number of components P_NO_OF_COMPONENTS
755
756 ln_no_of_components :=hr_transaction_api.get_number_value
757 (p_transaction_step_id => ln_step_id,
758 p_name =>'P_NO_OF_COMPONENTS');
759 ln_salary_percent_change:= 0;
760 FOR i in 1..ln_no_of_components
761 LOOP
762 lv_param_name := 'p_change_percent'||i;
763 ln_salary_percent_change:= ln_salary_percent_change + fnd_number.number_to_canonical(hr_transaction_api.get_number_value
764 (p_transaction_step_id => ln_step_id,
765 p_name =>lv_param_name));
766 end loop;
767 else
768
769 ln_salary_percent_change:= hr_transaction_api.get_number_value
770 (p_transaction_step_id => ln_step_id,
771 p_name =>'p_change_percent');
772 end if;
773
774 --changes made by schowdhu Bug#6919576
775 else
776 begin
777 select sum(change_percentage)
778 into p_sum_percentage
779 from per_pay_transactions ppt
780 where parent_pay_transaction_id is null
781 and pay_proposal_id is null ---8847573
782 and ppt.transaction_step_id = ln_step_id;
783 exception
784 when no_data_found then
785 return null;
786 when others then
787 raise;
788 end;
789
790 ln_salary_percent_change := p_sum_percentage;
791
792 end if;
793 end if;
794 return ln_salary_percent_change;
795
796
797 if(hr_utility.debug_enabled) then
798 -- write debug statements
799 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
800 end if;
801 EXCEPTION
802
803 WHEN OTHERS THEN
804 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
805 RAISE;
806
807 END get_salary_percent_change;
808
809
810 FUNCTION get_salary_amount_change
811 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
812 return varchar2 is
813 -- local variables
814 lv_procedure_name constant varchar2(24) := 'get_salary_amount_change';
815 ln_salary_amt_change number default null;
816 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
817 ln_no_of_components NUMBER ;
818 lv_param_name hr_api_transaction_values.varchar2_value%type;
819 p_sum_amount per_pay_transactions.change_amount_n%type;
820 BEGIN
821 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
822 if(hr_utility.debug_enabled) then
823 -- write debug statements
824 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
825 end if;
826
827 -- get the payrate step id
828 ln_step_id := get_payrate_step_id(p_transaction_id);
829
830 if(ln_step_id is null) then
831 ln_salary_amt_change := null;
832 else
833 if (is_new_change_pay(ln_step_id) = ame_util.booleanAttributeFalse) then
834 -- fix for bug 4148680
835 if(hr_transaction_api.get_varchar2_value(ln_step_id,'p_multiple_components')='Y') then
836 -- get number of components P_NO_OF_COMPONENTS
837 ln_no_of_components :=hr_transaction_api.get_number_value
838 (p_transaction_step_id => ln_step_id,
839 p_name =>'P_NO_OF_COMPONENTS');
840 ln_salary_amt_change:= 0;
841 FOR i in 1..ln_no_of_components
842 LOOP
843 lv_param_name := 'p_change_amount'||i;
844 ln_salary_amt_change:= ln_salary_amt_change + fnd_number.number_to_canonical(hr_transaction_api.get_number_value
845 (p_transaction_step_id => ln_step_id,
846 p_name =>lv_param_name));
847 end loop;
848 else
849 ln_salary_amt_change:= fnd_number.number_to_canonical(hr_transaction_api.get_number_value
850 (p_transaction_step_id => ln_step_id,
851 p_name =>'p_change_amount'));
852 end if;
853 --changes made by schowdhu Bug#6919576
854 else
855 begin
856 select sum(change_amount_n)
857 into p_sum_amount
858 from per_pay_transactions ppt
859 where parent_pay_transaction_id is null
860 and pay_proposal_id is null ---8847573
861 and ppt.transaction_step_id = ln_step_id;
862 exception
863 when no_data_found then
864 return null;
865 when others then
866 raise;
867 end;
868 ln_salary_amt_change := p_sum_amount;
869 end if;
870 end if;
871 return ln_salary_amt_change;
872
873 if(hr_utility.debug_enabled) then
874 -- write debug statements
875 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
876 end if;
877 EXCEPTION
878
879 WHEN OTHERS THEN
880 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
881 RAISE;
882
883 END get_salary_amount_change;
884
885 --function added by schowdhu Bug#6919576
886
887 FUNCTION is_new_change_pay(p_transaction_step_id IN hr_api_transaction_steps.transaction_step_id%TYPE)
888 return varchar2 is
889 -- local variables
890 lv_procedure_name constant varchar2(19) := 'is_new_change_pay';
891 ln_api_name hr_api_transaction_steps.api_name%type;
892 lv_status varchar2(10);
893 BEGIN
894 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
895 if(hr_utility.debug_enabled) then
896 -- write debug statements
897 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_step_id:'||p_transaction_step_id, 2);
898 end if;
899 begin
900 select api_name
901 into ln_api_name
902 from hr_api_transaction_steps
903 where hr_api_transaction_steps.transaction_step_id=p_transaction_step_id;
904 exception
905 when no_data_found then
906 return null;
907 when others then
908 raise;
909 end;
910 if(hr_utility.debug_enabled) then
911 -- write debug statements
912 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_step__id:'||p_transaction_step_id, 10);
913 end if;
914 if (ln_api_name='PER_SSHR_CHANGE_PAY.PROCESS_API') then
915 lv_status := ame_util.booleanAttributeTrue;
916 else
917 lv_status := ame_util.booleanAttributeFalse;
918 end if;
919 return lv_status;
920 EXCEPTION
921
922 WHEN OTHERS THEN
923 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_step_id);
924 RAISE;
925 END is_new_change_pay;
926
927 FUNCTION get_transaction_init_date
928 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
929 return varchar2 is
930 -- local variables
931 lv_procedure_name constant varchar2(25) := 'get_transaction_init_date';
932 lv_creation_date_string varchar2(30) default null;
933 BEGIN
934 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
935 if(hr_utility.debug_enabled) then
936 -- write debug statements
937 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
938 end if;
939 begin
940 select ame_util.versiondatetostring(creation_date)
941 into lv_creation_date_string
942 from hr_api_transactions
943 where transaction_id=p_transaction_id;
944 exception
945 when others then
946 raise;
947 end;
948
949 if(hr_utility.debug_enabled) then
950 -- write debug statements
951 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
952 end if;
953 return lv_creation_date_string;
954 EXCEPTION
955
956 WHEN OTHERS THEN
957 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
958 RAISE;
959
960 END get_transaction_init_date;
961
962
963
964 FUNCTION get_transaction_effective_date
965 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
966 return varchar2 is
967 -- local variables
968 lv_procedure_name constant varchar2(30) := 'get_transaction_effective_date';
969 lv_effective_date_string varchar2(30) default null;
970 lv_item_type varchar2(50);
971 lv_item_key varchar2(100);
972 BEGIN
973 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
974 if(hr_utility.debug_enabled) then
975 -- write debug statements
976 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
977 end if;
978
979
980 lv_item_type := get_item_type(p_transaction_id);
981 lv_item_key := get_item_key(p_transaction_id);
982
983 if(lv_item_key is not null) then
984 lv_effective_date_string := ame_util.versiondatetostring(wf_engine.GetItemAttrDate(itemtype => lv_item_type ,
985 itemkey => lv_item_key,
986 aname => 'CURRENT_EFFECTIVE_DATE',
987 ignore_notfound => true));
988
989 else
990 select transaction_effective_date into lv_effective_date_string from hr_api_transactions
991 where transaction_id = p_transaction_id;
992 end if;
993
994 if(hr_utility.debug_enabled) then
995 -- write debug statements
996 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
997 end if;
998
999 return lv_effective_date_string;
1000
1001 EXCEPTION
1002
1003 WHEN OTHERS THEN
1004 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1005 RAISE;
1006
1007 END get_transaction_effective_date;
1008
1009
1010
1011 FUNCTION get_sel_person_prop_sup_id
1012 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1013 return varchar2 is
1014 -- local variables
1015 lv_procedure_name constant varchar2(26) := 'get_sel_person_prop_sup_id';
1016 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
1017 ln_sel_person_prop_sup_id varchar2(10);
1018 ln_new_sel_person_prop_sup_id number;
1019 ln_old_sel_person_prop_sup_id number;
1020
1021 BEGIN
1022 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1023 if(hr_utility.debug_enabled) then
1024 -- write debug statements
1025 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1026 end if;
1027 -- get step id
1028 ln_step_id:= get_supeversior_Chg_step_id(p_transaction_id);
1029 if(ln_step_id is not null) then
1030 ln_new_sel_person_prop_sup_id :=
1031 fnd_number.number_to_canonical(hr_transaction_api.get_number_value
1032 (p_transaction_step_id => ln_step_id,
1033 p_name =>'p_selected_person_sup_id'));
1034 ln_old_sel_person_prop_sup_id :=
1035 fnd_number.number_to_canonical(hr_transaction_api.get_number_value
1036 (p_transaction_step_id => ln_step_id,
1037 p_name =>'p_selected_person_old_sup_id'));
1038
1039 if(nvl(ln_new_sel_person_prop_sup_id,-111)<>nvl(ln_old_sel_person_prop_sup_id,-111)) then
1040 ln_sel_person_prop_sup_id:=ln_new_sel_person_prop_sup_id;
1041 else
1042 ln_sel_person_prop_sup_id:= null;
1043 end if;
1044 else
1045 ln_sel_person_prop_sup_id := null;
1046 end if;
1047
1048
1049 if(hr_utility.debug_enabled) then
1050 -- write debug statements
1051 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1052 end if;
1053 return ln_sel_person_prop_sup_id;
1054
1055 EXCEPTION
1056
1057 WHEN OTHERS THEN
1058 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1059 RAISE;
1060
1061 END get_sel_person_prop_sup_id;
1062
1063 FUNCTION get_selected_person_id
1064 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1065 return varchar2 is
1066 -- local variables
1067 lv_procedure_name constant varchar2(22) := 'get_selected_person_id';
1068 lv_selected_person_id varchar2(10);
1069 lv_item_type varchar2(50);
1070 lv_item_key varchar2(100);
1071 BEGIN
1072 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1073 if(hr_utility.debug_enabled) then
1074 -- write debug statements
1075 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1076 end if;
1077
1078 lv_item_type := get_item_type(p_transaction_id);
1079 lv_item_key := get_item_key(p_transaction_id);
1080
1081 -- CURRENT_PERSON_ID
1082 if ( lv_item_key is not NULL) then
1083 lv_selected_person_id := wf_engine.GetItemAttrNumber(itemtype => lv_item_type ,
1084 itemkey => lv_item_key,
1085 aname => 'CURRENT_PERSON_ID',
1086 ignore_notfound => true);
1087
1088 else
1089 select selected_person_id into lv_selected_person_id from hr_api_transactions
1090 where transaction_id = p_transaction_id;
1091 end if;
1092
1093 if(hr_utility.debug_enabled) then
1094 -- write debug statements
1095 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1096 end if;
1097
1098 return fnd_number.number_to_canonical(lv_selected_person_id);
1099 EXCEPTION
1100
1101 WHEN OTHERS THEN
1102 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1103 RAISE;
1104
1105 END get_selected_person_id;
1106
1107 FUNCTION get_proposed_job_id
1108 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1109 return varchar2 is
1110 -- local variables
1111 lv_procedure_name constant varchar2(19) := 'get_proposed_job_id';
1112 lv_job_id varchar2(15);
1113 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
1114 ln_new_job_id number;
1115 ln_orginal_job_id number;
1116 BEGIN
1117 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1118 if(hr_utility.debug_enabled) then
1119 -- write debug statements
1120 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1121 end if;
1122
1123 ln_step_id := get_assignment_step_id(p_transaction_id);
1124 if(ln_step_id is not null) then
1125 -- fix for bug 4145754
1126 ln_new_job_id := fnd_number.number_to_canonical(hr_transaction_api.get_number_value
1127 (p_transaction_step_id => ln_step_id
1128 ,p_name => 'P_JOB_ID'));
1129
1130 ln_orginal_job_id := fnd_number.number_to_canonical(hr_transaction_api.get_original_number_value
1131 (p_transaction_step_id => ln_step_id
1132 ,p_name => 'P_JOB_ID'));
1133 if(nvl(ln_new_job_id,-111)<>nvl(ln_orginal_job_id,-111)) then
1134 lv_job_id:=ln_new_job_id;
1135 else
1136 lv_job_id:= null;
1137 end if;
1138 else
1139 lv_job_id:= null;
1140 end if;
1141
1142 if(hr_utility.debug_enabled) then
1143 -- write debug statements
1144 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1145 end if;
1146
1147 return lv_job_id;
1148 EXCEPTION
1149
1150 WHEN OTHERS THEN
1151 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1152 RAISE;
1153
1154 END get_proposed_job_id;
1155
1156
1157 FUNCTION get_proposed_position_id
1158 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1159 return varchar2 is
1160 -- local variables
1161 lv_procedure_name constant varchar2(24) := 'get_proposed_position_id';
1162 lv_position_id varchar2(15);
1163 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
1164 ln_new_position_id number;
1165 ln_orginal_position_id number;
1166 BEGIN
1167 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1168 if(hr_utility.debug_enabled) then
1169 -- write debug statements
1170 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1171 end if;
1172
1173 ln_step_id := get_assignment_step_id(p_transaction_id);
1174 if(ln_step_id is not null) then
1175
1176 ln_new_position_id := fnd_number.number_to_canonical(hr_transaction_api.get_number_value
1177 (p_transaction_step_id => ln_step_id
1178 ,p_name => 'P_POSITION_ID'));
1179
1180 ln_orginal_position_id := fnd_number.number_to_canonical(hr_transaction_api.get_original_number_value
1181 (p_transaction_step_id => ln_step_id
1182 ,p_name => 'P_POSITION_ID'));
1183 if(nvl(ln_new_position_id,-111)<>nvl(ln_orginal_position_id,-111)) then
1184 lv_position_id:=ln_new_position_id;
1185 else
1186 lv_position_id:= null;
1187 end if;
1188 else
1189 lv_position_id:= null;
1190 end if;
1191
1192 if(hr_utility.debug_enabled) then
1193 -- write debug statements
1194 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1195 end if;
1196 return lv_position_id;
1197 EXCEPTION
1198
1199 WHEN OTHERS THEN
1200 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1201 RAISE;
1202
1203 END get_proposed_position_id;
1204
1205
1206 FUNCTION get_proposed_grade_id
1207 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1208 return varchar2 is
1209 -- local variables
1210 lv_procedure_name constant varchar2(21) := 'get_proposed_grade_id';
1211 lv_grade_id varchar2(15);
1212 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
1213
1214 ln_new_id number;
1215 ln_orginal_id number;
1216
1217 BEGIN
1218 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1219 if(hr_utility.debug_enabled) then
1220 -- write debug statements
1221 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1222 end if;
1223
1224
1225 ln_step_id := get_assignment_step_id(p_transaction_id);
1226 if(ln_step_id is not null) then
1227
1228 ln_new_id := fnd_number.number_to_canonical(hr_transaction_api.get_number_value
1229 (p_transaction_step_id => ln_step_id
1230 ,p_name => 'P_GRADE_ID'));
1231
1232 ln_orginal_id := fnd_number.number_to_canonical(hr_transaction_api.get_original_number_value
1233 (p_transaction_step_id => ln_step_id
1234 ,p_name => 'P_GRADE_ID'));
1235 if(nvl(ln_new_id,-111)<>nvl(ln_orginal_id,-111)) then
1236 lv_grade_id:=ln_new_id;
1237 else
1238 lv_grade_id:= null;
1239 end if;
1240
1241 else
1242 lv_grade_id:= null;
1243 end if;
1244
1245
1246 if(hr_utility.debug_enabled) then
1247 -- write debug statements
1248 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1249 end if;
1250 return lv_grade_id;
1251 EXCEPTION
1252
1253 WHEN OTHERS THEN
1254 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1255 RAISE;
1256
1257 END get_proposed_grade_id;
1258
1259 FUNCTION get_proposed_location_id
1260 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1261 return varchar2 is
1262 -- local variables
1263 lv_procedure_name constant varchar2(24) := 'get_proposed_location_id';
1264 lv_location_id varchar2(15);
1265 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
1266 ln_new_id number;
1267 ln_orginal_id number;
1268 BEGIN
1269 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1270 if(hr_utility.debug_enabled) then
1271 -- write debug statements
1272 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1273 end if;
1274 ln_step_id := get_assignment_step_id(p_transaction_id);
1275 if(ln_step_id is not null) then
1276
1277 ln_new_id := fnd_number.number_to_canonical(hr_transaction_api.get_number_value
1278 (p_transaction_step_id => ln_step_id
1279 ,p_name => 'P_LOCATION_ID'));
1280
1281 ln_orginal_id := fnd_number.number_to_canonical(hr_transaction_api.get_original_number_value
1282 (p_transaction_step_id => ln_step_id
1283 ,p_name => 'P_LOCATION_ID'));
1284 if(nvl(ln_new_id,-111)<>nvl(ln_orginal_id,-111)) then
1285 lv_location_id:=ln_new_id;
1286 else
1287 lv_location_id:= null;
1288 end if;
1289 else
1290 lv_location_id:= null;
1291 end if;
1292
1293 if(hr_utility.debug_enabled) then
1294 -- write debug statements
1295 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1296 end if;
1297 return lv_location_id;
1298 EXCEPTION
1299
1300 WHEN OTHERS THEN
1301 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1302 RAISE;
1303
1304 END get_proposed_location_id;
1305
1306 FUNCTION get_appraisal_type
1307 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1308 return varchar2 is
1309 -- local variables
1310 lv_procedure_name constant varchar2(18) := 'get_appraisal_type';
1311 lv_transaction_ref_table hr_api_transactions.transaction_ref_table%type;
1312 lv_transaction_ref_id hr_api_transactions.transaction_ref_id%type;
1313 lv_system_type VARCHAR2(30) default null;
1314 BEGIN
1315 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1316 if(hr_utility.debug_enabled) then
1317 -- write debug statements
1318 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1319 end if;
1320
1321 begin
1322 select transaction_ref_table,transaction_ref_id
1323 into lv_transaction_ref_table,lv_transaction_ref_id
1324 from hr_api_transactions
1325 where transaction_id=p_transaction_id;
1326
1327 if(lv_transaction_ref_table='PER_APPRAISALS') then
1328 begin
1329 select per_appraisals.system_type
1330 into lv_system_type
1331 from per_appraisals
1332 where appraisal_id=lv_transaction_ref_id;
1333 exception
1334 when others then
1335 -- do not raise, return
1336 null;
1337 end;
1338 end if;
1339 exception
1340 when others then
1341 hr_utility.trace(' exception in checking the hr_api_transactions.transaction_ref_table:'|| sqlerrm);
1342 -- just log the message no need to raise it
1343 end;
1344 if(hr_utility.debug_enabled) then
1345 -- write debug statements
1346 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1347 end if;
1348 return lv_system_type;
1349 EXCEPTION
1350
1351 WHEN OTHERS THEN
1352 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1353 RAISE;
1354
1355 END get_appraisal_type;
1356
1357
1358 FUNCTION get_overall_appraisal_rating
1359 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1360 return number is
1361 -- local variables
1362 lv_procedure_name constant varchar2(28) := 'get_overall_appraisal_rating';
1363 lv_transaction_ref_table hr_api_transactions.transaction_ref_table%type;
1364 lv_transaction_ref_id hr_api_transactions.transaction_ref_id%type;
1365 ln_overall_rating number;
1366
1367 BEGIN
1368 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1369 if(hr_utility.debug_enabled) then
1370 -- write debug statements
1371 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1372 end if;
1373
1374 begin
1375 select transaction_ref_table,transaction_ref_id
1376 into lv_transaction_ref_table,lv_transaction_ref_id
1377 from hr_api_transactions
1378 where transaction_id=p_transaction_id;
1379
1380 if(lv_transaction_ref_table='PER_APPRAISALS') then
1381 begin
1382 Select prl.step_value
1383 into ln_overall_rating
1384 from per_appraisals appr, per_rating_levels prl
1385 where appraisal_id = lv_transaction_ref_id
1386 and appr.overall_performance_level_id = prl.rating_level_id;
1387 exception
1388 when others then
1389 -- do not raise, return
1390 null;
1391 end;
1392 end if;
1393 exception
1394 when others then
1395 hr_utility.trace(' exception in checking the hr_api_transactions.transaction_ref_table:'|| sqlerrm);
1396 -- just log the message no need to raise it
1397 end;
1398
1399
1400
1401
1402
1403
1404 if(hr_utility.debug_enabled) then
1405 -- write debug statements
1406 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1407 end if;
1408 return fnd_number.number_to_canonical(ln_overall_rating);
1409 EXCEPTION
1410
1411 WHEN OTHERS THEN
1412 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1413 RAISE;
1414
1415 END get_overall_appraisal_rating;
1416
1417 FUNCTION get_absence_type_id
1418 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1419 return number is
1420 -- local variables
1421 lv_procedure_name constant varchar2(19) := 'get_absence_type_id';
1422 lv_absence_type_id number;
1423 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
1424 BEGIN
1425 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1426 if(hr_utility.debug_enabled) then
1427 -- write debug statements
1428 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1429 end if;
1430
1431 ln_step_id := get_loa_step_id(p_transaction_id);
1432 if(ln_step_id is not null) then
1433 select INFORMATION5 INTO lv_absence_type_id from hr_api_transaction_steps where transaction_step_id = ln_step_id;
1434 else
1435 lv_absence_type_id:= null;
1436 end if;
1437
1438 if(hr_utility.debug_enabled) then
1439 -- write debug statements
1440 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1441 end if;
1442 return lv_absence_type_id;
1443 EXCEPTION
1444
1445 WHEN OTHERS THEN
1446 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1447 RAISE;
1448
1449 END get_absence_type_id;
1450
1451 FUNCTION get_proposed_payroll_id
1452 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1453 return varchar2 is
1454 -- local variables
1455 lv_procedure_name constant varchar2(23) := 'get_proposed_payroll_id';
1456
1457 lv_payroll_id varchar2(15);
1458 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
1459 ln_new_id number;
1460 ln_orginal_id number;
1461
1462 BEGIN
1463 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1464 if(hr_utility.debug_enabled) then
1465 -- write debug statements
1466 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1467 end if;
1468 ln_step_id := get_assignment_step_id(p_transaction_id);
1469 if(ln_step_id is not null) then
1470
1471 ln_new_id := fnd_number.number_to_canonical(hr_transaction_api.get_number_value
1472 (p_transaction_step_id => ln_step_id
1473 ,p_name => 'P_PAYROLL_ID'));
1474
1475 ln_orginal_id := fnd_number.number_to_canonical(hr_transaction_api.get_original_number_value
1476 (p_transaction_step_id => ln_step_id
1477 ,p_name => 'P_PAYROLL_ID'));
1478 if(nvl(ln_new_id,-111)<>nvl(ln_orginal_id,-111)) then
1479 lv_payroll_id:=ln_new_id;
1480 else
1481 lv_payroll_id:= null;
1482 end if;
1483 else
1484 lv_payroll_id:= null;
1485 end if;
1486
1487 if(hr_utility.debug_enabled) then
1488 -- write debug statements
1489 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1490 end if;
1491 return lv_payroll_id;
1492 EXCEPTION
1493
1494 WHEN OTHERS THEN
1495 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1496 RAISE;
1497
1498 END get_proposed_payroll_id;
1499
1500
1501 FUNCTION get_proposed_salary_basis
1502 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1503 return varchar2 is
1504 -- local variables
1505 lv_procedure_name constant varchar2(25) := 'get_proposed_salary_basis';
1506 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
1507 lv_salary_basis VARCHAR2(30) default null;
1508 ln_pay_basis_id number;
1509 lv_item_type varchar2(50);
1510 lv_item_key varchar2(100);
1511 ld_effective_date date;
1512 ln_new_id number;
1513 ln_orginal_id number;
1514
1515 BEGIN
1516 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1517 if(hr_utility.debug_enabled) then
1518 -- write debug statements
1519 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1520 end if;
1521
1522 ln_step_id := get_assignment_step_id(p_transaction_id);
1523 if(ln_step_id is not null) then
1524 ln_new_id := fnd_number.number_to_canonical(hr_transaction_api.get_number_value
1525 (p_transaction_step_id => ln_step_id
1526 ,p_name => 'P_PAY_BASIS_ID'));
1527
1528 ln_orginal_id := fnd_number.number_to_canonical(hr_transaction_api.get_original_number_value
1529 (p_transaction_step_id => ln_step_id
1530 ,p_name => 'P_PAY_BASIS_ID'));
1531 if(nvl(ln_new_id,-111)<>nvl(ln_orginal_id,-111)) then
1532 ln_pay_basis_id:=ln_new_id;
1533 else
1534 ln_pay_basis_id:= null;
1535 return null;
1536 end if;
1537
1538
1539 lv_item_type := get_item_type(p_transaction_id);
1540 lv_item_key := get_item_key(p_transaction_id);
1541
1542 if (lv_item_key is not null) then
1543 ld_effective_date:= wf_engine.GetItemAttrDate(itemtype => lv_item_type ,
1544 itemkey => lv_item_key,
1545 aname => 'CURRENT_EFFECTIVE_DATE',
1546 ignore_notfound => true);
1547
1548 else
1549 select transaction_effective_date into ld_effective_date from hr_api_transactions
1550 where transaction_id = p_transaction_id;
1551 end if;
1552
1553 select ppb.name
1554 into lv_salary_basis
1555 from pay_element_types_f pet,
1556 pay_input_values_f piv,
1557 per_pay_bases ppb
1558 where ppb.pay_basis_id=ln_pay_basis_id
1559 and ppb.input_value_id=piv.input_value_id
1560 and ld_effective_date between
1561 piv.effective_start_date and
1562 piv.effective_end_date
1563 and piv.element_type_id=pet.element_type_id
1564 and ld_effective_date between
1565 pet.effective_start_date and
1566 pet.effective_end_date;
1567
1568 else
1569 lv_salary_basis:= null;
1570 end if;
1571
1572
1573 if(hr_utility.debug_enabled) then
1574 -- write debug statements
1575 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1576 end if;
1577 return lv_salary_basis;
1578 EXCEPTION
1579
1580 WHEN OTHERS THEN
1581 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1582 RAISE;
1583
1584 END get_proposed_salary_basis;
1585
1586
1587 FUNCTION get_asg_change_reason
1588 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1589 return varchar2 is
1590 -- local variables
1591 lv_procedure_name constant varchar2(21) := 'get_asg_change_reason';
1592 lv_asg_change_reason VARCHAR2(30) default null;
1593 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
1594 lv_new_value VARCHAR2(30);
1595 lv_orginal_value VARCHAR2(30);
1596
1597 BEGIN
1598 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1599 if(hr_utility.debug_enabled) then
1600 -- write debug statements
1601 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1602 end if;
1603
1604 ln_step_id := get_assignment_step_id(p_transaction_id);
1605 if(ln_step_id is not null) then
1606
1607 lv_new_value:= hr_transaction_api.get_varchar2_value
1608 (p_transaction_step_id => ln_step_id
1609 ,p_name => 'P_CHANGE_REASON');
1610
1611 lv_orginal_value:= hr_transaction_api.get_original_varchar2_value
1612 (p_transaction_step_id => ln_step_id
1613 ,p_name => 'P_CHANGE_REASON');
1614
1615 if(nvl(lv_new_value,'-111')<>nvl(lv_orginal_value,'-111')) then
1616 lv_asg_change_reason:=lv_new_value;
1617 else
1618 lv_asg_change_reason:=null;
1619 end if;
1620
1621 else
1622 lv_asg_change_reason:= null;
1623 end if;
1624
1625
1626 if(hr_utility.debug_enabled) then
1627 -- write debug statements
1628 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1629 end if;
1630 return lv_asg_change_reason;
1631 EXCEPTION
1632
1633 WHEN OTHERS THEN
1634 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1635 RAISE;
1636
1637 END get_asg_change_reason;
1638
1639 FUNCTION get_leaving_reason
1640 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1641 return varchar2 is
1642 -- local variables
1643 lv_procedure_name constant varchar2(18) := 'get_leaving_reason';
1644 lv_leaving_reason VARCHAR2(30) default null;
1645 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
1646
1647 BEGIN
1648 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1649 if(hr_utility.debug_enabled) then
1650 -- write debug statements
1651 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1652 end if;
1653 ln_step_id := get_termination_step_id(p_transaction_id);
1654 if(ln_step_id is not null) then
1655 lv_leaving_reason:= hr_transaction_api.get_varchar2_value
1656 (p_transaction_step_id => ln_step_id
1657 ,p_name => 'P_LEAVING_REASON');
1658 else
1659 lv_leaving_reason:= null;
1660 end if;
1661
1662 if(hr_utility.debug_enabled) then
1663 -- write debug statements
1664 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1665 end if;
1666 return lv_leaving_reason;
1667 EXCEPTION
1668
1669 WHEN OTHERS THEN
1670 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1671 RAISE;
1672
1673 END get_leaving_reason;
1674
1675
1676 FUNCTION get_person_type_id
1677 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1678 return varchar2 is
1679 -- local variables
1680 lv_procedure_name constant varchar2(18) := 'get_person_type_id';
1681 lv_person_type_id VARCHAR2(30) default null;
1682 BEGIN
1683 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1684 if(hr_utility.debug_enabled) then
1685 -- write debug statements
1686 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1687 end if;
1688
1689
1690 if(hr_utility.debug_enabled) then
1691 -- write debug statements
1692 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1693 end if;
1694 return lv_person_type_id;
1695 EXCEPTION
1696
1697 WHEN OTHERS THEN
1698 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1699 RAISE;
1700
1701 END get_person_type_id;
1702
1703
1704 FUNCTION getYOS(
1705 p_person_id IN NUMBER
1706 ,p_eff_date IN DATE )
1707 RETURN NUMBER
1708 IS
1709 ln_result NUMBER:=0;
1710
1711 CURSOR c_yos (p_person_id IN per_all_people_f.person_id%TYPE)
1712 IS
1713 SELECT ROUND(SUM(MONTHS_BETWEEN(
1714 decode(sign(p_eff_date-nvl(actual_termination_date, p_eff_date)),
1715 -1, trunc(p_eff_date), nvl(actual_termination_date, trunc(p_eff_date))),
1716 trunc(ser.date_start))/12), 2) yos
1717 FROM per_periods_of_service ser
1718 WHERE ser.person_id = p_person_id
1719 AND ser.date_start <= p_eff_date;
1720
1721 BEGIN
1722 OPEN c_yos(p_person_id => p_person_id);
1723 FETCH c_yos INTO ln_result;
1724 CLOSE c_yos;
1725
1726 IF ln_result < 1/365
1727 THEN ln_result := ROUND(1/365,2);
1728 END IF;
1729
1730 RETURN ln_result;
1731 Exception When Others then
1732 return 0;
1733 END getYOS;
1734
1735
1736 FUNCTION get_length_of_service
1737 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1738 return number is
1739 -- local variables
1740 lv_procedure_name constant varchar2(21) := 'get_length_of_service';
1741 ln_length_of_service number;
1742 BEGIN
1743 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1744 if(hr_utility.debug_enabled) then
1745 -- write debug statements
1746 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1747 end if;
1748
1749 ln_length_of_service := getYOS(get_selected_person_id(p_transaction_id),trunc(sysdate));
1750
1751 if(hr_utility.debug_enabled) then
1752 -- write debug statements
1753 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1754 end if;
1755 return fnd_number.number_to_canonical(ln_length_of_service);
1756 EXCEPTION
1757
1758 WHEN OTHERS THEN
1759 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1760 RAISE;
1761
1762 END get_length_of_service;
1763
1764
1765
1766 FUNCTION get_assignment_category
1767 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1768 return varchar2 is
1769 -- local variables
1770 lv_procedure_name constant varchar2(23) := 'get_assignment_category';
1771 lv_assignment_category VARCHAR2(30) default null;
1772 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
1773 lv_new_value VARCHAR2(30);
1774 lv_orginal_value VARCHAR2(30);
1775 BEGIN
1776 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1777 if(hr_utility.debug_enabled) then
1778 -- write debug statements
1779 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1780 end if;
1781 ln_step_id := get_assignment_step_id(p_transaction_id);
1782 if(ln_step_id is not null) then
1783 lv_new_value:= hr_transaction_api.get_varchar2_value
1784 (p_transaction_step_id => ln_step_id
1785 ,p_name => 'P_EMPLOYMENT_CATEGORY');
1786
1787 lv_orginal_value:= hr_transaction_api.get_original_varchar2_value
1788 (p_transaction_step_id => ln_step_id
1789 ,p_name => 'P_EMPLOYMENT_CATEGORY');
1790
1791 if(nvl(lv_new_value,'-111')<>nvl(lv_orginal_value,'-111')) then
1792 lv_assignment_category:=lv_new_value;
1793 else
1794 lv_assignment_category:=null;
1795 end if;
1796
1797 else
1798 lv_assignment_category:= null;
1799 end if;
1800
1801
1802
1803 if(hr_utility.debug_enabled) then
1804 -- write debug statements
1805 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1806 end if;
1807 return lv_assignment_category;
1808 EXCEPTION
1809
1810 WHEN OTHERS THEN
1811 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1812 RAISE;
1813
1814 END get_assignment_category;
1815
1816 FUNCTION get_payroll_con_user_name
1817 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1818 return varchar2 is
1819 -- local variables
1820 lv_procedure_name constant varchar2(25) := 'get_payroll_con_user_name';
1821 lv_user_name varchar2(30) default null;
1822 lv_orig_system varchar2(50);
1823 lv_orig_system_id number;
1824 BEGIN
1825
1826 if(hr_utility.debug_enabled) then
1827 -- write debug statements
1828 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1829 end if;
1830
1831 if(isMidPayPayPeriodChange(p_transaction_id)=ame_util.booleanAttributeTrue) then
1832 if(hr_utility.debug_enabled) then
1833 hr_utility.set_location('calling wf_engine.getitemattrtext ',3);
1834 end if;
1835 lv_user_name:= wf_engine.getitemattrtext(get_item_type(p_transaction_id),get_item_key(p_transaction_id),'HR_PAYROLL_CONTACT_USERNAME',true);
1836 -- get the role info details
1837 if(lv_user_name is not null) then
1838 wf_directory.getroleorigsysinfo(lv_user_name,lv_orig_system,lv_orig_system_id);
1839 lv_user_name:=lv_orig_system||':'||lv_orig_system_id;
1840 end if;
1841 end if;
1842
1843 if(hr_utility.debug_enabled) then
1844 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1845 end if;
1846
1847 return lv_user_name;
1848
1849 EXCEPTION
1850
1851 WHEN OTHERS THEN
1852 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1853 RAISE;
1854
1855 END get_payroll_con_user_name;
1856
1857
1858 FUNCTION get_basic_details_step_id
1859 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1860 return number is
1861 -- local variables
1862 lv_procedure_name constant varchar2(25) := 'get_basic_details_step_id';
1863 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
1864 BEGIN
1865 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1866 if(hr_utility.debug_enabled) then
1867 -- write debug statements
1868 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1869 end if;
1870 begin
1871 select transaction_step_id
1872 into ln_step_id
1873 from hr_api_transaction_steps
1874 where hr_api_transaction_steps.transaction_id=p_transaction_id
1875 and hr_api_transaction_steps.api_name='HR_PROCESS_PERSON_SS.PROCESS_API';
1876 exception
1877 when no_data_found then
1878 return null;
1879 when others then
1880 raise;
1881 end;
1882 if(hr_utility.debug_enabled) then
1883 -- write debug statements
1884 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1885 end if;
1886 return fnd_number.number_to_canonical(ln_step_id);
1887 EXCEPTION
1888
1889 WHEN OTHERS THEN
1890 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1891 RAISE;
1892
1893 END get_basic_details_step_id;
1894
1895
1896 FUNCTION isPersonDetailsChange
1897 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1898 return varchar2 is
1899 -- local variables
1900 lv_procedure_name constant varchar2(21) := 'isPersonDetailsChange';
1901 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
1902 lv_status varchar2(10);
1903 BEGIN
1904 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1905 if(hr_utility.debug_enabled) then
1906 -- write debug statements
1907 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1908 end if;
1909
1910 ln_step_id := get_basic_details_step_id(p_transaction_id);
1911
1912 if(ln_step_id is not null) then
1913 lv_status := ame_util.booleanAttributeTrue;
1914 else
1915 lv_status := ame_util.booleanAttributeFalse;
1916 end if;
1917
1918 if(hr_utility.debug_enabled) then
1919 -- write debug statements
1920 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1921 end if;
1922 return lv_status;
1923 EXCEPTION
1924
1925 WHEN OTHERS THEN
1926 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1927 RAISE;
1928
1929 END isPersonDetailsChange;
1930
1931
1932
1933
1934 FUNCTION get_person_address_step_id
1935 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1936 return number is
1937 -- local variables
1938 lv_procedure_name constant varchar2(26) := 'get_person_address_step_id';
1939 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
1940 BEGIN
1941 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1942 if(hr_utility.debug_enabled) then
1943 -- write debug statements
1944 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1945 end if;
1946 begin
1947 select transaction_step_id
1948 into ln_step_id
1949 from hr_api_transaction_steps
1950 where hr_api_transaction_steps.transaction_id=p_transaction_id
1951 and hr_api_transaction_steps.api_name='HR_PROCESS_ADDRESS_SS.PROCESS_API';
1952 exception
1953 when no_data_found then
1954 return null;
1955 when others then
1956 raise;
1957 end;
1958 if(hr_utility.debug_enabled) then
1959 -- write debug statements
1960 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1961 end if;
1962 return fnd_number.number_to_canonical(ln_step_id);
1963 EXCEPTION
1964
1965 WHEN OTHERS THEN
1966 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1967 RAISE;
1968
1969 END get_person_address_step_id;
1970
1971
1972 FUNCTION isPersonAddressChange
1973 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1974 return varchar2 is
1975 -- local variables
1976 lv_procedure_name constant varchar2(21) := 'isPersonAddressChange';
1977 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
1978 lv_status varchar2(10);
1979 BEGIN
1980 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1981 if(hr_utility.debug_enabled) then
1982 -- write debug statements
1983 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1984 end if;
1985
1986 ln_step_id := get_person_address_step_id(p_transaction_id);
1987
1988 if(ln_step_id is not null) then
1989 lv_status := ame_util.booleanAttributeTrue;
1990 else
1991 lv_status := ame_util.booleanAttributeFalse;
1992 end if;
1993
1994 if(hr_utility.debug_enabled) then
1995 -- write debug statements
1996 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1997 end if;
1998 return lv_status;
1999 EXCEPTION
2000
2001 WHEN OTHERS THEN
2002 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
2003 RAISE;
2004
2005 END isPersonAddressChange;
2006
2007
2008 FUNCTION get_person_contact_step_id
2009 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
2010 return number is
2011 -- local variables
2012 lv_procedure_name constant varchar2(30) := 'get_person_contact_step_id';
2013 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
2014 BEGIN
2015 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
2016 if(hr_utility.debug_enabled) then
2017 -- write debug statements
2018 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
2019 end if;
2020 begin
2021 select transaction_step_id
2022 into ln_step_id
2023 from hr_api_transaction_steps
2024 where hr_api_transaction_steps.transaction_id=p_transaction_id
2025 and hr_api_transaction_steps.api_name in ('HR_PROCESS_CONTACT_SS.PROCESS_API',
2026 'HR_PROCESS_CONTACT_SS.PROCESS_CREATE_CONTACT_API')
2027 and rownum<2;
2028
2029 exception
2030 when no_data_found then
2031 return null;
2032 when others then
2033 raise;
2034 end;
2035 if(hr_utility.debug_enabled) then
2036 -- write debug statements
2037 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
2038 end if;
2039 return fnd_number.number_to_canonical(ln_step_id);
2040 EXCEPTION
2041
2042 WHEN OTHERS THEN
2043 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
2044 RAISE;
2045
2046 END get_person_contact_step_id;
2047
2048
2049 FUNCTION isPersonContactChange
2050 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
2051 return varchar2 is
2052 -- local variables
2053 lv_procedure_name constant varchar2(21) := 'isPersonAddressChange';
2054 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
2055 lv_status varchar2(10);
2056 BEGIN
2057 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
2058 if(hr_utility.debug_enabled) then
2059 -- write debug statements
2060 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
2061 end if;
2062
2063 ln_step_id := get_person_contact_step_id(p_transaction_id);
2064
2065 if(ln_step_id is not null) then
2066 lv_status := ame_util.booleanAttributeTrue;
2067 else
2068 lv_status := ame_util.booleanAttributeFalse;
2069 end if;
2070
2071 if(hr_utility.debug_enabled) then
2072 -- write debug statements
2073 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
2074 end if;
2075 return lv_status;
2076 EXCEPTION
2077
2078 WHEN OTHERS THEN
2079 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
2080 RAISE;
2081
2082 END isPersonContactChange;
2083
2084
2085
2086 FUNCTION get_caed_step_id
2087 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
2088 return number is
2089 -- local variables
2090 lv_procedure_name constant varchar2(16) := 'get_caed_step_id';
2091 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
2092 BEGIN
2093 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
2094 if(hr_utility.debug_enabled) then
2095 -- write debug statements
2096 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
2097 end if;
2098 begin
2099 select transaction_step_id
2100 into ln_step_id
2101 from hr_api_transaction_steps
2102 where hr_api_transaction_steps.transaction_id=p_transaction_id
2103 and hr_api_transaction_steps.api_name='HR_CAED_SS.PROCESS_API';
2104 exception
2105 when no_data_found then
2106 return null;
2107 when others then
2108 raise;
2109 end;
2110 if(hr_utility.debug_enabled) then
2111 -- write debug statements
2112 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
2113 end if;
2114 return fnd_number.number_to_canonical(ln_step_id);
2115 EXCEPTION
2116
2117 WHEN OTHERS THEN
2118 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
2119 RAISE;
2120
2121 END get_caed_step_id;
2122
2123
2124 FUNCTION isReleaseInformation
2125 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
2126 return varchar2 is
2127 -- local variables
2128 lv_procedure_name constant varchar2(20) := 'isReleaseInformation';
2129 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
2130 lv_status varchar2(10);
2131 BEGIN
2132 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
2133 if(hr_utility.debug_enabled) then
2134 -- write debug statements
2135 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
2136 end if;
2137
2138 ln_step_id := get_caed_step_id(p_transaction_id);
2139
2140 if(ln_step_id is not null) then
2141 lv_status := ame_util.booleanAttributeTrue;
2142 else
2143 lv_status := ame_util.booleanAttributeFalse;
2144 end if;
2145
2146 if(hr_utility.debug_enabled) then
2147 -- write debug statements
2148 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
2149 end if;
2150 return lv_status;
2151 EXCEPTION
2152
2153 WHEN OTHERS THEN
2154 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
2155 RAISE;
2156
2157 END isReleaseInformation;
2158
2159
2160 FUNCTION get_paybasis_id
2161 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
2162 return number is
2163 -- local variables
2164 lv_procedure_name constant varchar2(25) := 'get_proposed_salary_basis';
2165 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
2166 lv_salary_basis VARCHAR2(30) default null;
2167 ln_pay_basis_id number;
2168 lv_item_type varchar2(50);
2169 lv_item_key varchar2(100);
2170 ld_effective_date date;
2171 BEGIN
2172 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
2173 if(hr_utility.debug_enabled) then
2174 -- write debug statements
2175 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
2176 end if;
2177
2178 ln_step_id := get_assignment_step_id(p_transaction_id);
2179 if(ln_step_id is not null) then
2180 ln_pay_basis_id := hr_transaction_api.get_number_value
2181 (p_transaction_step_id => ln_step_id
2182 ,p_name => 'P_PAY_BASIS_ID');
2183
2184 end if;
2185
2186 if(hr_utility.debug_enabled) then
2187 -- write debug statements
2188 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
2189 end if;
2190 return fnd_number.number_to_canonical(ln_pay_basis_id);
2191 EXCEPTION
2192
2193 WHEN OTHERS THEN
2194 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
2195 RAISE;
2196
2197 END get_paybasis_id;
2198
2199 FUNCTION isMidPayPayPeriodChange
2200 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
2201 return varchar2 is
2202 -- local variables
2203 lv_procedure_name constant varchar2(25) := 'isMidPayPayPeriodChange';
2204 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
2205 lv_status varchar2(10);
2206 result varchar2(50);
2207
2208
2209 l_assignment_id per_all_assignments_f.assignment_id%type default null;
2210 l_payroll_id per_all_assignments_f.payroll_id%type default null;
2211 l_old_pay_basis_id per_all_assignments_f.pay_basis_id%type default null;
2212 l_new_pay_basis_id per_all_assignments_f.pay_basis_id%type default null;
2213 l_pay_period_start_date date default null;
2214 l_pay_period_end_date date default null;
2215
2216 l_asg_txn_step_id hr_api_transaction_steps.transaction_step_id%type
2217 default null;
2218 l_effective_date date default null;
2219
2220
2221 CURSOR csr_check_mid_pay_period(p_eff_date_csr in date
2222 ,p_payroll_id_csr in number) IS
2223 select start_date, end_date
2224 from per_time_periods
2225 where p_eff_date_csr > start_date
2226 and p_eff_date_csr <= end_date
2227 and payroll_id = p_payroll_id_csr;
2228
2229 -- Get existing assignment data
2230 CURSOR csr_get_old_asg_data IS
2231 SELECT pay_basis_id
2232 FROM per_all_assignments_f
2233 WHERE assignment_id = l_assignment_id
2234 AND l_effective_date between effective_start_date
2235 and effective_end_date
2236 AND assignment_type = 'E';
2237
2238 BEGIN
2239 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
2240 if(hr_utility.debug_enabled) then
2241 -- write debug statements
2242 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
2243 end if;
2244
2245 -- check if this transaction has assignment step
2246 if(isAssignmentChange(p_transaction_id)=ame_util.booleanAttributeTrue) then
2247 -- code logic from hr_workflow_ss.check_mid_pay_period_change
2248
2249 l_asg_txn_step_id:= get_assignment_step_id(p_transaction_id);
2250 l_effective_date := to_date(
2251 hr_transaction_ss.get_wf_effective_date
2252 (p_transaction_step_id => l_asg_txn_step_id),
2253 hr_transaction_ss.g_date_format);
2254
2255 -- Get the pay_basis_id and payroll_id
2256 l_new_pay_basis_id := hr_transaction_api.get_number_value
2257 (p_transaction_step_id => l_asg_txn_step_id
2258 ,p_name => 'P_PAY_BASIS_ID');
2259
2260 l_payroll_id := hr_transaction_api.get_number_value
2261 (p_transaction_step_id => l_asg_txn_step_id
2262 ,p_name => 'P_PAYROLL_ID');
2263
2264 l_assignment_id := hr_transaction_api.get_number_value
2265 (p_transaction_step_id => l_asg_txn_step_id
2266 ,p_name => 'P_ASSIGNMENT_ID');
2267
2268 -- Now get the old pay basis id
2269 OPEN csr_get_old_asg_data;
2270 FETCH csr_get_old_asg_data into l_old_pay_basis_id;
2271 IF csr_get_old_asg_data%NOTFOUND THEN
2272 -- could be a new hire or applicant hire, there is no asg rec
2273 CLOSE csr_get_old_asg_data;
2274 ELSE
2275 CLOSE csr_get_old_asg_data;
2276 END IF;
2277
2278 IF l_old_pay_basis_id IS NOT NULL and
2279 l_new_pay_basis_id IS NOT NULL and
2280 l_old_pay_basis_id <> l_new_pay_basis_id and
2281 l_payroll_id IS NOT NULL
2282 THEN
2283 -- perform mid pay period check
2284 OPEN csr_check_mid_pay_period
2285 (p_eff_date_csr => l_effective_date
2286 ,p_payroll_id_csr => l_payroll_id);
2287 FETCH csr_check_mid_pay_period into l_pay_period_start_date
2288 ,l_pay_period_end_date;
2289 IF csr_check_mid_pay_period%NOTFOUND THEN
2290 -- That means the effective date is not in mid pay period
2291 lv_status := ame_util.booleanAttributeFalse;
2292 CLOSE csr_check_mid_pay_period;
2293 ELSE
2294 lv_status := ame_util.booleanAttributeTrue;
2295 CLOSE csr_check_mid_pay_period;
2296 END IF;
2297 END IF;
2298 else
2299 lv_status := ame_util.booleanAttributeFalse;
2300 end if;
2301
2302 if(hr_utility.debug_enabled) then
2303 -- write debug statements
2304 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
2305 end if;
2306
2307 return lv_status;
2308
2309
2310 EXCEPTION
2311
2312 WHEN OTHERS THEN
2313 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
2314 RAISE;
2315
2316 END isMidPayPayPeriodChange;
2317
2318
2319 FUNCTION getRequestorPositionId
2320 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
2321 return number
2322 is
2323 -- local variable
2324 ln_position_id number;
2325 c_proc constant varchar2(30) := 'getRequestorPositionId';
2326
2327
2328 cursor requestorPosId is
2329 select paf.position_id
2330 from per_all_assignments_f paf,
2331 per_all_people_f ppf,
2332 per_position_structures pps, per_pos_structure_versions ppsv,
2333 hr_api_transactions hat
2334 where hat.transaction_id = p_transaction_id
2335 and paf.person_id = hat.creator_person_id
2336 and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
2337 and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
2338 and paf.primary_flag = 'Y'
2339 and paf.assignment_type in ('E','C')
2340 and paf.person_id = ppf.person_id
2341 and ppf.business_group_id = pps.business_group_id(+)
2342 and pps.primary_position_flag (+) = 'Y'
2343 and pps.position_structure_id = ppsv.position_structure_id(+)
2344 and trunc(sysdate) between ppsv.date_from(+) and nvl(ppsv.date_to(+),sysdate);
2345 begin
2346 if g_debug then
2347 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
2348 end if;
2349
2350 open requestorPosId;
2351 fetch requestorPosId into ln_position_id;
2352 if(requestorPosId%notfound) then
2353 ln_position_id:= null;
2354 end if;
2355 close requestorPosId;
2356
2357 if (g_debug ) then
2358 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
2359 end if;
2360
2361 return ln_position_id;
2362
2363 exception
2364 when others then
2365
2366 if g_debug then
2367 hr_utility.set_location('Error in getRequestorPositionId SQLERRM' ||' '||to_char(SQLCODE),20);
2368 end if;
2369 -- close the cursor if open
2370 if(requestorPosId%isopen) then
2371 close requestorPosId;
2372 end if;
2373 raise;
2374 end getRequestorPositionId;
2375
2376 END HR_AMEUTIL_SS;