1 PACKAGE BODY pa_org AS
2 /* $Header: PAORGB.pls 120.2 2007/02/06 10:19:48 dthakker ship $ */
3 --
4 /*
5 NAME
6 pa_os_predel_validation
7 DESCRIPTION
8 Foreign key reference check.
9 */
10 --
11 PROCEDURE pa_predel_validation (p_org_id number) IS
12 BEGIN
13 --
14 -- This procedure is not necessary in this version of PA. All
15 -- organization_id references in PA are limited to only those
16 -- organizations that belong to the organization hierarchy used by
17 -- PA. The Define Organizations form already ensures that an
18 -- organization cannot be deleted if it belongs to a hierarchy.
19 -- This procedure remains for possible future use.
20 --
21 NULL;
22 END pa_predel_validation;
23 --
24 PROCEDURE pa_os_predel_validation (p_org_structure_id number) IS
25 -- This procedure checks that if an Org structure has been specified for
26 -- PA use then it should not be allowed to be deleted.
27
28 dummy1 VARCHAR2(4); -- into arg for main SELECT
29 cursor check_org_structure_exists is
30 select 'X'
31 from pa_implementations_all pai
32 where ( (p_org_structure_id = pai.organization_structure_id)
33 OR (p_org_structure_id = pai.proj_org_structure_id)
34 OR (p_org_structure_id = pai.exp_org_structure_id)
35 );
36 BEGIN
37 --
38 -- hr_utility.set_location('PA_ORG.PA_OS_PREDEL_VALIDATION', 1);
39 --
40 IF (pa_imp.pa_implemented_all) THEN
41
42 -- Check if the Org Structure being deleted
43 -- is used in PA Implementations
44 --
45 open check_org_structure_exists;
46 fetch check_org_structure_exists into dummy1;
47 if check_org_structure_exists%found then
48 hr_utility.set_message (275,'PA_ORG_CANT_DEL_HIER');
49 hr_utility.raise_error;
50 end if;
51 close check_org_structure_exists;
52
53 --
54 -- Check if structure being deleted is the struture used for
55 -- burdening in PA (10.7+):
56
57 dummy1 := NULL;
58
59 BEGIN
60 -- Check if this Org Structure is used for burdening'
61 /* Bug 5405854: The check has to be made from pa_ind_rate_sch_revisions
62 as this has the details about the burden schedule revisions.
63 We should not be looking at the hierarchy attached at the
64 Business group.
65 SELECT 'X'
66 INTO dummy1
67 FROM hr_organization_information
68 WHERE organization_id = pa_utils.business_group_id
69 AND org_information_context = 'Project Burdening Hierarchy'
70 AND to_number(org_information1) = p_org_structure_id;
71 */
72
73 /* Replacing the above SELECT with the below SELECT statement. */
74 SELECT 'X'
75 INTO dummy1
76 FROM sys.dual
77 WHERE exists (
78 SELECT 'X'
79 FROM pa_ind_rate_sch_revisions ind
80 WHERE ind.organization_structure_id = p_org_structure_id
81 );
82 EXCEPTION
83 WHEN NO_DATA_FOUND THEN
84 NULL;
85 END;
86
87 IF ( dummy1 IS NOT NULL ) THEN
88 hr_utility.set_message (275,'PA_ORG_CANT_DEL_HIER');
89 hr_utility.raise_error;
90 END IF;
91
92
93 ELSE
94 -- pass validation.
95 NULL;
96 END IF;
97 END pa_os_predel_validation;
98 -------------
99 PROCEDURE pa_osv_predel_validation (p_org_structure_version_id number) IS
100 -- This procedure checks that if an Org structure Version has been specified for
101 -- PA use then it should not be allowed to be deleted.
102 --
103 dummy1 VARCHAR2(4); -- into arg for main SELECT
104 cursor check_org_structure_ver_exists is
105 select 'X'
106 from pa_implementations_all pai
107 where ( (p_org_structure_version_id = pai.org_structure_version_id)
108 OR (p_org_structure_version_id = pai.proj_org_structure_version_id)
109 OR (p_org_structure_version_id = pai.exp_org_structure_version_id)
110 );
111 BEGIN
112 --
113 -- hr_utility.set_location('PA_ORG.PA_OSV_PREDEL_VALIDATION', 1);
114 --
115 IF (pa_imp.pa_implemented_all) THEN
116 --
117 -- Check if this Org Struct Version is not in the OSV
118 -- named in PA_Implementations
119 --
120 open check_org_structure_ver_exists;
121 fetch check_org_structure_ver_exists into dummy1;
122 if check_org_structure_ver_exists%found then
123 hr_utility.set_message (275,'PA_ORG_CANT_DEL_HIER');
124 hr_utility.raise_error;
125 end if;
126 close check_org_structure_ver_exists;
127
128 -- Check if structure version being deleted is the structure version
129 -- used by PA for burdening (10.7+):
130
131 dummy1 := NULL;
132
133 BEGIN
134 -- Check if this Org Structure is used for burdening
135 /* Bug 5405854: The check has to be made from pa_ind_rate_sch_revisions
136 as this has the details about the burden schedule revisions.
137 We should not be looking at the hierarchy attached at the
138 Business group.
139 SELECT 'X'
140 INTO dummy1
141 FROM hr_organization_information
142 WHERE organization_id = pa_utils.business_group_id
143 AND org_information_context = 'Project Burdening Hierarchy'
144 AND to_number(org_information2) = p_org_structure_version_id;
145 */
146
147 /* Replacing the above SELECT with the below SELECT statement. */
148 SELECT 'X'
149 INTO dummy1
150 FROM sys.dual
151 WHERE exists (
152 SELECT 'X'
153 FROM pa_ind_rate_sch_revisions ind
154 WHERE ind.org_structure_version_id = p_org_structure_version_id
155 );
156
157 EXCEPTION
158 WHEN NO_DATA_FOUND THEN
159 NULL;
160 END;
161
162 IF ( dummy1 IS NOT NULL ) THEN
163 hr_utility.set_message (275,'PA_ORG_CANT_DEL_OSV');
164 hr_utility.raise_error;
165 END IF;
166
167 ELSE
168 -- pass validation.
169 NULL;
170 END IF;
171 --
172 END pa_osv_predel_validation;
173 --------------
174 PROCEDURE pa_ose_predel_validation (p_org_structure_element_id number) IS
175 -- This procedure checks that if Start Org has been specified for
176 -- PA use then it should not be allowed to be deleted from the hierarchy.
177 --
178 dummy1 VARCHAR2(4); -- into arg for main SELECT
179 --
180 cursor check_start_org_exists is
181 (
182 SELECT 'X' /* Reporting Start Org */
183 FROM pa_implementations_all pai,
184 per_org_structure_elements ose
185 WHERE p_org_structure_element_id = ose.org_structure_element_id
186 AND pai.org_structure_version_id = ose.org_structure_version_id
187 AND pai.start_organization_id = ose.organization_id_child
188 union
189 SELECT 'X' /* Project Start Org */
190 FROM pa_implementations_all pai,
191 per_org_structure_elements ose
192 WHERE p_org_structure_element_id = ose.org_structure_element_id
193 AND pai.proj_org_structure_version_id = ose.org_structure_version_id
194 AND pai.proj_start_org_id = ose.organization_id_child
195 union
196 SELECT 'X' /* Exp Start Org */
197 FROM pa_implementations_all pai,
198 per_org_structure_elements ose
199 WHERE p_org_structure_element_id = ose.org_structure_element_id
200 AND pai.exp_org_structure_version_id = ose.org_structure_version_id
201 AND pai.exp_start_org_id = ose.organization_id_child
202 UNION /* Added for bug 5405854 - Burdening start org */
203 SELECT 'X'
204 FROM pa_ind_rate_sch_revisions ind,
205 per_org_structure_elements ose
206 WHERE ose.org_structure_element_id = p_org_structure_element_id
207 AND ind.org_structure_version_id = ose.org_structure_version_id
208 AND ind.start_organization_id = ose.organization_id_child
209 ) ;
210 BEGIN
211 -- hr_utility.trace_on(null, 'RMBUG');
212 -- hr_utility.trace('START - pa_ose_predel_validation');
213 --
214 -- hr_utility.set_location('PA_ORG.PA_OSE_PREDEL_VALIDATION',1);
215 --
216 IF (pa_imp.pa_implemented_all) THEN
217 --
218 -- Check if this Element is the starting org specified in
219 -- PA_Implementations for Reports, '
220
221 open check_start_org_exists;
222 fetch check_start_org_exists into dummy1;
223 if check_start_org_exists%found then
224 hr_utility.set_message (275,'PA_ORG_CANT_DELETE_STARTORG'); /* Message_name changed for bug fix 1713199 */
225 hr_utility.raise_error;
226 end if;
227 close check_start_org_exists;
228
229 -- Check if structure element is used by PA for burdening (10.7+):
230
231 dummy1 := NULL;
232
233 BEGIN
234 -- Check if this Element is in the Project Burdening Hierarchy'
235 /* Bug 5405854: The check has to be made from pa_ind_rate_sch_revisions
236 as this has the details about the burden schedule revisions.
237 We should not be looking at the hierarchy attached at the
238 Business group.
239 SELECT 'X'
240 INTO dummy1
241 FROM sys.dual
242 WHERE p_org_structure_element_id IN (
243 SELECT org_structure_element_id
244 FROM hr_organization_information info,
245 per_org_structure_elements ose
246 WHERE info.organization_id = pa_utils.business_group_id
247 AND ose.business_group_id = pa_utils.business_group_id
248 AND info.org_information_context = 'Project Burdening Hierarchy'
249 AND to_number(info.org_information2) = ose.org_structure_version_id
250 );
251 */
252 /* Replacing the above SELECT with the below SELECT statement. */
253 /* Check if the organization being deleted is used in Burdening */
254 -- hr_utility.trace('before check');
255 -- hr_utility.trace('before check p_org_structure_element_id IS ' || p_org_structure_element_id);
256 SELECT 'X'
257 INTO dummy1
258 FROM sys.dual
259 WHERE exists (
260 SELECT icm.organization_id
261 FROM pa_ind_cost_multipliers icm,
262 per_org_structure_elements ose
263 WHERE ose.org_structure_element_id = p_org_structure_element_id
264 AND ose.organization_id_child = icm.organization_id);
265
266 EXCEPTION
267 WHEN NO_DATA_FOUND THEN
268 -- hr_utility.trace('after check exception');
269 NULL;
270 END;
271
272 -- hr_utility.trace('after check');
273 IF ( dummy1 IS NOT NULL ) THEN
274 hr_utility.set_message (275,'PA_ORG_DEL_LINK');
275 hr_utility.raise_error;
276 END IF;
277
278 ELSE
279 -- pass validation.
280 NULL;
281 END IF;
282 --
283 END;
284
285 PROCEDURE pa_org_predel_validation (p_org_id number) IS
286 -- This procedure will check if the org being deleted
287 -- has been specified for PA use.
288 -- This procedure will be called from the Define Org form.
289
290 dummy1 VARCHAR2(4); -- into arg for main SELECT
291 --
292 cursor pa_org_exists is
293 select 'X'
294 from pa_all_organizations
295 where organization_id = p_org_id;
296
297 cursor nlr_org_exists is
298 select 'X'
299 from pa_non_labor_resource_orgs
300 where organization_id = p_org_id;
301
302 cursor bill_rate_org_exists is
303 select 'X'
304 from pa_std_bill_rate_schedules
305 where organization_id = p_org_id;
306
307 Begin
308 open pa_org_exists;
309 fetch pa_org_exists into dummy1;
310 if pa_org_exists%found then
311 hr_utility.set_message (275,'PA_ORG_CANT_DEL_PAORG');
312 hr_utility.raise_error;
313 end if;
314 close pa_org_exists;
315 --
316 open nlr_org_exists;
317 fetch nlr_org_exists into dummy1;
318 if nlr_org_exists%found then
319 hr_utility.set_message (275,'PA_ORG_CANT_DEL_NLRORG');
320 hr_utility.raise_error;
321 end if;
322 close nlr_org_exists;
323 --
324 open bill_rate_org_exists;
325 fetch bill_rate_org_exists into dummy1;
326 if bill_rate_org_exists%found then
327 hr_utility.set_message (275,'PA_ORG_CANT_DEL_BRORG');
328 hr_utility.raise_error;
329 end if;
330 close bill_rate_org_exists;
331
332 End;
333
334 END pa_org;
[Home] [Help]