1 PACKAGE BODY hri_oltp_disc_training AS
2 /* $Header: hriodtrn.pkb 120.0 2005/05/29 07:29:32 appldev noship $ */
3
4 ---------------------------
5 -- Package global variables
6 ---------------------------
7 g_bg_currency_code per_business_groups.currency_code%type;
8 g_business_group_id per_business_groups.business_group_id%type;
9 g_precision fnd_currencies.precision%type;
10 g_rate_type varchar2(30);
11 g_training_formula_id ff_formulas_f.formula_id%type;
12
13
14 /******************************************************************************/
15 /* Procedure to set package global variables for Training Analysis */
16 /******************************************************************************/
17 PROCEDURE set_training_globals(p_event_id IN NUMBER) IS
18
19 CURSOR bg_csr is
20 SELECT business_group_id
21 FROM ota_events
22 WHERE event_id = p_event_id;
23
24 CURSOR bg_currency_csr is
25 SELECT bg.currency_code, NVL(c.precision,2)
26 FROM fnd_currencies c
27 ,per_business_groups bg
28 WHERE bg.currency_code = c.currency_code
29 AND bg.business_group_id = g_business_group_id;
30
31 BEGIN
32
33 IF p_event_id IS NOT NULL THEN
34
35 -- Get Business Group Id
36 OPEN bg_csr;
37 FETCH bg_csr INTO g_business_group_id;
38 CLOSE bg_csr;
39
40 -- Get currency of business group
41 OPEN bg_currency_csr;
42 FETCH bg_currency_csr INTO g_bg_currency_code, g_precision;
43 CLOSE bg_currency_csr;
44
45 -- Determine Rate Type for BIS
46 g_rate_type := hr_currency_pkg.get_rate_type(
47 p_business_group_id => g_business_group_id
48 ,p_conversion_date => sysdate
49 ,p_processing_type => 'I');
50
51 -- Determine Formula Id of the Training Convert Duration FastFormula
52 g_training_formula_id := hr_disc_calculations.get_formula_id(
53 p_business_group_id => g_business_group_id
54 ,p_formula_name => 'BIS_TRAINING_CONVERT_DURATION');
55
56 END IF;
57
58 END set_training_globals;
59
60
61 /******************************************************************************/
62 /* Private function to pro-rate an amount held against a Programme training */
63 /* event across the members of the Programme */
64 /******************************************************************************/
65 FUNCTION pro_rata_amount(p_event_id IN NUMBER
66 ,p_program_event_id IN NUMBER
67 ,p_programme_amount IN NUMBER
68 ,p_currency_code IN VARCHAR2)
69 RETURN NUMBER IS
70
71 CURSOR program_member_csr( p_program_event_id NUMBER ) is
72 SELECT
73 evt.event_id
74 ,evt.duration
75 ,evt.duration_units
76 ,evt.title
77 ,tav.version_name
78 FROM
79 ota_activity_versions tav
80 ,ota_events evt
81 ,ota_program_memberships pm
82 WHERE evt.activity_version_id = tav.activity_version_id
83 AND evt.event_id = pm.event_id
84 AND pm.program_event_id = p_program_event_id;
85
86 program_member_rec program_member_csr%rowtype;
87
88 l_activity_version_name ota_activity_versions.version_name%type;
89 l_duration ota_events.duration%type := 0;
90 l_event_duration ota_events.duration%type := 0;
91 l_event_name ota_events.title%type;
92 l_pro_rata_amount number := 0;
93 l_pro_rata_total number := 0;
94 l_total_duration number := 0;
95
96 BEGIN
97
98 ------------------------------------
99 -- Have all parameters been passed ?
100 ------------------------------------
101 IF (p_event_id IS NULL) OR (p_program_event_id IS NULL) OR
102 (p_programme_amount IS NULL) OR (p_currency_code IS NULL) THEN
103 RETURN(0);
104 END IF;
105
106 -------------------------------------------------------------
107 -- Loop round all the events which are members of the program
108 -------------------------------------------------------------
109 FOR program_member_rec IN program_member_csr( p_program_event_id ) LOOP
110
111 IF program_member_rec.duration_units = 'H' THEN
112 l_duration := program_member_rec.duration;
113 ELSE
114
115 ----------------------------------
116 -- Convert event duration to hours
117 ----------------------------------
118 l_duration := hr_disc_calculations.training_convert_duration(
119 p_formula_id => g_training_formula_id
120 ,p_FROM_duration => program_member_rec.duration
121 ,p_FROM_duration_units => program_member_rec.duration_units
122 ,p_to_duration_units => 'H'
123 ,p_activity_version_name => program_member_rec.version_name
124 ,p_event_name => program_member_rec.title
125 ,p_session_date => sysdate);
126
127 IF program_member_rec.event_id = p_event_id then
128 l_event_duration := l_duration;
129 END IF;
130
131 END IF;
132
133 l_total_duration := l_total_duration + nvl(l_duration,0);
134
135 END loop;
136
137 -----------------------------------
138 -- Perform the pro-rata calculation
139 -----------------------------------
140 l_pro_rata_amount := l_event_duration * p_programme_amount / l_total_duration;
141
142 --------------------------------------------------------------------
143 -- Convert to currency of BG AND update total for all program events
144 --------------------------------------------------------------------
145 l_pro_rata_amount := hri_bpl_currency.convert_currency_amount(
146 p_from_currency => p_currency_code
147 ,p_to_currency => g_bg_currency_code
148 ,p_conversion_date => SYSDATE
149 ,p_amount => l_pro_rata_amount
150 ,p_rate_type => g_rate_type);
151
152 l_pro_rata_total := l_pro_rata_total + l_pro_rata_amount;
153
154 RETURN(l_pro_rata_total);
155
156 EXCEPTION
157 WHEN OTHERS THEN
158
159 RETURN(0);
160
161 END pro_rata_amount;
162
163
164 /******************************************************************************/
165 /* Public function to calculate the Budget Cost of a training event */
166 /******************************************************************************/
167 FUNCTION get_event_budget_cost(p_event_id IN NUMBER)
168 RETURN NUMBER IS
169
170 CURSOR budget_cost_csr(p_csr_event_id NUMBER) IS
171 SELECT
172 nvl(budget_cost,0) budget_cost
173 ,budget_currency_code
174 ,business_group_id
175 FROM ota_events
176 WHERE event_id = p_csr_event_id;
177
178 l_business_group_id ota_events.business_group_id%type;
179
180 CURSOR program_event_csr is
181 SELECT program_event_id
182 FROM ota_program_memberships
183 WHERE event_id = p_event_id;
184
185 program_event_rec program_event_csr%rowtype;
186 l_budget_cost ota_events.budget_cost%type := 0;
187 l_budget_currency_code ota_events.budget_currency_code%type;
188 l_converted_amount ota_events.budget_cost%type := 0;
189 l_pro_rata_amount NUMBER := 0;
190 l_total_budget_cost NUMBER := 0;
191 l_pro_rata_total NUMBER := 0;
192
193 BEGIN
194
195 IF p_event_id IS NULL THEN
196 RETURN(0);
197 END IF;
198
199 -- Get Budget Cost of the event
200 OPEN budget_cost_csr( p_event_id );
201 FETCH budget_cost_csr INTO
202 l_budget_cost, l_budget_currency_code, l_business_group_id;
203 CLOSE budget_cost_csr;
204
205 IF l_business_group_id IS NOT NULL THEN
206
207 -- Set package global variables
208 set_training_globals(p_event_id => p_event_id);
209
210 -- Convert Budget Cost to business group currency
211 l_converted_amount := hri_bpl_currency.convert_currency_amount(
212 p_from_currency => l_budget_currency_code
213 ,p_to_currency => g_bg_currency_code
214 ,p_conversion_date => SYSDATE
215 ,p_amount => l_budget_cost
216 ,p_rate_type => g_rate_type);
217
218 END IF;
219
220 -- Is the event a member of a programme(s) ?
221 FOR program_event_rec IN program_event_csr LOOP
222
223 -- Get Budget Cost of the programme event
224 OPEN budget_cost_csr( program_event_rec.program_event_id );
225 FETCH budget_cost_csr INTO
226 l_budget_cost, l_budget_currency_code, l_business_group_id;
227 CLOSE budget_cost_csr;
228
229 -- Pro-rate programme amount if necessary
230 IF l_budget_cost <> 0 THEN
231
232 l_pro_rata_amount := pro_rata_amount(
233 p_event_id => p_event_id
234 ,p_program_event_id => program_event_rec.program_event_id
235 ,p_programme_amount => l_budget_cost
236 ,p_currency_code => l_budget_currency_code);
237
238 END IF;
239
240 l_pro_rata_total := l_pro_rata_total + l_pro_rata_amount;
241
242 END LOOP;
243
244 l_total_budget_cost := l_converted_amount + l_pro_rata_total;
245
246 RETURN (ROUND(l_total_budget_cost, g_precision));
247
248 EXCEPTION
249 WHEN OTHERS THEN
250
251 RETURN(0);
252
253 END get_event_budget_cost;
254
255
256 /******************************************************************************/
257 /* Public function to calculate the Actual Cost of a training event */
258 /******************************************************************************/
259 FUNCTION get_event_actual_cost(p_event_id IN NUMBER)
260 RETURN NUMBER IS
261
262 -- This cursor won't work if the resource is priced in units other than Days
263 -- Need to fix this in a future release
264 CURSOR resource_bookings_csr(p_csr_event_id NUMBER) IS
265 SELECT
266 sr.currency_code
267 ,sr.cost_unit
268 ,nvl(sr.cost,0) * nvl(rb.quantity,0) *
269 nvl(trunc(rb.required_date_to - rb.required_date_FROM + 1),0) cost
270 FROM
271 ota_suppliable_resources sr
272 ,ota_resource_bookings rb
273 WHERE rb.supplied_resource_id = sr.supplied_resource_id
274 AND rb.event_id = p_csr_event_id
275 AND rb.status = 'C'; -- Confirmed bookings only
276
277 resource_bookings_rec resource_bookings_csr%rowtype;
278
279 CURSOR actual_cost_csr IS
280 SELECT
281 nvl(actual_cost,0)
282 ,budget_currency_code
283 ,business_group_id
284 FROM ota_events
285 WHERE event_id = p_event_id;
286
287 l_actual_cost NUMBER := 0;
288 l_actual_currency_code ota_events.budget_currency_code%type;
289 l_business_group_id ota_events.business_group_id%type;
290 l_event_cost NUMBER := 0;
291 l_resource_cost NUMBER := 0;
292
293 BEGIN
294
295 IF p_event_id IS NULL THEN
296 RETURN(0);
297 END IF;
298
299 -- Get Actual Cost of the event
300 OPEN actual_cost_csr;
301 FETCH actual_cost_csr INTO
302 l_actual_cost, l_actual_currency_code, l_business_group_id;
303 CLOSE actual_cost_csr;
304
305 -- Set package global variables
306 set_training_globals(p_event_id => p_event_id);
307
308 -- Calculate total cost of resource bookings against the event
309 FOR resource_bookings_rec IN resource_bookings_csr(p_event_id) LOOP
310
311 -- Convert to currency of BG
312 l_resource_cost := hri_bpl_currency.convert_currency_amount(
313 p_from_currency => resource_bookings_rec.currency_code
314 ,p_to_currency => g_bg_currency_code
315 ,p_conversion_date => SYSDATE
316 ,p_amount => resource_bookings_rec.cost
317 ,p_rate_type => g_rate_type);
318
319 l_event_cost := l_event_cost + l_resource_cost;
320
321 END loop;
322
323 -- Default to the actual cost on the event if necessary
324 IF l_event_cost = 0 THEN
325
326 l_event_cost := hri_bpl_currency.convert_currency_amount(
327 p_from_currency => l_actual_currency_code
328 ,p_to_currency => g_bg_currency_code
329 ,p_conversion_date => SYSDATE
330 ,p_amount => l_actual_cost
331 ,p_rate_type => g_rate_type);
332
333 END IF;
334
335 RETURN (ROUND(l_event_cost, g_precision));
336
337 EXCEPTION
338 WHEN OTHERS THEN
339
340 RETURN(0);
341
342 END get_event_actual_cost;
343
344
345 /******************************************************************************/
346 /* Private function to calculate the Internal Revenue generated by a training */
347 /* event */
348 /******************************************************************************/
349 FUNCTION get_internal_revenue(p_event_id IN NUMBER)
350 RETURN NUMBER IS
351
352 CURSOR event_csr is
353 SELECT
354 price_basis
355 ,nvl(stANDard_price,0)
356 ,currency_code
357 FROM ota_events
358 WHERE event_id = p_event_id;
359
360 CURSOR internal_bookings_csr is
361 SELECT NVL(SUM(NVL(tdb.number_of_places,1)),0)
362 FROM
363 ota_booking_status_types bst
364 ,ota_delegate_bookings tdb
365 WHERE tdb.booking_status_type_id = bst.booking_status_type_id
366 AND bst.type = 'A' -- Attended
367 AND tdb.organization_id IS NOT NULL
368 AND tdb.event_id = p_event_id;
369
370 l_currency_code ota_events.currency_code%type;
371 l_internal_revenue NUMBER := 0;
372 l_price_basis ota_events.price_basis%type;
373 l_standard_price ota_events.standard_price%type;
374 l_total_int_students NUMBER := 0;
375
376 BEGIN
377 -- Get event details
378 OPEN event_csr;
379 FETCH event_csr INTO
380 l_price_basis, l_standard_price, l_currency_code;
381 CLOSE event_csr;
382
383 -- Price Basis = 'Student' ?
384 IF (l_price_basis = 'S') AND (l_standard_price <> 0) then
385
386 OPEN internal_bookings_csr;
387 FETCH internal_bookings_csr INTO l_total_int_students;
388 CLOSE internal_bookings_csr;
389
390 -- Calculate internal revenue
391 l_internal_revenue := l_total_int_students * l_standard_price;
392
393 -- Convert to currency of business group
394 l_internal_revenue := hri_bpl_currency.convert_currency_amount(
395 p_from_currency => l_currency_code
396 ,p_to_currency => g_bg_currency_code
397 ,p_conversion_date => SYSDATE
398 ,p_amount => l_internal_revenue
399 ,p_rate_type => g_rate_type);
400
401 END IF;
402
403 RETURN(l_internal_revenue);
404
405 EXCEPTION
406 WHEN OTHERS THEN
407
408 RETURN(0);
409
410 END get_internal_revenue;
411
412
413 /******************************************************************************/
414 /* Private function to calculate the External Revenue generated by a training */
415 /* event */
416 /******************************************************************************/
417 FUNCTION get_external_revenue(p_event_id IN NUMBER)
418 RETURN NUMBER IS
419
420 CURSOR external_bookings_csr is
421 SELECT tdb.booking_id
422 FROM ota_booking_status_types bst
423 ,ota_delegate_bookings tdb
424 WHERE tdb.booking_status_type_id = bst.booking_status_type_id
425 AND bst.type = 'A' -- Attended
426 AND tdb.customer_id is not null
427 AND tdb.event_id = p_event_id;
428
429 external_bookings_rec external_bookings_csr%rowtype;
430
431 CURSOR finance_lines_csr( p_delegate_booking_id NUMBER ) is
432 SELECT
433 currency_code
434 ,SUM(NVL(money_amount,0)) amount
435 FROM ota_finance_lines
436 WHERE line_type = 'E' -- Enrollment
437 AND cancelled_flag = 'N'
438 AND booking_id = p_delegate_booking_id
439 GROUP BY currency_code;
440
441 finance_lines_rec finance_lines_csr%rowtype;
442
443 l_external_revenue NUMBER := 0;
444 l_line_amount NUMBER := 0;
445
446 BEGIN
447
448 FOR external_bookings_rec IN external_bookings_csr LOOP
449
450 FOR finance_lines_rec IN finance_lines_csr(external_bookings_rec.booking_id) LOOP
451
452 -- Convert to currency of business group
453 l_line_amount := hri_bpl_currency.convert_currency_amount(
454 p_FROM_currency => finance_lines_rec.currency_code
455 ,p_to_currency => g_bg_currency_code
456 ,p_conversion_date => SYSDATE
457 ,p_amount => finance_lines_rec.amount
458 ,p_rate_type => g_rate_type);
459
460 l_external_revenue := l_external_revenue + l_line_amount;
461
462 END LOOP;
463
464 END LOOP;
465
466 RETURN(l_external_revenue);
467
468 EXCEPTION
469 WHEN OTHERS THEN
470
471 RETURN(0);
472
473 END get_external_revenue;
474
475
476 /******************************************************************************/
477 /* cbridge, 13/09/20000, course ranking workbook functions */
478 /* Private function to calculate the Internal Revenue generated by a training */
479 /* event for a particular delegate booking where the delegate attended the */
480 /* event */
481 /******************************************************************************/
482 FUNCTION get_att_int_rev_booking(p_event_id IN NUMBER,
483 p_booking_id IN NUMBER)
484 RETURN NUMBER IS
485
486 CURSOR event_csr is
487 SELECT
488 price_basis
489 ,nvl(stANDard_price,0)
490 ,currency_code
491 FROM ota_events
492 WHERE event_id = p_event_id;
493
494 CURSOR internal_bookings_csr is
495 SELECT NVL(SUM(NVL(tdb.number_of_places,1)),0)
496 FROM ota_booking_status_types bst
497 ,ota_delegate_bookings tdb
498 WHERE tdb.booking_status_type_id = bst.booking_status_type_id
499 AND bst.type = 'A' -- AttENDed
500 AND tdb.organization_id IS NOT NULL
501 AND tdb.booking_id = p_booking_id
502 AND tdb.event_id = p_event_id;
503
504 l_currency_code ota_events.currency_code%type;
505 l_internal_revenue NUMBER := 0;
506 l_price_basis ota_events.price_basis%type;
507 l_standard_price ota_events.standard_price%type;
508 l_total_int_students NUMBER := 0;
509
510 BEGIN
511
512 ----------------------
513 -- Set package globals
514 ----------------------
515 set_training_globals(p_event_id => p_event_id);
516
517 -- Get event details
518 OPEN event_csr;
519 FETCH event_csr INTO
520 l_price_basis, l_stANDard_price, l_currency_code;
521 CLOSE event_csr;
522
523 -- Price Basis = 'Student' ?
524 IF (l_price_basis = 'S') AND (l_standard_price <> 0) THEN
525
526 OPEN internal_bookings_csr;
527 FETCH internal_bookings_csr INTO l_total_int_students;
528 CLOSE internal_bookings_csr;
529
530 -- Calculate internal revenue
531 l_internal_revenue := l_total_int_students * l_stANDard_price;
532
533 -- Convert to currency of business group
534 l_internal_revenue := hri_bpl_currency.convert_currency_amount(
535 p_from_currency => l_currency_code
536 ,p_to_currency => g_bg_currency_code
537 ,p_conversion_date => SYSDATE
538 ,p_amount => l_internal_revenue
539 ,p_rate_type => g_rate_type);
540
541 END IF;
542
543 RETURN(l_internal_revenue);
544
545 EXCEPTION
546 WHEN OTHERS THEN
547
548 RETURN(0);
549
550 END get_att_int_rev_booking;
551
552
553 /******************************************************************************/
554 /* Private function to calculate the External Revenue generated by a training */
555 /* event for a particular delegate booking where the delegate attended the */
556 /* event */
557 /******************************************************************************/
558 FUNCTION get_att_ext_rev_booking(p_event_id IN NUMBER,
559 p_booking_id IN NUMBER)
560 RETURN NUMBER IS
561
562 CURSOR external_bookings_csr IS
563 SELECT tdb.booking_id
564 FROM ota_booking_status_types bst
565 ,ota_delegate_bookings tdb
566 WHERE tdb.booking_status_type_id = bst.booking_status_type_id
567 AND bst.type = 'A' -- Attended
568 AND tdb.booking_id = p_booking_id
569 AND tdb.organization_id IS NULL -- bug fix 1432057
570 AND tdb.event_id = p_event_id;
571
572 external_bookings_rec external_bookings_csr%rowtype;
573
574 CURSOR finance_lines_csr(p_delegate_booking_id NUMBER) IS
575 SELECT
576 currency_code
577 ,sum(nvl(money_amount,0)) amount
578 FROM ota_finance_lines
579 WHERE line_type = 'E' -- Enrollment
580 AND cancelled_flag = 'N'
581 AND booking_id = p_delegate_booking_id
582 GROUP BY currency_code;
583
584 finance_lines_rec finance_lines_csr%rowtype;
585
586 l_external_revenue NUMBER := 0;
587 l_line_amount NUMBER := 0;
588
589 BEGIN
590
591 ----------------------
592 -- Set package globals
593 ----------------------
594 set_training_globals(p_event_id => p_event_id);
595
596 FOR external_bookings_rec IN external_bookings_csr LOOP
597
598 FOR finance_lines_rec IN finance_lines_csr(external_bookings_rec.booking_id) LOOP
599
600 -- Convert to currency of business group
601 l_line_amount := hri_bpl_currency.convert_currency_amount(
602 p_from_currency => finance_lines_rec.currency_code
603 ,p_to_currency => g_bg_currency_code
604 ,p_conversion_date => SYSDATE
605 ,p_amount => finance_lines_rec.amount
606 ,p_rate_type => g_rate_type);
607
608 l_external_revenue := l_external_revenue + l_line_amount;
609
610 END LOOP;
611
612 END LOOP;
613
614 RETURN(l_external_revenue);
615
616 EXCEPTION
617 WHEN OTHERS THEN
618
619 RETURN(0);
620
621 END get_att_ext_rev_booking;
622
623
624 /******************************************************************************/
625 /* Private function to calculate the Internal Revenue generated by a training */
626 /* event for a particular delegate booking where the delegate did not attend */
627 /* the event */
628 /******************************************************************************/
629 FUNCTION get_non_att_int_rev_booking(p_event_id IN NUMBER,
630 p_booking_id IN NUMBER)
631 RETURN NUMBER IS
632
633 CURSOR event_csr is
634 SELECT
635 price_basis
636 ,nvl(stANDard_price,0)
637 ,currency_code
638 FROM ota_events
639 WHERE event_id = p_event_id;
640
641 CURSOR internal_bookings_csr is
642 SELECT NVL(SUM(NVL(tdb.number_of_places,1)),0)
643 FROM ota_booking_status_types bst
644 ,ota_delegate_bookings tdb
645 WHERE tdb.booking_status_type_id = bst.booking_status_type_id
646 AND bst.type IN ('P','C') -- Placed, Cancelled
647 AND tdb.organization_id IS NOT NULL
648 AND tdb.booking_id = p_booking_id
649 AND tdb.event_id = p_event_id;
650
651 l_currency_code ota_events.currency_code%type;
652 l_internal_revenue NUMBER := 0;
653 l_price_basis ota_events.price_basis%type;
654 l_standard_price ota_events.standard_price%type;
655 l_total_int_students NUMBER := 0;
656
657 BEGIN
658
659 ----------------------
660 -- Set package globals
661 ----------------------
662 set_training_globals(p_event_id => p_event_id);
663
664 -- Get event details
665 OPEN event_csr;
666 FETCH event_csr INTO
667 l_price_basis, l_standard_price, l_currency_code;
668 CLOSE event_csr;
669
670 -- Price Basis = 'Student' ?
671 IF (l_price_basis = 'S') AND (l_standard_price <> 0) THEN
672
673 OPEN internal_bookings_csr;
674 FETCH internal_bookings_csr INTO l_total_int_students;
675 CLOSE internal_bookings_csr;
676
677 -- Calculate internal revenue
678 l_internal_revenue := l_total_int_students * l_stANDard_price;
679
680 -- Convert to currency of business group
681 l_internal_revenue := hri_bpl_currency.convert_currency_amount(
682 p_FROM_currency => l_currency_code
683 ,p_to_currency => g_bg_currency_code
684 ,p_conversion_date => SYSDATE
685 ,p_amount => l_internal_revenue
686 ,p_rate_type => g_rate_type);
687
688 END IF;
689
690 RETURN(l_internal_revenue);
691
692 EXCEPTION
693 WHEN OTHERS THEN
694
695 RETURN(0);
696
697 END get_non_att_int_rev_booking;
698
699
700 /******************************************************************************/
701 /* Private function to calculate the External Revenue generated by a training */
702 /* event for a particular delegate booking where the delegate did not attend */
703 /* the event */
704 /******************************************************************************/
705 FUNCTION get_non_att_ext_rev_booking(p_event_id IN NUMBER,
706 p_booking_id IN NUMBER)
707 RETURN NUMBER IS
708
709 CURSOR external_bookings_csr is
710 SELECT tdb.booking_id
711 FROM ota_booking_status_types bst
712 ,ota_delegate_bookings tdb
713 WHERE tdb.booking_status_type_id = bst.booking_status_type_id
714 AND bst.type IN ('P','C') -- Placed, Cancelled
715 AND tdb.booking_id = p_booking_id
716 AND tdb.organization_id IS NULL -- bug fix 1432057
717 AND tdb.event_id = p_event_id;
718
719 external_bookings_rec external_bookings_csr%rowtype;
720
721 CURSOR finance_lines_csr(p_delegate_booking_id NUMBER) IS
722 SELECT
723 currency_code
724 ,SUM(NVL(money_amount,0)) amount
725 FROM ota_finance_lines
726 WHERE line_type = 'E' -- Enrollment
727 AND cancelled_flag = 'N'
728 AND booking_id = p_delegate_booking_id
729 GROUP BY currency_code;
730
731 finance_lines_rec finance_lines_csr%rowtype;
732
733 l_external_revenue NUMBER := 0;
734 l_line_amount NUMBER := 0;
735
736 BEGIN
737
738 ----------------------
739 -- Set package globals
740 ----------------------
741 set_training_globals(p_event_id => p_event_id);
742
743 FOR external_bookings_rec IN external_bookings_csr LOOP
744
745 FOR finance_lines_rec IN finance_lines_csr(external_bookings_rec.booking_id) LOOP
746
747 -- Convert to currency of business group
748 l_line_amount := hri_bpl_currency.convert_currency_amount(
749 p_from_currency => finance_lines_rec.currency_code
750 ,p_to_currency => g_bg_currency_code
751 ,p_conversion_date => SYSDATE
752 ,p_amount => finance_lines_rec.amount
753 ,p_rate_type => g_rate_type);
754
755 l_external_revenue := l_external_revenue + l_line_amount;
756
757 END loop;
758
759 END loop;
760
761 RETURN(l_external_revenue);
762
763 EXCEPTION
764 WHEN OTHERS THEN
765
766 RETURN(0);
767
768 END get_non_att_ext_rev_booking;
769
770
771 /******************************************************************************/
772 /* Public function to calculate the Total Revenue generated by a training */
773 /* event */
774 /******************************************************************************/
775 FUNCTION get_event_revenue(p_event_id IN NUMBER)
776 RETURN NUMBER IS
777
778 CURSOR program_event_csr IS
779 SELECT program_event_id
780 FROM ota_program_memberships
781 WHERE event_id = p_event_id;
782
783 program_event_rec program_event_csr%rowtype;
784
785 l_external_revenue NUMBER := 0;
786 l_internal_revenue NUMBER := 0;
787 l_program_ext_revenue NUMBER := 0;
788 l_program_int_revenue NUMBER := 0;
789 l_program_prorata_revenue NUMBER := 0;
790 l_program_prorata_total NUMBER := 0;
791 l_program_revenue NUMBER := 0;
792 l_total_revenue NUMBER := 0;
793
794 BEGIN
795
796 IF p_event_id IS NULL THEN
797 RETURN(0);
798 END IF;
799
800 ----------------------
801 -- Set package globals
802 ----------------------
803 set_training_globals(p_event_id => p_event_id);
804
805 -------------------------------------
806 -- Get Internal Revenue for the event
807 -------------------------------------
808 l_internal_revenue := get_internal_revenue(p_event_id => p_event_id);
809
810 -------------------------------------
811 -- Get External Revenue for the event
812 -------------------------------------
813 l_external_revenue := get_external_revenue(p_event_id => p_event_id);
814
815 ----------------------------
816 -- Calculate program revenue
817 ----------------------------
818
819 -- Is the event a member of a programme ?
820 FOR program_event_rec IN program_event_csr LOOP
821
822 -- Determine internal revenue for the program event
823 l_program_int_revenue := get_internal_revenue
824 (p_event_id => program_event_rec.program_event_id);
825
826 -- Determine external revenue for the program event
827 l_program_ext_revenue := get_external_revenue
828 (p_event_id => program_event_rec.program_event_id);
829
830 -- Calculate total program revenue
831 l_program_revenue := l_program_int_revenue + l_program_ext_revenue;
832
833 -- Prorate the program revenue
834
835 l_program_prorata_revenue := pro_rata_amount(
836 p_event_id => p_event_id
837 ,p_program_event_id => program_event_rec.program_event_id
838 ,p_programme_amount => l_program_revenue
839 ,p_currency_code => g_bg_currency_code);
840
841 l_program_prorata_total := l_program_prorata_total + l_program_prorata_revenue;
842
843 END loop;
844
845 --------------------------
846 -- Calculate total revenue
847 --------------------------
848 l_total_revenue := l_internal_revenue + l_external_revenue + l_program_prorata_total;
849
850 RETURN (ROUND(l_total_revenue, g_precision) );
851
852 EXCEPTION
853 WHEN OTHERS THEN
854
855 RETURN(0);
856
857 END get_event_revenue;
858
859 /******************************************************************************/
860 /* Public function to convert Training Duration FROM one set of units to */
861 /* another */
862 /******************************************************************************/
863 FUNCTION convert_training_duration(p_formula_id IN NUMBER
864 ,p_from_duration IN NUMBER
865 ,p_from_duration_units IN VARCHAR2
866 ,p_to_duration_units IN VARCHAR2
867 ,p_activity_version_name IN VARCHAR2
868 ,p_event_name IN VARCHAR2
869 ,p_session_date IN DATE)
870 RETURN NUMBER IS
871
872 l_to_duration NUMBER := 0;
873
874 l_inputs FF_Exec.Inputs_T;
875 l_outputs FF_Exec.Outputs_T;
876
877 BEGIN
878
879 -- Check whether all mandatory parameters have been supplied
880 IF (p_formula_id IS NULL) OR (p_from_duration IS NULL) OR
881 (p_from_duration_units IS NULL) OR (p_to_duration_units IS NULL) OR
882 (p_event_name IS NULL) OR (p_session_date IS NULL) THEN
883 --
884 -- bug fix 1432188 remove check for (p_activity_version_name IS NULL)
885 --
886 RETURN(0);
887 END IF;
888
889 -- Initialise the Inputs and Outputs tables
890 FF_Exec.Init_Formula(
891 p_formula_id => p_formula_id
892 ,p_effective_date => sysdate
893 ,p_inputs => l_inputs
894 ,p_outputs => l_outputs);
895
896 IF (l_inputs.first IS NOT NULL) AND (l_inputs.last IS NOT NULL) THEN
897
898 -- Set up context values for the formula
899 FOR i IN l_inputs.first..l_inputs.last LOOP
900
901 IF l_inputs(i).name = 'FROM_DURATION' THEN
902 l_inputs(i).value := to_char(p_from_duration);
903
904 ELSIF l_inputs(i).name = 'FROM_DURATION_UNITS' THEN
905 l_inputs(i).value := p_FROM_duration_units;
906
907 ELSIF l_inputs(i).name = 'TO_DURATION_UNITS' THEN
908 l_inputs(i).value := p_to_duration_units;
909
910 ELSIF l_inputs(i).name = 'ACTIVITY_VERSION_NAME' THEN
911 -- bug fix 1432188, added NVL(p_activity_version_name,'X')
912 l_inputs(i).value := NVL(p_activity_version_name,'X');
913
914 ELSIF l_inputs(i).name = 'EVENT_NAME' THEN
915 l_inputs(i).value := p_event_name;
916
917 END IF;
918
919 END loop;
920
921 END IF;
922
923 -- Run the Fast Formula
924 FF_Exec.Run_Formula(
925 p_inputs => l_inputs
926 ,p_outputs => l_outputs);
927
928 l_to_duration := to_number(l_outputs(l_outputs.first).value);
929
930 RETURN(l_to_duration);
931
932 EXCEPTION
933 WHEN OTHERS THEN
934
935 RETURN(0);
936
937 END convert_training_duration;
938
939 END hri_oltp_disc_training;
[Home] [Help]