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