1 PACKAGE BODY pa_org AS
2 /* $Header: PAORGB.pls 120.6 2011/01/21 12:24:03 vikarora 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 --
181 (
178 dummy1 VARCHAR2(4); -- into arg for main SELECT
179 --
180 cursor check_start_org_exists is
182 SELECT 'X'
183 FROM pa_implementations_all pai,
184 per_org_structure_elements ose
185 WHERE p_org_structure_element_id = ose.org_structure_element_id /*Start-Added for bug:8285339*/
186 AND ((pai.org_structure_version_id = ose.org_structure_version_id
187 AND pai.start_organization_id = ose.organization_id_child)
188 OR (pai.proj_org_structure_version_id = ose.org_structure_version_id
189 AND pai.proj_start_org_id = ose.organization_id_child)
190 OR ( pai.exp_org_structure_version_id = ose.org_structure_version_id
191 AND pai.exp_start_org_id = ose.organization_id_child)) /*End-Added for bug:8285339*/
192 UNION /* Added for bug 5405854 - Burdening start org */
193 SELECT 'X'
194 FROM pa_ind_rate_sch_revisions ind,
195 per_org_structure_elements ose
196 WHERE ose.org_structure_element_id = p_org_structure_element_id
197 AND ind.org_structure_version_id = ose.org_structure_version_id
198 AND ind.start_organization_id = ose.organization_id_child
199
200 ) ;
201 BEGIN
202 -- hr_utility.trace_on(null, 'RMBUG');
203 -- hr_utility.trace('START - pa_ose_predel_validation');
204 --
205 -- hr_utility.set_location('PA_ORG.PA_OSE_PREDEL_VALIDATION',1);
206 --
207 IF (pa_imp.pa_implemented_all) THEN
208 --
209 -- Check if this Element is the starting org specified in
210 -- PA_Implementations for Reports, '
211
212 open check_start_org_exists;
213 fetch check_start_org_exists into dummy1;
214 if check_start_org_exists%found then
215 hr_utility.set_message (275,'PA_ORG_CANT_DELETE_STARTORG'); /* Message_name changed for bug fix 1713199 */
216 hr_utility.raise_error;
217 end if;
218 close check_start_org_exists;
219
220 -- Check if structure element is used by PA for burdening (10.7+):
221
222 dummy1 := NULL;
223
224 BEGIN
225 -- Check if this Element is in the Project Burdening Hierarchy'
226 /* Bug 5405854: The check has to be made from pa_ind_rate_sch_revisions
227 as this has the details about the burden schedule revisions.
228 We should not be looking at the hierarchy attached at the
229 Business group.
230 SELECT 'X'
231 INTO dummy1
232 FROM sys.dual
233 WHERE p_org_structure_element_id IN (
234 SELECT org_structure_element_id
235 FROM hr_organization_information info,
236 per_org_structure_elements ose
237 WHERE info.organization_id = pa_utils.business_group_id
238 AND ose.business_group_id = pa_utils.business_group_id
239 AND info.org_information_context = 'Project Burdening Hierarchy'
240 AND to_number(info.org_information2) = ose.org_structure_version_id
241 );
242 */
243 /* Replacing the above SELECT with the below SELECT statement. */
244 /* Check if the organization being deleted is used in Burdening */
245 -- hr_utility.trace('before check');
246 -- hr_utility.trace('before check p_org_structure_element_id IS ' || p_org_structure_element_id);
247 SELECT 'X'
248 INTO dummy1
249 FROM sys.dual
250 WHERE exists (
251 SELECT ics.organization_id
252 FROM pa_ind_compiled_sets ics,/*10327103 changed the table used from "pa_ind_cost_multipliers" to pa_ind_compiled_sets*/
253 pa_ind_rate_sch_revisions irr, --Bug 6074710
254 per_org_structure_elements ose
255 WHERE ose.org_structure_element_id = p_org_structure_element_id
256 /* Added below 2 conditions for bug 6074710*/
257 AND ose.org_structure_version_id = irr.org_structure_version_id
258 AND ics.ind_rate_sch_revision_id = irr.ind_rate_sch_revision_id
259 AND ose.organization_id_child = ics.organization_id); /*10327103*/
260 /*Bug 10327103. Uncommented the above condition and changed the table used .*/
261
262
263 EXCEPTION
264 WHEN NO_DATA_FOUND THEN
265 -- hr_utility.trace('after check exception');
266 NULL;
267 END;
268
269 -- hr_utility.trace('after check');
270 IF ( dummy1 IS NOT NULL ) THEN
271 hr_utility.set_message (275,'PA_ORG_DEL_LINK');
272 hr_utility.raise_error;
273 END IF;
274
275 ELSE
276 -- pass validation.
277 NULL;
278 END IF;
279 --
280 END;
281
282 PROCEDURE pa_org_predel_validation (p_org_id number) IS
283 -- This procedure will check if the org being deleted
284 -- has been specified for PA use.
285 -- This procedure will be called from the Define Org form.
286
287 dummy1 VARCHAR2(4); -- into arg for main SELECT
288 --
289 cursor pa_org_exists is
290 select 'X'
291 from pa_all_organizations
292 where organization_id = p_org_id;
293
294 cursor nlr_org_exists is
295 select 'X'
296 from pa_non_labor_resource_orgs
297 where organization_id = p_org_id;
298
299 cursor bill_rate_org_exists is
300 select 'X'
301 from pa_std_bill_rate_schedules
302 where organization_id = p_org_id;
303
304 Begin
305 open pa_org_exists;
306 fetch pa_org_exists into dummy1;
307 if pa_org_exists%found then
308 hr_utility.set_message (275,'PA_ORG_CANT_DEL_PAORG');
309 hr_utility.raise_error;
310 end if;
311 close pa_org_exists;
312 --
313 open nlr_org_exists;
314 fetch nlr_org_exists into dummy1;
315 if nlr_org_exists%found then
316 hr_utility.set_message (275,'PA_ORG_CANT_DEL_NLRORG');
317 hr_utility.raise_error;
318 end if;
319 close nlr_org_exists;
320 --
321 open bill_rate_org_exists;
322 fetch bill_rate_org_exists into dummy1;
323 if bill_rate_org_exists%found then
324 hr_utility.set_message (275,'PA_ORG_CANT_DEL_BRORG');
325 hr_utility.raise_error;
326 end if;
327 close bill_rate_org_exists;
328
329 End;
330
331 END pa_org;
[Home] [Help]