1 PACKAGE BODY pay_pl_utility as
2 /* $Header: pyplutil.pkb 120.3 2006/03/01 22:09 mseshadr noship $ */
3 --------------------------------------------------------------------------------
4 -- FUNCTION pay_pl_nip_format
5 --------------------------------------------------------------------------------
6 FUNCTION pay_pl_nip_format(p_nip IN NUMBER
7 ) RETURN VARCHAR2
8 IS
9 l_nip_format VARCHAR2(15);
10 BEGIN
11 if p_nip is not null then
12 l_nip_format:= substr(p_nip,1,3)||'-'||substr(p_nip,4,3)||'-'||substr(p_nip,7,2)||'-'||substr(p_nip,9);
13 end if;
14 return l_nip_format;
15 exception when others then
16 return null;
17 END pay_pl_nip_format;
18
19 FUNCTION pl_get_sii_details(
20 p_assignment_id number,
21 p_date_earned date ,
22 p_payroll_id number,
23 p_active_term_flag out nocopy varchar2,
24 p_sii_code out nocopy varchar2,
25 p_old_age_contrib out nocopy varchar2,
26 p_pension_contrib out nocopy varchar2,
27 p_sickness_contrib out nocopy varchar2,
28 p_work_injury_contrib out nocopy varchar2,
29 p_labor_contrib out nocopy varchar2,
30 p_unemployment_contrib out nocopy varchar2,
31 p_health_contrib out nocopy varchar2,
32 p_term_sii_code out nocopy varchar2,
33 p_term_old_age_contrib out nocopy varchar2,
34 p_term_pension_contrib out nocopy varchar2,
35 p_term_sickness_contrib out nocopy varchar2,
36 p_term_work_injury_contrib out nocopy varchar2,
37 p_term_labor_contrib out nocopy varchar2,
38 p_term_unemployment_contrib out nocopy varchar2,
39 p_term_health_contrib out nocopy varchar2
40 ) return number is
41
42 cursor csr_payroll_end_date is
43 select end_date from per_time_periods where
44 payroll_id = p_payroll_id
45 and p_date_earned between start_date and end_date;
46
47 cursor csr_date_terminated is
48 select paaf1.effective_end_date,nvl(paaf1.payroll_id,-1)
49 from per_all_assignments_f paaf1,
50 per_assignment_status_types past1,
51 per_all_assignments_f paaf2,
52 per_assignment_status_types past2,
53 pay_all_payrolls_f papf,
54 per_time_periods ptp
55 where past1.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
56 and paaf1.assignment_status_type_id=past1.assignment_status_type_id
57 and paaf1.assignment_id=p_assignment_id
58 and past2.per_system_status ='TERM_ASSIGN'
59 and paaf2.assignment_id=p_assignment_id
60 and paaf2.assignment_status_type_id=past2.assignment_status_type_id
61 and paaf1.effective_end_date+1=paaf2.effective_start_date
62 and paaf2.effective_start_date >ptp.start_date --not >=
63 and paaf2.effective_start_date<=ptp.end_date
64 and papf.payroll_id= paaf2.payroll_id
65 and p_date_earned between papf.effective_start_date and papf.effective_end_date
66 and papf.payroll_id=ptp.payroll_id
67 and p_date_earned between ptp.start_date and ptp.end_date;
68
69 --cursor duplicated in pl_get_tax_details
70 cursor csr_contract_assgt_type(r_date date) is
71 select kyflx.segment3,past.per_system_status,paaf.person_id
72 from hr_soft_coding_keyflex kyflx,
73 per_assignment_status_types past,
74 per_all_assignments_f paaf
75 where paaf.assignment_id=p_assignment_id
76 and r_date between paaf.effective_start_date and paaf.effective_end_date
77 and paaf.soft_coding_keyflex_id=kyflx.soft_coding_keyflex_id
78 and paaf.assignment_status_type_id=past.assignment_status_type_id ;
79
80
81 cursor csr_sii_details(r_per_or_asg_id number,r_contract_category varchar2,r_sii_date date) is
82 select EMP_SOCIAL_SECURITY_INFO,
83 old_age_contribution,
84 pension_contribution,
85 sickness_contribution,
86 work_injury_contribution,
87 labor_contribution,
88 unemployment_contribution,
89 health_contribution
90
91 from pay_pl_sii_details_f
92 where per_or_asg_id=r_per_or_asg_id
93 and contract_category=r_contract_category
94 and r_sii_date between effective_start_date and effective_end_date ;
95
96 l_contract_category hr_soft_coding_keyflex.segment3%type;
97 l_per_system_status per_assignment_status_types.per_system_status%type;
98 l_join_variable pay_pl_sii_details_f.per_or_asg_id%type;
99 l_date_earned date;
100 l_date_terminated date;
101 l_payroll_id number;
102 l_sii_date date;
103 l_person_id number;
104 l_assignment_id number;
105 l_proc varchar2(33);
106 l_period_end_date date;
107
108
109 begin
110 l_proc:='PAY_PL_UTILITY.PL_GET_SII_DETAILS';
111 hr_utility.set_location(l_proc,10);
112 open csr_payroll_end_date;
113 fetch csr_payroll_end_date into l_period_end_date;
114 close csr_payroll_end_date;
115
116 --fetch person id.It will be reqd later.person id as a context is not available for Oracle Payroll
117 --check if assgt is term_assign or is in (active_assign or susp_assign)
118 open csr_contract_assgt_type(p_date_earned);
119 fetch csr_contract_assgt_type into l_contract_category,l_per_system_status,l_person_id;
120 close csr_contract_assgt_type;
121
122
123
124 if l_per_system_status<>'TERM_ASSIGN' then
125 if l_contract_category='NORMAL' then
126 l_join_variable:=l_person_id;
127 else
128 l_join_variable:=p_assignment_id;
129 end if;
130
131 p_active_term_flag:= 'YN';
132
133
134 hr_utility.set_location(l_proc,20);
135 open csr_sii_details(l_join_variable,l_contract_category,l_period_end_date);
136 fetch csr_sii_details into p_sii_code,
137 p_old_age_contrib,p_pension_contrib,
138 p_sickness_contrib,
139 p_work_injury_contrib,p_labor_contrib,
140 p_unemployment_contrib,p_health_contrib;
141
142 close csr_sii_details;
143 hr_utility.set_location(l_proc,30);
144
145
146
147 else --per_system_status =term_assign?
148
149 --the employee has been terminated
150 --check if he was terminated in the current
151 --payroll period or some other previous period
152 hr_utility.set_location(l_proc,40);
153
154 open csr_date_terminated;
155 fetch csr_date_terminated into l_date_terminated,l_payroll_id;
156
157 if csr_date_terminated%found then
158 --employee has been terminated in current period only...
159 --so he has both active and term sii details in the tables
160 hr_utility.set_location(l_proc,50);
161 p_active_term_flag:= 'YY';
162 close csr_date_terminated;
163
164 if l_contract_category='NORMAL' then
165 l_join_variable:=l_person_id;
166 open csr_sii_details(l_join_variable,l_contract_category,l_period_end_date);
167 fetch csr_sii_details into p_sii_code,
168 p_old_age_contrib,p_pension_contrib,
169 p_sickness_contrib,
170 p_work_injury_contrib,p_labor_contrib,
171 p_unemployment_contrib,p_health_contrib;
172 close csr_sii_details;
173
174 else
175 l_join_variable:=p_assignment_id;
176 open csr_sii_details(l_join_variable,l_contract_category,l_date_terminated);
177 fetch csr_sii_details into p_sii_code,
178 p_old_age_contrib,p_pension_contrib,
179 p_sickness_contrib,
180 p_work_injury_contrib,p_labor_contrib,
181 p_unemployment_contrib,p_health_contrib;
182 close csr_sii_details;
183 end if;
184 --processing finished for active assign in this period
185 --before being terminated
186
187 hr_utility.set_location(l_proc,60);
188
189 if l_contract_category='NORMAL' then
190 l_contract_category:='TERM_NORMAL';
191 end if;
192 l_join_variable:=p_assignment_id;
193
194 open csr_sii_details(l_join_variable,l_contract_category,l_period_end_date);
195 fetch csr_sii_details into p_term_sii_code,
196 p_term_old_age_contrib,p_term_pension_contrib,
197 p_term_sickness_contrib,
198 p_term_work_injury_contrib,p_term_labor_contrib,
199 p_term_unemployment_contrib,p_term_health_contrib;
200 close csr_sii_details;
201
202 hr_utility.set_location(l_proc,70);
203 else --csr_date_terminated%found ?
204 hr_utility.set_location(l_proc,80);
205 close csr_date_terminated;
206 p_active_term_flag:= 'NY';
207 --employee has been terminated in some other period and in this period he has been
208 --terminated throughout
209 if l_contract_category='NORMAL' then
210 l_contract_category:='TERM_NORMAL';
211 end if;
212
213 l_join_variable:=p_assignment_id;
214
215 open csr_sii_details(l_join_variable,l_contract_category,l_period_end_date);
216 fetch csr_sii_details into p_term_sii_code,
217 p_term_old_age_contrib,p_term_pension_contrib,
218 p_term_sickness_contrib,
219 p_term_work_injury_contrib,p_term_labor_contrib,
220 p_term_unemployment_contrib,p_term_health_contrib;
221 close csr_sii_details;
222
223 hr_utility.set_location(l_proc,90);
224 end if;--csr_date_terminated%found?
225 end if; --per_system_status =term_assign?
226 hr_utility.set_location(l_proc,95);
227 return 1;
228 Exception
229 when others then
230 hr_utility.set_location(l_proc,99);
231 hr_utility.raise_error;
232
233 end pl_get_sii_details; --end of function pl_get_sii_details
234 -- Start of function Get Rate of Tax
235 FUNCTION GET_RATE_OF_TAX(p_date_earned IN DATE,
236 p_taxable_base IN NUMBER,
237 p_rate_of_tax IN VARCHAR2,
238 p_spouse_or_child_flag IN VARCHAR2,
239 p_tax_percentage OUT NOCOPY NUMBER) RETURN NUMBER is
240
241 cursor csr_level is select ci.value from
242 pay_user_column_instances_f ci,
243 pay_user_columns c,
244 pay_user_rows_f r,
245 pay_user_tables t
246 where r.user_table_id = t.user_table_id
247 and c.user_table_id = t.user_table_id
248 and ci.user_row_id = r.user_row_id
249 and ci.user_column_id = c.user_column_id
250 and t.legislation_code = 'PL'
251 and c.user_column_name = 'Level'
252 and t.user_table_name = 'PL_NORMAL_TAX'
253 and p_date_earned between ci.effective_start_date and ci.effective_end_date
254 and round(p_taxable_base,2) between r.row_low_range_or_name and r.row_high_range;
255
256 cursor csr_rate_of_tax(p_level number) is select ci.value
257 from
258 pay_user_column_instances_f ci,
259 pay_user_column_instances_f cilvl,
260 pay_user_rows_f r,
261 pay_user_rows_f rlvl,
262 pay_user_columns c,
263 pay_user_columns clvl,
264 pay_user_tables t
265 where c.user_table_id = t.user_table_id
266 and r.user_table_id = t.user_table_id
267 and ci.user_row_id = r.user_row_id
268 and ci.user_column_id = c.user_column_id
269 and clvl.user_table_id = t.user_table_id
270 and rlvl.user_table_id = t.user_table_id
271 and cilvl.user_row_id = rlvl.user_row_id
272 and cilvl.user_column_id = clvl.user_column_id
273 and t.legislation_code = 'PL'
274 and c.user_column_name = 'Standard'
275 and t.user_table_name = 'PL_NORMAL_TAX'
276 and r.row_low_range_or_name = rlvl.row_low_range_or_name
277 and p_date_earned between ci.effective_start_date and ci.effective_end_date
278 and p_date_earned between cilvl.effective_start_date and cilvl.effective_end_date
279 and cilvl.value = p_level;
280 l_row_level pay_user_column_instances_f.value%type;
281
282 BEGIN
283 open csr_level;
284 fetch csr_level into l_row_level;
285 close csr_level;
286
287 if p_rate_of_tax = 'N02' then
288 if l_row_level < 3 then
289 l_row_level:= l_row_level +1;
290 end if;
291 elsif p_rate_of_tax = 'N03' then
292 if l_row_level < 2 then
293 l_row_level:= l_row_level +1;
294 end if;
295 elsif p_rate_of_tax = 'N04' then
296 l_row_level:= 3;
297 end if;
298
299 if p_spouse_or_child_flag = 'Y' then
300 if l_row_level > 1 then
301 l_row_level:= l_row_level -1;
302 end if;
303 end if;
304 open csr_rate_of_tax(l_row_level);
305 fetch csr_rate_of_tax into p_tax_percentage;
306 close csr_rate_of_tax;
307
308 return l_row_level;
309
310 END GET_RATE_OF_TAX;
311 -- End of function GET_RATE_OF_TAX
312 -- Start of function pl_get_tax_details
313 Function pl_get_tax_details(
314 p_assignment_id number,
315 p_date_earned date ,
316 p_payroll_id number,
317 p_sii_code out nocopy varchar2,
318 p_spouse_or_child_flag out nocopy varchar2,
319 p_income_reduction out nocopy varchar2,
320 p_tax_reduction out nocopy varchar2,
321 p_income_reduction_amount out nocopy NUMBER,
322 p_rate_of_tax out nocopy varchar2,
323 p_contract_category out nocopy varchar2,
324 p_contract_type out nocopy varchar2,
325 p_ir_flag out nocopy varchar2
326 ) return number is
327 cursor csr_payroll_end_date is
328 select end_date
329 from per_time_periods
330 where payroll_id = p_payroll_id
331 and p_date_earned between start_date and end_date;
332
333 cursor csr_date_terminated is
334 select paaf1.effective_end_date terminated_date,nvl(paaf1.payroll_id,-1) payroll_id
335 from per_all_assignments_f paaf1,
336 per_assignment_status_types past1,
337 per_all_assignments_f paaf2,
338 per_assignment_status_types past2,
339 pay_all_payrolls_f papf,
340 per_time_periods ptp
341 where past1.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
342 and paaf1.assignment_status_type_id=past1.assignment_status_type_id
343 and paaf1.assignment_id=p_assignment_id
344 and past2.per_system_status ='TERM_ASSIGN'
345 and paaf2.assignment_id=p_assignment_id
349 and paaf2.effective_start_date<=ptp.end_date
346 and paaf2.assignment_status_type_id=past2.assignment_status_type_id
347 and paaf1.effective_end_date+1=paaf2.effective_start_date
348 and paaf2.effective_start_date >ptp.start_date --not >=
350 and papf.payroll_id= paaf2.payroll_id
351 and p_date_earned between papf.effective_start_date and papf.effective_end_date
352 and papf.payroll_id=ptp.payroll_id
353 and p_date_earned between ptp.start_date and ptp.end_date;
354
355 cursor csr_payroll_run (r_assignment_id number,r_date date,r_payroll_id number,r_less_than_date date) is
356 select 'Y' payroll_run,paa.assignment_action_id ,ppa.date_earned
357 from pay_payroll_actions ppa,
358 pay_Assignment_actions paa,
359 per_time_periods ptp,
360 pay_all_payrolls_f papf,
361 pay_run_results prr,
362 pay_element_types_f petf
363 where paa.source_action_id is not null
364 and paa.assignment_id=r_assignment_id
365 and paa.action_status='C'
366 and ppa.action_type in ('R','Q')
367 and ppa.payroll_action_id=paa.payroll_action_id
368 and ppa.date_earned between ptp.start_date and ptp.end_date
369 and ptp.payroll_id = r_payroll_id
370 and papf.payroll_id=ptp.payroll_id
371 and r_date between ptp.start_date and ptp.end_date
372 and ppa.date_earned<=r_less_than_date
373 and prr.assignment_action_id=paa.assignment_action_id
374 and petf.legislation_code='PL'
375 and petf.element_name ='Tax'
376 and r_date between petf.effective_start_date and petf.effective_end_date
377 and prr.status='P'
378 and prr.element_type_id=petf.element_type_id;
379 --didnot use order by as
380 -- all payroll runs will have same sii code
381 --if need arises to order payroll runs,order by ppa.date_earned
382
383 cursor csr_run_result_values(r_assignment_action_id number,r_element varchar2) is
384 select pivf.name,result_value,petf.element_name
385 from pay_run_results prr,
386 pay_run_result_values prrv,
387 pay_element_types_f petf,
388 pay_input_values_f pivf
389 where prr.assignment_action_id=r_assignment_action_id
390 and petf.legislation_code='PL'
391 and pivf.legislation_code='PL'
392 and petf.element_name =r_element
393 and petf.element_type_id=pivf.element_type_id
394 and p_date_earned between pivf.effective_start_date and pivf.effective_end_date
395 and p_date_earned between petf.effective_start_date and petf.effective_end_date
396 and prr.status='P'
397 and prr.element_type_id=petf.element_type_id
398 and prr.run_result_id=prrv.run_result_id
399 and pivf.input_value_id=prrv.input_value_id;
400
401 cursor csr_contract_assgt_type(r_date date) is
402 select kyflx.segment3,kyflx.segment4,past.per_system_status,paaf.person_id
403 from hr_soft_coding_keyflex kyflx,
404 per_assignment_status_types past,
405 per_all_assignments_f paaf
406 where paaf.assignment_id=p_assignment_id
407 and r_date between paaf.effective_start_date and paaf.effective_end_date
408 and paaf.soft_coding_keyflex_id=kyflx.soft_coding_keyflex_id
409 and paaf.assignment_status_type_id=past.assignment_status_type_id ;
410
411 cursor csr_normal_assignments(r_person_id number) is
412 select assignment_id
413 from per_all_assignments_f paaf,hr_soft_coding_keyflex kyflex
414 where paaf.person_id=r_person_id
415 and payroll_id= p_payroll_id
416 and kyflex.segment3='NORMAL'
417 and p_date_earned between paaf.effective_start_date and paaf.effective_end_date
418 and kyflex.soft_coding_keyflex_id=paaf.soft_coding_keyflex_id;
419
420 cursor csr_other_normal_contracts(r_person_id number) is
421 select 'Y'
422 from per_all_assignments_f paaf
423 ,hr_soft_coding_keyflex kyflex
424 ,per_assignment_status_types past
425 where paaf.person_id=r_person_id
426 and paaf.assignment_id<>p_assignment_id
427 and p_date_earned between paaf.effective_start_date and paaf.effective_end_date
428 and kyflex.segment3='NORMAL'
429 and kyflex.soft_coding_keyflex_id=paaf.soft_coding_keyflex_id;
430
431
432 cursor csr_get_tax_details(r_per_or_asg_id number,r_contract_category varchar2,r_date date ) is
433 select TAX_REDUCTION,TAX_CALC_WITH_SPOUSE_CHILD,INCOME_REDUCTION,
434 INCOME_REDUCTION_AMOUNT,RATE_OF_TAX,EMP_SOCIAL_SECURITY_INFO
435 from pay_pl_paye_details_f pppdf,
436 pay_pl_sii_details_f ppsdf
437 where pppdf.per_or_asg_id =r_per_or_asg_id
438 and r_date between pppdf.effective_start_date and pppdf.effective_end_date
439 and pppdf.contract_category=r_contract_category
440 and ppsdf.per_or_asg_id =r_per_or_asg_id
441 and ppsdf.contract_category=r_contract_category
442 and r_date between ppsdf.effective_start_date and ppsdf.effective_end_date;
443
444 TYPE run_result_table IS TABLE OF csr_run_result_values%ROWTYPE;
445
446 l_run_result_table run_result_table;
447 l_csr_date_terminated csr_date_terminated%rowtype;
448 l_csr_payroll_run csr_payroll_run%rowtype;
449 l_per_system_status per_assignment_status_types.per_system_status%type;
450 l_person_id number;
451 l_other_normal varchar2(1);--'Y' or 'N'
452 l_payroll_end_date date;
453 l_payroll_run varchar2(1);--'Y' or 'N'
454 l_assignment_action_id number;
455
456 /* The 3 variables needed to fetch tax params from pay_pl_paye_details_f*/
457 l_join_variable pay_pl_sii_details_f.per_or_asg_id%type;
458 l_paye_table_date date;
459 l_paye_contract_category pay_pl_paye_details_f.contract_category%type;
460
461 l_proc_name varchar2(33);
462
463 begin
464
465 l_proc_name:='PAY_PL_UTILITY.PL_GET_TAX_DETAILS';
466
467 hr_utility.set_location(l_proc_name,10);
471 p_contract_type,
468 open csr_contract_assgt_type(p_date_earned);
469 fetch csr_contract_assgt_type into
470 p_contract_category,
472 l_per_system_status,
473 l_person_id;
474 close csr_contract_assgt_type;
475
476 l_payroll_run:='N';
477
478 if p_contract_category='NORMAL' then
479 for i in csr_normal_assignments(l_person_id) loop
480 for j in csr_payroll_run(i.assignment_id,p_date_earned,p_payroll_id,p_date_earned-1) loop
481 l_payroll_run:=j.payroll_run;
482 l_assignment_action_id:=j.assignment_action_id;
483 if l_payroll_run='Y' then
484 goto end_of_loop;
485 end if;
486 end loop;
487 end loop;
488 <<end_of_loop>>
489
490 if l_payroll_run='Y' then
491 --payroll run for this or some other normal contract of this person
492 --with same payroll_id
493 hr_utility.set_location(l_proc_name,20);
494 open csr_run_result_values(l_assignment_action_id,'Tax Details');
495 fetch csr_run_result_values bulk collect into l_run_result_table;
496 close csr_run_result_values;
497 FOR indx IN l_run_result_table.FIRST .. l_run_result_table.LAST loop
498 case l_run_result_table(indx).name
499 when 'SII Code' then p_sii_code :=l_run_result_table(indx).result_value;
500 when 'Spouse or Child Flag' then p_spouse_or_child_flag :=l_run_result_table(indx).result_value;
501 when 'Income Reduction' then p_income_reduction :=l_run_result_table(indx).result_value;
502 when 'Tax Reduction' then p_tax_reduction :=l_run_result_table(indx).result_value;
503 when 'Rate of Tax' then p_rate_of_tax :=l_run_result_table(indx).result_value;
504 when 'Contract Category' then p_contract_category :=l_run_result_table(indx).result_value;
505 when 'Contract Type' then p_contract_type :=l_run_result_table(indx).result_value;
506 when 'IR Flag' then p_ir_flag :=l_run_result_table(indx).result_value;
507 when 'Income Reduction Amount' then p_income_reduction_amount:=l_run_result_table(indx).result_value;
508 end case;
509 end loop;
510 hr_utility.set_location(l_proc_name,30);
511 else--l_payroll_run='Y'?
512 hr_utility.set_location(l_proc_name,40);
513 open csr_payroll_end_date;
514 fetch csr_payroll_end_date into l_payroll_end_date;
515 close csr_payroll_end_date;
516
517 --No payroll has been run for any Normal Contract of this person in the current payroll period
518 if l_per_system_status<>'TERM_ASSIGN' then
519 --Active Normal Contract,
520 --pick up_values from pay_pl_paye_details_f with person_id and date as of ptp.end_date
521
522 l_join_variable :=l_person_id;
523 l_paye_contract_category:='NORMAL';
524 l_paye_table_date:=l_payroll_end_date;
525
526 else--l_per_system_status<>'TERM_ASSIGN'
527 --Assignment is terminated.
528 --Find out if it has been terminated in current Payroll period
529 hr_utility.set_location(l_proc_name,50);
530 open csr_date_terminated ;
531 fetch csr_date_terminated into l_csr_date_terminated;
532 --this is the last date as active/susp assign.
533 --Pass next date for getting terminated sii/tax record
534 if csr_date_terminated%found then
535 close csr_date_terminated ;
536 open csr_other_normal_contracts(l_person_id);
537 fetch csr_other_normal_contracts into l_other_normal;
538 if csr_other_normal_contracts%found then
539 l_join_variable :=l_person_id;
540 l_paye_contract_category:='NORMAL';
541 l_paye_table_date :=l_payroll_end_date;
542 else
543 l_join_variable :=p_assignment_id;
544 l_paye_contract_category :='TERM_NORMAL';--corrected in 115.7
545 l_paye_table_date :=l_csr_date_terminated.terminated_date+1;
546 end if;
547 close csr_other_normal_contracts;
548 else
549 close csr_date_terminated ;
550 l_join_variable :=p_assignment_id;
551 l_paye_contract_category :='TERM_NORMAL';
552 l_paye_table_date :=l_payroll_end_date;
553 end if;--csr_date_terminated%found
554 end if;--l_per_system_status<>'TERM_ASSIGN'
555
556 open csr_get_tax_details(l_join_variable,l_paye_contract_category,l_paye_table_date);
557 fetch csr_get_tax_details into p_tax_reduction,p_spouse_or_child_flag,p_income_reduction,
558 p_income_reduction_amount,p_rate_of_tax,p_sii_code;
559 close csr_get_tax_details;
560
561 hr_utility.set_location(l_proc_name,60);
562 end if;--l_payroll_run='Y'?
563 else--l_contract_category='NORMAL'?
564 /* ***************Tax Params for Civil Contract,Lump and F_Lump*************** */
565 hr_utility.set_location(l_proc_name,70);
566 open csr_payroll_run(p_assignment_id,p_date_earned,p_payroll_id,p_date_earned-1);
567 fetch csr_payroll_run into l_csr_payroll_run.payroll_run,l_csr_payroll_run.assignment_action_id,l_csr_payroll_run.date_earned;
568 if l_csr_payroll_run.payroll_run='Y' then
569 hr_utility.set_location(l_proc_name,20);
570 open csr_run_result_values(l_csr_payroll_run.assignment_action_id,'Tax Details');
571 fetch csr_run_result_values bulk collect into l_run_result_table;
572 close csr_run_result_values;
573
574 FOR indx IN l_run_result_table.FIRST .. l_run_result_table.LAST loop
575 case l_run_result_table(indx).name
576 when 'SII Code' then p_sii_code :=l_run_result_table(indx).result_value;
577 when 'Spouse or Child Flag' then p_spouse_or_child_flag :=l_run_result_table(indx).result_value;
578 when 'Income Reduction' then p_income_reduction :=l_run_result_table(indx).result_value;
579 when 'Tax Reduction' then p_tax_reduction :=l_run_result_table(indx).result_value;
580 when 'Rate of Tax' then p_rate_of_tax :=l_run_result_table(indx).result_value;
581 when 'Contract Category' then p_contract_category :=l_run_result_table(indx).result_value;
582 when 'Contract Type' then p_contract_type :=l_run_result_table(indx).result_value;
583 when 'IR Flag' then p_ir_flag :=l_run_result_table(indx).result_value;
584 when 'Income Reduction Amount' then p_income_reduction_amount:=l_run_result_table(indx).result_value;
585 end case;
586 end loop;
587 hr_utility.set_location(l_proc_name,80);
588 close csr_payroll_run;
589 else --l_payroll_run='Y'?
590 close csr_payroll_run;
591 --No payroll has been run for this Civil Contract,Lump or F_Lump contract
592 --in the current payroll period
593 hr_utility.set_location(l_proc_name,40);
594
595 open csr_payroll_end_date;
596 fetch csr_payroll_end_date into l_paye_table_date;
597 close csr_payroll_end_date;
598 --p_contract_category=CIVIL or LUMP or F_LUMP
599 open csr_get_tax_details(p_assignment_id,p_contract_category,l_paye_table_date);
600 fetch csr_get_tax_details into p_tax_reduction,p_spouse_or_child_flag,p_income_reduction,
601 p_income_reduction_amount,p_rate_of_tax,p_sii_code;
602 close csr_get_tax_details;
603
604 if p_income_reduction_amount is null then
605 p_ir_flag:='N';
606 p_income_reduction_amount:=0;
607 else
608 p_ir_flag:='Y';
609 end if;
613 hr_utility.set_location(l_proc_name,100);
610 end if;--l_payroll_run='Y'?
611 hr_utility.set_location(l_proc_name,90);
612 end if;--l_contract_category='NORMAL'?
614 return 0;
615 Exception
616 when others then
617 hr_utility.set_location(l_proc_name,99);
618 hr_utility.raise_error;
619 end pl_get_tax_details; --end of function pl_get_tax_details
620 END pay_pl_utility;
[Home] [Help]