1 Package body pqh_ss_utility as
2 /* $Header: pqutlswi.pkb 120.0.12000000.2 2007/06/14 09:13:05 gpurohit noship $*/
3 --
4 CURSOR cur_asgn (c_assignmentId NUMBER, c_effectiveDate DATE) IS
5 SELECT to_char(object_version_number), effective_start_date
6 FROM per_all_assignments_f
7 WHERE assignment_id = c_assignmentId
8 AND c_effectiveDate BETWEEN effective_start_date AND effective_end_date;
9
10 procedure get_rg_eligibility(p_person_id in number,
11 p_rptg_grp_id in number,
12 p_eligibility_flag out nocopy varchar2,
13 p_eligibility out nocopy varchar2) is
14 --
15 --
16 l_dummy0 varchar2(10);
17 l_dummy1 varchar2(10);
18 l_dummy2 varchar2(10);
19 l_bus_grp number;
20 --
21 cursor c_bus_grp_id(p_person_id number) is
22 select business_group_id
23 from per_all_people_f
24 where person_id = p_person_id;
25 --
26 cursor c_run(p_person_id number, p_rptg_grp_id number,
27 p_business_group_id number) is
28 select 'x'
29 from BEN_POPL_RPTG_GRP_F prg
30 where rptg_grp_id = p_rptg_grp_id
31 and business_group_id = p_business_group_id
32 and prg.pgm_id is null
33 and sysdate between prg.effective_start_date and prg.effective_end_date;
34 --
35 cursor c_eligible(p_person_id number, p_rptg_grp_id number) is
36 select 'x'
37 from dual
38 where exists
39 (select eper.pl_id
40 from BEN_POPL_RPTG_GRP_F prg, ben_elig_per_f eper
41 where rptg_grp_id = p_rptg_grp_id
42 and person_id = p_person_id
43 and prg.pl_id = eper.pl_id
44 and eper.pgm_id is null
45 and sysdate between prg.effective_start_date and prg.effective_end_date
46 and sysdate between eper.effective_start_date and eper.effective_end_date
47 and eper.elig_flag = 'Y'
48 );
49 --
50 cursor c_ineligible(p_person_id number, p_rptg_grp_id number) is
51 select 'x'
52 from dual
53 where exists
54 (select eper.pl_id
55 from BEN_POPL_RPTG_GRP_F prg, ben_elig_per_f eper
56 where rptg_grp_id = p_rptg_grp_id
57 and person_id = p_person_id
58 and prg.pl_id = eper.pl_id
59 and eper.pgm_id is null
60 and sysdate between prg.effective_start_date and prg.effective_end_date
61 and sysdate between eper.effective_start_date and eper.effective_end_date
62 and eper.elig_flag = 'N'
63 );
64 --
65 begin
66 --
67 if p_rptg_grp_id is null then
68 p_eligibility_flag := 'Y';
69 else
70 --
71 open c_bus_grp_id(p_person_id);
72 fetch c_bus_grp_id into l_bus_grp;
73 close c_bus_grp_id;
74 --
75 open c_run(p_person_id, p_rptg_grp_id,l_bus_grp);
76 fetch c_run into l_dummy0;
77 --
78 if c_run%notfound then
79 close c_run;
80 p_eligibility_flag := 'Y';
81 else
82 --
83 close c_run;
84 --
85 open c_eligible(p_person_id, p_rptg_grp_id);
86 fetch c_eligible into l_dummy1;
87 --
88 if c_eligible%found then
89 close c_eligible;
90 p_eligibility_flag := 'Y';
91 else
92 close c_eligible;
93 open c_ineligible(p_person_id, p_rptg_grp_id);
94 fetch c_ineligible into l_dummy2;
95 if c_ineligible%found then
96 p_eligibility_flag := 'N';
97 end if;
98 close c_ineligible;
99 end if;
100 --
101 end if;
102 --
103 end if;
104 --
105 if p_eligibility_flag = 'Y' then
106 p_eligibility := hr_general.decode_lookup('YES_NO', 'Y');
107 elsif p_eligibility_flag = 'N' then
108 p_eligibility := hr_general.decode_lookup('YES_NO', 'N');
109 else
110 p_eligibility_flag := null;
111 p_eligibility := null;
112 end if;
113 --
114 end;
115 --
116 --
117 function get_Reporting_Group_id(
118 p_function_name varchar2,
119 p_business_group_id number) return number is
120 --
121 cursor c1(p_function_name varchar2,p_business_group_id varchar2) is
122 select rptg_grp_id
123 from ben_rptg_grp
124 where function_code = p_function_name
125 and rptg_prps_cd = 'PERACT'
126 and nvl(business_group_id, p_business_group_id) = p_business_group_id
127 order by business_group_id;
128 --
129 l_reporting_group_id number;
130 --
131 begin
132 --
133 open c1(p_function_name, p_business_group_id);
134 fetch c1 into l_reporting_group_id;
135 close c1;
136 --
137 return l_reporting_group_id;
138 end;
139 --
140 --
141 function check_eligibility(
142 p_person_id number,
143 p_rptg_grp_id number) return boolean is
144 --
145 cursor c1(p_person_id number, p_rptg_grp_id number) is
146 select 'x'
147 from dual
148 where exists
149 (select eper.pl_id
150 from BEN_POPL_RPTG_GRP prg, ben_elig_per_f eper
151 where rptg_grp_id = p_rptg_grp_id
152 and person_id = p_person_id
153 and prg.pl_id = eper.pl_id
154 and eper.pgm_id is null
155 and sysdate between prg.effective_start_date and prg.effective_end_date
156 and sysdate between eper.effective_start_date and eper.effective_end_date
157 and eper.elig_flag = 'Y'
158 );
159 --
160 l_dummy varchar2(10);
161 --
162 begin
163 --
164 open c1(p_person_id, p_rptg_grp_id);
165 fetch c1 into l_dummy;
166 if c1%found then close c1; return true; end if;
167 close c1;
168 --
169 return false;
170 --
171 end;
172 --
173 --
174 --
175 FUNCTION get_pos_structure_version
176 ( p_business_group_id number)
177 RETURN number is
178 --
179 l_pos_structure_version_id number;
180 --
181 cursor c_pos_struct_ver(p_business_group_id number) is
182 select ver.pos_structure_version_id
183 from per_position_structures str, per_pos_structure_versions ver
184 where str.position_structure_id = ver.position_structure_id
185 and sysdate between ver.date_from and nvl(ver.date_to, hr_general.end_of_time)
186 and str.primary_position_flag = 'Y'
187 and str.business_group_id = p_business_group_id;
188 --
189 BEGIN
190 open c_pos_struct_ver(p_business_group_id);
191 fetch c_pos_struct_ver into l_pos_structure_version_id;
192 close c_pos_struct_ver;
193 --
194 return l_pos_structure_version_id;
195 --
196 END;
197 --
198 --
199 PROCEDURE get_Role_Info (
200 p_roleTypeCd IN VARCHAR2
201 ,p_businessGroupId IN NUMBER
202 ,p_globalRoleFlag OUT NOCOPY VARCHAR2
203 ,p_roleName OUT NOCOPY VARCHAR2
204 ,p_roleId OUT NOCOPY NUMBER ) IS
205 --
206 --
207 CURSOR cur_role IS
208 SELECT decode(business_group_id,null,'Y','N') global_role, role_name, role_id
209 FROM pqh_roles
210 WHERE role_type_cd = p_roleTypeCd
211 AND enable_flag = 'Y'
212 AND ( business_group_id = p_businessGroupId OR business_group_id IS NULL);
213 --
214 --
215 BEGIN
216 --
217 --
218 OPEN cur_role;
219 FETCH cur_role INTO p_globalRoleFlag, p_roleName, p_roleId;
220 CLOSE cur_role;
221 --
222 --
223 END;
224 --
225 --
226 function check_edit_privilege (
227 p_personId IN NUMBER
228 ,p_businessGroupId IN NUMBER ) return VARCHAR2 is
229 l_editAllowed varchar2(10) := 'N';
230 begin
231 pqh_ss_utility.check_edit_privilege (
232 p_personId =>p_personId
233 ,p_businessGroupId =>p_businessGroupId
234 ,p_editAllowed =>l_editAllowed );
235 return l_editAllowed;
236 end;
237 --
238 /* ****************************************************************** */
239 --
240 -- Check edit privilege
241 -- This procedure first checks if the Editable by approver
242 -- exclusion role is defined, if so, it then checks if the
243 -- Person is added to the exclusion role. If yes, the procedure
244 -- returns p_editAllowed as N
245 -- If either the exclusion role is not defined or the person is
246 -- not in the list, it then checks if the inclusion role is defined
247 -- if defined, it checks for the person in the list of that role
248 -- If person is found, it returns editAllowed as Y else returns
249 -- editAllowed as N.
250 -- If the inclusion role is not defined editAllowed=Y.
251 --
252 -- In general if both exclusion d roles are not defined
253 -- Person has the privilege to edit
254 -- If the person is added to both exclusion role and inclusion role
255 -- person does not have the privilege, exclusion role takes the precedence.
256 -- if both roles are defined but person is not in either of the list,
257 -- person does not have the privlege.
258 -- if person is found in exactly one list (either inclusion or exclusion)
259 -- person will (or will not) have the privilege to edit depending on the list
260 -- he/she is included.
261 -- If only exclusion list is defined, and the person is not in that list,
262 -- person will have edit privilege
263 -- If only inclusion list is defined, and the person is not in the list,
264 -- person will not have edit privilege.
265 /* ****************************************************************************** */
266 PROCEDURE check_edit_privilege (
267 p_personId IN NUMBER
268 ,p_businessGroupId IN NUMBER
269 ,p_editAllowed OUT NOCOPY VARCHAR2 ) IS
270 --
271 --
272 l_roleId NUMBER;
273 l_roleName PQH_ROLES.role_name%TYPE;
274 l_flag VARCHAR2(10);
275 dummy VARCHAR2(1);
276 --
277 --
278 CURSOR cur_role (p_role_type_cd VARCHAR2) IS
279 SELECT role_id
280 FROM pqh_roles
281 WHERE role_type_cd = p_role_type_cd
282 AND enable_flag = 'Y'
283 AND ( business_group_id = p_businessGroupId OR business_group_id IS NULL); -- either cross business_group or the bg of logged user
284 --
285 --
286 CURSOR cur_edit (p_roleId NUMBER) IS
287 SELECT 'X'
288 from per_people_extra_info pei , pqh_roles rls
289 WHERE information_type = 'PQH_ROLE_USERS'
290 and rls.role_id = to_number(pei.pei_information3)
291 and nvl(pei.pei_information5,'Y') ='Y'
292 and rls.role_id = p_roleId
293 and pei.person_id = p_personId;
294 --
295 --
296 BEGIN
297 --Check if exclusion role is defined.
298 get_Role_Info (
299 p_roleTypeCd => 'PQH_EXCL'
300 ,p_businessGroupId => p_businessGroupId
301 ,p_globalRoleFlag => l_flag
302 ,p_roleName => l_roleName
303 ,p_roleId => l_roleId );
304 --
305 --If defined, check if the person is added to the exclusion role
306 IF ( l_roleId IS NOT NULL) THEN
307 OPEN cur_edit(l_roleId);
308 FETCH cur_edit INTO dummy;
309 CLOSE cur_edit;
310 --If found then edit not allowed, no further check needed, return.
311 IF (dummy = 'X') THEN
312 p_editAllowed := 'N';
313 return;
314 END IF;
315 END IF;
316 --
317 --If exclusion rule is not defined, or the person is not included in the exclusion role then
318 --check if inclusion rule is defined.
319 get_Role_Info (
320 p_roleTypeCd => 'PQH_INCL'
321 ,p_businessGroupId => p_businessGroupId
322 ,p_globalRoleFlag => l_flag
323 ,p_roleName => l_roleName
324 ,p_roleId => l_roleId );
325 --If defined, check if the person is included in the inclusion role.
326 IF ( l_roleId IS NOT NULL) THEN
327 OPEN cur_edit(l_roleId);
328 FETCH cur_edit INTO dummy;
329 CLOSE cur_edit;
330 --If found then the person is allowed to edit the transaction.
331 IF ( dummy = 'X' ) THEN
332 p_editAllowed := 'Y';
333 --If not found then the person is not allowed to edit.
334 ELSE
335 p_editAllowed := 'N';
336 END IF;
337 -- If Not defined, person is eligile to edit the transaction
338 ELSE
339 p_editAllowed := 'Y';
340 END IF;
341 END check_edit_privilege;
342 --
343 --
344 PROCEDURE check_edit_privilege(p_person_id IN NUMBER
345 ,p_business_group_id IN NUMBER
346 ,p_transaction_status IN VARCHAR2
347 ,p_edit_privilege OUT NOCOPY VARCHAR2)
348 IS
349 --
350 BEGIN
351 --
352 IF ( (INSTR(p_transaction_status,'S') > 0) OR ( p_transaction_status IN ('C','N','W','RI') )) THEN
353 p_edit_privilege := 'Y';
354 ELSE
355 IF ( NVL(fnd_profile.value('PQH_ALLOW_APPROVER_TO_EDIT_TXN'),'N') = 'Y' ) THEN
356 PQH_SS_UTILITY.check_edit_privilege (
357 p_personId => p_person_id
358 ,p_businessGroupId => p_business_group_id
359 ,p_editAllowed => p_edit_privilege);
360 ELSE
361 p_edit_privilege := 'N';
362 END IF;
363 END IF;
364
365 END check_edit_privilege;
366 --
367 --
368
369 FUNCTION check_future_change (
370 p_txnId IN NUMBER
371 ,p_assignmentId IN NUMBER
372 ,p_effectiveDate IN DATE
373 ,p_calledFrom IN VARCHAR2 DEFAULT 'REQUEST' ) RETURN VARCHAR2 IS
374 --
375 --
376 CURSOR cur_chg IS
377 SELECT object_version_number
378 FROM per_all_assignments_f
379 WHERE assignment_id = p_assignmentId
380 AND effective_start_date > p_effectiveDate;
381 --
382 dummy VARCHAR2(1);
383 l_asgnStepId NUMBER;
384 l_ovn VARCHAR2(18);
385 l_effDate DATE;
386 --
387 CURSOR cur_txnStep (c_txnId NUMBER ) IS
388 SELECT transaction_step_id
389 FROM hr_api_transaction_steps
390 WHERE transaction_id = c_txnId
391 AND api_name IN ( 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API', 'HR_SUPERVISOR_SS.PROCESS_API');
392 --
393 BEGIN
394 --
395 IF ( p_assignmentId IS NULL) THEN
396 RETURN ('N');
397 END IF;
398
399 OPEN cur_txnStep (p_txnId);
400 FETCH cur_txnStep INTO l_asgnStepId;
401 IF cur_txnStep%NOTFOUND THEN
402 CLOSE cur_TxnStep;
403 RETURN ('N');
404 END IF;
405 CLOSE cur_txnStep;
406
407 --
408 -- The code below will be executed only if there are future dated changes
409 --
410 OPEN cur_chg;
411 FETCH cur_chg INTO l_ovn;
412 IF cur_chg%NOTFOUND THEN
413 CLOSE cur_chg;
414 RETURN ('N');
415 END IF;
416 CLOSE cur_chg;
417 --
418 -- Refresh the object version number when called from Submit
419 IF (l_ovn IS NOT NULL) THEN
420 IF (p_calledFrom = 'SUBMIT') THEN
421 OPEN cur_asgn( p_assignmentId, p_effectiveDate);
422 FETCH cur_asgn INTO l_ovn, l_effDate;
423 CLOSE cur_asgn;
424
425 HR_TRANSACTION_API.set_number_value (
426 p_transaction_step_id => l_asgnStepId
427 ,p_person_id => null
428 ,p_name => 'P_OBJECT_VERSION_NUMBER'
429 ,p_value => l_ovn
430 ,p_original_value => l_ovn
431 );
432 commit;
433 END IF;
434 END IF;
435
436 RETURN('Y');
437 END check_future_change;
438 --
439 --
440 FUNCTION check_pending_transaction(
441 p_txnId IN NUMBER
442 ,p_itemType IN VARCHAR2
443 ,p_personId IN NUMBER
444 ,p_assignId IN NUMBER ) RETURN VARCHAR2 IS
445 --
446 CURSOR cur_txn IS
447 SELECT 'X'
448 FROM hr_api_transaction_steps ts
449 WHERE ts.transaction_id = p_txnId
450 AND ts.api_name IN ('HR_SUPERVISOR_SS.PROCESS_API',
451 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API',
452 'HR_PAY_RATE_SS.PROCESS_API',
453 'HR_TERMINATION_SS.PROCESS_API' );
454 --
455 CURSOR cur_pnd IS
456 SELECT 'X'
457 FROM hr_api_transactions t, hr_api_transaction_steps ts
458 WHERE t.transaction_id = ts.transaction_id
459 AND ts.item_type = p_itemType
460 AND ts.update_person_id = p_personId
461 AND t.status in ('Y','YS','RO','ROS') -- not considering C, RI, RIS as they are with init
462 AND ts.api_name IN ('HR_SUPERVISOR_SS.PROCESS_API','HR_PROCESS_ASSIGNMENT_SS.PROCESS_API',
463 'HR_PAY_RATE_SS.PROCESS_API', 'HR_TERMINATION_SS.PROCESS_API' )
464 AND EXISTS (SELECT NULL
465 FROM hr_api_transaction_values tsv
466 WHERE tsv.transaction_step_id = ts.transaction_step_id
467 AND tsv.name = 'P_REVIEW_PROC_CALL'
468 AND tsv.varchar2_value IS NOT NULL)
469 AND EXISTS (SELECT NULL
470 FROM wf_item_attribute_values iav2
471 WHERE iav2.item_type = ts.item_type
472 AND iav2.item_key = ts.item_key
473 AND iav2.name = 'TRAN_SUBMIT'
474 AND iav2.text_value = 'Y')
475 AND EXISTS (SELECT NULL
476 FROM wf_item_attribute_values iav
477 WHERE iav.item_type = ts.item_type
478 AND iav.item_key = ts.item_key
479 AND iav.name = 'CURRENT_ASSIGNMENT_ID'
480 AND iav.number_value = p_assignId)
481 AND ts.transaction_id <> p_txnId ;
482 --
483 Dummy VARCHAR2(1);
484 --
485 BEGIN
486 --
487 OPEN cur_txn;
488 FETCH cur_txn INTO dummy;
489 --
490 IF cur_txn%NOTFOUND THEN
491 CLOSE cur_txn;
492 return('N');
493 END IF;
494 --
495 CLOSE cur_txn;
496 --
497 -- Reset Dummy before using it again in the next cursor
498 -- else it will pick the previously fetched value if no record
499 -- exist for the current cursor i.e cur_pend%notfound
500 dummy := null;
501 --
502 OPEN cur_pnd;
503 FETCH cur_pnd INTO dummy;
504 CLOSE cur_pnd;
505 --
506 IF (NVL(Dummy,'~') = 'X' ) THEN
507 return('Y');
508 END IF;
509 RETURN('N'); -- No Record Found
510 --
511 END check_pending_transaction;
512 --
513 --
514 FUNCTION check_eligibility (
515 p_planId IN NUMBER
516 ,p_personId IN NUMBER
517 ,p_effectiveDate IN DATE ) RETURN VARCHAR2 IS
518 --
519 CURSOR cur_elig IS
520 SELECT eper.elig_flag eligibility_flag
521 FROM ben_elig_per_f eper
522 WHERE eper.pl_id = p_planId
523 AND eper.pgm_id IS NULL
524 AND eper.person_id = p_personId
525 AND p_EffectiveDate BETWEEN eper.effective_start_date AND eper.effective_end_date
526 ORDER BY eper.effective_start_date desc;
527 --
528 l_isEligible BEN_ELIG_PER_F.elig_flag%TYPE;
529 --
530 BEGIN
531 --
532 OPEN cur_elig;
533 FETCH cur_elig INTO l_isEligible;
534 CLOSE cur_elig;
535 --
536 IF (l_isEligible <> 'Y') THEN
537 --
538 l_isEligible := 'N';
539 --
540 END IF;
541 --
542 RETURN l_isEligible;
543 END;
544 --
545 --
546 FUNCTION get_business_group_id (
547 p_personId IN NUMBER
548 ,p_effectiveDate IN DATE ) RETURN NUMBER IS
549 --
550 CURSOR cur_bg(p_effectiveDate IN DATE) IS
551 SELECT business_group_id
552 FROM per_all_people_f
553 WHERE person_id = p_personId
554 AND p_effectiveDate BETWEEN effective_start_date AND effective_end_date ;
555 --
556 l_businessGrpId NUMBER;
557 l_effectiveDate date;
558 --
559 BEGIN
560 if p_effectiveDate is null then
561 l_effectiveDate := trunc(sysdate);
562 else
563 l_effectiveDate := p_effectiveDate;
564 end if;
565 --
566 OPEN cur_bg(l_effectiveDate) ;
567 FETCH cur_bg INTO l_businessGrpId;
568 CLOSE cur_bg;
569 --
570 RETURN l_businessGrpId;
571 END;
572 --
573 --
574 Function get_desc (p_function VARCHAR2 ) return varchar2
575 is
576 begin
577 if p_function is null then
578 return null;
579 else
580 execute immediate 'begin select '||p_function||'into pqh_ss_utility.l_description from dual; end;';
581 end if;
582 return pqh_ss_utility.l_description;
583 end get_desc;
584
585 PROCEDURE set_datetrack_mode (
586 p_txnId IN VARCHAR2
587 ,p_dateTrack_mode IN VARCHAR2 ) IS
588 --
589 CURSOR cur_asgn IS
590 SELECT null
591 FROM per_all_assignments_f af, hr_api_transactions hat
592 WHERE af.assignment_id = hat.assignment_id
593 AND af.effective_start_date = hat.transaction_effective_date
594 AND hat.transaction_id = p_txnId;
595 --
596 dummy varchar2(10);
597 --
598 l_mode varchar2(30) := p_dateTrack_mode;
599 --
600 BEGIN
601 OPEN cur_asgn;
602 FETCH cur_asgn INTO dummy;
603 IF (cur_asgn%FOUND) THEN
604 l_mode := 'CORRECTION';
605 END IF;
606 CLOSE cur_asgn;
607
608 UPDATE hr_api_transaction_values
609 SET varchar2_value = l_mode
610 WHERE name = 'P_DATETRACK_UPDATE_MODE'
611 AND transaction_step_id = (
612 SELECT transaction_step_id
613 FROM hr_api_transaction_steps
614 WHERE transaction_id = p_txnId
615 AND api_name = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API' )
616 AND varchar2_value <> l_mode;
617
618 commit;
619 END;
620 --
621 --
622 FUNCTION check_function_parameter (
623 p_functionId IN NUMBER,
624 p_paramName IN VARCHAR2 ) RETURN VARCHAR2 IS
625 --
626 CURSOR cur_fn IS
627 SELECT parameters
628 FROM fnd_form_functions
629 WHERE function_id = p_functionId;
630 --
631 l_parameter fnd_form_functions.parameters%TYPE;
632 BEGIN
633 --
634 OPEN cur_fn;
635 FETCH cur_fn INTO l_parameter;
636 CLOSE cur_fn;
637 --
638 IF ( INSTR(l_parameter,p_paramName) > 0 ) THEN
639 RETURN ('Y');
640 END IF;
641 --
642 RETURN ('N');
643 --
644 END check_function_parameter;
645 --
646 --
647
648 FUNCTION get_assignment_startdate ( p_assignmentId IN VARCHAR2 ) RETURN DATE IS
649 --
650 l_assignment_startdate DATE;
651 --
652 CURSOR cur_asgn IS
653 SELECT min(effective_Start_date)
654 FROM per_all_assignments_f
655 WHERE assignment_id = p_assignmentId
656 AND assignment_type in ('E','C');
657 --
658 BEGIN
659 --
660 OPEN cur_asgn;
661 FETCH cur_asgn INTO l_assignment_startDate;
662 CLOSE cur_asgn;
663 --
664 RETURN l_assignment_startDate;
665 --
666 END get_assignment_startdate;
667 --
668 function get_approval_process_version(p_itemType varchar2, p_itemKey varchar2 )
669 return varchar2 is
670 l_approval_process_version varchar2(10);
671 begin
672 l_approval_process_version := wf_engine.GetItemAttrText(
673 itemtype => p_itemType,
674 itemkey => p_itemKey,
675 aname => 'HR_APPROVAL_PRC_VERSION');
676 return l_approval_process_version;
677 exception
678 when others then
679 return 'N';
680 end;
681 --
682 /* Private Function used by Public Function IS_REFRESH_NEEDED */
683 FUNCTION get_hr_value (p_attribute_key IN VARCHAR2
684 ,p_assignment_rec IN PER_ALL_ASSIGNMENTS_F%ROWTYPE)
685 RETURN VARCHAR2
686 IS
687 BEGIN
688 IF p_attribute_key = 'P_ORGANIZATION_ID' THEN RETURN p_assignment_rec.organization_id;
689 ELSIF p_attribute_key = 'P_JOB_ID' THEN RETURN p_assignment_rec.job_id;
690 ELSIF p_attribute_key = 'P_MANAGER_FLAG' THEN RETURN p_assignment_rec.manager_flag;
691 ELSIF p_attribute_key = 'P_SUPERVISOR_ID' THEN RETURN p_assignment_rec.supervisor_id;
692 ELSIF p_attribute_key = 'P_POSITION_ID' THEN RETURN p_assignment_rec.position_id;
693 ELSIF p_attribute_key = 'P_WORK_AT_HOME' THEN RETURN p_assignment_rec.work_at_home;
694 ELSIF p_attribute_key = 'P_LOCATION_ID' THEN RETURN p_assignment_rec.location_id;
695 ELSIF p_attribute_key = 'P_GRADE_ID' THEN RETURN p_assignment_rec.grade_id;
696 ELSIF p_attribute_key = 'P_SPECIAL_CEILING_STEP_ID' THEN RETURN p_assignment_rec.special_ceiling_step_id;
697 ELSIF p_attribute_key = 'P_PAYROLL_ID' THEN RETURN p_assignment_rec.payroll_id;
698 ELSIF p_attribute_key = 'P_ASSIGNMENT_STATUS_TYPE_ID' THEN RETURN p_assignment_rec.assignment_status_type_id;
699 ELSIF p_attribute_key = 'P_CHANGE_REASON' THEN RETURN p_assignment_rec.change_reason;
700 ELSIF p_attribute_key = 'P_ESTABLISHMENT_ID' THEN RETURN p_assignment_rec.establishment_id;
701 ELSIF p_attribute_key = 'P_PAY_BASIS_ID' THEN RETURN p_assignment_rec.pay_basis_id;
702 ELSIF p_attribute_key = 'P_SAL_REVIEW_PERIOD' THEN RETURN p_assignment_rec.sal_review_period;
703 ELSIF p_attribute_key = 'P_SAL_REVIEW_PERIOD_FREQUENCY' THEN RETURN p_assignment_rec.sal_review_period_frequency;
704 ELSIF p_attribute_key = 'P_PERF_REVIEW_PERIOD' THEN RETURN p_assignment_rec.perf_review_period;
705 ELSIF p_attribute_key = 'P_PERF_REVIEW_PERIOD_FREQUENCY' THEN RETURN p_assignment_rec.perf_review_period_frequency;
706 ELSIF p_attribute_key = 'P_NORMAL_HOURS' THEN RETURN p_assignment_rec.normal_hours;
707 ELSIF p_attribute_key = 'P_FREQUENCY' THEN RETURN p_assignment_rec.frequency;
708 ELSIF p_attribute_key = 'P_TIME_NORMAL_START' THEN RETURN p_assignment_rec.time_normal_start;
709 ELSIF p_attribute_key = 'P_TIME_NORMAL_FINISH' THEN RETURN p_assignment_rec.time_normal_finish;
710 ELSIF p_attribute_key = 'P_EMPLOYEE_CATEGORY' THEN RETURN p_assignment_rec.employee_category;
711 ELSIF p_attribute_key = 'P_EMPLOYMENT_CATEGORY' THEN RETURN p_assignment_rec.employment_category;
712 ELSIF p_attribute_key = 'P_PEOPLE_GROUP_ID' THEN RETURN p_assignment_rec.people_group_id;
713 ELSIF p_attribute_key = 'P_SOFT_CODING_KEYFLEX_ID' THEN RETURN p_assignment_rec.soft_coding_keyflex_id;
714 ELSIF p_attribute_key = 'P_DEFAULT_CODE_COMB_ID' THEN RETURN p_assignment_rec.default_code_comb_id;
715 -- ELSIF p_attribute_key = 'P_OBJECT_VERSION_NUMBER' THEN RETURN
716 -- p_assignment_rec.object_version_number;
717 ELSIF p_attribute_key = 'P_ASS_ATTRIBUTE_CATEGORY' THEN RETURN p_assignment_rec.ass_attribute_category;
718 ELSIF p_attribute_key = 'P_ASS_ATTRIBUTE1' THEN RETURN p_assignment_rec.ass_attribute1;
719 ELSIF p_attribute_key = 'P_ASS_ATTRIBUTE2' THEN RETURN p_assignment_rec.ass_attribute2;
720 ELSIF p_attribute_key = 'P_ASS_ATTRIBUTE3' THEN RETURN p_assignment_rec.ass_attribute3;
721 ELSIF p_attribute_key = 'P_ASS_ATTRIBUTE4' THEN RETURN p_assignment_rec.ass_attribute4;
722 ELSIF p_attribute_key = 'P_ASS_ATTRIBUTE5' THEN RETURN p_assignment_rec.ass_attribute5;
723 ELSIF p_attribute_key = 'P_ASS_ATTRIBUTE6' THEN RETURN p_assignment_rec.ass_attribute6;
724 ELSIF p_attribute_key = 'P_ASS_ATTRIBUTE7' THEN RETURN p_assignment_rec.ass_attribute7;
725 ELSIF p_attribute_key = 'P_ASS_ATTRIBUTE8' THEN RETURN p_assignment_rec.ass_attribute8;
726 ELSIF p_attribute_key = 'P_ASS_ATTRIBUTE9' THEN RETURN p_assignment_rec.ass_attribute9;
727 ELSIF p_attribute_key = 'P_ASS_ATTRIBUTE10' THEN RETURN p_assignment_rec.ass_attribute10;
728 ELSIF p_attribute_key = 'P_ASS_ATTRIBUTE11' THEN RETURN p_assignment_rec.ass_attribute11;
729 ELSIF p_attribute_key = 'P_ASS_ATTRIBUTE12' THEN RETURN p_assignment_rec.ass_attribute12;
730 ELSIF p_attribute_key = 'P_ASS_ATTRIBUTE13' THEN RETURN p_assignment_rec.ass_attribute13;
731 ELSIF p_attribute_key = 'P_ASS_ATTRIBUTE14' THEN RETURN p_assignment_rec.ass_attribute14;
732 ELSIF p_attribute_key = 'P_ASS_ATTRIBUTE15' THEN RETURN p_assignment_rec.ass_attribute15;
733 ELSIF p_attribute_key = 'P_ASS_ATTRIBUTE16' THEN RETURN p_assignment_rec.ass_attribute16;
734 ELSIF p_attribute_key = 'P_ASS_ATTRIBUTE17' THEN RETURN p_assignment_rec.ass_attribute17;
735 ELSIF p_attribute_key = 'P_ASS_ATTRIBUTE18' THEN RETURN p_assignment_rec.ass_attribute18;
736 ELSIF p_attribute_key = 'P_ASS_ATTRIBUTE19' THEN RETURN p_assignment_rec.ass_attribute19;
737 ELSIF p_attribute_key = 'P_ASS_ATTRIBUTE20' THEN RETURN p_assignment_rec.ass_attribute20;
738 ELSIF p_attribute_key = 'P_ASS_ATTRIBUTE21' THEN RETURN p_assignment_rec.ass_attribute21;
739 ELSIF p_attribute_key = 'P_ASS_ATTRIBUTE22' THEN RETURN p_assignment_rec.ass_attribute22;
740 ELSIF p_attribute_key = 'P_ASS_ATTRIBUTE23' THEN RETURN p_assignment_rec.ass_attribute23;
741 ELSIF p_attribute_key = 'P_ASS_ATTRIBUTE24' THEN RETURN p_assignment_rec.ass_attribute24;
742 ELSIF p_attribute_key = 'P_ASS_ATTRIBUTE25' THEN RETURN p_assignment_rec.ass_attribute25;
743 ELSIF p_attribute_key = 'P_ASS_ATTRIBUTE26' THEN RETURN p_assignment_rec.ass_attribute26;
744 ELSIF p_attribute_key = 'P_ASS_ATTRIBUTE27' THEN RETURN p_assignment_rec.ass_attribute27;
745 ELSIF p_attribute_key = 'P_ASS_ATTRIBUTE28' THEN RETURN p_assignment_rec.ass_attribute28;
746 ELSIF p_attribute_key = 'P_ASS_ATTRIBUTE29' THEN RETURN p_assignment_rec.ass_attribute29;
747 ELSIF p_attribute_key = 'P_ASS_ATTRIBUTE30' THEN RETURN p_assignment_rec.ass_attribute30;
748 ELSIF p_attribute_key = 'P_NOTICE_PERIOD' THEN RETURN p_assignment_rec.notice_period;
749 ELSIF p_attribute_key = 'P_NOTICE_PERIOD_UOM' THEN RETURN p_assignment_rec.notice_period_uom;
750 ELSIF p_attribute_key = 'P_PROBATION_PERIOD' THEN RETURN p_assignment_rec.probation_period;
751 ELSIF p_attribute_key = 'P_PROBATION_UNIT' THEN RETURN p_assignment_rec.probation_unit;
752 ELSIF p_attribute_key = 'P_DATE_PROBATION_END' THEN RETURN p_assignment_rec.date_probation_end;
753 ELSIF p_attribute_key = 'P_INTERNAL_ADDRESS_LINE' THEN RETURN p_assignment_rec.internal_address_line;
754 ELSIF p_attribute_key = 'P_BARGAINING_UNIT_CODE' THEN RETURN p_assignment_rec.bargaining_unit_code;
755 ELSIF p_attribute_key = 'P_COLLECTIVE_AGREEMENT_ID' THEN RETURN p_assignment_rec.collective_agreement_id;
756 ELSIF p_attribute_key = 'P_CAGR_ID_FLEX_NUM' THEN RETURN p_assignment_rec.cagr_id_flex_num;
757 ELSIF p_attribute_key = 'P_CAGR_GRADE_DEF_ID' THEN RETURN p_assignment_rec.cagr_grade_def_id;
758 ELSIF p_attribute_key = 'P_CONTRACT_ID' THEN RETURN p_assignment_rec.contract_id;
759 ELSIF p_attribute_key = 'P_LABOUR_UNION_MEMBER_FLAG' THEN RETURN p_assignment_rec.labour_union_member_flag;
760 ELSIF p_attribute_key = 'P_VENDOR_ID' THEN RETURN p_assignment_rec.vendor_id;
761 ELSIF p_attribute_key = 'P_VENDOR_EMPLOYEE_NUMBER' THEN RETURN p_assignment_rec.vendor_employee_number;
762 ELSIF p_attribute_key = 'P_VENDOR_ASSIGNMENT_NUMBER' THEN RETURN p_assignment_rec.vendor_assignment_number;
763 ELSIF p_attribute_key = 'P_TITLE' THEN RETURN p_assignment_rec.title;
764 ELSIF p_attribute_key = 'P_PROJECT_TITLE' THEN RETURN p_assignment_rec.project_title;
765 -- GSP change
766 ELSIF p_attribute_key = 'P_GRADE_LADDER_PGM_ID' THEN RETURN p_assignment_rec.grade_ladder_pgm_id;
767 --End of GSP change
768
769 ELSIF p_attribute_key = 'P_PO_HEADER_ID' THEN RETURN p_assignment_rec.po_header_id;
770 ELSIF p_attribute_key = 'P_PO_LINE_ID' THEN RETURN p_assignment_rec.po_line_id;
771 ELSIF p_attribute_key = 'P_VENDOR_SITE_ID' THEN RETURN p_assignment_rec.vendor_site_id;
772 ELSIF p_attribute_key = 'P_PROJ_ASGN_END' THEN RETURN p_assignment_rec.projected_assignment_end;
773 END IF;
774 RETURN NULL;
775 END get_hr_value;
776
777
778 --
779 -- Function to check if refresh is needed or not
780 -- The p_futureChange flag will decide whethar to compare attribute by attribute
781 -- (if futurechange=Y, else, simple OVN comparison would suffice
782
783 FUNCTION check_intervening_action (
784 p_txnId IN VARCHAR2
785 ,p_assignmentId IN NUMBER
786 ,p_effectiveDate IN DATE
787 ,p_futureChange IN VARCHAR2 ) RETURN VARCHAR2 IS
788 --
789
790 CURSOR csr_transaction_values IS
791 SELECT name,
792 atv.varchar2_value|| atv.number_value||atv.date_value txn_curr_value,
793 atv.original_varchar2_value|| atv.original_number_value||atv.original_date_value txn_old_value
794 FROM hr_api_transaction_values atv,
795 pqh_attributes att,
796 pqh_table_route ptr
797 WHERE att.column_name = name
798 AND att.master_table_route_id = ptr.table_route_id
799 AND ptr.table_alias IN ('PQH_SS_ASG_PG1','PQH_SS_OTHER_EMP_INFO_PG1')
800 AND atv.transaction_step_id IN ( SELECT transaction_step_id
801 FROM hr_api_transaction_steps
802 WHERE transaction_id = p_txnId
803 AND api_name = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API');
804
805 CURSOR csr_assignment IS
806 SELECT *
807 FROM per_all_assignments_f
808 WHERE assignment_id = p_assignmentId
809 AND p_effectiveDate between effective_start_date and effective_end_date
810 AND assignment_type IN ('E','A','C');
811 --
812 CURSOR cur_val (p_stepId NUMBER, p_param_name VARCHAR2) IS
813 SELECT NVL(original_number_value,number_value)
814 FROM hr_api_transaction_values
815 WHERE transaction_step_id = p_stepId
816 AND name = p_param_name;
817 --
818 CURSOR cur_txnStep (c_txnId NUMBER ) IS
819 SELECT transaction_step_id
820 FROM hr_api_transaction_steps
821 WHERE transaction_id = c_txnId
822 AND api_name = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API';
823 --
824 l_ovn VARCHAR2(15);
825 l_hrOvn VARCHAR2(15);
826 l_hrEffDate DATE;
827 l_txnId NUMBER;
828 l_stepId NUMBER;
829 --
830 l_refresh_needed VARCHAR2(5) := 'N';
831 l_assignment_rec PER_ALL_ASSIGNMENTS_F%ROWTYPE;
832 BEGIN
833 --If future changes are present then check each attribute to see if refresh
834 --is needed due to an intervening action
835 -- IF ( p_futureChange = 'Y') THEN
836 --
837 -- Bug 2980660: Always perform attr vs attr comparision to check
838 -- if intervening action has taken place.
839 OPEN csr_assignment;
840 FETCH csr_assignment INTO l_assignment_rec;
841 CLOSE csr_assignment;
842
843 FOR csr_transaction_values_rec IN csr_transaction_values
844 LOOP
845 --
846 IF nvl(get_hr_value(csr_transaction_values_rec.name,l_assignment_rec),-1) <>
847 nvl(csr_transaction_values_rec.txn_old_value,-1) THEN
848 l_refresh_needed := 'Y';
849 EXIT;
850 END IF;
851
852 END LOOP;
853 --
854
855 IF ( l_refresh_needed = 'Y' ) AND
856 ( l_assignment_rec.effective_start_date = p_effectiveDate) THEN
857 l_refresh_needed := 'YC';
858 END IF;
859 /*
860 -- If no future change exist, a simple comparision of OVN and Effective date would suffice
861 ELSE
862 --
863 OPEN cur_txnStep (p_txnId);
864 FETCH cur_txnStep INTO l_stepId;
865 CLOSE cur_txnStep;
866
867 -- if assignment step id is null then we don't check for intervening actions.
868 IF (l_stepId IS NULL ) THEN
869 RETURN('N');
870 END IF;
871 --
872 OPEN cur_val(l_stepId, 'P_OBJECT_VERSION_NUMBER');
873 FETCH cur_val INTO l_ovn;
874 CLOSE cur_val;
875 --
876 OPEN cur_asgn( p_assignmentId, p_effectiveDate);
877 FETCH cur_asgn INTO l_hrOvn, l_hrEffDate;
878 CLOSE cur_asgn;
879 --
880 IF ( NVL(l_ovn,'X') <> NVL(l_hrOvn,'Y') ) THEN
881 --
882 IF ( l_hrEffDate = p_effectiveDate ) THEN
883 l_refresh_needed := 'YC'; -- Yes Correction
884 ELSE
885 l_refresh_needed := 'Y'; -- Yes No Correction
886 END IF;
887 --
888 END IF;
889 --
890 END IF; -- if future found Y/N
891 */
892 --
893 RETURN (l_refresh_needed);
894 --
895 END check_intervening_action;
896
897 --
898 --
899 FUNCTION get_transaction_step_id (
900 p_itemType IN VARCHAR2
901 ,p_itemKey IN VARCHAR2
902 ,p_apiName IN VARCHAR2 ) RETURN VARCHAR2 IS
903 --
904 CURSOR cur_txnStep IS
905 SELECT transaction_step_id
906 FROM hr_api_transaction_steps
907 WHERE item_type = p_itemType
908 AND item_key = p_itemKey
909 AND api_name = p_apiName;
910 --
911 l_stepId VARCHAR2(15);
912 --
913 BEGIN
914
915 OPEN cur_txnStep;
916 FETCH cur_txnStep INTO l_stepId;
917 CLOSE cur_txnStep;
918 --
919 RETURN l_stepId;
920 --
921 END get_transaction_step_id;
922 --
923 FUNCTION chk_transaction_step_exist (
924 p_transaction_id IN NUMBER ) RETURN VARCHAR2 IS
925 --
926 CURSOR csr_transaction_step_exist IS
927 SELECT 'Y'
928 FROM hr_api_transaction_steps
929 WHERE transaction_id = p_transaction_id;
930 --
931 l_transaction_step_exist VARCHAR2(5) := 'N';
932 --
933 BEGIN
934 IF p_transaction_id IS NOT NULL THEN
935 OPEN csr_transaction_step_exist;
936 FETCH csr_transaction_step_exist INTO l_transaction_step_exist;
937 CLOSE csr_transaction_step_exist;
938 END IF;
939 --
940 RETURN l_transaction_step_exist;
941 --
942 END chk_transaction_step_exist;
943 --
944 --
945 function plans_exists_for_rg(p_reporting_group_id number,
946 p_business_group_id number,
947 p_effective_date date) return varchar2 is
948 --
949 cursor c2(p_reporting_group_id number, p_business_group_id number,
950 p_effective_date date) is
951 SELECT 'x'
952 FROM ben_popl_rptg_grp_f
953 where rptg_grp_id = p_reporting_group_id
954 and business_group_id = p_business_group_id
955 and trunc(p_effective_date) between effective_start_date and effective_end_date;
956 --
957 l_plans_exist boolean;
958 l_dummy varchar2(10);
959 --
960 begin
961 --
962 if p_reporting_group_id is not null then
963 open c2(p_reporting_group_id, p_business_group_id, p_effective_date);
964 fetch c2 into l_dummy;
965 l_plans_exist := c2%found;
966 close c2;
967 --
968 if l_plans_exist then
969 return 'Y';
970 end if;
971 --
972 end if;
973 --
974 return 'N';
975 --
976 end;
977 --
978 --
979 end;
980
[Home] [Help]