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