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