1 Package Body pqh_fr_sync_sal_rates as
2 /* $Header: pqfrssrt.pkb 115.1 2004/01/08 09:14:26 kgowripe noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_fr_sync_sal_rates.'; -- Global package name
9 g_debug boolean := hr_utility.debug_enabled; -- check for debug enabled or not
10 g_status varchar2(1) := 'S'; -- Status variable defaulted to success
11 -- point value for the salary as of the effective date
12 g_sal_point_rate pqh_fr_global_indices_f.basic_salary_rate%TYPE;
13 g_sal_point_currency pqh_fr_global_indices_f.currency_code%TYPE;
14 g_gl_cet_id pqh_copy_entity_txns.copy_entity_txn_id%TYPE := -1;
15 g_gl_currency_cd pqh_fr_global_indices_f.currency_code%TYPE;
16 g_conv_factor NUMBER := 1;
17 --
18 -- ----------------------------------------------------------------------------
19 -- | Private Procedure/Function Definitions |
20 -- ----------------------------------------------------------------------------
21 --
22 PROCEDURE sync_all_rates(p_effective_date IN DATE);
23
24 PROCEDURE sync_rates_for_ib(p_effective_date IN DATE,
25 p_ib IN NUMBER,
26 p_inm IN NUMBER);
27 PROCEDURE sync_rates_in_gsp_stage(p_effective_date IN DATE,
28 p_ib IN NUMBER,
29 p_inm IN NUMBER);
30 PROCEDURE get_bareme_point_value(p_effective_date IN DATE
31 ,p_basic_sal_rate OUT NOCOPY NUMBER
32 ,p_bareme_currency_cd OUT NOCOPY VARCHAR2);
33 --
34 -- ----------------------------------------------------------------------------
35 -- | sync_gsp_sal_rt_with_bareme |
36 -- ----------------------------------------------------------------------------
37 --
38
39 PROCEDURE sync_gsp_sal_rt_with_bareme(errbuf OUT NOCOPY VARCHAR2,
40 retcode OUT NOCOPY VARCHAR2,
41 p_effective_date IN DATE,
42 p_mode IN VARCHAR2,
43 p_commit_mode IN VARCHAR2,
44 p_ib1 IN NUMBER Default NULL,
45 p_ib2 IN NUMBER Default NULL,
46 p_ib3 IN NUMBER Default NULL,
47 p_ib4 IN NUMBER Default NULL,
48 p_ib5 IN NUMBER Default NULL,
49 p_ib6 IN NUMBER Default NULL,
50 p_ib7 IN NUMBER Default NULL,
51 p_ib8 IN NUMBER Default NULL,
52 p_ib9 IN NUMBER Default NULL,
53 p_ib10 IN NUMBER Default NULL) IS
54 l_conc_status boolean;
55 CURSOR csr_inm_for_ib (p_ib IN Number) IS
56 SELECT increased_index
57 FROM pqh_fr_global_indices_f
58 WHERE p_effective_date BETWEEN effective_start_date AND effective_end_date
59 AND gross_index = p_ib;
60 l_inm1 NUMBER;
61 l_inm2 NUMBER;
62 l_inm3 NUMBER;
63 l_inm4 NUMBER;
64 l_inm5 NUMBER;
65 l_inm6 NUMBER;
66 l_inm7 NUMBER;
67 l_inm8 NUMBER;
68 l_inm9 NUMBER;
69 l_inm10 NUMBER;
70 l_proc varchar2(72) := g_package||'sync_gsp_sal_rt_with_bareme';
71 BEGIN
72 g_debug := hr_utility.debug_enabled;
73 if g_debug then
74 hr_utility.set_location('Entering '||l_proc,10);
75 end if;
76 g_status := 'S';
77 IF p_commit_mode = 'VALIDATE' THEN
78 SAVEPOINT sync_sal_rates;
79 END IF;
80 --
81 get_bareme_point_value(p_effective_date => p_effective_date
82 ,p_basic_sal_rate => g_sal_point_rate
83 ,p_bareme_currency_cd => g_sal_point_currency);
84 IF p_mode = 'ALL' THEN
85
86 sync_all_rates(p_effective_date => p_effective_date);
87
88 ELSIF p_mode = 'SPECIFIC' THEN
89 IF p_ib1 IS NOT NULL THEN
90 OPEN csr_inm_for_ib(p_ib1);
91 FETCH csr_inm_for_ib INTO l_inm1;
92 CLOSE csr_inm_for_ib;
93 IF l_inm1 IS NOT NULL THEN
94 sync_rates_for_ib(p_effective_date => p_effective_date,
95 p_ib => p_ib1,
96 p_inm => l_inm1);
97 END IF;
98 END IF;
99 IF p_ib2 IS NOT NULL THEN
100 OPEN csr_inm_for_ib(p_ib2);
101 FETCH csr_inm_for_ib INTO l_inm2;
102 CLOSE csr_inm_for_ib;
103 IF l_inm2 IS NOT NULL THEN
104 sync_rates_for_ib(p_effective_date => p_effective_date,
105 p_ib => p_ib2,
106 p_inm => l_inm2);
107 END IF;
108 END IF;
109 IF p_ib3 IS NOT NULL THEN
110 OPEN csr_inm_for_ib(p_ib3);
111 FETCH csr_inm_for_ib INTO l_inm3;
112 CLOSE csr_inm_for_ib;
113 IF l_inm3 IS NOT NULL THEN
114 sync_rates_for_ib(p_effective_date => p_effective_date,
115 p_ib => p_ib3,
116 p_inm => l_inm3);
117 END IF;
118 END IF;
119 IF p_ib4 IS NOT NULL THEN
120 OPEN csr_inm_for_ib(p_ib4);
121 FETCH csr_inm_for_ib INTO l_inm4;
122 CLOSE csr_inm_for_ib;
123 IF l_inm4 IS NOT NULL THEN
124 sync_rates_for_ib(p_effective_date => p_effective_date,
125 p_ib => p_ib4,
126 p_inm => l_inm4);
127 END IF;
128 END IF;
129 IF p_ib5 IS NOT NULL THEN
130 OPEN csr_inm_for_ib(p_ib5);
131 FETCH csr_inm_for_ib INTO l_inm5;
132 CLOSE csr_inm_for_ib;
133 IF l_inm5 IS NOT NULL THEN
134 sync_rates_for_ib(p_effective_date => p_effective_date,
135 p_ib => p_ib5,
136 p_inm => l_inm5);
137 END IF;
138 END IF;
139 IF p_ib6 IS NOT NULL THEN
140 OPEN csr_inm_for_ib(p_ib6);
141 FETCH csr_inm_for_ib INTO l_inm6;
142 CLOSE csr_inm_for_ib;
143 IF l_inm6 IS NOT NULL THEN
144 sync_rates_for_ib(p_effective_date => p_effective_date,
145 p_ib => p_ib6,
146 p_inm => l_inm6);
147 END IF;
148 END IF;
149 IF p_ib7 IS NOT NULL THEN
150 OPEN csr_inm_for_ib(p_ib7);
151 FETCH csr_inm_for_ib INTO l_inm7;
152 CLOSE csr_inm_for_ib;
153 IF l_inm7 IS NOT NULL THEN
154 sync_rates_for_ib(p_effective_date => p_effective_date,
155 p_ib => p_ib7,
156 p_inm => l_inm7);
157 END IF;
158 END IF;
159 IF p_ib8 IS NOT NULL THEN
160 OPEN csr_inm_for_ib(p_ib8);
161 FETCH csr_inm_for_ib INTO l_inm8;
162 CLOSE csr_inm_for_ib;
163 IF l_inm8 IS NOT NULL THEN
164 sync_rates_for_ib(p_effective_date => p_effective_date,
165 p_ib => p_ib8,
166 p_inm => l_inm8);
167 END IF;
168 END IF;
169 IF p_ib9 IS NOT NULL THEN
170 OPEN csr_inm_for_ib(p_ib9);
171 FETCH csr_inm_for_ib INTO l_inm9;
172 CLOSE csr_inm_for_ib;
173 IF l_inm9 IS NOT NULL THEN
174 sync_rates_for_ib(p_effective_date => p_effective_date,
175 p_ib => p_ib9,
176 p_inm => l_inm9);
177 END IF;
178 END IF;
179 IF p_ib10 IS NOT NULL THEN
180 OPEN csr_inm_for_ib(p_ib10);
181 FETCH csr_inm_for_ib INTO l_inm10;
182 CLOSE csr_inm_for_ib;
183 IF l_inm10 IS NOT NULL THEN
184 sync_rates_for_ib(p_effective_date => p_effective_date,
185 p_ib => p_ib10,
186 p_inm => l_inm10);
187 END IF;
188 END IF;
189 END IF; --End Mode Specific
190
191 IF g_status = 'E' THEN
192 l_conc_status := fnd_concurrent.set_completion_status(status => 'ERROR'
193 ,message=>SQLERRM);
194 END IF;
195 IF p_commit_mode = 'VALIDATE' THEN
196 ROLLBACK TO sync_sal_rates;
197 ELSE
198 COMMIT;
199 END IF;
200
201 if g_debug then
202 hr_utility.set_location('Leaving '||l_proc,20);
203 end if;
204
205 END sync_gsp_sal_rt_with_bareme;
206 --
207 -- ----------------------------------------------------------------------------
208 -- | sync_all_rates |
209 -- ----------------------------------------------------------------------------
210 --
211 PROCEDURE sync_all_rates(p_effective_date IN DATE) IS
212 CURSOR csr_all_ib_inms IS
213 SELECT gross_index,increased_index
214 FROM pqh_fr_global_indices_f
215 WHERE p_effective_date BETWEEN effective_start_date AND effective_end_date;
216 l_proc varchar2(72) := g_package||'.sync_all_rates';
217 BEGIN
218 if g_debug then
219 hr_utility.set_location('Entering '||l_proc,10);
220 end if;
221 FOR l_glb_ind_rec IN csr_all_ib_inms
222 LOOP
223 sync_rates_for_ib(p_effective_date => p_effective_date,
224 p_ib => l_glb_ind_rec.gross_index,
225 p_inm => l_glb_ind_rec.increased_index);
226 END LOOP;
227 if g_debug then
228 hr_utility.set_location('Entering '||l_proc,10);
229 end if;
230 EXCEPTION
231 When Others THEN
232 fnd_file.put_line(fnd_file.log,SQLERRM);
233 g_status := 'E';
234 END sync_all_rates;
235 --
236 -- ----------------------------------------------------------------------------
237 -- | sync_rates_for_ib |
238 -- ----------------------------------------------------------------------------
239 --
240 PROCEDURE sync_rates_for_ib(p_effective_date IN DATE,
241 p_ib IN NUMBER,
242 p_inm IN NUMBER) IS
243 CURSOR csr_prog_points_with_ib IS
244 SELECT pps.parent_spine_id,pps.name,psp.spinal_point_id
245 FROM per_spinal_points psp,
246 per_parent_spines pps
247 WHERE psp.information_category = 'FR_PQH'
248 AND psp.information1 = p_ib
249 AND psp.parent_spine_id = pps.parent_spine_id;
250
251 /* Update rates that have the name same as the Pay scale name */
252 CURSOR csr_hrrate_for_point(p_point_id IN NUMBER, p_scale_name varchar2) IS
253 SELECT pgr.grade_rule_id,pgr.currency_code,pgr.value,pgr.object_version_number
254 FROM pay_grade_rules_f pgr,
255 pay_rates pr
256 WHERE pgr.rate_type = 'SP'
257 AND pgr.grade_or_spinal_point_id = p_point_id
258 AND p_effective_date BETWEEN pgr.effective_start_date AND pgr.effective_end_date
259 AND pgr.rate_id =pr.rate_id
260 AND pr.name = p_scale_name;
261 l_rt_currency_cd pay_grade_rules_f.currency_code%TYPE;
262 l_new_value NUMBER := 0;
263 l_conv_factor NUMBER := 1;
264 l_dt_upd_mode VARCHAR2(30);
265 l_effective_start_date DATE;
266 l_effective_end_date DATE;
267 l_proc varchar2(72) := g_package||'.sync_rates_for_ib';
268 BEGIN
269
270 if g_debug then
271 hr_utility.set_location('Entering '||l_proc,10);
272 end if;
273
274 FOR l_sp_rec IN csr_prog_points_with_ib
275 LOOP
276 FOR l_rate_rec IN csr_hrrate_for_point(l_sp_rec.spinal_point_id,l_sp_rec.name)
277 LOOP
278 IF l_rate_rec.currency_code IS NOT NULL THEN
279 BEGIN
280 l_conv_factor := hr_currency_pkg.get_rate(p_from_currency => g_sal_point_currency,
281 p_to_currency => l_rate_rec.currency_code,
282 p_conversion_date => p_effective_date,
283 p_rate_type => 'Corporate');
284 IF l_conv_factor IS NULL THEN
285 l_conv_factor := 1;
286 END IF;
287 EXCEPTION
288 When Others Then
289 l_conv_factor := 1;
290 END;
291 END IF;
292 -- Now apply the conversion rates to Grade ladder Currency from the Bareme Currency
293 l_new_value := (p_inm*g_sal_point_rate)*l_conv_factor;
294
295 l_dt_upd_mode := pqh_gsp_stage_to_ben.get_update_mode(p_table_name => 'PAY_GRADE_RULES_F'
296 ,p_key_column_name => 'GRADE_RULE_ID'
297 ,p_key_column_value => l_rate_rec.grade_rule_id
298 ,p_effective_date => p_effective_date);
299 hr_rate_values_api.update_rate_value
300 (p_effective_date => p_effective_date
301 ,p_value => nvl(l_new_value,0)
302 ,p_grade_rule_id => l_rate_rec.grade_rule_id
303 ,p_datetrack_mode => l_dt_upd_mode
304 ,p_object_version_number => l_rate_rec.object_version_number
305 ,p_effective_start_date => l_effective_start_date
306 ,p_effective_end_date => l_effective_end_date);
307 END LOOP;
308 END LOOP;
309 /*
310 sync_rates_in_gsp_stage(p_effective_date => p_effective_date,
311 p_ib => p_ib,
312 p_inm => p_inm);
313 */
314 if g_debug then
315 hr_utility.set_location('Leaving '||l_proc,20);
316 end if;
317 EXCEPTION
318 When Others THEN
319 fnd_file.put_line(fnd_file.log,SQLERRM);
320 g_status := 'E';
321 END sync_rates_for_ib;
322 --
323 -- ----------------------------------------------------------------------------
324 -- | get_bareme_point_value |
325 -- ----------------------------------------------------------------------------
326 --
327 PROCEDURE get_bareme_point_value(p_effective_date IN DATE
328 ,p_basic_sal_rate OUT NOCOPY NUMBER
329 ,p_bareme_currency_cd OUT NOCOPY VARCHAR2) IS
330 CURSOR csr_point_value IS
331 SELECT basic_salary_rate,currency_code
332 FROM pqh_fr_global_indices_f
333 WHERE p_effective_date BETWEEN effective_start_date AND effective_end_date
334 AND type_of_record = 'INM';
335 l_proc varchar2(72) := g_package||'.get_bareme_point_value';
336 BEGIN
337 if g_debug then
338 hr_utility.set_location('Entering '||l_proc,10);
339 end if;
340 OPEN csr_point_value;
341 FETCH csr_point_value INTO p_basic_sal_rate,p_bareme_currency_cd;
342 CLOSE csr_point_value;
343 if g_debug then
344 hr_utility.set_location('Leaving '||l_proc,20);
345 end if;
346 END get_bareme_point_value;
347 --
348 -- ----------------------------------------------------------------------------
349 -- | sync_rates_in_gsp_stage |
350 -- ----------------------------------------------------------------------------
351 --
352 PROCEDURE sync_rates_in_gsp_stage(p_effective_date IN DATE,
353 p_ib IN NUMBER,
354 p_inm IN NUMBER) IS
355 CURSOR csr_sp_rows_in_stage IS
356 SELECT cer.copy_entity_result_id,cer.copy_entity_txn_id
357 FROM ben_copy_entity_results cer,
358 pqh_copy_entity_txns cet
359 WHERE cer.table_alias = 'OPT'
360 AND cer.dml_operation = 'INSERT'
361 AND NVL(cer.information101,'XXX') = 'FR_PQH'
362 AND NVL(cer.information173,-9999) = p_ib
363 AND cer.copy_entity_txn_id = cet.copy_entity_txn_id
364 AND cet.status <> 'COMPLETED'
365 ORDER BY cer.copy_entity_txn_id;
366
367 CURSOR csr_gl_currency (p_cet_id IN NUMBER) IS
368 SELECT information50
369 FROM ben_copy_entity_results
370 WHERE copy_entity_txn_id = p_cet_id
371 AND table_alias = 'PGM';
372 --Only Update rates for newly created rows.. as for completed information
373 --the udpates are done already.
374 CURSOR csr_hrrate_rows_in_stage(p_sp_cer_id IN NUMBER, p_cet_id IN NUMBER) IS
375 SELECT copy_entity_result_id,information297
376 FROM ben_copy_entity_results
377 WHERE copy_entity_txn_id = p_cet_id
378 AND table_alias = 'HRRATE'
379 AND dml_operation = 'INSERT'
380 AND information278 = p_sp_cer_id
381 AND information1 IS NULL;
382
383 l_new_value NUMBER := 0;
384 l_proc varchar2(72) := g_package||'sync_rates_in_gsp_stage';
385 BEGIN
386 if g_debug then
387 hr_utility.set_location('Entering '||l_proc,10);
388 end if;
389 FOR l_sp_rec IN csr_sp_rows_in_stage
390 LOOP
391 --get the GL currency code and the corresponding currency value for each CET
392 IF NVL(g_gl_cet_id,-1) <> l_sp_rec.copy_entity_txn_id THEN
393 OPEN csr_gl_currency(l_sp_rec.copy_entity_txn_id);
394 FETCH csr_gl_currency INTO g_gl_currency_cd;
395 CLOSE csr_gl_currency;
396 IF g_gl_currency_cd IS NOT NULL THEN
397 BEGIN
398 g_conv_factor := hr_currency_pkg.get_rate(p_from_currency => g_sal_point_currency,
399 p_to_currency => g_gl_currency_cd,
400 p_conversion_date => p_effective_date,
401 p_rate_type => 'Corporate');
402 IF g_conv_factor IS NULL THEN
403 g_conv_factor := 1;
404 END IF;
405 EXCEPTION
406 When Others Then
407 g_conv_factor := 1;
408 END;
409 END IF;
410 END IF;
411 -- Now apply the conversion rates to Grade ladder Currency from the Bareme Currency
412 l_new_value := (p_inm*g_sal_point_rate)*g_conv_factor;
413
414
415 FOR l_hrrate_rec IN csr_hrrate_rows_in_stage(l_sp_rec.copy_entity_result_id,l_sp_rec.copy_entity_txn_id)
416 LOOP
417 UPDATE ben_copy_entity_results
418 SET information297 = NVL(l_new_value,0)
419 WHERE copy_entity_result_id = l_hrrate_rec.copy_entity_result_id;
420 END LOOP;
421 END LOOP;
422 if g_debug then
423 hr_utility.set_location('Leaving '||l_proc,20);
424 end if;
425 EXCEPTION
426 When Others THEN
427 fnd_file.put_line(fnd_file.log,SQLERRM);
428 g_status := 'E';
429 END sync_rates_in_gsp_stage;
430 END pqh_fr_sync_sal_rates;
[Home] [Help]