[Home] [Help]
PACKAGE BODY: APPS.BEN_PDW_MASS_UPD_RATES
Source
1 package body ben_pdw_mass_upd_rates as
2 /* $Header: bepdwmrt.pkb 120.4 2006/05/16 11:37:53 sparimi noship $ */
3
4 procedure get_dt_modes(p_effective_date in date,
5 p_effective_end_date in date,
6 p_effective_start_date in date,
7 p_dml_operation in varchar2,
8 p_datetrack_mode in out nocopy varchar2
9 -- p_update out nocopy boolean
10 ) is
11 l_update boolean := true ;
12 l_datetrack_mode varchar2(80) := p_datetrack_mode ;
13 begin
14 --
15 hr_utility.set_location('Intering get_dt_modes p_dt_mode '||l_datetrack_mode,10);
16 hr_utility.set_location('p_effective_start_date '||p_effective_start_date,10);
17 hr_utility.set_location('p_effective_end_date '||p_effective_end_date,10);
18 hr_utility.set_location('p_effective_date '||p_effective_date,10);
19 --
20 if p_effective_end_date <> hr_api.g_eot then
21 --
22 if p_dml_operation = 'INSERT' then
23 --
24 l_datetrack_mode := hr_api.g_update;
25 l_update := true;
26 --
27 elsif l_datetrack_mode in ('CORRECTION') then
28 --
29 l_datetrack_mode := hr_api.g_correction ;
30 l_update := true;
31 --
32 elsif l_datetrack_mode in ('UPDATE_OVERRIDE','UPDATE' ) then
33 --
34 if p_effective_date = p_effective_start_date then
35 l_datetrack_mode := hr_api.g_correction ;
36 l_update := true;
37 else
38 --
39 if l_datetrack_mode in ('UPDATE_OVERRIDE') then
40 --
41 l_datetrack_mode := hr_api.g_update_override ;
42 l_update := false ;
43 --
44 elsif l_datetrack_mode in ('UPDATE') then
45 --
46 l_datetrack_mode := hr_api.g_update;
47 --
48 end if;
49 --
50 end if;
51 --
52 elsif l_datetrack_mode in ('UPDATE_CHANGE_INSERT') then
53 --
54 if p_effective_date = p_effective_start_date then
55 l_datetrack_mode := hr_api.g_correction ;
56 l_update := true;
57 else
58 l_datetrack_mode := hr_api.g_update_change_insert ;
59 l_update := true;
60 end if;
61 --
62 else
63 --
64 l_datetrack_mode := hr_api.g_update;
65 l_update := false;
66 --
67 end if;
68 --
69 else
70 if p_dml_operation = 'INSERT' then
71 --
72 l_datetrack_mode := hr_api.g_update;
73 l_update := false;
74 --
75 elsif l_datetrack_mode in ('CORRECTION') then
76 --
77 l_datetrack_mode := hr_api.g_correction ;
78 l_update := false;
79 --
80 elsif l_datetrack_mode in ('UPDATE_OVERRIDE','UPDATE' ) then
81 --
82 if p_effective_date = p_effective_start_date then
83 l_datetrack_mode := hr_api.g_correction ;
84 l_update := true;
85 else
86 l_datetrack_mode := hr_api.g_update ;
87 l_update := false ;
88 end if;
89 --
90 elsif l_datetrack_mode in ('UPDATE_CHANGE_INSERT') then
91 --
92 if p_effective_date = p_effective_start_date then
93 l_datetrack_mode := hr_api.g_correction ;
94 l_update := false;
95 else
96 l_datetrack_mode := hr_api.g_update ;
97 l_update := false;
98 end if;
99 --
100 else
101 --
102 l_datetrack_mode := hr_api.g_update;
103 l_update := false;
104 --
105 end if;
106 --
107 end if ;
108 --
109 p_datetrack_mode := l_datetrack_mode ;
110 -- p_update := l_update ;
111 --
112 hr_utility.set_location('Leaving get_dt_modes p_dt_mode '||p_datetrack_mode,10);
113 --
114 end get_dt_modes ;
115
116 procedure UPLOAD_RATE(
117 P_RATE_ID in Number,
118 P_PL_TYP_ID in Number default hr_api.g_number,
119 P_PLAN_TYPE_NAME in varchar2 default hr_api.g_varchar2,
120 P_PL_ID in Number default hr_api.g_number,
121 P_PLAN_NAME in varchar2 default hr_api.g_varchar2,
122 P_OPT_ID in Number default hr_api.g_number,
123 P_OPTION_NAME in varchar2 default hr_api.g_varchar2,
124 P_ABR_LEVEL in varchar2 default hr_api.g_varchar2,
125 P_RT_MLT_CD in varchar2 default hr_api.g_varchar2,
126 P_RATE_TYPE in varchar2,
127 P_RATE_NAME in varchar2 default hr_api.g_varchar2,
128 P_VARIABLE_RATE_NAME in varchar2 default hr_api.g_varchar2,
129 P_ACTY_TYP_CD in varchar2 default hr_api.g_varchar2,
130 P_OLD_VAL in number default hr_api.g_number,
131 P_NEW_VAL in number default hr_api.g_number,
132 P_RNDG_CD in varchar2 default hr_api.g_varchar2,
133 P_RT_TYP_CD in varchar2 default hr_api.g_varchar2,
134 P_BNFT_RT_TYP_CD in varchar2 default hr_api.g_varchar2,
135 P_COMP_LVL_FCTR_ID in number default hr_api.g_varchar2, --NOTE: this P_COMP_LVL_FCTR_ID is VARCHAR2 and NOT NUMBER as it suggests.
136 P_ELEMENT_TYPE_ID in number default hr_api.g_varchar2, --NOTE: this P_ELEMENT_TYPE_ID is VARCHAR2 and NOT NUMBER as it suggests.
137 P_INPUT_VALUE_ID in varchar2 default hr_api.g_varchar2, --NOTE: this P_INPUT_VALUE_ID is VARCHAR2 and NOT NUMBER as it suggests.
138 P_ELE_ENTRY_VAL_CD in varchar2 default hr_api.g_varchar2,
139 P_OBJECT_VERSION_NUMBER in number,
140 P_EFFECTIVE_START_DATE in date,
141 P_EFFECTIVE_END_DATE in date,
142 P_DATETRACK_MODE in varchar2,
143 P_EFFECTIVE_DATE in date
144 ) is
145 -- Reason of P_INPUT_VALUE_ID being varchar2 instead of number is that,
146 -- this attribute is implemented as a dependant lov field in WeADI.
147 -- For such fields, we cannot specify a different return mapping other than what user selects in the field (Input Value Name)
148 l_effective_start_date date;
149 l_effective_end_date date;
150 l_object_version_number number;
151 l_future_data_exists char := 'N';
152 l_input_value_id number;
153 l_datetrack_mode pqh_copy_entity_txns.datetrack_mode%TYPE;
154 BEGIN
155 l_object_version_number := p_object_version_number;
156 hr_utility.set_location('UPLOAD_RATE: Entering',10);
157 -- As of now, user can choose UPDATE or CORRECTION as the date track selection.
158 -- We are not allowing future data date track selections
159 IF (P_RATE_TYPE = hr_general.decode_lookup('BEN_MRT_RT_TYPE','STD_RT') or 'ABR' = P_RATE_TYPE) then
160 hr_utility.set_location('UPloading Standard Rate',15);
161 begin
162 select 'Y' into l_future_data_exists
163 from
164 BEN_ACTY_BASE_RT_F a
165 where
166 a.acty_base_rt_id = p_rate_id
167 and p_effective_date between a.effective_start_date and a.effective_end_date
168 and a.effective_end_date < to_date('4712/12/31','YYYY/MM/DD')
169 and exists
170 ( select 'Y' from BEN_ACTY_BASE_RT_F b
171 where b.acty_base_rt_id = a.acty_base_rt_id
172 and b.effective_start_date = a.effective_end_date + 1);
173 exception when no_data_found then
174 null;
175 end;
176
177 if(p_datetrack_mode <> 'CORRECTION') then
178 if(l_future_data_exists = 'N') then
179 l_datetrack_mode := 'UPDATE';
180 else
181 l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
182 end if;
183 else
184 l_datetrack_mode := 'CORRECTION';
185 end if;
186
187
188
189 get_dt_modes(
190 p_effective_date => p_effective_date
191 ,p_effective_end_date => p_effective_end_date
192 ,p_effective_start_date => p_effective_start_date
193 ,p_dml_operation => 'UPDATE' -- since we only allow updating existing rates, dml_operation is always UPDATE
194 ,p_datetrack_mode => l_datetrack_mode);
195
196 if p_input_value_id is null
197 then
198 l_input_value_id := null;
199 else
200 begin
201 select
202 pivt.input_value_id into l_input_value_id
203 from
204 pay_input_values_f_tl pivt,
205 pay_input_values_f piv
206 where
207 pivt.name = p_input_value_id
208 and piv.element_type_id = p_element_type_id
209 and p_effective_date between piv.effective_start_date and piv.effective_end_date
210 and piv.input_value_id = pivt.input_value_id
211 and pivt.language = userenv('LANG');
212 exception when no_data_found then
213 l_input_value_id := null;
214 end;
215 end if;
216
217
218
219 ben_acty_base_rate_api.update_acty_base_rate(
220 p_validate => false
221 ,p_acty_base_rt_id => P_RATE_ID
222 -- ,p_acty_typ_cd => P_ACTY_TYP_CD
223 ,p_rt_typ_cd => P_RT_TYP_CD
224 ,p_bnft_rt_typ_cd => P_BNFT_RT_TYP_CD
225 -- ,p_rt_mlt_cd => P_RT_MLT_CD
226 ,p_val => P_NEW_VAL
227 -- ,p_rndg_cd => P_RNDG_CD
228 ,p_element_type_id => FND_NUMBER.canonical_to_number(P_ELEMENT_TYPE_ID)
229 ,p_input_value_id => L_INPUT_VALUE_ID
230 ,p_comp_lvl_fctr_id => FND_NUMBER.canonical_to_number(P_COMP_LVL_FCTR_ID)
231 ,p_ele_entry_val_cd => P_ELE_ENTRY_VAL_CD
232 ,p_object_version_number => l_object_version_number
233 ,p_effective_date => P_EFFECTIVE_DATE
234 ,p_datetrack_mode => l_datetrack_mode
235 ,p_effective_start_date => l_effective_start_date
236 ,p_effective_end_date => l_effective_end_date
237 );
238 hr_utility.set_location('Finished uploadeing Standard Rate',18);
239 elsif (P_RATE_TYPE = hr_general.decode_lookup('BEN_MRT_RT_TYPE','VRBL_RT') or 'VPF' = P_RATE_TYPE) then
240 hr_utility.set_location('Uploading Variable Rate',15);
241 begin
242 select 'Y' into l_future_data_exists
243 from
244 BEN_VRBL_RT_PRFL_F a
245 where
246 vrbl_rt_prfl_id = p_rate_id
247 and p_effective_date between a.effective_start_date and a.effective_end_date
248 and a.effective_end_date < to_date('4712/12/31','YYYY/MM/DD')
249 and exists
250 ( select 'Y' from BEN_VRBL_RT_PRFL_F b
251 where b.vrbl_rt_prfl_id = a.vrbl_rt_prfl_id
252 and b.effective_start_date = a.effective_end_date + 1);
253 exception when no_data_found then
254 null;
255 end;
256
257 if(p_datetrack_mode <> 'CORRECTION') then
258 if(l_future_data_exists = 'N') then
259 l_datetrack_mode := 'UPDATE';
260 else
261 l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
262 end if;
263 else
264 l_datetrack_mode := 'CORRECTION';
265 end if;
266
267 get_dt_modes(
268 p_effective_date => p_effective_date
269 ,p_effective_end_date => p_effective_end_date
270 ,p_effective_start_date => p_effective_start_date
271 ,p_dml_operation => 'UPDATE' -- since we only allow updating existing rates, dml_operation is always UPDATE
272 ,p_datetrack_mode => l_datetrack_mode);
273
274 ben_vrbl_rate_profile_api.update_vrbl_rate_profile(
275 p_validate => false
276 ,p_vrbl_rt_prfl_id => P_RATE_ID
277 -- ,p_acty_typ_cd => P_ACTY_TYP_CD
278 ,p_rt_typ_cd => P_RT_TYP_CD
279 ,p_bnft_rt_typ_cd => P_BNFT_RT_TYP_CD
280 -- ,p_mlt_cd => P_RT_MLT_CD
281 ,p_val => P_NEW_VAL
282 -- ,p_rndg_cd => P_RNDG_CD
283 ,p_comp_lvl_fctr_id => FND_NUMBER.canonical_to_number(P_COMP_LVL_FCTR_ID)
284 ,p_object_version_number => l_object_version_number
285 ,p_effective_date => P_EFFECTIVE_DATE
286 ,p_datetrack_mode => l_datetrack_mode
287 ,p_effective_start_date => l_effective_start_date
288 ,p_effective_end_date => l_effective_end_date
289 );
290 hr_utility.set_location('Finished uploading Variable Rate',18);
291 end if;
292 hr_utility.set_location('UPLOAD_RATE: Leaving',20);
293 /* */
294 end UPLOAD_RATE;
295
296
297
298 END ben_pdw_mass_upd_rates;
299