[Home] [Help]
PACKAGE BODY: APPS.PAY_CN_PAYSLIP
Source
1 PACKAGE BODY pay_cn_payslip AS
2 /* $Header: pycnpslp.pkb 120.1.12010000.2 2008/08/06 07:03:20 ubhat ship $ */
3
4 --
5 g_package_name VARCHAR2(100);
6 --
7 --------------------------------------------------------------------------
8 -- --
9 -- Name : CURRENT_AND_YTD_BALANCES --
10 -- Type : PROCEDURE --
11 -- Access : Public --
12 -- Description : Procedure to return PTD and YTD values of a balance --
13 -- for a given assignment_action_id --
14 -- --
15 -- Parameters : --
16 -- IN : p_prepaid_tag VARCHAR2 --
17 -- p_assignment_action_id NUMBER --
18 -- p_balance_name VARCHAR2 --
19 -- OUT : p_current_balance NUMBER --
20 -- p_ytd_balance NUMBER --
21 -- --
22 -- Change History : --
23 --------------------------------------------------------------------------
24 -- Rev# Date Userid Description --
25 --------------------------------------------------------------------------
26 -- 115.0 30-JUN-2003 bramajey Initial Version --
27 -- 115.1 03-JUL-2003 bramajey Replaced %TYPE with actual data type --
28 -- in parameter list. --
29 -- Changed parameters for cursor --
30 -- c_balance_value --
31 --------------------------------------------------------------------------
32 --
33 PROCEDURE current_and_ytd_balances
34 (
35 p_prepaid_tag IN VARCHAR2
36 ,p_assignment_action_id IN NUMBER
37 ,p_balance_name IN VARCHAR2
38 ,p_current_balance OUT NOCOPY NUMBER
39 ,p_ytd_balance OUT NOCOPY NUMBER
40 )
41 IS
42 --
43 l_curr_balance NUMBER;
44 l_ytd_balance NUMBER;
45 l_procedure VARCHAR2(100);
46 --
47 CURSOR c_balance_value
48 (
49 p_dimension_name VARCHAR2
50 )
51 IS
52 SELECT nvl(pay_balance_pkg.get_value(pdb.defined_balance_id,p_assignment_action_id),0)
53 FROM pay_balance_dimensions pbd
54 ,pay_defined_balances pdb
55 ,pay_balance_types pbt
56 WHERE pbt.balance_name = p_balance_name
57 AND pbd.dimension_name = p_dimension_name
58 AND pbt.balance_type_id = pdb.balance_type_id
59 AND pbd.balance_dimension_id = pdb.balance_dimension_id
60 AND pbt.legislation_code = 'CN';
61 --
62
63 --
64 BEGIN
65 --
66 --
67 l_procedure := g_package_name || '.current_and_ytd_balances';
68 hr_utility.set_location('Entering '||l_procedure,10);
69 --
70 hr_utility.set_location('Fetching value of ' ||p_balance_name|| '_ASG_PTD',20);
71
72 OPEN c_balance_value('_ASG_PTD');
73 FETCH c_balance_value INTO l_curr_balance;
74 CLOSE c_balance_value;
75
76 hr_utility.set_location('Fetching value of '||p_balance_name||'_ASG_PTD',30);
77
78 OPEN c_balance_value('_ASG_YTD');
79 FETCH c_balance_value INTO l_ytd_balance;
80 CLOSE c_balance_value;
81
82 p_current_balance := l_curr_balance;
83 p_ytd_balance := l_ytd_balance;
84
85 --
86 hr_utility.set_location('Leaving '||l_procedure,40);
87 --
88 EXCEPTION
89 --
90 WHEN others THEN
91 hr_utility.set_location('Error in '||l_procedure,50);
92 IF c_balance_value%ISOPEN THEN
93 CLOSE c_balance_value;
94 END IF;
95 RAISE;
96 --
97 END current_and_ytd_balances;
98
99 --------------------------------------------------------------------------
100 -- --
101 -- Name : BALANCE_TOTALS --
102 -- Type : PROCEDURE --
103 -- Access : Public --
104 -- Description : Procedure to return PTD and YTD values of the --
105 -- values of taxable_earnings,non_taxable_earnings, --
106 -- voluntary_deductions and statutory_deductions for a --
107 -- given assignment_action_id --
108 -- --
109 -- Parameters : --
110 -- IN : p_prepaid_tag VARCHAR2 --
111 -- p_assignment_action_id NUMBER --
112 -- OUT : p_taxable_earnings_current NUMBER --
113 -- p_non_taxable_earnings_current NUMBER --
114 -- p_voluntary_deductions_current NUMBER --
115 -- p_statutory_deductions_current NUMBER --
116 -- p_taxable_earnings_ytd NUMBER --
117 -- p_non_taxable_earnings_ytd NUMBER --
118 -- p_voluntary_deductions_ytd NUMBER --
119 -- p_statutory_deductions_ytd NUMBER --
120 -- --
121 -- Change History : --
122 --------------------------------------------------------------------------
123 -- Rev# Date Userid Description --
124 --------------------------------------------------------------------------
125 -- 115.0 30-JUN-2003 bramajey Initial Version --
126 -- 115.1 03-JUL-2003 bramajey Replaced %TYPE with actual data type --
127 -- in parameter list. --
128 -- Added EXCEPTION block. --
129 -- 115.2 03-SEP-2003 bramajey Added code to get the value of --
130 -- Severance Earnings, --
131 -- Special Payments Separate --
132 -- and Special Payments Spread balance --
133 -- and add them tp 'Taxable Earnings' --
134 -- 115.3 17-OCT-2003 vinaraya Added code to include the 'Direct --
135 -- Payments' balance values in 'Non --
136 -- Taxable Earnings' Balance values for --
137 -- bug 3198882 --
138 -- 115.4 05-Apr-2004 bramajey Added calls to --
139 -- 'CURRENT_AND_YTD_BALANCES' --
140 -- to fetch 'Retro Taxable --
141 -- Earnings','Retro Statutory --
142 -- Deductions' and 'Retro --
143 -- Special Payments' --
144 -- 115.5 05-Aug-2004 snekkala Added calls to --
145 -- 'CURRENT_AND_YTD_BALANCES' --
146 -- to fetch 'Variable Yearly Earnings' --
147 -- and 'Retro Variable Yearly Earnings' --
148 -- 115.6 05-Aug-2004 snekkala Removed the coding errors --
149 -- 115.7 20-Jul-2005 rpalli Bug4303538: Yearly Annual Bonus --
150 -- Implementation --
151 --------------------------------------------------------------------------
152 --
153
154 PROCEDURE balance_totals
155 (
156 p_prepaid_tag IN VARCHAR2
157 ,p_assignment_action_id IN NUMBER
158 ,p_taxable_earnings_current OUT NOCOPY NUMBER
159 ,p_non_taxable_earnings_current OUT NOCOPY NUMBER
160 ,p_voluntary_deductions_current OUT NOCOPY NUMBER
161 ,p_statutory_deductions_current OUT NOCOPY NUMBER
162 ,p_taxable_earnings_ytd OUT NOCOPY NUMBER
163 ,p_non_taxable_earnings_ytd OUT NOCOPY NUMBER
164 ,p_voluntary_deductions_ytd OUT NOCOPY NUMBER
165 ,p_statutory_deductions_ytd OUT NOCOPY NUMBER
166 )
167 IS
168 --
169 l_procedure VARCHAR2(100);
170 l_tot_taxable_earnings_current NUMBER;
171 l_taxable_earnings_current NUMBER;
172 l_sev_earnings_current NUMBER;
173 l_spec_separate_current NUMBER;
174 l_spec_spread_current NUMBER;
175 l_non_taxable_earnings_current NUMBER;
176 l_voluntary_deductions_current NUMBER;
177 l_statutory_deductions_current NUMBER;
178 l_tot_taxable_earnings_ytd NUMBER;
179 l_taxable_earnings_ytd NUMBER;
180 l_sev_earnings_ytd NUMBER;
181 l_spec_separate_ytd NUMBER;
182 l_spec_spread_ytd NUMBER;
183 l_non_taxable_earnings_ytd NUMBER;
184 l_voluntary_deductions_ytd NUMBER;
185 l_statutory_deductions_ytd NUMBER;
186
187 -- Bug 4303538
188 -- Declare Variables
189 l_annual_bonus_current NUMBER;
190 l_annual_bonus_ytd NUMBER;
191 l_retro_ann_bonus_current NUMBER;
192 l_retro_ann_bonus_ytd NUMBER;
193
194 -- Bug 3290973
195 -- Declare Variables
196 l_retro_tax_earnings_current NUMBER;
197 l_retro_tax_earnings_ytd NUMBER;
198 l_retro_stat_ded_current NUMBER;
199 l_retro_stat_ded_ytd NUMBER;
200 l_retro_spec_pay_current NUMBER;
201 l_retro_spec_pay_ytd NUMBER;
202 l_tot_stat_ded_current NUMBER;
203 l_tot_stat_ded_ytd NUMBER;
204 --
205 -- Bug 3812288
206 -- Declare Variables
207 --
208 l_var_yrly_pay_current NUMBER;
209 l_var_yrly_pay_ytd NUMBER;
210 l_retro_var_yrly_current NUMBER;
211 l_retro_var_yrly_ytd NUMBER;
212
213 /************* Bug 3198882: Changes Start *******************************************/
214 /************* Local variables to store 'Direct Payment' PTD and YTD balances *********/
215
216 l_dir_payments_current NUMBER;
217 l_dir_payments_ytd NUMBER;
218 l_tot_non_taxable_earnings_cur NUMBER;
219 l_tot_non_taxable_earnings_ytd NUMBER;
220
221 --
222 BEGIN
223 --
224 l_procedure := g_package_name || '.balance_totals';
225 hr_utility.set_location('Entering '||l_procedure,10);
226
227 -- Call procedure to get Current and YTD balances for Payment Summary Totals
228
229 hr_utility.set_location('Fetching value of Taxable Earnings balance',20);
230
231 current_and_ytd_balances (
232 p_prepaid_tag => p_prepaid_tag
233 ,p_assignment_action_id => p_assignment_action_id
234 ,p_balance_name => 'Taxable Earnings'
235 ,p_current_balance => l_taxable_earnings_current
236 ,p_ytd_balance => l_taxable_earnings_ytd
237 );
238
239
240 -- Bug 3116630 starts
241 -- The following balances should be added up to 'Taxable Earnings' Balance
242 --
243 hr_utility.set_location('Fetching value of Severance Earnings balance',22);
244
245 current_and_ytd_balances (
246 p_prepaid_tag => p_prepaid_tag
247 ,p_assignment_action_id => p_assignment_action_id
248 ,p_balance_name => 'Severance Earnings'
249 ,p_current_balance => l_sev_earnings_current
250 ,p_ytd_balance => l_sev_earnings_ytd
251 );
252
253 -- Bug 4303538 starts
254 -- The following balance should be added up to 'Taxable Earnings' Balance
255 --
256 hr_utility.set_location('Fetching value of Annual Bonus balance',24);
257
258 current_and_ytd_balances (
259 p_prepaid_tag => p_prepaid_tag
260 ,p_assignment_action_id => p_assignment_action_id
261 ,p_balance_name => 'Annual Bonus'
262 ,p_current_balance => l_annual_bonus_current
263 ,p_ytd_balance => l_annual_bonus_ytd
264 );
265 -- Bug 4303538 ends
266
267 hr_utility.set_location('Fetching value of Special Payments Separate balance',25);
268
269 current_and_ytd_balances (
270 p_prepaid_tag => p_prepaid_tag
271 ,p_assignment_action_id => p_assignment_action_id
272 ,p_balance_name => 'Special Payments Separate'
273 ,p_current_balance => l_spec_separate_current
274 ,p_ytd_balance => l_spec_separate_ytd
275 );
276
277
278 hr_utility.set_location('Fetching value of Special Payments Spread balance',27);
279
280 current_and_ytd_balances (
281 p_prepaid_tag => p_prepaid_tag
282 ,p_assignment_action_id => p_assignment_action_id
283 ,p_balance_name => 'Special Payments Spread'
284 ,p_current_balance => l_spec_spread_current
285 ,p_ytd_balance => l_spec_spread_ytd
286 );
287
288 -- Bug 3290973 Starts
289 -- Fetch 'Retro Taxable Earnings' and 'Retro Special Payments' Balances and them
290 -- to 'Taxable Earnings' Balance
291
292 hr_utility.set_location('Fetching value of Retro Taxable Earnings balance',28);
293
294 current_and_ytd_balances (
295 p_prepaid_tag => p_prepaid_tag
296 ,p_assignment_action_id => p_assignment_action_id
297 ,p_balance_name => 'Retro Taxable Earnings'
298 ,p_current_balance => l_retro_tax_earnings_current
299 ,p_ytd_balance => l_retro_tax_earnings_ytd
300 );
301
302 hr_utility.set_location('Fetching value of Retro Special Payments balance',28);
303
304 current_and_ytd_balances (
305 p_prepaid_tag => p_prepaid_tag
306 ,p_assignment_action_id => p_assignment_action_id
307 ,p_balance_name => 'Retro Special Payments'
308 ,p_current_balance => l_retro_spec_pay_current
309 ,p_ytd_balance => l_retro_spec_pay_ytd
310 );
311
312 -- Bug 4303538 starts
313 -- The following balance should be added up to 'Taxable Earnings' Balance
314 --
315 hr_utility.set_location('Fetching value of Retro Annual Bonus balance',28);
316
317 current_and_ytd_balances (
318 p_prepaid_tag => p_prepaid_tag
319 ,p_assignment_action_id => p_assignment_action_id
320 ,p_balance_name => 'Retro Annual Bonus'
321 ,p_current_balance => l_retro_ann_bonus_current
325
322 ,p_ytd_balance => l_retro_ann_bonus_ytd
323 );
324 -- Bug 4303538 ends
326 hr_utility.set_location('Fetching value of Variable Yearly Earnings balance',28);
327
328 --
329 -- Bug 3812288 Changes start
330 --
331 current_and_ytd_balances (
332 p_prepaid_tag => p_prepaid_tag
333 ,p_assignment_action_id => p_assignment_action_id
334 ,p_balance_name => 'Variable Yearly Earnings'
335 ,p_current_balance => l_var_yrly_pay_current
336 ,p_ytd_balance => l_var_yrly_pay_ytd
337 );
338
339 hr_utility.set_location('Fetching value of Retro Variable yearly Earnings balance',28);
340
341 current_and_ytd_balances (
342 p_prepaid_tag => p_prepaid_tag
343 ,p_assignment_action_id => p_assignment_action_id
344 ,p_balance_name => 'Retro Variable Yearly Earnings'
345 ,p_current_balance => l_retro_var_yrly_current
346 ,p_ytd_balance => l_retro_var_yrly_ytd
347 );
348
349
350 -- Add the balances
351 --
352 l_tot_taxable_earnings_current := l_taxable_earnings_current + l_sev_earnings_current
353 + l_spec_separate_current + l_spec_spread_current
354 + l_retro_tax_earnings_current + l_retro_spec_pay_current
355 + l_var_yrly_pay_current + l_retro_var_yrly_current
356 + l_annual_bonus_current + l_retro_ann_bonus_current;
357
358 l_tot_taxable_earnings_ytd := l_taxable_earnings_ytd + l_sev_earnings_ytd
359 + l_spec_separate_ytd + l_spec_spread_ytd
360 + l_retro_tax_earnings_ytd + l_retro_spec_pay_ytd
361 + l_var_yrly_pay_ytd + l_retro_var_yrly_ytd
362 + l_annual_bonus_ytd + l_retro_ann_bonus_ytd;
363
364
365 --
366 -- Bug 3812288 Changes end
367 --
368 -- Bug 3290973 ends
369
370 -- Bug 3116630 ends
371
372 hr_utility.set_location('Fetching value of Non Taxable Earnings balance',30);
373
374 current_and_ytd_balances (
375 p_prepaid_tag => p_prepaid_tag
376 ,p_assignment_action_id => p_assignment_action_id
377 ,p_balance_name => 'Non Taxable Earnings'
378 ,p_current_balance => l_non_taxable_earnings_current
379 ,p_ytd_balance => l_non_taxable_earnings_ytd
380 );
381
382 -- Bug 3198882 : Changes Start
383 -- Included the 'Direct Payments' PTD and YTD balance values in 'Non Taxable Earnings'
384 -- PTD and YTD balances for archival
385
386 hr_utility.set_location('Fetching Value of Direct payments balance',35);
387
388 current_and_ytd_balances (
389 p_prepaid_tag => p_prepaid_tag
390 ,p_assignment_action_id => p_assignment_action_id
391 ,p_balance_name => 'Direct Payments'
392 ,p_current_balance => l_dir_payments_current
393 ,p_ytd_balance => l_dir_payments_ytd
394 );
395
396 -- Add the 'Direct Payments' Balance Values to 'Non Taxable Earnings' balance values
397
398 l_tot_non_taxable_earnings_cur := l_non_taxable_earnings_current + l_dir_payments_current;
399 l_tot_non_taxable_earnings_ytd := l_non_taxable_earnings_ytd + l_dir_payments_ytd;
400
401 -- Bug 3198882 : Changes End
402
403 hr_utility.set_location('Fetching value of Voluntary Deductions balance',40);
404
405
406 current_and_ytd_balances (
407 p_prepaid_tag => p_prepaid_tag
408 ,p_assignment_action_id => p_assignment_action_id
409 ,p_balance_name => 'Voluntary Deductions'
410 ,p_current_balance => l_voluntary_deductions_current
411 ,p_ytd_balance => l_voluntary_deductions_ytd
412 );
413
414 hr_utility.set_location('Fetching value of Statutory Deductions balance',50);
415
416 current_and_ytd_balances (
417 p_prepaid_tag => p_prepaid_tag
418 ,p_assignment_action_id => p_assignment_action_id
419 ,p_balance_name => 'Statutory Deductions'
420 ,p_current_balance => l_statutory_deductions_current
421 ,p_ytd_balance => l_statutory_deductions_ytd
422 );
423
424 -- Bug 3290973 Starts
425 -- Fetch 'Retro Statutory Deductions' and it
426 -- to 'Statutory Deductions' Balance
427
428 hr_utility.set_location('Fetching value of Retro Statutory Deductions balance',52);
429
430 current_and_ytd_balances (
431 p_prepaid_tag => p_prepaid_tag
435 ,p_ytd_balance => l_retro_stat_ded_ytd
432 ,p_assignment_action_id => p_assignment_action_id
433 ,p_balance_name => 'Retro Statutory Deductions'
434 ,p_current_balance => l_retro_stat_ded_current
436 );
437
438 -- Add the Balances
439
440 l_tot_stat_ded_current := l_statutory_deductions_current + l_retro_stat_ded_current;
441 l_tot_stat_ded_ytd := l_statutory_deductions_ytd + l_retro_stat_ded_ytd;
442
443
444 p_taxable_earnings_current := l_tot_taxable_earnings_current;
445 p_voluntary_deductions_current := l_voluntary_deductions_current;
446 p_statutory_deductions_current := l_tot_stat_ded_current;
447 p_taxable_earnings_ytd := l_tot_taxable_earnings_ytd;
448 p_voluntary_deductions_ytd := l_voluntary_deductions_ytd;
449 p_statutory_deductions_ytd := l_tot_stat_ded_ytd;
450
451 -- Bug 3290973 ends
452
453 -- Bug 3198882 : Changes Start
454
455 p_non_taxable_earnings_current := l_tot_non_taxable_earnings_cur;
456 p_non_taxable_earnings_ytd := l_tot_non_taxable_earnings_ytd;
457
458 -- Bug 3198882 : Changes End
459
460 hr_utility.set_location('Leaving '||l_procedure,55);
461
462 --
463 EXCEPTION
464 --
465 WHEN others THEN
466 hr_utility.set_location('Error in '||l_procedure,60);
467 RAISE;
468 --
469 END balance_totals;
470
471
472 --------------------------------------------------------------------------
473 -- --
474 -- Name : GET_RUN_RESULT_VALUE --
475 -- Type : PROCEDURE --
476 -- Access : Public --
477 -- Description : Procedure to return run_result_value of a given --
478 -- element name and input value name for a given --
479 -- payroll assignment_action_id --
480 -- --
481 -- Parameters : --
482 -- IN : --
483 -- p_assignment_action_id NUMBER --
484 -- p_element_name VARCHAR2 --
485 -- p_input_value_name VARCHAR2 --
486 -- OUT : p_value NUMBER --
487 -- --
488 --------------------------------------------------------------------------
489 -- Rev# Date Userid Description --
490 --------------------------------------------------------------------------
491 -- 115.0 03-SEP-2003 bramajey Initial Version --
492 -- 115.1 14-Sep-2004 snekkala Added condition to check if --
493 -- run result value exists or not --
494 -- 115.2 28-May-2008 dduvvuri Added fnd_number.canonical_to_number
495 -- in the cursor before selecting the
496 -- run result value.
497 --------------------------------------------------------------------------
498 --
499 PROCEDURE get_run_result_value
500 (
501 p_assignment_action_id IN NUMBER
502 ,p_element_name IN VARCHAR2
503 ,p_input_value_name IN VARCHAR2
504 ,p_value OUT NOCOPY NUMBER
505 )
506 IS
507 --
508 CURSOR csr_value
509 IS
510 --
511 SELECT fnd_number.canonical_to_number(prrv.result_value)
512 FROM pay_assignment_actions paa
513 ,pay_payroll_actions ppa
514 ,pay_element_types_f pet
515 ,pay_input_values_f piv
516 ,pay_run_results prr
517 ,pay_run_result_values prrv
518 WHERE paa.assignment_action_id = p_assignment_action_id
519 AND ppa.payroll_action_id = paa.payroll_action_id
520 AND ppa.action_type IN ('Q','R')
521 AND ppa.action_status = 'C'
522 AND paa.assignment_action_id = prr.assignment_action_id
523 AND pet.element_name = p_element_name
524 AND pet.legislation_code = 'CN'
525 AND pet.element_type_id = prr.element_type_id
526 AND prr.run_result_id = prrv.run_result_id
527 AND pet.element_type_id = piv.element_type_id
528 AND piv.name = p_input_value_name
529 AND piv.input_value_id = prrv.input_value_id
530 AND ppa.effective_date BETWEEN piv.effective_start_date
531 AND piv.effective_end_date
532 AND ppa.effective_date BETWEEN pet.effective_start_date
533 AND pet.effective_end_date;
534 --
535 l_procedure VARCHAR2(100);
536 l_value NUMBER;
537 --
538 BEGIN
539 --
540 l_procedure := g_package_name || '.get_value';
541 hr_utility.set_location('Entering '||l_procedure,10);
542
543 OPEN csr_value;
544
545 FETCH csr_value
549 -- Sparse matrix
546 INTO l_value;
547 --
548 -- Bug 3771856 Changes start
550 --
551 IF csr_value%NOTFOUND THEN
552 l_value:=0;
553 END IF;
554 --
555 -- Bug 3771856 Changes end
556 --
557 CLOSE csr_value;
558
559 p_value := l_value;
560
561 hr_utility.set_location('Leaving '||l_procedure,10);
562 --
563 EXCEPTION
564 --
565 WHEN others THEN
566 IF csr_value%ISOPEN THEN
567 --
568 CLOSE csr_value;
569 --
570 END IF;
571 --
572 END get_run_result_value;
573 --
574
575
576
577 --------------------------------------------------------------------------
578 -- --
579 -- Name : GET_EXCHANGE_RATE --
580 -- Type : FUNCTION --
581 -- Access : Public --
582 -- Description : Function to return the exchange rate for a given --
583 -- FROM and TO currency --
584 -- --
585 -- Parameters : --
586 -- IN : p_from_currency VARCHAR2 --
587 -- p_to_currency VARCHAR2 --
588 -- p_eff_date DATE --
589 -- p_business_group_id NUMBER --
590 -- OUT : N/A --
591 -- RETURN : NUMBER --
592 -- --
593 -- Change History : --
594 --------------------------------------------------------------------------
595 -- Rev# Date Userid Description --
596 --------------------------------------------------------------------------
597 -- 115.0 30-JUN-2003 bramajey Initial Version --
598 -- 115.1 03-JUL-2003 bramajey Replaced %TYPE with actual data type --
599 -- in parameter list. --
600 --------------------------------------------------------------------------
601 --
602 FUNCTION get_exchange_rate
603 (
604 p_from_currency IN VARCHAR2
605 ,p_to_currency IN VARCHAR2
606 ,p_eff_date IN DATE
607 ,p_business_group_id IN NUMBER
608 )
609 RETURN NUMBER IS
610 --
611 --
612 CURSOR c_rate
613 IS
614 SELECT gdr.conversion_rate
615 FROM gl_daily_rates gdr
616 ,gl_daily_conversion_types gdct
617 WHERE gdr.conversion_type = gdct.conversion_type
618 AND gdr.from_currency = p_from_currency
619 AND gdr.to_currency = p_to_currency
620 AND gdr.conversion_date = p_eff_date
621 AND gdct.user_conversion_type = (
622 SELECT puci.value
623 FROM pay_user_column_instances_f puci
624 ,pay_user_rows_f pur
625 ,pay_user_columns puc
626 ,pay_user_tables put
627 WHERE puci.user_row_id = pur.user_row_id
628 AND puci.user_column_id = puc.user_column_id
629 AND pur.user_table_id = put.user_table_id
630 AND puc.user_table_id = put.user_table_id
631 AND puci.business_group_id = p_business_group_id
632 AND pur.ROW_LOW_RANGE_OR_NAME = 'PAY'
633 AND put.user_table_name = 'EXCHANGE_RATE_TYPES'
634 );
635 --
636 l_rate NUMBER;
637 l_procedure VARCHAR2(100);
638 --
639 BEGIN
640 --
641 l_procedure := g_package_name || '.get_exchange_rate';
642 hr_utility.set_location('Entering '||l_procedure,10);
643
644 IF p_from_currency <> p_to_currency THEN
645 --
646 hr_utility.set_location('Opening cursor c_rate ', 20);
647
648 OPEN c_rate;
649 FETCH c_rate INTO l_rate;
650 IF c_rate%NOTFOUND THEN
651 --
652 l_rate := null;
653 --
654 END IF;
655 CLOSE c_rate;
656
657 hr_utility.set_location('Closing cursor c_rate ',30);
658 --
659 ELSE
660 --
661 l_rate := null;
662 --
663 END IF;
664 RETURN(l_rate);
665
666 hr_utility.set_location('Leaving '||l_procedure,40);
667 --
668 EXCEPTION
669 --
670 WHEN others THEN
671 hr_utility.set_location('Error in '||l_procedure,50);
672 IF c_rate%ISOPEN THEN
673 CLOSE c_rate;
674 END IF;
675 RAISE;
676 --
677 END get_exchange_rate;
678
679 --------------------------------------------------------------------------
680 -- --
681 -- Name : BUSINESS_CURRENCY_CODE --
682 -- Type : FUNCTION --
683 -- Access : Public --
684 -- Description : Function to return business_currency_code for --
685 -- given business_group_id --
686 -- --
687 -- Parameters : --
688 -- IN : p_business_group_id NUMBER --
689 -- OUT : N/A --
690 -- RETURN : VARCHAR2 --
691 --------------------------------------------------------------------------
692 --
693 FUNCTION business_currency_code
694 (
695 p_business_group_id IN NUMBER
696 )
697 RETURN VARCHAR2
698 IS
699 --
700 l_currency_code VARCHAR2(15);
701 l_procedure VARCHAR2(100);
702
703 --
704 CURSOR c_currency_code
705 IS
706 SELECT fcu.currency_code
707 FROM hr_organization_information hoi,
708 hr_organization_units hou,
709 fnd_currencies fcu
710 WHERE hou.business_group_id = p_business_group_id
711 AND hou.organization_id = hoi.organization_id
712 AND hoi.org_information_context = 'Business Group Information'
713 AND fcu.issuing_territory_code = hoi.org_information9;
714 --
715 --
716 BEGIN
717 --
718 l_procedure := g_package_name || '.business_currency_code';
719 hr_utility.set_location('Entering '||l_procedure,10);
720
721 hr_utility.set_location('Opening cursor c_currency_code',20);
722
723 OPEN c_currency_code ;
724 FETCH c_currency_code INTO l_currency_code;
725 CLOSE c_currency_code;
726
727 hr_utility.set_location('Closing cursor c_currency_code',30);
728
729 hr_utility.set_location('Leaving '||l_procedure,40);
730
731 RETURN l_currency_code;
732 --
733 EXCEPTION
734 --
735 WHEN others THEN
736 hr_utility.set_location('Error in '||l_procedure,50);
737 IF c_currency_code%ISOPEN THEN
738 CLOSE c_currency_code;
739 END IF;
740 RAISE;
741 --
742 END business_currency_code;
743
744 BEGIN
745 g_package_name := 'pay_cn_payslip';
746 --
747 END pay_cn_payslip;