1 package pay_year_end_extract AUTHID CURRENT_USER as
2 /* $Header: payyeext.pkh 115.2 99/07/17 05:40:40 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.pkh
13 NOTES
14 GB Year End Extract - populate the year end tables ready for the p35
15 --
16 EOY EXTRACT
17
18 Table Structure: Key Reference Attributes
19 Pay_year_end_payrolls Payroll
20 Tax_reference
21 Permit
22 Business_group
23
24 Pay_year_end_assignments Assignment, Effective_end_date
25 Payroll
26 Request
27 Multi_Assignment Indicator
28 Last_Asg_Action
29 Last_Year_Last_Asg_action
30 Tax_Run_Result
31
32 Pay_year_end_values Assignment,
33 Effective_end_date,
34 Reportable
35 NI Category
36
37 Extract Design:
38 0) Delete from year end tables for the permit
39 0.1 delete all rows in values for this Permit,
40 or if no Permit is specified for the tax ref
41 or if no tax ref specified for business group.
42 0.2 delete all rows in Assignments for this Permit, Tax Reference
43 or Business_group
44 0.3 delete all rows from Payrolls for this Permit , Tax Reference
45 or Business Group
46
47 1) Populate pay_year_end_payrolls
48 set up the payrolls with the start and end dates for the year from
49 per_time_periods note populate all permits for the Payrolls table
50 - we need to know the start and end of the year for all payrolls
51 within the bg
52
53 2) Populate Pay_year_end_assignments
54 2.1 Insert rows to extract
55 asg_id
56 payroll_id
57 effective_end_date
58 request_id
59 assignment_number
60 person_id
61 organization_id
62 location_id
63 people_group_id
64 select all the assignments for a particular Permit, note only
65 latest date effective row is required-the Permit(via Payroll_id)
66 for that row dictates where it is to be reported even if the
67 assignment has been on more than one payroll in the year. The
68 exception is where Tax Reference transfers have occurred - these
69 are reported separately as though they were terminations. Some
70 assignments may have been terminated in the previous year but
71 reported at this year end as they incurred NI Y -set up ASG rows
72 for these cases - some of these rows will be deleted later after
73 the NI Y has been fetched as 0.
74
75 2.1.1 find the latest assignment within payroll year
76 2.1.2 add assignment rows for tax reference changes
77 set termination_date = effective_end_date
78 set termination_type = 'R' meaning tax Reference Transfer
79
80
81 2.2 Set effective_start_date for each row
82 2.2.1 If extract is for all permits
83 :Set effective_start_date from prior ASG row
84 2.2.2 else :set effective_start_date from base table
85 note the effective_start_date remains unset for people with no transfer
86
87 2.3 set dates to retrieve balances from
88 2.3.1 Find the last action current year
89 (set last_asg_action_id,last_effective_date)
90 Completed actions for runs, quickpay,
91 balance adjustments, balance initialization
92
93 2.4 Fetch Person Information as of end of year
94 last_name substr(1,20)
95 first_name substr(1,7)
96 middle_name substr(1,7)
97 date_of_birth ddmmyy
98 expense_check_send_to_address
99 national_insurance_number national_identifier(1,9)
100 sex substr(1,1)
101 pensioner_indicator P if per_information4 = 'Y'
102 director_indicator D if per_information1 = 'Y'
103 ni_period_type per_information9(1,30)
104
105 2.5 set termination date if its within the 2 years dependent
106 on whether the last date processed is within the
107 effective_start_date and effective_end_date ( ie
108 cater for rehires) set termination date for tax
109 reference transfers to effective_end_date
110
111 2.6 Find the last action last year
112 (set previous_year_asg_action_id,previous_year_effective_date]-
113 only needed for NI Y balance so filter if possible)
114
115
116 2.7 Set Assignment Balances [ASSIGNMENT LOOP for each row in ASG do]
117 2.7.1 Fetch NI Y
118 2.7.1.1 get NI Y _asg_stat_ytd using previous_year_asg_action_id
119 2.7.1.2 if no NI Y balance get NI Y Last Year _asg_stat_ytd
120 using last_asg_action_id
121 2.7.1.3 if no NI Y balance and no last_asg_action_id then
122 delete the person row
123 2.7.1.4 insert into Values the NI Y row
124
125 2.7.2 Fetch NI A using last_asg_action_id
126 2.7.2.1 get NI A Total _asg_td_ytd
127 2.7.2.2 if NI A Total exists get NI A _asg_td_ytd
128 2.7.2.3 if NI A Total exists get NI A Able_asg_td_ytd
129 2.7.2.4 if NI A Total exists insert into Values the NI A row
130
131 2.7.3 Fetch NI B as for NI A only check Female employees
132
133 2.7.4 Fetch NI C
134 2.7.4.1 get NI C Total _asg_td_ytd using last_asg_action_id
135 2.7.4.2 if NI C Total exists insert into Values the NI C row
136
137 2.7.5 Fetch NI D using last_asg_action_id
138 2.7.5.1 get NI D Total _asg_td_ytd
139 2.7.5.2 if NI D Total exists get NI D _asg_td_ytd
140 2.7.5.3 if NI D Total exists get NI D Able_asg_td_ytd
141 2.7.5.4 if NI D Total exists get NI D CO Able_asg_td_ytd
142 2.7.5.5 if NI D Total exists get NI D CO_asg_td_ytd
143 2.7.5.4 if NI D Total exists insert into Values the NI D row
144
145 2.7.6 Fetch NI E as for NI A only check Female employees
146
147 2.7.7 fetch Assignment balances using last_asg_action_id
148 smp SMP Total_asg_td_ytd (females)
149 ssp SSP Total_asg_td_ytd
150 gross_pay Gross Pay_asg_td_ytd
151 tax_paid PAYE_asg_td_ytd
152 superannuation_paid Superannuation Total_asg_td_ytd
153 widows and orphans Widows and Orphans_asg_td_ytd
154 taxable_pay Taxable_pay_asg_td_ytd
155 update ASG row with balances
156 [end ASSIGNMENT LOOP]
157
158 2.8 Tax information is picked up the last time it was calculated
159 2.8.1 check PAYE was updated in last_asg_action_id[tax_run_result_id]
160 2.8.2 if not - look for the last PAYE result[tax_result_id]
161 2.8.3 set tax_code Tax Code
162 w1_m1_indicator Tax Basis
163 previous_taxable_pay Pay Previous
164 previous_tax_paid Tax Previous
165
166
167
168 2.9 Reset codes on the ASG table
169 week_53_indicator 53(3), 54(4), 56(6) from
170 Payrolls.max_period_number
171 w1m1_indicator number_per_fiscal = 1,2,4,6,12,
172 24 M else W
173 tax_refund if tax_paid < 0
174 tax_paid make +ve
175 superannuation_refund if superannuation_paid < 0
176 superannuation_paid make +ve
177
178
179
180 3) Re-assign NI Values for multiple assignments
181 3.1 check that multiple assignments don't span permits -report error
182
183 3.2 sum the values balances for all the assignments associated with the eoy_primary assignment( set reportable flag)
184
185 3.3 mark the balances for secondary assignments as non reportable
186
187 3.4 insert the X rows for non primary multiple assignments
188 {issue do we need X rows for all assignments with no NI figures?}
189
190
191
192 MODIFIED
193 --
194 asnell 23-OCT-1995 Created
195 asnell 28-APR-1998 BUG 662438 Scon number fetch for Initialized
196 balances
197 --
198 * ---------------------------------------------------------------------------
199 */
200 --
201 -- output a message on the process log
202 procedure PLOG ( p_message IN varchar2 );
203
204 -- housekeeping procedure called pre extract to delete previously extracted
205 -- data
206
207 procedure trash(p_permit in varchar2 ,
208 p_business_group_id in number,
209 p_tax_district_ref in varchar2,
210 p_year in number) ;
211
212 -- populate the eoy tables
213 procedure extract(p_permit in varchar2 ,
214 p_business_group_id in number,
215 p_tax_district_ref in varchar2,
216 p_year in number,
217 p_request_id in number,
218 p_niy in varchar2,
219 p_retcode out number ,
220 p_errbuf out varchar2
221 ) ;
222 --
223 function get_nearest_scon
224 (
225 p_element_entry_id IN number ,
226 p_category IN varchar2 ,
227 p_effective_date IN date
228 ) return varchar2;
229 pragma restrict_references (get_nearest_scon, WNDS);
230 --
231
232
233 end pay_year_end_extract;
[Home] [Help]