[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_P11D_EDI_2008
Source
1 PACKAGE BODY PAY_GB_P11D_EDI_2008 AS
2 /* $Header: pygbp11d08.pkb 120.3.12010000.11 2008/12/02 07:32:06 smeduri ship $ */
3 --type type_output is table of varchar2(255) index by binary_integer;
4 /******************* BEGIN PRIVATE FUNCTIONS/PROCEDURES ********************/
5
6 /******************* Assets Transferred (Multi Occurance) ***********************/
7 procedure get_asset_transferred(p_person_id in varchar2,
8 p_emp_ref in varchar2,
9 p_pact_id in varchar2,
10 p_edi_rec1 out NOCOPY varchar2,
11 p_edi_rec2 out NOCOPY varchar2,
12 p_edi_rec3 out NOCOPY varchar2,
13 p_edi_rec4 out NOCOPY varchar2,
14 p_edi_rec5 out NOCOPY varchar2,
15 p_edi_rec6 out NOCOPY varchar2,
16 p_edi_rec7 out NOCOPY varchar2,
17 p_edi_rec8 out NOCOPY varchar2,
18 p_edi_rec9 out NOCOPY varchar2,
19 p_edi_rec10 out NOCOPY varchar2,
20 p_edi_rec11 out NOCOPY varchar2,
21 p_edi_rec12 out NOCOPY varchar2,
22 p_edi_rec13 out NOCOPY varchar2,
23 p_edi_rec14 out NOCOPY varchar2,
24 p_edi_rec15 out NOCOPY varchar2,
25 p_edi_rec16 out NOCOPY varchar2,
26 p_edi_rec17 out NOCOPY varchar2,
27 p_edi_rec18 out NOCOPY varchar2,
28 p_edi_rec19 out NOCOPY varchar2,
29 p_edi_rec20 out NOCOPY varchar2,
30 p_edi_rec21 out NOCOPY varchar2,
31 p_edi_rec22 out NOCOPY varchar2)
32 is
33 type r_assets is record(
34 description varchar2(70),
35 other_description varchar2(70),
36 cost_or_mkt_value varchar2(50),
37 cash_equivalent varchar2(50),
38 amount_made_good varchar2(50));
39
40 type t_assets is table of r_assets index by binary_integer;
41 type t_edi_record is table of varchar2(255) index by binary_integer;
42
43 edi_record t_edi_record;
44 assets t_assets;
45 l_total number;
46 l_count number;
47 l_cost number;
48 l_index number;
49 l_edi number;
50
51 edi_ftx1a varchar2(6);
52 edi_tax1 varchar2(6);
53 edi_moa1 varchar2(6);
54 edi_currency varchar2(3);
55 edi_cat varchar2(18);
56 edi_tax_qualifier1 varchar2(3);
57 edi_tax_qualifier8 varchar2(3);
58 edi_tax_qualifier12 varchar2(3);
59 edi_tax_qualifier115 varchar2(3);
60
61 cursor get_data is
62 select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
63 use_index(pai_person,pay_action_information_n2)
64 use_index(pai,pay_action_information_n2) */
65 sum(to_number(nvl(pai.action_information9,0))) cash_equivalent,
66 sum(to_number(nvl(pai.action_information7,0))) cost_or_mkt_value,
67 sum(to_number(nvl(pai.action_information8,0))) amount_made_good,
68 upper(max(pai.action_information5)) asset_description,
69 pay_gb_p11d_magtape.get_description(pai.action_information6,'GB_ASSET_TYPE',
70 pai.action_information4) asset_type
71 from per_all_assignments_f paf,
72 pay_assignment_actions paa,
73 pay_action_information pai,
74 pay_action_information pai_person
75 where paf.person_id = p_person_id
76 and paf.effective_end_date = (select max(paf2.effective_end_date)
77 from per_all_assignments_f paf2
78 where paf2.assignment_id = paf.assignment_id
79 and paf2.person_id = p_person_id)
80 and paf.assignment_id = paa.assignment_id
81 and paa.payroll_action_id = p_pact_id
82 and pai.action_context_id = paa.assignment_action_id
83 and pai.action_context_type = 'AAP'
84 and pai.action_information_category = 'ASSETS TRANSFERRED'
85 and pai_person.action_context_id = paa.assignment_action_id
86 and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
87 and upper(pai_person.action_information13) = upper(p_emp_ref)
88 and pai_person.action_context_type = 'AAP'
89 group by pay_gb_p11d_magtape.get_description(pai.action_information6,'GB_ASSET_TYPE',
90 pai.action_information4);
91 begin
92 edi_cat := rpad('A',18);
93 edi_ftx1a := rpad('FTX1A',6);
94 edi_moa1 := rpad('MOA1',6);
95 edi_tax1 := rpad('TAX1',6);
96 edi_currency := 'GBP';
97 edi_tax_qualifier1 := rpad('1',3);
98 edi_tax_qualifier8 := rpad('8',3);
99 edi_tax_qualifier12 := rpad('12',3);
100 edi_tax_qualifier115 := rpad('115',3);
101
102 l_total := 0;
103 l_count := 0;
104 for asset in get_data loop
105 if asset.cash_equivalent >= 1 then
106 l_count := l_count + 1;
107 l_total := l_total + asset.cash_equivalent;
108 l_cost := asset.cash_equivalent + asset.amount_made_good;
109 assets(l_count).description := substr(asset.asset_type,1,70);
110 assets(l_count).cost_or_mkt_value:= pay_gb_p11d_magtape.format_edi_currency(l_cost);
111 assets(l_count).cash_equivalent := pay_gb_p11d_magtape.format_edi_currency(asset.cash_equivalent);
112 assets(l_count).amount_made_good := pay_gb_p11d_magtape.format_edi_currency(asset.amount_made_good);
113 if asset.asset_type = 'OTHER' then
114 assets(l_count).other_description := substr(asset.asset_description,1,70);
115 else
116 assets(l_count).other_description := ' ';
117 end if;
118 end if;
119 end loop;
120
121 l_edi := 0;
122 --- This can repeat up to 5 times
123 for l_index in 1..l_count loop
124 l_edi := l_edi + 1;
125 edi_record(l_edi) := edi_ftx1a || edi_cat || rpad(assets(l_index).description, 71) ||
126 rpad(assets(l_index).other_description, 71) || rpad(' ',70) || fnd_global.local_chr(10);
127 l_edi := l_edi + 1;
128 edi_record(l_edi) := edi_tax1 || edi_tax_qualifier8 || fnd_global.local_chr(10) ||
129 edi_moa1 || assets(l_index).cost_or_mkt_value || edi_currency || fnd_global.local_chr(10);
130 l_edi := l_edi + 1;
131 edi_record(l_edi) := edi_tax1 || edi_tax_qualifier1 || fnd_global.local_chr(10) ||
132 edi_moa1 || assets(l_index).amount_made_good || edi_currency || fnd_global.local_chr(10);
133 l_edi := l_edi + 1;
134 edi_record(l_edi) := edi_tax1 || edi_tax_qualifier12 || fnd_global.local_chr(10) ||
135 edi_moa1 || assets(l_index).cash_equivalent || edi_currency || fnd_global.local_chr(10);
136 end loop;
137
138 if (l_total >= 1) then
139 edi_cat := rpad('Q',18);
140 l_edi := l_edi + 1;
141 edi_record(l_edi) := edi_ftx1a || edi_cat || rpad(' ',71) || rpad(' ',71) || rpad(' ',70) || fnd_global.local_chr(10);
142 l_edi := l_edi + 1;
143 edi_record(l_edi) := edi_tax1 || edi_tax_qualifier115 || fnd_global.local_chr(10) ||
144 edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_total) || edi_currency || fnd_global.local_chr(10);
145 end if;
146
147 -- Maximum output is 22 + 1 edi records
148 l_edi := l_edi + 1;
149 for l_index in l_edi..23 loop
150 edi_record(l_index) := null;
151 end loop;
152
153 p_edi_rec1 := edi_record(1);
154 p_edi_rec2 := edi_record(2);
155 p_edi_rec3 := edi_record(3);
156 p_edi_rec4 := edi_record(4);
157 p_edi_rec5 := edi_record(5);
158 p_edi_rec6 := edi_record(6);
159 p_edi_rec7 := edi_record(7);
160 p_edi_rec8 := edi_record(8);
161 p_edi_rec9 := edi_record(9);
162 p_edi_rec10 := edi_record(10);
163 p_edi_rec11 := edi_record(11);
164 p_edi_rec12 := edi_record(12);
165 p_edi_rec13 := edi_record(13);
166 p_edi_rec14 := edi_record(14);
167 p_edi_rec15 := edi_record(15);
168 p_edi_rec16 := edi_record(16);
169 p_edi_rec17 := edi_record(17);
170 p_edi_rec18 := edi_record(18);
171 p_edi_rec19 := edi_record(19);
172 p_edi_rec20 := edi_record(20);
173 p_edi_rec21 := edi_record(21);
174 p_edi_rec22 := edi_record(22);
175 end get_asset_transferred;
176 /******************* Assets Transferred (Multi Occurance) ***********************/
177
178 /******************* Payment Made For Emp (Multi Occurance) ***********************/
179 procedure get_payments_for_emp(p_person_id in varchar2,
180 p_emp_ref in varchar2,
181 p_pact_id in varchar2,
182 p_edi_rec1 out NOCOPY varchar2,
183 p_edi_rec2 out NOCOPY varchar2,
184 p_edi_rec3 out NOCOPY varchar2,
185 p_edi_rec4 out NOCOPY varchar2,
186 p_edi_rec5 out NOCOPY varchar2,
187 p_edi_rec6 out NOCOPY varchar2,
188 p_edi_rec7 out NOCOPY varchar2,
189 p_edi_rec8 out NOCOPY varchar2,
190 p_edi_rec9 out NOCOPY varchar2,
191 p_edi_rec10 out NOCOPY varchar2,
192 p_edi_rec11 out NOCOPY varchar2,
193 p_edi_rec12 out NOCOPY varchar2,
194 p_edi_rec13 out NOCOPY varchar2,
195 p_edi_rec14 out NOCOPY varchar2,
196 p_edi_rec15 out NOCOPY varchar2,
197 p_edi_rec16 out NOCOPY varchar2,
198 p_edi_rec17 out NOCOPY varchar2)
199 is
200 type r_payments is record(
201 description varchar2(70),
202 cash_equivalent varchar2(50));
203
204 type t_payments is table of r_payments index by binary_integer;
205 type t_edi_record is table of varchar2(255) index by binary_integer;
206
207 edi_record t_edi_record;
208 payments t_payments;
209 l_total number;
210 l_count number;
211 l_notional number;
212 l_index number;
213 l_edi number;
214
215 edi_ftx1a varchar2(6);
216 edi_tax1 varchar2(6);
217 edi_moa1 varchar2(6);
218 edi_currency varchar2(3);
219 edi_cat varchar2(18);
220 edi_tax_qualifier4 varchar2(3);
221 edi_tax_qualifier116 varchar2(3);
222 edi_tax_qualifier117 varchar2(3);
223
224 cursor get_data is
225 select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
226 use_index(pai_person,pay_action_information_n2)
227 use_index(pai,pay_action_information_n2) */
228 sum(to_number(nvl(pai.action_information7,0))) cash_equivalent,
229 sum(to_number(nvl(pai.action_information8,0))) tax_on_notional_payments,
230 --UPPER(pai.action_information5) payment_description,
231 pay_gb_p11d_magtape.get_description(pai.action_information6,'GB_PAYMENTS_MADE',
232 pai.action_information4) payment_type
233 from per_all_assignments_f paf,
234 pay_assignment_actions paa,
235 pay_action_information pai,
236 pay_action_information pai_person
237 where paf.person_id = p_person_id
238 and paf.effective_end_date = (select max(paf2.effective_end_date)
239 from per_all_assignments_f paf2
240 where paf2.assignment_id = paf.assignment_id
241 and paf2.person_id = p_person_id)
242 and paf.assignment_id = paa.assignment_id
243 and paa.payroll_action_id = p_pact_id
244 and pai.action_context_id = paa.assignment_action_id
245 and pai.action_context_type = 'AAP'
246 and pai.action_information_category = 'PAYMENTS MADE FOR EMP'
247 and pai_person.action_context_id = paa.assignment_action_id
248 and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
249 and upper(pai_person.action_information13) = upper(p_emp_ref)
250 and pai_person.action_context_type = 'AAP'
251 group by pay_gb_p11d_magtape.get_description(pai.action_information6,'GB_PAYMENTS_MADE',
252 pai.action_information4);
253 begin
254 edi_cat := rpad('B',18);
255 edi_ftx1a := rpad('FTX1A',6);
256 edi_moa1 := rpad('MOA1',6);
257 edi_tax1 := rpad('TAX1',6);
258 edi_currency := 'GBP';
259 edi_tax_qualifier4 := rpad('4',3);
260 edi_tax_qualifier116 := rpad('116',3);
261 edi_tax_qualifier117 := rpad('117',3);
262
263 l_total := 0;
264 l_count := 0;
265 l_notional := 0;
266 for payment in get_data loop
267 l_notional := l_notional + payment.tax_on_notional_payments;
268 if payment.cash_equivalent >= 1 then
269 l_count := l_count + 1;
270 l_total := l_total + payment.cash_equivalent;
271 payments(l_count).description := substr(payment.payment_type,1,70);
272 payments(l_count).cash_equivalent:= pay_gb_p11d_magtape.format_edi_currency(payment.cash_equivalent);
273 end if;
274 end loop;
275
276 l_edi := 0;
277 --- This can repeat up to 7 times
278 for l_index in 1..l_count loop
279 l_edi := l_edi + 1;
280 edi_record(l_edi) := edi_ftx1a || edi_cat || rpad(payments(l_index).description, 71) || rpad(' ', 71) || rpad(' ',70) || fnd_global.local_chr(10);
281 l_edi := l_edi + 1;
282 edi_record(l_edi) := edi_tax1 || edi_tax_qualifier4 || fnd_global.local_chr(10) ||
283 edi_moa1 || payments(l_index).cash_equivalent || edi_currency || fnd_global.local_chr(10);
284 end loop;
285
286 if (l_total >= 1) then
287 edi_cat := rpad('R',18);
288 l_edi := l_edi + 1;
289 edi_record(l_edi) := edi_ftx1a || edi_cat || rpad(' ',71) || rpad(' ',71) || rpad(' ',70) || fnd_global.local_chr(10);
290 l_edi := l_edi + 1;
291 edi_record(l_edi) := edi_tax1 || edi_tax_qualifier116 || fnd_global.local_chr(10) ||
292 edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_total) || edi_currency || fnd_global.local_chr(10);
293 l_edi := l_edi + 1;
294 edi_record(l_edi) := edi_tax1 || edi_tax_qualifier117 || fnd_global.local_chr(10) ||
295 edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_notional) || edi_currency || fnd_global.local_chr(10);
296 end if;
297
298 -- Maximum output is 17 + 1 edi records
299 l_edi := l_edi + 1;
300 for l_index in l_edi..18 loop
301 edi_record(l_index) := null;
302 end loop;
303
304 p_edi_rec1 := edi_record(1);
305 p_edi_rec2 := edi_record(2);
306 p_edi_rec3 := edi_record(3);
307 p_edi_rec4 := edi_record(4);
308 p_edi_rec5 := edi_record(5);
309 p_edi_rec6 := edi_record(6);
313 p_edi_rec10 := edi_record(10);
310 p_edi_rec7 := edi_record(7);
311 p_edi_rec8 := edi_record(8);
312 p_edi_rec9 := edi_record(9);
314 p_edi_rec11 := edi_record(11);
315 p_edi_rec12 := edi_record(12);
316 p_edi_rec13 := edi_record(13);
317 p_edi_rec14 := edi_record(14);
318 p_edi_rec15 := edi_record(15);
319 p_edi_rec16 := edi_record(16);
320 p_edi_rec17 := edi_record(17);
321 end get_payments_for_emp;
322 /******************* Payment Made For Emp (Multi Occurance) ***********************/
323
324 /******************* Voucher or Credit Cards (Single Occurance) ***********************/
325 procedure get_voucher_n_creditcard(p_person_id in varchar2,
326 p_emp_ref in varchar2,
327 p_pact_id in varchar2,
328 p_edi_rec1 out NOCOPY varchar2,
329 p_edi_rec2 out NOCOPY varchar2,
330 p_edi_rec3 out NOCOPY varchar2,
331 p_edi_rec4 out NOCOPY varchar2,
332 p_edi_rec5 out NOCOPY varchar2,
333 p_edi_rec6 out NOCOPY varchar2,
334 p_edi_rec7 out NOCOPY varchar2)
335 is
336 l_cash_equivalent number;
337 l_gross_amount number;
338 l_amount_m_good number;
339
340 edi_ftx1a varchar2(6);
341 edi_tax1 varchar2(6);
342 edi_moa1 varchar2(6);
343 edi_currency varchar2(3);
344 edi_cat varchar2(18);
345 edi_tax_qualifier1 varchar2(3);
346 edi_tax_qualifier12 varchar2(3);
347 edi_tax_qualifier41 varchar2(3);
348
349 cursor get_data is
350 select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
351 use_index(pai_person,pay_action_information_n2)
352 use_index(pai,pay_action_information_n2) */
353 sum(to_number(nvl(pai.action_information11,0))) cash_equivalent,
354 sum(to_number(nvl(pai.action_information6,0))) gross_amount,
355 sum(to_number(nvl(pai.action_information7,0))) amount_m_good
356 from per_all_assignments_f paf,
357 pay_assignment_actions paa,
358 pay_action_information pai,
359 pay_action_information pai_person
360 where paf.person_id = p_person_id
361 and paf.effective_end_date = (select max(paf2.effective_end_date)
362 from per_all_assignments_f paf2
363 where paf2.assignment_id = paf.assignment_id
364 and paf2.person_id = p_person_id)
365 and paf.assignment_id = paa.assignment_id
366 and paa.payroll_action_id = p_pact_id
367 and pai.action_context_id = paa.assignment_action_id
368 and pai.action_context_type = 'AAP'
369 and pai.action_information_category = 'VOUCHERS OR CREDIT CARDS'
370 and pai_person.action_context_id = paa.assignment_action_id
371 and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
372 and upper(pai_person.action_information13) = upper(p_emp_ref)
373 and pai_person.action_context_type = 'AAP';
374 begin
375 edi_ftx1a := rpad('FTX1A',6);
376 edi_tax1 := rpad('TAX1',6);
377 edi_moa1 := rpad('MOA1',6);
378 edi_currency := 'GBP';
379 edi_cat := rpad('C',18);
380 edi_tax_qualifier1 := rpad('1',3);
381 edi_tax_qualifier12 := rpad('12',3);
382 edi_tax_qualifier41 := rpad('41',3);
383
384 open get_data;
385 fetch get_data into l_cash_equivalent,
386 l_gross_amount,
387 l_amount_m_good;
388 close get_data;
389
390 if l_cash_equivalent >= 1 then
391 p_edi_rec1 := edi_ftx1a || edi_cat || rpad(' ', 71) || rpad(' ', 71) ||
392 rpad(' ', 70) || fnd_global.local_chr(10);
393 p_edi_rec2 := edi_tax1 || edi_tax_qualifier41 || fnd_global.local_chr(10);
394 p_edi_rec3 := edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_gross_amount) ||
395 edi_currency || fnd_global.local_chr(10);
396 p_edi_rec4 := edi_tax1 || edi_tax_qualifier1 || fnd_global.local_chr(10);
397 p_edi_rec5 := edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_amount_m_good) ||
398 edi_currency || fnd_global.local_chr(10);
399 p_edi_rec6 := edi_tax1 || edi_tax_qualifier12 || fnd_global.local_chr(10);
400 p_edi_rec7 := edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_cash_equivalent) ||
401 edi_currency || fnd_global.local_chr(10);
402 end if;
403 end get_voucher_n_creditcard;
404 /******************* Voucher or Credit Cards (Single Occurance) ***********************/
405
406 /******************* Living Accommodation (Single Occurance) ***********************/
407 procedure get_living_accommodation(p_person_id in varchar2,
408 p_emp_ref in varchar2,
409 p_pact_id in varchar2,
410 p_edi_rec1 out NOCOPY varchar2,
411 p_edi_rec2 out NOCOPY varchar2,
415
412 p_edi_rec3 out NOCOPY varchar2)
413 is
414 l_cash_equivalent number;
416 edi_ftx1a varchar2(6);
417 edi_tax1 varchar2(6);
418 edi_moa1 varchar2(6);
419 edi_currency varchar2(3);
420 edi_cat varchar2(18);
421 edi_tax_qualifier12 varchar2(3);
422
423 cursor get_data is
424 select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
425 use_index(pai_person,pay_action_information_n2)
426 use_index(pai,pay_action_information_n2) */
427 sum(to_number(nvl(pai.action_information10,0))) cash_equivalent
428 from per_all_assignments_f paf,
429 pay_assignment_actions paa,
430 pay_action_information pai,
431 pay_action_information pai_person
432 where paf.person_id = p_person_id
433 and paf.effective_end_date = (select max(paf2.effective_end_date)
434 from per_all_assignments_f paf2
435 where paf2.assignment_id = paf.assignment_id
436 and paf2.person_id = p_person_id)
437 and paf.assignment_id = paa.assignment_id
438 and paa.payroll_action_id = p_pact_id
439 and pai.action_context_id = paa.assignment_action_id
440 and pai.action_context_type = 'AAP'
441 and pai.action_information_category = 'LIVING ACCOMMODATION'
442 and pai_person.action_context_id = paa.assignment_action_id
443 and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
444 and upper(pai_person.action_information13) = upper(p_emp_ref)
445 and pai_person.action_context_type = 'AAP';
446 begin
447 edi_ftx1a := rpad('FTX1A',6);
448 edi_tax1 := rpad('TAX1',6);
449 edi_moa1 := rpad('MOA1',6);
450 edi_currency := 'GBP';
451 edi_cat := rpad('D',18);
452 edi_tax_qualifier12 := rpad('12',3);
453
454 open get_data;
455 fetch get_data into l_cash_equivalent;
456 close get_data;
457
458 if l_cash_equivalent >= 1 then
459 p_edi_rec1 := edi_ftx1a || edi_cat || rpad(' ', 71) || rpad(' ', 71) ||
460 rpad(' ', 70) || fnd_global.local_chr(10);
461 p_edi_rec2 := edi_tax1 || edi_tax_qualifier12 || fnd_global.local_chr(10);
462 p_edi_rec3 := edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_cash_equivalent) ||
463 edi_currency || fnd_global.local_chr(10);
464 end if;
465 end get_living_accommodation;
466 /******************* Living Accommodation (Single Occurance) ***********************/
467
468 /******************* Mileage Allowance (Single Occurance) ***********************/
469 procedure get_mileage_allowance(p_person_id in varchar2,
470 p_emp_ref in varchar2,
471 p_pact_id in varchar2,
472 p_edi_rec1 out NOCOPY varchar2,
473 p_edi_rec2 out NOCOPY varchar2,
474 p_edi_rec3 out NOCOPY varchar2)
475 is
476 l_taxable_payments number;
477
478 edi_ftx1a varchar2(6);
479 edi_tax1 varchar2(6);
480 edi_moa1 varchar2(6);
481 edi_currency varchar2(3);
482 edi_cat varchar2(18);
483 edi_tax_qualifier71 varchar2(3);
484
485 cursor get_data is
486 select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
487 use_index(pai_person,pay_action_information_n2)
488 use_index(pai,pay_action_information_n2) */
489 sum(to_number(nvl(pai.action_information12,0))) cash_equivalent
490 from per_all_assignments_f paf,
491 pay_assignment_actions paa,
492 pay_action_information pai,
493 pay_action_information pai_person
494 where paf.person_id = p_person_id
495 and paf.effective_end_date = (select max(paf2.effective_end_date)
496 from per_all_assignments_f paf2
497 where paf2.assignment_id = paf.assignment_id
498 and paf2.person_id = p_person_id)
499 and paf.assignment_id = paa.assignment_id
500 and paa.payroll_action_id = p_pact_id
501 and pai.action_context_id = paa.assignment_action_id
502 and pai.action_context_type = 'AAP'
503 and pai.action_information_category = 'GB P11D ASSIGNMENT RESULTA'
504 and pai_person.action_context_id = paa.assignment_action_id
505 and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
506 and upper(pai_person.action_information13) = upper(p_emp_ref)
507 and pai_person.action_context_type = 'AAP';
508 begin
509 edi_ftx1a := rpad('FTX1A',6);
510 edi_tax1 := rpad('TAX1',6);
511 edi_moa1 := rpad('MOA1',6);
512 edi_currency := 'GBP';
513 edi_cat := rpad('E',18);
514 edi_tax_qualifier71 := rpad('71',3);
515
516 open get_data;
517 fetch get_data into l_taxable_payments;
518 close get_data;
519
520 if l_taxable_payments >= 1 then
521 p_edi_rec1 := edi_ftx1a || edi_cat || rpad(' ', 71) || rpad(' ', 71) ||
522 rpad(' ', 70) || fnd_global.local_chr(10);
526 end if;
523 p_edi_rec2 := edi_tax1 || edi_tax_qualifier71 || fnd_global.local_chr(10);
524 p_edi_rec3 := edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_taxable_payments) ||
525 edi_currency || fnd_global.local_chr(10);
527 end get_mileage_allowance;
528 /******************* Mileage Allowance (Single Occurance) ***********************/
529
530 /******************* Car and Car Fuel (Multi Occurance) ***********************/
531 procedure get_car_or_fuel(p_person_id in varchar2,
532 p_emp_ref in varchar2,
533 p_pact_id in varchar2,
534 p_tax_year in varchar2,
535 p_ben_count in varchar2,
536 p_value1 in out NOCOPY varchar2,
537 p_value2 in out NOCOPY varchar2,
538 p_edi_rec1 out NOCOPY varchar2,
539 p_edi_rec2 out NOCOPY varchar2,
540 p_edi_rec3 out NOCOPY varchar2,
541 p_edi_rec4 out NOCOPY varchar2,
542 p_edi_rec5 out NOCOPY varchar2,
543 p_edi_rec6 out NOCOPY varchar2,
544 p_edi_rec7 out NOCOPY varchar2,
545 p_edi_rec8 out NOCOPY varchar2,
546 p_edi_rec9 out NOCOPY varchar2,
547 p_edi_rec10 out NOCOPY varchar2,
548 p_edi_rec11 out NOCOPY varchar2,
549 p_edi_rec12 out NOCOPY varchar2,
550 p_edi_rec13 out NOCOPY varchar2,
551 p_edi_rec14 out NOCOPY varchar2,
552 p_edi_rec15 out NOCOPY varchar2,
553 p_edi_rec16 out NOCOPY varchar2,
554 p_edi_rec17 out NOCOPY varchar2,
555 p_edi_rec18 out NOCOPY varchar2,
556 p_edi_rec19 out NOCOPY varchar2,
557 p_edi_rec20 out NOCOPY varchar2,
558 p_edi_rec21 out NOCOPY varchar2,
559 p_edi_rec22 out NOCOPY varchar2,
560 p_edi_rec23 out NOCOPY varchar2)
561 is
562 l_cash_equivalent_for_car varchar2(35);
563 l_cash_equivalent_for_fuel varchar2(35);
564 l_date_free_fuel_withdrawn varchar2(35);
565 l_free_fuel_reinstated varchar2(35);
566 l_co2_emission varchar2(35);
567 l_mileage_band varchar2(35);
568 l_fuel_type varchar2(35);
569 l_date_first_registered varchar2(35);
570 l_engine_cc_for_fuel_charge varchar2(35);
571 l_benefit_start_date varchar2(35);
572 l_benefit_end_date varchar2(35);
573 l_list_price varchar2(35);
574 l_tax_year varchar2(35);
575 l_private_use_payments varchar2(35);
576 l_make_of_car varchar2(70);
577 l_model varchar2(70);
578 l_optional_accessories_fitted varchar2(35);
579 l_capital_contribution_made varchar2(35);
580 l_withdrawn_date varchar2(35);
581 l_row_num number;
582 l_valid_benefit_end_date_flag varchar2(35);
583 l_pos number;
584
585 edi_ftx1a varchar2(6);
586 edi_tax1 varchar2(6);
587 edi_moa1 varchar2(6);
588 edi_currency varchar2(3);
589 edi_cat varchar2(18);
590 edi_att3 varchar2(6);
591 edi_att3_qualifier5 varchar2(4);
592 edi_tax_qualifier4 varchar2(4);
593 edi_tax_qualifier5 varchar2(3);
594 edi_tax_qualifier9 varchar2(3);
595 edi_tax_qualifier10 varchar2(3);
596 edi_tax_qualifier13 varchar2(4);
597 edi_tax_qualifier21 varchar2(3);
598 edi_tax_qualifier28 varchar2(4);
599 edi_tax_qualifier30 varchar2(4);
600 edi_tax_qualifier34 varchar2(4);
601 edi_tax_qualifier43 varchar2(3);
602 edi_tax_qualifier136 varchar2(3);
603 -- edi_dtm2 varchar2(6); -- commented EOY 2008
604 edi_dtm3 varchar2(6); -- EOY 2008
605 edi_dtmg varchar2(4);
606 edi_dtm375 varchar2(4);
607 edi_dtm488 varchar2(4);
608 edi_dtm489 varchar2(4);
609 edi_dtm102 varchar2(3);
610 edi_tax_year_end varchar2(10);
611 edi_tax_year_start varchar2(10);
612 edi_date_from varchar2(10);
613 edi_date_to varchar2(10);
614
615 cursor get_data(p_benefit_number number) is
616 select *
617 from (
618 select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
619 use_index(pai_person,pay_action_information_n2)
620 use_index(pai,pay_action_information_n2) */
621 rownum as row_num,
622 pai.action_information3 benefit_start_date,
623 pai.action_information4 benefit_end_date,
624 upper(pai.action_information6) make_of_car,
625 upper(pai.action_information7) model,
626 pai.action_information8 date_first_registered,
630 upper(pai.action_information12) fuel_type,
627 pai.action_information9 list_price,
628 pai.action_information10 cash_equivalent_for_car,
629 pai.action_information11 cash_equivalent_for_fuel,
631 pai.action_information13 co2_emission,
632 pai.action_information15 optional_accessories,
633 pai.action_information16 capital_contribution,
634 pai.action_information17 private_use_payments,
635 pai.action_information18 engine_cc,
636 pai.action_information25 valid_benefit_end_date_flag, -- EOY 2008
637 pai.action_information26 date_free_fuel_withdrawn,
638 pai.action_information27 free_fuel_reinstated
639 from per_all_assignments_f paf,
640 pay_assignment_actions paa,
641 pay_action_information pai,
642 pay_action_information pai_person
643 where paf.person_id = p_person_id
644 and paf.effective_end_date = (select max(paf2.effective_end_date)
645 from per_all_assignments_f paf2
646 where paf2.assignment_id = paf.assignment_id
647 and paf2.person_id = p_person_id)
648 and paf.assignment_id = paa.assignment_id
649 and paa.payroll_action_id = p_pact_id
650 and pai.action_context_id = paa.assignment_action_id
651 and pai.action_context_type = 'AAP'
652 and pai.action_information_category = 'CAR AND CAR FUEL 2003_04'
653 and pai_person.action_context_id = paa.assignment_action_id
654 and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
655 and upper(pai_person.action_information13) = upper(p_emp_ref)
656 and pai_person.action_context_type = 'AAP')
657 where row_num = p_benefit_number;
658 begin
659 edi_ftx1a := rpad('FTX1A',6);
660 edi_tax1 := rpad('TAX1',6);
661 edi_moa1 := rpad('MOA1',6);
662 edi_currency := 'GBP';
663 edi_cat := rpad('F',18);
664 edi_att3 := rpad('ATT3',6);
665 edi_att3_qualifier5 := rpad('5',4);
666 edi_tax_qualifier4 := rpad('4',4);
667 edi_tax_qualifier5 := rpad('5',3);
668 edi_tax_qualifier9 := rpad('9',3);
669 edi_tax_qualifier10 := rpad('10',3);
670 edi_tax_qualifier13 := rpad('13',4);
671 edi_tax_qualifier21 := rpad('21',3);
672 edi_tax_qualifier28 := rpad('28',4);
673 edi_tax_qualifier30 := rpad('30',4);
674 edi_tax_qualifier34 := rpad('34',4);
675 edi_tax_qualifier43 := rpad('43',3);
676 edi_tax_qualifier136:= rpad('136',3);
677 -- edi_dtm2 := rpad('DTM2',6);
678 edi_dtm3 := rpad('DTM3',6);
679 edi_dtmg := rpad('G',4);
680 edi_dtm375:= rpad('375',4);
681 edi_dtm488:= rpad('488',4);
682 edi_dtm489:= rpad('489',4);
683 edi_dtm102:= rpad('102',3);
684
685 l_tax_year := p_tax_year;
686
687 open get_data(to_number(p_ben_count));
688 fetch get_data into l_row_num,
689 l_benefit_start_date,
690 l_benefit_end_date,
691 l_make_of_car,
692 l_model,
693 l_date_first_registered,
694 l_list_price,
695 l_cash_equivalent_for_car,
696 l_cash_equivalent_for_fuel,
697 l_fuel_type,
698 l_co2_emission,
699 l_optional_accessories_fitted,
700 l_capital_contribution_made,
701 l_private_use_payments,
702 l_engine_cc_for_fuel_charge,
703 l_valid_benefit_end_date_flag, -- EOY 2008
704 l_date_free_fuel_withdrawn,
705 l_free_fuel_reinstated;
706 close get_data;
707
708 l_cash_equivalent_for_car := nvl(l_cash_equivalent_for_car,0);
709 l_cash_equivalent_for_fuel := nvl(l_cash_equivalent_for_fuel,0);
710 l_optional_accessories_fitted := nvl(l_optional_accessories_fitted,0);
711 l_list_price := nvl(l_list_price,0);
712 l_capital_contribution_made := nvl(l_capital_contribution_made,0);
713 l_private_use_payments := nvl(l_private_use_payments,0);
714
715 -- EOY 2008
716 l_pos := instr(l_valid_benefit_end_date_flag, ':');
717 l_valid_benefit_end_date_flag := substr(l_valid_benefit_end_date_flag,l_pos+1,1);
718
719 if to_number(l_cash_equivalent_for_car) >= 1 or
720 to_number(l_cash_equivalent_for_fuel) >= 1 then
721 p_value1 := to_char(to_number(p_value1) + l_cash_equivalent_for_car);
722 p_value2 := to_char(to_number(p_value2) + l_cash_equivalent_for_fuel);
723
724 -- l_mileage_band is the non-formatted version of the l_co2_emission
725 -- l_mileage_band is use for any validation.
726 l_mileage_band := l_co2_emission;
727
728 if (to_date(substr(l_date_first_registered,1,10),'YYYY/MM/DD') >= to_date('1998/01/01','YYYY/MM/DD')) and
732 l_co2_emission := ' ';
729 (l_co2_emission is not null and l_fuel_type <> 'BATTERY_ELECTRIC') then
730 l_co2_emission := pay_gb_p11d_magtape.round_and_pad(l_co2_emission,3);
731 else
733 end if;
734
735 if (to_date(substr(l_date_first_registered,1,10),'YYYY/MM/DD') <
736 to_date('1998/01/01','YYYY/MM/DD') or
737 (to_date(substr(l_date_first_registered,1,10),'YYYY/MM/DD') >=
738 to_date('1998/01/01','YYYY/MM/DD') and l_mileage_band is null)) then
739 l_engine_cc_for_fuel_charge := pay_gb_p11d_magtape.round_and_pad(l_engine_cc_for_fuel_charge,4);
740 else
741 l_engine_cc_for_fuel_charge := ' ';
742 end if;
743
744 if (to_date(substr(l_date_first_registered,1,10),'YYYY/MM/DD') >=
745 to_date('1998/01/01','YYYY/MM/DD')) then
746 select decode(l_fuel_type,
747 'BATTERY_ELECTRIC','E',
748 'DIESEL','D',
749 'EURO_IV_DIESEL','L',
750 'HYBRID_ELECTRIC','H',
751 'LPG_CNG','B',
752 'LPG_CNG_PETROL','B',
753 'LPG_CNG_PETROL_CONV','C',
754 'PETROL','P',
755 'D')
756 into l_fuel_type
757 from dual;
758 else
759 l_fuel_type := ' ';
760 end if;
761
762 edi_tax_year_end := l_tax_year || '0405';
763 edi_tax_year_start := to_char(to_number(l_tax_year) - 1) || '0406';
764 edi_date_from := substr(l_benefit_start_date,1,4) ||
765 substr(l_benefit_start_date,6,2) ||
766 substr(l_benefit_start_date,9,2);
767 edi_date_to := substr(l_benefit_end_date,1,4) ||
768 substr(l_benefit_end_date,6,2) ||
769 substr(l_benefit_end_date,9,2);
770
771 p_edi_rec1 := edi_ftx1a || edi_cat || rpad(' ', 71) || rpad(' ', 71) || rpad(' ', 70) || fnd_global.local_chr(10);
772 p_edi_rec2 := edi_tax1 || edi_tax_qualifier43 || fnd_global.local_chr(10);
773 p_edi_rec3 := edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_list_price) || edi_currency || fnd_global.local_chr(10);
774
775 if to_number(l_optional_accessories_fitted) > 0 then
776 p_edi_rec4 := edi_tax1 || edi_tax_qualifier136 || fnd_global.local_chr(10);
777 p_edi_rec5 := edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_optional_accessories_fitted) ||
778 edi_currency || fnd_global.local_chr(10);
779 end if;
780
781 if to_number(l_capital_contribution_made) > 0 then
782 p_edi_rec6 := edi_tax1 || edi_tax_qualifier21 || fnd_global.local_chr(10);
783 p_edi_rec7 := edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_capital_contribution_made) ||
784 edi_currency || fnd_global.local_chr(10);
785 end if;
786
787 if to_number(l_private_use_payments) > 0 then
788 p_edi_rec8 := edi_tax1 || edi_tax_qualifier5 || fnd_global.local_chr(10);
789 p_edi_rec9 := edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_private_use_payments) || edi_currency || fnd_global.local_chr(10);
790 end if;
791
792 p_edi_rec10:= edi_tax1 || edi_tax_qualifier9 || fnd_global.local_chr(10);
793 p_edi_rec11:= edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_cash_equivalent_for_car) ||
794 edi_currency || fnd_global.local_chr(10);
795
796 --if (l_co2_emission is not null or l_engine_cc_for_fuel_charge <> ' ') then
797 --if to_number(l_cash_equivalent_for_fuel) > 0 then
798 if ((l_co2_emission <> ' ' or l_engine_cc_for_fuel_charge <> ' ') and
799 to_number(l_cash_equivalent_for_fuel) > 0
800 ) then
801 p_edi_rec12 := edi_tax1 || edi_tax_qualifier10 || fnd_global.local_chr(10);
802 p_edi_rec13 := edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_cash_equivalent_for_fuel) ||
803 edi_currency || fnd_global.local_chr(10);
804 end if;
805
806 p_edi_rec14:= edi_att3 || edi_att3_qualifier5 || rpad(l_make_of_car || ' ' || l_model,35) || fnd_global.local_chr(10);
807
808 if (to_date(substr(l_date_first_registered,1,10),'YYYY/MM/DD') >=
809 to_date('1998/01/01','YYYY/MM/DD') and l_mileage_band is not null and
810 to_number(nvl(l_mileage_band,0)) > 0 ) and
811 (l_fuel_type <> 'E') then
812 p_edi_rec15 := edi_att3 || edi_tax_qualifier28 || rpad(l_co2_emission,35) || fnd_global.local_chr(10);
813 end if;
814
815 if (to_date(substr(l_date_first_registered,1,10),'YYYY/MM/DD') >=
816 to_date('1998/01/01','YYYY/MM/DD') and l_mileage_band is null) or
817 (l_fuel_type = 'E') then
818 p_edi_rec16 := edi_att3 || edi_tax_qualifier30 || rpad(' ',35) || fnd_global.local_chr(10);
819 end if;
820
821 if l_engine_cc_for_fuel_charge <> ' ' then
822 if l_fuel_type = 'E' then
823 l_engine_cc_for_fuel_charge := '0000';
824 end if;
828 if (to_date(substr(l_date_first_registered,1,10),'YYYY/MM/DD') >=
825 p_edi_rec17 := edi_att3 || edi_tax_qualifier13 || rpad(l_engine_cc_for_fuel_charge,35) || fnd_global.local_chr(10);
826 end if;
827
829 to_date('1998/01/01','YYYY/MM/DD')) then
830 p_edi_rec18 := edi_att3 || edi_tax_qualifier4 || rpad(l_fuel_type,35) || fnd_global.local_chr(10);
831 end if;
832
833 if l_free_fuel_reinstated <> 'N' then
834 p_edi_rec19 := edi_att3 || edi_tax_qualifier34 || rpad(' ',35) || fnd_global.local_chr(10);
835 end if;
836
837 p_edi_rec20 := edi_dtm3 || edi_dtm375 ||
838 rpad(to_char(to_date(substr(l_date_first_registered,1,10),'YYYY/MM/DD'),'YYYYMMDD'),36) ||
839 edi_dtm102 || fnd_global.local_chr(10); -- EOY 2008
840
841 if to_number(edi_date_from) >= to_number(edi_tax_year_start) then -- EOY 2008
842 p_edi_rec21 := edi_dtm3 || edi_dtm488 || rpad(edi_date_from,36) || edi_dtm102 || fnd_global.local_chr(10); -- EOY 2008
843 end if;
844
845 if to_number(edi_date_to) <= to_number(edi_tax_year_end) then -- EOY 2008
846 -- EOY 2008
847 hr_utility.trace('edi_date_to:'||edi_date_to);
848 hr_utility.trace('l_valid_benefit_end_date_flag:'||l_valid_benefit_end_date_flag);
849 IF substr(edi_date_to,5,4) = '0405' THEN
850 IF l_valid_benefit_end_date_flag = 'Y' THEN
851 p_edi_rec22 := edi_dtm3 || edi_dtm489 || rpad(edi_date_to,36) || edi_dtm102 || fnd_global.local_chr(10); -- EOY 2008
852 hr_utility.trace('printing edi_date_to');
853 ELSE
854 hr_utility.trace('NOT printing edi_date_to');
855 END IF ;
856 ELSE
857 p_edi_rec22 := edi_dtm3 || edi_dtm489 || rpad(edi_date_to,36) || edi_dtm102 || fnd_global.local_chr(10); -- EOY 2008
858 hr_utility.trace('Unconditional printing edi_date_to');
859 END IF ;
860 end if;
861
862 if l_free_fuel_reinstated <> 'N' and to_number(l_cash_equivalent_for_fuel) > 0
863 and l_date_free_fuel_withdrawn is not null then
864 l_withdrawn_date := substr(l_date_free_fuel_withdrawn,1,4) ||
865 substr(l_date_free_fuel_withdrawn,6,2) ||
866 substr(l_date_free_fuel_withdrawn,9,2);
867 p_edi_rec23 := edi_dtm3 || edi_dtmg || rpad(l_withdrawn_date, 36) || edi_dtm102 || fnd_global.local_chr(10); -- EOY 2008
868 end if;
869 end if;
870 end get_car_or_fuel;
871 /******************* Car and Car Fuel (Multi Occurance) ***********************/
872
873 /******************* Car and Car Fuel (Summary) ***********************/
874 procedure get_car_summary(p_value1 in varchar2,
875 p_value2 in varchar2,
876 p_edi_rec1 out NOCOPY varchar2,
877 p_edi_rec2 out NOCOPY varchar2,
878 p_edi_rec3 out NOCOPY varchar2,
879 p_edi_rec4 out NOCOPY varchar2,
880 p_edi_rec5 out NOCOPY varchar2)
881 is
882 edi_ftx1a varchar2(18);
883 edi_tax1 varchar2(6);
884 edi_moa1 varchar2(6);
885 edi_currency varchar2(3);
886 edi_cat varchar2(18);
887 edi_tax_qualifier74 varchar2(3);
888 edi_tax_qualifier75 varchar2(3);
889 begin
890 edi_cat := rpad('X',18);
891 edi_ftx1a := rpad('FTX1A',6);
892 edi_moa1 := rpad('MOA1',6);
893 edi_tax1 := rpad('TAX1',6);
894 edi_currency := 'GBP';
895 edi_tax_qualifier74 := rpad('74',3);
896 edi_tax_qualifier75 := rpad('75',3);
897
898 if (to_number(p_value1) >= 1 or
899 to_number(p_value2) >= 1) then
900
901 p_edi_rec1 := edi_ftx1a || edi_cat || rpad(' ',71) || rpad(' ',71) || rpad(' ',70) || fnd_global.local_chr(10);
902 p_edi_rec2 := edi_tax1 || edi_tax_qualifier74 || fnd_global.local_chr(10);
903 p_edi_rec3 := edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(p_value1) || edi_currency || fnd_global.local_chr(10);
904 if to_number(p_value2) >= 0 then
905 p_edi_rec4 := edi_tax1 || edi_tax_qualifier75 || fnd_global.local_chr(10);
906 p_edi_rec5 := edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(p_value2) || edi_currency || fnd_global.local_chr(10);
907 end if;
908 end if;
909 end get_car_summary;
910 /******************* Car and Car Fuel (Summary) ***********************/
911
912 /******************* Vans (Single Occurance) ***********************/
913 procedure get_vans(p_person_id in varchar2,
914 p_emp_ref in varchar2,
915 p_pact_id in varchar2,
916 p_edi_rec1 out NOCOPY varchar2,
917 p_edi_rec2 out NOCOPY varchar2,
918 p_edi_rec3 out NOCOPY varchar2,
919 p_edi_rec4 out NOCOPY varchar2,
920 p_edi_rec5 out NOCOPY varchar2)
921 is
922 l_cash_equivalent number;
923 l_vans_fuel number ; -- EOY 2008
924
925 edi_ftx1a varchar2(6);
926 edi_tax1 varchar2(6);
930 edi_tax_qualifier12 varchar2(3);
927 edi_moa1 varchar2(6);
928 edi_currency varchar2(3);
929 edi_cat varchar2(18);
931 edi_tax_qualifier139 varchar2(3);
932
933 cursor get_data is
934 select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
935 use_index(pai_person,pay_action_information_n2)
936 use_index(pai,pay_action_information_n2) */
937 sum(to_number(nvl(pai.action_information14,0))) cash_equivalent -- EOY 2008
938 from per_all_assignments_f paf,
939 pay_assignment_actions paa,
940 pay_action_information pai,
941 pay_action_information pai_person
942 where paf.person_id = p_person_id
943 and paf.effective_end_date = (select max(paf2.effective_end_date)
944 from per_all_assignments_f paf2
945 where paf2.assignment_id = paf.assignment_id
946 and paf2.person_id = p_person_id)
947 and paf.assignment_id = paa.assignment_id
948 and paa.payroll_action_id = p_pact_id
949 and pai.action_context_id = paa.assignment_action_id
950 and pai.action_context_type = 'AAP'
951 and pai.action_information_category = 'VANS 2007' -- EOY 2008
952 and pai_person.action_context_id = paa.assignment_action_id
953 and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
954 and upper(pai_person.action_information13) = upper(p_emp_ref)
955 and pai_person.action_context_type = 'AAP';
956
957 -- EOY 2008
958 cursor get_data2 is
959 select /*+ ORDERED use_nl(paf,paa,pai)
960 use_index(pai,pay_action_information_n2) */
961 sum (pai.action_information30) vans_fuel
962 from per_all_assignments_f paf,
963 pay_assignment_actions paa,
964 pay_action_information pai
965 where paf.person_id = p_person_id
966 and paf.effective_end_date = (select max(paf2.effective_end_date)
967 from per_all_assignments_f paf2
968 where paf2.assignment_id = paf.assignment_id
969 and paf2.person_id = p_person_id)
970 and paf.assignment_id = paa.assignment_id
971 and paa.payroll_action_id = p_pact_id
972 and pai.action_context_id = paa.assignment_action_id
973 and pai.action_context_type = 'AAP'
974 and pai.action_information_category = 'GB P11D ASSIGNMENT RESULTB' ;
975
976
977 begin
978 edi_ftx1a := rpad('FTX1A',6);
979 edi_tax1 := rpad('TAX1',6);
980 edi_moa1 := rpad('MOA1',6);
981 edi_currency := 'GBP';
982 edi_cat := rpad('G',18);
983 edi_tax_qualifier12 := rpad('12',3);
984 edi_tax_qualifier139 := rpad('139',3); -- EOY 2008
985
986 open get_data;
987 fetch get_data into l_cash_equivalent;
988 close get_data;
989
990 -- EOY 2008
991 open get_data2;
992 fetch get_data2 into l_vans_fuel;
993 close get_data2;
994 --commented the below logic for bug 7231650
995 -- if l_cash_equivalent >= 1 and l_vans_fuel >=1 then
996 --bug 7231650 added the below logic
997 if l_cash_equivalent >= 1 or l_vans_fuel >=1 then
998 p_edi_rec1 := edi_ftx1a || edi_cat || rpad(' ', 71) || rpad(' ', 71) ||
999 rpad(' ', 70) || fnd_global.local_chr(10);
1000 p_edi_rec2 := edi_tax1 || edi_tax_qualifier12 || fnd_global.local_chr(10);
1001 p_edi_rec3 := edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_cash_equivalent) ||
1002 edi_currency || fnd_global.local_chr(10);
1003
1004 -- EOY 2008
1005 --commenting the below added if as van fuel have to be printed even it is null or zero.
1006 --bug 7231650 enclsoed the two edi records in IF condition
1007 --if l_vans_fuel >=1 then
1008 p_edi_rec4 := edi_tax1 || edi_tax_qualifier139 || fnd_global.local_chr(10);
1009 p_edi_rec5 := edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(nvl(l_vans_fuel,0)) ||
1010 edi_currency || fnd_global.local_chr(10);
1011 --end if;
1012 end if;
1013 end get_vans;
1014 /******************* Vans (Single Occurance) ***********************/
1015
1016 /******************* Interest Free and Low Interest Loan (Multi Occurance) ***********************/
1017 procedure get_low_int_loan(p_person_id in varchar2,
1018 p_emp_ref in varchar2,
1019 p_pact_id in varchar2,
1020 p_ben_count in varchar2,
1021 p_tax_year in varchar2,
1022 p_value1 in out NOCOPY varchar2,
1023 p_edi_rec1 out NOCOPY varchar2,
1024 p_edi_rec2 out NOCOPY varchar2,
1025 p_edi_rec3 out NOCOPY varchar2,
1026 p_edi_rec4 out NOCOPY varchar2,
1027 p_edi_rec5 out NOCOPY varchar2,
1028 p_edi_rec6 out NOCOPY varchar2,
1029 p_edi_rec7 out NOCOPY varchar2,
1030 p_edi_rec8 out NOCOPY varchar2,
1031 p_edi_rec9 out NOCOPY varchar2)
1032 is
1033 l_total_loan number;
1037 l_date_loan_made varchar2(50);
1034 l_cash_equivalent number;
1035 l_date_from varchar2(10);
1036 l_date_to varchar2(10);
1038 l_date_loan_discharged varchar2(50);
1039 l_no_of_borrowers number;
1040 l_max_outstanding number;
1041 l_total_int_paid number;
1042 l_amount_ostd_at_start number;
1043 l_amount_ostd_at_end number;
1044 l_temp number;
1045
1046 edi_ftx1a varchar2(6);
1047 edi_tax1 varchar2(6);
1048 edi_moa1 varchar2(6);
1049 edi_currency varchar2(3);
1050 edi_cat varchar2(18);
1051 edi_tax_qualifier2 varchar2(3);
1052 edi_tax_qualifier3 varchar2(3);
1053 edi_tax_qualifier12 varchar2(3);
1054 edi_tax_qualifier45 varchar2(3);
1055 edi_tax_qualifier72 varchar2(3);
1056 edi_qty varchar2(6);
1057 edi_qtyg varchar2(4);
1058 -- edi_dtm2 varchar2(6); -- commented EOY 2008
1059 edi_dtm3 varchar2(6); -- EOY 2008
1060 edi_dtm167 varchar2(4);
1061 edi_dtm168 varchar2(4);
1062 edi_dtm102 varchar2(3);
1063 edi_tax_year_start varchar2(10);
1064 edi_tax_year_end varchar2(10);
1065
1066 cursor get_loan_amount is
1067 select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1068 use_index(pai_person,pay_action_information_n2)
1069 use_index(pai,pay_action_information_n2) */
1070 sum(to_number(nvl(pai.action_information7,0)))
1071 from per_all_assignments_f paf,
1072 pay_assignment_actions paa,
1073 pay_action_information pai,
1074 pay_action_information pai_person
1075 where paf.person_id = p_person_id
1076 and paf.effective_end_date = (select max(paf2.effective_end_date)
1077 from per_all_assignments_f paf2
1078 where paf2.assignment_id = paf.assignment_id
1079 and paf2.person_id = p_person_id)
1080 and paf.assignment_id = paa.assignment_id
1081 and paa.payroll_action_id = p_pact_id
1082 and pai.action_context_id = paa.assignment_action_id
1083 and pai.action_context_type = 'AAP'
1084 and pai.action_information_category = 'INT FREE AND LOW INT LOANS'
1085 and pai_person.action_context_id = paa.assignment_action_id
1086 and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
1087 and upper(pai_person.action_information13) = upper(p_emp_ref)
1088 and pai_person.action_context_type = 'AAP';
1089
1090 cursor get_data(p_benefit_number number) is
1091 select *
1092 from (
1093 select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1094 use_index(pai_person,pay_action_information_n2)
1095 use_index(pai,pay_action_information_n2) */
1096 rownum as row_num,
1097 to_number(nvl(pai.action_information5,1)) number_of_borrower,
1098 to_number(nvl(pai.action_information6,0)) amount_oustanding_at_5th_april,
1099 to_number(nvl(pai.action_information7,0)) maximum_amount_outstanding,
1100 to_number(nvl(pai.action_information8,0)) total_interest_paid,
1101 pai.action_information9 date_loan_made,
1102 pai.action_information10 date_loan_discharged,
1103 to_number(nvl(pai.action_information11,1))cash_equivalent,
1104 to_number(nvl(pai.action_information16,1))amount_outstanding_at_year_end
1105 from per_all_assignments_f paf,
1106 pay_assignment_actions paa,
1107 pay_action_information pai,
1108 pay_action_information pai_person
1109 where paf.person_id = p_person_id
1110 and paf.effective_end_date = (select max(paf2.effective_end_date)
1111 from per_all_assignments_f paf2
1112 where paf2.assignment_id = paf.assignment_id
1113 and paf2.person_id = p_person_id)
1114 and paf.assignment_id = paa.assignment_id
1115 and paa.payroll_action_id = p_pact_id
1116 and pai.action_context_id = paa.assignment_action_id
1117 and pai.action_context_type = 'AAP'
1118 and pai.action_information_category = 'INT FREE AND LOW INT LOANS'
1119 and pai_person.action_context_id = paa.assignment_action_id
1120 and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
1121 and upper(pai_person.action_information13) = upper(p_emp_ref)
1122 and pai_person.action_context_type = 'AAP')
1123 where row_num = p_benefit_number;
1124 begin
1125
1126 if to_number(p_value1) < 1 then
1127 open get_loan_amount;
1128 fetch get_loan_amount into l_total_loan;
1129 close get_loan_amount;
1130
1131 p_value1 := to_char(l_total_loan);
1132 end if;
1133
1134 if to_number(p_value1) > 5000 then
1135
1136 edi_cat := rpad('H',18);
1137 edi_ftx1a := rpad('FTX1A',6);
1138 edi_moa1 := rpad('MOA1',6);
1139 edi_tax1 := rpad('TAX1',6);
1140 edi_currency := 'GBP';
1141 edi_tax_qualifier2 := rpad('2',3);
1142 edi_tax_qualifier3 := rpad('3',3);
1143 edi_tax_qualifier12 := rpad('12',3);
1144 edi_tax_qualifier45 := rpad('45',3);
1148 -- edi_dtm2 := rpad('DTM2',6); commented EOY 2008
1145 edi_tax_qualifier72 := rpad('72',3);
1146 edi_qty := rpad('QTY0',6);
1147 edi_qtyg := rpad('G',4);
1149 edi_dtm3 := rpad('DTM3',6);
1150 edi_dtm167 := rpad('167',4);
1151 edi_dtm168 := rpad('168',4);
1152 edi_dtm102 := rpad('102',3);
1153
1154 open get_data(to_number(p_ben_count));
1155 fetch get_data into l_temp,
1156 l_no_of_borrowers,
1157 l_amount_ostd_at_start,
1158 l_max_outstanding,
1159 l_total_int_paid ,
1160 l_date_loan_made,
1161 l_date_loan_discharged,
1162 l_cash_equivalent,
1163 l_amount_ostd_at_end;
1164 close get_data;
1165
1166 if to_number(l_cash_equivalent) > 0 then
1167 --bug 7042975 changed p_tax_year to to_char(to_number(p_tax_year)-1)
1168 edi_tax_year_start := to_char(to_number(p_tax_year)-1) || '0406';
1169 edi_tax_year_end := p_tax_year || '0405';
1170
1171 if l_date_loan_made is not null then
1172 l_date_from := substr(l_date_loan_made,1,4) ||
1173 substr(l_date_loan_made,6,2) ||
1174 substr(l_date_loan_made,9,2);
1175 if to_number(l_date_from) < to_number(edi_tax_year_start) then
1176 l_date_from := ' ';
1177 end if;
1178 end if;
1179
1180 if l_date_loan_discharged is not null then
1181 l_date_to := substr(l_date_loan_discharged,1,4) ||
1182 substr(l_date_loan_discharged,6,2) ||
1183 substr(l_date_loan_discharged,9,2);
1184 if to_number(l_date_to) > to_number(edi_tax_year_end) then
1185 l_date_to := ' ';
1186 end if;
1187 end if;
1188
1189 p_edi_rec1 := edi_ftx1a || edi_cat || rpad(' ', 71) || rpad(' ', 71) || rpad(' ',70) || fnd_global.local_chr(10);
1190 p_edi_rec2 := edi_tax1 || edi_tax_qualifier2 || fnd_global.local_chr(10) || edi_moa1 ||
1191 pay_gb_p11d_magtape.format_edi_currency(l_amount_ostd_at_start) || edi_currency || fnd_global.local_chr(10);
1192 p_edi_rec3 := edi_tax1 || edi_tax_qualifier3 || fnd_global.local_chr(10) || edi_moa1 ||
1193 pay_gb_p11d_magtape.format_edi_currency(l_amount_ostd_at_end) || edi_currency || fnd_global.local_chr(10);
1194 p_edi_rec4 := edi_tax1 || edi_tax_qualifier45 || fnd_global.local_chr(10) || edi_moa1 ||
1195 pay_gb_p11d_magtape.format_edi_currency(l_max_outstanding) || edi_currency || fnd_global.local_chr(10);
1196 p_edi_rec5 := edi_tax1 || edi_tax_qualifier72 || fnd_global.local_chr(10) || edi_moa1 ||
1197 pay_gb_p11d_magtape.format_edi_currency(l_total_int_paid) || edi_currency || fnd_global.local_chr(10);
1198 p_edi_rec6 := edi_tax1 || edi_tax_qualifier12 || fnd_global.local_chr(10) || edi_moa1 ||
1199 pay_gb_p11d_magtape.format_edi_currency(l_cash_equivalent) || edi_currency || fnd_global.local_chr(10);
1200
1201 if l_no_of_borrowers > 1 then
1202 p_edi_rec7 := edi_qty || edi_qtyg || lpad(l_no_of_borrowers,15,0) || fnd_global.local_chr(10);
1203 end if;
1204
1205 if l_date_from <> ' ' then
1206 p_edi_rec8 := edi_dtm3 || edi_dtm167 ||
1207 rpad(to_char(to_date(substr(l_date_loan_made,1,10),'YYYY/MM/DD'),'YYYYMMDD'),36) ||
1208 edi_dtm102 || fnd_global.local_chr(10); -- EOY 2008
1209 end if;
1210
1211 if l_date_to <> ' ' then
1212 p_edi_rec9 := edi_dtm3 || edi_dtm168 ||
1213 rpad(to_char(to_date(substr(l_date_loan_discharged,1,10),'YYYY/MM/DD'),'YYYYMMDD'),36) ||
1214 edi_dtm102 || fnd_global.local_chr(10); -- EOY 2008
1215 end if;
1216 end if;
1217 end if;
1218 end get_low_int_loan;
1219 /******************* Interest Free and Low Interest Loan (Multi Occurance) ***********************/
1220
1221 /******************* Private Medical Treatment or Insurance (Single Occurance) ***********************/
1222 procedure get_pvt_med_or_ins(p_person_id in varchar2,
1223 p_emp_ref in varchar2,
1224 p_pact_id in varchar2,
1225 p_edi_rec1 out NOCOPY varchar2,
1226 p_edi_rec2 out NOCOPY varchar2,
1227 p_edi_rec3 out NOCOPY varchar2,
1228 p_edi_rec4 out NOCOPY varchar2,
1229 p_edi_rec5 out NOCOPY varchar2,
1230 p_edi_rec6 out NOCOPY varchar2,
1231 p_edi_rec7 out NOCOPY varchar2)
1232 is
1233 l_cash_equivalent number;
1234 l_cost_to_you number;
1235 l_amount_m_good number;
1236
1237 edi_ftx1a varchar2(6);
1238 edi_tax1 varchar2(6);
1239 edi_moa1 varchar2(6);
1240 edi_currency varchar2(3);
1241 edi_cat varchar2(18);
1242 edi_tax_qualifier1 varchar2(3);
1243 edi_tax_qualifier12 varchar2(3);
1247 select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1244 edi_tax_qualifier13 varchar2(3);
1245
1246 cursor get_data is
1248 use_index(pai_person,pay_action_information_n2)
1249 use_index(pai,pay_action_information_n2) */
1250 sum(to_number(nvl(pai.action_information7,0))) cash_equivalent,
1251 sum(to_number(nvl(pai.action_information5,0))) cost_to_you,
1252 sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1253 from per_all_assignments_f paf,
1254 pay_assignment_actions paa,
1255 pay_action_information pai,
1256 pay_action_information pai_person
1257 where paf.person_id = p_person_id
1258 and paf.effective_end_date = (select max(paf2.effective_end_date)
1259 from per_all_assignments_f paf2
1260 where paf2.assignment_id = paf.assignment_id
1261 and paf2.person_id = p_person_id)
1262 and paf.assignment_id = paa.assignment_id
1263 and paa.payroll_action_id = p_pact_id
1264 and pai.action_context_id = paa.assignment_action_id
1265 and pai.action_context_type = 'AAP'
1266 and pai.action_information_category = 'PVT MED TREATMENT OR INSURANCE'
1267 and pai_person.action_context_id = paa.assignment_action_id
1268 and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
1269 and upper(pai_person.action_information13) = upper(p_emp_ref)
1270 and pai_person.action_context_type = 'AAP';
1271 begin
1272 edi_ftx1a := rpad('FTX1A',6);
1273 edi_tax1 := rpad('TAX1',6);
1274 edi_moa1 := rpad('MOA1',6);
1275 edi_currency := 'GBP';
1276 edi_cat := rpad('I',18);
1277 edi_tax_qualifier1 := rpad('1',3);
1278 edi_tax_qualifier12 := rpad('12',3);
1279 edi_tax_qualifier13 := rpad('13',3);
1280
1281 open get_data;
1282 fetch get_data into l_cash_equivalent,
1283 l_cost_to_you,
1284 l_amount_m_good;
1285 close get_data;
1286
1287 if l_cash_equivalent >= 1 then
1288 l_cost_to_you := l_cash_equivalent + l_amount_m_good;
1289 end if;
1290
1291 if l_cash_equivalent >= 1 then
1292 p_edi_rec1 := edi_ftx1a || edi_cat || rpad(' ', 71) || rpad(' ', 71) ||
1293 rpad(' ', 70) || fnd_global.local_chr(10);
1294 p_edi_rec2 := edi_tax1 || edi_tax_qualifier13 || fnd_global.local_chr(10);
1295 p_edi_rec3 := edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_cost_to_you) ||
1296 edi_currency || fnd_global.local_chr(10);
1297 p_edi_rec4 := edi_tax1 || edi_tax_qualifier1 || fnd_global.local_chr(10);
1298 p_edi_rec5 := edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_amount_m_good) ||
1299 edi_currency || fnd_global.local_chr(10);
1300 p_edi_rec6 := edi_tax1 || edi_tax_qualifier12 || fnd_global.local_chr(10);
1301 p_edi_rec7 := edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_cash_equivalent) ||
1302 edi_currency || fnd_global.local_chr(10);
1303 end if;
1304 end get_pvt_med_or_ins;
1305 /******************* Private Medical Treatment or Insurance (Single Occurance) ***********************/
1306
1307 /******************* Qualifying Relocation Expenses Payments and Benefits (Single Occurance) ***********************/
1308 procedure get_relocation(p_person_id in varchar2,
1309 p_emp_ref in varchar2,
1310 p_pact_id in varchar2,
1311 p_edi_rec1 out NOCOPY varchar2,
1312 p_edi_rec2 out NOCOPY varchar2,
1313 p_edi_rec3 out NOCOPY varchar2)
1314 is
1315 l_cash_equivalent number;
1316
1317 edi_ftx1a varchar2(6);
1318 edi_tax1 varchar2(6);
1319 edi_moa1 varchar2(6);
1320 edi_currency varchar2(3);
1321 edi_cat varchar2(18);
1322 edi_tax_qualifier64 varchar2(3);
1323
1324 cursor get_data is
1325 select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1326 use_index(pai_person,pay_action_information_n2)
1327 use_index(pai,pay_action_information_n2) */
1328 sum(to_number(nvl(pai.action_information5,0))) cash_equivalent
1329 from per_all_assignments_f paf,
1330 pay_assignment_actions paa,
1331 pay_action_information pai,
1332 pay_action_information pai_person
1333 where paf.person_id = p_person_id
1334 and paf.effective_end_date = (select max(paf2.effective_end_date)
1335 from per_all_assignments_f paf2
1336 where paf2.assignment_id = paf.assignment_id
1337 and paf2.person_id = p_person_id)
1338 and paf.assignment_id = paa.assignment_id
1339 and paa.payroll_action_id = p_pact_id
1340 and pai.action_context_id = paa.assignment_action_id
1341 and pai.action_context_type = 'AAP'
1342 and pai.action_information_category = 'RELOCATION EXPENSES'
1343 and pai_person.action_context_id = paa.assignment_action_id
1344 and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
1345 and upper(pai_person.action_information13) = upper(p_emp_ref)
1349 edi_tax1 := rpad('TAX1',6);
1346 and pai_person.action_context_type = 'AAP';
1347 begin
1348 edi_ftx1a := rpad('FTX1A',6);
1350 edi_moa1 := rpad('MOA1',6);
1351 edi_currency := 'GBP';
1352 edi_cat := rpad('J',18);
1353 edi_tax_qualifier64 := rpad('64',3);
1354
1355 open get_data;
1356 fetch get_data into l_cash_equivalent;
1357 close get_data;
1358
1359 if l_cash_equivalent >= 1 then
1360 p_edi_rec1 := edi_ftx1a || edi_cat || rpad(' ', 71) || rpad(' ', 71) ||
1361 rpad(' ', 70) || fnd_global.local_chr(10);
1362 p_edi_rec2 := edi_tax1 || edi_tax_qualifier64 || fnd_global.local_chr(10);
1363 p_edi_rec3 := edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_cash_equivalent) ||
1364 edi_currency || fnd_global.local_chr(10);
1365 end if;
1366 end get_relocation;
1367 /******************* Qualifying Relocation Expenses Payments and Benefits (Single Occurance) ***********************/
1368
1369 /******************* Services Supplied (Single Occurance) ***********************/
1370 procedure get_service_supplied(p_person_id in varchar2,
1371 p_emp_ref in varchar2,
1372 p_pact_id in varchar2,
1373 p_edi_rec1 out NOCOPY varchar2,
1374 p_edi_rec2 out NOCOPY varchar2,
1375 p_edi_rec3 out NOCOPY varchar2,
1376 p_edi_rec4 out NOCOPY varchar2,
1377 p_edi_rec5 out NOCOPY varchar2,
1378 p_edi_rec6 out NOCOPY varchar2,
1379 p_edi_rec7 out NOCOPY varchar2)
1380 is
1381 l_cash_equivalent number;
1382 l_cost_to_you number;
1383 l_amount_m_good number;
1384
1385 edi_ftx1a varchar2(6);
1386 edi_tax1 varchar2(6);
1387 edi_moa1 varchar2(6);
1388 edi_currency varchar2(3);
1389 edi_cat varchar2(18);
1390 edi_tax_qualifier1 varchar2(3);
1391 edi_tax_qualifier12 varchar2(3);
1392 edi_tax_qualifier13 varchar2(3);
1393
1394 cursor get_data is
1395 select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1396 use_index(pai_person,pay_action_information_n2)
1397 use_index(pai,pay_action_information_n2) */
1398 sum(to_number(nvl(pai.action_information7,0))) cash_equivalent,
1399 sum(to_number(nvl(pai.action_information5,0))) cost_to_you,
1400 sum(to_number(nvl(pai.action_information6,0))) amount_m_good
1401 from per_all_assignments_f paf,
1402 pay_assignment_actions paa,
1403 pay_action_information pai,
1404 pay_action_information pai_person
1405 where paf.person_id = p_person_id
1406 and paf.effective_end_date = (select max(paf2.effective_end_date)
1407 from per_all_assignments_f paf2
1408 where paf2.assignment_id = paf.assignment_id
1409 and paf2.person_id = p_person_id)
1410 and paf.assignment_id = paa.assignment_id
1411 and paa.payroll_action_id = p_pact_id
1412 and pai.action_context_id = paa.assignment_action_id
1413 and pai.action_context_type = 'AAP'
1414 and pai.action_information_category = 'SERVICES SUPPLIED'
1415 and pai_person.action_context_id = paa.assignment_action_id
1416 and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
1417 and upper(pai_person.action_information13) = upper(p_emp_ref)
1418 and pai_person.action_context_type = 'AAP';
1419 begin
1420 edi_ftx1a := rpad('FTX1A',6);
1421 edi_tax1 := rpad('TAX1',6);
1422 edi_moa1 := rpad('MOA1',6);
1423 edi_currency := 'GBP';
1424 edi_cat := rpad('K',18);
1425 edi_tax_qualifier1 := rpad('1',3);
1426 edi_tax_qualifier12 := rpad('12',3);
1427 edi_tax_qualifier13 := rpad('13',3);
1428
1429 open get_data;
1430 fetch get_data into l_cash_equivalent,
1431 l_cost_to_you,
1432 l_amount_m_good;
1433 close get_data;
1434
1435 if l_cash_equivalent >= 1 then
1436 l_cost_to_you := l_cash_equivalent + l_amount_m_good;
1437 end if;
1438
1439 if l_cash_equivalent >= 1 then
1440 p_edi_rec1 := edi_ftx1a || edi_cat || rpad(' ', 71) || rpad(' ', 71) ||
1441 rpad(' ', 70) || fnd_global.local_chr(10);
1442 p_edi_rec2 := edi_tax1 || edi_tax_qualifier13 || fnd_global.local_chr(10);
1443 p_edi_rec3 := edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_cost_to_you) ||
1444 edi_currency || fnd_global.local_chr(10);
1445 p_edi_rec4 := edi_tax1 || edi_tax_qualifier1 || fnd_global.local_chr(10);
1446 p_edi_rec5 := edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_amount_m_good) ||
1447 edi_currency || fnd_global.local_chr(10);
1448 p_edi_rec6 := edi_tax1 || edi_tax_qualifier12 || fnd_global.local_chr(10);
1449 p_edi_rec7 := edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_cash_equivalent) ||
1450 edi_currency || fnd_global.local_chr(10);
1454
1451 end if;
1452 end get_service_supplied;
1453 /******************* Services Supplied (Single Occurance) ***********************/
1455 /******************* Assets Placed at The Employee's Disposal (Multi Occurance) ***********************/
1456 procedure get_assets_at_emp(p_person_id in varchar2,
1457 p_emp_ref in varchar2,
1458 p_pact_id in varchar2,
1459 p_edi_rec1 out NOCOPY varchar2,
1460 p_edi_rec2 out NOCOPY varchar2,
1461 p_edi_rec3 out NOCOPY varchar2,
1462 p_edi_rec4 out NOCOPY varchar2,
1463 p_edi_rec5 out NOCOPY varchar2,
1464 p_edi_rec6 out NOCOPY varchar2,
1465 p_edi_rec7 out NOCOPY varchar2,
1466 p_edi_rec8 out NOCOPY varchar2,
1467 p_edi_rec9 out NOCOPY varchar2,
1468 p_edi_rec10 out NOCOPY varchar2,
1469 p_edi_rec11 out NOCOPY varchar2,
1470 p_edi_rec12 out NOCOPY varchar2,
1471 p_edi_rec13 out NOCOPY varchar2,
1472 p_edi_rec14 out NOCOPY varchar2,
1473 p_edi_rec15 out NOCOPY varchar2,
1474 p_edi_rec16 out NOCOPY varchar2)
1475 is
1476 type r_assets is record(
1477 description varchar2(70),
1478 annual_value varchar2(70),
1479 cash_equivalent varchar2(70),
1480 amount_made_good varchar2(70));
1481
1482 type t_assets is table of r_assets index by binary_integer;
1483 type t_edi_record is table of varchar2(255) index by binary_integer;
1484
1485 assets t_assets;
1486 edi_record t_edi_record;
1487 l_edi number;
1488 l_index number;
1489 l_count number;
1490 l_total number;
1491 l_annual number;
1492
1493 edi_ftx1a varchar2(6);
1494 edi_tax1 varchar2(6);
1495 edi_moa1 varchar2(6);
1496 edi_currency varchar2(3);
1497 edi_cat varchar2(18);
1498 edi_tax_qualifier1 varchar2(3);
1499 edi_tax_qualifier7 varchar2(3);
1500 edi_tax_qualifier12 varchar2(3);
1501 edi_tax_qualifier116 varchar2(3);
1502
1503 cursor get_data is
1504 select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1505 use_index(pai_person,pay_action_information_n2)
1506 use_index(pai,pay_action_information_n2) */
1507 sum(to_number(nvl(pai.action_information7,0))) annual_value,
1508 sum(to_number(nvl(pai.action_information8,0))) amount_made_good,
1509 sum(to_number(nvl(pai.action_information9,0))) cash_equivalent,
1510 pay_gb_p11d_magtape.get_description(pai.action_information5,'GB_ASSETS',
1511 pai.action_information4) asset_type
1512 from per_all_assignments_f paf,
1513 pay_assignment_actions paa,
1514 pay_action_information pai,
1515 pay_action_information pai_person
1516 where paf.person_id = p_person_id
1517 and paf.effective_end_date = (select max(paf2.effective_end_date)
1518 from per_all_assignments_f paf2
1519 where paf2.assignment_id = paf.assignment_id
1520 and paf2.person_id = p_person_id)
1521 and paf.assignment_id = paa.assignment_id
1522 and paa.payroll_action_id = p_pact_id
1523 and pai.action_context_id = paa.assignment_action_id
1524 and pai.action_context_type = 'AAP'
1525 and pai.action_information_category = 'ASSETS AT EMP DISPOSAL'
1526 and pai_person.action_context_id = paa.assignment_action_id
1527 and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
1528 and upper(pai_person.action_information13) = upper(p_emp_ref)
1529 and pai_person.action_context_type = 'AAP'
1530 group by pay_gb_p11d_magtape.get_description(pai.action_information5,'GB_ASSETS',
1531 pai.action_information4);
1532 begin
1533 edi_cat := rpad('L',18);
1534 edi_ftx1a := rpad('FTX1A',6);
1535 edi_moa1 := rpad('MOA1',6);
1536 edi_tax1 := rpad('TAX1',6);
1537 edi_currency := 'GBP';
1538 edi_tax_qualifier1 := rpad('1',3);
1539 edi_tax_qualifier7 := rpad('7',3);
1540 edi_tax_qualifier12 := rpad('12',3);
1541 edi_tax_qualifier116 := rpad('116',3);
1542
1543 l_total := 0;
1544 l_count := 0;
1545 for asset in get_data loop
1546 hr_utility.trace('Asset : ' || asset.asset_type );
1547 hr_utility.trace('Cash : ' || asset.cash_equivalent );
1548 if asset.cash_equivalent >= 1 then
1549 l_count := l_count + 1;
1550 l_total := l_total + asset.cash_equivalent;
1551 l_annual := asset.cash_equivalent + asset.amount_made_good;
1552 assets(l_count).description := substr(asset.asset_type,1,70);
1553 assets(l_count).annual_value := pay_gb_p11d_magtape.format_edi_currency(l_annual);
1557 end loop;
1554 assets(l_count).cash_equivalent := pay_gb_p11d_magtape.format_edi_currency(asset.cash_equivalent);
1555 assets(l_count).amount_made_good := pay_gb_p11d_magtape.format_edi_currency(asset.amount_made_good);
1556 end if;
1558
1559 l_edi := 0;
1560 --- This can repeat up to 7 times
1561 for l_index in 1..l_count loop
1562 l_edi := l_edi + 1;
1563 edi_record(l_edi) := edi_ftx1a || edi_cat || rpad(assets(l_index).description, 71) || rpad(' ', 71) || rpad(' ',70) || fnd_global.local_chr(10);
1564 l_edi := l_edi + 1;
1565 edi_record(l_edi) := edi_tax1 || edi_tax_qualifier7 || fnd_global.local_chr(10) ||
1566 edi_moa1 || assets(l_index).annual_value || edi_currency || fnd_global.local_chr(10) ||
1567 edi_tax1 || edi_tax_qualifier1 || fnd_global.local_chr(10) ||
1568 edi_moa1 || assets(l_index).amount_made_good || edi_currency || fnd_global.local_chr(10) ||
1569 edi_tax1 || edi_tax_qualifier12 || fnd_global.local_chr(10) ||
1570 edi_moa1 || assets(l_index).cash_equivalent || edi_currency || fnd_global.local_chr(10);
1571 end loop;
1572
1573 if (l_total >= 1) then
1574 edi_cat := rpad('S',18);
1575 l_edi := l_edi + 1;
1576 edi_record(l_edi) := edi_ftx1a || edi_cat || rpad(' ',71) || rpad(' ',71) || rpad(' ',70) || fnd_global.local_chr(10);
1577 l_edi := l_edi + 1;
1578 edi_record(l_edi) := edi_tax1 || edi_tax_qualifier116 || fnd_global.local_chr(10) || edi_moa1 ||
1579 pay_gb_p11d_magtape.format_edi_currency(l_total) || edi_currency || fnd_global.local_chr(10);
1580 end if;
1581
1582 -- Maximum output is 16 + 1 edi records
1583 l_edi := l_edi + 1;
1584 for l_index in l_edi..17 loop
1585 edi_record(l_index) := null;
1586 end loop;
1587
1588 p_edi_rec1 := edi_record(1);
1589 p_edi_rec2 := edi_record(2);
1590 p_edi_rec3 := edi_record(3);
1591 p_edi_rec4 := edi_record(4);
1592 p_edi_rec5 := edi_record(5);
1593 p_edi_rec6 := edi_record(6);
1594 p_edi_rec7 := edi_record(7);
1595 p_edi_rec8 := edi_record(8);
1596 p_edi_rec9 := edi_record(9);
1597 p_edi_rec10 := edi_record(10);
1598 p_edi_rec11 := edi_record(11);
1599 p_edi_rec12 := edi_record(12);
1600 p_edi_rec13 := edi_record(13);
1601 p_edi_rec14 := edi_record(14);
1602 p_edi_rec15 := edi_record(15);
1603 p_edi_rec16 := edi_record(16);
1604 end get_assets_at_emp;
1605 /******************* Assets Placed at The Employee's Disposal (Multi Occurance) ***********************/
1606
1607 /******************* Other Items (Multi Occurance) ***********************/
1608 procedure get_other_items(p_person_id in varchar2,
1609 p_emp_ref in varchar2,
1610 p_pact_id in varchar2,
1611 p_edi_rec1 out NOCOPY varchar2,
1612 p_edi_rec2 out NOCOPY varchar2,
1613 p_edi_rec3 out NOCOPY varchar2,
1614 p_edi_rec4 out NOCOPY varchar2,
1615 p_edi_rec5 out NOCOPY varchar2,
1616 p_edi_rec6 out NOCOPY varchar2,
1617 p_edi_rec7 out NOCOPY varchar2,
1618 p_edi_rec8 out NOCOPY varchar2,
1619 p_edi_rec9 out NOCOPY varchar2,
1620 p_edi_rec10 out NOCOPY varchar2,
1621 p_edi_rec11 out NOCOPY varchar2,
1622 p_edi_rec12 out NOCOPY varchar2,
1623 p_edi_rec13 out NOCOPY varchar2,
1624 p_edi_rec14 out NOCOPY varchar2,
1625 p_edi_rec15 out NOCOPY varchar2,
1626 p_edi_rec16 out NOCOPY varchar2,
1627 p_edi_rec17 out NOCOPY varchar2,
1628 p_edi_rec18 out NOCOPY varchar2,
1629 p_edi_rec19 out NOCOPY varchar2,
1630 p_edi_rec20 out NOCOPY varchar2,
1631 p_edi_rec21 out NOCOPY varchar2,
1632 p_edi_rec22 out NOCOPY varchar2)
1633 is
1634 type r_other_items is record(
1635 description varchar2(70),
1636 cost_to_you varchar2(70),
1637 cash_equivalent varchar2(70),
1638 amount_made_good varchar2(70));
1639
1640 type t_other_items is table of r_other_items index by binary_integer;
1641 type t_edi_record is table of varchar2(255) index by binary_integer;
1642
1643 class_1A t_other_items;
1644 non_class_1A t_other_items;
1645 edi_record t_edi_record;
1646 l_edi number;
1647 l_index number;
1648 o1A_count number;
1649 non_1A_count number;
1650 l_count number;
1651 o1A_total number;
1652 non_1a_total number;
1653 dir_total number;
1654 o1a_desc varchar2(50);
1655 non1a_desc varchar2(50);
1656 edi_ftx1a varchar2(6);
1657 edi_tax1 varchar2(6);
1661 edi_desc varchar2(71);
1658 edi_moa1 varchar2(6);
1659 edi_currency varchar2(3);
1660 edi_cat varchar2(18);
1662 edi_tax_qualifier56 varchar2(3);
1663 edi_tax_qualifier57 varchar2(3);
1664 edi_tax_qualifier58 varchar2(3);
1665 edi_tax_qualifier109 varchar2(3);
1666 edi_tax_qualifier110 varchar2(3);
1667 edi_tax_qualifier111 varchar2(3);
1668 edi_tax_qualifier118 varchar2(3);
1669 edi_tax_qualifier119 varchar2(3);
1670 edi_tax_qualifier120 varchar2(3);
1671
1672 cursor get_data(p_category varchar2, p_lookup varchar2) is
1673 select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1674 use_index(pai_person,pay_action_information_n2)
1675 use_index(pai,pay_action_information_n2) */
1676 sum(to_number(nvl(pai.action_information9,0))) cash_equivalent,
1677 sum(to_number(nvl(pai.action_information7,0))) cost_to_you,
1678 sum(to_number(nvl(pai.action_information8,0))) amount_made_good,
1679 pay_gb_p11d_magtape.get_description(pai.action_information5,
1680 p_lookup, pai.action_information4) description
1681 from per_all_assignments_f paf,
1682 pay_assignment_actions paa,
1683 pay_action_information pai,
1684 pay_action_information pai_person
1685 where paf.person_id = p_person_id
1686 and paf.effective_end_date = (select max(paf2.effective_end_date)
1687 from per_all_assignments_f paf2
1688 where paf2.assignment_id = paf.assignment_id
1689 and paf2.person_id = p_person_id)
1690 and paf.assignment_id = paa.assignment_id
1691 and paa.payroll_action_id = p_pact_id
1692 and pai.action_context_id = paa.assignment_action_id
1693 and pai.action_context_type = 'AAP'
1694 and pai.action_information_category = p_category
1695 and pai_person.action_context_id = paa.assignment_action_id
1696 and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
1697 and upper(pai_person.action_information13) = upper(p_emp_ref)
1698 and pai_person.action_context_type = 'AAP'
1699 group by pay_gb_p11d_magtape.get_description(pai.action_information5,
1700 p_lookup, pai.action_information4);
1701 begin
1702 edi_cat := rpad('M',18);
1703 edi_ftx1a := rpad('FTX1A',6);
1704 edi_moa1 := rpad('MOA1',6);
1705 edi_tax1 := rpad('TAX1',6);
1706 edi_currency := 'GBP';
1707 edi_desc := rpad('DESC',71);
1708 edi_tax_qualifier56 := rpad('56',3);
1709 edi_tax_qualifier57 := rpad('57',3);
1710 edi_tax_qualifier58 := rpad('58',3);
1711 edi_tax_qualifier109 := rpad('109',3);
1712 edi_tax_qualifier110 := rpad('110',3);
1713 edi_tax_qualifier111 := rpad('111',3);
1714 edi_tax_qualifier118 := rpad('118',3);
1715 edi_tax_qualifier119 := rpad('119',3);
1716 edi_tax_qualifier120 := rpad('120',3);
1717
1718 o1A_count := 0;
1719 o1A_total := 0;
1720 for o1a in get_data('OTHER ITEMS','GB_OTHER_ITEMS')loop
1721 if o1a.cash_equivalent >= 1 then
1722 o1A_count := o1A_count + 1;
1723 o1A_total := o1A_total + o1a.cash_equivalent;
1724 class_1A(o1A_count).description := o1a.description;
1725 class_1A(o1A_count).cost_to_you := pay_gb_p11d_magtape.format_edi_currency(o1a.cost_to_you);
1726 class_1A(o1A_count).cash_equivalent := pay_gb_p11d_magtape.format_edi_currency(o1a.cash_equivalent);
1727 class_1A(o1A_count).amount_made_good := pay_gb_p11d_magtape.format_edi_currency(o1a.amount_made_good);
1728 end if;
1729 end loop;
1730
1731 non_1A_count := 0;
1732 non_1A_total := 0;
1733 dir_total := 0;
1734 for non1a in get_data('OTHER ITEMS NON 1A','GB_OTHER_ITEMS_NON_1A')loop
1735 if non1a.description = 'DIRECTOR TAX PAID NOT DEDUCTED' then
1736 dir_total := dir_total + non1a.cash_equivalent;
1737 else
1738 if non1a.cash_equivalent >= 1 then
1739 non_1A_count := non_1A_count + 1;
1740 non_1A_total := non_1A_total + non1a.cash_equivalent;
1741 non_class_1A(non_1A_count).description := non1a.description;
1742 non_class_1A(non_1A_count).cost_to_you := pay_gb_p11d_magtape.format_edi_currency(non1a.cost_to_you);
1743 non_class_1A(non_1A_count).cash_equivalent := pay_gb_p11d_magtape.format_edi_currency(non1a.cash_equivalent);
1744 non_class_1A(non_1A_count).amount_made_good := pay_gb_p11d_magtape.format_edi_currency(non1a.amount_made_good);
1745 end if;
1746 end if;
1747 end loop;
1748
1749 l_count := greatest(o1A_count, non_1A_count);
1750 l_edi := 0;
1751 /*** This can repeat up to 6 times ***/
1752 for l_index in 1..l_count loop
1753 o1a_desc := ' ';
1754 non1a_desc := ' ';
1755 if l_index <= o1A_count then
1756 o1a_desc := class_1A(l_index).description;
1757 end if;
1758 if l_index <= non_1A_count then
1759 non1a_desc := non_class_1A(l_index).description;
1760 end if;
1764 if o1a_desc <> ' ' then
1761 l_edi := l_edi + 1;
1762 edi_record(l_edi) := edi_ftx1a || edi_cat || edi_desc || rpad(o1a_desc,71) ||
1763 rpad(non1a_desc,70) || fnd_global.local_chr(10);
1765 l_edi := l_edi + 1;
1766 edi_record(l_edi) := edi_tax1 || edi_tax_qualifier58 || fnd_global.local_chr(10) || edi_moa1 ||
1767 class_1A(l_index).cost_to_you || edi_currency || fnd_global.local_chr(10) ||
1768 edi_tax1 || edi_tax_qualifier56 || fnd_global.local_chr(10) || edi_moa1 ||
1769 class_1A(l_index).amount_made_good || edi_currency || fnd_global.local_chr(10) ||
1770 edi_tax1 || edi_tax_qualifier57 || fnd_global.local_chr(10) || edi_moa1 ||
1771 class_1A(l_index).cash_equivalent || edi_currency || fnd_global.local_chr(10);
1772 end if;
1773
1774 if non1a_desc <> ' ' then
1775 l_edi := l_edi + 1;
1776 edi_record(l_edi) := edi_tax1 || edi_tax_qualifier111 || fnd_global.local_chr(10) || edi_moa1 ||
1777 non_class_1A(l_index).cost_to_you || edi_currency || fnd_global.local_chr(10) ||
1778 edi_tax1 || edi_tax_qualifier109 || fnd_global.local_chr(10) || edi_moa1 ||
1779 non_class_1A(l_index).amount_made_good || edi_currency || fnd_global.local_chr(10) ||
1780 edi_tax1 || edi_tax_qualifier110 || fnd_global.local_chr(10) || edi_moa1 ||
1781 non_class_1A(l_index).cash_equivalent || edi_currency || fnd_global.local_chr(10);
1782 end if;
1783 end loop;
1784
1785 if (o1A_total >= 1 or
1786 dir_total >= 1 or
1787 non_1A_total >= 1 ) then
1788 edi_cat := rpad('T',18);
1789 l_edi := l_edi + 1;
1790 edi_record(l_edi) := edi_ftx1a || edi_cat || rpad(' ',71) || rpad(' ',71) || rpad(' ',70) || fnd_global.local_chr(10);
1791 l_edi := l_edi + 1;
1792 edi_record(l_edi) := edi_tax1 || edi_tax_qualifier118 || fnd_global.local_chr(10) || edi_moa1 ||
1793 pay_gb_p11d_magtape.format_edi_currency(o1A_total) || edi_currency || fnd_global.local_chr(10);
1794 l_edi := l_edi + 1;
1795 edi_record(l_edi) := edi_tax1 || edi_tax_qualifier119 || fnd_global.local_chr(10) || edi_moa1 ||
1796 pay_gb_p11d_magtape.format_edi_currency(non_1A_total) || edi_currency || fnd_global.local_chr(10);
1797 l_edi := l_edi + 1;
1798 edi_record(l_edi) := edi_tax1 || edi_tax_qualifier120 || fnd_global.local_chr(10) || edi_moa1 ||
1799 pay_gb_p11d_magtape.format_edi_currency(dir_total) || edi_currency || fnd_global.local_chr(10);
1800 end if;
1801
1802 -- Total output edi record is 22 + 1 records
1803 l_edi := l_edi + 1;
1804 for l_index in l_edi..23 loop
1805 edi_record(l_index) := null;
1806 end loop;
1807
1808 p_edi_rec1 := edi_record(1);
1809 p_edi_rec2 := edi_record(2);
1810 p_edi_rec3 := edi_record(3);
1811 p_edi_rec4 := edi_record(4);
1812 p_edi_rec5 := edi_record(5);
1813 p_edi_rec6 := edi_record(6);
1814 p_edi_rec7 := edi_record(7);
1815 p_edi_rec8 := edi_record(8);
1816 p_edi_rec9 := edi_record(9);
1817 p_edi_rec10 := edi_record(10);
1818 p_edi_rec11 := edi_record(11);
1819 p_edi_rec12 := edi_record(12);
1820 p_edi_rec13 := edi_record(13);
1821 p_edi_rec14 := edi_record(14);
1822 p_edi_rec15 := edi_record(15);
1823 p_edi_rec16 := edi_record(16);
1824 p_edi_rec17 := edi_record(17);
1825 p_edi_rec18 := edi_record(18);
1826 p_edi_rec19 := edi_record(19);
1827 p_edi_rec20 := edi_record(20);
1828 p_edi_rec21 := edi_record(21);
1829 p_edi_rec22 := edi_record(22);
1830 end get_other_items;
1831 /******************* Other Items (Multi Occurance) ***********************/
1832
1833 /******************* Expenses Payments Made To or On Behalf of The Employee (Single Occurance) ***********************/
1834 procedure get_exp_payment(p_person_id in varchar2,
1835 p_emp_ref in varchar2,
1836 p_pact_id in varchar2,
1837 p_edi_rec1 out NOCOPY varchar2,
1838 p_edi_rec2 out NOCOPY varchar2,
1839 p_edi_rec3 out NOCOPY varchar2,
1840 p_edi_rec4 out NOCOPY varchar2,
1841 p_edi_rec5 out NOCOPY varchar2,
1842 p_edi_rec6 out NOCOPY varchar2,
1843 p_edi_rec7 out NOCOPY varchar2,
1844 p_edi_rec8 out NOCOPY varchar2,
1845 p_edi_rec9 out NOCOPY varchar2,
1846 p_edi_rec10 out NOCOPY varchar2,
1847 p_edi_rec11 out NOCOPY varchar2,
1848 p_edi_rec12 out NOCOPY varchar2,
1849 p_edi_rec13 out NOCOPY varchar2,
1850 p_edi_rec14 out NOCOPY varchar2,
1851 p_edi_rec15 out NOCOPY varchar2,
1852 p_edi_rec16 out NOCOPY varchar2,
1856 p_edi_rec20 out NOCOPY varchar2)
1853 p_edi_rec17 out NOCOPY varchar2,
1854 p_edi_rec18 out NOCOPY varchar2,
1855 p_edi_rec19 out NOCOPY varchar2,
1857 is
1858 l_trvlnsubs_cost_to_you number;
1859 l_trvlnsubs_amount_made_good number;
1860 l_trvlnsubs_cash_equivalent number;
1861 l_entertain_cost_to_you number;
1862 l_entertain_amount_made_good number;
1863 l_entertain_cash_equivalent number;
1864 l_bustrvl_cost_to_you number;
1865 l_bustrvl_amount_made_good number;
1866 l_bustrvl_cash_equivalent number;
1867 l_hometel_cost_to_you number;
1868 l_hometel_amount_made_good number;
1869 l_hometel_cash_equivalent number;
1870 l_nonqreloc_cost_to_you number;
1871 l_nonqreloc_amount_made_good number;
1872 l_nonqreloc_cash_equivalent number;
1873 l_other_cost_to_you number;
1874 l_other_amount_made_good number;
1875 l_other_cash_equivalent number;
1876 l_other_description varchar2(255);
1877 l_trading_indicator varchar2(255);
1878 edi_cat varchar2(18);
1879 edi_ftx1a varchar2(18);
1880 edi_tax1 varchar2(6);
1881 edi_moa1 varchar2(6);
1882 edi_currency varchar2(3);
1883 edi_att3 varchar2(6);
1884 edi_tax_qualifier3 varchar2(4);
1885 edi_tax_qualifier25 varchar2(3);
1886 edi_tax_qualifier26 varchar2(3);
1887 edi_tax_qualifier27 varchar2(3);
1888 edi_tax_qualifier28 varchar2(3);
1889 edi_tax_qualifier29 varchar2(3);
1890 edi_tax_qualifier30 varchar2(3);
1891 edi_tax_qualifier50 varchar2(3);
1892 edi_tax_qualifier51 varchar2(3);
1893 edi_tax_qualifier52 varchar2(3);
1894 edi_tax_qualifier53 varchar2(3);
1895 edi_tax_qualifier54 varchar2(3);
1896 edi_tax_qualifier55 varchar2(3);
1897 edi_tax_qualifier85 varchar2(3);
1898 edi_tax_qualifier86 varchar2(3);
1899 edi_tax_qualifier87 varchar2(3);
1900 edi_tax_qualifier88 varchar2(3);
1901 edi_tax_qualifier89 varchar2(3);
1902 edi_tax_qualifier90 varchar2(3);
1903
1904 cursor get_data
1905 is
1906 select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
1907 use_index(pai_person,pay_action_information_n2)
1908 use_index(pai,pay_action_information_n2) */
1909 to_number(pai.action_information6) cost_to_you,
1910 to_number(pai.action_information7) amount_m_good,
1911 to_number(pai.action_information8) cash_equivalent,
1912 pay_gb_p11d_magtape.get_description(pai.action_information5,'GB_EXPENSE_TYPE',
1913 pai.action_information4) expense_type,
1914 nvl(pai.action_information10,'N') trading_indicator
1915 from per_all_assignments_f paf,
1916 pay_assignment_actions paa,
1917 pay_action_information pai,
1918 pay_action_information pai_person
1919 where paf.person_id = p_person_id
1920 and paf.effective_end_date = (select max(paf2.effective_end_date)
1921 from per_all_assignments_f paf2
1922 where paf2.assignment_id = paf.assignment_id
1923 and paf2.person_id = p_person_id)
1924 and paf.assignment_id = paa.assignment_id
1925 and paa.payroll_action_id = p_pact_id
1926 and pai.action_context_id = paa.assignment_action_id
1927 and pai.action_context_type = 'AAP'
1928 and pai.action_information_category = 'EXPENSES PAYMENTS'
1929 and pai_person.action_context_id = paa.assignment_action_id
1930 and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
1931 and upper(pai_person.action_information13) = upper(p_emp_ref)
1932 and pai_person.action_context_type = 'AAP';
1933 begin
1934 edi_cat := rpad('N',18);
1935 edi_ftx1a := rpad('FTX1A',6);
1936 edi_moa1 := rpad('MOA1',6);
1937 edi_tax1 := rpad('TAX1',6);
1938 edi_currency := 'GBP';
1939 edi_att3 := rpad('ATT3',6);
1940 edi_tax_qualifier3 := rpad('3',4);
1941 edi_tax_qualifier25 := rpad('25',3);
1942 edi_tax_qualifier26 := rpad('26',3);
1943 edi_tax_qualifier27 := rpad('27',3);
1944 edi_tax_qualifier28 := rpad('28',3);
1945 edi_tax_qualifier29 := rpad('29',3);
1946 edi_tax_qualifier30 := rpad('30',3);
1947 edi_tax_qualifier50 := rpad('50',3);
1948 edi_tax_qualifier51 := rpad('51',3);
1949 edi_tax_qualifier52 := rpad('52',3);
1950 edi_tax_qualifier53 := rpad('53',3);
1951 edi_tax_qualifier54 := rpad('54',3);
1952 edi_tax_qualifier55 := rpad('55',3);
1953 edi_tax_qualifier85 := rpad('85',3);
1954 edi_tax_qualifier86 := rpad('86',3);
1955 edi_tax_qualifier87 := rpad('87',3);
1956 edi_tax_qualifier88 := rpad('88',3);
1957 edi_tax_qualifier89 := rpad('89',3);
1958 edi_tax_qualifier90 := rpad('90',3);
1959 l_trvlnsubs_cost_to_you := 0;
1960 l_trvlnsubs_amount_made_good := 0;
1961 l_trvlnsubs_cash_equivalent := 0;
1962 l_entertain_cost_to_you := 0;
1963 l_entertain_amount_made_good := 0;
1964 l_entertain_cash_equivalent := 0;
1968 l_hometel_cost_to_you := 0;
1965 l_bustrvl_cost_to_you := 0;
1966 l_bustrvl_amount_made_good := 0;
1967 l_bustrvl_cash_equivalent := 0;
1969 l_hometel_amount_made_good := 0;
1970 l_hometel_cash_equivalent := 0;
1971 l_nonqreloc_cost_to_you := 0;
1972 l_nonqreloc_amount_made_good := 0;
1973 l_nonqreloc_cash_equivalent := 0;
1974 l_other_cost_to_you := 0;
1975 l_other_amount_made_good := 0;
1976 l_other_cash_equivalent := 0;
1977
1978 l_other_description := ' ';
1979
1980 for expense in get_data loop
1981 if expense.expense_type = 'TRAVEL AND SUBSISTENCE' then
1982 l_trvlnsubs_cost_to_you := l_trvlnsubs_cost_to_you + expense.cost_to_you;
1983 l_trvlnsubs_amount_made_good := l_trvlnsubs_amount_made_good + expense.amount_m_good;
1984 l_trvlnsubs_cash_equivalent := l_trvlnsubs_cash_equivalent + expense.cash_equivalent;
1985 elsif expense.expense_type = 'ENTERTAINMENT' then
1986 l_trading_indicator := expense.trading_indicator;
1987 l_entertain_cost_to_you := l_entertain_cost_to_you + expense.cost_to_you;
1988 l_entertain_amount_made_good := l_entertain_amount_made_good + expense.amount_m_good;
1989 l_entertain_cash_equivalent := l_entertain_cash_equivalent + expense.cash_equivalent;
1990 elsif expense.expense_type = 'ALLOWANCE FOR BUSINESS TRAVEL' then
1991 l_bustrvl_cost_to_you := l_bustrvl_cost_to_you + expense.cost_to_you;
1992 l_bustrvl_amount_made_good := l_bustrvl_amount_made_good + expense.amount_m_good;
1993 l_bustrvl_cash_equivalent := l_bustrvl_cash_equivalent + expense.cash_equivalent;
1994 elsif expense.expense_type = 'USE OF HOME TELEPHONE' then
1995 l_hometel_cost_to_you := l_hometel_cost_to_you + expense.cost_to_you;
1996 l_hometel_amount_made_good := l_hometel_amount_made_good + expense.amount_m_good;
1997 l_hometel_cash_equivalent := l_hometel_cash_equivalent + expense.cash_equivalent;
1998 elsif expense.expense_type = 'NON-QUALIFYING RELOCATION' then
1999 l_nonqreloc_cost_to_you := l_nonqreloc_cost_to_you + expense.cost_to_you;
2000 l_nonqreloc_amount_made_good := l_nonqreloc_amount_made_good + expense.amount_m_good;
2001 l_nonqreloc_cash_equivalent := l_nonqreloc_cash_equivalent + expense.cash_equivalent;
2002 else
2003 if l_other_description <> ' ' then
2004 l_other_description := 'MULTIPLE' || rpad(' ',30,' ');
2005 else
2006 l_other_description := expense.expense_type || rpad(' ',30,' ');
2007 end if;
2008 l_other_cost_to_you := l_other_cost_to_you + expense.cost_to_you;
2009 l_other_amount_made_good := l_other_amount_made_good + expense.amount_m_good;
2010 l_other_cash_equivalent := l_other_cash_equivalent + expense.cash_equivalent;
2011 end if;
2012 end loop;
2013
2014 if (l_trvlnsubs_cash_equivalent >= 1 or
2015 l_entertain_cash_equivalent >= 1 or
2016 l_bustrvl_cash_equivalent >= 1 or
2017 l_hometel_cash_equivalent >= 1 or
2018 l_nonqreloc_cash_equivalent >= 1 or
2019 l_other_cash_equivalent >= 1) then
2020
2021 if l_trvlnsubs_cash_equivalent < 1 then
2022 l_trvlnsubs_cash_equivalent := 0;
2023 end if;
2024 if l_entertain_cash_equivalent < 1 then
2025 l_entertain_cash_equivalent := 0;
2026 end if;
2027 if l_bustrvl_cash_equivalent < 1 then
2028 l_bustrvl_cash_equivalent := 0;
2029 end if;
2030 if l_hometel_cash_equivalent < 1 then
2031 l_hometel_cash_equivalent := 0;
2032 end if;
2033 if l_nonqreloc_cash_equivalent < 1 then
2034 l_nonqreloc_cash_equivalent := 0;
2035 end if;
2036 if l_other_cash_equivalent < 1 then
2037 l_other_cash_equivalent := 0;
2038 end if;
2039
2040 p_edi_rec1 := edi_ftx1a || edi_cat || rpad(rpad(l_other_description,30),71) || rpad(' ',71) ||
2041 rpad(' ',70) || fnd_global.local_chr(10);
2042 p_edi_rec2 := edi_tax1 || edi_tax_qualifier86 || fnd_global.local_chr(10) ||
2043 edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_trvlnsubs_cost_to_you) || edi_currency || fnd_global.local_chr(10);
2044 p_edi_rec3 := edi_tax1 || edi_tax_qualifier85 || fnd_global.local_chr(10) ||
2045 edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_trvlnsubs_amount_made_good) || edi_currency || fnd_global.local_chr(10);
2046 p_edi_rec4 := edi_tax1 || edi_tax_qualifier87 || fnd_global.local_chr(10) ||
2047 edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_trvlnsubs_cash_equivalent) || edi_currency || fnd_global.local_chr(10);
2048 p_edi_rec5 := edi_tax1 || edi_tax_qualifier26 || fnd_global.local_chr(10) ||
2049 edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_entertain_cost_to_you) || edi_currency || fnd_global.local_chr(10);
2050 p_edi_rec6 := edi_tax1 || edi_tax_qualifier25 || fnd_global.local_chr(10) ||
2051 edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_entertain_amount_made_good) || edi_currency || fnd_global.local_chr(10);
2055 edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_bustrvl_cost_to_you) || edi_currency || fnd_global.local_chr(10);
2052 p_edi_rec7 := edi_tax1 || edi_tax_qualifier27 || fnd_global.local_chr(10) ||
2053 edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_entertain_cash_equivalent) || edi_currency || fnd_global.local_chr(10);
2054 p_edi_rec8 := edi_tax1 || edi_tax_qualifier29 || fnd_global.local_chr(10) ||
2056 p_edi_rec9 := edi_tax1 || edi_tax_qualifier28 || fnd_global.local_chr(10) ||
2057 edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_bustrvl_amount_made_good) || edi_currency || fnd_global.local_chr(10);
2058 p_edi_rec10 := edi_tax1 || edi_tax_qualifier30 || fnd_global.local_chr(10) ||
2059 edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_bustrvl_cash_equivalent) || edi_currency || fnd_global.local_chr(10);
2060 p_edi_rec11 := edi_tax1 || edi_tax_qualifier89 || fnd_global.local_chr(10) ||
2061 edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_hometel_cost_to_you) || edi_currency || fnd_global.local_chr(10);
2062 p_edi_rec12 := edi_tax1 || edi_tax_qualifier88 || fnd_global.local_chr(10) ||
2063 edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_hometel_amount_made_good) || edi_currency || fnd_global.local_chr(10);
2064 p_edi_rec13 := edi_tax1 || edi_tax_qualifier90 || fnd_global.local_chr(10) ||
2065 edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_hometel_cash_equivalent) || edi_currency || fnd_global.local_chr(10);
2066 p_edi_rec14 := edi_tax1 || edi_tax_qualifier51 || fnd_global.local_chr(10) ||
2067 edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_nonqreloc_cost_to_you) || edi_currency || fnd_global.local_chr(10);
2068 p_edi_rec15 := edi_tax1 || edi_tax_qualifier50 || fnd_global.local_chr(10) ||
2069 edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_nonqreloc_amount_made_good) || edi_currency || fnd_global.local_chr(10);
2070 p_edi_rec16 := edi_tax1 || edi_tax_qualifier52 || fnd_global.local_chr(10) ||
2071 edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_nonqreloc_cash_equivalent) || edi_currency || fnd_global.local_chr(10);
2072 p_edi_rec17 := edi_tax1 || edi_tax_qualifier54 || fnd_global.local_chr(10) ||
2073 edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_other_cost_to_you) || edi_currency || fnd_global.local_chr(10);
2074 p_edi_rec18 := edi_tax1 || edi_tax_qualifier53 || fnd_global.local_chr(10) ||
2075 edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_other_amount_made_good) || edi_currency || fnd_global.local_chr(10);
2076 p_edi_rec19 := edi_tax1 || edi_tax_qualifier55 || fnd_global.local_chr(10) ||
2077 edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_other_cash_equivalent) || edi_currency || fnd_global.local_chr(10);
2078 if l_entertain_cash_equivalent >= 1 then
2079 p_edi_rec20 := edi_att3 || edi_tax_qualifier3 || rpad(l_trading_indicator,35) || fnd_global.local_chr(10);
2080 end if;
2081 end if;
2082 end get_exp_payment;
2083 /******************* Expenses Payments Made To or On Behalf of The Employee (Single Occurance) ***********************/
2084
2085 /******************* Mileage Allowance Relief Optional Reporting Scheme (Single Occurance) ***********************/
2086 procedure get_marors(p_person_id in varchar2,
2087 p_emp_ref in varchar2,
2088 p_pact_id in varchar2,
2089 p_edi_rec1 out NOCOPY varchar2,
2090 p_edi_rec2 out NOCOPY varchar2,
2091 p_edi_rec3 out NOCOPY varchar2)
2092 is
2093 l_marors number;
2094
2095 edi_ftx1a varchar2(6);
2096 edi_tax1 varchar2(6);
2097 edi_moa1 varchar2(6);
2098 edi_currency varchar2(3);
2099 edi_cat varchar2(18);
2100 edi_tax_qualifier121 varchar2(3);
2101
2102 cursor get_data is
2103 select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
2104 use_index(pai_person,pay_action_information_n2)
2105 use_index(pai,pay_action_information_n2) */
2106 sum(to_number(NVL(pai.action_information7, 0))) mileage_allowance
2107 from per_all_assignments_f paf,
2108 pay_assignment_actions paa,
2109 pay_action_information pai,
2110 pay_action_information pai_person
2111 where paf.person_id = p_person_id
2112 and paf.effective_end_date = (select max(paf2.effective_end_date)
2113 from per_all_assignments_f paf2
2114 where paf2.assignment_id = paf.assignment_id
2115 and paf2.person_id = p_person_id)
2116 and paf.assignment_id = paa.assignment_id
2117 and paa.payroll_action_id = p_pact_id
2118 and pai.action_context_id = paa.assignment_action_id
2119 and pai.action_context_type = 'AAP'
2120 and pai.action_information_category = 'MARORS'
2121 and pai_person.action_context_id = paa.assignment_action_id
2122 and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
2123 and upper(pai_person.action_information13) = upper(p_emp_ref)
2124 and pai_person.action_context_type = 'AAP';
2125 begin
2126 edi_cat := rpad('U',18);
2127 edi_ftx1a := rpad('FTX1A',6);
2128 edi_moa1 := rpad('MOA1',6);
2129 edi_tax1 := rpad('TAX1',6);
2133 open get_data;
2130 edi_currency := 'GBP';
2131 edi_tax_qualifier121 := rpad('121',3);
2132
2134 fetch get_data into l_marors;
2135 close get_data;
2136
2137 if l_marors <= -1 then
2138 p_edi_rec1 := edi_ftx1a || edi_cat || rpad(' ', 71) || rpad(' ', 71) ||
2139 rpad(' ', 70) || fnd_global.local_chr(10);
2140 p_edi_rec2 := edi_tax1 || edi_tax_qualifier121 || fnd_global.local_chr(10);
2141 p_edi_rec3 := edi_moa1 || pay_gb_p11d_magtape.format_edi_currency(l_marors) ||
2142 edi_currency || fnd_global.local_chr(10);
2143 end if;
2144 end get_marors;
2145 /******************* Mileage Allowance Relief Optional Reporting Scheme (Single Occurance) ***********************/
2146
2147 /******************** END PRIVATE FUNCTIONS/PROCEDURES ********************/
2148
2149 /******************* BEGIN PUBLIC FUNCTIONS/PROCEDURES ********************/
2150 function get_header(p_sender_id in varchar2,
2151 p_transmission_date in varchar2,
2152 p_test_transmission in varchar2,
2153 p_unique_reference in varchar2,
2154 p_tax_year in varchar2,
2155 p_missing_val in out NoCopy number,
2156 p_error_count in out NoCopy number,
2157 p_error_msg1 out NoCopy varchar2,
2158 p_error_msg2 out NoCopy varchar2,
2159 p_error_msg3 out NoCopy varchar2,
2160 p_error_msg4 out NoCopy varchar2,
2161 p_error_msg5 out NoCopy varchar2,
2162 p_error_msg6 out NoCopy varchar2,
2163 p_edi_rec1 out NoCopy varchar2,
2164 p_edi_rec2 out NoCopy varchar2,
2165 p_edi_rec3 out NoCopy varchar2,
2166 p_edi_rec4 out NoCopy varchar2,
2167 p_edi_rec5 out NoCopy varchar2,
2168 p_edi_rec6 out NoCopy varchar2) return number
2169 is
2170 edi_header_identifier varchar2(4);
2171 edi_header_version varchar2(4);
2172 edi_data_type varchar2(8);
2173 edi_data_type_version varchar2(4);
2174 edi_data_type_release varchar2(1);
2175 edi_sender_id varchar2(35);
2176 edi_recipient_id varchar2(35);
2177 edi_test_indicator varchar2(1);
2178 edi_urgent_marker varchar2(1);
2179 edi_transmission_date varchar2(8);
2180 edi_transmission_time varchar2(6);
2181 edi_unique_reference varchar2(14);
2182 edi_sender_sub_addr varchar2(14);
2183 edi_recipient_s_addr varchar2(14);
2184 edi_bgm1 varchar2(6);
2185 edi_form_type varchar2(3);
2186 begin
2187 edi_header_identifier := '****';
2188 edi_header_version := '001A';
2189 edi_data_type := rpad('P11D' || substr(p_tax_year,3,4),8);
2190 edi_data_type_version := rpad('1.0',4);
2191 edi_data_type_release := ' ';
2192 edi_sender_id := upper(rpad(p_sender_id,35));
2193 edi_recipient_id := rpad('HMRC',35); -- EOY 2008
2194 edi_test_indicator := ' ';
2195 edi_urgent_marker := ' ';
2196 edi_transmission_date := substr(p_transmission_date,1,8);
2197 edi_transmission_time := substr(p_transmission_date,9,6);
2198 edi_unique_reference := lpad(p_unique_reference,14,'0');
2199 edi_sender_sub_addr := rpad(' ',14);
2200 edi_recipient_s_addr := rpad(' ',14);
2201 edi_bgm1 := rpad('BGM1',6);
2202 edi_form_type := rpad('12',3);
2203
2204 if p_test_transmission = 'Y' then
2205 edi_test_indicator := '1';
2206 end if;
2207
2208 /* Perform EDI validation */
2209 if (edi_sender_id = ' ' or edi_sender_id is null) then
2210 p_error_msg1 := ':Sender ID missing.';
2211 p_missing_val := p_missing_val + 1;
2212 elsif pay_gb_eoy_magtape.validate_input(edi_sender_id,'FULL_EDI') > 0 then
2213 p_error_msg1 := ':Sender ID contains illegal character(s)';
2214 p_error_count := p_error_count + 1;
2215 end if;
2216
2217 /* Header Record */
2218 p_edi_rec1 := edi_header_identifier || edi_header_version || edi_data_type ||
2219 edi_data_type_version || edi_data_type_release || edi_sender_id ||
2220 edi_recipient_id || edi_test_indicator || edi_urgent_marker ||
2221 edi_transmission_date || edi_transmission_time || edi_unique_reference ||
2222 edi_sender_sub_addr || edi_recipient_s_addr || fnd_global.local_chr(10);
2223
2224 /* BGM1 Record */
2225 p_edi_rec2 := edi_bgm1 || edi_form_type || fnd_global.local_chr(10);
2226
2227 return 0;
2228 end get_header;
2229
2230 function get_employer(p_tax_office_name in varchar2,
2231 p_tax_phone_no in varchar2,
2232 p_employer_ref in varchar2,
2233 p_employer_name in varchar2,
2234 p_employer_addr in varchar2,
2235 p_submitter_ref in varchar2,
2236 p_message_date in varchar2,
2237 p_tax_year in varchar2,
2241 p_error_msg2 out NoCopy varchar2,
2238 p_party in varchar2,
2239 p_error_count in out NoCopy number,
2240 p_error_msg1 out NoCopy varchar2,
2242 p_error_msg3 out NoCopy varchar2,
2243 p_error_msg4 out NoCopy varchar2,
2244 p_error_msg5 out NoCopy varchar2,
2245 p_error_msg6 out NoCopy varchar2,
2246 p_error_msg7 out NoCopy varchar2,
2247 p_error_msg8 out NoCopy varchar2,
2248 p_error_msg9 out NoCopy varchar2,
2249 p_edi_rec1 out NoCopy varchar2,
2250 p_edi_rec2 out NoCopy varchar2,
2251 p_edi_rec3 out NoCopy varchar2,
2252 p_edi_rec4 out NoCopy varchar2,
2253 p_edi_rec5 out NoCopy varchar2,
2254 p_edi_rec6 out NoCopy varchar2,
2255 p_edi_rec7 out NoCopy varchar2,
2256 p_edi_rec8 out NoCopy varchar2,
2257 p_edi_rec9 out NoCopy varchar2,
2258 p_edi_rec10 out NoCopy varchar2,
2259 p_edi_rec11 out NoCopy varchar2,
2260 p_edi_rec12 out NoCopy varchar2) return number
2261 is
2262 edi_att1 varchar2(6);
2263 edi_att_qualifier1 varchar2(4);
2264 edi_att_qualifier7 varchar2(4);
2265 edi_att_qualifier17 varchar2(4);
2266 edi_date_qualifier166 varchar2(4);
2267 edi_date_qualifier243 varchar2(4);
2268 edi_dtm1 varchar2(6);
2269 edi_format_qualifier102 varchar2(3);
2270 edi_format_qualifier602 varchar2(3);
2271 edi_nad1a varchar2(6);
2272 edi_nad1b varchar2(6);
2273 edi_party_qualifier_bg varchar2(4);
2274 edi_party_qualifier_tc varchar2(4);
2275 edi_uns1 varchar2(5);
2276 edi_addr1 varchar2(255);
2277 edi_addr2 varchar2(255);
2278 edi_addr3 varchar2(255);
2279 edi_addr4 varchar2(255);
2280 ref_id number;
2281 l_wrap_point number;
2282 begin
2283 edi_att1 := rpad('ATT1',6);
2284 edi_att_qualifier1 := rpad('1',4);
2285 edi_att_qualifier7 := rpad('7',4);
2286 edi_att_qualifier17 := rpad('17',4);
2287 edi_date_qualifier166 := rpad('166',4);
2288 edi_date_qualifier243 := rpad('243',4);
2289 edi_dtm1 := rpad('DTM1',6);
2290 edi_format_qualifier102 := '102';
2291 edi_format_qualifier602 := '602';
2292 edi_nad1a := rpad('NAD1A',6);
2293 edi_nad1b := rpad('NAD1B',6);
2294 edi_party_qualifier_bg := rpad('BG',4);
2295 edi_party_qualifier_tc := rpad('TC',4);
2296 edi_uns1 := rpad('UNS1',5);
2297
2298 edi_addr1 := p_employer_addr;
2299
2300 if length(edi_addr1) > 35 then
2301 l_wrap_point := instr(edi_addr1,',', 34 - length(edi_addr1));
2302 if l_wrap_point = 0 then
2303 l_wrap_point := 35;
2304 end if;
2305 edi_addr2 := ltrim(substr(edi_addr1, 1 + l_wrap_point),' ,');
2306 edi_addr1 := substr(edi_addr1, 1, l_wrap_point);
2307 end if;
2308 if length(edi_addr2) > 35 then
2309 l_wrap_point := instr(edi_addr2,',', 34 - length(edi_addr2));
2310 if l_wrap_point = 0 then
2311 l_wrap_point := 35;
2312 end if;
2313 edi_addr3 := ltrim(substr(edi_addr2, 1 + l_wrap_point),' ,');
2314 edi_addr2 := substr(edi_addr2, 1, l_wrap_point);
2315 end if;
2316 if length(edi_addr3) > 35 then
2317 l_wrap_point := instr(edi_addr3,',', 34 - length(edi_addr2));
2318 if l_wrap_point = 0 then
2319 l_wrap_point := 35;
2320 end if;
2321 edi_addr4 := ltrim(substr(edi_addr3, 1 + l_wrap_point),' ,');
2322 edi_addr3 := substr(edi_addr3, 1, l_wrap_point);
2323 end if;
2324
2325 /* Perform EDI validation */
2326 /* Validations are now done at archive level
2327 if pay_gb_eoy_magtape.validate_input(to_number(substr(p_employer_ref,1,3)),'NUMBER') > 0 then
2328 p_error_msg1 := ':Tax District ' || substr(p_employer_ref,1,3) || ' is non-numeric';
2329 p_error_count := p_error_count + 1;
2330 end if;
2331 if pay_gb_eoy_magtape.validate_input(p_employer_addr,'EDI_SURNAME') > 0 then
2332 p_error_msg2 := ':Employers Address contains illegal character(s) for ' ||
2333 'Tax Ref : ' || p_employer_ref;
2334 p_error_count := p_error_count + 1;
2335 end if;
2336 if pay_gb_eoy_magtape.validate_input(p_employer_addr,'EDI_SURNAME') > 0 then
2337 p_error_msg3 := ':Employers Name contains illegal character(s) for ' ||
2338 'Tax Ref : ' || p_employer_ref;
2339 p_error_count := p_error_count + 1;
2340 end if;
2341 */
2342
2343
2344 ref_id := to_number(nvl(substr(p_employer_ref,1,3),0));
2345 if ref_id < 1 or ref_id > 999 then
2349
2346 p_error_msg1 := ':HMRC Office number ' || substr(p_employer_ref,1,3) || ' must be between 001 to 999';
2347 p_error_count := p_error_count + 1;
2348 end if;
2350 /*BUG 7354812 */
2351 if p_submitter_ref IS NULL then
2352 p_error_msg2 := ':The P11D Submitter Number is missing';
2353 p_error_count := p_error_count + 1;
2354 end if;
2355 /*BUG 7354812*/
2356
2357 p_edi_rec1 := edi_nad1a || edi_party_qualifier_bg ||
2358 rpad(nvl(edi_addr1,' '), 36) || rpad(nvl(edi_addr2,' '), 36) || rpad(nvl(edi_addr3,' '), 36) ||
2359 rpad(nvl(edi_addr4,' '), 36) || rpad(' ', 35) || fnd_global.local_chr(10);
2360 /* bug 7594748 start */
2361 p_edi_rec2 := edi_nad1b || rpad(upper(substr(p_party,1,35)), 36) ||
2362 -- p_edi_rec2 := edi_nad1b || rpad(upper(p_party), 36) ||
2363 --bug 7594748 ends
2364 rpad(' ',9) || fnd_global.local_chr(10);
2365 p_edi_rec3 := edi_att1 || edi_att_qualifier1 || rpad(p_submitter_ref,35) || fnd_global.local_chr(10);
2366 p_edi_rec4 := edi_att1 || edi_att_qualifier7 || rpad(substr(p_employer_ref,5),35) || fnd_global.local_chr(10);
2367 p_edi_rec5 := edi_nad1a || edi_party_qualifier_tc || rpad(' ', 179) || fnd_global.local_chr(10);
2368 p_edi_rec6 := edi_att1 || edi_att_qualifier17 || rpad(substr(p_employer_ref,1,3),35) || fnd_global.local_chr(10);
2369 p_edi_rec7 := edi_dtm1 || edi_date_qualifier243 || rpad(p_message_date,36) ||
2370 edi_format_qualifier102 || fnd_global.local_chr(10);
2371 p_edi_rec8 := edi_dtm1 || edi_date_qualifier166 || rpad(p_tax_year,36) ||
2372 edi_format_qualifier602 || fnd_global.local_chr(10);
2373 p_edi_rec9 := edi_uns1 || fnd_global.local_chr(10);
2374
2375 return 0;
2376 end get_employer;
2377
2378 function get_employee(p_person_id in varchar2,
2379 p_pact_id in varchar2,
2380 p_error_count in out NoCopy number,
2381 p_error_msg1 out NoCopy varchar2,
2382 p_error_msg2 out NoCopy varchar2,
2383 p_error_msg3 out NoCopy varchar2,
2384 p_error_msg4 out NoCopy varchar2,
2385 p_error_msg5 out NoCopy varchar2,
2386 p_error_msg6 out NoCopy varchar2,
2387 p_error_msg7 out NoCopy varchar2,
2388 p_error_msg8 out NoCopy varchar2,
2389 p_edi_rec1 out NoCopy varchar2,
2390 p_edi_rec2 out NoCopy varchar2,
2391 p_edi_rec3 out NoCopy varchar2,
2392 p_edi_rec4 out NoCopy varchar2,
2393 p_edi_rec5 out NoCopy varchar2,
2394 p_edi_rec6 out NoCopy varchar2,
2395 p_edi_rec7 out NoCopy varchar2,
2396 p_edi_rec8 out NoCopy varchar2) return number
2397 is
2398 edi_att2 varchar2(6);
2399 edi_dtm2 varchar2(6); -- EOY 2008
2400 edi_att_qualifier3 varchar2(4);
2401 edi_att_qualifier11 varchar2(4);
2402 edi_att_qualifier19 varchar2(4);
2403 edi_att_qualifier9 varchar2(4); -- EOY 2008
2404 edi_dtm_qualifer329 varchar2(4); -- EOY 2008
2405 edi_dtm_qualifier102 varchar2(4); -- EOY 2008
2406 edi_nad2a varchar2(6);
2407 edi_nad2b varchar2(6);
2408 edi_party_qualifier_bv varchar2(4);
2409 l_pact_id number;
2410 l_assact_id number;
2411 l_ni_number varchar2(11);
2412 l_first_name varchar2(80);
2413 l_middle_name varchar2(80);
2414 l_last_name varchar2(80);
2415 l_dir_flag varchar2(2);
2416 l_employee_no varchar2(30);
2417 l_addr1 varchar2(255);
2418 l_addr2 varchar2(255);
2419 l_addr3 varchar2(255);
2420 l_addr4 varchar2(255);
2421 l_addr5 varchar2(255);
2422 l_date_of_birth varchar2(25); -- EOY 2008
2423 l_gender varchar2(1); -- EOY 2008
2424
2425 cursor get_assact_id(p_pact_id number) is
2426 select action_context_id
2427 from pay_assignment_actions paa,
2428 pay_action_information pai
2429 where paa.payroll_action_id = p_pact_id
2430 and pai.action_context_id = paa.assignment_action_id
2431 and pai.action_information_category = 'ADDRESS DETAILS'
2432 and pai.action_information14 = 'Employee Address'
2433 and pai.action_information1 = p_person_id
2434 and pai.action_context_type = 'AAP';
2435
2436 cursor get_details(p_act_id number) is
2437 select NVL(SUBSTR(UPPER(pai_gb.action_information8), 1, 36), ' '), -- last name
2438 NVL(SUBSTR(UPPER(pai_gb.action_information6), 1, 36), ' '), -- first name
2439 NVL(SUBSTR(UPPER(pai_gb.action_information7), 1, 36), ' '), -- middle name
2440 NVL(UPPER(pai_gb.action_information4), 'N'), -- dir flag
2441 NVL(UPPER(pai_gb.action_information11), ' '), -- emp no
2442 NVL(UPPER(pai_gb.action_information12), 'NONE'), -- NI
2443 NVL(UPPER(pai_person.action_information5), ' '), -- addr line 1
2447 NVL(UPPER(hl.meaning), ' '), -- addr line 5
2444 NVL(UPPER(pai_person.action_information6), ' '), -- addr line 2
2445 NVL(UPPER(pai_person.action_information7), ' '), -- addr line 3
2446 NVL(UPPER(pai_person.action_information8), ' '), -- addr line 4
2448 NVL(pai_gb.action_information15,' '), -- Date_of_birth EOY 2008
2449 NVL(UPPER(pai_gb.action_information17),' ') -- Gender EOY 2008
2450 from pay_action_information pai_gb,
2451 pay_action_information pai_person,
2452 hr_lookups hl
2453 where pai_person.action_context_id = p_act_id
2454 and pai_person.action_information_category = 'ADDRESS DETAILS'
2455 and pai_person.action_information14 = 'Employee Address'
2456 and pai_person.action_context_type = 'AAP'
2457 and pai_gb.action_context_id = pai_person.action_context_id
2458 and pai_gb.action_information_category = 'GB EMPLOYEE DETAILS'
2459 and pai_gb.action_context_type = 'AAP'
2460 and hl.lookup_type(+) = 'GB_COUNTY'
2461 and hl.lookup_code(+) = pai_person.action_information9;
2462 begin
2463 edi_att2 := rpad('ATT2',6);
2464 edi_att_qualifier3 := rpad('3',4);
2465 edi_att_qualifier11 := rpad('11',4);
2466 edi_att_qualifier19 := rpad('19',4);
2467 edi_nad2a := rpad('NAD2A',6);
2468 edi_nad2b := rpad('NAD2B',6);
2469 edi_party_qualifier_bv := rpad('BV',4);
2470 edi_dtm2 := rpad('DTM2',6); -- EOY 2008
2471 edi_att_qualifier9 := rpad('9',4); -- EOY 2008
2472 edi_dtm_qualifer329 := rpad('329',4); -- EOY 2008
2473 edi_dtm_qualifier102 := rpad('102',3); -- EOY 2008
2474
2475
2476 l_pact_id := p_pact_id;
2477
2478 open get_assact_id(l_pact_id);
2479 fetch get_assact_id into l_assact_id;
2480 close get_assact_id;
2481
2482 open get_details(l_assact_id);
2483 fetch get_details into l_last_name,
2484 l_first_name,
2485 l_middle_name,
2486 l_dir_flag,
2487 l_employee_no,
2488 l_ni_number,
2489 l_addr1,
2490 l_addr2,
2491 l_addr3,
2492 l_addr4,
2493 l_addr5,
2494 l_date_of_birth, -- EOY 2008
2495 l_gender; -- EOY 2008
2496 close get_details;
2497
2498 /* EOY 2008 starts */
2499 -- if substr(l_ni_number,1,2) = 'TN' then
2500 -- l_ni_number := 'NONE';
2501 -- end if;
2502 /* EOY 2008 ends */
2503
2504 /* EDI Validations are removed, this is now done at archive level*/
2505 /*
2506 if pay_gb_eoy_magtape.validate_input(l_last_name,'EDI_SURNAME') > 0 then
2507 p_error_msg1 := ':Illegal Character for last name for employee id ' || l_employee_no;
2508 p_error_count := p_error_count + 1;
2509 end if;
2510 if pay_gb_eoy_magtape.validate_input(l_first_name,'EDI_SURNAME') > 0 then
2511 p_error_msg2 := ':Illegal Character for first name for employee id ' || l_employee_no;
2512 p_error_count := p_error_count + 1;
2513 end if;
2514 if pay_gb_eoy_magtape.validate_input(l_middle_name,'EDI_SURNAME') > 0 then
2515 p_error_msg3 := ':Illegal Character for middle name for employee id ' || l_employee_no;
2516 p_error_count := p_error_count + 1;
2517 end if;
2518 if pay_gb_eoy_magtape.validate_input(l_addr1,'EDI_SURNAME') > 0 then
2519 p_error_msg4 := ':Employee id ' || l_employee_no || ' has illegal Character(s) in Address Line1' ;
2520 p_error_count := p_error_count + 1;
2521 end if;
2522 if pay_gb_eoy_magtape.validate_input(l_addr2,'EDI_SURNAME') > 0 then
2523 p_error_msg5 := ':Employee id ' || l_employee_no || ' has illegal Character(s) in Address Line3' ;
2524 p_error_count := p_error_count + 1;
2525 end if;
2526 if pay_gb_eoy_magtape.validate_input(l_addr3,'EDI_SURNAME') > 0 then
2527 p_error_msg6 := ':Employee id ' || l_employee_no || ' has illegal Character(s) in Address Line3' ;
2528 p_error_count := p_error_count + 1;
2529 end if;
2530 if pay_gb_eoy_magtape.validate_input(l_addr4,'EDI_SURNAME') > 0 then
2531 p_error_msg7 := ':Employee id ' || l_employee_no || ' has illegal Character(s) in Address Line4' ;
2532 p_error_count := p_error_count + 1;
2533 end if;
2534 if pay_gb_eoy_magtape.validate_input(l_addr5,'EDI_SURNAME') > 0 then
2535 p_error_msg8 := ':Employee id ' || l_employee_no || ' has illegal Character(s) in Address Line5' ;
2536 p_error_count := p_error_count + 1;
2537 end if;
2538 */
2539 p_edi_rec1 := edi_nad2a || edi_party_qualifier_bv ||
2540 rpad(upper(rpad(l_addr1,35)),36) || rpad(upper(rpad(l_addr2,35)),36) ||
2541 rpad(upper(rpad(l_addr3,35)),36) || rpad(upper(rpad(l_addr4,35)),36) ||
2542 rpad(upper(rpad(l_addr5,35)),35) || fnd_global.local_chr(10);
2543 p_edi_rec2 := edi_nad2b || rpad(l_last_name,36) ||
2544 rpad(l_first_name,36) || rpad(l_middle_name,36) ||
2548 end if;
2545 rpad(' ', 36) || rpad(' ',36) || rpad(' ',9) || fnd_global.local_chr(10);
2546 if l_ni_number <> 'NONE' then -- EOY 2008
2547 p_edi_rec3 := edi_att2 || edi_att_qualifier11 || rpad(l_ni_number,35) || fnd_global.local_chr(10);
2549 p_edi_rec4 := edi_att2 || edi_att_qualifier19 || rpad(l_employee_no,35) || fnd_global.local_chr(10);
2550 if substr(l_dir_flag,1,1) = 'Y' then
2551 p_edi_rec5 := edi_att2 || edi_att_qualifier3 || rpad(' ',35) || fnd_global.local_chr(10);
2552 end if;
2553
2554 /*EOY 2008 starts*/
2555 if l_gender <> ' ' then
2556 p_edi_rec6 := edi_att2 || edi_att_qualifier9 || rpad(l_gender,35)|| fnd_global.local_chr(10) ;
2557 end if ;
2558
2559 if l_date_of_birth <> ' ' then
2560 l_date_of_birth := to_char(to_date(l_date_of_birth,'YYYY/MM/DD HH24:MI:SS'), 'YYYYMMDD') ;
2561 p_edi_rec7 := edi_dtm2 || edi_dtm_qualifer329 || rpad(l_date_of_birth,36) || edi_dtm_qualifier102 || fnd_global.local_chr(10);
2562 end if ;
2563 /*EOY 2008 ends */
2564
2565 return 0;
2566 end get_employee;
2567
2568 function get_benefit(p_benefit_type in varchar2,
2569 p_person_id in varchar2,
2570 p_employer_ref in varchar2,
2571 p_tax_year in varchar2,
2572 p_benefit_count in varchar2,
2573 p_pact_id in varchar2,
2574 p_value1 in out NoCopy varchar2,
2575 p_value2 in out NoCopy varchar2,
2576 p_value3 in out NoCopy varchar2,
2577 p_value4 in out NoCopy varchar2,
2578 p_value5 in out NoCopy varchar2,
2579 p_value6 in out NoCopy varchar2,
2580 p_value7 in out NoCopy varchar2,
2581 p_value8 in out NoCopy varchar2,
2582 p_value9 in out NoCopy varchar2,
2583 p_value10 in out NoCopy varchar2,
2584 p_value11 in out NoCopy varchar2,
2585 p_value12 in out NoCopy varchar2,
2586 p_value13 in out NoCopy varchar2,
2587 p_value14 in out NoCopy varchar2,
2588 p_value15 in out NoCopy varchar2,
2589 p_value16 in out NoCopy varchar2,
2590 p_value17 in out NoCopy varchar2,
2591 p_value18 in out NoCopy varchar2,
2592 p_value19 in out NoCopy varchar2,
2593 p_value20 in out NoCopy varchar2,
2594 p_value21 in out NoCopy varchar2,
2595 p_value22 in out NoCopy varchar2,
2596 p_value23 in out NoCopy varchar2,
2597 p_value24 in out NoCopy varchar2,
2598 p_value25 in out NoCopy varchar2,
2599 p_value26 in out NoCopy varchar2,
2600 p_value27 in out NoCopy varchar2,
2601 p_value28 in out NoCopy varchar2,
2602 p_value29 in out NoCopy varchar2,
2603 p_value30 in out NoCopy varchar2,
2604 p_edi_rec1 out NoCopy varchar2,
2605 p_edi_rec2 out NoCopy varchar2,
2606 p_edi_rec3 out NoCopy varchar2,
2607 p_edi_rec4 out NoCopy varchar2,
2608 p_edi_rec5 out NoCopy varchar2,
2609 p_edi_rec6 out NoCopy varchar2,
2610 p_edi_rec7 out NoCopy varchar2,
2611 p_edi_rec8 out NoCopy varchar2,
2612 p_edi_rec9 out NoCopy varchar2,
2613 p_edi_rec10 out NoCopy varchar2,
2614 p_edi_rec11 out NoCopy varchar2,
2615 p_edi_rec12 out NoCopy varchar2,
2616 p_edi_rec13 out NoCopy varchar2,
2617 p_edi_rec14 out NoCopy varchar2,
2618 p_edi_rec15 out NoCopy varchar2,
2619 p_edi_rec16 out NoCopy varchar2,
2620 p_edi_rec17 out NoCopy varchar2,
2621 p_edi_rec18 out NoCopy varchar2,
2622 p_edi_rec19 out NoCopy varchar2,
2623 p_edi_rec20 out NoCopy varchar2,
2624 p_edi_rec21 out NoCopy varchar2,
2625 p_edi_rec22 out NoCopy varchar2,
2626 p_edi_rec23 out NoCopy varchar2,
2627 p_edi_rec24 out NoCopy varchar2,
2628 p_edi_rec25 out NoCopy varchar2,
2629 p_edi_rec26 out NoCopy varchar2,
2630 p_edi_rec27 out NoCopy varchar2,
2631 p_edi_rec28 out NoCopy varchar2,
2632 p_edi_rec29 out NoCopy varchar2,
2633 p_edi_rec30 out NoCopy varchar2) return number
2634 is
2635 begin
2636 hr_utility.trace('\nxxxx get_benefit called - ' ||p_benefit_type) ;
2637 if p_benefit_type = 'ASSETS TRANSFERRED' then
2638 hr_utility.trace('Assets Transferred');
2639 get_asset_transferred(p_person_id => p_person_id,
2640 p_emp_ref => p_employer_ref,
2641 p_pact_id => p_pact_id,
2642 p_edi_rec1 => p_edi_rec1,
2646 p_edi_rec5 => p_edi_rec5,
2643 p_edi_rec2 => p_edi_rec2,
2644 p_edi_rec3 => p_edi_rec3,
2645 p_edi_rec4 => p_edi_rec4,
2647 p_edi_rec6 => p_edi_rec6,
2648 p_edi_rec7 => p_edi_rec7,
2649 p_edi_rec8 => p_edi_rec8,
2650 p_edi_rec9 => p_edi_rec9,
2651 p_edi_rec10 => p_edi_rec10,
2652 p_edi_rec11 => p_edi_rec11,
2653 p_edi_rec12 => p_edi_rec12,
2654 p_edi_rec13 => p_edi_rec13,
2655 p_edi_rec14 => p_edi_rec14,
2656 p_edi_rec15 => p_edi_rec15,
2657 p_edi_rec16 => p_edi_rec16,
2658 p_edi_rec17 => p_edi_rec17,
2659 p_edi_rec18 => p_edi_rec18,
2660 p_edi_rec19 => p_edi_rec19,
2661 p_edi_rec20 => p_edi_rec20,
2662 p_edi_rec21 => p_edi_rec21,
2663 p_edi_rec22 => p_edi_rec22);
2664 return 0;
2665 end if;
2666 if p_benefit_type = 'PAYMENTS MADE FOR EMP' then
2667 hr_utility.trace('Payment made for employee');
2668 get_payments_for_emp(p_person_id => p_person_id,
2669 p_emp_ref => p_employer_ref,
2670 p_pact_id => p_pact_id,
2671 p_edi_rec1 => p_edi_rec1,
2672 p_edi_rec2 => p_edi_rec2,
2673 p_edi_rec3 => p_edi_rec3,
2674 p_edi_rec4 => p_edi_rec4,
2675 p_edi_rec5 => p_edi_rec5,
2676 p_edi_rec6 => p_edi_rec6,
2677 p_edi_rec7 => p_edi_rec7,
2678 p_edi_rec8 => p_edi_rec8,
2679 p_edi_rec9 => p_edi_rec9,
2680 p_edi_rec10 => p_edi_rec10,
2681 p_edi_rec11 => p_edi_rec11,
2682 p_edi_rec12 => p_edi_rec12,
2683 p_edi_rec13 => p_edi_rec13,
2684 p_edi_rec14 => p_edi_rec14,
2685 p_edi_rec15 => p_edi_rec15,
2686 p_edi_rec16 => p_edi_rec16,
2687 p_edi_rec17 => p_edi_rec17);
2688 return 0;
2689 end if;
2690 if p_benefit_type = 'VOUCHERS OR CREDIT CARDS' then
2691 hr_utility.trace('Voucher');
2692 get_voucher_n_creditcard(p_person_id => p_person_id,
2693 p_emp_ref => p_employer_ref,
2694 p_pact_id => p_pact_id,
2695 p_edi_rec1 => p_edi_rec1,
2696 p_edi_rec2 => p_edi_rec2,
2697 p_edi_rec3 => p_edi_rec3,
2698 p_edi_rec4 => p_edi_rec4,
2699 p_edi_rec5 => p_edi_rec5,
2700 p_edi_rec6 => p_edi_rec6,
2701 p_edi_rec7 => p_edi_rec7);
2702 return 0;
2703 end if;
2704 if p_benefit_type = 'LIVING ACCOMMODATION' then
2705 hr_utility.trace('Living accommodation');
2706 get_living_accommodation(p_person_id => p_person_id,
2707 p_emp_ref => p_employer_ref,
2708 p_pact_id => p_pact_id,
2709 p_edi_rec1 => p_edi_rec1,
2710 p_edi_rec2 => p_edi_rec2,
2711 p_edi_rec3 => p_edi_rec3);
2712 return 0;
2713 end if;
2714 if p_benefit_type = 'MILEAGE ALLOWANCE AND PPAYMENT' then
2715 hr_utility.trace('Mileage allowance and prepayment');
2716 get_mileage_allowance(p_person_id => p_person_id,
2717 p_emp_ref => p_employer_ref,
2718 p_pact_id => p_pact_id,
2719 p_edi_rec1 => p_edi_rec1,
2720 p_edi_rec2 => p_edi_rec2,
2721 p_edi_rec3 => p_edi_rec3);
2722 return 0;
2723 end if;
2724 if p_benefit_type = 'CAR AND CAR FUEL 2003_04' then
2725 hr_utility.trace('Car');
2726 get_car_or_fuel(p_person_id => p_person_id,
2727 p_emp_ref => p_employer_ref,
2728 p_pact_id => p_pact_id,
2729 p_tax_year => p_tax_year,
2730 p_ben_count => p_benefit_count,
2731 p_value1 => p_value1,
2732 p_value2 => p_value2,
2733 p_edi_rec1 => p_edi_rec1,
2734 p_edi_rec2 => p_edi_rec2,
2735 p_edi_rec3 => p_edi_rec3,
2736 p_edi_rec4 => p_edi_rec4,
2737 p_edi_rec5 => p_edi_rec5,
2738 p_edi_rec6 => p_edi_rec6,
2739 p_edi_rec7 => p_edi_rec7,
2740 p_edi_rec8 => p_edi_rec8,
2744 p_edi_rec12 => p_edi_rec12,
2741 p_edi_rec9 => p_edi_rec9,
2742 p_edi_rec10 => p_edi_rec10,
2743 p_edi_rec11 => p_edi_rec11,
2745 p_edi_rec13 => p_edi_rec13,
2746 p_edi_rec14 => p_edi_rec14,
2747 p_edi_rec15 => p_edi_rec15,
2748 p_edi_rec16 => p_edi_rec16,
2749 p_edi_rec17 => p_edi_rec17,
2750 p_edi_rec18 => p_edi_rec18,
2751 p_edi_rec19 => p_edi_rec19,
2752 p_edi_rec20 => p_edi_rec20,
2753 p_edi_rec21 => p_edi_rec21,
2754 p_edi_rec22 => p_edi_rec22,
2755 p_edi_rec23 => p_edi_rec23);
2756 return 0;
2757 end if;
2758 if p_benefit_type = 'VANS 2007' then -- EOY 2008
2759 hr_utility.trace('\nxxxx Vans called - ' ||p_benefit_type) ;
2760 hr_utility.trace('Vans');
2761 -- EOY 2008. Added 2 extra parameters p_edi_rec4 and p_edi_rec5
2762 get_vans(p_person_id => p_person_id,
2763 p_emp_ref => p_employer_ref,
2764 p_pact_id => p_pact_id,
2765 p_edi_rec1 => p_edi_rec1,
2766 p_edi_rec2 => p_edi_rec2,
2767 p_edi_rec3 => p_edi_rec3,
2768 p_edi_rec4 => p_edi_rec4,
2769 p_edi_rec5 => p_edi_rec5);
2770 return 0;
2771 end if;
2772 if p_benefit_type = 'INT FREE AND LOW INT LOANS' then
2773 hr_utility.trace('Loans');
2774 get_low_int_loan(p_person_id => p_person_id,
2775 p_emp_ref => p_employer_ref,
2776 p_pact_id => p_pact_id,
2777 p_ben_count => p_benefit_count,
2778 p_tax_year => p_tax_year,
2779 p_value1 => p_value1,
2780 p_edi_rec1 => p_edi_rec1,
2781 p_edi_rec2 => p_edi_rec2,
2782 p_edi_rec3 => p_edi_rec3,
2783 p_edi_rec4 => p_edi_rec4,
2784 p_edi_rec5 => p_edi_rec5,
2785 p_edi_rec6 => p_edi_rec6,
2786 p_edi_rec7 => p_edi_rec7,
2787 p_edi_rec8 => p_edi_rec8,
2788 p_edi_rec9 => p_edi_rec9);
2789 return 0;
2790 end if;
2791 if p_benefit_type = 'PVT MED TREATMENT OR INSURANCE' then
2792 hr_utility.trace('Insurance');
2793 get_pvt_med_or_ins(p_person_id => p_person_id,
2794 p_emp_ref => p_employer_ref,
2795 p_pact_id => p_pact_id,
2796 p_edi_rec1 => p_edi_rec1,
2797 p_edi_rec2 => p_edi_rec2,
2798 p_edi_rec3 => p_edi_rec3,
2799 p_edi_rec4 => p_edi_rec4,
2800 p_edi_rec5 => p_edi_rec5,
2801 p_edi_rec6 => p_edi_rec6,
2802 p_edi_rec7 => p_edi_rec7);
2803 return 0;
2804 end if;
2805 if p_benefit_type = 'RELOCATION EXPENSES' then
2806 hr_utility.trace('Relocation expenses');
2807 get_relocation(p_person_id => p_person_id,
2808 p_emp_ref => p_employer_ref,
2809 p_pact_id => p_pact_id,
2810 p_edi_rec1 => p_edi_rec1,
2811 p_edi_rec2 => p_edi_rec2,
2812 p_edi_rec3 => p_edi_rec3);
2813 return 0;
2814 end if;
2815 if p_benefit_type = 'SERVICES SUPPLIED' then
2816 hr_utility.trace('Service supplied');
2817 get_service_supplied(p_person_id => p_person_id,
2818 p_emp_ref => p_employer_ref,
2819 p_pact_id => p_pact_id,
2820 p_edi_rec1 => p_edi_rec1,
2821 p_edi_rec2 => p_edi_rec2,
2822 p_edi_rec3 => p_edi_rec3,
2823 p_edi_rec4 => p_edi_rec4,
2824 p_edi_rec5 => p_edi_rec5,
2825 p_edi_rec6 => p_edi_rec6,
2826 p_edi_rec7 => p_edi_rec7);
2827 return 0;
2828 end if;
2829 if p_benefit_type = 'ASSETS AT EMP DISPOSAL' then
2830 hr_utility.trace('Assets at employee disposal');
2831 get_assets_at_emp(p_person_id => p_person_id,
2832 p_emp_ref => p_employer_ref,
2833 p_pact_id => p_pact_id,
2834 p_edi_rec1 => p_edi_rec1,
2835 p_edi_rec2 => p_edi_rec2,
2836 p_edi_rec3 => p_edi_rec3,
2837 p_edi_rec4 => p_edi_rec4,
2838 p_edi_rec5 => p_edi_rec5,
2839 p_edi_rec6 => p_edi_rec6,
2840 p_edi_rec7 => p_edi_rec7,
2841 p_edi_rec8 => p_edi_rec8,
2842 p_edi_rec9 => p_edi_rec9,
2843 p_edi_rec10 => p_edi_rec10,
2844 p_edi_rec11 => p_edi_rec11,
2845 p_edi_rec12 => p_edi_rec12,
2846 p_edi_rec13 => p_edi_rec13,
2847 p_edi_rec14 => p_edi_rec14,
2848 p_edi_rec15 => p_edi_rec15,
2849 p_edi_rec16 => p_edi_rec16);
2850 return 0;
2851 end if;
2852 if p_benefit_type = 'OTHER ITEMS' then
2853 hr_utility.trace('Other Items');
2854 get_other_items(p_person_id => p_person_id,
2855 p_emp_ref => p_employer_ref,
2856 p_pact_id => p_pact_id,
2857 p_edi_rec1 => p_edi_rec1,
2858 p_edi_rec2 => p_edi_rec2,
2859 p_edi_rec3 => p_edi_rec3,
2860 p_edi_rec4 => p_edi_rec4,
2861 p_edi_rec5 => p_edi_rec5,
2862 p_edi_rec6 => p_edi_rec6,
2863 p_edi_rec7 => p_edi_rec7,
2864 p_edi_rec8 => p_edi_rec8,
2865 p_edi_rec9 => p_edi_rec9,
2866 p_edi_rec10 => p_edi_rec10,
2867 p_edi_rec11 => p_edi_rec11,
2868 p_edi_rec12 => p_edi_rec12,
2869 p_edi_rec13 => p_edi_rec13,
2870 p_edi_rec14 => p_edi_rec14,
2871 p_edi_rec15 => p_edi_rec15,
2872 p_edi_rec16 => p_edi_rec16,
2873 p_edi_rec17 => p_edi_rec17,
2874 p_edi_rec18 => p_edi_rec18,
2875 p_edi_rec19 => p_edi_rec19,
2876 p_edi_rec20 => p_edi_rec20,
2877 p_edi_rec21 => p_edi_rec21,
2878 p_edi_rec22 => p_edi_rec22);
2879 return 0;
2880 end if;
2881 if p_benefit_type = 'EXPENSES PAYMENTS' then
2882 hr_utility.trace('Expenses Payments');
2883 get_exp_payment(p_person_id => p_person_id,
2884 p_emp_ref => p_employer_ref,
2885 p_pact_id => p_pact_id,
2886 p_edi_rec1 => p_edi_rec1,
2887 p_edi_rec2 => p_edi_rec2,
2888 p_edi_rec3 => p_edi_rec3,
2889 p_edi_rec4 => p_edi_rec4,
2890 p_edi_rec5 => p_edi_rec5,
2891 p_edi_rec6 => p_edi_rec6,
2892 p_edi_rec7 => p_edi_rec7,
2893 p_edi_rec8 => p_edi_rec8,
2894 p_edi_rec9 => p_edi_rec9,
2895 p_edi_rec10 => p_edi_rec10,
2896 p_edi_rec11 => p_edi_rec11,
2897 p_edi_rec12 => p_edi_rec12,
2898 p_edi_rec13 => p_edi_rec13,
2899 p_edi_rec14 => p_edi_rec14,
2900 p_edi_rec15 => p_edi_rec15,
2901 p_edi_rec16 => p_edi_rec16,
2902 p_edi_rec17 => p_edi_rec17,
2903 p_edi_rec18 => p_edi_rec18,
2904 p_edi_rec19 => p_edi_rec19,
2905 p_edi_rec20 => p_edi_rec20);
2906 return 0;
2907 end if;
2908 if p_benefit_type = 'MARORS' then
2909 hr_utility.trace('Marrors');
2910 get_marors(p_person_id => p_person_id,
2911 p_emp_ref => p_employer_ref,
2912 p_pact_id => p_pact_id,
2913 p_edi_rec1 => p_edi_rec1,
2914 p_edi_rec2 => p_edi_rec2,
2915 p_edi_rec3 => p_edi_rec3);
2916 return 0;
2917 end if;
2918 return 0;
2919 end get_benefit;
2920
2921 function get_summary(p_benefit_type in varchar2,
2922 p_tax_year in varchar2,
2923 p_value1 in varchar2,
2924 p_value2 in varchar2,
2925 p_value3 in varchar2,
2926 p_value4 in varchar2,
2927 p_value5 in varchar2,
2928 p_value6 in varchar2,
2929 p_value7 in varchar2,
2930 p_value8 in varchar2,
2931 p_value9 in varchar2,
2932 p_value10 in varchar2,
2933 p_value11 in varchar2,
2934 p_value12 in varchar2,
2935 p_value13 in varchar2,
2936 p_value14 in varchar2,
2937 p_value15 in varchar2,
2938 p_value16 in varchar2,
2939 p_value17 in varchar2,
2940 p_value18 in varchar2,
2941 p_value19 in varchar2,
2942 p_value20 in varchar2,
2943 p_value21 in varchar2,
2944 p_value22 in varchar2,
2945 p_value23 in varchar2,
2946 p_value24 in varchar2,
2947 p_value25 in varchar2,
2948 p_value26 in varchar2,
2949 p_value27 in varchar2,
2950 p_value28 in varchar2,
2951 p_value29 in varchar2,
2952 p_value30 in varchar2,
2953 p_edi_rec1 out NoCopy varchar2,
2954 p_edi_rec2 out NoCopy varchar2,
2955 p_edi_rec3 out NoCopy varchar2,
2956 p_edi_rec4 out NoCopy varchar2,
2957 p_edi_rec5 out NoCopy varchar2,
2958 p_edi_rec6 out NoCopy varchar2,
2959 p_edi_rec7 out NoCopy varchar2,
2960 p_edi_rec8 out NoCopy varchar2,
2961 p_edi_rec9 out NoCopy varchar2,
2962 p_edi_rec10 out NoCopy varchar2,
2963 p_edi_rec11 out NoCopy varchar2,
2964 p_edi_rec12 out NoCopy varchar2,
2965 p_edi_rec13 out NoCopy varchar2,
2966 p_edi_rec14 out NoCopy varchar2,
2967 p_edi_rec15 out NoCopy varchar2,
2968 p_edi_rec16 out NoCopy varchar2,
2969 p_edi_rec17 out NoCopy varchar2,
2970 p_edi_rec18 out NoCopy varchar2,
2971 p_edi_rec19 out NoCopy varchar2,
2972 p_edi_rec20 out NoCopy varchar2,
2973 p_edi_rec21 out NoCopy varchar2,
2974 p_edi_rec22 out NoCopy varchar2,
2975 p_edi_rec23 out NoCopy varchar2,
2976 p_edi_rec24 out NoCopy varchar2,
2977 p_edi_rec25 out NoCopy varchar2,
2978 p_edi_rec26 out NoCopy varchar2,
2979 p_edi_rec27 out NoCopy varchar2,
2980 p_edi_rec28 out NoCopy varchar2,
2981 p_edi_rec29 out NoCopy varchar2,
2985 if p_benefit_type = 'CAR AND CAR FUEL 2003_04' then
2982 p_edi_rec30 out NoCopy varchar2) return number
2983 is
2984 begin
2986 get_car_summary(p_value1 => p_value1,
2987 p_value2 => p_value2,
2988 p_edi_rec1 => p_edi_rec1,
2989 p_edi_rec2 => p_edi_rec2,
2990 p_edi_rec3 => p_edi_rec3,
2991 p_edi_rec4 => p_edi_rec4,
2992 p_edi_rec5 => p_edi_rec5);
2993 return 0;
2994 end if;
2995 return 0;
2996 end get_summary;
2997
2998 function get_footer(p_record_count in varchar2,
2999 p_error_count in varchar2,
3000 p_missing_val in varchar2,
3001 p_error_msg1 out NoCopy varchar2,
3002 p_error_msg2 out NoCopy varchar2,
3003 p_edi_rec1 out NoCopy varchar2,
3004 p_edi_rec2 out NoCopy varchar2,
3005 p_edi_rec3 out NoCopy varchar2) return number
3006 is
3007 edi_cnt1 varchar2(6);
3008 edi_qty1 varchar2(6);
3009 edi_uns2 varchar2(5);
3010 edi_qty_qualifierI varchar2(4);
3011 begin
3012 edi_cnt1 := rpad('CNT1',6);
3013 edi_qty1 := rpad('QTY1',6);
3014 edi_uns2 := rpad('UNS2',5);
3015 edi_qty_qualifierI := rpad('I',4);
3016
3017 if to_number(p_error_count) > 0 then
3018 p_error_msg1 := ':Failing process as invalid characters found in ' ||
3019 p_error_count || ' field(s).';
3020 end if;
3021
3022 if to_number(p_missing_val) > 0 then
3023 p_error_msg2 := ':Failing process as there are missing values for ' ||
3024 p_missing_val || ' mandatory field(s).';
3025 end if;
3026
3027 p_edi_rec1 := edi_uns2 || fnd_global.local_chr(10);
3028 p_edi_rec2 := edi_qty1 || edi_qty_qualifierI ||
3029 lpad(p_record_count,15,'0') || fnd_global.local_chr(10);
3030 p_edi_rec3 := edi_cnt1 || lpad(p_record_count,18,'0') || fnd_global.local_chr(10);
3031 return 0;
3032 end;
3033
3034 function get_benefit_name(p_benefit_type in varchar2) return varchar2
3035 is
3036 l_benefit_name varchar2(80);
3037 begin
3038 hr_utility.trace('\nxxxx Get_benefit name called - ' ||p_benefit_type) ;
3039 select decode(p_benefit_type,
3040 'A', 'ASSETS TRANSFERRED',
3041 'B', 'PAYMENTS MADE FOR EMP',
3042 'C', 'VOUCHERS OR CREDIT CARDS',
3043 'D', 'LIVING ACCOMMODATION',
3044 'E', 'MILEAGE ALLOWANCE AND PPAYMENT',
3045 'F', 'CAR AND CAR FUEL 2003_04',
3046 'G', 'VANS 2007', -- EOY 2008
3047 'H', 'INT FREE AND LOW INT LOANS',
3048 'I', 'PVT MED TREATMENT OR INSURANCE',
3049 'J', 'RELOCATION EXPENSES',
3050 'K', 'SERVICES SUPPLIED',
3051 'L', 'ASSETS AT EMP DISPOSAL',
3052 'M', 'OTHER ITEMS', -- 'OTHER ITEMS NON 1A'
3053 'N', 'EXPENSES PAYMENTS',
3054 'U', 'MARORS')
3055 into l_benefit_name
3056 from dual;
3057
3058 return l_benefit_name;
3059 end get_benefit_name;
3060
3061 function count_occurrence(p_benefit_type in varchar2,
3062 p_person_id in varchar2,
3063 p_employer_ref in varchar2,
3064 p_pact_id in varchar2) return number
3065 is
3066 l_benefit_type varchar(80);
3067 l_pact_id number;
3068 l_count number;
3069 l_count1 number;
3070
3071 cursor count_occurrence(p_pact_id number,
3072 p_benefit_type varchar2) is
3073 select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
3074 use_index(pai_person,pay_action_information_n2)
3075 use_index(pai,pay_action_information_n2) */
3076 count(*)
3077 from per_all_assignments_f paf,
3078 pay_assignment_actions paa,
3079 pay_action_information pai,
3080 pay_action_information pai_person
3081 where paf.person_id = p_person_id
3082 and paf.effective_end_date = (select max(paf2.effective_end_date)
3083 from per_all_assignments_f paf2
3084 where paf2.assignment_id = paf.assignment_id
3085 and paf2.person_id = p_person_id)
3086 and paf.assignment_id = paa.assignment_id
3087 and paa.payroll_action_id = p_pact_id
3088 and pai.action_context_id = paa.assignment_action_id
3089 and pai.action_context_type = 'AAP'
3090 and pai.action_information_category = p_benefit_type
3091 and pai_person.action_context_id = paa.assignment_action_id
3092 and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
3093 and upper(pai_person.action_information13) = upper(p_employer_ref)
3094 and pai_person.action_context_type = 'AAP';
3095 begin
3096 l_pact_id := p_pact_id;
3097
3098 open count_occurrence(l_pact_id, p_benefit_type);
3099 fetch count_occurrence into l_count;
3100 close count_occurrence;
3101 /** If Other Item then we also need to check Non 1A Other item as well */
3102 if p_benefit_type = 'OTHER ITEMS' then
3103 open count_occurrence(l_pact_id,'OTHER ITEMS NON 1A');
3104 fetch count_occurrence into l_count1;
3105 close count_occurrence;
3109 end count_occurrence;
3106 l_count := l_count + l_count1;
3107 end if;
3108 return l_count;
3110
3111 function check_occurrence(p_benefit_type in varchar2) return varchar2
3112 is
3113 multiple constant varchar2(6) := 'FH';
3114 single constant varchar2(15) := 'ABCDEGIJKLMNU';
3115 ret varchar2(10);
3116 begin
3117 ret := translate(p_benefit_type, single || multiple, single);
3118
3119 if ret is not null then
3120 ret := 'S';
3121 else
3122 ret := 'M';
3123 end if;
3124
3125 return ret;
3126 end check_occurrence;
3127
3128 function fetch_total_benefit(p_assact_id in number,
3129 p_employer_ref in varchar2) return number
3130 is
3131 l_total number;
3132 l_marror number;
3133
3134 cursor csr_marror is
3135 select /*+ ORDERED use_nl(paa,pai,pai_person)
3136 use_index(pai_person,pay_action_information_n2)
3137 use_index(pai,pay_action_information_n2) */
3138 sum(pai.action_information7)
3139 from pay_assignment_actions paa,
3140 pay_action_information pai,
3141 pay_action_information pai_person
3142 where paa.assignment_action_id = p_assact_id
3143 and pai.action_context_id = paa.assignment_action_id
3144 and pai.action_context_type = 'AAP'
3145 and pai.action_information_category = 'MARORS'
3146 and pai_person.action_context_id = paa.assignment_action_id
3147 and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
3148 and pai_person.action_context_type = 'AAP'
3149 and upper(pai_person.action_information13) = upper(p_employer_ref);
3150
3151 /* modified cursor csr_benefit for bug 7231911 */
3152 /* cursor csr_benefit is
3153 select /*+ ORDERED use_nl(paa,pai,pai_a,pai_person)
3154 use_index(pai_person,pay_action_information_n2)
3155 use_index(pai,pay_action_information_n2)
3156 use_index(pai_a,pay_action_information_n2)*/
3157 /* sum(decode(pai.action_information_category,
3158 'ASSETS TRANSFERRED', pai.action_information9,
3159 'PAYMENTS MADE FOR EMP', pai.action_information7,
3160 'VOUCHERS OR CREDIT CARDS', pai.action_information11,
3161 'LIVING ACCOMMODATION', pai.action_information10 + pai.action_information17,
3162 'MILEAGE ALLOWANCE AND PPAYMENT', pai_a.action_information12,
3163 'CAR AND CAR FUEL 2003_04', pai.action_information10 + pai.action_information11,
3164 'VANS 2002_03',pai.action_information15,
3165 'VANS 2005', pai.action_information15,
3166 'VANS 2007', pai.action_information14, -- EOY 2008
3167 'INT FREE AND LOW INT LOANS', pai.action_information11,
3168 'PVT MED TREATMENT OR INSURANCE', pai.action_information7,
3169 'RELOCATION EXPENSES', pai.action_information5,
3170 'SERVICES SUPPLIED', pai.action_information7,
3171 'ASSETS AT EMP DISPOSAL', pai.action_information9,
3172 'OTHER ITEMS', pai.action_information9,
3173 'OTHER ITEMS NON 1A', pai.action_information9,
3174 'EXPENSES PAYMENTS', pai.action_information8)) total
3175 from pay_assignment_actions paa,
3176 pay_action_information pai,
3177 pay_action_information pai_a,
3178 pay_action_information pai_person
3179 where paa.assignment_action_id = p_assact_id
3180 and pai.action_context_id = paa.assignment_action_id
3181 and pai.action_context_type = 'AAP'
3182 and pai.action_information_category = pai.action_information_category
3183 and pai_person.action_context_id = paa.assignment_action_id
3184 and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
3185 and pai_person.action_context_type = 'AAP'
3186 and upper(pai_person.action_information13) = upper(p_employer_ref)
3187 and pai_a.action_context_id = paa.assignment_action_id
3188 and pai_a.action_context_type = 'AAP'
3189 and pai_a.action_information_category = 'GB P11D ASSIGNMENT RESULTA'; */
3190 cursor csr_benefit is
3191 select /*+ ORDERED use_nl(paa,pai,pai_a,pai_person)
3192 use_index(pai_person,pay_action_information_n2)
3193 use_index(pai,pay_action_information_n2)
3194 use_index(pai_a,pay_action_information_n2)*/
3195 decode(pai.action_information_category,
3196 'ASSETS TRANSFERRED', pai.action_information9,
3197 'PAYMENTS MADE FOR EMP', pai.action_information7,
3198 'VOUCHERS OR CREDIT CARDS', pai.action_information11,
3199 'LIVING ACCOMMODATION', pai.action_information10 + pai.action_information17,
3200 'MILEAGE ALLOWANCE AND PPAYMENT', pai_a.action_information12,
3201 'CAR AND CAR FUEL 2003_04', pai.action_information10 + pai.action_information11,
3202 'VANS 2002_03',pai.action_information15,
3203 'VANS 2005', pai.action_information15,
3204 'VANS 2007', pai.action_information14, -- EOY 2008
3205 'INT FREE AND LOW INT LOANS', pai.action_information11,
3206 'PVT MED TREATMENT OR INSURANCE', pai.action_information7,
3207 'RELOCATION EXPENSES', pai.action_information5,
3208 'SERVICES SUPPLIED', pai.action_information7,
3209 'ASSETS AT EMP DISPOSAL', pai.action_information9,
3210 'OTHER ITEMS', pai.action_information9,
3211 'OTHER ITEMS NON 1A', pai.action_information9,
3212 'EXPENSES PAYMENTS', pai.action_information8)total
3213 from pay_assignment_actions paa,
3214 pay_action_information pai,
3215 pay_action_information pai_a,
3216 pay_action_information pai_person
3217 where paa.assignment_action_id = p_assact_id
3218 and pai.action_context_id = paa.assignment_action_id
3219 and pai.action_context_type = 'AAP'
3220 and pai.action_information_category = pai.action_information_category
3221 and pai_person.action_context_id = paa.assignment_action_id
3222 and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
3223 and pai_person.action_context_type = 'AAP'
3224 and upper(pai_person.action_information13) = upper(p_employer_ref)
3225 and pai_a.action_context_id = paa.assignment_action_id
3226 and pai_a.action_context_type = 'AAP'
3227 and pai_a.action_information_category = 'GB P11D ASSIGNMENT RESULTA';
3228
3229 begin
3230 /* modified the fetching logic of csr_benefit for bug 7231911 */
3231 /* open csr_benefit;
3232 fetch csr_benefit into l_total;
3233 close csr_benefit; */
3234 for v_csr_benefit in csr_benefit
3235 loop
3236
3237 if v_csr_benefit.total >= 1 then
3238 return 1;
3239 end if;
3240 end loop;
3241
3242 open csr_marror;
3243 fetch csr_marror into l_marror;
3244 close csr_marror;
3245 /* commented for bug 7231911 */
3246 /* if l_total > 0 then
3247 return 1;
3248 end if; */
3249
3250 if l_marror <> 0 then
3251 return 1;
3252 end if;
3253 return 0;
3254 end;
3255 /******************** END PUBLIC FUNCTIONS/PROCEDURES ********************/
3256
3257 END PAY_GB_P11D_EDI_2008;