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.15.12020000.4 2013/02/04 11:44:12 rsahai 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 paaf.ASSIGNMENT_TYPE <> 'A'  --16210193
164 	      AND TO_DATE (
165                                   pay_ie_p35.get_parameter (
166                                      ppa.payroll_action_id,
167                                      'END_DATE'
168                                   ),
169                                   'YYYY/MM/DD'
170                                ) BETWEEN l_start_date AND l_end_date
171 	      AND (ppf.payroll_id in (select b.payroll_id from per_assignments_f a,per_assignments_f b
172 				       where a.payroll_id = l_p_payroll
173 					and a.person_id = b.person_id
174 					and a.person_id = paaf.person_id
175 					--bug 6642916
176 					and a.effective_start_date<= l_end_date
177                                          and  a.effective_end_date >= l_start_date) or l_p_payroll is null)
178 	      AND ((l_assignment_set is not null
179 	     AND (l_set_flag ='I' AND EXISTS(SELECT 1
180 						    FROM  hr_assignment_set_amendments hasa
181 							 ,  hr_assignment_sets has
182 							 ,  per_assignments_f paf
183 					 WHERE has.assignment_set_id = hasa.assignment_set_id
184 					  AND   has.business_group_id = p_bg_id
185 					  AND   has.assignment_set_id = l_assignment_set
186 					  AND   hasa.assignment_id    = paf.assignment_id
187 					  AND   paf.person_id         = paaf.person_id)
188 
189 		OR l_set_flag = 'E' AND NOT EXISTS(SELECT 1
190 						    FROM  hr_assignment_set_amendments hasa
191 							 ,  hr_assignment_sets has
192 							 ,  per_assignments_f paf
193 				  WHERE has.assignment_set_id = hasa.assignment_set_id
194 					  AND   has.business_group_id = p_bg_id
195 					  AND   has.assignment_set_id = l_assignment_set
196 					  AND   hasa.assignment_id    = paf.assignment_id
197 					  AND   paf.person_id         = paaf.person_id)))
198 	  OR l_assignment_set IS NULL)
199 	  ORDER BY SurName, FirstName;
200 
201      CURSOR csr_get_pension_details (p_payroll_action_id NUMBER, p_assignment_action_id NUMBER) IS
202      SELECT count(decode(pact.action_information2,0,null,null,null,1)) EMP_RBS,
203        sum(to_number(pact.action_information2)) EMP_RBS_BAL,
204        count(decode(pact.action_information3,0,null,null,null,1)) EMPR_RBS,
205 	 sum(to_number(pact.action_information3)) EMPR_RBS_BAL,
206        count(decode(pact.action_information4,0,null,null,null,1)) EMP_PRSA,
207 	 sum(to_number(pact.action_information4)) EMP_PRSA_BAL,
208        count(decode(pact.action_information5,0,null,null,null,1)) EMPR_PRSA,
209 	 sum(to_number(pact.action_information5)) EMPR_PRSA_BAL,
210        count(decode(pact.action_information6,0,null,null,null,1)) EMP_RAC,
211 	 sum(to_number(pact.action_information6)) EMP_RAC_BAL,
212 	 sum(to_number(pact.action_information1)) TAXABLEBENEFITS,
213        count(decode(pact.action_information23,0,null,null,null,1)) EMP_PARKING, /* knadhan */
214 	 sum(to_number(pact.action_information23)) EMP_PARKING_BAL,
215 	 sum(to_number(pact.action_information19)) EMP_INCOME_LEVY_BAL,
216 	 sum(to_number(pact.action_information18)) EMP_GROSS_INCOME
217 --12382953
218 	 ,sum(to_number(pact.action_information7)) EMP_USC_BAL,
219 	 sum(to_number(pact.action_information27)) EMP_GROSS_INCOME_USC,
220 	 sum(to_number(pact.action_information28)) EXCLUSIONORDER
221 --12382953
222 	 ,sum(to_number(pact.action_information22)) EMP_IB --14656910
223      FROM   pay_assignment_actions  paa
224       ,pay_action_information  pact
225      WHERE paa.payroll_action_id        = p_payroll_action_id
226 	 and paa.assignment_action_id = pact.action_context_id
227 	 and paa.assignment_action_id = p_assignment_action_id
228     and   paa.source_action_id         is null
229     and   pact.action_information_category  = 'IE P35 ADDITIONAL DETAILS'
230     and   pact.action_context_type           = 'AAP';
231 
232 	CURSOR csr_header_footer_info(p_payroll_action_id NUMBER) IS
233 	SELECT
234           to_char(ppa.request_id),
235           p_end_date,
236 	      to_char(ppa.effective_date,'dd-mm-yyyy'),
237           pact.action_information1,
238           pact.action_information26 ,
239           pact.action_information27 ,
240           pact.action_information28 ,
241           pact.action_information5 ,
242           pact.action_information6 ,
243           pact.action_information7 ,
244           decode(trim(p_weeks),'Y','1','0'),
245 	      'Oracle HRMS',
246 	      'E'
247          FROM   pay_payroll_actions                ppa
248         ,pay_action_information             pact
249         WHERE  ppa.payroll_action_id  = p_payroll_action_id
250 	  AND    pact.action_context_id = ppa.payroll_action_id
251 	  AND    pact.action_information_category  = 'ADDRESS DETAILS'
252 	  AND    pact.action_context_type          = 'PA';
253 
254 	r_payroll_action_id NUMBER;
255 	r_assgt_action_id NUMBER;
256 	l_request_id NUMBER;
257 	l_tax_year VARCHAR2(30);
258 	l_date VARCHAR2(30);
259 	l_empr_no VARCHAR2(50);
260 	l_empr_name VARCHAR2(150);
261     l_contact_name VARCHAR2 (150);
262 	l_phone VARCHAR2 (30);
263 	l_addr1 VARCHAR2 (240);
264 	l_addr2 VARCHAR2 (240);
265 	l_addr3 VARCHAR2 (240);
266 	l_week53 VARCHAR2 (15);
267 	l_payroll VARCHAR2 (50);
268 	l_currency VARCHAR2 (30);
269 	l_tot_pay NUMBER := 0;
270 	l_tot_pay_rnd NUMBER := 0;
271     l_tot_tax NUMBER := 0;
272 	l_tot_prsi NUMBER := 0;
273 	l_tot_emp_prsi NUMBER := 0;
274     l_emp_rbs NUMBER := 0;
275     l_emp_rbs_bal NUMBER := 0;
276 	l_empr_rbs NUMBER := 0;
277     l_empr_rbs_bal NUMBER := 0;
278 	l_emp_prsa NUMBER := 0;
279 	l_emp_prsa_bal NUMBER := 0;
280     l_empr_prsa NUMBER := 0;
281 	l_empr_prsa_bal NUMBER := 0;
282 	l_emp_rac NUMBER := 0;
283 	l_emp_rac_bal NUMBER := 0;
284 	l_taxable_benefits NUMBER := 0;
285 	flag NUMBER := 0;
286 	l_emp_parking NUMBER := 0; /* knadhan */
287         l_emp_parking_bal NUMBER := 0;
288         l_emp_income_levy_bal NUMBER := 0;
289 	l_emp_gross_income_bal NUMBER := 0;
290 	c_pension csr_get_pension_details%ROWTYPE;
291     l_cess_date VARCHAR2(30);
292     l_initial_class pay_action_information.action_information4%TYPE;
293 
294 --12382953
295 	l_emp_usc_bal NUMBER := 0;
296 	l_emp_gross_income_bal_usc NUMBER := 0;
297 	l_reporting_year VARCHAR2(10);
298 --12382953
299 	BEGIN
300 
301 
302      -- hr_utility.trace_on(null,'vikp35');
303 	hr_utility.set_location('Start of Generation...', 1);
304 
305    vXMLTable.DELETE;
306 	vCtr := 1;
307 	--vXMLTable(vCtr).xmlString := '<?xml version="1.0" encoding="UTF-8"?> <P35LFile>';  -- get_IANA_charset
308 	vXMLTable(vCtr).xmlString := '<?xml version="1.0" encoding="'|| get_IANA_charset ||'"?> <P35LFile>';
309 	vCtr := vCtr + 1;
310 	l_start_date := PAY_IE_P35_XML_PKG.get_start_date();
311         -- l_end_date := PAY_IE_P35_XML_PKG.get_end_date();
312         l_end_date := fnd_date.canonical_to_date(p_end_date);                              --4641756
313         hr_utility.set_location('End Date : '||p_end_date,100);
314 
315 	l_reporting_year := to_char(l_start_date,'RRRR'); --12382953
316 
317 
318 	IF p_payroll = 0 then
319 	    l_p_payroll := NULL;
320 	 ELSE
321 	    l_p_payroll := p_payroll;
322 	 END IF;
323 	IF p_assignment_set = 0 then
324 	    l_assignment_set := NULL;
325 	ELSE
326 	    l_assignment_set := p_assignment_set;
327 	END IF;
328 	OPEN csr_get_flag_from_set;
329     FETCH csr_get_flag_from_set into l_set_flag;
330     CLOSE csr_get_flag_from_set;
331 
332 	FOR c_action IN getPayrollAction LOOP
333 	flag := flag + 1;
334 	hr_utility.set_location('Loop Count ' || to_char(flag), 1);
335 	hr_utility.set_location('pactid' || to_char(r_payroll_action_id),13);
336 	IF flag = 1 THEN
337     OPEN csr_header_footer_info(c_action.payroll_action_id);
338 	FETCH csr_header_footer_info into l_request_id, l_tax_year,l_date,l_empr_no,l_empr_name,
339 	      l_contact_name,l_phone,l_addr1,l_addr2,l_addr3,l_week53,l_payroll,l_currency;
340 	CLOSE csr_header_footer_info;
341     hr_utility.set_location('emprno' || to_char(l_empr_no),13);
342 
343 	vXMLTable(vCtr).xmlString := '<TaxYear>' || to_char(fnd_date.canonical_to_date(l_tax_year),'YYYY') || '</TaxYear>';
344 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<RequestID>' || l_request_id || '</RequestID>';
345 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<Date>' || l_date || '</Date>';
346 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<EmployerNumber>' || l_empr_no || '</EmployerNumber>';
347 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<EmployerName>' || l_empr_name || '</EmployerName>';
348 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<ContactName>' || l_contact_name || '</ContactName>';
349 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<Phone>' || l_phone || '</Phone>';
350 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<Address1><![CDATA[' || l_addr1 || ']]></Address1>';
351 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<Address2><![CDATA[' || l_addr2 || ']]></Address2>';
352 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<Address3><![CDATA[' || l_addr3 || ']]></Address3>';
353 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<Week53>' || l_week53 || '</Week53>';
354 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<Payroll>' || l_payroll || '</Payroll>';
355 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<Currency>' || l_currency || '</Currency>';
356     vCtr := vCtr + 1;
357 	END IF;
358 	hr_utility.set_location('Fetching detail...', 3);
359 	hr_utility.set_location('Fetching Pension...', 3);
360 	OPEN csr_get_pension_details (c_action.payroll_action_id, c_action.assignment_action_id);
361     FETCH csr_get_pension_details into c_pension;
362     CLOSE csr_get_pension_details;
363 	l_emp_rbs := l_emp_rbs +  c_pension.EMP_RBS;
364 	l_emp_rbs_bal := l_emp_rbs_bal + c_pension.EMP_RBS_BAL;
365 	l_empr_rbs := l_empr_rbs + c_pension.EMPR_RBS;
366 	l_empr_rbs_bal := l_empr_rbs_bal +  c_pension.EMPR_RBS_BAL;
367 	l_emp_prsa := l_emp_prsa + c_pension.EMP_PRSA;
368 	l_emp_prsa_bal := l_emp_prsa_bal + c_pension.EMP_PRSA_BAL;
369 	l_empr_prsa := l_empr_prsa + c_pension.EMPR_PRSA;
370 	l_empr_prsa_bal := l_empr_prsa_bal + c_pension.EMPR_PRSA_BAL;
371 	l_emp_rac := l_emp_rac + c_pension.EMP_RAC;
372 	l_emp_rac_bal := l_emp_rac_bal + c_pension.EMP_RAC_BAL;
373 	l_emp_parking:=l_emp_parking+c_pension.EMP_PARKING; /* knadhan */
374 	l_emp_parking_bal:=l_emp_parking_bal+c_pension.EMP_PARKING_BAL;
375 	l_emp_income_levy_bal :=l_emp_income_levy_bal+c_pension.EMP_INCOME_LEVY_BAL;
376 	l_emp_gross_income_bal :=c_pension.EMP_GROSS_INCOME;
377 --12382953
378 	l_emp_usc_bal := l_emp_usc_bal + c_pension.EMP_USC_BAL; --14656910  --14656910V9
379 	l_emp_gross_income_bal_usc :=c_pension.EMP_GROSS_INCOME_USC;
380 --12382953
381     l_taxable_benefits := l_taxable_benefits + c_pension.TAXABLEBENEFITS;
382 	hr_utility.set_location('Generating...', 5);
383 	hr_utility.set_location('TableCnt' || to_char(vXMLTable.count),13);
384 
385 	l_cess_date:=c_action.EndDate;
386 	IF trim(l_cess_date) = '31-12-4712' THEN
387 	  l_cess_date := ' ';
388 	END IF;
389 	      hr_utility.set_location('P1..'||c_action.Works,100);
390 		vXMLTable(vCtr).xmlString := '<G_P35L>';
391 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<PPSN>' || (c_action.PPSN) || '</PPSN>';
392 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<Works>' || (c_action.Works) || '</Works>';
393 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<TotIWeeks>' || (c_action.TotIWeeks) || '</TotIWeeks>';
394 		hr_utility.set_location('P2..'||c_action.Works,100);
395 		if instr(c_action.IClass,'-') > 0 then
396 			l_initial_class := substr(c_action.IClass,1,instr(c_action.IClass,'-',1)-1);
397 			vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<IClass>' || (l_initial_class) || '</IClass>';
398 		else
399 			vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<IClass>' || (c_action.IClass) || '</IClass>';
400 		end if;
401 		hr_utility.set_location('P3..'||c_action.Works,100);
402 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<SClass>' || (c_action.SClass) || '</SClass>';
403 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<SWeeks>' || (c_action.SWeeks) || '</SWeeks>';
404 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<TClass>' || (c_action.TClass) || '</TClass>';
405 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<TWeeks>' || (c_action.TWeeks) || '</TWeeks>';
406 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<FClass>' || (c_action.FClass) || '</FClass>';
407 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<FWeeks>' || (c_action.FWeeks) || '</FWeeks>';
408 		hr_utility.set_location('P4..'||c_action.Works,100);
409 		IF c_action.FifthClass is not null then
410 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<FifthClass> Note-: PRSI Fifth Class ' || (c_action.FifthClass) || ' with Insurable Weeks </FifthClass>';
411 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<Fmessage>' || (c_action.FifthWeek) || ' exists </Fmessage>';
412 		ELSE
413 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<FifthClass>' || (c_action.FifthClass) || '</FifthClass>';
414 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<Fmessage>' || (c_action.FifthClass) || '</Fmessage>';
415 		END IF;
416 		hr_utility.set_location('P5..'||c_action.Works,100);
417 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<NetTax>' || to_char(to_number(c_action.NetTax),'FM99999999990D00') || '</NetTax>'; --14656910  --14656910V9
418     		l_tot_tax := l_tot_tax + to_number(c_action.NetTax);  --14656910 --14656910V9
419 		vCtr := vCtr + 1;
420 		vXMLTable(vCtr).xmlString := '<TaxPaid>' || (c_action.TaxPaid) || '</TaxPaid>';
421 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<EmpPRSI>' || to_char(to_number(c_action.EmpPRSI),'FM99999999990D00') || '</EmpPRSI>';  --14656910 --14656910V9
422 		l_tot_emp_prsi := l_tot_emp_prsi + to_number(c_action.EmpPRSI);  --14656910 --14656910V9
423 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<TotPRSI>' || to_char(to_number(c_action.TotPRSI),'FM99999999990D00') || '</TotPRSI>';  --14656910 --14656910V9
424 		l_tot_prsi := l_tot_prsi + to_number(c_action.TotPRSI);  --14656910 --14656910V9
425 		hr_utility.set_location('After Adding....TotPRSI=' || l_tot_prsi, 10);
426 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<Pay>' || to_char(to_number(c_action.Pay),'FM99999999990D00') || '</Pay>';  --14656910 --14656910V9
427 		l_tot_pay := l_tot_pay + to_number(c_action.Pay); --14656910 --14656910V9
428 		l_tot_pay_rnd := l_tot_pay_rnd + round(to_number(c_action.Pay),0);  --14656910 --14656910V9
429 --12382953
430 		IF l_reporting_year >= '2011' THEN
431                l_emp_gross_income_bal := l_emp_gross_income_bal_usc;
432 		END IF;
433 --12382953
434 
435 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<GrossIncome>' || to_char(l_emp_gross_income_bal,'FM99999999990D00') || '</GrossIncome>';  --14656910  --14656910V9
436 --12382953
437 		IF l_reporting_year < '2011' THEN
438 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<IncomeLevy>' || to_char(to_number(c_pension.EMP_INCOME_LEVY_BAL),'FM99999999990D00') || '</IncomeLevy>';   /* knadhan 8520684 */
439 		ELSE
440             vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<usc>' || to_char(to_number(c_pension.EMP_USC_BAL),'FM99999999990D00') || '</usc>'; --14656910 --14656910V9
441 		END IF;
442             vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<ib>' || to_char(to_number(c_pension.EMP_IB),'FM99999999990D00') || '</ib>';   --14656910 --14656910V9
443 
444 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<exclusionOrder>' || (c_pension.EXCLUSIONORDER) || '</exclusionOrder>';
445 --12382953
446 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<TaxBasis>' || (c_action.TaxBasis) || '</TaxBasis>';
447 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<SurName><![CDATA[' || (c_action.SurName) || ']]></SurName>';
448 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<FirstName><![CDATA[' || (c_action.FirstName) || ']]></FirstName>';
449 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<DOB>' || (c_action.DOB) || '</DOB>';
450 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<Address><![CDATA[' || (c_action.Address1) || ']]></Address>';
451 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<StartDate>' || (c_action.StartDate) || '</StartDate>';
452 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<EndDate>' || l_cess_date || '</EndDate>';
453 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<Credit>' || to_char(to_number(c_action.Credit),'FM99999999990D00') || '</Credit>';
454 		vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '</G_P35L>';
455 		vCtr := vCtr + 1;
456 		hr_utility.set_location('TableCnt' || to_char(vXMLTable.count),18);
457 	END LOOP;
458 	hr_utility.set_location('Generated...', 6);
459 	IF flag = 0 THEN
460   	  vXMLTable(vCtr).xmlString := '<Exception>No Data Found</Exception>';
461 	  vCtr := vCtr + 1;
462 	END IF;
463 	l_emp_parking_bal:= round(to_number(l_emp_parking_bal));  --14656910  --14656910V9
464       l_emp_income_levy_bal:=round(to_number(l_emp_income_levy_bal));
465 
466 	l_emp_usc_bal := round(to_number(l_emp_usc_bal));  --12382953  --14656910 --14656910V9
467 
468 	vXMLTable(vCtr).xmlString := '<TotPay>' || to_char(to_number(l_tot_pay),'FM99999999990D00') || '</TotPay>';
469 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<TotPayRnd>' || l_tot_pay_rnd || '</TotPayRnd>';
470 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<TotTax>' || to_char(to_number(l_tot_tax),'FM99999999990D00') || '</TotTax>';
471 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<TotalPRSI>' || to_char(to_number(l_tot_prsi),'FM99999999990D00') || '</TotalPRSI>';
472 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<TotEmpPRSI>' || to_char(to_number(l_tot_emp_prsi),'FM99999999990D00') || '</TotEmpPRSI>';
473 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<RBSEENum>' || l_emp_rbs || '</RBSEENum>';
474 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<RBSEEAmt>' || l_emp_rbs_bal || '</RBSEEAmt>';  --14656910 --14656910V9
475 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<RBSERNum>' || l_empr_rbs || '</RBSERNum>';
476 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<RBSERAmt>' || l_empr_rbs_bal || '</RBSERAmt>'; --14656910 --14656910V9
477 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<PRSAEENum>' || l_emp_prsa || '</PRSAEENum>';
478 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<PRSAEEAmt>' || l_emp_prsa_bal || '</PRSAEEAmt>';  --14656910 --14656910V9
479 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<PRSAERNum>' || l_empr_prsa || '</PRSAERNum>';
480 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<PRSAERAmt>' || l_empr_prsa_bal || '</PRSAERAmt>';  --14656910 --14656910V9
481 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<RACEENum>' || l_emp_rac || '</RACEENum>';
482 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<RACEEAmt>' || l_emp_rac_bal || '</RACEEAmt>';  --14656910 --14656910V9
483 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<TotNotPay>' || l_taxable_benefits || '</TotNotPay>';  --14656910 --14656910V9
484       vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<CARPARKNum>' || l_emp_parking || '</CARPARKNum>';   /* knadhan */
485 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<CARPARKAmt>' || l_emp_parking_bal || '</CARPARKAmt>';
486 --12382953
487       IF l_reporting_year < '2011' THEN
488 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<IncomeLevyAmt>' || l_emp_income_levy_bal || '</IncomeLevyAmt>';
489 	ELSE
490       vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '<totalUSC>' || l_emp_usc_bal || '</totalUSC>';
491 	END IF;
492 --12382953
493 	vXMLTable(vCtr).xmlString := vXMLTable(vCtr).xmlString || '</P35LFile>';
494 	vCtr := vCtr + 1;
495 
496   	hr_utility.set_location('TableCnt' || to_char(vXMLTable.count),13);
497    EXCEPTION
498       WHEN OTHERS  THEN
499 	  null;
500   END populate_plsql_table;
501 	--
502   FUNCTION get_start_date
503       RETURN DATE
504    AS
505       l_start_date   DATE;
506    BEGIN
507       SELECT fnd_date.canonical_to_date (
508                    SUBSTR (fpov.profile_option_value, 1, 4)
509                 || '01/01 00:00:00'
510              )
511         INTO l_start_date
512         FROM fnd_profile_option_values fpov, fnd_profile_options fpo
513        WHERE fpo.profile_option_id = fpov.profile_option_id
514          AND fpo.application_id = fpov.application_id
515          AND fpo.profile_option_name = 'PAY_IE_P35_REPORTING_YEAR'
516          AND fpov.level_id = 10001
517          AND fpov.level_value = 0;
518 
519       RETURN l_start_date;
520    END get_start_date;
521 
522 
523 --
524    FUNCTION get_end_date
525       RETURN DATE
526    AS
527       l_end_date   DATE;
528    BEGIN
529       SELECT fnd_date.canonical_to_date (
530                    SUBSTR (fpov.profile_option_value, 1, 4)
531                 || '12/31 23:59:59'
532              )
533         INTO l_end_date
534         FROM fnd_profile_option_values fpov, fnd_profile_options fpo
535        WHERE fpo.profile_option_id = fpov.profile_option_id
536          AND fpo.application_id = fpov.application_id
537          AND fpo.profile_option_name = 'PAY_IE_P35_REPORTING_YEAR'
538          AND fpov.level_id = 10001
539          AND fpov.level_value = 0;
540 
541       RETURN l_end_date;
542    END get_end_date;
543    --
544 END PAY_IE_P35_XML_PKG;