1 PACKAGE BODY po_person AS
2
3 /* $Header: popredeb.pls 115.0 99/07/17 02:27:14 porting ship $ */
4
5 --
6 /*
7 NAME
8 po_predel_validation
9 DESCRIPTION
10 Foreign key reference check.
11 */
12 --
13 PROCEDURE po_predel_validation (p_person_id number)
14 IS
15 --
16 v_delete_permitted varchar2(1);
17 --
18 BEGIN
19 --
20 hr_utility.set_location('PO_PERSON.PO_PREDEL_VALIDATION', 1);
21 --
22 begin
23 select 'Y'
24 into v_delete_permitted
25 from sys.dual
26 where not exists (
27 select null
28 from po_vendors pov
29 where pov.employee_id = P_PERSON_ID);
30 exception
31 when NO_DATA_FOUND then
32 hr_utility.set_message (801, 'HR_6246_ALL_PO_PER_NO_DEL');
33 hr_utility.raise_error;
34 end;
35 --
36 hr_utility.set_location('PO_PERSON.PO_PREDEL_VALIDATION', 2);
37 --
38 begin
39 select 'Y'
40 into v_delete_permitted
41 from sys.dual
42 where not exists (
43 select null
44 from hr_locations hr
45 where hr.designated_receiver_id = P_PERSON_ID);
46 exception
47 when NO_DATA_FOUND then
48 hr_utility.set_message (801, 'HR_6250_ALL_PO2_PER_NO_DEL');
49 hr_utility.raise_error;
50 end;
51 --
52 hr_utility.set_location('PO_PERSON.PO_PREDEL_VALIDATION', 3);
53 --
54 begin
55 select 'Y'
56 into v_delete_permitted
57 from sys.dual
58 where not exists (
59 select null
60 from po_agents po
61 where po.agent_id = P_PERSON_ID);
62 exception
63 when NO_DATA_FOUND then
64 hr_utility.set_message (801, 'HR_6251_ALL_PO3_PER_NO_DEL');
65 hr_utility.raise_error;
66 end;
67 --
68 hr_utility.set_location('PO_PERSON.PO_PREDEL_VALIDATION', 4);
69 --
70 begin
71 select 'Y'
72 into v_delete_permitted
73 from sys.dual
74 where not exists (
75 select null
76 from po_acceptances po
77 where po.employee_id = P_PERSON_ID);
78 exception
79 when NO_DATA_FOUND then
80 hr_utility.set_message (801, 'HR_6252_ALL_PO4_PER_NO_DEL');
81 hr_utility.raise_error;
82 end;
83 --
84 hr_utility.set_location('PO_PERSON.PO_PREDEL_VALIDATION', 5);
85 --
86 begin
87 select 'Y'
88 into v_delete_permitted
89 from sys.dual
90 where not exists (
91 select null
92 from po_quotation_approvals_all po
93 where po.approver_id = P_PERSON_ID);
94 exception
95 when NO_DATA_FOUND then
96 hr_utility.set_message (801, 'HR_6253_ALL_PO5_PER_NO_DEL');
97 hr_utility.raise_error;
98 end;
99 --
100 hr_utility.set_location('PO_PERSON.PO_PREDEL_VALIDATION', 6);
101 --
102 begin
103 select 'Y'
104 into v_delete_permitted
105 from sys.dual
106 where not exists (
107 select null
108 from po_headers_all po
109 where po.agent_id = P_PERSON_ID)
110 and not exists (
111 select null
112 from po_headers_archive_all po
113 where po.agent_id = P_PERSON_ID);
114 exception
115 when NO_DATA_FOUND then
116 hr_utility.set_message (801, 'HR_6256_ALL_PO8_PER_NO_DEL');
117 hr_utility.raise_error;
118 end;
119 --
120 hr_utility.set_location('PO_PERSON.PO_PREDEL_VALIDATION', 7);
121 --
122 begin
123 select 'Y'
124 into v_delete_permitted
125 from sys.dual
126 where not exists (
127 select null
128 from po_distributions_all po
129 where po.deliver_to_person_id = P_PERSON_ID)
130 and not exists (
131 select null
132 from po_distributions_archive_all po
133 where po.deliver_to_person_id = P_PERSON_ID);
134 exception
135 when NO_DATA_FOUND then
136 hr_utility.set_message (801, 'HR_6257_ALL_PO9_PER_NO_DEL');
137 hr_utility.raise_error;
138 end;
139 --
140 hr_utility.set_location('PO_PERSON.PO_PREDEL_VALIDATION', 8);
141 --
142 begin
143 null;
144 /* po_notifications_all is obsolete in R11
145 select 'Y'
146 into v_delete_permitted
147 from sys.dual
148 where not exists (
149 select null
150 from po_notifications_all po
151 where po.employee_id = P_PERSON_ID);
152 exception
153 when NO_DATA_FOUND then
154 hr_utility.set_message (801, 'HR_6261_ALL_PO11_PER_NO_DEL');
155 hr_utility.raise_error;
156 */
157 end;
158 --
159 hr_utility.set_location('PO_PERSON.PO_PREDEL_VALIDATION', 9);
160 --
161 begin
162 select 'Y'
163 into v_delete_permitted
164 from sys.dual
165 where not exists (
166 select null
167 from po_releases_all po
168 where po.agent_id = P_PERSON_ID
169 or po.cancelled_by = P_PERSON_ID
170 or po.hold_by = P_PERSON_ID)
171 and not exists (
172 select null
173 from po_releases_archive_all po
174 where po.agent_id = P_PERSON_ID
175 or po.cancelled_by = P_PERSON_ID
176 or po.hold_by = P_PERSON_ID);
177 exception
178 when NO_DATA_FOUND then
179 hr_utility.set_message (801, 'HR_6265_ALL_PO15_PER_NO_DEL');
180 hr_utility.raise_error;
181 end;
182 --
183 hr_utility.set_location('PO_PERSON.PO_PREDEL_VALIDATION', 10);
184 --
185 begin
186 select 'Y'
187 into v_delete_permitted
188 from sys.dual
189 where not exists (
190 select null
191 from po_requisitions_interface_all po
192 where po.approver_id = P_PERSON_ID
193 or po.deliver_to_requestor_id = P_PERSON_ID
194 or po.suggested_buyer_id = P_PERSON_ID
195 or po.preparer_id = P_PERSON_ID);
196 exception
197 when NO_DATA_FOUND then
198 hr_utility.set_message (801, 'HR_6266_ALL_PO16_PER_NO_DEL');
199 hr_utility.raise_error;
200 end;
201 --
202 hr_utility.set_location('PO_PERSON.PO_PREDEL_VALIDATION', 11);
203 --
204 begin
205 select 'Y'
206 into v_delete_permitted
207 from sys.dual
208 where not exists (
209 select null
210 from po_requisition_headers_all po
211 where po.preparer_id = P_PERSON_ID);
212 exception
213 when NO_DATA_FOUND then
214 hr_utility.set_message (801, 'HR_6267_ALL_PO17_PER_NO_DEL');
215 hr_utility.raise_error;
216 end;
217 --
218 hr_utility.set_location('PO_PERSON.PO_PREDEL_VALIDATION', 12);
219 --
220 begin
221 select 'Y'
222 into v_delete_permitted
223 from sys.dual
224 where not exists (
225 select null
226 from po_requisition_lines_all po
227 where po.to_person_id = P_PERSON_ID
228 or po.purchasing_agent_id = P_PERSON_ID
229 or po.research_agent_id = P_PERSON_ID);
230 exception
231 when NO_DATA_FOUND then
232 hr_utility.set_message (801, 'HR_6268_ALL_PO18_PER_NO_DEL');
233 hr_utility.raise_error;
234 end;
235 --
236 hr_utility.set_location('PO_PERSON.PO_PREDEL_VALIDATION', 13);
237 --
238 begin
239 select 'Y'
240 into v_delete_permitted
241 from sys.dual
242 where not exists (
243 select null
244 from po_action_history po
245 where po.employee_id = P_PERSON_ID);
246 exception
247 when NO_DATA_FOUND then
248 hr_utility.set_message (801, 'HR_6255_ALL_PO19_PRE_NO_DEL');
249 hr_utility.raise_error;
250 end;
251 --
252 hr_utility.set_location('PO_PERSON.PO_PREDEL_VALIDATION',14);
253 --
254 begin
255 select 'Y'
256 into v_delete_permitted
257 from sys.dual
258 where not exists (
259 select null
260 from po_employee_hierarchies po
261 where po.employee_id = P_PERSON_ID
262 or po.superior_id = P_PERSON_ID);
263 exception
264 when NO_DATA_FOUND then
265 hr_utility.set_message (801, 'HR_6260_ALL_PO20_PER_NO_DEL');
266 hr_utility.raise_error;
267 end;
268 --
269 hr_utility.set_location('PO_PERSON.PO_PREDEL_VALIDATION', 15);
270 --
271 begin
272 select 'Y'
273 into v_delete_permitted
274 from sys.dual
275 where not exists (
276 select null
277 from po_lines_all po
278 where po.cancelled_by = P_PERSON_ID
279 or po.closed_by = P_PERSON_ID)
280 and not exists (
281 select null
282 from po_lines_archive_all po
283 where po.cancelled_by = P_PERSON_ID
284 or po.closed_by = P_PERSON_ID);
285 exception
286 when NO_DATA_FOUND then
287 hr_utility.set_message (801, 'HR_6264_ALL_PO21_PER_NO_DEL');
288 hr_utility.raise_error;
289 end;
290 --
291 hr_utility.set_location('PO_PERSON.PO_PREDEL_VALIDATION', 16);
292 --
293 begin
294 select 'Y'
295 into v_delete_permitted
296 from sys.dual
297 where not exists (
298 select null
299 from po_line_locations_all po
300 where po.cancelled_by = P_PERSON_ID
301 or po.closed_by = P_PERSON_ID)
302 and not exists (
303 select null
304 from po_line_locations_archive_all po
305 where po.cancelled_by = P_PERSON_ID
306 or po.closed_by = P_PERSON_ID);
307 exception
308 when NO_DATA_FOUND then
309 hr_utility.set_message (801, 'HR_6573_ALL_PO22_PER_NO_DEL');
310 hr_utility.raise_error;
311 end;
312 --
313 hr_utility.set_location('PO_PERSON.PO_PREDEL_VALIDATION', 17);
314 --
315 begin
316 select 'Y'
317 into v_delete_permitted
318 from sys.dual
319 where not exists (
320 select null
321 from po_reqexpress_lines_all po
322 where po.suggested_buyer_id = P_PERSON_ID);
323 exception
324 when NO_DATA_FOUND then
325 hr_utility.set_message (801, 'HR_6574_ALL_PO23_PER_NO_DEL');
326 hr_utility.raise_error;
327 end;
328 --
329 END po_predel_validation;
330 --
331 END po_person;