[Home] [Help]
PACKAGE BODY: APPS.PAY_NO_TAX_LEVY
Source
1 PACKAGE BODY PAY_NO_TAX_LEVY AS
2 /* $Header: pynotaxlevy.pkb 120.5.12020000.2 2012/08/10 08:25:26 smeduri ship $ */
3 g_debug boolean := hr_utility.debug_enabled;
4 l_business_id NUMBER;
5 l_leg_emp_id NUMBER;
6 l_effective_date DATE;
7 l_bimonth_term VARCHAR2(10);
8 l_archive VARCHAR2(3);
9 l_payee_org NUMBER;
10 g_err_num NUMBER;
11
12 /* GET PARAMETER */
13 FUNCTION GET_PARAMETER(
14 p_parameter_string IN VARCHAR2
15 ,p_token IN VARCHAR2
16 ,p_segment_number IN NUMBER default NULL ) RETURN VARCHAR2
17 IS
18 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
19 l_start_pos NUMBER;
20 l_delimiter VARCHAR2(1):=' ';
21
22 BEGIN
23 IF g_debug THEN
24 hr_utility.set_location(' Entering Function GET_PARAMETER',10);
25 END IF;
26 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
27
28 IF l_start_pos = 0 THEN
29 l_delimiter := '|';
30 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
31 END IF;
32
33 IF l_start_pos <> 0 THEN
34 l_start_pos := l_start_pos + length(p_token||'=');
35 l_parameter := substr(p_parameter_string, l_start_pos,
36 instr(p_parameter_string||' ', l_delimiter,l_start_pos) - l_start_pos);
37
38 IF p_segment_number IS NOT NULL THEN
39 l_parameter := ':'||l_parameter||':';
40 l_parameter := substr(l_parameter,
41 instr(l_parameter,':',1,p_segment_number)+1,
42 instr(l_parameter,':',1,p_segment_number+1) -1
43 - instr(l_parameter,':',1,p_segment_number));
44 END IF;
45 END IF;
46
47 RETURN l_parameter;
48 IF g_debug THEN
49 hr_utility.set_location(' Leaving Function GET_PARAMETER',20);
50 END IF;
51
52 END GET_PARAMETER;
53
54
55 /* GET ALL PARAMETERS */
56 PROCEDURE GET_ALL_PARAMETERS(
57 p_payroll_action_id IN NUMBER
58 ,p_business_group_id OUT NOCOPY NUMBER
59 ,p_legal_employer_id OUT NOCOPY NUMBER
60 ,p_effective_date OUT NOCOPY DATE
61 ,p_bimonth_term OUT NOCOPY VARCHAR2
62 ,p_archive OUT NOCOPY VARCHAR2
63 ,p_payee_org OUT NOCOPY NUMBER
64 )IS
65
66
67 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
68 SELECT PAY_NO_TAX_LEVY.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER_ID')
69 ,PAY_NO_TAX_LEVY.GET_PARAMETER(legislative_parameters,'BIMONTH_TERM')
70 ,PAY_NO_TAX_LEVY.GET_PARAMETER(legislative_parameters,'ARCHIVE')
71 ,PAY_NO_TAX_LEVY.GET_PARAMETER(legislative_parameters,'THIRD_PARTY_PAYEE')
72 ,effective_date
73 ,business_group_id
74 FROM pay_payroll_actions
75 WHERE payroll_action_id = p_payroll_action_id;
76
77 BEGIN
78
79 OPEN csr_parameter_info (p_payroll_action_id);
80 FETCH csr_parameter_info INTO p_legal_employer_id ,p_bimonth_term ,p_archive,p_payee_org ,p_effective_date ,p_business_group_id;
81 CLOSE csr_parameter_info;
82 IF g_debug THEN
83 hr_utility.set_location(' Leaving Procedure GET_ALL_PARAMETERS',30);
84 END IF;
85
86 END GET_ALL_PARAMETERS;
87
88
89 /* RANGE CODE */
90 PROCEDURE RANGE_CODE (pactid IN NUMBER
91 ,sqlstr OUT NOCOPY VARCHAR2)
92 IS
93
94 -- Variable declarations
95 l_count NUMBER := 0;
96 l_action_info_id NUMBER;
97 l_ovn NUMBER;
98 l_bimonth_year NUMBER;
99 l_le_org_num VARCHAR2(240);
100 l_le_name VARCHAR2(240);
101 l_payee_org_name hr_all_organization_units.NAME%type;
102
103
104 -- cursor to get Legal Employer Name and Org Num
105
106 cursor csr_le_name ( l_leg_emp_id NUMBER ) is
107 select hou.name
108 from hr_organization_units hou
109 where hou.organization_id = l_leg_emp_id;
110
111
112 cursor csr_org_number ( l_leg_emp_id NUMBER ) is
113 select hoi.org_information1
114 from hr_organization_information hoi
115 where hoi.organization_id = l_leg_emp_id
116 and hoi.org_information_context = to_char('NO_LEGAL_EMPLOYER_DETAILS');
117
118
119 --cursor to get the third_party_name
120 cursor csr_third_party_name(l_payee_org number) is
121 select distinct haou.name name from hr_all_organization_units haou
122 where haou.ORGANIZATION_ID=l_payee_org;
123
124 --cursor to check current archive exists
125 cursor csr_count is
126 select count(*)
127 from pay_action_information
128 where action_information_category = 'EMEA REPORT DETAILS'
129 and action_information1 = 'PYNOTAXLEVYA'
130 and action_context_id = pactid;
131
132 --cursor to get the bimonth year
133 cursor csr_bimonth_year is
134 select to_char(l_effective_date,'RRRR') from dual;
135
136
137 BEGIN
138 IF g_debug THEN
139 hr_utility.set_location(' Entering Procedure RANGE_CODE',40);
140 END IF;
141 -- the sql string to return
142 sqlstr := 'SELECT DISTINCT person_id
143 FROM per_people_f ppf
144 ,pay_payroll_actions ppa
145 WHERE ppa.payroll_action_id = :payroll_action_id
146 AND ppa.business_group_id = ppf.business_group_id
147 ORDER BY ppf.person_id';
148
149 -- fetch the data for the REPORT DETAILS
150 PAY_NO_TAX_LEVY.GET_ALL_PARAMETERS(
151 pactid
152 ,l_business_id
153 ,l_leg_emp_id
154 ,l_effective_date
155 ,l_bimonth_term
156 ,l_archive
157 ,l_payee_org) ;
158
159 -- check if we have to archive again
160 if (l_archive = 'Y') then
161
162 -- check if record for current archive exists
163 open csr_count;
164 fetch csr_count into l_count;
165 close csr_count;
166
167 open csr_third_party_name(l_payee_org);
168 fetch csr_third_party_name into l_payee_org_name;
169 close csr_third_party_name;
170
171 -- archive Report Details only if no record exosts
172 IF (l_count < 1) THEN
173 -- fetch LE name and LE Org Num from LE ID
174 OPEN csr_le_name (l_leg_emp_id) ;
175 FETCH csr_le_name INTO l_le_name ;
176 CLOSE csr_le_name ;
177
178 OPEN csr_org_number (l_leg_emp_id) ;
179 FETCH csr_org_number INTO l_le_org_num ;
180 CLOSE csr_org_number ;
181
182 -- get the bimonth year
183 open csr_bimonth_year;
184 fetch csr_bimonth_year into l_bimonth_year;
185 close csr_bimonth_year;
186
187 -- archive the report details
188
189 pay_action_information_api.create_action_information (
190 p_action_information_id => l_action_info_id -- out parameter
191 ,p_object_version_number => l_ovn -- out parameter
192 ,p_action_context_id => pactid -- context id = payroll action id (of Archive)
193 ,p_action_context_type => 'PA' -- context type
194 ,p_effective_date => l_effective_date -- Date of running the archive
195 ,p_action_information_category => 'EMEA REPORT DETAILS' -- Information Category
196 ,p_tax_unit_id => l_leg_emp_id -- Legal Employer ID
197 ,p_jurisdiction_code => NULL -- Tax Municipality ID
198 ,p_action_information1 => 'PYNOTAXLEVYA' -- Conc Prg Short Name
199 ,p_action_information2 => NULL -- Local Unit ID
200 ,p_action_information3 => l_le_name -- Legal Employer Name
201 ,p_action_information4 => l_le_org_num -- Legal Employer Organization Number
202 ,p_action_information5 => l_bimonth_term -- Reporting Bimonth Term
203 ,p_action_information6 => l_bimonth_year -- Reporting Bimonth Year
204 ,p_action_information7 => l_business_id -- Business Group ID
205 ,p_action_information8 => l_payee_org --Third Party id
206 ,p_action_information9 => l_payee_org_name); --Third Party name
207
208 END IF; -- l_count < 1
209
210 END IF; -- l_archive = 'Y'
211 IF g_debug THEN
212 hr_utility.set_location(' Leaving Procedure RANGE_CODE',50);
213 END IF;
214
215 END RANGE_CODE;
216
217 -------------------------------------------------------------------------------------------------------------------------
218 /* INITIALIZATION CODE */
219 PROCEDURE INITIALIZATION_CODE(p_payroll_action_id IN NUMBER)
220 IS
221
222 BEGIN
223 IF g_debug THEN
224 hr_utility.set_location(' Entering Procedure INITIALIZATION_CODE',80);
225 END IF;
226
227 NULL;
228 IF g_debug THEN
229 hr_utility.set_location(' Leaving Procedure INITIALIZATION_CODE',90);
230 END IF;
231 exception when others then
232 g_err_num := SQLCODE;
233 IF g_debug THEN
234 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In INITIALIZATION_CODE',180);
235 END IF;
236
237 END INITIALIZATION_CODE;
238
239 -------------------------------------------------------------------------------------------------------------------------
240 /* ASSIGNMENT ACTION CODE */
241 PROCEDURE ASSIGNMENT_ACTION_CODE
242 (p_payroll_action_id IN NUMBER
243 ,p_start_person IN NUMBER
244 ,p_end_person IN NUMBER
245 ,p_chunk IN NUMBER)
246 IS
247
248 ---------------------------------------------------------------------------------------------------------------
249 CURSOR csr_assignments
250 (p_payroll_action_id NUMBER,
251 p_leg_emp_id NUMBER,
252 p_start_person NUMBER,
253 p_end_person NUMBER,
254 l_start_date DATE,
255 l_end_date DATE,
256 p_payee_org NUMBER)
257 IS
258
259 SELECT
260 assact.ASSIGNMENT_ID asg_id
261 ,assact.assignment_action_id asg_act_id
262 ,assact.TAX_UNIT_ID tax_unit_id
263 ,prr.LOCAL_UNIT_ID local_unit_id
264 --,prr.JURISDICTION_CODE tax_mun_id
265 ,(select act_con.CONTEXT_VALUE from pay_action_contexts act_con,ff_contexts con
266 where con.CONTEXT_NAME = 'JURISDICTION_CODE'
267 AND act_con.CONTEXT_ID = con.CONTEXT_ID
268 AND act_con.ASSIGNMENT_ACTION_ID = assact.ASSIGNMENT_ACTION_ID
269 AND act_con.ASSIGNMENT_ID = assact.ASSIGNMENT_ID) tax_mun_id
270 ,prrv.result_value payee_id -- bug 14249180
271
272 FROM
273 pay_assignment_actions assact
274 ,pay_assignment_actions assact1
275 ,pay_payroll_actions ppa
276 ,pay_payroll_actions ppa2
277 ,pay_payroll_actions ppa3
278 ,per_all_assignments_f asg
279 ,pay_element_types_f ele
280 ,pay_run_results prr
281 ,pay_run_result_values prrv
282 ,pay_input_values_f inpv
283 ,pay_action_interlocks pai
284
285
286 WHERE -- initial conditions
287
288 ppa.payroll_action_id = p_payroll_action_id
289
290 -- for 2nd pay payroll act table
291 AND ppa2.date_earned between l_start_date and l_end_date
292 AND ppa2.action_type IN ('R','Q') -- Payroll Run or Quickpay Run
293
294 -- for asg act table
295 AND assact.PAYROLL_ACTION_ID = ppa2.PAYROLL_ACTION_ID
296 AND assact.TAX_UNIT_ID = p_leg_emp_id
297 AND assact.action_status = 'C' -- Completed
298 AND assact.source_action_id IS NOT NULL -- Not Master Action
299
300
301 -- for asg table
302 AND assact.ASSIGNMENT_ID = asg.ASSIGNMENT_ID
303 --AND ppa.date_earned between asg.EFFECTIVE_START_DATE and asg.EFFECTIVE_END_DATE
304 --AND ppa.effective_date between asg.EFFECTIVE_START_DATE and asg.EFFECTIVE_END_DATE
305 AND ppa2.effective_date between asg.EFFECTIVE_START_DATE and asg.EFFECTIVE_END_DATE -- Bug 5116907 fix
306 AND asg.person_id BETWEEN p_start_person AND p_end_person
307
308 -- for run results
309 AND assact.ASSIGNMENT_ACTION_ID = prr.ASSIGNMENT_ACTION_ID
310
311 --for prepayments
312 AND assact1.action_status IN ('C','S') -- 10229512
313 AND assact1.assignment_action_id = pai.locking_action_id
314 AND assact1.payroll_action_id = ppa3.payroll_action_id
315 AND ppa3.action_type IN ('P','U')
316 AND ppa3.date_earned between l_start_date and l_end_date
317
318 AND assact.ASSIGNMENT_ACTION_ID = pai.locked_action_id
319
320 -- for element 'Wage Attachment Tax Levy'
321 AND prr.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
322 AND ele.ELEMENT_NAME = 'Wage Attachment Tax Levy'
323 AND ele.LEGISLATION_CODE = 'NO'
324 --AND ppa.date_earned between ele.EFFECTIVE_START_DATE and ele.EFFECTIVE_END_DATE
325 AND ppa.effective_date between ele.EFFECTIVE_START_DATE and ele.EFFECTIVE_END_DATE
326
327 AND prr.RUN_RESULT_ID = prrv.RUN_RESULT_ID
328 AND inpv.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
329 AND inpv.NAME = 'Third Party Payee'
330 AND inpv.LEGISLATION_CODE = 'NO'
331 --AND ppa.date_earned between inpv.EFFECTIVE_START_DATE and inpv.EFFECTIVE_END_DATE
332 AND prrv.INPUT_VALUE_ID = inpv.INPUT_VALUE_ID
333 --and haou.organization_id=prrv5.result_value
334 AND prrv.result_value like nvl(to_char(p_payee_org),prrv.result_value)
335
336
337 ORDER BY assact.assignment_id ;
338
339 --to get the next action_id
340 cursor csr_actid is
341 SELECT pay_assignment_actions_s.NEXTVAL FROM dual;
342 ---------------------------------------------------------------------------------------------------------------
343 l_count NUMBER := 0;
344 l_action_info_id NUMBER;
345 l_ovn NUMBER;
346 l_bimonth_year NUMBER;
347 l_le_org_num VARCHAR2(240);
348 l_le_name VARCHAR2(240);
349 l_start_date DATE;
350 l_end_date DATE;
351 l_actid NUMBER;
352 l_tax_mun_id NUMBER;
353 l_local_unit_id NUMBER;
354
355
356
357 BEGIN
358 IF g_debug THEN
359 hr_utility.set_location(' Entering Procedure ASSIGNMENT_ACTION_CODE',60);
360 END IF;
361 -- fetch the data for the REPORT DETAILS
362 PAY_NO_TAX_LEVY.GET_ALL_PARAMETERS(
363 p_payroll_action_id
364 ,l_business_id
365 ,l_leg_emp_id
366 ,l_effective_date
367 ,l_bimonth_term
368 ,l_archive
369 ,l_payee_org) ;
370
371 -- check if we have to archive again
372 IF (l_archive = 'Y')
373
374 THEN
375 --fnd_file.put_line(fnd_file.log,'SUGARG: l_archive = Y');
376
377 -- get the period start and end dates
378
379 select to_date(decode(l_bimonth_term,'01','JAN','02','MAR','03','MAY','04','JUL','05','SEP','06','NOV','00','JAN')
380 ||to_char(l_effective_date,'YY'),'MONYY') into l_start_date from dual;
381
382 IF l_bimonth_term IS NOT NULL AND l_bimonth_term <> '00' THEN
383
384 select last_day(to_date(decode(l_bimonth_term,'01','FEB','02','APR','03','JUN','04','AUG','05','OCT','06','DEC','DEC')
385 ||to_char(l_effective_date,'YY'),'MONYY')) into l_end_date from dual;
386 ELSE
387 l_end_date := l_effective_date;
388
389 END IF;
390
391 -- fetch assignments details
392
393 FOR csr_rec IN csr_assignments
394 ( p_payroll_action_id
395 ,l_leg_emp_id
396 ,p_start_person
397 ,p_end_person
398 ,l_start_date
399 ,l_end_date
400 ,l_payee_org)
401 LOOP
402 open csr_actid;
403 fetch csr_actid into l_actid;
404 close csr_actid;
405
406 -- Create the archive assignment action
407 hr_nonrun_asact.insact(l_actid ,csr_rec.asg_id ,p_payroll_action_id ,p_chunk ,NULL);
408
409 -- Creating Initial Archive Entries
410 pay_action_information_api.create_action_information (
411
412 p_action_information_id => l_action_info_id -- out parameter
413 ,p_object_version_number => l_ovn -- out parameter
414 ,p_action_context_id => l_actid -- context id = assignment action id (of Archive)
415 ,p_action_context_type => 'AAP' -- context type
416 ,p_effective_date => l_effective_date -- Date of running the archive
417 ,p_assignment_id => csr_rec.asg_id -- Assignment ID
418 ,p_action_information_category => 'EMEA REPORT INFORMATION' -- Information Category
419 ,p_tax_unit_id => l_leg_emp_id -- Legal Employer ID
420 ,p_jurisdiction_code => csr_rec.tax_mun_id -- Tax Municipality ID
421 ,p_action_information1 => 'PYNOTAXLEVYA' -- Conc Prg Short Name
422 ,p_action_information2 => csr_rec.local_unit_id -- Local Unit ID
423 ,p_action_information3 => p_payroll_action_id -- payroll action id (of Archive)
424 ,p_action_information4 => csr_rec.asg_act_id -- Original / Main Asg Action ID
425 ,p_action_information30 => csr_rec.payee_id -- bug 14249180
426 );
427
428 END LOOP;
429
430 END IF; -- l_archive = 'Y'
431
432 IF g_debug THEN
433 hr_utility.set_location(' Leaving Procedure ASSIGNMENT_ACTION_CODE',70);
434 END IF;
435
436 END ASSIGNMENT_ACTION_CODE;
437
438
439 -------------------------------------------------------------------------------------------------------------------------
440 /* ARCHIVE CODE */
441 PROCEDURE ARCHIVE_CODE(p_assignment_action_id IN NUMBER
442 ,p_effective_date IN DATE)
443 IS
444
445 -- Cursor to get the action_information_id and original/main assignment_action_id
446
447 cursor csr_get_act_info (p_assignment_action_id NUMBER , p_effective_date DATE) is
448 select to_number(ACTION_INFORMATION_ID) , to_number(ACTION_INFORMATION4)
449 ,to_number(ACTION_INFORMATION3),to_number(ACTION_INFORMATION30) -- bug 14249180
450 from pay_action_information
451 where ACTION_INFORMATION_CATEGORY = 'EMEA REPORT INFORMATION'
452 and ACTION_INFORMATION1 = 'PYNOTAXLEVYA'
453 and ACTION_CONTEXT_TYPE = 'AAP'
454 and ACTION_CONTEXT_ID = p_assignment_action_id
455 and EFFECTIVE_DATE = p_effective_date ;
456
457 --varibale declaration
458 l_third_party_id NUMBER;
459 l_main_asg_act_id NUMBER;
460 l_payroll_action_id NUMBER ;
461
462 -- cursor to get the assignment,person details and run values
463
464 CURSOR csr_asg_details (p_assignment_action_id NUMBER,p_payee_id number) IS
465 SELECT
466 per.last_name last_name
467 ,per.first_name first_name
468 ,per.order_name order_Name
469 ,per.title per_title
470 ,per.date_of_birth dob
471 ,per.original_date_of_hire doh
472 ,per.PERSON_ID per_id
473 ,per.NATIONAL_IDENTIFIER per_ni
474 ,per.EMPLOYEE_NUMBER emp_no
475 ,per.business_group_id bg_id
476 ,prrv1.result_value res_val_1 -- Pay Value
477 ,prrv2.result_value res_val_2 -- Income Year
478 ,prrv3.result_value res_val_3 -- Deduction Percentage
479 ,prrv4.result_value res_val_4 -- Fixed Deduction Amount
480 ,prrv5.result_value res_val_5 -- Third Party Payee
481
482 FROM
483 pay_assignment_actions assact
484 ,pay_payroll_actions ppa
485 ,per_all_assignments_f asg
486 ,per_all_people_f per
487 ,pay_element_types_f ele
488 ,pay_input_values_f inpv1
489 ,pay_input_values_f inpv2
490 ,pay_input_values_f inpv3
491 ,pay_input_values_f inpv4
492 ,pay_input_values_f inpv5
493 ,pay_run_results prr
494 ,pay_run_result_values prrv1
495 ,pay_run_result_values prrv2
496 ,pay_run_result_values prrv3
497 ,pay_run_result_values prrv4
498 ,pay_run_result_values prrv5
499
500
501 WHERE assact.ASSIGNMENT_ACTION_ID = p_assignment_action_id
502 AND assact.ASSIGNMENT_ID = asg.ASSIGNMENT_ID
503 AND ppa.date_earned between asg.EFFECTIVE_START_DATE and asg.EFFECTIVE_END_DATE
504 AND asg.PERSON_ID = per.PERSON_ID
505 AND ppa.date_earned between per.EFFECTIVE_START_DATE and per.EFFECTIVE_END_DATE
506 AND assact.PAYROLL_ACTION_ID = ppa.PAYROLL_ACTION_ID
507 AND assact.ASSIGNMENT_ACTION_ID = prr.ASSIGNMENT_ACTION_ID
508
509 -- for element 'Wage Attachment Tax Levy'
510 AND prr.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
511 AND ele.ELEMENT_NAME = 'Wage Attachment Tax Levy'
512 AND ele.LEGISLATION_CODE = 'NO'
513 AND ppa.date_earned between ele.EFFECTIVE_START_DATE and ele.EFFECTIVE_END_DATE
514
515 -- for input value 'Pay Value'
516 AND prr.RUN_RESULT_ID = prrv1.RUN_RESULT_ID
517 AND inpv1.NAME = 'Pay Value'
518 AND inpv1.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
519 AND inpv1.LEGISLATION_CODE = 'NO'
520 AND ppa.date_earned between inpv1.EFFECTIVE_START_DATE and inpv1.EFFECTIVE_END_DATE
521 AND prrv1.INPUT_VALUE_ID = inpv1.INPUT_VALUE_ID
522
523 -- for input value 'Income Year'
524 AND prr.RUN_RESULT_ID = prrv2.RUN_RESULT_ID
525 AND inpv2.NAME = 'Income Year'
526 AND inpv2.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
527 AND inpv2.LEGISLATION_CODE = 'NO'
528 AND ppa.date_earned between inpv2.EFFECTIVE_START_DATE and inpv2.EFFECTIVE_END_DATE
529 AND prrv2.INPUT_VALUE_ID = inpv2.INPUT_VALUE_ID
530
531 -- for input value 'Deduction Percentage'
532 AND prr.RUN_RESULT_ID = prrv3.RUN_RESULT_ID
533 AND inpv3.NAME = 'Deduction Percentage'
534 AND inpv3.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
535 AND inpv3.LEGISLATION_CODE = 'NO'
536 AND ppa.date_earned between inpv3.EFFECTIVE_START_DATE and inpv3.EFFECTIVE_END_DATE
537 AND prrv3.INPUT_VALUE_ID = inpv3.INPUT_VALUE_ID
538
539 -- for input value 'Fixed Deduction Amount'
540 AND prr.RUN_RESULT_ID = prrv4.RUN_RESULT_ID
541 AND inpv4.NAME = 'Fixed Deduction Amount'
542 AND inpv4.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
543 AND inpv4.LEGISLATION_CODE = 'NO'
544 AND ppa.date_earned between inpv4.EFFECTIVE_START_DATE and inpv4.EFFECTIVE_END_DATE
545 AND prrv4.INPUT_VALUE_ID = inpv4.INPUT_VALUE_ID
546
547 -- for input value 'Third Party Payee'
548 AND prr.RUN_RESULT_ID = prrv5.RUN_RESULT_ID
549 AND inpv5.NAME = 'Third Party Payee'
550 AND inpv5.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
551 AND inpv5.LEGISLATION_CODE = 'NO'
552 AND ppa.date_earned between inpv5.EFFECTIVE_START_DATE and inpv5.EFFECTIVE_END_DATE
553 AND prrv5.INPUT_VALUE_ID = inpv5.INPUT_VALUE_ID
554 and prrv5.result_value = p_payee_id;
555
556
557
558 -- cursor to get Third Party Destination Acc Number
559
560 cursor csr_third_party_dest_acc (p_organization_id NUMBER) is
561 select segment6
562 from pay_external_accounts acc
563 ,pay_org_payment_methods_f pay_org
564 ,hr_organization_information hoi
565 where hoi.organization_id = p_organization_id
566 and hoi.org_information_context = 'NO_THIRD_PARTY_PAYMENT'
567 and pay_org.org_payment_method_id = hoi.org_information2
568 and pay_org.pmeth_information1 = 'DESTINATION'
569 and acc.external_account_id = pay_org.external_account_id;
570
571
572 -- cursor to get the third party loaction
573
574 cursor csr_third_party_loc (p_organization_id NUMBER) is
575 select loc.address_line_1 line_1
576 ,loc.address_line_2 line_2
577 ,loc.address_line_3 line_3
578 ,hr_general.decode_fnd_comm_lookup('NO_POSTAL_CODE',loc.postal_code) post_code
579 from hr_locations_all loc
580 ,hr_all_organization_units hou
581 where hou.organization_id = p_organization_id
582 and loc.location_id = hou.location_id
583 and loc.style = 'NO';
584
585 --cursor to get the payroll_action_id
586 cursor csr_payroll_action_id is
587 SELECT payroll_action_id
588 FROM pay_assignment_actions
589 WHERE assignment_action_id = p_assignment_action_id ;
590
591 --cursor to get the third party name
592 cursor csr_third_party_name is
593 select name from hr_organization_units
594 where organization_id = l_third_party_id ;
595
596 -- cursor to fetch the balance value of TAX DEDUCTION BASIS
597 cursor csr_tax_dedn_basis is
598 select
599 pay_balance_pkg.get_value(pay_no_emp_cont.get_defined_balance_id('Tax Deduction Basis','_ASG_PTD'),
600 l_main_asg_act_id)
601 from dual;
602
603 -- Variable declaration
604
605 l_action_info_id NUMBER;
606 l_ovn NUMBER;
607
608 rec_asg_detail csr_asg_details%rowtype;
609 rec_loc_detail csr_third_party_loc%rowtype;
610
611 l_count NUMBER := 0;
612 l_third_party_name VARCHAR2(240);
613 l_third_party_dest_acc VARCHAR2(150);
614 l_tax_dedn_basis NUMBER;
615 l_new_payroll_action_id NUMBER; -- To store the payroll action id created.
616 l_payee_id number; -- bug 14249180
617
618 BEGIN
619 IF g_debug THEN
620 hr_utility.set_location(' Entering Procedure ARCHIVE_CODE',380);
621 END IF;
622
623 -- get the action_information_id and original/main assignment_action_id for this asg_act_id
624 OPEN csr_get_act_info (p_assignment_action_id , p_effective_date );
625 FETCH csr_get_act_info INTO l_action_info_id , l_main_asg_act_id ,l_new_payroll_action_id,l_payee_id;
626 CLOSE csr_get_act_info ;
627
628 -- fetch the data for the REPORT DETAILS
629 PAY_NO_TAX_LEVY.GET_ALL_PARAMETERS(
630 l_new_payroll_action_id
631 ,l_business_id
632 ,l_leg_emp_id
633 ,l_effective_date
634 ,l_bimonth_term
635 ,l_archive
636 ,l_payee_org) ;
637
638
639 open csr_payroll_action_id;
640 fetch csr_payroll_action_id into l_payroll_action_id;
641 close csr_payroll_action_id;
642
643 -- fetch the data
644
645 -- check if we have to archive again
646 IF (l_archive = 'Y')
647
648 THEN
649
650 -- fetch assignment details
651 OPEN csr_asg_details (l_main_asg_act_id,l_payee_id) ;
652 FETCH csr_asg_details INTO rec_asg_detail ;
653 CLOSE csr_asg_details ;
654
655 l_third_party_id := to_number(rec_asg_detail.res_val_5) ;
656
657 -- to get the Third Party Name
658 open csr_third_party_name;
659 fetch csr_third_party_name into l_third_party_name;
660 close csr_third_party_name;
661
662 -- to get the third party Destination acc no.
663 OPEN csr_third_party_dest_acc (l_third_party_id) ;
664 FETCH csr_third_party_dest_acc INTO l_third_party_dest_acc ;
665 CLOSE csr_third_party_dest_acc ;
666
667
668 -- to get the third party location details
669 OPEN csr_third_party_loc (l_third_party_id) ;
670 FETCH csr_third_party_loc INTO rec_loc_detail;
671 CLOSE csr_third_party_loc ;
672
673 -- to fetch the balance value of TAX DEDUCTION BASIS
674 open csr_tax_dedn_basis;
675 fetch csr_tax_dedn_basis into l_tax_dedn_basis;
676 close csr_tax_dedn_basis;
677
678 l_third_party_dest_acc := substr(l_third_party_dest_acc,1,4)||'.'||
679 substr(l_third_party_dest_acc,5,2)||'.'||
680 substr(l_third_party_dest_acc,7,5) ;
681
682 -- Updating the Initial Archive Entries
683 pay_action_information_api.update_action_information (
684 p_action_information_id => l_action_info_id -- in parameter
685 ,p_object_version_number => l_ovn -- in out parameter
686 ,p_action_information5 => l_third_party_id --Third Party ID (Tax Collector ID)
687 ,p_action_information6 => l_third_party_name --Third Party Name (Tax Collector's Name)
688 ,p_action_information7 => rec_loc_detail.line_1 --Third Party Address Line 1
689 ,p_action_information8 => rec_loc_detail.line_2 --Third Party Address Line 2
690 ,p_action_information9 => rec_loc_detail.line_3 --Third Party Address Line 3
691 ,p_action_information10 => rec_loc_detail.post_code --Third Party Postal Code + City
692 ,p_action_information11 => l_third_party_dest_acc --Third Party Destination Bank Account Number (Formatted)
693 ,p_action_information12 => rec_asg_detail.bg_id --Business Group ID
694 ,p_action_information13 => rec_asg_detail.per_id --PERSON_ID
695 ,p_action_information14 => rec_asg_detail.per_ni --Person NATIONAL_IDENTIFIER
696 ,p_action_information15 => rec_asg_detail.last_name --Person Lastname
697 ,p_action_information16 => rec_asg_detail.first_name --Person Firstname
698 ,p_action_information17 => rec_asg_detail.order_name --Person Ordername
699 ,p_action_information18 => rec_asg_detail.emp_no --Person Employee number
700 ,p_action_information19 => rec_asg_detail.dob --Person date of birth
701 ,p_action_information20 => rec_asg_detail.doh --Person date of hiring
702 ,p_action_information21 => rec_asg_detail.per_title --Person title
703 ,p_action_information22 => rec_asg_detail.res_val_2 --Income Year (Input Value)
704 ,p_action_information23 => rec_asg_detail.res_val_3 --Percentage (Input Value)
705 ,p_action_information24 => rec_asg_detail.res_val_4 --Amount (Input Value)
706 ,p_action_information25 => rec_asg_detail.res_val_1 --Deducted This Period (Input Value)
707 ,p_action_information26 => l_tax_dedn_basis --Tax Deduction Basis (Balance Value)
708 ,p_action_information27 => NULL );
709
710
711 END IF; -- l_archive = 'Y'
712
713
714 IF g_debug THEN
715 hr_utility.set_location(' Leaving Procedure ARCHIVE_CODE',390);
716 END IF;
717
718 END ARCHIVE_CODE;
719
720 -------------------------------------------------------------------------------------------------------------------------
721 procedure populate_details(p_payroll_action_id in varchar2,
722 p_template_name in varchar2,
723 p_xml out nocopy clob) is
724
725 l_employer hr_all_organization_units.name%type;
726 l_orgnumber hr_organization_information.org_information1%type;
727 l_term varchar2(30);
728 l_year varchar2(8);
729 l_thirdparty_id pay_action_information.ACTION_INFORMATION5%type:='-999';
730 l_payroll_action_id varchar2(150);
731 l_archiver varchar2(150);
732 xml_ctr number;
733 l_IANA_charset VARCHAR2 (50);
734
735 cursor csr_legalemployer is
736 select pai.action_information3 employer,pai.action_information4 orgnumber,
737 pai.action_information5 term,pai.action_information6 year,
738 pai.effective_date || ' (' ||pai.action_context_id||')' archiver
739 from pay_action_information pai
740 where to_char(pai.action_context_id)=l_payroll_action_id;
741
742 --Bug 5116700 fix - Taking the sum of TaxDeductionBasis
743 cursor csr_emp is
744 select thirdparty_id,thirdparty_name,thirdparty_address,
745 bankaccountno,ni,last_name,first_name,order_name,
746 emp_no,emp_dob,emp_doh,emp_title,
747 sum(TaxDeductionBasis) TaxDeductionBasis,
748 incomeyear,percentage,amount,
749 sum(deductedthisperiod) deductedthisperiod from
750 (select pai.action_information5 thirdparty_id
751 ,pai.action_information6 thirdparty_name,
752 decode(pai.action_information7,null,'',',')
753 ||pai.action_information7||
754 decode(pai.action_information8,null,'',',')
755 ||pai.action_information8||
756 decode(pai.action_information9,null,'',',')
757 ||pai.action_information9||
758 decode(pai.action_information10,null,'',',')
759 ||pai.action_information10 thirdparty_address
760 ,pai.action_information11 bankaccountno
761 ,pai.action_information14 ni
762 ,action_information15 last_name
763 ,action_information16 first_name
764 ,action_information17 order_name
765 ,action_information18 emp_no
766 ,action_information19 emp_dob
767 ,action_information20 emp_doh
768 ,action_information21 emp_title
769 ,pai.action_information26 TaxDeductionBasis
770 ,pai.action_information22 incomeyear
771 ,pai.action_information23 percentage
772 ,pai.action_information24 amount
773 ,pai.action_information25 deductedthisperiod
774 from pay_action_information pai
775 where pai.action_information3=l_payroll_action_id)
776 group by
777 thirdparty_id,thirdparty_name,thirdparty_address,
778 bankaccountno,ni,last_name,first_name,order_name,
779 emp_no,emp_dob,emp_doh,emp_title,
780 incomeyear,percentage,amount
781 order by thirdparty_id,order_name;
782
783
784
785
786 begin
787
788 xml_ctr := 0;
789 /*pgopal - picking the charset dynamically from the db*/
790 l_IANA_charset := HR_NO_UTILITY.get_IANA_charset ;
791 --xml_tab(xml_ctr).xmlstring := '<?xml version="1.0" encoding="utf-8"?>';
792 xml_tab(xml_ctr).xmlstring := '<?xml version = "1.0" encoding = "'||l_IANA_charset||'"?>';
793 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '<START>';
794 xml_ctr := xml_ctr +1;
795
796 IF p_payroll_action_id IS NULL THEN
797
798 BEGIN
799
800 SELECT payroll_action_id
801 INTO l_payroll_action_id
802 FROM pay_payroll_actions ppa,
803 fnd_conc_req_summary_v fcrs,
804 fnd_conc_req_summary_v fcrs1
805 WHERE fcrs.request_id = FND_GLOBAL.CONC_REQUEST_ID
806 AND fcrs.priority_request_id = fcrs1.priority_request_id
807 AND ppa.request_id between fcrs1.request_id and fcrs.request_id
808 AND ppa.request_id = fcrs1.request_id;
809
810 EXCEPTION
811 WHEN others THEN
812 NULL;
813 END ;
814 ELSE
815 l_payroll_action_id:=p_payroll_action_id;
816 END IF;
817
818
819
820 for emp_rec in csr_emp loop
821
822 if (l_thirdparty_id <> emp_rec.thirdparty_id) then
823
824 if csr_emp%rowcount = 1 then
825 open csr_legalemployer;
826 fetch csr_legalemployer into l_employer,l_orgnumber,l_term,l_year,l_archiver;
827 close csr_legalemployer;
828 xml_tab(xml_ctr).xmlstring := '<LEGALEMPLOYER_RECORD>';
829 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '<Employer>'||l_employer||'</Employer>';
830 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Organizationnumber>'||l_orgnumber||'</Organizationnumber>';
831 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '<Term>'||l_term||'</Term>';
832 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '<Year>'||l_year||'</Year>';
833 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '<Archiver>'||l_archiver||'</Archiver>';
834 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '</LEGALEMPLOYER_RECORD>';
835 xml_ctr := xml_ctr +1;
836 xml_tab(xml_ctr).xmlstring := '<THIRDPARTY_RECORD>';
837 else
838 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '</THIRDPARTY_RECORD>';
839 xml_ctr := xml_ctr + 1;
840 xml_tab(xml_ctr).xmlstring := '<THIRDPARTY_RECORD>';
841 end if ;
842
843 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Thirdparty_Name>'||emp_rec.thirdparty_name||'</Thirdparty_Name>';
844 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Thirdparty_address>'||emp_rec.thirdparty_address||'</Thirdparty_address>';
845 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<BankAccountno>'||emp_rec.bankaccountno||'</BankAccountno>';
846 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EMP_RECORD>';
847 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<NI-number>'||emp_rec.ni||'</NI-number>';
848 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Name>'||emp_rec.order_name||'</Name>';
849 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<TaxDeductionBasis>'||emp_rec.taxdeductionbasis||'</TaxDeductionBasis>';
850 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Incomeyear>'||emp_rec.incomeyear||'</Incomeyear>';
851 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Percentage>'||emp_rec.percentage||'</Percentage>';
852 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Amount>'||emp_rec.amount||'</Amount>';
853 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Deductedthisperiod>'||emp_rec.deductedthisperiod||'</Deductedthisperiod>';
854 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeLastName>'|| emp_rec.last_name ||'</EmployeeLastName>';
855 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeFirstName>'|| emp_rec.first_name ||'</EmployeeFirstName>';
856 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeDOB>'|| emp_rec.emp_dob ||'</EmployeeDOB>';
857 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeDOH>'|| emp_rec.emp_doh ||'</EmployeeDOH>';
858 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeTitle>'|| emp_rec.emp_title ||'</EmployeeTitle>';
859 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Emp-number>'||emp_rec.emp_no||'</Emp-number>';
860
861 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring||'</EMP_RECORD>';
862 l_thirdparty_id := emp_rec.thirdparty_id;
863 else
864 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EMP_RECORD>';
865 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<NI-number>'||emp_rec.ni||'</NI-number>';
866 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Name>'||emp_rec.order_name||'</Name>';
867 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<TaxDeductionBasis>'||emp_rec.taxdeductionbasis||'</TaxDeductionBasis>';
868 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Incomeyear>'||emp_rec.incomeyear||'</Incomeyear>';
869 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Percentage>'||emp_rec.percentage||'</Percentage>';
870 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Amount>'||emp_rec.amount||'</Amount>';
871 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Deductedthisperiod>'||emp_rec.deductedthisperiod||'</Deductedthisperiod>';
872 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeLastName>'|| emp_rec.last_name ||'</EmployeeLastName>';
873 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeFirstName>'|| emp_rec.first_name ||'</EmployeeFirstName>';
874 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Emp-number>'||emp_rec.emp_no||'</Emp-number>';
875 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeDOB>'|| emp_rec.emp_dob ||'</EmployeeDOB>';
876 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeDOH>'|| emp_rec.emp_doh ||'</EmployeeDOH>';
877 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeTitle>'|| emp_rec.emp_title ||'</EmployeeTitle>';
878 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring||'</EMP_RECORD>';
879 l_thirdparty_id := emp_rec.thirdparty_id;
880 end if ;
881 end loop;
882
883 if(xml_tab(xml_ctr).xmlstring is null) THEN
884 raise no_data_found;
885 else
886 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring||'</THIRDPARTY_RECORD>';
887 xml_ctr := xml_ctr +1;
888 xml_tab(xml_ctr).xmlstring := '</START>';
889 end if;
890
891 write_to_clob(p_xml);
892
893 exception when no_data_found then
894 xml_tab(xml_ctr).xmlstring := '<LEGALEMPLOYER_RECORD>';
895 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '<Employer>'||'</Employer>';
896 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Organizationnumber>'||'</Organizationnumber>';
897 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '<Term>'||'</Term>';
898 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '<Year>'||'</Year>';
899 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '<Archiver>'||l_archiver||'</Archiver>';
900 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '</LEGALEMPLOYER_RECORD>';
901 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<THIRDPARTY_RECORD>';
902 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Thirdparty_Name>'||'</Thirdparty_Name>';
903 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Thirdparty_address>'||'</Thirdparty_address>';
904 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<BankAccountno>'||'</BankAccountno>';
905 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EMP_RECORD>';
906 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<NI-number>'||'</NI-number>';
907 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Name>'||'</Name>';
908 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<TaxDeductionBasis>'||'</TaxDeductionBasis>';
909 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Incomeyear>'||'</Incomeyear>';
910 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Percentage>'||'</Percentage>';
911 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Amount>'||'</Amount>';
912 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Deductedthisperiod>'||'</Deductedthisperiod>';
913 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeLastName>'|| '</EmployeeLastName>';
914 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeFirstName>'|| '</EmployeeFirstName>';
915 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeDOB>'|| '</EmployeeDOB>';
916 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeDOH>'|| '</EmployeeDOH>';
917 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Emp-number>'||'</Emp-number>';
918 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeTitle>'|| '</EmployeeTitle>';
919 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring||'</EMP_RECORD>';
920 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'</THIRDPARTY_RECORD>';
921 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'</START>';
922
923 write_to_clob(p_xml);
924
925 end populate_details;
926
927
928 ------------------------------------------------------------------------------------------------------------
929
930 procedure write_to_clob (p_xml out nocopy clob) is
931 l_xfdf_string clob;
932 begin
933 dbms_lob.createtemporary(p_xml,false,dbms_lob.call);
934 dbms_lob.open(p_xml,dbms_lob.lob_readwrite);
935 if xml_tab.count > 0 then
936 for ctr_table in xml_tab.first .. xml_tab.last loop
937 dbms_lob.writeappend( p_xml, length(xml_tab(ctr_table).xmlstring), xml_tab(ctr_table).xmlstring );
938 end loop;
939 end if;
940 --dbms_lob.createtemporary(p_xml,true);
941 --clob_to_blob(l_xfdf_string,p_xml);
942 exception
943 when others then
944 hr_utility.trace('sqleerm ' || sqlerrm);
945 hr_utility.raise_error;
946
947 end write_to_clob;
948
949 -------------------------------------------------------------------------------------------------------------------------
950 END PAY_NO_TAX_LEVY;