1 PACKAGE BODY hri_bpl_org AS
2 /* $Header: hriborg.pkb 115.2 2003/04/07 14:24:00 cbridge noship $ */
3
4 /* Define type for global organization hierarchy table */
5 TYPE g_varchar2_tabtype IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
6
7 /* Define table for caching last subtree of organization hierarchy */
8 g_OrgInHrchy_tab g_varchar2_tabtype;
9
10 /* Defing empty table for resetting the cache */
11 g_OrgInHrchy_empty_tab g_varchar2_tabtype;
12
13 /* Define globals for testing whether the cache is valid */
14 g_Org_Hierarchy_Version_id hri_cs_orgh_v.ORG_HIERARCHY_VERSION_ID%TYPE := -1;
15 g_Sup_Organization_id hri_cs_orgh_v.SUP_ORGANIZATION_ID%TYPE := -1;
16
17 /* define globals for testing if a user can see certain org hrcy versions */
18 g_organization_structure_id
19 per_security_profiles.organization_structure_id%TYPE;
20
21
22 /******************************************************************************/
23 /* Empties and repopulates the global cache for the values passed in */
24 /******************************************************************************/
25 PROCEDURE reset_subtree_cache
26 ( p_org_hierarchy_version_id IN hri_cs_orgh_v.ORG_HIERARCHY_VERSION_ID%TYPE
27 , p_sup_organization_id IN hri_cs_orgh_v.SUP_ORGANIZATION_ID%TYPE)
28 IS
29
30 CURSOR csr_orgs_in_subtree
31 ( cp_org_hierarchy_version_id IN hri_cs_orgh_v.ORG_HIERARCHY_VERSION_ID%TYPE
32 , cp_sup_organization_id IN hri_cs_orgh_v.SUP_ORGANIZATION_ID%TYPE) IS
33 -- Selects all children of the organization passed in
34 SELECT sub_organization_id
35 FROM hri_cs_orgh_v orh
36 WHERE orh.org_hierarchy_version_id = cp_org_hierarchy_version_id
37 AND orh.sup_organization_id = cp_sup_organization_id;
38
39 BEGIN
40 -- Clear cache table
41 g_OrgInHrchy_tab := g_OrgInHrchy_empty_tab;
42
43 -- Open Cursor with params in explicit loop
44 FOR l_OrgInHrchy_rec IN csr_orgs_in_subtree( p_Org_Hierarchy_Version_id
45 , p_Sup_Organization_id) LOOP
46 g_OrgInHrchy_tab(l_OrgInHrchy_rec.Sub_Organization_id) := 'Y';
47 END LOOP;
48
49 -- Set globals with cache information
50 g_Org_Hierarchy_Version_id := p_org_hierarchy_version_id;
51 g_Sup_Organization_id := p_sup_organization_id;
52
53 EXCEPTION
54 WHEN OTHERS THEN
55 IF csr_orgs_in_subtree%ISOPEN THEN
56 CLOSE csr_orgs_in_subtree;
57 END IF;
58 -- re-raise error
59 RAISE;
60 END reset_subtree_cache;
61
62 /******************************************************************************/
63 /* Returns 1 if the test organization is within the subtree of the given */
64 /* organization hierarchy defined by the given top organization */
65 /******************************************************************************/
66 FUNCTION indicate_in_orgh
67 ( p_org_hierarchy_version_id IN hri_cs_orgh_v.ORG_HIERARCHY_VERSION_ID%TYPE
68 , p_top_organization_id IN hri_cs_orgh_v.SUP_ORGANIZATION_ID%TYPE
69 , p_test_organization_id IN hri_cs_orgh_v.SUP_ORGANIZATION_ID%TYPE)
70 RETURN NUMBER IS
71
72 l_return_value NUMBER;
73
74 BEGIN
75 -- If the cache is invalid then reset it
76 IF ( p_org_hierarchy_version_id <> g_org_hierarchy_version_id OR
77 p_top_organization_id <> g_sup_organization_id) THEN
78 reset_subtree_cache( p_org_hierarchy_version_id
79 , p_top_organization_id);
80 END IF;
81
82 -- Trap exception in PL/SQL block
83 BEGIN
84 -- If organization stored in cache then its in the hierarchy
85 IF ( g_OrgInHrchy_tab(p_test_organization_id) = 'Y')
86 THEN
87 l_return_value := 1;
88 END IF;
89 EXCEPTION
90 -- Otherwise an exception will be raised - either the organization is not
91 -- in the hierarchy and so a NO_DATA_FOUND error occurs, or the organization
92 -- id passed is null and so a null index error occurs
93 WHEN OTHERS THEN
94 l_return_value := 0;
95 END;
96
97 RETURN l_return_value;
98
99 END indicate_in_orgh;
100
101
102 -- returns the organization hierarchy structure id
103 -- attached to the current user/responsibility security profile
104 -- if none is attached, returns -1
105 FUNCTION get_org_structure_id RETURN NUMBER IS
106
107 l_organization_structure_id
108 per_security_profiles.organization_structure_id%TYPE;
109
110 -- to get the value of the organization hierarchy structure id
111 -- associated with this security profile (if any)
112 CURSOR csr_get_security_profile(p_security_profile_id NUMBER) IS
113 SELECT organization_structure_id
114 FROM per_security_profiles
115 WHERE security_profile_id = p_security_profile_id;
116
117
118 BEGIN
119
120 -- get the organization hierarchy associated with that
121 -- security profile
122 OPEN csr_get_security_profile(hr_security.get_security_profile);
123 FETCH csr_get_security_profile INTO l_organization_structure_id;
124 CLOSE csr_get_security_profile;
125
126 RETURN(NVL(l_organization_structure_id,-1));
127
128 END get_org_structure_id;
129
130 -- bug 2711570
131 --check if the user/responsibility secure profile has an
132 --organization hierarchy set against it (org security profile form):
133 --a)if it does, then only show that organization hiearchy in the list
134 -- (including all of its versions, if it has more than one version)
135 --b)if it does not, then show all organization hierarchy versions
136
137 FUNCTION exist_orghvrsn_for_security(p_org_structure_version_id
138 per_org_structure_versions.org_structure_version_id%type)
139 RETURN VARCHAR2 IS
140
141 l_check_org_vrsn NUMBER;
142
143 CURSOR csr_check_hrchy_vrsn(p_organization_structure_id NUMBER
144 ,p_org_structure_version_id NUMBER) IS
145 SELECT 1
146 FROM per_org_structure_versions
147 WHERE organization_structure_id = p_organization_structure_id
148 AND org_structure_version_id = p_org_structure_version_id;
149
150
151 BEGIN
152
153
154 IF g_organization_structure_id IS NULL THEN
155 -- not yet checked the user/responsibility
156 -- to see if organization hierarchy set against
157 -- it's security profile
158
159 g_organization_structure_id := get_org_structure_id;
160
161 -- if no organization hierarchy is set on the security
162 -- profile then get_org_structure_id returns -1
163
164 END IF;
165
166 IF g_organization_structure_id = -1 THEN
167 -- org hierarchy security not set for the
168 -- user/responisbilty security profile
169 -- therefore show all hierarchies.
170 RETURN 'TRUE';
171 ELSE
172 -- an organization hierarchy is assigned to security profile
173 -- only show version if it has same organization_structure_id
174 -- and org_structure_version_id
175 OPEN csr_check_hrchy_vrsn(g_organization_structure_id
176 ,p_org_structure_version_id);
177 FETCH csr_check_hrchy_vrsn INTO l_check_org_vrsn;
178 IF csr_check_hrchy_vrsn%NOTFOUND THEN
179 CLOSE csr_check_hrchy_vrsn;
180 RETURN 'FALSE';
181 ELSE
182 CLOSE csr_check_hrchy_vrsn;
183 RETURN 'TRUE';
184 END IF;
185
186 END IF;
187
188 END exist_orghvrsn_for_security;
189
190 -- bug 2711570
191 --check if the user/responsibility secure profile has an
192 --organization hierarchy set against it (org security profile form):
193 --a)if it does, then only show that organization hiearchy in the list
194 --b)if it does not then show all organization hierarchies
195
196 FUNCTION exist_orgh_for_security(p_organization_structure_id
197 per_org_structure_versions.organization_structure_id%type)
198 RETURN VARCHAR2 IS
199
200 BEGIN
201
202
206 -- it's security profile
203 IF g_organization_structure_id IS NULL THEN
204 -- not yet checked the user/responsibility
205 -- to see if organization hierarchy set against
207
208 g_organization_structure_id := get_org_structure_id;
209
210 -- if no organization hierarchy is set on the security
211 -- profile then get_org_structure_id returns -1
212 END IF;
213
214 IF g_organization_structure_id = -1 THEN
215 -- org hierarchy security not set for the
216 -- user/responisbilty security profile
217 -- therefore show all hierarchies.
218 RETURN 'TRUE';
219 ELSE
220 -- an organization hierarchy is assigned to security profile
221 -- only show hierarchy if it has same organization_structure_id
222 IF g_organization_structure_id <> p_organization_structure_id THEN
223 RETURN 'FALSE';
224 ELSE
225 RETURN 'TRUE';
226 END IF;
227
228 END IF;
229
230 END exist_orgh_for_security;
231
232 END hri_bpl_org;
[Home] [Help]