[Home] [Help]
PACKAGE BODY: APPS.OTA_EVENT_ASSOCIATIONS_PKG
Source
1 package body ota_event_associations_pkg as
2 /* $Header: ottea02t.pkb 120.0.12010000.2 2008/08/05 11:45:22 ubhat ship $ */
3
4 --
5 -- ----------------------------------------------------------------------------
6 -- | Private Global Definitions |
7 -- ----------------------------------------------------------------------------
8 --
9 g_package varchar2(33) := ' ota_event_associations_pkg.'; -- Global package name
10 --
11 --
12 -- Check that max places is not exceeded for a booking deal
13 --
14 --
15 procedure check_booking_deal_places (p_event_id in number,
16 p_customer_id in number,
17 p_booking_deal_id in number,
18 p_places in number) is
19 --
20 l_proc varchar2(80) := g_package||' check_booking_deal_places';
21 l_places_used number(9) := 0;
22 l_max_places number(9);
23 --
24 cursor c_places_used is
25 select nvl(sum(number_of_places),0)
26 from ota_delegate_bookings tdb,
27 ota_finance_lines fin,
28 ota_booking_status_types bst,
29 ota_events evt
30 where fin.booking_deal_id = p_booking_deal_id
31 and fin.cancelled_flag = 'N'
32 and fin.booking_id = tdb.booking_id
33 and tdb.booking_status_type_id = bst.booking_status_type_id
34 and bst.type in ('P','A','E')
35 and tdb.event_id = evt.event_id
36 and ((evt.price_basis = 'C'
37 and tdb.delegate_contact_id is null
38 and tdb.customer_id <> p_customer_id
39 and tdb.event_id <> p_event_id)
40 or evt.price_basis = 'S');
41 --
42 cursor c_max_places is
43 select number_of_places
44 from ota_booking_deals
45 where booking_deal_id = p_booking_deal_id;
46 --
47 begin
48 --
49 hr_utility.set_location('Entering '||l_proc,10);
50 --
51 -- Get maximum number of places for booking deal
52 --
53 open c_max_places;
54 --
55 fetch c_max_places into l_max_places;
56 if c_max_places%notfound then
57 --
58 l_max_places := -1;
59 --
60 end if;
61 --
62 close c_max_places;
63 --
64 -- If no limit on booking deal then return to calling procedure
65 --
66 if l_max_places = -1 then
67 return;
68 end if;
69 --
70 -- Get number of places used
71 --
72 open c_places_used;
73 --
74 fetch c_places_used into l_places_used;
75 --
76 close c_places_used;
77 --
78 -- Check if number of places used and number of places requested
79 -- exceed limit on places
80 --
81 if l_max_places < (l_places_used + p_places) then
82 --
83 fnd_message.set_name('OTA','OTA_13606_BOOKING_DEAL_PLACES');
84 fnd_message.raise_error;
85 --
86 end if;
87 --
88 hr_utility.set_location('Leaving '||l_proc,10);
89 --
90 end check_booking_deal_places;
91 --
92 procedure maintain_delegate_bookings
93 (
94 p_validate in boolean
95 ,p_price_basis in varchar2
96 ,p_business_group_id in number
97 ,p_event_id in number
98 ,p_customer_id in number
99 ,p_booking_id in out nocopy number
100 ,p_tdb_object_version_number in out nocopy number
101 ,p_booking_status_type_id in number
102 ,p_date_status_changed in date
103 ,p_status_change_comments in varchar2
104 ,p_booking_contact_id in number
105 ,p_contact_address_id in number
106 ,p_delegate_contact_phone in varchar2
107 ,p_delegate_contact_fax in varchar2
108 ,p_internal_booking_flag in varchar2
109 ,p_source_of_booking in varchar2
110 ,p_number_of_places in number
111 ,p_date_booking_placed in date
112 ,p_update_finance_line in varchar2
113 ,p_tfl_object_version_number in out nocopy number
114 ,p_finance_header_id in number
115 ,p_currency_code in varchar2
116 ,p_standard_amount in number
117 ,p_unitary_amount in number
118 ,p_money_amount in number
119 ,p_booking_deal_id in number
120 ,p_booking_deal_type in varchar2
121 ,p_finance_line_id in out nocopy number
122 ,p_delegate_contact_email in varchar2
123 ) is
124 --
125 l_proc varchar2(72) := g_package||'maintain_delegate_bookings';
126 --
127 l_create_finance_line varchar2(1);
128 l_type varchar2(10);
129 cursor c1 is
130 select type
131 from ota_booking_status_types
132 where booking_status_type_id = p_booking_status_type_id;
133 --
134 begin
135 --
136 hr_utility.set_location('Entering:'||l_proc,5);
137 --
138 if p_price_basis not in('C','O') or p_price_basis is null then
139 return;
140 end if;
141 --
142 if p_booking_contact_id is null then
143 fnd_message.set_name('OTA','OTA_13554_TEA_CONTACT_NULL');
144 fnd_message.raise_error;
145 end if;
146 --
147 if p_number_of_places is null then
148 fnd_message.set_name('OTA','OTA_13555_TEA_PLACES_NULL');
149 fnd_message.raise_error;
150 end if;
151 --
152 -- Check if booking needs a place
153 --
154 open c1;
155 --
156 fetch c1 into l_type;
157 --
158 close c1;
159 --
160 if l_type in ('P','A') then
161 --
162 check_booking_deal_places(p_event_id,
163 p_customer_id,
164 p_booking_deal_id,
165 p_number_of_places);
166 --
167 end if;
168 --
169 if p_tdb_object_version_number is null then
170 --
171 if p_finance_header_id is not null then
172 l_create_finance_line := 'Y';
173 else
174 l_create_finance_line := 'N';
175 end if;
176 --
177 ota_tdb_api_ins2.create_enrollment (
178 p_booking_id => p_booking_id
179 ,p_booking_status_type_id => p_booking_status_type_id
180 ,p_contact_id => p_booking_contact_id
181 ,p_contact_address_id => p_contact_address_id
182 ,p_delegate_contact_phone => p_delegate_contact_phone
183 ,p_delegate_contact_fax => p_delegate_contact_fax
184 ,p_business_group_id => p_business_group_id
185 ,p_event_id => p_event_id
186 ,p_customer_id => p_customer_id
187 ,p_date_booking_placed => p_date_booking_placed
188 ,p_corespondent => 'C'
189 ,p_internal_booking_flag => p_internal_booking_flag
190 ,p_number_of_places => p_number_of_places
191 ,p_object_version_number => p_tdb_object_version_number
192 ,p_source_of_booking => p_source_of_booking
193 ,p_create_finance_line => l_create_finance_line
194 ,p_finance_header_id => p_finance_header_id
195 ,p_currency_code => p_currency_code
196 ,p_standard_amount => p_standard_amount
197 ,p_unitary_amount => p_unitary_amount
198 ,p_money_amount => p_money_amount
199 ,p_booking_deal_id => p_booking_deal_id
200 ,p_booking_deal_type => p_booking_deal_type
201 ,p_finance_line_id => p_finance_line_id
202 ,p_enrollment_type => 'C'
203 ,p_validate => p_validate
204 ,p_delegate_contact_email => p_delegate_contact_email
205 );
206 --
207 else
208 --
209 ota_tdb_api_upd2.update_enrollment (
210 p_booking_id => p_booking_id
211 ,p_booking_status_type_id => p_booking_status_type_id
212 ,p_date_status_changed => p_date_status_changed
213 ,p_status_change_comments => p_status_change_comments
214 ,p_contact_id => p_booking_contact_id
215 ,p_contact_address_id => p_contact_address_id
216 ,p_delegate_contact_phone => p_delegate_contact_phone
217 ,p_delegate_contact_fax => p_delegate_contact_fax
218 ,p_business_group_id => p_business_group_id
219 ,p_event_id => p_event_id
220 ,p_customer_id => p_customer_id
221 ,p_internal_booking_flag => p_internal_booking_flag
222 ,p_number_of_places => p_number_of_places
223 ,p_object_version_number => p_tdb_object_version_number
224 ,p_source_of_booking => p_source_of_booking
225 ,p_update_finance_line => p_update_finance_line
226 ,p_tfl_object_version_number => p_tfl_object_version_number
227 ,p_finance_header_id => p_finance_header_id
228 ,p_currency_code => p_currency_code
229 ,p_standard_amount => p_standard_amount
230 ,p_unitary_amount => p_unitary_amount
231 ,p_money_amount => p_money_amount
232 ,p_booking_deal_id => p_booking_deal_id
233 ,p_booking_deal_type => p_booking_deal_type
234 ,p_finance_line_id => p_finance_line_id
235 ,p_enrollment_type => 'C'
236 ,p_validate => p_validate
237 ,p_delegate_contact_email => p_delegate_contact_email
238
239 );
240 --
241 end if;
242 --
243 hr_utility.set_location('Leaving:'||l_proc,10);
244 --
245 end maintain_delegate_bookings;
246
247 end ota_event_associations_pkg;