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