[Home] [Help]
PACKAGE: APPS.PAY_GB_P11D_MAGTAPE
Source
1 Package pay_gb_p11d_magtape As
2 /* $Header: pygbpdtp.pkh 120.3.12000000.1 2007/01/17 20:29:28 appldev noship $ */
3 level_cnt NUMBER; -- required by the generic magtape procedure.
4
5 FUNCTION round_and_pad(l_input_value VARCHAR2, l_cut_to_size NUMBER)
6 RETURN VARCHAR2;
7
8 FUNCTION format_edi_currency(l_input_value VARCHAR2)
9 RETURN VARCHAR2;
10
11 FUNCTION get_description(
12 l_lookup_code VARCHAR2,
13 l_lookup_type VARCHAR2,
14 l_effective_date VARCHAR2)
15 RETURN VARCHAR2;
16
17 FUNCTION get_parameters(
18 p_payroll_action_id IN NUMBER,
19 p_token_name IN VARCHAR2,
20 p_tax_ref IN VARCHAR2 DEFAULT NULL)
21 RETURN VARCHAR2;
22
23 -- CURSORS
24 CURSOR csr_p11d_header
25 IS
26 SELECT 'TAX_YEAR=P',
27 pay_gb_p11d_magtape.get_parameters(
28 pay_magtape_generic.get_parameter_value(
29 'ARCH_PAYROLL_ACTION_ID'),
30 'Rep_Run'),
31 'SUBMITTER_REF_NO=P',
32 UPPER(
33 TRANSLATE(
34 pay_magtape_generic.get_parameter_value(
35 'SUBMITTER_REF_NO'),
36 fnd_global.local_chr(10),
37 ' ') ),
38 'SUBMITTER_NAME=P',
39 UPPER(
40 TRANSLATE(
41 pay_magtape_generic.get_parameter_value('SUBMITTER_NAME'),
42 fnd_global.local_chr(10),
43 ' ') ),
44 'SUB_RET_NO=P',
45 pay_magtape_generic.get_parameter_value('SUB_RET_NO'),
46 'TOT_SUB_RET=P',
47 pay_magtape_generic.get_parameter_value('TOT_SUB_RET'),
48 'VOL_NO=P', pay_magtape_generic.get_parameter_value('VOL_NO'),
49 'SENDER_ID=P',pay_gb_p11d_magtape.get_parameters(
50 pay_magtape_generic.get_parameter_value(
51 'ARCH_PAYROLL_ACTION_ID'),
52 'SENDER_ID'),
53 'TEST_SUBMISSION=P',pay_magtape_generic.get_parameter_value('TEST_SUBMISSION'),
54 'SUBMISSION_TYPE=P',pay_magtape_generic.get_parameter_value('SUBMISSION_TYPE'),
55 'TRANSMISSION_DATE=P',to_char(sysdate,'YYYYMMDDHHMMSS'),
56 'UNIQUE_REFERENCE=P',pay_gb_p11d_magtape.get_parameters(
57 pay_magtape_generic.get_parameter_value(
58 'ARCH_PAYROLL_ACTION_ID'),
59 'REQUEST_ID')
60 FROM DUAL;
61
62 CURSOR csr_p11d_employer
63 IS
64 SELECT /*+ ordered use_nl(paa,pai)
65 use_index(pai,pay_action_information_n2) */
66 DISTINCT 'TAX_OFFICE_NAME=P',
67 NVL(UPPER(action_information4), ' '),
68 'TAX_OFFICE_PHONE_NO=P',
69 NVL(UPPER(action_information5), ' '),
70 'EMPLOYERS_REF_NO=P',
71 NVL(UPPER(action_information6), ' '),
72 'EMPLOYERS_NAME=P',
73 NVL(UPPER(action_information7), ' '),
74 'EMPLOYERS_ADDRESS=P',
75 NVL(UPPER(action_information8), ' '),
76 'MESSAGE_DATE=P',
77 to_char(sysdate,'YYYYMMDD'),
78 'BENEFIT_TAX_YEAR=P',
79 SUBSTR(pay_gb_p11d_magtape.get_parameters(
80 pay_magtape_generic.get_parameter_value(
81 'ARCH_PAYROLL_ACTION_ID'),
82 'BENEFIT_END_DATE'),1,4),
83 'PARTY_NAME=P',
84 pay_gb_p11d_magtape.get_parameters(
85 pay_magtape_generic.get_parameter_value(
86 'ARCH_PAYROLL_ACTION_ID'),
87 'PARTY_NAME')
88 FROM pay_assignment_actions paa, pay_action_information pai
89 WHERE paa.payroll_action_id =
90 pay_magtape_generic.get_parameter_value(
91 'ARCH_PAYROLL_ACTION_ID')
92 AND pai.action_context_id = paa.assignment_action_id
93 AND pai.action_information_category = 'EMEA PAYROLL INFO';
94
95 CURSOR csr_p11d_employee
96 IS
97 SELECT /*+ ordered
98 use_nl(paa, pai)
99 use_nl(paa, pai_emp)
100 use_nl(paa, pai_gb)
101 use_nl(paa, pai_person)
102 use_index(pai,pay_action_information_n2)
103 use_index(pai_emp,pay_action_information_n2)
104 use_index(pai_gb,pay_action_information_n2)
105 use_index(pai_person,pay_action_information_n2)*/
106 DISTINCT 'LAST_NAME=P',
107 NVL(
108 SUBSTR(UPPER(pai_gb.action_information8), 1, 36),
109 ' '),
110 'FIRST_NAME=P',
111 NVL(
112 SUBSTR(UPPER(pai_gb.action_information6), 1, 36),
113 ' '),
114 'MIDDLE_NAME=P',
115 NVL(
116 SUBSTR(UPPER(pai_gb.action_information7), 1, 36),
117 ' '),
118 'DIRECTOR_FLAG=P',
119 NVL(UPPER(pai_gb.action_information4), 'N'),
120 'EMPLOYEE_NUMBER=P',
121 NVL(UPPER(pai_emp.action_information10), ' '),
122 'NATIONAL_INS_NO=P',
123 NVL(UPPER(pai_emp.action_information4), 'NONE'),
124 'PERSON_ID=P', pai_person.action_information1,
125 'ADDRESS_LINE_1=P',
126 NVL(pai_person.action_information5, ' '),
127 'ADDRESS_LINE_2=P',
128 NVL(pai_person.action_information6, ' '),
129 'ADDRESS_LINE_3=P',
130 NVL(pai_person.action_information7, ' '),
131 'ADDRESS_LINE_4=P',
132 NVL(pai_person.action_information8, ' '),
133 'ADDRESS_LINE_5=P',
134 NVL(hl.meaning, ' ')
135 FROM pay_assignment_actions paa,
136 pay_action_information pai,
137 pay_action_information pai_emp,
138 pay_action_information pai_gb,
139 pay_action_information pai_person,
140 hr_lookups hl
141 WHERE paa.payroll_action_id =
142 pay_magtape_generic.get_parameter_value(
143 'ARCH_PAYROLL_ACTION_ID')
144 AND pai.action_context_id = paa.assignment_action_id
145 AND pai.action_information_category = 'EMEA PAYROLL INFO'
146 AND pai.action_information6 =
147 pay_magtape_generic.get_parameter_value(
148 'EMPLOYERS_REF_NO')
149 AND pai_person.action_context_id = paa.assignment_action_id
150 AND pai_person.action_information_category =
151 'ADDRESS DETAILS'
152 AND pai_person.action_information14 = 'Employee Address'
153 AND pai_gb.action_context_id = paa.assignment_action_id
154 AND pai_gb.action_information_category =
155 'GB EMPLOYEE DETAILS'
156 AND pai_emp.action_context_id = paa.assignment_action_id
157 AND pai_emp.action_information_category =
158 'EMPLOYEE DETAILS'
159 AND hl.lookup_type(+) = 'GB_COUNTY'
160 AND hl.lookup_code(+) = pai_person.action_information9;
161
162 CURSOR csr_p11d_emp_ben_catg
163 IS
164 SELECT /*+ ordered
165 use_nl(paa,pai)
166 use_nl(paa,pai_comp)
167 use_nl(paa,pai_person)
168 use_index(pai,pay_action_information_n2)
169 use_index(pai_comp,pay_action_information_n2)
170 use_index(pai_person,pay_action_information_n2)*/
171 'ACTION_INFORMATION_CATG=P',
172 pai.action_information_category,
173 DECODE(
174 pai.action_information_category,
175 'FPCS_CAERS', 'U',
176 'MARORS', 'U',
177 'TAXABLE EXPENSE PAYMENTS', 'V',
178 'ASSETS TRANSFERRED', 'A',
179 'ASSETS AT EMP DISPOSAL', 'L',
180 'EXPENSES PAYMENTS', 'O',
181 'INT FREE AND LOW INT LOANS', 'H',
182 'LIVING ACCOMMODATION', 'D',
183 'MILEAGE ALLOWANCE', 'E',
184 'MILEAGE ALLOWANCE AND PPAYMENT', 'E',
185 'OTHER ITEMS', 'N',
186 'PAYMENTS MADE FOR EMP', 'B',
187 'PVT MED TREATMENT OR INSURANCE', 'I',
188 'RELOCATION EXPENSES', 'J',
189 'SERVICES SUPPLIED', 'K',
190 'VANS 2002_03', 'G',
191 'VOUCHERS OR CREDIT CARDS', 'C',
192 'CAERS', 'U',
193 'OTHER ITEMS NON 1A', 'N',
194 'CAR AND CAR FUEL 2003_04', 'F') cat_order
195 FROM pay_assignment_actions paa,
196 pay_action_information pai_person,
197 pay_action_information pai_comp,
198 pay_action_information pai
199 WHERE paa.payroll_action_id =
200 pay_magtape_generic.get_parameter_value(
201 'ARCH_PAYROLL_ACTION_ID')
202 AND pai_comp.action_context_id = paa.assignment_action_id
203 AND pai_comp.action_information_category = 'EMEA PAYROLL INFO'
204 AND pai_comp.action_context_type = 'AAP'
205 AND pai_comp.action_information6 =
206 pay_magtape_generic.get_parameter_value('EMPLOYERS_REF_NO')
207 AND pai_person.action_context_id = paa.assignment_action_id
208 AND pai_person.action_information_category ='ADDRESS DETAILS'
209 AND pai_person.action_context_type = 'AAP'
210 AND pai_person.action_information14 = 'Employee Address'
211 AND pai_person.action_information1 =
212 pay_magtape_generic.get_parameter_value('PERSON_ID')
213 AND pai.action_context_id = paa.assignment_action_id
214 AND pai.action_context_type = 'AAP'
215 AND pai.action_information_category NOT IN
216 ('EMPLOYEE DETAILS',
217 'ADDRESS DETAILS',
218 'EMEA PAYROLL INFO',
219 'GB EMPLOYEE DETAILS',
220 'GB P11D ASSIGNMENT RESULTA',
221 'GB P11D ASSIGNMENT RESULTB',
222 'GB P11D ASSIGNMENT RESULTC')
223 AND (( pai.action_information_category = 'OTHER ITEMS NON 1A'
224 AND not exists (select 1
225 from pay_action_information pai1
226 where pai1.action_context_id = paa.assignment_action_id
227 and pai1.action_context_type = 'AAP'
228 and pai1.action_information_category = 'OTHER ITEMS')
229 )
230 OR
231 pai.action_information_category <> 'OTHER ITEMS NON 1A'
232 )
233 GROUP BY pai.action_information_category
234 ORDER BY cat_order;
235
236 CURSOR csr_p11d_emp_benefits
237 IS
238 SELECT /*+ ordered
239 use_nl(paa,pai)
240 use_nl(paa,pai_comp)
241 use_nl(paa,pai_person)
242 use_index(pai,pay_action_information_n2)
243 use_index(pai_comp,pay_action_information_n2)
244 use_index(pai_person,pay_action_information_n2)*/
245 'SCHEME_TYPE=P',
246 DECODE(
247 pai.action_information_category,
248 'TAXABLE EXPENSE PAYMENTS', pai.action_information6,
249 ' '),
250 'TYPE_OF_USER=P',
251 DECODE(
252 pai.action_information_category,
253 'TAXABLE EXPENSE PAYMENTS', pai.action_information7,
254 ' '),
255 'CLASS_OF_CAR=P',
256 DECODE(
257 pai.action_information_category,
258 'TAXABLE EXPENSE PAYMENTS', pai.action_information8,
259 ' '),
260 'REVENUE_RELIEF_RATE=P',
261 DECODE(
262 pai.action_information_category,
263 'CAERS', pai.action_information6,
264 'FPCS_CAERS', pai.action_information6,
265 ' '),
266 'MILEAGE_RATE=P',
267 DECODE(
268 pai.action_information_category,
269 'CAERS', NVL(pai.action_information8, 0),
270 'FPCS_CAERS', NVL(pai.action_information9, 0),
271 ' '),
272 'RECORD_IDENTIFIER=P',
273 DECODE(
274 pai.action_information_category,
275 'INT FREE AND LOW INT LOANS', pai.action_information1,
276 'OTHER ITEMS', 'OTHER ITEMS',
277 'OTHER ITEMS NON 1A', 'OTHER ITEMS NON 1A',
278 ' '),
279 'BENEFIT_START_DATE=P',
280 DECODE(
281 pai.action_information_category,
282 'CARS AND CAR FUEL', pai.action_information3,
283 'CARS AND CAR FUEL 2001 2002', pai.action_information3,
284 'CAR AND CAR FUEL', pai.action_information3,
285 'CAR AND CAR FUEL 2003_04', pai.action_information3,
286 ' '),
287 'BENEFIT_END_DATE=P',
288 DECODE(
289 pai.action_information_category,
290 'CARS AND CAR FUEL', pai.action_information4,
291 'CARS AND CAR FUEL 2001 2002', pai.action_information4,
292 'CAR AND CAR FUEL', pai.action_information4,
293 'CAR AND CAR FUEL 2003_04', pai.action_information4,
294 ' '),
295 'MAKE_OF_CAR=P',
296 DECODE(
297 pai.action_information_category,
298 'CARS AND CAR FUEL', UPPER(pai.action_information6),
299 'CARS AND CAR FUEL 2001 2002', UPPER(
300 pai.action_information6),
301 'CAR AND CAR FUEL', UPPER(pai.action_information6),
302 'CAR AND CAR FUEL 2003_04', UPPER(pai.action_information6),
303 ' '),
304 'MODEL=P',
305 DECODE(
306 pai.action_information_category,
307 'CARS AND CAR FUEL', UPPER(pai.action_information7),
308 'CARS AND CAR FUEL 2001 2002', UPPER(
309 pai.action_information7),
310 'CAR AND CAR FUEL', UPPER(pai.action_information7),
311 'CAR AND CAR FUEL 2003_04', UPPER(pai.action_information7),
312 ' '),
313 'DATE_FIRST_REGISTERED=P',
314 DECODE(
315 pai.action_information_category,
316 'CARS AND CAR FUEL', pai.action_information8,
317 'CARS AND CAR FUEL 2001 2002', pai.action_information8,
318 'CAR AND CAR FUEL', pai.action_information8,
319 'CAR AND CAR FUEL 2003_04', nvl(pai.action_information8,0),
320 '0'),
321 'LIST_PRICE=P',
322 DECODE(
323 pai.action_information_category,
327 'CAR AND CAR FUEL 2003_04', pai.action_information9,
324 'CARS AND CAR FUEL', pai.action_information9,
325 'CARS AND CAR FUEL 2001 2002', pai.action_information9,
326 'CAR AND CAR FUEL', pai.action_information9,
328 ' '),
329 'CASH_EQUIVALENT_FOR_CAR=P',
330 SUM(
331 DECODE(
332 pai.action_information_category,
333 'CARS AND CAR FUEL', NVL(pai.action_information10, 0),
334 'CARS AND CAR FUEL 2001 2002', NVL(
335 pai.action_information10,
336 0),
337 'CAR AND CAR FUEL', NVL(pai.action_information10, 0),
338 'CAR AND CAR FUEL 2003_04', NVL(pai.action_information10, 0),
339 '0') ),
340 'PRIMARY_INDICATOR=P',
341 DECODE(
342 pai.action_information_category,
343 'CARS AND CAR FUEL', NVL(
344 UPPER(pai.action_information11),
345 'N'),
346 'CARS AND CAR FUEL 2001 2002', NVL(
347 UPPER(
348 pai.action_information11),
349 'N'),
350 'CAR AND CAR FUEL 2003_04', NVL(pai.action_information27, 'N'),
351 -- for car this hold the FREE_FUEL_REINSTATED value
352 ' '),
353 'CASH_EQUIVALENT_OF_FUEL=P',
354 SUM(
355 DECODE(
356 pai.action_information_category,
357 'CARS AND CAR FUEL', NVL(pai.action_information12, 0),
358 'CARS AND CAR FUEL 2001 2002', NVL(
359 pai.action_information12,
360 0),
361 'CAR AND CAR FUEL', NVL(pai.action_information11, 0),
362 'CAR AND CAR FUEL 2003_04', NVL(pai.action_information11, 0),
363 '0') ),
364 'FUEL_TYPE=P',
365 DECODE(
366 pai.action_information_category,
367 'CARS AND CAR FUEL', NVL(
368 UPPER(pai.action_information13),
369 'DIESEL'),
370 'CARS AND CAR FUEL 2001 2002', NVL(
371 UPPER(
372 pai.action_information13),
373 'DIESEL'),
374 'CAR AND CAR FUEL', NVL(
375 UPPER(pai.action_information12),
376 'DIESEL'),
377 'CAR AND CAR FUEL 2003_04', NVL(
378 UPPER(pai.action_information12),
379 'DIESEL'),
380 ' '),
381 'OPTIONAL_ACCESSORIES_FITTED=P',
382 DECODE(
383 pai.action_information_category,
384 'CARS AND CAR FUEL', NVL(pai.action_information17, 0),
385 'CARS AND CAR FUEL 2001 2002', NVL(
386 pai.action_information18,
387 0),
388 'CAR AND CAR FUEL', NVL(pai.action_information16, 0),
389 'CAR AND CAR FUEL 2003_04', NVL(pai.action_information15, 0),
390 ' '),
391 'PRICE_OF_ACCESSORIES_ADDED_AFT=P',
392 DECODE(
393 pai.action_information_category,
394 'CARS AND CAR FUEL', NVL(pai.action_information18, 0),
395 'CARS AND CAR FUEL 2001 2002', NVL(
396 pai.action_information19,
397 0),
398 'CAR AND CAR FUEL', NVL(pai.action_information17, 0),
399 'CAR AND CAR FUEL 2003_04', NVL(pai.action_information26,0),
400 -- for 03 04 this holds the DATE_FREE_FUEL_WITHDRWAN
401 ' '),
402 'CAPITAL_CONTRIBUTIONS_MADE=P',
403 DECODE(
404 pai.action_information_category,
405 'CARS AND CAR FUEL', NVL(pai.action_information19, 0),
406 'CARS AND CAR FUEL 2001 2002', NVL(
407 pai.action_information27,
408 0),
409 'CAR AND CAR FUEL', NVL(pai.action_information18, 0),
410 'CAR AND CAR FUEL 2003_04', NVL(pai.action_information16, 0),
411 ' '),
412 'PRIVATE_USE_PAYMENTS=P',
413 DECODE(
414 pai.action_information_category,
415 'CARS AND CAR FUEL', NVL(pai.action_information20, 0),
416 'CARS AND CAR FUEL 2001 2002', NVL(
417 pai.action_information20,
418 0),
419 'CAR AND CAR FUEL', NVL(pai.action_information19, 0),
423 DECODE(
420 'CAR AND CAR FUEL 2003_04', NVL(pai.action_information17, 0),
421 ' '),
422 'ENGINE_CC_FOR_FUEL_CHARGE=P',
424 pai.action_information_category,
425 'CARS AND CAR FUEL', NVL(pai.action_information21, '9999'),
426 'CARS AND CAR FUEL 2001 2002', NVL(
427 pai.action_information21,
428 '9999'),
429 'CAR AND CAR FUEL', NVL(pai.action_information20, '9999'),
430 'CAR AND CAR FUEL 2003_04', NVL(pai.action_information18, '9999'),
431 ' '),
432 'MILEAGE_BAND=P', --CO2_EMISSIONS for CAR AND CAR FUEL element
433 DECODE(
434 pai.action_information_category,
435 'CARS AND CAR FUEL', NVL(
436 UPPER(pai.action_information22),
437 1),
438 'CARS AND CAR FUEL 2001 2002', NVL(
439 UPPER(
440 pai.action_information22),
441 1),
442 'CAR AND CAR FUEL', pai.action_information13, --CO2_EMISSIONS
443 'CAR AND CAR FUEL 2003_04', pai.action_information13, --CO2_EMISSIONS
444 ' '),
445 'ASSET_TYPE=P',
446 DECODE(
447 pai.action_information_category,
448 'ASSETS TRANSFERRED', pay_gb_p11d_magtape.get_description(
449 pai.action_information6,
450 'GB_ASSET_TYPE',
451 pai.action_information4),
452 ' '),
453 'ASSET_DESCRIPTION=P',
454 DECODE(
455 pai.action_information_category,
456 'ASSETS TRANSFERRED', UPPER(pai.action_information5),
457 ' '),
458 'ASSETS=P',
459 DECODE(
460 pai.action_information_category,
461 'ASSETS AT EMP DISPOSAL', pay_gb_p11d_magtape.get_description(
462 pai.action_information5,
463 'GB_ASSETS',
464 pai.action_information4),
465 ' '),
466 'EXPENSE_TYPE=P',
467 DECODE(
468 pai.action_information_category,
469 'EXPENSES PAYMENTS', pay_gb_p11d_magtape.get_description(
470 pai.action_information5,
471 'GB_EXPENSE_TYPE',
472 pai.action_information4),
473 ' '),
474 'OTHER_ITEMS=P',
475 DECODE(
476 pai.action_information_category,
477 'OTHER ITEMS', pay_gb_p11d_magtape.get_description(
478 pai.action_information5,
479 'GB_OTHER_ITEMS',
480 pai.action_information4),
481 'OTHER ITEMS NON 1A', pay_gb_p11d_magtape.get_description(
482 pai.action_information5,
483 'GB_OTHER_ITEMS_NON_1A',
484 pai.action_information4),
485 ' '),
486 'PAYMENTS_MADE=P',
487 DECODE(
488 pai.action_information_category,
489 'PAYMENTS MADE FOR EMP', pay_gb_p11d_magtape.get_description(
490 pai.action_information6,
491 'GB_PAYMENTS_MADE',
492 pai.action_information4),
493 ' '),
494 'NUMBER_OF_JOINT_BORROWERS=P',
495 DECODE(
496 pai.action_information_category,
497 'INT FREE AND LOW INT LOANS', NVL(
498 pai.action_information5,
499 1),
500 ' '),
501 'SHARES_INDICATOR=P',
502 DECODE(
503 pai.action_information_category,
504 'P11D SHARES', pai.action_information5,
505 ' '),
506 'TRADING_ORGANISATION_INDICATOR=P',
507 DECODE(
508 pai.action_information_category,
509 'EXPENSES PAYMENTS', NVL(
510 UPPER(pai.action_information10),
511 'N'),
512 ' '),
513 'DATE_LOAN_MADE=P',
514 DECODE(
515 pai.action_information_category,
516 'INT FREE AND LOW INT LOANS', NVL(
517 pai.action_information9,
518 ' '),
519 ' '),
520 'DATE_LOAN_DISCHARGED=P',
521 DECODE(
522 pai.action_information_category,
523 'INT FREE AND LOW INT LOANS', NVL(
527
524 pai.action_information10,
525 ' '),
526 ' '),
528 -- SUM FIELDS
529 'CASH_EQUIVALENT=P',
530 SUM(
531 DECODE(
532 pai.action_information_category,
533 'LIVING ACCOMMODATION', pai.action_information10,
534 'RELOCATION EXPENSES', pai.action_information5,
535 'PAYMENTS MADE FOR EMP', pai.action_information7,
536 'PVT MED TREATMENT OR INSURANCE', pai.action_information7,
537 'VOUCHERS OR CREDIT CARDS', pai.action_information11,
538 'ASSETS TRANSFERRED', pai.action_information9,
539 'ASSETS AT EMP DISPOSAL', pai.action_information9,
540 'EXPENSES PAYMENTS', pai.action_information8,
541 'OTHER ITEMS', pai.action_information9,
542 'OTHER ITEMS NON 1A', pai.action_information9,
543 'SERVICES SUPPLIED', pai.action_information7,
544 'VANS', pai.action_information5,
545 'VANS 2002_03', NVL(pai.action_information15, 0),
546 'INT FREE AND LOW INT LOANS', pai.action_information11,
547 '0') ),
548 'GROSS_AMOUNT=P',
549 SUM(
550 DECODE(
551 pai.action_information_category,
552 'MILEAGE ALLOWANCE', NVL(pai.action_information5, 0),
553 'VOUCHERS OR CREDIT CARDS', NVL(
554 pai.action_information6,
555 0),
556 '0') ),
557 'COST_TO_YOU=P',
558 SUM(
559 DECODE(
560 pai.action_information_category,
561 'PVT MED TREATMENT OR INSURANCE', round(decode(
562 pai.action_information5,
563 0,pai.ACTION_INFORMATION7,
564 pai.action_information5
565 ),2),
566 /*
567 'PVT MED TREATMENT OR INSURANCE', NVL(
568 pai.action_information5,
569 0), */
570 'EXPENSES PAYMENTS', NVL(pai.action_information6, 0),
571 'OTHER ITEMS', NVL(pai.action_information7, 0),
572 'OTHER ITEMS NON 1A', NVL(pai.action_information7, 0),
573 'SERVICES SUPPLIED', round(NVL(
574 pai.action_information5,
575 nvl(pai.ACTION_INFORMATION7,
576 0)),2),
577 /*
578 'SERVICES SUPPLIED', NVL(pai.action_information5, 0), */
579 '0') ),
580 'COST_OR_MARKET_VALUE=P',
581 SUM(
582 DECODE(
583 pai.action_information_category,
584 'ASSETS TRANSFERRED', NVL(pai.action_information7, 0),
585 '0') ),
586 'ANNUAL_VALUE=P',
587 SUM(
588 DECODE(
589 pai.action_information_category,
590 'ASSETS AT EMP DISPOSAL', NVL(
591 pai.action_information7,
592 NVL(pai.ACTION_INFORMATION9,
593 '0')),
594 /*
595 'ASSETS AT EMP DISPOSAL', NVL(
596 pai.action_information7,
597 '0'),*/
598 '0') ),
599 'AMOUNT_OUTSTANDING_AT_5TH_APRI=P',
600 SUM(
601 DECODE(
602 pai.action_information_category,
603 'INT FREE AND LOW INT LOANS', NVL(
604 pai.action_information6,
605 0),
606 '0') ),
607 'AMOUNT_MADE_GOOD=P',
608 SUM(
609 DECODE(
610 pai.action_information_category,
611 'MILEAGE ALLOWANCE', NVL(pai.action_information6, 0),
612 'PVT MED TREATMENT OR INSURANCE', NVL(
613 pai.action_information6,
614 0),
615 'VOUCHERS OR CREDIT CARDS', NVL(
616 pai.action_information7,
617 0),
618 'ASSETS TRANSFERRED', NVL(pai.action_information8, 0),
619 'ASSETS AT EMP DISPOSAL', NVL(
620 pai.action_information8,
621 0),
622 'EXPENSES PAYMENTS', NVL(pai.action_information7, 0),
623 'OTHER ITEMS', NVL(pai.action_information8, 0),
627 'MAXIMUM_AMOUNT_OUTSTANDING=P',
624 'OTHER ITEMS NON 1A', NVL(pai.action_information8, 0),
625 'SERVICES SUPPLIED', NVL(pai.action_information6, 0),
626 '0') ),
628 SUM(
629 DECODE(
630 pai.action_information_category,
631 'INT FREE AND LOW INT LOANS', NVL(
632 pai.action_information7,
633 0),
634 '0') ),
635 'TAXABLE_PAYMENT=P',
636 MAX(
637 DECODE(
638 pai.action_information_category,
639 'MILEAGE ALLOWANCE', NVL(pai.action_information7, 0),
640
641 'MILEAGE ALLOWANCE AND PPAYMENT', NVL(pai_resultA.action_information12,
642 0),
643 '0') ),
644 'TAX_ON_NOTIONAL_PAYMENTS=P',
645 SUM(
646 DECODE(
647 pai.action_information_category,
648 'PAYMENTS MADE FOR EMP', NVL(pai.action_information8, 0),
649 '0') ),
650 'TOTAL_AMOUNT_OF_INTEREST_PAID=P',
651 SUM(
652 DECODE(
653 pai.action_information_category,
654 'INT FREE AND LOW INT LOANS', NVL(
655 pai.action_information8,
656 0),
657 '0') ),
658 'AMOUNT_OUTSTANDING_AT_YEAR_END=P',
659 SUM(
660 DECODE(
661 pai.action_information_category,
662 'INT FREE AND LOW INT LOANS', NVL(
663 pai.action_information16,
664 0),
665 '0') ),
666 'MILEAGE=P',
667 SUM(
668 DECODE(
669 pai.action_information_category,
670 'FPCS_CAERS', NVL(pai.action_information10, 0),
671 'CAERS', NVL(pai.action_information9, 0),
672 0) ),
673 'CAR_OR_MILEAGE_ALLOWANCE=P',
674 SUM(
675 DECODE(
676 pai.action_information_category,
677 'FPCS_CAERS', pai.action_information11,
678 'CAERS', pai.action_information10,
679 'MARORS', NVL(pai.action_information7, 0),
680 '0') ),
681 'LUMP_SUM_PAYMENTS=P',
682 SUM(
683 DECODE(
684 pai.action_information_category,
685 'FPCS_CAERS', pai.action_information12,
686 'CAERS', pai.action_information11,
687 '0') ),
688 'TAXABLE_BENEFIT=P',
689 SUM(
690 DECODE(
691 pai.action_information_category,
692 'FPCS_CAERS', pai.action_information13,
693 'CAERS', pai.action_information12,
694 'TAXABLE EXPENSE PAYMENTS', pai.action_information10,
695 '0') ),
696 'BUSINESS_MILES=P',
697 SUM(
698 DECODE(
699 pai.action_information_category,
700 'TAXABLE EXPENSE PAYMENTS', pai.action_information9,
701 '0') )
702 FROM pay_assignment_actions paa,
703 pay_action_information pai_person,
704 pay_action_information pai_comp,
705 pay_action_information pai_resultA,
706 pay_action_information pai
707 WHERE paa.payroll_action_id =
708 pay_magtape_generic.get_parameter_value(
709 'ARCH_PAYROLL_ACTION_ID')
710 AND pai_comp.action_context_id = paa.assignment_action_id
711 AND pai_comp.action_information_category = 'EMEA PAYROLL INFO'
712 AND pai_comp.action_context_type = 'AAP'
713 AND pai_comp.action_information6 =
714 pay_magtape_generic.get_parameter_value('EMPLOYERS_REF_NO')
715
716 AND pai_resultA.action_context_id = paa.assignment_action_id
717 AND pai_resultA.action_information_category = 'GB P11D ASSIGNMENT RESULTA'
718 AND pai_resultA.action_context_type = 'AAP'
719
720 AND pai_person.action_context_id = paa.assignment_action_id
721 AND pai_person.action_information_category = 'ADDRESS DETAILS'
722 AND pai_person.action_context_type = 'AAP'
723 AND pai_person.action_information14 = 'Employee Address'
724 AND pai_person.action_information1 =
725 pay_magtape_generic.get_parameter_value('PERSON_ID')
726 AND pai.action_context_id = paa.assignment_action_id
727 AND pai.action_context_type = 'AAP'
728 AND pai.action_information_category LIKE
729 ( pay_magtape_generic.get_parameter_value(
730 'ACTION_INFORMATION_CATG')
731 || '%')
732 GROUP BY 'SCHEME_TYPE=P',
733 DECODE(
734 pai.action_information_category,
738 DECODE(
735 'TAXABLE EXPENSE PAYMENTS', pai.action_information6,
736 ' '),
737 'TYPE_OF_USER=P',
739 pai.action_information_category,
740 'TAXABLE EXPENSE PAYMENTS', pai.action_information7,
741 ' '),
742 'CLASS_OF_CAR=P',
743 DECODE(
744 pai.action_information_category,
745 'TAXABLE EXPENSE PAYMENTS', pai.action_information8,
746 ' '),
747 'REVENUE_RELIEF_RATE=P',
748 DECODE(
749 pai.action_information_category,
750 'CAERS', pai.action_information6,
751 'FPCS_CAERS', pai.action_information6,
752 ' '),
753 'MILEAGE_RATE=P',
754 DECODE(
755 pai.action_information_category,
756 'CAERS', NVL(pai.action_information8, 0),
757 'FPCS_CAERS', NVL(pai.action_information9, 0),
758 ' '),
759 'RECORD_IDENTIFIER=P',
760 DECODE(
761 pai.action_information_category,
762 'INT FREE AND LOW INT LOANS', pai.action_information1,
763 'OTHER ITEMS', 'OTHER ITEMS',
764 'OTHER ITEMS NON 1A', 'OTHER ITEMS NON 1A',
765 ' '),
766 'BENEFIT_START_DATE=P',
767 DECODE(
768 pai.action_information_category,
769 'CARS AND CAR FUEL', pai.action_information3,
770 'CARS AND CAR FUEL 2001 2002', pai.action_information3,
771 'CAR AND CAR FUEL', pai.action_information3,
772 'CAR AND CAR FUEL 2003_04', pai.action_information3,
773 ' '),
774 'BENEFIT_END_DATE=P',
775 DECODE(
776 pai.action_information_category,
777 'CARS AND CAR FUEL', pai.action_information4,
778 'CARS AND CAR FUEL 2001 2002', pai.action_information4,
779 'CAR AND CAR FUEL', pai.action_information4,
780 'CAR AND CAR FUEL 2003_04', pai.action_information4,
781 ' '),
782 'MAKE_OF_CAR=P',
783 DECODE(
784 pai.action_information_category,
785 'CARS AND CAR FUEL', UPPER(pai.action_information6),
786 'CARS AND CAR FUEL 2001 2002', UPPER(
787 pai.action_information6),
788 'CAR AND CAR FUEL', UPPER(pai.action_information6),
789 'CAR AND CAR FUEL 2003_04', UPPER(pai.action_information6),
790 ' '),
791 'MODEL=P',
792 DECODE(
793 pai.action_information_category,
794 'CARS AND CAR FUEL', UPPER(pai.action_information7),
795 'CARS AND CAR FUEL 2001 2002', UPPER(
796 pai.action_information7),
797 'CAR AND CAR FUEL', UPPER(pai.action_information7),
798 'CAR AND CAR FUEL 2003_04', UPPER(pai.action_information7),
799 ' '),
800 'DATE_FIRST_REGISTERED=P',
801 DECODE(
802 pai.action_information_category,
803 'CARS AND CAR FUEL', pai.action_information8,
804 'CARS AND CAR FUEL 2001 2002', pai.action_information8,
805 'CAR AND CAR FUEL', pai.action_information8,
806 'CAR AND CAR FUEL 2003_04', nvl(pai.action_information8,0),
807 '0'),
808 'LIST_PRICE=P',
809 DECODE(
810 pai.action_information_category,
811 'CARS AND CAR FUEL', pai.action_information9,
812 'CARS AND CAR FUEL 2001 2002', pai.action_information9,
813 'CAR AND CAR FUEL', pai.action_information9,
814 'CAR AND CAR FUEL 2003_04', pai.action_information9,
815 ' '),
816 'PRIMARY_INDICATOR=P',
817 DECODE(
818 pai.action_information_category,
819 'CARS AND CAR FUEL', NVL(
820 UPPER(pai.action_information11),
821 'N'),
822 'CARS AND CAR FUEL 2001 2002', NVL(
823 UPPER(
824 pai.action_information11),
825 'N'),
826 'CAR AND CAR FUEL 2003_04', NVL(pai.action_information27, 'N'),
827 -- for car this hold the FREE_FUEL_REINSTATED value
828 ' '),
829 'FUEL_TYPE=P',
830 DECODE(
831 pai.action_information_category,
832 'CARS AND CAR FUEL', NVL(
833 UPPER(pai.action_information13),
834 'DIESEL'),
835 'CARS AND CAR FUEL 2001 2002', NVL(
836 UPPER(
837 pai.action_information13),
841 'DIESEL'),
838 'DIESEL'),
839 'CAR AND CAR FUEL', NVL(
840 UPPER(pai.action_information12),
842 'CAR AND CAR FUEL 2003_04', NVL(
843 UPPER(pai.action_information12),
844 'DIESEL'),
845 ' '),
846 'OPTIONAL_ACCESSORIES_FITTED=P',
847 DECODE(
848 pai.action_information_category,
849 'CARS AND CAR FUEL', NVL(pai.action_information17, 0),
850 'CARS AND CAR FUEL 2001 2002', NVL(
851 pai.action_information18,
852 0),
853 'CAR AND CAR FUEL', NVL(pai.action_information16, 0),
854 'CAR AND CAR FUEL 2003_04', NVL(pai.action_information15, 0),
855 ' '),
856 'PRICE_OF_ACCESSORIES_ADDED_AFT=P',
857 DECODE(
858 pai.action_information_category,
859 'CARS AND CAR FUEL', NVL(pai.action_information18, 0),
860 'CARS AND CAR FUEL 2001 2002', NVL(
861 pai.action_information19,
862 0),
863 'CAR AND CAR FUEL', NVL(pai.action_information17, 0),
864 'CAR AND CAR FUEL 2003_04', NVL(pai.action_information26,0),
865 -- for 03 04 this holds the DATE_FREE_FUEL_WITHDRWAN
866 ' '),
867 'CAPITAL_CONTRIBUTIONS_MADE=P',
868 DECODE(
869 pai.action_information_category,
870 'CARS AND CAR FUEL', NVL(pai.action_information19, 0),
871 'CARS AND CAR FUEL 2001 2002', NVL(
872 pai.action_information27,
873 0),
874 'CAR AND CAR FUEL', NVL(pai.action_information18, 0),
875 'CAR AND CAR FUEL 2003_04', NVL(pai.action_information16, 0),
876 ' '),
877 'PRIVATE_USE_PAYMENTS=P',
878 DECODE(
879 pai.action_information_category,
880 'CARS AND CAR FUEL', NVL(pai.action_information20, 0),
881 'CARS AND CAR FUEL 2001 2002', NVL(
882 pai.action_information20,
883 0),
884 'CAR AND CAR FUEL', NVL(pai.action_information19, 0),
885 'CAR AND CAR FUEL 2003_04', NVL(pai.action_information17, 0),
886 ' '),
887 'ENGINE_CC_FOR_FUEL_CHARGE=P',
888 DECODE(
889 pai.action_information_category,
890 'CARS AND CAR FUEL', NVL(pai.action_information21, '9999'),
891 'CARS AND CAR FUEL 2001 2002', NVL(
892 pai.action_information21,
893 '9999'),
894 'CAR AND CAR FUEL', NVL(pai.action_information20, '9999'),
895 'CAR AND CAR FUEL 2003_04', NVL(pai.action_information18, '9999'),
896 ' '),
897 'MILEAGE_BAND=P', --CO2_EMISSIONS for CAR AND CAR FUEL element
898 DECODE(
899 pai.action_information_category,
900 'CARS AND CAR FUEL', NVL(
901 UPPER(pai.action_information22),
902 1),
903 'CARS AND CAR FUEL 2001 2002', NVL(
904 UPPER(
905 pai.action_information22),
906 1),
907 'CAR AND CAR FUEL', pai.action_information13, --CO2_EMISSIONS
908 'CAR AND CAR FUEL 2003_04', pai.action_information13, --CO2_EMISSIONS
909 ' '),
910 'ASSET_TYPE=P',
911 DECODE(
912 pai.action_information_category,
913 'ASSETS TRANSFERRED', pay_gb_p11d_magtape.get_description(
914 pai.action_information6,
915 'GB_ASSET_TYPE',
916 pai.action_information4),
917 ' '),
918 'ASSET_DESCRIPTION=P',
919 DECODE(
920 pai.action_information_category,
921 'ASSETS TRANSFERRED', UPPER(pai.action_information5),
922 ' '),
923 'ASSETS=P',
924 DECODE(
925 pai.action_information_category,
926 'ASSETS AT EMP DISPOSAL', pay_gb_p11d_magtape.get_description(
927 pai.action_information5,
928 'GB_ASSETS',
929 pai.action_information4),
930 ' '),
931 'EXPENSE_TYPE=P',
932 DECODE(
933 pai.action_information_category,
934 'EXPENSES PAYMENTS', pay_gb_p11d_magtape.get_description(
935 pai.action_information5,
936 'GB_EXPENSE_TYPE',
937 pai.action_information4),
941 pai.action_information_category,
938 ' '),
939 'OTHER_ITEMS=P',
940 DECODE(
942 'OTHER ITEMS', pay_gb_p11d_magtape.get_description(
943 pai.action_information5,
944 'GB_OTHER_ITEMS',
945 pai.action_information4),
946 'OTHER ITEMS NON 1A', pay_gb_p11d_magtape.get_description(
947 pai.action_information5,
948 'GB_OTHER_ITEMS_NON_1A',
949 pai.action_information4),
950 ' '),
951 'PAYMENTS_MADE=P',
952 DECODE(
953 pai.action_information_category,
954 'PAYMENTS MADE FOR EMP', pay_gb_p11d_magtape.get_description(
955 pai.action_information6,
956 'GB_PAYMENTS_MADE',
957 pai.action_information4),
958 ' '),
959 'NUMBER_OF_JOINT_BORROWERS=P',
960 DECODE(
961 pai.action_information_category,
962 'INT FREE AND LOW INT LOANS', NVL(
963 pai.action_information5,
964 1),
965 ' '),
966 'SHARES_INDICATOR=P',
967 DECODE(
968 pai.action_information_category,
969 'P11D SHARES', pai.action_information5,
970 ' '),
971 'TRADING_ORGANISATION_INDICATOR=P',
972 DECODE(
973 pai.action_information_category,
974 'EXPENSES PAYMENTS', NVL(
975 UPPER(pai.action_information10),
976 'N'),
977 ' '),
978 'DATE_LOAN_MADE=P',
979 DECODE(
980 pai.action_information_category,
981 'INT FREE AND LOW INT LOANS', NVL(
982 pai.action_information9,
983 ' '),
984 ' '),
985 'DATE_LOAN_DISCHARGED=P',
986 DECODE(
987 pai.action_information_category,
988 'INT FREE AND LOW INT LOANS', NVL(
989 pai.action_information10,
990 ' '),
991 ' '),
992
993 -- SUM FIELDS
994 'CASH_EQUIVALENT=P',
995 'GROSS_AMOUNT=P',
996 'COST_TO_YOU=P',
997 'COST_OR_MARKET_VALUE=P',
998 'ANNUAL_VALUE=P',
999 'AMOUNT_OUTSTANDING_AT_5TH_APRI=P',
1000 'AMOUNT_MADE_GOOD=P',
1001 'MAXIMUM_AMOUNT_OUTSTANDING=P',
1002 'TAXABLE_PAYMENT=P',
1003 'TAX_ON_NOTIONAL_PAYMENTS=P',
1004 'TOTAL_AMOUNT_OF_INTEREST_PAID=P',
1005 'AMOUNT_OUTSTANDING_AT_YEAR_END=P',
1006 'MILEAGE=P',
1007 'CAR_OR_MILEAGE_ALLOWANCE=P',
1008 'LUMP_SUM_PAYMENTS=P',
1009 'TAXABLE_BENEFIT=P',
1010 'BUSINESS_MILES=P';
1011
1012
1013 --
1014 -- PROCEDURE range_cursor
1015 -- Procedure which stamps the payroll action with the PAYROLL_ID (if
1016 -- supplied), then returns a varchar2 defining a SQL Stateent to select
1017 -- all the people in the business group.
1018 -- The archiver uses this cursor to split the people into chunks for parallel
1019 -- processing.
1020 PROCEDURE range_cursor(pactid IN NUMBER, sqlstr OUT NOCOPY VARCHAR2);
1021
1022
1023 --
1024 PROCEDURE action_creation(
1025 pactid IN NUMBER,
1026 stperson IN NUMBER,
1027 endperson IN NUMBER,
1028 CHUNK IN NUMBER);
1029 END pay_gb_p11d_magtape;