1 PACKAGE BODY PER_PERSON_ANALYSES_PKG as
2 /* $Header: pepea01t.pkb 115.6 2003/09/03 02:36:29 smparame ship $ */
3 /*===========================================================================+
4 | Copyright (c) 1993 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +===========================================================================*/
8 /*
9 |
10 | 07/07/2000 C.Simpson Modified single_info_type to compensate for change in
11 | base view PER_SPECIAL_INFO_TYPES_V where INFO_EXISTS
12 | is no longer populated, in r115 only. Broken dual
13 | maintenance on this file as a result. #1347225.
14 |
15 |
16 */
17 -------------------------------------------------------------------------
18 -- CHECK_FOR_DUPLICATES
19 --
20 -- Looks for duplicate person analyses records for the current person
21 -- on the same start and end dates
22 -------------------------------------------------------------------------
23 --
24 procedure check_for_duplicates(p_bg_id number
25 ,p_id_flex_num number
26 ,p_analysis_criteria_id number
27 ,p_end_of_time date
28 ,p_date_from date
29 ,p_date_to date
30 ,p_person_id number
31 ,p_rowid varchar2) is
32 cursor c is
33 select 'x'
34 from per_person_analyses pa
35 , per_analysis_criteria ac
36 where pa.analysis_criteria_id = p_analysis_criteria_id
37 and pa.business_group_id + 0 = p_bg_id
38 and pa.analysis_criteria_id = ac.analysis_criteria_id
39 and ac.id_flex_num = p_id_flex_num
40 and nvl(pa.date_to,p_end_of_time) =
41 nvl(p_date_to,p_end_of_time)
42 and pa.date_from = p_date_from
43 and pa.person_id = p_person_id
44 and (p_rowid is null or
45 (p_rowid is not null and
46 pa.rowid <> chartorowid(p_rowid)));
47 --
48 l_exists varchar2(1);
49 begin
50 hr_utility.set_location('per_person_analyses_pkg.check_for_duplicates',1);
51 open c;
52 fetch c into l_exists;
53 if c%found then
54 close c;
55 hr_utility.set_message(801,'HR_6012_ROW_INSERTED');
56 hr_utility.raise_error;
57 end if;
58 close c;
59 end check_for_duplicates;
60 --
61 -------------------------------------------------------------------------
62 -- GET_UNIQUE_ID
63 --
64 -- Returns next unique ID for Person Analyses
65 -------------------------------------------------------------------------
66 --
67 function get_unique_id return number is
68 l_id number;
69 cursor c is
70 select per_person_analyses_s.nextval
71 from sys.dual;
72 --
73 begin
74 open c;
75 fetch c into l_id;
76 close c;
77 return(l_id);
78 end get_unique_id;
79 --
80 -------------------------------------------------------------------------
81 -- SINGLE_INFO_TYPE
82 --
83 -- Determines whether the Customization specifies that only one
84 -- INFO_TYPE should be displayed.
85 --
86 -------------------------------------------------------------------------
87 --
88 procedure single_info_type(p_bg_id number
89 ,p_person_id number
90 ,p_customized_restriction_id number
91 ,p_count_info_type IN OUT NOCOPY number ) is
92 -- ,p_info_exist IN OUT varchar2
93 -- ,p_id_flex_num IN OUT number) is
94 --
95 begin
96 --
97 select count(customized_restriction_id)
98 into p_count_info_type
99 from pay_restriction_values
100 where restriction_code = 'INFO_TYPE'
101 and customized_restriction_id = p_customized_restriction_id;
102 --
103 end single_info_type;
104 --
105 ------------------------------------------------------------------------
106 --
107 -- UNIQUE CASE NUMBER
108 --
109 -- determines if the Case Number specified for an OSHA person analysis
110 -- record is unique within the business group
111 --
112 procedure unique_case_number (p_business_group_id in number,
113 p_legislation_code in varchar2,
114 p_id_flex_num in number,
115 p_segment1 in varchar2) is
116 --
117 -- cursor to check if the special information being entered is OSHA
118 --
119 cursor c_osha is
120 select 'X'
121 from pay_legislation_rules
122 where legislation_code = p_legislation_code
123 and rule_type = 'OSHA'
124 and rule_mode = to_char(p_id_flex_num);
125 --
126 cursor c_duplicate_case is
127 select 'X'
128 from per_person_analyses pa,
129 per_analysis_criteria ac
130 where pa.business_group_id + 0 = p_business_group_id
131 and pa.analysis_criteria_id = ac.analysis_criteria_id
132 and ac.id_flex_num = p_id_flex_num
133 and ac.segment1 = p_segment1;
134 --
135 l_dummy varchar2(1);
136 --
137 begin
138 --
139 open c_osha;
140 fetch c_osha into l_dummy;
141 if c_osha%FOUND then
142 --
143 close c_osha;
144 --
145 open c_duplicate_case;
146 fetch c_duplicate_case into l_dummy;
147 if c_duplicate_case%FOUND then
148 --
149 close c_duplicate_case;
150 --
151 hr_utility.set_message(801,'HR_7443_PA_UNIQUE_CASE_NUMBER');
152 hr_utility.raise_error;
153 --
154 --
155 end if;
156 --
157 close c_duplicate_case;
158 --
159 else
160 --
161 close c_osha;
162 --
163 end if;
164 --
165 end;
166
167 --
168 ------------------------------------------------------------------------
169 --
170 -- POPULATE_INFO_EXISTS
171 --
172 -- function accepts flex structure id, person_id, and business group and
173 -- returns 'Y' if a per_person_analyses record exists.
174 --
175 FUNCTION populate_info_exists
176 ( p_id_flex_num in per_person_analyses.id_flex_num%TYPE
177 , p_person_id in per_person_analyses.person_id%TYPE
178 , p_business_group_id in per_person_analyses.business_group_id%TYPE
179 ) RETURN VARCHAR2 IS
180 --
181 -- Bug fix 2863766
182 -- where clause to check the id_flex_num in per_analysis_criteria table
183 -- added to the cursor c_ppa.
184 cursor c_ppa is
185 select 'Y'
186 from per_person_analyses ppa,per_analysis_criteria pac
187 where ppa.id_flex_num = p_id_flex_num
188 and ppa.person_id = p_person_id
189 and ppa.business_group_id = p_business_group_id
190 and pac.analysis_criteria_id = ppa.analysis_criteria_id
191 and pac.id_flex_num = ppa.id_flex_num;
192 --
193 l_return varchar2(1) := 'N';
194 --
195 BEGIN
196 --
197 open c_ppa;
198 fetch c_ppa into l_return;
199 close c_ppa;
200
201 return l_return;
202
203 END populate_info_exists;
204 --
205 END PER_PERSON_ANALYSES_PKG;
[Home] [Help]