[Home] [Help]
PACKAGE BODY: APPS.PQH_EFC
Source
1 Package Body PQH_EFC AS
2 /* $Header: pqefccon.pkb 120.3 2005/09/29 15:44:39 srajakum noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_efc.'; -- Global package name
9 --
10
11 ------------------------------------------------------------------------------------
12 FUNCTION get_currency_cd
13 (
14 p_primary_key IN NUMBER,
15 p_entity_cd IN VARCHAR2,
16 p_business_group_id IN NUMBER
17 ) RETURN varchar2 IS
18
19 /*
20 This function will return the currency code of the budget
21
22 */
23
24 l_proc varchar2(72) := g_package||'get_currency_cd';
25
26 l_currency_cd varchar2(240);
27
28 --
29 CURSOR csr_bus_grp IS
30 SELECT currency_code
31 FROM per_business_groups
32 WHERE business_group_id = p_business_group_id;
33 --
34 --
35 CURSOR csr_bvr IS
36 SELECT currency_code
37 FROM pqh_budgets bgt,
38 pqh_budget_versions bvr
39 WHERE bgt.budget_id = bvr.budget_id
40 AND bvr.budget_version_id = p_primary_key;
41 --
42 --
43 CURSOR csr_bdt IS
44 SELECT currency_code
45 FROM pqh_budgets bgt,
46 pqh_budget_versions bvr,
47 pqh_budget_details bdt
48 WHERE bgt.budget_id = bvr.budget_id
49 AND bvr.budget_version_id = bdt.budget_version_id
50 AND bdt.budget_detail_id = p_primary_key;
51 --
52 --
53 CURSOR csr_bpr IS
54 SELECT currency_code
55 FROM pqh_budgets bgt,
56 pqh_budget_versions bvr,
57 pqh_budget_details bdt,
58 pqh_budget_periods bpr
59 WHERE bgt.budget_id = bvr.budget_id
60 AND bvr.budget_version_id = bdt.budget_version_id
61 AND bdt.budget_detail_id = bpr.budget_detail_id
62 AND bpr.budget_period_id = p_primary_key;
63 --
64 --
65 CURSOR csr_bst IS
66 SELECT currency_code
67 FROM pqh_budgets bgt,
68 pqh_budget_versions bvr,
69 pqh_budget_details bdt,
70 pqh_budget_periods bpr,
71 pqh_budget_sets bst
72 WHERE bgt.budget_id = bvr.budget_id
73 AND bvr.budget_version_id = bdt.budget_version_id
74 AND bdt.budget_detail_id = bpr.budget_detail_id
75 AND bpr.budget_period_id = bst.budget_period_id
76 AND bst.budget_set_id = p_primary_key;
77 --
78 --
79 CURSOR csr_wdt IS
80 SELECT currency_code
81 FROM pqh_budgets bgt,
82 pqh_worksheets wks,
83 pqh_worksheet_details wdt
84 WHERE bgt.budget_id = wks.budget_id
85 AND wks.worksheet_id = wdt.worksheet_id
86 AND wdt.worksheet_detail_id = p_primary_key;
87 --
88 --
89 CURSOR csr_wpr IS
90 SELECT currency_code
91 FROM pqh_budgets bgt,
92 pqh_worksheets wks,
93 pqh_worksheet_details wdt,
94 pqh_worksheet_periods wpr
95 WHERE bgt.budget_id = wks.budget_id
96 AND wks.worksheet_id = wdt.worksheet_id
97 AND wdt.worksheet_detail_id = wpr.worksheet_detail_id
98 AND wpr.worksheet_period_id = p_primary_key;
99 --
100 --
101 CURSOR csr_wst IS
102 SELECT currency_code
103 FROM pqh_budgets bgt,
104 pqh_worksheets wks,
105 pqh_worksheet_details wdt,
106 pqh_worksheet_periods wpr,
107 pqh_worksheet_budget_sets wst
108 WHERE bgt.budget_id = wks.budget_id
109 AND wks.worksheet_id = wdt.worksheet_id
110 AND wdt.worksheet_detail_id = wpr.worksheet_detail_id
111 AND wpr.worksheet_period_id = wst.worksheet_period_id
112 AND wst.worksheet_budget_set_id = p_primary_key;
113 --
114 --
115 CURSOR csr_pec IS
116 SELECT currency_code
117 FROM pqh_budgets bgt,
118 pqh_budget_versions bvr,
119 pqh_element_commitments pec
120 WHERE bgt.budget_id = bvr.budget_id
121 AND bvr.budget_version_id = pec.budget_version_id
122 AND pec.element_commitment_id = p_primary_key;
123 --
124 --
125 CURSOR csr_bre IS
126 SELECT currency_code
127 FROM pqh_budgets bgt,
128 pqh_budget_versions bvr,
129 pqh_budget_pools bpl,
130 pqh_bdgt_pool_realloctions bre
131 WHERE bgt.budget_id = bvr.budget_id
132 AND bvr.budget_version_id = bpl.budget_version_id
133 AND bre.pool_id = bpl.pool_id
134 AND bre.reallocation_id = p_primary_key;
135 --
136 --
137 CURSOR csr_rmr IS
138 SELECT currency_code
139 FROM pqh_criteria_rate_defn crd,
140 pqh_rate_matrix_rates_f rmr
141 WHERE crd.criteria_rate_defn_id = rmr.criteria_rate_defn_id
142 AND rmr.rate_matrix_rate_id = p_primary_key
143 and rmr.effective_start_date = (
144 Select min(rmr1.effective_start_date)
145 From pqh_rate_matrix_rates_f rmr1
146 Where rmr1.rate_matrix_rate_id = p_primary_key );
147 --
148 CURSOR csr_ssl IS
149 SELECT currency_code
150 FROM per_salary_survey_lines ssl
151 WHERE ssl.salary_survey_line_id = p_primary_key;
152 --
153
154 BEGIN
155
156 hr_utility.set_location('Entering: '||l_proc, 5);
157
158 IF p_entity_cd = 'BVR' THEN
159 -- Budget Version Table BVR
160 OPEN csr_bvr;
161 FETCH csr_bvr INTO l_currency_cd;
162 CLOSE csr_bvr;
163 --
164 ELSIF p_entity_cd = 'BDT' THEN
165 -- Budget Details Table BDT
166 OPEN csr_bdt;
167 FETCH csr_bdt INTO l_currency_cd;
168 CLOSE csr_bdt;
169 --
170 ELSIF p_entity_cd = 'BPR' THEN
171 -- Budget Period Table BPR
172 OPEN csr_bpr;
173 FETCH csr_bpr INTO l_currency_cd;
174 CLOSE csr_bpr;
175 --
176 ELSIF p_entity_cd = 'BST' THEN
177 -- Budget Sets Table BST
178 OPEN csr_bst;
179 FETCH csr_bst INTO l_currency_cd;
180 CLOSE csr_bst;
181 --
182 ELSIF p_entity_cd = 'WDT' THEN
183 -- Worksheet Details Table WDT
184 OPEN csr_wdt;
185 FETCH csr_wdt INTO l_currency_cd;
186 CLOSE csr_wdt;
187 --
188 ELSIF p_entity_cd = 'WPR' THEN
189 -- Worksheet Periods Table WPR
190 OPEN csr_wpr;
191 FETCH csr_wpr INTO l_currency_cd;
192 CLOSE csr_wpr;
193 --
194 ELSIF p_entity_cd = 'WST' THEN
195 -- Worksheet Budget Set Table WST
196 OPEN csr_wst;
197 FETCH csr_wst INTO l_currency_cd;
198 CLOSE csr_wst;
199 --
200 ELSIF p_entity_cd = 'PEC' THEN
201 -- pqh_element_commitments PEC
202 OPEN csr_pec;
203 FETCH csr_pec INTO l_currency_cd;
204 CLOSE csr_pec;
205 --
206 ELSIF p_entity_cd = 'BRE' THEN
207 -- pqh_bdgt_pool_realloctions BRE
208 OPEN csr_bre;
209 FETCH csr_bre INTO l_currency_cd;
210 CLOSE csr_bre;
211 --
212 ELSIF p_entity_cd = 'RMR' THEN
213 -- pqh_rate_matrix_rates_f RMR
214 OPEN csr_rmr;
215 FETCH csr_rmr INTO l_currency_cd;
216 CLOSE csr_rmr;
217 --
218 ELSIF p_entity_cd = 'SSL' THEN
219 -- per_salary_survey_lines SSL
220 OPEN csr_ssl;
221 FETCH csr_ssl INTO l_currency_cd;
222 CLOSE csr_ssl;
223 --
224 END IF;
225
226
227 IF l_currency_cd IS NULL THEN
228
229 -- get currenct code for the business group
230 OPEN csr_bus_grp;
231 FETCH csr_bus_grp INTO l_currency_cd;
232 CLOSE csr_bus_grp;
233
234 END IF; -- currency for business group
235
236 hr_utility.set_location('Leaving:'||l_proc, 1000);
237
238 RETURN l_currency_cd;
239
240 EXCEPTION
241 WHEN OTHERS THEN
242 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
243 hr_utility.set_message_token('ROUTINE', l_proc);
244 hr_utility.set_message_token('REASON', SQLERRM);
245 raise ;
246 END;
247
248 ------------------------------------------------------------------------------------
249 --
250
251 FUNCTION convert_value
252 (
253 p_primary_key IN NUMBER,
254 p_entity_cd IN VARCHAR2,
255 p_business_group_id IN NUMBER,
256 p_unit_value IN NUMBER,
257 p_column_no IN NUMBER
258 ) RETURN number IS
259
260
261 /*
262 This function will return the converted amount
263 */
264
265 l_proc varchar2(72) := g_package||'convert_value';
266 l_converted_amt pqh_budget_versions.budget_unit1_value%TYPE;
267 l_unit1_type varchar2(30);
268 l_unit2_type varchar2(30);
269 l_unit3_type varchar2(30);
270 l_existing_curr_cd varchar2(150);
271
272 --
273 CURSOR csr_bvr_units IS
274 SELECT pst1.system_type_cd ,
275 pst2.system_type_cd ,
276 pst3.system_type_cd
277 FROM pqh_budgets bgt,
278 pqh_budget_versions bvr,
279 per_shared_types_vl pst1 ,
280 per_shared_types_vl pst2 ,
281 per_shared_types_vl pst3
282 WHERE bgt.budget_id = bvr.budget_id
283 AND bgt.budget_unit1_id = pst1.shared_type_id (+)
284 AND bgt.budget_unit2_id = pst2.shared_type_id (+)
285 AND bgt.budget_unit3_id = pst3.shared_type_id (+)
286 AND bvr.budget_version_id = p_primary_key ;
287 --
288 --
289 CURSOR csr_bdt_units IS
290 SELECT pst1.system_type_cd ,
291 pst2.system_type_cd ,
292 pst3.system_type_cd
293 FROM pqh_budgets bgt,
294 pqh_budget_versions bvr,
295 pqh_budget_details bdt,
296 per_shared_types_vl pst1 ,
297 per_shared_types_vl pst2 ,
298 per_shared_types_vl pst3
299 WHERE bgt.budget_id = bvr.budget_id
300 AND bvr.budget_version_id = bdt.budget_version_id
301 AND bgt.budget_unit1_id = pst1.shared_type_id (+)
302 AND bgt.budget_unit2_id = pst2.shared_type_id (+)
303 AND bgt.budget_unit3_id = pst3.shared_type_id (+)
304 AND bdt.budget_detail_id = p_primary_key;
305 --
306 --
307 CURSOR csr_bpr_units IS
308 SELECT pst1.system_type_cd ,
309 pst2.system_type_cd ,
310 pst3.system_type_cd
311 FROM pqh_budgets bgt,
312 pqh_budget_versions bvr,
313 pqh_budget_details bdt,
314 pqh_budget_periods bpr,
315 per_shared_types_vl pst1 ,
316 per_shared_types_vl pst2 ,
317 per_shared_types_vl pst3
318 WHERE bgt.budget_id = bvr.budget_id
319 AND bvr.budget_version_id = bdt.budget_version_id
320 AND bdt.budget_detail_id = bpr.budget_detail_id
321 AND bgt.budget_unit1_id = pst1.shared_type_id (+)
322 AND bgt.budget_unit2_id = pst2.shared_type_id (+)
323 AND bgt.budget_unit3_id = pst3.shared_type_id (+)
324 AND bpr.budget_period_id = p_primary_key;
325 --
326 --
327 CURSOR csr_bst_units IS
328 SELECT pst1.system_type_cd ,
329 pst2.system_type_cd ,
330 pst3.system_type_cd
331 FROM pqh_budgets bgt,
332 pqh_budget_versions bvr,
333 pqh_budget_details bdt,
334 pqh_budget_periods bpr,
335 pqh_budget_sets bst,
336 per_shared_types_vl pst1 ,
337 per_shared_types_vl pst2 ,
338 per_shared_types_vl pst3
339 WHERE bgt.budget_id = bvr.budget_id
340 AND bvr.budget_version_id = bdt.budget_version_id
341 AND bdt.budget_detail_id = bpr.budget_detail_id
342 AND bpr.budget_period_id = bst.budget_period_id
343 AND bgt.budget_unit1_id = pst1.shared_type_id (+)
344 AND bgt.budget_unit2_id = pst2.shared_type_id (+)
345 AND bgt.budget_unit3_id = pst3.shared_type_id (+)
346 AND bst.budget_set_id = p_primary_key;
347 --
348 --
349 CURSOR csr_wdt_units IS
350 SELECT pst1.system_type_cd ,
351 pst2.system_type_cd ,
352 pst3.system_type_cd
353 FROM pqh_budgets bgt,
354 pqh_worksheets wks,
355 pqh_worksheet_details wdt,
356 per_shared_types_vl pst1 ,
357 per_shared_types_vl pst2 ,
358 per_shared_types_vl pst3
359 WHERE bgt.budget_id = wks.budget_id
360 AND wks.worksheet_id = wdt.worksheet_id
361 AND bgt.budget_unit1_id = pst1.shared_type_id (+)
362 AND bgt.budget_unit2_id = pst2.shared_type_id (+)
363 AND bgt.budget_unit3_id = pst3.shared_type_id (+)
364 AND wdt.worksheet_detail_id = p_primary_key;
365 --
366 --
367 CURSOR csr_wpr_units IS
368 SELECT pst1.system_type_cd ,
369 pst2.system_type_cd ,
370 pst3.system_type_cd
371 FROM pqh_budgets bgt,
372 pqh_worksheets wks,
373 pqh_worksheet_details wdt,
374 pqh_worksheet_periods wpr,
375 per_shared_types_vl pst1 ,
376 per_shared_types_vl pst2 ,
377 per_shared_types_vl pst3
378 WHERE bgt.budget_id = wks.budget_id
379 AND wks.worksheet_id = wdt.worksheet_id
380 AND wdt.worksheet_detail_id = wpr.worksheet_detail_id
381 AND bgt.budget_unit1_id = pst1.shared_type_id (+)
382 AND bgt.budget_unit2_id = pst2.shared_type_id (+)
383 AND bgt.budget_unit3_id = pst3.shared_type_id (+)
384 AND wpr.worksheet_period_id = p_primary_key;
385 --
386 --
387 CURSOR csr_wst_units IS
388 SELECT pst1.system_type_cd ,
389 pst2.system_type_cd ,
390 pst3.system_type_cd
391 FROM pqh_budgets bgt,
392 pqh_worksheets wks,
393 pqh_worksheet_details wdt,
394 pqh_worksheet_periods wpr,
395 pqh_worksheet_budget_sets wst,
396 per_shared_types_vl pst1 ,
397 per_shared_types_vl pst2 ,
398 per_shared_types_vl pst3
399 WHERE bgt.budget_id = wks.budget_id
400 AND wks.worksheet_id = wdt.worksheet_id
401 AND wdt.worksheet_detail_id = wpr.worksheet_detail_id
402 AND wpr.worksheet_period_id = wst.worksheet_period_id
403 AND bgt.budget_unit1_id = pst1.shared_type_id (+)
404 AND bgt.budget_unit2_id = pst2.shared_type_id (+)
405 AND bgt.budget_unit3_id = pst3.shared_type_id (+)
406 AND wst.worksheet_budget_set_id = p_primary_key;
407 --
408 --
409 --
410 CURSOR csr_bre_unit IS
411 SELECT pst1.system_type_cd
412 FROM pqh_bdgt_pool_realloctions bre,
413 pqh_budget_pools bpl,
414 per_shared_types_vl pst1
415 WHERE bre.pool_id = bpl.pool_id
416 AND bpl.budget_unit_id = pst1.shared_type_id
417 AND bre.reallocation_id = p_primary_key;
418 --
419 --
420 CURSOR csr_rmr IS
421 SELECT crd.uom
422 FROM pqh_criteria_rate_defn crd,
423 pqh_rate_matrix_rates_f rmr
424 WHERE crd.criteria_rate_defn_id = rmr.criteria_rate_defn_id
425 AND rmr.rate_matrix_rate_id = p_primary_key
426 and rmr.effective_start_date = (
427 Select min(rmr1.effective_start_date)
428 From pqh_rate_matrix_rates_f rmr1
429 Where rmr1.rate_matrix_rate_id = p_primary_key );
430 --
431 Cursor csr_ssl is
432 Select stock_display_type
433 from per_salary_survey_lines
434 Where salary_survey_line_id = p_primary_key;
435 --
436
437 BEGIN
438
439 hr_utility.set_location('Entering: '||l_proc, 5);
440
441 -- Get the unit of measure
442
443 IF p_entity_cd = 'BVR' THEN
444 -- Budget Version Table BVR
445 OPEN csr_bvr_units;
446 FETCH csr_bvr_units INTO l_unit1_type, l_unit2_type, l_unit3_type ;
447 CLOSE csr_bvr_units;
448 --
449 ELSIF p_entity_cd = 'BDT' THEN
450 --
451 OPEN csr_bdt_units;
452 FETCH csr_bdt_units INTO l_unit1_type, l_unit2_type, l_unit3_type ;
453 CLOSE csr_bdt_units;
454 --
455 ELSIF p_entity_cd = 'BPR' THEN
456 --
457 OPEN csr_bpr_units;
458 FETCH csr_bpr_units INTO l_unit1_type, l_unit2_type, l_unit3_type ;
459 CLOSE csr_bpr_units;
460 --
461 ELSIF p_entity_cd = 'BST' THEN
462 --
463 OPEN csr_bst_units;
464 FETCH csr_bst_units INTO l_unit1_type, l_unit2_type, l_unit3_type ;
465 CLOSE csr_bst_units;
466 --
467 ELSIF p_entity_cd = 'WDT' THEN
468 --
469 OPEN csr_wdt_units;
470 FETCH csr_wdt_units INTO l_unit1_type, l_unit2_type, l_unit3_type ;
471 CLOSE csr_wdt_units;
472 --
473 ELSIF p_entity_cd = 'WPR' THEN
474 --
475 OPEN csr_wpr_units;
476 FETCH csr_wpr_units INTO l_unit1_type, l_unit2_type, l_unit3_type ;
477 CLOSE csr_wpr_units;
478 --
479 ELSIF p_entity_cd = 'WST' THEN
480 --
481 OPEN csr_wst_units;
482 FETCH csr_wst_units INTO l_unit1_type, l_unit2_type, l_unit3_type ;
483 CLOSE csr_wst_units;
484 --
485 ELSIF p_entity_cd = 'BRE' THEN
486 --
487 OPEN csr_bre_unit;
488 FETCH csr_bre_unit INTO l_unit1_type;
489 CLOSE csr_bre_unit;
490 --
491 ELSIF p_entity_cd = 'RMR' THEN
492 --
493 OPEN csr_rmr;
494 FETCH csr_rmr INTO l_unit1_type;
495 CLOSE csr_rmr;
496 --
497 ELSIF p_entity_cd = 'SSL' THEN
498 --
499 OPEN csr_ssl;
500 FETCH csr_ssl INTO l_unit1_type;
501 CLOSE csr_ssl;
502 --
503
504 END IF;
505
506 -- Get the currency code
507
508 l_existing_curr_cd :=
509 get_currency_cd
510 (
511 p_primary_key => p_primary_key,
512 p_entity_cd => p_entity_cd,
513 p_business_group_id => p_business_group_id
514 );
515
516
517 -- check if the value needs to be converted
518
519 IF ( p_column_no = 1 AND l_unit1_type = 'MONEY' ) THEN
520 -- convert
521 l_converted_amt :=
522 hr_currency_pkg.convert_amount
523 ( p_from_currency => l_existing_curr_cd,
524 p_to_currency => 'EUR',
525 p_conversion_date => sysdate,
526 p_amount => NVL(p_unit_value,0),
527 p_rate_type => null
528 );
529
530 ELSIF ( p_column_no = 1 AND l_unit1_type = 'M' ) THEN
531 -- convert
532 l_converted_amt :=
533 hr_currency_pkg.convert_amount
534 ( p_from_currency => l_existing_curr_cd,
535 p_to_currency => 'EUR',
536 p_conversion_date => sysdate,
537 p_amount => NVL(p_unit_value,0),
538 p_rate_type => null
539 );
540
541 ELSIF ( p_column_no = 1 AND p_entity_cd = 'SSL' AND l_unit1_type = 'MONEY_VALUE') THEN
542 -- convert for stock columns
543 l_converted_amt :=
544 hr_currency_pkg.convert_amount
545 ( p_from_currency => l_existing_curr_cd,
546 p_to_currency => 'EUR',
547 p_conversion_date => sysdate,
548 p_amount => NVL(p_unit_value,0),
549 p_rate_type => null
550 );
551 ELSIF ( p_column_no = 2 AND p_entity_cd = 'SSL' ) THEN
552 -- convert for monetary columns
553 l_converted_amt :=
554 hr_currency_pkg.convert_amount
555 ( p_from_currency => l_existing_curr_cd,
556 p_to_currency => 'EUR',
557 p_conversion_date => sysdate,
558 p_amount => NVL(p_unit_value,0),
559 p_rate_type => null
560 );
561 ELSIF ( p_column_no = 2 AND l_unit2_type = 'MONEY' ) THEN
562 -- convert
563 l_converted_amt :=
564 hr_currency_pkg.convert_amount
565 ( p_from_currency => l_existing_curr_cd,
566 p_to_currency => 'EUR',
567 p_conversion_date => sysdate,
568 p_amount => NVL(p_unit_value,0),
569 p_rate_type => null
570 );
571
572 ELSIF ( p_column_no = 3 AND l_unit3_type = 'MONEY' ) THEN
573 -- convert
574 l_converted_amt :=
575 hr_currency_pkg.convert_amount
576 ( p_from_currency => l_existing_curr_cd,
577 p_to_currency => 'EUR',
578 p_conversion_date => sysdate,
579 p_amount => NVL(p_unit_value,0),
580 p_rate_type => null
581 );
582 ELSIF ( p_entity_cd = 'PEC' ) THEN
583 -- convert
584 l_converted_amt :=
585 hr_currency_pkg.convert_amount
586 ( p_from_currency => l_existing_curr_cd,
587 p_to_currency => 'EUR',
588 p_conversion_date => sysdate,
589 p_amount => NVL(p_unit_value,0),
590 p_rate_type => null
591 );
592 ELSIF ( p_entity_cd = 'BRE' AND l_unit1_type = 'MONEY' ) THEN
593 -- convert
594 l_converted_amt :=
595 hr_currency_pkg.convert_amount
596 ( p_from_currency => l_existing_curr_cd,
597 p_to_currency => 'EUR',
598 p_conversion_date => sysdate,
599 p_amount => NVL(p_unit_value,0),
600 p_rate_type => null
601 );
602
603 ELSE
604 -- don't convert
605 l_converted_amt := p_unit_value;
606 END IF;
607
608
609
610
611
612
613 hr_utility.set_location('Leaving:'||l_proc, 1000);
614
615 RETURN l_converted_amt;
616
617
618 EXCEPTION
619 WHEN OTHERS THEN
620 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
621 hr_utility.set_message_token('ROUTINE', l_proc);
622 hr_utility.set_message_token('REASON', SQLERRM);
623 raise ;
624 END;
625 --
626
627 --
628
629
630 ------------------------------------------------------------------------------------
631
632
633
634 END; -- Package Body PQH_EFC