[Home] [Help]
Skip to content
PACKAGE BODY: APPS.HR_AMEUTIL_SS
Source
1 PACKAGE BODY HR_AMEUTIL_SS AS
2 /* $Header: hrameutlss.pkb 120.7.12010000.3 2008/08/18 13:01:37 schowdhu 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 ppt.transaction_step_id = ln_step_id;
782 exception
783 when no_data_found then
784 return null;
785 when others then
786 raise;
787 end;
788
789 ln_salary_percent_change := p_sum_percentage;
790
791 end if;
792 end if;
793 return ln_salary_percent_change;
794
795
796 if(hr_utility.debug_enabled) then
797 -- write debug statements
798 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
799 end if;
800 EXCEPTION
801
802 WHEN OTHERS THEN
803 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
804 RAISE;
805
806 END get_salary_percent_change;
807
808
809 FUNCTION get_salary_amount_change
810 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
811 return varchar2 is
812 -- local variables
813 lv_procedure_name constant varchar2(24) := 'get_salary_amount_change';
814 ln_salary_amt_change number default null;
815 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
816 ln_no_of_components NUMBER ;
817 lv_param_name hr_api_transaction_values.varchar2_value%type;
818 p_sum_amount per_pay_transactions.change_amount_n%type;
819 BEGIN
820 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
821 if(hr_utility.debug_enabled) then
822 -- write debug statements
823 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
824 end if;
825
826 -- get the payrate step id
827 ln_step_id := get_payrate_step_id(p_transaction_id);
828
829 if(ln_step_id is null) then
830 ln_salary_amt_change := null;
831 else
832 if (is_new_change_pay(ln_step_id) = ame_util.booleanAttributeFalse) then
833 -- fix for bug 4148680
834 if(hr_transaction_api.get_varchar2_value(ln_step_id,'p_multiple_components')='Y') then
835 -- get number of components P_NO_OF_COMPONENTS
836 ln_no_of_components :=hr_transaction_api.get_number_value
837 (p_transaction_step_id => ln_step_id,
838 p_name =>'P_NO_OF_COMPONENTS');
839 ln_salary_amt_change:= 0;
840 FOR i in 1..ln_no_of_components
841 LOOP
842 lv_param_name := 'p_change_amount'||i;
843 ln_salary_amt_change:= ln_salary_amt_change + fnd_number.number_to_canonical(hr_transaction_api.get_number_value
844 (p_transaction_step_id => ln_step_id,
845 p_name =>lv_param_name));
846 end loop;
847 else
848 ln_salary_amt_change:= fnd_number.number_to_canonical(hr_transaction_api.get_number_value
849 (p_transaction_step_id => ln_step_id,
850 p_name =>'p_change_amount'));
851 end if;
852 --changes made by schowdhu Bug#6919576
853 else
854 begin
855 select sum(change_amount_n)
856 into p_sum_amount
857 from per_pay_transactions ppt
858 where parent_pay_transaction_id is null
859 and ppt.transaction_step_id = ln_step_id;
860 exception
861 when no_data_found then
862 return null;
863 when others then
864 raise;
865 end;
866 ln_salary_amt_change := p_sum_amount;
867 end if;
868 end if;
869 return ln_salary_amt_change;
870
871 if(hr_utility.debug_enabled) then
872 -- write debug statements
873 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
874 end if;
875 EXCEPTION
876
877 WHEN OTHERS THEN
878 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
879 RAISE;
880
881 END get_salary_amount_change;
882
883 --function added by schowdhu Bug#6919576
884
885 FUNCTION is_new_change_pay(p_transaction_step_id IN hr_api_transaction_steps.transaction_step_id%TYPE)
886 return varchar2 is
887 -- local variables
888 lv_procedure_name constant varchar2(19) := 'is_new_change_pay';
889 ln_api_name hr_api_transaction_steps.api_name%type;
890 lv_status varchar2(10);
891 BEGIN
892 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
893 if(hr_utility.debug_enabled) then
894 -- write debug statements
895 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_step_id:'||p_transaction_step_id, 2);
896 end if;
897 begin
898 select api_name
899 into ln_api_name
900 from hr_api_transaction_steps
901 where hr_api_transaction_steps.transaction_step_id=p_transaction_step_id;
902 exception
903 when no_data_found then
904 return null;
905 when others then
906 raise;
907 end;
908 if(hr_utility.debug_enabled) then
909 -- write debug statements
910 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_step__id:'||p_transaction_step_id, 10);
911 end if;
912 if (ln_api_name='PER_SSHR_CHANGE_PAY.PROCESS_API') then
913 lv_status := ame_util.booleanAttributeTrue;
914 else
915 lv_status := ame_util.booleanAttributeFalse;
916 end if;
917 return lv_status;
918 EXCEPTION
919
920 WHEN OTHERS THEN
921 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_step_id);
922 RAISE;
923 END is_new_change_pay;
924
925 FUNCTION get_transaction_init_date
926 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
927 return varchar2 is
928 -- local variables
929 lv_procedure_name constant varchar2(25) := 'get_transaction_init_date';
930 lv_creation_date_string varchar2(30) default null;
931 BEGIN
932 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
933 if(hr_utility.debug_enabled) then
934 -- write debug statements
935 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
936 end if;
937 begin
938 select ame_util.versiondatetostring(creation_date)
939 into lv_creation_date_string
940 from hr_api_transactions
941 where transaction_id=p_transaction_id;
942 exception
943 when others then
944 raise;
945 end;
946
947 if(hr_utility.debug_enabled) then
948 -- write debug statements
949 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
950 end if;
951 return lv_creation_date_string;
952 EXCEPTION
953
954 WHEN OTHERS THEN
955 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
956 RAISE;
957
958 END get_transaction_init_date;
959
960
961
962 FUNCTION get_transaction_effective_date
963 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
964 return varchar2 is
965 -- local variables
966 lv_procedure_name constant varchar2(30) := 'get_transaction_effective_date';
967 lv_effective_date_string varchar2(30) default null;
968 lv_item_type varchar2(50);
969 lv_item_key varchar2(100);
970 BEGIN
971 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
972 if(hr_utility.debug_enabled) then
973 -- write debug statements
974 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
975 end if;
976
977
978 lv_item_type := get_item_type(p_transaction_id);
979 lv_item_key := get_item_key(p_transaction_id);
980
981 if(lv_item_key is not null) then
982 lv_effective_date_string := ame_util.versiondatetostring(wf_engine.GetItemAttrDate(itemtype => lv_item_type ,
983 itemkey => lv_item_key,
984 aname => 'CURRENT_EFFECTIVE_DATE',
985 ignore_notfound => true));
986
987 else
988 select transaction_effective_date into lv_effective_date_string from hr_api_transactions
989 where transaction_id = p_transaction_id;
990 end if;
991
992 if(hr_utility.debug_enabled) then
993 -- write debug statements
994 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
995 end if;
996
997 return lv_effective_date_string;
998
999 EXCEPTION
1000
1001 WHEN OTHERS THEN
1002 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1003 RAISE;
1004
1005 END get_transaction_effective_date;
1006
1007
1008
1009 FUNCTION get_sel_person_prop_sup_id
1010 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1011 return varchar2 is
1012 -- local variables
1013 lv_procedure_name constant varchar2(26) := 'get_sel_person_prop_sup_id';
1014 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
1015 ln_sel_person_prop_sup_id varchar2(10);
1016 ln_new_sel_person_prop_sup_id number;
1017 ln_old_sel_person_prop_sup_id number;
1018
1019 BEGIN
1020 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1021 if(hr_utility.debug_enabled) then
1022 -- write debug statements
1023 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1024 end if;
1025 -- get step id
1026 ln_step_id:= get_supeversior_Chg_step_id(p_transaction_id);
1027 if(ln_step_id is not null) then
1028 ln_new_sel_person_prop_sup_id :=
1029 fnd_number.number_to_canonical(hr_transaction_api.get_number_value
1030 (p_transaction_step_id => ln_step_id,
1031 p_name =>'p_selected_person_sup_id'));
1032 ln_old_sel_person_prop_sup_id :=
1033 fnd_number.number_to_canonical(hr_transaction_api.get_number_value
1034 (p_transaction_step_id => ln_step_id,
1035 p_name =>'p_selected_person_old_sup_id'));
1036
1037 if(nvl(ln_new_sel_person_prop_sup_id,-111)<>nvl(ln_old_sel_person_prop_sup_id,-111)) then
1038 ln_sel_person_prop_sup_id:=ln_new_sel_person_prop_sup_id;
1039 else
1040 ln_sel_person_prop_sup_id:= null;
1041 end if;
1042 else
1043 ln_sel_person_prop_sup_id := null;
1044 end if;
1045
1046
1047 if(hr_utility.debug_enabled) then
1048 -- write debug statements
1049 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1050 end if;
1051 return ln_sel_person_prop_sup_id;
1052
1053 EXCEPTION
1054
1055 WHEN OTHERS THEN
1056 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1057 RAISE;
1058
1059 END get_sel_person_prop_sup_id;
1060
1061 FUNCTION get_selected_person_id
1062 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1063 return varchar2 is
1064 -- local variables
1065 lv_procedure_name constant varchar2(22) := 'get_selected_person_id';
1066 lv_selected_person_id varchar2(10);
1067 lv_item_type varchar2(50);
1068 lv_item_key varchar2(100);
1069 BEGIN
1070 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1071 if(hr_utility.debug_enabled) then
1072 -- write debug statements
1073 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1074 end if;
1075
1076 lv_item_type := get_item_type(p_transaction_id);
1077 lv_item_key := get_item_key(p_transaction_id);
1078
1079 -- CURRENT_PERSON_ID
1080 if ( lv_item_key is not NULL) then
1081 lv_selected_person_id := wf_engine.GetItemAttrNumber(itemtype => lv_item_type ,
1082 itemkey => lv_item_key,
1083 aname => 'CURRENT_PERSON_ID',
1084 ignore_notfound => true);
1085
1086 else
1087 select selected_person_id into lv_selected_person_id from hr_api_transactions
1088 where transaction_id = p_transaction_id;
1089 end if;
1090
1091 if(hr_utility.debug_enabled) then
1092 -- write debug statements
1093 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1094 end if;
1095
1096 return fnd_number.number_to_canonical(lv_selected_person_id);
1097 EXCEPTION
1098
1099 WHEN OTHERS THEN
1100 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1101 RAISE;
1102
1103 END get_selected_person_id;
1104
1105 FUNCTION get_proposed_job_id
1106 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1107 return varchar2 is
1108 -- local variables
1109 lv_procedure_name constant varchar2(19) := 'get_proposed_job_id';
1110 lv_job_id varchar2(15);
1111 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
1112 ln_new_job_id number;
1113 ln_orginal_job_id number;
1114 BEGIN
1115 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1116 if(hr_utility.debug_enabled) then
1117 -- write debug statements
1118 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1119 end if;
1120
1121 ln_step_id := get_assignment_step_id(p_transaction_id);
1122 if(ln_step_id is not null) then
1123 -- fix for bug 4145754
1124 ln_new_job_id := fnd_number.number_to_canonical(hr_transaction_api.get_number_value
1125 (p_transaction_step_id => ln_step_id
1126 ,p_name => 'P_JOB_ID'));
1127
1128 ln_orginal_job_id := fnd_number.number_to_canonical(hr_transaction_api.get_original_number_value
1129 (p_transaction_step_id => ln_step_id
1130 ,p_name => 'P_JOB_ID'));
1131 if(nvl(ln_new_job_id,-111)<>nvl(ln_orginal_job_id,-111)) then
1132 lv_job_id:=ln_new_job_id;
1133 else
1134 lv_job_id:= null;
1135 end if;
1136 else
1137 lv_job_id:= null;
1138 end if;
1139
1140 if(hr_utility.debug_enabled) then
1141 -- write debug statements
1142 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1143 end if;
1144
1145 return lv_job_id;
1146 EXCEPTION
1147
1148 WHEN OTHERS THEN
1149 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1150 RAISE;
1151
1152 END get_proposed_job_id;
1153
1154
1155 FUNCTION get_proposed_position_id
1156 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1157 return varchar2 is
1158 -- local variables
1159 lv_procedure_name constant varchar2(24) := 'get_proposed_position_id';
1160 lv_position_id varchar2(15);
1161 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
1162 ln_new_position_id number;
1163 ln_orginal_position_id number;
1164 BEGIN
1165 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1166 if(hr_utility.debug_enabled) then
1167 -- write debug statements
1168 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1169 end if;
1170
1171 ln_step_id := get_assignment_step_id(p_transaction_id);
1172 if(ln_step_id is not null) then
1173
1174 ln_new_position_id := fnd_number.number_to_canonical(hr_transaction_api.get_number_value
1175 (p_transaction_step_id => ln_step_id
1176 ,p_name => 'P_POSITION_ID'));
1177
1178 ln_orginal_position_id := fnd_number.number_to_canonical(hr_transaction_api.get_original_number_value
1179 (p_transaction_step_id => ln_step_id
1180 ,p_name => 'P_POSITION_ID'));
1181 if(nvl(ln_new_position_id,-111)<>nvl(ln_orginal_position_id,-111)) then
1182 lv_position_id:=ln_new_position_id;
1183 else
1184 lv_position_id:= null;
1185 end if;
1186 else
1187 lv_position_id:= null;
1188 end if;
1189
1190 if(hr_utility.debug_enabled) then
1191 -- write debug statements
1192 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1193 end if;
1194 return lv_position_id;
1195 EXCEPTION
1196
1197 WHEN OTHERS THEN
1198 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1199 RAISE;
1200
1201 END get_proposed_position_id;
1202
1203
1204 FUNCTION get_proposed_grade_id
1205 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1206 return varchar2 is
1207 -- local variables
1208 lv_procedure_name constant varchar2(21) := 'get_proposed_grade_id';
1209 lv_grade_id varchar2(15);
1210 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
1211
1212 ln_new_id number;
1213 ln_orginal_id number;
1214
1215 BEGIN
1216 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1217 if(hr_utility.debug_enabled) then
1218 -- write debug statements
1219 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1220 end if;
1221
1222
1223 ln_step_id := get_assignment_step_id(p_transaction_id);
1224 if(ln_step_id is not null) then
1225
1226 ln_new_id := fnd_number.number_to_canonical(hr_transaction_api.get_number_value
1227 (p_transaction_step_id => ln_step_id
1228 ,p_name => 'P_GRADE_ID'));
1229
1230 ln_orginal_id := fnd_number.number_to_canonical(hr_transaction_api.get_original_number_value
1231 (p_transaction_step_id => ln_step_id
1232 ,p_name => 'P_GRADE_ID'));
1233 if(nvl(ln_new_id,-111)<>nvl(ln_orginal_id,-111)) then
1234 lv_grade_id:=ln_new_id;
1235 else
1236 lv_grade_id:= null;
1237 end if;
1238
1239 else
1240 lv_grade_id:= null;
1241 end if;
1242
1243
1244 if(hr_utility.debug_enabled) then
1245 -- write debug statements
1246 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1247 end if;
1248 return lv_grade_id;
1249 EXCEPTION
1250
1251 WHEN OTHERS THEN
1252 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1253 RAISE;
1254
1255 END get_proposed_grade_id;
1256
1257 FUNCTION get_proposed_location_id
1258 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1259 return varchar2 is
1260 -- local variables
1261 lv_procedure_name constant varchar2(24) := 'get_proposed_location_id';
1262 lv_location_id varchar2(15);
1263 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
1264 ln_new_id number;
1265 ln_orginal_id number;
1266 BEGIN
1267 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1268 if(hr_utility.debug_enabled) then
1269 -- write debug statements
1270 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1271 end if;
1272 ln_step_id := get_assignment_step_id(p_transaction_id);
1273 if(ln_step_id is not null) then
1274
1275 ln_new_id := fnd_number.number_to_canonical(hr_transaction_api.get_number_value
1276 (p_transaction_step_id => ln_step_id
1277 ,p_name => 'P_LOCATION_ID'));
1278
1279 ln_orginal_id := fnd_number.number_to_canonical(hr_transaction_api.get_original_number_value
1280 (p_transaction_step_id => ln_step_id
1281 ,p_name => 'P_LOCATION_ID'));
1282 if(nvl(ln_new_id,-111)<>nvl(ln_orginal_id,-111)) then
1283 lv_location_id:=ln_new_id;
1284 else
1285 lv_location_id:= null;
1286 end if;
1287 else
1288 lv_location_id:= null;
1289 end if;
1290
1291 if(hr_utility.debug_enabled) then
1292 -- write debug statements
1293 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1294 end if;
1295 return lv_location_id;
1296 EXCEPTION
1297
1298 WHEN OTHERS THEN
1299 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1300 RAISE;
1301
1302 END get_proposed_location_id;
1303
1304 FUNCTION get_appraisal_type
1305 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1306 return varchar2 is
1307 -- local variables
1308 lv_procedure_name constant varchar2(18) := 'get_appraisal_type';
1309 lv_transaction_ref_table hr_api_transactions.transaction_ref_table%type;
1310 lv_transaction_ref_id hr_api_transactions.transaction_ref_id%type;
1311 lv_system_type VARCHAR2(30) default null;
1312 BEGIN
1313 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1314 if(hr_utility.debug_enabled) then
1315 -- write debug statements
1316 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1317 end if;
1318
1319 begin
1320 select transaction_ref_table,transaction_ref_id
1321 into lv_transaction_ref_table,lv_transaction_ref_id
1322 from hr_api_transactions
1323 where transaction_id=p_transaction_id;
1324
1325 if(lv_transaction_ref_table='PER_APPRAISALS') then
1326 begin
1327 select per_appraisals.system_type
1328 into lv_system_type
1329 from per_appraisals
1330 where appraisal_id=lv_transaction_ref_id;
1331 exception
1332 when others then
1333 -- do not raise, return
1334 null;
1335 end;
1336 end if;
1337 exception
1338 when others then
1339 hr_utility.trace(' exception in checking the hr_api_transactions.transaction_ref_table:'|| sqlerrm);
1340 -- just log the message no need to raise it
1341 end;
1342 if(hr_utility.debug_enabled) then
1343 -- write debug statements
1344 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1345 end if;
1346 return lv_system_type;
1347 EXCEPTION
1348
1349 WHEN OTHERS THEN
1350 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1351 RAISE;
1352
1353 END get_appraisal_type;
1354
1355
1356 FUNCTION get_overall_appraisal_rating
1357 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1358 return number is
1359 -- local variables
1360 lv_procedure_name constant varchar2(28) := 'get_overall_appraisal_rating';
1361 lv_transaction_ref_table hr_api_transactions.transaction_ref_table%type;
1362 lv_transaction_ref_id hr_api_transactions.transaction_ref_id%type;
1363 ln_overall_rating number;
1364
1365 BEGIN
1366 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1367 if(hr_utility.debug_enabled) then
1368 -- write debug statements
1369 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1370 end if;
1371
1372 begin
1373 select transaction_ref_table,transaction_ref_id
1374 into lv_transaction_ref_table,lv_transaction_ref_id
1375 from hr_api_transactions
1376 where transaction_id=p_transaction_id;
1377
1378 if(lv_transaction_ref_table='PER_APPRAISALS') then
1379 begin
1380 Select prl.step_value
1381 into ln_overall_rating
1382 from per_appraisals appr, per_rating_levels prl
1383 where appraisal_id = lv_transaction_ref_id
1384 and appr.overall_performance_level_id = prl.rating_level_id;
1385 exception
1386 when others then
1387 -- do not raise, return
1388 null;
1389 end;
1390 end if;
1391 exception
1392 when others then
1393 hr_utility.trace(' exception in checking the hr_api_transactions.transaction_ref_table:'|| sqlerrm);
1394 -- just log the message no need to raise it
1395 end;
1396
1397
1398
1399
1400
1401
1402 if(hr_utility.debug_enabled) then
1403 -- write debug statements
1404 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1405 end if;
1406 return fnd_number.number_to_canonical(ln_overall_rating);
1407 EXCEPTION
1408
1409 WHEN OTHERS THEN
1410 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1411 RAISE;
1412
1413 END get_overall_appraisal_rating;
1414
1415 FUNCTION get_absence_type_id
1416 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1417 return number is
1418 -- local variables
1419 lv_procedure_name constant varchar2(19) := 'get_absence_type_id';
1420 lv_absence_type_id number;
1421 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
1422 BEGIN
1423 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1424 if(hr_utility.debug_enabled) then
1425 -- write debug statements
1426 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1427 end if;
1428
1429 ln_step_id := get_loa_step_id(p_transaction_id);
1430 if(ln_step_id is not null) then
1431 select INFORMATION5 INTO lv_absence_type_id from hr_api_transaction_steps where transaction_step_id = ln_step_id;
1432 else
1433 lv_absence_type_id:= null;
1434 end if;
1435
1436 if(hr_utility.debug_enabled) then
1437 -- write debug statements
1438 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1439 end if;
1440 return lv_absence_type_id;
1441 EXCEPTION
1442
1443 WHEN OTHERS THEN
1444 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1445 RAISE;
1446
1447 END get_absence_type_id;
1448
1449 FUNCTION get_proposed_payroll_id
1450 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1451 return varchar2 is
1452 -- local variables
1453 lv_procedure_name constant varchar2(23) := 'get_proposed_payroll_id';
1454
1455 lv_payroll_id varchar2(15);
1456 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
1457 ln_new_id number;
1458 ln_orginal_id number;
1459
1460 BEGIN
1461 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1462 if(hr_utility.debug_enabled) then
1463 -- write debug statements
1464 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1465 end if;
1466 ln_step_id := get_assignment_step_id(p_transaction_id);
1467 if(ln_step_id is not null) then
1468
1469 ln_new_id := fnd_number.number_to_canonical(hr_transaction_api.get_number_value
1470 (p_transaction_step_id => ln_step_id
1471 ,p_name => 'P_PAYROLL_ID'));
1472
1473 ln_orginal_id := fnd_number.number_to_canonical(hr_transaction_api.get_original_number_value
1474 (p_transaction_step_id => ln_step_id
1475 ,p_name => 'P_PAYROLL_ID'));
1476 if(nvl(ln_new_id,-111)<>nvl(ln_orginal_id,-111)) then
1477 lv_payroll_id:=ln_new_id;
1478 else
1479 lv_payroll_id:= null;
1480 end if;
1481 else
1482 lv_payroll_id:= null;
1483 end if;
1484
1485 if(hr_utility.debug_enabled) then
1486 -- write debug statements
1487 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1488 end if;
1489 return lv_payroll_id;
1490 EXCEPTION
1491
1492 WHEN OTHERS THEN
1493 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1494 RAISE;
1495
1496 END get_proposed_payroll_id;
1497
1498
1499 FUNCTION get_proposed_salary_basis
1500 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1501 return varchar2 is
1502 -- local variables
1503 lv_procedure_name constant varchar2(25) := 'get_proposed_salary_basis';
1504 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
1505 lv_salary_basis VARCHAR2(30) default null;
1506 ln_pay_basis_id number;
1507 lv_item_type varchar2(50);
1508 lv_item_key varchar2(100);
1509 ld_effective_date date;
1510 ln_new_id number;
1511 ln_orginal_id number;
1512
1513 BEGIN
1514 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1515 if(hr_utility.debug_enabled) then
1516 -- write debug statements
1517 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1518 end if;
1519
1520 ln_step_id := get_assignment_step_id(p_transaction_id);
1521 if(ln_step_id is not null) then
1522 ln_new_id := fnd_number.number_to_canonical(hr_transaction_api.get_number_value
1523 (p_transaction_step_id => ln_step_id
1524 ,p_name => 'P_PAY_BASIS_ID'));
1525
1526 ln_orginal_id := fnd_number.number_to_canonical(hr_transaction_api.get_original_number_value
1527 (p_transaction_step_id => ln_step_id
1528 ,p_name => 'P_PAY_BASIS_ID'));
1529 if(nvl(ln_new_id,-111)<>nvl(ln_orginal_id,-111)) then
1530 ln_pay_basis_id:=ln_new_id;
1531 else
1532 ln_pay_basis_id:= null;
1533 return null;
1534 end if;
1535
1536
1537 lv_item_type := get_item_type(p_transaction_id);
1538 lv_item_key := get_item_key(p_transaction_id);
1539
1540 if (lv_item_key is not null) then
1541 ld_effective_date:= wf_engine.GetItemAttrDate(itemtype => lv_item_type ,
1542 itemkey => lv_item_key,
1543 aname => 'CURRENT_EFFECTIVE_DATE',
1544 ignore_notfound => true);
1545
1546 else
1547 select transaction_effective_date into ld_effective_date from hr_api_transactions
1548 where transaction_id = p_transaction_id;
1549 end if;
1550
1551 select ppb.name
1552 into lv_salary_basis
1553 from pay_element_types_f pet,
1554 pay_input_values_f piv,
1555 per_pay_bases ppb
1556 where ppb.pay_basis_id=ln_pay_basis_id
1557 and ppb.input_value_id=piv.input_value_id
1558 and ld_effective_date between
1559 piv.effective_start_date and
1560 piv.effective_end_date
1561 and piv.element_type_id=pet.element_type_id
1562 and ld_effective_date between
1563 pet.effective_start_date and
1564 pet.effective_end_date;
1565
1566 else
1567 lv_salary_basis:= null;
1568 end if;
1569
1570
1571 if(hr_utility.debug_enabled) then
1572 -- write debug statements
1573 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1574 end if;
1575 return lv_salary_basis;
1576 EXCEPTION
1577
1578 WHEN OTHERS THEN
1579 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1580 RAISE;
1581
1582 END get_proposed_salary_basis;
1583
1584
1585 FUNCTION get_asg_change_reason
1586 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1587 return varchar2 is
1588 -- local variables
1589 lv_procedure_name constant varchar2(21) := 'get_asg_change_reason';
1590 lv_asg_change_reason VARCHAR2(30) default null;
1591 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
1592 lv_new_value VARCHAR2(30);
1593 lv_orginal_value VARCHAR2(30);
1594
1595 BEGIN
1596 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1597 if(hr_utility.debug_enabled) then
1598 -- write debug statements
1599 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1600 end if;
1601
1602 ln_step_id := get_assignment_step_id(p_transaction_id);
1603 if(ln_step_id is not null) then
1604
1605 lv_new_value:= hr_transaction_api.get_varchar2_value
1606 (p_transaction_step_id => ln_step_id
1607 ,p_name => 'P_CHANGE_REASON');
1608
1609 lv_orginal_value:= hr_transaction_api.get_original_varchar2_value
1610 (p_transaction_step_id => ln_step_id
1611 ,p_name => 'P_CHANGE_REASON');
1612
1613 if(nvl(lv_new_value,'-111')<>nvl(lv_orginal_value,'-111')) then
1614 lv_asg_change_reason:=lv_new_value;
1615 else
1616 lv_asg_change_reason:=null;
1617 end if;
1618
1619 else
1620 lv_asg_change_reason:= null;
1621 end if;
1622
1623
1624 if(hr_utility.debug_enabled) then
1625 -- write debug statements
1626 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1627 end if;
1628 return lv_asg_change_reason;
1629 EXCEPTION
1630
1631 WHEN OTHERS THEN
1632 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1633 RAISE;
1634
1635 END get_asg_change_reason;
1636
1637 FUNCTION get_leaving_reason
1638 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1639 return varchar2 is
1640 -- local variables
1641 lv_procedure_name constant varchar2(18) := 'get_leaving_reason';
1642 lv_leaving_reason VARCHAR2(30) default null;
1643 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
1644
1645 BEGIN
1646 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1647 if(hr_utility.debug_enabled) then
1648 -- write debug statements
1649 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1650 end if;
1651 ln_step_id := get_termination_step_id(p_transaction_id);
1652 if(ln_step_id is not null) then
1653 lv_leaving_reason:= hr_transaction_api.get_varchar2_value
1654 (p_transaction_step_id => ln_step_id
1655 ,p_name => 'P_LEAVING_REASON');
1656 else
1657 lv_leaving_reason:= null;
1658 end if;
1659
1660 if(hr_utility.debug_enabled) then
1661 -- write debug statements
1662 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1663 end if;
1664 return lv_leaving_reason;
1665 EXCEPTION
1666
1667 WHEN OTHERS THEN
1668 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1669 RAISE;
1670
1671 END get_leaving_reason;
1672
1673
1674 FUNCTION get_person_type_id
1675 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1676 return varchar2 is
1677 -- local variables
1678 lv_procedure_name constant varchar2(18) := 'get_person_type_id';
1679 lv_person_type_id VARCHAR2(30) default null;
1680 BEGIN
1681 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1682 if(hr_utility.debug_enabled) then
1683 -- write debug statements
1684 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1685 end if;
1686
1687
1688 if(hr_utility.debug_enabled) then
1689 -- write debug statements
1690 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1691 end if;
1692 return lv_person_type_id;
1693 EXCEPTION
1694
1695 WHEN OTHERS THEN
1696 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1697 RAISE;
1698
1699 END get_person_type_id;
1700
1701
1702 FUNCTION getYOS(
1703 p_person_id IN NUMBER
1704 ,p_eff_date IN DATE )
1705 RETURN NUMBER
1706 IS
1707 ln_result NUMBER:=0;
1708
1709 CURSOR c_yos (p_person_id IN per_people_f.person_id%TYPE)
1710 IS
1711 SELECT ROUND(SUM(MONTHS_BETWEEN(
1712 decode(sign(p_eff_date-nvl(actual_termination_date, p_eff_date)),
1713 -1, trunc(p_eff_date), nvl(actual_termination_date, trunc(p_eff_date))),
1714 trunc(ser.date_start))/12), 2) yos
1715 FROM per_periods_of_service ser
1716 WHERE ser.person_id = p_person_id
1717 AND ser.date_start <= p_eff_date;
1718
1719 BEGIN
1720 OPEN c_yos(p_person_id => p_person_id);
1721 FETCH c_yos INTO ln_result;
1722 CLOSE c_yos;
1723
1724 IF ln_result < 1/365
1725 THEN ln_result := ROUND(1/365,2);
1726 END IF;
1727
1728 RETURN ln_result;
1729 Exception When Others then
1730 return 0;
1731 END getYOS;
1732
1733
1734 FUNCTION get_length_of_service
1735 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1736 return number is
1737 -- local variables
1738 lv_procedure_name constant varchar2(21) := 'get_length_of_service';
1739 ln_length_of_service number;
1740 BEGIN
1741 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1742 if(hr_utility.debug_enabled) then
1743 -- write debug statements
1744 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1745 end if;
1746
1747 ln_length_of_service := getYOS(get_selected_person_id(p_transaction_id),trunc(sysdate));
1748
1749 if(hr_utility.debug_enabled) then
1750 -- write debug statements
1751 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1752 end if;
1753 return fnd_number.number_to_canonical(ln_length_of_service);
1754 EXCEPTION
1755
1756 WHEN OTHERS THEN
1757 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1758 RAISE;
1759
1760 END get_length_of_service;
1761
1762
1763
1764 FUNCTION get_assignment_category
1765 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1766 return varchar2 is
1767 -- local variables
1768 lv_procedure_name constant varchar2(23) := 'get_assignment_category';
1769 lv_assignment_category VARCHAR2(30) default null;
1770 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
1771 lv_new_value VARCHAR2(30);
1772 lv_orginal_value VARCHAR2(30);
1773 BEGIN
1774 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1775 if(hr_utility.debug_enabled) then
1776 -- write debug statements
1777 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1778 end if;
1779 ln_step_id := get_assignment_step_id(p_transaction_id);
1780 if(ln_step_id is not null) then
1781 lv_new_value:= hr_transaction_api.get_varchar2_value
1782 (p_transaction_step_id => ln_step_id
1783 ,p_name => 'P_EMPLOYMENT_CATEGORY');
1784
1785 lv_orginal_value:= hr_transaction_api.get_original_varchar2_value
1786 (p_transaction_step_id => ln_step_id
1787 ,p_name => 'P_EMPLOYMENT_CATEGORY');
1788
1789 if(nvl(lv_new_value,'-111')<>nvl(lv_orginal_value,'-111')) then
1790 lv_assignment_category:=lv_new_value;
1791 else
1792 lv_assignment_category:=null;
1793 end if;
1794
1795 else
1796 lv_assignment_category:= null;
1797 end if;
1798
1799
1800
1801 if(hr_utility.debug_enabled) then
1802 -- write debug statements
1803 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1804 end if;
1805 return lv_assignment_category;
1806 EXCEPTION
1807
1808 WHEN OTHERS THEN
1809 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1810 RAISE;
1811
1812 END get_assignment_category;
1813
1814 FUNCTION get_payroll_con_user_name
1815 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1816 return varchar2 is
1817 -- local variables
1818 lv_procedure_name constant varchar2(25) := 'get_payroll_con_user_name';
1819 lv_user_name varchar2(30) default null;
1820 lv_orig_system varchar2(50);
1821 lv_orig_system_id number;
1822 BEGIN
1823
1824 if(hr_utility.debug_enabled) then
1825 -- write debug statements
1826 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1827 end if;
1828
1829 if(isMidPayPayPeriodChange(p_transaction_id)=ame_util.booleanAttributeTrue) then
1830 if(hr_utility.debug_enabled) then
1831 hr_utility.set_location('calling wf_engine.getitemattrtext ',3);
1832 end if;
1833 lv_user_name:= wf_engine.getitemattrtext(get_item_type(p_transaction_id),get_item_key(p_transaction_id),'HR_PAYROLL_CONTACT_USERNAME',true);
1834 -- get the role info details
1835 if(lv_user_name is not null) then
1836 wf_directory.getroleorigsysinfo(lv_user_name,lv_orig_system,lv_orig_system_id);
1837 lv_user_name:=lv_orig_system||':'||lv_orig_system_id;
1838 end if;
1839 end if;
1840
1841 if(hr_utility.debug_enabled) then
1842 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1843 end if;
1844
1845 return lv_user_name;
1846
1847 EXCEPTION
1848
1849 WHEN OTHERS THEN
1850 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1851 RAISE;
1852
1853 END get_payroll_con_user_name;
1854
1855
1856 FUNCTION get_basic_details_step_id
1857 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1858 return number is
1859 -- local variables
1860 lv_procedure_name constant varchar2(25) := 'get_basic_details_step_id';
1861 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
1862 BEGIN
1863 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1864 if(hr_utility.debug_enabled) then
1865 -- write debug statements
1866 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1867 end if;
1868 begin
1869 select transaction_step_id
1870 into ln_step_id
1871 from hr_api_transaction_steps
1872 where hr_api_transaction_steps.transaction_id=p_transaction_id
1873 and hr_api_transaction_steps.api_name='HR_PROCESS_PERSON_SS.PROCESS_API';
1874 exception
1875 when no_data_found then
1876 return null;
1877 when others then
1878 raise;
1879 end;
1880 if(hr_utility.debug_enabled) then
1881 -- write debug statements
1882 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1883 end if;
1884 return fnd_number.number_to_canonical(ln_step_id);
1885 EXCEPTION
1886
1887 WHEN OTHERS THEN
1888 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1889 RAISE;
1890
1891 END get_basic_details_step_id;
1892
1893
1894 FUNCTION isPersonDetailsChange
1895 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1896 return varchar2 is
1897 -- local variables
1898 lv_procedure_name constant varchar2(21) := 'isPersonDetailsChange';
1899 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
1900 lv_status varchar2(10);
1901 BEGIN
1902 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1903 if(hr_utility.debug_enabled) then
1904 -- write debug statements
1905 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1906 end if;
1907
1908 ln_step_id := get_basic_details_step_id(p_transaction_id);
1909
1910 if(ln_step_id is not null) then
1911 lv_status := ame_util.booleanAttributeTrue;
1912 else
1913 lv_status := ame_util.booleanAttributeFalse;
1914 end if;
1915
1916 if(hr_utility.debug_enabled) then
1917 -- write debug statements
1918 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1919 end if;
1920 return lv_status;
1921 EXCEPTION
1922
1923 WHEN OTHERS THEN
1924 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1925 RAISE;
1926
1927 END isPersonDetailsChange;
1928
1929
1930
1931
1932 FUNCTION get_person_address_step_id
1933 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1934 return number is
1935 -- local variables
1936 lv_procedure_name constant varchar2(26) := 'get_person_address_step_id';
1937 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
1938 BEGIN
1939 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1940 if(hr_utility.debug_enabled) then
1941 -- write debug statements
1942 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1943 end if;
1944 begin
1945 select transaction_step_id
1946 into ln_step_id
1947 from hr_api_transaction_steps
1948 where hr_api_transaction_steps.transaction_id=p_transaction_id
1949 and hr_api_transaction_steps.api_name='HR_PROCESS_ADDRESS_SS.PROCESS_API';
1950 exception
1951 when no_data_found then
1952 return null;
1953 when others then
1954 raise;
1955 end;
1956 if(hr_utility.debug_enabled) then
1957 -- write debug statements
1958 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1959 end if;
1960 return fnd_number.number_to_canonical(ln_step_id);
1961 EXCEPTION
1962
1963 WHEN OTHERS THEN
1964 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
1965 RAISE;
1966
1967 END get_person_address_step_id;
1968
1969
1970 FUNCTION isPersonAddressChange
1971 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1972 return varchar2 is
1973 -- local variables
1974 lv_procedure_name constant varchar2(21) := 'isPersonAddressChange';
1975 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
1976 lv_status varchar2(10);
1977 BEGIN
1978 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
1979 if(hr_utility.debug_enabled) then
1980 -- write debug statements
1981 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
1982 end if;
1983
1984 ln_step_id := get_person_address_step_id(p_transaction_id);
1985
1986 if(ln_step_id is not null) then
1987 lv_status := ame_util.booleanAttributeTrue;
1988 else
1989 lv_status := ame_util.booleanAttributeFalse;
1990 end if;
1991
1992 if(hr_utility.debug_enabled) then
1993 -- write debug statements
1994 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
1995 end if;
1996 return lv_status;
1997 EXCEPTION
1998
1999 WHEN OTHERS THEN
2000 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
2001 RAISE;
2002
2003 END isPersonAddressChange;
2004
2005
2006 FUNCTION get_person_contact_step_id
2007 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
2008 return number is
2009 -- local variables
2010 lv_procedure_name constant varchar2(30) := 'get_person_contact_step_id';
2011 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
2012 BEGIN
2013 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
2014 if(hr_utility.debug_enabled) then
2015 -- write debug statements
2016 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
2017 end if;
2018 begin
2019 select transaction_step_id
2020 into ln_step_id
2021 from hr_api_transaction_steps
2022 where hr_api_transaction_steps.transaction_id=p_transaction_id
2023 and hr_api_transaction_steps.api_name in ('HR_PROCESS_CONTACT_SS.PROCESS_API',
2024 'HR_PROCESS_CONTACT_SS.PROCESS_CREATE_CONTACT_API')
2025 and rownum<2;
2026
2027 exception
2028 when no_data_found then
2029 return null;
2030 when others then
2031 raise;
2032 end;
2033 if(hr_utility.debug_enabled) then
2034 -- write debug statements
2035 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
2036 end if;
2037 return fnd_number.number_to_canonical(ln_step_id);
2038 EXCEPTION
2039
2040 WHEN OTHERS THEN
2041 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
2042 RAISE;
2043
2044 END get_person_contact_step_id;
2045
2046
2047 FUNCTION isPersonContactChange
2048 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
2049 return varchar2 is
2050 -- local variables
2051 lv_procedure_name constant varchar2(21) := 'isPersonAddressChange';
2052 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
2053 lv_status varchar2(10);
2054 BEGIN
2055 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
2056 if(hr_utility.debug_enabled) then
2057 -- write debug statements
2058 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
2059 end if;
2060
2061 ln_step_id := get_person_contact_step_id(p_transaction_id);
2062
2063 if(ln_step_id is not null) then
2064 lv_status := ame_util.booleanAttributeTrue;
2065 else
2066 lv_status := ame_util.booleanAttributeFalse;
2067 end if;
2068
2069 if(hr_utility.debug_enabled) then
2070 -- write debug statements
2071 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
2072 end if;
2073 return lv_status;
2074 EXCEPTION
2075
2076 WHEN OTHERS THEN
2077 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
2078 RAISE;
2079
2080 END isPersonContactChange;
2081
2082
2083
2084 FUNCTION get_caed_step_id
2085 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
2086 return number is
2087 -- local variables
2088 lv_procedure_name constant varchar2(16) := 'get_caed_step_id';
2089 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
2090 BEGIN
2091 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
2092 if(hr_utility.debug_enabled) then
2093 -- write debug statements
2094 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
2095 end if;
2096 begin
2097 select transaction_step_id
2098 into ln_step_id
2099 from hr_api_transaction_steps
2100 where hr_api_transaction_steps.transaction_id=p_transaction_id
2101 and hr_api_transaction_steps.api_name='HR_CAED_SS.PROCESS_API';
2102 exception
2103 when no_data_found then
2104 return null;
2105 when others then
2106 raise;
2107 end;
2108 if(hr_utility.debug_enabled) then
2109 -- write debug statements
2110 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
2111 end if;
2112 return fnd_number.number_to_canonical(ln_step_id);
2113 EXCEPTION
2114
2115 WHEN OTHERS THEN
2116 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
2117 RAISE;
2118
2119 END get_caed_step_id;
2120
2121
2122 FUNCTION isReleaseInformation
2123 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
2124 return varchar2 is
2125 -- local variables
2126 lv_procedure_name constant varchar2(20) := 'isReleaseInformation';
2127 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
2128 lv_status varchar2(10);
2129 BEGIN
2130 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
2131 if(hr_utility.debug_enabled) then
2132 -- write debug statements
2133 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
2134 end if;
2135
2136 ln_step_id := get_caed_step_id(p_transaction_id);
2137
2138 if(ln_step_id is not null) then
2139 lv_status := ame_util.booleanAttributeTrue;
2140 else
2141 lv_status := ame_util.booleanAttributeFalse;
2142 end if;
2143
2144 if(hr_utility.debug_enabled) then
2145 -- write debug statements
2146 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
2147 end if;
2148 return lv_status;
2149 EXCEPTION
2150
2151 WHEN OTHERS THEN
2152 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
2153 RAISE;
2154
2155 END isReleaseInformation;
2156
2157
2158 FUNCTION get_paybasis_id
2159 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
2160 return number is
2161 -- local variables
2162 lv_procedure_name constant varchar2(25) := 'get_proposed_salary_basis';
2163 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
2164 lv_salary_basis VARCHAR2(30) default null;
2165 ln_pay_basis_id number;
2166 lv_item_type varchar2(50);
2167 lv_item_key varchar2(100);
2168 ld_effective_date date;
2169 BEGIN
2170 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
2171 if(hr_utility.debug_enabled) then
2172 -- write debug statements
2173 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
2174 end if;
2175
2176 ln_step_id := get_assignment_step_id(p_transaction_id);
2177 if(ln_step_id is not null) then
2178 ln_pay_basis_id := hr_transaction_api.get_number_value
2179 (p_transaction_step_id => ln_step_id
2180 ,p_name => 'P_PAY_BASIS_ID');
2181
2182 end if;
2183
2184 if(hr_utility.debug_enabled) then
2185 -- write debug statements
2186 hr_utility.set_location('Leaving'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
2187 end if;
2188 return fnd_number.number_to_canonical(ln_pay_basis_id);
2189 EXCEPTION
2190
2191 WHEN OTHERS THEN
2192 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
2193 RAISE;
2194
2195 END get_paybasis_id;
2196
2197 FUNCTION isMidPayPayPeriodChange
2198 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
2199 return varchar2 is
2200 -- local variables
2201 lv_procedure_name constant varchar2(25) := 'isMidPayPayPeriodChange';
2202 ln_step_id hr_api_transaction_steps.transaction_step_id%type;
2203 lv_status varchar2(10);
2204 result varchar2(50);
2205
2206
2207 l_assignment_id per_all_assignments_f.assignment_id%type default null;
2208 l_payroll_id per_all_assignments_f.payroll_id%type default null;
2209 l_old_pay_basis_id per_all_assignments_f.pay_basis_id%type default null;
2210 l_new_pay_basis_id per_all_assignments_f.pay_basis_id%type default null;
2211 l_pay_period_start_date date default null;
2212 l_pay_period_end_date date default null;
2213
2214 l_asg_txn_step_id hr_api_transaction_steps.transaction_step_id%type
2215 default null;
2216 l_effective_date date default null;
2217
2218
2219 CURSOR csr_check_mid_pay_period(p_eff_date_csr in date
2220 ,p_payroll_id_csr in number) IS
2221 select start_date, end_date
2222 from per_time_periods
2223 where p_eff_date_csr > start_date
2224 and p_eff_date_csr <= end_date
2225 and payroll_id = p_payroll_id_csr;
2226
2227 -- Get existing assignment data
2228 CURSOR csr_get_old_asg_data IS
2229 SELECT pay_basis_id
2230 FROM per_all_assignments_f
2231 WHERE assignment_id = l_assignment_id
2232 AND l_effective_date between effective_start_date
2233 and effective_end_date
2234 AND assignment_type = 'E';
2235
2236 BEGIN
2237 hr_utility.set_location(g_package||'.'||lv_procedure_name,1);
2238 if(hr_utility.debug_enabled) then
2239 -- write debug statements
2240 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 2);
2241 end if;
2242
2243 -- check if this transaction has assignment step
2244 if(isAssignmentChange(p_transaction_id)=ame_util.booleanAttributeTrue) then
2245 -- code logic from hr_workflow_ss.check_mid_pay_period_change
2246
2247 l_asg_txn_step_id:= get_assignment_step_id(p_transaction_id);
2248 l_effective_date := to_date(
2249 hr_transaction_ss.get_wf_effective_date
2250 (p_transaction_step_id => l_asg_txn_step_id),
2251 hr_transaction_ss.g_date_format);
2252
2253 -- Get the pay_basis_id and payroll_id
2254 l_new_pay_basis_id := hr_transaction_api.get_number_value
2255 (p_transaction_step_id => l_asg_txn_step_id
2256 ,p_name => 'P_PAY_BASIS_ID');
2257
2258 l_payroll_id := hr_transaction_api.get_number_value
2259 (p_transaction_step_id => l_asg_txn_step_id
2260 ,p_name => 'P_PAYROLL_ID');
2261
2262 l_assignment_id := hr_transaction_api.get_number_value
2263 (p_transaction_step_id => l_asg_txn_step_id
2264 ,p_name => 'P_ASSIGNMENT_ID');
2265
2266 -- Now get the old pay basis id
2267 OPEN csr_get_old_asg_data;
2268 FETCH csr_get_old_asg_data into l_old_pay_basis_id;
2269 IF csr_get_old_asg_data%NOTFOUND THEN
2270 -- could be a new hire or applicant hire, there is no asg rec
2271 CLOSE csr_get_old_asg_data;
2272 ELSE
2273 CLOSE csr_get_old_asg_data;
2274 END IF;
2275
2276 IF l_old_pay_basis_id IS NOT NULL and
2277 l_new_pay_basis_id IS NOT NULL and
2278 l_old_pay_basis_id <> l_new_pay_basis_id and
2279 l_payroll_id IS NOT NULL
2280 THEN
2281 -- perform mid pay period check
2282 OPEN csr_check_mid_pay_period
2283 (p_eff_date_csr => l_effective_date
2284 ,p_payroll_id_csr => l_payroll_id);
2285 FETCH csr_check_mid_pay_period into l_pay_period_start_date
2286 ,l_pay_period_end_date;
2287 IF csr_check_mid_pay_period%NOTFOUND THEN
2288 -- That means the effective date is not in mid pay period
2289 lv_status := ame_util.booleanAttributeFalse;
2290 CLOSE csr_check_mid_pay_period;
2291 ELSE
2292 lv_status := ame_util.booleanAttributeTrue;
2293 CLOSE csr_check_mid_pay_period;
2294 END IF;
2295 END IF;
2296 else
2297 lv_status := ame_util.booleanAttributeFalse;
2298 end if;
2299
2300 if(hr_utility.debug_enabled) then
2301 -- write debug statements
2302 hr_utility.set_location('Entered'||lv_procedure_name||'with transaction_id:'||p_transaction_id, 10);
2303 end if;
2304
2305 return lv_status;
2306
2307
2308 EXCEPTION
2309
2310 WHEN OTHERS THEN
2311 WF_CORE.CONTEXT(g_package,'.lv_procedure_name',p_transaction_id);
2312 RAISE;
2313
2314 END isMidPayPayPeriodChange;
2315
2316
2317 FUNCTION getRequestorPositionId
2318 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
2319 return number
2320 is
2321 -- local variable
2322 ln_position_id number;
2323 c_proc constant varchar2(30) := 'getRequestorPositionId';
2324
2325
2326 cursor requestorPosId is
2327 select paf.position_id
2328 from per_all_assignments_f paf,
2329 per_all_people_f ppf,
2330 per_position_structures pps, per_pos_structure_versions ppsv,
2331 hr_api_transactions hat
2332 where hat.transaction_id = p_transaction_id
2333 and paf.person_id = hat.creator_person_id
2334 and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
2335 and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
2336 and paf.primary_flag = 'Y'
2337 and paf.assignment_type in ('E','C')
2338 and paf.person_id = ppf.person_id
2339 and ppf.business_group_id = pps.business_group_id(+)
2340 and pps.primary_position_flag (+) = 'Y'
2341 and pps.position_structure_id = ppsv.position_structure_id(+)
2342 and trunc(sysdate) between ppsv.date_from(+) and nvl(ppsv.date_to(+),sysdate);
2343 begin
2344 if g_debug then
2345 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
2346 end if;
2347
2348 open requestorPosId;
2349 fetch requestorPosId into ln_position_id;
2350 if(requestorPosId%notfound) then
2351 ln_position_id:= null;
2352 end if;
2353 close requestorPosId;
2354
2355 if (g_debug ) then
2356 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
2357 end if;
2358
2359 return ln_position_id;
2360
2361 exception
2362 when others then
2363
2364 if g_debug then
2365 hr_utility.set_location('Error in getRequestorPositionId SQLERRM' ||' '||to_char(SQLCODE),20);
2366 end if;
2367 -- close the cursor if open
2368 if(requestorPosId%isopen) then
2369 close requestorPosId;
2370 end if;
2371 raise;
2372 end getRequestorPositionId;
2373
2374 END HR_AMEUTIL_SS;