1 PACKAGE BODY pa_person AS
2 /* $Header: PAPERB.pls 120.1 2005/08/19 16:39:22 mwasowic noship $ */
3 --
4 --
5 PROCEDURE check_business_group (p_person_id IN number,
6 Other_Business_group OUT NOCOPY varchar2); --File.Sql.39 bug 4440895
7 PROCEDURE phase2 (p_person_id number);
8
9 PROCEDURE pa_predel_validation (p_person_id number)
10 IS
11 Allow_deletion exception;
12 Not_Allow_deletion exception;
13 Error_Message varchar2(30);
14 Reference_Exist varchar2(30);
15 Other_Business_group varchar2(1);
16 BEGIN
17
18 check_business_group(p_person_id,
19 Other_Business_group);
20 if ( Other_Business_group = 'Y' ) then
21 raise Allow_deletion;
22 end if;
23
24 Pa_Hr_Project_setup.Check_person_reference(p_person_id,
25 Error_Message,
26 Reference_Exist);
27 if ( Reference_Exist = 'Y' ) then
28 raise Not_Allow_deletion;
29 end if;
30
31 /*Bug#2738741 - Commenting this code as we will be adding this as the last check
32 as this involved deletion of records also.
33
34 Pa_Hr_Resource.Check_person_reference(p_person_id,
35 Error_Message,
36 Reference_Exist);
37 if ( Reference_Exist = 'Y' ) then
38 raise Not_Allow_deletion;
39 end if;
40 ** */
41
42 Pa_Hr_Summarizations.Check_person_reference(p_person_id,
43 Error_Message,
44 Reference_Exist);
45 if ( Reference_Exist = 'Y' ) then
46 raise Not_Allow_deletion;
47 end if;
48
49 Pa_Hr_Budgets.Check_person_reference(p_person_id,
50 Error_Message,
51 Reference_Exist);
52 if ( Reference_Exist = 'Y' ) then
53 raise Not_Allow_deletion;
54 end if;
55
56 Pa_Hr_Cost_Rates.Check_person_reference(p_person_id,
57 Error_Message,
58 Reference_Exist);
59 if ( Reference_Exist = 'Y' ) then
60 raise Not_Allow_deletion;
61 end if;
62
63 Pa_Hr_Transactions.Check_person_reference(p_person_id,
64 Error_Message,
65 Reference_Exist);
66 if ( Reference_Exist = 'Y' ) then
67 raise Not_Allow_deletion;
68 end if;
69
70 Pa_Hr_Bill_Rates.Check_person_reference(p_person_id,
71 Error_Message,
72 Reference_Exist);
73 if ( Reference_Exist = 'Y' ) then
74 raise Not_Allow_deletion;
75 end if;
76
77 Pa_Hr_Agreements.Check_person_reference(p_person_id,
78 Error_Message,
79 Reference_Exist);
80 if ( Reference_Exist = 'Y' ) then
81 raise Not_Allow_deletion;
82 end if;
83
84 Pa_Hr_Invoice.Check_person_reference(p_person_id,
85 Error_Message,
86 Reference_Exist);
87 if ( Reference_Exist = 'Y' ) then
88 raise Not_Allow_deletion;
89 end if;
90
91 Pa_Hr_Capital.Check_person_reference(p_person_id,
92 Error_Message,
93 Reference_Exist);
94 if ( Reference_Exist = 'Y' ) then
95 raise Not_Allow_deletion;
96 end if;
97
98 /* Bug#2738741-Added the call at end instead of start as this call will be deleting records in
99 PA Tables */
100
101 Pa_Hr_Resource.Check_person_reference(p_person_id,
102 Error_Message,
103 Reference_Exist);
104 if ( Reference_Exist = 'Y' ) then
105 raise Not_Allow_deletion;
106 end if;
107
108 exception
109 when Allow_deletion then
110 Return;
111 when Not_Allow_deletion then
112 hr_utility.set_message (275, Error_Message);
113 hr_utility.raise_error;
114 when OTHERS then
115 raise;
116 END pa_predel_validation;
117
118 PROCEDURE check_business_group (p_person_id IN number,
119 Other_Business_group OUT NOCOPY varchar2) --File.Sql.39 bug 4440895
120 IS
121 per_delete_allowed varchar2(1);
122 BEGIN
123 select 'X'
124 into per_delete_allowed
125 from sys.dual
126 where not exists (
127 select null
128 from pa_implementations_all imp,
129 per_all_people_f per
130 -- per_person_types ptypes -- commenting out for CWK 11.5.10
131 where per.person_id = p_person_id
132 and per.business_group_id = imp.business_group_id
133 and (per.current_employee_flag = 'Y' OR -- for 11.5.10 CWK
134 per.current_npw_flag = 'Y')); -- for FP M CWK
135 -- commenting out for CWK 11.5.10
136 -- and per.person_type_id = ptypes.person_type_id
137 -- and ptypes.system_person_type like '%EMP%' );
138 Other_Business_group := 'Y';
139 exception
140 when NO_DATA_FOUND then
141 Other_Business_group := 'N';
142 when OTHERS then
143 Raise;
144 /* ***
145 when NO_DATA_FOUND then
146 hr_utility.set_message (275, 'PA_PER_PHS1_NO_DEL');
147 hr_utility.raise_error;
148 ** */
149 END check_business_group;
150
151 PROCEDURE phase2 (p_person_id number)
152 IS
153 --
154 v_delete_permitted varchar2(1);
155 dummy varchar2(240);
156 cursor pa_per_exists is
157 SELECT 'X'
158 FROM fnd_product_installations fpi
159 , pa_implementations_all imp
160 WHERE fpi.application_id = 275
161 AND fpi.status = 'I';
162 --
163 BEGIN
164 --
165 hr_utility.set_location('PA_PERSON.PA_PREDEL_VALIDATION', 1);
166 --
167 begin
168 select 'Y'
169 into v_delete_permitted
170 from sys.dual
171 where not exists (
172 select null
173 from pa_compensation_details pa
174 where pa.person_id = P_PERSON_ID);
175 exception
176 when NO_DATA_FOUND then
177 hr_utility.set_message (801, 'HR_6285_ALL_PA_PER_NO_DEL');
178 hr_utility.raise_error;
179 end;
180 --
181 hr_utility.set_location('PA_PERSON.PA_PREDEL_VALIDATION', 2);
182 --
183 begin
184 select 'Y'
185 into v_delete_permitted
186 from sys.dual
187 where not exists (
188 select null
189 from pa_emp_bill_rate_overrides pa
190 where pa.project_id > -1
191 and pa.task_id > -1
192 and pa.person_id = P_PERSON_ID);
193 exception
194 when NO_DATA_FOUND then
195 hr_utility.set_message (801, 'HR_6286_ALL_PA2_PER_NO_DEL');
196 hr_utility.raise_error;
197 end;
198 --
199 hr_utility.set_location('PA_PERSON.PA_PREDEL_VALIDATION', 3);
200 --
201 begin
202 select 'Y'
203 into v_delete_permitted
204 from sys.dual
205 where not exists (
206 select null
207 from pa_job_assignment_overrides pa
208 where ((pa.project_id > -1 AND
209 pa.person_id = P_PERSON_ID)
210 OR
211 (pa.task_id > -1 AND
212 pa.person_id = P_PERSON_ID)));
213 exception
214 when NO_DATA_FOUND then
215 hr_utility.set_message (801, 'HR_6287_ALL_PA3_PER_NO_DEL');
216 hr_utility.raise_error;
217 end;
218 --
219 hr_utility.set_location('PA_PERSON.PA_PREDEL_VALIDATION', 4);
220 --
221 begin
222 select 'Y'
223 into v_delete_permitted
224 from sys.dual
225 where not exists (
226 select /*+ INDEX ( pa PA_BILL_RATES_U1 ) */
227 null
228 from pa_bill_rates pa
229 where pa.bill_rate_organization_id > -1
230 and pa.std_bill_rate_schedule <> 'DUMMY'
231 and pa.person_id = P_PERSON_ID);
232 exception
233 when NO_DATA_FOUND then
234 hr_utility.set_message (801, 'HR_6288_ALL_PA4_PER_NO_DEL');
235 hr_utility.raise_error;
236 end;
237 --
238 hr_utility.set_location('PA_PERSON.PA_PREDEL_VALIDATION', 5);
239 --
240 begin
241 select 'Y'
242 into v_delete_permitted
243 from sys.dual
244 where not exists (
245 select null
246 from pa_project_players pa
247 where pa.person_id = P_PERSON_ID);
248 exception
249 when NO_DATA_FOUND then
250 hr_utility.set_message (801, 'HR_6289_ALL_PA5_PER_NO_DEL');
251 hr_utility.raise_error;
252 end;
253 --
254 hr_utility.set_location('PA_PERSON.PA_PREDEL_VALIDATION', 6);
255 --
256 begin
257 select 'Y'
258 into v_delete_permitted
259 from sys.dual
260 where not exists (
261 select null
262 from pa_agreements pa
263 where pa.owned_by_person_id = P_PERSON_ID);
264 exception
265 when NO_DATA_FOUND then
266 hr_utility.set_message (801, 'HR_6290_ALL_PA6_PER_NO_DEL');
267 hr_utility.raise_error;
268 end;
269 --
270 hr_utility.set_location('PA_PERSON.PA_PREDEL_VALIDATION', 7);
271 --
272 begin
273 select 'Y'
274 into v_delete_permitted
275 from sys.dual
276 where not exists (
277 select null
278 from pa_tasks pa
279 where pa.task_manager_person_id = P_PERSON_ID);
280 exception
281 when NO_DATA_FOUND then
282 hr_utility.set_message (801, 'HR_6295_ALL_PA7_PER_NO_DEL');
283 hr_utility.raise_error;
284 end;
285 --
286 hr_utility.set_location('PA_PERSON.PA_PREDEL_VALIDATION', 8);
287 --
288 begin
289 select /*+ INDEX (pa PA_CREDIT_RECEIVERS_U1) */
290 'Y'
291 into v_delete_permitted
292 from sys.dual
293 where not exists (
294 select null
295 from pa_credit_receivers pa
296 where pa.person_id = P_PERSON_ID);
297 exception
298 when NO_DATA_FOUND then
299 hr_utility.set_message (801, 'HR_6297_ALL_PA8_PER_NO_DEL');
300 hr_utility.raise_error;
301 end;
302 --
303 hr_utility.set_location('PA_PERSON.PA_PREDEL_VALIDATION', 9);
304 --
305 begin
306 select 'Y'
307 into v_delete_permitted
308 from sys.dual
309 where not exists (
310 select null
311 from pa_expenditures pa
312 where pa.incurred_by_person_id = P_PERSON_ID);
313
314 select 'Y'
315 into v_delete_permitted
316 from sys.dual
317 where not exists (
318 select null
319 from pa_expenditures pa
320 where pa.entered_by_person_id = P_PERSON_ID);
321 exception
322 when NO_DATA_FOUND then
323 hr_utility.set_message (801, 'HR_6300_ALL_PA9_PER_NO_DEL');
324 hr_utility.raise_error;
325 end;
326 --
327 hr_utility.set_location('PA_PERSON.PA_PREDEL_VALIDATION', 10);
328 --
329 begin
330 select 'Y'
331 into v_delete_permitted
332 from sys.dual
333 where not exists (
334 select null
335 from pa_draft_invoices pa
336 where pa.approved_by_person_id = P_PERSON_ID
337 or pa.released_by_person_id = P_PERSON_ID);
338 exception
339 when NO_DATA_FOUND then
340 hr_utility.set_message (801, 'HR_6301_ALL_PA10_PER_NO_DEL');
341 hr_utility.raise_error;
342 end;
343 --
344
345 /* -- sowong - these lines of code are removed since PA EMPLOYEE ACCUM
346 -- references are not needed (see bug 967781).
347
348 hr_utility.set_location('PA_PERSON.PA_PREDEL_VALIDATION', 11);
349 --
350 begin
351 select 'Y'
352 into v_delete_permitted
353 from sys.dual
354 where not exists (
355 select null
356 from pa_employee_accum pa
357 where pa.person_id = P_PERSON_ID);
358 exception
359 when NO_DATA_FOUND then
360 hr_utility.set_message (801, 'HR_6302_ALL_PA11_PER_NO_DEL');
361 hr_utility.raise_error;
362 end;
363 --
364 */
365
366 hr_utility.set_location('PA_PERSON.PA_PREDEL_VALIDATION', 12);
367 --
368 begin
369 select 'Y'
370 into v_delete_permitted
371 from sys.dual
372 where not exists (
373 select null
374 from pa_routings pa
375 where (pa.routed_to_person_id = P_PERSON_ID
376 OR
377 pa.routed_from_person_id = P_PERSON_ID));
378 exception
379 when NO_DATA_FOUND then
380 hr_utility.set_message (801, 'HR_7057_ALL_PA12_PER_NO_DEL');
381 hr_utility.raise_error;
382 end;
383 --
384 hr_utility.set_location('PA_PERSON.PA_PREDEL_VALIDATION', 16);
385 --
386 begin
387 select 'Y'
388 into v_delete_permitted
389 from sys.dual
390 where not exists (
391 select null
392 from pa_transaction_controls pa
393 where pa.project_id > -1
394 and pa.person_id = P_PERSON_ID);
395 exception
396 when NO_DATA_FOUND then
397 hr_utility.set_message (801, 'HR_7061_ALL_PA16_PER_NO_DEL');
398 hr_utility.raise_error;
399 end;
400
401 open pa_per_exists;
402 fetch pa_per_exists into dummy;
403 if pa_per_exists%found then
404 hr_utility.set_message (275,'PA_PER_CANT_DELETE');
405 hr_utility.raise_error;
406 end if;
407 close pa_per_exists;
408 --
409 --
410 END phase2;
411 --
412 END pa_person;
[Home] [Help]