[Home] [Help]
PACKAGE BODY: APPS.BEN_CWB_MTRX_UTILS
Source
1 Package Body ben_cwb_mtrx_utils as
2 /* $Header: bencwbmtrxutils.pkb 120.4.12000000.1 2007/01/19 15:25:17 appldev noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'ben_cwb_mtrx_utils.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |-----------------------------< UPDATE_RATES >-----------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure update_rates
13 (p_group_per_in_ler_id in number
17 ,p_elig_sal in number
14 ,p_pl_id in number
15 ,p_oipl_id in number
16 ,p_object_version_number in number
18 ,p_xchg_rate in number
19 ,p_nn_mntry in varchar2
20 ,p_rndg_cd in varchar2
21 ,p_assignment_id in number
22 ,p_alct_by in varchar2
23 ,p_trg_val in number
24 ) is
25 --
26 l_val number;
27 l_ovn number;
28 l_proc varchar2(72) := g_package||'update_rates';
29 begin
30 --
31 hr_utility.set_location('Entering:'|| l_proc, 10);
32 --
33 if (p_alct_by = 'PCT') then
34 -- Calculate Amount using Pct of Elig Sal
35 l_val := (p_trg_val * p_elig_sal) / 100;
36 elsif (p_alct_by = 'AMT' and p_nn_mntry is null) then
37 -- Convert Amount into Local Currency Value
38 l_val := p_trg_val * p_xchg_rate;
39 else
40 -- Don't convert the value if units is non-monetary
41 l_val := p_trg_val;
42 end if;
43 -- Bug 5532745
44 if p_rndg_cd is not null then
45 l_val := benutils.do_rounding
46 (p_rounding_cd => p_rndg_cd
47 ,p_rounding_rl => null
48 ,p_assignment_id => p_assignment_id
49 ,p_value => l_val
50 ,p_effective_date => trunc(sysdate));
51 end if;
52 --
53 -- Assign Object Version Number to local variable
54 l_ovn := p_object_version_number;
55 --
56 --
57 ben_cwb_person_rates_api.update_person_rate(p_group_per_in_ler_id => p_group_per_in_ler_id
58 ,p_pl_id => p_pl_id
59 ,p_oipl_id => p_oipl_id
60 ,p_rec_val => l_val
61 ,p_perf_min_max_edit => 'N'
62 ,p_object_version_number => l_ovn);
63 --
64 hr_utility.set_location(' Leaving:'|| l_proc, 20);
65 --
66 end update_rates;
67
68 --
69 function get_profile_value(p_profile_name in varchar2
70 ,p_current_value in number) return number is
71 l_profile_value varchar2(255);
72 begin
73 l_profile_value := fnd_profile.value(p_profile_name);
74 if l_profile_value is not null then
75 return to_number(l_profile_value, '9999999.99999');
76 else
77 return p_current_value;
78 end if;
79 exception
80 when others then
81 return p_current_value;
82 end get_profile_value;
83
84 -- End of Procedure
85 --
86 -- ----------------------------------------------------------------------------
87 -- |-----------------------------< POP_TRG_AMTS >-----------------------------|
88 -- ----------------------------------------------------------------------------
89 --
90 procedure pop_trg_amts
91 (p_validate in boolean
92 ,p_acting_mgr_pil_id in number
93 ,p_mgr_pil_ids in BEN_CWB_ACCESS_STRING_ARRAY
94 ,p_lvl_num in number
95 ,p_grp_pl_id in number
96 ,p_grp_oipl_id in number
97 ,p_name_type in varchar2
98 ,p_crit_cd1 in varchar2
99 ,p_crit_cd2 in varchar2
100 ,p_crit_vals1 in BEN_CWB_ACCESS_STRING_ARRAY
101 ,p_crit_vals2 in BEN_CWB_ACCESS_STRING_ARRAY
102 ,p_alct_by in varchar2
103 ,p_trg_val in BEN_CWB_ACCESS_STRING_ARRAY
104 ) is
105 --
106 -- Declare cursors and local variables
107 l_compratio_range number := 5;
108 l_los_range number := 1;
109 --
110 -- Cursor for getting Population under a Manager depending upon the Pop code
111 --
112 cursor csr_get_population
113 (c_acting_mgr_pil_id number,
114 c_mgr_pil_id number,
115 c_grp_pl_id number,
116 c_grp_oipl_id number,
117 c_lvl_num varchar2,
118 c_crit_cd1 varchar2,
119 c_crit_cd2 varchar2,
120 c_name_type varchar2) is
121 Select Temp.LerId Emp_ler_id,
122 Temp.PlId Pl_Id,
123 Temp.OiplId Oipl_Id,
124 Temp.Ovn Ovn,
125 Temp.Elig_Sal Elig_Sal,
126 Temp.pl_xchg_rate Xchg_Rate,
127 Temp.nn_mntry Nn_Mntry,
128 Temp.rndg_cd rndg_cd,
129 Temp.assignment_id assignment_id,
130 decode(c_crit_cd1, 'JOB', JobTl.name, 'POS', PosTl.name, 'GRD', GrdTl.name, 'GQ', GrdQrtLkp.meaning,
131 'LOS', trunc(People.years_employed/l_los_range)*l_los_range, 'ORG', OrgTl.name,
132 'COM', trunc(People.grd_comparatio/l_compratio_range)*l_compratio_range, 'CNTY', terr.territory_short_name,
133 'PFR', OldPerfLkp.meaning, 'NPFR', NewPerfLkp.meaning, 'JF1', JobDef.segment1, 'JF2', JobDef.segment2,
134 'JF3', JobDef.segment3, 'JF4', JobDef.segment4, 'JF5', JobDef.segment5, 'EOF1', People.cpi_attribute1,
135 'EOF2', People.cpi_attribute2, 'EOF3', People.cpi_attribute3, 'EOF4', People.cpi_attribute4,
136 'EOF5', People.cpi_attribute5, 'EOF6', People.cpi_attribute6, 'CS1', People.custom_segment1,
137 'CS2', People.custom_segment2, 'CS3', People.custom_segment3, 'CS4', People.custom_segment4,
138 'CS5', People.custom_segment5, 'CS6', People.custom_segment6, 'CS7', People.custom_segment7,
142 'DIRREP', decode(c_name_type, 'FN', DirRep.full_name, 'CN', DirRep.custom_name, DirRep.brief_name),
139 'CS8', People.custom_segment8, 'CS9', People.custom_segment9, 'CS10', People.custom_segment10,
140 'CS11', People.custom_segment11, 'CS12', People.custom_segment12, 'CS13', People.custom_segment13,
141 'CS14', People.custom_segment14, 'CS15', People.custom_segment15,
143 'LOC', nvl(Loc.description, Loc.location_code)) Crit_Val1,
144 decode(c_crit_cd2, 'JOB', JobTl.name, 'POS', PosTl.name, 'GRD', GrdTl.name, 'GQ', GrdQrtLkp.meaning,
145 'LOS', trunc(People.years_employed/l_los_range)*l_los_range, 'ORG', OrgTl.name,
146 'COM', trunc(People.grd_comparatio/l_compratio_range)*l_compratio_range, 'CNTY', terr.territory_short_name,
147 'PFR', OldPerfLkp.meaning, 'NPFR', NewPerfLkp.meaning, 'JF1', JobDef.segment1, 'JF2', JobDef.segment2,
148 'JF3', JobDef.segment3, 'JF4', JobDef.segment4, 'JF5', JobDef.segment5, 'EOF1', People.cpi_attribute1,
149 'EOF2', People.cpi_attribute2, 'EOF3', People.cpi_attribute3, 'EOF4', People.cpi_attribute4,
150 'EOF5', People.cpi_attribute5, 'EOF6', People.cpi_attribute6, 'CS1', People.custom_segment1,
151 'CS2', People.custom_segment2, 'CS3', People.custom_segment3, 'CS4', People.custom_segment4,
152 'CS5', People.custom_segment5, 'CS6', People.custom_segment6, 'CS7', People.custom_segment7,
153 'CS8', People.custom_segment8, 'CS9', People.custom_segment9, 'CS10', People.custom_segment10,
154 'CS11', People.custom_segment11, 'CS12', People.custom_segment12, 'CS13', People.custom_segment13,
155 'CS14', People.custom_segment14, 'CS15', People.custom_segment15,
156 'DIRREP', decode(c_name_type, 'FN', DirRep.full_name, 'CN', DirRep.custom_name, DirRep.brief_name),
157 'LOC', nvl(Loc.description, Loc.location_code)) Crit_Val2
158 From ben_cwb_person_info People
159 ,ben_cwb_person_info DirRep
160 ,ben_transaction Txn
161 ,per_jobs Job
162 ,per_jobs_tl JobTl
163 ,per_job_definitions JobDef
164 ,per_grades_tl GrdTl
165 ,hr_all_positions_f_tl PosTl
166 ,hr_all_organization_units_tl OrgTl
167 ,hr_locations_all_tl Loc
168 ,hr_lookups GrdQrtLkp
169 ,hr_lookups OldPerfLkp
170 ,hr_lookups NewPerfLkp
171 ,fnd_territories_tl terr
172 ,(Select max(Hrchy.mgr_per_in_ler_id) MgrLerId
173 ,Rates.group_per_in_ler_id LerId
174 ,max(Rates.object_version_number) Ovn
175 ,max(Rates.pl_id) PlId
176 ,max(Rates.oipl_id) OiplId
177 ,max(Rates.elig_sal_val) Elig_Sal
178 ,max(GrpPl.perf_revw_strt_dt) perf_date
179 ,max(GrpPl.emp_interview_typ_cd) perf_type
180 ,max(xchg.xchg_rate) pl_xchg_rate
181 ,max(Pl.rec_nnmntry_uom) nn_mntry
182 ,max(Pl.rec_rndg_cd) rndg_cd
183 ,max(Rates.assignment_id) assignment_id
184 from ben_cwb_person_rates Rates
185 ,ben_cwb_pl_dsgn Pl
186 ,ben_cwb_pl_dsgn GrpPl
187 ,ben_cwb_xchg Xchg
188 ,ben_cwb_group_hrchy Hrchy
189 where Hrchy.mgr_per_in_ler_id = c_mgr_pil_id
190 and ((Hrchy.lvl_num between 1 and c_lvl_num and Hrchy.mgr_per_in_ler_id <> c_acting_mgr_pil_id)
191 or (Hrchy.lvl_num = 1 and Hrchy.mgr_per_in_ler_id = c_acting_mgr_pil_id))
192 and Rates.group_per_in_ler_id = Hrchy.emp_per_in_ler_id
193 and Rates.group_pl_id = c_grp_pl_id
194 and Rates.group_oipl_id = c_grp_oipl_id
195 and Rates.elig_flag = 'Y'
196 and Pl.pl_id = Rates.pl_id
197 and Pl.oipl_id = Rates.oipl_id
198 and Pl.lf_evt_ocrd_dt = Rates.lf_evt_ocrd_dt
199 and Rates.group_pl_id = xchg.group_pl_id
200 and Rates.lf_evt_ocrd_dt = xchg.lf_evt_ocrd_dt
201 and Rates.currency = xchg.currency
202 and Pl.group_pl_id = GrpPl.pl_id
203 and Pl.lf_evt_ocrd_dt = GrpPl.lf_evt_ocrd_dt
204 and GrpPl.oipl_id = -1
205 group by Rates.group_per_in_ler_id) Temp
206 Where People.group_per_in_ler_id = Temp.LerId
207 and DirRep.group_per_in_ler_id(+) = Temp.MgrLerId
208 and Job.job_id (+) = People.job_id
209 and JobTl.job_id (+) = People.job_id
210 and JobTl.language (+) = userenv('LANG')
211 and JobDef.job_definition_id (+) = Job.job_definition_id
212 and PosTl.position_id (+) = People.position_id
213 and PosTl.language (+) = userenv('LANG')
214 and GrdTl.grade_id (+) = People.grade_id
215 and GrdTl.language (+) = userenv('LANG')
216 and OrgTl.organization_id (+) = People.organization_id
217 and OrgTl.language (+) = userenv('LANG')
218 and GrdQrtLkp.lookup_code (+) = People.grd_quartile
219 and GrdQrtLkp.lookup_type (+) = 'BEN_CWB_QUAR_IN_GRD'
220 and Loc.location_id (+) = People.location_id
221 and Loc.language (+) = userenv('LANG')
222 and OldPerfLkp.lookup_code (+) = People.performance_rating
223 and OldPerfLkp.lookup_type (+) = 'PERFORMANCE_RATING'
224 and Txn.transaction_id (+) = Temp.assignment_id
225 and Txn.transaction_type (+) = 'CWBPERF' || to_char(Temp.perf_date,'yyyy/mm/dd') || Temp.perf_type
226 and NewPerfLkp.lookup_code (+) = Txn.attribute3
227 and NewPerfLkp.lookup_type (+) = 'PERFORMANCE_RATING'
228 and people.legislation_code = terr.territory_code (+)
229 and terr.language (+) = userenv('LANG');
230 --
231 l_proc varchar2(72) := g_package||'pop_trg_amts';
232 --
233 begin
234 --
235 hr_utility.set_location('Entering:'|| l_proc, 10);
236 --
237 if p_crit_cd1 = 'LOS' or p_crit_cd2 = 'LOS' then
238 l_los_range := get_profile_value('BEN_CWB_WIZ_YRS_WKD_RANGE'
239 ,l_los_range);
240 end if;
241 if p_crit_cd1 = 'COM' or p_crit_cd2 = 'COM' then
242 l_compratio_range := get_profile_value('BEN_CWB_WIZ_COMPRATIO_RANGE'
243 ,l_compratio_range);
244 end if;
245 --
246 -- Issue a savepoint
247 --
248 savepoint pop_trg_amts;
249 --
250 --
251 for l_mgr_pil_id in p_mgr_pil_ids.first .. p_mgr_pil_ids.last
252 loop
253 --
254 for pop in csr_get_population(p_acting_mgr_pil_id,
255 p_mgr_pil_ids(l_mgr_pil_id),
256 p_grp_pl_id,
257 p_grp_oipl_id,
258 p_lvl_num,
259 p_crit_cd1,
260 p_crit_cd2,
261 p_name_type)
262 loop
263 --
264 for l_num in 1 .. p_crit_vals1.count
265 loop
266 --
267 if (p_crit_cd2 is not null) then
268 --
269 if (nvl(pop.Crit_Val1, 'NV') = p_crit_vals1(l_num) and
270 nvl(pop.Crit_Val2, 'NV') = p_crit_vals2(l_num)) then
271 --
272 update_rates(pop.Emp_ler_id
273 ,pop.Pl_Id
274 ,pop.Oipl_Id
275 ,pop.Ovn
276 ,pop.Elig_Sal
277 ,pop.Xchg_Rate
278 ,pop.Nn_Mntry
279 ,pop.rndg_cd
280 ,pop.assignment_id
281 ,p_alct_by
282 ,p_trg_val(l_num));
283 --
284 end if;
285 --
286 else
287 --
288 if (nvl(pop.Crit_Val1, 'NV') = p_crit_vals1(l_num)) then
289 --
290 update_rates(pop.Emp_ler_id
291 ,pop.Pl_Id
292 ,pop.Oipl_Id
293 ,pop.Ovn
294 ,pop.Elig_Sal
295 ,pop.Xchg_Rate
296 ,pop.Nn_Mntry
297 ,pop.rndg_cd
298 ,pop.assignment_id
299 ,p_alct_by
300 ,p_trg_val(l_num));
301 --
302 end if;
303 --
304 end if;
305 -- End of If statement
306 end loop;
307 -- End of For Loop (First Criterion)
308 end loop;
309 -- End of For Loop (Cursor)
310 end loop;
311 -- End of For Loop (Array)
312 --
313 -- Update the Summary Table
314 ben_cwb_summary_pkg.save_pl_sql_tab;
315 --
316 -- When in validation only mode raise the Validate_Enabled exception
317 --
318 if p_validate then
319 raise hr_api.validate_enabled;
320 end if;
321 --
322 hr_utility.set_location(' Leaving:'|| l_proc, 20);
323 --
324 exception
325 when hr_api.validate_enabled then
326 --
327 -- As the Validate_Enabled exception has been raised
328 -- we must rollback to the savepoint
329 --
330 rollback to pop_trg_amts;
331 --
332 hr_utility.set_location(' Leaving:'|| l_proc, 30);
333 --
334 when others then
335 --
336 -- A validation or unexpected error has occured
337 --
338 rollback to pop_trg_amts;
339 --
340 -- Reset IN OUT parameters and set all
341 -- OUT parameters, including warnings, to null
342 --
343 hr_utility.set_location(' Leaving:'|| l_proc, 40);
344 raise;
345 --
346 end pop_trg_amts;
347 -- End of Procedure
348 --
349 end ben_cwb_mtrx_utils;
350 -- End of Package