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.4.12020000.6 2012/10/31 12:47:40 dakhuran ship $ */
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 	,prrv.result_value payee_id 										-- bug 14483053
275 
276 	FROM
277 	pay_assignment_actions	assact
278 	,pay_assignment_actions	assact1
279 	,pay_payroll_actions	ppa
280 	,pay_payroll_actions	ppa2
281 	,pay_payroll_actions	ppa3
282 	,per_all_assignments_f	asg
283 	,pay_element_types_f    ele
284 	,pay_run_results	prr
285 	,pay_run_result_values	prrv
286 	,pay_input_values_f	inpv
287 	,pay_action_interlocks  pai
288 
289 
290 	WHERE -- initial conditions
291 
292 	ppa.payroll_action_id = p_payroll_action_id
293 
294 	-- for 2nd pay payroll act table
295 	AND ppa2.date_earned between l_from_date and l_to_date
296 	AND ppa2.action_type IN ('R','Q')  -- Payroll Run or Quickpay Run
297 
298 	-- for asg act table
299 	AND assact.PAYROLL_ACTION_ID = ppa2.PAYROLL_ACTION_ID
300 	AND assact.TAX_UNIT_ID = p_leg_emp_id
301 	AND assact.action_status = 'C'  -- Completed
302 	AND assact.source_action_id  IS NOT NULL -- Not Master Action
303 
304 	-- for asg table
305 	AND assact.ASSIGNMENT_ID = asg.ASSIGNMENT_ID
306 	--AND ppa.date_earned between asg.EFFECTIVE_START_DATE and asg.EFFECTIVE_END_DATE
307 	--AND ppa.effective_date between asg.EFFECTIVE_START_DATE and asg.EFFECTIVE_END_DATE
308 	-- To pick the terminated assignments.
309 	AND ppa2.effective_date between asg.EFFECTIVE_START_DATE and asg.EFFECTIVE_END_DATE
310 	AND asg.person_id   BETWEEN p_start_person AND p_end_person
311 
312 	-- for run results
313 	AND assact.ASSIGNMENT_ACTION_ID = prr.ASSIGNMENT_ACTION_ID
314 
315 	--for prepayments
316 	AND    assact1.action_status           IN ('C','S') -- 10229512
317 	AND    assact1.assignment_action_id    = pai.locking_action_id
318 	AND    assact1.payroll_action_id       = ppa3.payroll_action_id
319 	AND    ppa3.action_type            IN ('P','U')
320 	AND    ppa3.date_earned between l_from_date and l_to_date
321 
322 	AND assact.ASSIGNMENT_ACTION_ID = pai.locked_action_id
323 
324 
325 	-- for element 'Wage Attachment Tax Levy'
326 	AND prr.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
327 	AND ele.ELEMENT_NAME = 'Union Dues'
328 	AND ele.LEGISLATION_CODE = 'NO'
329 	--AND ppa.date_earned between ele.EFFECTIVE_START_DATE and ele.EFFECTIVE_END_DATE
330 	AND ppa.effective_date between ele.EFFECTIVE_START_DATE and ele.EFFECTIVE_END_DATE
331 
332 	AND prr.RUN_RESULT_ID = prrv.RUN_RESULT_ID
333 	AND inpv.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
334 	AND inpv.NAME = 'Third Party Payee'
335 	AND inpv.LEGISLATION_CODE = 'NO'
336 	--AND ppa.date_earned between inpv.EFFECTIVE_START_DATE and inpv.EFFECTIVE_END_DATE
337 	AND prrv.INPUT_VALUE_ID = inpv.INPUT_VALUE_ID
338 	--and haou.organization_id=prrv5.result_value
339 	AND prrv.result_value like nvl(to_char(p_payee_org),prrv.result_value)
340 
341 
342 	ORDER BY assact.assignment_id ;
343 
344 
345 l_count			NUMBER := 0;
346 l_action_info_id	NUMBER;
347 l_ovn			NUMBER;
348 l_bimonth_year		NUMBER;
349 l_le_org_num		VARCHAR2(240);
350 l_le_name		VARCHAR2(240);
351 l_actid			NUMBER;
352 l_tax_mun_id		NUMBER;
353 l_local_unit_id		NUMBER;
354 
355 
356  BEGIN
357  IF g_debug THEN
358       hr_utility.set_location(' Entering Procedure ASSIGNMENT_ACTION_CODE',60);
359 END IF;
360 
361 
362 -- fetch the data
363 PAY_NO_UNION_DUES.GET_ALL_PARAMETERS(
364 	p_payroll_action_id
365 	,l_business_id
366 	,l_leg_emp_id
367 	,l_effective_date
368 	,l_archive
369 	,l_payee_org
370 	,l_from_date
371 	,l_to_date) ;
372 
373 
374 
375 -- check if we have to archive again
376 IF  (l_archive = 'Y')
377 
378 THEN
379 
380 
381 
382 
383 -- fetch assignments details
384 
385 FOR csr_rec IN csr_assignments
386 	( p_payroll_action_id
387 	 ,l_leg_emp_id
388 	 ,p_start_person
389 	 ,p_end_person
390 	 ,l_from_date
391 	 ,l_to_date
392 	 ,l_payee_org)
393 LOOP
394 
395 	begin
396 		SELECT pay_assignment_actions_s.NEXTVAL INTO l_actid FROM   dual;
397 	exception
398 		when others then
399 		null;
400 	end;
401 
402 
403   -- Create the archive assignment action
404   hr_nonrun_asact.insact(l_actid ,csr_rec.asg_id ,p_payroll_action_id ,p_chunk ,NULL);
405 
406   -- Creating Initial Archive Entries
407   pay_action_information_api.create_action_information (
408 
409 	 p_action_information_id        => l_action_info_id		-- out parameter
410 	,p_object_version_number        => l_ovn			-- out parameter
411 	,p_action_context_id            => l_actid        		-- context id = assignment action id (of Archive)
412 	,p_action_context_type          => 'AAP'			-- context type
413 	,p_effective_date               => l_effective_date		-- Date of running the archive
414 	,p_assignment_id		=> csr_rec.asg_id		-- Assignment ID
415 	,p_action_information_category  => 'EMEA REPORT INFORMATION'	-- Information Category
416 	,p_tax_unit_id                  => l_leg_emp_id			-- Legal Employer ID
417 	,p_jurisdiction_code            => csr_rec.tax_mun_id		-- Tax Municipality ID
418 	,p_action_information1          => 'PYNOUNDUEA'			-- Conc Prg Short Name
419 	,p_action_information2          => csr_rec.local_unit_id	-- Local Unit ID
420 	,p_action_information3         => p_payroll_action_id		-- payroll action id (of Archive)
421 	,p_action_information4          => csr_rec.asg_act_id		-- Original / Main Asg Action ID
422 	,p_action_information30          => csr_rec.payee_id		-- bug 14483053
423 
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  END ASSIGNMENT_ACTION_CODE;
435 
436 ------------------------------------------------------------------------------------------------
437  /* ARCHIVE CODE */
438  PROCEDURE ARCHIVE_CODE(p_assignment_action_id IN NUMBER
439  		      ,p_effective_date    IN DATE)
440  IS
441 
442 -- Cursor to get the action_information_id and original/main assignment_action_id
443 
444 cursor csr_get_act_info (p_assignment_action_id  NUMBER , p_effective_date DATE) is
445 	select to_number(ACTION_INFORMATION_ID) , to_number(ACTION_INFORMATION4),to_number(ACTION_INFORMATION30)
446 	from pay_action_information
447 	where ACTION_INFORMATION_CATEGORY = 'EMEA REPORT INFORMATION'
448 	and   ACTION_INFORMATION1 = 'PYNOUNDUEA'
449 	and   ACTION_CONTEXT_TYPE = 'AAP'
450 	and   ACTION_CONTEXT_ID = p_assignment_action_id
451 	and   EFFECTIVE_DATE = p_effective_date ;
452 
453 
454 -- cursor to get the assignment,person details and run values
455 
456 CURSOR csr_asg_details (p_assignment_action_id	NUMBER,p_payee_id number) IS				  -- bug 14483053
457 	SELECT
458 	per.last_name last_name
459 	,per.first_name first_name
460 	,per.order_name order_Name
461 	,per.title per_title
462 	,per.date_of_birth dob
463 	,per.original_date_of_hire doh
464 	,per.PERSON_ID			per_id
465 	,per.NATIONAL_IDENTIFIER	per_ni
466 	,per.EMPLOYEE_NUMBER		emp_no
467 	,per.business_group_id		bg_id
468 	,prrv1.result_value		res_val_1 -- Pay Value
469 	,prrv2.result_value		res_val_2 --Union Member Number
470 	,prrv3.result_value		res_val_3 -- Deduction Percentage
471 	,prrv4.result_value		res_val_4 -- Fixed Deduction Amount
472 	,prrv5.result_value		res_val_5 -- Third Party Payee
473 
474 	FROM
475 	pay_assignment_actions	assact
476 	,pay_payroll_actions	ppa
477 	,per_all_assignments_f	asg
478 	,per_all_people_f	per
479 	,pay_element_types_f    ele
480 	,pay_input_values_f	inpv1
481 	,pay_input_values_f	inpv2
482 	,pay_input_values_f	inpv3
483 	,pay_input_values_f	inpv4
484 	,pay_input_values_f	inpv5
485 	,pay_run_results	prr
486 	,pay_run_result_values	prrv1
487 	,pay_run_result_values	prrv2
488 	,pay_run_result_values	prrv3
489 	,pay_run_result_values	prrv4
490 	,pay_run_result_values	prrv5
491 
492 
493 	WHERE assact.ASSIGNMENT_ACTION_ID = p_assignment_action_id
494 	AND assact.ASSIGNMENT_ID = asg.ASSIGNMENT_ID
495 	AND ppa.date_earned between asg.EFFECTIVE_START_DATE and asg.EFFECTIVE_END_DATE
496 	AND asg.PERSON_ID = per.PERSON_ID
497 	AND ppa.date_earned between per.EFFECTIVE_START_DATE and per.EFFECTIVE_END_DATE
498 	AND assact.PAYROLL_ACTION_ID = ppa.PAYROLL_ACTION_ID
499 	AND assact.ASSIGNMENT_ACTION_ID = prr.ASSIGNMENT_ACTION_ID
500 
501 	-- for element 'Wage Attachment Tax Levy'
502 	AND prr.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
503 	AND ele.ELEMENT_NAME = 'Union Dues'
504 	AND ele.LEGISLATION_CODE = 'NO'
505 	AND ppa.date_earned between ele.EFFECTIVE_START_DATE and ele.EFFECTIVE_END_DATE
506 
507 	-- for input value 'Pay Value'
508 	AND prr.RUN_RESULT_ID = prrv1.RUN_RESULT_ID
509 --	AND inpv1.NAME = 'Pay Value'
510 	--Bug 4661167 fix - Taking the 'Union Due Amount' instead of pay value
511 	AND inpv1.NAME = 'Union Due Amount'
512 	AND inpv1.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
513 	AND inpv1.LEGISLATION_CODE = 'NO'
514 	AND ppa.date_earned between inpv1.EFFECTIVE_START_DATE and inpv1.EFFECTIVE_END_DATE
515 	AND prrv1.INPUT_VALUE_ID = inpv1.INPUT_VALUE_ID
516 
517 	-- for input value 'Income Year'
518 	AND prr.RUN_RESULT_ID = prrv2.RUN_RESULT_ID
519 	AND inpv2.NAME = 'Union Member Number'
520 	AND inpv2.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
521 	AND inpv2.LEGISLATION_CODE = 'NO'
522 	AND ppa.date_earned between inpv2.EFFECTIVE_START_DATE and inpv2.EFFECTIVE_END_DATE
523 	AND prrv2.INPUT_VALUE_ID = inpv2.INPUT_VALUE_ID
524 
525 	-- for input value 'Deduction Percentage'
526 	AND prr.RUN_RESULT_ID = prrv3.RUN_RESULT_ID
527 	AND inpv3.NAME = 'Percentage'
528 	AND inpv3.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
529 	AND inpv3.LEGISLATION_CODE = 'NO'
530 	AND ppa.date_earned between inpv3.EFFECTIVE_START_DATE and inpv3.EFFECTIVE_END_DATE
531 	AND prrv3.INPUT_VALUE_ID = inpv3.INPUT_VALUE_ID
532 
533 	-- for input value 'Fixed Deduction Amount'
534 	AND prr.RUN_RESULT_ID = prrv4.RUN_RESULT_ID
535 	AND inpv4.NAME = 'Amount'
536 	AND inpv4.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
537 	AND inpv4.LEGISLATION_CODE = 'NO'
538 	AND ppa.date_earned between inpv4.EFFECTIVE_START_DATE and inpv4.EFFECTIVE_END_DATE
539 	AND prrv4.INPUT_VALUE_ID = inpv4.INPUT_VALUE_ID
540 
541 	-- for input value 'Third Party Payee'
542 	AND prr.RUN_RESULT_ID = prrv5.RUN_RESULT_ID
543 	AND inpv5.NAME = 'Third Party Payee'
544 	AND inpv5.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
545 	AND inpv5.LEGISLATION_CODE = 'NO'
546 	AND ppa.date_earned between inpv5.EFFECTIVE_START_DATE and inpv5.EFFECTIVE_END_DATE
547 	AND prrv5.INPUT_VALUE_ID = inpv5.INPUT_VALUE_ID
548     AND prrv5.result_value = p_payee_id;			  -- bug 14483053
549 
550 
551 -- cursor to get Third Party Destination Acc Number
552 
553 cursor csr_third_party_dest_acc (p_organization_id	NUMBER) is
554 	select  segment6
555 	from pay_external_accounts	acc
556 	,pay_org_payment_methods_f	pay_org
557 	,hr_organization_information	hoi
558 	where hoi.organization_id = p_organization_id
559 	and hoi.org_information_context = 'NO_THIRD_PARTY_PAYMENT'
560 	and pay_org.org_payment_method_id = hoi.org_information2
561 	and pay_org.pmeth_information1 = 'DESTINATION'
562 	and acc.external_account_id = pay_org.external_account_id;
563 
564 
565 -- cursor to get the third party loaction
566 
567 cursor csr_third_party_loc (p_organization_id	NUMBER) is
568 	select loc.address_line_1	line_1
569 	,loc.address_line_2		line_2
570 	,loc.address_line_3		line_3
571 	,hr_general.decode_fnd_comm_lookup('NO_POSTAL_CODE',loc.postal_code) post_code
572 	from hr_locations_all		loc
573 	,hr_all_organization_units	hou
574 	where hou.organization_id = p_organization_id
575 	and loc.location_id = hou.location_id
576 	and loc.style = 'NO';
577 
578 
579 
580 -- Variable declaration
581 
582 l_action_info_id	NUMBER;
583 l_ovn			NUMBER;
584 l_main_asg_act_id	NUMBER;
585 
586 rec_asg_detail		csr_asg_details%rowtype;
587 rec_loc_detail		csr_third_party_loc%rowtype;
588 
589 l_count			NUMBER := 0;
590 l_third_party_id	NUMBER;
591 l_third_party_name	VARCHAR2(240);
592 l_third_party_dest_acc	VARCHAR2(150);
593 l_tax_dedn_basis	NUMBER;
594 l_payroll_action_id	NUMBER;
595 l_payee_id number;				 -- bug 14483053
596 
597 
598  BEGIN
599  IF g_debug THEN
600  		hr_utility.set_location(' Entering Procedure ARCHIVE_CODE',380);
601  END IF;
602 	begin
603 		SELECT payroll_action_id
604 		INTO l_payroll_action_id
605 		FROM pay_assignment_actions
606 		WHERE assignment_action_id = p_assignment_action_id ;
607 	exception
608 	when others then
609 	null;
610 	end;
611 
612 
613        -- fetch the data
614       PAY_NO_UNION_DUES.GET_ALL_PARAMETERS(
615 			l_payroll_action_id
616 			,l_business_id
617 			,l_leg_emp_id
618 			,l_effective_date
619 			,l_archive
620 			,l_payee_org
621 			,l_from_date
622 			,l_to_date) ;
623 
624 
625  -- check if we have to archive again
626  IF  (l_archive = 'Y')
627 
628    THEN
629 	-- get the action_information_id and original/main assignment_action_id for this asg_act_id
630 	OPEN csr_get_act_info (p_assignment_action_id  , p_effective_date );
631 	FETCH csr_get_act_info  INTO l_action_info_id , l_main_asg_act_id, l_payee_id ;	 		-- bug 14483053
632 	CLOSE csr_get_act_info ;
633 
634 
635 	-- fetch assignment details
636 	OPEN csr_asg_details (l_main_asg_act_id, l_payee_id) ;							   -- bug 14483053
637 	FETCH csr_asg_details INTO rec_asg_detail ;
638 	CLOSE csr_asg_details ;
639 
640 
641 	l_third_party_id := to_number(rec_asg_detail.res_val_5) ;
642 
643 	-- to get the Third Party Name
644 	begin
645 		select name into l_third_party_name  from hr_organization_units where organization_id = l_third_party_id ;
646 	exception
647 		when others then
648 		null;
649 	end;
650 
651 	-- to get the third party Destination acc no.
652 	OPEN csr_third_party_dest_acc (l_third_party_id) ;
653 	FETCH csr_third_party_dest_acc INTO l_third_party_dest_acc ;
654 	CLOSE csr_third_party_dest_acc ;
655 
656 
657 	-- to get the third party location details
658 	OPEN csr_third_party_loc (l_third_party_id) ;
659 	FETCH csr_third_party_loc INTO rec_loc_detail;
660 	CLOSE csr_third_party_loc ;
661 
662 
663 	-- to fetch the balance value of TAX DEDUCTION BASIS
664 	begin
665 		select pay_balance_pkg.get_value(pay_no_emp_cont.get_defined_balance_id('Subject to Union Dues','_ASG_PTD'),
666 				l_main_asg_act_id)
667 		into l_tax_dedn_basis
668 		from dual;
669 	exception
670 		when others then
671 		null;
672 	end;
673 	l_third_party_dest_acc := substr(l_third_party_dest_acc,1,4)||'.'||
674 	                          substr(l_third_party_dest_acc,5,2)||'.'||
675 				  substr(l_third_party_dest_acc,7,5) ;
676 
677   -- Updating the Initial Archive Entries
678 	pay_action_information_api.update_action_information (
679 	 p_action_information_id        => l_action_info_id		-- in parameter
680 	,p_object_version_number        => l_ovn			-- in out parameter
681 	,p_action_information5          => l_third_party_id		--Third Party ID (Tax Collector ID)
682 	,p_action_information6          => l_third_party_name		--Third Party Name (Tax Collector's Name)
683 	,p_action_information7          => rec_loc_detail.line_1	--Third Party Address Line 1
684 	,p_action_information8          => rec_loc_detail.line_2	--Third Party Address Line 2
685 	,p_action_information9          => rec_loc_detail.line_3	--Third Party Address Line 3
686 	,p_action_information10          => rec_loc_detail.post_code	--Third Party Postal Code + City
687 	,p_action_information11         => l_third_party_dest_acc	--Third Party Destination Bank Account Number (Formatted)
688 	,p_action_information12         => rec_asg_detail.bg_id		--Business Group ID
689 	,p_action_information13         => rec_asg_detail.per_id	--PERSON_ID
690 	,p_action_information14         => rec_asg_detail.per_ni	--Person NATIONAL_IDENTIFIER
691 	,p_action_information15         => rec_asg_detail.last_name	--Person Lastname
692 
693 	,p_action_information16         => rec_asg_detail.first_name	--Person Firstname
694 	,p_action_information17         => rec_asg_detail.order_name	--Person Ordername
695 	,p_action_information18         => rec_asg_detail.emp_no	--Person Employee number
696 	,p_action_information19         => rec_asg_detail.dob		--Person date of birth
697 	,p_action_information20         => rec_asg_detail.doh		--Person date of hiring
698 	,p_action_information21         => rec_asg_detail.per_title	--Person title
699 
700 	,p_action_information22         => rec_asg_detail.res_val_2 	--Union Member Number (Input Value)
701 	,p_action_information23         => rec_asg_detail.res_val_3 	--Percentage (Input Value)
702 	,p_action_information24         => rec_asg_detail.res_val_4 	--Amount (Input Value)
703 	,p_action_information25         => rec_asg_detail.res_val_1 	--Deducted This Period (Input Value)
704 	,p_action_information26         => l_tax_dedn_basis );		--Tax Deduction Basis (Balance Value)
705 
706 
707 
708  END IF; -- l_archive = 'Y'
709 
710  IF g_debug THEN
711  		hr_utility.set_location(' Leaving Procedure ARCHIVE_CODE',390);
712  END IF;
713 
714  END ARCHIVE_CODE;
715 
716 -------------------------------------------------------------------------------------------------------------------------
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_from_date DATE;
726 l_to_date DATE;
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 from_date,pai.action_information6 to_date1,
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 5107535 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 Unionmemberno,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 Unionmemberno
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 TaxDeductionBasis,Unionmemberno,percentage,amount
779 order by thirdparty_id,order_name;
780 
781 
782 begin
783 
784 xml_ctr := 0;
785 /*pgopal - picking the charset dynamically from the db*/
786 l_IANA_charset := HR_NO_UTILITY.get_IANA_charset ;
787 --xml_tab(xml_ctr).xmlstring := '<?xml version="1.0" encoding="utf-8"?>';
788 xml_tab(xml_ctr).xmlstring := '<?xml version = "1.0" encoding = "'||l_IANA_charset||'"?>';
789 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '<START>';
790 xml_ctr := xml_ctr +1;
791 
792 IF p_payroll_action_id  IS NULL THEN
793 
794 	BEGIN
795 
796 		SELECT payroll_action_id
797 		INTO  l_payroll_action_id
798 		FROM pay_payroll_actions ppa,
799 		fnd_conc_req_summary_v fcrs,
800 		fnd_conc_req_summary_v fcrs1
801 		WHERE  fcrs.request_id = FND_GLOBAL.CONC_REQUEST_ID
802 		AND fcrs.priority_request_id = fcrs1.priority_request_id
803 		AND ppa.request_id between fcrs1.request_id  and fcrs.request_id
804 		AND ppa.request_id = fcrs1.request_id;
805 
806 	EXCEPTION
807 	WHEN others THEN
808 	NULL;
809 	END ;
810 	ELSE
811 		l_payroll_action_id:=p_payroll_action_id;
812 	END IF;
813 
814 
815 
816 for emp_rec in csr_emp loop
817 
818 if (l_thirdparty_id <> emp_rec.thirdparty_id) then
819   if csr_emp%rowcount = 1 then
820    open csr_legalemployer;
821    fetch csr_legalemployer into l_employer,l_orgnumber,l_from_date,l_to_date,l_archiver;
822    close csr_legalemployer;
823  xml_tab(xml_ctr).xmlstring := '<LEGALEMPLOYER_RECORD>';
824  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '<Employer>'||l_employer||'</Employer>';
825  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Organizationnumber>'||l_orgnumber||'</Organizationnumber>';
826  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<FromPeriod>'||l_from_date||'</FromPeriod>';
827  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<ToPeriod>'||l_to_date||'</ToPeriod>';
828  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '<Archiver>'||l_archiver||'</Archiver>';
829  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '</LEGALEMPLOYER_RECORD>';
830  xml_ctr := xml_ctr +1;
831  xml_tab(xml_ctr).xmlstring := '<THIRDPARTY_RECORD>';
832 else
833  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '</THIRDPARTY_RECORD>';
834  xml_ctr := xml_ctr + 1;
835  xml_tab(xml_ctr).xmlstring := '<THIRDPARTY_RECORD>';
836 end if ;
837 
838  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Thirdparty_Name>'||emp_rec.thirdparty_name||'</Thirdparty_Name>';
839  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Thirdparty_address>'||emp_rec.thirdparty_address||'</Thirdparty_address>';
840  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<BankAccountno>'||emp_rec.bankaccountno||'</BankAccountno>';
841  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EMP_RECORD>';
842  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Emp-number>'||emp_rec.emp_no||'</Emp-number>';
843  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Name>'||emp_rec.order_name||'</Name>';
844  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<TaxDeductionBasis>'||emp_rec.taxdeductionbasis||'</TaxDeductionBasis>';
845  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Unionmemberno>'||emp_rec.Unionmemberno||'</Unionmemberno>';
846  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Percentage>'||emp_rec.percentage||'</Percentage>';
847  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Amount>'||emp_rec.amount||'</Amount>';
848  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Deductedthisperiod>'||emp_rec.deductedthisperiod||'</Deductedthisperiod>';
849  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<NI-number>'||emp_rec.ni||'</NI-number>';
850  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeLastName>'|| emp_rec.last_name ||'</EmployeeLastName>';
851  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeFirstName>'|| emp_rec.first_name ||'</EmployeeFirstName>';
852  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeDOB>'|| emp_rec.emp_dob ||'</EmployeeDOB>';
853  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeDOH>'|| emp_rec.emp_doh ||'</EmployeeDOH>';
854  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeTitle>'|| emp_rec.emp_title ||'</EmployeeTitle>';
855  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring||'</EMP_RECORD>';
856  l_thirdparty_id := emp_rec.thirdparty_id;
857 else
858  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EMP_RECORD>';
859  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Emp-number>'||emp_rec.emp_no||'</Emp-number>';
860  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Name>'||emp_rec.order_name||'</Name>';
861  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<TaxDeductionBasis>'||emp_rec.taxdeductionbasis||'</TaxDeductionBasis>';
862  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Unionmemberno>'||emp_rec.Unionmemberno||'</Unionmemberno>';
863  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Percentage>'||emp_rec.percentage||'</Percentage>';
864  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Amount>'||emp_rec.amount||'</Amount>';
865  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Deductedthisperiod>'||emp_rec.deductedthisperiod||'</Deductedthisperiod>';
866  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<NI-number>'||emp_rec.ni||'</NI-number>';
867  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeLastName>'|| emp_rec.last_name ||'</EmployeeLastName>';
868  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeFirstName>'|| emp_rec.first_name ||'</EmployeeFirstName>';
869  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeDOB>'|| emp_rec.emp_dob ||'</EmployeeDOB>';
870  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeDOH>'|| emp_rec.emp_doh ||'</EmployeeDOH>';
871  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeTitle>'|| emp_rec.emp_title ||'</EmployeeTitle>';
872  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring||'</EMP_RECORD>';
873  l_thirdparty_id := emp_rec.thirdparty_id;
874 end if ;
875 end loop;
876 
877 if(xml_tab(xml_ctr).xmlstring is null) THEN
878  raise no_data_found;
879 else
880  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring||'</THIRDPARTY_RECORD>';
881  xml_ctr := xml_ctr +1;
882  xml_tab(xml_ctr).xmlstring := '</START>';
883 end if;
884 
885 write_to_clob(p_xml);
886 
887 exception when no_data_found then
888  xml_tab(xml_ctr).xmlstring := '<LEGALEMPLOYER_RECORD>';
889  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '<Employer>'||'</Employer>';
890  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Organizationnumber>'||'</Organizationnumber>';
891  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<FromPeriod>'||'</FromPeriod>';
892  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<ToPeriod>'||'</ToPeriod>';
893  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '<Archiver>'||'</Archiver>';
894  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '</LEGALEMPLOYER_RECORD>';
895  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<THIRDPARTY_RECORD>';
896  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Thirdparty_Name>'||'</Thirdparty_Name>';
897  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Thirdparty_address>'||'</Thirdparty_address>';
898  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<BankAccountno>'||'</BankAccountno>';
899  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EMP_RECORD>';
900  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Emp-number>'||'</Emp-number>';
901  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Name>'||'</Name>';
902  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<TaxDeductionBasis>'||'</TaxDeductionBasis>';
903  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Unionmemberno>'||'</Unionmemberno>';
904  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Percentage>'||'</Percentage>';
905  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Amount>'||'</Amount>';
906  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Deductedthisperiod>'||'</Deductedthisperiod>';
907  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<NI-number>'||'</NI-number>';
908  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeLastName>'||'</EmployeeLastName>';
909  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeFirstName>'||'</EmployeeFirstName>';
910  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeDOB>'||'</EmployeeDOB>';
911  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeDOH>'||'</EmployeeDOH>';
912  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeTitle>'||'</EmployeeTitle>';
913  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring||'</EMP_RECORD>';
914  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'</THIRDPARTY_RECORD>';
915  xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'</START>';
916 
917 write_to_clob(p_xml);
918 
919 end populate_details;
920 
921 
922 ------------------------------------------------------------------------------------------------------------
923 
924 procedure write_to_clob (p_xml out nocopy clob) is
925 l_xfdf_string clob;
926 begin
927 
928 dbms_lob.createtemporary(p_xml,false,dbms_lob.call);
929 dbms_lob.open(p_xml,dbms_lob.lob_readwrite);
930 if xml_tab.count > 0 then
931  for ctr_table in xml_tab.first .. xml_tab.last loop
932  dbms_lob.writeappend( p_xml, length(xml_tab(ctr_table).xmlstring), xml_tab(ctr_table).xmlstring );
933  end loop;
934 end if;
935 --dbms_lob.createtemporary(p_xml,true);
936 --clob_to_blob(l_xfdf_string,p_xml);
937 exception
938 when others then
939 hr_utility.trace('sqleerm ' || sqlerrm);
940 hr_utility.raise_error;
941 
942 end write_to_clob;
943 ------------------------------------------------------------------------------------------------------------
944 /* Added the function GET_UNION_DUE_GLOBAL for the BUG:14549415  */
945 
946 FUNCTION GET_UNION_DUE_GLOBAL
947   (p_payroll_action_id IN            number
948  , p_organization_id   IN            number
949  , p_exists            IN OUT NOCOPY number) RETURN number IS
950   p_global_id number;
951   p_global_value number DEFAULT 0;
952 BEGIN
953 p_global_value := 0;
954   BEGIN
955     SELECT  org_information5
956     INTO    p_global_id
957     FROM    hr_organization_information
958     WHERE   org_information_context = 'NO_THIRD_PARTY_PAYMENT'
959     AND     organization_id = p_organization_id;
960    IF p_global_id is null then
961      p_exists := 0;
962      RETURN p_global_value;
963    END IF;
964   EXCEPTION
965     WHEN no_data_found THEN
966       p_exists := 0;
967     RETURN p_global_value;
968   END;
969 
970 
971       IF (p_exists <> 0) AND (p_global_id is not null) THEN
972          BEGIN
973           SELECT  fffunc.cn (decode (data_type, 'N'
974                                    , global_value, NULL))
975           INTO    p_global_value
976           FROM    ff_globals_f glb
977                 , pay_payroll_actions ppa
978           WHERE   glb.global_id = p_global_id
979           AND     ppa.payroll_action_id = p_payroll_action_id
980           AND     ppa.effective_date
981                   BETWEEN glb.effective_start_date
982                   AND     glb.effective_end_date;
983         if p_global_value is null then
984             p_exists := 0;
985         END IF;
986        END;
987      END IF;
988 
989   RETURN p_global_value;
990 
991 
992 END get_union_due_global;
993 
994 ------------------------------------------------------------------------------------------------------------
995 
996  END PAY_NO_UNION_DUES;