DBA Data[Home] [Help]

PACKAGE BODY: APPS.PO_PERSON

Source


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;