eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PAY_NO_UNION_DUES

Source


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