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