1 PACKAGE BODY PAY_SE_GENERAL AS
2 /* $Header: pysegen.pkb 120.5 2007/02/13 05:53:49 rravi noship $ */
3 --
4 /*
5 ----------------------------------------------------------------------------
6
7 FUNCTION NAME : get_Tax_Amount
8 PARAMATERS :
9 p_DATE_EARNED Date on which the payroll run
10 p_ASSIGNMENT_ID Assignment Id of the person
11
12 PURPOSE : To get Tax Amount.
13 EXCEPTIONS
14 HANDLED : When Date of Birth not found Age is
15 assigned to Zero.
16 ----------------------------------------------------------------------------
17
18 */
19 FUNCTION get_Tax_Amount
20 (
21 p_DATE_EARNED in Date,
22 p_ASSIGNMENT_ID in Number,
23 p_Period_Type in varchar2,
24 p_Tax_Table_No in Number,
25 p_Taxable_Base in Number,
26 p_Tax_Column in Number
27 )
28 RETURN Number
29 IS
30
31
32 l_amount1 PAY_RANGES_F.amount1%type;
33 l_amount2 PAY_RANGES_F.amount1%type;
34 l_amount3 PAY_RANGES_F.amount1%type;
35 l_amount4 PAY_RANGES_F.amount1%type;
36 l_amount5 PAY_RANGES_F.amount1%type;
37
38 cursor csr_amount(ROM char,PERIOD Number,Tax_Base Number)
39 is
40 select amount1,amount2,amount3,amount4,amount5
41 INTO l_amount1,l_amount2,l_amount3,l_amount4,l_amount5
42 from pay_range_tables_f PRTF ,pay_ranges_f PRF,per_all_assignments_f PAAF
43 where PAAF.ASSIGNMENT_ID = p_ASSIGNMENT_ID
44 --and PAAF.BUSINESS_GROUP_ID = PRTF.BUSINESS_GROUP_ID
45 and PRTF.LEGISLATION_CODE='SE'
46 and PRTF.range_table_id = PRF.range_table_id
47 and PRTF.range_table_number = TO_NUMBER(TO_CHAR(p_Tax_Table_No))
48 and PRTF.period_frequency = PERIOD
49 and PRTF.row_value_uom = ROM
50 and Tax_Base
51 between PRF.low_band
52 and PRF.high_band
53 and p_DATE_EARNED between
54 PRTF.EFFECTIVE_START_DATE
55 and PRTF.EFFECTIVE_END_DATE
56 and p_DATE_EARNED between
57 PAAF.EFFECTIVE_START_DATE
58 and PAAF.EFFECTIVE_END_DATE
59 and p_DATE_EARNED between
60 PRF.EFFECTIVE_START_DATE
61 and PRF.EFFECTIVE_END_DATE;
62
63
64 cursor csr_Get_Age
65 is
66 select floor((months_between(TRUNC(p_DATE_EARNED,'yyyy') ,PAPF.DATE_OF_BIRTH ))/12)
67 from per_all_assignments_f PAAF,per_all_people_f PAPF
68 where PAAF.ASSIGNMENT_ID =p_ASSIGNMENT_ID
69 and PAAF.PERSON_ID = PAPF.PERSON_ID
70 and PAAF.BUSINESS_GROUP_ID= PAPF.BUSINESS_GROUP_ID
71 and p_DATE_EARNED between
72 PAPF.EFFECTIVE_START_DATE
73 and PAPF.EFFECTIVE_END_DATE
74 and p_DATE_EARNED between
75 PAAF.EFFECTIVE_START_DATE
76 and PAAF.EFFECTIVE_END_DATE;
77
78
79 l_csr_amount csr_amount%rowtype;
80 l_Period_Frequency Number;
81 l_Not_found Number;
82 l_Age Number;
83 l_Return Number;
84 l_Taxable_Base Number;
85 l_NO_RECORD Number; -- Tax Table Number is Null so return Zero
86 BEGIN
87 -- Flag to find that value cant be find for the tax table given
88 l_NO_RECORD := 0;
89
90 /* flag to find if the row found or not */
91 l_Not_found := 0;
92
93 /* Check to find out 14 0r 30 Using the Period Type */
94 IF p_Period_Type in ('Bi-Week','Week')
95 THEN
96 l_Period_Frequency := 14;
97 ELSIF p_Period_Type in ('Calendar Month','Bi-Month')
98 THEN
99 l_Period_Frequency := 30;
100 END IF;
101 /*Adjusting the Taxable base if Pay Period type is Week or Bi-Month*/
102 IF p_Period_Type='Week'
103 then l_Taxable_Base:=p_Taxable_Base*2;
104 elsif p_Period_Type='Bi-Month'
105 then l_Taxable_Base:=p_Taxable_Base/2;
106 else l_Taxable_Base:=p_Taxable_Base;
107 end if;
108
109 /* first pick up with the B type if anything found Use this If not set the flag to not found*/
110 open csr_amount('B',l_Period_Frequency,l_Taxable_Base);
111 fetch csr_amount into l_csr_amount;
112 IF csr_amount%NOTFOUND THEN
113 l_Not_found := 1;
114 END IF;
115 close csr_amount;
116
117 /* If the flag is set to not found then try with % Type */
118 IF l_Not_found = 1
119 THEN
120 open csr_amount('%',l_Period_Frequency,l_Taxable_Base);
121 fetch csr_amount into l_csr_amount;
122 IF csr_amount%NOTFOUND THEN
123 l_NO_RECORD := 1;
124 END IF;
125 close csr_amount;
126
127 END IF;
128 IF l_NO_RECORD = 0
129 THEN
130 /* Calculate the Age Here */
131
132
133 OPEN csr_Get_Age;
134 FETCH csr_Get_Age INTO l_Age;
135 IF csr_Get_Age%NOTFOUND THEN
136 l_Age := 0;
137 END IF;
138 CLOSE csr_Get_Age;
139
140
141 /* Check For Age*/
142 /* Age less than or equal to 65 */
143 IF l_Age <=65
144 THEN
145 l_Return := l_csr_amount.amount1;
146 /*ELSE
147 l_Return := l_csr_amount.amount2;*/
148 /* Age between 66 and 69 */
149 ELSIF l_Age <70 THEN
150 l_Return := l_csr_amount.amount3;
151 /* Age 70 and above */
152 ELSE
153 l_Return := l_csr_amount.amount4;
154 END IF;
155
156 /* Check for Default Tax Column. It Overrides the calculation based on age*/
157 IF p_Tax_Column='1'
158 THEN
159 l_Return := l_csr_amount.amount1;
160 ELSIF p_Tax_Column='2' THEN
161 l_Return := l_csr_amount.amount3;
162 ELSIF p_Tax_Column='3' THEN
163 l_Return := l_csr_amount.amount4;
164 /*END IF;
165 IF p_Tax_Column='2'
166 THEN
167 l_Return := l_csr_amount.amount2;*/
168 END IF;
169
170 /* Calculate the Taxable value on that Taxable Base */
171 IF l_Not_found = 1
172 THEN
173 l_Return := (l_Return * p_Taxable_Base)/100;
174 END IF;
175
176 /*Check for the Weekly and Bi-Monthly Pay period*/
177 IF p_Period_Type='Week'
178 THEN
179 l_Return :=l_Return/2;
180 ELSIF p_Period_Type='Bi-Month'
181 THEN
182 l_Return:=l_Return*2;
183 END IF;
184
185 ELSE
186 l_Return := 0; -- As no record found for the given combination of TT no,Period freq.etc...
187 END IF; -- No record If stmt
188
189 return l_Return;
190
191
192 end get_Tax_Amount;
193
194 /*
195 ----------------------------------------------------------------------------
196
197 FUNCTION NAME : get_Tax_Card_Details
198 PARAMATERS :
199 p_ASSIGNMENT_ID Assignment Id of the person
200 p_DATE_EARNED Date
201
202 PURPOSE : To get Details of Tax Card.
203 EXCEPTIONS
204 HANDLED : None.
205
206 ----------------------------------------------------------------------------
207
208 */
209 FUNCTION get_tax_card_details
210 (
211
212 P_ASSIGNMENT_ID IN NUMBER
213 ,p_DATE_EARNED IN DATE
214 ,p_tax_card_type OUT NOCOPY VARCHAR2
215 ,p_Tax_Percentage OUT NOCOPY NUMBER
216 ,p_Tax_Table_Number OUT NOCOPY NUMBER
217 ,p_Tax_Column OUT NOCOPY VARCHAR2
218 ,p_Tax_Free_Threshold OUT NOCOPY NUMBER
219 ,p_Calculation_Code OUT NOCOPY VARCHAR2
220 ,p_Calculation_Sum OUT NOCOPY NUMBER
221 )
222 RETURN NUMBER
223 IS
224 --
225 CURSOR get_details(csr_v_input_value VARCHAR2 ) IS
226 SELECT eev1.screen_entry_value screen_entry_value
227 FROM per_all_assignments_f asg1
228 ,per_all_assignments_f asg2
229 ,per_all_people_f per
230 ,pay_element_links_f el
231 ,pay_element_types_f et
232 ,pay_input_values_f iv1
233 ,pay_element_entries_f ee
234 ,pay_element_entry_values_f eev1
235 WHERE asg1.assignment_id = P_ASSIGNMENT_ID
236 AND p_DATE_EARNED BETWEEN asg1.effective_start_date AND asg1.effective_end_date
237 AND p_DATE_EARNED BETWEEN per.effective_start_date AND per.effective_end_date
238 AND p_DATE_EARNED BETWEEN asg2.effective_start_date AND asg2.effective_end_date
239 AND per.person_id = asg1.person_id
240 AND asg2.person_id = per.person_id
241 AND asg2.primary_flag = 'Y'
242 AND et.element_name = 'Tax Card'
243 AND et.legislation_code = 'SE'
244 AND iv1.element_type_id = et.element_type_id
245 AND iv1.name = csr_v_input_value
246 AND el.business_group_id = per.business_group_id
247 AND el.element_type_id = et.element_type_id
248 AND ee.assignment_id = asg2.assignment_id
249 AND ee.element_link_id = el.element_link_id
250 AND eev1.element_entry_id = ee.element_entry_id
251 AND eev1.input_value_id = iv1.input_value_id
252 AND p_DATE_EARNED BETWEEN ee.effective_start_date AND ee.effective_end_date
253 AND p_DATE_EARNED BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
254
255
256 l_rec get_details%ROWTYPE;
257 l_Not_found Number;
258 --
259 BEGIN
260 /* flag to find if the row found or not */
261 l_Not_found := 0;
262 --
263 OPEN get_details('Tax Card Type');
264 FETCH get_details INTO l_rec;
265 IF get_details%NOTFOUND THEN
266 l_Not_found := 0;
267 ELSE
268 l_Not_found := 1;
269 END IF;
270 CLOSE get_details;
271 --
272
273 p_tax_card_type := l_rec.screen_entry_value;
274
275 OPEN get_details('Tax Percentage');
276 FETCH get_details INTO l_rec;
277 IF get_details%NOTFOUND THEN
278 l_Not_found := 0;
279 ELSE
280 l_Not_found := 1;
281 END IF;
282 CLOSE get_details;
283
284 p_Tax_Percentage := NVL(l_rec.screen_entry_value,0);
285
286 OPEN get_details('Tax Table Number');
287 FETCH get_details INTO l_rec;
288 IF get_details%NOTFOUND THEN
289 l_Not_found := 0;
290 ELSE
291 l_Not_found := 1;
292 END IF;
293 CLOSE get_details;
294 p_Tax_Table_Number :=NVL(l_rec.screen_entry_value,0);
295
296
297 OPEN get_details('Tax Column');
298 FETCH get_details INTO l_rec;
299 IF get_details%NOTFOUND THEN
300 l_Not_found := 0;
301 ELSE
302 l_Not_found := 1;
303 END IF;
304 CLOSE get_details;
305 p_Tax_Column := l_rec.screen_entry_value;
306
307 OPEN get_details('Tax Free Threshold');
308 FETCH get_details INTO l_rec;
309 IF get_details%NOTFOUND THEN
310 l_Not_found := 0;
311 ELSE
312 l_Not_found := 1;
313 END IF;
314 CLOSE get_details;
315
316 p_Tax_Free_Threshold :=NVL(l_rec.screen_entry_value,0);
317
318 OPEN get_details('Calculation Code');
319 FETCH get_details INTO l_rec;
320 IF get_details%NOTFOUND THEN
321 l_Not_found := 0;
322 ELSE
323 l_Not_found := 1;
324 END IF;
325 CLOSE get_details;
326 p_Calculation_Code := NVL(l_rec.screen_entry_value,0);
327
328 OPEN get_details('Calculation Sum');
329 FETCH get_details INTO l_rec;
330 IF get_details%NOTFOUND THEN
331 l_Not_found := 0;
332 ELSE
333 l_Not_found := 1;
334 END IF;
335 CLOSE get_details;
336 p_Calculation_Sum := NVL(l_rec.screen_entry_value,0);
337 --
338 RETURN l_Not_found;
339 EXCEPTION
340 WHEN OTHERS THEN
341 RETURN 0 ;
342 --
343 END get_tax_card_details;
344
345 FUNCTION Get_no_of_payroll
346 (
347 p_PAYROLL_ID in Number,
348 p_EMP_START_DATE in Date,
349 p_CURR_PAY_END_DATE iN date
350 )
351 RETURN Number
352 IS
353
354 CURSOR csr_first_end_date
355 IS
356 select end_date from per_time_periods
357 where payroll_id = p_PAYROLL_ID
358 and to_char(end_date,'YYYY') = to_char(p_CURR_PAY_END_DATE,'YYYY')
359 and rownum < 2
360 order by end_date;
361
362 cursor csr_Get_pay_run(l_mdate date,l_first_end_date date)
363 is
364 select count(*) from per_time_periods
365 where payroll_id = p_PAYROLL_ID
366 and to_char(end_date,'YYYY') = to_char(l_first_end_date,'YYYY')
367 and p_CURR_PAY_END_DATE >= end_date --DBI Item, Current Payroll End Date
368 and l_mdate <= end_date; --Variable, Current Year First Period End Date OR Join Date, whichever is Max
369
370 l_payroll_run number;
371 l_max_date date;
372 l_first_pay_end_date date;
373
374 Begin
375
376 open csr_first_end_date ;
377 fetch csr_first_end_date into l_first_pay_end_date;
378 close csr_first_end_date ;
379
380 IF p_EMP_START_DATE >= l_first_pay_end_date
381 THEN
382 l_max_date := p_EMP_START_DATE;
383 ELSE
384 l_max_date := l_first_pay_end_date;
385 END IF;
386
387 open csr_Get_pay_run(l_max_date,l_first_pay_end_date) ;
388 fetch csr_Get_pay_run into l_payroll_run;
389 IF csr_Get_pay_run%NOTFOUND THEN
390 l_payroll_run := 0;
391 END IF;
392 close csr_Get_pay_run ;
393
394 return l_payroll_run;
395 END Get_no_of_payroll;
396
397 FUNCTION Get_Absence_Detail(
398 p_ASG_Id IN Number,
399 p_Effective_Date IN Date,
400 p_ASG_Absent_days IN Number,
401 p_ASG_Absent_hours IN Number,
402 p_Gross_Pay_ASG_Run IN Number
403 )
404 RETURN NUMBER IS
405
406 Cursor csr_Hourly_Salaried
407 (
408 p_ASG_Id Number,
409 p_Effective_Date Date,
410 p_ASG_Absent_days Number,
411 p_ASG_Absent_hours Number
412 )
413 IS
414 select Hourly_Salaried_Code,segment9 from
415 per_all_assignments_f paaf,
416 hr_soft_coding_keyflex hsck
417 where
418 paaf.assignment_id= p_ASG_id --20805
419 and paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
420 and p_Effective_Date between
421 paaf.effective_start_date and paaf.effective_end_date;
422
423 CURSOR csr_get_schedule_id(
424 b_schdl_cat VARCHAR2,
425 b_object_type VARCHAR2,
426 b_object_id NUMBER,
427 b_start_dt DATE,
428 b_end_dt DATE
429 )
430 IS
431 SELECT CSSB.SCHEDULE_ID
432 from
433 CAC_SR_SCHDL_OBJECTS CSSO,
434 CAC_SR_SCHEDULES_B CSSB
435 where
436 CSSO.OBJECT_TYPE = b_object_type
437 AND CSSO.OBJECT_ID = b_object_id
438 AND CSSO.START_DATE_ACTIVE <= b_end_dt
439 AND CSSO.END_DATE_ACTIVE >= b_start_dt
440 AND CSSO.SCHEDULE_ID = CSSB.SCHEDULE_ID
441 AND CSSB.DELETED_DATE IS NULL
442 AND (CSSB.SCHEDULE_CATEGORY = b_schdl_cat
443 OR CSSB.SCHEDULE_ID IN (SELECT SCHEDULE_ID
444 FROM CAC_SR_PUBLISH_SCHEDULES
445 WHERE OBJECT_TYPE = b_object_type
446 AND OBJECT_ID = b_object_id
447 AND b_schdl_cat IS NULL
448 ));
449 CURSOR csr_get_template_details(b_schedule_id number)
450 IS
451 select CSTB.Template_Id,CSTB.TEMPLATE_LENGTH_DAYS from CAC_SR_SCHEDULES_B CSSB,
452 CAC_SR_TEMPLATES_B CSTB
456
453 where
454 CSSB.Template_Id=CSTB.Template_Id
455 and CSSB.Schedule_id=b_schedule_id; --10206
457 CURSOR csr_get_shift_duration(b_template_id number)
458 IS
459 select CSRB.DURATION from
460 CAC_SR_TEMPLATES_B CSTB,
461 CAC_SR_TMPL_DETAILS CSTD,
462 CAC_SR_PERIODS_B CSRB
463 where
464 CSTB.Template_id =CSTD.Template_Id
465 and CSTD.Child_Period_Id=CSRB.Period_ID
466 and CSTB.Template_Id=b_template_id; --10284
467
468
469
470 l_Mtly_Hrly_Emp Char(1);
471 --l_Work_Perc_Emp Number;
472 l_NOR_Days_Month Number;
473 l_NOR_Days_Week Number;
474 --l_return=-1
475 --l_shift_duration Number;
476 l_pattern_length Number;
477 l_working_days Number;
478 l_working_hours Number;
479 l_deduction_working_day Number;
480 l_deduction_working_hours Number;
481 l_absence_deduction_amount Number;
482 l_deduction_calendar_day Number;
483 l_schedule_id Number;
484 l_template_id Number;
485 l_shift_duration Number;
486 l_Working_Perc Number;
487 l_Monthly_Salary Number;
488
489 BEGIN
490 l_Monthly_Salary:=p_Gross_Pay_ASG_Run;
491
492 OPEN csr_get_schedule_id(NULL, 'PERSON_ASSIGNMENT', p_ASG_id,trunc(p_effective_date,'MON'),p_effective_date);
493 FETCH csr_get_schedule_id INTO l_schedule_id;
494 CLOSE csr_get_schedule_id;
495
496 OPEN csr_get_template_details(l_schedule_id);
497 FETCH csr_get_template_details --.Template_Id,csr_get_template_details.TEMPLATE_LENGTH_DAYS
498 INTO l_template_id,l_pattern_length;
499 CLOSE csr_get_template_details;
500
501 OPEN csr_get_shift_duration(l_template_id);
502 FETCH csr_get_shift_duration INTO l_shift_duration;
503 CLOSE csr_get_shift_duration;
504
505 IF (l_pattern_length=5) THEN
506 l_NOR_Days_Month:=21;
507 l_NOR_Days_Week:=5;
508 ELSIF (l_pattern_length=6) THEN
509 l_NOR_Days_Month:=25;
510 l_NOR_Days_Week:=6;
511 END IF;
512
513 IF (p_ASG_Absent_days=l_NOR_Days_Month) THEN
514 l_absence_deduction_amount:=l_Monthly_Salary;
515 RETURN l_absence_deduction_amount;
516 END IF;
517 OPEN csr_Hourly_Salaried(p_ASG_Id,p_Effective_Date,p_ASG_Absent_days,p_ASG_Absent_hours );
518 FETCH csr_Hourly_Salaried INTO l_Mtly_Hrly_Emp,l_Working_Perc;
519 CLOSE csr_Hourly_Salaried;
520
521 IF l_Mtly_Hrly_Emp='S' THEN
522 IF l_working_perc=0 THEN
523 IF p_ASG_absent_days<=5 THEN
524 l_deduction_working_day:=l_Monthly_Salary/l_NOR_Days_Month;
525 l_absence_deduction_amount:=l_deduction_working_day * p_Asg_Absent_days;
526 ELSE
527 l_deduction_calendar_day:=l_Monthly_Salary *12/365;
528 l_absence_deduction_amount:=l_deduction_calendar_day * p_Asg_Absent_days;
529 END IF;
530 ELSE
531 l_deduction_working_day:=l_Monthly_Salary/((l_NOR_Days_Week/(l_working_perc/100*l_NOR_Days_Week))
532 * l_NOR_Days_Month);
533 l_absence_deduction_amount:=l_deduction_working_day * p_ASG_Absent_days;
534 END IF;
535 ELSE
536 IF (l_NOR_Days_Week * l_shift_duration)=40 THEN
537 l_deduction_working_hours:=l_Monthly_Salary/175;
538 l_absence_deduction_amount:=l_deduction_working_hours* p_ASG_Absent_hours;
539 END IF;
540 END IF;
541 RETURN l_absence_deduction_amount;
542 END Get_Absence_Detail;
543
544
545
546 END pay_se_general;
[Home] [Help]