[Home] [Help]
PACKAGE BODY: APPS.OTA_FR_2483_DATA
Source
1 package body ota_fr_2483_data as
2 /* $Header: otfr248d.pkb 120.1.12010000.3 2008/10/14 06:49:55 parusia ship $ */
3 --
4 g_package varchar2(30) := 'ota_fr_2483_data';
5 /* ---------------------------------------------------------------------------
6 Description
7 This inserts measurement type data into the business group, that
8 is needed for the 2483 report. Where the data already exists for
9 business group, it is updated.
10 -------------------------------------------------------------------------*/
11 --
12 procedure load_bg_measure_item(p_business_group_id number
13 ,p_tp_measurement_code varchar2
14 ,p_unit varchar2
15 ,p_cost_level varchar2
16 ,p_budget_level varchar2 default 'PLAN') is
17 /* --------------------------------------------------------------------------
18 Description
19 This procedure inserts or updates 1 measure into the table
20 for the OTA 2483 Report
21 ------------------------------------------------------------------------- */
22 --
23 l_mt_id number;
24 l_ovn number;
25 l_exists number := 1;
26 l_proc varchar2(72) := g_package||'.load_bg_measure_item';
27 --
28 cursor csr_existing_measure_code (l_bg_id number
29 ,l_measure_code varchar2) is
30 select tp_measurement_type_id
31 ,object_version_number
32 from ota_tp_measurement_types
33 where business_group_id = l_bg_id
34 and tp_measurement_code = l_measure_code;
35 --
36 begin
37 --
38 hr_utility.set_location('Entering:'||l_proc, 5);
39 hr_utility.trace(p_tp_measurement_code);
40 --
41 open csr_existing_measure_code (p_business_group_id
42 ,p_tp_measurement_code);
43 fetch csr_existing_measure_code into l_mt_id
44 ,l_ovn;
45 if csr_existing_measure_code%NOTFOUND then
46 l_exists := 0;
47 end if;
48
49 close csr_existing_measure_code;
50
51 if l_exists = 0 then
52 hr_utility.set_location(l_proc,10);
53 ota_tmt_api.create_measure
54 (p_effective_date => sysdate
55 ,p_business_group_id => p_business_group_id
56 ,p_tp_measurement_code => p_tp_measurement_code
57 ,p_unit => p_unit
58 ,p_budget_level => p_budget_level -- modified for 5239200
59 ,p_cost_level => p_cost_level
60 ,p_many_budget_values_flag => 'N'
61 ,p_tp_measurement_type_id => l_mt_id
62 ,p_object_version_number => l_ovn);
63 else
64 /* user may change data, as they own it.
65 thus we shall not subsequently update it */
66 hr_utility.set_location(l_proc,20);
67 --ota_tmt_api.update_measure
68 --(p_effective_date => sysdate
69 --,p_tp_measurement_type_id => l_mt_id
70 --,p_object_version_number => l_ovn
71 --,p_unit => p_unit
72 --,p_cost_level => p_cost_level);
73 end if;
74 hr_utility.set_location('Leaving:'||l_proc, 5);
75 end load_bg_measure_item;
76
77 procedure load_bg_measurement_types(p_business_group_id number) is
78 /* -----------------------------------------------------------------
79 Description
80 This procedure loads each measurement type in turn
81 ----------------------------------------------------------------- */
82 l_proc varchar2(72) := g_package||'.load_bg_measurement_types';
83 Begin
84 hr_utility.set_location(l_proc,10);
85 load_bg_measure_item(
86 p_business_group_id, 'FR_DEDUCT_EXT_TRN_PLAN', 'M','PLAN');
87 load_bg_measure_item(
88 p_business_group_id, 'FR_DEDUCT_EXT_TRN_PLAN_SA', 'M','PLAN');
89 load_bg_measure_item(
90 p_business_group_id, 'FR_DEDUCT_EXT_TRN_PLAN_VAE', 'M','PLAN');
91 load_bg_measure_item(
92 p_business_group_id, 'FR_OTHER_PLAN_DEDUCT_COSTS', 'M','PLAN');
93 load_bg_measure_item(
94 p_business_group_id, 'FR_DEDUCT_TRAINER_SALARY', 'M','EVENT');
95 load_bg_measure_item(
96 p_business_group_id, 'FR_DEDUCT_ADMIN_SALARY', 'M','EVENT');
97 load_bg_measure_item(
98 p_business_group_id, 'FR_DEDUCT_RUNNING_COSTS', 'M','EVENT');
99 load_bg_measure_item(
100 p_business_group_id, 'FR_DEDUCT_TRAINER_TRANSPRT', 'M','EVENT');
101 load_bg_measure_item(
102 p_business_group_id, 'FR_DEDUCT_TRAINER_ACCOM', 'M','EVENT');
103 load_bg_measure_item(
104 p_business_group_id, 'FR_DEDUCT_EXT_TRN_CLASS', 'M','EVENT');
105 load_bg_measure_item(
106 p_business_group_id, 'FR_OTHER_CLASS_DEDUCT_COST', 'M','EVENT');
107 load_bg_measure_item(
108 p_business_group_id, 'FR_ACTUAL_HOURS', 'N','DELEGATE');
109 load_bg_measure_item(
110 p_business_group_id, 'FR_SKILLS_ASSESSMENT' , 'M','DELEGATE');
111 load_bg_measure_item(
112 p_business_group_id, 'FR_VAE', 'M','DELEGATE');
113 load_bg_measure_item(
114 p_business_group_id, 'FR_DEDUCT_LEARNER_SALARY' , 'M','DELEGATE');
115 load_bg_measure_item(
116 p_business_group_id, 'FR_DEDUCT_TRN_ALLOWANCE' , 'M','DELEGATE');
117 load_bg_measure_item(
118 p_business_group_id, 'FR_OTHER_LEARN_DEDUCT_INT' , 'M','DELEGATE');
119 load_bg_measure_item(
120 p_business_group_id, 'FR_OTHER_LEARN_DEDUCT_EXT', 'M','DELEGATE');
121 -- added for 5230200 (french training plan report)
122 load_bg_measure_item(
123 p_business_group_id, 'FR_DURATION_HOURS', 'N', 'NONE', 'ACTIVITY');
124 load_bg_measure_item(
125 p_business_group_id, 'FR_NUMBER_EVENTS', 'I', 'NONE', 'ACTIVITY');
126 load_bg_measure_item(
127 p_business_group_id, 'FR_DELEGATES_PER_CATEGORY', 'I', 'NONE', 'ACTIVITY');
128 --
129 hr_utility.set_location(l_proc,100);
130 End Load_BG_Measurement_types;
131 --
132 END ota_fr_2483_data;