[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_ONLINE_SOE
Source
1 package body pay_gb_online_soe as
2 /* $Header: pygbsoer.pkb 120.3 2006/05/04 07:50 kthampan noship $ */
3
4 g_package varchar2(33) := ' PAY_GB_ONLINE_SOE.';
5
6 ------------------------------------------------------------------------
7 --- Function : checkPrePayment
8 ---
9 --- Text : check for pre payment
10 ------------------------------------------------------------------------
11 FUNCTION checkPrepayment(p_assignment_action_id number) return number is
12 l_action_type varchar2(1);
13 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
14 --
15 cursor csr_get_action_type is
16 select pact.action_type
17 from pay_assignment_actions assact,
18 pay_payroll_actions pact
19 where assact.assignment_action_id = p_assignment_action_id
20 and pact.payroll_action_id = assact.payroll_action_id;
21 --
22 cursor csr_get_latest_interlocked is
23 select assact.assignment_action_id
24 from pay_assignment_actions assact,
25 pay_action_interlocks loc
26 where loc.locking_action_id = p_assignment_action_id
27 and assact.assignment_action_id = loc.locked_action_id
28 order by assact.action_sequence desc;
29 BEGIN
30 -- get the action type
31 open csr_get_action_type;
32 fetch csr_get_action_type into l_action_type;
33 close csr_get_action_type;
34
35 if l_action_type in ('P', 'U') then
36 open csr_get_latest_interlocked;
37 fetch csr_get_latest_interlocked into l_assignment_action_id;
38 close csr_get_latest_interlocked;
39 else
40 l_assignment_action_id := p_assignment_action_id;
41 end if;
42
43 return l_assignment_action_id;
44 END checkPrepayment;
45
46 ---------------------------------------------------------------------
47 --- Function : getEmployerBalance
48 ---
49 --- Text : get Employer balances
50 ---------------------------------------------------------------------
51 FUNCTION getEmployerBalance(p_assignment_action_id number) return number is
52
53 l_ni_a_total_value number;
54 l_ni_b_total_value number;
55 l_ni_d_total_value number;
56 l_ni_e_total_value number;
57 l_ni_f_total_value number;
58 l_ni_g_total_value number;
59 l_ni_l_total_value number;
60 l_ni_j_total_value number;
61 l_ni_s_total_value number;
62 l_temp_balance number;
63 l_employer_balance number;
64 l_tax_district_ytd varchar2(20);
65
66 BEGIN
67
68 l_ni_a_total_value := 0;
69 l_ni_b_total_value := 0;
70 l_ni_d_total_value := 0;
71 l_ni_e_total_value := 0;
72 l_ni_f_total_value := 0;
73 l_ni_g_total_value := 0;
74 l_ni_l_total_value := 0;
75 l_ni_j_total_value := 0;
76 l_ni_s_total_value := 0;
77 l_temp_balance := 0;
78 l_employer_balance := 0;
79 l_tax_district_ytd := '_ASG_TD_YTD';
80
81
82 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'A') = 1 THEN
83 l_ni_a_total_value := pay_gb_payroll_actions_pkg.report_balance_items
84 (p_balance_name => 'NI A Total',
85 p_dimension => l_tax_district_ytd,
86 p_assignment_action_id => p_assignment_action_id);
87 end if;
88 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'B') = 1 THEN
89 l_ni_b_total_value := pay_gb_payroll_actions_pkg.report_balance_items
90 (p_balance_name => 'NI B Total',
91 p_dimension => l_tax_district_ytd,
92 p_assignment_action_id => p_assignment_action_id);
93 end if;
94 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'D') = 1 THEN
95 l_ni_d_total_value := pay_gb_payroll_actions_pkg.report_balance_items
96 (p_balance_name => 'NI D Total',
97 p_dimension => l_tax_district_ytd,
98 p_assignment_action_id => p_assignment_action_id);
99 end if;
100 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'E') = 1 THEN
101 l_ni_e_total_value := pay_gb_payroll_actions_pkg.report_balance_items
102 (p_balance_name => 'NI E Total',
103 p_dimension => l_tax_district_ytd,
104 p_assignment_action_id => p_assignment_action_id);
105 end if;
106 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'F') = 1 THEN
107 l_ni_f_total_value := pay_gb_payroll_actions_pkg.report_balance_items
108 (p_balance_name => 'NI F Total',
109 p_dimension => l_tax_district_ytd,
110 p_assignment_action_id => p_assignment_action_id);
111 end if;
112 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'G') = 1 THEN
113 l_ni_g_total_value := pay_gb_payroll_actions_pkg.report_balance_items
114 (p_balance_name => 'NI G Total',
115 p_dimension => l_tax_district_ytd,
116 p_assignment_action_id => p_assignment_action_id);
117 end if;
118 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'L') = 1 THEN
119 l_ni_l_total_value := pay_gb_payroll_actions_pkg.report_balance_items
120 (p_balance_name => 'NI L Total',
121 p_dimension => l_tax_district_ytd,
122 p_assignment_action_id => p_assignment_action_id);
123 end if;
124 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'J') = 1 THEN
125 l_ni_j_total_value := pay_gb_payroll_actions_pkg.report_balance_items
126 (p_balance_name => 'NI J Total',
127 p_dimension => l_tax_district_ytd,
128 p_assignment_action_id => p_assignment_action_id);
129 end if;
130 IF HR_GBBAL.NI_CATEGORY_EXISTS_IN_YEAR(p_assignment_action_id,'S') = 1 THEN
131 l_ni_s_total_value := pay_gb_payroll_actions_pkg.report_balance_items
132 (p_balance_name => 'NI S Total',
133 p_dimension => l_tax_district_ytd,
134 p_assignment_action_id => p_assignment_action_id);
135 end if;
136
137 l_employer_balance := l_ni_a_total_value + l_ni_b_total_value + l_ni_d_total_value
138 + l_ni_e_total_value + l_ni_f_total_value + l_ni_g_total_value
139 + l_ni_j_total_value + l_ni_l_total_value + l_ni_s_total_value;
140
141 l_temp_balance := pay_gb_payroll_actions_pkg.report_all_ni_balance
142 (p_balance_name => 'NI Employee',
143 p_dimension => l_tax_district_ytd,
144 p_assignment_action_id => p_assignment_action_id);
145
146 l_employer_balance := l_employer_balance - l_temp_balance;
147
148 l_temp_balance := pay_gb_payroll_actions_pkg.report_balance_items
149 (p_balance_name => 'NI C Employer',
150 p_dimension => l_tax_district_ytd,
151 p_assignment_action_id => p_assignment_action_id);
152
153 l_employer_balance := l_employer_balance + l_temp_balance;
154
155 l_temp_balance := pay_gb_payroll_actions_pkg.report_balance_items
156 (p_balance_name => 'NI S Employer',
157 p_dimension => l_tax_district_ytd,
158 p_assignment_action_id => p_assignment_action_id);
159
160 l_employer_balance := l_employer_balance + l_temp_balance;
161
162 return l_employer_balance;
163 END getEmployerBalance;
164
165 ---------------------------------------------------------------------
166 --- Function : getBalances
167 ---
168 --- Text : Similar to core function : pay_soe_glb.getBalances
169 --- This fuction will check for prepayment run. If action is
170 --- from prepayment, we will use latest run instead
171 ---------------------------------------------------------------------
172 FUNCTION getBalances(p_assignment_action_id number ,p_balance_attribute varchar2) return long is
173
174 TYPE balance_type_lst_rec is RECORD (balance_name varchar2(80)
175 ,reporting_name varchar2(80)
176 ,dimension_name varchar2(80)
177 ,defined_balance_name varchar2(80)
178 ,defined_balance_id number);
179 TYPE balance_type_lst_tab is TABLE of balance_type_lst_rec INDEX BY BINARY_INTEGER;
180 --
181 l_balance_type_lst balance_type_lst_tab;
182 --
183 l_effective_date date;
184 l_earliest_ctx_date date;
185 l_temp_date date;
186 l_action_sequence number;
187 l_payroll_id number;
188 l_assignment_id number;
189 l_business_group_id number;
190 l_legislation_code varchar2(30);
191 l_save_asg_run_bal varchar2(30);
192 l_inp_val_name pay_input_values_f.name%type;
193 l_si_needed_chr varchar2(10);
194 l_st_needed_chr varchar2(10);
195 l_sn_needed_chr varchar2(10);
196 l_st2_needed_chr varchar2(10);
197 l_found boolean;
198 balCount number;
199 --
200 l_defined_balance_lst pay_balance_pkg.t_balance_value_tab;
201 l_context_lst pay_balance_pkg.t_context_tab;
202 l_output_table pay_balance_pkg.t_detailed_bal_out_tab;
203 --
204 i number;
205 l_calculated_balance number;
206 l_display boolean;
207 --
208 cursor getAction is
209 select pa.payroll_id
210 , aa.action_sequence
211 , pa.effective_date
212 , aa.assignment_id
213 , pa.business_group_id
214 , bg.legislation_code
215 , lrl.rule_mode
216 from pay_payroll_actions pa
217 , pay_assignment_actions aa
218 , per_business_groups bg
219 , pay_legislation_rules lrl
220 where aa.assignment_action_id = p_assignment_action_id
221 and aa.payroll_action_id = pa.payroll_action_id
222 and pa.business_group_id = bg.business_group_id
223 and lrl.legislation_code(+) = bg.legislation_code
224 and lrl.rule_type(+) = 'SAVE_ASG_RUN_BAL';
225 --
226 cursor csr_get_DBal is
227 select ba.defined_balance_id
228 , bd.dimension_name
229 , bd.period_type
230 , bt.balance_name
231 , bt.reporting_name
232 , nvl(oi.org_information7,nvl(bt.reporting_name,bt.balance_name)) defined_balance_name
233 from pay_balance_attributes ba
234 , pay_bal_attribute_definitions bad
235 , pay_defined_balances db
236 , pay_balance_dimensions bd
237 , pay_balance_types_tl bt
238 , hr_organization_information oi
239 where bad.attribute_name = p_balance_attribute
240 and ( bad.BUSINESS_GROUP_ID IS NULL
241 OR bad.BUSINESS_GROUP_ID = l_business_group_id)
242 AND ( bad.LEGISLATION_CODE IS NULL
243 OR bad.LEGISLATION_CODE = 'GB')
244 and bad.attribute_id = ba.attribute_id
245 and ba.defined_balance_id = db.defined_balance_id
246 and db.balance_dimension_id = bd.balance_dimension_id
247 and db.balance_type_id = bt.balance_type_id
248 and bt.language = userenv('LANG')
249 and oi.org_information1 = 'BALANCE'
250 and oi.org_information4 = to_char(bt.balance_type_id)
251 and oi.org_information5 = to_char(db.balance_dimension_id)
252 and oi.org_information_context = 'Business Group:SOE Detail'
253 and oi.organization_id = l_business_group_id;
254 --
255 cursor getRBContexts is
256 select rb.TAX_UNIT_ID
257 , rb.JURISDICTION_CODE
258 , rb.SOURCE_ID
259 , rb.SOURCE_TEXT
260 , rb.SOURCE_NUMBER
261 , rb.SOURCE_TEXT2
262 from pay_run_balances rb
263 , pay_assignment_actions aa
264 , pay_payroll_actions pa
265 where rb.ASSIGNMENT_ID = l_assignment_id
266 and l_action_sequence >= aa.action_sequence
270 --
267 and rb.assignment_action_id = aa.assignment_action_id
268 and aa.payroll_action_id = pa.payroll_action_id
269 and pa.effective_date >= l_earliest_ctx_date;
271 cursor getRRContexts is
272 select distinct
273 aa.tax_unit_id tax_unit_id
274 , rr.jurisdiction_code jurisdiction_code
275 , decode(l_si_needed_chr,
276 'Y', pay_balance_pkg.find_context('SOURCE_ID'
277 ,rr.run_result_id)
278 ,null) source_id
279 , decode(l_st_needed_chr,
280 'Y', pay_balance_pkg.find_context('SOURCE_TEXT'
281 ,rr.run_result_id)
282 ,null) source_text
283 , decode(l_sn_needed_chr,
284 'Y', pay_balance_pkg.find_context('SOURCE_NUMBER'
285 ,rr.run_result_id)
286 ,null) source_number
287 , decode(l_st2_needed_chr,
288 'Y', pay_balance_pkg.find_context('SOURCE_TEXT2'
289 ,rr.run_result_id)
290 ,null) source_text2
291 from pay_assignment_actions aa,
292 pay_payroll_actions pa,
293 pay_run_results rr
294 where aa.ASSIGNMENT_ID = l_assignment_id
295 and aa.assignment_action_id = rr.assignment_action_id
296 and l_action_sequence >= aa.action_sequence
297 and aa.payroll_action_id = pa.payroll_action_id
298 and pa.effective_date >= l_earliest_ctx_date;
299 --
300 BEGIN
301 --
302 open getAction;
303 fetch getAction into l_payroll_id,
304 l_action_sequence,
305 l_effective_date,
306 l_assignment_id,
307 l_business_group_id,
308 l_legislation_code,
309 l_save_asg_run_bal;
310 close getAction;
311 --
312 l_earliest_ctx_date := l_effective_date;
313
314 i := 0;
315
316 for db in csr_get_DBal loop
317 i := i + 1;
318 l_defined_balance_lst(i).defined_balance_id := db.defined_balance_id;
319 l_balance_type_lst(db.defined_balance_id).balance_name := db.balance_name;
320 l_balance_type_lst(db.defined_balance_id).reporting_name := db.reporting_name;
321 l_balance_type_lst(db.defined_balance_id).defined_balance_name:= db.defined_balance_name;
322 l_balance_type_lst(db.defined_balance_id).dimension_name := db.dimension_name;
323 l_balance_type_lst(db.defined_balance_id).defined_balance_id := db.defined_balance_id;
324 --
325 pay_balance_pkg.get_period_type_start
326 (p_period_type => db.period_type
327 ,p_effective_date => l_effective_date
328 ,p_payroll_id => l_payroll_id
329 ,p_start_date => l_temp_date);
330 --
331 if l_temp_date < l_earliest_ctx_date then
332 l_earliest_ctx_date := l_temp_date;
333 end if;
334 end loop;
335
336 i := 0;
337 if l_save_asg_run_bal = 'Y' then
338 for ctx in getRBContexts loop
339 i := i + 1;
340 l_context_lst(i).TAX_UNIT_ID := ctx.TAX_UNIT_ID;
341 l_context_lst(i).JURISDICTION_CODE := ctx.JURISDICTION_CODE;
342 l_context_lst(i).SOURCE_ID := ctx.SOURCE_ID;
343 l_context_lst(i).SOURCE_TEXT := ctx.SOURCE_TEXT;
344 l_context_lst(i).SOURCE_NUMBER := ctx.SOURCE_NUMBER;
345 l_context_lst(i).SOURCE_TEXT2 := ctx.SOURCE_TEXT2;
346 end loop;
347 else
348 -- Check whether the SOURCE_ID, SOURCE_TEXT contexts are used.
349 l_si_needed_chr := 'N';
350 l_st_needed_chr := 'N';
351 l_sn_needed_chr := 'N';
352 l_st2_needed_chr := 'N';
353 --
354 pay_core_utils.get_leg_context_iv_name('SOURCE_ID',
355 l_legislation_code,
356 l_inp_val_name,
357 l_found);
358 if (l_found = TRUE) then
359 l_si_needed_chr := 'Y';
360 end if;
361 --
362 pay_core_utils.get_leg_context_iv_name('SOURCE_TEXT',
363 l_legislation_code,
364 l_inp_val_name,
365 l_found);
366 if (l_found = TRUE) then
367 l_st_needed_chr := 'Y';
368 end if;
369 --
370 pay_core_utils.get_leg_context_iv_name('SOURCE_NUMBER',
371 l_legislation_code,
372 l_inp_val_name,
373 l_found);
374 if (l_found = TRUE) then
375 l_sn_needed_chr := 'Y';
376 end if;
377 --
378 pay_core_utils.get_leg_context_iv_name('SOURCE_TEXT2',
379 l_legislation_code,
380 l_inp_val_name,
381 l_found);
382 if (l_found = TRUE) then
383 l_st2_needed_chr := 'Y';
384 end if;
385 --
386 --
387 for ctx in getRRContexts loop
388 i := i + 1;
389 l_context_lst(i).TAX_UNIT_ID := ctx.TAX_UNIT_ID;
390 l_context_lst(i).JURISDICTION_CODE := ctx.JURISDICTION_CODE;
391 l_context_lst(i).SOURCE_ID := ctx.SOURCE_ID;
392 l_context_lst(i).SOURCE_TEXT := ctx.SOURCE_TEXT;
393 l_context_lst(i).SOURCE_NUMBER := ctx.SOURCE_NUMBER;
397
394 l_context_lst(i).SOURCE_TEXT2 := ctx.SOURCE_TEXT2;
395 end loop;
396 end if;
398 pay_balance_pkg.get_value (p_assignment_action_id => p_assignment_action_id
399 ,p_defined_balance_lst => l_defined_balance_lst
400 ,p_context_lst => l_context_lst
401 ,p_output_table => l_output_table);
402 --
403 pay_soe_util.clear;
404 --
405 balCount := 0;
406 if l_output_table.count > 0 then
407 for i in l_output_table.first..l_output_table.last loop
408 if l_output_table(i).balance_value <> 0 then
409 balCount := balCount + 1;
410 pay_soe_util.setValue('01',l_balance_type_lst(l_output_table(i).defined_balance_id).balance_name,TRUE,FALSE);
411 pay_soe_util.setValue('02',l_balance_type_lst(l_output_table(i).defined_balance_id).reporting_name,FALSE,FALSE);
412 pay_soe_util.setValue('03',l_balance_type_lst(l_output_table(i).defined_balance_id).dimension_name,FALSE,FALSE);
413 pay_soe_util.setValue('04',l_balance_type_lst(l_output_table(i).defined_balance_id).defined_balance_name,FALSE,FALSE);
414 pay_soe_util.setValue('16',to_char(l_output_table(i).balance_value,
415 fnd_currency.get_format_mask(substr(PAY_SOE_GLB.g_currency_code,2,3),40)),FALSE,FALSE);
416 pay_soe_util.setValue('06',to_char(l_output_table(i).defined_balance_id),FALSE,TRUE);
417 end if;
418 end loop;
419 end if;
420
421 l_display := FALSE;
422 l_calculated_balance := pay_gb_payroll_actions_pkg.report_all_ni_balance('NI Able',p_assignment_action_id,'_ASG_TD_YTD');
423 hr_utility.trace('NI Able : ' || l_calculated_balance);
424 if l_calculated_balance <> 0 then
425 l_display := TRUE;
426 pay_soe_util.setValue('01',null,TRUE,FALSE);
427 pay_soe_util.setValue('02',null,FALSE,FALSE);
428 pay_soe_util.setValue('03','ASG_TD_YTD',FALSE,FALSE);
429 pay_soe_util.setValue('04','NIable YTD',FALSE,FALSE);
430 pay_soe_util.setValue('16',to_char(l_calculated_balance,
431 fnd_currency.get_format_mask(substr(PAY_SOE_GLB.g_currency_code,2,3),40)),FALSE,FALSE);
432 pay_soe_util.setValue('06',null,FALSE,TRUE);
433 end if;
434
435 l_calculated_balance := pay_gb_payroll_actions_pkg.report_all_ni_balance('NI Able',p_assignment_action_id,'_ASG_TRANSFER_PTD');
436 hr_utility.trace('NI Able PTD : ' || l_calculated_balance);
437 if l_calculated_balance <> 0 then
438 l_display := TRUE;
439 pay_soe_util.setValue('01',null,TRUE,FALSE);
440 pay_soe_util.setValue('02',null,FALSE,FALSE);
441 pay_soe_util.setValue('03','ASG_TRANSFER_PTD',FALSE,FALSE);
442 pay_soe_util.setValue('04','NIable PTD',FALSE,FALSE);
443 pay_soe_util.setValue('16',to_char(l_calculated_balance,
444 fnd_currency.get_format_mask(substr(PAY_SOE_GLB.g_currency_code,2,3),40)),FALSE,FALSE);
445 pay_soe_util.setValue('06',null,FALSE,TRUE);
446 end if;
447
448 l_calculated_balance := pay_gb_payroll_actions_pkg.report_all_ni_balance('NI Employee',p_assignment_action_id,'_ASG_TD_YTD');
449 hr_utility.trace('NI Employee : ' || l_calculated_balance);
450 if l_calculated_balance <> 0 then
451 l_display := TRUE;
452 pay_soe_util.setValue('01',null,TRUE,FALSE);
453 pay_soe_util.setValue('02',null,FALSE,FALSE);
454 pay_soe_util.setValue('03','ASG_TD_YTD',FALSE,FALSE);
455 pay_soe_util.setValue('04','NI Ees YTD',FALSE,FALSE);
456 pay_soe_util.setValue('16',to_char(l_calculated_balance,
457 fnd_currency.get_format_mask(substr(PAY_SOE_GLB.g_currency_code,2,3),40)),FALSE,FALSE);
458 pay_soe_util.setValue('06',null,FALSE,TRUE);
459 end if;
460
461 l_calculated_balance := getEmployerBalance(p_assignment_action_id);
462 hr_utility.trace('NI Employee : ' || l_calculated_balance);
463 if l_calculated_balance <> 0 then
464 l_display := TRUE;
465 pay_soe_util.setValue('01',null,TRUE,FALSE);
466 pay_soe_util.setValue('02',null,FALSE,FALSE);
467 pay_soe_util.setValue('03','ASG_TD_YTD',FALSE,FALSE);
468 pay_soe_util.setValue('04','NI Ers YTD',FALSE,FALSE);
469 pay_soe_util.setValue('16',to_char(l_calculated_balance,
470 fnd_currency.get_format_mask(substr(PAY_SOE_GLB.g_currency_code,2,3),40)),FALSE,FALSE);
471 pay_soe_util.setValue('06',null,FALSE,TRUE);
472 end if;
473
474 if balCount > 0 or l_display then
475 return pay_soe_util.genCursor;
476 else
477 return ('select null COL01 from dual where 1=0');
478 end if;
479
480 END getBalances;
481
482 ---------------------------------------------------------------------
483 --- Function : Balances1
484 ---
485 --- Text : Displays the Balances in the Balances Region
486 ---------------------------------------------------------------------
487 function Balances1(p_assignment_action_id number) return long is
488 begin
489 return getBalances(checkPrepayment(p_assignment_action_id)
490 ,pay_soe_util.getConfig('BALANCES1'));
491 end Balances1;
492
493 --
494 ---------------------------------------------------------------------
495 --- Function : Balances2
496 ---
497 --- Text : Displays the Balances in the Balances Region
498 ---------------------------------------------------------------------
499 function Balances2(p_assignment_action_id number) return long is
500 begin
501 return pay_soe_glb.balances2(checkPrepayment(p_assignment_action_id));
502 end Balances2;
503
504 ---------------------------------------------------------------------
505 --- Function : Balances3
506 ---
507 --- Text : Displays the Balances in the Balances Region
508 ---------------------------------------------------------------------
512 end Balances3;
509 function Balances3(p_assignment_action_id number) return long is
510 begin
511 return pay_soe_glb.balances3(checkPrepayment(p_assignment_action_id));
513
514 ---------------------------------------------------------------------
515 --- Function : Tax_Info
516 ---
517 --- Text : Fetches Tax Information
518 ---------------------------------------------------------------------
519 FUNCTION Tax_Info(p_assignment_action_id NUMBER) RETURN LONG IS
520 cursor getInfo is
521 select ppa.date_earned,
522 ppa.payroll_action_id,
523 paa.assignment_id
524 from pay_payroll_actions ppa,
525 pay_assignment_actions paa
526 where paa.assignment_action_id = p_assignment_action_id
527 and paa.payroll_action_id = ppa.payroll_action_id;
528
529 cursor getTaxPhone(p_payroll_act number) is
530 select max(org_information8)
531 from pay_payrolls_f p,
532 pay_payroll_actions pact,
533 hr_soft_coding_keyflex flex,
534 hr_organization_information org
535 where p.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
536 and org.org_information_context = 'Tax Details References'
537 and org.org_information1 = flex.segment1
538 and p.business_group_id = org.organization_id
539 and pact.payroll_action_id = p_payroll_act
540 and pact.payroll_id = p.payroll_id
541 and pact.effective_date between p.effective_start_date and p.effective_end_date;
542
543 res getInfo%rowtype;
544 l_tax_period varchar2(30);
545 l_tax_ref varchar2(30);
546 l_tax_code varchar2(30);
547 l_tax_basis varchar2(30);
548 l_ni_cat varchar2(30);
549 l_tax_phone varchar2(30);
550 l_asg_act_id number;
551 l_sql long;
552 begin
553 open getInfo;
554 fetch getInfo into res;
555 close getInfo;
556
557 open getTaxPhone(res.payroll_action_id);
558 fetch getTaxPhone into l_tax_phone;
559 close getTaxPhone;
560
561 l_asg_act_id := checkPrepayment(p_assignment_action_id);
562
563 pay_gb_payroll_actions_pkg.get_database_items
564 (p_assignment_id => res.assignment_id,
565 p_run_assignment_action_id => l_asg_act_id,
566 p_date_earned => to_char(res.date_earned,'YYYY/MM/DD'),
567 p_payroll_action_id => res.payroll_action_id,
568 p_tax_period => l_tax_period,
569 p_tax_refno => l_tax_ref,
570 p_tax_code => l_tax_code,
571 p_tax_basis => l_tax_basis,
572 p_ni_category => l_ni_cat);
573
574 pay_soe_util.clear;
575 pay_soe_util.setValue('01',l_tax_period,TRUE ,FALSE);
576 pay_soe_util.setValue('02',l_tax_ref ,FALSE,FALSE);
577 pay_soe_util.setValue('03',l_tax_phone ,FALSE,FALSE);
578 pay_soe_util.setValue('04',l_tax_code ,FALSE,FALSE);
579 pay_soe_util.setValue('05',l_tax_basis ,FALSE,FALSE);
580 pay_soe_util.setValue('06',l_ni_cat ,FALSE,TRUE);
581
582 return pay_soe_util.genCursor;
583 end Tax_Info;
584
585 ---------------------------------------------------------------------
586 --- Function : SetParameters
587 ---
588 --- Text : Set paramters
589 ---------------------------------------------------------------------
590 function setParameters(p_assignment_action_id in number) return varchar2 is
591 begin
592 return (pay_soe_glb.setParameters(p_assignment_action_id));
593 end setParameters;
594
595 ---------------------------------------------------------------------
596 --- Function : SetParameters (Overload function)
597 ---
598 --- Text : Set parameters
599 ---------------------------------------------------------------------
600 function setParameters(p_person_id in number, p_assignment_id in number, p_effective_date date) return varchar2 is
601
602 cursor csr_get_asg_id is
603 select assignment_id
604 from per_all_assignments_f
605 where person_id = p_person_id
606 and p_effective_date between effective_start_date and effective_end_date;
607
608 cursor csr_get_action_id(asg_id number) is
609 select to_number(substr(max(to_char(pa.effective_date,'J')||lpad(aa.assignment_action_id,15,'0')),8))
610 from pay_payroll_actions pa,
611 pay_assignment_actions aa,
612 per_time_periods ptp
613 where aa.action_status = 'C'
614 and pa.payroll_action_id = aa.payroll_action_id
615 and aa.assignment_id = asg_id
616 and ptp.payroll_id = pa.payroll_id
617 and pa.effective_date <= ptp.regular_payment_date
618 and p_effective_date between ptp.start_date and ptp.end_date
619 and pa.action_type in ('P','Q','R','U');
620
621 l_assignment_action_id number;
622 l_assignment_id number;
623
624 begin
625 l_assignment_id := p_assignment_id;
626 if l_assignment_id is null then
627 open csr_get_asg_id;
628 fetch csr_get_asg_id into l_assignment_id;
629 close csr_get_asg_id;
630 end if;
631
632 open csr_get_action_id(l_assignment_id);
633 fetch csr_get_action_id into l_assignment_action_id;
634 close csr_get_action_id;
635
636 return (pay_soe_glb.setParameters(l_assignment_action_id));
637
638 end setParameters;
639
640 end pay_gb_online_soe;