PACKAGE BODY: APPS.PAY_KR_SPAY_EFILE_FUN_PKG

Source

1 package body pay_kr_spay_efile_fun_pkg as
2 /*$Header: pykrspen.pkb 120.1 2006/12/19 11:07:02 ssutar noship $ */
3 
4    FUNCTION get_prev_emp_count (p_assignment_action_id IN NUMBER) RETURN NUMBER
5    IS
6       l_prev_emp_count NUMBER(4);
7       cursor csr_get_prev_emp_count is
8          select
9          nvl(count(fue.user_entity_id),0) prev_emp_count
10           from  ff_Archive_items fai
11                ,ff_user_entities fue
12          where fue.user_entity_id               = fai.user_entity_id
13          and fue.user_entity_name               = 'X_KR_PREV_BP_NUMBER'
14          and fai.context1                       = p_assignment_action_id
15          group by fai.context1;
16    BEGIN
17         open  csr_get_prev_emp_count;
18         fetch csr_get_prev_emp_count into l_prev_emp_count;
19         close csr_get_prev_emp_count;
20 
21         return l_prev_emp_count;
22    EXCEPTION
23      WHEN NO_DATA_FOUND THEN
24         l_prev_emp_count := 0;
25         return l_prev_emp_count;
26    END;
27    --
28    /***************************************************************************
29     Function returns separation pay amount after considering separation pension.
30     p_amount => Total Earning by working places.
31     If "Amount Expected" is present, return "Receivable Separation Pay"
32     Else directly return p_amount.
33     ***************************************************************************/
34     function get_sep_pay_amount (
35         p_assact      in number,
36         p_amount      in number
37     ) return number
38     is
39         l_amount_expected     number;
40         l_receivable_sep_pay  number;
41         l_return              number;
42 
43         cursor csr_sep_amounts is
44         select fai1.value      amount_expected,
45                fai2.value      receivable_sep_pay
46           from ff_Archive_items fai1,
47                ff_user_entities fue1,
48                ff_Archive_items fai2,
49                ff_user_entities fue2
50          where fue1.user_entity_name               = 'A_AMOUNT_EXPECTED_ASG_RUN'
51            and fue1.user_entity_id                 = fai1.user_entity_id
52            and fai1.context1                       = p_assact
53            and fue2.user_entity_name               = 'A_RECEIVABLE_SEPARATION_PAY_ASG_RUN'
54            and fue2.user_entity_id                 = fai2.user_entity_id
55            and fai2.context1                       = fai1.context1;
56     begin
57         l_amount_expected    := 0;
58         l_receivable_sep_pay := 0;
59 
60         open csr_sep_amounts;
61         fetch csr_sep_amounts into l_amount_expected, l_receivable_sep_pay;
62         close csr_sep_amounts;
63 
64         if l_amount_expected > 0 then
65             l_return := l_receivable_sep_pay;
66         else
67             l_return := p_amount;
68         end if;
69 
70         return l_return;
71 
72     end get_sep_pay_amount;
73 
74 end pay_kr_spay_efile_fun_pkg;