1 PACKAGE BODY pay_gb_eoy_magtape AS
2 /* $Header: pygbemag.pkb 120.37.12020000.5 2013/01/18 09:51:59 sampmand ship $ */
3 /*
4 Change List
5 -----------
6 Date Name Vers Bug No Description
7 +-----------+-------------+--------+-------+-----------------------+
8 11-Dec-1995 P.Driver (Original)
9 22-Nov-1999 A.Mills 40.0 Used original as start point,
10 cursors re-written to use
11 archive views or functions:
12 header_cur, emps_cur,
13 emp_values, econ_chk.
14 New functionality and
15 procedures added, package
16 renamed to above. See LLD.
17 14-Dec-1999 A.Parkes 40.1 Fire econ_chk cursor for
18 every type one record.
19 18-Jan-2000 A.Parkes 40.2 Allow >= 5 type 2 errors.
20 24-Jan-2000 A.Mills 110.0 = 40.3 Added P60 Type functionality.
21 11-Feb-2000 A.Parkes 110.1 1178972 Changed select of gross_pay
22 in emps_cur cursor.
23 29-Feb-2000 A.Mills 115.0 forward ported.
24 22-Mar-2000 A.Mills 115.1 1232417 Expanded error message size
25 for bug fix of eoy process.
26 Using DBI for error message.
27 31-Mar-2000 A.Parkes 115.2 1232417 Allow length(tax_code) <= 7
28 and smp <= 99999999
29 17-Apr-2000 A.Mills 115.3 1265531 Changed emps_cur to ensure
30 Middle name is 7 chars
31 in the Magtape.
32 12-Jun-2000 A.Blinko 115.4 1268568 Now processes assignments with
33 >5 NI categories correctly.
34 13-Jul-2000 A.Mills 115.5 1364509 Add EET, Student Loans,
35 =110.6 Tax Credits and Ees Rebate
36 to outputs to MAG_RECORD2 and 4.
37 Altered validation of name fields
38 removed SCON checking in main
39 procedure, now validated in the
40 formula using call to new
41 generic validate function.
42 02-Aug-2000 A.Mills 115.6 Fixed minor conversion error
43 found in unit testing.
44 07-Sep-2000 A.Parkes 115.7 Fixed magtape validation so
45 + is disallowed.
46 Added EDI validation checks.
47 19-Oct-2000 A.Mills 115.8 Performance tune for emps_cur.
48 NB more substr etc formatting
49 inline with 10.7 code, this
50 speeds up code due to reduced
51 sort key.
52 16-Feb-2001 A.Parkes 115.9 Allow = in EDI charset
53 13-Mar-2001 A.Parkes 115.10 1682586 Changed header_cur subquery
54 to filter on char payroll_ids
55 Also removed 'Dan Tow Decode'.
56 18-Sep-2001 A.Mills 115.11 1778139 Added Assignment Message for
57 asgs that have been updated
58 during the run (warnings).
59 20-Sep-2001 R. Makhija 115.12 1585510 Removed references to EET
60 1802363 balance values, changed emps_cur
61 to select full assignment_number,
62 increased length of number
63 variabes.
64 19-Oct-2001 K.Thampan 115.13 Put blank into tax code field of
65 mag record row two
66 17-DEC-2001 R.Makhija 115.14 Increased length of student
67 loan variables
68 18-Nov-2001 R.Makhija 115.15 Added P14 EDI functionality
69 09-JAN-2002 R.Makhija 115.16 Added Checkfile commands
70 29-JAN-2002 R.Makhija 115.17 Added 'SET VARIFY OFF' at the
71 beginning to fix GSCC warning.
72 11-FEB-2002 R.Makhija 115.18 Added 2 more parameters for
73 EDI EMP HEADER formula to
74 pass middle name and
75 and title of an employee.
76 08-MAY-2002 A.Mills 115.19 Aggregated PAYE changes. Skip
77 the employee type 2 record if
78 all balances are zero, must be
79 aggregated.
80 19-jul-2002 Vimal 115.21 Fixes bug 2392279. The chanegs added
81 to version 20 does not work as the pkg
82 fails to compile on UTF8 database.
83 So the fix was to chaneg the variable
84 declaration of the address line
85 to size greater than 27. Some other
86 variable were also changed so that
87 the process does not fail bcos of this
88 error again.
89 05-DEC-2002 V.Vinod 115.22 2696015 P14 EDI Enhancement for Year 2003
90 13-SEP-2003 npershad 115.25 3133921 P14 EDI/P35 MT Functional Changes
91 for End of Year 2003/2004
92 24-MAR-2004 A.Mills 115.26 3527428 Fixed header_cur to ensure that
93 Tax District Ref is 3 characters,
94 issue found in P14EDI with short
95 Tax Dist Ref No.
96 10-MAY-2004 npershad 115.27 3614251 Added nvl call in cursor emps_cur,
97 for field X_SUPERANNUATION_PAID.
98 21-OCT-2004 rmakhija 115.28 3962706 Changed cursor emps_cur to suppress
99 21-OCT-2004 rmakhija 115.28 3962706 Changed cursor emps_cur to suppress
100 21-OCT-2004 rmakhija 115.28 3962706 Changed cursor emps_cur to suppress
101 secondary aggregated asssignments.
102 15-NOV-2004 rmakhija 115.29 4011263 P14 EDI Changes for 2004-2005.
103 07-DEC-2004 rmakhija 115.30 4011263 Added coomit and exit at the end
104 21-JAN-2005 rmakhija 115.31 4108896 Added new validations for First, Last
105 and Middle name in validate_input
106 function. Also changed emp_values
107 cursor to select only non-zero
108 NI records.
109 01-MAR-2005 rmakhija 115.32 4216135 Changed emp_values to make sure
110 atleast NI Cat X is reported
111 when there is not enough earning
112 therefore NI balances are 0
113 11-MAR-2005 rmakhija 115.33 4234348 Changed emp_values to make sure
114 Ni Cats with 0 lel/et/uel are
115 processed first so that contribs
116 in these NI Cats can be rolled up
117 into another NI Cat
118 19-MAY-2005 rmakhija 115.34 4362883 Change submit_reports to set
119 printer and copies oprions
120 as entered by the user on EOY
121 request before submitting the
122 reports.
123 09-JUN-2005 rmakhija 115.35 Added nvl,ltrim and rtrim around
124 first_name, middle_names, title
125 and country to handle spaces in
126 these fields as null values.
127 16-JUN-2005 rmakhija 115.36 Increased length of some number
128 variables in this package so
129 pl/sql error is not raised by
130 this package when value is too
131 large but a user friendly error
132 message will be raised by the EOY
133 formula
134 14-Nov-2005 rmakhija 115.37 Changed for EOY 2005-06
135 01-Dec-205 mgera 115.38 Added extra validation for SCON check.
136 in validate_input function
137 02-Dec-2005 rmakhija 115.39 Further changes for EOY 2005-06
138 10-JAN-2005 rmakhija 115.40 Further changes for EOY 2005-06
139 08-FEB-2006 kthampan 115.41 Added validation for P11D_EDI
140 08-DEC-2006 rmakhija 115.42 EOY 2006-07 changes
141 21-JAN-2007 rmakhija 115.43 Excluded NI Cat C from aggregated
142 validations. Also added sum of
143 total contributions as a
144 parameter to the P14 emp trailer
145 formula.
146 25-Nov-2007 A.Ganguly 115.44 Added validate_tax_code_1
147 29-Oct-2007 pbalu 115.45 6281170 Added a new parameter for Formula
148 PAY_GB_EDI_P14_EMP_TRAILER
149 29-Oct-2007 rlingama 115.46 5671777 BUG 5671777-5 Changed Start date of the EOY process
150 to reflect start of the tax year.so no need to add
151 12 months to the start date
152 2-Nov-2007 parusia 115.47 6345375 Included 2 additional validation modes for
153 in validate_input function for validating Last_name
154 and First_name in P45(3) and P46 PENNOT
155 13-Nov-2007 A.Ganguly 115.48 6345375 Added function get_payroll_version
156 for the EOY Apr 08 Changes
157 26-Nov-2007 parusia 115.49 6345375 Added validation modes in validate_input()
158 for PostalCode and Title.
159 Added code to remove leading minus
160 sign from NUMBER_1 validate_mode.
161 28-Nov-2007 parusia 115.50 6345375 Remove hardcoded 'apps' from csr_get_version
162 as it was failing in GSCC checks.
163 30-Nov-2007 parusia 115.51 6345375 Removed numbers from valid character set for
164 P45_46_FIRST_NAME, P45_46_LAST_NAME, P45_46_TITLE
165 30-NOV-2007 pbalu 115.52 6281170 To change the condition for contribution
166 rollup and LEL rollup as part of EOY 07/08
167 20-Nov-2008 namgoyal 115.55 7540858 Allowed space as a valid character in P45_46_FIRST_NAME
168 19-DEC-2009 vijranga 115.56 7043405 LEL Rollup condition (added for EOY 07/08) reverted back.
169 17-Mar-2009 rlingama 115.57 8338575 Removed the first character validation for address lines
170 22-Apr-2009 dwkrishn 115.58 8439388 Last Name should not have '.' Full stop in the char set
171 18-Jun-2009 pbalu 115.59/60 8357870 Created new formula based on PAY_GB_EDI_P14_NI_DETAILS to
172 enable users to run EOY for reconciliation.
173 25-Jun-2009 pbalu 115.61 8357870 To pass NI UAP balance value to PAY_GB_EDI_P14_NI_DETAILS_INTERIM
174 21-Aug-2009 krreddy 115.62 8541978 Added PRAGMA statement in the procedure submit_recon_report.
175 10-Sep-2009 npannamp 115.63 8816832 Implement 2009-10 EOY validations as in MIG
176 03-Nov-2009 namgoyal 115.64 8986543 Added mode P46_CAR_TIT_N_FSTNM in validate_input for
177 P46 Car EDI version3
178 05-Nov-2009 npannamp 115.65 8833756 2009-10 EOY - Added check to identify single assignments with
179 NI Aggregation flag set wrongly.
180 05-Nov-2009 npannamp 115.66 8833756 Code review comments incorporated.
181 26-Feb-2010 npannamp 115.67 9414865 Bug Fix in create_record_type1 procedure.
182 06-Oct-2010 npannamp 115.68 10188309 EOY 10/11 Changes.
183 18-Feb-2011 krreddy 115.69 10066755 Modified to avoid impact of enabling skip term leg rule
184 04-Jul-2011 pprvenka 115.70 12694562 EOY 11/12 Changes.
185 18-Jul-2011 pprvenka 115.71 12765309 EOY 11/12 Changes. Included 2 parameters for the
186 formula PAY_GB_EDI_P14_NI_DETAILS
187 08-OCT-12 sampmand 115.72 14729775 EOY 2012/13 changes for bug
188 17-OCT-12 sampmand 115.73 14729775 EOY 2012/13 changes-updated comments
189 17-OCT-12 sampmand 115.74 14729775 EOY 2012/13 changes-updated comments
190 18-NOV-12 sampmand 115.75 16090623 prm_values reduced for 26 to 24 for PAY_GB_EDI_P14_NI_DETAILS formula
191 */
192 fetch_new_header BOOLEAN := TRUE; -- Shows if new header record needed
193 process_emps BOOLEAN := FALSE; -- Shows if get employees records
194 edi_process_emp_header BOOLEAN := FALSE; -- get employee header for EDI
195 edi_process_ni_details BOOLEAN := FALSE; -- get employee ni details for EDI
196 edi_process_emp_trailer BOOLEAN := FALSE; -- get employee trailer for EDI
197 fin_run BOOLEAN := FALSE; -- End of run flag
198 sub_header BOOLEAN := FALSE; -- Create the record type2 sub
199 permit_change BOOLEAN := FALSE; -- set if the permit_no changes
200 process_dummy BOOLEAN := FALSE; -- Set if > 4 NI codes are found
201 g_ni_total NUMBER(3) := 0; -- Number of Ni codes found
202 g_last_ni NUMBER(3) := 0; -- Index through NI PL/SQL tables
203 --
204 g_permit_no VARCHAR2(12); -- The current permit number must be held
205 g_tax_dist_ref VARCHAR2(3) :=NULL;
206 g_payroll_id NUMBER(15); -- The current payroll id held between
207 g_payroll_action_id NUMBER(9); -- The current payroll action id.
208 g_assignment_action_id NUMBER(15); -- Assignment Action
209 g_record_index NUMBER(2) := 0; -- Counter for mag tape parameters
210 g_tot_contribs NUMBER(15):=0; -- Total contribution by permit_no
211 g_tot_student_ln NUMBER(15):=0; -- Total Student Loans for permit
212 g_tot_tax NUMBER(12):=0; -- Total tax by permit_no
213 g_tot_rec2 NUMBER(7) :=0; -- Total of record 2's
214 g_tot_rec2_per NUMBER(7) :=0; -- Number of record 2's by permit_no
215 g_tot_ssp_rec NUMBER(15):=0; -- Total ssp by permit_no
216 g_tot_smp_rec NUMBER(15):=0; -- Total smp by permit_no
217 g_tot_sap_rec NUMBER(15):=0; -- Total sap by permit_no --P35/P14 EOY 2003/2004
218 g_tot_spp_rec NUMBER(15):=0; -- Total spp by permit_no --P35/P14 EOY 2003/2004
219 g_tot_aspp_rec NUMBER(15):=0; -- Total aspp by permit_no --P35/P14 EOY 2012/2013
220 /* Start 4011263
221 g_tot_smp_comp NUMBER(15):=0; -- Total smp compensated by permit_no
222 g_tot_spp_comp NUMBER(15):=0; -- Total spp compensated by permit_no --P35/P14 EOY 2003/2004
223 g_tot_sap_comp NUMBER(15):=0; -- Total sap compensated by permit_no --P35/P14 EOY 2003/2004
224 End 4011263 */
225 g_tot_ers_rebate NUMBER(11):=0; -- Total ers rebate by permit
226 g_tot_ees_rebate NUMBER(11):=0; -- Total Ees rebate by permit
227 g_eoy_mode VARCHAR2(30):='P'; -- THE eoy mode defaults to partial
228 g_edi_sender_id VARCHAR2(35); -- EDI sender id
229 g_request_id NUMBER; -- Payroll action's request id
230 g_test_indicator VARCHAR2(1):='N'; -- THE test indicator defaults to No
231 -- 4011263: Add Unique Test ID
232 g_unique_test_id VARCHAR2(12);
233 g_return_type VARCHAR2(1);
234 -- g_urgent_marker VARCHAR2(1):='N'; THE urgent marker removed for 4011263
235 --
236 -- Record type 1 placeholders
237 g_new_permit_no VARCHAR2(12); -- The recently fetched permit number
238 g_new_payroll_id NUMBER(15); -- The recently fetched payroll id
239 g_tax_district_ref VARCHAR2(3);
240 g_old_tax_dist_ref VARCHAR2(3);
241 g_tax_ref_no VARCHAR2(10); -- 4011263: length 10 chars
242 g_old_tax_ref_no VARCHAR2(10);
243 -- 4011263: g_tax_district_name VARCHAR2(40);
244 g_tax_year VARCHAR2(4);
245 g_employers_name VARCHAR2(100);
246 -- 4752018:g_employers_address VARCHAR2(300);
247 /* Start 4011263
248 g_econ VARCHAR2(9);
249 g_ssp_recovery NUMBER(15);
250 g_smp_recovery NUMBER(15);
251 g_smp_compensation NUMBER(15);
252 g_spp_recovery NUMBER(15); --P35/P14 EOY 2003/2004
253 g_spp_compensation NUMBER(15); --P35/P14 EOY 2003/2004
254 g_sap_recovery NUMBER(15); --P35/P14 EOY 2003/2004
255 g_sap_compensation NUMBER(15); --P35/P14 EOY 2003/2004
256 End 4011263 */
257
258 --
259 -- Record type 2 placeholders
260 g_employee_number VARCHAR2(30);
261 g_last_name VARCHAR2(80);
262 g_first_name VARCHAR2(80);
263 g_middle_name VARCHAR2(80);
264 g_full_name VARCHAR2(165);
265 g_title VARCHAR2(80);
266 g_date_of_birth VARCHAR2(8);
267 g_national_insurance_number VARCHAR2(9);
268 g_start_of_emp VARCHAR2(8);
269 g_termination_date VARCHAR2(8);
270 g_sex VARCHAR2(1);
271 g_address_line1 VARCHAR2(80);
272 g_address_line2 VARCHAR2(80);
273 g_address_line3 VARCHAR2(80);
274 g_town_or_city VARCHAR2(80);
275 g_country VARCHAR2(80);
276 g_full_address VARCHAR2(320); -- temp var used in address ordering
277 g_postal_code VARCHAR2(9);
278 g_tax_code VARCHAR2(7);
279 g_assignment_id per_all_assignments_f.assignment_id%type;
280 g_w1_m1_indicator VARCHAR2(1);
281 g_ssp NUMBER;
282 g_smp NUMBER;
283 g_spp NUMBER; --P35/P14 EOY 2003/2004
284 g_aspp NUMBER; -- EOY 12/13
285 g_sap NUMBER; --P35/P14 EOY 2003/2004
286 l_spp_adopt NUMBER; --P35/P14 EOY 2003/2004
287 l_spp_birth NUMBER; --P35/P14 EOY 2003/2004
288 -- EOY 12/13
289 l_aspp_adopt NUMBER;
290 l_aspp_birth NUMBER;
291 --4011263: g_gross_pay NUMBER(15);
292 g_tax_paid NUMBER;
293 g_tax_refund VARCHAR2(1);
294 g_previous_taxable_pay NUMBER;
295 g_previous_tax_paid NUMBER;
296 -- 4011263: g_superannuation_paid NUMBER(9);
297 -- 4011263: g_superannuation_refund VARCHAR2(1);
298 g_widows_and_orphans NUMBER;
299 g_student_loans NUMBER;
300 g_week_53_indicator VARCHAR2(1);
301 g_taxable_pay NUMBER;
302 /* 4011263
303 g_pension_indicator VARCHAR2(1);
304 4011263 */
305 g_director_indicator VARCHAR2(1);
306 g_ni_multi_asg_flag VARCHAR2(1);
307 --
308 -- Some variables for P14 EDI process
309 g_edi_ni_cat_count NUMBER := 0; -- counts number of NI categories for an employee
310 g_edi_ni_cat_index NUMBER := 0; -- index of NI categories of an employee
311 g_edi_emp_ers_rebate NUMBER := 0; -- Total of ers rebate for an employee
312 g_edi_emp_ees_rebate NUMBER := 0; -- Total of ees rebate for an employee
313 /* Start 4011263
314 -- Bug 2696015: Added for P14 EDI Enhancement
315 g_edi_submitter_no VARCHAR2(10); -- EDI Submitter Number
316 --
317 End 4011263 */
318 g_rollup_ni_cat VARCHAR2(1) := ' ';
319 --g_rollup_scon VARCHAR2(9) := ' ';--EOY 2012/2013
320 g_rollup_emp_contrib NUMBER := 0;
321 g_rollup_tot_contrib NUMBER := 0;
322 --
323 g_start_year DATE;
324 g_end_year DATE;
325 /* PL/SQL table definitions */
326 --
327 --TYPE scon_typ IS TABLE OF VARCHAR2(9) --EOY 2012/2013
328 -- INDEX BY BINARY_INTEGER;
329 TYPE category_typ IS TABLE OF VARCHAR2(1)
330 INDEX BY BINARY_INTEGER;
331 TYPE balance_tab_typ IS TABLE OF NUMBER(15)
332 INDEX BY BINARY_INTEGER;
333 --
334 --scon_tab scon_typ; --EOY 2012/2013
335 category_tab category_typ;
336 total_contrib_tab balance_tab_typ;
337 employees_contrib_tab balance_tab_typ;
338 ni_able_et_tab balance_tab_typ;
339 ni_able_lel_tab balance_tab_typ;
340 ni_able_uel_tab balance_tab_typ;
341 ni_able_uap_tab balance_tab_typ; -- 8357870
342 ni_able_auel_tab balance_tab_typ; --- EOY 07/08
343 employers_rebate_tab balance_tab_typ;
344 employees_rebate_tab balance_tab_typ;
345 --
346 g_rollup_lel_ni_cat pay_gb_year_end_values_v.ni_category_code%TYPE;
347 g_total_rollup_lel pay_gb_year_end_values_v.ni_able_lel%TYPE;
348 --
349 g_emp_tot_lel NUMBER; -- total of NI ABle LEL for agg asgs (not NI X)
350 g_emp_tot_et NUMBER; -- total of NI ABle ET for agg asgs (not NI X)
351 g_emp_tot_uap NUMBER; -- 8816832 total of NI ABle UAP for agg asgs (not NI X)
352 g_emp_tot_uel NUMBER; -- total of NI ABle UEL for agg asgs (not NI X)
353 g_emp_tot_ee_contrib NUMBER; -- total of EE Contribs for agg asgs (not NI X)
354 g_emp_tot_ee_er_contrib NUMBER; -- total of EE Contribs for agg asgs (not NI X)
355
356 -- 8357870 begin - interim solution
357 l_ni_tax_year varchar2(4); -- EOY 2011/12
358 --
359 -- Cursor definitions
360 CURSOR C_NI_NEW_TAX_YEAR is
361 select to_char(fnd_date.canonical_to_date(max(ni.global_value)),'YYYY')
362 from ff_globals_f ni
363 where ni.global_name = 'NI_NEW_TAX_YEAR'
364 and ni.business_group_id is null
365 and ni.legislation_code = 'GB';
366 /* and sysdate between ni.effective_start_date
367 and ni.effective_end_date;*/
368 -- 8357870 end
369 --
370 CURSOR header_cur(c_payroll_action_id NUMBER) IS
371 SELECT UPPER(a.permit_number)
372 ,a.payroll_id
373 ,lpad(TO_CHAR(a.tax_district_reference),3,'0')
374 ,a.tax_reference_number
375 ,NVL(TO_CHAR(a.tax_year),' ') -- 4011263
376 ,a.employers_name
377 /* 4752018 - EOY 2005-06
378 ,a.employers_address_line
379 4752018 */
380 /* Start 4011263
381 ,UPPER(NVL(a.econ,'?'))
382 ,nvl(a.ssp_recovered,0)
383 ,nvl(a.smp_recovered,0)
384 ,nvl(a.smp_compensation,0)
385 --Added the below four fields for P35/P14 EOY 2003/2004
386 ,nvl(a.spp_recovered,0)
387 ,nvl(a.spp_compensation,0)
388 ,nvl(a.sap_recovered,0)
389 ,nvl(a.sap_compensation,0)
390 End 4011263 */
391 FROM pay_gb_year_end_payrolls_v a
392 WHERE a.payroll_action_id = c_payroll_action_id
393 AND EXISTS (SELECT '1'
394 FROM pay_assignment_actions paa,
395 ff_user_entities fue,
396 ff_archive_items fai
397 WHERE paa.payroll_action_id = a.payroll_action_id
398 AND fue.user_entity_name = 'X_PAYROLL_ID'
399 AND fai.user_entity_id = fue.user_entity_id
400 AND fai.context1 = paa.assignment_action_id
401 AND fai.value = to_char(a.payroll_id))
402 ORDER BY a.tax_district_reference, a.tax_reference_number, a.permit_number,a.payroll_id;
403 --
404 CURSOR emps_cur(c_payroll_id NUMBER, c_payroll_action_id NUMBER) IS
405 SELECT
406 max(decode(fue2.user_entity_name,'X_ASSIGNMENT_NUMBER',
407 fai2.VALUE))
408 ,act.assignment_action_id
409 ,nvl(max(decode(fue2.user_entity_name,'X_LAST_NAME',
410 substr(fai2.value,1,35))),' ') LAST_NAME
411 ,nvl(max(decode(fue2.user_entity_name,'X_FIRST_NAME',
412 SUBSTR(ltrim(rtrim(fai2.value)),1,35))), ' ') FIRST_NAME
413 ,nvl(max(decode(fue2.user_entity_name,'X_MIDDLE_NAME', SUBSTR(ltrim(rtrim(fai2.value)),1,35))), ' ')
414 ,nvl(max(decode(fue2.user_entity_name,'X_TITLE', SUBSTR(ltrim(rtrim(fai2.value)),1,35))), ' ')
415 ,nvl(max(decode(fue2.user_entity_name,'X_DATE_OF_BIRTH',
416 TO_CHAR(fnd_date.canonical_to_date(fai2.value),'DDMMYYYY'))),' ')
417 ,nvl(max(decode(fue2.user_entity_name,'X_SEX', substr(UPPER(fai2.value),1,1))),' ')
418 ,nvl(ltrim(max(decode(fue2.user_entity_name,'X_ADDRESS_LINE1',
419 decode(fai2.value,'','',rpad(fai2.value,35))))), ' ')
420 ,nvl(ltrim(max(decode(fue2.user_entity_name,'X_ADDRESS_LINE2',
421 decode(fai2.value,'','',rpad(fai2.value,35))))), ' ')
422 ,nvl(ltrim(max(decode(fue2.user_entity_name,'X_ADDRESS_LINE3',
423 decode(fai2.value,'','',rpad(fai2.value,35))))), ' ')
424 ,nvl(ltrim(max(decode(fue2.user_entity_name,'X_TOWN_OR_CITY',
425 decode(fai2.value,'','',rpad(fai2.value,35))))), ' ')
426 ,nvl(max(decode(fue2.user_entity_name,'X_COUNTRY', -- 4011263
427 decode(fai2.value,'','',rpad(ltrim(rtrim(fai2.value)),27)))), ' ')
428 ,nvl(max(decode(fue2.user_entity_name,'X_POSTAL_CODE',
429 substr(fai2.value,1,9))),' ')
430 ,nvl(max(decode(fue2.user_entity_name,'X_TAX_CODE',
431 ltrim(rtrim(UPPER(fai2.value))))),' ')
432 ,nvl(max(decode(fue2.user_entity_name,'X_W1_M1_INDICATOR',
433 substr(UPPER(fai2.value),1,1))),' ')
434 ,nvl(max(decode(fue2.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',
435 substr(UPPER(fai2.value),1,9))),' ')
436 ,nvl(max(decode(fue2.user_entity_name,'X_SSP', to_number(fai2.value))),0)
437 ,nvl(max(decode(fue2.user_entity_name,'X_SMP', to_number(fai2.value))),0)
438 -- Added the below 2 fields for P35/P14 EOY 2003/2004
439 ,nvl(max(decode(fue2.user_entity_name,'X_SPP_ADOPT', to_number(fai2.value))),0) -- for SPP
440 ,nvl(max(decode(fue2.user_entity_name,'X_SPP_BIRTH', to_number(fai2.value))),0) -- for SPP
441 ,nvl(max(decode(fue2.user_entity_name,'X_ASPP_ADOPT', to_number(fai2.value))),0) -- for ASPP EOY 2012/13
442 ,nvl(max(decode(fue2.user_entity_name,'X_ASPP_BIRTH', to_number(fai2.value))),0) -- for ASPP
443 ,nvl(max(decode(fue2.user_entity_name,'X_SAP', to_number(fai2.value))),0) -- for SAP
444 /*4011263: Gross Pay not needed anymore
445 ,nvl(max(decode(fue2.user_entity_name,'X_GROSS_PAY',to_number(fai2.VALUE))),0) gross_pay
446 */
447 --
448 ,decode(max(decode(fue2.user_entity_name,'X_TAX_REFUND',substr(fai2.VALUE,1,1))), 'R',
449 NVL(-1*max(decode(fue2.user_entity_name,'X_TAX_PAID',to_number(fai2.VALUE))),0),
450 NVL(max(decode(fue2.user_entity_name,'X_TAX_PAID',to_number(fai2.VALUE))),0)) tax_paid
451 --
452 ,nvl(max(decode(fue2.user_entity_name,'X_TAX_REFUND',
453 substr(UPPER(fai2.value),1,1))),' ')
454 ,nvl(max(decode(fue2.user_entity_name,'X_PREVIOUS_TAXABLE_PAY',
455 to_number(fai2.value))),0) previous_taxable
456 ,nvl(max(decode(fue2.user_entity_name,'X_PREVIOUS_TAX_PAID', to_number(fai2.value))),0)
457 ,nvl(max(decode(fue2.user_entity_name,'X_START_OF_EMP',
458 TO_CHAR(fnd_date.canonical_to_date(fai2.value),'DDMMYYYY'))),' ')
459 ,max(decode(fue2.user_entity_name,'X_TERMINATION_DATE',
460 TO_CHAR(fnd_date.canonical_to_date(fai2.value),'DDMMYYYY')))
461 ,nvl(max(decode(fue2.user_entity_name,'X_WIDOWS_AND_ORPHANS',
462 ROUND(to_number(fai2.value)/100))),0)
463 ,nvl(max(decode(fue2.user_entity_name,'X_STUDENT_LOANS', trunc(fai2.value/100))),0) student_loans
464 ,nvl(max(decode(fue2.user_entity_name,'X_WEEK_53_INDICATOR',
465 substr(UPPER(fai2.value),1,1))),' ')
466 ,nvl(max(decode(fue2.user_entity_name,'X_TAXABLE_PAY', to_number(fai2.value))),0) taxable_pay
467 /* 4011263
468 ,nvl(max(decode(fue2.user_entity_name,'X_PENSIONER_INDICATOR',
469 substr(UPPER(fai2.value),1,1))),' ')
470 4011263 */
471 ,nvl(max(decode(fue2.user_entity_name,'X_DIRECTOR_INDICATOR',
472 substr(UPPER(fai2.value),1,1))),' ')
473 ,act.assignment_id
474 ,max(decode(fue2.user_entity_name,'X_EFFECTIVE_END_DATE',
475 fnd_date.canonical_to_date(fai2.value)))
476 ,nvl(max(decode(fue2.user_entity_name,'X_ASSIGNMENT_MESSAGE',
477 SUBSTR(fai2.VALUE, 1,60))),' ')
478 ,nvl(max(decode(fue2.user_entity_name,'X_MULTIPLE_ASG_FLAG',
479 SUBSTR(fai2.VALUE, 1,1))),' ')
480 FROM
481 ff_archive_items fai1,
482 ff_user_entities fue1,
483 ff_archive_items fai2,
484 ff_user_entities fue2,
485 pay_assignment_actions act
486 WHERE act.assignment_action_id = fai1.context1
487 AND act.payroll_action_id = c_payroll_action_id
488 --AND act.action_status = 'C'
489 AND act.action_status in ('C','S') --Modified for the bug 10066755
490 AND fue1.legislation_code = 'GB'
491 AND fue1.user_entity_name = 'X_PAYROLL_ID'
492 AND fue1.business_group_id IS NULL
493 AND fue1.user_entity_id + decode(act.assignment_action_id,0,0,0) = fai1.user_entity_id
494 and fai1.value = to_char(c_payroll_id)
495 AND fue2.user_entity_id = fai2.user_entity_id
496 AND fai2.context1 = act.assignment_action_id
497 GROUP BY
498 act.assignment_action_id
499 , act.assignment_id
500 HAVING
501 (
502 nvl(max(decode(fue2.user_entity_name,'X_AGGREGATED_PAYE_FLAG', fai2.value)), 'N')='N'
503 OR (
504 nvl(max(decode(fue2.user_entity_name,'X_AGGREGATED_PAYE_FLAG', fai2.value)), 'N')='Y'
505 AND nvl(max(decode(fue2.user_entity_name,'X_EOY_PRIMARY_FLAG', fai2.value)), 'N')='Y'
506 )
507 )
508 AND
509 (
510 nvl(max(decode(fue2.user_entity_name, 'X_TAXABLE_PAY', to_number(fai2.value))),0) <> 0
511 OR NVL(max(decode(fue2.user_entity_name,'X_TAX_PAID',to_number(fai2.VALUE))),0) <> 0
512 OR nvl(max(decode(fue2.user_entity_name,'X_STUDENT_LOANS', trunc(fai2.value/100))),0) <> 0
513 OR nvl(max(decode(fue2.user_entity_name,'X_PREVIOUS_TAXABLE_PAY', to_number(fai2.value))),0) <> 0
514 OR nvl(max(decode(fue2.user_entity_name,'X_PREVIOUS_TAX_PAID', to_number(fai2.value))),0) <> 0
515 OR nvl(max(decode(fue2.user_entity_name,'X_SSP', to_number(fai2.value))),0) <> 0
516 OR nvl(max(decode(fue2.user_entity_name,'X_SMP', to_number(fai2.value))),0) <> 0
517 OR nvl(max(decode(fue2.user_entity_name,'X_SAP', to_number(fai2.value))),0) <> 0
518 OR nvl(max(decode(fue2.user_entity_name,'X_SPP_ADOPT', to_number(fai2.value))),0) <> 0
519 OR nvl(max(decode(fue2.user_entity_name,'X_SPP_BIRTH', to_number(fai2.value))),0) <> 0
520 OR nvl(max(decode(fue2.user_entity_name,'X_REPORTABLE_NI', fai2.value)),'N') <> 'N'
521 )
522 ORDER BY last_name, first_name;
523 --
524 /* EOY 2012/2013 changes */
525 CURSOR get_rollup_ni_cat(c_assignment_action_id NUMBER) IS
526 SELECT /*NVL(UPPER(a.scon),' ') scon
527 ,*/UPPER(a.ni_category_code) cat_code
528 FROM pay_gb_year_end_values_v a
529 WHERE a.assignment_action_id = c_assignment_action_id
530 AND a.reportable <> 'N'
531 AND NVL(trunc(a.ni_able_uel/100),0) > 0
532 AND NVL(a.employees_contributions,0) > 0
533 AND UPPER(a.ni_category_code) <> 'X'
534 AND UPPER(a.ni_category_code) <> 'C'
535 ORDER BY NVL(trunc(a.ni_able_uel/100),0), NVL(a.employees_contributions,0),
536 UPPER(a.ni_category_code)/*, NVL(UPPER(a.scon),' ')*/ DESC;
537 --
538 -- Cursor to find NI category with LEL and ET > 0 so that Ni Cats with LEL but
539 -- no other values can be rolledup into this
540 --
541 /* EOY 2012/2013 changes */
542 CURSOR get_lel_rollup_ni_cat(c_assignment_action_id NUMBER) IS
543 SELECT UPPER(a.ni_category_code) cat_code
544 FROM pay_gb_year_end_values_v a
545 WHERE a.assignment_action_id = c_assignment_action_id
546 AND a.reportable <> 'N'
547 AND NVL(trunc(a.ni_able_et/100),0) > 0
548 AND NVL(trunc(a.ni_able_lel/100),0) > 0
549 AND UPPER(a.ni_category_code) <> 'X'
550 ORDER BY NVL(trunc(a.ni_able_et/100),0),
551 NVL(trunc(a.ni_able_lel/100),0),
552 NVL(a.employees_contributions,0),
553 UPPER(a.ni_category_code)/*, NVL(UPPER(a.scon),' ')*/ DESC;
554 --
555 -- Cursor to get total of LEL for NI Cats with LEL > 0,
556 -- ET=0, UEL=0, ER Cont=0 and EE Cont=0
557 -- Total of these LELs will be rolled into first NI Cat
558 -- returned by above cursor get_lel_rollup_ni_cat
559 --
560 CURSOR get_only_lel_total(c_assignment_action_id NUMBER) IS
561 SELECT NVL(sum(trunc(a.ni_able_lel/100)),0) tot_ni_able_lel
562 FROM pay_gb_year_end_values_v a
563 WHERE a.assignment_action_id = c_assignment_action_id
564 AND a.reportable <> 'N'
565 AND UPPER(a.ni_category_code) <> 'X'
566 -- Check LEL > 0 but ET, UEL, EE and ER Conrib = 0
567 -- Bug#7043405 LEL Rollup condition reverted back
568 AND NVL(a.total_contributions,0) = 0 -- EOY 07/08 removed Total contribution from LEL roll up
569 AND NVL(trunc(a.ni_able_et/100),0) = 0
570 AND NVL(trunc(a.ni_able_uap/100),0) = 0 -- 8816832 EOY 09/10
571 AND NVL(trunc(a.ni_able_uel/100),0) = 0
572 -- Bug#7043405 LEL Rollup condition reverted back
573 AND NVL(a.employees_contributions,0) = 0 -- EOY 07/08 removed Employee contribution from LEL roll up
574 AND NVL(trunc(a.ni_able_lel/100),0) > 0;
575 --
576 -- ni able per threshold figures to be stored in pounds, so trunc the
577 -- pence value from the view after dividing by 100.
578 --
579 /* EOY 2012/2013 changes */
580 CURSOR emp_values(c_assignment_action_id NUMBER) IS
581 SELECT /*NVL(UPPER(a.scon),' ') scon
582 ,*/UPPER(a.ni_category_code) cat_code
583 ,NVL(a.total_contributions,0) tot_cont
584 ,NVL(a.employees_contributions,0) emps_cont
585 ,NVL(trunc(a.ni_able_et/100),0) ni_able_et
586 ,NVL(trunc(a.ni_able_lel/100),0) ni_able_lel
587 ,NVL(trunc(a.ni_able_uel/100),0) ni_able_uel
588 ,NVL(trunc(a.ni_able_uap/100),0) ni_able_uap -- 8357870
589 ,NVL(trunc(a.ni_able_auel/100),0) ni_able_auel --EOY 07/08 added AUEL for contributions rollup
590 ,NVL(a.employers_rebate,0) employers_rebate
591 ,NVL(a.employees_rebate,0) employees_rebate
592 FROM pay_gb_year_end_values_v a
593 WHERE a.assignment_action_id = c_assignment_action_id
594 AND a.reportable <> 'N'
595 -- Check atleast one value is non-zero to report
596 AND NOT (NVL(a.total_contributions,0) = 0
597 AND NVL(trunc(a.ni_able_et/100),0) = 0
598 AND NVL(trunc(a.ni_able_lel/100),0) = 0
599 AND NVL(trunc(a.ni_able_uap/100),0) = 0 -- 8816832 EOY 09/10
600 AND NVL(trunc(a.ni_able_uel/100),0) = 0
601 AND NVL(a.employees_contributions,0) = 0)
602 UNION -- Added union to fix 4216135
603 SELECT /*' ' EOY 2012/2013 remove '' to match no of cols
604 ,*/'X'
605 ,0
606 ,0
607 ,0
608 ,0 -- 8357870
609 ,0 --EOY 07/08
610 ,0
611 ,0
612 ,0
613 ,0
614 FROM dual
615 WHERE NOT EXISTS
616 (SELECT 1 FROM pay_gb_year_end_values_v b
617 WHERE b.assignment_action_id = c_assignment_action_id
618 AND b.reportable <> 'N'
619 AND NOT (NVL(b.total_contributions,0) = 0
620 AND NVL(trunc(b.ni_able_et/100),0) = 0
621 AND NVL(trunc(b.ni_able_lel/100),0) = 0
622 AND NVL(trunc(b.ni_able_uap/100),0) = 0 -- 8816832 EOY 09/10
623 AND NVL(trunc(b.ni_able_uel/100),0) = 0
624 AND NVL(b.employees_contributions,0) = 0))
625 /* Bug Fix 8816832 EOY 09/10 Included UAP also in order by
626 ORDER BY 6, 5, 7, 2, 1; -- order by clause added for 4234348 to ensure Ni Cats with 0 lel/et/uel are processed first */
627 /* ORDER BY 6, 5, 8, 7, 2, 1; -- order by clause added for 4234348 to ensure Ni Cats with 0 lel/et/uap/uel are processed first
628 Bug Fix 10188309, post to EOY 07/08 changes order by clause to ensure Ni Cats with 0 uap/uel are processed first */
629 ORDER BY 8, 7, 2, 1;
630 --
631 /* Start 4011263
632 CURSOR econ_chk(c_permit_no VARCHAR2
633 ,c_tax_dist_ref VARCHAR2
634 ,c_tax_ref_no VARCHAR2
635 ,c_payroll_action_id NUMBER) IS
636 SELECT 1
637 FROM ff_archive_item_contexts fac,
638 ff_archive_items fai,
639 ff_user_entities fue,
640 ff_archive_items fai2,
641 ff_user_entities fue2,
642 pay_assignment_actions paa
643 WHERE paa.payroll_action_id = c_payroll_action_id
644 AND fue.user_entity_name = 'X_NI_TOTAL_CONTRIBUTIONS'
645 AND fue.user_entity_id + decode(paa.assignment_action_id,0,0,0)
646 = fai.user_entity_id
647 AND fue.legislation_code = 'GB'
648 AND fai.context1 = paa.assignment_action_id
649 AND fai.archive_item_id = fac.archive_item_id
650 AND fac.sequence_no = 2
651 AND fac.context in ('D','E','L') --P35/P14 EOY 2003/2004
652 AND fue2.user_entity_name = 'X_PAYROLL_ID'
653 AND fue2.user_entity_id + decode(paa.assignment_action_id,0,0,0)
654 = fai2.user_entity_id
655 AND fue2.legislation_code = 'GB'
656 AND fai2.context1 = paa.assignment_action_id
657 AND decode (c_tax_dist_ref,NULL,1,
658 pay_gb_eoy_archive.get_arch_num(c_payroll_action_id,
659 'X_TAX_DISTRICT_REFERENCE',fai2.value),1,0) = 1
660 AND decode (c_tax_ref_no,NULL,1,
661 pay_gb_eoy_archive.get_arch_str(c_payroll_action_id,
662 'X_TAX_REFERENCE_NUMBER',fai2.value),1,0) = 1
663 AND decode (c_permit_no,NULL,1,
664 pay_gb_eoy_archive.get_arch_str(c_payroll_action_id,
665 'X_PERMIT_NUMBER',fai2.value),1,0) = 1;
666 End 4011263 */
667 ------------------------------------------------------------------------------------
668 -- PROCEDURE: submit_recon_report
669 -- DESCRIPTION: Submit year End Reconciliation Report
670 ------------------------------------------------------------------------------------
671 PROCEDURE submit_recon_report(p_payroll_action_id in number,
672 p_p35_req_id out nocopy varchar2) IS
673 --
674 l_printer fnd_concurrent_requests.printer%TYPE;
675 l_no_of_copies fnd_concurrent_requests.number_of_copies%TYPE;
676 l_dummy BOOLEAN := FALSE;
677 --
678 l_p35_id NUMBER := -1;
679 --
680 CURSOR get_print_options IS
681 SELECT printer, number_of_copies
682 FROM fnd_concurrent_requests
683 WHERE request_id = fnd_global.conc_request_id;
684 --
685 /******************************* Below line added to fix the bug 8541978.
686 It makes sure that even if the value for MAGTAPE_FILE_SAVE is set to 'Y',
687 process will not error out. ********************************************/
688 PRAGMA Autonomous_transaction;
689
690 BEGIN
691 -- Fix 4363883: Find and Set print options as entered on EOY process
692 OPEN get_print_options;
693 FETCH get_print_options INTO l_printer, l_no_of_copies;
694 CLOSE get_print_options;
695 -- Call P35 report.
696 --
697 l_dummy := fnd_request.set_print_options(printer => l_printer,
698 copies => l_no_of_copies);
699 l_p35_id := fnd_request.submit_request(application => 'PAY',
700 program => 'PAYRPP35',
701 argument1 => p_payroll_action_id);
702 hr_utility.trace('The p35 request ID is '||to_char(l_p35_id));
703 --
704 p_p35_req_id := to_char(l_p35_id);
705 --
706 -- this commit ensures that reconciliation report does run even when
707 -- the EOY process fails due to type 1 errors
708 commit;
709 EXCEPTION
710
711 WHEN OTHERS THEN
712 p_p35_req_id := to_char(l_p35_id);
713 --
714
715 END submit_recon_report;
716
717 ------------------------------------------------------------------------------------
718 -- PROCEDURE: submit_reports
719 -- DESCRIPTION: Submit the Multiple Asg Reports.
720 -- Called at the end of the magtape process.
721 ------------------------------------------------------------------------------------
722 PROCEDURE submit_reports(p_payroll_action_id in number,
723 p_eoy_mode in varchar2,
724 p_mar_req_id out nocopy varchar2) IS
725 --
726 l_printer fnd_concurrent_requests.printer%TYPE;
727 l_no_of_copies fnd_concurrent_requests.number_of_copies%TYPE;
728 l_dummy BOOLEAN := FALSE;
729 --
730 l_mar_id NUMBER := -1;
731 --
732 CURSOR get_print_options IS
733 SELECT printer, number_of_copies
734 FROM fnd_concurrent_requests
735 WHERE request_id = fnd_global.conc_request_id;
736 --
737 BEGIN
738 -- Fix 4363883: Find and Set print options as entered on EOY process
739 OPEN get_print_options;
740 FETCH get_print_options INTO l_printer, l_no_of_copies;
741 CLOSE get_print_options;
742 --
743 -- Call Multiple Assignments Report.
744 --
745 l_dummy := fnd_request.set_print_options(printer => l_printer,
746 copies => l_no_of_copies);
747 l_mar_id := fnd_request.submit_request(application => 'PAY',
748 program => 'PAYYEMAR',
749 argument1 => p_payroll_action_id);
750 hr_utility.trace('The mar request ID is '||to_char(l_mar_id));
751 --
752 --
753 -- Assign Out Params
754 --
755 p_mar_req_id := to_char(l_mar_id);
756 --
757
758
759 -- Added for nocopy fix
760 EXCEPTION
761
762 WHEN OTHERS THEN
763 p_mar_req_id := to_char(l_mar_id);
764 --
765
766 END submit_reports;
767 --
768 /* 8833756 begin - Added to get the count of assignments for a person
769 in the tax year, with the same tax district and tax reference details
770 and have overlapping effective dates.*/
771 FUNCTION get_assign_count(p_assignment_id NUMBER,
772 p_min_start_year_date DATE,
773 p_max_end_year_date DATE,
774 p_tax_dist_ref VARCHAR2,
775 p_tax_ref VARCHAR2) RETURN NUMBER IS
776 -- Get the number of assignments for the person in the tax year
777 p_assg_count NUMBER;
778 l_person_id per_all_assignments_f.person_id%type := 0;
779
780 cursor csr_pers_id(c_assignment_id per_all_assignments_f.assignment_id%type) IS
781 select person_id from per_all_assignments_f where
782 assignment_id=c_assignment_id order by effective_end_date DESC;
783
784 cursor csr_pers_assg_count (p_person_id per_all_assignments_f.person_id%type,
785 p_min_start_year_date DATE,
786 p_max_end_year_date DATE,
787 p_tax_dist_ref VARCHAR2,
788 p_tax_ref VARCHAR2) IS
789 select count(distinct master.assignment_id) from
790 ( SELECT /*+ ORDERED INDEX (asg PER_ASSIGNMENTS_F_N12,
791 ppf PAY_PAYROLLS_F_PK,
792 flex HR_SOFT_CODING_KEYFLEX_PK,
793 org HR_ORGANIZATION_INFORMATIO_FK1)
794 USE_NL(asg,ppf,flex,org) */
795 distinct asg.assignment_id, asg.effective_start_date, asg.effective_end_date
796 FROM per_all_assignments_f asg,
797 pay_all_payrolls_f ppf,
798 hr_soft_coding_keyflex flex,
799 hr_organization_information org
800 WHERE asg.person_id = p_person_id
801 AND asg.effective_end_date >= p_min_start_year_date
802 AND asg.effective_start_date <= p_max_end_year_date
803 AND asg.payroll_id = ppf.payroll_id
804 AND asg.period_of_service_id is not null
805 AND ppf.effective_end_date >= p_min_start_year_date
806 AND ppf.effective_start_date <= p_max_end_year_date
807 AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
808 AND asg.business_group_id +0 = org.organization_id
809 AND org.org_information_context =
810 'Tax Details References'||decode(flex.segment1,'','','')
811 AND org.org_information1 = flex.segment1
812 AND nvl(org.org_information10,'UK') = 'UK'
813 AND nvl(p_tax_dist_ref, substr(flex.segment1,1,3)) =
814 substr(flex.segment1,1,3)
815 AND nvl(p_tax_ref, substr(ltrim(substr(org_information1,4,11),'/') ,1,10))
816 = substr(ltrim(substr(org_information1,4,11),'/') ,1,10)
817 ) master,
818 ( SELECT /*+ ORDERED INDEX (asg PER_ASSIGNMENTS_F_N12,
819 ppf PAY_PAYROLLS_F_PK,
820 flex HR_SOFT_CODING_KEYFLEX_PK,
821 org HR_ORGANIZATION_INFORMATIO_FK1)
822 USE_NL(asg,ppf,flex,org) */
823 distinct asg.assignment_id, asg.effective_start_date, asg.effective_end_date
824 FROM per_all_assignments_f asg,
825 pay_all_payrolls_f ppf,
826 hr_soft_coding_keyflex flex,
827 hr_organization_information org
828 WHERE asg.person_id = p_person_id
829 AND asg.effective_end_date >= p_min_start_year_date
830 AND asg.effective_start_date <= p_max_end_year_date
831 AND asg.payroll_id = ppf.payroll_id
832 AND asg.period_of_service_id is not null
833 AND ppf.effective_end_date >= p_min_start_year_date
834 AND ppf.effective_start_date <= p_max_end_year_date
835 AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
836 AND asg.business_group_id +0 = org.organization_id
837 AND org.org_information_context =
838 'Tax Details References'||decode(flex.segment1,'','','')
839 AND org.org_information1 = flex.segment1
840 AND nvl(org.org_information10,'UK') = 'UK'
841 AND nvl(p_tax_dist_ref, substr(flex.segment1,1,3)) =
842 substr(flex.segment1,1,3)
843 AND nvl(p_tax_ref, substr(ltrim(substr(org_information1,4,11),'/') ,1,10))
844 = substr(ltrim(substr(org_information1,4,11),'/') ,1,10)
845 ) child
846 where (master.effective_start_date between child.effective_start_date and child.effective_end_date
847 or
848 master.effective_end_date between child.effective_start_date and child.effective_end_date)
849 and master.assignment_id <> child.assignment_id;
850 BEGIN
851 OPEN csr_pers_id(p_assignment_id);
852 FETCH csr_pers_id into l_person_id;
853 CLOSE csr_pers_id;
854 OPEN csr_pers_assg_count(l_person_id ,
855 p_min_start_year_date ,
856 p_max_end_year_date ,
857 p_tax_dist_ref ,
858 p_tax_ref );
859 FETCH csr_pers_assg_count INTO p_assg_count;
860 CLOSE csr_pers_assg_count;
861 RETURN p_assg_count;
862 END;
863 /* 8833756 End */
864 --
865 FUNCTION get_formula_id(p_formula_name VARCHAR2) RETURN INTEGER IS
866 -- Get the formula id from the formula name
867 p_formula_id INTEGER;
868 CURSOR form IS
869 SELECT a.formula_id
870 FROM ff_formulas_f a,
871 ff_formula_types t
872 WHERE a.formula_name = p_formula_name
873 AND a.formula_type_id = t.formula_type_id
874 AND t.formula_type_name = 'Oracle Payroll';
875 BEGIN
876 OPEN form;
877 FETCH form INTO p_formula_id;
878 CLOSE form;
879 RETURN p_formula_id;
880 END;
881 --
882 PROCEDURE get_edi_sender_id(p_payroll_action_id IN NUMBER) IS
883 -- Get the EDI sender id from hr_organization_information
884 l_edi_sender_id VARCHAR2(35) := ' ';
885 CURSOR sender_id_cur IS
886 SELECT upper(nvl(org_information11,' ')) edi_sender_id,
887 pact.request_id
888 FROM pay_payroll_actions pact,
889 hr_organization_information hoi
890 WHERE pact.payroll_action_id = p_payroll_action_id
891 AND hoi.org_information_context = 'Tax Details References'
892 AND hoi.org_information1 = g_tax_district_ref||'/'||g_tax_ref_no
893 AND hoi.organization_id = pact.business_group_id;
894 BEGIN
895 OPEN sender_id_cur;
896 FETCH sender_id_cur INTO g_edi_sender_id, g_request_id;
897 CLOSE sender_id_cur;
898 END get_edi_sender_id;
899 --
900 -- Bug 2696015: Added for P14 EDI 2003 Enhancement
901
902 /* Start 4011263
903 PROCEDURE get_edi_submitter_no(p_payroll_action_id IN NUMBER) IS
904 -- Get the EDI sender id from hr_organization_information
905 edi_submitter_no VARCHAR2(10) := ' ';
906 CURSOR cur_sumbmitter_no IS
907 SELECT nvl(org_information13,' ') edi_submitter_no
908 FROM pay_payroll_actions pact,
909 hr_organization_information hoi
910 WHERE pact.payroll_action_id = p_payroll_action_id
911 AND hoi.org_information_context = 'Tax Details References'
912 AND hoi.org_information1 = g_tax_district_ref||'/'||g_tax_ref_no
913 AND hoi.organization_id = pact.business_group_id;
914 BEGIN
915 OPEN cur_sumbmitter_no;
916 FETCH cur_sumbmitter_no INTO g_edi_submitter_no;
917 CLOSE cur_sumbmitter_no;
918 END get_edi_submitter_no;
919 End 4011263 */
920 --
921
922
923 FUNCTION check_number(p_check_digit CHAR) RETURN BOOLEAN IS
924 BEGIN
925 IF p_check_digit BETWEEN '0' AND '9' THEN
926 RETURN TRUE;
927 ELSE
928 RETURN FALSE;
929 END IF;
930 END;
931 --
932 FUNCTION check_char(p_check_digit CHAR) RETURN BOOLEAN IS
933 BEGIN
934 IF p_check_digit BETWEEN 'A' AND 'Z' THEN
935 RETURN TRUE;
936 ELSE
937 RETURN FALSE;
938 END IF;
939 END;
940 --
941 FUNCTION check_special_char(p_check_digit CHAR) RETURN BOOLEAN IS
942 BEGIN
943 IF p_check_digit BETWEEN 'A' AND 'Z'
944 OR p_check_digit in ('''', '-', '.') THEN
945 RETURN TRUE;
946 ELSE
947 RETURN FALSE;
948 END IF;
949 END;
950 --
951 PROCEDURE mag_tape_init(p_no NUMBER) IS
952 -- The initialization of the record type formulae
953 -- and number of parameters
954 BEGIN
955 /* Reserved parameter names */
956 pay_mag_tape.internal_prm_names(1) := 'NO_OF_PARAMETERS';
957 pay_mag_tape.internal_prm_names(2) := 'NEW_FORMULA_ID';
958 pay_mag_tape.internal_prm_names(3) := 'TRANSFER_TYPE1_ERRORS';
959 pay_mag_tape.internal_prm_names(4) := 'TRANSFER_TYPE2_ERRORS';
960 pay_mag_tape.internal_prm_names(5) := 'TRANSFER_CHAR_ERRORS';
961 IF p_no = 1 THEN
962 /* Record type 1 */
963 pay_mag_tape.internal_prm_values(1) := 15;
964 pay_mag_tape.internal_prm_values(2) := get_formula_id('MAG_RECORD1');
965 ELSIF p_no = 2 THEN
966 /* Record type 2 */
967 pay_mag_tape.internal_prm_values(1) := 69;
968 pay_mag_tape.internal_prm_values(2) := get_formula_id('MAG_RECORD2');
969 /* Reset the record index to start at the third parameter */
970 ELSIF p_no = 3 THEN
971 /* Sub-header */
972 -- hr_utility.trace('record index is '||to_char(g_record_index));
973 pay_mag_tape.internal_prm_values(1) := 7;
974 pay_mag_tape.internal_prm_values(2) := get_formula_id('MAG_RECORD3');
975 ELSIF p_no = 4 THEN
976 /* Permit total */
977 -- hr_utility.trace('record index is '||to_char(g_record_index));
978 pay_mag_tape.internal_prm_values(1) := 21; -- Incremented as P35/P14 EOY 2003/2004
979 pay_mag_tape.internal_prm_values(2) := get_formula_id('MAG_RECORD4');
980 ELSIF p_no = 5 THEN
981 /* End of record */
982 -- hr_utility.trace('record index is '||to_char(g_record_index));
983 pay_mag_tape.internal_prm_values(1) := 12;
984 pay_mag_tape.internal_prm_values(2) := get_formula_id('MAG_RECORD5');
985 ELSIF p_no = 6 THEN
986 /* Dummy record */
987 pay_mag_tape.internal_prm_values(1) := 3;
988 pay_mag_tape.internal_prm_values(2) := get_formula_id('MAG_RECORD6');
989 ELSIF p_no = 7 THEN
990 pay_mag_tape.internal_prm_values(1) := 6;
991 pay_mag_tape.internal_prm_values(2) := get_formula_id('MAG_RECORD7');
992 END IF;
993 -- Set parameter count to start at transfer_char_errors
994 g_record_index := 6;
995 END;
996 --
997 PROCEDURE p14_edi_init(p_no NUMBER) IS
998 -- The initialization of the P14 EDI record type formulae
999 -- and number of parameters
1000 BEGIN
1001 -- 8357870 begin - interim solution
1002 /* Commented for EOY 2011/12. Bug 12694562.
1003 open C_NI_NEW_TAX_YEAR;
1004 fetch C_NI_NEW_TAX_YEAR into l_ni_tax_year;
1005 close C_NI_NEW_TAX_YEAR;*/
1006 --8357870 end
1007
1008 -- Reserved parameter names
1009 pay_mag_tape.internal_prm_names(1) := 'NO_OF_PARAMETERS';
1010 pay_mag_tape.internal_prm_names(2) := 'NEW_FORMULA_ID';
1011 pay_mag_tape.internal_prm_names(3) := 'TRANSFER_TYPE1_ERRORS';
1012 pay_mag_tape.internal_prm_names(4) := 'TRANSFER_TYPE2_ERRORS';
1013 pay_mag_tape.internal_prm_names(5) := 'TRANSFER_CHAR_ERRORS';
1014 IF p_no = 1 THEN
1015 -- Permit Header
1016 -- pay_mag_tape.internal_prm_values(1) := 15; -- Changed for 4752018
1017 /* Removed 'TAX_YEAR' input as part of 8833756
1018 pay_mag_tape.internal_prm_values(1) := 16; -- EOY 09/10 8816832*/
1019 -- EOY Change 11/12 Included TAX_YEAR Parameter.
1020 -- pay_mag_tape.internal_prm_values(1) := 15;
1021 pay_mag_tape.internal_prm_values(1) := 16;
1022 pay_mag_tape.internal_prm_values(2) := get_formula_id('PAY_GB_EDI_P14_PERMIT_HEADER');
1023 ELSIF p_no = 2 THEN
1024 -- Employee Header
1025 pay_mag_tape.internal_prm_values(1) := 22; -- Changed for 4752018
1026 pay_mag_tape.internal_prm_values(2) := get_formula_id('PAY_GB_EDI_P14_EMP_HEADER');
1027 ELSIF p_no = 3 THEN
1028 -- Employee NI details
1029 --pay_mag_tape.internal_prm_values(1) := 22; -- Changed for EOY 2006/7
1030 --pay_mag_tape.internal_prm_values(1) := 23; -- Added one more parameter for EOY 07/08
1031 /* 8357870 begin conditionally call formula. Added PAY_GB_EDI_P14_NI_DETAILS_INTERIM
1032 formula to contain new validations.
1033 8816832 EOY 09/10 validation in PAY_GB_EDI_P14_NI_DETAILS &
1034 EOY 08/09 validation in PAY_GB_EDI_P14_NI_DETAILS_INTERIM
1035 8833756 EOY 09/10 changes
1036 */
1037 --pay_mag_tape.internal_prm_values(1) := 24; -- Added one more parameter for 8816832 EOY 09/10
1038 pay_mag_tape.internal_prm_values(1) := 24; -- Added Tax Year, NI_NEW_TAX_YEAR input parameter. EOY 2011/12. Bug 12694562.
1039 pay_mag_tape.internal_prm_values(2) := get_formula_id('PAY_GB_EDI_P14_NI_DETAILS');
1040 --8816832 end
1041 ELSIF p_no = 4 THEN
1042 -- Employee Trailer
1043 --pay_mag_tape.internal_prm_values(1) := 31; -- Changed for EOY 2006/7
1044 -- pay_mag_tape.internal_prm_values(1) := 32; -- Changed for 6281170
1045 -- EOY 2012/13
1046 -- pay_mag_tape.internal_prm_values(1) := 34; -- 8816832 EOY 09/10
1047 pay_mag_tape.internal_prm_values(1) := 37; -- Added Tax Year, NI_NEW_TAX_YEAR input parameter. EOY 2011/12. Bug 12694562.
1048 pay_mag_tape.internal_prm_values(2) := get_formula_id('PAY_GB_EDI_P14_EMP_TRAILER');
1049 ELSIF p_no = 5 THEN
1050 -- Permit Trailer
1051 pay_mag_tape.internal_prm_values(1) := 19; -- Added Tax Year, NI_NEW_TAX_YEAR input parameter. EOY 2011/12. Bug 12694562.
1052 pay_mag_tape.internal_prm_values(2) := get_formula_id('PAY_GB_EDI_P14_PERMIT_TRAILER');
1053 ELSIF p_no = 6 THEN
1054 -- File Trailer
1055 pay_mag_tape.internal_prm_values(1) := 11;
1056 pay_mag_tape.internal_prm_values(2) := get_formula_id('PAY_GB_EDI_P14_FILE_TRAILER');
1057 ELSIF p_no = 7 THEN
1058 -- Dummy EDI record
1059 pay_mag_tape.internal_prm_values(1) := 3;
1060 pay_mag_tape.internal_prm_values(2) := get_formula_id('PAY_GB_EDI_P14_DUMMY');
1061 END IF;
1062 -- Set parameter count to start at transfer_char_errors
1063 g_record_index := 6;
1064 END;
1065 PROCEDURE mag_tape_interface(p_name VARCHAR2
1066 ,p_values VARCHAR2) IS
1067 /* The interface to the magnetic tape writer process */
1068 BEGIN
1069 pay_mag_tape.internal_prm_names(g_record_index) := p_name;
1070 pay_mag_tape.internal_prm_values(g_record_index) := p_values;
1071 /* Inc the parameter table index */
1072 g_record_index := g_record_index +1;
1073 END;
1074 --
1075 PROCEDURE mag_tape_interface(p_name VARCHAR2
1076 ,p_values NUMBER) IS
1077 /* The interface to the magnetic tape writer process */
1078 BEGIN
1079 pay_mag_tape.internal_prm_names(g_record_index) := p_name;
1080 pay_mag_tape.internal_prm_values(g_record_index) := p_values;
1081 g_record_index := g_record_index +1;
1082 END;
1083 --
1084 PROCEDURE p_mag_form_clear(l_tab_index NUMBER) IS
1085 /* This procedure will clear the NIx to NI4 records for the
1086 employee. This will stop any earlier records appearing in
1087 later records. */
1088 BEGIN
1089 FOR l_index IN l_tab_index..4 LOOP
1090 -- mag_tape_interface('SCON'||TO_CHAR(l_index) ,' '); --EOY 2012/2013
1091 mag_tape_interface('NI_CATEGORY_CODE'||
1092 TO_CHAR(l_index),' ');
1093 mag_tape_interface('TOTAL_CONTRIBUTIONS'||l_index,'0');
1094 mag_tape_interface('EMPLOYEES_CONTRIBUTIONS'|| TO_CHAR(l_index),'0');
1095 mag_tape_interface('NI_ABLE_ET'|| TO_CHAR(l_index),'0');
1096 mag_tape_interface('NI_ABLE_LEL'|| TO_CHAR(l_index),'0');
1097 mag_tape_interface('NI_ABLE_UEL'|| TO_CHAR(l_index),'0');
1098 END LOOP;
1099 END;
1100 --
1101 PROCEDURE create_record_type1 IS
1102 l_index NUMBER :=0;
1103 l_result VARCHAR2(1);
1104 l_tax_dist_ref NUMBER :=0; --Bug Fix 9414865
1105 -- 4011263: l_econ_required VARCHAR2(1) := '0';
1106 BEGIN
1107 -- Now start validating the record type 1
1108 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',600);
1109 -- Initialise the record type 1 parameters
1110 hr_utility.trace('Writing record type 1');
1111 IF g_eoy_mode in ('F - P14 EDI', 'P - P14 EDI') THEN
1112 p14_edi_init(1);
1113 ELSE
1114 mag_tape_init(1);
1115 END IF;
1116 -- Pass the record fields as paramteres to the mag tape process
1117 hr_utility.trace('Record type1 passed eoy_mode '||g_eoy_mode);
1118 hr_utility.trace('no params: '||pay_mag_tape.internal_prm_values(1));
1119 hr_utility.trace('formula id: '||pay_mag_tape.internal_prm_values(2));
1120 hr_utility.trace('type1 errors: '||pay_mag_tape.internal_prm_values(3));
1121 hr_utility.trace('type2 errors: '||pay_mag_tape.internal_prm_values(4));
1122 hr_utility.trace('char errors: '||pay_mag_tape.internal_prm_values(5));
1123 hr_utility.trace('permit: '||g_new_permit_no);
1124 hr_utility.trace('tax distr ref: '||g_tax_district_ref);
1125 hr_utility.trace('tax refno: '||g_tax_ref_no);
1126 -- 4011263: hr_utility.trace('tax dist name: '||g_tax_district_name);
1127 hr_utility.trace('tax yr: '||g_tax_year);
1128 hr_utility.trace('emp name: '||g_employers_name);
1129 -- 4752018: hr_utility.trace('emp add: '||g_employers_address);
1130 -- 4011263: hr_utility.trace('econ: '||g_econ);
1131 -- 4011263: hr_utility.trace('econ reqd: '||l_econ_required);
1132 mag_tape_interface('EOY_MODE',g_eoy_mode);
1133 mag_tape_interface('PERMIT_NO',NVL(g_new_permit_no,' '));
1134 --
1135 /* Field must be three numeric characters */
1136 /* An invalid or missing char will be passed as a blank space*/
1137 /* which will cause an error to be raised in magtape formula*/
1138 BEGIN
1139 l_tax_dist_ref := TO_NUMBER(g_tax_district_ref); -- Bug Fix 9414865
1140 /* g_tax_district_ref may have leading zeroes */
1141 --g_tax_district_ref := TO_NUMBER(g_tax_district_ref);
1142 EXCEPTION
1143 WHEN VALUE_ERROR THEN
1144 -- Any non-numeric characters will raise an exception
1145 g_tax_district_ref := ' ';
1146 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',610);
1147 END;
1148 mag_tape_interface('TAX_DISTRICT_REF' ,NVL(g_tax_district_ref,' '));
1149 mag_tape_interface('TAX_REF_NO',nvl(g_tax_ref_no,' '));
1150 --
1151 -- 4011263: mag_tape_interface('TAX_DISTRICT_NAME',nvl(g_tax_district_name,' '));
1152 -- 4752018: mag_tape_interface('TAX_YEAR',g_tax_year);
1153 mag_tape_interface('EMPLOYERS_NAME',NVL(g_employers_name,' '));
1154 -- 4752018: mag_tape_interface('EMPLOYERS_ADDRESS',NVL(g_employers_address,' '));
1155 --
1156 /* Start 4011263
1157 -- Check whether the ECON is required, and whether the Global ECON
1158 -- is NULL. If it is required and is null, the formula gives a
1159 -- specific error. All format validation is initiated by the formula.
1160 --
1161 IF NOT(econ_chk%ISOPEN) THEN
1162 OPEN econ_chk(g_permit_no
1163 ,g_tax_dist_ref
1164 ,g_tax_ref_no
1165 ,g_payroll_action_id);
1166 END IF;
1167 --
1168 FETCH econ_chk INTO l_result; -- NB l_result will be the payroll ID.
1169 --
1170 IF g_econ = '?' THEN
1171 -- If NVL forced a ? then overwrite to a space
1172 g_econ := ' ';
1173 END IF;
1174 --
1175 IF l_result IS NULL THEN
1176 --
1177 -- No econ is needed as no match on the above parameters to
1178 -- the cursor. Set ECON_REQUIRED to 0.
1179 --
1180 l_econ_required := '0';
1181 ELSE
1182 -- Econ should be present
1183 l_econ_required := '1';
1184 --
1185 END IF;
1186 mag_tape_interface('ECON',g_econ);
1187 mag_tape_interface('ECON_REQUIRED',l_econ_required);
1188 ENd 4011263 */
1189 IF g_eoy_mode in ('F - P14 EDI', 'P - P14 EDI') THEN
1190
1191 mag_tape_interface('TEST_INDICATOR', g_test_indicator);
1192 --mag_tape_interface('URGENT_MARKER', g_urgent_marker); 4011263
1193 mag_tape_interface('EDI_SENDER_ID', nvl(g_edi_sender_id,' '));
1194 mag_tape_interface('UNIQUE_ID', substr(g_new_payroll_id||g_request_id,1,14));
1195 -- 4011263: Add Unique Test Id
1196 mag_tape_interface('UNIQUE_TEST_ID', g_unique_test_id);
1197 mag_tape_interface('RETURN_TYPE', g_return_type);
1198 mag_tape_interface('TAX_YEAR', g_tax_year); -- EOY 11/12
1199 /* Removed 'TAX_YEAR' input for 8833756
1200 mag_tape_interface('TAX_YEAR', g_tax_year); -- EOY 09/10 8816832 */
1201 /* Start 4011263
1202 -- Bug 2696015: Added for P14 EDI Enhancement 2003
1203 mag_tape_interface('SUBMITTER_NO', nvl(g_edi_submitter_no,' '));
1204 End 4011263 */
1205 END IF;
1206 END create_record_type1;
1207 --
1208 PROCEDURE create_sub_header IS
1209 BEGIN
1210 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',500);
1211 IF g_eoy_mode in ('F - P14 EDI', 'P - P14 EDI') THEN
1212 -- EDI process does not need sub header therefore call dummy formula to skip this step
1213 p14_edi_init(7);
1214 ELSE
1215 hr_utility.trace('Writing record type 2 subheader');
1216 mag_tape_init(3);
1217 mag_tape_interface('EOY_MODE',g_eoy_mode);
1218 mag_tape_interface('SUB_TOTAL','SUBTOTAL');
1219 END IF;
1220 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',510);
1221 END;
1222 --
1223 PROCEDURE create_record_type3 IS
1224 --
1225 -- Create the Type 3 Magtape record (Grand Total Record), and reset
1226 -- all Permit-level global totals.
1227 --
1228 l_tot_refund VARCHAR2(1) :=NULL; -- Set to 'R' if tax refund
1229 --l_total_nic_rebate NUMBER(11) := 0; --P35/P14 EOY 2003/2004
1230 --
1231 BEGIN
1232 hr_utility.trace('Writing record type 3');
1233 IF g_eoy_mode in ('F - P14 EDI', 'P - P14 EDI') THEN
1234 p14_edi_init(5);
1235 ELSE
1236 mag_tape_init(4);
1237 END IF;
1238 mag_tape_interface('EOY_MODE',g_eoy_mode);
1239 mag_tape_interface('PERMIT_NO',g_permit_no); -- For inclusion in Error Messages
1240 mag_tape_interface('TOTAL_CONTRIBUTIONS',NVL(g_tot_contribs,0));
1241 g_tot_contribs := 0;
1242 hr_utility.trace('The tot tax is '||to_char(g_tot_tax));
1243 mag_tape_interface('TOTAL_TAX',NVL(ABS(g_tot_tax),0));
1244 IF SIGN(g_tot_tax) = -1 THEN
1245 -- The tax is a refund so set the refund status
1246 l_tot_refund := 'R';
1247 ELSE
1248 l_tot_refund := ' ';
1249 END IF;
1250 hr_utility.trace('The tot refund is '||l_tot_refund||'.');
1251 mag_tape_interface('TOTAL_TAX_REFUND',l_tot_refund);
1252 g_tot_tax := 0;
1253 mag_tape_interface('TOTAL_RECORDS',NVL(g_tot_rec2_per,0));
1254 -- Now add to the total record 2 count
1255 g_tot_rec2 := g_tot_rec2 + NVL(g_tot_rec2_per,0);
1256 hr_utility.trace('The per record is '||to_char(g_tot_rec2_per));
1257 hr_utility.trace('The current grand tot is '||to_char(g_tot_rec2));
1258 g_tot_rec2_per := 0;
1259 mag_tape_interface('TOTAL_SSP',NVL(g_tot_ssp_rec,0));
1260 -- Copy across new values to the variables
1261 -- g_tot_ssp_rec := g_ssp_recovery;
1262 g_tot_ssp_rec := 0;
1263 mag_tape_interface('TOTAL_SMP',NVL(g_tot_smp_rec,0));
1264 -- g_tot_smp_rec := g_smp_recovery;
1265 g_tot_smp_rec := 0;
1266 /* Start 4011263
1267 mag_tape_interface('TOTAL_SMP_COMP',NVL(g_tot_smp_comp,0));
1268 -- g_tot_smp_comp := g_smp_compensation;
1269 g_tot_smp_comp := 0;
1270 -- l_total_nic_rebate := g_tot_ers_rebate + g_tot_ees_rebate; --P35/P14 EOY 2003/2004
1271 mag_tape_interface('TOTAL_SPP_COMP',NVL(g_tot_spp_comp,0));
1272 g_tot_spp_comp := 0;
1273 End 4011263 */
1274 mag_tape_interface('TOTAL_SPP_REC',NVL(g_tot_spp_rec,0));
1275 g_tot_spp_rec := 0;
1276 /* EOY 2012/13 for bug : 12329727 */
1277 mag_tape_interface('TOTAL_ASPP_REC',NVL(g_tot_aspp_rec,0));
1278 g_tot_aspp_rec := 0;
1279 /* Start 4011263
1280 mag_tape_interface('TOTAL_SAP_COMP',NVL(g_tot_sap_comp,0));
1281 g_tot_sap_comp := 0;
1282 End 4011263 */
1283 mag_tape_interface('TOTAL_SAP_REC',NVL(g_tot_sap_rec,0));
1284 g_tot_sap_rec := 0;
1285 -- mag_tape_interface('TOTAL_NIC_REBATE', nvl(l_total_nic_rebate,0)); --P35/P14 EOY 2003/2004
1286 -- g_tot_ers_rebate := 0; --P35/P14 EOY 2003/2004
1287 -- g_tot_ees_rebate := 0; --P35/P14 EOY 2003/2004
1288 mag_tape_interface('TOTAL_STUDENT_LOANS',nvl(g_tot_student_ln,0));
1289 g_tot_student_ln := 0;
1290 mag_tape_interface('TAX_YEAR',g_tax_year);
1291 mag_tape_interface('NI_LATEST_TAX_YEAR', l_ni_tax_year);
1292 END;
1293 --
1294 PROCEDURE p_create_dummy(l_tab_index NUMBER
1295 ,l_no_nis NUMBER) IS
1296 --
1297 l_local_date DATE; -- Used to hold a converted char
1298 -- l_ers_rebate NUMBER(9); --P35/P14 EOY 2003/2004
1299 -- l_ees_rebate NUMBER(9); --P35/P14 EOY 2003/2004
1300 l_param_index NUMBER(1);
1301 --
1302 BEGIN
1303 /* Now create a dummy record type 2 */
1304 /* This is for the extra NI details for an employee */
1305 mag_tape_init(2);
1306 mag_tape_interface('EOY_MODE',g_eoy_mode);
1307 mag_tape_interface('EMPLOYEE_NUMBER',NVL(g_employee_number,' '));
1308 hr_utility.trace('The employee is '||g_employee_number);
1309 --
1310 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',530);
1311 --
1312 -- Note all name validation performed in formula.
1313 --
1314 mag_tape_interface('LAST_NAME',NVL(g_last_name,' '));
1315 mag_tape_interface('FIRST_NAME',NVL(g_first_name,' '));
1316 mag_tape_interface('MIDDLE_NAME',NVL(g_middle_name,' '));
1317 mag_tape_interface('DATE_OF_BIRTH',g_date_of_birth);
1318 mag_tape_interface('GENDER',g_sex);
1319 mag_tape_interface('ADDRESS_LINE1',g_address_line1);
1320 mag_tape_interface('ADDRESS_LINE2',g_address_line2);
1321 mag_tape_interface('ADDRESS_LINE3',g_address_line3);
1322 mag_tape_interface('TOWN_OR_CITY',g_town_or_city);
1323 mag_tape_interface('COUNTRY',g_country); -- 4011263
1324 mag_tape_interface('POSTAL_CODE',g_postal_code);
1325 /**************************************/
1326 /* Put blank space into tax code field*/
1327 /**************************************/
1328 mag_tape_interface('TAX_CODE',' ');
1329 mag_tape_interface('W1_M1',' ');
1330 mag_tape_interface('NI_NO',g_national_insurance_number);
1331 --
1332 -- Send the first record from the pl/sql tables to the mag tape
1333 --
1334 --mag_tape_interface('SCON1',scon_tab(l_tab_index + 1)); --EOY 2012/2013
1335 mag_tape_interface('NI_CATEGORY_CODE1',category_tab(l_tab_index + 1));
1336 mag_tape_interface('TOTAL_CONTRIBUTIONS1',total_contrib_tab(l_tab_index+1));
1337 mag_tape_interface('EMPLOYEES_CONTRIBUTIONS1',
1338 employees_contrib_tab(l_tab_index+1));
1339 mag_tape_interface('NI_ABLE_ET1', ni_able_et_tab(l_tab_index+1));
1340 mag_tape_interface('NI_ABLE_LEL1', ni_able_lel_tab(l_tab_index+1));
1341 mag_tape_interface('NI_ABLE_UEL1', ni_able_uel_tab(l_tab_index+1));
1342 -- l_ers_rebate := employers_rebate_tab(l_tab_index+1); --P35/P14 EOY 2003/2004
1343 -- l_ees_rebate := employees_rebate_tab(l_tab_index+1); --P35/P14 EOY 2003/2004
1344 mag_tape_interface('SSP','0');
1345 mag_tape_interface('SMP','0');
1346 mag_tape_interface('SPP','0'); --P35/P14 EOY 2003/2004
1347 mag_tape_interface('SAP','0'); --P35/P14 EOY 2003/2004
1348 -- 4011263: mag_tape_interface('GROSS_PAY','0');
1349 mag_tape_interface('TAX_PAID','0');
1350 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',560);
1351 mag_tape_interface('TAX_REFUND',' ');
1352 mag_tape_interface('PREVIOUS_TAXABLE_PAY','0');
1353 --
1354 mag_tape_interface('PREVIOUS_TAX_PAID','0');
1355 --
1356 mag_tape_interface('DATE_OF_STARTING',g_start_of_emp);
1357 BEGIN
1358 IF g_termination_date IS NOT NULL THEN
1359 l_local_date := TO_DATE(g_termination_date,'DDMMYYYY');
1360 END IF;
1361 EXCEPTION
1362 WHEN value_error THEN
1363 g_termination_date := ' ';
1364 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',570);
1365 END;
1366 mag_tape_interface('TERMINATION_DATE',NVL(g_termination_date,' '));
1367 /* Start 4011263
1368 mag_tape_interface('SUPERANNUATION','0');
1369 --
1370 mag_tape_interface('SUPERANNUATION_REFUND',' ');
1371 End 4011263 */
1372 mag_tape_interface('WIDOWS_ORPHANS','0');
1373 --
1374 mag_tape_interface('STUDENT_LOANS','0');
1375 mag_tape_interface('TAX_CREDITS','0');
1376 --
1377 mag_tape_interface('WEEK_53',' ');
1378 mag_tape_interface('TAXABLE_PAY','0');
1379 --
1380 /* 4011263
1381 mag_tape_interface('PENSIONER_INDICATOR',' ');
1382 mag_tape_interface('DIRECTOR_INDICATOR',' ');
1383 4011263 */
1384 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',580);
1385 --
1386 --
1387 hr_utility.trace('Start is '||to_char(l_tab_index+2));
1388 hr_utility.trace('End is '||to_char(l_no_nis));
1389 l_param_index := 2;
1390 FOR l_index IN l_tab_index+2..l_tab_index+l_no_nis LOOP
1391 hr_utility.trace('Index is now '||to_char(l_index));
1392 --mag_tape_interface('SCON'||TO_CHAR(l_param_index),scon_tab(l_index)); --EOY 2012/2013
1393 mag_tape_interface('NI_CATEGORY_CODE'||
1394 TO_CHAR(l_param_index),category_tab(l_index));
1395 mag_tape_interface('TOTAL_CONTRIBUTIONS'||TO_CHAR(l_param_index)
1396 ,total_contrib_tab(l_index));
1397 mag_tape_interface('EMPLOYEES_CONTRIBUTIONS'||
1398 TO_CHAR(l_param_index),employees_contrib_tab(l_index));
1399 mag_tape_interface('NI_ABLE_ET'|| TO_CHAR(l_param_index),
1400 ni_able_et_tab(l_index));
1401 mag_tape_interface('NI_ABLE_LEL'|| TO_CHAR(l_param_index),
1402 ni_able_lel_tab(l_index));
1403 mag_tape_interface('NI_ABLE_UEL'|| TO_CHAR(l_param_index),
1404 ni_able_uel_tab(l_index));
1405 -- l_ers_rebate := l_ers_rebate + employers_rebate_tab(l_index); --P35/P14 EOY 2003/2004
1406 -- l_ees_rebate := l_ees_rebate + employees_rebate_tab(l_index); --P35/P14 EOY 2003/2004
1407 l_param_index := l_param_index + 1;
1408 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',590);
1409 END LOOP;
1410 p_mag_form_clear(l_param_index);
1411 -- mag_tape_interface('NI_ERS_REBATE',l_ers_rebate); --P35/P14 EOY 2003/2004
1412 -- mag_tape_interface('NIEES_REBATE',l_ees_rebate); --P35/P14 EOY 2003/2004
1413 mag_tape_interface('ASSIGNMENT_MESSAGE', ' ');
1414 --
1415 -- g_tot_ers_rebate := g_tot_ers_rebate + l_ers_rebate; --P35/P14 EOY 2003/2004
1416 -- g_tot_ees_rebate := g_tot_ees_rebate + l_ees_rebate; --P35/P14 EOY 2003/2004
1417 --
1418 -- Running count of all employee records
1419 --
1420 g_tot_rec2_per := g_tot_rec2_per + 1;
1421 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',595);
1422 END;
1423 -----------------------------------------------------------------------------
1424 -- PROCEDURE: get_parameters
1425 -- DESCRIPTION: This procedure obtains all parameter values passed into this
1426 -- process. The values are selected from an outside plsql table,
1427 -- the positions of each parameter in that table is unknown
1428 -- hence a loop is used.
1429 -----------------------------------------------------------------------------
1430 PROCEDURE get_parameters(p_permit_no IN OUT nocopy VARCHAR2
1431 ,p_eoy_mode IN OUT nocopy VARCHAR2
1432 ,p_tax_dist_ref IN OUT nocopy VARCHAR2
1433 ,p_tax_ref_no IN OUT nocopy VARCHAR2
1434 ,p_test_indicator IN OUT nocopy VARCHAR2
1435 --,p_urgent_marker IN OUT nocopy VARCHAR2 4011263
1436 ,p_unique_test_id IN OUT nocopy VARCHAR2 -- 4011263
1437 ,p_return_type IN OUT nocopy VARCHAR2 -- 4011263
1438 ,p_payroll_action_id IN OUT nocopy NUMBER) IS
1439 --
1440 l_count number := 0;
1441 l_payroll_action_id VARCHAR2(81); -- Reqd for assertion.
1442 --
1443 -- Added for nocopy
1444 ln_permit_no VARCHAR2(12);
1445 ln_eoy_mode VARCHAR2(30);
1446 ln_tax_dist_ref VARCHAR2(3);
1447 ln_tax_ref_no VARCHAR2(10);
1448 ln_test_indicator VARCHAR2(1);
1449 ln_unique_test_id VARCHAR2(12); -- 4011263
1450 ln_return_type VARCHAR2(12); -- 4011263
1451 -- ln_urgent_marker VARCHAR2(1); 4011263
1452 ln_payroll_action_id NUMBER(9);
1453 --
1454 cursor get_action_eoy_mode(c_payroll_action_id number) is
1455 select report_category
1456 from pay_payroll_actions
1457 where payroll_action_id = c_payroll_action_id;
1458 --
1459 BEGIN
1460 -- Added for nocopy
1461 ln_permit_no := p_permit_no;
1462 ln_eoy_mode := p_eoy_mode;
1463 ln_tax_dist_ref := p_tax_dist_ref;
1464 ln_tax_ref_no := p_tax_ref_no;
1465 ln_test_indicator := p_test_indicator;
1466 ln_unique_test_id := p_unique_test_id; -- 4011263
1467 ln_return_type := p_return_type; -- 4011263
1468 -- ln_urgent_marker := p_urgent_marker; 4011263
1469 ln_payroll_action_id := p_payroll_action_id;
1470 --
1471 -- Get the parameters passed to the module
1472 -- Default the EOY Mode to 'P'
1473 p_eoy_mode := 'P';
1474 --
1475 BEGIN
1476 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',400);
1477 -- This loop is used to obtain all parameter values. The prerequisite to
1478 -- this functioning correctly is that rows are populated in the
1479 -- pay_mag_tape tables from position 1 onwards. When a row in the names
1480 -- table is not found, the loop exits by means of an exception.
1481 -- Also note that if a corresponding value is missing, the loop will exit.
1482 LOOP
1483 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',405);
1484 l_count := l_count + 1;
1485 hr_utility.trace(to_char(l_count));
1486 hr_utility.trace('Name: '||pay_mag_tape.internal_prm_names(l_count));
1487 hr_utility.trace('Value: '||pay_mag_tape.internal_prm_values(l_count));
1488 IF pay_mag_tape.internal_prm_names(l_count) = 'TRANSFER_PAYROLL_ACTION_ID'
1489 THEN
1490 l_payroll_action_id := pay_mag_tape.internal_prm_values(l_count);
1491 -- elsif pay_mag_tape.internal_prm_names(l_count) = 'PERMIT' then
1492 -- p_permit_no := pay_mag_tape.internal_prm_values(l_count);
1493 -- elsif pay_mag_tape.internal_prm_names(l_count) = 'TAX_DISTRICT_REFERENCE' then
1494 -- p_tax_dist_ref := SUBSTR(pay_mag_tape.internal_prm_values(l_count),1,3);
1495 -- p_tax_ref_no := LTRIM(SUBSTR(pay_mag_tape.internal_prm_values(l_count),4), '/');
1496 ELSIF pay_mag_tape.internal_prm_names(l_count) = 'TEST' THEN
1497 p_test_indicator := nvl(pay_mag_tape.internal_prm_values(l_count),'N');
1498 ELSIF pay_mag_tape.internal_prm_names(l_count) = 'UNIQUE_TEST_ID' THEN
1499 p_unique_test_id := nvl(pay_mag_tape.internal_prm_values(l_count),'N');
1500 ELSIF pay_mag_tape.internal_prm_names(l_count) = 'RETURN_TYPE' THEN
1501 p_return_type := nvl(pay_mag_tape.internal_prm_values(l_count),'N');
1502 /* Start 4011263
1503 ELSIF pay_mag_tape.internal_prm_names(l_count) = 'URGENT' THEN
1504 p_urgent_marker := nvl(pay_mag_tape.internal_prm_values(l_count),'N');
1505 End 4011263 */
1506 END IF;
1507 --
1508 END LOOP;
1509 --
1510 EXCEPTION
1511 WHEN no_data_found THEN
1512 -- Use this exception to exit loop as no. of plsql tab items
1513 -- is not known beforehand. All values should be assigned.
1514 hr_utility.trace('No data Found from plsql table loop');
1515 NULL;
1516 WHEN value_error THEN
1517 hr_utility.trace(to_char(l_count));
1518 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',413);
1519 END;
1520 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',415);
1521 p_payroll_action_id := to_number(l_payroll_action_id);
1522 --
1523 -- Obtain EOY Mode from the Payroll Action ID.
1524 --
1525 OPEN get_action_eoy_mode(p_payroll_action_id);
1526 FETCH get_action_eoy_mode INTO p_eoy_mode;
1527 IF get_action_eoy_mode%notfound THEN
1528 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',419);
1529 RAISE no_data_found; -- means no payroll action exists.
1530 END IF;
1531 CLOSE get_action_eoy_mode;
1532 --
1533 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',420);
1534 --
1535 -- Added for nocopy
1536 EXCEPTION
1537 WHEN OTHERS THEN
1538 p_permit_no := ln_permit_no;
1539 p_eoy_mode := ln_eoy_mode;
1540 p_tax_dist_ref := ln_tax_dist_ref;
1541 p_tax_ref_no := ln_tax_ref_no;
1542 p_test_indicator := ln_test_indicator;
1543 p_unique_test_id := ln_unique_test_id; -- 4011263
1544 p_return_type := ln_return_type; -- 4011263
1545 -- p_urgent_marker := ln_urgent_marker; 4011263
1546 p_payroll_action_id := ln_payroll_action_id;
1547
1548 END get_parameters;
1549 --
1550 -- START HERE
1551 --
1552 PROCEDURE eoy_control IS
1553 --
1554 cursor get_errored_actions(c_payroll_action_id number) is
1555 select '1' from dual where exists
1556 (select action_status
1557 from pay_assignment_actions
1558 where payroll_action_id = c_payroll_action_id
1559 and action_status = 'E');
1560 --
1561 -- Start of BUG 5671777-5
1562 -- Changed start date of the EOY process to reflect start of the current tax year
1563 -- so need to add 12 months to the start date.
1564 --
1565 CURSOR get_start_end_year(p_payroll_action_id NUMBER) IS
1566 SELECT to_date('06/04/'||to_char(start_date,'YYYY'),'dd/mm/yyyy')
1567 -- add_months(to_date('06/04/'||to_char(start_date,'YYYY'),'dd/mm/yyyy'),12)
1568 -- End of BUG 5671777-5
1569 start_year,
1570 effective_date end_year
1571 FROM pay_payroll_actions
1572 WHERE payroll_action_id = p_payroll_action_id;
1573 --
1574 -- Record type 2 placeholders
1575 l_effective_date DATE;
1576 l_error_text VARCHAR2(240);
1577 l_errored BOOLEAN := FALSE;
1578 l_dummy VARCHAR2(1);
1579 l_dummy_number NUMBER;
1580 --l_ers_rebate NUMBER(9); --P35/P14 EOY 2003/2004
1581 --l_ees_rebate NUMBER(9); --P35/P14 EOY 2003/2004
1582 l_asg_message VARCHAR2(60);
1583 --
1584 -- General purpose variables
1585 l_index NUMBER(3) :=0; -- General purpose loop counter
1586 l_index2 NUMBER(3) :=0; -- General purpose loop counter
1587 l_plsql_index NUMBER(3) :=0; -- Index of the pl/sql tables
1588 l_local_char VARCHAR2(1); -- Holds a char for testing
1589 l_local_date DATE; -- Used to hold a converted char
1590 l_tot_refund VARCHAR2(1):=NULL; -- Set to 'R' if tax refund
1591 l_type2_errors NUMBER;
1592 l_type1_errors NUMBER;
1593 l_char_errors NUMBER;
1594 l_loc_per NUMBER;
1595 l_mar_req_id VARCHAR2(81) := '-1'; -- Chars, as passed into formula.
1596 l_p35_req_id VARCHAR2(81) := '-1';
1597 --
1598 BEGIN
1599 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',0);
1600 --
1601 -- Start checking for record type 1
1602 --
1603
1604 -- Added l_ni_tax_year input parameter. EOY 2011/12. Bug 12694562.
1605
1606 open C_NI_NEW_TAX_YEAR;
1607 fetch C_NI_NEW_TAX_YEAR into l_ni_tax_year;
1608 close C_NI_NEW_TAX_YEAR;
1609
1610 IF fetch_new_header THEN
1611 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',10);
1612 -- A Record type 1 is required
1613 IF NOT (header_cur%ISOPEN) THEN
1614 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',20);
1615 -- Get all necessary parameters. The payroll action ID
1616 -- is validated.
1617 get_parameters(g_permit_no
1618 ,g_eoy_mode
1619 ,g_tax_dist_ref
1620 ,g_tax_ref_no
1621 ,g_test_indicator
1622 ,g_unique_test_id -- 4011263
1623 ,g_return_type -- 4011263
1624 -- ,g_urgent_marker 4011263
1625 ,g_payroll_action_id);
1626 hr_utility.trace('The passed in Mode is '||g_eoy_mode||'@');
1627 hr_utility.trace('The payroll action ID is '||g_payroll_action_id||'@');
1628 --
1629 g_old_tax_dist_ref := g_tax_dist_ref;
1630 g_old_tax_ref_no := g_tax_ref_no;
1631 --
1632 OPEN get_start_end_year(g_payroll_action_id);
1633 FETCH get_start_end_year INTO g_start_year, g_end_year;
1634 CLOSE get_start_end_year;
1635 hr_utility.trace('After get_start_end_year, g_start_year='||
1636 fnd_Date.date_to_displaydate(g_start_year));
1637 hr_utility.trace('g_end_year='||
1638 fnd_Date.date_to_displaydate(g_end_year));
1639 --
1640 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',25);
1641 --
1642 -- Check to see if the Payroll Action just retrieved has any
1643 -- errors. If not, check whether any assignment actions within the payroll
1644 -- action have errored. 1st error msg takes precedence.
1645 --
1646 l_error_text :=
1647 pay_gb_eoy_archive.get_arch_str(g_payroll_action_id,'X_PAYROLL_ACTION_MESSAGE');
1648 if l_error_text is null then
1649 open get_errored_actions(g_payroll_action_id);
1650 fetch get_errored_actions into l_dummy;
1651 if get_errored_actions%found then
1652 l_errored := TRUE;
1653 -- This will use the default error value in MAG_RECORD7
1654 end if;
1655 close get_errored_actions;
1656 else
1657 --
1658 -- There is a payroll action error, this will be picked up by
1659 -- the DBI call in MAG_RECORD7.
1660 --
1661 l_errored := TRUE;
1662 end if;
1663 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',27);
1664 --
1665 -- First time in so clear the error type counts
1666 --
1667 pay_mag_tape.internal_prm_values(3) := 0;
1668 pay_mag_tape.internal_prm_values(4) := 0;
1669 pay_mag_tape.internal_prm_values(5) := 0;
1670 OPEN header_cur(g_payroll_action_id);
1671 END IF;
1672 IF NOT(permit_change) THEN
1673 -- Get record from EOY table as next record
1674 -- for record type 1 required
1675 hr_utility.trace('1 The global tax dist is '||g_old_tax_dist_ref);
1676 hr_utility.trace('1 The global tax ref is '||g_old_tax_ref_no);
1677 hr_utility.trace('1 The global Permit is '||g_permit_no);
1678 hr_utility.trace('1 The global Payroll is '||g_payroll_id);
1679 --
1680 IF l_errored THEN
1681 -- Either the Payroll Action or an Assignment Action has Errored.
1682 hr_utility.trace('Errored Payroll Action: '||g_payroll_action_id);
1683 -- Call formula to error payroll.
1684 mag_tape_init(7);
1685 mag_tape_interface('L_PAYROLL_ACTION_ID',to_char(g_payroll_action_id));
1686 hr_utility.trace('after mag tape interface calls');
1687 pay_mag_tape.internal_cxt_names(1) := 'NUMBER_OF_CONTEXT';
1688 pay_mag_tape.internal_cxt_values(1) := '2';
1689 pay_mag_tape.internal_cxt_names(2) := 'PAYROLL_ACTION_ID';
1690 pay_mag_tape.internal_cxt_values(2) := to_char(g_payroll_action_id);
1691 hr_utility.trace('after cxt calls: '||pay_mag_tape.internal_cxt_values(2));
1692 ELSE
1693 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',28);
1694 -- No errors so fetch header info.
1695 FETCH header_cur INTO g_new_permit_no
1696 ,g_new_payroll_id
1697 ,g_tax_district_ref
1698 ,g_tax_ref_no
1699 -- 4011263: ,g_tax_district_name
1700 ,g_tax_year
1701 ,g_employers_name;
1702 -- 4752018: ,g_employers_address;
1703 /* Start 4011263
1704 ,g_econ
1705 ,g_ssp_recovery
1706 ,g_smp_recovery
1707 ,g_smp_compensation
1708 ,g_spp_recovery --P35/P14 EOY 2003/2004
1709 ,g_spp_compensation --P35/P14 EOY 2003/2004
1710 ,g_sap_recovery --P35/P14 EOY 2003/2004
1711 ,g_sap_compensation;--P35/P14 EOY 2003/2004
1712 --
1713 End 4011263 */
1714 -- Fetch EDI sender ID and Payroll action's request_id
1715 --
1716 get_edi_sender_id(g_payroll_action_id);
1717 /* Start 4011263
1718 -- Bug 2696015: Added for P14 EDI Enhancement 2003
1719 get_edi_submitter_no(g_payroll_action_id);
1720 End 4011263 */
1721 --
1722 IF header_cur%NOTFOUND THEN
1723 -- No more records found so end of run
1724 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',30);
1725 IF g_tot_rec2_per > 0 THEN
1726 -- If at least one record has been found then create
1727 -- a permit total
1728 create_record_type3;
1729 ELSE
1730 -- No records found for permit create dummy record
1731 mag_tape_init(6);
1732 END IF;
1733 fetch_new_header := FALSE;
1734 process_emps := FALSE;
1735 edi_process_emp_header := FALSE;
1736 edi_process_ni_details := FALSE;
1737 edi_process_emp_trailer := FALSE;
1738 sub_header := FALSE;
1739 fin_run := TRUE;
1740 /* A fetch of a new header is due to the first fetch or
1741 change of permit or payroll */
1742 ELSIF (g_tax_district_ref <> NVL(g_old_tax_dist_ref, g_tax_district_ref)
1743 OR g_tax_ref_no <> NVL(g_old_tax_ref_no, g_tax_ref_no)
1744 OR g_new_permit_no <> NVL(g_permit_no,g_new_permit_no)) THEN
1745 --
1746 -- The permit has changed so construct the record type 3
1747 --
1748 hr_utility.trace('2 Fetched tax dist is '||g_tax_district_ref);
1749 hr_utility.trace('2 Fetched tax ref is '||g_tax_ref_no);
1750 hr_utility.trace('2 Fetched Permit is '||g_new_permit_no);
1751 hr_utility.trace('2 Fetched Payroll_id is '||g_new_payroll_id);
1752 create_record_type3;
1753 -- Save required values in globals
1754 g_old_tax_dist_ref := g_tax_district_ref;
1755 g_old_tax_ref_no := g_tax_ref_no;
1756 g_permit_no := g_new_permit_no;
1757 g_payroll_id := g_new_payroll_id;
1758 permit_change := TRUE;
1759 -- Close the type 2 cursor so it will be re-opened with
1760 -- the new parameters
1761 CLOSE emps_cur;
1762 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',40);
1763 ELSE
1764 -- No permit change so add new smp and smp values to totals
1765 /* Start 4011263
1766 g_tot_ssp_rec := g_tot_ssp_rec + g_ssp_recovery;
1767 g_tot_smp_rec := g_tot_smp_rec + g_smp_recovery;
1768 g_tot_smp_comp := g_tot_smp_comp + g_smp_compensation;
1769 g_tot_spp_rec := g_tot_spp_rec + g_spp_recovery; --P35/P14 EOY 2003/2004
1770 g_tot_spp_comp := g_tot_spp_comp + g_spp_compensation;--P35/P14 EOY 2003/2004
1771 g_tot_sap_rec := g_tot_sap_rec + g_sap_recovery; --P35/P14 EOY 2003/2004
1772 g_tot_sap_comp := g_tot_sap_comp + g_sap_compensation;--P35/P14 EOY 2003/2004
1773 End 4011263 */
1774 hr_utility.trace('3 Fetched tax dist is '||g_tax_district_ref);
1775 hr_utility.trace('3 Fetched tax ref is '||g_tax_ref_no);
1776 hr_utility.trace('3 Fetched Permit is '||g_new_permit_no);
1777 hr_utility.trace('3 Fetched Payroll_id is '||g_new_payroll_id);
1778 IF g_new_payroll_id <> NVL(g_payroll_id,g_new_payroll_id) THEN
1779 -- The payroll_id has changed in permit_no
1780 g_payroll_id := g_new_payroll_id;
1781 -- Write the sub_header and then get the employee details
1782 create_sub_header;
1783 -- Close the type 2 cursor so it will be re-opened with
1784 -- the new parameters
1785 CLOSE emps_cur;
1786 fetch_new_header := FALSE;
1787 permit_change := FALSE;
1788 IF g_eoy_mode IN ( 'F - P14 EDI', 'P - P14 EDI') THEN
1789 process_emps := FALSE;
1790 edi_process_emp_header := TRUE;
1791 edi_process_ni_details := FALSE;
1792 edi_process_emp_trailer := FALSE;
1793 ELSE
1794 process_emps := TRUE;
1795 edi_process_emp_header := FALSE;
1796 edi_process_ni_details := FALSE;
1797 edi_process_emp_trailer := FALSE;
1798 END IF;
1799 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',45);
1800 ELSE
1801 hr_utility.trace('No payroll or permit change ');
1802 hr_utility.trace('4 Fetched tax dist is '||g_tax_district_ref);
1803 hr_utility.trace('4 Fetched tax ref is '||g_tax_ref_no);
1804 hr_utility.trace('4 Fetched Permit is '||g_new_permit_no);
1805 hr_utility.trace('4 Fetched Payroll_id is '||g_new_payroll_id);
1806 -- Save required values in globals
1807 g_old_tax_dist_ref := g_tax_district_ref;
1808 g_old_tax_ref_no := g_tax_ref_no;
1809 g_permit_no := g_new_permit_no;
1810 g_payroll_id := g_new_payroll_id;
1811 create_record_type1;
1812 fetch_new_header := FALSE;
1813 sub_header := TRUE;
1814 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',50);
1815 END IF;
1816 END IF;
1817 END IF; -- End of Errored payroll check.
1818 ELSE
1819 -- Change of permit so create a type 1 record from old values
1820 permit_change := FALSE;
1821 create_record_type1;
1822 fetch_new_header := FALSE;
1823 sub_header := TRUE;
1824 -- 1st record with this permit so set totals to 0
1825 /* Start 4011263 */
1826 g_tot_ssp_rec := 0; --g_ssp_recovery;
1827 g_tot_smp_rec := 0; --g_smp_recovery;
1828 g_tot_spp_rec := 0; --g_spp_recovery; --P35/P14 EOY 2003/2004
1829 g_tot_aspp_rec := 0; -- EOY 2011/12 for bug : 12329727
1830 g_tot_sap_rec := 0; --g_sap_recovery; --P35/P14 EOY 2003/2004
1831 -- g_tot_smp_comp := g_smp_compensation;
1832 -- g_tot_spp_comp := g_spp_compensation; --P35/P14 EOY 2003/2004
1833 -- g_tot_sap_comp := g_sap_compensation; --P35/P14 EOY 2003/2004
1834 /* End 4011263 */
1835
1836 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',60);
1837 END IF;
1838 --
1839 -- Check if sub-header required
1840 --
1841 ELSIF sub_header THEN
1842 create_sub_header;
1843 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',70);
1844 sub_header := FALSE;
1845 IF g_eoy_mode IN ( 'F - P14 EDI', 'P - P14 EDI') THEN
1846 process_emps := FALSE;
1847 edi_process_emp_header := TRUE;
1848 edi_process_ni_details := FALSE;
1849 edi_process_emp_trailer := FALSE;
1850 ELSE
1851 process_emps := TRUE;
1852 edi_process_emp_header := FALSE;
1853 edi_process_ni_details := FALSE;
1854 edi_process_emp_trailer := FALSE;
1855 END IF;
1856 --
1857 -- Check for a dummy record 2 needed when more than 4 Ni cats exist for
1858 -- a single employee
1859 --
1860 ELSIF process_dummy THEN
1861 -- A special record type 2
1862 -- More than 4 more NI categories exist for the employee
1863 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',700);
1864 IF g_ni_total - g_last_ni > 4 THEN
1865 p_create_dummy(g_last_ni,4);
1866 g_last_ni := g_last_ni + 4;
1867 ELSE
1868 -- Less than 4 more NI categories exist for the employee
1869 p_create_dummy(g_last_ni,g_ni_total-g_last_ni);
1870 g_last_ni := 0;
1871 g_ni_total := 0;
1872 -- Reset the flags to continue processing any further employees
1873 process_emps := TRUE;
1874 process_dummy := FALSE;
1875 END IF;
1876 --
1877 -- Check for processing record type 2
1878 --
1879 ELSIF process_emps THEN
1880 -- Record type 2 required
1881 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',100);
1882 hr_utility.trace('The emp tax dist is '||g_tax_district_ref);
1883 hr_utility.trace('The emp tax ref is '||g_tax_ref_no);
1884 hr_utility.trace('The emp permit_no is '||g_permit_no);
1885 hr_utility.trace('The emp payroll_id is '||to_char(g_payroll_id));
1886 IF NOT (emps_cur%ISOPEN) THEN
1887 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',110);
1888 OPEN emps_cur(g_payroll_id, g_payroll_action_id);
1889 END IF;
1890 FETCH emps_cur INTO g_employee_number
1891 ,g_assignment_action_id
1892 ,g_last_name
1893 ,g_first_name
1894 ,g_middle_name
1895 ,g_title
1896 ,g_date_of_birth
1897 ,g_sex
1898 ,g_address_line1
1899 ,g_address_line2
1900 ,g_address_line3
1901 ,g_town_or_city
1902 ,g_country -- 4011263
1903 ,g_postal_code
1904 ,g_tax_code
1905 ,g_w1_m1_indicator
1906 ,g_national_insurance_number
1907 ,g_ssp
1908 ,g_smp
1909 ,l_spp_adopt --P35/P14 EOY 2003/2004
1910 ,l_spp_birth --P35/P14 EOY 2003/2004
1911 ,l_aspp_adopt --P35/P14 EOY 2011/12
1912 ,l_aspp_birth --P35/P14 EOY 2011/12
1913 ,g_sap --P35/P14 EOY 2003/2004
1914 -- 4011263: ,g_gross_pay
1915 ,g_tax_paid
1916 ,g_tax_refund
1917 ,g_previous_taxable_pay
1918 ,g_previous_tax_paid
1919 ,g_start_of_emp
1920 ,g_termination_date
1921 /* Start 4011263
1922 ,g_superannuation_paid
1923 ,g_superannuation_refund
1924 End 4011263 */
1925 ,g_widows_and_orphans
1926 ,g_student_loans
1927 ,g_week_53_indicator
1928 ,g_taxable_pay
1929 /* 4011263
1930 ,g_pension_indicator
1931 4011263 */
1932 ,g_director_indicator
1933 ,g_assignment_id
1934 ,l_effective_date
1935 ,l_asg_message
1936 ,g_ni_multi_asg_flag;
1937 --
1938 g_full_name := ltrim(rtrim(g_last_name)) ||', '|| ltrim(rtrim(g_first_name));
1939 --
1940 IF emps_cur%NOTFOUND THEN
1941 --
1942 -- End of record type 2
1943 --
1944 -- Set escape from this section
1945 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',130);
1946 /* Each call of this package must return 1 record even */
1947 /* if its only a dummy formula call to do so */
1948 mag_tape_init(6);
1949 fetch_new_header:= TRUE;
1950 process_emps := FALSE;
1951 edi_process_emp_header := FALSE;
1952 edi_process_ni_details := FALSE;
1953 edi_process_emp_trailer := FALSE;
1954 ELSIF (nvl(g_ssp + g_smp +
1955 -- 4011263: g_gross_pay +
1956 g_tax_paid + g_previous_taxable_pay +
1957 g_previous_tax_paid + g_widows_and_orphans +
1958 g_student_loans + g_taxable_pay ,0) = 0) THEN
1959 -- 4011263: removed superannuation amount from above if condition
1960 /* The record fetched has all zero balances, no need on tape */
1961 /* exit to get next employee record */
1962 mag_tape_init(6);
1963 fetch_new_header:= FALSE;
1964 process_emps := TRUE;
1965 edi_process_emp_header := FALSE;
1966 edi_process_ni_details := FALSE;
1967 edi_process_emp_trailer := FALSE;
1968 ELSE
1969 --
1970 -- Fetch all the ni contributions for each employee
1971 -- in one hit.
1972 --
1973 --
1974 -- Note SCON validation done in the formula.
1975 --
1976 l_index := 1;
1977 FOR emp_values_rec IN emp_values(g_assignment_action_id)
1978 LOOP
1979 --scon_tab(l_index) := emp_values_rec.scon; --EOY 2012/2013
1980 category_tab(l_index) := emp_values_rec.cat_code;
1981 total_contrib_tab(l_index) := emp_values_rec.tot_cont;
1982 employees_contrib_tab(l_index) := emp_values_rec.emps_cont;
1983 ni_able_et_tab(l_index) := emp_values_rec.ni_able_et;
1984 ni_able_lel_tab(l_index) := emp_values_rec.ni_able_lel;
1985 ni_able_uel_tab(l_index) := emp_values_rec.ni_able_uel;
1986 ni_able_uap_tab(l_index) := emp_values_rec.ni_able_uap; -- 8357870
1987 ni_able_auel_tab(l_index) := emp_values_rec.ni_able_auel; --- EOY 07/08
1988 employers_rebate_tab(l_index) := emp_values_rec.employers_rebate;
1989 employees_rebate_tab(l_index) := emp_values_rec.employees_rebate;
1990 --
1991 hr_utility.trace('looping for asg action: '||to_char(g_assignment_action_id));
1992 if (emp_values_rec.cat_code) = 'P' then
1993 null; -- 4752018: NIC Holiday will not be reported on P14 anymore
1994 else
1995 g_tot_contribs := g_tot_contribs + emp_values_rec.tot_cont;
1996 end if; -- IF NI CODE = 'P'
1997 l_index := l_index + 1;
1998 END LOOP;
1999 hr_utility.trace('Fetched emp_values, now get NI for all CAT codes');
2000 hr_utility.trace('Total NI Cats index: '||to_char(l_index));
2001 /* Keep the total number of NI category codes for the employee */
2002 /* If > 5 then raise warning in the mag tape log file */
2003 g_ni_total := l_index - 1;
2004 IF l_index < 5 THEN
2005 /* Even if no category codes exist the fields must be */
2006 /* defaulted and written to the mag tape. */
2007 FOR l_plsql_index IN l_index..4 LOOP
2008 --scon_tab(l_plsql_index) := ' '; --EOY 2012/2013
2009 category_tab(l_plsql_index) := ' ';
2010 total_contrib_tab(l_plsql_index) := 0;
2011 employees_contrib_tab(l_plsql_index) := 0;
2012 ni_able_et_tab(l_plsql_index) := 0;
2013 ni_able_lel_tab(l_plsql_index) := 0;
2014 ni_able_uel_tab(l_plsql_index) := 0;
2015 ni_able_uap_tab(l_plsql_index) := 0; -- 8357870
2016 ni_able_auel_tab(l_plsql_index) := 0; -- EOY 07/08
2017 employers_rebate_tab(l_plsql_index) := 0;
2018 employees_rebate_tab(l_plsql_index) := 0;
2019 END LOOP;
2020 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',150);
2021 END IF;
2022 hr_utility.trace('Total NI Cats index: '||to_char(l_index));
2023 /* Create a type 2 record */
2024 -- IF nvl(g_ssp + g_smp + g_gross_pay + g_tax_paid + g_previous_taxable_pay +
2025 -- g_previous_tax_paid + nvl(g_superannuation_paid,0) + g_widows_and_orphans +
2026 -- g_student_loans + g_taxable_pay ,0) > 0 THEN
2027 /* Set up the no of parameters and the formula professor */
2028 hr_utility.trace('Writing record type 2');
2029 mag_tape_init(2);
2030 /* Now create a record type 2 */
2031 mag_tape_interface('EOY_MODE',g_eoy_mode);
2032 mag_tape_interface('EMPLOYEE_NUMBER',NVL(g_employee_number,' '));
2033 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',250);
2034 --
2035 -- Note name validation performed in formula.
2036 mag_tape_interface('LAST_NAME',NVL(g_last_name,' '));
2037 mag_tape_interface('FIRST_NAME',NVL(g_first_name,' '));
2038 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',275);
2039 mag_tape_interface('MIDDLE_NAME',NVL(g_middle_name,' '));
2040 mag_tape_interface('DATE_OF_BIRTH',g_date_of_birth);
2041 mag_tape_interface('GENDER',g_sex);
2042 /* 4011263
2043 -- Order Address lines to push nulls to end, using g_full_address as
2044 -- a temporary variable.
2045 g_full_address := rpad(nvl(g_address_line1||g_address_line2||
2046 g_address_line3||g_town_or_city,' '),108);
2047 -- Split into 4 and pass them to formula
2048 g_address_line1:=substr(g_full_address,1,27);
2049 g_address_line2:=substr(g_full_address,28,27);
2050 g_address_line3:=substr(g_full_address,55,27);
2051 g_town_or_city:=substr(g_full_address,82);
2052 4011263 */
2053 mag_tape_interface('ADDRESS_LINE1',g_address_line1);
2054 mag_tape_interface('ADDRESS_LINE2',g_address_line2);
2055 mag_tape_interface('ADDRESS_LINE3',g_address_line3);
2056 mag_tape_interface('TOWN_OR_CITY',g_town_or_city);
2057 mag_tape_interface('COUNTRY',g_country); -- 4011263
2058 --
2059 mag_tape_interface('POSTAL_CODE',g_postal_code);
2060 mag_tape_interface('TAX_CODE',g_tax_code);
2061 mag_tape_interface('W1_M1',g_w1_m1_indicator);
2062 mag_tape_interface('NI_NO',g_national_insurance_number);
2063 --
2064 -- Send the first record from the pl/sql tables to the mag tape
2065 --
2066 --mag_tape_interface('SCON1',scon_tab(1)); --EOY 2012/2013
2067 mag_tape_interface('NI_CATEGORY_CODE1',category_tab(1));
2068 mag_tape_interface('TOTAL_CONTRIBUTIONS1',total_contrib_tab(1));
2069 mag_tape_interface('EMPLOYEES_CONTRIBUTIONS1',
2070 employees_contrib_tab(1));
2071 mag_tape_interface('NI_ABLE_ET1', ni_able_et_tab(1));
2072 mag_tape_interface('NI_ABLE_LEL1', ni_able_lel_tab(1));
2073 mag_tape_interface('NI_ABLE_UEL1', ni_able_uel_tab(1));
2074 --l_ers_rebate := employers_rebate_tab(1); --P35/P14 EOY 2003/2004
2075 --l_ees_rebate := employees_rebate_tab(1); --P35/P14 EOY 2003/2004
2076 mag_tape_interface('SSP',g_ssp);
2077 mag_tape_interface('SMP',g_smp);
2078 g_spp := nvl(l_spp_birth,0) + nvl(l_spp_adopt,0); --P35/P14 EOY 2003/2004
2079 mag_tape_interface('SPP',g_spp); --P35/P14 EOY 2003/2004
2080 mag_tape_interface('SAP',g_sap); --P35/P14 EOY 2003/2004
2081 -- 4011263: mag_tape_interface('GROSS_PAY',g_gross_pay);
2082 mag_tape_interface('TAX_PAID',ABS(g_tax_paid));
2083 g_tot_tax := g_tot_tax + g_tax_paid;
2084 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',280);
2085 --
2086 -- Tax Refund must be 'R' or blank. Formula validates this.
2087 --
2088 mag_tape_interface('TAX_REFUND',nvl(g_tax_refund,' '));
2089 mag_tape_interface('PREVIOUS_TAXABLE_PAY',
2090 g_previous_taxable_pay);
2091 --
2092 mag_tape_interface('PREVIOUS_TAX_PAID',
2093 g_previous_tax_paid);
2094 --
2095 mag_tape_interface('DATE_OF_STARTING',g_start_of_emp);
2096 BEGIN
2097 IF g_termination_date IS NOT NULL THEN
2098 l_local_date := TO_DATE(g_termination_date,'DDMMYYYY');
2099 END IF;
2100 EXCEPTION
2101 WHEN value_error THEN
2102 g_termination_date := ' ';
2103 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',300);
2104 END;
2105 mag_tape_interface('TERMINATION_DATE',NVL(g_termination_date,' '));
2106 /* 4011263: Remove superannuation from EOY
2107 --added nvl for bug fix 3614251
2108 mag_tape_interface('SUPERANNUATION',nvl(g_superannuation_paid,0));
2109 --
2110 -- Superannuation Refund must be 'R' or blank. Formula validates.
2111 --
2112 mag_tape_interface('SUPERANNUATION_REFUND',
2113 nvl(g_superannuation_refund,' '));
2114 4011263 */
2115 mag_tape_interface('WIDOWS_ORPHANS',
2116 g_widows_and_orphans);
2117 -- Added Student Loan
2118 mag_tape_interface('STUDENT_LOANS', g_student_loans);
2119 --
2120 -- Keep totals of Student Loans
2121 --
2122 g_tot_student_ln := g_tot_student_ln + g_student_loans;
2123 --
2124 -- Week 53 must be 3,4,6 or blank, formula validates.
2125 --
2126 mag_tape_interface('WEEK_53', nvl(g_week_53_indicator,' '));
2127 mag_tape_interface('TAXABLE_PAY',g_taxable_pay);
2128 /* 4011263
2129 mag_tape_interface('PENSIONER_INDICATOR', nvl(g_pension_indicator,' '));
2130 mag_tape_interface('DIRECTOR_INDICATOR', nvl(g_director_indicator,' '));
2131 4011263 */
2132 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',350);
2133 --
2134 -- Now send up to 3 of the remaining contribution records to mag tape
2135 -- If they do not exist they have been defaulted
2136 --
2137 FOR l_index IN 2..4 LOOP
2138 -- mag_tape_interface('SCON'||TO_CHAR(l_index),scon_tab(l_index)); --EOY 2012/2013
2139 mag_tape_interface('NI_CATEGORY_CODE'||
2140 TO_CHAR(l_index) ,category_tab(l_index));
2141 mag_tape_interface('TOTAL_CONTRIBUTIONS'||l_index
2142 ,total_contrib_tab(l_index));
2143 mag_tape_interface('EMPLOYEES_CONTRIBUTIONS'||
2144 TO_CHAR(l_index), employees_contrib_tab(l_index));
2145 mag_tape_interface('NI_ABLE_ET'||
2146 TO_CHAR(l_index), ni_able_et_tab(l_index));
2147 mag_tape_interface('NI_ABLE_LEL'||
2148 TO_CHAR(l_index), ni_able_lel_tab(l_index));
2149 mag_tape_interface('NI_ABLE_UEL'||
2150 TO_CHAR(l_index), ni_able_uel_tab(l_index));
2151 -- l_ers_rebate := l_ers_rebate + employers_rebate_tab(l_index); --P35/P14 EOY 2003/2004
2152 -- l_ees_rebate := l_ees_rebate + employees_rebate_tab(l_index); --P35/P14 EOY 2003/2004
2153 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',360);
2154 END LOOP;
2155 --mag_tape_interface('NI_ERS_REBATE', l_ers_rebate); --P35/P14 EOY 2003/2004
2156 --mag_tape_interface('NIEES_REBATE', l_ees_rebate); --P35/P14 EOY 2003/2004
2157 mag_tape_interface('ASSIGNMENT_MESSAGE', l_asg_message);
2158 --
2159 --g_tot_ers_rebate := g_tot_ers_rebate + l_ers_rebate; --P35/P14 EOY 2003/2004
2160 --g_tot_ees_rebate := g_tot_ees_rebate + l_ees_rebate; --P35/P14 EOY 2003/2004
2161 --
2162 -- Running count of all employee records
2163 --
2164 g_tot_rec2_per := g_tot_rec2_per + 1;
2165 -- Now check the number of NI categories found for this employee
2166 IF g_ni_total > 4 THEN
2167 hr_utility.trace('The employee is '||g_employee_number);
2168 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',365);
2169 -- More than four so set flags for creation of dummy record
2170 process_emps := FALSE;
2171 process_dummy := TRUE;
2172 -- Index in PL/SQL tables set to the last record selected
2173 g_last_ni := 4;
2174 END IF;
2175 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',370);
2176 --
2177 END IF; /* End of create type 2 record */
2178 --
2179 -- If EOY mode is P14 EDI then write employee header and NI Details and employee
2180 -- trailer records instead of above Mag Tape type 2 record.
2181 ELSIF edi_process_emp_header THEN
2182 -- Need to process employee header record for EDI Process
2183 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',100);
2184 hr_utility.trace('The emp tax dist is '||g_tax_district_ref);
2185 hr_utility.trace('The emp tax ref is '||g_tax_ref_no);
2186 hr_utility.trace('The emp permit_no is '||g_permit_no);
2187 hr_utility.trace('The emp payroll_id is '||to_char(g_payroll_id));
2188 --
2189 IF NOT (emps_cur%ISOPEN) THEN
2190 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',110);
2191 OPEN emps_cur(g_payroll_id, g_payroll_action_id);
2192 END IF;
2193 --
2194 FETCH emps_cur INTO g_employee_number
2195 ,g_assignment_action_id
2196 ,g_last_name
2197 ,g_first_name
2198 ,g_middle_name
2199 ,g_title
2200 ,g_date_of_birth
2201 ,g_sex
2202 ,g_address_line1
2203 ,g_address_line2
2204 ,g_address_line3
2205 ,g_town_or_city
2206 ,g_country -- 4011263
2207 ,g_postal_code
2208 ,g_tax_code
2209 ,g_w1_m1_indicator
2210 ,g_national_insurance_number
2211 ,g_ssp
2212 ,g_smp
2213 ,l_spp_adopt --P35/P14 EOY 2003/2004
2214 ,l_spp_birth --P35/P14 EOY 2003/2004
2215 ,l_aspp_adopt -- EOY 2011/12
2216 ,l_aspp_birth
2217 ,g_sap --P35/P14 EOY 2003/2004
2218 -- 4011263: ,g_gross_pay
2219 ,g_tax_paid
2220 ,g_tax_refund
2221 ,g_previous_taxable_pay
2222 ,g_previous_tax_paid
2223 ,g_start_of_emp
2224 ,g_termination_date
2225 -- 4011263: ,g_superannuation_paid
2226 -- 4011263: ,g_superannuation_refund
2227 ,g_widows_and_orphans
2228 ,g_student_loans
2229 ,g_week_53_indicator
2230 ,g_taxable_pay
2231 /* 4011263
2232 ,g_pension_indicator
2233 4011263 */
2234 ,g_director_indicator
2235 ,g_assignment_id
2236 ,l_effective_date
2237 ,l_asg_message
2238 ,g_ni_multi_asg_flag;
2239 --
2240 g_full_name := ltrim(rtrim(g_last_name)) ||', '|| ltrim(rtrim(g_first_name));
2241 --
2242 IF emps_cur%NOTFOUND THEN
2243 -- End of employee details for EDI process
2244 -- Set escape from this section
2245 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',130);
2246 -- Each call of this package must return 1 record even
2247 -- if its only a dummy formula call to do so
2248 mag_tape_init(6);
2249 fetch_new_header:= TRUE;
2250 edi_process_emp_header := FALSE;
2251 ELSE
2252 -- another employee found, increament the count
2253 g_tot_rec2_per := g_tot_rec2_per + 1;
2254 -- Update grand totals
2255 g_tot_tax := g_tot_tax + g_tax_paid;
2256 g_tot_student_ln := g_tot_student_ln + g_student_loans;
2257 -- Set up the no of parameters and the formula professor
2258 hr_utility.trace('Writing employee header');
2259 p14_edi_init(2);
2260 -- Now create employee header
2261 mag_tape_interface('EOY_MODE',g_eoy_mode);
2262 mag_tape_interface('EMPLOYEE_COUNT', nvl(g_tot_rec2_per,0));
2263 mag_tape_interface('EMPLOYEE_NUMBER',NVL(g_employee_number,' '));
2264 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',250);
2265 --
2266 -- Note name validation performed in formula.
2267 mag_tape_interface('LAST_NAME',NVL(g_last_name,' '));
2268 mag_tape_interface('FIRST_NAME',NVL(g_first_name,' '));
2269 mag_tape_interface('MIDDLE_NAME',NVL(g_middle_name,' '));
2270 --4011263: mag_tape_interface('TITLE',NVL(g_title,' '));
2271 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',275);
2272 mag_tape_interface('GENDER',g_sex);
2273 /* 4011263
2274 -- Order Address lines to push nulls to end, using g_full_address as
2275 -- a temporary variable.
2276 g_full_address := rpad(nvl(g_address_line1||g_address_line2||
2277 g_address_line3||g_town_or_city,' '),108);
2278 -- Split into 4 and pass them to formula
2279 g_address_line1:=substr(g_full_address,1,27);
2280 g_address_line2:=substr(g_full_address,28,27);
2281 g_address_line3:=substr(g_full_address,55,27);
2282 g_town_or_city:=substr(g_full_address,82);
2283 4011263 */
2284 mag_tape_interface('ADDRESS_LINE1', nvl(g_address_line1, ' '));
2285 mag_tape_interface('ADDRESS_LINE2', nvl(g_address_line2, ' '));
2286 mag_tape_interface('ADDRESS_LINE3', nvl(g_address_line3, ' '));
2287 mag_tape_interface('TOWN_OR_CITY', nvl(g_town_or_city, ' '));
2288 mag_tape_interface('COUNTRY', nvl(g_country, ' '));
2289 mag_tape_interface('POSTAL_CODE', nvl(g_postal_code, ' '));
2290 mag_tape_interface('NI_NO', nvl(g_national_insurance_number, ' '));
2291 mag_tape_interface('WEEK_53_INDICATOR', nvl(g_week_53_indicator, ' '));
2292 /* 4011263
2293 mag_tape_interface('PENSION_INDICATOR', nvl(g_pension_indicator, ' '));
2294 mag_tape_interface('DIRECTOR_INDICATOR', nvl(g_director_indicator, ' '));
2295 4011263 */
2296 mag_tape_interface('ASSIGNMENT_MESSAGE', nvl(l_asg_message, ' '));
2297 mag_tape_interface('FULL_NAME',NVL(g_full_name,' '));
2298 --
2299 hr_utility.trace('Employee Number='||g_employee_number);
2300 hr_utility.trace('full name='||g_first_name||' '||g_last_name);
2301 -- Fetch values for NI Details record
2302 g_edi_ni_cat_count := 0;
2303 g_edi_emp_ers_rebate := 0;
2304 g_edi_emp_ees_rebate := 0;
2305 g_rollup_ni_cat := ' ';
2306 --g_rollup_scon := ' '; --EOY 2012/2013
2307 g_rollup_emp_contrib := 0;
2308 g_rollup_tot_contrib := 0;
2309 g_rollup_lel_ni_cat := ' ';
2310 g_total_rollup_lel := 0;
2311 g_emp_tot_lel := 0;
2312 g_emp_tot_et := 0;
2313 g_emp_tot_uap := 0; -- 8816832 EOY 09/10
2314 g_emp_tot_uel := 0;
2315 g_emp_tot_ee_contrib := 0;
2316 g_emp_tot_ee_er_contrib := 0;
2317 --
2318 /* 8833756 begin - Added to identify agg assignments
2319 If for single assignments, NI Agg Flag is set wrongly, then for
2320 such assignments, rollup should not happen, and the validations
2321 inside PAY_GB_EDI_P14_NI_DETAILS should not be skipped, and
2322 hence g_ni_multi_asg_flag is set to 'N'.
2323 */
2324 if (g_ni_multi_asg_flag = 'Y') then
2325 hr_utility.trace('Inside g_ni_multi_asg_flag:'||g_ni_multi_asg_flag);
2326 hr_utility.trace('...g_assignment_id:'||g_assignment_id);
2327 hr_utility.trace('...g_start_year:'||g_start_year);
2328 hr_utility.trace('...g_end_year:'||g_end_year);
2329 hr_utility.trace('...g_tax_district_ref:'||g_tax_district_ref);
2330 hr_utility.trace('...g_tax_ref_no:'||g_tax_ref_no);
2331 if (get_assign_count(g_assignment_id,
2332 g_start_year,
2333 g_end_year ,
2334 g_tax_district_ref ,
2335 g_tax_ref_no ) = 0) then
2336 g_ni_multi_asg_flag := 'N';
2337 end if;
2338 end if;
2339 /* 8833756 - End of identify agg assignments */
2340 /* EOY 2012/2013 changes */
2341 IF g_ni_multi_asg_flag = 'Y' THEN
2342 hr_utility.trace('Before get_rollup_ni_cat cursor.');
2343 OPEN get_rollup_ni_cat(g_assignment_action_id);
2344 FETCH get_rollup_ni_cat INTO /*g_rollup_scon,*/ g_rollup_ni_cat;
2345 IF get_rollup_ni_cat%NOTFOUND THEN
2346 g_rollup_ni_cat := ' ';
2347 --g_rollup_scon := ' ';
2348 END IF;
2349 CLOSE get_rollup_ni_cat;
2350 hr_utility.trace('After get_rollup_ni_cat cursor, g_rollup_ni_cat='||g_rollup_ni_cat);
2351 --
2352 hr_utility.trace('Before get_lel_rollup_ni_cat cursor.');
2353 OPEN get_lel_rollup_ni_cat(g_assignment_action_id);
2354 FETCH get_lel_rollup_ni_cat INTO g_rollup_lel_ni_cat;
2355 IF get_lel_rollup_ni_cat%NOTFOUND THEN
2356 g_rollup_lel_ni_cat := ' ';
2357 END IF;
2358 CLOSE get_lel_rollup_ni_cat;
2359 hr_utility.trace('After get_lel_rollup_ni_cat cursor, g_rollup_lel_ni_cat='||g_rollup_lel_ni_cat);
2360 --
2361 hr_utility.trace('Before get_only_lel_total cursor.');
2362 OPEN get_only_lel_total(g_assignment_action_id);
2363 FETCH get_only_lel_total INTO g_total_rollup_lel;
2364 CLOSE get_only_lel_total;
2365 -- For bug#7043405 begin
2366 IF g_total_rollup_lel = 0 AND g_rollup_lel_ni_cat IS NOT NULL THEN
2367 g_rollup_lel_ni_cat := NULL;
2368 hr_utility.trace('setting g_rollup_lel_ni_cat to NULL as g_total_rollup_lel is 0. ');
2369 END IF;
2370 -- For bug#7043405 end
2371 hr_utility.trace('After get_only_lel_total cursor, g_total_rollup_lel='||g_total_rollup_lel);
2372 END IF;
2373 --
2374 hr_utility.trace('Looping through emp_values cursor.');
2375 FOR emp_values_rec IN emp_values(g_assignment_action_id)
2376 LOOP
2377 -- hr_utility.trace('SCON='||emp_values_rec.scon); --EOY 2012/2013
2378 hr_utility.trace('CATE_CODE='||emp_values_rec.cat_code);
2379 hr_utility.trace('LEL='||emp_values_rec.ni_able_lel);
2380 hr_utility.trace('ET='||emp_values_rec.ni_able_et);
2381 hr_utility.trace('UEL='||emp_values_rec.ni_able_uel);
2382 hr_utility.trace('UAP='||emp_values_rec.ni_able_uap); -- 8357870
2383 hr_utility.trace('AUEL='||emp_values_rec.ni_able_auel); -- EOY 07/08
2384 hr_utility.trace('TOT_CONT='||emp_values_rec.tot_cont);
2385 hr_utility.trace('EMPS_CONT='||emp_values_rec.emps_cont);
2386 --scon_tab(g_edi_ni_cat_count) := emp_values_rec.scon; --EOY 2012/2013
2387 category_tab(g_edi_ni_cat_count) := emp_values_rec.cat_code;
2388 total_contrib_tab(g_edi_ni_cat_count) := emp_values_rec.tot_cont;
2389 employees_contrib_tab(g_edi_ni_cat_count) := emp_values_rec.emps_cont;
2390 ni_able_et_tab(g_edi_ni_cat_count) := emp_values_rec.ni_able_et;
2391 ni_able_lel_tab(g_edi_ni_cat_count) := emp_values_rec.ni_able_lel;
2392 ni_able_uel_tab(g_edi_ni_cat_count) := emp_values_rec.ni_able_uel;
2393 ni_able_uap_tab(g_edi_ni_cat_count) := emp_values_rec.ni_able_uap; -- 8357870
2394 ni_able_auel_tab(g_edi_ni_cat_count) := emp_values_rec.ni_able_auel; ---EOY 07/08
2395 employers_rebate_tab(g_edi_ni_cat_count) := emp_values_rec.employers_rebate;
2396 employees_rebate_tab(g_edi_ni_cat_count) := emp_values_rec.employees_rebate;
2397 --
2398 hr_utility.trace('looping for asg action: '||to_char(g_assignment_action_id));
2399 if (emp_values_rec.cat_code) = 'P' then
2400 null; -- 4752018: NIC Holiday will not be reported on P14 anymore
2401 else
2402 g_tot_contribs := g_tot_contribs + emp_values_rec.tot_cont;
2403 end if; -- IF NI CODE = 'P'
2404 -- sum up ees and ers rebates for the employee
2405 g_edi_emp_ers_rebate := g_edi_emp_ers_rebate + emp_values_rec.employers_rebate;
2406 g_edi_emp_ees_rebate := g_edi_emp_ees_rebate + emp_values_rec.employees_rebate;
2407 IF g_ni_multi_asg_flag = 'Y' THEN
2408 --
2409 -- maintain total of employee's LEL/ET/UEL and EE Contributions
2410 --
2411 IF emp_values_rec.cat_code NOT IN ('X', 'C') THEN
2412 g_emp_tot_lel := g_emp_tot_lel + emp_values_rec.ni_able_lel;
2413 g_emp_tot_et := g_emp_tot_et + emp_values_rec.ni_able_et ;
2414 g_emp_tot_uap := g_emp_tot_uap + emp_values_rec.ni_able_uap; -- 8816832 EOY 09/10
2415 g_emp_tot_uel := g_emp_tot_uel + emp_values_rec.ni_able_uel;
2416 g_emp_tot_ee_contrib := g_emp_tot_ee_contrib
2417 + emp_values_rec.emps_cont;
2418 g_emp_tot_ee_er_contrib := g_emp_tot_ee_er_contrib
2419 + emp_values_rec.tot_cont;
2420 hr_utility.trace('g_emp_tot_lel='||g_emp_tot_lel);
2421 hr_utility.trace('g_emp_tot_et='||g_emp_tot_et);
2422 hr_utility.trace('g_emp_tot_uap='||g_emp_tot_uap); -- 8816832 EOY 09/10
2423 hr_utility.trace('g_emp_tot_uel='||g_emp_tot_uel);
2424 hr_utility.trace('g_emp_tot_ee_contrib='||g_emp_tot_ee_contrib);
2425 hr_utility.trace('g_emp_tot_ee_er_contrib='||g_emp_tot_ee_er_contrib);
2426 END IF;
2427 -- Check whther ni figures need to roll into another cat
2428 --EOY 07/08 Begin
2429 /*IF emp_values_rec.ni_able_lel = 0 AND
2430 emp_values_rec.ni_able_et = 0 AND
2431 emp_values_rec.ni_able_uel = 0 AND
2432 emp_values_rec.cat_code in ('A', 'B', 'D', 'E', 'F', 'G', 'J', 'L', 'S') THEN */
2433
2434 ---Changing the condition for Contribution Rollup
2435 /* Bug fix 10188309, added uap also in the check
2436 IF emp_values_rec.ni_able_uel = 0 AND */
2437 /* EOY 2012/2013 changes */
2438 IF emp_values_rec.ni_able_uap = 0 AND
2439 emp_values_rec.ni_able_uel = 0 AND
2440 emp_values_rec.ni_able_auel <> 0 AND
2441 emp_values_rec.tot_cont <> 0 AND
2442 emp_values_rec.cat_code in ('A', 'B', 'D', 'E',/* 'F', 'G', 'S',*/ 'J', 'L') THEN
2443 --EOY 07/08 End --
2444 hr_utility.trace('Update rollup figures.');
2445 --
2446 g_rollup_tot_contrib := g_rollup_tot_contrib
2447 + emp_values_rec.tot_cont;
2448 g_rollup_emp_contrib := g_rollup_emp_contrib
2449 + emp_values_rec.emps_cont;
2450 --
2451 hr_utility.trace('Rollup TOT_CONT='||g_rollup_tot_contrib);
2452 hr_utility.trace('Rollup EMP_CONT='||g_rollup_emp_contrib);
2453 END IF;
2454 --
2455 -- Check whether this is the NI cat to rollup contrib figures into
2456 IF emp_values_rec.cat_code = g_rollup_ni_cat
2457 --AND emp_values_rec.scon = g_rollup_scon --EOY 2012/2013
2458 --
2459 THEN
2460 hr_utility.trace('TOT_CONT Before rollup='||total_contrib_tab(g_edi_ni_cat_count));
2461 hr_utility.trace('EMP_CONT BEfore rollup='||employees_contrib_tab(g_edi_ni_cat_count));
2462 hr_utility.trace('Roll in figures from other NI Cats.');
2463 total_contrib_tab(g_edi_ni_cat_count) :=
2464 total_contrib_tab(g_edi_ni_cat_count) + g_rollup_tot_contrib;
2465 employees_contrib_tab(g_edi_ni_cat_count) :=
2466 employees_contrib_tab(g_edi_ni_cat_count) + g_rollup_emp_contrib;
2467 --
2468 hr_utility.trace('TOT_CONT After rollup='||total_contrib_tab(g_edi_ni_cat_count));
2469 hr_utility.trace('EMP_CONT After rollup='||employees_contrib_tab(g_edi_ni_cat_count));
2470 END IF;
2471 -- Check whether this is the NI Cat to rollup lel into
2472 IF emp_values_rec.cat_code = g_rollup_lel_ni_cat
2473 AND g_total_rollup_lel > 0 THEN
2474 --
2475 hr_utility.trace('LEL before rollup='||ni_able_lel_tab(g_edi_ni_cat_count));
2476 hr_utility.trace('Roll in LEL from other NI Cats.');
2477 ni_able_lel_tab(g_edi_ni_cat_count) :=
2478 ni_able_lel_tab(g_edi_ni_cat_count) + g_total_rollup_lel;
2479 hr_utility.trace('LEL after rollup='||ni_able_lel_tab(g_edi_ni_cat_count));
2480 END IF;
2481 --
2482 END IF; -- g_ni_multi_asg_flag = 'Y'
2483 -- Inreament category count for the employee
2484 g_edi_ni_cat_count := g_edi_ni_cat_count + 1;
2485 END LOOP;
2486 -- Initialize index
2487 g_edi_ni_cat_index := 0;
2488 -- Sum up ees and ers rebates accross employees
2489 g_tot_ers_rebate := g_tot_ers_rebate + g_edi_emp_ers_rebate;
2490 g_tot_ees_rebate := g_tot_ees_rebate + g_edi_emp_ees_rebate;
2491 -- Set flags to write NI details for this employee if NI
2492 -- categories exist else write employee trailer in next run
2493 IF g_edi_ni_cat_count > 0 THEN
2494 edi_process_emp_header := FALSE;
2495 edi_process_ni_details := TRUE;
2496 edi_process_emp_trailer := FALSE;
2497 ELSE
2498 edi_process_emp_header := FALSE;
2499 edi_process_ni_details := FALSE;
2500 edi_process_emp_trailer := TRUE;
2501 END IF;
2502 END IF; -- End of EDI employee header
2503 ELSIF edi_process_ni_details THEN
2504 -- Write NI details of the employee
2505 p14_edi_init(3);
2506 mag_tape_interface('EOY_MODE',g_eoy_mode);
2507 mag_tape_interface('NI_CATEGORY_CODE', category_tab(g_edi_ni_cat_index));
2508 --mag_tape_interface('SCON', nvl(scon_tab(g_edi_ni_cat_index), ' ')); --EOY 2012/2013
2509 mag_tape_interface('NI_ABLE_LEL', ni_able_lel_tab(g_edi_ni_cat_index));
2510 mag_tape_interface('NI_ABLE_ET', ni_able_et_tab(g_edi_ni_cat_index));
2511 mag_tape_interface('NI_ABLE_UEL', ni_able_uel_tab(g_edi_ni_cat_index));
2512 mag_tape_interface('NI_ABLE_AUEL', ni_able_auel_tab(g_edi_ni_cat_index)); ---EOY 07/08
2513 mag_tape_interface('TOTAL_CONTRIBUTIONS',total_contrib_tab(g_edi_ni_cat_index));
2514 mag_tape_interface('EMPLOYEE_CONTRIBUTIONS',
2515 employees_contrib_tab(g_edi_ni_cat_index));
2516 mag_tape_interface('GENDER', nvl(g_sex, ' '));
2517 mag_tape_interface('EMPLOYEE_NUMBER',NVL(g_employee_number,' '));
2518 mag_tape_interface('NI_CATEGORY_INDEX',NVL(to_char(g_edi_ni_cat_index+1),' '));
2519 mag_tape_interface('ROLLUP_NI_CAT',NVL(g_rollup_ni_cat,' '));
2520 --mag_tape_interface('ROLLUP_NI_SCON',NVL(g_rollup_scon,' ')); --EOY 2012/2013
2521 mag_tape_interface('ROLLUP_LEL_NI_CAT',NVL(g_rollup_lel_ni_cat,' '));
2522 mag_tape_interface('NI_MULTI_ASG_FLAG',NVL(g_ni_multi_asg_flag,' '));
2523 mag_tape_interface('DIRECTOR_INDICATOR', nvl(g_director_indicator, ' '));
2524 mag_tape_interface('FULL_NAME',NVL(g_full_name,' '));
2525 mag_tape_interface('NI_ABLE_UAP', ni_able_uap_tab(g_edi_ni_cat_index));
2526 mag_tape_interface('TAX_YEAR',g_tax_year); -- Added Tax Year, NI_NEW_TAX_YEAR input parameter. EOY 2011/12. Bug 12694562.
2527 mag_tape_interface('NI_LATEST_TAX_YEAR', l_ni_tax_year);
2528 --
2529 -- 10188309 removed this conditional check and moved it above unconditionally.
2530 -- 8357870 begin
2531 /* 8816832 EOY 09/10 Changed if condition
2532 -- if trunc(g_end_year) > trunc(l_ni_tax_year) then
2533 To support EOY validations for both 2008-09 and 2009-10 financial years, the below
2534 if structure is required. Later the if structure can be removed and the procedure
2535 call could be made unconditional.
2536 if g_tax_year <> '2009' then
2537 mag_tape_interface('NI_ABLE_UAP', ni_able_uap_tab(g_edi_ni_cat_index));
2538 end if;*/
2539 -- 8357870 end
2540 g_edi_ni_Cat_index := g_edi_ni_cat_index + 1;
2541 --
2542 -- Check if all NI categories have been written then
2543 -- prepare to write employee trailer record
2544 IF g_edi_ni_cat_index > g_edi_ni_cat_count-1 THEN
2545 g_edi_ni_cat_index := 0;
2546 g_edi_ni_cat_count := 0;
2547 edi_process_emp_header := FALSE;
2548 edi_process_ni_details := FALSE;
2549 edi_process_emp_trailer := TRUE;
2550 END IF; -- End of employee NI details
2551 ELSIF edi_process_emp_trailer THEN
2552 -- Write trailer record of the employee
2553 p14_edi_init(4);
2554 mag_tape_interface('EOY_MODE', g_eoy_mode);
2555 -- mag_tape_interface('NI_ERS_REBATE', g_edi_emp_ers_rebate); --P14 EDI 2003/2004
2556 -- mag_tape_interface('NIEES_REBATE', g_edi_emp_ees_rebate); --P14 EDI 2003/2004
2557 mag_tape_interface('SSP', g_ssp);
2558 g_tot_ssp_rec := g_tot_ssp_rec + g_ssp; -- 4011263
2559 mag_tape_interface('SMP', g_smp);
2560 g_tot_smp_rec := g_tot_smp_rec + g_smp; -- 4011263
2561 g_spp := nvl(l_spp_birth,0) + nvl(l_spp_adopt,0); --P35/P14 EOY 2003/2004
2562 mag_tape_interface('SPP', g_spp); --P35/P14 EOY 2003/2004
2563 g_tot_spp_rec := g_tot_spp_rec + g_spp; -- 4011263
2564 -- EOY 12/13
2565 g_aspp := nvl(l_aspp_birth,0) + nvl(l_aspp_adopt,0);
2566 mag_tape_interface('ASPP', g_aspp); -- Included Additional Paternity Pay
2567 g_tot_aspp_rec := g_tot_aspp_rec + g_aspp; --
2568
2569 mag_tape_interface('SAP', g_sap); --P35/P14 EOY 2003/2004
2570 g_tot_sap_rec := g_tot_sap_rec + g_sap; -- 4011263
2571 -- 4011263: mag_tape_interface('GROSS_PAY', g_gross_pay);
2572 mag_tape_interface('TAX_PAID', g_tax_paid);
2573 mag_tape_interface('TAX_REFUND', g_tax_refund);
2574 mag_tape_interface('PREVIOUS_TAXABLE_PAY', g_previous_taxable_pay);
2575 mag_tape_interface('PREVIOUS_TAX_PAID', g_previous_tax_paid);
2576 /* 4011263: Remove superannuation from EOY
2577 --added nvl for bug fix 3614251
2578 mag_tape_interface('SUPERANNUATION', nvl(g_superannuation_paid,0));
2579 mag_tape_interface('SUPERANNUATION_REFUND', g_superannuation_refund);
2580 4011263 */
2581 mag_tape_interface('WIDOWS_ORPHANS', g_widows_and_orphans);
2582 mag_tape_interface('STUDENT_LOANS', g_student_loans);
2583 mag_tape_interface('TAXABLE_PAY', g_taxable_pay);
2584 mag_tape_interface('DATE_OF_BIRTH', g_date_of_birth);
2585 mag_tape_interface('DATE_OF_STARTING', g_start_of_emp);
2586 -- Pass termination date to the formula only if it is in current tax year
2587 IF TO_DATE(g_termination_date,'DDMMYYYY') BETWEEN
2588 g_start_year and g_end_year THEN
2589 mag_tape_interface('TERMINATION_DATE', g_termination_date);
2590 ELSE
2591 mag_tape_interface('TERMINATION_DATE', ' ');
2592 END IF;
2593 mag_tape_interface('TAX_CODE', g_tax_code);
2594 mag_tape_interface('W1_M1', g_w1_m1_indicator);
2595 mag_tape_interface('EMPLOYEE_NUMBER', g_employee_number);
2596 mag_tape_interface('GENDER', g_sex);
2597 mag_tape_interface('ASSIGNMENT_ID', g_assignment_id);
2598 mag_tape_interface('FULL_NAME',NVL(g_full_name,' '));
2599 mag_tape_interface('TOT_NI_ABLE_LEL',NVL(g_emp_tot_lel,0));
2600 mag_tape_interface('TOT_NI_ABLE_ET',NVL(g_emp_tot_et,0));
2601 mag_tape_interface('TOT_NI_ABLE_UEL',NVL(g_emp_tot_uel,0));
2602 mag_tape_interface('TOT_EE_CONTRIB',NVL(g_emp_tot_ee_contrib,0));
2603 mag_tape_interface('TOT_EE_ER_CONTRIB',NVL(g_emp_tot_ee_er_contrib,0));
2604 mag_tape_interface('NI_NO', nvl(g_national_insurance_number, ' ')); -- 6281170
2605 -- 8816832 EOY 09/10 Changes, 2 additional parameters included
2606 mag_tape_interface('TOT_NI_ABLE_UAP',NVL(g_emp_tot_uap,0));
2607 mag_tape_interface('TAX_YEAR',g_tax_year); -- Added Tax Year, NI_NEW_TAX_YEAR input parameter. EOY 2011/12. Bug 12694562.
2608 mag_tape_interface('NI_LATEST_TAX_YEAR', l_ni_tax_year);
2609 -- 8816832 EOY 09/10 Changes End
2610 edi_process_emp_header := TRUE;
2611 edi_process_ni_details := FALSE;
2612 edi_process_emp_trailer := FALSE;
2613 ELSIF fin_run THEN
2614 --
2615 -- Start the end of tape procedure.
2616 --
2617 --
2618 submit_recon_report(p_payroll_action_id => g_payroll_action_id,
2619 p_p35_req_id => l_p35_req_id);
2620 l_type2_errors := to_number(pay_mag_tape.internal_prm_values(4));
2621 l_type1_errors := to_number(pay_mag_tape.internal_prm_values(3));
2622 l_char_errors := to_number(pay_mag_tape.internal_prm_values(5));
2623 l_loc_per := to_number(g_tot_rec2)/200; -- Half percent.
2624 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',600);
2625 --
2626 -- Check for type 1 and type 2 errors. Similar to MAG_RECORD5 checks.
2627 --
2628 if l_type1_errors > 0 -- Type 1 errors
2629 or (to_number(g_tot_rec2) = 0) -- No recs processed
2630 or (g_eoy_mode in ('F', 'P')
2631 AND ((l_type2_errors > 5 and l_type2_errors > l_loc_per)
2632 or l_type2_errors > 200)) -- Too many type2s in Mag Tape
2633 or (g_eoy_mode in ('F','F - P14 EDI') and l_char_errors > 0) -- Full Mode with
2634 -- Illegal chars.
2635 then
2636 -- error raised, do not submit reports.
2637 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',602);
2638 NULL;
2639 else
2640 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',605);
2641 -- No errors, so submit the reports.
2642 submit_reports(p_payroll_action_id => g_payroll_action_id,
2643 p_eoy_mode => g_eoy_mode,
2644 p_mar_req_id => l_mar_req_id);
2645 end if;
2646 --
2647 -- Write footer to the output report
2648 l_dummy_number := pay_gb_eoy_archive.write_output_footer;
2649 --
2650 hr_utility.trace('P35 Req ID: '||l_p35_req_id);
2651 hr_utility.trace('Writing record type 4');
2652 --
2653 IF g_eoy_mode in ('F - P14 EDI', 'P - P14 EDI') THEN
2654 p14_edi_init(6);
2655 ELSE
2656 mag_tape_init(5);
2657 END IF;
2658 mag_tape_interface('EOY_MODE',g_eoy_mode);
2659 mag_tape_interface('TOTAL_RECORDS',g_tot_rec2);
2660 mag_tape_interface('P35_REQUEST_ID',l_p35_req_id);
2661 mag_tape_interface('MAR_REQUEST_ID',l_mar_req_id);
2662 hr_utility.trace('The tot record is '||to_char(g_tot_rec2));
2663 IF g_eoy_mode NOT in ('F - P14 EDI', 'P - P14 EDI') THEN
2664 mag_tape_interface('END_OF_DATA','END OF DATA');
2665 END IF;
2666 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',610);
2667 IF header_cur%ISOPEN THEN
2668 CLOSE header_cur;
2669 END IF;
2670 IF emps_cur%ISOPEN THEN
2671 CLOSE emps_cur;
2672 END IF;
2673 END IF;
2674 hr_utility.set_location('pay_gb_eoy_magtape.eoy_control',999);
2675 END;
2676 --
2677 --------------------------------------------------------------------------
2678 -- Function: validate_input
2679 -- Description: Validate the passed-in formula input, called from the
2680 -- MAG_RECORD2 and MAG_RECORD1 formulae. This returns a
2681 -- 1 if invalid or 0 if valid; Boolean expressions are
2682 -- incompatible with FF.
2683 -- Also used by EDI processes to validate character set.
2684 --------------------------------------------------------------------------
2685 --
2686 function validate_input(p_input_value varchar2,
2687 p_validate_mode varchar2 default 'FULL_CHAR')
2688 return number is
2689 --
2690 l_valid number := 0;
2691 l_invalid_char constant varchar2(1) := '~'; -- required for translate
2692 l_char_chk constant varchar2(26) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
2693 l_number_chk constant varchar2(10) := '0123456789';
2694 l_extra_name_chk constant varchar2(7) := ''''||'- .'; -- Allowed in names
2695 l_all_name_chars varchar2(33);
2696 l_translated_value varchar2(200); -- Required to output failing char.
2697 l_all_extras_chk constant varchar2(10):= ''''||'/-,.&)( '; -- Full magtape set
2698 l_all_allowed_chars varchar2(50);
2699 l_alpha_numeric varchar2(36);
2700 l_extended_edi constant varchar2(36) := '/-,.&)( !"%*;<>=';
2701 l_paye_ref_chars constant varchar2(26) := '.*-()&'''; -- Emplr PAYE Ref
2702 l_mix_chars constant varchar2(52) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
2703 l_p14_extended_edi constant varchar2(36) := ' .,-()/=!"%&*;<>''+:?';
2704 l_p14_edi_surname constant varchar2(36) := ' .,-()/&''';
2705 l_p14_edi_forename constant varchar2(36) := '-''';
2706 l_p11d_edi constant varchar2(26) := '/-,.''&)( ';
2707 --l_scon_fixed_value number := 51 ; --EOY 2012/2013
2708 --l_scon_value number := 0 ; --EOY 2012/2013
2709 --l_scon_mod_chk_string constant varchar2(19) := 'ABCDEFHJKLMNPQRTWXY' ; --EOY 2012/2013
2710 --For bug 7540858: Added space as a valid character
2711 l_p45_46_first_name_chk2 constant varchar2(10) := '''.'||'- ';/*added for P45PT3*/
2712 l_p45_46_title_chk constant varchar2(10) := '''.'||'- '; /*added for P45PT3*/
2713 l_p45_46_postcode_chk constant varchar2(10) := ' '; /*added for P45PT3*/
2714 l_p45_46_last_name constant varchar2(10) := ''''||'- '; -- Bug 8439388
2715 p_input_value_temp varchar2(35) ;
2716 --
2717 BEGIN
2718 --
2719 hr_utility.trace('Entering pay_gb_eoy_magetape.validate_input');
2720 hr_utility.trace('p_validate_mode='||p_validate_mode);
2721 hr_utility.trace('p_input_value='||p_input_value);
2722 --
2723 if p_validate_mode = 'FULL_EDI' then
2724 -- ensure characters exist in the EDI character set
2725 l_translated_value :=
2726 translate(p_input_value,
2727 l_invalid_char||l_char_chk||l_number_chk||l_extended_edi,
2728 l_invalid_char);
2729 --
2730 if l_translated_value is not null then
2731 hr_utility.trace('Invalid chars found: '||l_translated_value);
2732 l_valid := 1; -- Not valid
2733 else
2734 l_valid := 0; -- Valid
2735 end if;
2736 --
2737 elsif p_validate_mode = 'EDI_SURNAME' then
2738 -- ensure characters exist in the EDI character set
2739 -- Surname can additionally contain apostrophe
2740 l_translated_value :=
2741 translate(p_input_value,
2742 l_invalid_char||l_char_chk||l_number_chk||l_extended_edi||'''',
2743 l_invalid_char);
2744 --
2745 if l_translated_value is not null then
2746 hr_utility.trace('Invalid chars found: '||l_translated_value);
2747 l_valid := 1; -- Not valid
2748 else
2749 l_valid := 0; -- Valid
2750 end if;
2751 --
2752 elsif p_validate_mode = 'NUMBER' then
2753 --
2754 -- Ensure that the input value passed in is a number
2755 --
2756 l_translated_value := translate(p_input_value,
2757 l_invalid_char||l_number_chk,
2758 l_invalid_char);
2759 --
2760 if l_translated_value is not null then
2761 hr_utility.trace('Invalid chars found: '||l_translated_value);
2762 l_valid := 1; -- Not valid
2763 else
2764 l_valid := 0; -- Valid
2765 end if;
2766 --
2767 elsif p_validate_mode = 'NUMBER_1' then
2768 --
2769 -- Ensure that the input value passed in is a number
2770 --
2771 -- Remove leading minus sign if present.
2772 --
2773 if substr(p_input_value,1,1) = '-' then
2774 p_input_value_temp := substr(p_input_value,2) ;
2775 end if ;
2776 l_translated_value := translate(p_input_value_temp,
2777 l_invalid_char||l_number_chk||'.',
2778 l_invalid_char);
2779 --
2780 if l_translated_value is not null then
2781 hr_utility.trace('Invalid chars found: '||l_translated_value);
2782 l_valid := 1; -- Not valid
2783 else
2784 l_valid := 0; -- Valid
2785 end if;
2786 elsif p_validate_mode = 'CHAR' then
2787 --
2788 -- Ensure that the input value passed in is in the range A-Z
2789 --
2790 l_translated_value := translate(p_input_value,
2791 l_invalid_char||l_char_chk,
2792 l_invalid_char);
2793 if l_translated_value is not null then
2794 hr_utility.trace('Invalid chars found: '||l_translated_value);
2795 l_valid := 1;
2796 else
2797 l_valid := 0;
2798 end if;
2799 --
2800 elsif p_validate_mode = 'ALPHA_NUM' then
2801 --
2802 -- Ensure that the input value passed in is A-Z or 0-9
2803 --
2804 l_alpha_numeric := l_char_chk||l_number_chk;
2805 l_translated_value := translate(p_input_value,
2806 l_invalid_char||l_alpha_numeric,
2807 l_invalid_char);
2808 --
2809 if l_translated_value is not null then
2810 hr_utility.trace('Invalid chars found: '||l_translated_value);
2811 l_valid := 1; -- Not valid
2812 else
2813 l_valid := 0; -- Valid
2814 end if;
2815 --
2816 elsif p_validate_mode = 'MIXED_CHAR_ALPHA_NUM' then
2817 --
2818 -- Ensure that the input value passed in is A-Z or a-z or 0-9
2819 --
2820 l_translated_value := translate(p_input_value,
2821 l_invalid_char||l_mix_chars||l_number_chk,
2822 l_invalid_char);
2823 --
2824 if l_translated_value is not null then
2825 hr_utility.trace('Invalid chars found: '||l_translated_value);
2826 l_valid := 1; -- Not valid
2827 else
2828 l_valid := 0; -- Valid
2829 end if;
2830 --
2831 elsif p_validate_mode = 'SCON' or p_validate_mode = 'ECON' then
2832 --
2833 -- The first character of SCON must be 'S', the first char of
2834 -- ECON must be 'E'. The following 7 characters must be numeric,
2835 -- and the final character must be a
2836 -- letter. Note 2 different error codes must be returned denoting
2837 -- whether the scon or econ is empty or invalid.
2838 -- The value passed in should be 9 characters.
2839 --
2840 if ltrim(p_input_value) is null then
2841 l_valid := 1;
2842 elsif substr(p_input_value,1,1) <> substr(p_validate_mode,1,1)
2843 or length(p_input_value) <> 9 then
2844 --
2845 l_valid := 2;
2846 else
2847 if translate(substr(p_input_value,2,7),l_invalid_char||l_number_chk,
2848 l_invalid_char)
2849 is not null
2850 then
2851 l_valid := 2;
2852 else
2853 if instr(l_char_chk,substr(p_input_value,9,1)) = 0 then
2854 l_valid := 2;
2855 else
2856 l_valid := 0; -- All checks done, c.o. number is valid at this point
2857 end if;
2858
2859 /*EOY 2012/2013 begin block*/
2860 /*if p_validate_mode = 'SCON' then
2861 if not(substr(p_input_value,2,1) in ('0','1','2','4','6','8')) then
2862 l_valid := 2 ;
2863 end if;
2864
2865 l_scon_value := l_scon_fixed_value ;
2866 for j in 2..(length(p_input_value) -1 ) loop
2867 l_scon_value := l_scon_value + (to_number( substr(p_input_value,j,1) ))*(8+2-j) ;
2868 end loop;
2869
2870 if substr( l_scon_mod_chk_string, mod(l_scon_value,19)+1, 1)<> substr(p_input_value,9,1) then
2871 l_valid := 2;
2872 end if;
2873 end if; */
2874 /*EOY 2012/2013 end block*/
2875
2876 end if;
2877 end if;
2878 --
2879 elsif p_validate_mode = 'NAME' then
2880 --
2881 -- For All names, extra characters are allowed
2882 -- for all but the first character, which must be A-Z.
2883 -- Return a 1 if an Invalid character, or a 2 if an Illegal one.
2884 --
2885 l_all_name_chars := l_char_chk||l_extra_name_chk;
2886 l_all_allowed_chars := l_char_chk||l_number_chk||l_all_extras_chk;
2887 --
2888 if not substr(p_input_value,1,1) between 'A' and 'Z' then
2889 --
2890 -- First char invalid
2891 --
2892 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
2893 l_valid := 1;
2894 else
2895 l_translated_value :=
2896 translate(p_input_value,l_invalid_char||l_all_name_chars,
2897 l_invalid_char);
2898 if l_translated_value is not null then
2899 hr_utility.trace('Invalid chars found: '||l_translated_value);
2900 l_valid := 1;
2901 --
2902 -- Now check for Illegal chars
2903 --
2904 l_translated_value :=
2905 translate(p_input_value,
2906 l_invalid_char||l_all_allowed_chars,
2907 l_invalid_char);
2908 if l_translated_value is not null then
2909 hr_utility.trace('Illegal chars found: '||l_translated_value);
2910 l_valid := 2;
2911 end if;
2912 else
2913 l_valid := 0;
2914 end if;
2915 --
2916 end if;
2917 --
2918 -- This mode is the default
2919 --
2920 elsif p_validate_mode = 'FULL_CHAR' then
2921 --
2922 -- Check all characters in the allowable set
2923 --
2924 l_all_allowed_chars := l_char_chk||l_number_chk||l_all_extras_chk;
2925 --
2926 l_translated_value :=
2927 translate(p_input_value,l_invalid_char||l_all_allowed_chars,
2928 l_invalid_char);
2929 if l_translated_value is not null then
2930 hr_utility.trace('Invalid chars found: '||l_translated_value);
2931 l_valid := 1;
2932 else
2933 l_valid := 0;
2934 end if;
2935 --
2936 elsif p_validate_mode = 'EDI_NAME' then
2937 --
2938 -- Check for Valid First Char
2939 --
2940 if not substr(p_input_value,1,1) between 'A' and 'Z' then
2941 --
2942 -- First char invalid
2943 --
2944 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
2945 l_valid := 2;
2946 else
2947 -- ensure characters exist in the EDI character set
2948 -- Surname can additionally contain apostrophe
2949 l_translated_value :=
2950 translate(p_input_value,l_invalid_char||l_char_chk||l_number_chk||l_extended_edi||'''',
2951 l_invalid_char);
2952
2953 if l_translated_value is not null then
2954 hr_utility.trace('Invalid chars found: '||l_translated_value);
2955 l_valid := 1; -- Not valid
2956 else
2957 l_valid := 0; -- Valid
2958 end if;
2959 end if;
2960 elsif p_validate_mode = 'PAYE_REF' then
2961
2962 l_translated_value := translate(p_input_value,
2963 l_invalid_char||l_mix_chars||l_number_chk||l_paye_ref_chars,
2964 l_invalid_char);
2965 if l_translated_value is not null then
2966 hr_utility.trace('Invalid chars found: '||l_translated_value);
2967 l_valid := 1; -- Not valid
2968 else
2969 l_valid := 0; -- Valid
2970 end if;
2971 elsif p_validate_mode = 'P14_FULL_EDI' then
2972 -- ensure characters exist in the EDI character set
2973 l_translated_value :=
2974 translate(p_input_value,
2975 l_invalid_char||l_mix_chars||l_number_chk||l_p14_extended_edi,
2976 l_invalid_char);
2977 --
2978 if l_translated_value is not null then
2979 hr_utility.trace('Invalid chars found: '||l_translated_value);
2980 l_valid := 1; -- Not valid
2981 else
2982 l_valid := 0; -- Valid
2983 end if;
2984 --
2985 elsif p_validate_mode = 'P14_EDI_SURNAME' then
2986 --
2987 -- Check for Valid First Char
2988 --
2989 if not (substr(p_input_value,1,1) between 'A' and 'Z'
2990 or substr(p_input_value,1,1) between 'a' and 'z') then
2991 --
2992 -- First char invalid
2993 --
2994 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
2995 l_valid := 2;
2996 else
2997 -- ensure characters exist in the EDI character set
2998 -- Surname can additionally contain apostrophe
2999 l_translated_value :=
3000 translate(p_input_value,
3001 l_invalid_char||l_mix_chars||l_number_chk||l_p14_edi_surname,
3002 l_invalid_char);
3003
3004 if l_translated_value is not null then
3005 hr_utility.trace('Invalid chars found: '||l_translated_value);
3006 l_valid := 1; -- Not valid
3007 else
3008 l_valid := 0; -- Valid
3009 end if;
3010 end if;
3011 --
3012 elsif p_validate_mode = 'P14_EDI_FORENAME' then
3013 --
3014 -- Check for Valid First Char
3015 --
3016 if not (substr(p_input_value,1,1) between 'A' and 'Z'
3017 or substr(p_input_value,1,1) between 'a' and 'z') then
3018 --
3019 -- First char invalid
3020 --
3021 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
3022 l_valid := 2;
3023 else
3024 -- ensure characters exist in the EDI character set
3025 -- Surname can additionally contain apostrophe
3026 l_translated_value :=
3027 translate(p_input_value,
3028 l_invalid_char||l_mix_chars||l_p14_edi_forename,
3029 l_invalid_char);
3030
3031 if l_translated_value is not null then
3032 hr_utility.trace('Invalid chars found: '||l_translated_value);
3033 l_valid := 1; -- Not valid
3034 else
3035 l_valid := 0; -- Valid
3036 end if;
3037 end if;
3038 --
3039 elsif p_validate_mode = 'P14_EDI_ADDRESS' then
3040 --
3041 -- Check for Valid First Char
3042 --
3043 /* Bug start 8338575 Commented the first character validation for address lines */
3044 /*if not (substr(p_input_value,1,1) between 'A' and 'Z'
3045 or substr(p_input_value,1,1) between 'a' and 'z'
3046 or substr(p_input_value,1,1) between '0' and '9') then
3047 --
3048 -- First char invalid
3049 --
3050 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
3051 l_valid := 2;
3052 else*/
3053 -- ensure characters exist in the EDI character set
3054 -- Surname can additionally contain apostrophe
3055 l_translated_value :=
3056 translate(p_input_value,
3057 l_invalid_char||l_mix_chars||l_number_chk||l_p14_extended_edi,
3058 l_invalid_char);
3059
3060 if l_translated_value is not null then
3061 hr_utility.trace('Invalid chars found: '||l_translated_value);
3062 l_valid := 1; -- Not valid
3063 else
3064 l_valid := 0; -- Valid
3065 end if;
3066 -- end if;
3067 --
3068 --
3069 elsif p_validate_mode = 'P11D_EDI' then
3070 --
3071 l_translated_value :=
3072 translate(p_input_value,
3073 l_invalid_char||l_char_chk||l_number_chk||l_p11d_edi||'''',
3074 l_invalid_char);
3075 --
3076 if l_translated_value is not null then
3077 hr_utility.trace('Invalid chars found: '||l_translated_value);
3078 l_valid := 1; -- Not valid
3079 else
3080 l_valid := 0; -- Valid
3081 end if;
3082 --
3083 /*addition for P45PT3/P46 starts. Bug 6345375*/
3084 elsif p_validate_mode = 'P45_46_FIRST_NAME' then
3085 if ( not substr(p_input_value,1,1) between 'A' and 'Z' and
3086 not substr(p_input_value,1,1) between 'a' and 'z' ) then
3087 --
3088 -- First char invalid
3089 --
3090 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
3091 l_valid := 2;
3092 else
3093 l_translated_value :=
3094 translate(p_input_value,
3095 l_invalid_char||l_mix_chars||l_p45_46_first_name_chk2,
3096 l_invalid_char);
3097
3098 if l_translated_value is not null then
3099 hr_utility.trace('Invalid chars found: '||l_translated_value);
3100 l_valid := 1; -- Not valid
3101 else
3102 l_valid := 0; -- Valid
3103 end if;
3104 end if ;
3105 --
3106 elsif p_validate_mode = 'P45_46_TITLE' then
3107 if ( not substr(p_input_value,1,1) between 'A' and 'Z' and
3108 not substr(p_input_value,1,1) between 'a' and 'z' ) then
3109 --
3110 -- First char invalid
3111 --
3112 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
3113 l_valid := 2;
3114 else
3115 l_translated_value :=
3116 translate(p_input_value,
3117 l_invalid_char||l_mix_chars||l_p45_46_title_chk,
3118 l_invalid_char);
3119
3120 if l_translated_value is not null then
3121 hr_utility.trace('Invalid chars found: '||l_translated_value);
3122 l_valid := 1; -- Not valid
3123 else
3124 l_valid := 0; -- Valid
3125 end if;
3126 end if ;
3127 --
3128 elsif p_validate_mode = 'P45_46_POSTCODE' then
3129 --
3130 -- Ensure that the input value passed in is A-Z or a-z or 0-9 or spaces
3131 --
3132 l_translated_value := translate(p_input_value,
3133 l_invalid_char||l_mix_chars||l_number_chk||l_p45_46_postcode_chk,
3134 l_invalid_char);
3135 --
3136 if l_translated_value is not null then
3137 hr_utility.trace('Invalid chars found: '||l_translated_value);
3138 l_valid := 1; -- Not valid
3139 else
3140 l_valid := 0; -- Valid
3141 end if;
3142 --
3143 elsif p_validate_mode = 'P45_46_LAST_NAME' then
3144 if ( not substr(p_input_value,1,1) between 'A' and 'Z' and
3145 not substr(p_input_value,1,1) between 'a' and 'z' ) then
3146 --
3147 -- First char invalid
3148 --
3149 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
3150 l_valid := 2;
3151 else
3152 l_translated_value :=
3153 translate(p_input_value,
3154 l_invalid_char||l_mix_chars||l_p45_46_last_name,
3155 l_invalid_char);
3156
3157 if l_translated_value is not null then
3158 hr_utility.trace('Invalid chars found: '||l_translated_value);
3159 l_valid := 1; -- Not valid
3160 else
3161 l_valid := 0; -- Valid
3162 end if;
3163 end if;
3164 /*addition for P45PT3/P46 ends. Bug 6345375*/
3165 --
3166
3167 --Bug 8986543: Added for P46 Car EDI V3
3168 elsif p_validate_mode = 'P46_CAR_TIT_N_FSTNM' then
3169 -- Check for Valid First Char
3170 if not substr(p_input_value,1,1) between 'A' and 'Z' then
3171 -- First char invalid
3172 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
3173 l_valid := 2;
3174 else
3175 -- ensure characters exist in the EDI character set
3176 l_translated_value :=
3177 translate(p_input_value,l_invalid_char||l_char_chk||l_number_chk||l_extended_edi,
3178 l_invalid_char);
3179
3180 if l_translated_value is not null then
3181 hr_utility.trace('Invalid chars found: '||l_translated_value);
3182 l_valid := 1; -- Not valid
3183 else
3184 l_valid := 0; -- Valid
3185 end if;
3186 end if;
3187
3188 else
3189 --
3190 -- Invalid validate mode used.
3191 --
3192 hr_utility.trace('Invalid validate mode used: '||p_validate_mode);
3193 --
3194 end if;
3195 --
3196 return l_valid;
3197 end validate_input;
3198
3199 ---------------------------------------------------------------
3200 -- Function: validate_tax_code --
3201 -- Description: Used to validate tax codes by End Of Year --
3202 -- Calls hr_gb_utility.tax_code_validate but --
3203 -- when no error is found it will return ' ' --
3204 -- instead of Null so that fast formulae can --
3205 -- handle it. --
3206 ---------------------------------------------------------------
3207 FUNCTION validate_tax_code(p_tax_code in varchar2,
3208 p_effective_date in date,
3209 p_assignment_id in number)
3210 RETURN VARCHAR2 IS
3211 l_return_value VARCHAR2(250) := NULL;
3212 BEGIN
3213 --
3214 -- hr_utility.trace_on(null, 'RMEOYVTC');
3215 hr_utility.trace('Entering pay_gb_eoy_magtape.validate_tax_code.');
3216 hr_utility.trace('p_tax_code = '|| p_tax_code);
3217 hr_utility.trace('p_effective_date ='|| fnd_date.date_to_displaydate(p_effective_date));
3218 hr_utility.trace('p_assignment_id = '||p_assignment_id);
3219 l_return_value := hr_gb_utility.tax_code_validate(p_tax_code => p_tax_code,
3220 p_effective_date => p_effective_date,
3221 p_assignment_id => p_assignment_id);
3222 --
3223 hr_utility.trace('validate_tax_code: l_return_value='||l_return_value);
3224 IF l_return_value IS NULL THEN
3225 l_return_value := ' ';
3226 END IF;
3227 --
3228 -- hr_utility.trace_off;
3229 RETURN l_return_value;
3230 END validate_tax_code;
3231
3232 /********** added validate_tax_code_yrfil .... Abhgangu******/
3233
3234 FUNCTION validate_tax_code_yrfil(c_assignment_action_id in number,
3235 p_tax_code in varchar2,
3236 p_effective_date in date)
3237 return VARCHAR2 IS
3238 l_return_value VARCHAR2(250) := NULL;
3239 CURSOR csr_ass_id
3240 is
3241 select
3242 assignment_id
3243 from pay_assignment_actions
3244 where assignment_action_id = c_assignment_action_id;
3245
3246 l_assignment_id NUMBER;
3247
3248 BEGIN
3249 OPEN csr_ass_id;
3250 FETCH csr_ass_id INTO l_assignment_id;
3251 CLOSE csr_ass_id;
3252
3253 l_return_value := validate_tax_code(p_tax_code => p_tax_code,
3254 p_effective_date => p_effective_date,
3255 p_assignment_id => l_assignment_id);
3256 --
3257 hr_utility.trace('validate_tax_code: l_return_value='||l_return_value);
3258 return l_return_value;
3259 END validate_tax_code_yrfil;
3260
3261
3262 FUNCTION get_payroll_version
3263 RETURN VARCHAR2
3264 IS
3265 cursor csr_get_version
3266 is
3267 select ver.version from
3268 ad_file_versions ver, ad_files f
3269 where f.file_id = ver.file_id
3270 and f.filename = 'pygbffedi.hdt'
3271 order by ver.file_version_id desc;
3272
3273 l_version VARCHAR2(35);
3274 BEGIN
3275 open csr_get_version;
3276 fetch csr_get_version into l_version;
3277 if csr_get_version%notfound then
3278 l_version := ' ';
3279 end if;
3280 close csr_get_version;
3281 return l_version;
3282 END get_payroll_version;
3283
3284 END pay_gb_eoy_magtape;
[Home] [Help]