1 PACKAGE BODY PAY_GB_HR_HELPDESK AS
2 /* $Header: pygbhelpdesk.pkb 120.2 2011/03/01 14:28:51 krreddy noship $ */
3 gv_package_name VARCHAR2(100);
4
5 -- This procedure is used to fetch gb hr helpdesk data.
6 -- ----------------------------------------------------------------------------
7 -- |-------------------------< GET_UKPAY_DETAILS >--------------------------|
8 -- ----------------------------------------------------------------------------
9 procedure GET_UKPAY_DETAILS (p_per_id number,
10 p_bg_id number,
11 p_eff_date date,
12 p_leg_code varchar2,
13 --p_pyrl_dtls out nocopy HR_PERSON_PAY_RECORD.PAYROLL_RECORD
14 p_pyrl_dtls out nocopy HR_PERSON_RECORD.PAYROLL_RECORD,
15 p_error out nocopy varchar2)
16 is
17 -- declaration for uk payroll starts here
18
19 cursor csr_ukpay_req (p_person_id number,p_eff_date date) is
20 /*select to_char(action_context_id) assignment_action_id
21 from pay_emp_payslip_action_info_v
22 where person_id = p_person_id
23 and effective_date = (select max(effective_date)
24 from pay_emp_payslip_action_info_v
25 where person_id = p_person_id
26 and effective_date <= p_eff_date);*/
27
28 SELECT
29 DISTINCT
30 paa.assignment_action_id
31 FROM
32 pay_payroll_actions ppa,
33 pay_assignment_actions paa,
34 per_assignments_f paf,
35 per_people_f ppf
36 WHERE ppa.action_type = 'X'
37 AND ppa.action_status = 'C'
38 AND ppa.report_type = 'UKPS'
39 AND ppa.payroll_action_id = paa.payroll_action_id
40 AND paa.assignment_id = paf.assignment_id
41 AND paf.person_id = ppf.person_id
42 AND ppf.person_id = p_person_id
43 AND ppa.effective_date = (
44 SELECT
45 max(ppa1.effective_date)
46 FROM
47 pay_payroll_actions ppa1,
48 pay_assignment_actions paa1
49 WHERE ppa1.effective_date <= p_eff_date
50 AND ppa1.action_type = 'X'
51 AND ppa1.action_status = 'C'
52 AND ppa1.report_type = 'UKPS'
53 AND ppa1.payroll_action_id = paa1.payroll_action_id
54 AND paa1.assignment_id = paa.assignment_id
55 AND ppa1.business_group_id = ppa.business_group_id )
56 and SOURCE_ACTION_ID is null -- add here to avoid child action
57 AND ppa.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date;
58
59
60 -- cursor modified for the bug 8233506
61
62 cursor csr_ukpay_ps_det(p_asg_action_id number) is
63 select organization_name ,
64 location_name ,
65 job ,
66 payroll_name ,
67 to_char(payment_date,'YYYY-MM-DD'),
68 pbg.currency_code ,
69 to_char(beginning_date,'YYYY-MM-DD') ,
70 to_char(ending_date,'YYYY-MM-DD'),
71 paa.assignment_id
72 from pay_employee_action_info_v empv,
73 pay_assignment_actions paa,
74 per_business_groups pbg,
75 per_all_assignments_f paaf
76 where empv.action_context_id = p_asg_action_id
77 and empv.action_context_id = paa.assignment_action_id
78 and paa.assignment_id = nvl(empv.assignment_id,paa.assignment_id)
79 and paa.assignment_id = paaf.assignment_id
80 and payment_date between paaf.effective_start_date and paaf.effective_end_date
81 and paaf.business_group_id = pbg.business_group_id;
82
83 -- cursor to fetch the run type
84 cursor csr_run_type(p_assignment_action_id number) is
85 select prtf.run_type_name
86 from pay_action_interlocks lck,
87 pay_assignment_actions paa1,
88 pay_action_interlocks pac,
89 pay_assignment_actions paa,
90 pay_run_types_f prtf
91 where lck.locked_action_id = paa1.assignment_action_id
92 and paa1.assignment_action_id = pac.locking_action_id
93 and pac.locked_action_id = paa.assignment_action_id
94 and lck.locking_action_id = p_assignment_action_id
95 and paa.source_action_id is not null
96 and prtf.run_type_id = paa.run_type_id
97 and prtf.legislation_code = 'GB';
98
99 /*cursor csr_run_type(p_assignment_id number,p_eff_date date) is
100 select prtf.run_type_name
101 from pay_payroll_actions ppa,
102 pay_assignment_actions paa,
103 pay_run_types_f prtf
104 where paa.assignment_action_id in (SELECT + USE_NL(paa, pact, ptp)
105 to_number(substr(max(lpad(paa.action_sequence,15,'0')||
106 paa.assignment_action_id),16)) assignment_action_id
107 FROM pay_assignment_actions paa,
108 pay_payroll_actions pact
109 WHERE paa.assignment_id = p_assignment_id
110 AND paa.payroll_action_id = pact.payroll_action_id
111 AND pact.action_type IN ('Q','R','B','I','V')
112 AND paa.action_status = 'C'
113 AND pact.effective_date <= p_eff_date)
114 and ppa.payroll_action_id = paa.payroll_action_id
115 and prtf.run_type_id = ppa.run_type_id
116 and prtf.legislation_code = 'GB';*/
117
118 --cursor to fetch the UK earnings current value
119
120 cursor csr_uk_earnings_cv(p_assignment_action_id number) is
121 SELECT /*+ leading(lck,paa2) */
122 --pai.action_information4 NARRATIVE,
123 SUM(FND_NUMBER.CANONICAL_TO_NUMBER(prv.result_value)) value
124 FROM pay_action_interlocks lck, -- archive action locking prepayment
125 pay_assignment_actions paa1, -- prepayment action
126 pay_assignment_actions paa2, -- archive action
127 pay_payroll_actions ppa, -- prepayment
128 pay_action_information pai, -- archived element/input value definition
129 pay_action_interlocks pac, -- prepayment locking payroll run/quickpay
130 pay_assignment_actions paa, -- payroll run/quickpay action
131 pay_payroll_actions ppa1, -- payroll run/quickpay action
132 pay_element_types_f pet, -- element types processed by the payroll run/quickpay
133 pay_input_values_f piv, -- "Pay values" of type Money
134 pay_run_results prr, -- run result created by the payroll run/quick pay
135 pay_run_result_values prv -- Run Result value (Pay Value) created by the payroll run/quickpay
136 WHERE lck.locking_action_id = paa2.assignment_action_id
137 AND paa2.payroll_action_id = pai.action_context_id
138 AND pai.action_context_type = 'PA'
139 AND pai.action_information_category = 'EMEA ELEMENT DEFINITION'
140 AND lck.locked_action_id = paa1.assignment_action_id
141 AND paa1.source_action_id IS NULL
142 AND paa1.payroll_action_id = ppa.payroll_action_id
143 AND ppa.action_type IN ('P','U')
144 AND ppa.payroll_action_id = NVL (pai.action_information1,ppa.payroll_action_id)
145 AND paa1.assignment_action_id = pac.locking_action_id
146 AND pet.element_type_id = pai.action_information2
147 AND pet.element_type_id = piv.element_type_id
148 AND piv.input_value_id = pai.action_information3
149 AND prr.element_type_id = pet.element_type_id
150 AND prr.status IN ('P','PA')
151 AND prv.input_value_id = piv.input_value_id
152 AND prv.run_result_id = prr.run_result_id
153 AND piv.name = 'Pay Value'
154 AND piv.uom = 'M'
155 AND pac.locked_action_id = prr.assignment_action_id
156 AND pac.locked_action_id = paa.assignment_action_id
157 AND paa.payroll_action_id = ppa1.payroll_action_id
158 AND ppa1.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
159 AND ppa1.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
160 AND lck.locking_action_id = p_assignment_action_id
161 AND pai.action_information5 in ( 'E','P')
162 GROUP BY lck.locking_action_id;
163
164 -- cursor to fetch the tax and national insurance current value
165 cursor csr_uk_tx_cv(p_assignment_action_id number) is
166 SELECT /*+ leading(lck,paa2) */
167 SUM(FND_NUMBER.CANONICAL_TO_NUMBER(prv.result_value)) value
168 FROM pay_action_interlocks lck, -- archive action locking prepayment
169 pay_assignment_actions paa1, -- prepayment action
170 pay_assignment_actions paa2, -- archive action
171 pay_payroll_actions ppa, -- prepayment
172 pay_action_information pai, -- archived element/input value definition
173 pay_action_interlocks pac, -- prepayment locking payroll run/quickpay
174 pay_assignment_actions paa, -- payroll run/quickpay action
175 pay_payroll_actions ppa1, -- payroll run/quickpay action
176 pay_element_types_f pet, -- element types processed by the payroll run/quickpay
177 pay_input_values_f piv, -- "Pay values" of type Money
178 pay_run_results prr, -- run result created by the payroll run/quick pay
179 pay_run_result_values prv -- Run Result value (Pay Value) created by the payroll run/quickpay
180 WHERE lck.locking_action_id = paa2.assignment_action_id
181 AND paa2.payroll_action_id = pai.action_context_id
182 AND pai.action_context_type = 'PA'
183 AND pai.action_information_category = 'EMEA ELEMENT DEFINITION'
184 AND lck.locked_action_id = paa1.assignment_action_id
185 AND paa1.source_action_id IS NULL
186 AND paa1.payroll_action_id = ppa.payroll_action_id
187 AND ppa.action_type IN ('P','U')
188 AND ppa.payroll_action_id = NVL (pai.action_information1,ppa.payroll_action_id)
189 AND paa1.assignment_action_id = pac.locking_action_id
190 AND pet.element_type_id = pai.action_information2
191 AND pet.element_type_id = piv.element_type_id
192 AND piv.input_value_id = pai.action_information3
193 AND prr.element_type_id = pet.element_type_id
194 AND prr.status IN ('P','PA')
195 AND prv.input_value_id = piv.input_value_id
196 AND prv.run_result_id = prr.run_result_id
197 AND piv.name = 'Pay Value'
198 AND piv.uom = 'M'
199 AND pac.locked_action_id = prr.assignment_action_id
200 AND pac.locked_action_id = paa.assignment_action_id
201 AND paa.payroll_action_id = ppa1.payroll_action_id
202 AND ppa1.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
203 AND ppa1.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
204 AND lck.locking_action_id = p_assignment_action_id
205 AND pai.action_information5 in ('D', NULL)
206 AND pai.action_information4 = ('PAYE')
207 GROUP BY lck.locking_action_id, pet.element_type_id, piv.input_value_id, pai.action_information4, pai.action_information5;
208
209 -- cursor to fetch the tax and national insurance current value
210 cursor csr_uk_ni_cv(p_assignment_action_id number) is
211 SELECT /*+ leading(lck,paa2) */
212 SUM(FND_NUMBER.CANONICAL_TO_NUMBER(prv.result_value)) value
213 FROM pay_action_interlocks lck, -- archive action locking prepayment
214 pay_assignment_actions paa1, -- prepayment action
215 pay_assignment_actions paa2, -- archive action
216 pay_payroll_actions ppa, -- prepayment
217 pay_action_information pai, -- archived element/input value definition
218 pay_action_interlocks pac, -- prepayment locking payroll run/quickpay
219 pay_assignment_actions paa, -- payroll run/quickpay action
220 pay_payroll_actions ppa1, -- payroll run/quickpay action
221 pay_element_types_f pet, -- element types processed by the payroll run/quickpay
222 pay_input_values_f piv, -- "Pay values" of type Money
223 pay_run_results prr, -- run result created by the payroll run/quick pay
224 pay_run_result_values prv -- Run Result value (Pay Value) created by the payroll run/quickpay
225 WHERE lck.locking_action_id = paa2.assignment_action_id
226 AND paa2.payroll_action_id = pai.action_context_id
227 AND pai.action_context_type = 'PA'
228 AND pai.action_information_category = 'EMEA ELEMENT DEFINITION'
229 AND lck.locked_action_id = paa1.assignment_action_id
230 AND paa1.source_action_id IS NULL
231 AND paa1.payroll_action_id = ppa.payroll_action_id
232 AND ppa.action_type IN ('P','U')
233 AND ppa.payroll_action_id = NVL (pai.action_information1,ppa.payroll_action_id)
234 AND paa1.assignment_action_id = pac.locking_action_id
235 AND pet.element_type_id = pai.action_information2
236 AND pet.element_type_id = piv.element_type_id
237 AND piv.input_value_id = pai.action_information3
238 AND prr.element_type_id = pet.element_type_id
239 AND prr.status IN ('P','PA')
240 AND prv.input_value_id = piv.input_value_id
241 AND prv.run_result_id = prr.run_result_id
242 AND piv.name = 'Pay Value'
243 AND piv.uom = 'M'
244 AND pac.locked_action_id = prr.assignment_action_id
245 AND pac.locked_action_id = paa.assignment_action_id
246 AND paa.payroll_action_id = ppa1.payroll_action_id
247 AND ppa1.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
248 AND ppa1.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
249 AND lck.locking_action_id = p_assignment_action_id
250 AND pai.action_information5 in ('D', NULL)
251 AND pai.action_information4 like 'NI%'
252 GROUP BY lck.locking_action_id, pet.element_type_id, piv.input_value_id, pai.action_information4, pai.action_information5;
253
254
255 -- cursor to fetch the current net pay value for UK
256 cursor csr_net_pay_cv (p_asg_action_id number) is
257 select ACTION_INFORMATION16
258 from pay_action_information pai
259 where pai.action_context_id = p_asg_action_id
260 and pai.action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION'
261 and pai.action_context_type = 'AAP';
262
263 -- cursor to fetch the tax and earnings YTD
264 cursor csr_tx_er_ytd(p_assignment_action_id number,p_bal_name varchar2) is
265 select pai.ACTION_INFORMATION4
266 from pay_action_information pai,
267 pay_defined_balances pdb,
268 pay_balance_types pbt
269 where pai.action_context_id = p_assignment_action_id -- 182069
270 and to_char(pdb.DEFINED_BALANCE_ID) = (pai.ACTION_INFORMATION1)
271 and pdb.BALANCE_TYPE_ID = pbt.BALANCE_TYPE_ID
272 and pai.action_information_category = 'EMEA BALANCES'
273 and pai.action_context_type = 'AAP'
274 and balance_name = p_bal_name
275 and pbt.legislation_code = 'GB'
276 and pdb.legislation_code = 'GB';
277
278 -- cursor to get the defined balance id
279 cursor csr_def_bal_id(p_assignment_action_id number) is
280 select defined_balance_id
281 from pay_defined_balances pdb,
282 pay_balance_types pbt,
283 pay_balance_dimensions pbd
284 where pbt.balance_name = 'NI '||(select ACTION_INFORMATION23
285 from pay_action_information pai
286 where pai.action_context_id = p_assignment_action_id --182069
287 and pai.action_information_category = 'GB EMPLOYEE DETAILS'
288 and pai.action_context_type = 'AAP')
289 ||' Employee'
290 and pbd.dimension_name = '_ASG_TD_YTD'
291 and pdb.balance_type_id = pbt.balance_type_id
292 and pdb.balance_dimension_id = pbd.balance_dimension_id
293 and pbd.legislation_code='GB'
294 and pbt.legislation_code='GB';
295
296 -- cursor to get the latest_action_id
297 cursor csr_lat_action_id(p_assignment_id number,p_payment_date date)is
298
299 SELECT /*+ USE_NL(paa, pact, ptp) */
300 to_number(substr(max(lpad(paa.action_sequence,15,'0')||
301 paa.assignment_action_id),16)) assignment_action_id
302 FROM pay_assignment_actions paa,
303 pay_payroll_actions pact
304 WHERE paa.assignment_id = p_assignment_id --16986
305 AND paa.payroll_action_id = pact.payroll_action_id
306 AND pact.action_type IN ('Q','R','B','I','V')
307 --AND paa.action_status = 'C'
308 AND paa.action_status in ('C','S') --Modified for the bug 10066755
309 AND pact.effective_date <= p_payment_date;
310
311 -- cursor to fetch the netpay balance id
312 cursor csr_netpay_bal_id is
313 select defined_balance_id
314 from pay_defined_balances pdb,
315 pay_balance_types pbt,
316 pay_balance_dimensions pbd
317 where pbt.balance_name = 'Net Pay'
318 and pbd.dimension_name = '_ASG_TD_YTD'
319 and pdb.balance_type_id = pbt.balance_type_id
320 and pdb.balance_dimension_id = pbd.balance_dimension_id
321 and pbd.legislation_code='GB'
322 and pbt.legislation_code='GB';
323
324 cursor csr_periods_of_service(p_person_id number, p_employee_number number, p_effective_end_date varchar2) is
325 select to_char(pps.adjusted_svc_date,'YYYY-MM-DD') adjusted_svc_date
326 ,to_char(pps.date_start,'YYYY-MM-DD') date_start
327 ,to_char(pps.accepted_termination_date,'YYYY-MM-DD') accepted_termination_date
328 ,to_char(pps.actual_termination_date,'YYYY-MM-DD') actual_termination_date
329 ,to_char(pps.final_process_date,'YYYY-MM-DD') final_process_date
330 ,to_char(pps.last_standard_process_date,'YYYY-MM-DD') last_standard_process_date
331 ,leaving_reason
332 from per_periods_of_service pps
333 where pps.person_id = p_person_id
334 and ( ( p_employee_number is null )
335 or ( p_employee_number is not null
336 and pps.date_start = (
337 select max(pps1.date_start)
338 from per_periods_of_service pps1
339 where pps1.person_id = p_person_id
340 and pps1.date_start <= to_date(p_effective_end_date,'YYYY-MM-DD') ) ) );
341 cursor csr_periods_of_placement(p_person_id number, p_employee_number number, p_effective_end_date varchar2) is
342 select null adjusted_svc_date
343 ,to_char(ppp.date_start,'YYYY-MM-DD') date_start
344 ,null accepted_termination_date
345 ,to_char(ppp.actual_termination_date,'YYYY-MM-DD') actual_termination_date
346 ,to_char(ppp.final_process_date,'YYYY-MM-DD') final_process_date
347 ,to_char(ppp.last_standard_process_date,'YYYY-MM-DD') last_standard_process_date
348 ,termination_reason leaving_reason
349 from per_periods_of_placement ppp
350 where ppp.person_id = p_person_id
351 and (ppp.date_start = (
352 select max(ppp1.date_start)
353 from per_periods_of_placement ppp1
354 where ppp1.person_id = p_person_id
355 and ppp1.date_start <= to_date(p_effective_end_date,'YYYY-MM-DD') ) );
356
357
358
359 p_latest_action_id number;
360 p_netpay_bal_id number;
361 p_def_bal_id number;
362 p_assignment_id per_all_assignments_f.assignment_id%type;
363 p_assg_action_id pay_assignment_actions.assignment_action_id%type;
364 p_pyrl_action_id pay_payroll_actions.payroll_action_id%type;
365
366 p_cnt number;
367 -- Declaration for uk payroll ends here
368
369
370 begin
371 p_cnt := 1;
372
373 open csr_ukpay_req(p_per_id,p_eff_date);
374 loop
375
376 fetch csr_ukpay_req into p_assg_action_id;
377 exit when csr_ukpay_req%notfound;
378
379 -- to fetch the details in the pay summary region
380 open csr_ukpay_ps_det(p_assg_action_id);
381 fetch csr_ukpay_ps_det into p_pyrl_dtls(p_cnt).company,
382 p_pyrl_dtls(p_cnt).address,
383 p_pyrl_dtls(p_cnt).job_title,
384 p_pyrl_dtls(p_cnt).pay_group,
385 p_pyrl_dtls(p_cnt).payment_date,
386 p_pyrl_dtls(p_cnt).currency_code,
387 p_pyrl_dtls(p_cnt).period_begin,
388 p_pyrl_dtls(p_cnt).period_end,
389 p_assignment_id;
390 exit when csr_ukpay_ps_det%notfound;
391 close csr_ukpay_ps_det;
392
393 p_pyrl_dtls(p_cnt).LEGISLATION_CODE := p_leg_code;
394
395 -- fetch the run type for the pay summary region
396 open csr_run_type(p_assg_action_id);
397 fetch csr_run_type into p_pyrl_dtls(p_cnt).RUN_TYPE;
398 close csr_run_type;
399
400 --fetch the earnings current value
401 open csr_uk_earnings_cv(p_assg_action_id);
402 fetch csr_uk_earnings_cv into p_pyrl_dtls(p_cnt).TOTAL_EARNINGS_CV;
403 close csr_uk_earnings_cv;
404
405 -- fetch the tax and national insurance current value
406
407 open csr_uk_ni_cv(p_assg_action_id);
408 fetch csr_uk_ni_cv into p_pyrl_dtls(p_cnt).NI_CV;
409 close csr_uk_ni_cv;
410
411 open csr_uk_tx_cv(p_assg_action_id);
412 fetch csr_uk_tx_cv into p_pyrl_dtls(p_cnt).TOTAL_TAXES_CV;
413 close csr_uk_tx_cv;
414
415 -- fetch the net pay current value
416
417 open csr_net_pay_cv(p_assg_action_id);
418 fetch csr_net_pay_cv into p_pyrl_dtls(p_cnt).TOTAL_NETPAY_CV;
419 close csr_net_pay_cv;
420
421 -- fetch the earnings YTD
422 open csr_tx_er_ytd(p_assg_action_id, 'Gross Pay');
423 fetch csr_tx_er_ytd into p_pyrl_dtls(p_cnt).TOTAL_EARNINGS_YTD;
424 close csr_tx_er_ytd;
425
426 -- fetch the tax YTD
427 open csr_tx_er_ytd(p_assg_action_id, 'PAYE');
428 fetch csr_tx_er_ytd into p_pyrl_dtls(p_cnt).TOTAL_TAXES_YTD;
429 close csr_tx_er_ytd;
430
431 open csr_def_bal_id(p_assg_action_id);
432 fetch csr_def_bal_id into p_def_bal_id;
433 close csr_def_bal_id;
434
435 open csr_netpay_bal_id;
436 fetch csr_netpay_bal_id into p_netpay_bal_id;
437 close csr_netpay_bal_id;
438
439 open csr_lat_action_id(p_assignment_id,to_date(p_pyrl_dtls(p_cnt).payment_date,
440 'YYYY-MM-DD'));
441 fetch csr_lat_action_id into p_latest_action_id;
442 close csr_lat_action_id;
443
444 -- fetch the net pay YTD value
445 if (p_netpay_bal_id is not null) and (p_latest_action_id is not null)
446 then
447 select pay_balance_pkg.get_value(p_netpay_bal_id,p_latest_action_id)
448 into p_pyrl_dtls(p_cnt).TOTAL_NETPAY_YTD
449 from dual ;
450 end if;
451
452 -- fetch the NI YTD
453 if(p_def_bal_id is not null) and (p_latest_action_id is not null)
454 then
455 select pay_balance_pkg.get_value(p_def_bal_id,p_latest_action_id)
456 into p_pyrl_dtls(p_cnt).NI_YTD
457 from dual;
458 end if;
459
460 p_cnt := p_cnt+1;
461
462 end loop;
463
464 close csr_ukpay_req;
465
466 exception when others
467 then
468 p_error := 'FROM UK PAYROLL :'||substr(SQLERRM,1,1500);
469
470 end GET_UKPAY_DETAILS;
471 BEGIN
472 gv_package_name := 'PAY_GB_HR_HELPDESK';
473 END PAY_GB_HR_HELPDESK;
[Home] [Help]