eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PAY_IE_P35_XML_PKG

Source


1 PACKAGE BODY PAY_IE_P35_XML_PKG AS
2 /* $Header: pyiep35p.pkb 120.11.12010000.1 2008/07/27 22:50:15 appldev ship $ */
3 
4 -------------------------------------------------------------------------------
5 -- get_IANA_charset
6 -------------------------------------------------------------------------------
7 FUNCTION get_IANA_charset RETURN VARCHAR2 IS
8     CURSOR csr_get_iana_charset IS
9         SELECT tag
10           FROM fnd_lookup_values
11          WHERE lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
12            AND lookup_code = SUBSTR(USERENV('LANGUAGE'),
13                                     INSTR(USERENV('LANGUAGE'), '.') + 1)
14            AND language = 'US';
15 
16     lv_iana_charset fnd_lookup_values.tag%type;
17 BEGIN
18     OPEN csr_get_iana_charset;
19         FETCH csr_get_iana_charset INTO lv_iana_charset;
20     CLOSE csr_get_iana_charset;
21 
22     hr_utility.trace('IANA Charset = '||lv_iana_charset);
23     RETURN (lv_iana_charset);
24 END get_IANA_charset;
25 --
26 	-------------------------------------------------------------------------------
27 	-- WRITETOCLOB
28 	--------------------------------------------------------------------------------
29 	PROCEDURE WritetoCLOB (p_xfdf_string out nocopy clob)
30 	IS
31 	  l_str varchar2(240);
32 	  l_str1 varchar2(6000);
33 	BEGIN
34 	-- bug 5852148
35        --l_str := '<?xml version="1.0" encoding="UTF-8"?> <P35LFile></P35LFile>';
36          l_str := '<?xml version="1.0" encoding="' || get_IANA_charset ||'"?> <P35LFile></P35LFile>';
37 		dbms_lob.createtemporary(p_xfdf_string,FALSE,DBMS_LOB.CALL);
38 		dbms_lob.open(p_xfdf_string,dbms_lob.lob_readwrite);
39   	    hr_utility.set_location('TableCnt' || to_char(vXMLTable.count),13);
40 	    IF vXMLTable.count > 0 THEN
41 		FOR ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST LOOP
42                   -- Bug 4705094
43 		l_str1 := vXMLTable(ctr_table).xmlString;
44 		dbms_lob.writeAppend( p_xfdf_string, length(l_str1), l_str1);
45 		hr_utility.set_location(to_char(ctr_table),15);
46 		END LOOP;
47 		ELSE
48 		  l_str1 := l_str;
49 		  dbms_lob.writeAppend( p_xfdf_string, length(l_str1), l_str1 );
50 	   END IF;
51 	END WritetoCLOB;
52 	--------------------------------------------------------------------------------
53 	-- POPULATE_P35_REPORT
54 	--------------------------------------------------------------------------------
55 	PROCEDURE populate_p35_rep
56 	  (p_bg_id IN NUMBER
57 	   ,p_emp_no IN VARCHAR2
58 	   ,p_payroll        IN NUMBER
59    	   ,p_assignment_set        IN NUMBER
60 	   ,p_end_date IN VARCHAR2
61 	   ,p_weeks IN VARCHAR2
62    	   ,p_template_name IN VARCHAR2
63        ,p_xml         OUT NOCOPY CLOB
64 	   )IS
65 	BEGIN
66 	--hr_utility.TRACE_ON( null, 'IEP35XML' );
67 	  populate_plsql_table(p_bg_id
68 			   ,p_emp_no
69 			   ,p_payroll
70    			   ,p_assignment_set
71 			   ,p_end_date
72 			   ,p_weeks);
73 	  WritetoCLOB (p_xml);
74 	END populate_p35_rep;
75 	--------------------------------------------------------------------------------
76 	-- POPULATE_PLSQL_TABLE
77 	--------------------------------------------------------------------------------
78 	PROCEDURE populate_plsql_table
79 	  (p_bg_id IN NUMBER
80 	   ,p_emp_no IN VARCHAR2
81 	   ,p_payroll        IN NUMBER
82    	   ,p_assignment_set        IN NUMBER
83 	   ,p_end_date IN VARCHAR2
84 	    ,p_weeks IN VARCHAR2) IS
85 
86      l_start_date DATE;
87 	l_end_date DATE;
88 	l_p_payroll number;
89 	l_assignment_set number;
90     l_set_flag	hr_assignment_set_amendments.include_or_exclude%TYPE;
91 
92 	CURSOR csr_get_flag_from_set
93     is
94 	select distinct hasa.include_or_exclude from hr_assignment_set_amendments hasa, hr_assignment_sets has
95 	where hasa.assignment_set_id = has.assignment_set_id
96 	and has.business_group_id = p_bg_id
97 	and has.assignment_set_id = l_assignment_set;
98 
99 	CURSOR getPayrollAction IS
100       SELECT distinct paa.payroll_action_id, paa.assignment_action_id,
101  	    nvl(SUBSTR(pactd.action_information1,1,9),' ') PPSN,
102 	    -- for bug 5301598
103         nvl(SUBSTR(pactd.action_information2,1,12),' ') Works,
104         pactd.action_information3 TotIWeeks,
105         pactd.action_information4 IClass,
106         pactd.action_information5 SClass,
107         pactd.action_information6 SWeeks,
108         pactd.action_information7 TClass,
109         pactd.action_information8 TWeeks,
110         pactd.action_information9 FClass,
111         pactd.action_information10 FWeeks,
112         substr(pactd.action_information11,1,instr(pactd.action_information11,'-',1)-1) FifthClass,
113 	  substr(pactd.action_information11,instr(pactd.action_information11,'-',1)+1,length(pactd.action_information11)) FifthWeek,
114         pactd.action_information12 NetTax,
115         pactd.action_information13 TaxPaid,
116         pactd.action_information14 EmpPRSI,
117         pactd.action_information15 TotPRSI,
118         pactd.action_information16 Pay,
119         pactd.action_information17 TaxBasis,
120         pactd.action_information18 SurName,
121         pactd.action_information19 FirstName,
122         to_char(to_date(trim(pactd.action_information20),'DD-MM-YYYY'),'DD/MM/YYYY') DOB,
123         pactd.action_information21 Address1,
124         pactd.action_information22 Address2,
125         pactd.action_information23 Address3,
126         to_char(to_date(trim(pactd.action_information24),'DD-MM-YYYY'),'DD/MM/YYYY') StartDate,
127         decode(to_char(to_date(trim(pactd.action_information25),'DD-MM-YYYY'),'DD/MM/YYYY'),'31/12/4712',null,to_char(to_date(trim(pactd.action_information25),'DD-MM-YYYY'),'DD/MM/YYYY')) EndDate,
128         pactd.action_information26 Credit,
129         pactd.action_information27
130         FROM pay_assignment_actions   paa,
131                   pay_payroll_actions      ppa,
132  				  pay_assignment_actions  paad,
133                   pay_action_information  pactd,
134                   pay_action_information   pai,
135 			per_assignments_f    paaf,
136 			pay_all_payrolls_f           ppf,
137 			hr_soft_coding_keyflex   flex
138         WHERE paa.payroll_action_id = ppa.payroll_action_id
139               AND paa.action_status = 'C'
140               AND ppa.action_type ='X'
141               AND ppa.business_group_id = p_bg_id
142 		  AND paa.source_action_id is null
143 		  AND pai.action_context_id = paa.assignment_action_id
144 	      AND pai.action_information_category = 'IE P35 DETAIL'
145 	      AND ppa.report_type = 'IEP35'
146 	      AND paa.assignment_id = pai.assignment_id
147 	      AND paaf.assignment_id = paa.assignment_id
148           AND paaf.business_group_id = ppa.business_group_id
149 		  --For Detail Record
150 		  AND  paad.payroll_action_id = paa.payroll_action_id
151           AND pactd.action_information_category   = 'IE P35 DETAIL'
152           AND pactd.action_context_type           = 'AAP'
153           AND paad.assignment_action_id = pactd.action_context_id
154 		  AND paad.assignment_action_id = paa.assignment_action_id
155   	      --End of Detail Record
156 	      AND paaf.payroll_id = ppf.payroll_id
157       	  AND ppf.effective_start_date <= l_end_date
158           AND ppf.effective_end_date >= l_start_date
159 	      AND flex.soft_coding_keyflex_id = ppf.soft_coding_keyflex_id
160 	      AND flex.segment4 = p_emp_no
161           AND paaf.effective_start_date <= l_end_date
162           AND paaf.effective_end_date >= l_start_date
163 	      AND TO_DATE (
164                                   pay_ie_p35.get_parameter (
165                                      ppa.payroll_action_id,
166                                      'END_DATE'
167                                   ),
168                                   'YYYY/MM/DD'
169                                ) BETWEEN l_start_date AND l_end_date
170 	      AND (ppf.payroll_id in (select b.payroll_id from per_assignments_f a,per_assignments_f b
171 				       where a.payroll_id = l_p_payroll
172 					and a.person_id = b.person_id
173 					and a.person_id = paaf.person_id
174 					--bug 6642916
175 					and a.effective_start_date<= l_end_date
176                                          and  a.effective_end_date >= l_start_date) or l_p_payroll is null)
177 	      AND ((l_assignment_set is not null
178 	     AND (l_set_flag ='I' AND EXISTS(SELECT 1
179 						    FROM  hr_assignment_set_amendments hasa
180 							 ,  hr_assignment_sets has
181 							 ,  per_assignments_f paf
182 					 WHERE has.assignment_set_id = hasa.assignment_set_id
183 					  AND   has.business_group_id = p_bg_id
184 					  AND   has.assignment_set_id = l_assignment_set
185 					  AND   hasa.assignment_id    = paf.assignment_id
186 					  AND   paf.person_id         = paaf.person_id)
187 
188 		OR l_set_flag = 'E' AND NOT EXISTS(SELECT 1
189 						    FROM  hr_assignment_set_amendments hasa
190 							 ,  hr_assignment_sets has
191 							 ,  per_assignments_f paf
192 				  WHERE has.assignment_set_id = hasa.assignment_set_id
193 					  AND   has.business_group_id = p_bg_id
194 					  AND   has.assignment_set_id = l_assignment_set
195 					  AND   hasa.assignment_id    = paf.assignment_id
196 					  AND   paf.person_id         = paaf.person_id)))
197 	  OR l_assignment_set IS NULL)
198 	  ORDER BY SurName, FirstName;
199 
200      CURSOR csr_get_pension_details (p_payroll_action_id NUMBER, p_assignment_action_id NUMBER) IS
201      SELECT count(decode(pact.action_information2,0,null,null,null,1)) EMP_RBS,
202        sum(to_number(pact.action_information2)) EMP_RBS_BAL,
203        count(decode(pact.action_information3,0,null,null,null,1)) EMPR_RBS,
204 	 sum(to_number(pact.action_information3)) EMPR_RBS_BAL,
205        count(decode(pact.action_information4,0,null,null,null,1)) EMP_PRSA,
206 	 sum(to_number(pact.action_information4)) EMP_PRSA_BAL,
207        count(decode(pact.action_information5,0,null,null,null,1)) EMPR_PRSA,
208 	 sum(to_number(pact.action_information5)) EMPR_PRSA_BAL,
209        count(decode(pact.action_information6,0,null,null,null,1)) EMP_RAC,
210 	 sum(to_number(pact.action_information6)) EMP_RAC_BAL,
211 	 sum(to_number(pact.action_information1)) TAXABLEBENEFITS
212      FROM   pay_assignment_actions  paa
213       ,pay_action_information  pact
214      WHERE paa.payroll_action_id        = p_payroll_action_id
215 	 and paa.assignment_action_id = pact.action_context_id
216 	 and paa.assignment_action_id = p_assignment_action_id
217     and   paa.source_action_id         is null
218     and   pact.action_information_category  = 'IE P35 ADDITIONAL DETAILS'
219     and   pact.action_context_type           = 'AAP';
220 
221 	CURSOR csr_header_footer_info(p_payroll_action_id NUMBER) IS
222 	SELECT
223           to_char(ppa.request_id),
224           p_end_date,
225 	      to_char(ppa.effective_date,'dd-mm-yyyy'),
226           pact.action_information1,
227           pact.action_information26 ,
228           pact.action_information27 ,
229           pact.action_information28 ,
230           pact.action_information5 ,
231           pact.action_information6 ,
232           pact.action_information7 ,
233           decode(trim(p_weeks),'Y','1','0'),
234 	      'Oracle HRMS',
235 	      'E'
236          FROM   pay_payroll_actions                ppa
237         ,pay_action_information             pact
238         WHERE  ppa.payroll_action_id  = p_payroll_action_id
239 	  AND    pact.action_context_id = ppa.payroll_action_id
240 	  AND    pact.action_information_category  = 'ADDRESS DETAILS'
241 	  AND    pact.action_context_type          = 'PA';
242 
243 	r_payroll_action_id NUMBER;
244 	r_assgt_action_id NUMBER;
245 	l_request_id NUMBER;
246 	l_tax_year VARCHAR2(30);
247 	l_date VARCHAR2(30);
248 	l_empr_no VARCHAR2(50);
249 	l_empr_name VARCHAR2(150);
250     l_contact_name VARCHAR2 (150);
251 	l_phone VARCHAR2 (30);
252 	l_addr1 VARCHAR2 (240);
253 	l_addr2 VARCHAR2 (240);
254 	l_addr3 VARCHAR2 (240);
255 	l_week53 VARCHAR2 (15);
256 	l_payroll VARCHAR2 (50);
257 	l_currency VARCHAR2 (30);
258 	l_tot_pay NUMBER := 0;
259 	l_tot_pay_rnd NUMBER := 0;
260     l_tot_tax NUMBER := 0;
261 	l_tot_prsi NUMBER := 0;
262 	l_tot_emp_prsi NUMBER := 0;
263     l_emp_rbs NUMBER := 0;
264     l_emp_rbs_bal NUMBER := 0;
265 	l_empr_rbs NUMBER := 0;
266     l_empr_rbs_bal NUMBER := 0;
267 	l_emp_prsa NUMBER := 0;
268 	l_emp_prsa_bal NUMBER := 0;
269     l_empr_prsa NUMBER := 0;
270 	l_empr_prsa_bal NUMBER := 0;
271 	l_emp_rac NUMBER := 0;
272 	l_emp_rac_bal NUMBER := 0;
273 	l_taxable_benefits NUMBER := 0;
274 	flag NUMBER := 0;
275 	c_pension csr_get_pension_details%ROWTYPE;
276     l_cess_date VARCHAR2(30);
277     l_initial_class pay_action_information.action_information4%TYPE;
278 
279 	BEGIN
280 
281 
282      -- hr_utility.trace_on(null,'vikp35');
283 	hr_utility.set_location('Start of Generation...', 1);
284 
285    vXMLTable.DELETE;
286 	vCtr := 1;
287 	--vXMLTable(vCtr).xmlString := '<?xml version="1.0" encoding="UTF-8"?> <P35LFile>';  -- get_IANA_charset
288 	vXMLTable(vCtr).xmlString := '<?xml version="1.0" encoding="'|| get_IANA_charset ||'"?> <P35LFile>';
289 	vCtr := vCtr + 1;
290 	l_start_date := PAY_IE_P35_XML_PKG.get_start_date();
291         -- l_end_date := PAY_IE_P35_XML_PKG.get_end_date();
292         l_end_date := fnd_date.canonical_to_date(p_end_date);                              --4641756
293         hr_utility.set_location('End Date : '||p_end_date,100);
294 
295 	IF p_payroll = 0 then
296 	    l_p_payroll := NULL;
297 	 ELSE
298 	    l_p_payroll := p_payroll;
299 	 END IF;
300 	IF p_assignment_set = 0 then
301 	    l_assignment_set := NULL;
302 	ELSE
303 	    l_assignment_set := p_assignment_set;
304 	END IF;
305 	OPEN csr_get_flag_from_set;
306     FETCH csr_get_flag_from_set into l_set_flag;
307     CLOSE csr_get_flag_from_set;
308 
309 	FOR c_action IN getPayrollAction LOOP
310 	flag := flag + 1;
311 	hr_utility.set_location('Loop Count ' || to_char(flag), 1);
312 	hr_utility.set_location('pactid' || to_char(r_payroll_action_id),13);
313 	IF flag = 1 THEN
314     OPEN csr_header_footer_info(c_action.payroll_action_id);
315 	FETCH csr_header_footer_info into l_request_id, l_tax_year,l_date,l_empr_no,l_empr_name,
316 	      l_contact_name,l_phone,l_addr1,l_addr2,l_addr3,l_week53,l_payroll,l_currency;
317 	CLOSE csr_header_footer_info;
318     hr_utility.set_location('emprno' || to_char(l_empr_no),13);
319 
320 	vXMLTable(vCtr).xmlString := '<TaxYear>' || to_char(fnd_date.canonical_to_date(l_tax_year),'YYYY') || '</TaxYear>';
321 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<RequestID>' || l_request_id || '</RequestID>';
322 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<Date>' || l_date || '</Date>';
323 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<EmployerNumber>' || l_empr_no || '</EmployerNumber>';
324 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<EmployerName>' || l_empr_name || '</EmployerName>';
325 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<ContactName>' || l_contact_name || '</ContactName>';
326 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<Phone>' || l_phone || '</Phone>';
327 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<Address1><![CDATA[' || l_addr1 || ']]></Address1>';
328 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<Address2><![CDATA[' || l_addr2 || ']]></Address2>';
329 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<Address3><![CDATA[' || l_addr3 || ']]></Address3>';
330 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<Week53>' || l_week53 || '</Week53>';
331 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<Payroll>' || l_payroll || '</Payroll>';
332 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<Currency>' || l_currency || '</Currency>';
333     vCtr := vCtr + 1;
334 	END IF;
335 	hr_utility.set_location('Fetching detail...', 3);
336 	hr_utility.set_location('Fetching Pension...', 3);
337 	OPEN csr_get_pension_details (c_action.payroll_action_id, c_action.assignment_action_id);
338     FETCH csr_get_pension_details into c_pension;
339     CLOSE csr_get_pension_details;
340 	l_emp_rbs := l_emp_rbs +  c_pension.EMP_RBS;
341     l_emp_rbs_bal := l_emp_rbs_bal + c_pension.EMP_RBS_BAL;
342     l_empr_rbs := l_empr_rbs + c_pension.EMPR_RBS;
343 	l_empr_rbs_bal := l_empr_rbs_bal +  c_pension.EMPR_RBS_BAL;
344     l_emp_prsa := l_emp_prsa + c_pension.EMP_PRSA;
345     l_emp_prsa_bal := l_emp_prsa_bal + c_pension.EMP_PRSA_BAL;
346 	l_empr_prsa := l_empr_prsa + c_pension.EMPR_PRSA;
347 	l_empr_prsa_bal := l_empr_prsa_bal + c_pension.EMPR_PRSA_BAL;
348 	l_emp_rac := l_emp_rac + c_pension.EMP_RAC;
349 	l_emp_rac_bal := l_emp_rac_bal + c_pension.EMP_RAC_BAL;
350     l_taxable_benefits := l_taxable_benefits + c_pension.TAXABLEBENEFITS;
351 	hr_utility.set_location('Generating...', 5);
352 	hr_utility.set_location('TableCnt' || to_char(vXMLTable.count),13);
353 
354 	l_cess_date:=c_action.EndDate;
355 	IF trim(l_cess_date) = '31-12-4712' THEN
356 	  l_cess_date := ' ';
357 	END IF;
358 	      hr_utility.set_location('P1..'||c_action.Works,100);
359 		vXMLTable(vCtr).xmlString := '<G_P35L>';
360 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<PPSN>' || (c_action.PPSN) || '</PPSN>';
361 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<Works>' || (c_action.Works) || '</Works>';
362 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<TotIWeeks>' || (c_action.TotIWeeks) || '</TotIWeeks>';
363 		hr_utility.set_location('P2..'||c_action.Works,100);
364 		if instr(c_action.IClass,'-') > 0 then
365 			l_initial_class := substr(c_action.IClass,1,instr(c_action.IClass,'-',1)-1);
366 			vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<IClass>' || (l_initial_class) || '</IClass>';
367 		else
368 			vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<IClass>' || (c_action.IClass) || '</IClass>';
369 		end if;
370 		hr_utility.set_location('P3..'||c_action.Works,100);
371 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<SClass>' || (c_action.SClass) || '</SClass>';
372 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<SWeeks>' || (c_action.SWeeks) || '</SWeeks>';
373 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<TClass>' || (c_action.TClass) || '</TClass>';
374 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<TWeeks>' || (c_action.TWeeks) || '</TWeeks>';
375 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<FClass>' || (c_action.FClass) || '</FClass>';
376 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<FWeeks>' || (c_action.FWeeks) || '</FWeeks>';
377 		hr_utility.set_location('P4..'||c_action.Works,100);
378 		IF c_action.FifthClass is not null then
379 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<FifthClass> Note-: PRSI Fifth Class ' || (c_action.FifthClass) || ' with Insurable Weeks </FifthClass>';
380 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<Fmessage>' || (c_action.FifthWeek) || ' exists </Fmessage>';
381 		ELSE
382 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<FifthClass>' || (c_action.FifthClass) || '</FifthClass>';
383 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<Fmessage>' || (c_action.FifthClass) || '</Fmessage>';
384 		END IF;
385 		hr_utility.set_location('P5..'||c_action.Works,100);
386 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<NetTax>' || to_char(to_number(c_action.NetTax),'FM99999999990D00') || '</NetTax>';
387     		l_tot_tax := l_tot_tax + to_number(c_action.NetTax);
388 		vCtr := vCtr + 1;
389 		vXMLTable(vCtr).xmlString := '<TaxPaid>' || (c_action.TaxPaid) || '</TaxPaid>';
390 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<EmpPRSI>' || to_char(to_number(c_action.EmpPRSI),'FM99999999990D00') || '</EmpPRSI>';
391 		l_tot_emp_prsi := l_tot_emp_prsi + to_number(c_action.EmpPRSI);
392 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<TotPRSI>' || to_char(to_number(c_action.TotPRSI),'FM99999999990D00') || '</TotPRSI>';
393 		l_tot_prsi := l_tot_prsi + to_number(c_action.TotPRSI);
394 		hr_utility.set_location('After Adding....TotPRSI=' || l_tot_prsi, 10);
395 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<Pay>' || to_char(to_number(c_action.Pay),'FM99999999990D00') || '</Pay>';
396 		l_tot_pay := l_tot_pay + to_number(c_action.Pay);
397 		l_tot_pay_rnd := l_tot_pay_rnd + round(to_number(c_action.Pay),0);
398 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<TaxBasis>' || (c_action.TaxBasis) || '</TaxBasis>';
399 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<SurName><![CDATA[' || (c_action.SurName) || ']]></SurName>';
400 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<FirstName><![CDATA[' || (c_action.FirstName) || ']]></FirstName>';
401 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<DOB>' || (c_action.DOB) || '</DOB>';
402 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<Address><![CDATA[' || (c_action.Address1) || ']]></Address>';
403 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<StartDate>' || (c_action.StartDate) || '</StartDate>';
404 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<EndDate>' || l_cess_date || '</EndDate>';
405 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<Credit>' || to_char(to_number(c_action.Credit),'FM99999999990D00') || '</Credit>';
406 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '</G_P35L>';
407 		vCtr := vCtr + 1;
408 		hr_utility.set_location('TableCnt' || to_char(vXMLTable.count),18);
409 	END LOOP;
410 	hr_utility.set_location('Generated...', 6);
411 	IF flag = 0 THEN
412   	  vXMLTable(vCtr).xmlString := '<Exception>No Data Found</Exception>';
413 	  vCtr := vCtr + 1;
414 	END IF;
415 	vXMLTable(vCtr).xmlString := '<TotPay>' || to_char(to_number(l_tot_pay),'FM99999999990D00') || '</TotPay>';
416 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<TotPayRnd>' || l_tot_pay_rnd || '</TotPayRnd>';
417 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<TotTax>' || to_char(to_number(l_tot_tax),'FM99999999990D00') || '</TotTax>';
418 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<TotalPRSI>' || to_char(to_number(l_tot_prsi),'FM99999999990D00') || '</TotalPRSI>';
419 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<TotEmpPRSI>' || to_char(to_number(l_tot_emp_prsi),'FM99999999990D00') || '</TotEmpPRSI>';
420 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<RBSEENum>' || l_emp_rbs || '</RBSEENum>';
421 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<RBSEEAmt>' || l_emp_rbs_bal || '</RBSEEAmt>';
422 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<RBSERNum>' || l_empr_rbs || '</RBSERNum>';
423 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<RBSERAmt>' || l_empr_rbs_bal || '</RBSERAmt>';
424 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<PRSAEENum>' || l_emp_prsa || '</PRSAEENum>';
425 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<PRSAEEAmt>' || l_emp_prsa_bal || '</PRSAEEAmt>';
426 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<PRSAERNum>' || l_empr_prsa || '</PRSAERNum>';
427 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<PRSAERAmt>' || l_empr_prsa_bal || '</PRSAERAmt>';
428 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<RACEENum>' || l_emp_rac || '</RACEENum>';
429 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<RACEEAmt>' || l_emp_rac_bal || '</RACEEAmt>';
430 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<TotNotPay>' || l_taxable_benefits || '</TotNotPay>';
431 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '</P35LFile>';
432 	vCtr := vCtr + 1;
433 
434   	hr_utility.set_location('TableCnt' || to_char(vXMLTable.count),13);
435    EXCEPTION
436       WHEN OTHERS  THEN
437 	  null;
438   END populate_plsql_table;
439 	--
440   FUNCTION get_start_date
441       RETURN DATE
442    AS
443       l_start_date   DATE;
444    BEGIN
445       SELECT fnd_date.canonical_to_date (
446                    SUBSTR (fpov.profile_option_value, 1, 4)
447                 || '01/01 00:00:00'
448              )
449         INTO l_start_date
450         FROM fnd_profile_option_values fpov, fnd_profile_options fpo
451        WHERE fpo.profile_option_id = fpov.profile_option_id
452          AND fpo.application_id = fpov.application_id
453          AND fpo.profile_option_name = 'PAY_IE_P35_REPORTING_YEAR'
454          AND fpov.level_id = 10001
455          AND fpov.level_value = 0;
456 
457       RETURN l_start_date;
458    END get_start_date;
459 
460 
461 --
462    FUNCTION get_end_date
463       RETURN DATE
464    AS
465       l_end_date   DATE;
466    BEGIN
467       SELECT fnd_date.canonical_to_date (
468                    SUBSTR (fpov.profile_option_value, 1, 4)
469                 || '12/31 23:59:59'
470              )
471         INTO l_end_date
472         FROM fnd_profile_option_values fpov, fnd_profile_options fpo
473        WHERE fpo.profile_option_id = fpov.profile_option_id
474          AND fpo.application_id = fpov.application_id
475          AND fpo.profile_option_name = 'PAY_IE_P35_REPORTING_YEAR'
476          AND fpov.level_id = 10001
477          AND fpov.level_value = 0;
478 
479       RETURN l_end_date;
480    END get_end_date;
481    --
482 END PAY_IE_P35_XML_PKG;