[Home] [Help]
PACKAGE BODY: APPS.PAY_NO_TAX_LEVY
Source
1 PACKAGE BODY PAY_NO_TAX_LEVY AS
2 /* $Header: pynotaxlevy.pkb 120.4.12000000.1 2007/05/20 08:06:12 rlingama noship $ */
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
271 FROM
272 pay_assignment_actions assact
273 ,pay_assignment_actions assact1
274 ,pay_payroll_actions ppa
275 ,pay_payroll_actions ppa2
276 ,pay_payroll_actions ppa3
277 ,per_all_assignments_f asg
278 ,pay_element_types_f ele
279 ,pay_run_results prr
280 ,pay_run_result_values prrv
281 ,pay_input_values_f inpv
282 ,pay_action_interlocks pai
283
284
285 WHERE -- initial conditions
286
287 ppa.payroll_action_id = p_payroll_action_id
288
289 -- for 2nd pay payroll act table
290 AND ppa2.date_earned between l_start_date and l_end_date
291 AND ppa2.action_type IN ('R','Q') -- Payroll Run or Quickpay Run
292
293 -- for asg act table
294 AND assact.PAYROLL_ACTION_ID = ppa2.PAYROLL_ACTION_ID
295 AND assact.TAX_UNIT_ID = p_leg_emp_id
296 AND assact.action_status = 'C' -- Completed
297 AND assact.source_action_id IS NOT NULL -- Not Master Action
298
299
300 -- for asg table
301 AND assact.ASSIGNMENT_ID = asg.ASSIGNMENT_ID
302 --AND ppa.date_earned between asg.EFFECTIVE_START_DATE and asg.EFFECTIVE_END_DATE
303 --AND ppa.effective_date between asg.EFFECTIVE_START_DATE and asg.EFFECTIVE_END_DATE
304 AND ppa2.effective_date between asg.EFFECTIVE_START_DATE and asg.EFFECTIVE_END_DATE -- Bug 5116907 fix
305 AND asg.person_id BETWEEN p_start_person AND p_end_person
306
307 -- for run results
308 AND assact.ASSIGNMENT_ACTION_ID = prr.ASSIGNMENT_ACTION_ID
309
310 --for prepayments
311 AND assact1.action_status = 'C' -- Completed
312 AND assact1.assignment_action_id = pai.locking_action_id
313 AND assact1.payroll_action_id = ppa3.payroll_action_id
314 AND ppa3.action_type IN ('P','U')
315 AND ppa3.date_earned between l_start_date and l_end_date
316
317 AND assact.ASSIGNMENT_ACTION_ID = pai.locked_action_id
318
319 -- for element 'Wage Attachment Tax Levy'
320 AND prr.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
321 AND ele.ELEMENT_NAME = 'Wage Attachment Tax Levy'
322 AND ele.LEGISLATION_CODE = 'NO'
323 --AND ppa.date_earned between ele.EFFECTIVE_START_DATE and ele.EFFECTIVE_END_DATE
324 AND ppa.effective_date between ele.EFFECTIVE_START_DATE and ele.EFFECTIVE_END_DATE
325
326 AND prr.RUN_RESULT_ID = prrv.RUN_RESULT_ID
327 AND inpv.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
328 AND inpv.NAME = 'Third Party Payee'
329 AND inpv.LEGISLATION_CODE = 'NO'
330 --AND ppa.date_earned between inpv.EFFECTIVE_START_DATE and inpv.EFFECTIVE_END_DATE
331 AND prrv.INPUT_VALUE_ID = inpv.INPUT_VALUE_ID
332 --and haou.organization_id=prrv5.result_value
333 AND prrv.result_value like nvl(to_char(p_payee_org),prrv.result_value)
334
335
336 ORDER BY assact.assignment_id ;
337
338 --to get the next action_id
339 cursor csr_actid is
340 SELECT pay_assignment_actions_s.NEXTVAL FROM dual;
341 ---------------------------------------------------------------------------------------------------------------
342 l_count NUMBER := 0;
343 l_action_info_id NUMBER;
344 l_ovn NUMBER;
345 l_bimonth_year NUMBER;
346 l_le_org_num VARCHAR2(240);
347 l_le_name VARCHAR2(240);
348 l_start_date DATE;
349 l_end_date DATE;
350 l_actid NUMBER;
351 l_tax_mun_id NUMBER;
352 l_local_unit_id NUMBER;
353
354
355
356 BEGIN
357 IF g_debug THEN
358 hr_utility.set_location(' Entering Procedure ASSIGNMENT_ACTION_CODE',60);
359 END IF;
360 -- fetch the data for the REPORT DETAILS
361 PAY_NO_TAX_LEVY.GET_ALL_PARAMETERS(
362 p_payroll_action_id
363 ,l_business_id
364 ,l_leg_emp_id
365 ,l_effective_date
366 ,l_bimonth_term
367 ,l_archive
368 ,l_payee_org) ;
369
370 -- check if we have to archive again
371 IF (l_archive = 'Y')
372
373 THEN
374 --fnd_file.put_line(fnd_file.log,'SUGARG: l_archive = Y');
375
376 -- get the period start and end dates
377
378 select to_date(decode(l_bimonth_term,'01','JAN','02','MAR','03','MAY','04','JUL','05','SEP','06','NOV','00','JAN')
379 ||to_char(l_effective_date,'YY'),'MONYY') into l_start_date from dual;
380
381 IF l_bimonth_term IS NOT NULL AND l_bimonth_term <> '00' THEN
382
383 select last_day(to_date(decode(l_bimonth_term,'01','FEB','02','APR','03','JUN','04','AUG','05','OCT','06','DEC','DEC')
384 ||to_char(l_effective_date,'YY'),'MONYY')) into l_end_date from dual;
385 ELSE
386 l_end_date := l_effective_date;
387
388 END IF;
389
390 -- fetch assignments details
391
392 FOR csr_rec IN csr_assignments
393 ( p_payroll_action_id
394 ,l_leg_emp_id
395 ,p_start_person
396 ,p_end_person
397 ,l_start_date
398 ,l_end_date
399 ,l_payee_org)
400 LOOP
401 open csr_actid;
402 fetch csr_actid into l_actid;
403 close csr_actid;
404
405 -- Create the archive assignment action
406 hr_nonrun_asact.insact(l_actid ,csr_rec.asg_id ,p_payroll_action_id ,p_chunk ,NULL);
407
408 -- Creating Initial Archive Entries
409 pay_action_information_api.create_action_information (
410
411 p_action_information_id => l_action_info_id -- out parameter
412 ,p_object_version_number => l_ovn -- out parameter
413 ,p_action_context_id => l_actid -- context id = assignment action id (of Archive)
414 ,p_action_context_type => 'AAP' -- context type
415 ,p_effective_date => l_effective_date -- Date of running the archive
416 ,p_assignment_id => csr_rec.asg_id -- Assignment ID
417 ,p_action_information_category => 'EMEA REPORT INFORMATION' -- Information Category
418 ,p_tax_unit_id => l_leg_emp_id -- Legal Employer ID
419 ,p_jurisdiction_code => csr_rec.tax_mun_id -- Tax Municipality ID
420 ,p_action_information1 => 'PYNOTAXLEVYA' -- Conc Prg Short Name
421 ,p_action_information2 => csr_rec.local_unit_id -- Local Unit ID
422 ,p_action_information3 => p_payroll_action_id -- payroll action id (of Archive)
423 ,p_action_information4 => csr_rec.asg_act_id -- Original / Main Asg Action ID
424
425 );
426
427 END LOOP;
428
429 END IF; -- l_archive = 'Y'
430
431 IF g_debug THEN
432 hr_utility.set_location(' Leaving Procedure ASSIGNMENT_ACTION_CODE',70);
433 END IF;
434
435 END ASSIGNMENT_ACTION_CODE;
436
437
438 -------------------------------------------------------------------------------------------------------------------------
439 /* ARCHIVE CODE */
440 PROCEDURE ARCHIVE_CODE(p_assignment_action_id IN NUMBER
441 ,p_effective_date IN DATE)
442 IS
443
444 -- Cursor to get the action_information_id and original/main assignment_action_id
445
446 cursor csr_get_act_info (p_assignment_action_id NUMBER , p_effective_date DATE) is
447 select to_number(ACTION_INFORMATION_ID) , to_number(ACTION_INFORMATION4)
448 ,to_number(ACTION_INFORMATION3)
449 from pay_action_information
450 where ACTION_INFORMATION_CATEGORY = 'EMEA REPORT INFORMATION'
451 and ACTION_INFORMATION1 = 'PYNOTAXLEVYA'
452 and ACTION_CONTEXT_TYPE = 'AAP'
453 and ACTION_CONTEXT_ID = p_assignment_action_id
454 and EFFECTIVE_DATE = p_effective_date ;
455
456 --varibale declaration
457 l_third_party_id NUMBER;
458 l_main_asg_act_id NUMBER;
459 l_payroll_action_id NUMBER ;
460
461 -- cursor to get the assignment,person details and run values
462
463 CURSOR csr_asg_details (p_assignment_action_id NUMBER) IS
464 SELECT
465 per.last_name last_name
466 ,per.first_name first_name
467 ,per.order_name order_Name
468 ,per.title per_title
469 ,per.date_of_birth dob
470 ,per.original_date_of_hire doh
471 ,per.PERSON_ID per_id
472 ,per.NATIONAL_IDENTIFIER per_ni
473 ,per.EMPLOYEE_NUMBER emp_no
474 ,per.business_group_id bg_id
475 ,prrv1.result_value res_val_1 -- Pay Value
476 ,prrv2.result_value res_val_2 -- Income Year
477 ,prrv3.result_value res_val_3 -- Deduction Percentage
478 ,prrv4.result_value res_val_4 -- Fixed Deduction Amount
479 ,prrv5.result_value res_val_5 -- Third Party Payee
480
481 FROM
482 pay_assignment_actions assact
483 ,pay_payroll_actions ppa
484 ,per_all_assignments_f asg
485 ,per_all_people_f per
486 ,pay_element_types_f ele
487 ,pay_input_values_f inpv1
488 ,pay_input_values_f inpv2
489 ,pay_input_values_f inpv3
490 ,pay_input_values_f inpv4
491 ,pay_input_values_f inpv5
492 ,pay_run_results prr
493 ,pay_run_result_values prrv1
494 ,pay_run_result_values prrv2
495 ,pay_run_result_values prrv3
496 ,pay_run_result_values prrv4
497 ,pay_run_result_values prrv5
498
499
500 WHERE assact.ASSIGNMENT_ACTION_ID = p_assignment_action_id
501 AND assact.ASSIGNMENT_ID = asg.ASSIGNMENT_ID
502 AND ppa.date_earned between asg.EFFECTIVE_START_DATE and asg.EFFECTIVE_END_DATE
503 AND asg.PERSON_ID = per.PERSON_ID
504 AND ppa.date_earned between per.EFFECTIVE_START_DATE and per.EFFECTIVE_END_DATE
505 AND assact.PAYROLL_ACTION_ID = ppa.PAYROLL_ACTION_ID
506 AND assact.ASSIGNMENT_ACTION_ID = prr.ASSIGNMENT_ACTION_ID
507
508 -- for element 'Wage Attachment Tax Levy'
509 AND prr.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
510 AND ele.ELEMENT_NAME = 'Wage Attachment Tax Levy'
511 AND ele.LEGISLATION_CODE = 'NO'
512 AND ppa.date_earned between ele.EFFECTIVE_START_DATE and ele.EFFECTIVE_END_DATE
513
514 -- for input value 'Pay Value'
515 AND prr.RUN_RESULT_ID = prrv1.RUN_RESULT_ID
516 AND inpv1.NAME = 'Pay Value'
517 AND inpv1.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
518 AND inpv1.LEGISLATION_CODE = 'NO'
519 AND ppa.date_earned between inpv1.EFFECTIVE_START_DATE and inpv1.EFFECTIVE_END_DATE
520 AND prrv1.INPUT_VALUE_ID = inpv1.INPUT_VALUE_ID
521
522 -- for input value 'Income Year'
523 AND prr.RUN_RESULT_ID = prrv2.RUN_RESULT_ID
524 AND inpv2.NAME = 'Income Year'
525 AND inpv2.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
526 AND inpv2.LEGISLATION_CODE = 'NO'
527 AND ppa.date_earned between inpv2.EFFECTIVE_START_DATE and inpv2.EFFECTIVE_END_DATE
528 AND prrv2.INPUT_VALUE_ID = inpv2.INPUT_VALUE_ID
529
530 -- for input value 'Deduction Percentage'
531 AND prr.RUN_RESULT_ID = prrv3.RUN_RESULT_ID
532 AND inpv3.NAME = 'Deduction Percentage'
533 AND inpv3.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
534 AND inpv3.LEGISLATION_CODE = 'NO'
535 AND ppa.date_earned between inpv3.EFFECTIVE_START_DATE and inpv3.EFFECTIVE_END_DATE
536 AND prrv3.INPUT_VALUE_ID = inpv3.INPUT_VALUE_ID
537
538 -- for input value 'Fixed Deduction Amount'
539 AND prr.RUN_RESULT_ID = prrv4.RUN_RESULT_ID
540 AND inpv4.NAME = 'Fixed Deduction Amount'
541 AND inpv4.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
542 AND inpv4.LEGISLATION_CODE = 'NO'
543 AND ppa.date_earned between inpv4.EFFECTIVE_START_DATE and inpv4.EFFECTIVE_END_DATE
544 AND prrv4.INPUT_VALUE_ID = inpv4.INPUT_VALUE_ID
545
546 -- for input value 'Third Party Payee'
547 AND prr.RUN_RESULT_ID = prrv5.RUN_RESULT_ID
548 AND inpv5.NAME = 'Third Party Payee'
549 AND inpv5.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
550 AND inpv5.LEGISLATION_CODE = 'NO'
551 AND ppa.date_earned between inpv5.EFFECTIVE_START_DATE and inpv5.EFFECTIVE_END_DATE
552 AND prrv5.INPUT_VALUE_ID = inpv5.INPUT_VALUE_ID;
553
554
555
556 -- cursor to get Third Party Destination Acc Number
557
558 cursor csr_third_party_dest_acc (p_organization_id NUMBER) is
559 select segment6
560 from pay_external_accounts acc
561 ,pay_org_payment_methods_f pay_org
562 ,hr_organization_information hoi
563 where hoi.organization_id = p_organization_id
564 and hoi.org_information_context = 'NO_THIRD_PARTY_PAYMENT'
565 and pay_org.org_payment_method_id = hoi.org_information2
566 and pay_org.pmeth_information1 = 'DESTINATION'
567 and acc.external_account_id = pay_org.external_account_id;
568
569
570 -- cursor to get the third party loaction
571
572 cursor csr_third_party_loc (p_organization_id NUMBER) is
573 select loc.address_line_1 line_1
574 ,loc.address_line_2 line_2
575 ,loc.address_line_3 line_3
576 ,hr_general.decode_fnd_comm_lookup('NO_POSTAL_CODE',loc.postal_code) post_code
577 from hr_locations_all loc
578 ,hr_all_organization_units hou
579 where hou.organization_id = p_organization_id
580 and loc.location_id = hou.location_id
581 and loc.style = 'NO';
582
583 --cursor to get the payroll_action_id
584 cursor csr_payroll_action_id is
585 SELECT payroll_action_id
586 FROM pay_assignment_actions
587 WHERE assignment_action_id = p_assignment_action_id ;
588
589 --cursor to get the third party name
590 cursor csr_third_party_name is
591 select name from hr_organization_units
592 where organization_id = l_third_party_id ;
593
594 -- cursor to fetch the balance value of TAX DEDUCTION BASIS
595 cursor csr_tax_dedn_basis is
596 select
597 pay_balance_pkg.get_value(pay_no_emp_cont.get_defined_balance_id('Tax Deduction Basis','_ASG_PTD'),
598 l_main_asg_act_id)
599 from dual;
600
601 -- Variable declaration
602
603 l_action_info_id NUMBER;
604 l_ovn NUMBER;
605
606 rec_asg_detail csr_asg_details%rowtype;
607 rec_loc_detail csr_third_party_loc%rowtype;
608
609 l_count NUMBER := 0;
610 l_third_party_name VARCHAR2(240);
611 l_third_party_dest_acc VARCHAR2(150);
612 l_tax_dedn_basis NUMBER;
613 l_new_payroll_action_id NUMBER; -- To store the payroll action id created.
614
615
616 BEGIN
617 IF g_debug THEN
618 hr_utility.set_location(' Entering Procedure ARCHIVE_CODE',380);
619 END IF;
620
621 -- get the action_information_id and original/main assignment_action_id for this asg_act_id
622 OPEN csr_get_act_info (p_assignment_action_id , p_effective_date );
623 FETCH csr_get_act_info INTO l_action_info_id , l_main_asg_act_id ,l_new_payroll_action_id;
624 CLOSE csr_get_act_info ;
625
626 -- fetch the data for the REPORT DETAILS
627 PAY_NO_TAX_LEVY.GET_ALL_PARAMETERS(
628 l_new_payroll_action_id
629 ,l_business_id
630 ,l_leg_emp_id
631 ,l_effective_date
632 ,l_bimonth_term
633 ,l_archive
634 ,l_payee_org) ;
635
636
637 open csr_payroll_action_id;
638 fetch csr_payroll_action_id into l_payroll_action_id;
639 close csr_payroll_action_id;
640
641 -- fetch the data
642
643 -- check if we have to archive again
644 IF (l_archive = 'Y')
645
646 THEN
647
648 -- fetch assignment details
649 OPEN csr_asg_details (l_main_asg_act_id) ;
650 FETCH csr_asg_details INTO rec_asg_detail ;
651 CLOSE csr_asg_details ;
652
653 l_third_party_id := to_number(rec_asg_detail.res_val_5) ;
654
655 -- to get the Third Party Name
656 open csr_third_party_name;
657 fetch csr_third_party_name into l_third_party_name;
658 close csr_third_party_name;
659
660 -- to get the third party Destination acc no.
661 OPEN csr_third_party_dest_acc (l_third_party_id) ;
662 FETCH csr_third_party_dest_acc INTO l_third_party_dest_acc ;
663 CLOSE csr_third_party_dest_acc ;
664
665
666 -- to get the third party location details
667 OPEN csr_third_party_loc (l_third_party_id) ;
668 FETCH csr_third_party_loc INTO rec_loc_detail;
669 CLOSE csr_third_party_loc ;
670
671 -- to fetch the balance value of TAX DEDUCTION BASIS
672 open csr_tax_dedn_basis;
673 fetch csr_tax_dedn_basis into l_tax_dedn_basis;
674 close csr_tax_dedn_basis;
675
676 l_third_party_dest_acc := substr(l_third_party_dest_acc,1,4)||'.'||
677 substr(l_third_party_dest_acc,5,2)||'.'||
678 substr(l_third_party_dest_acc,7,5) ;
679
680 -- Updating the Initial Archive Entries
681 pay_action_information_api.update_action_information (
682 p_action_information_id => l_action_info_id -- in parameter
683 ,p_object_version_number => l_ovn -- in out parameter
684 ,p_action_information5 => l_third_party_id --Third Party ID (Tax Collector ID)
685 ,p_action_information6 => l_third_party_name --Third Party Name (Tax Collector's Name)
686 ,p_action_information7 => rec_loc_detail.line_1 --Third Party Address Line 1
687 ,p_action_information8 => rec_loc_detail.line_2 --Third Party Address Line 2
688 ,p_action_information9 => rec_loc_detail.line_3 --Third Party Address Line 3
689 ,p_action_information10 => rec_loc_detail.post_code --Third Party Postal Code + City
690 ,p_action_information11 => l_third_party_dest_acc --Third Party Destination Bank Account Number (Formatted)
691 ,p_action_information12 => rec_asg_detail.bg_id --Business Group ID
692 ,p_action_information13 => rec_asg_detail.per_id --PERSON_ID
693 ,p_action_information14 => rec_asg_detail.per_ni --Person NATIONAL_IDENTIFIER
694 ,p_action_information15 => rec_asg_detail.last_name --Person Lastname
695 ,p_action_information16 => rec_asg_detail.first_name --Person Firstname
696 ,p_action_information17 => rec_asg_detail.order_name --Person Ordername
697 ,p_action_information18 => rec_asg_detail.emp_no --Person Employee number
698 ,p_action_information19 => rec_asg_detail.dob --Person date of birth
699 ,p_action_information20 => rec_asg_detail.doh --Person date of hiring
700 ,p_action_information21 => rec_asg_detail.per_title --Person title
701 ,p_action_information22 => rec_asg_detail.res_val_2 --Income Year (Input Value)
702 ,p_action_information23 => rec_asg_detail.res_val_3 --Percentage (Input Value)
703 ,p_action_information24 => rec_asg_detail.res_val_4 --Amount (Input Value)
704 ,p_action_information25 => rec_asg_detail.res_val_1 --Deducted This Period (Input Value)
705 ,p_action_information26 => l_tax_dedn_basis --Tax Deduction Basis (Balance Value)
706 ,p_action_information27 => NULL );
707
708
709 END IF; -- l_archive = 'Y'
710
711
712 IF g_debug THEN
713 hr_utility.set_location(' Leaving Procedure ARCHIVE_CODE',390);
714 END IF;
715
716 END ARCHIVE_CODE;
717
718 -------------------------------------------------------------------------------------------------------------------------
719 procedure populate_details(p_payroll_action_id in varchar2,
720 p_template_name in varchar2,
721 p_xml out nocopy clob) is
722
723 l_employer hr_all_organization_units.name%type;
724 l_orgnumber hr_organization_information.org_information1%type;
725 l_term varchar2(30);
726 l_year varchar2(8);
727 l_thirdparty_id pay_action_information.ACTION_INFORMATION5%type:='-999';
728 l_payroll_action_id varchar2(150);
729 l_archiver varchar2(150);
730 xml_ctr number;
731 l_IANA_charset VARCHAR2 (50);
732
733 cursor csr_legalemployer is
734 select pai.action_information3 employer,pai.action_information4 orgnumber,
735 pai.action_information5 term,pai.action_information6 year,
736 pai.effective_date || ' (' ||pai.action_context_id||')' archiver
737 from pay_action_information pai
738 where to_char(pai.action_context_id)=l_payroll_action_id;
739
740 --Bug 5116700 fix - Taking the sum of TaxDeductionBasis
741 cursor csr_emp is
742 select thirdparty_id,thirdparty_name,thirdparty_address,
743 bankaccountno,ni,last_name,first_name,order_name,
744 emp_no,emp_dob,emp_doh,emp_title,
745 sum(TaxDeductionBasis) TaxDeductionBasis,
746 incomeyear,percentage,amount,
747 sum(deductedthisperiod) deductedthisperiod from
748 (select pai.action_information5 thirdparty_id
749 ,pai.action_information6 thirdparty_name,
750 decode(pai.action_information7,null,'',',')
751 ||pai.action_information7||
752 decode(pai.action_information8,null,'',',')
753 ||pai.action_information8||
754 decode(pai.action_information9,null,'',',')
755 ||pai.action_information9||
756 decode(pai.action_information10,null,'',',')
757 ||pai.action_information10 thirdparty_address
758 ,pai.action_information11 bankaccountno
759 ,pai.action_information14 ni
760 ,action_information15 last_name
761 ,action_information16 first_name
762 ,action_information17 order_name
763 ,action_information18 emp_no
764 ,action_information19 emp_dob
765 ,action_information20 emp_doh
766 ,action_information21 emp_title
767 ,pai.action_information26 TaxDeductionBasis
768 ,pai.action_information22 incomeyear
769 ,pai.action_information23 percentage
770 ,pai.action_information24 amount
771 ,pai.action_information25 deductedthisperiod
772 from pay_action_information pai
773 where pai.action_information3=l_payroll_action_id)
774 group by
775 thirdparty_id,thirdparty_name,thirdparty_address,
776 bankaccountno,ni,last_name,first_name,order_name,
777 emp_no,emp_dob,emp_doh,emp_title,
778 incomeyear,percentage,amount
779 order by thirdparty_id,order_name;
780
781
782
783
784 begin
785
786 xml_ctr := 0;
787 /*pgopal - picking the charset dynamically from the db*/
788 l_IANA_charset := HR_NO_UTILITY.get_IANA_charset ;
789 --xml_tab(xml_ctr).xmlstring := '<?xml version="1.0" encoding="utf-8"?>';
790 xml_tab(xml_ctr).xmlstring := '<?xml version = "1.0" encoding = "'||l_IANA_charset||'"?>';
791 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '<START>';
792 xml_ctr := xml_ctr +1;
793
794 IF p_payroll_action_id IS NULL THEN
795
796 BEGIN
797
798 SELECT payroll_action_id
799 INTO l_payroll_action_id
800 FROM pay_payroll_actions ppa,
801 fnd_conc_req_summary_v fcrs,
802 fnd_conc_req_summary_v fcrs1
803 WHERE fcrs.request_id = FND_GLOBAL.CONC_REQUEST_ID
804 AND fcrs.priority_request_id = fcrs1.priority_request_id
805 AND ppa.request_id between fcrs1.request_id and fcrs.request_id
806 AND ppa.request_id = fcrs1.request_id;
807
808 EXCEPTION
809 WHEN others THEN
810 NULL;
811 END ;
812 ELSE
813 l_payroll_action_id:=p_payroll_action_id;
814 END IF;
815
816
817
818 for emp_rec in csr_emp loop
819
820 if (l_thirdparty_id <> emp_rec.thirdparty_id) then
821
822 if csr_emp%rowcount = 1 then
823 open csr_legalemployer;
824 fetch csr_legalemployer into l_employer,l_orgnumber,l_term,l_year,l_archiver;
825 close csr_legalemployer;
826 xml_tab(xml_ctr).xmlstring := '<LEGALEMPLOYER_RECORD>';
827 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '<Employer>'||l_employer||'</Employer>';
828 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Organizationnumber>'||l_orgnumber||'</Organizationnumber>';
829 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '<Term>'||l_term||'</Term>';
830 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '<Year>'||l_year||'</Year>';
831 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '<Archiver>'||l_archiver||'</Archiver>';
832 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '</LEGALEMPLOYER_RECORD>';
833 xml_ctr := xml_ctr +1;
834 xml_tab(xml_ctr).xmlstring := '<THIRDPARTY_RECORD>';
835 else
836 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '</THIRDPARTY_RECORD>';
837 xml_ctr := xml_ctr + 1;
838 xml_tab(xml_ctr).xmlstring := '<THIRDPARTY_RECORD>';
839 end if ;
840
841 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Thirdparty_Name>'||emp_rec.thirdparty_name||'</Thirdparty_Name>';
842 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Thirdparty_address>'||emp_rec.thirdparty_address||'</Thirdparty_address>';
843 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<BankAccountno>'||emp_rec.bankaccountno||'</BankAccountno>';
844 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EMP_RECORD>';
845 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<NI-number>'||emp_rec.ni||'</NI-number>';
846 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Name>'||emp_rec.order_name||'</Name>';
847 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<TaxDeductionBasis>'||emp_rec.taxdeductionbasis||'</TaxDeductionBasis>';
848 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Incomeyear>'||emp_rec.incomeyear||'</Incomeyear>';
849 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Percentage>'||emp_rec.percentage||'</Percentage>';
850 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Amount>'||emp_rec.amount||'</Amount>';
851 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Deductedthisperiod>'||emp_rec.deductedthisperiod||'</Deductedthisperiod>';
852 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeLastName>'|| emp_rec.last_name ||'</EmployeeLastName>';
853 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeFirstName>'|| emp_rec.first_name ||'</EmployeeFirstName>';
854 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeDOB>'|| emp_rec.emp_dob ||'</EmployeeDOB>';
855 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeDOH>'|| emp_rec.emp_doh ||'</EmployeeDOH>';
856 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeTitle>'|| emp_rec.emp_title ||'</EmployeeTitle>';
857 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Emp-number>'||emp_rec.emp_no||'</Emp-number>';
858
859 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring||'</EMP_RECORD>';
860 l_thirdparty_id := emp_rec.thirdparty_id;
861 else
862 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EMP_RECORD>';
863 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<NI-number>'||emp_rec.ni||'</NI-number>';
864 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Name>'||emp_rec.order_name||'</Name>';
865 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<TaxDeductionBasis>'||emp_rec.taxdeductionbasis||'</TaxDeductionBasis>';
866 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Incomeyear>'||emp_rec.incomeyear||'</Incomeyear>';
867 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Percentage>'||emp_rec.percentage||'</Percentage>';
868 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Amount>'||emp_rec.amount||'</Amount>';
869 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Deductedthisperiod>'||emp_rec.deductedthisperiod||'</Deductedthisperiod>';
870 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeLastName>'|| emp_rec.last_name ||'</EmployeeLastName>';
871 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeFirstName>'|| emp_rec.first_name ||'</EmployeeFirstName>';
872 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Emp-number>'||emp_rec.emp_no||'</Emp-number>';
873 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeDOB>'|| emp_rec.emp_dob ||'</EmployeeDOB>';
874 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeDOH>'|| emp_rec.emp_doh ||'</EmployeeDOH>';
875 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeTitle>'|| emp_rec.emp_title ||'</EmployeeTitle>';
876 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring||'</EMP_RECORD>';
877 l_thirdparty_id := emp_rec.thirdparty_id;
878 end if ;
879 end loop;
880
881 if(xml_tab(xml_ctr).xmlstring is null) THEN
882 raise no_data_found;
883 else
884 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring||'</THIRDPARTY_RECORD>';
885 xml_ctr := xml_ctr +1;
886 xml_tab(xml_ctr).xmlstring := '</START>';
887 end if;
888
889 write_to_clob(p_xml);
890
891 exception when no_data_found then
892 xml_tab(xml_ctr).xmlstring := '<LEGALEMPLOYER_RECORD>';
893 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '<Employer>'||'</Employer>';
894 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Organizationnumber>'||'</Organizationnumber>';
895 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '<Term>'||'</Term>';
896 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '<Year>'||'</Year>';
897 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '<Archiver>'||l_archiver||'</Archiver>';
898 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '</LEGALEMPLOYER_RECORD>';
899 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<THIRDPARTY_RECORD>';
900 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Thirdparty_Name>'||'</Thirdparty_Name>';
901 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Thirdparty_address>'||'</Thirdparty_address>';
902 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<BankAccountno>'||'</BankAccountno>';
903 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EMP_RECORD>';
904 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<NI-number>'||'</NI-number>';
905 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Name>'||'</Name>';
906 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<TaxDeductionBasis>'||'</TaxDeductionBasis>';
907 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Incomeyear>'||'</Incomeyear>';
908 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Percentage>'||'</Percentage>';
909 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Amount>'||'</Amount>';
910 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Deductedthisperiod>'||'</Deductedthisperiod>';
911 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeLastName>'|| '</EmployeeLastName>';
912 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeFirstName>'|| '</EmployeeFirstName>';
913 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeDOB>'|| '</EmployeeDOB>';
914 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeDOH>'|| '</EmployeeDOH>';
915 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Emp-number>'||'</Emp-number>';
916 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeTitle>'|| '</EmployeeTitle>';
917 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring||'</EMP_RECORD>';
918 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'</THIRDPARTY_RECORD>';
919 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'</START>';
920
921 write_to_clob(p_xml);
922
923 end populate_details;
924
925
926 ------------------------------------------------------------------------------------------------------------
927
928 procedure write_to_clob (p_xml out nocopy clob) is
929 l_xfdf_string clob;
930 begin
931 dbms_lob.createtemporary(p_xml,false,dbms_lob.call);
932 dbms_lob.open(p_xml,dbms_lob.lob_readwrite);
933 if xml_tab.count > 0 then
934 for ctr_table in xml_tab.first .. xml_tab.last loop
935 dbms_lob.writeappend( p_xml, length(xml_tab(ctr_table).xmlstring), xml_tab(ctr_table).xmlstring );
936 end loop;
937 end if;
938 --dbms_lob.createtemporary(p_xml,true);
939 --clob_to_blob(l_xfdf_string,p_xml);
940 exception
941 when others then
942 hr_utility.trace('sqleerm ' || sqlerrm);
943 hr_utility.raise_error;
944
945 end write_to_clob;
946
947 -------------------------------------------------------------------------------------------------------------------------
948 END PAY_NO_TAX_LEVY;