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