[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.7 2009/04/28 13:25:27 sravikum noship $ */
3
4 -- =============================================================================
5 -- ~ Package Body Global variables:
6 -- =============================================================================
7 g_package varchar2(33) := 'pqp_riw_offering_wrapper.';
8 g_offering_rec ota_offerings_vl%rowtype;
9 g_interface_code varchar2(150);
10 g_player_toolbar_flag varchar2(9);
11 g_player_new_window_flag varchar2(9);
12
13 -- =============================================================================
14 -- Default_Record_Values:
15 -- =============================================================================
16 function Default_Offering_Rec
17 return ota_offerings_vl%rowtype is
18 l_proc_name constant varchar2(150) := g_package||'Default_Offering_Rec';
19 l_offering_rec ota_offerings_vl%rowtype;
20
21 begin
22
23 Hr_Utility.set_location(' Entering: '||l_proc_name, 5);
24 /*
25 ==========================================================================
26 g_varchar2 constant varchar2(9) := '$Sys_Def$';
27 g_number constant number := -987123654;
28 g_date constant date := to_date('01-01-4712', 'DD-MM-SYYYY');
29 ==========================================================================
30 */
31 Hr_Utility.set_location(' Before : ', 5);
32 l_offering_rec.business_group_id := hr_api.g_number;
33 l_offering_rec.name := hr_api.g_varchar2 ;
34 l_offering_rec.start_date := hr_api.g_date ;
35 l_offering_rec.activity_version_id := hr_api.g_number ;
36 l_offering_rec.end_date := hr_api.g_date ;
37 l_offering_rec.owner_id := hr_api.g_number ;
38 l_offering_rec.delivery_mode_id := hr_api.g_number ;
39 l_offering_rec.language_id := hr_api.g_number ;
40 Hr_Utility.set_location(' after : l_offering_rec.language_id', 5);
41 l_offering_rec.duration := hr_api.g_number ;
42 Hr_Utility.set_location(' after : l_offering_rec.duration', 5);
43 l_offering_rec.duration_units := hr_api.g_varchar2 ;
44 Hr_Utility.set_location(' after : l_offering_rec.duration_units', 5);
45 l_offering_rec.learning_object_id := hr_api.g_number ;
46 Hr_Utility.set_location(' Before duration : ', 5);
47 -- l_offering_rec.player_toolbar_flag := hr_api.g_varchar2 ;
48 g_player_toolbar_flag :=hr_api.g_varchar2;
49
50 Hr_Utility.set_location(' after : l_offering_rec.player_toolbar_flag', 5);
51 l_offering_rec.player_toolbar_bitset := hr_api.g_number ;
52 Hr_Utility.set_location(' after : l_offering_rec.player_toolbar_bitset', 5);
53 -- l_offering_rec.player_new_window_flag := hr_api.g_varchar2 ;
54
55 g_player_new_window_flag :=hr_api.g_varchar2;
56 Hr_Utility.set_location(' after :g_player_new_window_flag '||g_player_new_window_flag, 5);
57 l_offering_rec.maximum_attendees := hr_api.g_number ;
58 Hr_Utility.set_location(' after :l_offering_rec.maximum_attendees ', 5);
59 l_offering_rec.maximum_internal_attendees := hr_api.g_number ;
60 Hr_Utility.set_location(' after :l_offering_rec.maximum_attendees ', 5);
61
62 l_offering_rec.minimum_attendees := hr_api.g_number ;
63 Hr_Utility.set_location(' after :l_offering_rec.maximum_attendees ', 5);
64
65 l_offering_rec.actual_cost := hr_api.g_number ;
66
67 Hr_Utility.set_location(' after :l_offering_rec.maximum_attendees ', 5);
68 l_offering_rec.budget_cost := hr_api.g_number ;
69 Hr_Utility.set_location(' after :l_offering_rec.maximum_attendees ', 5);
70 l_offering_rec.budget_currency_code := hr_api.g_varchar2 ;
71 Hr_Utility.set_location(' after :l_offering_rec.maximum_attendees ', 5);
72 l_offering_rec.price_basis := hr_api.g_varchar2 ;
73 l_offering_rec.currency_code := hr_api.g_varchar2 ;
74 l_offering_rec.standard_price := hr_api.g_number ;
75 l_offering_rec.attribute_category := hr_api.g_varchar2 ;
76 l_offering_rec.attribute1 := hr_api.g_varchar2 ;
77 l_offering_rec.attribute2 := hr_api.g_varchar2 ;
78 l_offering_rec.attribute3 := hr_api.g_varchar2 ;
79 l_offering_rec.attribute4 := hr_api.g_varchar2 ;
80 l_offering_rec.attribute5 := hr_api.g_varchar2 ;
81 l_offering_rec.attribute6 := hr_api.g_varchar2 ;
82 l_offering_rec.attribute7 := hr_api.g_varchar2 ;
83 l_offering_rec.attribute8 := hr_api.g_varchar2 ;
84 l_offering_rec.attribute9 := hr_api.g_varchar2 ;
85 l_offering_rec.attribute10 := hr_api.g_varchar2 ;
86 l_offering_rec.attribute11 := hr_api.g_varchar2 ;
87 l_offering_rec.attribute12 := hr_api.g_varchar2 ;
88 l_offering_rec.attribute13 := hr_api.g_varchar2 ;
89 l_offering_rec.attribute14 := hr_api.g_varchar2 ;
90 l_offering_rec.attribute15 := hr_api.g_varchar2 ;
91 l_offering_rec.attribute16 := hr_api.g_varchar2 ;
92 l_offering_rec.attribute17 := hr_api.g_varchar2 ;
93 l_offering_rec.attribute18 := hr_api.g_varchar2 ;
94 l_offering_rec.attribute19 := hr_api.g_varchar2 ;
95 l_offering_rec.attribute20 := hr_api.g_varchar2 ;
96 l_offering_rec.data_source := hr_api.g_varchar2 ;
97 l_offering_rec.vendor_id := hr_api.g_number ;
98 l_offering_rec.description := hr_api.g_varchar2 ;
99 l_offering_rec.competency_update_level := hr_api.g_varchar2 ;
100 l_offering_rec.language_code := hr_api.g_varchar2 ;
101
102 Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
103 return l_offering_rec;
104 exception
105 when others then
106 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
107 raise;
108
109 end Default_Offering_Rec;
110
111
112 -- =============================================================================
113 -- Get_Record_Values:
114 -- =============================================================================
115 function Get_Record_Values
116 (p_interface_code in varchar2 default null)
117 return ota_offerings_vl%rowtype is
118
119 cursor bne_cols(c_interface_code in varchar2) is
120 select lower(bic.interface_col_name) interface_col_name
121 from bne_interface_cols_b bic
122 where bic.interface_code = c_interface_code
123 and bic.display_flag ='Y';
124 --and bic.interface_col_type <> 2;
125
126 -- To query cols which are not displayed (DFF segments)
127 cursor bne_cols_no_disp(c_interface_code in varchar2) is
128 select lower(bic.interface_col_name) interface_col_name
129 from bne_interface_cols_b bic
130 where bic.interface_code = c_interface_code
131 and bic.display_flag ='N';
132
133 l_offering_rec ota_offerings_vl%rowtype;
134 col_name varchar2(150);
135 l_proc_name constant varchar2(150) := g_package||'Get_Record_Values';
136 begin
137
138 Hr_Utility.set_location(' Entering: '||l_proc_name, 5);
139 hr_utility.set_location('p_interface_code'||p_interface_code, 10);
140 l_offering_rec := Default_Offering_Rec;
141 hr_utility.set_location('p_interface_code'||p_interface_code, 20);
142 hr_utility.set_location('g_interface_code'||g_interface_code, 5);
143
144
145 for col_rec in bne_cols (g_interface_code)
146 loop
147 hr_utility.set_location(' in loop col_rec.interface_col_name'||col_rec.interface_col_name, 15);
148 case col_rec.interface_col_name
149
150 when 'p_business_group_id' then
151 l_offering_rec.business_group_id := g_offering_rec.business_group_id;
152 when 'p_name' then
153 l_offering_rec.name := g_offering_rec.name;
154 when 'p_start_date' then
155 l_offering_rec.start_date := g_offering_rec.start_date;
156 when 'p_activity_version_id' then
157 l_offering_rec.activity_version_id := g_offering_rec.activity_version_id;
158 when 'p_end_date' then
159 l_offering_rec.end_date := g_offering_rec.end_date;
160 when 'p_owner_id' then
161 l_offering_rec.owner_id := g_offering_rec.owner_id;
162 when 'p_delivery_mode_id' then
163 l_offering_rec.delivery_mode_id := g_offering_rec.delivery_mode_id;
164 when 'p_language_id' then
165 l_offering_rec.language_id := g_offering_rec.language_id;
166 when 'p_duration' then
167 l_offering_rec.duration := g_offering_rec.duration;
168 when 'p_duration_units' then
169 l_offering_rec.duration_units := g_offering_rec.duration_units;
170 when 'p_learning_object_id' then
171 l_offering_rec.learning_object_id := g_offering_rec.learning_object_id;
172 when 'p_player_toolbar_flag' then
173 l_offering_rec.player_toolbar_flag := g_offering_rec.player_toolbar_flag;
174 g_player_toolbar_flag := g_offering_rec.player_toolbar_flag;
175 when 'p_player_toolbar_bitset' then
176 l_offering_rec.player_toolbar_bitset := g_offering_rec.player_toolbar_bitset;
177 when 'p_player_exit_flag' then
178 l_offering_rec.player_toolbar_bitset := g_offering_rec.player_toolbar_bitset;
179 when 'p_player_previous_flag' then
180 l_offering_rec.player_toolbar_bitset := g_offering_rec.player_toolbar_bitset;
181 when 'p_player_outline_flag' then
182 l_offering_rec.player_toolbar_bitset := g_offering_rec.player_toolbar_bitset;
183 when 'p_player_next_flag' then
184 l_offering_rec.player_toolbar_bitset := g_offering_rec.player_toolbar_bitset;
185
186 when 'p_player_new_window_flag' then
187 l_offering_rec.player_new_window_flag := g_offering_rec.player_new_window_flag;
188 g_player_new_window_flag := g_offering_rec.player_new_window_flag;
189 when 'p_maximum_attendees' then
190 l_offering_rec.maximum_attendees := g_offering_rec.maximum_attendees;
191 when 'p_maximum_internal_attendees' then
192 l_offering_rec.maximum_internal_attendees := g_offering_rec.maximum_internal_attendees;
193 when 'p_minimum_attendees' then
194 l_offering_rec.minimum_attendees := g_offering_rec.minimum_attendees;
195 when 'p_actual_cost' then
196 l_offering_rec.actual_cost := g_offering_rec.actual_cost;
197 when 'p_budget_cost' then
198 l_offering_rec.budget_cost := g_offering_rec.budget_cost;
199 when 'p_budget_currency_code' then
200 l_offering_rec.budget_currency_code := g_offering_rec.budget_currency_code;
201 when 'p_price_basis' then
202 l_offering_rec.price_basis := g_offering_rec.price_basis;
203 when 'p_currency_code' then
204 l_offering_rec.currency_code := g_offering_rec.currency_code;
205 when 'p_standard_price' then
206 l_offering_rec.standard_price := g_offering_rec.standard_price;
207 when 'p_vendor_id' then
208 l_offering_rec.vendor_id := g_offering_rec.vendor_id;
209 when 'p_description' then
210 l_offering_rec.description := g_offering_rec.description;
211 when 'p_competency_update_level' then
212 l_offering_rec.competency_update_level := g_offering_rec.competency_update_level;
213 when 'p_language_code' then
214 l_offering_rec.language_code := g_offering_rec.language_code;
215 -- DFF
216 when 'p_attribute_category' then
217 l_offering_rec.attribute_category := g_offering_rec.attribute_category;
218 if l_offering_rec.attribute_category is not null then
219 for col_rec1 in bne_cols_no_disp(g_interface_code) loop
220
221 case col_rec1.interface_col_name
222 when 'p_attribute1' then
223 l_offering_rec.attribute1 := g_offering_rec.attribute1;
224 when 'p_attribute2' then
225 l_offering_rec.attribute2 := g_offering_rec.attribute2;
226 when 'p_attribute3' then
227 l_offering_rec.attribute3 := g_offering_rec.attribute3;
228 when 'p_attribute4' then
229 l_offering_rec.attribute4 := g_offering_rec.attribute4;
230 when 'p_attribute5' then
231 l_offering_rec.attribute5 := g_offering_rec.attribute5;
232 when 'p_attribute6' then
233 l_offering_rec.attribute6 := g_offering_rec.attribute6;
234 when 'p_attribute7' then
235 l_offering_rec.attribute7 := g_offering_rec.attribute7;
236 when 'p_attribute8' then
237 l_offering_rec.attribute8 := g_offering_rec.attribute8;
238 when 'p_attribute9' then
239 l_offering_rec.attribute9 := g_offering_rec.attribute9;
240 when 'p_attribute10' then
241 l_offering_rec.attribute10 := g_offering_rec.attribute10;
242 when 'p_attribute11' then
243 l_offering_rec.attribute11 := g_offering_rec.attribute11;
244 when 'p_attribute12' then
245 l_offering_rec.attribute12 := g_offering_rec.attribute12;
246 when 'p_attribute13' then
247 l_offering_rec.attribute13 := g_offering_rec.attribute13;
248 when 'p_attribute14' then
249 l_offering_rec.attribute14 := g_offering_rec.attribute14;
250 when 'p_attribute15' then
251 l_offering_rec.attribute15 := g_offering_rec.attribute15;
252 when 'p_attribute16' then
253 l_offering_rec.attribute16 := g_offering_rec.attribute16;
254 when 'p_attribute17' then
255 l_offering_rec.attribute17 := g_offering_rec.attribute17;
256 when 'p_attribute18' then
257 l_offering_rec.attribute18 := g_offering_rec.attribute18;
258 when 'p_attribute19' then
259 l_offering_rec.attribute19 := g_offering_rec.attribute19;
260 when 'p_attribute20' then
261 l_offering_rec.attribute20 := g_offering_rec.attribute20;
262 else
263 null;
264 end case;
265 end loop;
266 end if;
267 else
268 null;
269 end case;
270 end loop;
271 Hr_Utility.set_location(' Leaving: '||l_proc_name, 80);
272 return l_offering_rec;
273
274 end Get_Record_Values;
275
276 -- =============================================================================
277 -- InsUpd_Offering:
278 -- =============================================================================
279
280 PROCEDURE InsUpd_Offering
281 (p_validate in number default hr_api.g_false_num
282 ,p_effective_date in date
283 ,p_business_group_id in number
284 ,p_name in varchar2
285 ,p_start_date in date
286 ,p_activity_version_id in number default null
287 ,p_end_date in date default null
288 ,p_owner_id in number default null
289 ,p_delivery_mode_id in number default null
290 ,p_language_id in number default null
291 ,p_duration in number default null
292 ,p_duration_units in varchar2 default null
293 ,p_learning_object_id in number default null
294 ,p_player_toolbar_flag in varchar2 default null
295 ,p_player_exit_flag in varchar2 default null
296 ,p_player_next_flag in varchar2 default null
297 ,p_player_previous_flag in varchar2 default null
298 ,p_player_outline_flag in varchar2 default null
299 ,p_player_new_window_flag in varchar2 default null
300 ,p_maximum_attendees in number default null
301 ,p_maximum_internal_attendees in number default null
302 ,p_minimum_attendees in number default null
303 ,p_actual_cost in number default null
304 ,p_budget_cost in number default null
305 ,p_budget_currency_code in varchar2 default null
306 ,p_price_basis in varchar2 default null
307 ,p_currency_code in varchar2 default null
308 ,p_standard_price in number default null
309 ,p_attribute_category in varchar2 default null
310 ,p_attribute1 in varchar2 default null
311 ,p_attribute2 in varchar2 default null
312 ,p_attribute3 in varchar2 default null
313 ,p_attribute4 in varchar2 default null
314 ,p_attribute5 in varchar2 default null
315 ,p_attribute6 in varchar2 default null
316 ,p_attribute7 in varchar2 default null
317 ,p_attribute8 in varchar2 default null
318 ,p_attribute9 in varchar2 default null
319 ,p_attribute10 in varchar2 default null
320 ,p_attribute11 in varchar2 default null
321 ,p_attribute12 in varchar2 default null
322 ,p_attribute13 in varchar2 default null
323 ,p_attribute14 in varchar2 default null
324 ,p_attribute15 in varchar2 default null
325 ,p_attribute16 in varchar2 default null
326 ,p_attribute17 in varchar2 default null
327 ,p_attribute18 in varchar2 default null
328 ,p_attribute19 in varchar2 default null
329 ,p_attribute20 in varchar2 default null
330 ,p_offering_id in number default null
331 ,p_object_version_number in number
332 ,p_return_status out nocopy varchar2
333 ,p_data_source in varchar2 default null
334 ,p_vendor_id in number default null
335 ,p_description in varchar2 default null
336 ,p_competency_update_level in varchar2 default null
337 ,p_language_code in varchar2 default null
338 ,P_CRT_UPD in varchar2 default null
339 ) is
340 -- =============================================================================
341 -- Variables for API Boolean parameters
342 -- =============================================================================
343 l_validate boolean;
344
345 -- =============================================================================
346 -- Other variables
347 -- =============================================================================
348
349 l_activity_version_id number;
350 l_category_usage_id number;
351 l_player_toolbar_bitset number;
352 l_offering_id number;
353 l_player_exit_flag varchar2(72);
354 l_player_next_flag varchar2(72);
355 l_player_previous_flag varchar2(72);
356 l_player_outline_flag varchar2(72);
357 l_error_msg varchar2(4000);
358 p_organization_id number;
359 p_event_type varchar2(72);
360 p_title varchar2(72);
361 p_course_end_date date;
362 p_course_start_date date;
363 p_enrolment_end_date date;
364 p_enrolment_start_date date;
365 p_event_status varchar2(72);
366 p_book_independent_flag varchar2(72);
367 p_public_event_flag varchar2(72);
368 p_secure_event_flag varchar2(72);
369 p_timezone varchar2(72);
370 l_title varchar2(72);
371 l_event_id number;
372 l_create_flag number(3) := 1;
373 l_off_id number;
374 l_obj_ver_num number;
375 l_object_version_number number;
376 e_upl_not_allowed exception; -- when mode is 'View Only'
377 e_crt_not_allowed exception; -- when mode is 'Update Only'
378 g_upl_err_msg varchar2(100) := 'Upload NOT allowed.';
379 g_crt_err_msg varchar2(100) := 'Creating NOT allowed.';
380 l_offering_rec ota_offerings_vl%rowtype;
381 l_interface_code varchar2(40);
382 l_crt_upd varchar2(1);
383 l_crt_upd_len number;
384
385 -- =============================================================================
386 -- Default_Record_Values:
387 -- =============================================================================
388 l_proc varchar2(72) := g_package||'InsUpd_Offering';
389
390 begin
391 --hr_utility.trace_on(null, 'Offer_Trace');
392 hr_utility.set_location('Entering: '|| l_proc, 11);
393 --
394 -- Issue a savepoint
395 --
396 savepoint InsUpd_Offering;
397
398 --
399 -- Convert constant values to their corresponding boolean value
400 --
401 l_validate := hr_api.constant_to_boolean (p_constant_value => p_validate);
402
403 -- $ Finding the value for the l_player_toolbar_bitset based on the input
404 -- $ Flag values
405 l_player_exit_flag :=1;
406 l_player_previous_flag :=2;
407 l_player_next_flag :=4;
408 l_player_outline_flag :=1024;
409 l_player_toolbar_bitset :=0;
410
411 l_offering_id := p_offering_id;
412
413 hr_utility.set_location('The offering id is : '||l_offering_id, 90);
414
415 if l_offering_id is not null then
416 l_create_flag := 2; --Update Offering
417 else
418 l_create_flag := 1; --create Offering
419 end if;
420
421 l_crt_upd_len := LENGTH(p_crt_upd);
422 l_crt_upd := SUBSTR(p_crt_upd, 1, 1);
423 IF l_crt_upd_len > 1 THEN
424 l_interface_code := SUBSTR(p_crt_upd, 3);
425 ELSE
426 l_interface_code := null;
427 END IF;
428
429
430 if (l_crt_upd = 'D') then
431 raise e_upl_not_allowed; -- View only flag is enabled but Trying to Upload
432 end if;
433 if (l_crt_upd = 'U' and l_create_flag = 1) then
434 raise e_crt_not_allowed; -- Update only flag is enabled but Trying to Create
435 end if;
436
437 if p_player_exit_flag ='Y' then
438 l_player_toolbar_bitset := l_player_toolbar_bitset + l_player_exit_flag;
439 end if;
440
441 if p_player_previous_flag ='Y' then
442 l_player_toolbar_bitset := l_player_toolbar_bitset + l_player_previous_flag;
443 end if;
444
445 if p_player_next_flag ='Y' then
446 l_player_toolbar_bitset := l_player_toolbar_bitset + l_player_next_flag;
447 end if;
448
449 if p_player_outline_flag ='Y' then
450 l_player_toolbar_bitset := l_player_toolbar_bitset + l_player_outline_flag;
451 end if;
452
453 --
454 -- Call API
455 --
456 if l_create_flag = 1 then
457 ota_offering_api.create_offering
458 (p_validate => l_validate
459 ,p_effective_date => p_effective_date
460 ,p_business_group_id => p_business_group_id
461 ,p_name => p_name
462 ,p_start_date => p_start_date
463 ,p_activity_version_id => p_activity_version_id
464 ,p_end_date => p_end_date
465 ,p_owner_id => p_owner_id
466 ,p_delivery_mode_id => p_delivery_mode_id
467 ,p_language_id => p_language_id
468 ,p_duration => p_duration
469 ,p_duration_units => p_duration_units
470 ,p_learning_object_id => p_learning_object_id
471 ,p_player_toolbar_flag => p_player_toolbar_flag
472 ,p_player_toolbar_bitset => l_player_toolbar_bitset
473 ,p_player_new_window_flag => p_player_new_window_flag
474 ,p_maximum_attendees => p_maximum_attendees
475 ,p_maximum_internal_attendees => p_maximum_internal_attendees
476 ,p_minimum_attendees => p_minimum_attendees
477 ,p_actual_cost => p_actual_cost
478 ,p_budget_cost => p_budget_cost
479 ,p_budget_currency_code => p_budget_currency_code
480 ,p_price_basis => p_price_basis
481 ,p_currency_code => p_currency_code
482 ,p_standard_price => p_standard_price
483 ,p_attribute_category => p_attribute_category
484 ,p_attribute1 => p_attribute1
485 ,p_attribute2 => p_attribute2
486 ,p_attribute3 => p_attribute3
487 ,p_attribute4 => p_attribute4
488 ,p_attribute5 => p_attribute5
489 ,p_attribute6 => p_attribute6
490 ,p_attribute7 => p_attribute7
491 ,p_attribute8 => p_attribute8
492 ,p_attribute9 => p_attribute9
493 ,p_attribute10 => p_attribute10
494 ,p_attribute11 => p_attribute11
495 ,p_attribute12 => p_attribute12
496 ,p_attribute13 => p_attribute13
497 ,p_attribute14 => p_attribute14
498 ,p_attribute15 => p_attribute15
499 ,p_attribute16 => p_attribute16
500 ,p_attribute17 => p_attribute17
501 ,p_attribute18 => p_attribute18
502 ,p_attribute19 => p_attribute19
503 ,p_attribute20 => p_attribute20
504 ,p_offering_id => l_off_id
505 ,p_object_version_number => l_obj_ver_num
506 ,p_data_source => p_data_source
507 ,p_vendor_id => p_vendor_id
508 ,p_description => p_description
509 ,p_competency_update_level => p_competency_update_level
510 ,p_language_code => p_language_code
511 );
512
513 -- $ Creating the Default Class for SELFPACED Offering (407)
514 -- $ Setting the default values for the Default Class
515
516 p_organization_id := p_business_group_id;
517 p_event_type := 'SELFPACED';
518 p_title := p_name;
519 p_course_end_date := p_end_date;
520 p_course_start_date := p_start_date;
521 p_enrolment_end_date := p_end_date;
522 p_enrolment_start_date := p_start_date;
523 p_event_status := 'N';
524 p_book_independent_flag := 'N';
525 p_public_event_flag := 'N';
526 p_secure_event_flag :='N';
527 p_timezone:= 'America/Chicago';
528
529 if p_delivery_mode_id = 407 then
530
531 ota_event_api.create_class
532 (p_effective_date => p_effective_date
533 ,p_event_id => l_event_id
534 ,p_vendor_id => p_vendor_id
535 ,p_activity_version_id => p_activity_version_id
536 ,p_business_group_id => p_business_group_id
537 ,p_organization_id => p_organization_id
538 ,p_event_type => p_event_type
539 ,p_object_version_number => l_obj_ver_num
540 ,p_title => p_title
541 ,p_budget_cost => p_budget_cost
542 ,p_actual_cost => p_actual_cost
543 ,p_budget_currency_code => p_budget_currency_code
544 -- ,p_centre => p_centre
545 -- ,p_comments => p_comments
546 ,p_course_end_date => p_course_end_date
547 -- ,p_course_end_time => p_course_end_time
548 ,p_course_start_date => p_course_start_date
549 -- ,p_course_start_time => p_course_start_time
550 ,p_duration => p_duration
551 ,p_duration_units => p_duration_units
552 ,p_enrolment_end_date => p_enrolment_end_date
553 ,p_enrolment_start_date => p_enrolment_start_date
554 ,p_language_id => p_language_id
555 -- ,p_user_status => p_user_status
556 -- ,p _development_event_type => p_development_event_type
557 ,p_event_status => p_event_status
558 ,p_price_basis => p_price_basis
559 ,p_currency_code => p_currency_code
560 ,p_maximum_attendees => p_maximum_attendees
561 ,p_maximum_internal_attendees => p_maximum_internal_attendees
562 ,p_minimum_attendees => p_minimum_attendees
563 ,p_standard_price => p_standard_price
564 -- ,p_category_code => p_category_code
565 -- ,p_parent_event_id => p_parent_event_id
566 ,p_book_independent_flag => p_book_independent_flag
567 ,p_public_event_flag => p_public_event_flag
568 ,p_secure_event_flag => p_secure_event_flag
569 -- ,p_project_id => p_project_id
570 ,p_owner_id => p_owner_id
571 -- ,p_line_id => p_line_id
572 -- ,p_org_id => p_org_id
573 -- ,p_training_center_id => p_training_center_id
574 -- ,p_location_id => p_location_id
575 -- ,p_offering_id => l_offering_id
576 ,p_timezone => p_timezone
577 ,p_parent_offering_id => l_off_id
578 ,p_validate => l_validate
579 ,p_data_source => p_data_source
580 );
581
582
583 l_title := p_name || to_char(l_event_id);
584 update ota_events set title = l_title where event_id = l_event_id;
585 update ota_events_tl set title = l_title where event_id = l_event_id;
586
587 end if;
588
589 end if;
590
591 if l_create_flag = 2 then
592
593 g_interface_code := nvl(l_interface_code,'PQP_OLM_OFFERING_INTF');
594 hr_utility.set_location('g_interface_code'||g_interface_code, 95);
595
596 g_offering_rec.business_group_id := p_business_group_id ;
597 g_offering_rec.name := p_name ;
598 g_offering_rec.start_date := p_start_date ;
599 g_offering_rec.activity_version_id := p_activity_version_id ;
600 g_offering_rec.end_date := p_end_date ;
601 g_offering_rec.owner_id := p_owner_id ;
602 g_offering_rec.delivery_mode_id := p_delivery_mode_id ;
603 g_offering_rec.language_id := p_language_id ;
604 g_offering_rec.duration := p_duration ;
605 g_offering_rec.duration_units := p_duration_units ;
606 g_offering_rec.learning_object_id := p_learning_object_id ;
607 g_offering_rec.player_toolbar_flag := p_player_toolbar_flag ;
608 g_offering_rec.player_toolbar_bitset := l_player_toolbar_bitset ;
609 g_offering_rec.player_new_window_flag := p_player_new_window_flag ;
610 g_offering_rec.maximum_attendees := p_maximum_attendees ;
611 g_offering_rec.maximum_internal_attendees := p_maximum_internal_attendees ;
612 g_offering_rec.minimum_attendees := p_minimum_attendees ;
613 g_offering_rec.actual_cost := p_actual_cost ;
614 g_offering_rec.budget_cost := p_budget_cost ;
615 g_offering_rec.budget_currency_code := p_budget_currency_code ;
616 g_offering_rec.price_basis := p_price_basis ;
617 g_offering_rec.currency_code := p_currency_code ;
618 g_offering_rec.standard_price := p_standard_price ;
619 g_offering_rec.attribute_category := p_attribute_category ;
620 g_offering_rec.attribute1 := p_attribute1 ;
621 g_offering_rec.attribute2 := p_attribute2 ;
622 g_offering_rec.attribute3 := p_attribute3 ;
623 g_offering_rec.attribute4 := p_attribute4 ;
624 g_offering_rec.attribute5 := p_attribute5 ;
625 g_offering_rec.attribute6 := p_attribute6 ;
626 g_offering_rec.attribute7 := p_attribute7 ;
627 g_offering_rec.attribute8 := p_attribute8 ;
628 g_offering_rec.attribute9 := p_attribute9 ;
629 g_offering_rec.attribute10 := p_attribute10 ;
630 g_offering_rec.attribute11 := p_attribute11 ;
631 g_offering_rec.attribute12 := p_attribute12 ;
632 g_offering_rec.attribute13 := p_attribute13 ;
633 g_offering_rec.attribute14 := p_attribute14 ;
634 g_offering_rec.attribute15 := p_attribute15 ;
635 g_offering_rec.attribute16 := p_attribute16 ;
636 g_offering_rec.attribute17 := p_attribute17 ;
637 g_offering_rec.attribute18 := p_attribute18 ;
638 g_offering_rec.attribute19 := p_attribute19 ;
639 g_offering_rec.attribute20 := p_attribute20 ;
640 g_offering_rec.data_source := p_data_source ;
641 g_offering_rec.vendor_id := p_vendor_id ;
642 g_offering_rec.description := p_description ;
643 g_offering_rec.competency_update_level := p_competency_update_level ;
644 g_offering_rec.language_code := p_language_code ;
645
646 select object_version_number into l_object_version_number
647 from ota_offerings where
648 offering_id = l_offering_id;
649
650 l_offering_rec := Get_Record_Values(g_interface_code);
651 ota_offering_api.update_offering
652 (p_validate => l_validate
653 ,p_effective_date => p_effective_date
654 ,p_business_group_id => l_offering_rec.business_group_id
655 ,p_name => l_offering_rec.name
656 ,p_start_date => l_offering_rec.start_date
657 ,p_activity_version_id => l_offering_rec.activity_version_id
658 ,p_end_date => l_offering_rec.end_date
659 ,p_owner_id => l_offering_rec.owner_id
660 ,p_delivery_mode_id => l_offering_rec.delivery_mode_id
661 ,p_language_id => l_offering_rec.language_id
662 ,p_duration => l_offering_rec.duration
663 ,p_duration_units => l_offering_rec.duration_units
664 ,p_learning_object_id => l_offering_rec.learning_object_id
665 ,p_player_toolbar_flag => g_player_toolbar_flag
666 ,p_player_toolbar_bitset => l_offering_rec.player_toolbar_bitset
667 ,p_player_new_window_flag => g_player_new_window_flag
668 ,p_maximum_attendees => l_offering_rec.maximum_attendees
669 ,p_maximum_internal_attendees => l_offering_rec.maximum_internal_attendees
670 ,p_minimum_attendees => l_offering_rec.minimum_attendees
671 ,p_actual_cost => l_offering_rec.actual_cost
672 ,p_budget_cost => l_offering_rec.budget_cost
673 ,p_budget_currency_code => l_offering_rec.budget_currency_code
674 ,p_price_basis => l_offering_rec.price_basis
675 ,p_currency_code => l_offering_rec.currency_code
676 ,p_standard_price => l_offering_rec.standard_price
677 ,p_attribute_category => l_offering_rec.attribute_category
678 ,p_attribute1 => l_offering_rec.attribute1
679 ,p_attribute2 => l_offering_rec.attribute2
680 ,p_attribute3 => l_offering_rec.attribute3
681 ,p_attribute4 => l_offering_rec.attribute4
682 ,p_attribute5 => l_offering_rec.attribute5
683 ,p_attribute6 => l_offering_rec.attribute6
684 ,p_attribute7 => l_offering_rec.attribute7
685 ,p_attribute8 => l_offering_rec.attribute8
686 ,p_attribute9 => l_offering_rec.attribute9
687 ,p_attribute10 => l_offering_rec.attribute10
688 ,p_attribute11 => l_offering_rec.attribute11
689 ,p_attribute12 => l_offering_rec.attribute12
690 ,p_attribute13 => l_offering_rec.attribute13
691 ,p_attribute14 => l_offering_rec.attribute14
692 ,p_attribute15 => l_offering_rec.attribute15
693 ,p_attribute16 => l_offering_rec.attribute16
694 ,p_attribute17 => l_offering_rec.attribute17
695 ,p_attribute18 => l_offering_rec.attribute18
696 ,p_attribute19 => l_offering_rec.attribute19
697 ,p_attribute20 => l_offering_rec.attribute20
698 ,p_offering_id => l_offering_id
699 ,p_object_version_number => l_object_version_number
700 ,p_data_source => l_offering_rec.data_source
701 ,p_vendor_id => l_offering_rec.vendor_id
702 ,p_description => l_offering_rec.description
703 ,p_competency_update_level => l_offering_rec.competency_update_level
704 ,p_language_code => l_offering_rec.language_code
705 );
706 end if;
707
708 hr_utility.set_location(' Leaving:' || l_proc,30);
709 Exception
710 when e_upl_not_allowed then
711 hr_utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
712 hr_utility.set_message_token('GENERIC_TOKEN',g_upl_err_msg);
713 hr_utility.set_location('Leaving: ' || l_proc, 90);
714 hr_utility.raise_error;
715 when e_crt_not_allowed then
716 hr_utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
717 hr_utility.set_message_token('GENERIC_TOKEN',g_crt_err_msg);
718 hr_utility.set_location('Leaving: ' || l_proc, 100);
719 hr_utility.raise_error;
720 when others then
721
722 --l_error_msg := Substr(SQLERRM,1,2000);
723 hr_utility.set_location('SQLCODE :' || SQLCODE,90);
724 hr_utility.set_location('SQLERRM :' || SQLERRM,90);
725 --hr_utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
726 --hr_utility.set_message_token('GENERIC_TOKEN',substr(l_error_msg,1,500) );
727 hr_utility.set_location(' Leaving:' || l_proc,50);
728 hr_utility.raise_error;
729
730
731 end InsUpd_Offering;
732 end pqp_riw_offering_wrapper;