[Home] [Help]
PACKAGE BODY: APPS.PAY_IE_CESS_REPORT
Source
1 package body pay_ie_cess_report as
2 /* $Header: pyiecess.pkb 120.9.12020000.2 2012/07/06 10:48:28 vmaripal ship $ */
3 g_package CONSTANT VARCHAR2(30) := 'pay_ie_cess_report.';
4 EOL VARCHAR2(5) := fnd_global.local_chr(10);
5 g_archive_pact NUMBER;
6 g_archive_effective_date DATE;
7 g_archive_start_date DATE;
8 g_archive_end_date DATE;
9
10 g_paye_ref NUMBER;
11
12 FUNCTION test_XML(P_STRING VARCHAR2) RETURN VARCHAR2 AS
13 l_string varchar2(1000);
14
15 FUNCTION replace_xml_symbols(pp_string IN VARCHAR2)
16 RETURN VARCHAR2
17 AS
18
19 ll_string VARCHAR2(1000);
20
21 BEGIN
22
23
24 ll_string := pp_string;
25
26 ll_string := replace(ll_string, '&', '&');
27 ll_string := replace(ll_string, '<', '<');
28 ll_string := replace(ll_string, '>', '>');
29 ll_string := replace(ll_string, '''',''');
30 ll_string := replace(ll_string, '"', '"');
31
32 RETURN ll_string;
33 EXCEPTION when no_data_found then
34 null;
35 END replace_xml_symbols;
36
37 begin
38 l_string := p_string;
39 l_string := replace_xml_symbols(l_string);
40
41 l_string := pay_ie_p35_magtape.test_XML(l_string);
42
43 RETURN l_string;
44 END ;
45
46 FUNCTION c2b( c IN CLOB ) RETURN BLOB
47 -- typecasts CLOB to BLOB (binary conversion)
48 IS
49 pos PLS_INTEGER := 1;
50 buffer RAW( 32767 );
51 res BLOB;
52 lob_len PLS_INTEGER := DBMS_LOB.getLength( c );
53 BEGIN
54 Hr_Utility.set_location('Entering: pay_ie_cess_report.c2b',260);
55 DBMS_LOB.createTemporary( res, TRUE );
56 DBMS_LOB.OPEN( res, DBMS_LOB.LOB_ReadWrite );
57
58
59 LOOP
60 buffer := UTL_RAW.cast_to_raw( DBMS_LOB.SUBSTR( c, 16000, pos ) );
61
62 IF UTL_RAW.LENGTH( buffer ) > 0 THEN
63 DBMS_LOB.writeAppend( res, UTL_RAW.LENGTH( buffer ), buffer );
64 END IF;
65
66 pos := pos + 16000;
67 EXIT WHEN pos > lob_len;
68 END LOOP;
69
70 Hr_Utility.set_location('Leaving: pay_ie_cess_report.c2b',265);
71 RETURN res; -- res is OPEN here
72 END c2b;
73
74 PROCEDURE get_parameters(p_payroll_action_id IN NUMBER,
75 p_token_name IN VARCHAR2,
76 p_token_value OUT nocopy VARCHAR2) IS
77
78 CURSOR csr_parameter_info(p_pact_id NUMBER,
79 p_token CHAR) IS
80 SELECT SUBSTR(legislative_parameters,
81 INSTR(legislative_parameters,p_token)+(LENGTH(p_token)+1),
82 INSTR(legislative_parameters,' ',
83 INSTR(legislative_parameters,p_token))
84 - (INSTR(legislative_parameters,p_token)+LENGTH(p_token))),
85 business_group_id
86 FROM pay_payroll_actions
87 WHERE payroll_action_id = p_pact_id;
88
89 l_business_group_id VARCHAR2(20);
90 l_token_value VARCHAR2(50);
91
92 l_proc VARCHAR2(50) := g_package || 'get_parameters';
93
94 BEGIN
95
96 hr_utility.set_location('Entering ' || l_proc,10);
97
98 hr_utility.set_location('Step ' || l_proc,20);
99 hr_utility.set_location('p_token_name = ' || p_token_name,20);
100
101 OPEN csr_parameter_info(p_payroll_action_id,
102 p_token_name);
103
104 FETCH csr_parameter_info INTO l_token_value,
105 l_business_group_id;
106
107 CLOSE csr_parameter_info;
108
109 IF p_token_name = 'BG_ID'
110
111 THEN
112
113 p_token_value := l_business_group_id;
114
115 ELSE
116
117 p_token_value := l_token_value;
118
119 END IF;
120
121 hr_utility.set_location('l_token_value = ' || l_token_value,20);
122 hr_utility.set_location('Leaving ' || l_proc,30);
123
124 END get_parameters;
125
126
127
128 PROCEDURE get_termination_date (p_action_context_id IN NUMBER,
129 p_assignment_id IN NUMBER,
130 p_person_id IN NUMBER,
131 p_date_earned IN DATE,
132 p_termination_date OUT NOCOPY DATE,
133 p_supp_pymt_date OUT NOCOPY DATE,
134 p_supp_flag OUT NOCOPY VARCHAR2,
135 p_deceased_flag OUT NOCOPY VARCHAR2
136 ) is
137
138 CURSOR cur_service_leave IS
139 select decode(ppos.leaving_reason, 'D','Y','N'),
140 ppos.actual_termination_date
141 from per_periods_of_service ppos
142 where ppos.person_id = p_person_id
143 and ppos.period_of_service_id = (select max(paf.period_of_service_id)
144 from per_all_assignments_f paf,
145 pay_assignment_actions paa,
146 pay_action_interlocks pai
147 where pai.locking_action_id = p_action_context_id
148 and pai.locked_action_id = paa.assignment_action_id
149 and paa.action_status = 'C'
150 and paa.assignment_id = paf.assignment_id
151 );
152
153 CURSOR cur_max_end_date IS
154 SELECT max(paaf.effective_end_date)
155 FROM per_all_assignments_f paaf,
156 pay_all_payrolls_f papf,
157 hr_soft_coding_keyflex scl
158 WHERE paaf.person_id = p_person_id
159 AND paaf.payroll_id = papf.payroll_id
160 AND papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
161 AND scl.segment4 = to_char(g_paye_ref)
162 AND paaf.assignment_status_type_id in
163 (SELECT ast.assignment_status_type_id
164 FROM per_assignment_status_types ast
165 WHERE ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
166 )
167 AND paaf.effective_end_date between g_archive_start_date and g_archive_end_date;
168
169 CURSOR cur_get_asg_end_date IS
170 SELECT max(effective_end_date)
171 FROM per_all_assignments_f paaf
172 WHERE paaf.assignment_id = p_assignment_id
173 AND paaf.assignment_status_type_id in
174 (SELECT ast.assignment_status_type_id
175 FROM per_assignment_status_types ast
176 WHERE ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
177 );
178
179
180
181 cursor cur_supp_run is
182 select fnd_date.canonical_to_date(act_inf.action_information3)
183 from pay_assignment_actions paa_run,
184 pay_action_interlocks pai,
185 pay_assignment_actions paa,
186 pay_payroll_actions ppa,
187 pay_action_information act_inf
188 where ppa.payroll_action_id = paa.payroll_action_id
189 and ppa.report_type = 'IE_CESSATION'
190 and ppa.report_qualifier = 'IE'
191 and ppa.action_type = 'X'
192 and paa.assignment_action_id = act_inf.action_context_id
193 and act_inf.action_information_category = 'IE CESS INFORMATION'
194 and act_inf.action_context_type = 'AAP'
195 and ppa.payroll_action_id <> g_archive_pact
196 and paa.assignment_action_id = pai.locking_action_id
197 and paa.source_action_id is NULL
198 and pai.locked_action_id = paa_run.assignment_action_id
199 and paa_run.assignment_id = p_assignment_id
200 and paa_run.action_status = 'C'
201 and paa.action_status = 'C';
202
203
204 l_proc CONSTANT VARCHAR2(50):= g_package||'get_termination_date';
205 l_deceased_flg VARCHAR2(1);
206 l_termination_date DATE;
207 l_start_date DATE;
208 l_end_date DATE;
209 l_asg_end_date DATE;
210 l_last_end_date DATE;
211
212 BEGIN
213 hr_utility.set_location('Entering ' || l_proc,20);
214 hr_utility.set_location('Step ' || l_proc,20);
215 hr_utility.set_location('p_action_context_id = ' || p_action_context_id,20);
216 hr_utility.set_location('p_assignment_id = ' || p_assignment_id,20);
217 hr_utility.set_location('p_person_id = ' || p_person_id,20);
218 hr_utility.set_location('g_paye_ref = ' || g_paye_ref,20);
219 hr_utility.set_location('p_termination_date = ' || p_termination_date,20);
220
221
222
223 -- get deceased flag, date of leaving
224 OPEN cur_service_leave;
225 FETCH cur_service_leave INTO l_deceased_flg,l_termination_date;
226 CLOSE cur_service_leave;
227
228
229 p_deceased_flag := l_deceased_flg;
230
231 l_asg_end_date := l_termination_date;
232 hr_utility.set_location('l_termination_date = ' || l_termination_date,21);
233
234 /* If employee is not terminated using end employment check for asg end date */
235 IF l_termination_date IS NULL THEN
236 /* Get End Date of Employement with Employer */
237 OPEN cur_max_end_date;
238 FETCH cur_max_end_date INTO l_termination_date;
239 CLOSE cur_max_end_date;
240 /* Get End Date of Assignment */
241 OPEN cur_get_asg_end_date;
242 FETCH cur_get_asg_end_date INTO l_asg_end_date;
243 CLOSE cur_get_asg_end_date;
244 END IF;
245 hr_utility.set_location('l_termination_date = ' || l_termination_date,22);
246 p_termination_date := l_termination_date;
247 OPEN cur_supp_run;
248 FETCH cur_supp_run INTO l_last_end_date;
249 hr_utility.set_location('l_last_end_date = '|| l_last_end_date,20);
250 IF l_last_end_date IS NOT NULL THEN
251 p_supp_pymt_date := p_date_earned;
252 p_supp_flag:= 'Y';
253 p_termination_date := l_last_end_date;
254 ELSE
255 p_supp_flag:= 'N';
256 p_supp_pymt_date :=null;
257 END IF;
258 END get_termination_date;
259
260
261 PROCEDURE archive_cess_info(p_action_context_id IN NUMBER,
262 p_assignment_id IN NUMBER,
263 p_payroll_id IN NUMBER,
264 p_date_earned IN DATE,
265 p_child_run_ass_act_id IN NUMBER,
266 p_supp_flag IN VARCHAR2, -- 5383808
267 p_person_id IN NUMBER,
268 p_termination_date in DATE, -- 5383808
269 p_child_pay_action IN NUMBER,
270 -- p_source_id IN NUMBER,
271 p_supp_pymt_date IN DATE,
272 p_deceased_flag IN VARCHAR2,
273 p_last_cess_action IN NUMBER,
274 p_prev_src_id IN NUMBER
275 ) -- 5383808
276 IS
277 l_action_info_id NUMBER(15);
278 l_proc CONSTANT VARCHAR2(50):= g_package||'archive_cess_info';
279 l_ovn NUMBER;
280 l_deceased_flg VARCHAR2(1);
281 l_termination_date DATE;
282 l_supp_flg VARCHAR2(1);
283 l_supp_pymt_date DATE;
284
285
286 cursor cur_defined_balance_id (c_balance_name pay_balance_types.balance_name%type
287 ,c_dimension_name pay_balance_dimensions.database_item_suffix%type) is
288 select pdb.defined_balance_id
289
290 from pay_defined_balances pdb
291 ,pay_balance_dimensions pbd
292 ,pay_balance_types pbt
293
294 WHERE pbt.balance_name=c_balance_name
295 AND pbt.balance_type_id=pdb.balance_type_id
296 and pbd.database_item_suffix=c_dimension_name
297 and pbd.balance_dimension_id=pdb.balance_dimension_id
298 and pbt.legislation_code='IE'
299 and pdb.legislation_code='IE';
300
301 /* CURSOR get_last_source_id is
302 select source_id from
303 pay_action_information pai,
304 pay_assignment_actions paa,
305 pay_payroll_actions ppa,
306 pay_assignment_actions paa1
307 where paa.assignment_action_id = p_last_cess_action
308 and ppa.payroll_action_id=paa.payroll_action_id
309 and ppa.payroll_action_id=paa1.payroll_action_id
310 and paa1.assignment_id=p_assignment_id
311 and paa1.assignment_action_id = pai.action_context_id
312 and pai.action_information_category='IE CESS INFORMATION'
313 --order by source_id desc
314 ;
315 */
316 /*8615992 */
317 CURSOR get_last_source_id is
318 select source_id from
319 pay_action_information pai,
320 pay_assignment_actions paa
321 where paa.assignment_action_id = p_last_cess_action
322 and paa.assignment_action_id = pai.action_context_id
323 and pai.action_information_category='IE CESS INFORMATION'
324 and pai.action_context_type = 'AAP'
325 ;
326
327 /* CURSOR get_last_source_id_apr_09 is
328 select source_id , ppa.effective_date from
329 pay_action_information pai,
330 pay_assignment_actions paa,
331 pay_payroll_actions ppa,
332 pay_assignment_actions paa1
333 where paa.assignment_action_id = p_last_cess_action
334 and ppa.payroll_action_id=paa.payroll_action_id
335 and ppa.payroll_action_id=paa1.payroll_action_id
336 and paa1.assignment_id=p_assignment_id
337 and paa1.assignment_action_id = pai.action_context_id
338 and pai.action_information_category='IE CESS INFORMATION';
339 */
340 /*8615992 */
341 CURSOR get_last_source_id_apr_09 is
342 select source_id , ppa.effective_date from
343 pay_action_information pai,
344 pay_assignment_actions paa,
345 pay_payroll_actions ppa
346 where paa.assignment_action_id = p_last_cess_action
347 and ppa.payroll_action_id=paa.payroll_action_id
348 and paa.assignment_action_id = pai.action_context_id
349 and pai.action_information_category='IE CESS INFORMATION'
350 and pai.action_context_type = 'AAP';
351
352
353 CURSOR get_asg_action_eff_date(c_assignment_action_id pay_assignment_actions.assignment_action_id%type) is
354 select ppa.effective_date
355 from pay_assignment_actions paa,
356 pay_payroll_actions ppa
357 where paa.assignment_action_id=c_assignment_action_id
358 and ppa.payroll_action_id=paa.payroll_action_id;
359
360
361 cursor c_employee_details(cp_assignment_id in number
362 , cp_curr_eff_date in date
363 ) is
364 select ppf.last_name surname,
365 ppf.first_name first_name,
366 ppf.national_identifier PPSN,
367 paf.assignment_number works_no,
368 pps.date_start hire_date
369 from per_assignments_f paf,
370 per_all_people_f ppf,
371 per_periods_of_service pps
372 where paf.person_id = ppf.person_id
373 and paf.assignment_id = cp_assignment_id
374 and cp_curr_eff_date between paf.effective_start_date
375 and paf.effective_end_date
376 and cp_curr_eff_date between ppf.effective_start_date
377 and ppf.effective_end_date
378 and pps.person_id = ppf.person_id
379 and pps.date_start = (select max(pps1.date_start)
380 from per_periods_of_service pps1
381 where pps1.person_id = paf.person_id
382 and pps1.date_start <= cp_curr_eff_date);
383
384 c_employee_details_rec c_employee_details%ROWTYPE;
385 /*8615992 */
386 CURSOR cur_assignment_action_apr_09(c_ppsn varchar2) is
387 SELECT fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
388 paa.assignment_action_id),16))
389 FROM pay_assignment_actions paa
390 ,pay_payroll_actions ppa
391 -- ,pay_assignment_actions paa1
392 WHERE ((c_ppsn is null and paa.assignment_id=p_assignment_id) OR(c_ppsn is not null
393 and paa.assignment_id in (select paaf.assignment_id
394 from per_all_assignments_f paaf, per_assignment_extra_info paei
395 where paaf.person_id = p_person_id
396 and paaf.assignment_id=paei.assignment_id
397 and paei.information_type = 'IE_ASG_OVERRIDE'
398 and paei.aei_information1 = c_ppsn --'314678745T'
399 ))) /* knadhan12 */
400 AND paa.payroll_action_id=ppa.payroll_action_id
401 AND ppa.action_type in ('Q','B','R','I','V')
402 AND ppa.action_status ='C'
403 AND paa.source_action_id is null
404 AND ppa.effective_date<= to_date('30/04/2009','dd/mm/yyyy');
405 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
406 l_balance_value NUMBER:=0;
407 l_balance_value1 NUMBER:=0;
408 /* 8615992 */
409 l_assignment_action_apr_09 pay_assignment_actions.assignment_action_id%type;
410 l_balance_value_apr_09 NUMBER:=0;
411 l_balance_value1_apr_09 NUMBER:=0;
412 l_cess_last_bal_value NUMBER :=0;
413 l_cess_last_bal_value_apr_09 NUMBER :=0;
414 l_prev_source_id_apr_09 number;
415 l_payroll_effective_date date;
416 l_action_effective_date date;
417 l_child_action_eff_date date;
418
419 l_gross_pay number:=0;
420 l_gross_pay_adjust number:=0;
421 l_bik_prsi_taxable number:=0;
422 l_income_levy number:=0;
423 l_gross_pay_total number:=0;
424
425 l_gross_pay_apr_09 number:=0;
426 l_gross_pay_adjust_apr_09 number:=0;
427 l_bik_prsi_taxable_apr_09 number:=0;
428 l_income_levy_apr_09 number:=0;
429 l_gross_pay_total_apr_09 number:=0;
430 l_prev_source_id number;
431
432 l_usc number:=0;
433 l_court_order number:=0;
434 l_gross_uscable number:=0;
435 l_court_order_child_port number:=0; --11076169
436 l_prsa_er_contribution number:=0; --13013273
437
438 CURSOR csr_get_org_tax_address(g_paye_ref number
439 ) IS
440 SELECT
441 hrl.address_line_1 employer_tax_addr1,
442 hrl.address_line_2 employer_tax_addr2,
443 hrl.address_line_3 employer_tax_addr3,
444 org_info.org_information2 employer_no,
445 hrl.telephone_number_1 employer_tax_ref_phone,
446 org_all.name employer_tax_rep_name,
447 org_info1.org_information3 email /* knadhan */
448
449
450 FROM hr_all_organization_units org_all
451 ,hr_organization_information org_info
452 ,hr_locations_all hrl
453 ,hr_organization_information org_info1
454
455 WHERE org_info.organization_id = org_all.organization_id
456 AND org_info.org_information_context = 'IE_EMPLOYER_INFO' --for migration changes 4369280
457 AND org_all.location_id = hrl.location_id (+)
458 AND org_info1.org_information_context (+) = 'ORG_CONTACT_DETAILS'
459 AND org_info1.org_information1 (+) ='EMAIL'
460 AND org_info.organization_id = g_paye_ref
461 AND org_all.organization_id = org_info1.organization_id (+)
462 ;
463
464 csr_get_org_tax_address_rec csr_get_org_tax_address%ROWTYPE;
465 /* knadhan */
466
467 cursor csr_ppsn_override(p_asg_id number)
468 is
469 select aei_information1 PPSN_OVERRIDE
470 from per_assignment_extra_info
471 where assignment_id = p_asg_id
472 and aei_information_category = 'IE_ASG_OVERRIDE';
473
474 l_ppsn_override per_assignment_extra_info.aei_information1%type:=null;
475
476
477 BEGIN
478 --
479 hr_utility.set_location('Entering ' || l_proc,20);
480 hr_utility.set_location('Step ' || l_proc,20);
481 hr_utility.set_location('p_action_context_id = ' || p_action_context_id,20);
482 hr_utility.set_location('p_assignment_id = ' || p_assignment_id,20);
483 hr_utility.set_location('p_payroll_id = ' || p_payroll_id,20);
484 hr_utility.set_location('p_date_earned = ' || p_date_earned,20);
485 hr_utility.set_location('p_child_run_ass_act_id = ' || p_child_run_ass_act_id,20);
486 hr_utility.set_location('p_supp_flag = ' || p_supp_flag,20);
487 hr_utility.set_location('p_person_id = ' || p_person_id,20);
488 hr_utility.set_location('p_termination_date = ' || p_termination_date,20);
489 hr_utility.set_location('p_child_pay_action = ' || p_child_pay_action,20);
490 hr_utility.set_location('p_supp_pymt_date = ' || p_supp_pymt_date,20);
491 hr_utility.set_location('p_deceased_flag = ' || p_deceased_flag,20);
492 hr_utility.set_location('p_last_cess_action = ' || p_last_cess_action,20);
493 hr_utility.set_location('p_prev_src_id = ' || p_prev_src_id,20);
494 hr_utility.set_location('g_paye_ref = ' || g_paye_ref,20);
495
496 hr_utility.set_location('before PPSN cursor l_ppsn_override = ' || l_ppsn_override,20);
497 OPEN csr_ppsn_override(p_assignment_id);
498 FETCH csr_ppsn_override INTO l_ppsn_override;
499 IF csr_ppsn_override%NOTFOUND THEN
500 l_ppsn_override:=null;
501 END IF;
502
503 CLOSE csr_ppsn_override;
504 hr_utility.set_location('after PPSN cursor l_ppsn_override = ' || l_ppsn_override,20);
505
506
507 l_supp_flg := p_supp_flag;
508 l_supp_pymt_date := p_supp_pymt_date;
509 l_termination_date := p_termination_date;
510 hr_utility.set_location('supplementary flag = '||l_supp_flg,20);
511 hr_utility.set_location('supplementary date = '||l_supp_pymt_date,20);
512
513 IF l_ppsn_override is null THEN
514 /* OPEN cur_defined_balance_id('IE Gross Income','_PER_PAYE_REF_YTD'); */
515 OPEN cur_defined_balance_id('IE USCable Pay','_PER_PAYE_REF_YTD'); -- bug 11908300
516
517 hr_utility.set_location(' balance type _PER_PAYE_REF_YTD' ,30);
518
519 ELSE
520 /* OPEN cur_defined_balance_id('IE Gross Income','_PER_PAYE_REF_PPSN_YTD'); */
521 OPEN cur_defined_balance_id('IE USCable Pay','_PER_PAYE_REF_PPSN_YTD'); -- bug 11908300
522 hr_utility.set_location(' balance type _PER_PAYE_REF_PPSN_YTD' ,30);
523 END IF;
524
525 FETCH cur_defined_balance_id INTO l_defined_balance_id;
526 CLOSE cur_defined_balance_id;
527
528 hr_utility.set_location(' l_defined_balance_id' || l_defined_balance_id,30);
529 hr_utility.set_location(' l_balance_value' || l_balance_value,30);
530 IF (p_child_run_ass_act_id IS NOT NULL) THEN /* 8615992 */
531 l_balance_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
532 p_child_run_ass_act_id,
533 g_paye_ref,
534 null,
535 null,
536 null,
537 null,
538 null);
539 ELSE
540 l_balance_value:=0;
541 END IF;
542 hr_utility.set_location(' l_balance_value' || l_balance_value,30);
543 IF (nvl(p_supp_flag,'N') = 'N') AND (p_last_cess_action IS NOT NULL) THEN
544 l_cess_last_bal_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
545 p_prev_src_id,
546 g_paye_ref,
547 null,
548 null,
549 null,
550 null,
551 null);
552 hr_utility.set_location(' l_cess_last_bal_value' || l_cess_last_bal_value,30);
553 l_balance_value:=l_balance_value - l_cess_last_bal_value;
554 END IF;
555
556 IF p_supp_flag ='Y' THEN
557
558 OPEN get_last_source_id;
559 FETCH get_last_source_id into l_prev_source_id;
560 CLOSE get_last_source_id;
561
562 hr_utility.set_location(' l_prev_source_id' || l_prev_source_id,30);
563 IF l_prev_source_id IS NOT NULL THEN /* 9337590 */
564 l_balance_value1 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
565 l_prev_source_id,
566 g_paye_ref,
567 null,
568 null,
569 null,
570 null,
571 null);
572 hr_utility.set_location(' l_balance_value1' || l_balance_value1,30);
573 END IF;
574 ELSE
575 l_balance_value1:=0;
576 hr_utility.set_location(' l_balance_value1' || l_balance_value1,30);
577 END IF;
578 l_gross_pay := l_balance_value - l_balance_value1;
579
580 hr_utility.set_location(' l_gross_pay' || l_gross_pay,30);
581
582 l_defined_balance_id:=null;
583 l_balance_value:=0;
584 l_balance_value1:=0;
585
586
587 IF l_ppsn_override is null THEN
588 OPEN cur_defined_balance_id('IE Gross Income Adjustment','_PER_PAYE_REF_YTD');
589 hr_utility.set_location(' balance type _PER_PAYE_REF_YTD' ,30);
590
591 ELSE
592 OPEN cur_defined_balance_id('IE Gross Income Adjustment','_PER_PAYE_REF_PPSN_YTD');
593 hr_utility.set_location(' balance type _PER_PAYE_REF_PPSN_YTD' ,30);
594 END IF;
595
596
597 FETCH cur_defined_balance_id INTO l_defined_balance_id;
598 CLOSE cur_defined_balance_id;
599
600 hr_utility.set_location(' l_balance_value' || l_balance_value,40);
601 IF (p_child_run_ass_act_id IS NOT NULL) THEN
602 l_balance_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
603 p_child_run_ass_act_id,
604 g_paye_ref,
605 null,
606 null,
607 null,
608 null,
609 null);
610 ELSE
611 l_balance_value:=0;
612 END IF;
613 hr_utility.set_location(' l_balance_value' || l_balance_value,40);
614 IF (nvl(p_supp_flag,'N') = 'N') AND (p_last_cess_action IS NOT NULL) THEN
615 l_cess_last_bal_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
616 p_prev_src_id,
617 g_paye_ref,
618 null,
619 null,
620 null,
621 null,
622 null);
623 hr_utility.set_location(' l_cess_last_bal_value' || l_cess_last_bal_value,40);
624 l_balance_value:=l_balance_value - l_cess_last_bal_value;
625 END IF;
626
627 IF p_supp_flag ='Y' THEN
628
629 OPEN get_last_source_id;
630 FETCH get_last_source_id into l_prev_source_id;
631 CLOSE get_last_source_id;
632 hr_utility.set_location(' l_prev_source_id' || l_prev_source_id,40);
633 IF l_prev_source_id IS NOT NULL THEN /* 9337590 */
634 l_balance_value1 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
635 l_prev_source_id,
636 g_paye_ref,
637 null,
638 null,
639 null,
640 null,
641 null);
642 hr_utility.set_location(' l_balance_value1' || l_balance_value1,40);
643 END IF;
644 ELSE
645 l_balance_value1:=0;
646 hr_utility.set_location(' l_balance_value1' || l_balance_value1,40);
647 END IF;
648 l_gross_pay_adjust := l_balance_value - l_balance_value1;
649
650 hr_utility.set_location(' l_gross_pay_adjust' || l_gross_pay_adjust,30);
651 l_defined_balance_id:=null;
652 l_balance_value:=0;
653 l_balance_value1:=0;
654
655
656 l_defined_balance_id:=null;
657
658 IF l_ppsn_override is null THEN
659 OPEN cur_defined_balance_id('IE BIK Taxable and PRSIable Pay','_PER_PAYE_REF_YTD');
660 hr_utility.set_location(' balance type _PER_PAYE_REF_YTD' ,30);
661
662 ELSE
663 OPEN cur_defined_balance_id('IE BIK Taxable and PRSIable Pay','_PER_PAYE_REF_PPSN_YTD');
664 hr_utility.set_location(' balance type _PER_PAYE_REF_PPSN_YTD' ,30);
665 END IF;
666
667
668 FETCH cur_defined_balance_id INTO l_defined_balance_id;
669 CLOSE cur_defined_balance_id;
670 hr_utility.set_location(' l_balance_value' || l_balance_value,50);
671 IF (p_child_run_ass_act_id IS NOT NULL) THEN
672 l_balance_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
673 p_child_run_ass_act_id,
674 g_paye_ref,
675 null,
676 null,
677 null,
678 null,
679 null);
680 ELSE
681 l_balance_value:=0;
682 END IF;
683 hr_utility.set_location(' l_balance_value' || l_balance_value,50);
684 IF (nvl(p_supp_flag,'N') = 'N') AND (p_last_cess_action IS NOT NULL) THEN
685 l_cess_last_bal_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
686 p_prev_src_id,
687 g_paye_ref,
688 null,
689 null,
690 null,
691 null,
692 null);
693 hr_utility.set_location(' l_cess_last_bal_value' || l_cess_last_bal_value,50);
694 l_balance_value:=l_balance_value - l_cess_last_bal_value;
695 END IF;
696
697 IF p_supp_flag ='Y' THEN
698
699 OPEN get_last_source_id;
700 FETCH get_last_source_id into l_prev_source_id;
701 CLOSE get_last_source_id;
702 hr_utility.set_location(' l_prev_source_id' || l_prev_source_id,50);
703 IF l_prev_source_id IS NOT NULL THEN /* 9337590 */
704 l_balance_value1 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
705 l_prev_source_id,
706 g_paye_ref,
707 null,
708 null,
709 null,
710 null,
711 null);
712 hr_utility.set_location(' l_balance_value1' || l_balance_value1,50);
713 END IF;
714 ELSE
715 l_balance_value1:=0;
716 hr_utility.set_location(' l_balance_value1' || l_balance_value1,50);
717 END IF;
718 l_bik_prsi_taxable := l_balance_value - l_balance_value1;
719
720 hr_utility.set_location(' l_bik_prsi_taxable' || l_bik_prsi_taxable,50);
721
722 l_defined_balance_id:=null;
723 l_balance_value:=0;
724 l_balance_value1:=0;
725
726 l_defined_balance_id:=null;
727
728 IF l_ppsn_override is null THEN
729 OPEN cur_defined_balance_id('IE Income Tax Levy','_PER_PAYE_REF_YTD');
730
731 hr_utility.set_location(' balance type _PER_PAYE_REF_YTD' ,30);
732
733 ELSE
734 OPEN cur_defined_balance_id('IE Income Tax Levy','_PER_PAYE_REF_PPSN_YTD');
735 hr_utility.set_location(' balance type _PER_PAYE_REF_PPSN_YTD' ,30);
736 END IF;
737
738
739 FETCH cur_defined_balance_id INTO l_defined_balance_id;
740 CLOSE cur_defined_balance_id;
741
742 hr_utility.set_location(' l_balance_value' || l_balance_value,60);
743 IF (p_child_run_ass_act_id IS NOT NULL) THEN
744 l_balance_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
745 p_child_run_ass_act_id,
746 g_paye_ref,
747 null,
748 null,
749 null,
750 null,
751 null);
752 ELSE
753 l_balance_value:=0;
754 END IF;
755 hr_utility.set_location(' l_balance_value' || l_balance_value,60);
756 IF (nvl(p_supp_flag,'N') = 'N') AND (p_last_cess_action IS NOT NULL) THEN
757 l_cess_last_bal_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
758 p_prev_src_id,
759 g_paye_ref,
760 null,
761 null,
762 null,
763 null,
764 null);
765 hr_utility.set_location(' l_cess_last_bal_value' || l_cess_last_bal_value,60);
766 l_balance_value:=l_balance_value - l_cess_last_bal_value;
767 END IF;
768
769 IF p_supp_flag ='Y' THEN
770
771 OPEN get_last_source_id;
772 FETCH get_last_source_id into l_prev_source_id;
773 CLOSE get_last_source_id;
774 hr_utility.set_location(' l_prev_source_id' || l_prev_source_id,60);
775 IF l_prev_source_id IS NOT NULL THEN /* 9337590 */
776 l_balance_value1 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
777 l_prev_source_id,
778 g_paye_ref,
779 null,
780 null,
781 null,
782 null,
783 null);
784 hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
785 END IF;
786 ELSE
787 l_balance_value1:=0;
788 hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
789 END IF;
790 l_income_levy := l_balance_value - l_balance_value1;
791 hr_utility.set_location(' l_income_levy' || l_income_levy,30);
792 /* ---------------------------------------------------------------------------------------- */
793 /* 8615992 fetch the till april balance and make the split accordingly */
794
795 OPEN get_asg_action_eff_date(p_child_run_ass_act_id);
796 FETCH get_asg_action_eff_date INTO l_child_action_eff_date;
797 CLOSE get_asg_action_eff_date;
798 hr_utility.set_location(' l_child_action_eff_date' || l_child_action_eff_date,60);
799 IF l_child_action_eff_date is not null and (to_char(l_child_action_eff_date,'yyyy') = '2009')
800 THEN
801 OPEN cur_assignment_action_apr_09(l_ppsn_override);
802 FETCH cur_assignment_action_apr_09 into l_assignment_action_apr_09;
803 CLOSE cur_assignment_action_apr_09;
804
805 hr_utility.set_location(' l_assignment_action_apr_09 '||l_assignment_action_apr_09 ,30);
806
807 /* gross pay */
808 IF l_ppsn_override is null THEN
809 OPEN cur_defined_balance_id('IE Gross Income','_PER_PAYE_REF_YTD');
810 hr_utility.set_location(' balance type _PER_PAYE_REF_YTD' ,30);
811 ELSE
812 OPEN cur_defined_balance_id('IE Gross Income','_PER_PAYE_REF_PPSN_YTD');
813 hr_utility.set_location(' balance type _PER_PAYE_REF_PPSN_YTD' ,30);
814 END IF;
815
816 FETCH cur_defined_balance_id INTO l_defined_balance_id;
817 CLOSE cur_defined_balance_id;
818
819 hr_utility.set_location(' l_defined_balance_id' || l_defined_balance_id,30);
820 hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,30);
821 IF l_assignment_action_apr_09 is not null THEN
822 l_balance_value_apr_09 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
823 l_assignment_action_apr_09,
824 g_paye_ref,
825 null,
826 null,
827 null,
828 null,
829 null);
830 END IF;
831 hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,30);
832 IF (nvl(p_supp_flag,'N') = 'N') AND (p_last_cess_action IS NOT NULL) and l_assignment_action_apr_09 is not NULL THEN
833 OPEN get_asg_action_eff_date(p_prev_src_id);
834 FETCH get_asg_action_eff_date INTO l_action_effective_date;
835 CLOSE get_asg_action_eff_date;
836 hr_utility.set_location('l_action_effective_date = ' || l_action_effective_date,40);
837 IF l_action_effective_date is not null and l_action_effective_date <= to_date('30/04/2009','dd/mm/yyyy') THEN
838 l_cess_last_bal_value_apr_09 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
839 p_prev_src_id,
840 g_paye_ref,
841 null,
842 null,
843 null,
844 null,
845 null);
846 hr_utility.set_location(' l_cess_last_bal_value_apr_09' || l_cess_last_bal_value_apr_09,30);
847 ELSE
848 l_cess_last_bal_value_apr_09 :=0;
849 END IF;
850 l_balance_value_apr_09:=l_balance_value_apr_09 - l_cess_last_bal_value_apr_09;
851 END IF;
852 IF p_supp_flag ='Y' THEN
853
854
855 OPEN get_last_source_id_apr_09;
856 FETCH get_last_source_id_apr_09 into l_prev_source_id_apr_09,l_payroll_effective_date;
857 CLOSE get_last_source_id_apr_09;
858
859 hr_utility.set_location(' l_prev_source_id_apr_09' || l_prev_source_id_apr_09,40);
860 hr_utility.set_location('l_payroll_effective_date = ' || l_payroll_effective_date,40);
861 IF l_prev_source_id_apr_09 is not null and l_assignment_action_apr_09 is not null and l_payroll_effective_date <= to_date('30/04/2009','dd/mm/yyyy') THEN
862 l_balance_value1_apr_09 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
863 l_prev_source_id_apr_09,
864 g_paye_ref,
865 null,
866 null,
867 null,
868 null,
869 null);
870
871 hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,40);
872
873 ELSE
874 l_balance_value_apr_09:=0;
875 l_balance_value1_apr_09:=0;
876 END IF;
877
878 ELSE
879 l_balance_value1_apr_09:=0;
880 hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,40);
881 END IF;
882
883 hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,100);
884 hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,100);
885
886 l_gross_pay_apr_09 := nvl(l_balance_value_apr_09,0) - nvl(l_balance_value1_apr_09,0);
887 hr_utility.set_location(' l_gross_pay' || l_gross_pay,40);
888 hr_utility.set_location(' l_gross_pay_apr_09' || l_gross_pay_apr_09,40);
889 l_gross_pay := l_gross_pay - l_gross_pay_apr_09;
890 hr_utility.set_location(' l_gross_pay from may 2009 ' || l_gross_pay,40);
891 l_defined_balance_id:=null;
892 l_balance_value_apr_09:=0;
893 l_balance_value1_apr_09:=0;
894
895
896 /* gross pay adjust */
897 IF l_ppsn_override is null THEN
898 OPEN cur_defined_balance_id('IE Gross Income Adjustment','_PER_PAYE_REF_YTD');
899 hr_utility.set_location(' balance type _PER_PAYE_REF_YTD' ,30);
900 ELSE
901 OPEN cur_defined_balance_id('IE Gross Income Adjustment','_PER_PAYE_REF_PPSN_YTD');
902 hr_utility.set_location(' balance type _PER_PAYE_REF_PPSN_YTD' ,30);
903 END IF;
904
905 FETCH cur_defined_balance_id INTO l_defined_balance_id;
906 CLOSE cur_defined_balance_id;
907
908 hr_utility.set_location(' l_defined_balance_id' || l_defined_balance_id,30);
909 hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,30);
910 IF l_assignment_action_apr_09 is not null THEN
911 l_balance_value_apr_09 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
912 l_assignment_action_apr_09,
913 g_paye_ref,
914 null,
915 null,
916 null,
917 null,
918 null);
919 END IF;
920 hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,30);
921 IF (nvl(p_supp_flag,'N') = 'N') AND (p_last_cess_action IS NOT NULL) and l_assignment_action_apr_09 is not NULL THEN
922 OPEN get_asg_action_eff_date(p_prev_src_id);
923 FETCH get_asg_action_eff_date INTO l_action_effective_date;
924 CLOSE get_asg_action_eff_date;
925 hr_utility.set_location('l_action_effective_date = ' || l_action_effective_date,40);
926 IF l_action_effective_date is not null and l_action_effective_date <= to_date('30/04/2009','dd/mm/yyyy') THEN
927 l_cess_last_bal_value_apr_09 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
928 p_prev_src_id,
929 g_paye_ref,
930 null,
931 null,
932 null,
933 null,
934 null);
935 hr_utility.set_location(' l_cess_last_bal_value_apr_09' || l_cess_last_bal_value_apr_09,30);
936 ELSE
937 l_cess_last_bal_value_apr_09 :=0;
938 END IF;
939 l_balance_value_apr_09:=l_balance_value_apr_09 - l_cess_last_bal_value_apr_09;
940 END IF;
941 IF p_supp_flag ='Y' THEN
942
943 OPEN get_last_source_id_apr_09;
944 FETCH get_last_source_id_apr_09 into l_prev_source_id_apr_09,l_payroll_effective_date;
945 CLOSE get_last_source_id_apr_09;
946
947 hr_utility.set_location(' l_prev_source_id_apr_09' || l_prev_source_id_apr_09,40);
948 hr_utility.set_location('l_payroll_effective_date = ' || l_payroll_effective_date,40);
949 IF l_prev_source_id_apr_09 is not null and l_assignment_action_apr_09 is not null and l_payroll_effective_date <= to_date('30/04/2009','dd/mm/yyyy') THEN
950 l_balance_value1_apr_09 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
951 l_prev_source_id_apr_09,
952 g_paye_ref,
953 null,
954 null,
955 null,
956 null,
957 null);
958
959 hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,40);
960
961 ELSE
962 l_balance_value_apr_09:=0;
963 l_balance_value1_apr_09:=0;
964 END IF;
965
966 ELSE
967 l_balance_value1_apr_09:=0;
968 hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,40);
969 END IF;
970
971 hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,100);
972 hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,100);
973
974 l_gross_pay_adjust_apr_09 := nvl(l_balance_value_apr_09,0) - nvl(l_balance_value1_apr_09,0);
975 hr_utility.set_location(' l_gross_pay_adjust' || l_gross_pay_adjust,40);
976 hr_utility.set_location(' l_gross_pay_adjust_apr_09' || l_gross_pay_adjust_apr_09,40);
977 l_gross_pay_adjust := l_gross_pay_adjust - l_gross_pay_adjust_apr_09;
978 hr_utility.set_location(' l_gross_pay_adjust from may 2009 ' || l_gross_pay_adjust,40);
979 l_defined_balance_id:=null;
980 l_balance_value_apr_09:=0;
981 l_balance_value1_apr_09:=0;
982
983
984 /* IE BIK PRSIable and Taxanel pay */
985 IF l_ppsn_override is null THEN
986 OPEN cur_defined_balance_id('IE BIK Taxable and PRSIable Pay','_PER_PAYE_REF_YTD');
987 hr_utility.set_location(' balance type _PER_PAYE_REF_YTD' ,30);
988 ELSE
989 OPEN cur_defined_balance_id('IE BIK Taxable and PRSIable Pay','_PER_PAYE_REF_PPSN_YTD');
990 hr_utility.set_location(' balance type _PER_PAYE_REF_PPSN_YTD' ,30);
991 END IF;
992
993 FETCH cur_defined_balance_id INTO l_defined_balance_id;
994 CLOSE cur_defined_balance_id;
995
996 hr_utility.set_location(' l_defined_balance_id' || l_defined_balance_id,30);
997 hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,30);
998 IF l_assignment_action_apr_09 is not null THEN
999 l_balance_value_apr_09 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1000 l_assignment_action_apr_09,
1001 g_paye_ref,
1002 null,
1003 null,
1004 null,
1005 null,
1006 null);
1007 END IF;
1008 hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,30);
1009
1010 IF (nvl(p_supp_flag,'N') = 'N') AND (p_last_cess_action IS NOT NULL) and l_assignment_action_apr_09 is not NULL THEN
1011 OPEN get_asg_action_eff_date(p_prev_src_id);
1012 FETCH get_asg_action_eff_date INTO l_action_effective_date;
1013 CLOSE get_asg_action_eff_date;
1014 hr_utility.set_location('l_action_effective_date = ' || l_action_effective_date,40);
1015 IF l_action_effective_date is not null and l_action_effective_date <= to_date('30/04/2009','dd/mm/yyyy') THEN
1016 l_cess_last_bal_value_apr_09 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1017 p_prev_src_id,
1018 g_paye_ref,
1019 null,
1020 null,
1021 null,
1022 null,
1023 null);
1024 hr_utility.set_location(' l_cess_last_bal_value_apr_09' || l_cess_last_bal_value_apr_09,30);
1025 ELSE
1026 l_cess_last_bal_value_apr_09 :=0;
1027 END IF;
1028 l_balance_value_apr_09:=l_balance_value_apr_09 - l_cess_last_bal_value_apr_09;
1029 END IF;
1030 IF p_supp_flag ='Y' THEN
1031
1032 OPEN get_last_source_id_apr_09;
1033 FETCH get_last_source_id_apr_09 into l_prev_source_id_apr_09,l_payroll_effective_date;
1034 CLOSE get_last_source_id_apr_09;
1035
1036 hr_utility.set_location(' l_prev_source_id_apr_09' || l_prev_source_id_apr_09,40);
1037 hr_utility.set_location('l_payroll_effective_date = ' || l_payroll_effective_date,40);
1038 IF l_prev_source_id_apr_09 is not null and l_assignment_action_apr_09 is not null and l_payroll_effective_date <= to_date('30/04/2009','dd/mm/yyyy') THEN
1039 l_balance_value1_apr_09 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1040 l_prev_source_id_apr_09,
1041 g_paye_ref,
1042 null,
1043 null,
1044 null,
1045 null,
1046 null);
1047
1048 hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,40);
1049
1050 ELSE
1051 l_balance_value_apr_09:=0;
1052 l_balance_value1_apr_09:=0;
1053 END IF;
1054
1055 ELSE
1056 l_balance_value1_apr_09:=0;
1057 hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,40);
1058 END IF;
1059 hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,100);
1060 hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,100);
1061
1062 l_bik_prsi_taxable_apr_09 := nvl(l_balance_value_apr_09,0) - nvl(l_balance_value1_apr_09,0);
1063 hr_utility.set_location(' l_bik_prsi_taxable' || l_bik_prsi_taxable,40);
1064 hr_utility.set_location(' l_bik_prsi_taxable_apr_09' || l_bik_prsi_taxable_apr_09,40);
1065 l_bik_prsi_taxable := l_bik_prsi_taxable - l_bik_prsi_taxable_apr_09;
1066 hr_utility.set_location(' l_bik_prsi_taxable from may 2009 ' || l_bik_prsi_taxable,40);
1067 l_defined_balance_id:=null;
1068 l_balance_value_apr_09:=0;
1069 l_balance_value1_apr_09:=0;
1070
1071
1072 /* IE Incoem Levy */
1073 IF l_ppsn_override is null THEN
1074 OPEN cur_defined_balance_id('IE Income Tax Levy','_PER_PAYE_REF_YTD');
1075 hr_utility.set_location(' balance type _PER_PAYE_REF_YTD' ,30);
1076 ELSE
1077 OPEN cur_defined_balance_id('IE Income Tax Levy','_PER_PAYE_REF_PPSN_YTD');
1078 hr_utility.set_location(' balance type _PER_PAYE_REF_PPSN_YTD' ,30);
1079 END IF;
1080
1081 FETCH cur_defined_balance_id INTO l_defined_balance_id;
1082 CLOSE cur_defined_balance_id;
1083
1084 hr_utility.set_location(' l_defined_balance_id' || l_defined_balance_id,30);
1085 hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,30);
1086 IF l_assignment_action_apr_09 is not null THEN
1087 l_balance_value_apr_09 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1088 l_assignment_action_apr_09,
1089 g_paye_ref,
1090 null,
1091 null,
1092 null,
1093 null,
1094 null);
1095 END IF;
1096 hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,30);
1097 IF (nvl(p_supp_flag,'N') = 'N') AND (p_last_cess_action IS NOT NULL) and l_assignment_action_apr_09 is not NULL THEN
1098 OPEN get_asg_action_eff_date(p_prev_src_id);
1099 FETCH get_asg_action_eff_date INTO l_action_effective_date;
1100 CLOSE get_asg_action_eff_date;
1101 hr_utility.set_location('l_action_effective_date = ' || l_action_effective_date,40);
1102 IF l_action_effective_date is not null and l_action_effective_date <= to_date('30/04/2009','dd/mm/yyyy') THEN
1103 l_cess_last_bal_value_apr_09 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1104 p_prev_src_id,
1105 g_paye_ref,
1106 null,
1107 null,
1108 null,
1109 null,
1110 null);
1111 hr_utility.set_location(' l_cess_last_bal_value_apr_09' || l_cess_last_bal_value_apr_09,30);
1112 ELSE
1113 l_cess_last_bal_value_apr_09 :=0;
1114 END IF;
1115 l_balance_value_apr_09:=l_balance_value_apr_09 - l_cess_last_bal_value_apr_09;
1116 END IF;
1117 IF p_supp_flag ='Y' THEN
1118
1119 OPEN get_last_source_id_apr_09;
1120 FETCH get_last_source_id_apr_09 into l_prev_source_id_apr_09,l_payroll_effective_date;
1121 CLOSE get_last_source_id_apr_09;
1122
1123 hr_utility.set_location(' l_prev_source_id_apr_09' || l_prev_source_id_apr_09,40);
1124 hr_utility.set_location('l_payroll_effective_date = ' || l_payroll_effective_date,40);
1125 IF l_prev_source_id_apr_09 is not null and l_assignment_action_apr_09 is not null and l_payroll_effective_date <= to_date('30/04/2009','dd/mm/yyyy') THEN
1126 l_balance_value1_apr_09 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1127 l_prev_source_id_apr_09,
1128 g_paye_ref,
1129 null,
1130 null,
1131 null,
1132 null,
1133 null);
1134
1135 hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,40);
1136 ELSE
1137 l_balance_value_apr_09:=0;
1138 l_balance_value1_apr_09:=0;
1139 END IF;
1140
1141 ELSE
1142 l_balance_value1_apr_09:=0;
1143 hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,40);
1144 END IF;
1145 hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,100);
1146 hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,100);
1147
1148 l_income_levy_apr_09 := nvl(l_balance_value_apr_09,0) - nvl(l_balance_value1_apr_09,0);
1149 hr_utility.set_location(' l_income_levy' || l_income_levy,40);
1150 hr_utility.set_location(' l_income_levy_apr_09' || l_income_levy_apr_09,40);
1151 l_income_levy := l_income_levy - l_income_levy_apr_09;
1152 hr_utility.set_location(' l_income_levy from may 2009 ' || l_income_levy,40);
1153 l_defined_balance_id:=null;
1154 l_balance_value_apr_09:=0;
1155 l_balance_value1_apr_09:=0;
1156
1157 l_gross_pay_total_apr_09 :=l_gross_pay_apr_09+l_gross_pay_adjust_apr_09+l_bik_prsi_taxable_apr_09;
1158 END IF;
1159 /* ---------------------------------------------------------------------------------------------- */
1160 l_gross_pay_total :=l_gross_pay+l_gross_pay_adjust+l_bik_prsi_taxable;
1161
1162 hr_utility.set_location(' l_gross_pay_total' || l_gross_pay_total,30);
1163
1164 /*--- USC Code --Start abraghun */
1165
1166 l_defined_balance_id:=null;
1167 l_balance_value:=0;
1168 l_balance_value1:=0;
1169
1170 IF l_ppsn_override is null THEN
1171 OPEN cur_defined_balance_id('IE USC Balance','_PER_PAYE_REF_YTD');
1172
1173 hr_utility.set_location(' USC _PER_PAYE_REF_YTD' ,30);
1174
1175 ELSE
1176 OPEN cur_defined_balance_id('IE USC Balance','_PER_PAYE_REF_PPSN_YTD');
1177 hr_utility.set_location(' USC _PER_PAYE_REF_PPSN_YTD' ,30);
1178 END IF;
1179
1180
1181 FETCH cur_defined_balance_id INTO l_defined_balance_id;
1182 CLOSE cur_defined_balance_id;
1183
1184 hr_utility.set_location(' USC Balance value : ' || l_balance_value,60);
1185 IF (p_child_run_ass_act_id IS NOT NULL) THEN
1186 l_balance_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1187 p_child_run_ass_act_id,
1188 g_paye_ref,
1189 null,
1190 null,
1191 null,
1192 null,
1193 null);
1194 ELSE
1195 l_balance_value:=0;
1196 END IF;
1197 hr_utility.set_location(' USC Balance value : ' || l_balance_value,60);
1198 IF (nvl(p_supp_flag,'N') = 'N') AND (p_last_cess_action IS NOT NULL) THEN
1199 l_cess_last_bal_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1200 p_prev_src_id,
1201 g_paye_ref,
1202 null,
1203 null,
1204 null,
1205 null,
1206 null);
1207 hr_utility.set_location(' USC last balance value : ' || l_cess_last_bal_value,60);
1208 l_balance_value:=l_balance_value - l_cess_last_bal_value;
1209 END IF;
1210
1211 IF p_supp_flag ='Y' THEN
1212
1213 OPEN get_last_source_id;
1214 FETCH get_last_source_id into l_prev_source_id;
1215 CLOSE get_last_source_id;
1216 hr_utility.set_location(' l_prev_source_id' || l_prev_source_id,60);
1217 IF l_prev_source_id IS NOT NULL THEN /* 9337590 */
1218 l_balance_value1 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1219 l_prev_source_id,
1220 g_paye_ref,
1221 null,
1222 null,
1223 null,
1224 null,
1225 null);
1226 hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
1227 END IF;
1228 ELSE
1229 l_balance_value1:=0;
1230 hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
1231 END IF;
1232 l_usc := l_balance_value - l_balance_value1;
1233 hr_utility.set_location(' USC : ' || l_usc,100);
1234
1235 /* IE Court Order*/
1236 l_defined_balance_id:=null;
1237 l_balance_value:=0;
1238 l_balance_value1:=0;
1239
1240 IF l_ppsn_override is null THEN
1241 OPEN cur_defined_balance_id('IE Court Order','_PER_PAYE_REF_YTD');
1242 hr_utility.set_location(' Court Order _PER_PAYE_REF_YTD' ,30);
1243 ELSE
1244 OPEN cur_defined_balance_id('IE Court Order','_PER_PAYE_REF_PPSN_YTD');
1245 hr_utility.set_location(' Court Order _PER_PAYE_REF_PPSN_YTD' ,30);
1246 END IF;
1247
1248 FETCH cur_defined_balance_id INTO l_defined_balance_id;
1249 CLOSE cur_defined_balance_id;
1250
1251 hr_utility.set_location(' Court Order Balance value : ' || l_balance_value,60);
1252 IF (p_child_run_ass_act_id IS NOT NULL) THEN
1253 l_balance_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1254 p_child_run_ass_act_id,
1255 g_paye_ref,
1256 null,
1257 null,
1258 null,
1259 null,
1260 null);
1261 ELSE
1262 l_balance_value:=0;
1263 END IF;
1264 hr_utility.set_location(' Court Order Balance value : ' || l_balance_value,60);
1265 IF (nvl(p_supp_flag,'N') = 'N') AND (p_last_cess_action IS NOT NULL) THEN
1266 l_cess_last_bal_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1267 p_prev_src_id,
1268 g_paye_ref,
1269 null,
1270 null,
1271 null,
1272 null,
1273 null);
1274 hr_utility.set_location(' Court Order last balance value : ' || l_cess_last_bal_value,60);
1275 l_balance_value:=l_balance_value - l_cess_last_bal_value;
1276 END IF;
1277
1278 IF p_supp_flag ='Y' THEN
1279
1280 OPEN get_last_source_id;
1281 FETCH get_last_source_id into l_prev_source_id;
1282 CLOSE get_last_source_id;
1283 hr_utility.set_location(' l_prev_source_id' || l_prev_source_id,60);
1284 IF l_prev_source_id IS NOT NULL THEN /* 9337590 */
1285 l_balance_value1 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1286 l_prev_source_id,
1287 g_paye_ref,
1288 null,
1289 null,
1290 null,
1291 null,
1292 null);
1293 hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
1294 END IF;
1295 ELSE
1296 l_balance_value1:=0;
1297 hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
1298 END IF;
1299 l_court_order := l_balance_value - l_balance_value1;
1300 hr_utility.set_location(' Court Order Deductions : ' || l_court_order,101);
1301
1302 l_gross_uscable := l_gross_pay_total - l_court_order;
1303
1304 --11076169
1305 /* IE Court Order Child Portion */
1306 l_defined_balance_id:=null;
1307 l_balance_value:=0;
1308 l_balance_value1:=0;
1309
1310 IF l_ppsn_override is null THEN
1311 OPEN cur_defined_balance_id('IE Court Order Child Portion','_PER_PAYE_REF_YTD');
1312 hr_utility.set_location(' IE Court Order Child Portion _PER_PAYE_REF_YTD' ,30);
1313 ELSE
1314 OPEN cur_defined_balance_id('IE Court Order Child Portion','_PER_PAYE_REF_PPSN_YTD');
1315 hr_utility.set_location(' IE Court Order Child Portion _PER_PAYE_REF_PPSN_YTD' ,30);
1316 END IF;
1317
1318 FETCH cur_defined_balance_id INTO l_defined_balance_id;
1319 CLOSE cur_defined_balance_id;
1320
1321 hr_utility.set_location(' IE Court Order Child Portion Balance value : ' || l_balance_value,60);
1322 IF (p_child_run_ass_act_id IS NOT NULL) THEN
1323 l_balance_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1324 p_child_run_ass_act_id,
1325 g_paye_ref,
1326 null,
1327 null,
1328 null,
1329 null,
1330 null);
1331 ELSE
1332 l_balance_value:=0;
1333 END IF;
1334 hr_utility.set_location(' IE Court Order Child Portion Balance value : ' || l_balance_value,60);
1335 IF (nvl(p_supp_flag,'N') = 'N') AND (p_last_cess_action IS NOT NULL) THEN
1336 l_cess_last_bal_value := 0;
1337 l_cess_last_bal_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1338 p_prev_src_id,
1339 g_paye_ref,
1340 null,
1341 null,
1342 null,
1343 null,
1344 null);
1345 hr_utility.set_location(' IE Court Order Child Portion last balance value : ' || l_cess_last_bal_value,60);
1346 l_balance_value:=l_balance_value - l_cess_last_bal_value;
1347 END IF;
1348
1349 IF p_supp_flag ='Y' THEN
1350
1351 OPEN get_last_source_id;
1352 FETCH get_last_source_id into l_prev_source_id;
1353 CLOSE get_last_source_id;
1354 hr_utility.set_location(' l_prev_source_id' || l_prev_source_id,60);
1355 IF l_prev_source_id IS NOT NULL THEN /* 9337590 */
1356 l_balance_value1 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1357 l_prev_source_id,
1358 g_paye_ref,
1359 null,
1360 null,
1361 null,
1362 null,
1363 null);
1364 hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
1365 END IF;
1366 ELSE
1367 l_balance_value1:=0;
1368 hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
1369 END IF;
1370
1371 l_court_order_child_port := l_balance_value - l_balance_value1;
1372 hr_utility.set_location(' IE Court Order Child Portion Deductions : ' || l_court_order_child_port,101);
1373
1374 l_gross_uscable := l_gross_pay_total + l_court_order_child_port;
1375 --11076169
1376
1377 --13013273
1378 /* IE PRSA ER Contribution */
1379 l_defined_balance_id:=null;
1380 l_balance_value:=0;
1381 l_balance_value1:=0;
1382
1383 IF l_ppsn_override is null THEN
1384 OPEN cur_defined_balance_id('IE PRSA ER Contribution','_PER_PAYE_REF_YTD');
1385 hr_utility.set_location(' IE PRSA ER Contribution _PER_PAYE_REF_YTD' ,30);
1386 ELSE
1387 OPEN cur_defined_balance_id('IE PRSA ER Contribution','_PER_PAYE_REF_PPSN_YTD');
1388 hr_utility.set_location(' IE PRSA ER Contribution _PER_PAYE_REF_PPSN_YTD' ,30);
1389 END IF;
1390
1391 FETCH cur_defined_balance_id INTO l_defined_balance_id;
1392 CLOSE cur_defined_balance_id;
1393
1394 hr_utility.set_location(' IE PRSA ER Contribution Balance value : ' || l_balance_value,60);
1395 IF (p_child_run_ass_act_id IS NOT NULL) THEN
1396 l_balance_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1397 p_child_run_ass_act_id,
1398 g_paye_ref,
1399 null,
1400 null,
1401 null,
1402 null,
1403 null);
1404 ELSE
1405 l_balance_value:=0;
1406 END IF;
1407 hr_utility.set_location(' IE PRSA ER Contribution Balance value : ' || l_balance_value,60);
1408 IF (nvl(p_supp_flag,'N') = 'N') AND (p_last_cess_action IS NOT NULL) THEN
1409 l_cess_last_bal_value := 0;
1410 l_cess_last_bal_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1411 p_prev_src_id,
1412 g_paye_ref,
1413 null,
1414 null,
1415 null,
1416 null,
1417 null);
1418 hr_utility.set_location(' IE PRSA ER Contribution last balance value : ' || l_cess_last_bal_value,60);
1419 l_balance_value:=l_balance_value - l_cess_last_bal_value;
1420 END IF;
1421
1422 IF p_supp_flag ='Y' THEN
1423
1424 OPEN get_last_source_id;
1425 FETCH get_last_source_id into l_prev_source_id;
1426 CLOSE get_last_source_id;
1427 hr_utility.set_location(' l_prev_source_id' || l_prev_source_id,60);
1428 IF l_prev_source_id IS NOT NULL THEN /* 9337590 */
1429 l_balance_value1 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1430 l_prev_source_id,
1431 g_paye_ref,
1432 null,
1433 null,
1434 null,
1435 null,
1436 null);
1437 hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
1438 END IF;
1439 ELSE
1440 l_balance_value1:=0;
1441 hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
1442 END IF;
1443
1444 l_prsa_er_contribution := l_balance_value - l_balance_value1;
1445 hr_utility.set_location(' IE PRSA ER Contribution Deductions : ' || l_prsa_er_contribution,101);
1446
1447 l_gross_uscable := l_gross_pay_total + l_prsa_er_contribution;
1448 --13013273
1449
1450 hr_utility.set_location(' Gross USCable : ' || l_gross_uscable,102);
1451 /*--- USC Code --Ends abraghun */
1452
1453 OPEN c_employee_details(p_assignment_id,p_date_earned);
1454 FETCH c_employee_details INTO c_employee_details_rec;
1455 CLOSE c_employee_details;
1456 -- CLOSE cur_cal_option;
1457 OPEN csr_get_org_tax_address(g_paye_ref);
1458 FETCH csr_get_org_tax_address INTO csr_get_org_tax_address_rec;
1459 CLOSE csr_get_org_tax_address;
1460 --
1461 -- archive the details
1462 pay_action_information_api.create_action_information (
1463 p_action_information_id => l_action_info_id
1464 , p_action_context_id => p_action_context_id
1465 , p_action_context_type => 'AAP'
1466 , p_object_version_number => l_ovn
1467 , p_effective_date => g_archive_effective_date
1468 , p_source_id => p_child_run_ass_act_id
1469 , p_source_text => NULL
1470 , p_action_information_category => 'IE CESS INFORMATION'
1471 -- , p_action_information1 => p_deceased_flag /* knadhan */
1472 , p_action_information2 => l_supp_flg
1473 , p_action_information3 => fnd_date.date_to_canonical(l_termination_date)
1474 , p_action_information7 => l_supp_pymt_date
1475 , p_action_information8 => p_person_id
1476 , p_action_information9 => fnd_date.date_to_canonical(p_date_earned)
1477 , p_action_information10 => upper(csr_get_org_tax_address_rec.employer_tax_rep_name)
1478 , p_action_information11 => upper(csr_get_org_tax_address_rec.employer_tax_addr1)
1479 , p_action_information12 => upper(csr_get_org_tax_address_rec.employer_tax_addr2)
1480 , p_action_information13 => upper(csr_get_org_tax_address_rec.employer_tax_addr3)
1481 , p_action_information14 => lpad(upper(csr_get_org_tax_address_rec.employer_no), 8, ' ')
1482 , p_action_information15 => lpad(translate(csr_get_org_tax_address_rec.employer_tax_ref_phone,'1()-', '1'), 11, ' ')
1483 , p_action_information16 => upper(csr_get_org_tax_address_rec.email) /* knadhan */
1484 , p_action_information20 => upper(c_employee_details_rec.surname) -- surname
1485 , p_action_information21 => upper(c_employee_details_rec.first_name) -- first_name
1486 , p_action_information22 => upper(nvl(l_ppsn_override,c_employee_details_rec.PPSN)) -- PPSN
1487 , p_action_information23 => lpad(upper(c_employee_details_rec.works_no), 9, ' ') -- works_no
1488 , p_action_information24 => fnd_date.date_to_canonical(c_employee_details_rec.hire_date)
1489
1490 /* 8615992 */
1491 , p_action_information25 => lpad(trim(to_char(fnd_number.canonical_to_number(nvl(l_gross_pay_total_apr_09+l_gross_pay_total,0)) ,'9999999')),7,' ') -- lpad(l_gross_pay_total,10,' ') /* 9337590 */
1492 , p_action_information26 => lpad(trim(to_char(fnd_number.canonical_to_number(nvl(l_income_levy_apr_09+l_income_levy,0)) ,'999990.99')),8,' ') -- lpad(l_income_levy,10,' ') /* 9337590 */
1493 , p_action_information27 => lpad(trim(to_char(fnd_number.canonical_to_number(nvl(l_gross_pay_total_apr_09,0)) ,'9999999')),7,' ') -- lpad(l_gross_pay_total,10,' ') /* knadhan */
1494 , p_action_information28 => lpad(trim(to_char(fnd_number.canonical_to_number(nvl(l_income_levy_apr_09,0)) ,'999990.99')),8,' ') -- lpad(l_income_levy,10,' ') /* knadhan */
1495 , p_action_information29 => lpad(trim(to_char(fnd_number.canonical_to_number(nvl(l_gross_pay_total,0)) ,'9999999')),7,' ') -- lpad(l_gross_pay_total,10,' ') /* knadhan */
1496 , p_action_information30 => lpad(trim(to_char(fnd_number.canonical_to_number(nvl(l_income_levy,0)) ,'999990.99')),8,' ') -- lpad(l_income_levy,10,' ') /* knadhan */
1497 , p_action_information17 => lpad(trim(to_char(fnd_number.canonical_to_number(nvl(l_usc,0)) ,'999990.99')),8,' ') --USC
1498 , p_action_information18 => lpad(trim(to_char(fnd_number.canonical_to_number(nvl(l_gross_uscable,0)) ,'9999999')),7,' ') --GROSS USCable
1499
1500
1501 );
1502 --
1503 hr_utility.set_location('Leaving '||l_proc,20);
1504 END archive_cess_info;
1505
1506
1507
1508 /* Range Cursor */
1509
1510 PROCEDURE range_code (pactid IN NUMBER,
1511 sqlstr OUT nocopy VARCHAR2)
1512 -- public procedure which archives the payroll information, then returns a
1513 -- varchar2 defining a SQL statement to select all the people that may be
1514 -- eligible for payslip reports.
1515 -- The archiver uses this cursor to split the people into chunks for parallel
1516 -- processing.
1517 IS
1518 --
1519 l_proc CONSTANT VARCHAR2(50):= g_package||'range_code';
1520 -- vars for constructing the sqlstr
1521
1522 l_bg_id NUMBER;
1523 l_end_date VARCHAR2(30);
1524 l_start_date VARCHAR2(30);
1525 l_employer NUMBER;
1526
1527 BEGIN
1528 -- hr_utility.trace_on(null,'cess');
1529 hr_utility.set_location('Entering ' || l_proc,10);
1530
1531 pay_ie_cess_report.get_parameters (
1532 p_payroll_action_id => pactid
1533 , p_token_name => 'END_DATE'
1534 , p_token_value => l_end_date);
1535
1536 pay_ie_cess_report.get_parameters (
1537 p_payroll_action_id => pactid
1538 , p_token_name => 'BG_ID'
1539 , p_token_value => l_bg_id);
1540
1541 pay_ie_cess_report.get_parameters (
1542 p_payroll_action_id => pactid
1543 , p_token_name => 'EMPLOYER'
1544 , p_token_value => l_employer);
1545
1546 pay_ie_cess_report.get_parameters (
1547 p_payroll_action_id => pactid
1548 , p_token_name => 'START_DATE'
1549 , p_token_value => l_start_date);
1550
1551 hr_utility.set_location('Step ' || l_proc,20);
1552
1553 hr_utility.set_location('l_start_date = ' || l_start_date,20);
1554 hr_utility.set_location('l_end_date = ' || l_end_date,20);
1555 hr_utility.set_location('l_employer = ' || l_employer,20);
1556 hr_utility.set_location('l_bg_id = ' || l_bg_id,20);
1557
1558 sqlstr := 'SELECT DISTINCT person_id
1559 FROM per_people_f ppf,
1560 pay_payroll_actions ppa
1561 WHERE ppa.payroll_action_id = :payroll_action_id
1562 AND ppa.business_group_id +0= ppf.business_group_id
1563 ORDER BY ppf.person_id';
1564
1565 hr_utility.set_location('After sqlstr formed ' || l_proc,30);
1566
1567 hr_utility.set_location('Leaving ' || l_proc,40);
1568
1569 Exception
1570 when others then
1571 hr_utility.set_location('Leaving via exception section ' || l_proc,40);
1572 sqlstr:='select 1 from dual where to_char(:payroll_action_id) = dummy';
1573 END range_code;
1574
1575
1576 /* Action Creation */
1577
1578 PROCEDURE assignment_action_code (pactid in number,
1579 stperson in number,
1580 endperson in number,
1581 chunk in number) is
1582 --
1583 CURSOR csr_prepaid_assignments(p_pact_id NUMBER,
1584 stperson NUMBER,
1585 endperson NUMBER,
1586 p_paye_ref NUMBER,
1587 l_payroll_id NUMBER,
1588 l_person_id NUMBER
1589 ) IS
1590 SELECT as1.person_id person_id,
1591 act.assignment_id assignment_id,
1592 act.assignment_action_id run_action_id,
1593 act1.assignment_action_id prepaid_action_id,
1594 as1.assignment_number works_number,
1595 as1.period_of_service_id period_of_service_id
1596 FROM --per_periods_of_service ppos,
1597 per_all_assignments_f as1,
1598 pay_assignment_actions act,
1599 pay_payroll_actions appa,
1600 pay_action_interlocks pai,
1601 pay_assignment_actions act1,
1602 pay_payroll_actions appa2
1603 WHERE /*appa.consolidation_set_id = p_consolidation_id*/
1604 act.tax_unit_id = p_paye_ref
1605 AND appa.effective_date BETWEEN g_archive_start_date AND g_archive_end_date
1606 AND as1.person_id BETWEEN stperson AND endperson
1607
1608 AND as1.effective_end_date between g_archive_start_date AND g_archive_end_date
1609 AND (as1.effective_end_date = (select max(effective_end_date)
1610 from per_all_assignments_f paf1
1611 where paf1.assignment_id = as1.assignment_id
1612
1613 and paf1.assignment_status_type_id in
1614 (SELECT ast.assignment_status_type_id
1615 FROM per_assignment_status_types ast
1616 WHERE ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
1617 )
1618 )
1619 AND as1.effective_end_date <> to_date('31-12-4712','DD-MM-YYYY')
1620 )
1621 AND (as1.payroll_id in (select b.payroll_id
1622 from per_assignments_f a,per_assignments_f b
1623 where a.payroll_id = l_payroll_id
1624 and a.person_id = b.person_id
1625 and a.period_of_Service_id = b.period_of_Service_id
1626 and a.period_of_Service_id = as1.period_of_Service_id
1627 and a.person_id = as1.person_id
1628 and a.effective_start_date <= g_archive_end_date
1629
1630
1631 and a.effective_end_date = (select max(effective_end_date)
1632 from per_all_assignments_f paf1
1633 where paf1.assignment_id = a.assignment_id
1634 and paf1.assignment_status_type_id in
1635 (SELECT ast.assignment_status_type_id
1636 FROM per_assignment_status_types ast
1637 WHERE ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
1638 )
1639 )
1640 )
1641 OR l_payroll_id is null)
1642
1643 --
1644 AND appa.action_type IN ('R','Q') -- Payroll Run or Quickpay Run
1645 AND act.payroll_action_id = appa.payroll_action_id
1646 AND act.source_action_id IS NULL
1647 AND as1.assignment_id = act.assignment_id
1648 AND act.action_status = 'C'
1649 AND act.assignment_action_id = pai.locked_action_id
1650 AND act1.assignment_action_id = pai.locking_action_id
1651 AND act1.action_status = 'C'
1652 AND act1.payroll_action_id = appa2.payroll_action_id
1653 AND appa2.action_type IN ('P','U') -- Prepayments or Quickpay Prepayments
1654 AND appa2.payroll_action_id = (SELECT /*+ USE_NL(ACT2 APPA4)*/
1655 max(appa4.payroll_action_id)
1656 FROM /*pay_pre_payments ppp, --Bug 4193738 --Bug 4468864*/
1657 pay_assignment_actions act2,
1658 pay_payroll_actions appa4
1659 WHERE /*ppp.assignment_action_id=act2.assignment_action_id
1660 AND*/ act2.assignment_id = act.assignment_id
1661 AND act2.action_status = 'C'
1662 AND appa4.payroll_action_id = act2.payroll_action_id
1663 AND appa4.action_type in ('P','U')
1664 AND appa4.effective_date BETWEEN g_archive_start_date AND g_archive_end_date)
1665 -- bug 5597735, change the not exists clause.
1666 -- refer bug 5233518 for more details.
1667 AND NOT EXISTS (SELECT /*+ ORDERED use_nl(appa3)*/ null
1668 from pay_assignment_actions act3,
1669 pay_payroll_actions appa3,
1670 pay_action_interlocks pai, --bug 4208273
1671 pay_assignment_actions act2, --bug 4208273
1672 pay_payroll_actions appa4 --bug 4208273
1673 where pai.locked_action_id= act3.assignment_action_id
1674 and pai.locking_action_id=act2.assignment_action_id
1675 and act3.action_sequence >= act1.action_sequence --bug 4193738
1676 and act3.assignment_id in (select distinct paaf.assignment_id
1677 from per_all_assignments_f paaf
1678 where paaf.person_id = as1.person_id
1679 )
1680 and act3.tax_unit_id = act1.tax_unit_id
1681 and act3.action_status = 'C'
1682 and act2.action_status = 'C'
1683 and act3.payroll_action_id=appa4.payroll_action_id
1684 and appa4.action_type in ('P','U')
1685 and act2.payroll_action_id = appa3.payroll_action_id
1686 and appa3.action_type = 'X'
1687 and appa3.report_type = 'IE_CESSATION')
1688 /* check person does not hold employment with the employer between start of year and archive end date */
1689 AND NOT EXISTS (
1690 SELECT MIN(paf.effective_start_date),MAX(paf.effective_end_date)
1691 FROM per_all_assignments_f paf,
1692 pay_all_payrolls_f papf,
1693 hr_soft_coding_keyflex scl
1694 WHERE paf.person_id = as1.person_id
1695 AND paf.payroll_id = papf.payroll_id
1696 /* changed the cursor to handle case where 2 user defined assignment status exist mapping to
1697 same per_system_status (5073577) */
1698 AND paf.assignment_status_type_id in
1699 (SELECT ast.assignment_status_type_id
1700 FROM per_assignment_status_types ast
1701 WHERE ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
1702 )
1703 AND g_archive_end_date between papf.effective_start_date and papf.effective_end_date
1704 AND papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
1705 AND scl.segment4 = to_char(p_paye_ref)
1706 group by paf.assignment_id
1707 having min(paf.effective_start_date) <= g_archive_end_date
1708 and max(paf.effective_end_date) > g_archive_end_date
1709 )
1710 AND as1.person_id =nvl(l_person_id,as1.person_id) /* knadhan */
1711 ORDER BY as1.person_id,as1.assignment_number,act.assignment_id
1712 FOR UPDATE OF as1.assignment_id;
1713
1714
1715
1716 cursor csr_ppsn_override(p_asg_id number)
1717 is
1718 select aei_information1 PPSN_OVERRIDE
1719 from per_assignment_extra_info
1720 where assignment_id = p_asg_id
1721 and aei_information_category = 'IE_ASG_OVERRIDE';
1722
1723 l_ppsn_override per_assignment_extra_info.aei_information1%type;
1724
1725 /*
1726 cursor csr_ppsn_min_asg(p_ppsn_override varchar2, p_person_id number)
1727 is
1728 select MIN(paei.assignment_id) ovrride_asg
1729 from per_assignment_extra_info paei
1730 where paei.information_type = 'IE_ASG_OVERRIDE'
1731 and paei.aei_information1 = p_ppsn_override
1732 and exists
1733 (select 1 from per_all_assignments_f paaf
1734 where paaf.assignment_id = paei.assignment_id
1735 and paaf.person_id = p_person_id)
1736 GROUP BY paei.aei_information1; */
1737 /* 8615992 */
1738 cursor csr_ppsn_min_asg(p_ppsn_override varchar2, p_person_id number,c_period_of_service_id number)
1739 is
1740 select MIN(paei.assignment_id) ovrride_asg
1741 from per_assignment_extra_info paei,per_all_assignments_f paaf
1742 where paei.information_type = 'IE_ASG_OVERRIDE'
1743 and paei.aei_information1 = p_ppsn_override
1744 and paaf.assignment_id = paei.assignment_id
1745 and paaf.person_id = p_person_id
1746 and paaf.period_of_service_id=c_period_of_service_id
1747 GROUP BY paei.aei_information1;
1748
1749
1750 l_ppsn_override_asg per_assignment_extra_info.assignment_id%type;
1751 l_temp_person_id per_people_f.person_id%TYPE :=0;
1752
1753
1754 l_actid NUMBER;
1755 l_canonical_end_date DATE;
1756 l_canonical_start_date DATE;
1757 l_consolidation_set VARCHAR2(30);
1758 l_end_date VARCHAR2(20);
1759 l_payroll_id NUMBER;
1760 l_employee_person_id NUMBER;
1761 l_prepay_action_id NUMBER;
1762 l_start_date VARCHAR2(20);
1763 l_person_id NUMBER;
1764 l_assignment_id NUMBER;
1765 l_error varchar2(1) ;
1766 l_period_of_service_id NUMBER;
1767 l_bg_id NUMBER;
1768 --
1769 l_proc VARCHAR2(50) := g_package||'assignment_action_code';
1770 BEGIN
1771
1772 --hr_utility.trace_on(null,'cess');
1773 hr_utility.set_location('Entering ' || l_proc,10);
1774 pay_ie_cess_report.get_parameters (
1775 p_payroll_action_id => pactid
1776 , p_token_name => 'EMPLOYER'
1777 , p_token_value => g_paye_ref);
1778
1779 pay_ie_cess_report.get_parameters (
1780 p_payroll_action_id => pactid
1781 , p_token_name => 'END_DATE'
1782 , p_token_value => l_end_date);
1783
1784 pay_ie_cess_report.get_parameters (
1785 p_payroll_action_id => pactid
1786 , p_token_name => 'BG_ID'
1787 , p_token_value => l_bg_id);
1788
1789 pay_ie_cess_report.get_parameters (
1790 p_payroll_action_id => pactid
1791 , p_token_name => 'START_DATE'
1792 , p_token_value => l_start_date);
1793
1794 pay_ie_cess_report.get_parameters (
1795 p_payroll_action_id => pactid
1796 , p_token_name => 'PAYROLL'
1797 , p_token_value => l_payroll_id);
1798
1799 pay_ie_cess_report.get_parameters (
1800 p_payroll_action_id => pactid
1801 , p_token_name => 'EMPLOYEE'
1802 , p_token_value => l_employee_person_id); /* knadhan */
1803
1804 hr_utility.set_location('Step ' || l_proc,20);
1805 hr_utility.set_location('g_paye_ref = ' || g_paye_ref,20);
1806 hr_utility.set_location('l_end_date = ' || l_end_date,20);
1807 hr_utility.set_location('l_start_date = ' || l_start_date,20);
1808 hr_utility.set_location('l_payroll_id = ' || l_payroll_id,20);
1809 hr_utility.set_location('l_employee_person_id = ' || l_employee_person_id,20);
1810 hr_utility.set_location('l_bg_id = ' || l_bg_id,20);
1811
1812 l_canonical_start_date := TO_DATE(l_start_date,'yyyy/mm/dd');
1813 l_canonical_end_date := TO_DATE(l_end_date,'yyyy/mm/dd');
1814 g_archive_start_date := l_canonical_start_date;
1815 g_archive_end_date := TO_DATE(l_end_date,'yyyy/mm/dd');
1816
1817
1818 l_prepay_action_id := 0;
1819 l_person_id := 0;
1820 l_assignment_id:=0;
1821 l_period_of_service_id := 0;
1822
1823 hr_utility.set_location('l_canonical_start_date = ' || l_canonical_start_date,20);
1824 hr_utility.set_location('l_canonical_end_date = ' || l_canonical_end_date,20);
1825
1826 hr_utility.set_location('Before csr_prepaid_assignments',21);
1827
1828 l_ppsn_override := NULL;
1829 l_ppsn_override_asg := NULL;
1830
1831 FOR csr_rec IN csr_prepaid_assignments(pactid,
1832 stperson,
1833 endperson,
1834 g_paye_ref,
1835 l_payroll_id,
1836 l_employee_person_id)
1837 LOOP
1838
1839 hr_utility.set_location('Person id..'||to_char(csr_rec.person_id),21-1);
1840 hr_utility.set_location('assignment_id..'||to_char(csr_rec.assignment_id),21-1);
1841 hr_utility.set_location('run_action_id..'||to_char(csr_rec.run_action_id),21-1);
1842 hr_utility.set_location('prepaid_action_id.'||to_char(csr_rec.prepaid_action_id),21-1);
1843 hr_utility.set_location('works_number..'||to_char(csr_rec.works_number),21-1);
1844 hr_utility.set_location('period_of_service_id..'||to_char(csr_rec.period_of_service_id),21-1);
1845
1846 hr_utility.set_location('Person id..'||to_char(csr_rec.person_id),21-1);
1847 hr_utility.set_location('Temp Person id..'||to_char(l_person_id),21-2);
1848
1849 l_ppsn_override := NULL;
1850 l_ppsn_override_asg := NULL;
1851 hr_utility.set_location('before fetch l_ppsn_override'||to_char(l_ppsn_override),21-3);
1852 hr_utility.set_location(' before fetch l_ppsn_override_asg'||to_char(l_ppsn_override_asg),21-3);
1853
1854 OPEN csr_ppsn_override(csr_rec.assignment_id);
1855 FETCH csr_ppsn_override INTO l_ppsn_override;
1856 CLOSE csr_ppsn_override;
1857
1858 hr_utility.set_location('l_ppsn_override'||to_char(l_ppsn_override),21-3);
1859
1860 IF l_ppsn_override IS NOT NULL THEN
1861 OPEN csr_ppsn_min_asg(l_ppsn_override,csr_rec.person_id,csr_rec.period_of_service_id);
1862 FETCH csr_ppsn_min_asg INTO l_ppsn_override_asg;
1863 CLOSE csr_ppsn_min_asg;
1864 hr_utility.set_location('l_ppsn_override_asg'||to_char(l_ppsn_override_asg),21-4);
1865 END IF;
1866
1867
1868
1869 hr_utility.set_location('csr_rec.assignment_id'||csr_rec.assignment_id,21-4);
1870
1871 IF (l_person_id <> csr_rec.person_id and l_ppsn_override IS NULL )
1872 OR /* knadhan */
1873 ((l_person_id <> csr_rec.person_id and l_ppsn_override IS NOT NULL) OR (l_ppsn_override_asg=csr_rec.assignment_id and l_ppsn_override IS NOT NULL))
1874 THEN
1875
1876 hr_utility.set_location('Different Person '|| csr_rec.person_id ,22);
1877
1878 SELECT pay_assignment_actions_s.NEXTVAL
1879 INTO l_actid
1880 FROM dual;
1881
1882 -- CREATE THE ARCHIVE ASSIGNMENT ACTION FOR THE MASTER ASSIGNMENT ACTION
1883 hr_utility.set_location('ASSIGNMENT ID : ' || csr_rec.assignment_id,23);
1884 hr_utility.trace('ASSIGNMENT ID : ' || csr_rec.assignment_id);
1885
1886 hr_nonrun_asact.insact(l_actid,csr_rec.assignment_id,pactid,chunk,g_paye_ref);
1887 -- CREATE THE ARCHIVE TO PAYROLL MASTER ASSIGNMENT ACTION INTERLOCK AND
1888 -- THE ARCHIVE TO PREPAYMENT ASSIGNMENT ACTION INTERLOCK
1889 -- hr_utility.set_location('creating lock1 ' || l_actid || ' to ' || csr_rec.run_action_id,20);
1890 -- hr_utility.set_location('creating lock2 ' || l_actid || ' to ' || csr_rec.prepaid_action_id,20);
1891 END IF; --
1892 hr_utility.set_location('l_prepay_action_id : ' || l_prepay_action_id,100);
1893 hr_utility.set_location('csr_rec.prepaid_action_id : ' || csr_rec.prepaid_action_id,101);
1894 hr_utility.set_location('l_actid : ' || l_actid,102);
1895
1896 IF l_prepay_action_id <> csr_rec.prepaid_action_id THEN
1897 hr_utility.set_location('locked id : ' || csr_rec.prepaid_action_id,23);
1898 hr_nonrun_asact.insint(l_actid,csr_rec.prepaid_action_id);
1899 END IF;
1900
1901 hr_nonrun_asact.insint(l_actid,csr_rec.run_action_id);
1902
1903 l_prepay_action_id := csr_rec.prepaid_action_id;
1904 l_person_id := csr_rec.person_id;
1905 l_period_of_service_id := csr_rec.period_of_service_id;
1906
1907 END LOOP;
1908
1909 hr_utility.set_location('Leaving ' || l_proc,20);
1910 END assignment_action_code;
1911
1912
1913
1914
1915 /* arch init */
1916 PROCEDURE archive_init (p_payroll_action_id IN NUMBER)
1917 IS
1918
1919 CURSOR csr_archive_effective_date(pactid NUMBER) IS
1920 SELECT effective_date
1921 FROM pay_payroll_actions
1922 WHERE payroll_action_id = pactid;
1923
1924 CURSOR csr_input_value_id(p_element_name CHAR,
1925 p_value_name CHAR) IS
1926 SELECT pet.element_type_id,
1927 piv.input_value_id
1928 FROM pay_input_values_f piv,
1929 pay_element_types_f pet
1930 WHERE piv.element_type_id = pet.element_type_id
1931 AND pet.legislation_code = 'IE'
1932 AND pet.element_name = p_element_name
1933 AND piv.name = p_value_name;
1934
1935 l_proc VARCHAR2(50) := g_package || 'archive_init';
1936 l_assignment_set_id NUMBER;
1937 l_bg_id NUMBER;
1938 l_canonical_end_date DATE;
1939 l_canonical_start_date DATE;
1940 l_consolidation_set NUMBER;
1941 l_end_date VARCHAR2(30);
1942 l_payroll_id NUMBER;
1943 l_start_date VARCHAR2(30);
1944 l_dummy VARCHAR2(2);
1945 l_error varchar2(1) ;
1946 BEGIN
1947
1948
1949 hr_utility.set_location('Entering ' || l_proc,10);
1950
1951 g_archive_pact := p_payroll_action_id;
1952
1953 OPEN csr_archive_effective_date(p_payroll_action_id);
1954 FETCH csr_archive_effective_date
1955 INTO g_archive_effective_date;
1956 CLOSE csr_archive_effective_date;
1957
1958 pay_ie_cess_report.get_parameters (
1959 p_payroll_action_id => p_payroll_action_id
1960 , p_token_name => 'EMPLOYER'
1961 , p_token_value => g_paye_ref);
1962
1963 pay_ie_cess_report.get_parameters (
1964 p_payroll_action_id => p_payroll_action_id
1965 , p_token_name => 'END_DATE'
1966 , p_token_value => l_end_date);
1967
1968 pay_ie_cess_report.get_parameters (
1969 p_payroll_action_id => p_payroll_action_id
1970 , p_token_name => 'START_DATE'
1971 , p_token_value => l_start_date);
1972
1973 pay_ie_cess_report.get_parameters (
1974 p_payroll_action_id => p_payroll_action_id
1975 , p_token_name => 'BG_ID'
1976 , p_token_value => l_bg_id);
1977
1978 hr_utility.set_location('Step ' || l_proc,20);
1979 hr_utility.set_location('g_paye_ref = ' || g_paye_ref,20);
1980 hr_utility.set_location('l_end_date = ' || l_end_date,20);
1981 hr_utility.set_location('l_start_date = ' || l_start_date,20);
1982 hr_utility.set_location('l_bg_id = ' || l_bg_id,20);
1983
1984 l_canonical_start_date := TO_DATE(l_start_date,'yyyy/mm/dd');
1985 l_canonical_end_date := TO_DATE(l_end_date,'yyyy/mm/dd');
1986
1987 -- Initialized g_archive_end_date to support Retry Option
1988 g_archive_end_date := TO_DATE(l_end_date,'yyyy/mm/dd');
1989 g_archive_start_date := l_canonical_start_date;
1990
1991 hr_utility.set_location('l_canonical_start_date = ' || l_canonical_start_date,20);
1992
1993
1994
1995 hr_utility.set_location('stage 1',22);
1996
1997 hr_utility.set_location('stage 2',23);
1998
1999 hr_utility.set_location('stage 3',24);
2000
2001
2002 hr_utility.set_location('Leaving ' || l_proc,20);
2003 END archive_init;
2004
2005
2006
2007
2008
2009
2010
2011 /* Archive COde */
2012
2013 PROCEDURE archive_data (p_assactid in number,
2014 p_effective_date in date) IS
2015 CURSOR csr_assignment_actions(p_locking_action_id NUMBER) IS
2016 SELECT pre.locked_action_id pre_assignment_action_id,
2017 pay.locked_action_id master_assignment_action_id,
2018 assact.assignment_id assignment_id,
2019 assact.payroll_action_id pay_payroll_action_id,
2020 paa.effective_date effective_date,
2021 ppaa.effective_date pre_effective_date,
2022 paa.date_earned date_earned,
2023 ptp.time_period_id time_period_id
2024 FROM pay_action_interlocks pre,
2025 pay_action_interlocks pay,
2026 pay_payroll_actions paa,
2027 pay_payroll_actions ppaa,
2028 pay_assignment_actions assact,
2029 pay_assignment_actions passact,
2030 per_time_periods ptp -- Added to retrieve correct time_period_id 4906850
2031 WHERE pre.locked_action_id = pay.locking_action_id
2032 AND pre.locking_action_id = p_locking_action_id
2033 AND pre.locked_action_id = passact.assignment_action_id
2034 AND passact.payroll_action_id = ppaa.payroll_action_id
2035 AND ppaa.action_type IN ('P','U')
2036 AND pay.locked_action_id = assact.assignment_action_id
2037 AND assact.payroll_action_id = paa.payroll_action_id
2038 AND assact.source_action_id IS NULL
2039 AND ptp.payroll_id = paa.payroll_id
2040 AND paa.date_earned between ptp.start_date and ptp.end_date
2041 and paa.date_earned >= to_date('01/01/2009','dd/mm/yyyy')
2042 --
2043 ORDER BY pay.locked_action_id DESC;
2044
2045
2046
2047 /*New Cursor to fetch latest child action */
2048 CURSOR cur_child_pay_action (p_person_id IN NUMBER,
2049 p_effective_date IN DATE,
2050 p_lat_act_seq IN NUMBER) is
2051 SELECT /*+ USE_NL(paa, ppa) */
2052 fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
2053 paa.assignment_action_id),16))
2054 FROM pay_assignment_actions paa,
2055 pay_payroll_actions ppa
2056 WHERE paa.payroll_action_id = ppa.payroll_action_id
2057 AND paa.assignment_id in (select assignment_id
2058 from per_all_assignments_f
2059 where person_id = p_person_id
2060 )
2061 AND paa.tax_unit_id = g_paye_ref
2062 AND (paa.source_action_id is not null or ppa.action_type in ('I','V','B'))
2063 AND ppa.effective_date between trunc(p_effective_date,'Y') and p_effective_date
2064 AND paa.action_sequence > p_lat_act_seq
2065 AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
2066 AND paa.action_status = 'C';
2067
2068 -- cursor to find assignment action locked by latest cess child action
2069 CURSOR cur_get_latest_cess(p_pact_id NUMBER,
2070 p_person_id NUMBER,
2071 c_ppsn varchar2
2072 ) IS
2073 SELECT max(lpad(paa_src.action_sequence,15,'0')|| paa_src.assignment_action_id)
2074 FROM pay_payroll_actions ppa_cess,
2075 pay_assignment_actions cess_src,
2076 pay_action_information pai_cess,
2077 pay_assignment_actions paa_src
2078 WHERE ppa_cess.action_type = 'X'
2079 AND ppa_cess.report_type = 'IE_CESSATION'
2080 AND ppa_cess.report_qualifier = 'IE'
2081 AND ppa_cess.payroll_action_id <> p_pact_id
2082 AND ppa_cess.payroll_action_id = cess_src.payroll_action_id
2083 AND cess_src.assignment_action_id = pai_cess.action_context_id
2084 AND pai_cess.action_context_type = 'AAP'
2085 AND pai_cess.action_information_category = 'IE CESS INFORMATION'
2086 AND pai_cess.source_id = paa_src.assignment_action_id
2087 AND cess_src.action_status = 'C'
2088 AND paa_src.tax_unit_id = g_paye_ref
2089 AND cess_src.tax_unit_id = g_paye_ref
2090 AND pai_cess.action_information8 = to_char(p_person_id)
2091 AND ((c_ppsn is not null and pai_cess.action_information22=c_ppsn) or (c_ppsn is null)) /* 8615992 */
2092 ;
2093
2094 -- Cursor to fetch action context id of cess for previous period of service.
2095
2096 CURSOR cur_get_last_cess(p_person_id NUMBER,p_termination_date DATE,p_pact NUMBER, c_assignment_id NUMBER,c_ppsn varchar2) IS
2097 SELECT fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
2098 paa.assignment_action_id),16))
2099 FROM pay_payroll_actions ppa,
2100 pay_assignment_actions paa,
2101 pay_action_information pai
2102 WHERE paa.assignment_action_id = pai.action_context_id
2103 AND pai.action_information_category = 'IE CESS INFORMATION'
2104 AND pai.action_context_type = 'AAP'
2105 AND paa.tax_unit_id = g_paye_ref
2106 AND fnd_date.canonical_to_date(pai.action_information3) between trunc(p_termination_date,'Y') and p_termination_date
2107 AND ppa.payroll_action_id = paa.payroll_action_id
2108 AND ppa.report_type = 'IE_CESSATION'
2109 AND ppa.report_category = 'ARCHIVE'
2110 AND ppa.report_qualifier = 'IE'
2111 AND ppa.effective_date between trunc(g_archive_end_date,'Y') and g_archive_end_date
2112 AND paa.payroll_action_id <> p_pact
2113 AND paa.action_status = 'C'
2114 AND pai.action_information8 = to_char(p_person_id)
2115 AND ((c_ppsn is not null and pai.action_information22=c_ppsn) or (c_ppsn is null)) /* 8615992 */
2116 ;
2117
2118
2119 CURSOR cur_get_cess_pact(p_cess_aact pay_assignment_actions.assignment_action_id%TYPE) IS
2120 SELECT paa.payroll_action_id
2121 FROM pay_assignment_actions paa
2122 WHERE paa.assignment_action_id = p_cess_aact;
2123
2124 -- cursor to retrieve payroll id
2125 CURSOR cur_assgn_payroll(p_assignment_id NUMBER,
2126 p_date_earned DATE) IS
2127 SELECT payroll_id,person_id,period_of_service_id
2128 FROM per_all_assignments_f
2129 WHERE assignment_id = p_assignment_id
2130 AND p_date_earned
2131 BETWEEN effective_start_date AND effective_end_date;
2132
2133
2134
2135 cursor csr_ppsn_override(p_asg_id number)
2136 is
2137 select aei_information1 PPSN_OVERRIDE
2138 from per_assignment_extra_info
2139 where assignment_id = p_asg_id
2140 and aei_information_category = 'IE_ASG_OVERRIDE';
2141
2142 l_ppsn_override per_assignment_extra_info.aei_information1%type;
2143
2144 CURSOR cur_child_pay_action_ppsn (p_person_id IN NUMBER,
2145 p_effective_date IN DATE,
2146 p_lat_act_seq IN NUMBER,
2147 c_ppsn_override per_assignment_extra_info.aei_information1%type) is
2148 SELECT /*+ USE_NL(paa, ppa) */
2149 fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
2150 paa.assignment_action_id),16))
2151 FROM pay_assignment_actions paa,
2152 pay_payroll_actions ppa
2153 WHERE paa.payroll_action_id = ppa.payroll_action_id
2154 AND paa.assignment_id in (select paaf.assignment_id
2155 from per_all_assignments_f paaf, per_assignment_extra_info paei
2156 where paaf.person_id = p_person_id
2157 and paaf.assignment_id=paei.assignment_id
2158 and paei.information_type = 'IE_ASG_OVERRIDE'
2159 and paei.aei_information1 = c_ppsn_override --'314678745T'
2160 )
2161 AND paa.tax_unit_id = g_paye_ref
2162 AND (paa.source_action_id is not null or ppa.action_type in ('I','V','B'))
2163 AND ppa.effective_date between trunc(p_effective_date,'Y') and p_effective_date
2164 AND paa.action_sequence > p_lat_act_seq
2165 AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
2166 AND paa.action_status = 'C';
2167
2168 l_child_pay_action_ppsn NUMBER;
2169
2170 l_actid NUMBER;
2171 l_action_context_id NUMBER;
2172 l_action_info_id NUMBER(15);
2173 l_assignment_action_id NUMBER;
2174 l_business_group_id NUMBER;
2175 l_chunk_number NUMBER;
2176 l_assignment_id NUMBER;
2177 l_date_earned DATE;
2178 l_ovn NUMBER;
2179 l_person_id NUMBER;
2180 l_pos_id NUMBER;
2181 l_record_count NUMBER;
2182 l_salary VARCHAR2(10);
2183 l_sequence NUMBER;
2184 l_child_pay_action NUMBER;
2185 l_payroll_id NUMBER;
2186 l_supp_flag VARCHAR2(1):='N';
2187 l_deceased_flag VARCHAR2(1):='N';
2188 l_proc VARCHAR2(50) := g_package || 'archive_data';
2189 l_lat_act_seq NUMBER;
2190 l_termination_date DATE;
2191 l_last_cess_action NUMBER;
2192 l_max_stat_balance NUMBER := 19;
2193 l_concat_sequence VARCHAR2(40);
2194 l_prev_src_id NUMBER;
2195 l_last_cess_pact NUMBER;
2196 -- 5386432
2197 l_supp_pymt_date DATE;
2198
2199
2200
2201 BEGIN
2202
2203 l_lat_act_seq := NULL;
2204 hr_utility.set_location('Entering'|| l_proc,10);
2205 hr_utility.set_location('Step '|| l_proc,20);
2206 hr_utility.set_location('p_assactid = ' || p_assactid,20);
2207
2208 -- retrieve the chunk number for the current assignment action
2209 SELECT paa.chunk_number,paa.assignment_id
2210 INTO l_chunk_number,l_assignment_id
2211 FROM pay_assignment_actions paa
2212 WHERE paa.assignment_action_id = p_assactid;
2213
2214 l_action_context_id := p_assactid;
2215 l_record_count := 0;
2216
2217 FOR csr_rec IN csr_assignment_actions(p_assactid)
2218 LOOP
2219 hr_utility.set_location('csr_rec.master_assignment_action_id = ' || csr_rec.master_assignment_action_id,20);
2220 hr_utility.set_location('csr_rec.pre_assignment_action_id = ' || csr_rec.pre_assignment_action_id,20);
2221 hr_utility.set_location('csr_rec.assignment_id = ' || csr_rec.assignment_id,20);
2222 hr_utility.set_location('csr_rec.date_earned = ' ||to_char( csr_rec.date_earned,'dd-mon-yyyy'),20);
2223 hr_utility.set_location('csr_rec.pre_effective_date = ' ||to_char( csr_rec.pre_effective_date,'dd-mon-yyyy'),20);
2224 hr_utility.set_location('csr_rec.time_period_id = ' || csr_rec.time_period_id,20);
2225
2226 OPEN cur_assgn_payroll(csr_rec.assignment_id,csr_rec.date_earned);
2227 FETCH cur_assgn_payroll INTO l_payroll_id,l_person_id,l_pos_id;
2228 CLOSE cur_assgn_payroll;
2229
2230 l_ppsn_override:=null;
2231 open csr_ppsn_override(csr_rec.assignment_id);
2232 fetch csr_ppsn_override into l_ppsn_override;
2233 close csr_ppsn_override;
2234 hr_utility.set_location('PPSN Override value = ' || l_ppsn_override,20);
2235
2236
2237 --Fetch the action sequence of latest payroll run child action locked by latest cess
2238 --For the assignment 4468864
2239 OPEN cur_get_latest_cess(g_archive_pact,l_person_id,l_ppsn_override);
2240 FETCH cur_get_latest_cess INTO l_concat_sequence;
2241
2242 IF cur_get_latest_cess%NOTFOUND THEN
2243 hr_utility.set_location('Action Sequence notfound = ' || l_lat_act_seq,21);
2244 l_lat_act_seq := 0;
2245 l_prev_src_id := 0;
2246 END IF;
2247
2248 l_lat_act_seq := nvl(substr(l_concat_sequence,1,15),0);
2249 l_prev_src_id := nvl(substr(l_concat_sequence,16),0);
2250
2251 hr_utility.set_location('Action Sequence = ' || l_lat_act_seq,21);
2252 CLOSE cur_get_latest_cess;
2253
2254 hr_utility.set_location('Action Sequence = ' || l_lat_act_seq,21);
2255
2256
2257
2258 l_child_pay_action_ppsn := NULL;
2259 OPEN cur_child_pay_action_ppsn(l_person_id,g_archive_end_date,l_lat_act_seq,l_ppsn_override);
2260 FETCH cur_child_pay_action_ppsn INTO l_child_pay_action_ppsn;
2261 hr_utility.set_location('Child Action PPSN ='||l_child_pay_action_ppsn,20);
2262 CLOSE cur_child_pay_action_ppsn;
2263
2264 l_child_pay_action := NULL;
2265 OPEN cur_child_pay_action(l_person_id,g_archive_end_date,l_lat_act_seq);
2266 FETCH cur_child_pay_action INTO l_child_pay_action;
2267
2268 if (l_child_pay_action_ppsn is null) THEN
2269 l_child_pay_action_ppsn:=l_child_pay_action;
2270 end if;
2271 hr_utility.set_location('Child Action PPSN after assigning ='||l_child_pay_action_ppsn,20);
2272
2273 -- hr_utility.set_location('Child Action PPSN ='|| l_child_pay_action_ppsn,24);
2274 hr_utility.set_location('Child Action ='||l_child_pay_action,24);
2275
2276 -------------- Moved here for bug 5386432 ----
2277 get_termination_date(p_action_context_id => p_assactid,
2278 p_assignment_id => csr_rec.assignment_id,
2279 p_person_id => l_person_id,
2280 p_date_earned => csr_rec.date_earned,
2281 p_termination_date => l_termination_date,
2282 p_supp_pymt_date => l_supp_pymt_date,
2283 p_supp_flag => l_supp_flag,
2284 p_deceased_flag => l_deceased_flag
2285 );
2286 OPEN cur_get_last_cess(l_person_id,l_termination_date,g_archive_pact,csr_rec.assignment_id,l_ppsn_override);
2287 FETCH cur_get_last_cess into l_last_cess_action;
2288 CLOSE cur_get_last_cess;
2289
2290 -- Fetch the Payroll action of Last cess 5005788
2291 OPEN cur_get_cess_pact(l_last_cess_action);
2292 FETCH cur_get_cess_pact INTO l_last_cess_pact;
2293 CLOSE cur_get_cess_pact;
2294 hr_utility.set_location(' l_termination_date = '||l_termination_date,30);
2295 hr_utility.set_location(' l_supp_pymt_date = '||l_supp_pymt_date,30);
2296 hr_utility.set_location(' l_supp_flag = '||l_supp_flag,30);
2297 hr_utility.set_location(' l_child_pay_action = '||l_child_pay_action,30);
2298 hr_utility.set_location(' l_record_count = '|| l_record_count,30);
2299 hr_utility.set_location(' csr_rec.assignment_id = '|| csr_rec.assignment_id,30);
2300 hr_utility.set_location(' l_assignment_id = '|| l_assignment_id,30);
2301 ------------------
2302
2303 IF ((l_child_pay_action IS NULL) and l_supp_flag = 'Y' ) THEN
2304 NULL;
2305 ELSE
2306 IF (l_record_count = 0 AND csr_rec.assignment_id = l_assignment_id)
2307 THEN
2308 hr_utility.set_location(' entered if of else ',30);
2309 -- Create child cess action to lock the child payroll process child action
2310 -- To avoid data corruption 4468864
2311 SELECT pay_assignment_actions_s.NEXTVAL
2312 INTO l_actid
2313 FROM dual;
2314
2315 hr_nonrun_asact.insact(
2316 lockingactid => l_actid
2317 , assignid => l_assignment_id
2318 , pactid => g_archive_pact
2319 , chunk => l_chunk_number
2320 , greid => g_paye_ref
2321 , prepayid => NULL
2322 , status => 'C'
2323 , source_act => p_assactid);
2324
2325 hr_utility.set_location('creating lock4 ' || l_actid || ' to ' || l_child_pay_action,30);
2326 -- bug 5386432, checks l_child_pay_action is not null, since for zero
2327 -- earnigns there will not child actions, so cant lock any
2328 IF l_child_pay_action IS NOT NULL THEN
2329 hr_nonrun_asact.insint(
2330 lockingactid => l_actid
2331 , lockedactid => l_child_pay_action);
2332 END IF;
2333
2334 pay_ie_cess_report.archive_cess_info(
2335 p_action_context_id => p_assactid,
2336 p_assignment_id => csr_rec.assignment_id, -- assignment_id
2337 p_payroll_id => l_payroll_id,
2338 p_date_earned => csr_rec.effective_date, -- date earned 9337590
2339 p_child_run_ass_act_id => l_child_pay_action_ppsn, /* knahdan */
2340 p_supp_flag => l_supp_flag,
2341 p_person_id => l_person_id,
2342 p_termination_date => l_termination_date,
2343 p_child_pay_action => l_child_pay_action_ppsn, -- child payroll assignment action id
2344 --p_source_id => l_child_pay_action,
2345 p_supp_pymt_date => l_supp_pymt_date,
2346 p_deceased_flag => l_deceased_flag,
2347 p_last_cess_action => l_last_cess_action
2348 , p_prev_src_id => l_prev_src_id);
2349
2350
2351
2352 hr_utility.set_location('sg Person Id ='||l_person_id,32);
2353 hr_utility.set_location('sg Termination Date ='||l_termination_date,33);
2354 hr_utility.set_location('sg Payroll action ='||g_archive_pact,34);
2355 hr_utility.set_location('sg cess action ='||l_last_cess_action,35);
2356
2357 IF l_last_cess_action IS NOT NULL THEN
2358 hr_nonrun_asact.insint(
2359 lockingactid => l_actid
2360 , lockedactid => l_last_cess_action);
2361 END IF;
2362
2363 END IF;
2364 END IF;
2365 CLOSE cur_child_pay_action;
2366 l_date_earned := csr_rec.date_earned;
2367 hr_utility.set_location('Before loop end for assignment '||csr_rec.assignment_id,80);
2368 END LOOP;
2369 hr_utility.set_location('Leaving '|| l_proc,80);
2370 END archive_data;
2371
2372
2373 PROCEDURE gen_header_xml
2374 IS
2375 l_string varchar2(32767) := NULL;
2376 l_clob PAY_FILE_DETAILS.FILE_FRAGMENT%TYPE;
2377 l_blob PAY_FILE_DETAILS.BLOB_FILE_FRAGMENT%TYPE;
2378
2379 l_proc VARCHAR2(100);
2380 l_payroll_action_id number;
2381 BEGIN
2382 l_proc := g_package || 'gen_header_xml';
2383 hr_utility.set_location ('Entering '||l_proc,1500);
2384
2385 l_payroll_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
2386 hr_utility.set_location('Inside pay_ie_cess_report.gen_header_xml,l_payroll_action_id: '||l_payroll_action_id,300);
2387
2388
2389
2390
2391 l_string := l_string || '<ROOT>' ;
2392
2393 l_clob := l_clob||l_string;
2394 IF l_clob IS NOT NULL THEN
2395 l_blob := c2b(l_clob);
2396 pay_core_files.write_to_magtape_lob(l_blob);
2397 END IF;
2398
2399 EXCEPTION
2400 WHEN Others THEN
2401 Hr_Utility.set_location('..'||'SQL-ERRM :'||SQLERRM,1214);
2402
2403 END gen_header_xml;
2404
2405 PROCEDURE gen_footer_xml
2406 IS
2407 l_buf VARCHAR2(2000);
2408 l_proc VARCHAR2(100);
2409 begin
2410 l_proc := g_package || 'gen_footer_xml';
2411 hr_utility.set_location ('Entering '||l_proc, 1520);
2412
2413 l_buf := l_buf || '</ROOT>'||EOL ;
2414 --
2415 pay_core_files.write_to_magtape_lob(l_buf);
2416 hr_utility.set_location ('Leaving '||l_proc, 1530);
2417
2418 end gen_footer_xml;
2419
2420
2421 PROCEDURE gen_body_xml
2422 IS
2423 l_string varchar2(32767) := NULL;
2424 l_clob PAY_FILE_DETAILS.FILE_FRAGMENT%TYPE;
2425 l_blob PAY_FILE_DETAILS.BLOB_FILE_FRAGMENT%TYPE;
2426
2427 l_payroll_action_id NUMBER;
2428 l_asg_action_id NUMBER;
2429
2430 l_assignment_id per_all_assignments_f.assignment_id%type;
2431 CURSOR cur_assignment_id (c_assignment_action_id pay_assignment_actions.assignment_action_id%type) is
2432 select assignment_id
2433 from pay_assignment_actions
2434 where assignment_action_id=c_assignment_action_id;
2435
2436 l_assignment_id per_all_assignments_f.assignment_id%type;
2437
2438
2439 CURSOR cur_cess_emp_details (c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
2440 select PAI_IEcess.ACTION_INFORMATION20 last_name,
2441 PAI_IEcess.ACTION_INFORMATION21 first_name,
2442 PAI_IEcess.ACTION_INFORMATION22 pps_no,
2443 PAI_IEcess.ACTION_INFORMATION23 works_no,
2444 PAI_IEcess.ACTION_INFORMATION24 date_of_commencement,
2445 PAI_IEcess.ACTION_INFORMATION2 supplementary_flag,
2446 PAI_IEcess.ACTION_INFORMATION3 date_of_leaving,
2447 PAI_IEcess.ACTION_INFORMATION29 gross_pay_total_frm_may09,
2448 PAI_IEcess.ACTION_INFORMATION30 income_levy_frm_may09,
2449 PAI_IEcess.ACTION_INFORMATION27 gross_pay_total_apr_09,
2450 PAI_IEcess.ACTION_INFORMATION28 income_levy_apr_09,
2451 PAI_IEcess.ACTION_INFORMATION25 gross_pay_total_final, /* 8615992 */
2452 PAI_IEcess.ACTION_INFORMATION26 income_levy_final,
2453 PAI_IEcess.ACTION_INFORMATION10 employer_tax_rep_name,
2454 PAI_IEcess.ACTION_INFORMATION11 employer_tax_addr1,
2455 PAI_IEcess.ACTION_INFORMATION12 employer_tax_addr2,
2456 PAI_IEcess.ACTION_INFORMATION13 employer_tax_addr3,
2457 PAI_IEcess.ACTION_INFORMATION14 employer_no,
2458 PAI_IEcess.ACTION_INFORMATION15 employer_tax_ref_phone,
2459 PAI_IEcess.ACTION_INFORMATION16 email,
2460 PAI_IEcess.ACTION_INFORMATION9 date_paid, /* 9337590 */
2461 PAI_IEcess.ACTION_INFORMATION17 usc,
2462 PAI_IEcess.ACTION_INFORMATION18 gross_uscable
2463 from pay_action_information PAI_IEcess
2464 where PAI_IEcess.action_context_id=c_assignment_action_id
2465 AND PAI_IEcess.ACTION_INFORMATION_CATEGORY = 'IE CESS INFORMATION';
2466
2467
2468 cur_cess_emp_details_rec cur_cess_emp_details%ROWTYPE;
2469
2470 l_employer_number varchar2(10);
2471 l_employer_name varchar2(30);
2472 l_employer_add1 varchar2(30);
2473 l_employer_add2 varchar2(30);
2474 l_employer_add3 varchar2(30);
2475 l_employer_contact varchar2(20);
2476 l_employer_phone varchar2(12);
2477
2478
2479 l_gross_pay number;
2480 l_gross_pay_adjust number;
2481 l_bik_prsi_taxable number;
2482 l_income_levy number;
2483 l_gross_pay_total number;
2484
2485 l_action_info_id NUMBER(15);
2486 l_ovn NUMBER(15);
2487
2488
2489
2490 BEGIN
2491 hr_utility.set_location(' Entering: pay_ie_cess_report.gen_body_xml: ', 270);
2492
2493 l_payroll_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
2494 l_asg_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID');
2495
2496
2497 hr_utility.set_location('l_payroll_action_id '||TO_CHAR(l_payroll_action_id),275);
2498 hr_utility.set_location('l_asg_action_id '||TO_CHAR(l_asg_action_id),280);
2499
2500
2501 OPEN cur_cess_emp_details(l_asg_action_id);
2502 FETCH cur_cess_emp_details into cur_cess_emp_details_rec;
2503 CLOSE cur_cess_emp_details;
2504
2505 l_string := l_string ||'<EMPLOYEE>';
2506
2507 l_string := l_string ||'<SURNAME>'|| test_XML(cur_cess_emp_details_rec.last_name) ||'</SURNAME>';
2508 l_string := l_string ||'<FIRST_NAME>'||test_XML( cur_cess_emp_details_rec.first_name) ||'</FIRST_NAME>';
2509 l_string := l_string ||'<PPSN>'|| cur_cess_emp_details_rec.pps_no ||'</PPSN>';
2510 l_string := l_string ||'<WORKS_NUM>'|| cur_cess_emp_details_rec.works_no ||'</WORKS_NUM>';
2511 IF to_number(to_char(fnd_date.canonical_to_date(cur_cess_emp_details_rec.date_of_commencement),'rrrr'))= to_number(to_char(fnd_date.canonical_to_date(cur_cess_emp_details_rec.date_paid),'YYYY')) THEN -- greater than 2009 /* knadhan */
2512 l_string := l_string ||'<HIRE_DATE>'|| to_char(fnd_date.canonical_to_date(cur_cess_emp_details_rec.date_of_commencement),'ddmmrr') ||'</HIRE_DATE>';
2513 END IF;
2514 --IF (to_number(to_char(fnd_date.canonical_to_date(cur_cess_emp_details_rec.date_of_leaving),'yyyy'))>=2009) THEN -- greater than 2009 /* knadhan */
2515 l_string := l_string ||'<CESS_DATE>'|| to_char(fnd_date.canonical_to_date(cur_cess_emp_details_rec.date_of_leaving),'ddmmrr') ||'</CESS_DATE>';
2516 --END IF;
2517 /* 8615992 */
2518 l_string := l_string ||'<SUPPLEMENTARY_FLAG>'|| cur_cess_emp_details_rec.supplementary_flag ||'</SUPPLEMENTARY_FLAG>';
2519 l_string := l_string ||'<GROSS_INCOME>'|| cur_cess_emp_details_rec.gross_pay_total_final ||'</GROSS_INCOME>';
2520 l_string := l_string ||'<LEVY>'|| cur_cess_emp_details_rec.income_levy_final||'</LEVY>';
2521 l_string := l_string ||'<GROSS_INCOME_TILL_APR>'|| cur_cess_emp_details_rec.gross_pay_total_apr_09 ||'</GROSS_INCOME_TILL_APR>';
2522 l_string := l_string ||'<LEVY_TILL_APR>'|| cur_cess_emp_details_rec.income_levy_apr_09||'</LEVY_TILL_APR>';
2523 l_string := l_string ||'<GROSS_INCOME_FRM_MAY>'|| cur_cess_emp_details_rec.gross_pay_total_frm_may09 ||'</GROSS_INCOME_FRM_MAY>';
2524 l_string := l_string ||'<LEVY_FRM_MAY>'|| cur_cess_emp_details_rec.income_levy_frm_may09||'</LEVY_FRM_MAY>';
2525 l_string := l_string ||'<ER_NAME>'|| test_XML(cur_cess_emp_details_rec.employer_tax_rep_name) ||'</ER_NAME>';
2526 l_string := l_string ||'<ADDR_LINE1>'||test_XML(cur_cess_emp_details_rec.employer_tax_addr1) ||'</ADDR_LINE1>';
2527 l_string := l_string ||'<ADDR_LINE2>'||test_XML(cur_cess_emp_details_rec.employer_tax_addr2)||'</ADDR_LINE2>';
2528 l_string := l_string ||'<ADDR_LINE3>'||test_XML(cur_cess_emp_details_rec.employer_tax_addr3) ||'</ADDR_LINE3>';
2529 l_string := l_string ||'<EMAIL>'|| cur_cess_emp_details_rec.email ||'</EMAIL>'; /* knadhan */
2530 l_string := l_string ||'<ER_NUM>'|| cur_cess_emp_details_rec.employer_no ||'</ER_NUM>';
2531 l_string := l_string ||'<ER_PHONE>'||cur_cess_emp_details_rec.employer_tax_ref_phone||'</ER_PHONE>';
2532 l_string := l_string ||'<PAYMENT_DATE>'||to_char(fnd_date.canonical_to_date(cur_cess_emp_details_rec.date_paid),'YYYY') ||'</PAYMENT_DATE>'; /* 9337590 */
2533 l_string := l_string ||'<USC>'|| cur_cess_emp_details_rec.usc||'</USC>'; /* USC */
2534 l_string := l_string ||'<GROSS_USCABLE>'|| cur_cess_emp_details_rec.gross_uscable||'</GROSS_USCABLE>'; /* USCable (Gross - Court Order) */
2535
2536 l_string := l_string ||'</EMPLOYEE>';
2537
2538
2539 hr_utility.set_location('Before leaving gen_body_xml: length(l_string) = '||length(l_string),290);
2540 l_clob := l_clob||l_string;
2541
2542 IF l_clob IS NOT NULL THEN
2543 l_blob := c2b(l_clob);
2544 pay_core_files.write_to_magtape_lob(l_blob);
2545 END IF;
2546
2547 EXCEPTION
2548 WHEN Others THEN
2549 Hr_Utility.set_location('..'||'SQL-ERRM :'||SQLERRM,1213);
2550 END gen_body_xml;
2551 end;
2552