[Home] [Help]
PACKAGE BODY: APPS.PQP_RIW_OFFERING_WRAPPER
Source
1 PACKAGE BODY pqp_riw_offering_wrapper as
2 /* $Header: pqpriwofwr.pkb 120.0.12010000.4 2009/01/21 13:49:54 sbrahmad noship $ */
3
4 -- =============================================================================
5 -- ~ Package Body Global variables:
6 -- =============================================================================
7 g_package varchar2(33) := 'pqp_riw_offering_wrapper.';
8 PROCEDURE InsUpd_Offering
9 (p_validate in number default hr_api.g_false_num
10 ,p_effective_date in date
11 ,p_business_group_id in number
12 ,p_name in varchar2
13 ,p_start_date in date
14 ,p_activity_version_id in number default null
15 ,p_end_date in date default null
16 ,p_owner_id in number default null
17 ,p_delivery_mode_id in number default null
18 ,p_language_id in number default null
19 ,p_duration in number default null
20 ,p_duration_units in varchar2 default null
21 ,p_learning_object_id in number default null
22 ,p_player_toolbar_flag in varchar2 default null
23 ,p_player_exit_flag in varchar2 default null
24 ,p_player_next_flag in varchar2 default null
25 ,p_player_previous_flag in varchar2 default null
26 ,p_player_outline_flag in varchar2 default null
27 ,p_player_new_window_flag in varchar2 default null
28 ,p_maximum_attendees in number default null
29 ,p_maximum_internal_attendees in number default null
30 ,p_minimum_attendees in number default null
31 ,p_actual_cost in number default null
32 ,p_budget_cost in number default null
33 ,p_budget_currency_code in varchar2 default null
34 ,p_price_basis in varchar2 default null
35 ,p_currency_code in varchar2 default null
36 ,p_standard_price in number default null
37 ,p_attribute_category in varchar2 default null
38 ,p_attribute1 in varchar2 default null
39 ,p_attribute2 in varchar2 default null
40 ,p_attribute3 in varchar2 default null
41 ,p_attribute4 in varchar2 default null
42 ,p_attribute5 in varchar2 default null
43 ,p_attribute6 in varchar2 default null
44 ,p_attribute7 in varchar2 default null
45 ,p_attribute8 in varchar2 default null
46 ,p_attribute9 in varchar2 default null
47 ,p_attribute10 in varchar2 default null
48 ,p_attribute11 in varchar2 default null
49 ,p_attribute12 in varchar2 default null
50 ,p_attribute13 in varchar2 default null
51 ,p_attribute14 in varchar2 default null
52 ,p_attribute15 in varchar2 default null
53 ,p_attribute16 in varchar2 default null
54 ,p_attribute17 in varchar2 default null
55 ,p_attribute18 in varchar2 default null
56 ,p_attribute19 in varchar2 default null
57 ,p_attribute20 in varchar2 default null
58 ,p_offering_id in number default null
59 ,p_object_version_number in number
60 ,p_return_status out nocopy varchar2
61 ,p_data_source in varchar2 default null
62 ,p_vendor_id in number default null
63 ,p_description in varchar2 default null
64 ,p_competency_update_level in varchar2 default null
65 ,p_language_code in varchar2 default null
66 ,P_CRT_UPD in varchar2 default null
67 ) is
68 -- =============================================================================
69 -- Variables for API Boolean parameters
70 -- =============================================================================
71 l_validate boolean;
72
73 -- =============================================================================
74 -- Other variables
75 -- =============================================================================
76
77 l_activity_version_id number;
78 l_category_usage_id number;
79 l_player_toolbar_bitset number;
80 l_offering_id number;
81 l_player_exit_flag varchar2(72);
82 l_player_next_flag varchar2(72);
83 l_player_previous_flag varchar2(72);
84 l_player_outline_flag varchar2(72);
85 l_error_msg varchar2(4000);
86 p_organization_id number;
87 p_event_type varchar2(72);
88 p_title varchar2(72);
89 p_course_end_date date;
90 p_course_start_date date;
91 p_enrolment_end_date date;
92 p_enrolment_start_date date;
93 p_event_status varchar2(72);
94 p_book_independent_flag varchar2(72);
95 p_public_event_flag varchar2(72);
96 p_secure_event_flag varchar2(72);
97 p_timezone varchar2(72);
98 l_title varchar2(72);
99 l_event_id number;
100 l_create_flag number(3) := 1;
101 l_off_id number;
102 l_obj_ver_num number;
103 l_object_version_number number;
104 e_upl_not_allowed exception; -- when mode is 'View Only'
105 e_crt_not_allowed exception; -- when mode is 'Update Only'
106 g_upl_err_msg varchar2(100) := 'Upload NOT allowed.';
107 g_crt_err_msg varchar2(100) := 'Creating NOT allowed.';
108
109 -- =============================================================================
110 -- Default_Record_Values:
111 -- =============================================================================
112 l_proc varchar2(72) := g_package||'InsUpd_Offering';
113
114 begin
115 --hr_utility.trace_on(null, 'Offer_Trace');
116 hr_utility.set_location('Entering: '|| l_proc, 11);
117 --
118 -- Issue a savepoint
119 --
120 savepoint InsUpd_Offering;
121
122 --
123 -- Convert constant values to their corresponding boolean value
124 --
125 l_validate := hr_api.constant_to_boolean (p_constant_value => p_validate);
126
127 -- $ Finding the value for the l_player_toolbar_bitset based on the input
128 -- $ Flag values
129 l_player_exit_flag :=1;
130 l_player_previous_flag :=2;
131 l_player_next_flag :=4;
132 l_player_outline_flag :=1024;
133 l_player_toolbar_bitset :=0;
134
135 l_offering_id := p_offering_id;
136
137 hr_utility.set_location('The offering id is : '||l_offering_id, 90);
138
139 if l_offering_id is not null then
140 l_create_flag := 2; --Update Offering
141 else
142 l_create_flag := 1; --create Offering
143 end if;
144
145 if (P_CRT_UPD = 'D') then
146 raise e_upl_not_allowed; -- View only flag is enabled but Trying to Upload
147 end if;
148 if (P_CRT_UPD = 'U' and l_create_flag = 1) then
149 raise e_crt_not_allowed; -- Update only flag is enabled but Trying to Create
150 end if;
151
152 if p_player_exit_flag ='Y' then
153 l_player_toolbar_bitset := l_player_toolbar_bitset + l_player_exit_flag;
154 end if;
155
156 if p_player_previous_flag ='Y' then
157 l_player_toolbar_bitset := l_player_toolbar_bitset + l_player_previous_flag;
158 end if;
159
160 if p_player_next_flag ='Y' then
161 l_player_toolbar_bitset := l_player_toolbar_bitset + l_player_next_flag;
162 end if;
163
164 if p_player_outline_flag ='Y' then
165 l_player_toolbar_bitset := l_player_toolbar_bitset + l_player_outline_flag;
166 end if;
167
168 --
169 -- Call API
170 --
171 if l_create_flag = 1 then
172 ota_offering_api.create_offering
173 (p_validate => l_validate
174 ,p_effective_date => p_effective_date
175 ,p_business_group_id => p_business_group_id
176 ,p_name => p_name
177 ,p_start_date => p_start_date
178 ,p_activity_version_id => p_activity_version_id
179 ,p_end_date => p_end_date
180 ,p_owner_id => p_owner_id
181 ,p_delivery_mode_id => p_delivery_mode_id
182 ,p_language_id => p_language_id
183 ,p_duration => p_duration
184 ,p_duration_units => p_duration_units
185 ,p_learning_object_id => p_learning_object_id
186 ,p_player_toolbar_flag => p_player_toolbar_flag
187 ,p_player_toolbar_bitset => l_player_toolbar_bitset
188 ,p_player_new_window_flag => p_player_new_window_flag
189 ,p_maximum_attendees => p_maximum_attendees
190 ,p_maximum_internal_attendees => p_maximum_internal_attendees
191 ,p_minimum_attendees => p_minimum_attendees
192 ,p_actual_cost => p_actual_cost
193 ,p_budget_cost => p_budget_cost
194 ,p_budget_currency_code => p_budget_currency_code
195 ,p_price_basis => p_price_basis
196 ,p_currency_code => p_currency_code
197 ,p_standard_price => p_standard_price
198 ,p_attribute_category => p_attribute_category
199 ,p_attribute1 => p_attribute1
200 ,p_attribute2 => p_attribute2
201 ,p_attribute3 => p_attribute3
202 ,p_attribute4 => p_attribute4
203 ,p_attribute5 => p_attribute5
204 ,p_attribute6 => p_attribute6
205 ,p_attribute7 => p_attribute7
206 ,p_attribute8 => p_attribute8
207 ,p_attribute9 => p_attribute9
208 ,p_attribute10 => p_attribute10
209 ,p_attribute11 => p_attribute11
210 ,p_attribute12 => p_attribute12
211 ,p_attribute13 => p_attribute13
212 ,p_attribute14 => p_attribute14
213 ,p_attribute15 => p_attribute15
214 ,p_attribute16 => p_attribute16
215 ,p_attribute17 => p_attribute17
216 ,p_attribute18 => p_attribute18
217 ,p_attribute19 => p_attribute19
218 ,p_attribute20 => p_attribute20
219 ,p_offering_id => l_off_id
220 ,p_object_version_number => l_obj_ver_num
221 ,p_data_source => p_data_source
222 ,p_vendor_id => p_vendor_id
223 ,p_description => p_description
224 ,p_competency_update_level => p_competency_update_level
225 ,p_language_code => p_language_code
226 );
227
228 -- $ Creating the Default Class for SELFPACED Offering (407)
229 -- $ Setting the default values for the Default Class
230
231 p_organization_id := p_business_group_id;
232 p_event_type := 'SELFPACED';
233 p_title := p_name;
234 p_course_end_date := p_end_date;
235 p_course_start_date := p_start_date;
236 p_enrolment_end_date := p_end_date;
237 p_enrolment_start_date := p_start_date;
238 p_event_status := 'N';
239 p_book_independent_flag := 'N';
240 p_public_event_flag := 'N';
241 p_secure_event_flag :='N';
242 p_timezone:= 'America/Chicago';
243
244 if p_delivery_mode_id = 407 then
245
246 ota_event_api.create_class
247 (p_effective_date => p_effective_date
248 ,p_event_id => l_event_id
249 ,p_vendor_id => p_vendor_id
250 ,p_activity_version_id => p_activity_version_id
251 ,p_business_group_id => p_business_group_id
252 ,p_organization_id => p_organization_id
253 ,p_event_type => p_event_type
254 ,p_object_version_number => l_obj_ver_num
255 ,p_title => p_title
256 ,p_budget_cost => p_budget_cost
257 ,p_actual_cost => p_actual_cost
258 ,p_budget_currency_code => p_budget_currency_code
259 -- ,p_centre => p_centre
260 -- ,p_comments => p_comments
261 ,p_course_end_date => p_course_end_date
262 -- ,p_course_end_time => p_course_end_time
263 ,p_course_start_date => p_course_start_date
264 -- ,p_course_start_time => p_course_start_time
265 ,p_duration => p_duration
266 ,p_duration_units => p_duration_units
267 ,p_enrolment_end_date => p_enrolment_end_date
268 ,p_enrolment_start_date => p_enrolment_start_date
269 ,p_language_id => p_language_id
270 -- ,p_user_status => p_user_status
271 -- ,p _development_event_type => p_development_event_type
272 ,p_event_status => p_event_status
273 ,p_price_basis => p_price_basis
274 ,p_currency_code => p_currency_code
275 ,p_maximum_attendees => p_maximum_attendees
276 ,p_maximum_internal_attendees => p_maximum_internal_attendees
277 ,p_minimum_attendees => p_minimum_attendees
278 ,p_standard_price => p_standard_price
279 -- ,p_category_code => p_category_code
280 -- ,p_parent_event_id => p_parent_event_id
281 ,p_book_independent_flag => p_book_independent_flag
282 ,p_public_event_flag => p_public_event_flag
283 ,p_secure_event_flag => p_secure_event_flag
284 -- ,p_project_id => p_project_id
285 ,p_owner_id => p_owner_id
286 -- ,p_line_id => p_line_id
287 -- ,p_org_id => p_org_id
288 -- ,p_training_center_id => p_training_center_id
289 -- ,p_location_id => p_location_id
290 -- ,p_offering_id => l_offering_id
291 ,p_timezone => p_timezone
292 ,p_parent_offering_id => l_off_id
293 ,p_validate => l_validate
294 ,p_data_source => p_data_source
295 );
296
297
298 l_title := p_name || to_char(l_event_id);
299 update ota_events set title = l_title where event_id = l_event_id;
300 update ota_events_tl set title = l_title where event_id = l_event_id;
301
302 end if;
303
304 end if;
305
306 if l_create_flag = 2 then
307
308 select object_version_number into l_object_version_number
309 from ota_offerings where
310 offering_id = l_offering_id;
311 ota_offering_api.update_offering
312 (p_validate => l_validate
313 ,p_effective_date => p_effective_date
314 ,p_business_group_id => p_business_group_id
315 ,p_name => p_name
316 ,p_start_date => p_start_date
317 ,p_activity_version_id => p_activity_version_id
318 ,p_end_date => p_end_date
319 ,p_owner_id => p_owner_id
320 ,p_delivery_mode_id => p_delivery_mode_id
321 ,p_language_id => p_language_id
322 ,p_duration => p_duration
323 ,p_duration_units => p_duration_units
324 ,p_learning_object_id => p_learning_object_id
325 ,p_player_toolbar_flag => p_player_toolbar_flag
326 ,p_player_toolbar_bitset => l_player_toolbar_bitset
327 ,p_player_new_window_flag => p_player_new_window_flag
328 ,p_maximum_attendees => p_maximum_attendees
329 ,p_maximum_internal_attendees => p_maximum_internal_attendees
330 ,p_minimum_attendees => p_minimum_attendees
331 ,p_actual_cost => p_actual_cost
332 ,p_budget_cost => p_budget_cost
333 ,p_budget_currency_code => p_budget_currency_code
334 ,p_price_basis => p_price_basis
335 ,p_currency_code => p_currency_code
336 ,p_standard_price => p_standard_price
337 ,p_attribute_category => p_attribute_category
338 ,p_attribute1 => p_attribute1
339 ,p_attribute2 => p_attribute2
340 ,p_attribute3 => p_attribute3
341 ,p_attribute4 => p_attribute4
342 ,p_attribute5 => p_attribute5
343 ,p_attribute6 => p_attribute6
344 ,p_attribute7 => p_attribute7
345 ,p_attribute8 => p_attribute8
346 ,p_attribute9 => p_attribute9
347 ,p_attribute10 => p_attribute10
348 ,p_attribute11 => p_attribute11
349 ,p_attribute12 => p_attribute12
350 ,p_attribute13 => p_attribute13
351 ,p_attribute14 => p_attribute14
352 ,p_attribute15 => p_attribute15
353 ,p_attribute16 => p_attribute16
354 ,p_attribute17 => p_attribute17
355 ,p_attribute18 => p_attribute18
356 ,p_attribute19 => p_attribute19
357 ,p_attribute20 => p_attribute20
358 ,p_offering_id => l_offering_id
359 ,p_object_version_number => l_object_version_number
360 ,p_data_source => p_data_source
361 ,p_vendor_id => p_vendor_id
362 ,p_description => p_description
363 ,p_competency_update_level => p_competency_update_level
364 ,p_language_code => p_language_code
365 );
366 end if;
367
368 hr_utility.set_location(' Leaving:' || l_proc,30);
369 Exception
370 when e_upl_not_allowed then
371 hr_utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
372 hr_utility.set_message_token('GENERIC_TOKEN',g_upl_err_msg);
373 hr_utility.set_location('Leaving: ' || l_proc, 90);
374 hr_utility.raise_error;
375 when e_crt_not_allowed then
376 hr_utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
377 hr_utility.set_message_token('GENERIC_TOKEN',g_crt_err_msg);
378 hr_utility.set_location('Leaving: ' || l_proc, 100);
379 hr_utility.raise_error;
380 when others then
381
382 --l_error_msg := Substr(SQLERRM,1,2000);
383 hr_utility.set_location('SQLCODE :' || SQLCODE,90);
384 hr_utility.set_location('SQLERRM :' || SQLERRM,90);
385 --hr_utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
386 --hr_utility.set_message_token('GENERIC_TOKEN',substr(l_error_msg,1,500) );
387 hr_utility.set_location(' Leaving:' || l_proc,50);
388 hr_utility.raise_error;
389
390
391 end InsUpd_Offering;
392 end pqp_riw_offering_wrapper;