DBA Data[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;