[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_PAYSLIP_UTILS
Source
1 PACKAGE BODY pay_in_payslip_utils AS
2 /* $Header: pyinpslp.pkb 120.2 2006/05/27 18:40:33 statkar noship $ */
3
4 --
5 g_package VARCHAR2(100);
6 g_debug BOOLEAN ;
7 --
8 --------------------------------------------------------------------------
9 -- --
10 -- Name : YTD_BALANCE --
11 -- Type : PROCEDURE --
12 -- Access : Public --
13 -- Description : Procedure to YTD values of a balance --
14 -- for a given assignment_action_id --
15 -- --
16 -- Parameters : --
17 -- IN : p_assignment_action_id NUMBER --
18 -- p_balance_name VARCHAR2 --
19 -- OUT : p_ytd_balance NUMBER --
20 -- --
21 --------------------------------------------------------------------------
22 --
23 PROCEDURE ytd_balance
24 (
25 p_assignment_action_id IN NUMBER
26 ,p_balance_name IN VARCHAR2
27 ,p_ytd_balance OUT NOCOPY NUMBER
28 )
29 IS
30 --
31 l_ytd_balance NUMBER;
32 l_procedure VARCHAR2(100);
33 l_message VARCHAR2(250);
34 --
35 CURSOR c_balance_value
36 (
37 p_dimension_name VARCHAR2
38 )
39 IS
40 SELECT nvl(pay_balance_pkg.get_value(pdb.defined_balance_id,p_assignment_action_id),0)
41 FROM pay_balance_dimensions pbd
42 ,pay_defined_balances pdb
43 ,pay_balance_types pbt
44 WHERE pbt.balance_name = p_balance_name
45 AND pbd.dimension_name = p_dimension_name
46 AND pbt.balance_type_id = pdb.balance_type_id
47 AND pbd.balance_dimension_id = pdb.balance_dimension_id
48 AND pbt.legislation_code = 'IN';
49 --
50
51 --
52 BEGIN
53 --
54 l_procedure := g_package||'ytd_balance';
55 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
56
57 IF g_debug THEN
58 pay_in_utils.trace ('**************************************************','********************');
59 pay_in_utils.trace ('p_assignment_action_id',p_assignment_action_id);
60 pay_in_utils.trace ('p_balance_name',p_balance_name);
61 pay_in_utils.trace ('**************************************************','********************');
62 END IF;
63 pay_in_utils.trace('Fetching value of ',p_balance_name||'_ASG_RUN');
64 pay_in_utils.set_location(g_debug,l_procedure,20);
65
66 OPEN c_balance_value('_ASG_RUN');
67 FETCH c_balance_value INTO l_ytd_balance;
68 CLOSE c_balance_value;
69
70 pay_in_utils.trace(p_balance_name||'_ASG_RUN ',l_ytd_balance);
71 pay_in_utils.set_location(g_debug,l_procedure,30);
72 p_ytd_balance := l_ytd_balance;
73
74 IF g_debug THEN
75 pay_in_utils.trace ('**************************************************','********************');
76 pay_in_utils.trace ('p_ytd_balance',p_ytd_balance);
77 pay_in_utils.trace ('**************************************************','********************');
78 END IF;
79
80 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
81 --
82 EXCEPTION
83 WHEN OTHERS THEN
84 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
85 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,40);
86 pay_in_utils.trace(l_message,l_procedure);
87
88 IF c_balance_value%ISOPEN THEN
89 CLOSE c_balance_value;
90 END IF;
91 RAISE;
92 --
93 END ytd_balance;
94
95 --------------------------------------------------------------------------
96 -- --
97 -- Name : BALANCE_TOTALS --
98 -- Type : PROCEDURE --
99 -- Access : Public --
100 -- Description : Procedure to return PTD and YTD values of the --
101 -- values of taxable_earnings,non_taxable_earnings, --
102 -- voluntary_deductions and statutory_deductions for a --
103 -- given assignment_action_id --
104 -- --
105 -- Parameters : --
106 -- IN : p_assignment_action_id NUMBER --
107 -- OUT : p_gross_salary_les_alw_ytd NUMBER --
108 -- p_deductions_us_16_ytd NUMBER --
109 -- p_gross_salary_ytd NUMBER --
110 -- p_deductions_chapter_VIa_ytd NUMBER --
111 -- p_total_income_ytd NUMBER --
112 -- p_tax_on_total_income_ytd NUMBER --
113 -- p_rebates_us_88_ytd NUMBER --
114 -- p_income_tax_ytd NUMBER --
115 -- p_statutory_deductions_ytd NUMBER --
116 -- --
117 --------------------------------------------------------------------------
118 --
119
120 PROCEDURE balance_totals
121 (
122 p_assignment_action_id IN NUMBER
123 ,p_gross_salary_les_alw_ytd OUT NOCOPY NUMBER
124 ,p_deductions_us_16_ytd OUT NOCOPY NUMBER
125 ,p_gross_salary_ytd OUT NOCOPY NUMBER
126 ,p_deductions_chapter_VIa_ytd OUT NOCOPY NUMBER
127 ,p_total_income_ytd OUT NOCOPY NUMBER
128 ,p_tax_on_total_income_ytd OUT NOCOPY NUMBER
129 ,p_rebates_us_88_ytd OUT NOCOPY NUMBER
130 ,p_income_tax_ytd OUT NOCOPY NUMBER
131 )
132 IS
133 --
134 l_procedure VARCHAR2(100);
135 l_message VARCHAR2(250);
136
137
138 l_deductions_us_16_ytd NUMBER;
139 l_gross_salary_ytd NUMBER;
140 l_deductions_chapter_VIa_ytd NUMBER;
141 l_total_income_ytd NUMBER;
142 l_tax_on_total_income_ytd NUMBER;
143 l_rebates_us_88_ytd NUMBER;
144 l_income_tax_ytd NUMBER;
145 l_gross_sal_less_alw_ytd NUMBER;
146 l_rebates_sec88_ytd NUMBER;
147 l_rebates_sec88b_ytd NUMBER;
148 l_rebates_sec88c_ytd NUMBER;
149 l_rebates_sec88d_ytd NUMBER;
150
151 --
152 BEGIN
153 l_procedure := g_package||'balance_totals';
154 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
155
156 -- Call procedure to get Current and YTD balances for Payment Summary Totals
157
158 IF g_debug THEN
159 pay_in_utils.trace ('**************************************************','********************');
160 pay_in_utils.trace ('p_assignment_action_id',p_assignment_action_id);
161 pay_in_utils.trace ('**************************************************','********************');
162 END IF;
163
164 ytd_balance (
165 p_assignment_action_id => p_assignment_action_id
166 ,p_balance_name => 'F16 Gross Salary less Allowances'
167 ,p_ytd_balance => l_gross_sal_less_alw_ytd
168 );
169
170 pay_in_utils.trace('l_gross_sal_less_alw_ytd ',l_gross_sal_less_alw_ytd);
171 pay_in_utils.set_location(g_debug,l_procedure,20);
172
173 ytd_balance (
174 p_assignment_action_id => p_assignment_action_id
175 ,p_balance_name => 'F16 Income Chargeable Under head Salaries'
176 ,p_ytd_balance => l_gross_salary_ytd
177 );
178 pay_in_utils.trace('l_gross_salary_ytd ',l_gross_salary_ytd);
179 pay_in_utils.set_location(g_debug,l_procedure,30);
180 l_deductions_us_16_ytd := l_gross_sal_less_alw_ytd - l_gross_salary_ytd;
181 pay_in_utils.trace('l_deductions_us_16_ytd ',l_deductions_us_16_ytd);
182 pay_in_utils.set_location(g_debug,l_procedure,40);
183
184 ytd_balance (
185 p_assignment_action_id => p_assignment_action_id
186 ,p_balance_name => 'F16 Total Chapter VI A Deductions'
187 ,p_ytd_balance => l_deductions_chapter_VIa_ytd
188 );
189
190 pay_in_utils.trace('l_deductions_chapter_VIa_ytd ',l_deductions_chapter_VIa_ytd);
191 pay_in_utils.set_location(g_debug,l_procedure,50);
192
193 ytd_balance (
194 p_assignment_action_id => p_assignment_action_id
195 ,p_balance_name => 'F16 Total Income'
196 ,p_ytd_balance => l_total_income_ytd
197 );
198
199 pay_in_utils.trace('l_total_income_ytd ',l_total_income_ytd);
200 pay_in_utils.set_location(g_debug,l_procedure,60);
201
202 ytd_balance (
203 p_assignment_action_id => p_assignment_action_id
204 ,p_balance_name => 'F16 Tax on Total Income'
205 ,p_ytd_balance => l_tax_on_total_income_ytd
206 );
207
208 pay_in_utils.trace('l_tax_on_total_income_ytd ',l_tax_on_total_income_ytd);
209 pay_in_utils.set_location(g_debug,l_procedure,70);
210
211 ytd_balance (
212 p_assignment_action_id => p_assignment_action_id
213 ,p_balance_name => 'F16 Rebate 88'
214 ,p_ytd_balance => l_rebates_sec88_ytd
215 );
216
217 pay_in_utils.trace('l_rebates_sec88_ytd ',l_rebates_sec88_ytd);
218 pay_in_utils.set_location(g_debug,l_procedure,80);
219
220 ytd_balance (
221 p_assignment_action_id => p_assignment_action_id
222 ,p_balance_name => 'F16 Rebate 88B'
223 ,p_ytd_balance => l_rebates_sec88b_ytd
224 );
225
226 pay_in_utils.trace('l_rebates_sec88b_ytd ',l_rebates_sec88b_ytd);
227 pay_in_utils.set_location(g_debug,l_procedure,90);
228
229 ytd_balance (
230 p_assignment_action_id => p_assignment_action_id
231 ,p_balance_name => 'F16 Rebate 88C'
232 ,p_ytd_balance => l_rebates_sec88c_ytd
233 );
234
235 pay_in_utils.trace('l_rebates_sec88c_ytd ',l_rebates_sec88c_ytd);
236 pay_in_utils.set_location(g_debug,l_procedure,100);
237
238 ytd_balance (
239 p_assignment_action_id => p_assignment_action_id
240 ,p_balance_name => 'F16 Rebate 88D'
241 ,p_ytd_balance => l_rebates_sec88d_ytd
242 );
243
244 pay_in_utils.trace('l_rebates_sec88d_ytd ',l_rebates_sec88d_ytd);
245 pay_in_utils.set_location(g_debug,l_procedure,110);
246 l_rebates_us_88_ytd := l_rebates_sec88_ytd + l_rebates_sec88b_ytd + l_rebates_sec88c_ytd + l_rebates_sec88d_ytd;
247 pay_in_utils.trace('l_rebates_us_88_ytd ',l_rebates_us_88_ytd);
248 pay_in_utils.set_location(g_debug,l_procedure,120);
249
250 ytd_balance (
251 p_assignment_action_id => p_assignment_action_id
252 ,p_balance_name => 'F16 TDS'
253 ,p_ytd_balance => l_income_tax_ytd
254 );
255 pay_in_utils.trace('l_income_tax_ytd ',l_income_tax_ytd);
256 pay_in_utils.set_location(g_debug,l_procedure,130);
257
258 p_gross_salary_les_alw_ytd := l_gross_sal_less_alw_ytd;
259 p_deductions_us_16_ytd := l_deductions_us_16_ytd;
260 p_gross_salary_ytd := l_gross_salary_ytd;
261 p_deductions_chapter_VIa_ytd := l_deductions_chapter_VIa_ytd;
262 p_total_income_ytd := l_total_income_ytd;
263 p_tax_on_total_income_ytd := l_tax_on_total_income_ytd;
264 p_rebates_us_88_ytd := l_rebates_us_88_ytd;
265 p_income_tax_ytd := l_income_tax_ytd;
266
267 IF g_debug THEN
268 pay_in_utils.trace('**************************************************','********************');
269 pay_in_utils.trace('p_gross_salary_les_alw_ytd',p_gross_salary_les_alw_ytd);
270 pay_in_utils.trace('p_deductions_us_16_ytd',p_deductions_us_16_ytd);
271 pay_in_utils.trace('p_gross_salary_ytd',p_gross_salary_ytd);
272 pay_in_utils.trace('p_deductions_chapter_VIa_ytd',p_deductions_chapter_VIa_ytd);
273 pay_in_utils.trace('p_total_income_ytd',p_total_income_ytd);
274 pay_in_utils.trace('p_tax_on_total_income_ytd',p_tax_on_total_income_ytd);
275 pay_in_utils.trace('p_rebates_us_88_ytd',p_rebates_us_88_ytd);
276 pay_in_utils.trace('p_income_tax_ytd',p_income_tax_ytd);
277 pay_in_utils.trace('**************************************************','********************');
278 END IF;
279
280 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,140);
281
282 --
283 END balance_totals;
284
285
286 --------------------------------------------------------------------------
287 -- --
288 -- Name : GET_EXCHANGE_RATE --
289 -- Type : FUNCTION --
290 -- Access : Public --
291 -- Description : Function to return the exchange rate for a given --
292 -- FROM and TO currency --
293 -- --
294 -- Parameters : --
295 -- IN : p_from_currency VARCHAR2 --
296 -- p_to_currency VARCHAR2 --
297 -- p_eff_date DATE --
298 -- p_business_group_id NUMBER --
299 -- OUT : N/A --
300 -- RETURN : NUMBER --
301 -- --
302 -- Change History : --
303 --------------------------------------------------------------------------
304 -- Rev# Date Userid Description --
305 --------------------------------------------------------------------------
306 -- 115.0 30-JUN-2003 bramajey Initial Version --
307 -- 115.1 03-JUL-2003 bramajey Replaced %TYPE with actual data type --
308 -- in parameter list. --
309 --------------------------------------------------------------------------
310 --
311 FUNCTION get_exchange_rate
312 (
313 p_from_currency IN VARCHAR2
314 ,p_to_currency IN VARCHAR2
315 ,p_eff_date IN DATE
316 ,p_business_group_id IN NUMBER
317 )
318 RETURN NUMBER IS
319 --
320 --
321 CURSOR c_rate
322 IS
323 SELECT gdr.conversion_rate
324 FROM gl_daily_rates gdr
325 ,gl_daily_conversion_types gdct
326 WHERE gdr.conversion_type = gdct.conversion_type
327 AND gdr.from_currency = p_from_currency
328 AND gdr.to_currency = p_to_currency
329 AND gdr.conversion_date = p_eff_date
330 AND gdct.user_conversion_type = (
331 SELECT puci.value
332 FROM pay_user_column_instances_f puci
333 ,pay_user_rows_f pur
334 ,pay_user_columns puc
335 ,pay_user_tables put
336 WHERE puci.user_row_id = pur.user_row_id
337 AND puci.user_column_id = puc.user_column_id
338 AND pur.user_table_id = put.user_table_id
339 AND puc.user_table_id = put.user_table_id
340 AND puci.business_group_id = p_business_group_id
341 AND pur.ROW_LOW_RANGE_OR_NAME = 'PAY'
342 AND put.user_table_name = 'EXCHANGE_RATE_TYPES'
343 );
344 --
345 l_rate NUMBER;
346 l_procedure VARCHAR2(100);
347 l_message VARCHAR2(250);
348 --
349 BEGIN
350 --
351 l_procedure := g_package || 'get_exchange_rate';
352 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
353
354
355 IF g_debug THEN
356 pay_in_utils.trace ('**************************************************','********************');
357 pay_in_utils.trace ('p_from_currency',p_from_currency);
358 pay_in_utils.trace ('p_to_currency',p_to_currency);
359 pay_in_utils.trace ('p_eff_date',p_eff_date);
360 pay_in_utils.trace ('p_business_group_id',p_business_group_id);
361 pay_in_utils.trace ('**************************************************','********************');
362 END IF;
363
364 IF p_from_currency <> p_to_currency THEN
365 --
366 OPEN c_rate;
367 FETCH c_rate INTO l_rate;
368 IF c_rate%NOTFOUND THEN
369 --
370 l_rate := null;
371 --
372 END IF;
373 CLOSE c_rate;
374
375 --
376 ELSE
377 --
378 l_rate := null;
379 --
380 END IF;
384 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
381 pay_in_utils.trace ('**************************************************','********************');
382 pay_in_utils.trace('l_rate',l_rate);
383 pay_in_utils.trace ('**************************************************','********************');
385 RETURN(l_rate);
386 --
387 EXCEPTION
388 WHEN OTHERS THEN
389 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
390 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,40);
391 pay_in_utils.trace(l_message,l_procedure);
392
393 IF c_rate%ISOPEN THEN
394 CLOSE c_rate;
395 END IF;
396 RAISE;
397 --
398 END get_exchange_rate;
399
400 --
401 BEGIN
402 g_package := 'pay_in_payslip_utils.';
403 END pay_in_payslip_utils;