1 PACKAGE BODY HR_SA_ORG_INFO AS
2 /* $Header: pesaorgi.pkb 115.2 2004/01/14 07:09:47 abppradh noship $ */
3
4 --
5 --
6 -- Cursor which fetches Organizations from the named hierarchy - bottom to top
7 --
8 CURSOR organization_hierarchy(p_org_id NUMBER, p_org_structure_version_id NUMBER, p_org_exists_in_hierarchy VARCHAR2) IS
9 SELECT p_org_id organization_id_parent
10 ,0 lev
11 FROM dual
12 WHERE p_org_exists_in_hierarchy = 'Y'
13 UNION
14 SELECT organization_id_parent
15 ,level lev
16 FROM per_org_structure_elements
17 WHERE org_structure_version_id = p_org_structure_version_id
18 START WITH organization_id_child = p_org_id
19 CONNECT BY PRIOR organization_id_parent = organization_id_child
20 AND org_structure_version_id = p_org_structure_version_id
21 ORDER BY lev;
22 --
23 CURSOR org_hierarchy(p_org_id NUMBER, p_org_structure_version_id NUMBER, p_org_exists_in_hierarchy VARCHAR2) IS
24 SELECT p_org_id organization_id_parent
25 ,0 lev
26 FROM dual
27 WHERE p_org_exists_in_hierarchy = 'Y'
28 UNION
29 SELECT organization_id_parent
30 ,level lev
31 FROM per_org_structure_elements
32 WHERE org_structure_version_id = p_org_structure_version_id
33 START WITH organization_id_child = p_org_id
34 CONNECT BY PRIOR organization_id_parent = organization_id_child
35 AND org_structure_version_id = p_org_structure_version_id
36 ORDER BY lev;
37 --
38 --
39 -- Service function to return the current named hioerarchy.
40 --
41 FUNCTION named_hierarchy
42 (p_organization_id NUMBER) RETURN NUMBER IS
43 --
44 --
45 -- Cursor to return the current named hierarchy.
46 --
47 CURSOR c_hierarchy(p_organization_id NUMBER) IS
48 SELECT TO_NUMBER(inf.org_information1) organization_structure_id
49 FROM hr_organization_information inf
50 ,hr_all_organization_units org
51 WHERE org.organization_id = p_organization_id
52 AND inf.organization_id = org.business_group_id
53 AND inf.org_information_context = 'SA_HR_BG_INFO'
54 AND inf.org_information1 IS NOT NULL;
55 --
56 --
57 -- Local Variables.
58 --
59 l_rec c_hierarchy%ROWTYPE;
60 BEGIN
61 --
62 --
63 -- Find the current named organization hierarchy.
64 --
65 OPEN c_hierarchy(p_organization_id => p_organization_id);
66 FETCH c_hierarchy INTO l_rec;
67 CLOSE c_hierarchy;
68 --
69 --
70 -- Return ID.
71 --
72 RETURN l_rec.organization_structure_id;
73 END named_hierarchy;
74 --
75 --
76 -- Service function to return the current version of the named hioerarchy.
77 --
78 FUNCTION latest_named_hierarchy_vers
79 (p_organization_id NUMBER) RETURN NUMBER IS
80 --
81 --
82 -- Cursor to return the current named hierarchy version.
83 --
84 CURSOR c_hierarchy_version(p_organization_id NUMBER, p_organization_structure_id NUMBER) IS
85 SELECT sv.org_structure_version_id, sv.version_number
86 FROM per_org_structure_versions sv
87 ,fnd_sessions ses
88 WHERE sv.organization_structure_id = p_organization_structure_id
89 AND ses.session_id = USERENV('sessionid')
90 AND ses.effective_date BETWEEN sv.date_from AND NVL(sv.date_to, TO_DATE('31/12/4712','DD/MM/YYYY'))
91 ORDER BY sv.version_number DESC;
92 --
93 --
94 -- Local Variables.
95 --
96 l_rec c_hierarchy_version%ROWTYPE;
97 BEGIN
98 --
99 --
100 -- Find the current primary organization hierarchy.
101 --
102 OPEN c_hierarchy_version(p_organization_id => p_organization_id
103 ,p_organization_structure_id => named_hierarchy(p_organization_id));
104 FETCH c_hierarchy_version INTO l_rec;
105 CLOSE c_hierarchy_version;
106 --
107 --
108 -- Return ID.
109 --
110 RETURN l_rec.org_structure_version_id;
111 END latest_named_hierarchy_vers;
112 --
113 --
114 -- Service function to see if organization belongs to the current named hioerarchy.
115 --
116 FUNCTION org_exists_in_hierarchy
117 (p_organization_id NUMBER) RETURN VARCHAR2 IS
118 --
119 --
120 -- Cursor to see if the organization belongs to the current named hierarchy.
121 --
122 CURSOR c_org_exists(p_organization_id NUMBER, p_org_structure_version_id NUMBER) IS
123 SELECT se.organization_id_child
124 FROM per_org_structure_elements se
125 WHERE se.org_structure_version_id = p_org_structure_version_id
126 AND (se.organization_id_parent = p_organization_id OR
127 se.organization_id_child = p_organization_id);
128 --
129 --
130 -- Local Variables.
131 --
132 l_rec c_org_exists%ROWTYPE;
133 BEGIN
134 OPEN c_org_exists(p_organization_id => p_organization_id
135 ,p_org_structure_version_id => latest_named_hierarchy_vers(p_organization_id));
136 FETCH c_org_exists INTO l_rec;
137 IF c_org_exists%FOUND THEN
138 CLOSE c_org_exists;
139 RETURN 'Y';
140 ELSE
141 CLOSE c_org_exists;
142 RETURN 'N';
143 END IF;
144 END org_exists_in_hierarchy;
145
146 /*------------------------------------------------------------------------------
147 The following procedure checks if the Organization passed in exists in the Prima
148 ry Hierarchy. Called in HREMEA.pll to validate the Organization Name on the Assi
149 gnment form
150 --------------------------------------------------------------------------------*/
151 PROCEDURE chk_for_org_in_hierarchy(p_org_id in hr_organization_units.organization_id%TYPE,
152 p_exists out nocopy varchar2) IS
153 l_organization_id hr_organization_units.organization_id%TYPE;
154 l_level number;
155 BEGIN
156 p_exists := org_exists_in_hierarchy(p_org_id);
157 END chk_for_org_in_hierarchy;
158
159 PROCEDURE get_employer_name (p_org_id in hr_organization_units.organization_id%TYPE,
160 p_employer_name out nocopy varchar2,
161 p_business_group_id hr_organization_units.organization_id%TYPE) IS
162 cursor csr_employer (p_organization_id hr_organization_units.organization_id%TYPE,
163 p_bus_group_id hr_organization_units.organization_id%TYPE) is
164 select hou.name
165 from hr_organization_units hou,
166 hr_organization_information hoi1,
167 hr_organization_information hoi2
168 where (hou.business_group_id= p_bus_group_id
169 OR (hou.business_group_id=hou.organization_id and
170 hou.business_group_id <> p_bus_group_id )) and
171 trunc(sysdate) between hou.date_from and nvl(hou.date_to,
172 to_date('4712/12/31','YYYY/MM/DD')) and
173 hou.organization_id = hoi1.organization_id and
174 hou.organization_id = p_organization_id and
175 hoi1.org_information_context = 'CLASS' and
176 hoi1.org_information1 = 'HR_LEGAL' and
177 Hoi1.organization_id = hoi2.organization_id and
178 Hoi2.org_information_context = 'SA_EMPLOYER_GOSI_DETAILS'
179 order by hou.name;
180 l_results varchar2(240);
181 BEGIN
182 if 'Y' <> org_exists_in_hierarchy(p_org_id) then
183 p_employer_name := 'NO_EMPLOYER_FOUND';
184 end if;
185 FOR hier_org IN ORG_HIERARCHY (p_org_id, latest_named_hierarchy_vers(p_org_id),org_exists_in_hierarchy(p_org_id)) LOOP
186 OPEN csr_employer(hier_org.organization_id_parent,p_business_group_id);
187 FETCH csr_employer INTO l_results;
188 -- CLOSE csr_employer;
189
190 if csr_employer%FOUND then
191 p_employer_name := l_results;
192 CLOSE csr_employer;
193 exit;
194 else
195 p_employer_name := 'NO_EMPLOYER_FOUND';
196 CLOSE csr_employer;
197 end if;
198
199 END LOOP;
200
201 end get_employer_name;
202
203 PROCEDURE get_employer_name (p_org_id in hr_organization_units.organization_id%TYPE,
204 p_employer_name out nocopy varchar2,
205 p_business_group_id hr_organization_units.organization_id%TYPE,
206 p_structure_version_id number) IS
207 cursor csr_employer (p_organization_id hr_organization_units.organization_id%TYPE,
208 p_bus_group_id hr_organization_units.organization_id%TYPE) is
209 select hou.name
210 from hr_organization_units hou,
211 hr_organization_information hoi1,
212 hr_organization_information hoi2
213 where (hou.business_group_id= p_bus_group_id
214 OR (hou.business_group_id=hou.organization_id and
215 hou.business_group_id <> p_bus_group_id )) and
216 trunc(sysdate) between hou.date_from and nvl(hou.date_to,
217 to_date('4712/12/31','YYYY/MM/DD')) and
218 hou.organization_id = hoi1.organization_id and
219 hou.organization_id = p_organization_id and
220 hoi1.org_information_context = 'CLASS' and
221 hoi1.org_information1 = 'HR_LEGAL' and
222 Hoi1.organization_id = hoi2.organization_id and
223 Hoi2.org_information_context = 'SA_EMPLOYER_GOSI_DETAILS'
224 order by hou.name;
225 l_results varchar2(240);
226 BEGIN
227 FOR hier_org IN ORG_HIERARCHY (p_org_id, p_structure_version_id,'Y') LOOP
228 OPEN csr_employer(hier_org.organization_id_parent,p_business_group_id);
229 FETCH csr_employer INTO l_results;
230 -- CLOSE csr_employer;
231
232 if csr_employer%FOUND then
233 p_employer_name := l_results;
234 CLOSE csr_employer;
235 exit;
236 else
237 p_employer_name := 'NO_EMPLOYER_FOUND';
238 CLOSE csr_employer;
239 end if;
240
241 END LOOP;
242
243 end get_employer_name;
244
245
246 FUNCTION get_employer_name (p_org_id in hr_organization_units.organization_id%TYPE,
247 p_business_group_id hr_organization_units.organization_id%TYPE,
248 p_structure_version_id number default null) RETURN VARCHAR2 IS
249 cursor csr_employer (p_organization_id hr_organization_units.organization_id%TYPE,
250 p_bus_group_id hr_organization_units.organization_id%TYPE) is
251 select hou.name
252 from hr_organization_units hou,
253 hr_organization_information hoi1,
254 hr_organization_information hoi2
255 where (hou.business_group_id= p_bus_group_id
256 OR (hou.business_group_id=hou.organization_id and
257 hou.business_group_id <> p_bus_group_id )) and
258 trunc(sysdate) between hou.date_from and nvl(hou.date_to,
259 to_date('4712/12/31','YYYY/MM/DD')) and
260 hou.organization_id = hoi1.organization_id and
261 hou.organization_id = p_organization_id and
262 hoi1.org_information_context = 'CLASS' and
263 hoi1.org_information1 = 'HR_LEGAL' and
264 Hoi1.organization_id = hoi2.organization_id and
265 Hoi2.org_information_context = 'SA_EMPLOYER_GOSI_DETAILS'
266 order by hou.name;
267 l_employer_name varchar2(240);
268 l_results varchar2(240);
269 BEGIN
270 IF p_structure_version_id is not null then
271 FOR hier_org IN ORG_HIERARCHY (p_org_id, p_structure_version_id,'Y') LOOP
272 OPEN csr_employer(hier_org.organization_id_parent,p_business_group_id);
273 FETCH csr_employer INTO l_results;
274 -- CLOSE csr_employer;
275
276 if csr_employer%FOUND then
277 l_employer_name := l_results;
278 CLOSE csr_employer;
279 exit;
280 else
281 l_employer_name := null;
282 CLOSE csr_employer;
283 end if;
284
285 END LOOP;
286 ELSE
287 if 'Y' <> org_exists_in_hierarchy(p_org_id) then
288 l_employer_name := null;
289 end if;
290 FOR hier_org IN ORG_HIERARCHY (p_org_id, latest_named_hierarchy_vers(p_org_id),org_exists_in_hierarchy(p_org_id)) LOOP
291 OPEN csr_employer(hier_org.organization_id_parent,p_business_group_id);
292 FETCH csr_employer INTO l_results;
293 -- CLOSE csr_employer;
294 if csr_employer%FOUND then
295 l_employer_name := l_results;
296 CLOSE csr_employer;
297 exit;
298 else
299 l_employer_name := null;
300 CLOSE csr_employer;
301 end if;
302
303 END LOOP;
304 END IF;
305
306 RETURN l_employer_name;
307
308 end get_employer_name;
309
310 END HR_SA_ORG_INFO;
311
312
[Home] [Help]