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_SUPPORT_ORDER

Source


1 PACKAGE BODY PAY_NO_SUPPORT_ORDER AS
2 /* $Header: pynosupord.pkb 120.5 2010/10/27 07:22:00 vijranga ship $ */
3 g_debug   boolean   :=  hr_utility.debug_enabled;
4 
5 ------------------------------------------------------------------------------------------------------
6  /* GET PARAMETER */
7  FUNCTION GET_PARAMETER(
8 	 p_parameter_string IN VARCHAR2
9 	,p_token            IN VARCHAR2
10 	,p_segment_number   IN NUMBER default NULL ) RETURN VARCHAR2
11  IS
12    l_parameter  pay_payroll_actions.legislative_parameters%TYPE:=NULL;
13    l_start_pos  NUMBER;
14    l_delimiter  VARCHAR2(1):=' ';
15 
16 BEGIN
17 IF g_debug THEN
18 	  hr_utility.set_location(' Entering Function GET_PARAMETER',10);
19 END IF;
20  l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
21 
22  IF l_start_pos = 0 THEN
23 	l_delimiter := '|';
24 	l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
25  END IF;
26 
27  IF l_start_pos <> 0 THEN
28 	l_start_pos := l_start_pos + length(p_token||'=');
29 	l_parameter := substr(p_parameter_string, l_start_pos,
30 			  instr(p_parameter_string||' ', l_delimiter,l_start_pos) - l_start_pos);
31 
32 	 IF p_segment_number IS NOT NULL THEN
33 		l_parameter := ':'||l_parameter||':';
34 		l_parameter := substr(l_parameter,
35 		instr(l_parameter,':',1,p_segment_number)+1,
36 		instr(l_parameter,':',1,p_segment_number+1) -1
37 		- instr(l_parameter,':',1,p_segment_number));
38 	END IF;
39 END IF;
40 
41 	RETURN l_parameter;
42 IF g_debug THEN
43 	      hr_utility.set_location(' Leaving Function GET_PARAMETER',20);
44 END IF;
45 
46  END GET_PARAMETER;
47 
48 -------------------------------------------------------------------------------------------------------------------------
49 /* GET ALL PARAMETERS */
50 PROCEDURE GET_ALL_PARAMETERS(
51  		 p_payroll_action_id	IN           NUMBER
52 		,p_business_group_id    OUT  NOCOPY  NUMBER
53 		,p_legal_employer_id	OUT  NOCOPY  NUMBER
54 		,p_element_type_id	OUT  NOCOPY  NUMBER
55 		,p_effective_date	OUT  NOCOPY  DATE
56 		,p_from_date		OUT  NOCOPY  DATE
57 		,p_to_date		OUT  NOCOPY  DATE
58 		,p_third_party_id	OUT  NOCOPY  NUMBER
59 		,p_archive		OUT  NOCOPY  VARCHAR2
60 		) IS
61 
62 
63 	CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
64 	SELECT 	 PAY_NO_SUPPORT_ORDER.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER_ID')
65 		,PAY_NO_SUPPORT_ORDER.GET_PARAMETER(legislative_parameters,'ELEMENT_TYPE_ID')
66 		,fnd_date.canonical_to_date(PAY_NO_SUPPORT_ORDER.GET_PARAMETER(legislative_parameters,'FROM_DATE'))
67 		,fnd_date.canonical_to_date(PAY_NO_SUPPORT_ORDER.GET_PARAMETER(legislative_parameters,'TO_DATE'))
68 		,PAY_NO_SUPPORT_ORDER.GET_PARAMETER(legislative_parameters,'THIRD_PARTY_PAYEE')
69 		,PAY_NO_SUPPORT_ORDER.GET_PARAMETER(legislative_parameters,'ARCHIVE')
70 		,effective_date
71 		,business_group_id
72 	FROM  pay_payroll_actions
73 	WHERE payroll_action_id = p_payroll_action_id;
74 
75 BEGIN
76 
77 
78 
79 	 OPEN csr_parameter_info (p_payroll_action_id);
80 	 FETCH csr_parameter_info
81 	 INTO  p_legal_employer_id
82 	      ,p_element_type_id
83 	      ,p_from_date
84 	      ,p_to_date
85 	      ,p_third_party_id
86 	      ,p_archive
87 	      ,p_effective_date
88 	      ,p_business_group_id;
89 	 CLOSE csr_parameter_info;
90 
91 IF g_debug THEN
92       hr_utility.set_location(' Leaving Procedure GET_ALL_PARAMETERS',30);
93 END IF;
94 
95  END GET_ALL_PARAMETERS;
96 
97 -------------------------------------------------------------------------------------------------------------------------
98  /* RANGE CODE */
99  PROCEDURE RANGE_CODE (pactid    IN    NUMBER
100 		      ,sqlstr    OUT   NOCOPY VARCHAR2)
101  IS
102 
103 -- Variable declarations
104 
105 	l_count			NUMBER := 0;
106 	l_archive		VARCHAR2(3);
107 
108 	l_action_info_id	NUMBER;
109 	l_ovn			NUMBER;
110 	l_business_id		NUMBER;
111 	l_bimonth_year		NUMBER;
112 	l_bimonth_term		VARCHAR2(10);
113 	l_le_org_num		VARCHAR2(240);
114 	l_le_name		VARCHAR2(240);
115 	l_leg_emp_id		NUMBER;
116 	l_effective_date	DATE;
117 	l_third_party_id	NUMBER;
118 	l_from_date		DATE;
119 	l_to_date		DATE;
120 	l_third_party_name	VARCHAR2(240);
121 	l_ele_type_id		NUMBER;
122        	l_element_name		VARCHAR2(240);
123 	 l_def_bal_id		NUMBER;
124 	 ipv_third_party	VARCHAR2(240);
125 	 ipv_pay_value		VARCHAR2(240);
126 	 ipv_amount		VARCHAR2(240);
127 	 ipv_percent		VARCHAR2(240);
128 	 ipv_ref_num		VARCHAR2(240);
129 	 l_info_id		NUMBER;
130 	 info_amt		VARCHAR2(240);
131 	 info_percent		VARCHAR2(240);
132 	 info_refnum		VARCHAR2(240);
133 	 l_archived_on		VARCHAR2(240);
134 	 l_ele_code		VARCHAR2(150);
135 
136 	 /* Bug Fix 5110016 : New variable l_reporting_name added for storing the reporting name of the element */
137 	 l_reporting_name	VARCHAR2(240);
138 
139 -- Cursors
140 
141 
142 -- cursor to get Legal Employer Name and Org Num
143 
144 cursor csr_le_details ( l_leg_emp_id  NUMBER ) is
145 	select hou.name ,hoi.org_information1
146 	from hr_organization_units          hou
147 	    ,hr_organization_information    hoi
148 	where hou.organization_id = l_leg_emp_id
149 	and   hoi.organization_id = l_leg_emp_id
150 	and   hoi.org_information_context = to_char('NO_LEGAL_EMPLOYER_DETAILS');
151 
152 
153 
154 -- cursor to get the element name
155 CURSOR csr_element_name (l_ele_type_id  NUMBER) IS
156 	SELECT element_name
157 	FROM pay_element_types_f
158 	WHERE ELEMENT_TYPE_ID = l_ele_type_id ;
159 	--AND   LEGISLATION_CODE = 'NO' ;
160 
161 /* Bug Fix 5110016 : New cursor csr_reporting_name added for getting the reporting name of the element */
162 
163 -- cursor to get the reporting name for element
164 CURSOR csr_reporting_name (l_ele_type_id  NUMBER) IS
165 	SELECT nvl(REPORTING_NAME , ELEMENT_NAME)
166 	FROM pay_element_types_f
167 	WHERE ELEMENT_TYPE_ID = l_ele_type_id ;
168 
169 
170 /* Bug Fix 5110016 : Added INFORMATION_TYPE check in the cursor csr_element_info_iv. */
171 
172 -- cursor to get the other input value names for this element
173 
174 /*
175 CURSOR csr_element_info_iv (l_ele_type_id  NUMBER) IS
176 	select EEI_INFORMATION2	, EEI_INFORMATION3 , EEI_INFORMATION4
177 	from pay_element_type_extra_info
178 	where ELEMENT_TYPE_ID = l_ele_type_id ;
179 */
180 
181 CURSOR csr_element_info_iv (l_ele_type_id  NUMBER) IS
182 	select EEI_INFORMATION2	, EEI_INFORMATION3 , EEI_INFORMATION4
183 	from pay_element_type_extra_info
184 	where ELEMENT_TYPE_ID = l_ele_type_id
185 	and INFORMATION_TYPE = 'NO_EMPLOYEE_DEDUCTION_REPORT';
186 
187 
188 --cursor get the input value id for the input value names above
189 CURSOR csr_element_iv_id (l_ele_type_id  NUMBER , l_iv_name VARCHAR2) IS
190 	select INPUT_VALUE_ID
191 	from pay_input_values_f
192 	where ELEMENT_TYPE_ID = l_ele_type_id
193 	AND NAME = l_iv_name ;
194 
195 
196 -- cursor to fetch the element details
197 
198 cursor csr_element_details (p_ele_type_id  NUMBER) IS
199 
200 	select
201 	      ipv1.INPUT_VALUE_ID		ipv1_id  -- Third Party Payee
202 	      ,ipv2.INPUT_VALUE_ID		ipv2_id	 -- Pay Value
203 	      ,info.ELEMENT_TYPE_EXTRA_INFO_ID	info_id
204 	      ,info.EEI_INFORMATION5		def_bal_id
205 
206 	from pay_element_types_f	ele
207 	,pay_element_type_extra_info	info
208 	,pay_input_values_f		ipv1
209 	,pay_input_values_f		ipv2
210 
211 	where ele.ELEMENT_TYPE_ID = p_ele_type_id
212 	-- for pay_element_type_extra_info
213 	AND info.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
214 	AND info.INFORMATION_TYPE = 'NO_EMPLOYEE_DEDUCTION_REPORT'
215 
216 	-- for input value Third Party Payee
217 	AND ipv1.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
218 	AND ipv1.NAME = 'Third Party Payee'
219 
220 	-- for input value Pay Value
221 	AND ipv2.ELEMENT_TYPE_ID = ele.ELEMENT_TYPE_ID
222 	AND ipv2.NAME = 'Pay Value' ;
223 
224 
225  --cursor to get element code
226  cursor csr_ele_code(p_ele_type_id  NUMBER
227 	             ,p_leg_emp_id varchar2) is
228 	select nvl((select eei_information1 from pay_element_type_extra_info petei
229 	where petei.information_type='NO_ELEMENT_CODES'
230 	and element_type_id = p_ele_type_id
231 	and petei.eei_information2 = p_leg_emp_id
232 	and rownum=1),
233 	(select eei_information1 from pay_element_type_extra_info petei
234 	where petei.information_type='NO_ELEMENT_CODES'
235 	and element_type_id = p_ele_type_id
236 	and eei_information2 is null
237 	and rownum=1)) from dual;
238 
239 
240  -- fetch the element details of 'Wage Attachment Support Order'
241 
242 cursor csr_waso_deatils (p_ele_type_id  NUMBER) is
243 
244 	select ipv1.INPUT_VALUE_ID		ipv1_id -- Third Party Payee
245 	      ,ipv2.INPUT_VALUE_ID		ipv2_id -- Fixed Deduction Amount
246 	      ,ipv3.INPUT_VALUE_ID		ipv3_id -- Deduction Percentage
247 	      ,ipv4.INPUT_VALUE_ID		ipv4_id -- Reference Number
248 	      ,ipv5.INPUT_VALUE_ID		ipv5_id -- Pay Value
249 
250 	from pay_input_values_f		ipv1
251 	,pay_input_values_f		ipv2
252 	,pay_input_values_f		ipv3
253 	,pay_input_values_f		ipv4
254 	,pay_input_values_f		ipv5
255 
256 	WHERE
257 	-- for input value Third Party Payee
258 	ipv1.ELEMENT_TYPE_ID = p_ele_type_id
259 	AND ipv1.NAME = 'Third Party Payee'
260 
261 	-- for input value AMOUNT
262 	AND ipv2.ELEMENT_TYPE_ID = p_ele_type_id
263 	AND ipv2.NAME = 'Fixed Deduction Amount'
264 
265 	-- for input value PERCENTAGE
266 	AND ipv3.ELEMENT_TYPE_ID = p_ele_type_id
267 	AND ipv3.NAME = 'Deduction Percentage'
268 
269 	-- for input value REFERENCE NUMBER
270 	AND ipv4.ELEMENT_TYPE_ID = p_ele_type_id
271 	AND ipv4.NAME = 'Reference Number'
272 
273 	-- for input value PAY VALUE
274 	AND ipv5.ELEMENT_TYPE_ID = p_ele_type_id
275 	AND ipv5.NAME = 'Pay Value' ;
276 
277 
278   BEGIN
279  IF g_debug THEN
280       hr_utility.set_location(' Entering Procedure RANGE_CODE',40);
281  END IF;
282 
283  -- the sql string to return
284  sqlstr := 'SELECT DISTINCT person_id
285 	FROM  per_people_f ppf
286 	     ,pay_payroll_actions ppa
287 	WHERE ppa.payroll_action_id = :payroll_action_id
288 	AND   ppa.business_group_id = ppf.business_group_id
289 	ORDER BY ppf.person_id';
290 
291    -- fetch the data for the REPORT DETAILS
292  PAY_NO_SUPPORT_ORDER.GET_ALL_PARAMETERS(
293 		pactid
294 		,l_business_id
295 		,l_leg_emp_id
296 		,l_ele_type_id
297 		,l_effective_date
298 		,l_from_date
299 		,l_to_date
300 		,l_third_party_id
301 		,l_archive ) ;
302 
303 
304  -- check if we have to archive again
305  IF  (l_archive = 'Y')
306 
307    THEN
308 
309    -- check if record for current archive exists
310    SELECT count(*)  INTO l_count
311    FROM   pay_action_information
312    WHERE  action_information_category = 'EMEA REPORT DETAILS'
313    AND    action_information1         = 'PYNOSUPORDA'
314    AND    action_context_id           = pactid;
315 
316    -- archive Report Details only if no record exists
317    IF (l_count < 1)
318 
319        THEN
320 
321       -- fetch LE name and LE Org Num from LE ID
322        OPEN csr_le_details (l_leg_emp_id) ;
323        FETCH csr_le_details INTO l_le_name , l_le_org_num ;
324        CLOSE csr_le_details ;
325 
326 	-- to get the Third Party Name
327 	IF (l_third_party_id IS NOT NULL) THEN
328 
329 		l_third_party_id := to_number(l_third_party_id) ;
330 		select name into l_third_party_name  from hr_organization_units where organization_id = l_third_party_id ;
331 
332 
333 	END IF;
334 	-------------------------------------------------------------------
335 
336 	-- get the name of the element
337 	OPEN csr_element_name (l_ele_type_id);
338 	FETCH csr_element_name INTO l_element_name ;
339 	CLOSE csr_element_name ;
340 
341 	-- get the reporting name of the element
342 	OPEN csr_reporting_name (l_ele_type_id);
343 	FETCH csr_reporting_name INTO l_reporting_name ;
344 	CLOSE csr_reporting_name ;
345 
346 
347 	IF (l_element_name = 'Wage Attachment Support Order')
348 	  THEN
349 
350 
351 		/* Bug Fix 5110016 : TOTAL_PAY_ASG_PTD replaced by WAGE_ATTACHMENT_SUPPORT_ORDER_BASE_ASG_PTD
352 				     to be used as basis for Support Order Calculation */
353 
354 		-- get the defined balance id for the deduction balance of waso = TOTAL_PAY_ASG_PTD
355 		/* SELECT pay_no_emp_cont.get_defined_balance_id('Total Pay','_ASG_PTD') INTO l_def_bal_id FROM   dual ; */
356 
357 		-- deduction balance of waso now changed to WAGE_ATTACHMENT_SUPPORT_ORDER_BASE_ASG_PTD
358 		SELECT pay_no_emp_cont.get_defined_balance_id('Wage Attachment Support Order Base','_ASG_PTD') INTO l_def_bal_id FROM   dual ;
359 
360 		OPEN csr_waso_deatils (l_ele_type_id) ;
361 		FETCH csr_waso_deatils INTO ipv_third_party ,ipv_amount , ipv_percent ,ipv_ref_num , ipv_pay_value ;
362 		CLOSE csr_waso_deatils ;
363 
364 		l_info_id :=  NULL;
365 
366 		open csr_ele_code(l_ele_type_id,l_leg_emp_id);
367 		fetch csr_ele_code into l_ele_code;
368 		close csr_ele_code;
369 
370 	  ELSE -- the element is a user defined element
371 
372 
373 		-- get the details for this element
374 		OPEN csr_element_details (l_ele_type_id) ;
375 		FETCH csr_element_details INTO ipv_third_party , ipv_pay_value , l_info_id , l_def_bal_id ;
376 		CLOSE csr_element_details ;
377 
378 		open csr_ele_code(l_ele_type_id,l_leg_emp_id);
379 		fetch csr_ele_code into l_ele_code;
380 		close csr_ele_code;
381 
382 
383 		-- get the other input value names for this element
384 		OPEN csr_element_info_iv (l_ele_type_id);
385 		FETCH csr_element_info_iv into info_amt , info_percent , info_refnum ;
386 		CLOSE csr_element_info_iv ;
387 
388 
389 		-- get the input value id for the input value 'Amount'
390 		OPEN csr_element_iv_id (l_ele_type_id , info_amt);
391 		FETCH csr_element_iv_id into ipv_amount ;
392 		CLOSE csr_element_iv_id ;
393 
394 		-- get the input value id for the input value 'Percentage'
395 		OPEN csr_element_iv_id (l_ele_type_id ,info_percent );
396 		FETCH csr_element_iv_id into ipv_percent ;
397 		CLOSE csr_element_iv_id ;
398 
399 
400 		-- get the input value id for the input value 'Reference Number'
401 		OPEN csr_element_iv_id (l_ele_type_id , info_refnum);
402 		FETCH csr_element_iv_id into ipv_ref_num ;
403 		CLOSE csr_element_iv_id ;
404 
405 
406 	END IF;
407 
408 	/* Bug Fix 5110016 : Reporting Name should be displayed instead of element name where available */
409 
410 	l_element_name := l_reporting_name ;
411 
412 	l_archived_on := to_char(l_effective_date) || ' (' || to_char(pactid) ||')';
413 
414 	-------------------------------------------------------------------
415 
416        -- Archive the REPORT DETAILS
417 
418 		pay_action_information_api.create_action_information (
419 		 p_action_information_id        => l_action_info_id	-- out parameter
420 		,p_object_version_number        => l_ovn		-- out parameter
421 		,p_action_context_id            => pactid		-- context id = payroll action id (of Archive)
422 		,p_action_context_type          => 'PA'			-- context type
423 		,p_effective_date               => l_effective_date	-- Date of running the archive
424 		,p_action_information_category  => 'EMEA REPORT DETAILS' -- Information Category
425 		,p_tax_unit_id                  => l_leg_emp_id		-- Legal Employer ID
426 		,p_jurisdiction_code            => NULL			-- Tax Municipality ID
427 		,p_action_information1          => 'PYNOSUPORDA'	-- Conc Prg Short Name
428 		,p_action_information2          => NULL			-- Local Unit ID
429 		,p_action_information3          => l_le_name		-- Legal Employer Name
430 		,p_action_information4          => l_le_org_num		-- Legal Employer Organization Number
431 		,p_action_information5          => l_from_date		-- Reporting From Date
432 		,p_action_information6          => l_to_date		-- Reporting To Date
433 		,p_action_information7          => l_business_id 	-- Business Group ID
434 		,p_action_information8          => l_third_party_id	-- Third Party Org Id
435 		,p_action_information9          => l_third_party_name   -- Third Party Org Name
436 		,p_action_information10         => l_ele_type_id	-- Element Type ID
437 		,p_action_information11         => l_element_name	-- Element Name
438 		,p_action_information12         => l_ele_code		-- Element Code
439 		,p_action_information13         => ipv_third_party	-- Input Value ID 1 = Third Party Payee
440 		,p_action_information14         => ipv_amount		-- Input Value ID 2 = Amount
441 		,p_action_information15         => ipv_percent		-- Input Value ID 3 = Percentage
442 		,p_action_information16         => ipv_ref_num		-- Input Value ID 4 = Reference Number
443 		,p_action_information17         => ipv_pay_value	-- Input Value ID 5 = Pay Value
444 		,p_action_information18         => l_def_bal_id		-- Defined Balance ID
445 		,p_action_information19         => l_info_id		-- ELEMENT_TYPE_EXTRA_INFO_ID for this element
446 		,p_action_information20         => l_archived_on	-- Archived On
447 		);
448 
449 
450    END IF; -- l_count < 1
451 
452  END IF; -- l_archive = 'Y'
453 
454 
455 IF g_debug THEN
456       hr_utility.set_location(' Leaving Procedure RANGE_CODE',50);
457  END IF;
458 
459  END RANGE_CODE;
460 
461 -------------------------------------------------------------------------------------------------------------------------
462  /* INITIALIZATION CODE */
463  PROCEDURE INITIALIZATION_CODE(p_payroll_action_id IN NUMBER)
464  IS
465 
466  BEGIN
467   IF g_debug THEN
468       hr_utility.set_location(' Entering Procedure INITIALIZATION_CODE',80);
469  END IF;
470 
471 	NULL;
472 
473 IF g_debug THEN
474       hr_utility.set_location(' Leaving Procedure INITIALIZATION_CODE',90);
475 END IF;
476  END INITIALIZATION_CODE;
477 
478 -------------------------------------------------------------------------------------------------------------------------
479  /* ASSIGNMENT ACTION CODE */
480  PROCEDURE ASSIGNMENT_ACTION_CODE
481  (p_payroll_action_id     IN NUMBER
482  ,p_start_person          IN NUMBER
483  ,p_end_person            IN NUMBER
484  ,p_chunk                 IN NUMBER)
485  IS
486 
487  ---------------------------------------------------------------------------------------------------------------
488 CURSOR csr_assignments
489         (p_payroll_action_id    NUMBER,
490 	 p_leg_emp_id		NUMBER,
491          p_start_person      	NUMBER,
492          p_end_person           NUMBER,
493          l_start_date		DATE,
494          l_end_date		DATE,
495 	 p_bus_grp_id		NUMBER,
496 	 p_element_type_id	NUMBER,
497 	 p_third_party_id	VARCHAR2)
498 
499  IS
500 
501 	SELECT
502 	assact.ASSIGNMENT_ID		asg_id
503 	,assact.assignment_action_id	asg_act_id
504 	,assact.TAX_UNIT_ID		tax_unit_id
505 	,prr.LOCAL_UNIT_ID		local_unit_id
506 
507 	FROM
508 	pay_assignment_actions	assact
509 	,pay_assignment_actions	assact1
510 	,pay_payroll_actions	ppa
511 	,pay_payroll_actions	ppa2
512 	,pay_payroll_actions	ppa3
513 	,per_all_assignments_f	asg
514 	,pay_run_results	prr
515 	,pay_run_result_values	prrv
516 	,pay_input_values_f	inpv
517 	,pay_action_interlocks  pai
518 
519 	WHERE -- initial conditions
520 
521 	ppa.payroll_action_id = p_payroll_action_id
522 
523 	-- for 2nd pay payroll act table
524 	AND ppa2.date_earned between l_start_date and l_end_date
525 	AND ppa2.action_type IN ('R','Q')  -- Payroll Run or Quickpay Run
526 
527 	-- for asg act table
528 	AND assact.PAYROLL_ACTION_ID = ppa2.PAYROLL_ACTION_ID
529 	AND assact.TAX_UNIT_ID = p_leg_emp_id
530 	AND assact.action_status = 'C'  -- Completed
531 	AND assact.source_action_id  IS NOT NULL -- Not Master Action
532 
533 	-- for asg table
534 	AND assact.ASSIGNMENT_ID = asg.ASSIGNMENT_ID
535 	--AND ppa.date_earned between asg.EFFECTIVE_START_DATE and asg.EFFECTIVE_END_DATE
536 	--AND ppa.effective_date between asg.EFFECTIVE_START_DATE and asg.EFFECTIVE_END_DATE
537 	-- To pick the terminated assignments.
538 	AND ppa2.effective_date between asg.EFFECTIVE_START_DATE and asg.EFFECTIVE_END_DATE
539 	AND asg.person_id   BETWEEN p_start_person AND p_end_person
540 
541 	-- for run results
542 	AND assact.ASSIGNMENT_ACTION_ID = prr.ASSIGNMENT_ACTION_ID
543 
544 	--for prepayments
545 	AND    assact1.action_status           IN ('C','S') -- 10229512
546 	AND    assact1.assignment_action_id    = pai.locking_action_id
547 	AND    assact1.payroll_action_id       = ppa3.payroll_action_id
548 	AND    ppa3.action_type            IN ('P','U')
549 	AND    ppa3.date_earned between l_start_date and l_end_date
550 
551 	AND assact.ASSIGNMENT_ACTION_ID = pai.locked_action_id
552 
553 
554 	-- for element 'Wage Attachment Tax Levy' and USER DEFINED DEDUCTION ELEMENTS
555 	AND prr.ELEMENT_TYPE_ID = p_element_type_id
556 	AND prr.RUN_RESULT_ID = prrv.RUN_RESULT_ID
557 	AND prrv.INPUT_VALUE_ID = inpv.INPUT_VALUE_ID
558 	AND inpv.ELEMENT_TYPE_ID = p_element_type_id
559 	AND inpv.NAME = 'Third Party Payee'
560 	AND prrv.result_value = nvl(p_third_party_id,prrv.result_value)
561 
562 
563 	ORDER BY assact.assignment_id ;
564 
565 
566 
567 
568  -- cursor to get the jurisdiction code (Tax Municipality)
569 
570 CURSOR csr_tax_mun_id (p_assignment_action_id  NUMBER, p_assignment_id  NUMBER) IS
571 
572 	SELECT act_con.CONTEXT_VALUE	tax_mun_id
573 
574 	FROM pay_action_contexts  act_con
575 	    ,ff_contexts	  con
576 
577 	WHERE  con.CONTEXT_NAME = 'JURISDICTION_CODE'
578 	AND act_con.CONTEXT_ID = con.CONTEXT_ID
579 	AND act_con.ASSIGNMENT_ACTION_ID = p_assignment_action_id
580 	AND act_con.ASSIGNMENT_ID = p_assignment_id ;
581 
582 
583 l_count			NUMBER := 0;
584 l_archive		VARCHAR2(3);
585 
586 l_action_info_id	NUMBER;
587 l_ovn			NUMBER;
588 
589 l_business_id		NUMBER;
590 l_bimonth_year		NUMBER;
591 l_bimonth_term		VARCHAR2(10);
592 l_le_org_num		VARCHAR2(240);
593 l_le_name		VARCHAR2(240);
594 l_leg_emp_id		NUMBER;
595 l_effective_date	DATE;
596 l_start_date		DATE;
597 l_end_date		DATE;
598 l_actid			NUMBER;
599 l_local_unit_id		NUMBER;
600 l_third_party_id	NUMBER;
601 l_from_date		DATE;
602 l_to_date		DATE;
603 l_tax_mun_id		VARCHAR2(240);
604 --rec_waso		csr_waso_deatils%rowtype;
605 l_def_bal_id		NUMBER;
606 l_ele_type_id		NUMBER;
607 
608 BEGIN
609 IF g_debug THEN
610       hr_utility.set_location(' Entering Procedure ASSIGNMENT_ACTION_CODE',60);
611 END IF;
612 
613       -- fetch the data
614 	 PAY_NO_SUPPORT_ORDER.GET_ALL_PARAMETERS(
615 			p_payroll_action_id
616 			,l_business_id
617 			,l_leg_emp_id
618 			,l_ele_type_id
619 			,l_effective_date
620 			,l_from_date
621 			,l_to_date
622 			,l_third_party_id
623 			,l_archive ) ;
624 
625    	 -- check if we have to archive again
626 	 IF  (l_archive = 'Y')
627 
628 	   THEN
629 
630 		FOR rec_asg IN csr_assignments
631 				(p_payroll_action_id ,
632 				 l_leg_emp_id ,
633 				 p_start_person ,
634 				 p_end_person ,
635 				 l_from_date ,
636 				 l_to_date ,
637 				 l_business_id ,
638 				 l_ele_type_id,
639 				 l_third_party_id     ) LOOP
640 
641 			OPEN csr_tax_mun_id (rec_asg.asg_act_id , rec_asg.asg_id );
642 			FETCH csr_tax_mun_id INTO l_tax_mun_id ;
643 			CLOSE csr_tax_mun_id ;
644 
645 			SELECT pay_assignment_actions_s.NEXTVAL INTO l_actid FROM   dual;
646 
647 			  -- Create the archive assignment action
648 			  hr_nonrun_asact.insact(l_actid ,rec_asg.asg_id ,p_payroll_action_id ,p_chunk ,NULL);
649 
650 
651 			  -- Creating Initial Archive Entries
652 			  pay_action_information_api.create_action_information (
653 
654 				 p_action_information_id        => l_action_info_id		-- out parameter
655 				,p_object_version_number        => l_ovn			-- out parameter
656 				,p_action_context_id            => l_actid        		-- context id = assignment action id (of Archive)
657 				,p_action_context_type          => 'AAP'			-- context type
658 				,p_effective_date               => l_effective_date		-- Date of running the archive
659 				,p_assignment_id		=> rec_asg.asg_id		-- Assignment ID
660 				,p_action_information_category  => 'EMEA REPORT INFORMATION'	-- Information Category
661 				,p_tax_unit_id                  => l_leg_emp_id			-- Legal Employer ID
662 				,p_jurisdiction_code            => l_tax_mun_id			-- Tax Municipality ID
663 				,p_action_information1          => 'PYNOSUPORDA'		-- Conc Prg Short Name
664 				,p_action_information2          => rec_asg.local_unit_id	-- Local Unit ID
665 				,p_action_information3          => p_payroll_action_id		-- payroll_action_id (of this archive)
666 				,p_action_information4          => rec_asg.asg_act_id		-- Original / Main Asg Action ID
667 
668 				);
669 
670 
671 		END LOOP; -- rec_asg
672 
673 	 END IF; -- l_archive = 'Y'
674 
675  IF g_debug THEN
676       hr_utility.set_location(' Leaving Procedure ASSIGNMENT_ACTION_CODE',70);
677  END IF;
678  END ASSIGNMENT_ACTION_CODE;
679 
680 
681 -------------------------------------------------------------------------------------------------------------------------
682  /* ARCHIVE CODE */
683  PROCEDURE ARCHIVE_CODE(p_assignment_action_id IN NUMBER
684  		      ,p_effective_date    IN DATE)
685  IS
686 
687 
688 
689 -- Cursor to get the action_information_id and original/main assignment_action_id
690 
691 cursor csr_get_act_info (p_assignment_action_id  NUMBER , p_effective_date DATE , p_leg_emp_id NUMBER) is
692 
693 	select to_number(ACTION_INFORMATION_ID) action_info_id
694 	      ,to_number(ACTION_INFORMATION4)	main_asg_act_id
695 	      ,to_number(ACTION_INFORMATION2)  local_unit_id
696 	      ,jurisdiction_code
697 	from pay_action_information
698 	where ACTION_INFORMATION_CATEGORY = 'EMEA REPORT INFORMATION'
699 	and   ACTION_INFORMATION1 = 'PYNOSUPORDA'
700 	and   ACTION_CONTEXT_TYPE = 'AAP'
701 	and   ACTION_CONTEXT_ID = p_assignment_action_id
702 	and   EFFECTIVE_DATE = p_effective_date
703 	and   TAX_UNIT_ID = p_leg_emp_id ;
704 
705 
706 
707 -- cursor to get the element details from information archived in EMEA REPORT DETAILS
708 
709 cursor csr_get_ele_info (p_payroll_action_id  NUMBER , p_effective_date DATE, p_leg_emp_id NUMBER) is
710 
711 	select to_number(ACTION_INFORMATION10)	ele_type_id       -- Elemeny Type ID
712 	      ,to_number(ACTION_INFORMATION13)	ipv_third_party	  -- Input Value ID 1 = Third Party Payee
713 	      ,to_number(ACTION_INFORMATION14)	ipv_amount	  -- Input Value ID 2 = Amount
714 	      ,to_number(ACTION_INFORMATION15)	ipv_percent	  -- Input Value ID 3 = Percentage
715 	      ,to_number(ACTION_INFORMATION16)	ipv_ref_num	  -- Input Value ID 4 = Reference Number
716 	      ,to_number(ACTION_INFORMATION17)	ipv_pay_value	  -- Input Value ID 5 = Pay Value
717 	      ,to_number(ACTION_INFORMATION18)	def_bal_id	  -- Deduction Basis Balance : Defined Balance ID
718 
719 	from pay_action_information
720 
721 	where ACTION_INFORMATION_CATEGORY = 'EMEA REPORT DETAILS'
722 	and   ACTION_INFORMATION1 = 'PYNOSUPORDA'
723 	and   ACTION_CONTEXT_TYPE = 'PA'
724 	and   ACTION_CONTEXT_ID = p_payroll_action_id
725 	and   EFFECTIVE_DATE = p_effective_date
726 	and   TAX_UNIT_ID = p_leg_emp_id ;
727 
728 
729 -- cursor to get the assignment,person details and run values
730 
731 CURSOR csr_asg_details
732 	(p_assignment_action_id	NUMBER
733 	,p_ele_typ_id		NUMBER
734 	,p_ipvid_1		NUMBER
735 	,p_ipvid_2		NUMBER 	) IS
736 	SELECT
737 	 per.last_name			per_last_name
738 	,per.first_name			per_first_name
739 	,per.ORDER_NAME			per_order_name
740 	,per.PERSON_ID			per_id
741 	,per.NATIONAL_IDENTIFIER	per_ni
742 	,per.EMPLOYEE_NUMBER		emp_no
743 	,per.DATE_OF_BIRTH		per_dob
744 	,per.ORIGINAL_DATE_OF_HIRE	per_doh
745 	,per.TITLE			per_title
746 	,per.business_group_id		bg_id
747 	,prrv1.result_value		res_val_1 -- Third Party Payee
748 	,prrv2.result_value		res_val_2 -- Pay Value
749 	,prr.RUN_RESULT_ID		run_res_id
750 	,ppa.payroll_id			payroll_id  --payroll_id to set the context
751 	,ppa.date_earned		date_earned --date_earned to set the context
752 	,prr.source_id			original_entry_id --source_id to set the context
753 
754 	FROM
755 	pay_assignment_actions	assact
756 	,pay_payroll_actions	ppa
757 	,per_all_assignments_f	asg
758 	,per_all_people_f	per
759 	,pay_run_results	prr
760 	,pay_run_result_values	prrv1
761 	,pay_run_result_values	prrv2
762 
763 	WHERE assact.ASSIGNMENT_ACTION_ID = p_assignment_action_id
764 	AND assact.ASSIGNMENT_ID = asg.ASSIGNMENT_ID
765 	AND ppa.date_earned between asg.EFFECTIVE_START_DATE and asg.EFFECTIVE_END_DATE
766 	AND asg.PERSON_ID = per.PERSON_ID
767 	AND ppa.date_earned between per.EFFECTIVE_START_DATE and per.EFFECTIVE_END_DATE
768 	AND assact.PAYROLL_ACTION_ID = ppa.PAYROLL_ACTION_ID
769 	AND assact.ASSIGNMENT_ACTION_ID = prr.ASSIGNMENT_ACTION_ID
770 
771 	-- for element
772 	AND prr.ELEMENT_TYPE_ID = p_ele_typ_id
773 
774 	-- for input value 'Third Party Payee'
775 	AND prr.RUN_RESULT_ID = prrv1.RUN_RESULT_ID
776 	AND prrv1.INPUT_VALUE_ID = p_ipvid_1
777 
778 	-- for input value 'Pay Value'
779 	AND prr.RUN_RESULT_ID = prrv2.RUN_RESULT_ID
780 	AND prrv2.INPUT_VALUE_ID = p_ipvid_2 ;
781 
782 
783 -- cursor to get the assignment,person details and run values
784 
785 CURSOR csr_result_values ( p_run_res_id NUMBER , p_inp_val_id NUMBER ) IS
786 	SELECT result_value
787 	FROM pay_run_result_values
788 	WHERE RUN_RESULT_ID = p_run_res_id
789 	AND INPUT_VALUE_ID = p_inp_val_id ;
790 
791 
792 
793 -- cursor to get Third Party Destination Acc Number
794 
795 cursor csr_third_party_dest_acc (p_organization_id	NUMBER) is
796 	select  segment6
797 	from pay_external_accounts	acc
798 	,pay_org_payment_methods_f	pay_org
799 	,hr_organization_information	hoi
800 	where hoi.organization_id = p_organization_id
801 	and hoi.org_information_context = 'NO_THIRD_PARTY_PAYMENT'
802 	and pay_org.org_payment_method_id = hoi.org_information2
803 	and pay_org.pmeth_information1 = 'DESTINATION'
804 	and acc.external_account_id = pay_org.external_account_id;
805 
806 
807 
808 -- cursor to get the third party loaction
809 
810 cursor csr_third_party_loc (p_organization_id	NUMBER) is
811 	select loc.address_line_1	line_1
812 	,loc.address_line_2		line_2
813 	,loc.address_line_3		line_3
814 	,hr_general.decode_fnd_comm_lookup('NO_POSTAL_CODE',loc.postal_code) post_code
815 	from hr_locations_all		loc
816 	,hr_all_organization_units	hou
817 	where hou.organization_id = p_organization_id
818 	and loc.location_id = hou.location_id
819 	and loc.style = 'NO';
820 
821 
822 
823 
824 -- Variable declaration
825 
826 l_action_info_id	NUMBER;
827 l_ovn			NUMBER;
828 l_main_asg_act_id	NUMBER;
829 
830 rec_asg_detail		csr_asg_details%rowtype;
831 rec_loc_detail		csr_third_party_loc%rowtype;
832 
833 l_count			NUMBER := 0;
834 l_archive		VARCHAR2(3);
835 l_business_id		NUMBER;
836 l_bimonth_term		VARCHAR2(10);
837 l_leg_emp_id		NUMBER;
838 l_effective_date	DATE;
839 
840 l_third_party_id	NUMBER;
841 l_third_party_name	VARCHAR2(240);
842 l_third_party_dest_acc	VARCHAR2(150);
843 l_dedn_basis		NUMBER;
844 l_payroll_action_id	NUMBER;
845 l_from_date		DATE;
846 l_to_date		DATE;
847 rec_info		csr_get_act_info%rowtype;
848 ele_info		csr_get_ele_info%rowtype;
849 l_emp_ni		VARCHAR2(240);
850 l_ele_type_id		NUMBER;
851 ipv_third_party	NUMBER;
852 ipv_amount	NUMBER;
853 ipv_percent	NUMBER;
854 ipv_ref_num	NUMBER;
855 ipv_pay_value	NUMBER;
856 l_def_bal_id	NUMBER;
857 l_amt		NUMBER;
858 l_percent	NUMBER;
859 l_ref_num	VARCHAR2(240);
860 
861 
862  BEGIN
863 IF g_debug THEN
864  		hr_utility.set_location(' Entering Procedure ARCHIVE_CODE',380);
865  END IF;
866 
867 	SELECT payroll_action_id
868 	INTO l_payroll_action_id
869 	FROM pay_assignment_actions
870 	WHERE assignment_action_id = p_assignment_action_id ;
871 
872 
873        -- fetch the data
874 	 PAY_NO_SUPPORT_ORDER.GET_ALL_PARAMETERS(
875 			l_payroll_action_id
876 			,l_business_id
877 			,l_leg_emp_id
878 			,l_ele_type_id
879 			,l_effective_date
880 			,l_from_date
881 			,l_to_date
882 			,l_third_party_id
883 			,l_archive ) ;
884 
885 
886    	 -- check if we have to archive again
887 	 IF  (l_archive = 'Y')
888 
889 	   THEN
890 
891 
892 		-- get the action_information_id and original/main assignment_action_id for this asg_act_id
893 		OPEN csr_get_act_info (p_assignment_action_id  , p_effective_date , l_leg_emp_id );
894 		FETCH csr_get_act_info  INTO rec_info;
895 		CLOSE csr_get_act_info ;
896 
897 		--fnd_file.put_line(fnd_file.log,'SUGARG: after csr_get_act_info');
898 
899 		--  get the element details from information archived in EMEA REPORT DETAILS
900 		OPEN csr_get_ele_info (l_payroll_action_id , p_effective_date , l_leg_emp_id) ;
901 		FETCH csr_get_ele_info  INTO ele_info;
902 		CLOSE csr_get_ele_info ;
903 
904 		--fnd_file.put_line(fnd_file.log,'SUGARG: after csr_get_ele_info');
905 
906 
907 
908 		-- fetch assignment details
909 		OPEN csr_asg_details
910 			(rec_info.main_asg_act_id
911 			 ,ele_info.ele_type_id
912 			 ,ele_info.ipv_third_party
913 			 --,ele_info.ipv_amount
914 			 --,ele_info.ipv_percent
915 			 --,ele_info.ipv_ref_num
916 			 ,ele_info.ipv_pay_value  ) ;
917 
918 		FETCH csr_asg_details INTO rec_asg_detail ;
919 		CLOSE csr_asg_details ;
920 
921 
922 		-- get the result values 'Amount'
923 		OPEN csr_result_values ( rec_asg_detail.run_res_id , ele_info.ipv_amount ) ;
924 		FETCH csr_result_values INTO l_amt ;
925 		CLOSE csr_result_values ;
926 
927 		-- get the result values 'Percentage'
928 		OPEN csr_result_values ( rec_asg_detail.run_res_id , ele_info.ipv_percent  ) ;
929 		FETCH csr_result_values INTO l_percent ;
930 		CLOSE csr_result_values ;
931 
932 		-- get the result values 'Reference Number'
933 		OPEN csr_result_values ( rec_asg_detail.run_res_id , ele_info.ipv_ref_num  ) ;
934 		FETCH csr_result_values INTO l_ref_num ;
935 		CLOSE csr_result_values ;
936 
937 
938 
939 		l_third_party_id := to_number(rec_asg_detail.res_val_1) ;
940 
941 		--fnd_file.put_line(fnd_file.log,'SUGARG: after l_third_party_id' || to_char(l_third_party_id));
942 
943 		-- to get the Third Party Name
944 		select name into l_third_party_name  from hr_organization_units where organization_id = l_third_party_id ;
945 
946 
947 		-- to get the third party Destination acc no.
948 		OPEN csr_third_party_dest_acc (l_third_party_id) ;
949 		FETCH csr_third_party_dest_acc INTO l_third_party_dest_acc ;
950 		CLOSE csr_third_party_dest_acc ;
951 
952 
953 		-- to get the third party location details
954 		OPEN csr_third_party_loc (l_third_party_id) ;
955 		FETCH csr_third_party_loc INTO rec_loc_detail;
956 		CLOSE csr_third_party_loc ;
957 
958 		pay_balance_pkg.set_context('TAX_UNIT_ID', l_leg_emp_id);
959 		pay_balance_pkg.set_context('JURISDICTION_CODE', rec_info.jurisdiction_code);
960 		pay_balance_pkg.set_context('DATE_EARNED', to_char(rec_asg_detail.date_earned, 'YYYY/MM/DD HH24:MI:SS'));
961 		pay_balance_pkg.set_context('LOCAL_UNIT_ID',rec_info.local_unit_id);
962 		pay_balance_pkg.set_context('PAYROLL_ID', rec_asg_detail.payroll_id);
963 		pay_balance_pkg.set_context('ORGANIZATION_ID', l_third_party_id);
964 		pay_balance_pkg.set_context('ORIGINAL_ENTRY_ID', rec_asg_detail.original_entry_id);
965 
966 
967 
968 
969 		-- to fetch the balance value of DEDUCTION BASIS balance
970 		select pay_balance_pkg.get_value(ele_info.def_bal_id , rec_info.main_asg_act_id)
971 		into l_dedn_basis
972 		from dual;
973 
974 
975 		l_third_party_dest_acc := substr(l_third_party_dest_acc,1,4)||'.'||
976 		                          substr(l_third_party_dest_acc,5,2)||'.'||
977 					  substr(l_third_party_dest_acc,7,5) ;
978 
979 
980 		l_emp_ni := substr(rec_asg_detail.per_ni,1,6)||'-'||
981 		            substr(rec_asg_detail.per_ni,7,5) ;
982 
983 		  -- Updating the Initial Archive Entries
984 			pay_action_information_api.update_action_information (
985 			 p_action_information_id        => rec_info.action_info_id 	-- in parameter
986 			,p_object_version_number        => l_ovn			-- in out parameter
987 			,p_action_information5          => l_third_party_id		--Third Party ID (Tax Collector ID)
988 			,p_action_information6          => l_third_party_name		--Third Party Name (Tax Collector's Name)
989 			,p_action_information7          => rec_loc_detail.line_1	--Third Party Address Line 1
990 			,p_action_information8          => rec_loc_detail.line_2	--Third Party Address Line 2
991 			,p_action_information9          => rec_loc_detail.line_3	--Third Party Address Line 3
992 			,p_action_information10         => rec_loc_detail.post_code	--Third Party Postal Code + City
993 			,p_action_information11         => l_third_party_dest_acc	--Third Party Destination Bank Account Number (Formatted)
994 			,p_action_information12         => rec_asg_detail.bg_id		--Business Group ID
995 			,p_action_information13         => rec_asg_detail.per_id	--PERSON_ID
996 			,p_action_information14         => rec_asg_detail.per_ni	--Person NATIONAL_IDENTIFIER
997 			,p_action_information15         => rec_asg_detail.per_last_name	--Person Lastname
998 			,p_action_information16         => rec_asg_detail.per_first_name --Person FirstName
999 			,p_action_information17         => rec_asg_detail.per_order_name --Person Order Name
1000 			,p_action_information18         => rec_asg_detail.emp_no 	--Person Employee Number
1001 			,p_action_information19         => rec_asg_detail.per_dob 	--Person Date of Birth - DOB
1002 			,p_action_information20         => rec_asg_detail.per_doh	--Person Date of Hire - DOH
1003 			,p_action_information21         => rec_asg_detail.per_title	--Person Title
1004 			,p_action_information22         => l_ref_num			--Reference Number
1005 			,p_action_information23         => l_percent			--Percentage (Input Value)
1006 			,p_action_information24         => l_amt			--Amount (Input Value)
1007 			,p_action_information25         => rec_asg_detail.res_val_2	--Deducted This Period (Input Value)
1008 			,p_action_information26         => l_dedn_basis			--Deduction Basis (Balance Value)
1009 
1010 			);
1011 
1012 
1013 	 END IF; -- l_archive = 'Y'
1014 
1015 	--fnd_file.put_line(fnd_file.log,'SUGARG: leaving ARCHIVE_CODE');
1016  IF g_debug THEN
1017  		hr_utility.set_location(' Leaving Procedure ARCHIVE_CODE',390);
1018  END IF;
1019  END ARCHIVE_CODE;
1020 
1021 -------------------------------------------------------------------------------------------------------------------------
1022 
1023 
1024 
1025 
1026 
1027 -- PROCEDURE for writing the xml report
1028 
1029 PROCEDURE populate_details(p_payroll_action_id in NUMBER,
1030 		  	   p_template_name in VARCHAR2,
1031 			   p_xml out nocopy CLOB) is
1032 
1033 ------------------------------------
1034 
1035 -- cursor to get Legal Employer and other details for the report header
1036 
1037 cursor csr_legalemployer (p_payroll_action_id	NUMBER) is
1038 	select action_information3 employer
1039 	,action_information4 orgnumber
1040 	,action_information5 from_date
1041 	,action_information6 to_date
1042 	,action_information11 ele_name
1043 	,action_information12 ele_code
1044 	,action_information20 archived_on
1045 	from pay_action_information
1046 	where action_context_id = p_payroll_action_id ;
1047 
1048 
1049 -- cursor to get assignment and person details
1050 
1051 cursor csr_emp (p_payroll_action_id	NUMBER) is
1052 	select action_information6 thirdparty_name
1053 	,action_information5 thirdparty_id
1054 	,', '||action_information7||', '
1055 	     ||action_information8||', '
1056 	     ||action_information9||', '
1057 	     ||action_information10 thirdparty_address
1058 	,action_information11 bankaccountno
1059 	,action_information14 emp_ni
1060 	,action_information15 last_name
1061 	,action_information16 first_name
1062 	,action_information17 order_name
1063 	,action_information18 emp_no
1064 	,action_information19 emp_dob
1065 	,action_information20 emp_doh
1066 	,action_information21 emp_title
1067 	,action_information22 refno
1068 	,action_information23 percentage
1069 	,action_information24 amount
1070 	,action_information25 deductedthisperiod
1071 	,action_information26 deductionBasis
1072 	from pay_action_information pai
1073 	where action_information3 = to_char(p_payroll_action_id)
1074 	order by thirdparty_name , order_name;
1075 
1076 
1077 xml_ctr			NUMBER;
1078 l_payroll_action_id	NUMBER;
1079 l_employer		VARCHAR2(240);
1080 l_orgnumber		VARCHAR2(240);
1081 l_from_date		VARCHAR2(240);
1082 l_to_date		VARCHAR2(240);
1083 l_ele_name		varchar2(240);
1084 l_ele_code		varchar2(240);
1085 l_archived_on		varchar2(240);
1086 l_thirdparty_id		pay_action_information.ACTION_INFORMATION5%TYPE := '-999';
1087 l_IANA_charset VARCHAR2 (50);
1088 
1089 BEGIN
1090 
1091 
1092 xml_ctr := 0;
1093 /*pgopal - picking the charset dynamically from the db*/
1094 l_IANA_charset := HR_NO_UTILITY.get_IANA_charset ;
1095 --xml_tab(xml_ctr).xmlstring := '<?xml version="1.0" encoding="utf-8"?>';
1096 xml_tab(xml_ctr).xmlstring := '<?xml version = "1.0" encoding = "'||l_IANA_charset||'"?>';
1097 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '<START>';
1098 xml_ctr := xml_ctr +1;
1099 
1100 -- getting the pay_action_id if the current archive if no archive has been mentioned
1101 
1102 
1103 IF p_payroll_action_id  IS NULL THEN
1104 
1105 	BEGIN
1106 
1107 		--fnd_file.put_line(fnd_file.log,'SUGARG: p_payroll_action_id  IS NULL ');
1108 		SELECT payroll_action_id
1109 		INTO  l_payroll_action_id
1110 		FROM pay_payroll_actions ppa,
1111 		     fnd_conc_req_summary_v fcrs,
1112 		     fnd_conc_req_summary_v fcrs1
1113 		WHERE  fcrs.request_id = FND_GLOBAL.CONC_REQUEST_ID
1114 		AND fcrs.priority_request_id = fcrs1.priority_request_id
1115 		AND ppa.request_id between fcrs1.request_id  and fcrs.request_id
1116 		AND ppa.request_id = fcrs1.request_id;
1117 
1118 		--fnd_file.put_line(fnd_file.log,'SUGARG: inside IF -> l_payroll_action_id = '||l_payroll_action_id);
1119 
1120 	EXCEPTION
1121 	WHEN others THEN
1122 	NULL;
1123 	END ;
1124 ELSE
1125 	l_payroll_action_id := p_payroll_action_id ;
1126 
1127 END IF;
1128 
1129 
1130 -- Get Legal Employer and other details for the report header
1131 OPEN csr_legalemployer (l_payroll_action_id) ;
1132 FETCH csr_legalemployer INTO l_employer,l_orgnumber,l_from_date,l_to_date , l_ele_name , l_ele_code , l_archived_on;
1133 CLOSE csr_legalemployer;
1134 
1135 
1136 FOR emp_rec IN csr_emp (l_payroll_action_id) LOOP
1137 
1138 
1139 IF (l_thirdparty_id <> emp_rec.thirdparty_id) then
1140    if csr_emp%rowcount = 1 then
1141      xml_tab(xml_ctr).xmlstring := '<LEGALEMPLOYER_RECORD>';
1142      xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '<LegalEmployer>'||l_employer||'</LegalEmployer>';
1143      xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<OrgNumber>'||l_orgnumber||'</OrgNumber>';
1144      xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<FromPeriod>'||l_from_date||'</FromPeriod>';
1145      xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<ToPeriod>'||l_to_date||'</ToPeriod>';
1146      xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<ElementName>'|| l_ele_name ||'</ElementName>';
1147      xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<ElementCode>'|| l_ele_code ||'</ElementCode>';
1148      xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<ArchivedOn>'||  l_archived_on ||'</ArchivedOn>';
1149      xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '</LEGALEMPLOYER_RECORD>';
1150      xml_ctr := xml_ctr +1;
1151      xml_tab(xml_ctr).xmlstring := '<THIRDPARTY_RECORD>';
1152    else
1153      xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '</THIRDPARTY_RECORD>';
1154      --xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '</LEGALEMPLOYER_RECORD>';
1155      xml_ctr := xml_ctr + 1;
1156      xml_tab(xml_ctr).xmlstring := '<THIRDPARTY_RECORD>';
1157    end if ;
1158 
1159    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<ThirdPartyName>'||emp_rec.thirdparty_name||'</ThirdPartyName>';
1160    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<ThirdPartyAddress>'||emp_rec.thirdparty_address||'</ThirdPartyAddress>';
1161    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<BankAccNumber>'||emp_rec.bankaccountno||'</BankAccNumber>';
1162 
1163    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EMP_RECORD>';
1164 
1165    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<NI-Number>'|| emp_rec.emp_ni ||'</NI-Number>';
1166    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeName>'|| emp_rec.order_name ||'</EmployeeName>';
1167 
1168    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeLastName>'|| emp_rec.last_name ||'</EmployeeLastName>';
1169    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeFirstName>'|| emp_rec.first_name ||'</EmployeeFirstName>';
1170    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeNumber>'|| emp_rec.emp_no ||'</EmployeeNumber>';
1171    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeDOB>'|| emp_rec.emp_dob ||'</EmployeeDOB>';
1172    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeDOH>'|| emp_rec.emp_doh ||'</EmployeeDOH>';
1173    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeTitle>'|| emp_rec.emp_title ||'</EmployeeTitle>';
1174 
1175 
1176 
1177    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<ReferenceNumber>'||emp_rec.refno||'</ReferenceNumber>';
1178    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<DeductionBasis>'||emp_rec.deductionBasis||'</DeductionBasis>';
1179    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Percentage>'||emp_rec.percentage||'</Percentage>';
1180    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Amount>'||emp_rec.amount||'</Amount>';
1181    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<DeductedThisPeriod>'||emp_rec.deductedthisperiod||'</DeductedThisPeriod>';
1182    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring||'</EMP_RECORD>';
1183 
1184    l_thirdparty_id := emp_rec.thirdparty_id;
1185 
1186 ELSE
1187 
1188    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EMP_RECORD>';
1189 
1190    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<NI-Number>'|| emp_rec.emp_ni ||'</NI-Number>';
1191    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeName>'|| emp_rec.order_name ||'</EmployeeName>';
1192 
1193    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeLastName>'|| emp_rec.last_name ||'</EmployeeLastName>';
1194    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeFirstName>'|| emp_rec.first_name ||'</EmployeeFirstName>';
1195    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeNumber>'|| emp_rec.emp_no ||'</EmployeeNumber>';
1196    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeDOB>'|| emp_rec.emp_dob ||'</EmployeeDOB>';
1197    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeDOH>'|| emp_rec.emp_doh ||'</EmployeeDOH>';
1198    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeTitle>'|| emp_rec.emp_title ||'</EmployeeTitle>';
1199 
1200    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<ReferenceNumber>'||emp_rec.refno||'</ReferenceNumber>';
1201    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<DeductionBasis>'||emp_rec.deductionBasis||'</DeductionBasis>';
1202    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Percentage>'||emp_rec.percentage||'</Percentage>';
1203    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Amount>'||emp_rec.amount||'</Amount>';
1204    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<DeductedThisPeriod>'||emp_rec.deductedthisperiod||'</DeductedThisPeriod>';
1205    xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring||'</EMP_RECORD>';
1206 
1207    l_thirdparty_id := emp_rec.thirdparty_id;
1208 
1209 END IF ;
1210 
1211 END LOOP;
1212 
1213 --hr_utility.trace('SUP_ORD: after FOR loop ');
1214 
1215 IF (xml_tab(xml_ctr).xmlstring is null)
1216 	THEN  raise no_data_found;
1217 ELSE
1218 	xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring||'</THIRDPARTY_RECORD>';
1219 	xml_ctr := xml_ctr +1;
1220 	xml_tab(xml_ctr).xmlstring := '</START>';
1221 END IF;
1222 
1223 write_to_clob(p_xml);
1224 
1225 exception when no_data_found then
1226 	 xml_tab(xml_ctr).xmlstring := '<LEGALEMPLOYER_RECORD>';
1227 	 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '<LegalEmployer>'|| '</LegalEmployer>';
1228 	 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<OrgNumber>'|| '</OrgNumber>';
1229 	 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<FromPeriod>'|| '</FromPeriod>';
1230 	 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<ToPeriod>'|| '</ToPeriod>';
1231 	 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<ElementName>'|| '</ElementName>';
1232          xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<ElementCode>'|| '</ElementCode>';
1233          xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<ArchivedOn>'|| '</ArchivedOn>';
1234 	 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring || '</LEGALEMPLOYER_RECORD>';
1235 
1236 	 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<THIRDPARTY_RECORD>';
1237 	 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<ThirdPartyName>'||'</ThirdPartyName>';
1238 	 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<ThirdPartyAddress>'||'</ThirdPartyAddress>';
1239 	 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<BankAccNumber>'||'</BankAccNumber>';
1240 
1241 	 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EMP_RECORD>';
1242 	 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<NI-Number>'||'</NI-Number>';
1243 	 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeName>'||'</EmployeeName>';
1244 
1245          xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeLastName>'|| '</EmployeeLastName>';
1246 	 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeFirstName>'|| '</EmployeeFirstName>';
1247 	 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeNumber>'|| '</EmployeeNumber>';
1248 	 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeDOB>'|| '</EmployeeDOB>';
1249 	 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeDOH>'|| '</EmployeeDOH>';
1250 	 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<EmployeeTitle>'|| '</EmployeeTitle>';
1251 
1252 	 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<ReferenceNumber>'||'</ReferenceNumber>';
1253 	 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<DeductionBasis>'||'</DeductionBasis>';
1254 	 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Percentage>'||'</Percentage>';
1255 	 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<Amount>'||'</Amount>';
1256 	 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'<DeductedThisPeriod>'||'</DeductedThisPeriod>';
1257 	 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring||'</EMP_RECORD>';
1258 
1259 	 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'</THIRDPARTY_RECORD>';
1260 	 xml_tab(xml_ctr).xmlstring := xml_tab(xml_ctr).xmlstring ||'</START>';
1261 
1262          write_to_clob(p_xml);
1263 
1264 
1265 END populate_details;
1266 
1267 
1268 
1269 
1270 ------------------------------------------------------------------------------------------------------------
1271 
1272 -- PROCEDURE for writing the xml to clob
1273 
1274 PROCEDURE write_to_clob (p_xml out nocopy clob) is
1275 
1276 l_xfdf_string clob;
1277 
1278 BEGIN
1279 
1280 	dbms_lob.createtemporary(p_xml,false,dbms_lob.call);
1281 	dbms_lob.open(p_xml,dbms_lob.lob_readwrite);
1282 
1283 	if xml_tab.count > 0 then
1284 	  for ctr_table in xml_tab.first .. xml_tab.last loop
1285 		dbms_lob.writeappend( p_xml, length(xml_tab(ctr_table).xmlstring), xml_tab(ctr_table).xmlstring );
1286 	  end loop;
1287 	end if;
1288 
1289 	--dbms_lob.createtemporary(p_xml,true);
1290 	--clob_to_blob(l_xfdf_string,p_xml);
1291 
1292 	exception
1293 		when others then
1294 		hr_utility.trace('sqleerm ' || sqlerrm);
1295 		hr_utility.raise_error;
1296 
1297 END write_to_clob;
1298 
1299 
1300 -------------------------------------------------------------------------------------------------------------------------
1301  END PAY_NO_SUPPORT_ORDER;