1 Package Body ota_ar_delete as
2 /* $Header: otdar01t.pkb 115.0 99/07/16 00:51:11 porting ship $ */
3 --
4 g_package varchar2(33) := ' ota_ar_delete.'; -- Global package name
5 --
6 Procedure check_delete(p_customer_id number default null
7 ,p_contact_id number default null
8 ,p_address_id number default null) is
9 --
10 l_proc varchar2(72) := g_package||'check_delete';
11 l_exists varchar2(1);
12 --
13 cursor c_booking_deals is
14 select null
15 from ota_booking_deals
16 where customer_id = p_customer_id;
17 --
18 cursor c_delegate_bookings1 is
19 select null
20 from ota_delegate_bookings
21 where customer_id = p_customer_id;
22 --
23 cursor c_delegate_bookings2 is
24 select null
25 from ota_delegate_bookings
26 where third_party_customer_id = p_customer_id;
27 --
28 cursor c_delegate_bookings3 is
29 select null
30 from ota_delegate_bookings
31 where contact_id = p_contact_id;
32 --
33 cursor c_delegate_bookings4 is
34 select null
35 from ota_delegate_bookings
36 where third_party_contact_id = p_contact_id;
37 --
38 cursor c_delegate_bookings5 is
39 select null
40 from ota_delegate_bookings
41 where delegate_contact_id = p_contact_id;
42 --
43 cursor c_delegate_bookings6 is
44 select null
45 from ota_delegate_bookings
46 where contact_address_id = p_address_id;
47 --
48 cursor c_delegate_bookings7 is
49 select null
50 from ota_delegate_bookings
51 where third_party_address_id = p_address_id;
52 --
53 cursor c_event_associations is
54 select null
55 from ota_event_associations
56 where customer_id = p_customer_id;
57 --
58 cursor c_finance_headers1 is
59 select null
60 from ota_finance_headers
61 where customer_id = p_customer_id;
62 --
63 cursor c_finance_headers2 is
64 select null
65 from ota_finance_headers
66 where contact_id = p_contact_id
67 and customer_id is not null;
68 --
69 cursor c_finance_headers3 is
70 select null
71 from ota_finance_headers
72 where address_id = p_address_id
73 and customer_id is not null;
74 --
75 begin
76 hr_utility.set_location('Entering:'||l_proc, 5);
77 --
78 if p_customer_id is not null then
79 --
80 hr_utility.set_location('Entering:'||l_proc, 15);
81 open c_booking_deals;
82 fetch c_booking_deals into l_exists;
83 if c_booking_deals%found then
84 close c_booking_deals;
85 fnd_message.set_name('OTA','OTA_13540_CUSTOMER_DELETE');
86 fnd_message.set_token('TABLE','OTA_BOOKING_DEALS');
87 fnd_message.set_token('COLUMN','CUSTOMER_ID');
88 fnd_message.raise_error;
89 end if;
90 close c_booking_deals;
91 --
92 hr_utility.set_location('Entering:'||l_proc, 20);
93 open c_delegate_bookings1;
94 fetch c_delegate_bookings1 into l_exists;
95 if c_delegate_bookings1%found then
96 close c_delegate_bookings1;
97 fnd_message.set_name('OTA','OTA_13540_CUSTOMER_DELETE');
98 fnd_message.set_token('TABLE','OTA_BOOKING_DEALS');
99 fnd_message.set_token('COLUMN','CUSTOMER_ID');
100 fnd_message.raise_error;
101 end if;
102 close c_delegate_bookings1;
103 --
104 hr_utility.set_location('Entering:'||l_proc, 25);
105 open c_delegate_bookings2;
106 fetch c_delegate_bookings2 into l_exists;
107 if c_delegate_bookings2%found then
108 close c_delegate_bookings2;
109 fnd_message.set_name('OTA','OTA_13540_CUSTOMER_DELETE');
110 fnd_message.set_token('TABLE','OTA_DELEGATE_BOOKINGS');
111 fnd_message.set_token('COLUMN','THIRD_PARTY_CUSTOMER_ID');
112 fnd_message.raise_error;
113 end if;
114 close c_delegate_bookings2;
115 --
116 hr_utility.set_location('Entering:'||l_proc, 30);
117 open c_event_associations;
118 fetch c_event_associations into l_exists;
119 if c_event_associations%found then
120 close c_event_associations;
121 fnd_message.set_name('OTA','OTA_13540_CUSTOMER_DELETE');
122 fnd_message.set_token('TABLE','OTA_EVENT_ASSOCIATIONS');
123 fnd_message.set_token('COLUMN','CUSTOMER_ID');
124 fnd_message.raise_error;
125 end if;
126 close c_event_associations;
127 --
128 hr_utility.set_location('Entering:'||l_proc, 35);
129 open c_finance_headers1;
130 fetch c_finance_headers1 into l_exists;
131 if c_finance_headers1%found then
132 close c_finance_headers1;
133 fnd_message.set_name('OTA','OTA_13540_CUSTOMER_DELETE');
134 fnd_message.set_token('TABLE','OTA_FINANCE_HEADERS');
135 fnd_message.set_token('COLUMN','CUSTOMER_ID');
136 fnd_message.raise_error;
137 end if;
138 close c_finance_headers1;
139 --
140 elsif p_contact_id is not null then
141 --
142 hr_utility.set_location('Entering:'||l_proc, 40);
143 open c_delegate_bookings3;
144 fetch c_delegate_bookings3 into l_exists;
145 if c_delegate_bookings3%found then
146 close c_delegate_bookings3;
147 fnd_message.set_name('OTA','OTA_13541_CONTACT_DELETE');
148 fnd_message.set_token('TABLE','OTA_DELEGATE_BOOKINGS');
149 fnd_message.set_token('COLUMN','CONTACT_ID');
150 fnd_message.raise_error;
151 end if;
152 close c_delegate_bookings3;
153 --
154 hr_utility.set_location('Entering:'||l_proc, 45);
155 open c_delegate_bookings4;
156 fetch c_delegate_bookings4 into l_exists;
157 if c_delegate_bookings4%found then
158 close c_delegate_bookings4;
159 fnd_message.set_name('OTA','OTA_13541_CONTACT_DELETE');
160 fnd_message.set_token('TABLE','OTA_DELEGATE_BOOKINGS');
161 fnd_message.set_token('COLUMN','THIRD_PARTY_CONTACT_ID');
162 fnd_message.raise_error;
163 end if;
164 close c_delegate_bookings4;
165 --
166 hr_utility.set_location('Entering:'||l_proc, 50);
167 open c_delegate_bookings5;
168 fetch c_delegate_bookings5 into l_exists;
169 if c_delegate_bookings5%found then
170 close c_delegate_bookings5;
171 fnd_message.set_name('OTA','OTA_13541_CONTACT_DELETE');
172 fnd_message.set_token('TABLE','OTA_DELEGATE_BOOKINGS');
173 fnd_message.set_token('COLUMN','DELEGATE_CONTACT_ID');
174 fnd_message.raise_error;
175 end if;
176 close c_delegate_bookings5;
177 --
178 hr_utility.set_location('Entering:'||l_proc, 55);
179 open c_finance_headers2;
180 fetch c_finance_headers2 into l_exists;
181 if c_finance_headers2%found then
182 close c_finance_headers2;
183 fnd_message.set_name('OTA','OTA_13541_CONTACT_DELETE');
184 fnd_message.set_token('TABLE','OTA_FINANCE_HEADERS');
185 fnd_message.set_token('COLUMN','CONTACT_ID');
186 fnd_message.raise_error;
187 end if;
188 close c_finance_headers2;
189 --
190 elsif p_address_id is not null then
191 --
192 hr_utility.set_location('Entering:'||l_proc, 60);
193 open c_delegate_bookings6;
194 fetch c_delegate_bookings6 into l_exists;
195 if c_delegate_bookings6%found then
196 close c_delegate_bookings6;
197 fnd_message.set_name('OTA','OTA_13542_ADDRESS_DELETE');
198 fnd_message.set_token('TABLE','OTA_DELEGATE_BOOKINGS');
199 fnd_message.set_token('COLUMN','CONTACT_ADDRESS_ID');
200 fnd_message.raise_error;
201 end if;
202 close c_delegate_bookings6;
203 --
204 hr_utility.set_location('Entering:'||l_proc, 65);
205 open c_delegate_bookings7;
206 fetch c_delegate_bookings7 into l_exists;
207 if c_delegate_bookings7%found then
208 close c_delegate_bookings7;
209 fnd_message.set_name('OTA','OTA_13542_ADDRESS_DELETE');
210 fnd_message.set_token('TABLE','OTA_DELEGATE_BOOKINGS');
211 fnd_message.set_token('COLUMN','THIRD_PARTY_ADDRESS_ID');
212 fnd_message.raise_error;
213 end if;
214 close c_delegate_bookings7;
215 --
216 hr_utility.set_location('Entering:'||l_proc, 70);
217 open c_finance_headers3;
218 fetch c_finance_headers3 into l_exists;
219 if c_finance_headers3%found then
220 close c_finance_headers3;
221 fnd_message.set_name('OTA','OTA_13542_ADDRESS_DELETE');
222 fnd_message.set_token('TABLE','OTA_FINANCE_HEADERS');
223 fnd_message.set_token('COLUMN','ADDRESS_ID');
224 fnd_message.raise_error;
225 end if;
226 close c_finance_headers3;
227 --
228 end if;
229 --
230 hr_utility.set_location(' Leaving:'||l_proc, 75);
231 end;
232 --
233 end ota_ar_delete;