[Home] [Help]
PACKAGE BODY: APPS.PAY_IE_EOY_INCOMELEVY_REPORT
Source
1 PACKAGE BODY pay_ie_eoy_incomelevy_report as
2 /* $Header: pyieeoyc.pkb 120.6 2011/01/04 14:15:12 abraghun noship $ */
3 vCtr NUMBER;
4
5 /*6876894*/
6 TYPE t_asg_set_amnds IS TABLE OF hr_assignment_set_amendments.include_or_exclude%TYPE
7 INDEX BY BINARY_INTEGER;
8 l_tab_asg_set_amnds t_asg_set_amnds;
9
10
11 --
12 /*6876894*/
13 /*
14 This procedure returns formulae id for the assignment set criteria and
15 if its assignment set ammendents, then it retrives and stores those assignments
16 in p_tab_asg_set_amnds
17 */
18
19 FUNCTION test_XML(P_STRING VARCHAR2) RETURN VARCHAR2 AS
20 l_string varchar2(1000);
21
22 FUNCTION replace_xml_symbols(pp_string IN VARCHAR2)
23 RETURN VARCHAR2
24 AS
25
26 ll_string VARCHAR2(1000);
27
28 BEGIN
29
30
31 ll_string := pp_string;
32
33 ll_string := replace(ll_string, '&', '&');
34 ll_string := replace(ll_string, '<', '<');
35 ll_string := replace(ll_string, '>', '>');
36 ll_string := replace(ll_string, '''',''');
37 ll_string := replace(ll_string, '"', '"');
38
39 RETURN ll_string;
40 EXCEPTION when no_data_found then
41 null;
42 END replace_xml_symbols;
43
44 begin
45 l_string := p_string;
46 l_string := replace_xml_symbols(l_string);
47
48 l_string := pay_ie_p35_magtape.test_XML(l_string);
49
50 RETURN l_string;
51 END ;
52
53 PROCEDURE get_asg_set_details(
54 p_assignment_set_id IN NUMBER
55 ,p_formula_id OUT NOCOPY NUMBER
56 ,p_tab_asg_set_amnds OUT NOCOPY t_asg_set_amnds
57 )
58 IS
59 --
60 -- Cursor to get information about assignment set
61 CURSOR csr_get_asg_set_info(c_asg_set_id NUMBER)
62 IS
63 SELECT formula_id
64 FROM hr_assignment_sets ags
65 WHERE assignment_set_id = c_asg_set_id
66 AND EXISTS(SELECT 1
67 FROM hr_assignment_set_criteria agsc
68 WHERE agsc.assignment_set_id = ags.assignment_set_id);
69 -- Cursor to get assignment ids from asg set amendments
70 CURSOR csr_get_asg_amnd(c_asg_set_id NUMBER)
71 IS
72 SELECT assignment_id, NVL(include_or_exclude
73 ,'I') include_or_exclude
74 FROM hr_assignment_set_amendments
75 WHERE assignment_set_id = c_asg_set_id;
76 l_proc_step NUMBER(38, 10) := 0;
77 l_asg_set_amnds csr_get_asg_amnd%ROWTYPE;
78 l_tab_asg_set_amnds t_asg_set_amnds;
79 l_formula_id NUMBER;
80
81 --
82 BEGIN
83 --
84 fnd_file.put_line(fnd_file.LOG,'Entering get_asg_set_details');
85 -- Check whether the assignment set id has a criteria
86 -- if a formula id is attached or check whether this
87 -- is an amendments only
88 l_formula_id := NULL;
89 OPEN csr_get_asg_set_info(p_assignment_set_id);
90 FETCH csr_get_asg_set_info INTO l_formula_id;
91 fnd_file.put_line(fnd_file.LOG,' after csr_get_asg_set_info ');
92 fnd_file.put_line(fnd_file.LOG,' l_formula_id '|| l_formula_id);
93 IF csr_get_asg_set_info%FOUND
94 THEN
95 -- Criteria exists check for formula id
96 IF l_formula_id IS NULL
97 THEN
98 -- Raise error as the criteria is not generated
99 CLOSE csr_get_asg_set_info;
100 hr_utility.raise_error;
101 END IF; -- End if of formula id is null check ...
102 END IF; -- End if of asg criteria row found check ...
103 CLOSE csr_get_asg_set_info;
104 fnd_file.put_line(fnd_file.LOG,' before csr_get_asg_amd ');
105 OPEN csr_get_asg_amnd(p_assignment_set_id);
106 LOOP
107 FETCH csr_get_asg_amnd INTO l_asg_set_amnds;
108 EXIT WHEN csr_get_asg_amnd%NOTFOUND;
109 l_tab_asg_set_amnds(l_asg_set_amnds.assignment_id) :=
110 l_asg_set_amnds.include_or_exclude;
111 fnd_file.put_line(fnd_file.LOG,' l_asg_set_amnds.assignment_id '|| l_asg_set_amnds.assignment_id);
112 fnd_file.put_line(fnd_file.LOG,' l_asg_set_amnds.include_or_exclude '|| l_asg_set_amnds.include_or_exclude);
113 END LOOP;
114 CLOSE csr_get_asg_amnd;
115 p_formula_id := l_formula_id;
116 p_tab_asg_set_amnds := l_tab_asg_set_amnds;
117 EXCEPTION
118 WHEN OTHERS
119 THEN
123
120 fnd_file.put_line(fnd_file.LOG,'..'||'SQL-ERRM :'||SQLERRM);
121 END get_asg_set_details;
122
124
125 /*6876894*/
126 /*
127 firstly it checks whether the assignment is present in assinment set ammendments else
128 it executes the formulae if its not null for a particular assignment , returns whether
129 included or not.
130
131 */
132
133 FUNCTION chk_is_asg_in_asg_set(
134 p_assignment_id IN NUMBER
135 ,p_formula_id IN NUMBER
136 ,p_tab_asg_set_amnds IN t_asg_set_amnds
137 ,p_effective_date IN DATE
138 )
139 RETURN VARCHAR2
140 IS
141 l_session_date DATE;
142 l_include_flag VARCHAR2(10);
143 l_tab_asg_set_amnds t_asg_set_amnds;
144 l_inputs ff_exec.inputs_t;
145 l_outputs ff_exec.outputs_t;
146 --
147 BEGIN
148 --
149 fnd_file.put_line(fnd_file.LOG,'Entering chk_is_asg_in_asg_set');
150 l_include_flag := 'N';
151 l_tab_asg_set_amnds := p_tab_asg_set_amnds;
152 -- Check whether the assignment exists in the collection
153 -- first as the static assignment set overrides the
154 -- criteria one
155 IF l_tab_asg_set_amnds.EXISTS(p_assignment_id)
156 THEN
157 fnd_file.put_line(fnd_file.LOG,'Entered assignment ammendents if block');
158 -- Check whether to include or exclude
159 IF l_tab_asg_set_amnds(p_assignment_id) = 'I'
160 THEN
161 l_include_flag := 'Y';
162 ELSIF l_tab_asg_set_amnds(p_assignment_id) = 'E'
163 THEN
164 l_include_flag := 'N';
165 END IF; -- End if of include or exclude flag check ...
166 ELSIF p_formula_id IS NOT NULL
167 THEN
168 -- assignment does not exist in assignment set amendments
169 -- check whether a formula criteria exists for this
170 -- assignment set
171 -- Initialize the formula
172 fnd_file.put_line(fnd_file.LOG,'Entered assignment criteria block');
173 ff_exec.init_formula(p_formula_id => p_formula_id
174 ,p_effective_date => p_effective_date
175 ,p_inputs => l_inputs
176 ,p_outputs => l_outputs
177 );
178 fnd_file.put_line(fnd_file.LOG,'formula initialized');
179 -- Set the inputs first
180 -- Loop through them to set the contexts
181 FOR i IN l_inputs.FIRST .. l_inputs.LAST
182 LOOP
183 IF l_inputs(i).NAME = 'ASSIGNMENT_ID'
184 THEN
185 l_inputs(i).VALUE := p_assignment_id;
186 ELSIF l_inputs(i).NAME = 'DATE_EARNED'
187 THEN
188 l_inputs(i).VALUE := fnd_date.date_to_canonical(p_effective_date);
189 END IF;
190 END LOOP;
191 -- Run the formula
192 fnd_file.put_line(fnd_file.LOG,' before formaula run');
193
194
195 ff_exec.run_formula(l_inputs, l_outputs);
196
197
198 fnd_file.put_line(fnd_file.LOG,' aftre formaula run');
199 -- Check whether the assignment has to be included
200 -- by checking the output flag
201
202
203 fnd_file.put_line(fnd_file.LOG,' before outputs for run');
204 FOR i IN l_outputs.FIRST .. l_outputs.LAST
205 LOOP
206 IF l_outputs(i).NAME = 'INCLUDE_FLAG'
207 THEN
208 IF l_outputs(i).VALUE = 'Y'
209 THEN
210 l_include_flag := 'Y';
211 ELSIF l_outputs(i).VALUE = 'N'
212 THEN
213 l_include_flag := 'N';
214 END IF;
215 fnd_file.put_line(fnd_file.LOG,'p_assignment_id'||p_assignment_id);
216 fnd_file.put_line(fnd_file.LOG,'l_include_flag'||l_include_flag);
217 EXIT;
218 END IF;
219
220 END LOOP;
221 END IF; -- End if of assignment exists in amendments check ...
222
223 RETURN l_include_flag;
224 EXCEPTION
225 WHEN OTHERS
226 THEN
227 fnd_file.put_line(fnd_file.LOG,'..'||'SQL-ERRM :'||SQLERRM);
228 END chk_is_asg_in_asg_set;
229
230
231
232
233 procedure get_eoy_income_details(cp_start_date in date,
234 cp_effective_date in date,
235 cp_end_date in date,
236 p_business_group_id in number,
237 p_assignment_set_id in number,
238 p_payroll_id in number,
239 p_consolidation_set_id in number,
240 p_sort_order in varchar2)
241 is
242 cursor c_p60_records(cp_start_date date,
243 cp_effective_date date,
244 cp_end_date date,
245 p_business_group_id number,
246 p_assignment_set_id number,
247 p_payroll_id number,
248 p_consolidation_set_id number,
249 p_sort_order varchar2) IS
250 select
251 /* 9081004 */
252 upper(SUBSTR(trim(pai.action_information18),1,30)) surname
253 ,upper(SUBSTR(trim(pai.action_information19),1,30)) first_name
254 ,upper(pai.action_information1) ppsn
255 ,lpad(upper(pai.action_information2), 9, ' ') works_num
256 ,decode(sign(to_date(pai.action_information24,'DD-MM-YYYY')- cp_start_date),-1,Null,to_char(to_date(pai.action_information24,'DD-MM-YYYY'),'DDMMRR')) hire_date
257 ,nvl(pai_prsi.action_information24,0) total_gross_pay
258 ,nvl(pai_prsi.action_information25,0) total_income_levy
262 ,upper(substr(trim(pact_ade.action_information7),1,30)) address_line3
259 ,upper(nvl(rtrim(pact_ade.action_information26),'')) Employer_name
260 ,upper(substr(trim(pact_ade.action_information5),1,30)) address_line1
261 ,upper(substr(trim(pact_ade.action_information6),1,30)) address_line2
263 ,lpad(translate(pact_ade.action_information28,'1()-', '1'), 11, ' ') Phone_number
264 ,lpad(upper(nvl(rtrim(pact_ade.action_information1),'')), 8, ' ') Employer_number
265 ,upper(substr(trim(pact_ade.action_information11),1,30)) Location_name -- 10277535
266 ,paf.assignment_number assignment_number
267 ,paf.person_id Person_Id
268 ,paf.assignment_id assignment_id /*6876894*/
269 ,substr(trim(pai.action_information21),1,30) emp_Address1 /* 9160076 */
270 ,substr(trim(pai.action_information22),1,30) emp_Address2
271 ,rpad(substr(trim(pai.action_information23),1,30) ,30,' ') emp_County
272 ,pai_prsi.action_information26 asg_location_id -- 10277535
273 FROM pay_action_information pai /*Employee Details Info*/
274 ,pay_action_information pai_prsi /* prsi Details 5657992 */
275 ,pay_action_information pact_ade /*Address Details - for Employer Name -IE Employer Tax Address*/
276 ,pay_payroll_actions ppa35
277 ,pay_assignment_actions paa
278 ,per_assignments_f paf
279 ,per_periods_of_service pps
280 ,pay_ie_paye_details_f payef -- 10277535
281 ,pay_ie_prsi_details_f prsif
282 ,pay_all_payrolls_f PAPF
283 WHERE
284 NVl('N','N') = 'N'
285 and to_date(pay_ie_p35.get_parameter(ppa35.payroll_action_id,'END_DATE'),'YYYY/MM/DD') between cp_start_date and cp_end_date
286 -- and cp_start_date <= to_date(pay_ie_p35.get_parameter(ppa35.payroll_action_id,'END_DATE'),'YYYY/MM/DD') /*4641756*/
287 and ppa35.report_type = 'IEP35'
288 and ppa35.business_group_id = p_business_group_id /* p_business_group_id */
289 and paa.payroll_action_id = ppa35.payroll_action_id
290 and paa.assignment_id = paf.assignment_id
291 and paa.action_status IN ('C','S') --10225372
292 and paa.assignment_action_id = pai.action_context_id
293 and paf.period_of_service_id = pps.period_of_service_id
294 and paf.person_id= pps.person_id
295 and paf.business_group_id + 0 = p_business_group_id /*4483028*/
296 -- Bug 3446744 Checking if the employee has been terminated before issuing the P60
297 and (pps.actual_termination_date is null or pps.actual_termination_date > cp_end_date)
298 and paf.effective_start_date = (select max(asg2.effective_start_date)
299 from per_all_assignments_f asg2
300 where asg2.assignment_id = paf.assignment_id
301 and asg2.effective_start_date <= cp_end_date
302 and nvl(asg2.effective_end_date, to_date('31-12-4712','DD-MM-RRRR')) >= cp_start_date)
303 /*bug 3595646*/
304 and payef.assignment_id(+)= paa.assignment_id
305 -- For SR 5108858.993
306 -- Bug#9503612 Fix commented the following and added code using effective date 10277535
307 -- 6774415 Changed eff dates to cert dates
308 and (payef.certificate_start_date is null or payef.certificate_start_date <= cp_end_date) --8229764
309 and (payef.certificate_end_date IS NULL OR payef.certificate_end_date >= cp_start_date)
310
311 --
312 and payef.effective_start_date(+) <= cp_end_date -- 10277535
313 and payef.effective_end_date(+) >= cp_start_date -- 10277535
314 and (payef.effective_end_date = (select max(paye.effective_end_date)
315 from pay_ie_paye_details_f paye
316 where paye.assignment_id = paa.assignment_id
317 and paye.effective_start_date <= cp_end_date -- Bug#9503612 10277535
318 and paye.effective_end_date >= cp_start_date -- Bug#9503612 10277535
319 /* --6774415 Changed eff dates to cert dates, nvl for 8229764
320 and nvl(paye.certificate_start_date, to_date('01/01/0001','DD/MM/YYYY')) <= cp_end_date
321 and nvl(paye.certificate_end_date,to_date('31/12/4712','DD/MM/YYYY')) >= cp_start_date */
322 )
323 or
324 payef.effective_end_date IS NULL
325 )
326 and prsif.assignment_id(+)= paa.assignment_id
327 -- For SR - 5108858.993, similar changes were made to PRSI as
328 -- made for PAYE
329 and prsif.effective_start_date(+) <= cp_end_date /*to_date(pay_ie_p35.get_parameter(ppa35.payroll_action_id,'END_DATE'),'YYYY/MM/DD')*/
330 and prsif.effective_end_date(+) >= cp_start_date /*to_date(pay_ie_p35.get_parameter(ppa35.payroll_action_id,'START_DATE'),'YYYY/MM/DD')*/
331 --
332 and (prsif.effective_end_date = (select max(prsi.effective_end_date)
333 from pay_ie_prsi_details_f prsi
334 where prsi.assignment_id = paa.assignment_id
335 and prsi.effective_start_date <= cp_end_date /*to_date(pay_ie_p35.get_parameter(ppa35.payroll_action_id,'END_DATE'),'YYYY/MM/DD')*/
336 and prsi.effective_end_date >= cp_start_date /*to_date(pay_ie_p35.get_parameter(ppa35.payroll_action_id,'START_DATE'),'YYYY/MM/DD')*/
337 )
338 or
339 prsif.effective_end_date IS NULL
340 )
341 -- Bug 3446744 Removed the check of a P45 existence
345 paax.assignment_id = paa.assignment_id
342 /* and not exists (select 1 from pay_assignment_actions paax
343 ,pay_payroll_actions ppax
344 WHERE
346 and ppax.payroll_action_id = paax.payroll_action_id
347 and ppax.report_type = 'P45'
348 and ppax.business_group_id = ppa35.business_group_id
349 and ppax.action_status = 'C') */
350 /*6876894*/
351 /* removing the check with the assignment set ammendments and checking later for both ammendment set criteria
352 and ammendments for a particular assignment set id*/
353 /* AND (p_assignment_set_id IS NULL OR EXISTS (SELECT ' '
354 FROM HR_ASSIGNMENT_SET_AMENDMENTS HR_ASG
355 WHERE HR_ASG.ASSIGNMENT_SET_ID=NVL(p_assignment_set_id, HR_ASG.ASSIGNMENT_SET_ID)
356 AND HR_ASG.ASSIGNMENT_ID=PAA.ASSIGNMENT_ID ))
357 */
358 and PAPF.payroll_id = paf.payroll_id
359 and PAPF.business_group_id + 0 = p_business_group_id /*4483028*/
360 and PAPF.payroll_id = nvl(p_payroll_id,papf.payroll_id)
361 and papf.consolidation_set_id =nvl(p_consolidation_set_id,PAPF.consolidation_set_id)
362 and PAPF.effective_end_date = (select max(PAPF1.effective_end_date)
363 from pay_all_payrolls_f PAPF1
364 where PAPF1.payroll_id = PAPF.payroll_id
365 and PAPF1.effective_start_date <= cp_end_date --to_date(pay_ie_p35.get_parameter(ppa35.payroll_action_id,'END_DATE'),'YYYY/MM/DD')
366 and PAPF1.effective_end_date >= cp_start_date --to_date(pay_ie_p35.get_parameter(ppa35.payroll_action_id,'START_DATE'),'YYYY/MM/DD')
367 )
368 AND pact_ade.action_information_category = 'ADDRESS DETAILS'
369 AND pact_ade.action_context_type = 'PA'
370 AND pai.action_information_category = 'IE P35 DETAIL'
371 -- added for PRSI section changes 5657992
372 AND pai_prsi.action_information_category = 'IE P35 ADDITIONAL DETAILS'
373 AND pai.action_context_id = pai_prsi.action_context_id
374 -- end 5657992
375 AND pact_ade.ACTION_CONTEXT_ID = paa.payroll_action_id
376 and paf.period_of_service_id = pps.period_of_service_id
377 and paf.person_id= pps.person_id
378 order by decode(p_sort_order,'Last Name',SUBSTR(trim(pai.action_information18||','|| pai.action_information19),1,30),
379 'Address Line1',substr(trim(pai.action_information21),1,30),
380 'Address Line2',substr(trim(pai.action_information22),1,30),
381 'County',rpad(substr(trim(pai.action_information23),1,30) ,30,' '),
382 'Assignment Number',paf.assignment_number,
383 'National Identifier',nvl(pai.action_information1,' '),
384 SUBSTR(trim(pai.action_information18||','|| pai.action_information19),1,30));
385
386 /*
387 CURSOR cur_assignment_action_till_apr(c_ppsn varchar2
388 ,c_assignment_id per_all_assignments.assignment_id%type
389 ,c_person_id per_all_people_f.person_id%type) is
390 SELECT fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
391 paa.assignment_action_id),16))
392 FROM pay_assignment_actions paa,pay_payroll_actions ppa
393 WHERE ((c_ppsn is null and paa.assignment_id=c_assignment_id)
394 OR(c_ppsn is not null and paa.assignment_id in (select paaf.assignment_id
395 from per_all_assignments_f paaf, per_assignment_extra_info paei
396 where paaf.person_id = c_person_id
397 and paaf.assignment_id=paei.assignment_id
398 and paei.information_type = 'IE_ASG_OVERRIDE'
399 and paei.aei_information1 = c_ppsn --'314678745T'
400 )))
401 AND paa.payroll_action_id=ppa.payroll_action_id
402 AND ppa.action_type in ('Q','B','R','I','V')
403 AND ppa.action_status ='C'
404 AND paa.source_action_id is null
405 AND ppa.effective_date<= to_date('30/04'||'/'||to_char(cp_end_date,'yyyy'),'dd/mm/yyyy');
406 */
407
408 CURSOR cur_assignment_action_till_apr(c_assignment_id per_all_assignments.assignment_id%type
409 ) is
410 SELECT fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
411 paa.assignment_action_id),16))
412 FROM pay_assignment_actions paa
413 ,pay_payroll_actions ppa
414 WHERE paa.assignment_id=c_assignment_id
415 AND paa.payroll_action_id=ppa.payroll_action_id
416 AND ppa.action_type in ('Q','B','R','I','V')
417 AND ppa.action_status ='C'
418 AND paa.source_action_id is null
419 AND ppa.effective_date<= to_date('30/04'||'/'||to_char(cp_end_date,'yyyy'),'dd/mm/yyyy');
420
421 CURSOR cur_assignment_action(c_assignment_id per_all_assignments.assignment_id%type
422 ) is
423 SELECT fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
424 paa.assignment_action_id),16))
425 FROM pay_assignment_actions paa
426 ,pay_payroll_actions ppa
427 WHERE paa.assignment_id=c_assignment_id
428 AND paa.payroll_action_id=ppa.payroll_action_id
429 AND ppa.action_type in ('Q','B','R','I','V')
430 AND ppa.action_status ='C'
434 cursor cur_defined_balance_id (c_balance_name pay_balance_types.balance_name%type
431 AND paa.source_action_id is null
432 AND ppa.effective_date between cp_start_date and cp_end_date;
433
435 ,c_dimension_name pay_balance_dimensions.database_item_suffix%type) is
436 select pdb.defined_balance_id
437 from pay_defined_balances pdb
438 ,pay_balance_dimensions pbd
439 ,pay_balance_types pbt
440
441 WHERE pbt.balance_name=c_balance_name
442 AND pbt.balance_type_id=pdb.balance_type_id
443 and pbd.database_item_suffix=c_dimension_name
444 and pbd.balance_dimension_id=pdb.balance_dimension_id
445 and pbt.legislation_code='IE'
446 and pdb.legislation_code='IE';
447
448 CURSOR cur_paye_ref(c_assignment_id per_all_assignments.assignment_id%type
449 ,c_person_id per_all_people_f.person_id%type) IS
450 SELECT scl.segment4 paye_ref
451 FROM per_all_assignments_f paaf,
452 pay_all_payrolls_f papf,
453 hr_soft_coding_keyflex scl
454 WHERE paaf.person_id = c_person_id
455 AND paaf.assignment_id=c_assignment_id
456 AND paaf.payroll_id = papf.payroll_id
457 /* 9255733 */
458 AND papf.effective_end_date = (select max(PAPF1.effective_end_date)
459 from pay_all_payrolls_f PAPF1
460 where PAPF1.payroll_id = papf.payroll_id
461 and PAPF1.effective_start_date <= cp_end_date
462 and PAPF1.effective_end_date >= cp_start_date
463 )
464 AND papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id;
465
466 CURSOR csr_ppsn_override(p_asg_id NUMBER) IS
467 SELECT aei_information1 PPSN_OVERRIDE
468 FROM per_assignment_extra_info
469 WHERE assignment_id = p_asg_id
470 AND aei_information_category = 'IE_ASG_OVERRIDE';
471
472 CURSOR csr_get_org_email(l_paye_ref number
473 ) IS
474 SELECT
475 org_info1.org_information3 email /* knadhan */
476
477 FROM hr_organization_information org_info1
478 WHERE
479 org_info1.org_information_context = 'ORG_CONTACT_DETAILS'
480 AND org_info1.org_information1 ='EMAIL'
481 AND org_info1.organization_id = l_paye_ref
482 ;
483
484 CURSOR c_get_periods_of_service(v_person_id NUMBER,
485 v_assignment_id NUMBER,
486 v_paye_ref NUMBER) IS
487
488 SELECT max(pps.period_of_service_id)
489 FROM per_periods_of_service pps
490 ,per_assignments_f asg
491 ,pay_all_payrolls_f pay
492 ,hr_soft_coding_keyflex flex
493 WHERE pps.person_id = v_person_id
494 AND pps.person_id = asg.person_id
495 AND asg.period_of_service_id <> pps.period_of_service_id
496 AND asg.assignment_id = v_assignment_id
497 AND asg.payroll_id = pay.payroll_id
498 AND pay.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
499 AND flex.segment4 = v_paye_ref
500 AND actual_termination_date IS NOT NULL
501 AND actual_termination_date BETWEEN cp_start_date
502 AND cp_end_date;
503
504 /* 9560108 */
505 --
506 CURSOR c_get_max_aact(p_pds_id NUMBER, c_person_id NUMBER) IS
507 SELECT fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
508 paa.assignment_action_id),16))
509 FROM pay_assignment_Actions paa,
510 pay_payroll_actions ppa
511
512 WHERE paa.assignment_id in (SELECT assignment_id
513 FROM per_assignments_f
514 WHERE period_of_service_id = p_pds_id
515 and person_id=c_person_id)
516 AND paa.payroll_action_id = ppa.payroll_action_id
517 AND ppa.action_type IN ('R','Q','I','B','V')
518 AND paa.action_status IN ('C','S') --10225372
519 AND paa.source_action_id is null
520 AND ppa.effective_date<= to_date('30/04'||'/'||to_char(cp_end_date,'yyyy'),'dd/mm/yyyy');
521
522 CURSOR c_get_max_aact_ppsn(p_pds_id NUMBER, /* 9560108 */
523 c_ppsn varchar2,
524 c_person_id NUMBER) IS
525 SELECT fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
526 paa.assignment_action_id),16))
527 FROM pay_assignment_Actions paa,
528 pay_payroll_actions ppa
529
530 WHERE paa.assignment_id in (SELECT paf.assignment_id
531 FROM per_assignments_f paf, per_assignment_extra_info paei
532 WHERE paf.period_of_service_id = p_pds_id
533 AND paf.person_id=c_person_id
534 AND paf.assignment_id=paei.assignment_id
535 AND paei.information_type = 'IE_ASG_OVERRIDE'
536 AND paei.aei_information1 = c_ppsn
537 )
538
539
540 AND paa.payroll_action_id = ppa.payroll_action_id
541 AND ppa.action_type IN ('R','Q','I','B','V')
542 AND paa.action_status IN ('C','S') --10225372
543 AND paa.source_action_id is null
544 AND ppa.effective_date<= to_date('30/04'||'/'||to_char(cp_end_date,'yyyy'),'dd/mm/yyyy');
545 --
546 l_ppsn_override per_assignment_extra_info.aei_information1%type:=null;
547 /*6876894*/
548 l_formula_id NUMBER;
549 l_include_flag VARCHAR2(10);
550 l_email VARCHAR2(100):=null;
551 skip_assignment Exception;
552 l_flag VARCHAR2(2);
553 l_assignment_action_till_apr number;
554 l_assignment_action number; /* knadhan */
555 l_paye_ref number;
559 l_gross_pay_adjust_till_apr number;
556 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
557
558 l_gross_pay_till_apr number;
560 l_bik_prsi_taxable_till_apr number;
561 l_income_levy_till_apr number;
562 l_gross_pay_total_till_apr number;
563 l_balance_value_till_apr NUMBER:=0;
564 l_pre_bal_value_till_apr NUMBER:=0;
565
566 l_gross_pay_frm_may number;
567 l_gross_pay_adjust_frm_may number;
568 l_bik_prsi_taxable_frm_may number;
569 l_income_levy_frm_may number;
570 l_gross_pay_total_frm_may number;
571
572 l_prev_period_service_id NUMBER;
573 l_prev_asg_action_till_apr NUMBER;
574
575 /* 10277535 start */
576 CURSOR csr_asg_empr_location(c_location_id hr_locations.location_id%type)
577 IS
578 SELECT NVL (TRIM (RPAD (hl.ADDRESS_LINE_1, 30)), ' ') address_line1,
579 NVL (TRIM (RPAD (hl.ADDRESS_LINE_2, 30)), ' ') address_line2,
580 NVL (TRIM (RPAD (hl.ADDRESS_LINE_3, 30)), ' ') address_line3,
581 NVL (TRIM (RPAD (hl.LOCATION_CODE, 30)), ' ') Location_name
582 FROM hr_locations hl
583 WHERE hl.location_id =c_location_id;
584 r_asg_empr_location csr_asg_empr_location%ROWTYPE;
585 l_er_address_line1 hr_locations.ADDRESS_LINE_1%TYPE;
586 l_er_address_line2 hr_locations.ADDRESS_LINE_2%TYPE;
587 l_er_address_line3 hr_locations.ADDRESS_LINE_3%TYPE;
588 l_er_loc_name hr_locations.LOCATION_CODE%TYPE;
589
590
591 /* 10277535 end */
592
593 begin
594 hr_utility.set_location('Entering get_eoy_income_details',10);
595
596 vCtr := 0;
597 vXMLTable(vCtr).xmlstring := '<?xml version="1.0" encoding="UTF-8"?>';
598 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ROOT>';
599 vCtr := vCtr +1;
600
601
602 /*6876894*/
603 get_asg_set_details(p_assignment_set_id => p_assignment_set_id
604 ,p_formula_id => l_formula_id
605 ,p_tab_asg_set_amnds => l_tab_asg_set_amnds
606
607 );
608 fnd_file.put_line(fnd_file.LOG,'after get_asg_set_details' );
609
610
611 fnd_file.put_line(fnd_file.LOG,'till date ' || to_date('30/04'||'/'||to_char(cp_end_date,'yyyy'),'dd/mm/yyyy') );
612 for p60 in c_p60_records( cp_start_date,
613 cp_effective_date,
614 cp_end_date,
615 p_business_group_id,
616 p_assignment_set_id,
617 p_payroll_id,
618 p_consolidation_set_id,
619 p_sort_order) LOOP
620
621
622 /*6876894*/
623 fnd_file.put_line(fnd_file.LOG,'assignment_id'||p60.assignment_id);
624 l_gross_pay_till_apr :=0;
625 l_gross_pay_adjust_till_apr :=0;
626 l_bik_prsi_taxable_till_apr :=0;
627 l_income_levy_till_apr :=0;
628 l_gross_pay_total_till_apr :=0;
629 l_balance_value_till_apr :=0;
630
631 l_pre_bal_value_till_apr :=0;
632
633 l_gross_pay_frm_may :=0;
634 l_gross_pay_adjust_frm_may :=0;
635 l_bik_prsi_taxable_frm_may :=0;
636 l_income_levy_frm_may :=0;
637 l_gross_pay_total_frm_may :=0;
638 /* 10277535 start */
639 l_er_address_line1:='';
640 l_er_address_line2:='';
641 l_er_address_line3:='';
642 l_er_loc_name :='';
643 /* 10277535 end */
644 l_flag:='Y';
645 If p_assignment_set_id is not null then
646 l_include_flag := chk_is_asg_in_asg_set(p_assignment_id => p60.assignment_id
647 ,p_formula_id => l_formula_id
648 ,p_tab_asg_set_amnds => l_tab_asg_set_amnds
649 ,p_effective_date => cp_effective_date
650 );
651 fnd_file.put_line(fnd_file.LOG,'l_include_flag'||l_include_flag);
652 if l_include_flag = 'N' then
653 l_flag:='N';
654 end if;
655 end if;
656
657 fnd_file.put_line(fnd_file.LOG,'p60.ppsn' || p60.ppsn);
658 fnd_file.put_line(fnd_file.LOG,'p60.assignment_id' || p60.assignment_id);
659 fnd_file.put_line(fnd_file.LOG,'p60.person_id' || p60.person_id);
660 OPEN cur_assignment_action_till_apr(p60.assignment_id);
661 FETCH cur_assignment_action_till_apr into l_assignment_action_till_apr;
662 CLOSE cur_assignment_action_till_apr;
663
664 fnd_file.put_line(fnd_file.LOG,'l_assignment_action_till_apr'|| l_assignment_action_till_apr);
665 fnd_file.put_line(fnd_file.LOG,'cp_end_date'|| to_char(cp_end_date));
666 /* 10180910 start moved up out of year check of 2009*/
667 OPEN cur_paye_ref(p60.assignment_id,p60.person_id);
668 FETCH cur_paye_ref INTO l_paye_ref;
669 CLOSE cur_paye_ref;
670
671 fnd_file.put_line(fnd_file.LOG,'l_paye_ref'|| l_paye_ref);
672
673 OPEN csr_get_org_email(l_paye_ref);
674 FETCH csr_get_org_email INTO l_email;
675 CLOSE csr_get_org_email;
676
677 fnd_file.put_line(fnd_file.LOG,'l_email'|| l_email);
678 /* 10180910 end */
679 IF to_char(cp_end_date,'YYYY') = '2009' THEN -- 10180910
680 OPEN cur_assignment_action(p60.assignment_id);
681 FETCH cur_assignment_action into l_assignment_action;
682 CLOSE cur_assignment_action;
683
684 fnd_file.put_line(fnd_file.LOG,'l_assignment_action'|| l_assignment_action);
685 /*
686 OPEN cur_paye_ref(p60.assignment_id,p60.person_id);
690 fnd_file.put_line(fnd_file.LOG,'l_paye_ref'|| l_paye_ref);
687 FETCH cur_paye_ref INTO l_paye_ref;
688 CLOSE cur_paye_ref;
689
691
692 OPEN csr_get_org_email(l_paye_ref);
693 FETCH csr_get_org_email INTO l_email;
694 CLOSE csr_get_org_email;
695
696 fnd_file.put_line(fnd_file.LOG,'l_email'|| l_email); */
697 l_ppsn_override:=null;
698 OPEN csr_ppsn_override(p60.assignment_id);
699 FETCH csr_ppsn_override INTO l_ppsn_override;
700 IF csr_ppsn_override%NOTFOUND THEN
701 l_ppsn_override:=null;
702 END IF;
703 CLOSE csr_ppsn_override;
704
705 fnd_file.put_line(fnd_file.LOG,'l_ppsn_override'|| l_ppsn_override);
706
707 OPEN c_get_periods_of_service(p60.person_id,p60.assignment_id,l_paye_ref);
708 FETCH c_get_periods_of_service INTO l_prev_period_service_id;
709 CLOSE c_get_periods_of_service;
710
711 fnd_file.put_line(fnd_file.LOG,'l_prev_period_service_id'|| l_prev_period_service_id);
712 /* 9560108 */
713 --
714 IF l_ppsn_override IS NOT NULL THEN
715 OPEN c_get_max_aact_PPSN(l_prev_period_service_id,l_ppsn_override,p60.person_id);
716 FETCH c_get_max_aact_PPSN INTO l_prev_asg_action_till_apr;
717 CLOSE c_get_max_aact_PPSN;
718 ELSE
719 OPEN c_get_max_aact(l_prev_period_service_id,p60.person_id);
720 FETCH c_get_max_aact INTO l_prev_asg_action_till_apr;
721 CLOSE c_get_max_aact;
722 END IF;
723 --
724
725 fnd_file.put_line(fnd_file.LOG,'l_prev_asg_action_till_apr'|| l_prev_asg_action_till_apr);
726
727 IF l_ppsn_override is null THEN
728 OPEN cur_defined_balance_id('IE Gross Income','_PER_PAYE_REF_YTD');
729
730 ELSE
731 OPEN cur_defined_balance_id('IE Gross Income','_PER_PAYE_REF_PPSN_YTD');
732 END IF;
733
734 FETCH cur_defined_balance_id INTO l_defined_balance_id;
735 CLOSE cur_defined_balance_id;
736
737 IF l_assignment_action_till_apr is not null THEN
738 l_balance_value_till_apr := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
739 l_assignment_action_till_apr,
740 l_paye_ref,
741 null,
742 null,
743 null,
744 null,
745 null);
746 ELSE
747 l_balance_value_till_apr:=0;
748 END IF;
749
750 fnd_file.put_line(fnd_file.LOG,'l_balance_value_till_apr'|| l_balance_value_till_apr);
751 IF l_prev_asg_action_till_apr is not null and l_assignment_action_till_apr is not null THEN
752 l_pre_bal_value_till_apr := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
753 l_prev_asg_action_till_apr,
754 l_paye_ref,
755 null,
756 null,
757 null,
758 null,
759 null);
760 END IF;
761 fnd_file.put_line(fnd_file.LOG,'l_pre_bal_value_till_apr'|| l_pre_bal_value_till_apr);
762 l_balance_value_till_apr:=l_balance_value_till_apr-l_pre_bal_value_till_apr;
763 fnd_file.put_line(fnd_file.LOG,'l_balance_value_till_apr'|| l_balance_value_till_apr);
764 l_gross_pay_till_apr:=l_balance_value_till_apr;
765 fnd_file.put_line(fnd_file.LOG,'l_gross_pay_till_apr'|| l_gross_pay_till_apr);
766 l_defined_balance_id:=null;
767 l_balance_value_till_apr:=0;
768
769 IF l_ppsn_override is null THEN
770 OPEN cur_defined_balance_id('IE Gross Income Adjustment','_PER_PAYE_REF_YTD');
771
772 ELSE
773 OPEN cur_defined_balance_id('IE Gross Income Adjustment','_PER_PAYE_REF_PPSN_YTD');
774 END IF;
775
776 FETCH cur_defined_balance_id INTO l_defined_balance_id;
777 CLOSE cur_defined_balance_id;
778
779 IF l_assignment_action_till_apr is not null THEN
780 l_balance_value_till_apr := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
781 l_assignment_action_till_apr,
782 l_paye_ref,
783 null,
784 null,
785 null,
786 null,
787 null);
788 ELSE
789 l_balance_value_till_apr:=0;
790 END IF;
791
792 fnd_file.put_line(fnd_file.LOG,'l_balance_value_till_apr'|| l_balance_value_till_apr);
793
794 IF l_prev_asg_action_till_apr is not null and l_assignment_action_till_apr is not null THEN
795 l_pre_bal_value_till_apr := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
796 l_prev_asg_action_till_apr,
797 l_paye_ref,
798 null,
799 null,
800 null,
801 null,
802 null);
803 END IF;
804 fnd_file.put_line(fnd_file.LOG,'l_pre_bal_value_till_apr'|| l_pre_bal_value_till_apr);
805 l_balance_value_till_apr:=l_balance_value_till_apr-l_pre_bal_value_till_apr;
806 fnd_file.put_line(fnd_file.LOG,'l_balance_value_till_apr'|| l_balance_value_till_apr);
807
808 l_gross_pay_adjust_till_apr:=l_balance_value_till_apr;
809 fnd_file.put_line(fnd_file.LOG,'l_gross_pay_adjust_till_apr'|| l_gross_pay_adjust_till_apr);
810 l_defined_balance_id:=null;
811 l_balance_value_till_apr:=0;
812
813 IF l_ppsn_override is null THEN
817 OPEN cur_defined_balance_id('IE BIK Taxable and PRSIable Pay','_PER_PAYE_REF_PPSN_YTD');
814 OPEN cur_defined_balance_id('IE BIK Taxable and PRSIable Pay','_PER_PAYE_REF_YTD');
815
816 ELSE
818 END IF;
819
820 FETCH cur_defined_balance_id INTO l_defined_balance_id;
821 CLOSE cur_defined_balance_id;
822
823 IF l_assignment_action_till_apr is not null THEN
824 l_balance_value_till_apr := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id, /* 9123413 replaced l_bik_prsi_taxable_till_apr with l_balance_value_till_apr */
825 l_assignment_action_till_apr,
826 l_paye_ref,
827 null,
828 null,
829 null,
830 null,
831 null);
832 ELSE
833 l_balance_value_till_apr:=0;
834 END IF;
835
836 fnd_file.put_line(fnd_file.LOG,'l_balance_value_till_apr'|| l_balance_value_till_apr);
837
838 IF l_prev_asg_action_till_apr is not null and l_assignment_action_till_apr is not null THEN
839 l_pre_bal_value_till_apr := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
840 l_prev_asg_action_till_apr,
841 l_paye_ref,
842 null,
843 null,
844 null,
845 null,
846 null);
847 END IF;
848 fnd_file.put_line(fnd_file.LOG,'l_pre_bal_value_till_apr'|| l_pre_bal_value_till_apr);
849 l_balance_value_till_apr:=l_balance_value_till_apr-l_pre_bal_value_till_apr;
850 fnd_file.put_line(fnd_file.LOG,'l_balance_value_till_apr'|| l_balance_value_till_apr);
851
852 l_bik_prsi_taxable_till_apr:=l_balance_value_till_apr;
853 fnd_file.put_line(fnd_file.LOG,'l_bik_prsi_taxable_till_apr'|| l_bik_prsi_taxable_till_apr);
854 l_defined_balance_id:=null;
855 l_balance_value_till_apr:=0;
856
857 IF l_ppsn_override is null THEN
858 OPEN cur_defined_balance_id('IE Income Tax Levy','_PER_PAYE_REF_YTD');
859
860 ELSE
861 OPEN cur_defined_balance_id('IE Income Tax Levy','_PER_PAYE_REF_PPSN_YTD');
862 END IF;
863
864 FETCH cur_defined_balance_id INTO l_defined_balance_id;
865 CLOSE cur_defined_balance_id;
866
867 IF l_assignment_action_till_apr is not null THEN
868 l_balance_value_till_apr := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
869 l_assignment_action_till_apr,
870 l_paye_ref,
871 null,
872 null,
873 null,
874 null,
875 null);
876 ELSE
877 l_balance_value_till_apr:=0;
878 END IF;
879
880 fnd_file.put_line(fnd_file.LOG,'l_balance_value_till_apr'|| l_balance_value_till_apr);
881
882 IF l_prev_asg_action_till_apr is not null and l_assignment_action_till_apr is not null THEN
883 l_pre_bal_value_till_apr := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
884 l_prev_asg_action_till_apr,
885 l_paye_ref,
886 null,
887 null,
888 null,
889 null,
890 null);
891 END IF;
892 fnd_file.put_line(fnd_file.LOG,'l_pre_bal_value_till_apr'|| l_pre_bal_value_till_apr);
893 l_balance_value_till_apr:=l_balance_value_till_apr-l_pre_bal_value_till_apr;
894 fnd_file.put_line(fnd_file.LOG,'l_balance_value_till_apr'|| l_balance_value_till_apr);
895
896 l_income_levy_till_apr:=l_balance_value_till_apr;
897 fnd_file.put_line(fnd_file.LOG,'l_income_levy_till_apr'|| l_income_levy_till_apr);
898 l_defined_balance_id:=null;
899 l_balance_value_till_apr:=0;
900
901 /* knadhan the assignment action passed is the till end of year as recaluclated balance is fed in 53 week calculation*/
902 IF l_ppsn_override is null THEN
903 OPEN cur_defined_balance_id('IE Recalculated Levy','_PER_PAYE_REF_YTD');
904
905 ELSE
906 OPEN cur_defined_balance_id('IE Recalculated Levy','_PER_PAYE_REF_PPSN_YTD');
907 END IF;
908
909 FETCH cur_defined_balance_id INTO l_defined_balance_id;
910 CLOSE cur_defined_balance_id;
911
912 IF l_assignment_action_till_apr is not null THEN
913 l_balance_value_till_apr := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
914 l_assignment_action,
915 l_paye_ref,
916 null,
917 null,
918 null,
919 null,
920 null);
921 ELSE
922 l_balance_value_till_apr:=0;
923 END IF;
924
925 fnd_file.put_line(fnd_file.LOG,'l_balance_value_till_apr'|| l_balance_value_till_apr);
926
927 IF l_prev_asg_action_till_apr is not null and l_assignment_action_till_apr is not null THEN
928 l_pre_bal_value_till_apr := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
929 l_prev_asg_action_till_apr,
930 l_paye_ref,
931 null,
932 null,
933 null,
937 fnd_file.put_line(fnd_file.LOG,'l_pre_bal_value_till_apr'|| l_pre_bal_value_till_apr);
934 null,
935 null);
936 END IF;
938 l_balance_value_till_apr:=l_balance_value_till_apr-l_pre_bal_value_till_apr;
939 fnd_file.put_line(fnd_file.LOG,'l_balance_value_till_apr'|| l_balance_value_till_apr);
940 l_income_levy_till_apr:=l_income_levy_till_apr+l_balance_value_till_apr;
941
942 l_gross_pay_total_till_apr :=l_gross_pay_till_apr + l_gross_pay_adjust_till_apr + l_bik_prsi_taxable_till_apr;
943
944 l_gross_pay_frm_may := p60.total_gross_pay - l_gross_pay_total_till_apr;
945 l_income_levy_frm_may := p60.total_income_levy - l_income_levy_till_apr;
946 fnd_file.put_line(fnd_file.LOG,'l_income_levy_till_apr'|| l_income_levy_till_apr);
947
948 fnd_file.put_line(fnd_file.LOG,'p60.total_gross_pay'|| p60.total_gross_pay);
949 fnd_file.put_line(fnd_file.LOG,'l_gross_pay_total_till_apr'|| l_gross_pay_total_till_apr);
950 fnd_file.put_line(fnd_file.LOG,'l_gross_pay_frm_may'|| l_gross_pay_frm_may);
951
952 fnd_file.put_line(fnd_file.LOG,'p60.total_income_levy'|| p60.total_income_levy);
953 fnd_file.put_line(fnd_file.LOG,'l_income_levy_till_apr'|| l_income_levy_till_apr);
954 fnd_file.put_line(fnd_file.LOG,'l_income_levy_frm_may'|| l_income_levy_frm_may);
955 ELSE
956 l_gross_pay_total_till_apr :=0;
957 l_income_levy_till_apr :=0;
958 l_gross_pay_frm_may :=0;
959 l_income_levy_frm_may :=0;
960 fnd_file.put_line(fnd_file.LOG,'entered else for assignment_id' || p60.assignment_id);
961 fnd_file.put_line(fnd_file.LOG,'l_flag' || l_flag);
962 END IF; -- 10180910
963
964 IF(l_flag='Y') THEN
965
966 /* 10277535 start */
967 IF p60.asg_location_id IS NOT NULL THEN
968 fnd_file.put_line(fnd_file.LOG,'P60 Location id not null' || p60.asg_location_id);
969 OPEN csr_asg_empr_location(p60.asg_location_id);
970 FETCH csr_asg_empr_location INTO r_asg_empr_location;
971 CLOSE csr_asg_empr_location;
972
973 l_er_address_line1 := test_XML(r_asg_empr_location.address_line1);
974 l_er_address_line2 := test_XML(r_asg_empr_location.address_line2);
975 l_er_address_line3 := test_XML(r_asg_empr_location.address_line3);
976 l_er_loc_name := test_XML(r_asg_empr_location.Location_name);
977 fnd_file.put_line(fnd_file.LOG,'P60 Address lines set');
978 ELSE
979 l_er_address_line1 := test_XML(p60.address_line1);
980 l_er_address_line2 := test_XML(p60.address_line2);
981 l_er_address_line3 := test_XML(p60.address_line3);
982 l_er_loc_name := test_XML(p60.Location_name);
983 fnd_file.put_line(fnd_file.LOG,'P60 Location id is null');
984 END IF;
985
986 /* 10277535 end */
987 fnd_file.put_line(fnd_file.LOG,'entered if check for assignment_id' || p60.assignment_id);
988 vXMLTable(vCtr).xmlstring := '<EMPLOYEE>';
989 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<SURNAME>'|| test_XML(p60.surname) ||'</SURNAME>';
990 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<FIRST_NAME>'|| test_XML(p60.first_name) ||'</FIRST_NAME>';
991 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<PPSN>'|| p60.ppsn ||'</PPSN>';
992 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<WORKS_NUM>'|| p60.works_num ||'</WORKS_NUM>';
993 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<HIRE_DATE>'|| p60.hire_date ||'</HIRE_DATE>';
994 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<GROSS_INCOME>'|| lpad(trim(to_char(fnd_number.canonical_to_number(nvl(p60.total_gross_pay,0)) ,'9999999')),7,' ') ||'</GROSS_INCOME>';
995 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<LEVY>'|| lpad(trim(to_char(fnd_number.canonical_to_number(nvl( p60.total_income_levy ,0)) ,'999990.99')),8,' ') ||'</LEVY>';
996 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<GROSS_INCOME_TILL_APR>'|| lpad(trim(to_char(fnd_number.canonical_to_number(nvl(l_gross_pay_total_till_apr,0)) ,'9999999')),7,' ') ||'</GROSS_INCOME_TILL_APR>';
997 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<LEVY_TILL_APR>'|| lpad(trim(to_char(fnd_number.canonical_to_number(nvl(l_income_levy_till_apr,0)) ,'999990.99')),8,' ') ||'</LEVY_TILL_APR>';
998 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<GROSS_INCOME_FRM_MAY>'|| lpad(trim(to_char(fnd_number.canonical_to_number(nvl(l_gross_pay_frm_may,0)) ,'9999999')),7,' ') ||'</GROSS_INCOME_FRM_MAY>';
999 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<LEVY_FRM_MAY>'|| lpad(trim(to_char(fnd_number.canonical_to_number(nvl(l_income_levy_frm_may,0)) ,'999990.99')),8,' ') ||'</LEVY_FRM_MAY>';
1000 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ER_NAME>'|| test_XML(p60.Employer_name) ||'</ER_NAME>';
1001 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ADDR_LINE1>'|| l_er_address_line1 ||'</ADDR_LINE1>'; -- 10277535
1002 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ADDR_LINE2>'|| l_er_address_line2 ||'</ADDR_LINE2>'; -- 10277535
1003 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ADDR_LINE3>'|| l_er_address_line3 ||'</ADDR_LINE3>'; -- 10277535
1004 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<LOC_NAME>'|| l_er_loc_name ||'</LOC_NAME>'; -- 10277535
1005 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<EE_ADDR_LINE1>'|| test_XML(p60.emp_Address1) ||'</EE_ADDR_LINE1>'; /* 9160076 9323591*/
1006 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<EE_ADDR_LINE2>'|| test_XML(p60.emp_Address2) ||'</EE_ADDR_LINE2>'; /* 9323591 */
1007 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<EE_ADDR_LINE3>'|| test_XML(p60.emp_County) ||'</EE_ADDR_LINE3>'; /* 9323591 */
1008
1009 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<EMAIL>'||NVL( l_email,'') ||'</EMAIL>';
1010 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ER_NUM>'|| p60.Employer_number ||'</ER_NUM>';
1011 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ER_PHONE>'|| p60.Phone_number ||'</ER_PHONE>';
1012 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<YEAR>'||to_char(cp_end_date,'YYYY') ||'</YEAR>';
1013
1014 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</EMPLOYEE>';
1015 vCtr := vCtr + 1;
1016
1017
1018 END IF;
1019 END LOOP;
1020 vXMLTable(vCtr).xmlstring := '</ROOT>';
1021 end get_eoy_income_details;
1022 procedure populate_eoy_income_details(P_START_DATE IN VARCHAR2 DEFAULT NULL
1023 ,CP_EFFECTIVE_DATE IN VARCHAR2 DEFAULT NULL
1024 ,P_END_DATE IN VARCHAR2 DEFAULT NULL
1025 ,P_BUSINESS_GROUP_ID IN VARCHAR2 DEFAULT NULL
1026 ,P_ASSIGNMENT_SET_ID IN VARCHAR2 DEFAULT NULL
1027 ,P_PAYROLL_ID IN VARCHAR2 DEFAULT NULL
1028 ,P_CONSOLIDATION_SET_ID IN VARCHAR2 DEFAULT NULL
1029 ,P_SORT_ORDER IN VARCHAR2 DEFAULT NULL
1030 ,P_TEMPLATE_NAME IN VARCHAR2
1031 ,P_XML OUT NOCOPY CLOB
1032 ) IS
1033 cp_start_date date;
1034 p_effective_date date;
1035 cp_end_date date;
1036 cp_business_group_id number := to_number(p_business_group_id);
1037 cp_assignment_set_id number := to_number(p_assignment_set_id);
1038 cp_payroll_id number := to_number(p_payroll_id);
1039 cp_consolidation_set_id number := to_number(p_consolidation_set_id);
1040
1041 begin
1042
1043 cp_start_date := fnd_date.canonical_to_date(p_start_date);
1044 p_effective_date := fnd_date.canonical_to_date(cp_effective_date);
1045 cp_end_date := fnd_date.canonical_to_date(p_end_date);
1046 get_eoy_income_details(cp_start_date,p_effective_date,cp_end_date,
1047 cp_business_group_id,cp_assignment_set_id,cp_payroll_id,
1048 cp_consolidation_set_id,p_sort_order);
1049 WritetoCLOB(p_xml);
1050 end populate_eoy_income_details;
1051
1052 -- Fucntion to Convert to Local Caharacter set
1053 -- Bug 4705094
1054 FUNCTION TO_UTF8(str in varchar2 )RETURN VARCHAR2
1055 AS
1056 db_charset varchar2(30);
1057 BEGIN
1058 select value into db_charset
1059 from nls_database_parameters
1060 where parameter = 'NLS_CHARACTERSET';
1061 return convert(str,'UTF8',db_charset);
1062 END;
1063
1064 PROCEDURE WritetoCLOB (p_xml out nocopy clob) IS
1065 l_xfdf_string clob;
1066 l_str1 varchar2(6000);
1067 begin
1068 hr_utility.set_location('Entered Procedure Write to clob ',100);
1069 dbms_lob.createtemporary(p_xml,FALSE,DBMS_LOB.CALL);
1070 dbms_lob.open(p_xml,dbms_lob.lob_readwrite);
1071 if vXMLTable.count > 0 then
1072 FOR ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST LOOP
1073 -- Bug 4705094
1074 l_str1 := TO_UTF8(vXMLTable(ctr_table).xmlString);
1075 dbms_lob.writeAppend( p_xml, length(l_str1), l_str1 );
1076 END LOOP;
1077 end if;
1078 --DBMS_LOB.CREATETEMPORARY(p_xml,TRUE);
1079 --clob_to_blob(l_xfdf_string,p_xml);
1080 hr_utility.set_location('Finished Procedure Write to CLOB ,Before clob to blob ',110);
1081 EXCEPTION
1082 WHEN OTHERS then
1083 HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
1084 HR_UTILITY.RAISE_ERROR;
1085 END WritetoCLOB;
1086 end pay_ie_eoy_incomelevy_report;