[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_BONUS_PKG
Source
1 PACKAGE BODY pay_in_bonus_pkg AS
2 /* $Header: pyinbonp.pkb 120.1 2006/04/27 03:52:10 rpalli noship $ */
3
4 --
5 -- Globals
6 --
7 g_package constant VARCHAR2(100) := 'pay_in_bonus_pkg.' ;
8 g_debug BOOLEAN ;
9
10 --------------------------------------------------------------------------
11 -- --
12 -- Name : GET_DATE_EARNED --
13 -- Type : FUNCTION --
14 -- Access : Public --
15 -- Description : Function to return earn date --
16 -- --
17 -- Parameters : --
18 -- IN : p_date_earned DATE --
19 -- RETURN : VARCHAR2 --
20 --------------------------------------------------------------------------
21 -- Change History : --
22 --------------------------------------------------------------------------
23 -- Rev# Date Userid Bug Description --
24 --------------------------------------------------------------------------
25 -- 1.0 29-Sep-2004 abhjain 3826333 Created --
26 --------------------------------------------------------------------------
27 FUNCTION get_date_earned
28 (p_date_earned IN DATE)
29 RETURN DATE
30 IS
31 l_procedure VARCHAR2(100);
32 --
33 BEGIN
34 g_debug := hr_utility.debug_enabled ;
35 l_procedure := g_package || 'get_date_earned' ;
36 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
37
38 if g_debug then
39 pay_in_utils.trace('******************************','********************');
40 pay_in_utils.trace('p_date_earned : ',p_date_earned);
41 pay_in_utils.trace('******************************','********************');
42 end if;
43
44 RETURN trunc(p_date_earned);
45 END get_date_earned;
46
47
48 --------------------------------------------------------------------------
49 -- --
50 -- Name : GET_BALANCE_VALUE --
51 -- Type : FUNCTION --
52 -- Access : Public --
53 -- Description : Function to balance value for bonus --
54 -- --
55 -- Parameters : --
56 -- IN : p_assignment_id number --
57 -- : p_date_earned date --
58 -- OUT : p_last_earn_date date --
59 -- RETURN : NUMBER --
60 --------------------------------------------------------------------------
61 -- Change History : --
62 --------------------------------------------------------------------------
63 -- Rev# Date Userid Bug Description --
64 --------------------------------------------------------------------------
65 -- 1.0 29-Sep-2004 abhjain 3826333 Created --
66 --------------------------------------------------------------------------
67 FUNCTION get_balance_value
68 (p_date_earned IN DATE
69 ,p_assignment_id IN NUMBER
70 ,p_last_earn_date OUT NOCOPY DATE)
71 RETURN NUMBER
72 IS
73 CURSOR c_defined_balance_id IS
74 SELECT pdb.defined_balance_id
75 FROM pay_defined_balances pdb
76 ,pay_balance_types pbt
77 ,pay_balance_dimensions pbd
78 WHERE pdb.legislation_code = 'IN'
79 AND pbt.legislation_code = 'IN'
80 AND pbd.legislation_code = 'IN'
81 AND pdb.balance_type_id = pbt.balance_type_id
82 AND pbt.balance_name = 'F16 Gross Salary less Allowances'
83 AND pdb.balance_dimension_id = pbd.balance_dimension_id
84 AND pbd.database_item_suffix = '_ASG_RUN';
85
86 CURSOR c_assign_action(p_fy_start_date DATE) IS
87 select paa1.assignment_action_id
88 ,ppa1.date_earned
89 from pay_assignment_actions paa1
90 ,pay_payroll_actions ppa1
91 where ppa1.payroll_action_id = paa1.payroll_action_id
92 and assignment_action_id = (SELECT max(paa.assignment_action_id)
93 FROM pay_assignment_actions paa
94 ,pay_payroll_actions ppa
95 WHERE paa.assignment_id = p_assignment_id
96 AND ppa.payroll_action_id = paa.payroll_action_id
97 AND ppa.date_earned BETWEEN p_fy_start_date
98 AND p_date_earned
99 AND ppa.action_type IN ('Q','R','B','V','I')
100 AND ppa.action_status = 'C'
101 AND paa.action_status = 'C') ;
102
103 l_assignment_action_id NUMBER;
104 l_balance_value NUMBER := 0;
105 l_defined_balance_id NUMBER;
106 l_last_earn_date DATE;
107 l_fy_start_date DATE;
108 l_procedure VARCHAR2(100);
109 l_message VARCHAR2(255);
110
111 BEGIN
112
113 g_debug := hr_utility.debug_enabled ;
114 l_procedure := g_package || 'get_date_earned' ;
115 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
116
117 if g_debug then
118 pay_in_utils.trace('******************************','********************');
119 pay_in_utils.trace('p_date_earned : ',p_date_earned);
120 pay_in_utils.trace('p_assignment_id : ',p_assignment_id);
121 pay_in_utils.trace('******************************','********************');
122 end if;
123
124 IF to_number(to_char(p_date_earned,'mm')) > 3 THEN
125 l_fy_start_date := to_date('01-04-'||to_char(p_date_earned,'YYYY'),'dd-mm-yyyy');
126 ELSE
127 l_fy_start_date := add_months(to_date('01-04-'||to_char(p_date_earned,'YYYY'),'dd-mm-yyyy'),-12);
128 END IF;
129
130 if g_debug then
131 pay_in_utils.trace('l_fy_start_date : ',l_fy_start_date);
132 end if;
133
134 OPEN c_assign_action(l_fy_start_date);
135 FETCH c_assign_action INTO l_assignment_action_id, l_last_earn_date;
136 CLOSE c_assign_action;
137
138 if g_debug then
139 pay_in_utils.trace('l_assignment_action_id : ',l_assignment_action_id);
140 pay_in_utils.trace('l_last_earn_date : ',l_last_earn_date);
141 end if;
142
143 OPEN c_defined_balance_id;
144 FETCH c_defined_balance_id INTO l_defined_balance_id;
145 CLOSE c_defined_balance_id;
146
147 if g_debug then
148 pay_in_utils.trace('l_defined_balance_id : ',l_defined_balance_id);
149 end if;
150
151 l_balance_value := pay_balance_pkg.get_value(p_defined_balance_id => l_defined_balance_id
152 ,p_assignment_action_id => l_assignment_action_id);
153
154 if g_debug then
155 pay_in_utils.trace('******************************','********************');
156 pay_in_utils.trace('l_balance_value : ',l_balance_value);
157 pay_in_utils.trace('l_last_earn_date : ',l_last_earn_date);
158 pay_in_utils.trace('******************************','********************');
159 end if;
160
161 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
162
163 p_last_earn_date := NVL(l_last_earn_date, to_date('31-12-4712','dd-mm-yyyy'));
164 RETURN l_balance_value;
165
166 Exception
167 when others then
168 p_last_earn_date := to_date('31-12-4712','dd-mm-yyyy');
169 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
170 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
171 pay_in_utils.trace(l_message,l_procedure);
172 RETURN 0;
173
174 END get_balance_value;
175
176
177 END pay_in_bonus_pkg;