1 Package Body ota_hr_delete as
2 /* $Header: otdhr01t.pkb 115.0 99/07/16 00:51:19 porting ship $ */
3 --
4 g_package varchar2(33) := ' ota_hr_delete.'; -- Global package name
5 --
6 Procedure check_delete(p_person_id number default null
7 ,p_organization_id number default null
8 ,p_job_id number default null
9 ,p_position_id number default null
10 ,p_address_id number default null
11 ,p_analysis_criteria_id number default null
12 ) is
13 --
14 l_proc varchar2(72) := g_package||'check_delete';
15 l_exists varchar2(1);
16 --
17 cursor c_activity_versions1 is
18 select null
19 from ota_activity_versions
20 where controlling_person_id = p_person_id;
21 --
22 cursor c_activity_versions2 is
23 select null
24 from ota_activity_versions
25 where developer_organization_id = p_organization_id;
26 --
27 cursor c_delegate_bookings1 is
28 select null
29 from ota_delegate_bookings
30 where delegate_person_id = p_person_id;
31 --
32 cursor c_delegate_bookings2 is
33 select null
34 from ota_delegate_bookings
35 where sponsor_person_id = p_person_id;
36 --
37 cursor c_delegate_bookings3 is
38 select null
39 from ota_delegate_bookings
40 where organization_id = p_organization_id;
41 --
42 cursor c_delegate_bookings4 is
43 select null
44 from ota_delegate_bookings
45 where person_address_id = p_address_id;
46 --
47 cursor c_events is
48 select null
49 from ota_events
50 where organization_id = p_organization_id;
51 --
52 cursor c_event_associations1 is
53 select null
54 from ota_event_associations
55 where organization_id = p_organization_id;
56 --
57 cursor c_event_associations2 is
58 select null
59 from ota_event_associations
60 where job_id = p_job_id;
61 --
62 cursor c_event_associations3 is
63 select null
64 from ota_event_associations
65 where position_id = p_position_id;
66 --
67 cursor c_finance_headers1 is
68 select null
69 from ota_finance_headers
70 where organization_id = p_organization_id;
71 --
72 cursor c_skill_provisions is
73 select null
74 from ota_skill_provisions
75 where analysis_criteria_id = p_analysis_criteria_id;
76 --
77 begin
78 hr_utility.set_location('Entering:'||l_proc, 5);
79 --
80 if p_person_id is not null then
81 --
82 hr_utility.set_location('Entering:'||l_proc, 10);
83 open c_activity_versions1;
84 fetch c_activity_versions1 into l_exists;
85 if c_activity_versions1%found then
86 close c_activity_versions1;
87 fnd_message.set_name('OTA','OTA_13546_PERSON_DELETE');
88 fnd_message.set_token('TABLE','OTA_ACTIVITY_VERSIONS');
89 fnd_message.set_token('COLUMN','CONTROLLING_PERSON_ID');
90 fnd_message.raise_error;
91 end if;
92 close c_activity_versions1;
93 --
94 hr_utility.set_location('Entering:'||l_proc, 15);
95 open c_delegate_bookings1;
96 fetch c_delegate_bookings1 into l_exists;
97 if c_delegate_bookings1%found then
98 close c_delegate_bookings1;
99 fnd_message.set_name('OTA','OTA_13546_PERSON_DELETE');
100 fnd_message.set_token('TABLE','OTA_DELEGATE_BOOKINGS');
101 fnd_message.set_token('COLUMN','DELEGATE_PERSON_ID');
102 fnd_message.raise_error;
103 end if;
104 close c_delegate_bookings1;
105 --
106 hr_utility.set_location('Entering:'||l_proc, 20);
107 open c_delegate_bookings2;
108 fetch c_delegate_bookings2 into l_exists;
109 if c_delegate_bookings2%found then
110 close c_delegate_bookings2;
111 fnd_message.set_name('OTA','OTA_13546_PERSON_DELETE');
112 fnd_message.set_token('TABLE','OTA_DELEGATE_BOOKINGS');
113 fnd_message.set_token('COLUMN','SPONSOR_PERSON_ID');
114 fnd_message.raise_error;
115 end if;
116 close c_delegate_bookings2;
117 --
118 elsif p_organization_id is not null then
119 --
120 hr_utility.set_location('Entering:'||l_proc, 25);
121 open c_events;
122 fetch c_events into l_exists;
123 if c_events%found then
124 close c_events;
125 fnd_message.set_name('OTA','OTA_13547_ORGANIZATION_DELETE');
126 fnd_message.set_token('TABLE','OTA_EVENTS');
127 fnd_message.set_token('COLUMN','ORGANIZATION_ID');
128 fnd_message.raise_error;
129 end if;
130 close c_events;
131 --
132 hr_utility.set_location('Entering:'||l_proc, 30);
133 open c_activity_versions2;
134 fetch c_activity_versions2 into l_exists;
135 if c_activity_versions2%found then
136 close c_activity_versions2;
137 fnd_message.set_name('OTA','OTA_13547_ORGANIZATION_DELETE');
138 fnd_message.set_token('TABLE','OTA_ACTIVITY_VERSIONS');
139 fnd_message.set_token('COLUMN','DEVELOPER_ORGANIZATION_ID');
140 fnd_message.raise_error;
141 end if;
142 close c_activity_versions2;
143 --
144 hr_utility.set_location('Entering:'||l_proc, 35);
145 open c_finance_headers1;
146 fetch c_finance_headers1 into l_exists;
147 if c_finance_headers1%found then
148 close c_finance_headers1;
149 fnd_message.set_name('OTA','OTA_13547_ORGANIZATION_DELETE');
150 fnd_message.set_token('TABLE','OTA_FINANCE_HEADERS');
151 fnd_message.set_token('COLUMN','ORGANIZATION_ID');
152 fnd_message.raise_error;
153 end if;
154 close c_finance_headers1;
155 --
156 hr_utility.set_location('Entering:'||l_proc, 40);
157 open c_delegate_bookings3;
158 fetch c_delegate_bookings3 into l_exists;
159 if c_delegate_bookings3%found then
160 close c_delegate_bookings3;
161 fnd_message.set_name('OTA','OTA_13547_ORGANIZATION_DELETE');
162 fnd_message.set_token('TABLE','OTA_DELEGATE_BOOKINGS');
163 fnd_message.set_token('COLUMN','ORGANIZATION_ID');
164 fnd_message.raise_error;
165 end if;
166 close c_delegate_bookings3;
167 --
168 hr_utility.set_location('Entering:'||l_proc, 45);
169 open c_event_associations1;
170 fetch c_event_associations1 into l_exists;
171 if c_event_associations1%found then
172 close c_event_associations1;
173 fnd_message.set_name('OTA','OTA_13547_ORGANIZATION_DELETE');
174 fnd_message.set_token('TABLE','OTA_EVENT_ASSOCIATIONS');
175 fnd_message.set_token('COLUMN','ORGANIZATION_ID');
176 fnd_message.raise_error;
177 end if;
178 close c_event_associations1;
179 --
180 elsif p_job_id is not null then
181 --
182 hr_utility.set_location('Entering:'||l_proc, 50);
183 open c_event_associations2;
184 fetch c_event_associations2 into l_exists;
185 if c_event_associations2%found then
186 close c_event_associations2;
187 fnd_message.set_name('OTA','OTA_13548_JOB_DELETE');
188 fnd_message.set_token('TABLE','OTA_EVENT_ASSOCIATIONS');
189 fnd_message.set_token('COLUMN','JOB_ID');
190 fnd_message.raise_error;
191 end if;
192 close c_event_associations2;
193 --
194 elsif p_position_id is not null then
195 --
196 hr_utility.set_location('Entering:'||l_proc, 55);
197 open c_event_associations3;
198 fetch c_event_associations3 into l_exists;
199 if c_event_associations3%found then
200 close c_event_associations3;
201 fnd_message.set_name('OTA','OTA_13549_POSITION_DELETE');
202 fnd_message.set_token('TABLE','OTA_EVENT_ASSOCIATIONS');
203 fnd_message.set_token('COLUMN','POSITION_ID');
204 fnd_message.raise_error;
205 end if;
206 close c_event_associations3;
207 --
208 elsif p_address_id is not null then
209 --
210 hr_utility.set_location('Entering:'||l_proc, 60);
211 open c_delegate_bookings4;
212 fetch c_delegate_bookings4 into l_exists;
213 if c_delegate_bookings4%found then
214 close c_delegate_bookings4;
215 fnd_message.set_name('OTA','OTA_13550_ADDRESS_DELETE');
216 fnd_message.set_token('TABLE','OTA_DELEGATE_BOOKINGS');
217 fnd_message.set_token('COLUMN','PERSON_ADDRESS_ID');
218 fnd_message.raise_error;
219 end if;
220 close c_delegate_bookings4;
221 --
222 elsif p_analysis_criteria_id is not null then
223 --
224 hr_utility.set_location('Entering:'||l_proc, 65);
225 open c_skill_provisions;
226 fetch c_skill_provisions into l_exists;
227 if c_skill_provisions%found then
228 close c_skill_provisions;
229 fnd_message.set_name('OTA','OTA_13551_ANAL_CRIT_DELETE');
230 fnd_message.set_token('TABLE','OTA_SKILL_PROVISIONS');
231 fnd_message.set_token('COLUMN','ANALYSIS_CRITERIA_ID');
232 fnd_message.raise_error;
233 end if;
234 close c_skill_provisions;
235 --
236 end if;
237 --
238 hr_utility.set_location(' Leaving:'||l_proc, 70);
239 end;
240 --
241 end ota_hr_delete;
[Home] [Help]