1 PACKAGE BODY pay_gb_eoy AS
2 /* $Header: payeoy.pkb 115.3 99/07/17 05:38:23 porting ship $ */
3 /* Copyright (c) Oracle Corporation 1995. All rights reserved
4
5 Name : PAYEOY
6 Description : End of year magnetic tape control process
7 Author : P.Driver
8 Date Created : 17/11/95
9
10 Change List
11 -----------
12 Date Name Vers Bug No Description
13
14 +-----------+---------------+--------+-------+-----------------------+
15 11-Dec-1995 P.Driver 1.2 System test fixes
16 07-MAY-1996 T.Inekuku Included formula_type_id
17 in get formula id routine
18 to make use of index
19
20 30-JUL-1996 J.Alloun Added error handling.
21
22 23-SEP-1996 M.Iqbal Fix for error in tax calc, and
23 super-ann truncating - BUG: 401646
24
25 24-Sep-1996 M.Iqbal NIP reporting added
26 19-Jun-1997 A.Parkes 40.11 545556 changes due to revised CA51
27 (MM3) Jan-97
28 25-Feb-1998 A.Parkes 40.12 632451 Removed numeric test on permit
29 number from procedure
30 create_record_type1
31 17-Mar-1998 A.Mills 110.4 641807 Created new function
32 check_special_char that
33 allows for middle names
34 to have chars (exc. 1st char)
35 like '-. This caused type 2
36 BACS error before.
37 11-DEC-98 R Simms 110.5 Changed l_assigment length
38 to match the table attribute
39 */
40 fetch_new_header BOOLEAN := TRUE; -- Shows if new header record needed
41 process_emps BOOLEAN := FALSE; -- Shows if get employees records
42 fin_run BOOLEAN := FALSE; -- End of run flag
43 sub_header BOOLEAN := FALSE; -- Create the record type2 sub
44 permit_change BOOLEAN := FALSE; -- set if the permit_no changes
45 process_dummy BOOLEAN := FALSE; -- Set if > 4 NI codes are found
46 g_ni_total NUMBER(3) := 0; -- Number of Ni codes found
47 g_last_ni NUMBER(3) := 0; -- Index through NI PL/SQL tables
48 --
49 g_permit_no VARCHAR2(12); -- The current permit number must be held
50 g_tax_dist_ref VARCHAR2(3) :=NULL;
51 g_business_group_id NUMBER(15):=NULL;
52 g_payroll_id NUMBER(15); -- The current payroll id held between
53 g_record_index NUMBER(2) := 0; -- Counter for mag tape parameters
54 g_tot_contribs NUMBER(11):=0; -- Total contribution by permit_no
55 g_tot_nip NUMBER(11):=0; -- Total NIP deductions
56 g_tot_tax NUMBER(12):=0; -- Total tax by permit_no
57 g_tot_rec2 NUMBER(7) :=0; -- Total of record 2's
58 g_tot_rec2_per NUMBER(7) :=0; -- Number of record 2's by permit_no
59 g_tot_ssp_rec NUMBER(10):=0; -- Total ssp recovered by permit_no
60 g_tot_smp_rec NUMBER(10):=0; -- Total smp recovered by permit_no
61 g_tot_smp_comp NUMBER(10):=0; -- Total smp compensated by permit_no
62 g_tot_super NUMBER(12):=0; -- Total superannuation by payroll
63 g_nic_comp NUMBER(9) :=0; -- Nic compensation on smp by permit
64 g_eoy_mode VARCHAR2(1):='P'; -- THE eoy mode defaults to partial
65 --
66 -- Record type 1 placeholders
67 g_new_permit_no VARCHAR2(12); -- The recently fetched permit number
68 g_new_payroll_id NUMBER(15); -- The recently fetched payroll id
69 g_tax_district_ref VARCHAR2(3);
70 g_tax_ref_no VARCHAR2(7);
71 g_tax_district_name VARCHAR2(40);
72 g_tax_year VARCHAR2(4);
73 g_employers_name VARCHAR2(36);
74 g_employers_address VARCHAR2(60);
75 g_econ VARCHAR2(9);
76 g_ssp_recovery NUMBER(10);
77 g_smp_recovery NUMBER(10);
78 g_smp_compensation NUMBER(10);
79 --
80 -- Record type 2 placeholders
81 g_employee_number VARCHAR2(14);
82 g_last_name VARCHAR2(20);
83 g_first_name VARCHAR2(7);
84 g_middle_name VARCHAR2(7);
85 g_date_of_birth VARCHAR2(8);
86 g_national_insurance_number VARCHAR2(9);
87 g_start_of_emp VARCHAR2(8);
88 g_termination_date VARCHAR2(8);
89 g_sex VARCHAR2(1);
90 g_address_line1 VARCHAR2(27);
91 g_address_line2 VARCHAR2(27);
92 g_address_line3 VARCHAR2(27);
93 g_address_line4 VARCHAR2(27);
94 g_full_address VARCHAR2(108); -- temp var used in address ordering
95 g_postal_code VARCHAR2(8);
96 --
97 /* PL/SQL table definitions */
98 --
99 TYPE scon_typ IS TABLE OF pay_gb_year_end_values.scon%TYPE
100 INDEX BY BINARY_INTEGER;
101 TYPE category_typ IS TABLE OF pay_gb_year_end_values.ni_category_code%TYPE
102 INDEX BY BINARY_INTEGER;
103 TYPE earnings_typ IS TABLE OF pay_gb_year_end_values.earnings%TYPE
104 INDEX BY BINARY_INTEGER;
105 TYPE total_contrib_typ IS TABLE OF
106 pay_gb_year_end_values.total_contributions%TYPE
107 INDEX BY BINARY_INTEGER;
108 TYPE emps_contrib_typ IS TABLE OF
109 pay_gb_year_end_values.employees_contributions%TYPE
110 INDEX BY BINARY_INTEGER;
111 TYPE earnings_cont_out_typ IS TABLE OF
112 pay_gb_year_end_values.earnings_contracted_out%TYPE
113 INDEX BY BINARY_INTEGER;
114 TYPE contribs_cont_out_typ IS TABLE OF
115 pay_gb_year_end_values.contributions_contracted_out%TYPE
116 INDEX BY BINARY_INTEGER;
117 --
118 scon_tab scon_typ;
119 category_tab category_typ;
120 earnings_tab earnings_typ;
121 total_contrib_tab total_contrib_typ;
122 employees_contrib_tab emps_contrib_typ;
123 earnings_cont_out_tab earnings_cont_out_typ;
124 contribs_cont_out_tab contribs_cont_out_typ;
125 --
126 -- Cursor definitions
127 --
128 CURSOR header_cur(cp_permit_no VARCHAR2
129 ,cp_tax_dist_ref VARCHAR2
130 ,cp_tax_ref_no VARCHAR2
131 ,cp_business_group_id NUMBER) IS
132 SELECT UPPER(a.permit_number)
133 ,a.payroll_id
134 ,TO_CHAR(a.tax_district_reference)
135 ,UPPER(a.tax_reference_number)
136 ,UPPER(a.tax_district_name)
137 ,NVL(TO_CHAR(a.tax_year-1),'?')
138 ,UPPER(a.employers_name)
139 ,UPPER(a.employers_address_line)
140 ,UPPER(NVL(a.econ,'?'))
141 ,nvl(ssp_recovered,0)
142 ,nvl(smp_recovered,0)
143 ,nvl(smp_compensation,0)
144 FROM pay_gb_year_end_payrolls a
148 AND a.tax_district_reference =
145 WHERE a.permit_number = NVL(cp_permit_no,a.permit_number)
146 AND a.tax_reference_number =
147 NVL(cp_tax_ref_no,a.tax_reference_number)
149 NVL(cp_tax_dist_ref,a.tax_district_reference)
150 AND a.business_group_id = NVL(cp_business_group_id,
151 a.business_group_id)
152 AND EXISTS (SELECT '1'
153 FROM pay_gb_year_end_assignments b
154 WHERE a.payroll_id = b.payroll_id)
155 ORDER BY a.permit_number,a.payroll_id;
156 --
157 CURSOR emps_cur(cp_payroll_id NUMBER) IS
158 SELECT SUBSTR(UPPER(a.assignment_number),1,14)
159 ,NVL(UPPER(a.last_name),' ')
160 ,UPPER(SUBSTR(a.first_name,1,7))
161 ,UPPER(a.middle_name)
162 ,NVL(fnd_date.date_to_canonical(a.date_of_birth),' ')
163 ,NVL(UPPER(a.sex),' ')
164 ,decode(a.address_line1,'','',rpad(upper(a.address_line1),27))
165 ,decode(a.address_line2,'','',rpad(upper(a.address_line2),27))
166 ,ltrim(rpad(UPPER(NVL(a.town_or_city,a.address_line3)),27))
167 ,decode(a.county,'','',rpad(UPPER(a.county),27))
168 ,NVL(UPPER(a.postal_code),' ')
169 ,NVL(UPPER(a.tax_code),' ')
170 ,NVL(UPPER(a.w1_m1_indicator),' ')
171 ,NVL(UPPER(a.national_insurance_number), ' ')
172 ,NVL(a.ssp,0)
173 ,NVL(a.smp,0)
174 ,NVL(a.gross_pay,0)
175 ,decode(a.tax_refund, 'R', NVL(-1*a.tax_paid,0), NVL(a.tax_paid,0))
176 ,NVL(UPPER(a.tax_refund),' ')
177 ,NVL(a.previous_taxable_pay,0)
178 ,NVL(a.previous_tax_paid,0)
179 ,NVL(fnd_date.date_to_canonical(a.start_of_emp),' ')
180 ,fnd_date.date_to_canonical(a.termination_date)
181 ,100*(NVL(TRUNC(a.superannuation_paid/100),0))
182 ,NVL(UPPER(a.superannuation_refund),' ')
183 ,NVL(ROUND(a.widows_and_orphans/100),0)
184 ,NVL(UPPER(a.week_53_indicator),' ')
185 ,NVL(a.taxable_pay,0)
186 ,NVL(UPPER(a.pensioner_indicator),' ')
187 ,NVL(UPPER(a.director_indicator),' ')
188 ,a.assignment_id
189 ,a.effective_end_date
190 FROM pay_gb_year_end_assignments a
191 WHERE a.payroll_id = cp_payroll_id
192 ORDER BY a.last_name, a.first_name;
193 --
194 CURSOR emp_values(cp_assignment_id NUMBER
195 ,cp_effective_date DATE) IS
196 SELECT NVL(UPPER(a.scon),' ') scon
197 ,UPPER(a.ni_category_code) cat_code
198 ,NVL(TRUNC(a.earnings/100),0) earnings
199 ,NVL(a.total_contributions,0) tot_cont
200 ,NVL(a.employees_contributions,0) emps_cont
201 ,NVL(TRUNC(a.earnings_contracted_out/100),0) earnings_out
202 ,NVL(a.contributions_contracted_out,0) cont_out
203 FROM pay_gb_year_end_values a
204 WHERE a.assignment_id = cp_assignment_id
205 AND a.effective_end_date = cp_effective_date
206 AND a.reportable <> 'N';
207 --
208 CURSOR econ_chk(cp_permit_no VARCHAR2
209 ,cp_tax_dist_ref VARCHAR2
210 ,cp_tax_ref_no VARCHAR2
211 ,cp_business_group_id NUMBER) IS
215 AND (a.assignment_id,a.effective_end_date) IN
212 SELECT '1'
213 FROM pay_gb_year_end_values a
214 WHERE a.ni_category_code IN ('D','E')
216 (SELECT b.assignment_id,b.effective_end_date
217 FROM pay_gb_year_end_assignments b
218 ,pay_gb_year_end_payrolls c
219 WHERE c.permit_number = NVL(cp_permit_no, c.permit_number)
220 AND c.tax_reference_number =
221 NVL(cp_tax_ref_no,c.tax_reference_number)
222 AND c.tax_district_reference =
223 NVL(cp_tax_dist_ref,c.tax_district_reference)
224 AND c.business_group_id = NVL(cp_business_group_id,
225 c.business_group_id)
226 AND c.payroll_id = b.payroll_id
227 AND rownum < 5);
228 --
229 FUNCTION get_formula_id(p_formula_name VARCHAR2) RETURN INTEGER IS
230 -- Get the formula id from the formula name
231 p_formula_id INTEGER;
232 CURSOR form IS SELECT a.formula_id
233 FROM ff_formulas_f a,
234 ff_formula_types t
235 WHERE a.formula_name = p_formula_name
236 AND a.formula_type_id = t.formula_type_id
237 AND t.formula_type_name = 'Oracle Payroll';
238 BEGIN
239 OPEN form;
240 FETCH form INTO p_formula_id;
241 CLOSE form;
242 RETURN p_formula_id;
243 END;
244 --
245 FUNCTION check_number(p_check_digit CHAR) RETURN BOOLEAN IS
246 BEGIN
247 IF p_check_digit BETWEEN '0' AND '9' THEN
248 RETURN TRUE;
249 ELSE
250 RETURN FALSE;
251 END IF;
252 END;
253 --
254 FUNCTION check_char(p_check_digit CHAR) RETURN BOOLEAN IS
255 BEGIN
256 IF p_check_digit BETWEEN 'A' AND 'Z' THEN
257 RETURN TRUE;
258 ELSE
259 RETURN FALSE;
260 END IF;
261 END;
262 --
263 FUNCTION check_special_char(p_check_digit CHAR) RETURN BOOLEAN IS
264 BEGIN
265 IF p_check_digit BETWEEN 'A' AND 'Z'
266 OR p_check_digit in ('''', '-', '.') THEN
267 RETURN TRUE;
268 ELSE
269 RETURN FALSE;
270 END IF;
271 END;
272 --
273 FUNCTION f_middle_name(p_middle_name VARCHAR2) RETURN VARCHAR2 IS
274 l_index NUMBER(2):=1;
275 l_local_char VARCHAR2(1);
276 l_middle_name VARCHAR2(7);
277 BEGIN
278 -- Set the return variable at the start
279 -- It may be overwritten if validation fails
280 l_middle_name := p_middle_name;
281 l_local_char := SUBSTR(l_middle_name,l_index,1);
282 IF (NOT check_char(l_local_char)) AND l_local_char <> ' ' THEN
283 -- first char is not a character or space
284 l_middle_name := '?';
285 hr_utility.set_location('eoy_body.eoy',270);
286 ELSIF l_local_char = ' ' AND LENGTH(p_middle_name) > 1 THEN
287 -- Proceed to 2nd character
288 l_index := 2;
289 WHILE l_middle_name <> '?' AND l_index <= 7 LOOP
290 -- Check up to the seventh character
291 hr_utility.set_location('eoy_body.eoy',271);
292 l_local_char := SUBSTR(p_middle_name,l_index,1);
293 IF l_local_char <> ' ' THEN
294 -- Character is not a space so fail
295 hr_utility.trace('The failed char is '||l_local_char);
296 l_middle_name := '?';
297 hr_utility.set_location('eoy_body.eoy',272);
298 END IF;
299 l_index := l_index + 1;
300 END LOOP;
301 ELSIF check_char(l_local_char) AND LENGTH(p_middle_name) > 1 THEN
302 -- Proceed to 2nd character
303 l_index := 2;
304 WHILE l_middle_name <> '?' AND l_index <= 7 LOOP
305 -- Check up to the seventh character
306 l_local_char := SUBSTR(p_middle_name,l_index,1);
307 IF l_local_char <> ' ' AND (NOT check_special_char(l_local_char)) THEN
308 -- Character is not a space or special char (inc. .-' ) so fail
309 l_middle_name := '?';
310 END IF;
311 l_index := l_index + 1;
312 END LOOP;
313 END IF;
314 RETURN(l_middle_name);
315 END;
316 --
317 FUNCTION f_last_name(p_last_name VARCHAR2) RETURN VARCHAR2 IS
318 l_index NUMBER(2):=1;
319 l_local_char VARCHAR2(1);
320 l_last_name VARCHAR2(20);
321 BEGIN
322 -- Save the last name for the return clause
323 -- This value will be changed if validation fails
324 l_last_name := p_last_name;
325 l_local_char := SUBSTR(l_last_name,1,1);
326 IF NOT check_char(l_local_char) THEN
327 hr_utility.set_location('eoy_body.eoy',160);
328 l_last_name := '?';
329 ELSE
330 /* Start at the second character */
331 l_index := 2;
332 hr_utility.set_location('eoy_body.eoy',170);
333 -- Check last_name
334 WHILE l_last_name <> '?' AND l_index <= 4 LOOP
335 hr_utility.set_location('eoy_body.eoy',180);
336 l_local_char := SUBSTR(l_last_name,l_index,1);
337 IF (NOT check_char(l_local_char)) AND
338 (l_local_char NOT IN ('''','-',' ','.')) THEN
339 hr_utility.trace('The failed char is '||l_local_char);
340 l_last_name := '?';
341 hr_utility.set_location('eoy_body.eoy',190);
342 END IF;
343 hr_utility.set_location('eoy_body.eoy',240);
344 l_index := l_index + 1;
345 END LOOP;
346 END IF;
347 RETURN(l_last_name);
348 END;
349 --
350 --
351 PROCEDURE mag_tape_init(p_no NUMBER) IS
352 -- The initialization of the record type formulae
353 -- and number of parameters
354 BEGIN
355 /* Reserved parameter names */
356 pay_mag_tape.internal_prm_names(1) := 'NO_OF_PARAMETERS';
357 pay_mag_tape.internal_prm_names(2) := 'NEW_FORMULA_ID';
358 pay_mag_tape.internal_prm_names(3) := 'TRANSFER_TYPE1_ERRORS';
359 pay_mag_tape.internal_prm_names(4) := 'TRANSFER_TYPE2_ERRORS';
363 pay_mag_tape.internal_prm_values(2) := get_formula_id('MAG_RECORD1');
360 IF p_no = 1 THEN
361 /* Record type 1 */
362 pay_mag_tape.internal_prm_values(1) := 13;
364 ELSIF p_no = 2 THEN
365 /* Record type 2 */
366 pay_mag_tape.internal_prm_values(1) := 63;
367 pay_mag_tape.internal_prm_values(2) := get_formula_id('MAG_RECORD2');
368 /* Reset the record index to start at the third parameter */
369 ELSIF p_no = 3 THEN
370 /* Sub-header */
371 -- hr_utility.trace('record index is '||to_char(g_record_index));
372 pay_mag_tape.internal_prm_values(1) := 6;
373 pay_mag_tape.internal_prm_values(2) := get_formula_id('MAG_RECORD3');
374 ELSIF p_no = 4 THEN
375 /* Permit total */
376 -- hr_utility.trace('record index is '||to_char(g_record_index));
377 pay_mag_tape.internal_prm_values(1) := 15;
378 pay_mag_tape.internal_prm_values(2) := get_formula_id('MAG_RECORD4');
379 ELSIF p_no = 5 THEN
380 /* End of record */
381 -- hr_utility.trace('record index is '||to_char(g_record_index));
382 pay_mag_tape.internal_prm_values(1) := 7;
383 pay_mag_tape.internal_prm_values(2) := get_formula_id('MAG_RECORD5');
384 ELSIF p_no = 6 THEN
385 /* Dummy record */
386 pay_mag_tape.internal_prm_values(1) := 2;
387 pay_mag_tape.internal_prm_values(2) := get_formula_id('MAG_RECORD6');
388 END IF;
389 -- Set parameter count to start at transfer_type1_errors
390 g_record_index := 5;
391 END;
392 --
393 PROCEDURE mag_tape_interface(p_name VARCHAR2
394 ,p_values VARCHAR2) IS
395 /* The interface to the magnetic tape writer process */
396 BEGIN
397 pay_mag_tape.internal_prm_names(g_record_index) := p_name;
398 pay_mag_tape.internal_prm_values(g_record_index) := p_values;
399 /* Inc the parameter table index */
400 g_record_index := g_record_index +1;
401 END;
402 --
403 PROCEDURE mag_tape_interface(p_name VARCHAR2
404 ,p_values NUMBER) IS
405 /* The interface to the magnetic tape writer process */
406 BEGIN
407 pay_mag_tape.internal_prm_names(g_record_index) := p_name;
408 pay_mag_tape.internal_prm_values(g_record_index) := p_values;
409 g_record_index := g_record_index +1;
410 END;
411 --
412 PROCEDURE p_mag_form_clear IS
413 /* This procedure will clear the NI2 to NI4 records for the
414 employee. This will stop any earlier records appearing in
415 later records. */
416 BEGIN
417 FOR l_index IN 2..4 LOOP
418 mag_tape_interface('SCON'||TO_CHAR(l_index) ,' ');
419 mag_tape_interface('NI_CATEGORY_CODE'||
420 TO_CHAR(l_index),' ');
421 mag_tape_interface('EARNINGS'||TO_CHAR(l_index) ,'0');
422 mag_tape_interface('TOTAL_CONTRIBUTIONS'||l_index,'0');
423 mag_tape_interface('EMPLOYEES_CONTRIBUTIONS'|| TO_CHAR(l_index),'0');
424 mag_tape_interface('EARNINGS_CONTRACTED_OUT'|| TO_CHAR(l_index),'0');
425 mag_tape_interface('CONTRIBUTIONS_CONTRACTED_OUT'||TO_CHAR(l_index),'0');
426 END LOOP;
427 END;
428 --
429 PROCEDURE create_record_type1 IS
430 l_index NUMBER :=0;
431 l_local_char VARCHAR2(1);
432 l_result VARCHAR2(1);
433 BEGIN
434 -- Now start validating the record type 1
435 hr_utility.set_location('eoy_body.eoy',600);
436 -- Initialise the record type 1 parameters
437 hr_utility.trace('Writing record type 1');
438 mag_tape_init(1);
439 -- Pass the record fields as paramteres to the mag tape process
440 hr_utility.trace('Record type1 passed eoy_mode '||g_eoy_mode);
441 mag_tape_interface('EOY_MODE',g_eoy_mode);
442 mag_tape_interface('PERMIT_NO',NVL(g_new_permit_no,'?'));
443 --
444 /* Field must be three numeric characters */
445 BEGIN
446 g_tax_district_ref := FND_number.CANONICAL_TO_NUMBER(g_tax_district_ref);
447 EXCEPTION
448 WHEN VALUE_ERROR THEN
449 -- Any non-numeric characters will raise an exception
450 g_tax_district_ref := '?';
451 hr_utility.set_location('eoy_body.eoy',610);
452 END;
453 mag_tape_interface('TAX_DISTRICT_REF' ,NVL(g_tax_district_ref,'?'));
454 --
455 /* First char must be alphanumeric only */
456 IF NOT check_number(SUBSTR(g_tax_ref_no,1,1)) THEN
457 IF NOT check_char(SUBSTR(g_tax_ref_no,1,1)) THEN
458 hr_utility.set_location('eoy_body.eoy',620);
459 g_tax_ref_no := '?';
460 END IF;
461 END IF;
462 /* Start at the second character */
463 l_index := 2;
464 /* Next 5 may be alphanumeric, space, oblique or brackets */
465 WHILE g_tax_ref_no <> '?' AND l_index <= LENGTH(g_tax_ref_no) LOOP
466 l_local_char := SUBSTR(g_tax_ref_no,l_index,1);
467 IF NOT check_number(l_local_char) AND
468 NOT check_char(l_local_char) AND
469 l_local_char NOT IN (' ','/','\','(',')') THEN
470 hr_utility.set_location('eoy_body.eoy',630);
471 g_tax_ref_no := '?';
472 END IF;
473 l_index := l_index + 1;
474 END LOOP;
475 mag_tape_interface('TAX_REF_NO',g_tax_ref_no);
476 mag_tape_interface('TAX_DISTRICT_NAME',g_tax_district_name);
477 mag_tape_interface('TAX_YEAR',g_tax_year);
478 mag_tape_interface('EMPLOYERS_NAME',NVL(g_employers_name,'?'));
479 mag_tape_interface('EMPLOYERS_ADDRESS',NVL(g_employers_address,'?'));
480 --
481 IF NOT(econ_chk%ISOPEN) THEN
482 OPEN econ_chk(g_permit_no
483 ,g_tax_dist_ref
484 ,g_tax_ref_no
485 ,g_business_group_id);
486 END IF;
487 FETCH econ_chk INTO l_result;
488 IF l_result IS NULL THEN
489 -- No econ is needed as less than 5 contracted out employees
490 IF g_econ = '?' THEN
494 ELSE
491 -- If NVL forced a ? then overwrite to a space
492 g_econ := ' ';
493 END IF;
495 -- Econ must be present
496 IF g_econ = '?' THEN
497 -- If NVL forced a ? then overwrite to '?2'
498 g_econ := '?2';
499 ELSE
500 /* Econ 1st digit must be 'E', the next 7 between 0 and 9 */
501 /* and the last between 'A' and 'Z' */
502 g_econ:=rpad(g_econ,9);
503 -- prevent SUBSTR in loop from returning null to l_local_char
504 IF SUBSTR(g_econ,1,1) <> 'E' THEN
505 g_econ := '?1';
506 hr_utility.set_location('eoy_body.eoy',640);
507 END IF;
508 l_index := 2;
509 WHILE g_econ <> '?1' AND l_index <= 9 LOOP
510 l_local_char := SUBSTR(g_econ,l_index,1);
511 IF NOT check_number(l_local_char) AND l_index <= 8 THEN
512 g_econ := '?1';
513 hr_utility.set_location('eoy_body.eoy',650);
514 END IF;
515 IF NOT check_char(l_local_char) AND (l_index = 9) THEN
516 g_econ := '?1';
517 hr_utility.set_location('eoy_body.eoy',660);
518 END IF;
519 l_index := l_index + 1;
520 END LOOP;
521 END IF;
522 END IF;
523 mag_tape_interface('ECON',g_econ);
524 END;
525 --
526 PROCEDURE create_sub_header IS
527 BEGIN
528 hr_utility.set_location('eoy_body.eoy',500);
529 hr_utility.trace('Writting record type 2 subheader');
530 mag_tape_init(3);
531 mag_tape_interface('EOY_MODE',g_eoy_mode);
532 mag_tape_interface('SUB_TOTAL','SUBTOTAL');
533 hr_utility.set_location('eoy_body.eoy',510);
534 END;
535 --
536 PROCEDURE create_record_type3 IS
537 --
538 l_space_field VARCHAR2(20) :=NULL; -- Used for space filled values
539 l_tot_refund VARCHAR2(1) :=NULL; -- Set to 'R' if tax refund
540 --
541 BEGIN
542 hr_utility.trace('Writing record type 3');
543 mag_tape_init(4);
544 mag_tape_interface('EOY_MODE',g_eoy_mode);
545 mag_tape_interface('PERMIT_NO',g_permit_no); -- For inclusion in Error Messages
546 mag_tape_interface('TOTAL_CONTRIBUTIONS',NVL(g_tot_contribs,0));
547 g_tot_contribs := 0;
548 hr_utility.trace('The tot tax is '||to_char(g_tot_tax));
549 mag_tape_interface('TOTAL_TAX',NVL(ABS(g_tot_tax),0));
550 IF SIGN(g_tot_tax) = -1 THEN
551 -- The tax is a refund so set the refund status
552 l_tot_refund := 'R';
553 ELSE
554 l_tot_refund := ' ';
555 END IF;
556 hr_utility.trace('The tot refund is '||l_tot_refund||'.');
557 mag_tape_interface('TOTAL_TAX_REFUND',l_tot_refund);
558 g_tot_tax := 0;
559 mag_tape_interface('TOTAL_RECORDS',NVL(g_tot_rec2_per,0));
560 -- Now add to the total record 2 count
561 g_tot_rec2 := g_tot_rec2 + NVL(g_tot_rec2_per,0);
562 hr_utility.trace('The per record is '||to_char(g_tot_rec2_per));
563 hr_utility.trace('The current grand tot is '||to_char(g_tot_rec2));
564 g_tot_rec2_per := 0;
565 mag_tape_interface('TOTAL_SSP',NVL(g_tot_ssp_rec,0));
566 -- Copy across new values to the variables
567 -- g_tot_ssp_rec := g_ssp_recovery;
568 g_tot_ssp_rec := 0;
569 l_space_field := NULL;
570 -- The field will have to be padded with spaces in the
571 -- formulae
572 FOR l_index IN 1..9 LOOP
573 l_space_field := l_space_field||' ';
574 END LOOP;
575 mag_tape_interface('SPARE_FIELD',l_space_field);
576 mag_tape_interface('TOTAL_NIP',NVL(g_tot_nip, 0));
577 g_tot_nip := 0;
578 mag_tape_interface('TOTAL_SMP',NVL(g_tot_smp_rec,0));
579 -- g_tot_smp_rec := g_smp_recovery;
580 g_tot_smp_rec := 0;
581 mag_tape_interface('TOTAL_SMP_COMP',NVL(g_tot_smp_comp,0));
582 -- g_tot_smp_comp := g_smp_compensation;
583 g_tot_smp_comp := 0;
584 END;
585 --
586 PROCEDURE p_create_dummy(l_tab_index NUMBER
587 ,l_no_nis NUMBER) IS
588 --
589 l_local_date DATE; -- Used to hold a converted char
590 --
591 BEGIN
592 /* Now create a dummy record type 2 */
593 /* This is for the extra NI details for an employee */
594 mag_tape_init(2);
595 mag_tape_interface('EOY_MODE',g_eoy_mode);
596 mag_tape_interface('EMPLOYEE_NUMBER',NVL(g_employee_number,' '));
597 hr_utility.trace('The employee is '||g_employee_number);
598 --
599 g_last_name := f_last_name(g_last_name);
600 hr_utility.set_location('eoy_body.eoy',530);
601 mag_tape_interface('LAST_NAME',NVL(g_last_name,'?'));
602 --
603 -- Check first name
604 IF g_first_name IS NOT NULL AND
605 NOT(check_char(SUBSTR(g_first_name,1,1))) THEN
606 g_first_name := '?';
607 hr_utility.set_location('eoy_body.eoy',540);
608 END IF;
609 mag_tape_interface('FIRST_NAME',NVL(g_first_name,'?'));
610 --
611 -- Check middle_name
612 IF g_middle_name IS NOT NULL THEN
613 g_middle_name := f_middle_name(g_middle_name);
614 END IF;-- middle null check
615 hr_utility.set_location('eoy_body.eoy',550);
616 --
617 mag_tape_interface('MIDDLE_NAME',NVL(g_middle_name,' '));
618 mag_tape_interface('DATE_OF_BIRTH',g_date_of_birth);
619 mag_tape_interface('GENDER',g_sex);
620 mag_tape_interface('ADDRESS_LINE1',g_address_line1);
621 mag_tape_interface('ADDRESS_LINE2',g_address_line2);
622 mag_tape_interface('ADDRESS_LINE3',g_address_line3);
623 mag_tape_interface('ADDRESS_LINE4',g_address_line4);
624 mag_tape_interface('POSTAL_CODE',g_postal_code);
625 mag_tape_interface('TAX_CODE','NI');
626 mag_tape_interface('W1_M1',' ');
627 mag_tape_interface('NI_NO',g_national_insurance_number);
628 --
629 -- Send the first record from the pl/sql tables to the mag tape
630 --
634 mag_tape_interface('TOTAL_CONTRIBUTIONS1',total_contrib_tab(l_tab_index+1));
631 mag_tape_interface('SCON1',scon_tab(l_tab_index + 1));
632 mag_tape_interface('NI_CATEGORY_CODE1',category_tab(l_tab_index + 1));
633 mag_tape_interface('EARNINGS1',earnings_tab(l_tab_index+1));
635 mag_tape_interface('EMPLOYEES_CONTRIBUTIONS1',
636 employees_contrib_tab(l_tab_index+1));
637 mag_tape_interface('EARNINGS_CONTRACTED_OUT1',
638 earnings_cont_out_tab(l_tab_index+1));
639 mag_tape_interface('CONTRIBUTIONS_CONTRACTED_OUT1',
640 contribs_cont_out_tab(l_tab_index+1));
641 mag_tape_interface('SSP','0');
642 mag_tape_interface('SMP','0');
643 mag_tape_interface('GROSS_PAY','0');
644 mag_tape_interface('TAX_PAID','0');
645 hr_utility.set_location('eoy_body.eoy',560);
646 mag_tape_interface('TAX_REFUND',' ');
647 mag_tape_interface('PREVIOUS_TAXABLE_PAY','0');
648 --
649 mag_tape_interface('PREVIOUS_TAX_PAID','0');
650 --
651 mag_tape_interface('DATE_OF_STARTING',g_start_of_emp);
652 BEGIN
653 IF g_termination_date IS NOT NULL THEN
654 l_local_date := FND_DATE.CANONICAL_TO_DATE(g_termination_date);
655 END IF;
656 EXCEPTION
657 WHEN value_error THEN
658 g_termination_date := '?';
659 hr_utility.set_location('eoy_body.eoy',570);
660 END;
661 mag_tape_interface('TERMINATION_DATE',NVL(g_termination_date,' '));
662 mag_tape_interface('SUPERANNUATION','0');
663 --
664 mag_tape_interface('SUPERANNUATION_REFUND',' ');
665 mag_tape_interface('WIDOWS_ORPHANS','0');
666 --
667 mag_tape_interface('WEEK_53',' ');
668 mag_tape_interface('TAXABLE_PAY','0');
669 --
670 mag_tape_interface('PENSIONER_INDICATOR',' ');
671 mag_tape_interface('DIRECTOR_INDICATOR',' ');
672 hr_utility.set_location('eoy_body.eoy',580);
673 --
674 --
675 hr_utility.trace('Start is '||to_char(l_tab_index+2));
676 hr_utility.trace('End is '||to_char(l_no_nis));
677 -- This will clear any previous values out
678 p_mag_form_clear;
679 FOR l_index IN l_tab_index+2..l_tab_index+l_no_nis LOOP
680 hr_utility.trace('Index is now '||to_char(l_index));
681 mag_tape_interface('SCON'||TO_CHAR(l_index),scon_tab(l_index));
682 mag_tape_interface('NI_CATEGORY_CODE'||
683 TO_CHAR(l_index),category_tab(l_index));
684 mag_tape_interface('EARNINGS'||TO_CHAR(l_index)
685 ,earnings_tab(l_index));
686 mag_tape_interface('TOTAL_CONTRIBUTIONS'||l_index
687 ,total_contrib_tab(l_index));
688 mag_tape_interface('EMPLOYEES_CONTRIBUTIONS'||
689 TO_CHAR(l_index),employees_contrib_tab(l_index));
690 mag_tape_interface('EARNINGS_CONTRACTED_OUT'||
691 TO_CHAR(l_index),earnings_cont_out_tab(l_index));
692 mag_tape_interface('CONTRIBUTIONS_CONTRACTED_OUT'||
693 TO_CHAR(l_index),contribs_cont_out_tab(l_index));
694 hr_utility.set_location('eoy_body.eoy',590);
695 END LOOP;
696 hr_utility.set_location('eoy_body.eoy',595);
697 END;
698 --
699 PROCEDURE get_parameters(p_permit_no IN OUT VARCHAR2
700 ,p_eoy_mode IN OUT VARCHAR2
701 ,p_tax_dist_ref IN OUT VARCHAR2
702 ,p_tax_ref_no IN OUT VARCHAR2
703 ,p_business_group_id IN OUT VARCHAR2) IS
704 BEGIN
705 -- Get the parameters passed to the module
706 -- Permit number first
707 IF pay_mag_tape.internal_prm_names(3) = 'PERMIT' THEN
708 hr_utility.set_location('eoy_body.eoy',400);
709 -- Trap if the permit number was left null in parameters
710 BEGIN
711 p_permit_no := pay_mag_tape.internal_prm_values(3);
712 EXCEPTION
713 WHEN no_data_found THEN
714 hr_utility.set_location('eoy_body.eoy',410);
715 hr_utility.trace('No permit found');
716 p_permit_no := NULL;
717 END;
718 hr_utility.set_location('eoy_body.eoy',420);
719 END IF;
720 -- EOY MODE parameter
721 IF pay_mag_tape.internal_prm_names(4) = 'EOY_MODE' THEN
722 -- Trap if the eoy_mode was left null in parameters
723 hr_utility.set_location('eoy_body.eoy',430);
724 BEGIN
725 p_eoy_mode := UPPER(pay_mag_tape.internal_prm_values(4));
726 EXCEPTION
727 WHEN no_data_found THEN
728 -- Parameter left null so only P35 required
729 hr_utility.set_location('eoy_body.eoy',440);
730 hr_utility.trace('No eoy mode found');
731 p_eoy_mode := 'P';
732 END;
733 hr_utility.set_location('eoy_body.eoy',450);
734 hr_utility.trace('EOY mode is '||p_eoy_mode);
735 END IF;
736 -- The tax district reference
737 IF pay_mag_tape.internal_prm_names(5) = 'TAX_DISTRICT_REFERENCE' THEN
738 -- Trap if the tax_dist_ref was left null in parameters
739 hr_utility.set_location('eoy_body.eoy',460);
740 BEGIN
741 p_tax_dist_ref := SUBSTR(pay_mag_tape.internal_prm_values(5),1,3);
742 p_tax_ref_no := LTRIM(
743 SUBSTR(pay_mag_tape.internal_prm_values(5),4)
744 , '/');
745 EXCEPTION
746 WHEN no_data_found THEN
747 -- Parameter left null
748 hr_utility.set_location('eoy_body.eoy',470);
749 hr_utility.trace('No tax dist ref found');
750 p_tax_dist_ref := NULL;
751 END;
752 END IF;
753 -- The Business_group_id
754 IF pay_mag_tape.internal_prm_names(6) = 'BUSINESS_GROUP_ID' THEN
755 -- Trap if the tax_dist_ref was left null in parameters
756 hr_utility.set_location('eoy_body.eoy',480);
757 BEGIN
758 p_business_group_id := pay_mag_tape.internal_prm_values(6);
759 EXCEPTION
760 WHEN no_data_found THEN
761 -- Parameter left null
762 hr_utility.set_location('eoy_body.eoy',490);
763 hr_utility.trace('No business group id found');
767 hr_utility.set_location('eoy_body.eoy',495);
764 p_business_group_id := NULL;
765 END;
766 END IF;
768 EXCEPTION
769 WHEN no_data_found THEN
770 -- If this is raised then either the permit number and/or eoy_mode was
771 -- not entered as a parameter
772 hr_utility.set_location('eoy_body.eoy',499);
773 g_permit_no := NVL(p_permit_no,NULL);
774 p_eoy_mode := NVL(p_eoy_mode,'P');
775 p_tax_dist_ref := NVL(p_tax_dist_ref,NULL);
776 p_tax_ref_no := NVL(p_tax_ref_no,NULL);
777 p_business_group_id := NVL(p_business_group_id,NULL);
778 hr_utility.trace('In exception handler get_parameters');
779 END;
780 --
781 -- START HERE
782 --
783 PROCEDURE eoy IS
784 --
785 -- Record type 2 placeholders
786 l_tax_code VARCHAR2(5);
787 l_w1_m1_indicator VARCHAR2(1);
788 l_ssp NUMBER(6);
789 l_smp NUMBER(7);
790 l_gross_pay NUMBER(9);
791 l_tax_paid NUMBER(9);
792 l_tax_refund VARCHAR2(1);
793 l_previous_taxable_pay NUMBER(9);
794 l_previous_tax_paid NUMBER(9);
795 l_superannuation_paid NUMBER(9);
796 l_superannuation_refund VARCHAR2(1);
797 l_widows_and_orphans NUMBER(9);
798 l_week_53_indicator VARCHAR2(1);
799 l_taxable_pay NUMBER(9);
800 l_pension_indicator VARCHAR2(1);
801 l_director_indicator VARCHAR2(1);
802 l_assignment_id pay_gb_year_end_assignments.assignment_id%TYPE;
803 l_effective_date DATE;
804 --
805 -- General purpose variables
806 l_index NUMBER(3) :=0; -- General purpose loop counter
807 l_index2 NUMBER(3) :=0; -- General purpose loop counter
808 l_plsql_index NUMBER(3) :=0; -- Index of the pl/sql tables
809 l_local_char VARCHAR2(1); -- Holds a char for testing
810 l_local_date DATE; -- Used to hold a converted char
811 l_space_field VARCHAR2(500):=NULL; -- Used for space filled values
812 l_tot_refund VARCHAR2(1):=NULL; -- Set to 'R' if tax refund
813 --
814 BEGIN
815 hr_utility.set_location('eoy_body.eoy',0);
816 --
817 -- Start checking for record type 1
818 --
819 IF fetch_new_header THEN
820 hr_utility.set_location('eoy_body.eoy',10);
821 -- A Record type 1 is required
822 IF NOT (header_cur%ISOPEN) THEN
823 hr_utility.set_location('eoy_body.eoy',20);
824 -- Get any parameters that have been sent
825 get_parameters(g_permit_no
826 ,g_eoy_mode
827 ,g_tax_dist_ref
828 ,g_tax_ref_no
829 ,g_business_group_id);
830 hr_utility.trace('The passed in Mode is '||g_eoy_mode||'@');
831 hr_utility.trace('The passed in dist is '||g_tax_dist_ref||'@');
832 hr_utility.trace('The passed in ref is '||g_tax_ref_no||'@');
833 hr_utility.trace('The passed in business is '||g_business_group_id||'@');
834 -- First time in so clear the error type counts
835 pay_mag_tape.internal_prm_values(3) := 0;
836 pay_mag_tape.internal_prm_values(4) := 0;
837 OPEN header_cur(g_permit_no
838 ,g_tax_dist_ref
839 ,g_tax_ref_no
840 ,g_business_group_id);
841 END IF;
842 IF NOT(permit_change) THEN
843 -- Get record from EOY table as next record
844 -- for record type 1 required
845 hr_utility.trace('1 The global Permit is '||g_permit_no);
846 hr_utility.trace('1 The global Payroll is '||g_payroll_id);
847 FETCH header_cur INTO g_new_permit_no
848 ,g_new_payroll_id
849 ,g_tax_district_ref
850 ,g_tax_ref_no
851 ,g_tax_district_name
852 ,g_tax_year
853 ,g_employers_name
854 ,g_employers_address
855 ,g_econ
856 ,g_ssp_recovery
857 ,g_smp_recovery
858 ,g_smp_compensation;
859 IF header_cur%NOTFOUND THEN
860 -- No more records found so end of run
861 hr_utility.set_location('eoy_body.eoy',30);
862 IF g_tot_rec2_per > 0 THEN
863 -- If at least one record has been found then create
864 -- a permit total
865 create_record_type3;
866 ELSE
867 -- No records found for permit create dummy record
868 mag_tape_init(6);
869 END IF;
870 fetch_new_header := FALSE;
871 process_emps := FALSE;
872 sub_header := FALSE;
873 fin_run := TRUE;
874 /* A fetch of a new header is due to the first fetch or
875 change of permit or payroll */
876 ELSIF g_new_permit_no <> NVL(g_permit_no,g_new_permit_no) THEN
877 --
878 -- The permit has changed so construct the record type 3
879 --
880 hr_utility.trace('2 Fetched Permit is '||g_new_permit_no);
881 hr_utility.trace('2 Fetched Payroll_id is '||g_new_payroll_id);
882 create_record_type3;
883 -- Save required values in globals
884 g_permit_no := g_new_permit_no;
885 g_payroll_id := g_new_payroll_id;
886 permit_change := TRUE;
887 -- Close the type 2 cursor so it will be re-opened with
888 -- the new parameters
889 CLOSE emps_cur;
890 hr_utility.set_location('eoy_body.eoy',40);
891 ELSE
892 -- No permit change so add new smp and smp values to totals
893 g_tot_ssp_rec := g_tot_ssp_rec + g_ssp_recovery;
894 g_tot_smp_rec := g_tot_smp_rec + g_smp_recovery;
895 g_tot_smp_comp := g_tot_smp_comp + g_smp_compensation;
896 hr_utility.trace('3 Fetched Permit is '||g_new_permit_no);
897 hr_utility.trace('3 Fetched Payroll_id is '||g_new_payroll_id);
901 -- Write the sub_header and then get the employee details
898 IF g_new_payroll_id <> NVL(g_payroll_id,g_new_payroll_id) THEN
899 -- The payroll_id has changed in permit_no
900 g_payroll_id := g_new_payroll_id;
902 create_sub_header;
903 -- Close the type 2 cursor so it will be re-opened with
904 -- the new parameters
905 CLOSE emps_cur;
906 fetch_new_header := FALSE;
907 permit_change := FALSE;
908 process_emps := TRUE;
909 hr_utility.set_location('eoy_body.eoy',45);
910 ELSE
911 hr_utility.trace('No payroll or permit change ');
912 hr_utility.trace('4 Fetched Permit is '||g_new_permit_no);
913 hr_utility.trace('4 Fetched Payroll_id is '||g_new_payroll_id);
914 -- Save required values in globals
915 g_permit_no := g_new_permit_no;
916 g_payroll_id := g_new_payroll_id;
917 create_record_type1;
918 fetch_new_header := FALSE;
919 sub_header := TRUE;
920 hr_utility.set_location('eoy_body.eoy',50);
921 END IF;
922 END IF;
923 ELSE
924 -- Change of permit so create a type 1 record from old values
925 permit_change := FALSE;
926 create_record_type1;
927 fetch_new_header := FALSE;
928 sub_header := TRUE;
929 -- 1st record with this permit so set totals to new smp and ssp values
930 g_tot_ssp_rec := g_ssp_recovery;
931 g_tot_smp_rec := g_smp_recovery;
932 g_tot_smp_comp := g_smp_compensation;
933 hr_utility.set_location('eoy_body.eoy',60);
934 END IF;
935 --
936 -- Check if sub-header required
937 --
938 ELSIF sub_header THEN
939 create_sub_header;
940 hr_utility.set_location('eoy_body.eoy',70);
941 sub_header := FALSE;
942 process_emps := TRUE;
943 --
944 -- Check for a dummy record 2 needed when more than 4 Ni cats exist for
945 -- a single employee
946 --
947 ELSIF process_dummy THEN
948 -- A special record type 2
949 -- More than 4 more NI categories exist for the employee
950 hr_utility.set_location('eoy_body.eoy',700);
951 IF g_ni_total - g_last_ni > 4 THEN
952 p_create_dummy(g_last_ni,4);
953 g_last_ni := g_last_ni + 4;
954 ELSE
955 -- Less than 4 more NI categories exist for the employee
956 p_create_dummy(g_last_ni,g_ni_total-g_last_ni);
957 g_last_ni := 0;
958 g_ni_total := 0;
959 -- Reset the flags to continue processing any further employees
960 process_emps := TRUE;
961 process_dummy := FALSE;
962 END IF;
963 --
964 -- Check for processing record type 2
965 --
966 ELSIF process_emps THEN
967 -- Record type 2 required
968 hr_utility.set_location('eoy_body.eoy',100);
969 hr_utility.trace('The emp permit_no is '||g_permit_no);
970 hr_utility.trace('The emp payroll_id is '||to_char(g_payroll_id));
971 IF NOT (emps_cur%ISOPEN) THEN
972 hr_utility.set_location('eoy_body.eoy',110);
973 OPEN emps_cur(g_payroll_id);
974 END IF;
975 FETCH emps_cur INTO g_employee_number
976 ,g_last_name
977 ,g_first_name
978 ,g_middle_name
979 ,g_date_of_birth
980 ,g_sex
981 ,g_address_line1
982 ,g_address_line2
983 ,g_address_line3
984 ,g_address_line4
985 ,g_postal_code
986 ,l_tax_code
987 ,l_w1_m1_indicator
988 ,g_national_insurance_number
989 ,l_ssp
990 ,l_smp
991 ,l_gross_pay
992 ,l_tax_paid
993 ,l_tax_refund
994 ,l_previous_taxable_pay
995 ,l_previous_tax_paid
996 ,g_start_of_emp
997 ,g_termination_date
998 ,l_superannuation_paid
999 ,l_superannuation_refund
1000 ,l_widows_and_orphans
1001 ,l_week_53_indicator
1002 ,l_taxable_pay
1003 ,l_pension_indicator
1004 ,l_director_indicator
1005 ,l_assignment_id
1006 ,l_effective_date;
1007 IF emps_cur%NOTFOUND THEN
1008 --
1009 -- End of record type 2
1010 --
1011 -- Set escape from this section
1012 hr_utility.set_location('eoy_body.eoy',130);
1013 /* Each call of this package must return 1 record even */
1014 /* if its only a dummy formula call to do so */
1015 mag_tape_init(6);
1016 fetch_new_header:= TRUE;
1017 process_emps := FALSE;
1018 ELSE
1019 --
1020 -- Fetch all the ni contributions for each employee
1021 -- in one hit.
1022 --
1023 l_index := 1;
1024 hr_utility.set_location('eoy_body.eoy',140);
1025 FOR emp_values_rec IN emp_values(l_assignment_id,l_effective_date)
1026 LOOP
1027 scon_tab(l_index) := emp_values_rec.scon;
1028 category_tab(l_index) := emp_values_rec.cat_code;
1029 earnings_tab(l_index) := emp_values_rec.earnings;
1030 total_contrib_tab(l_index) := emp_values_rec.tot_cont;
1031 -- g_tot_contribs := g_tot_contribs + emp_values_rec.tot_cont;
1032 employees_contrib_tab(l_index) := emp_values_rec.emps_cont;
1033 earnings_cont_out_tab(l_index) := emp_values_rec.earnings_out;
1034 contribs_cont_out_tab(l_index) := emp_values_rec.cont_out;
1035 --
1036 if (emp_values_rec.cat_code) = 'P' then
1037 g_tot_nip := g_tot_nip + emp_values_rec.tot_cont;
1038 else
1039 g_tot_contribs := g_tot_contribs + emp_values_rec.tot_cont;
1040 end if; -- IF NI CODE = 'P'
1041 -- Perform SCON error checking if NI F/G/S
1042 if category_tab(l_index) in ('F','G','S') then
1046 else /* check format */
1043 if scon_tab(l_index) = ' ' then
1044 /* if nvl forced a ' ' then overwrite with ?1 */
1045 scon_tab(l_index) := '?1';
1047 /* 1st char must be A or S, followed by 7 numerics, then one alpha */
1048 if SUBSTR(scon_tab(l_index),1,1) NOT IN ('A','S') then
1049 scon_tab(l_index) := '?2';
1050 end if;
1051 l_index2:=2;
1052 scon_tab(l_index):=rpad(scon_tab(l_index),9);
1053 -- prevent SUBSTR in loop from returning null to l_local_char
1054 WHILE scon_tab(l_index) <> '?2' AND l_index2 <= 9 LOOP
1055 l_local_char := SUBSTR(scon_tab(l_index),l_index2,1);
1056 IF NOT check_number(l_local_char) AND l_index2 <= 8 THEN
1057 scon_tab(l_index) := '?2';
1058 END IF;
1059 IF NOT check_char(l_local_char) AND (l_index2 = 9) THEN
1060 scon_tab(l_index) := '?2';
1061 END IF;
1062 l_index2 := l_index2 + 1;
1063 END LOOP;
1064 end if;
1065 end if; -- if F/G/S
1066 -- end of SCON error check
1067 l_index := l_index + 1;
1068 END LOOP;
1069 /* Keep the total number of NI category codes for the employee */
1070 /* If > 5 then raise warning in the mag tape log file */
1071 g_ni_total := l_index - 1;
1072 IF l_index < 5 THEN
1073 /* Even if no category codes exist the fields must be */
1074 /* defaulted and written to the mag tape. */
1075 FOR l_plsql_index IN l_index..4 LOOP
1076 scon_tab(l_plsql_index) := ' ';
1077 category_tab(l_plsql_index) := ' ';
1078 earnings_tab(l_plsql_index) := 0;
1079 total_contrib_tab(l_plsql_index) := 0;
1080 employees_contrib_tab(l_plsql_index) := 0;
1081 earnings_cont_out_tab(l_plsql_index) := 0;
1082 contribs_cont_out_tab(l_plsql_index) := 0;
1083 END LOOP;
1084 hr_utility.set_location('eoy_body.eoy',150);
1085 END IF;
1086 /* Create a type 2 record */
1087 /* Set up the no of parameters and the formula professor */
1088 hr_utility.trace('Writting record type 2');
1089 mag_tape_init(2);
1090 /* Now create a record type 2 */
1091 mag_tape_interface('EOY_MODE',g_eoy_mode);
1092 mag_tape_interface('EMPLOYEE_NUMBER',NVL(g_employee_number,' '));
1093 --
1094 g_last_name := f_last_name(g_last_name);
1095 hr_utility.set_location('eoy_body.eoy',250);
1096 mag_tape_interface('LAST_NAME',NVL(g_last_name,'?'));
1097 --
1098 -- Check first name
1099 IF g_first_name IS NOT NULL AND
1100 NOT(check_char(SUBSTR(g_first_name,1,1))) THEN
1101 g_first_name := '?';
1102 hr_utility.set_location('eoy_body.eoy',260);
1103 END IF;
1104 mag_tape_interface('FIRST_NAME',NVL(g_first_name,'?'));
1105 --
1106 -- Check middle_name
1107 IF g_middle_name IS NOT NULL THEN
1108 g_middle_name := f_middle_name(g_middle_name);
1109 END IF;-- middle null check
1110 hr_utility.set_location('eoy_body.eoy',275);
1111 --
1112 mag_tape_interface('MIDDLE_NAME',NVL(g_middle_name,' '));
1113 mag_tape_interface('DATE_OF_BIRTH',g_date_of_birth);
1114 mag_tape_interface('GENDER',g_sex);
1115 -- Order Address lines to push nulls to end, using g_full_address as
1116 -- a temporary variable.
1117 g_full_address := rpad(nvl(g_address_line1||g_address_line2||
1118 g_address_line3||g_address_line4,' '),108);
1119 -- Split into 4 and pass them to formula
1120 g_address_line1:=substr(g_full_address,1,27);
1121 g_address_line2:=substr(g_full_address,28,27);
1122 g_address_line3:=substr(g_full_address,55,27);
1123 g_address_line4:=substr(g_full_address,82);
1124 mag_tape_interface('ADDRESS_LINE1',g_address_line1);
1125 mag_tape_interface('ADDRESS_LINE2',g_address_line2);
1126 mag_tape_interface('ADDRESS_LINE3',g_address_line3);
1127 mag_tape_interface('ADDRESS_LINE4',g_address_line4);
1128 --
1129 mag_tape_interface('POSTAL_CODE',g_postal_code);
1130 mag_tape_interface('TAX_CODE',l_tax_code);
1131 mag_tape_interface('W1_M1',l_w1_m1_indicator);
1132 mag_tape_interface('NI_NO',g_national_insurance_number);
1133 --
1134 -- Send the first record from the pl/sql tables to the mag tape
1135 --
1136 mag_tape_interface('SCON1',scon_tab(1));
1137 mag_tape_interface('NI_CATEGORY_CODE1',category_tab(1));
1138 mag_tape_interface('EARNINGS1',earnings_tab(1));
1139 mag_tape_interface('TOTAL_CONTRIBUTIONS1',total_contrib_tab(1));
1140 mag_tape_interface('EMPLOYEES_CONTRIBUTIONS1',
1141 employees_contrib_tab(1));
1142 mag_tape_interface('EARNINGS_CONTRACTED_OUT1',
1143 earnings_cont_out_tab(1));
1144 mag_tape_interface('CONTRIBUTIONS_CONTRACTED_OUT1',
1145 contribs_cont_out_tab(1));
1146 mag_tape_interface('SSP',l_ssp);
1147 mag_tape_interface('SMP',l_smp);
1148 mag_tape_interface('GROSS_PAY',l_gross_pay);
1149 mag_tape_interface('TAX_PAID',ABS(l_tax_paid));
1150 g_tot_tax := g_tot_tax + l_tax_paid;
1151 hr_utility.set_location('eoy_body.eoy',280);
1152 --
1153 IF l_tax_refund NOT IN ('R',' ') THEN
1154 l_tax_refund := '?';
1155 hr_utility.set_location('eoy_body.eoy',290);
1156 END IF;
1157 mag_tape_interface('TAX_REFUND',l_tax_refund);
1158 mag_tape_interface('PREVIOUS_TAXABLE_PAY',
1159 l_previous_taxable_pay);
1160 --
1161 mag_tape_interface('PREVIOUS_TAX_PAID',
1162 l_previous_tax_paid);
1163 --
1164 mag_tape_interface('DATE_OF_STARTING',g_start_of_emp);
1165 BEGIN
1166 IF g_termination_date IS NOT NULL THEN
1170 WHEN value_error THEN
1167 l_local_date := FND_DATE.CANONICAL_TO_DATE(g_termination_date);
1168 END IF;
1169 EXCEPTION
1171 g_termination_date := '?';
1172 hr_utility.set_location('eoy_body.eoy',300);
1173 END;
1174 mag_tape_interface('TERMINATION_DATE',NVL(g_termination_date,' '));
1175 mag_tape_interface('SUPERANNUATION',l_superannuation_paid);
1176 --
1177 IF l_superannuation_refund NOT IN ('R',' ') THEN
1178 l_superannuation_refund := '?';
1179 hr_utility.set_location('eoy_body.eoy',310);
1180 END IF;
1181 mag_tape_interface('SUPERANNUATION_REFUND',
1182 l_superannuation_refund);
1183 mag_tape_interface('WIDOWS_ORPHANS',
1184 l_widows_and_orphans);
1185 --
1186 IF l_week_53_indicator NOT IN ('3','4','6',' ') THEN
1187 l_week_53_indicator := '?';
1188 hr_utility.set_location('eoy_body.eoy',320);
1189 END IF;
1190 mag_tape_interface('WEEK_53', l_week_53_indicator);
1191 mag_tape_interface('TAXABLE_PAY',l_taxable_pay);
1192 --
1193 IF l_pension_indicator NOT IN ('P',' ') THEN
1194 l_pension_indicator := '?';
1195 hr_utility.set_location('eoy_body.eoy',330);
1196 END IF;
1197 mag_tape_interface('PENSIONER_INDICATOR', l_pension_indicator);
1198 --
1199 IF l_director_indicator NOT IN ('D',' ') THEN
1200 l_director_indicator := '?';
1201 hr_utility.set_location('eoy_body.eoy',340);
1202 END IF;
1203 mag_tape_interface('DIRECTOR_INDICATOR', l_director_indicator);
1204 hr_utility.set_location('eoy_body.eoy',350);
1205 --
1206 -- Now send up to 3 of the remaining contribution records to mag tape
1207 -- If they do not exist they have been defaulted
1208 --
1209 FOR l_index IN 2..4 LOOP
1210 mag_tape_interface('SCON'||TO_CHAR(l_index),scon_tab(l_index));
1211 mag_tape_interface('NI_CATEGORY_CODE'||
1212 TO_CHAR(l_index) ,category_tab(l_index));
1213 mag_tape_interface('EARNINGS'||TO_CHAR(l_index)
1214 ,earnings_tab(l_index));
1215 mag_tape_interface('TOTAL_CONTRIBUTIONS'||l_index
1216 ,total_contrib_tab(l_index));
1217 mag_tape_interface('EMPLOYEES_CONTRIBUTIONS'||
1218 TO_CHAR(l_index), employees_contrib_tab(l_index));
1219 mag_tape_interface('EARNINGS_CONTRACTED_OUT'||
1220 TO_CHAR(l_index), earnings_cont_out_tab(l_index));
1221 mag_tape_interface('CONTRIBUTIONS_CONTRACTED_OUT'||
1222 TO_CHAR(l_index), contribs_cont_out_tab(l_index));
1223 hr_utility.set_location('eoy_body.eoy',360);
1224 END LOOP;
1225 -- Running count of all employee records
1226 g_tot_rec2_per := g_tot_rec2_per + 1;
1227 -- Now check the number of NI categories found for this employee
1228 IF g_ni_total > 4 THEN
1229 hr_utility.trace('The employee is '||g_employee_number);
1230 hr_utility.set_location('eoy_body.eoy',365);
1231 -- More than four so set flags for creation of dummy record
1232 process_emps := FALSE;
1233 process_dummy := TRUE;
1234 -- Index in PL/SQL tables set to the last record selected
1235 g_last_ni := 4;
1236 END IF;
1237 hr_utility.set_location('eoy_body.eoy',370);
1238 --
1239 END IF; /* End of create type 2 record */
1240 ELSIF fin_run THEN
1241 --
1242 -- Start the end of tape procedure
1243 --
1244 hr_utility.trace('Writting record type 4');
1245 hr_utility.set_location('eoy_body.eoy',600);
1246 mag_tape_init(5);
1247 mag_tape_interface('EOY_MODE',g_eoy_mode);
1248 mag_tape_interface('TOTAL_RECORDS',g_tot_rec2);
1249 hr_utility.trace('The tot record is '||to_char(g_tot_rec2));
1250 mag_tape_interface('END_OF_DATA','END OF DATA');
1251 hr_utility.set_location('eoy_body.eoy',610);
1252 IF header_cur%ISOPEN THEN
1253 CLOSE header_cur;
1254 END IF;
1255 IF emps_cur%ISOPEN THEN
1256 CLOSE emps_cur;
1257 END IF;
1258 END IF;
1259 hr_utility.set_location('eoy_body.eoy',999);
1260 END;
1261 --
1262 END;
[Home] [Help]