1 package body pay_year_end_extract as
2 /* $Header: payyeext.pkb 115.8 99/10/11 06:38:04 porting ship $ */
3 /*
4 * ---------------------------------------------------------------------------
5 Copyright (c) Oracle Corporation (UK) Ltd 1992.
6 All Rights Reserved.
7 --
8 --
9 PRODUCT
10 Oracle*Payroll
11 NAME
12 payyeext.pkb
13 NOTES
14 GB Year End Extract - populate the year end tables ready for the p35
15 PROCEDURES
16 Trash - clear down year end tables for this permit
17 Extract - populate year end tables for this permit
18 --
19 MODIFIED
20 --
21 asnell 23-OCT-1995 Created
22 asnell 15-NOV-1995 Where no NI balances exist set to Current Cat
23 asnell 17-NOV-1995 Removed hardcoded value for Taxcode fetch
24 asnell 23-NOV-1995 Set termination date only if asg is same
25 period of service and rename table to GB
26 asnell 04-DEC-1995 ensured tax detail refs are within BG
27 asnell 05-DEC-1995 initialized l_ni_y local variable
28 asnell 11-DEC-1995 default taxcode to NI
29 asnell 19-DEC-1995 NI Y set on the assignment row live at prev.eoy
30 asnell 19-DEC-1995 NI Y Last Year set on latest row only
31 asnell 03-MAY-1996 invalid_multiple_asg fix bug + performance fix
32 asnell 05-JUN-1996 modified trash logic to cope with permit change
33 bug 371483
34 cbarbier 27-JUN-1996 add check_extract procedure to check if all
35 payrolls extract have at least one assignment.
36 cbarbier 26-JUL-1996 Changed dates definition for Y2000.
37 aswong 08-Aug-1996 Uncomment the exit statement at the end.
38 miqbal 26-Sep-1996 Added NIP extraction
39 miqbal 07-Oct-1996 Fix for multiple assignment extraction
40 Fix for NI Y, extraction
41 Changed NI C Total balance extraction to
42 NI C Employers total.
43 RThirlby 17-Jan-1997 Added new NI categories F, G and S.
44 tinekuku 26-Feb-1997 Bug 463778 - Used cursors to do inner/outer
45 selects for setting NI Balance on primary
46 asgmts for multiple assignments
47 asnell 08-May-1997 Bug 492246 - Ensure tax reference transfers are
48 also payroll transfers
49 Defaulted Taxcode from element if no run exists
50 Speeded up latest PAYE action fetch
51 aparkes 11-Jul-1997 Added updates to ye_assignments for employee
52 Address,Start_of_emp.
53 Added TITLE to extract from per_people_f
54 Used cursor for SCON NIF, G and S balances.
55 amills 16-Jan-1998 Bug 572938. Added a new update statement
56 'Retrieve element entry update recurring'
57 for update of pay_gb_year_end_assignments
58 where an element entry exists that is the
59 result of an update recurring rule. The
60 logic is altered to check for this rule
61 first before locating run result value,
62 then defaulting to element (non- U.R.R.)
63 value as before.
64 aparkes 19-Jan-1998 Used GB Balance direct call package hr_dirbal
65 for balance fetching.
66 arundell 30-Mar-1998 Fixed bug 639910. Adjusted the way the last
67 assignment_action_id, previous_year_asg_action_id
68 and the last tax_run_result_id is derived, so
69 that payroll reversals are included.
70 hanand 16-Apr-1998 Fixed byg 656417. Included cases with
71 termination_type of 'L' for reporting NI_Y_LAST
72 asnell 23-Apr-1998 Fixed bug 660289. Only create SCON balance
73 entry if results were non zero
74 asnell 29-Apr-1998 Fixed bug 662438. If SCON entry value doesn't
75 exist on the date find the nearest match
76 hanand 04-Jun-1998 Fixed bug 678573. Changed the cursor
77 get_scon_bal to include 'Employer' balances.
78 Setting 'Total' to 'Employer' balance for
79 category 'S' and all other balances to 0.
80 amyers 110.11 06-NOV-1998 Fixed bug 715534. Added status checks to table
81 PAY_RUN_RESULTS in updates after balances have
82 been fetched.
83 amyers 110.12 23-FEB-1999 Fixed bug 818887. Aggregate rows by scon and
84 category; takes care of situation where more
85 then one can be returned.
86 scgrant 115.1 20-APR-1999 Multi-radix changes.
87 pdavies 115.2 11-OCT-1999 Replaced all occurrences of DBMS_Output.
88 Put_Line with hr_utility.trace.
89 */
90 --
91 ---------------------------GLOBALS ------------------------------------------
92 g_ni_id number(9);
93 g_category_input_id number(9);
94 g_scon_input_id number(9);
95 /* ---------------------------------------------------------------------------
96 --
97 --
98 -- PROCEDURES --
99 --
100 --
101 -------------------------- PLOG --------------------------------------------*/
102 procedure PLOG ( p_message IN varchar2 ) is
103
104 -- output a message to the process log file
105 -- currently a cover for dbms.output but may be a cover for a generic function
106 begin
107 hr_utility.trace(rpad(p_message,69)||' '|| TO_CHAR(SYSDATE,'HH24:MI:SS'));
108 end plog;
109 /* ----------------------------------------------------------------------------*/
110 /* ---------------------------- CHECK EXTRACT ---------------------------------*/
111 /* ----------------------------------------------------------------------------*/
112 /* This function check if all the payrolls extracted have at least one */
113 /* assignment, if they have not, it return false and the extract process abort */
114 /* ----------------------------------------------------------------------------*/
115 FUNCTION check_data(
116 p_business_group_id IN NUMBER,
117 p_year IN NUMBER,
118 p_permit IN VARCHAR2 DEFAULT NULL,
119 p_tax_district_ref IN VARCHAR2 DEFAULT NULL
120 )
121 RETURN NUMBER
122 IS
123 l_count NUMBER;
124 BEGIN
125 SELECT COUNT(*)
126 INTO l_count
127 FROM
128 pay_gb_year_end_payrolls pay,
129 pay_gb_year_end_assignments ass,
130 pay_gb_year_end_values val
131 WHERE
132 pay.payroll_id = ass.payroll_id
133 AND ass.assignment_id = val.assignment_id
134 AND pay.business_group_id = p_business_group_id
135 AND pay.tax_year = p_year
136 AND pay.permit_number = NVL(p_permit,pay.permit_number)
137 AND pay.tax_reference_number = NVL(SUBSTR(p_tax_district_ref,4,8), pay.tax_reference_number)
138 AND pay.tax_district_reference = NVL(FND_NUMBER.CANONICAL_TO_NUMBER(SUBSTR(p_tax_district_ref,1,3)), pay.tax_district_reference);
139 RETURN l_count;
140 END check_data;
141
142 /* ----------------------------------------------------------------------------*/
143 /* ---------------------------- GET_NEAREST_SCON ------------------------------*/
144 /* ----------------------------------------------------------------------------*/
145 /* This function searches for a SCON number to associate with the SCON balance */
146 /* Balance initialization creates run results prior to the NI row that records */
147 /* the SCON number. So find a row for the same category after the effective */
148 /* date of the owning payroll action. */
149 /* Priority is next latest SCON input with the same Category */
150 /* down to next latest SCON input regardless of Category */
151 /* ----------------------------------------------------------------------------*/
152 FUNCTION GET_NEAREST_SCON(
153 p_element_entry_id IN number ,
154 p_category IN varchar2 ,
155 p_effective_date IN date
156 ) return varchar2
157 IS
158 CURSOR get_scon is
159 -- best match is if the category on the entry matches the balance category
160 -- as a workarround users may have entered scon against a different
161 -- category. So if no category matches just get the earliest scon value on or
162 -- after the effective date
163 SELECT scon.screen_entry_value
164 FROM
165 pay_element_entry_values_f scon,
166 pay_element_entry_values_f cat
167 WHERE
168 scon.element_entry_id = p_element_entry_id
169 and cat.element_entry_id = p_element_entry_id
170 and cat.effective_start_date = scon.effective_start_date
171 and cat.effective_end_date = scon.effective_end_date
172 and scon.input_value_id +0 = g_scon_input_id
173 and cat.input_value_id +0 = g_category_input_id
174 and scon.screen_entry_value is not null
175 and scon.effective_end_date >= p_effective_date
176 order by decode(cat.screen_entry_value,p_category,0,1),
177 scon.effective_end_date ;
178 --
179
180 l_scon pay_gb_year_end_values.scon%TYPE;
181 BEGIN
182 BEGIN
183 -- if global ids arent set set them
184 if g_ni_id is null then
185 select element_type_id into g_ni_id from
186 pay_element_types_f where element_name = 'NI'
187 and p_effective_date between
188 effective_start_date and effective_end_date;
189 --
190 select input_value_id into g_category_input_id from
191 pay_input_values_f
192 where name = 'Category'
193 and element_type_id = g_ni_id
194 and p_effective_date between
195 effective_start_date and effective_end_date;
196 --
197 select input_value_id into g_scon_input_id from
198 pay_input_values_f
199 where name = 'SCON'
200 and element_type_id = g_ni_id
201 and p_effective_date between
202 effective_start_date and effective_end_date;
203 end if;
204 end;
205
206 BEGIN
207 open get_scon;
208 fetch get_scon into l_scon;
209 close get_scon;
210 exception when no_data_found then l_scon := null;
211 END;
212
213 RETURN l_scon;
214
215 END get_nearest_scon;
216
217 /* --------------------------- trash ----------------------------------- */
218 /*
219 NAME
220 trash
221 DESCRIPTION
222 clear down year end tables for selected permit
223 NOTES
224 */
225 procedure trash(p_permit in varchar2 ,
226 p_business_group_id in number,
227 p_tax_district_ref in varchar2,
228 p_year in number) is
229 -- housekeeping procedure called pre extract to delete previously extracted
230 -- data
231 --
232 -- delete all the rows in values for this permit , if no permit specified
233 -- delete all rows within the business group
234
235 l_tax_district_reference pay_gb_year_end_payrolls.tax_district_reference%TYPE;
236 l_tax_reference_number pay_gb_year_end_payrolls.tax_reference_number%TYPE;
237 l_start_year date;
238 l_end_year date;
239
240 begin
241 hr_utility.set_location('PAY_YEAR_END_EXTRACT.TRASH',2);
242 --
243 l_tax_district_reference := fnd_number.canonical_to_number(substr(p_tax_district_ref,1,3));
244 l_tax_reference_number := substr(ltrim(substr(p_tax_district_ref,
245 4,8),'/') ,1,7);
246 -- set up the statutory start and end year
247 l_start_year := to_date('06-04-'||(p_year - 1),'dd-mm-yyyy');
248 l_end_year := to_date('05-04-'||p_year,'dd-mm-yyyy');
249 --
250 -- delete all the rows in values for this permit , if no permit specified
251 -- delete all the rows for this tax_district_reference else if no
252 -- tax_district_reference specified delete all rows within the business group
253 delete from pay_gb_year_end_values v where exists (
254 select '1' from pay_gb_year_end_assignments ye_asg,
255 pay_payrolls_f p,
256 hr_soft_coding_keyflex flex,
257 hr_organization_information org
258 where p.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
259 and org.ORG_INFORMATION_CONTEXT = 'Tax Details References'
260 and org.org_information1 = flex.segment1
261 and p.business_group_id = p_business_group_id
262 and org.organization_id = p_business_group_id
263 and nvl(p_permit,substr(flex.segment10,1,12)) =
264 substr(flex.segment10,1,12)
265 and nvl(l_tax_district_reference,substr(flex.segment1,1,3)) =
266 substr(flex.segment1,1,3)
267 and nvl(l_tax_reference_number,
268 substr(ltrim(substr(org_information1,4,8),'/') ,1,7)) =
269 substr(ltrim(substr(org_information1,4,8),'/') ,1,7)
270 and l_end_year between
271 p.effective_start_date and p.effective_end_date
272 and ye_asg.payroll_id = p.payroll_id
273 and v.assignment_id = ye_asg.assignment_id
274 and v.effective_end_date = ye_asg.effective_end_date);
275 --
276 plog ( '_ value rows deleted '||to_char(SQL%ROWCOUNT));
277
278 hr_utility.set_location('PAY_YEAR_END_EXTRACT.TRASH',4);
279 -- delete all the rows in assignments for this permit , if no permit specified
280 -- delete all the rows for this tax_district_reference else if no
281 -- tax_district_reference specified delete all rows within the business group
282 delete from pay_gb_year_end_assignments ye_asg where exists (
283 select '1' from pay_payrolls_f p,
284 hr_soft_coding_keyflex flex, hr_organization_information org
285 where p.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
286 and org.ORG_INFORMATION_CONTEXT = 'Tax Details References'
287 and org.org_information1 = flex.segment1
288 and p.business_group_id = p_business_group_id
289 and org.organization_id = p_business_group_id
290 and nvl(p_permit,substr(flex.segment10,1,12)) =
291 substr(flex.segment10,1,12)
292 and nvl(l_tax_district_reference,substr(flex.segment1,1,3)) =
293 substr(flex.segment1,1,3)
294 and nvl(l_tax_reference_number,
295 substr(ltrim(substr(org_information1,4,8),'/') ,1,7)) =
296 substr(ltrim(substr(org_information1,4,8),'/') ,1,7)
297 and l_end_year between
298 p.effective_start_date and p.effective_end_date
299 and ye_asg.payroll_id = p.payroll_id);
300 --
301 plog ( '_ assignment rows deleted '||to_char(SQL%ROWCOUNT));
302
303 hr_utility.set_location('PAY_YEAR_END_EXTRACT.TRASH',6);
304 -- delete all the rows in payrolls for this permit , if no permit specified
305 -- delete all the rows for this tax_district_reference else if no
306 -- tax_district_reference specified delete all rows within the business group
307 delete from pay_gb_year_end_payrolls ye_roll
308 where exists ( select '1' from pay_payrolls_f p,
309 hr_soft_coding_keyflex flex, hr_organization_information org
310 where p.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
311 and org.ORG_INFORMATION_CONTEXT = 'Tax Details References'
312 and org.org_information1 = flex.segment1
313 and p.business_group_id = p_business_group_id
314 and org.organization_id = p_business_group_id
315 and nvl(p_permit,substr(flex.segment10,1,12)) =
316 substr(flex.segment10,1,12)
317 and nvl(l_tax_district_reference,substr(flex.segment1,1,3)) =
318 substr(flex.segment1,1,3)
319 and nvl(l_tax_reference_number,
320 substr(ltrim(substr(org_information1,4,8),'/') ,1,7)) =
321 substr(ltrim(substr(org_information1,4,8),'/') ,1,7)
322 and l_end_year between
323 p.effective_start_date and p.effective_end_date
324 and ye_roll.payroll_id = p.payroll_id);
325
326 plog ( '_ payroll rows deleted '||to_char(SQL%ROWCOUNT));
327
328 commit;
329
330 end trash;
331 --
332 --
333 --------------------------- extract -----------------------------------
334 /*
335 NAME
336 extract
337 DESCRIPTION
338 populate the eoy tables
339 NOTES
340 */
341 procedure extract(p_permit in varchar2 ,
342 p_business_group_id in number,
343 p_tax_district_ref in varchar2,
344 p_year in number,
345 p_request_id in number,
346 p_niy in varchar2,
347 p_retcode out number ,
348 p_errbuf out varchar2
349 ) is
350 --
351 --
352 -- ensure the whole of the year_end_all table is populated even if
353 -- selecting on 1 permit, as there may be other payrolls on the selected
354 -- assignments
358 CURSOR get_defined_balance_id
355
356 -- declare local cursors
357 -- get the defined balance id for specified balance and dimension
359 (p_balance_name varchar2, p_dimension_name varchar2) is
360 --
361 select defined_balance_id
362 from pay_defined_balances db,
363 pay_balance_types b,
364 pay_balance_dimensions d
365 where b.balance_name = p_balance_name
366 and d.dimension_name = p_dimension_name
367 and db.balance_type_id = b.balance_type_id
368 and db.balance_dimension_id = d.balance_dimension_id;
369
370 CURSOR get_people is
371 -- fetch all the person rows
372 select assignment_id, effective_end_date, sex, payroll_id,
373 previous_year_asg_action_id, last_asg_action_id, termination_type
374 from pay_gb_year_end_assignments
375 where request_id = p_request_id;
376 --
377
378 CURSOR get_invalid_multiple_asg is
379 -- fetch any ye_asg rows that have people with assignments in different
380 -- permits but within the same tax reference
381
382 select ye_asg.rowid ye_asg_rowid
383 from pay_gb_year_end_assignments ye_asg
384 , pay_gb_year_end_payrolls ye_roll
385 where exists ( select person_id
386 from per_assignments_f asg,
387 pay_gb_year_end_payrolls yep2
388 where yep2.payroll_id = ye_asg.payroll_id
389 and yep2.TAX_REFERENCE_NUMBER = ye_roll.TAX_REFERENCE_NUMBER
390 and yep2.PERMIT_NUMBER <> ye_roll.PERMIT_NUMBER
391 and asg.effective_start_date < ye_roll.end_year
392 and asg.effective_end_date >= ye_roll.START_YEAR
393 and ye_asg.person_id = asg.person_id)
394 and MULTIPLE_ASG_FLAG is not null
395 and ye_asg.payroll_id = ye_roll.payroll_id
396 and request_id = p_request_id;
397 --
398 CURSOR get_multi_asg_people is
399 -- fetch all the person rows
400 select assignment_id, effective_end_date, sex, payroll_id,
401 previous_year_asg_action_id, last_asg_action_id, termination_type
402 from pay_gb_year_end_assignments
403 where request_id = p_request_id
404 and eoy_primary_flag = 'Y';
405 --
406 --
407 CURSOR get_multi_asg_prim_details(l_asg_id number) is
408 -- fetch any multiple assignment details for to be given to the
409 -- primary assignment
410 select yea_prim.assignment_id s_asg_id,
411 yea_prim.effective_end_date s_end_date,
412 yev.ni_category_code s_ni_cat_code,
413 'M',
414 sum(yev.EARNINGS) s_earnings,
415 sum(yev.TOTAL_CONTRIBUTIONS) s_tot_con,
416 sum(yev.EMPLOYEES_CONTRIBUTIONS) s_ees_con,
417 sum(yev.EARNINGS_CONTRACTED_OUT) s_earnings_co,
418 sum(yev.CONTRIBUTIONS_CONTRACTED_OUT) s_con_co
419 from pay_gb_year_end_assignments yea_prim,
420 pay_gb_year_end_assignments ye_asg,
421 pay_gb_year_end_values yev,
422 pay_gb_year_end_payrolls yep_prim,
423 pay_gb_year_end_payrolls ye_roll
424 where yea_prim.eoy_primary_flag = 'Y'
425 and ye_asg.person_id = yea_prim.person_id
426 and yea_prim.payroll_id = yep_prim.payroll_id
427 and ye_asg.payroll_id = ye_roll.payroll_id
428 and ye_asg.assignment_id = yev.assignment_id
429 and ye_asg.effective_end_date = yev.effective_end_date
430 and yep_prim.tax_reference_number = ye_roll.tax_reference_number
431 and (yev.total_contributions <> 0 or yev.ni_category_code = 'X')
432 and yea_prim.assignment_id = l_asg_id
433 and yea_prim.request_id = p_request_id
434 group by yea_prim.assignment_id,
435 yea_prim.effective_end_date,
436 yev.ni_category_code;
437 --
438 --
439 CURSOR get_scon_bal(cp_l_asg_id number, cp_inp_val number, cp_element_type number) is
440 -- fetch the scon balances for NI F, NI G and/or NI S
441 select substr(bal.balance_name,4,1) cat_code,
442 substr(HR_GENERAL.DECODE_LOOKUP('GB_SCON',decode(substr(bal.balance_name,4,1),
443 'F',nvl(max(EV_SCON.screen_entry_value),
444 pay_year_end_extract.get_nearest_scon(
445 max(EV_SCON.element_entry_id),'F',max(PACT.effective_date))),
446 'G',nvl(max(EV_SCON.screen_entry_value),
447 pay_year_end_extract.get_nearest_scon(
448 max(EV_SCON.element_entry_id),'G',max(PACT.effective_date))),
449 'S',nvl(max(EV_SCON.screen_entry_value),
450 pay_year_end_extract.get_nearest_scon(
451 max(EV_SCON.element_entry_id),'S',max(PACT.effective_date))),
452 null)),1,9) scon,
453 100*nvl(sum(decode(substr(bal.balance_name,6),'Able',
454 fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0) able,
455 100*nvl(sum(decode(substr(bal.balance_name,6),'Total',
456 fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0) Total,
457 100*nvl(sum(decode(substr(bal.balance_name,6),'Employee',
458 fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0) Employee,
459 --
460 -- Bug Fix 678573 Start
461 --
462 100*nvl(sum(decode(substr(bal.balance_name,6),'Employer',
463 fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0) Employer,
464 --
465 -- Bug Fix 678573 End
466 --
467 100*nvl(sum(decode(substr(bal.balance_name,6),'CO Able',
468 fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0) CO_able,
469 100*nvl(sum(decode(substr(bal.balance_name,6),'CO',
473 ,pay_run_result_values TARGET
470 fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0) CO
471 from pay_balance_feeds_f FEED
472 ,pay_balance_types BAL
474 ,pay_run_results RR
475 ,pay_element_entry_values_f EV_SCON
476 ,pay_element_entries_f E_NI
477 ,pay_element_links_f EL_NI
478 ,pay_payroll_actions PACT
479 ,pay_assignment_actions ASSACT
480 ,pay_payroll_actions BACT
481 ,per_time_periods BPTP
482 ,per_time_periods PPTP
483 ,pay_assignment_actions BAL_ASSACT
484 where BAL_ASSACT.assignment_action_id = cp_l_asg_id
485 and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
486 and FEED.balance_type_id = BAL.balance_type_id
487 and BAL.balance_name like 'NI%'
488 and substr(BAL.balance_name,4,1) in ('F','G','S')
489 and FEED.input_value_id = TARGET.input_value_id
490 and TARGET.run_result_id = RR.run_result_id
491 and nvl(TARGET.result_value,'0') <> '0'
492 and RR.assignment_action_id = ASSACT.assignment_action_id
493 and E_NI.assignment_id = BAL_ASSACT.assignment_id
494 and EV_SCON.input_value_id +
495 decode(EV_SCON.element_entry_id,null,0,0) = cp_inp_val
496 and EV_SCON.element_entry_id = E_NI.element_entry_id
497 and EL_NI.element_link_id = E_NI.element_link_id
498 and EL_NI.element_type_id = cp_element_type
499 and PACT.effective_date between
500 E_NI.effective_start_date and E_NI.effective_end_date
501 and PACT.effective_date between
502 EL_NI.effective_start_date and EL_NI.effective_end_date
503 and PACT.effective_date between
504 EV_SCON.effective_start_date and EV_SCON.effective_end_date
505 and ASSACT.payroll_action_id = PACT.payroll_action_id
506 and PACT.effective_date between
507 FEED.effective_start_date and FEED.effective_end_date
508 and RR.status in ('P','PA')
509 and BPTP.time_period_id = BACT.time_period_id
510 and PPTP.time_period_id = PACT.time_period_id
511 and PPTP.regular_payment_date >= /* fin year start */
512 ( to_date('06-04-' || to_char( to_number(
513 to_char( BPTP.regular_payment_date,'YYYY'))
514 + decode(sign( BPTP.regular_payment_date - to_date('06-04-'
515 || to_char(BPTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
516 -1,-1,0)),'DD-MM-YYYY'))
517 and PACT.effective_date >=
518 /* find the latest td payroll transfer date - compare each of the */
519 /* assignment rows with its predecessor looking for the payroll */
520 /* that had a different tax district at that date */
521 ( select nvl(max(ASS.effective_start_date),
522 fnd_date.canonical_to_date('01-01-0001'))
523 from per_assignments_f ASS
524 ,pay_payrolls_f NROLL
525 ,hr_soft_coding_keyflex FLEX
526 ,per_assignments_f PASS /* previous assignment */
527 ,pay_payrolls_f PROLL
528 ,hr_soft_coding_keyflex PFLEX
529 where ASS.assignment_id = BAL_ASSACT.assignment_id
530 and NROLL.payroll_id = ASS.payroll_id
531 and ASS.effective_start_date between
532 NROLL.effective_start_date and NROLL.effective_end_date
533 and NROLL.soft_coding_keyflex_id = FLEX.soft_coding_keyflex_id
534 and ASS.assignment_id = PASS.assignment_id
535 and PASS.effective_end_date = (ASS.effective_start_date - 1)
536 and ASS.effective_start_date <= BACT.effective_date
537 and PROLL.payroll_id = PASS.payroll_id
538 and ASS.effective_start_date between
539 PROLL.effective_start_date and PROLL.effective_end_date
540 and PROLL.soft_coding_keyflex_id = PFLEX.soft_coding_keyflex_id
541 and ASS.payroll_id <> PASS.payroll_id
542 and FLEX.segment1 <> PFLEX.segment1
543 )
544 and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
545 and ASSACT.assignment_id = BAL_ASSACT.assignment_id
546 group by EV_SCON.screen_entry_value, substr(bal.balance_name,4,1)
547 order by EV_SCON.screen_entry_value, substr(bal.balance_name,4,1);
548 --
549 --
550 l_tax_district_reference pay_gb_year_end_payrolls.tax_district_reference%TYPE;
551 l_tax_reference_number pay_gb_year_end_payrolls.tax_reference_number%TYPE;
552 l_start_year date;
553 l_end_year date;
554 l_niy number(9);
555 l_niy_id number(9);
556 l_niy_ly_id number(9);
557 l_ni_tot number(9);
558 l_ni_ees number(9);
559 l_ni_able number(9);
560 l_ni_co_able number(9);
561 l_ni_co number(9);
562 l_nia_able_id number(9);
563 l_nia_id number(9);
564 l_nia_tot_id number(9);
565 l_nib_able_id number(9);
566 l_nib_id number(9);
567 l_nib_tot_id number(9);
568 l_nic_tot_id number(9);
569 l_nid_able_id number(9);
570 l_nid_id number(9);
571 l_nid_tot_id number(9);
572 l_nid_co_able_id number(9);
573 l_nid_co_id number(9);
574 l_nie_able_id number(9);
575 l_nie_id number(9);
576 l_nie_tot_id number(9);
577 l_nie_co_able_id number(9);
578 l_nie_co_id number(9);
579 l_nif_tot_id number(9);
580 l_nig_tot_id number(9);
581 l_nis_tot_id number(9);
582 l_nip_id number(9);
583 l_nip number(9);
584 l_ssp number(9);
585 l_smp number(9);
586 l_gross number(9);
587 l_paye number(9);
588 l_super number(9);
589 l_widow number(9);
590 l_taxable number(9);
591 l_ssp_id number(9);
592 l_smp_id number(9);
593 l_gross_id number(9);
594 l_paye_id number(9);
595 l_super_id number(9);
596 l_widow_id number(9);
597 l_taxable_id number(9);
598 l_paye_details_id number(9);
599 l_max_run_result_id number(9);
603 l_error_text varchar2(132);
600 l_ni_id number(9);
601 l_category_input_id number(9);
602 l_scon_input_id number(9);
604 l_count number(9);
605 l_count_values number(9);
606 l_earnings number(9);
607 l_asg_id number(9);
608 --
609 tax_dist_ref_error exception; -- raised when l_tax_district_reference
610 -- has incorrect format
611 --
612
613 begin -- ( extract
614
615 plog('Year End Extract - Permit(' ||p_permit||')');
616 plog('_ BG(' ||to_char(p_business_group_id)||')');
617 plog('_ Tax_Ref('||p_tax_district_ref||')');
618 plog('_ NI_Y('||p_niy||')');
619 plog('_ Year(' ||to_char(p_year)||')');
620 plog('_ Request('||to_char(p_request_id)||')');
621 --
622 -- Check tax_district_reference input is valid (ie. numeric) else raise error
623 begin
624 l_tax_district_reference := fnd_number.canonical_to_number(substr(p_tax_district_ref,1,3));
625 if l_tax_district_reference < 0 then raise value_error; end if;
626 exception
627 when value_error then
628 raise tax_dist_ref_error;
629 end;
630 --
631 hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',1);
632 -- delete old data
633 plog ( '_ Delete previous extracted rows for this selection ');
634 pay_year_end_extract.trash(p_permit,p_business_group_id,
635 p_tax_district_ref,p_year);
636
637 begin -- ( setup ids
638 --
639 hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',2);
640
641 p_retcode := 1; -- default extract to success
642 -- l_tax_district_reference := fnd_number.canonical_to_number(substr(p_tax_district_ref,1,3));
643 l_tax_reference_number := substr(ltrim(substr(p_tax_district_ref,
644 4,8),'/') ,1,7);
645
646 -- set up the statutory start and end year
647 l_start_year := to_date('06-04-'||(p_year - 1),'dd-mm-yyyy');
648 l_end_year := to_date('05-04-'||p_year,'dd-mm-yyyy');
649 --
650 -- find the defined balance id's for balance / dimension combos
651 open get_defined_balance_id('NI Y','_ASG_STAT_YTD');
652 fetch get_defined_balance_id into l_niy_id;
653 close get_defined_balance_id;
654 --
655 open get_defined_balance_id('NI Y Last Year','_ASG_STAT_YTD');
656 fetch get_defined_balance_id into l_niy_ly_id;
657 close get_defined_balance_id;
658 --
659 open get_defined_balance_id('NI A Able','_ASG_TD_YTD');
660 fetch get_defined_balance_id into l_nia_able_id;
661 close get_defined_balance_id;
662 --
663 open get_defined_balance_id('NI A Employee','_ASG_TD_YTD');
664 fetch get_defined_balance_id into l_nia_id;
665 close get_defined_balance_id;
666 --
667 open get_defined_balance_id('NI A Total','_ASG_TD_YTD');
668 fetch get_defined_balance_id into l_nia_tot_id;
669 close get_defined_balance_id;
670 --
671 open get_defined_balance_id('NI B Able','_ASG_TD_YTD');
672 fetch get_defined_balance_id into l_nib_able_id;
673 close get_defined_balance_id;
674 --
675 open get_defined_balance_id('NI B Employee','_ASG_TD_YTD');
676 fetch get_defined_balance_id into l_nib_id;
677 close get_defined_balance_id;
678 --
679 open get_defined_balance_id('NI B Total','_ASG_TD_YTD');
680 fetch get_defined_balance_id into l_nib_tot_id;
681 close get_defined_balance_id;
682 --
683 open get_defined_balance_id('NI C Employer','_ASG_TD_YTD');
684 fetch get_defined_balance_id into l_nic_tot_id;
685 close get_defined_balance_id;
686 --
687 open get_defined_balance_id('NI D Able','_ASG_TD_YTD');
688 fetch get_defined_balance_id into l_nid_able_id;
689 close get_defined_balance_id;
690 --
691 open get_defined_balance_id('NI D Employee','_ASG_TD_YTD');
692 fetch get_defined_balance_id into l_nid_id;
693 close get_defined_balance_id;
694 --
695 open get_defined_balance_id('NI D Total','_ASG_TD_YTD');
696 fetch get_defined_balance_id into l_nid_tot_id;
697 close get_defined_balance_id;
698 --
699 open get_defined_balance_id('NI D CO Able','_ASG_TD_YTD');
700 fetch get_defined_balance_id into l_nid_co_able_id;
701 close get_defined_balance_id;
702 --
703 open get_defined_balance_id('NI D CO','_ASG_TD_YTD');
704 fetch get_defined_balance_id into l_nid_co_id;
705 close get_defined_balance_id;
706 --
707 open get_defined_balance_id('NI E Able','_ASG_TD_YTD');
708 fetch get_defined_balance_id into l_nie_able_id;
709 close get_defined_balance_id;
710 --
711 open get_defined_balance_id('NI E Employee','_ASG_TD_YTD');
712 fetch get_defined_balance_id into l_nie_id;
713 close get_defined_balance_id;
714 --
715 open get_defined_balance_id('NI E Total','_ASG_TD_YTD');
716 fetch get_defined_balance_id into l_nie_tot_id;
717 close get_defined_balance_id;
718 --
719 open get_defined_balance_id('NI E CO Able','_ASG_TD_YTD');
720 fetch get_defined_balance_id into l_nie_co_able_id;
721 close get_defined_balance_id;
722 --
723 open get_defined_balance_id('NI E CO','_ASG_TD_YTD');
724 fetch get_defined_balance_id into l_nie_co_id;
725 close get_defined_balance_id;
726 --
727 open get_defined_balance_id('NI F Total','_ASG_TD_YTD');
728 fetch get_defined_balance_id into l_nif_tot_id;
729 close get_defined_balance_id;
730 --
731 open get_defined_balance_id('NI G Total','_ASG_TD_YTD');
735 open get_defined_balance_id('NI S Employer','_ASG_TD_YTD');
732 fetch get_defined_balance_id into l_nig_tot_id;
733 close get_defined_balance_id;
734 --
736 fetch get_defined_balance_id into l_nis_tot_id;
737 close get_defined_balance_id;
738 --
739 open get_defined_balance_id('NIC Holiday','_ASG_TD_YTD');
740 fetch get_defined_balance_id into l_nip_id;
741 close get_defined_balance_id;
742 --
743 open get_defined_balance_id('SSP Total','_ASG_TD_YTD');
744 fetch get_defined_balance_id into l_ssp_id;
745 close get_defined_balance_id;
746 --
747 open get_defined_balance_id('SMP Total','_ASG_TD_YTD');
748 fetch get_defined_balance_id into l_smp_id;
749 close get_defined_balance_id;
750 --
751 open get_defined_balance_id('Gross Pay','_ASG_TD_YTD');
752 fetch get_defined_balance_id into l_gross_id;
753 close get_defined_balance_id;
754 --
755 open get_defined_balance_id('PAYE','_ASG_TD_YTD');
756 fetch get_defined_balance_id into l_paye_id;
757 close get_defined_balance_id;
758 --
759 open get_defined_balance_id('Superannuation Total','_ASG_TD_YTD');
760 fetch get_defined_balance_id into l_super_id;
761 close get_defined_balance_id;
762 --
763 open get_defined_balance_id('Widows and Orphans','_ASG_TD_YTD');
764 fetch get_defined_balance_id into l_widow_id;
765 close get_defined_balance_id;
766 --
767 open get_defined_balance_id('Taxable Pay','_ASG_TD_YTD');
768 fetch get_defined_balance_id into l_taxable_id;
769 close get_defined_balance_id;
770 --
771 select element_type_id into l_paye_details_id from
772 pay_element_types_f where element_name = 'PAYE Details'
773 and l_end_year between
774 effective_start_date and effective_end_date;
775 --
776 select 10 * pay_run_results_s.nextval
777 into l_max_run_result_id
778 from dual;
779 --
780 select element_type_id into l_ni_id from
781 pay_element_types_f where element_name = 'NI'
782 and l_end_year between
783 effective_start_date and effective_end_date;
784 --
785 select input_value_id into l_category_input_id from
786 pay_input_values_f
787 where name = 'Category'
788 and element_type_id = l_ni_id
789 and l_end_year between
790 effective_start_date and effective_end_date;
791 --
792 select input_value_id into l_scon_input_id from
793 pay_input_values_f
794 where name = 'SCON'
795 and element_type_id = l_ni_id
796 and l_end_year between
797 effective_start_date and effective_end_date;
798 --
799 end; -- ) setup ids
800
801 /* populate pay_gb_year_end_payrolls table for each payroll */
802 /* ISSUE - check ACCOUNTS_OFFICE_REF looks like its disabled */
803 -- pick up SCL segments and Tax Reference Info
804 -- sometime the tax reference is delimited by a '/' remove this */
805 begin -- ( insert pay_gb_year_end_payrolls
806 hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',4);
807 insert into pay_gb_year_end_payrolls
808 ( BUSINESS_GROUP_ID,
809 PAYROLL_ID,
810 PERMIT_NUMBER,
811 PAYROLL_NAME,
812 TAX_DISTRICT_REFERENCE,
813 TAX_REFERENCE_NUMBER ,
814 TAX_DISTRICT_NAME ,
815 TAX_YEAR,
816 EMPLOYERS_NAME,
817 EMPLOYERS_ADDRESS_LINE,
818 ECON ,
819 SMP_RECOVERED,
820 SMP_COMPENSATION,
821 SSP_RECOVERED )
822 select p.business_group_id business_group_id,
823 p.payroll_id PAYROLL_ID,
824 substr(flex.segment10,1,12) PERMIT_NUMBER,
825 p.payroll_name PAYROLL_NAME,
826 substr(flex.segment1,1,3) TAX_DISTRICT_REFERENCE,
827 substr(ltrim(substr(org_information1,4,8),'/') ,1,7) TAX_REFERENCE,
828 substr(org.org_information2 ,1,40) TAX_DISTRICT_NAME,
829 p_year TAX_YEAR,
830 substr(org.org_information3,1,36) EMPLOYERS_NAME,
831 substr(org.org_information4,1,60) EMPLOYERS_ADDRESS_LINE,
832 substr(org.org_information7,1,9) ECON,
833 flex.segment11 * 100 SMP_RECOVERED,
834 flex.segment12 * 100 SMP_COMPENSATION,
835 flex.segment13 * 100 SSP_RECOVERED
836 from pay_payrolls_f p,
837 hr_soft_coding_keyflex flex, hr_organization_information org
838 where p.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
839 and org.ORG_INFORMATION_CONTEXT = 'Tax Details References'
840 and org.org_information1 = flex.segment1
841 and p.business_group_id = p_business_group_id
842 and org.organization_id = p_business_group_id
843 and l_end_year between
844 p.effective_start_date and p.effective_end_date
845 and not exists ( select null from pay_gb_year_end_payrolls ye_roll
846 where ye_roll.payroll_id = p.payroll_id ) ;
847 --
848 --
849 hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',6);
850 -- set the start and end dates for the payroll year
851 update pay_gb_year_end_payrolls ye_roll
852 set ( START_YEAR, END_YEAR ,PERIOD_TYPE, MAX_PERIOD_NUMBER) =
853 ( select min(start_date), max(end_date), max(PERIOD_TYPE), max(PERIOD_NUM)
854 from per_time_periods ptp
855 where PTP.payroll_id = ye_roll.payroll_id
856 and PTP.regular_payment_date between
857 l_start_year and l_end_year);
858 --
859 hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',8);
863 ( select min(start_date), max(end_date) from per_time_periods ptp
860 -- set the start and end dates for the previous payroll year
861 update pay_gb_year_end_payrolls ye_roll
862 set ( START_PREVIOUS_YEAR, END_PREVIOUS_YEAR ) =
864 where PTP.payroll_id = ye_roll.payroll_id
865 and PTP.regular_payment_date between
866 add_months(l_start_year,-12)
867 and add_months(l_end_year,-12));
868 --
869 commit;
870 plog ( '_ pay_gb_year_end_payrolls data populated '||to_char(SQL%ROWCOUNT));
871
872
873 end; -- ) insert pay_gb_year_end_payrolls
874
875 begin -- ( insert assignments
876 -- select all the assignments for a particular permit
877 -- note we only want the last date effective row - the permit on the
878 -- payroll for this dictates where it is reported even if the assignment
879 -- has been on more than one payroll in the year. The exception to this
880 -- is where tax district/reference transfers have occurred
881 -- find the latest assignment row this payroll year
882 -- add any assignment rows that are for tax reference changes
883 -- pick up latest effective end date and latest payroll
884 -- don't pick up null permits and if ni y is not reportable only pick up
885 -- current year assignments
886 --
887 --
888 --
889 hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',12);
890 hr_utility.trace( 'extract the latest assignments ' );
891 insert into pay_gb_year_end_assignments (
892 assignment_id,
893 payroll_id,
894 effective_end_date,
895 request_id ,
896 extract_date,
897 assignment_number,
898 person_id,
899 organization_id,
900 location_id,
901 people_group_id)
902 select ass.assignment_id,
903 ass.payroll_id,
904 ass.effective_end_date,
905 p_request_id,
906 sysdate ,
907 ass.assignment_number,
908 ass.person_id,
909 ass.organization_id,
910 ass.location_id,
911 ass.people_group_id
912 from per_assignments_f ASS,
913 pay_gb_year_end_payrolls ye_roll
914 where ASS.payroll_id = ye_roll.payroll_id
915 /* 2 years scan to pick up NI Y last year - note we don't need to
916 worry about transfers for NI Y as it uses the STAT_YTD dimension */
917 and ASS.effective_end_date >=
918 decode(p_niy,'N', ye_roll.START_YEAR,
919 nvl(ye_roll.START_PREVIOUS_YEAR,ye_roll.START_YEAR))
920 and ASS.effective_start_date <= ye_roll.END_YEAR
921 and not exists ( select 1 from per_assignments_f ass2,
922 pay_gb_year_end_payrolls ye_roll2
923 where ass.assignment_id = ass2.assignment_id
924 and ass2.payroll_id = ye_roll2.payroll_id
925 and ass2.effective_end_date > ass.effective_end_date
926 and ass2.effective_end_date >=
927 decode(p_niy,'N', ye_roll2.START_YEAR,
928 nvl(ye_roll2.START_PREVIOUS_YEAR,ye_roll2.START_YEAR))
929 and ass2.effective_start_date <= ye_roll2.END_YEAR)
930 and exists
931 ( select yep2.payroll_id from pay_gb_year_end_payrolls yep2
932 where ass.payroll_id = yep2.payroll_id
933 and yep2.permit_number = nvl(p_permit,nvl(yep2.permit_number,'x'))
934 and nvl(l_tax_district_reference,yep2.tax_district_reference) =
935 yep2.tax_district_reference
936 and nvl(l_tax_reference_number,yep2.tax_reference_number) =
937 yep2.tax_reference_number
938 and yep2.business_group_id = p_business_group_id);
939 plog ( '_ assignments extracted '||to_char(SQL%ROWCOUNT));
940 --
941 hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',13);
942 hr_utility.trace( 'extract the tax reference transfer asgs' );
943 -- revised the select to only extract payroll transfers and
944 -- to check the tax reference is different date effective the start date of new
945 -- asg assumption here is that the old payroll still exists the day after transfer
946 insert into pay_gb_year_end_assignments (
947 assignment_id,
948 payroll_id,
949 effective_end_date,
950 request_id ,
951 extract_date,
952 termination_date,
953 termination_type,
954 assignment_number,
955 person_id,
956 organization_id,
957 location_id,
958 people_group_id)
959 select pass.assignment_id,
960 pass.payroll_id,
961 pass.effective_end_date,
962 p_request_id,
963 sysdate,
964 pass.effective_end_date termination_date,
965 'R' termination_type,
966 ass.assignment_number,
967 ass.person_id,
968 ass.organization_id,
969 ass.location_id,
970 ass.people_group_id
971 from per_assignments_f ASS
972 ,pay_payrolls_f NROLL
973 ,hr_soft_coding_keyflex FLEX
974 ,per_assignments_f PASS
975 ,pay_payrolls_f PROLL
976 ,hr_soft_coding_keyflex PFLEX
977 ,pay_gb_year_end_payrolls ye_roll
978 where NROLL.payroll_id = ASS.payroll_id
979 and ye_roll.payroll_id = PASS.payroll_id
980 and ASS.effective_start_date between
981 NROLL.effective_start_date and NROLL.effective_end_date
982 and NROLL.soft_coding_keyflex_id = FLEX.soft_coding_keyflex_id
986 and PASS.effective_start_date <= ye_roll.END_YEAR
983 and ASS.assignment_id = PASS.assignment_id
984 and PASS.effective_end_date = (ASS.effective_start_date - 1)
985 and PASS.effective_end_date >= ye_roll.START_YEAR
987 and PROLL.payroll_id = PASS.payroll_id
988 and ASS.effective_start_date between
989 PROLL.effective_start_date and PROLL.effective_end_date
990 and PROLL.soft_coding_keyflex_id = PFLEX.soft_coding_keyflex_id
991 and ASS.payroll_id <> PASS.payroll_id
992 and FLEX.segment1 <> PFLEX.segment1
993 and not exists ( select '1' from pay_gb_year_end_assignments ye_asg
994 where ye_asg.assignment_id = pass.assignment_id
995 and ye_asg.effective_end_date = pass.effective_end_date )
996 and exists ( select null from pay_gb_year_end_payrolls ye_roll
997 where ye_roll.payroll_id = pass.payroll_id
998 and ye_roll.permit_number = nvl(p_permit,ye_roll.permit_number)
999 and nvl(l_tax_district_reference,ye_roll.tax_district_reference) =
1000 ye_roll.tax_district_reference
1001 and nvl(l_tax_reference_number,ye_roll.tax_reference_number) =
1002 ye_roll.tax_reference_number
1003 and ye_roll.business_group_id = p_business_group_id);
1004 plog ( '_ tax ref transfer assignments extracted '||to_char(SQL%ROWCOUNT));
1005 --
1006 /* we need a condition here - if the extract is for all permits we */
1007 /* do a quick set effective start date by looking at the prior row on */
1008 /* the ye_asg table. If we are extracting for a particular permit we can't */
1009 /* rely on the person records being on the table so set the start date */
1010 /* from the base table */
1011 hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',14);
1012 hr_utility.trace( 'set the effective_start_date for transfers' );
1013 if p_permit is null and p_tax_district_ref is null then -- [ for null permit
1014 update pay_gb_year_end_assignments ye_asg set ( effective_start_date ) =
1015 (select max(effective_end_date) + 1 from pay_gb_year_end_assignments yea2
1016 where yea2.assignment_id = ye_asg.assignment_id
1017 and yea2.effective_end_date < ye_asg.effective_end_date )
1018 where ye_asg.request_id = p_request_id;
1019
1020 plog ( '_ transfers effective_start_date quick set '||to_char(SQL%ROWCOUNT));
1021 --
1022 else
1023 /* slow effective start date set */
1024 /* big overhead here is testing the value of tax reference date effective
1025 as of the payroll transfer - could we instead assume that tax reference
1026 on the payroll is not updated mid year? If so the following is simplified */
1027 hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',16);
1028 update pay_gb_year_end_assignments ye_asg set ( effective_start_date ) =
1029 ( select max(ASS.effective_start_date)
1030 from per_assignments_f ASS
1031 ,pay_payrolls_f NROLL
1032 ,hr_soft_coding_keyflex FLEX
1033 ,per_assignments_f PASS
1034 ,pay_payrolls_f PROLL
1035 ,hr_soft_coding_keyflex PFLEX
1036 ,pay_gb_year_end_payrolls ye_roll
1037 where ass.assignment_id = ye_asg.assignment_id
1038 and ass.effective_start_date < ye_asg.effective_end_date
1039 and NROLL.payroll_id = ASS.payroll_id
1040 and ye_roll.payroll_id = PASS.payroll_id
1041 and ASS.effective_start_date between
1042 NROLL.effective_start_date and NROLL.effective_end_date
1043 and NROLL.soft_coding_keyflex_id = FLEX.soft_coding_keyflex_id
1044 and ASS.assignment_id = PASS.assignment_id
1045 and PASS.effective_end_date = (ASS.effective_start_date - 1)
1046 and PASS.effective_end_date >= ye_roll.START_YEAR
1047 and PASS.effective_start_date <= ye_roll.END_YEAR
1048 and PROLL.payroll_id = PASS.payroll_id
1049 and PASS.payroll_id <> ASS.payroll_id
1050 and ASS.effective_start_date between
1051 PROLL.effective_start_date and PROLL.effective_end_date
1052 and PROLL.soft_coding_keyflex_id = PFLEX.soft_coding_keyflex_id
1053 and FLEX.segment1 <> PFLEX.segment1 )
1054 where ye_asg.request_id = p_request_id
1055 and ye_asg.effective_start_date is null;
1056 --
1057 plog ( '_ transfers effective_start_date set '||to_char(SQL%ROWCOUNT));
1058 end if; -- ] null permit
1059 --
1060 hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',18);
1061 hr_utility.trace( 'find the last action from the current year' );
1062 UPDATE PAY_GB_YEAR_END_ASSIGNMENTS ye_asg
1063 SET (LAST_ASG_ACTION_ID, LAST_EFFECTIVE_DATE) = (
1064 select assact.assignment_action_id,
1065 pact.effective_date
1066 from pay_payroll_actions pact,
1067 pay_assignment_actions assact
1068 where assact.payroll_action_id = pact.payroll_action_id
1069 and ye_asg.assignment_id = assact.assignment_id
1070 and ye_asg.request_id = p_request_id
1071 and assact.action_sequence =
1072 (
1073 select max(assact2.action_sequence)
1074 from pay_assignment_actions assact2,
1075 pay_payroll_actions pact2,
1076 pay_gb_year_end_payrolls ye_roll
1077 where assact2.assignment_id = ye_asg.assignment_id
1078 and assact2.payroll_action_id = pact2.payroll_action_id
1079 and pact2.payroll_id = ye_roll.payroll_id
1080 and pact2.action_type in ( 'Q','R','B','I')
1081 and assact2.action_status = 'C'
1082 and pact2.effective_date <= ye_asg.effective_end_date
1083 and pact2.effective_date between
1087 select '1'
1084 nvl(ye_asg.effective_start_date,ye_roll.START_YEAR)
1085 and ye_roll.END_YEAR
1086 and not exists(
1088 from pay_action_interlocks pai,
1089 pay_assignment_actions assact3,
1090 pay_payroll_actions pact3
1091 where pai.locked_action_id = assact2.assignment_action_id
1092 and pai.locking_action_id = assact3.assignment_action_id
1093 and pact3.payroll_action_id = assact3.payroll_action_id
1094 and pact3.action_type = 'V'
1095 and assact3.action_status = 'C'))
1096 )
1097 WHERE ye_asg.request_id = p_request_id;
1098 plog ( '_ latest assignment_action found '||to_char(SQL%ROWCOUNT));
1099 --
1100 hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',20);
1101 hr_utility.trace( 'Pick up person details as of stat_end_date' );
1102 update pay_gb_year_end_assignments ye_asg set
1103 ( LAST_NAME, FIRST_NAME, MIDDLE_NAME, DATE_OF_BIRTH, TITLE,
1104 EXPENSE_CHECK_SEND_TO_ADDRESS, NATIONAL_INSURANCE_NUMBER, SEX,
1105 PENSIONER_INDICATOR, MULTIPLE_ASG_FLAG) =
1106 ( select substr(last_name, 1,20) LAST_NAME,
1107 substr(FIRST_NAME, 1,16) FIRST_NAME,
1108 substr(MIDDLE_NAMES,1,7) MIDDLE_NAMES,
1109 DATE_OF_BIRTH, TITLE,
1110 substr(EXPENSE_CHECK_SEND_TO_ADDRESS,1,1) EXPENSE_CHECK_SENT_TO_ADDRESS,
1111 substr(NATIONAL_IDENTIFIER,1,9) NATIONAL_IDENTIFIER,
1112 substr(SEX,1,1) SEX ,
1113 decode(substr(PER_INFORMATION4,1,1),'Y','P',' ') PENSIONER_INDICATOR,
1114 decode(PER_INFORMATION9,'Y','Y',null) MULTIPLE_ASG_FLAG -- MII
1115 from per_people_f per
1116 where per.person_id = ye_asg.person_id
1117 and ye_asg.request_id = p_request_id
1118 and l_end_year between
1119 per.effective_start_date and per.effective_end_date )
1120 where ye_asg.request_id = p_request_id;
1121 plog ( '_ pick up person details '||to_char(SQL%ROWCOUNT));
1122 --
1123 -- was this employee ever a director this year
1124 hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',22);
1125 hr_utility.trace( 'set director indicator ' );
1126 update pay_gb_year_end_assignments ye_asg set ( DIRECTOR_INDICATOR ) =
1127 ( select 'D' from dual where exists ( select '1' from
1128 per_people_f per
1129 where ye_asg.person_id = per.person_id
1130 and per.effective_start_date <= l_end_year
1131 and per.effective_end_date >= l_start_year
1132 and substr(PER_INFORMATION2,1,1) = 'Y'))
1133 where ye_asg.request_id = p_request_id;
1134 plog ( '_ set director indicator '||to_char(SQL%ROWCOUNT));
1135
1136 --
1137 hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',24);
1138 hr_utility.trace( 'set termination date' );
1139 update pay_gb_year_end_assignments ye_asg
1140 set ( termination_date, termination_type ) =
1141 (select actual_termination_date ,'L'
1142 from per_periods_of_service pos,
1143 per_assignments_f asg,
1144 pay_gb_year_end_payrolls ye_roll
1145 where pos.person_id = ye_asg.person_id
1146 and ye_asg.assignment_id = asg.assignment_id
1147 and ye_asg.effective_end_date = asg.effective_end_date
1148 and asg.period_of_service_id = pos.period_of_service_id
1149 and ye_asg.payroll_id = ye_roll.payroll_id
1150 and pos.actual_termination_date is not null
1151 and nvl(pos.LAST_STANDARD_PROCESS_DATE,pos.actual_termination_date)
1152 between
1153 nvl(ye_asg.effective_start_date,
1154 nvl(ye_roll.start_previous_year,ye_roll.start_year))
1155 and least(ye_asg.effective_end_date,ye_roll.end_year))
1156 where ye_asg.request_id = p_request_id
1157 and exists ( select 1
1158 from per_periods_of_service pos,
1159 per_assignments_f asg,
1160 pay_gb_year_end_payrolls ye_roll
1161 where pos.person_id = ye_asg.person_id
1162 and ye_asg.assignment_id = asg.assignment_id
1163 and ye_asg.effective_end_date = asg.effective_end_date
1164 and asg.period_of_service_id = pos.period_of_service_id
1165 and ye_asg.payroll_id = ye_roll.payroll_id
1166 and pos.actual_termination_date is not null
1167 and nvl(pos.LAST_STANDARD_PROCESS_DATE,pos.actual_termination_date)
1168 between
1169 nvl(ye_asg.effective_start_date,
1170 nvl(ye_roll.start_previous_year,ye_roll.start_year))
1171 and least(ye_asg.effective_end_date,ye_roll.end_year))
1172 ;
1173 plog ( '_ pick up termination date '||to_char(SQL%ROWCOUNT));
1174 --
1175 hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',25);
1176 hr_utility.trace( 'Set start of Employment' );
1177 --
1178 update pay_gb_year_end_assignments ye_asg
1179 set (start_of_emp) =
1180 (select pos.date_start
1181 from per_periods_of_service pos,
1182 per_assignments_f asg,
1183 pay_gb_year_end_payrolls ye_roll
1184 where pos.person_id = ye_asg.person_id
1185 and ye_asg.assignment_id = asg.assignment_id
1186 and ye_asg.effective_end_date = asg.effective_end_date
1187 and asg.period_of_service_id = pos.period_of_service_id
1188 and ye_asg.payroll_id = ye_roll.payroll_id
1189 and pos.date_start between l_start_year and l_end_year)
1190 where ye_asg.request_id = p_request_id
1191 and exists ( select 1
1192 from per_periods_of_service pos,
1193 per_assignments_f asg,
1194 pay_gb_year_end_payrolls ye_roll
1195 where pos.person_id = ye_asg.person_id
1196 and ye_asg.assignment_id = asg.assignment_id
1197 and ye_asg.effective_end_date = asg.effective_end_date
1198 and asg.period_of_service_id = pos.period_of_service_id
1199 and ye_asg.payroll_id = ye_roll.payroll_id
1200 and pos.date_start between l_start_year and l_end_year)
1201 ;
1205 hr_utility.trace( 'Set Addresses' );
1202 plog ( '_ picked up '||to_char(SQL%ROWCOUNT) || ' Start dates');
1203 --
1204 hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',26);
1206 --
1207 update pay_gb_year_end_assignments ye_asg
1208 set (address_line1, address_line2, address_line3, town_or_city,
1209 county, postal_code) = (
1210 select pad.address_line1,
1211 pad.address_line2,
1212 pad.address_line3,
1213 pad.town_or_city,
1214 SUBSTR(l.meaning,1,27) county,
1215 SUBSTR(pad.postal_code,1,8)
1216 from per_addresses pad,
1217 hr_lookups l
1218 where pad.person_id = ye_asg.person_id
1219 and pad.primary_flag = 'Y'
1220 and l.lookup_type(+) = 'GB_COUNTY'
1221 and l.lookup_code(+) = pad.region_1
1222 and sysdate between nvl(pad.date_from, sysdate)
1223 and nvl(pad.date_to, sysdate))
1224 where ye_asg.request_id = p_request_id;
1225 plog ( '_ picked up '||to_char(SQL%ROWCOUNT) || ' Addresses');
1226
1227 --
1228 hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',27);
1229 hr_utility.trace( 'find the last action for last year' );
1230 --
1231 if p_niy = 'Y' then -- [ report NIY
1232 UPDATE PAY_GB_YEAR_END_ASSIGNMENTS ye_asg
1233 SET (PREVIOUS_YEAR_ASG_ACTION_ID, PREVIOUS_YEAR_EFFECTIVE_DATE) = (
1234 select assact.assignment_action_id,
1235 pact.effective_date
1236 from pay_payroll_actions pact,
1237 pay_assignment_actions assact
1238 where assact.payroll_action_id = pact.payroll_action_id
1239 and ye_asg.assignment_id = assact.assignment_id
1240 and ye_asg.request_id = p_request_id
1241 and assact.action_sequence =
1242 (
1243 select max(assact2.action_sequence)
1244 from pay_assignment_actions assact2,
1245 pay_payroll_actions pact2,
1246 pay_gb_year_end_payrolls ye_roll
1247 where assact2.assignment_id = ye_asg.assignment_id
1248 and assact2.payroll_action_id = pact2.payroll_action_id
1249 and pact2.payroll_id = ye_roll.payroll_id
1250 and pact2.action_type in ( 'Q','R','B','I')
1251 and assact2.action_status = 'C'
1252 and pact2.effective_date <= ye_asg.effective_end_date
1253 and pact2.effective_date between
1254 nvl(ye_asg.effective_start_date,ye_roll.START_PREVIOUS_YEAR)
1255 and ye_roll.END_PREVIOUS_YEAR
1256 and not exists(
1257 select '1'
1258 from pay_action_interlocks pai,
1259 pay_assignment_actions assact3,
1260 pay_payroll_actions pact3
1261 where pai.locked_action_id = assact2.assignment_action_id
1262 and pai.locking_action_id = assact3.assignment_action_id
1263 and pact3.payroll_action_id = assact3.payroll_action_id
1264 and pact3.action_type = 'V'
1265 and assact3.action_status = 'C'))
1266 )
1267 WHERE ye_asg.request_id = p_request_id;
1268 plog ( '_ last years latest assignment_action found '||to_char(SQL%ROWCOUNT));
1269
1270 end if; -- ] report NIY
1271 --
1272 end; -- ) insert people
1273
1274 begin -- ( insert balances
1275 hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',28);
1276 l_count := 0;
1277 for l_people in get_people loop -- { person loop
1278 l_count_values := 0; -- count the number of rows inserted into values for each
1279 l_niy := 0; -- initialize ni_y amount
1280 if p_niy = 'Y' then -- [ report NIY
1281 -- get the NI Y balance for each person
1282 if l_people.PREVIOUS_YEAR_ASG_ACTION_ID is not null then -- [ LY_ACTION
1283 hr_utility.trace( 'PREVIOUS_YEAR_ASG_ACTION_ID:'||
1284 l_people.PREVIOUS_YEAR_ASG_ACTION_ID);
1285 l_niy := 100 * hr_dirbal.get_balance(l_people.PREVIOUS_YEAR_ASG_ACTION_ID,
1286 l_niy_id);
1287 l_count := l_count + 1;
1288 end if; -- ] LY_ACTION
1289 --
1290 -- if there is no NI Y in last year and this is the latest assignment row
1291 -- for this assignment (ie not a Reference Transfer row) then check for
1292 -- NI Y Last Year. Only the latest row to avoid double counting since
1293 -- ASG_STAT_YTD goes across reference transfers unlike ASG_TD_YTD
1294 if l_niy = 0 and l_people.LAST_ASG_ACTION_ID is not null
1295 and ( l_people.termination_type is null
1296 or l_people.termination_type = 'L' ) then -- [
1297 -- get the NI Y Last Year balance
1298 l_niy := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_niy_ly_id);
1299 l_count := l_count + 1;
1300 end if; -- ]
1301 --
1302 -- delete rows for last year that have no NI Y
1303 if ( l_niy is null or l_niy = 0 ) -- [
1304 and l_people.LAST_ASG_ACTION_ID is null then
1305 delete from pay_gb_year_end_assignments
1306 where assignment_id = l_people.assignment_id
1307 and effective_end_date = l_people.effective_end_date;
1308 end if; -- ]
1309 --
1310 -- populate NI Y eoy values
1311 if l_niy <> 0 then -- [
1312 insert into pay_gb_year_end_values
1313 (ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE,REPORTABLE,
1314 TOTAL_CONTRIBUTIONS)
1315 values (
1316 l_people.assignment_id,
1317 l_people.effective_end_date,
1318 'Y' ,
1319 'Y',
1320 l_niy );
1321 end if; -- ]
1322 -- MII
1323 else -- report NIY is not 'Y'
1324 if (l_people.LAST_ASG_ACTION_ID is null) then
1325 delete from pay_gb_year_end_assignments
1326 where assignment_id = l_people.assignment_id
1327 and effective_end_date = l_people.effective_end_date;
1328 end if;
1329 end if; -- ] report NIY
1330 --
1334 -- populate NI A rows
1331 -- get the rest of the NI balances
1332 if l_people.LAST_ASG_ACTION_ID is not null then -- [ action exist
1333 hr_utility.trace( 'LAST_ASG_ACTION_ID:'||to_char(l_people.LAST_ASG_ACTION_ID));
1335 -- get the NI A Total Balance
1336 l_ni_tot := 0;
1337 l_ni_ees := 0;
1338 l_ni_able := 0;
1339 l_ni_tot := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_nia_tot_id);
1340 l_count := l_count + 1;
1341 -- if there is a total get the NI A and Able Balance
1342 if l_ni_tot <> 0 then -- [ A total exists
1343 l_ni_ees := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_nia_id);
1344 l_ni_able := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_nia_able_id);
1345 l_count := l_count + 2;
1346 -- populate year end values
1347 insert into pay_gb_year_end_values
1348 (ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE, REPORTABLE,
1349 EARNINGS,TOTAL_CONTRIBUTIONS,EMPLOYEES_CONTRIBUTIONS)
1350 values (
1351 l_people.assignment_id,
1352 l_people.effective_end_date,
1353 'A' ,'Y',
1354 l_ni_able,
1355 l_ni_tot,
1356 l_ni_ees );
1357 l_count_values := l_count_values + 1;
1358 end if; -- ] total exists
1359 -- populate NI B rows
1360 -- get the NI B Total Balance
1361 if l_people.sex = 'F' then -- [ cat B for Females only
1362 l_ni_tot := 0;
1363 l_ni_ees := 0;
1364 l_ni_able := 0;
1365 l_ni_tot := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_nib_tot_id);
1366 l_count := l_count + 1;
1367 -- if there is a total get the NI B and Able Balance
1368 if l_ni_tot <> 0 then -- [ B Total Exists
1369 l_ni_ees := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_nib_id);
1370 l_ni_able := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_nib_able_id);
1371 l_count := l_count + 2;
1372 -- populate year end values
1373 insert into pay_gb_year_end_values
1374 (ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE, REPORTABLE,
1375 EARNINGS,TOTAL_CONTRIBUTIONS,EMPLOYEES_CONTRIBUTIONS)
1376 values (
1377 l_people.assignment_id,
1378 l_people.effective_end_date,
1379 'B' ,'Y',
1380 l_ni_able,
1381 l_ni_tot,
1382 l_ni_ees );
1383 l_count_values := l_count_values + 1;
1384 end if; -- ] B Total Exists
1385 end if; -- ] cat B for Females only
1386 -- populate NI C rows
1387 l_ni_tot := 0;
1388 -- get the NI C Total Balance
1389 l_ni_tot := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_nic_tot_id);
1390 l_count := l_count + 1;
1391 -- populate year end values
1392 if l_ni_tot <> 0 then -- [ C Total exists
1393 insert into pay_gb_year_end_values
1394 (ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE, REPORTABLE,
1395 TOTAL_CONTRIBUTIONS)
1396 values (
1397 l_people.assignment_id,
1398 l_people.effective_end_date,
1399 'C' ,'Y',
1400 l_ni_tot);
1401 l_count_values := l_count_values + 1;
1402 end if; -- ] C total Exists
1403 -- populate NI D rows
1404 -- get the NI D Total Balance
1405 l_ni_tot := 0;
1406 l_ni_ees := 0;
1407 l_ni_able := 0;
1408 l_ni_co_able := 0;
1409 l_ni_co := 0;
1410 --
1411 l_ni_tot := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_nid_tot_id);
1412 l_count := l_count + 1;
1413 -- if there is a total get the NI D , CO and Able Balance
1414 if l_ni_tot <> 0 then -- [ D Total exists
1415 l_ni_ees := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_nid_id);
1416 l_ni_able := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_nid_able_id);
1417 l_ni_co_able := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_nid_co_able_id);
1418 l_ni_co := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_nid_co_id);
1419 l_count := l_count + 4;
1420 -- populate year end values
1421 insert into pay_gb_year_end_values
1422 (ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE, REPORTABLE,
1423 EARNINGS,TOTAL_CONTRIBUTIONS,EMPLOYEES_CONTRIBUTIONS,
1424 EARNINGS_CONTRACTED_OUT,CONTRIBUTIONS_CONTRACTED_OUT)
1425 values (
1426 l_people.assignment_id,
1427 l_people.effective_end_date,
1428 'D' ,'Y',
1429 l_ni_able,
1430 l_ni_tot,
1431 l_ni_ees,
1432 l_ni_co_able,
1433 l_ni_co );
1434 l_count_values := l_count_values + 1;
1435 end if; -- ] D Total Exists
1436 -- populate NI E rows
1437 -- get the NI E Total Balance
1438 if l_people.sex = 'F' then -- [ cat E for Females only
1439 l_ni_tot := 0;
1440 l_ni_ees := 0;
1441 l_ni_able := 0;
1442 l_ni_co_able := 0;
1443 l_ni_co := 0;
1444 l_ni_tot := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_nie_tot_id);
1445 l_count := l_count + 1;
1446 -- if there is a total get the NI E , CO and Able Balance
1447 if l_ni_tot <> 0 then -- [ E Total exists
1448 l_ni_ees := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_nie_id);
1449 l_ni_able := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_nie_able_id);
1450 l_ni_co_able := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_nie_co_able_id);
1451 l_ni_co := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_nie_co_id);
1452 l_count := l_count + 4;
1453 -- populate year end values
1454 insert into pay_gb_year_end_values
1458 values (
1455 (ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE, REPORTABLE,
1456 EARNINGS,TOTAL_CONTRIBUTIONS,EMPLOYEES_CONTRIBUTIONS,
1457 EARNINGS_CONTRACTED_OUT,CONTRIBUTIONS_CONTRACTED_OUT)
1459 l_people.assignment_id,
1460 l_people.effective_end_date,
1461 'E' ,'Y',
1462 l_ni_able,
1463 l_ni_tot,
1464 l_ni_ees,
1465 l_ni_co_able,
1466 l_ni_co );
1467 l_count_values := l_count_values + 1;
1468 end if; -- ] E total exists
1469 end if; -- ] cat E for Females only
1470 -- populate NI F, NI G and/or NI S rows
1471 -- sum the NI F/G/S Total Balances
1472 l_ni_tot := 0;
1473 l_ni_tot := hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_nif_tot_id) +
1474 hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_nig_tot_id) +
1475 hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_nis_tot_id);
1476 l_count := l_count + 3;
1477 -- if there is a total get the balance
1478 if l_ni_tot <> 0 then -- [ F/G/S Total(s) exist(s)
1479 -- open cursor and populate year end values
1480 declare
1481 wk_assignment_id number(10);
1482 wk_effective_end_date date;
1483 wk_cat_code varchar2(1);
1484 wk_able number(15,2) := 0;
1485 wk_Total number(15,2) := 0;
1486 wk_Employee number(15,2) := 0;
1487 wk_CO_able number(15,2) := 0;
1488 wk_CO number(15,2) := 0;
1489 wk_scon varchar2(20);
1490 wk_first_pass_yn varchar2(1) := 'Y';
1491 begin
1492 for scon_bal_rec in get_scon_bal(l_people.last_asg_action_id,
1493 l_scon_input_id, l_ni_id)
1494 loop
1495 if wk_first_pass_yn = 'Y'
1496 then
1497 wk_cat_code := scon_bal_rec.cat_code;
1498 wk_scon := scon_bal_rec.scon;
1499 wk_first_pass_yn := 'N';
1500 end if;
1501 --
1502 -- Bug Fix 678573 Start
1503 --
1504 if scon_bal_rec.cat_code = 'S' then
1505 scon_bal_rec.able := null;
1506 scon_bal_rec.Total := scon_bal_rec.Employer;
1507 scon_bal_rec.Employee := null;
1508 scon_bal_rec.CO_able := null;
1509 scon_bal_rec.CO := null;
1510 end if;
1511 --
1512 -- Bug Fix 678573 End
1513 --
1514 if wk_cat_code <> scon_bal_rec.cat_code or
1515 wk_scon <> scon_bal_rec.scon
1516 then
1517 insert into pay_gb_year_end_values
1518 (ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE, REPORTABLE,
1519 EARNINGS,TOTAL_CONTRIBUTIONS,EMPLOYEES_CONTRIBUTIONS,
1520 EARNINGS_CONTRACTED_OUT,CONTRIBUTIONS_CONTRACTED_OUT,SCON)
1521 values (
1522 l_people.assignment_id,
1523 l_people.effective_end_date,
1524 wk_cat_code,
1525 'Y',
1526 wk_able,
1527 wk_Total,
1528 wk_Employee,
1529 wk_CO_able,
1530 wk_CO,
1531 wk_scon);
1532
1533 l_count := l_count + 4;
1534 wk_able := 0;
1535 wk_Total := 0;
1536 wk_Employee := 0;
1537 wk_CO_able := 0;
1538 wk_CO := 0;
1539 end if;
1540 wk_able := wk_able + nvl(scon_bal_rec.able, 0);
1541 wk_Total := wk_Total + nvl(scon_bal_rec.Total, 0);
1542 wk_Employee := wk_Employee + nvl(scon_bal_rec.Employee, 0);
1543 wk_CO_able := wk_CO_able + nvl(scon_bal_rec.CO_able, 0);
1544 wk_CO := wk_CO + nvl(scon_bal_rec.CO, 0);
1545 wk_cat_code := scon_bal_rec.cat_code;
1546 wk_scon := scon_bal_rec.scon;
1547 /*four balances (able,Employee,CO_able & CO) fetched - even*/
1548 /* though not necessary for NIS. */
1549 /* Also no gender check performed for NIG */
1550 l_count_values := l_count_values + 1; /*one row inserted*/
1551 end loop;
1552 --
1553 if wk_first_pass_yn = 'N'
1554 then
1555 insert into pay_gb_year_end_values
1556 (ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE, REPORTABLE,
1557 EARNINGS,TOTAL_CONTRIBUTIONS,EMPLOYEES_CONTRIBUTIONS,
1558 EARNINGS_CONTRACTED_OUT,CONTRIBUTIONS_CONTRACTED_OUT,SCON)
1559 values (
1560 l_people.assignment_id,
1561 l_people.effective_end_date,
1562 wk_cat_code,
1563 'Y',
1564 wk_able,
1565 wk_Total,
1566 wk_Employee,
1567 wk_CO_able,
1568 wk_CO,
1569 wk_scon);
1570 end if;
1571 end;
1572 end if; -- ] F/G/S Total(s) Exist(s)
1573 --
1574 l_nip := 0;
1575 if l_nip_id <> 0 then
1576 l_nip := 100 * hr_dirbal.get_balance (l_people.LAST_ASG_ACTION_ID,l_nip_id);
1577 if l_nip <> 0 then
1578 insert into pay_gb_year_end_values
1579 (
1580 ASSIGNMENT_ID,
1581 EFFECTIVE_END_DATE,
1582 NI_CATEGORY_CODE,
1583 REPORTABLE,
1584 TOTAL_CONTRIBUTIONS
1585 )
1586 values
1587 (
1588 l_people.assignment_id,
1589 l_people.effective_end_date,
1590 'P',
1591 'Y',
1592 l_nip
1593 );
1597 --
1594 end if; -- l_nip <> 0
1595 l_count_values := l_count_values + 1;
1596 end if; -- l_nip_id <> 0
1598 -- if no values rows have been inserted set up a row with the current category
1599 if l_count_values = 0 then -- [ no category balances
1600 insert into pay_gb_year_end_values
1601 (ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE,REPORTABLE)
1602 select l_people.assignment_id,
1603 l_people.effective_end_date,
1604 SCREEN_ENTRY_VALUE,
1605 'Y'
1606 from pay_element_entries_f e,
1607 pay_element_entry_values_f v,
1608 pay_gb_year_end_payrolls ye_roll,
1609 pay_element_links_f link
1610 where e.assignment_id = l_people.assignment_id
1611 and v.input_value_id + 0 = l_category_input_id
1612 and link.element_type_id = l_ni_id
1613 and e.element_link_id = link.element_link_id
1614 and e.element_entry_id = v.element_entry_id
1615 and l_people.payroll_id = ye_roll.payroll_id
1616 and least(l_people.effective_end_date,ye_roll.end_year)
1617 between link.effective_start_date and link.effective_end_date
1618 and least(l_people.effective_end_date,ye_roll.end_year)
1619 between e.effective_start_date and e.effective_end_date
1620 and least(l_people.effective_end_date,ye_roll.end_year)
1621 between v.effective_start_date and v.effective_end_date;
1622 end if; -- ] no category balances
1623 -- populate the person balances
1624 if l_people.sex = 'F' then -- [ Maternity for females only
1625 l_smp := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_smp_id);
1626 l_count := l_count + 1;
1627 else l_smp := 0;
1628 end if; -- ] Maternity for females only
1629 l_ssp := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_ssp_id);
1630 l_gross := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_gross_id);
1631 l_paye := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_paye_id);
1632 l_super := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_super_id);
1633 l_widow := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_widow_id);
1634 l_taxable := 100 * hr_dirbal.get_balance(l_people.LAST_ASG_ACTION_ID,l_taxable_id);
1635 l_count := l_count + 6;
1636 update pay_gb_year_end_assignments ye_asg set
1637 SSP = l_ssp,
1638 SMP = l_smp,
1639 GROSS_PAY = l_gross,
1640 TAX_PAID = l_paye,
1641 SUPERANNUATION_PAID = l_super,
1642 WIDOWS_AND_ORPHANS = l_widow,
1643 TAXABLE_PAY = l_taxable
1644 where assignment_id = l_people.assignment_id
1645 and effective_end_date = l_people.effective_end_date;
1646 end if; -- ] action exists
1647 end loop; -- } person loop
1648 plog ( '_ balances fetched '||to_char(l_count));
1649 end; -- ) insert balances
1650 --
1651 --
1652 begin -- ( update non bal info
1653 /* most people will have had paye calculated on the last run - pick these up */
1654 hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',30);
1655 hr_utility.trace( 'see if tax was calculated on the last run of the year' );
1656 update pay_gb_year_end_assignments ye_asg set ( TAX_RUN_RESULT_ID ) =
1657 (select RUN_RESULT_ID from pay_run_results r
1658 where r.element_type_id = l_paye_details_id
1659 and r.status in ('P', 'PA')
1660 and r.assignment_action_id = ye_asg.LAST_ASG_ACTION_ID )
1661 where ye_asg.request_id = p_request_id;
1662 plog ( '_ find out if the latest action computed PAYE '||to_char(SQL%ROWCOUNT));
1663 --
1664 /* if there are any who have no tax update find the latest update */
1665 hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',32);
1666 hr_utility.trace( 'find the latest tax calculation in the year' );
1667 update pay_gb_year_end_assignments ye_asg set ( TAX_RUN_RESULT_ID ) =
1668 (select r.RUN_RESULT_ID
1669 from pay_assignment_actions assact,
1670 pay_run_results r
1671 where r.element_type_id+0 = l_paye_details_id + decode(assact.assignment_id,null,0,0)
1672 and r.assignment_action_id = assact.assignment_action_id
1673 and r.status in ('P', 'PA')
1674 and assact.assignment_id = ye_asg.assignment_id
1675 and assact.action_sequence = (
1676 select max(assact2.action_sequence)
1677 from pay_assignment_actions assact2,
1678 pay_payroll_actions pact,
1679 pay_gb_year_end_payrolls ye_roll
1680 where assact2.assignment_id = ye_asg.assignment_id
1681 and ye_roll.payroll_id = pact.payroll_id
1682 and pact.payroll_action_id = assact2.payroll_action_id
1683 and pact.effective_date between ye_roll.start_year and ye_roll.end_year
1684 and ye_asg.tax_run_result_id is null
1685 and ye_asg.last_asg_action_id is not null /*run this year */
1686 and pact.effective_date <= ye_asg.LAST_EFFECTIVE_DATE
1687 )
1688 )
1689 where ye_asg.TAX_RUN_RESULT_ID is null
1690 and ye_asg.last_asg_action_id is not null /* there has been a run this year */
1691 and ye_asg.request_id = p_request_id;
1692 plog ( '_ find the latest action that computed PAYE '||to_char(SQL%ROWCOUNT));
1693 --
1694 -- Get the details from the element entry on the added criteria that
1695 -- there exists an updating action id on the element_entry. In other words,
1696 -- this was achieved using an Update Recurring rule.
1697 --
1698 hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',33);
1699 hr_utility.trace( 'Retrieve element entry update recurring');
1700 update pay_gb_year_end_assignments ye_asg set
1701 ( TAX_CODE, W1_M1_INDICATOR,PREVIOUS_TAXABLE_PAY,PREVIOUS_TAX_PAID) =
1702 (select max(decode(iv.name,'Tax Code',SCREEN_ENTRY_VALUE,null)) Tax_code,
1703 max(decode(iv.name,'Tax Basis',SCREEN_ENTRY_VALUE,null)) Tax_Basis,
1704 100 * fnd_number.canonical_to_number(max(decode(iv.name,'Pay Previous',
1708 from pay_element_entries_f e,
1705 SCREEN_ENTRY_VALUE,null))) Pay_previous,
1706 100 * fnd_number.canonical_to_number(max(decode(iv.name,'Tax Previous',
1707 SCREEN_ENTRY_VALUE,null))) Tax_previous
1709 pay_element_entry_values_f v,
1710 pay_gb_year_end_payrolls ye_roll,
1711 pay_input_values_f iv,
1712 pay_element_links_f link
1713 where e.assignment_id = ye_asg.assignment_id
1714 and link.element_type_id = l_paye_details_id
1715 and e.element_link_id = link.element_link_id
1716 and e.element_entry_id = v.element_entry_id
1717 and iv.input_value_id = v.input_value_id
1718 and ye_asg.payroll_id = ye_roll.payroll_id
1719 and e.updating_action_id is not null
1720 and least(ye_asg.effective_end_date,ye_roll.end_year)
1721 between link.effective_start_date and link.effective_end_date
1722 and least(ye_asg.effective_end_date,ye_roll.end_year)
1723 between e.effective_start_date and e.effective_end_date
1724 and least(ye_asg.effective_end_date,ye_roll.end_year)
1725 between iv.effective_start_date and iv.effective_end_date
1726 and least(ye_asg.effective_end_date,ye_roll.end_year)
1727 between v.effective_start_date and v.effective_end_date)
1728 where ye_asg.request_id = p_request_id;
1729 plog ( '_ default tax details set '||to_char(SQL%ROWCOUNT));
1730 --
1731 -- If there is no tax code forthcoming from the last query, retrieve the
1732 -- details using the run result.
1733 --
1734 hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',34);
1735 hr_utility.trace( 'update taxcode with last taxcode used' );
1736 update pay_gb_year_end_assignments ye_asg set
1737 ( TAX_CODE, W1_M1_INDICATOR,PREVIOUS_TAXABLE_PAY,PREVIOUS_TAX_PAID) =
1738 (select max(decode(name,'Tax Code',result_value,null)) Tax_code,
1739 max(decode(name,'Tax Basis',result_value,null)) Tax_Basis,
1740 100 * fnd_number.canonical_to_number(max(decode(name,'Pay Previous',result_value,null))) Pay_previous,
1741 100 * fnd_number.canonical_to_number(max(decode(name,'Tax Previous',result_value,null))) Tax_previous
1742 from pay_input_values_f v,
1743 pay_run_result_values rrv
1744 where rrv.RUN_RESULT_ID = ye_asg.TAX_RUN_RESULT_ID
1745 and v.INPUT_VALUE_ID = rrv.INPUT_VALUE_ID
1746 and v.element_type_id = l_paye_details_id )
1747 where ye_asg.tax_code is null
1748 and ye_asg.request_id = p_request_id;
1749 plog ( '_ tax code set '||to_char(SQL%ROWCOUNT));
1750 --
1751 -- If there is still no tax code, use the element entry query without the
1752 -- update recurring criteria.
1753 --
1754 hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',35);
1755 hr_utility.trace( 'default taxcode to element entry if no tax calc run');
1756 update pay_gb_year_end_assignments ye_asg set
1757 ( TAX_CODE, W1_M1_INDICATOR,PREVIOUS_TAXABLE_PAY,PREVIOUS_TAX_PAID) =
1758 (select max(decode(iv.name,'Tax Code',SCREEN_ENTRY_VALUE,null)) Tax_code,
1759 max(decode(iv.name,'Tax Basis',SCREEN_ENTRY_VALUE,null)) Tax_Basis,
1760 100 * fnd_number.canonical_to_number(max(decode(iv.name,'Pay Previous',
1761 SCREEN_ENTRY_VALUE,null))) Pay_previous,
1762 100 * fnd_number.canonical_to_number(max(decode(iv.name,'Tax Previous',
1763 SCREEN_ENTRY_VALUE,null))) Tax_previous
1764 from pay_element_entries_f e,
1765 pay_element_entry_values_f v,
1766 pay_gb_year_end_payrolls ye_roll,
1767 pay_input_values_f iv,
1768 pay_element_links_f link
1769 where e.assignment_id = ye_asg.assignment_id
1770 and link.element_type_id = l_paye_details_id
1771 and e.element_link_id = link.element_link_id
1772 and e.element_entry_id = v.element_entry_id
1773 and iv.input_value_id = v.input_value_id
1774 and ye_asg.payroll_id = ye_roll.payroll_id
1775 and least(ye_asg.effective_end_date,ye_roll.end_year)
1776 between link.effective_start_date and link.effective_end_date
1777 and least(ye_asg.effective_end_date,ye_roll.end_year)
1778 between e.effective_start_date and e.effective_end_date
1779 and least(ye_asg.effective_end_date,ye_roll.end_year)
1780 between iv.effective_start_date and iv.effective_end_date
1781 and least(ye_asg.effective_end_date,ye_roll.end_year)
1782 between v.effective_start_date and v.effective_end_date)
1783 where ye_asg.tax_code is null
1784 and ye_asg.request_id = p_request_id;
1785 plog ( '_ default tax details set '||to_char(SQL%ROWCOUNT));
1786 --
1787 --
1788 hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',36);
1789 hr_utility.trace( 'reset codes on Year End Assignment table' );
1790 update pay_gb_year_end_assignments ye_asg set
1791 ( WEEK_53_INDICATOR, W1_M1_INDICATOR, TAX_REFUND, TAX_PAID,
1792 SUPERANNUATION_REFUND, SUPERANNUATION_PAID, TAX_CODE ) =
1793 ( select
1794 decode(ye_roll.MAX_PERIOD_NUMBER,53,'3',54,'4',56,'6',' ') WEEK_53_INDICATOR,
1795 decode(ye_asg.W1_M1_INDICATOR,'C',' ',decode(ptpt.NUMBER_PER_FISCAL_YEAR,
1796 1,'M',2,'M',4,'M',6,'M',12,'M',24,'M','W')) W1_M1_INDICATOR,
1797 decode(sign(ye_asg.TAX_PAID),-1,'R',' ') TAX_REFUND,
1798 ye_asg.TAX_PAID * sign(ye_asg.TAX_PAID) TAX_PAID,
1799 decode(sign(ye_asg.SUPERANNUATION_PAID),-1,'R',' ') SUPERANNUATION_REFUND,
1800 ye_asg.SUPERANNUATION_PAID *
1801 sign(ye_asg.SUPERANNUATION_PAID) SUPERANNUATION_PAID,
1802 nvl(ye_asg.TAX_CODE,'NI') TAX_CODE
1803 from pay_gb_year_end_payrolls ye_roll,
1804 per_time_period_types ptpt
1805 where ye_roll.payroll_id = ye_asg.payroll_id
1809 plog ( '_ reset codes '||to_char(SQL%ROWCOUNT));
1806 and ye_roll.period_type = ptpt.period_type
1807 and ye_asg.request_id = p_request_id )
1808 where ye_asg.request_id = p_request_id;
1810 --
1811 --
1812 -- multiple assignment logic
1813 -- function that fires when permit is specified that checks whether any
1814 -- multiple assignmnet people have all their assignments extracted within
1815 -- the one permit - if not error
1816 hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',40);
1817 hr_utility.trace( 'check that multiple asgs dont span permits');
1818
1819 if p_permit is not null then -- [ permit not null
1820
1821 for l_invalid_masg in get_invalid_multiple_asg loop -- { invalid
1822
1823 select substr('EMPNO:'||ASSIGNMENT_NUMBER||
1824 ' '||LAST_NAME||
1825 ' has multiple assignments on more than one permit',1,132) mess
1826 into l_error_text
1827 from pay_gb_year_end_assignments
1828 where rowid = l_invalid_masg.ye_asg_rowid
1829 and rownum = 1;
1830
1831 hr_utility.trace(l_error_text);
1832 p_retcode := 0;
1833 p_errbuf := 'multiple assignments found in more than 1 permit';
1834
1835 end loop; -- } invalid
1836
1837 plog ( '_ check multi asgs. span permits '||to_char(SQL%ROWCOUNT));
1838 end if; -- ] permit not null
1839
1840 -- identify which of the person records are the eoy primary rows
1841 hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',42);
1842 hr_utility.trace( 'find the multiple asgs primary assignment for NI');
1843 update pay_gb_year_end_assignments pp
1844 set eoy_primary_flag = 'Y' where pp.rowid = ( select
1845 substr(max(lpad(gross_pay,9,'0')||p.rowid),-18)
1846 from pay_gb_year_end_assignments p,
1847 pay_gb_year_end_payrolls ye_roll,
1848 pay_gb_year_end_payrolls yep2
1849 where p.person_id = pp.person_id
1850 and ye_roll.payroll_id = p.payroll_id
1851 and yep2.payroll_id = pp.payroll_id
1852 and ye_roll.tax_reference_number = yep2.tax_reference_number
1853 and p.MULTIPLE_ASG_FLAG is not null )
1854 and pp.MULTIPLE_ASG_FLAG is not null
1855 and pp.request_id = p_request_id;
1856 plog ( '_ for multi asgs. assign a primary asg '||to_char(SQL%ROWCOUNT));
1857
1858 -- insert a summation of all the values rows against the primary
1859 -- ensure the effective_end_date is reset to the primary's
1860 hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',44);
1861 hr_utility.trace( 'put all the NI Contribs on the primary assignment');
1862 -- Thad
1863 l_count := 0;
1864 for asg_rec in get_multi_asg_people loop
1865 for l_asg_details in get_multi_asg_prim_details(asg_rec.assignment_id)
1866 loop
1867 insert into pay_gb_year_end_values
1868 (ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE,REPORTABLE,
1869 EARNINGS,TOTAL_CONTRIBUTIONS,EMPLOYEES_CONTRIBUTIONS,
1870 EARNINGS_CONTRACTED_OUT,CONTRIBUTIONS_CONTRACTED_OUT)
1871 values (l_asg_details.s_asg_id, l_asg_details.s_end_date,
1872 l_asg_details.s_ni_cat_code,
1873 'M' , l_asg_details.s_earnings,
1874 l_asg_details.s_tot_con, l_asg_details.s_ees_con,
1875 l_asg_details.s_earnings_co, l_asg_details.s_con_co
1876 );
1877 l_count := l_count + 1;
1878 end loop;
1879 end loop;
1880 plog ( '_ for multi asgs. set NI Balances on Primary '||to_char(l_count));
1881
1882 -- set not reportable values to N on multiple assignmnet rows
1883 hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',46);
1884 hr_utility.trace( 'set reportable to N on secondary assignments');
1885 update pay_gb_year_end_values yev set REPORTABLE = 'N'
1886 where reportable = 'Y'
1887 and exists ( select '1' from pay_gb_year_end_assignments ye_asg
1888 where ye_asg.MULTIPLE_ASG_FLAG is not null
1889 and ye_asg.assignment_id = yev.assignment_id
1890 and ye_asg.effective_end_date = yev.effective_end_date
1891 and ye_asg.request_id = p_request_id );
1892
1893 plog ('_ for multi asgs. set NI Balances to non reportable on Non P Asgs '
1894 ||to_char(SQL%ROWCOUNT));
1895 -- insert x rows on non primaries
1896 hr_utility.set_location('PAY_YEAR_END_EXTRACT.EXTRACT',48);
1897 hr_utility.trace( 'set category to X where no values row exists ');
1898 insert into pay_gb_year_end_values
1899 ( ASSIGNMENT_ID,EFFECTIVE_END_DATE,NI_CATEGORY_CODE,REPORTABLE)
1900 select ye_asg.assignment_id, ye_asg.effective_end_date ,'X','Y'
1901 from pay_gb_year_end_assignments ye_asg
1902 where not exists ( select '1' from pay_gb_year_end_values yev
1903 where ye_asg.assignment_id = yev.assignment_id
1904 and ye_asg.effective_end_date = yev.effective_end_date
1905 and yev.reportable <> 'N')
1906 and ye_asg.request_id = p_request_id;
1907 plog ('_ set category to X where no values row exists '||to_char(SQL%ROWCOUNT));
1908 commit;
1909
1910 end; -- ) update non bal info
1911 plog ('PAY_YEAR_END_EXTRACT completed ');
1912
1913 -- check data extracted --
1914 /*
1915 plog ('Start CHECK TEMPORARY TABLES ');
1916 IF check_data(p_business_group_id, p_year, p_permit, p_tax_district_ref)=0 THEN
1917 p_retcode := 0;
1918 p_errbuf := 'TEMPORARY TABLE references a payroll without assignment';
1919 plog ('CHECK TEMPORARY TABLES completed with error');
1920 ELSE
1921 plog ('CHECK TEMPORARY TABLES completed ');
1922 END IF;
1923 */
1924 exception
1925 when tax_dist_ref_error then
1926 p_retcode := 0;
1927 p_errbuf := 'Above';
1928 plog ('Invalid Format for Tax District Reference: Must be three numerics');
1929 end; -- ) end extract
1930 --
1931 --
1932 end pay_year_end_extract;
[Home] [Help]