[Home] [Help]
PACKAGE BODY: APPS.PAY_NO_PACCR_PKG
Source
1 PACKAGE BODY PAY_NO_PACCR_PKG AS
2 /* $Header: pynopaccr.pkb 120.5.12000000.3 2007/07/05 10:07:34 kseth noship $ */
3
4 --Global parameters
5 g_package CONSTANT varchar2(33) := 'PAY_NO_PACCR_PKG.';
6 g_debug BOOLEAN := hr_utility.debug_enabled;
7 g_err_num NUMBER;
8
9
10
11 -----------------------------------------------------------------------------
12 -- GET_PARAMETER used in SQL to decode legislative parameters
13 -----------------------------------------------------------------------------
17 ,p_segment_number IN NUMBER default NULL ) RETURN VARCHAR2
14 FUNCTION GET_PARAMETER(
15 p_parameter_string IN VARCHAR2
16 ,p_token IN VARCHAR2
18 IS
19 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
20 l_start_pos NUMBER;
21 l_delimiter VARCHAR2(1):=' ';
22
23 BEGIN
24 IF g_debug THEN
25 hr_utility.set_location(' Entering Function GET_PARAMETER',10);
26 END IF;
27
28 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
29
30 IF l_start_pos = 0 THEN
31 l_delimiter := '|';
32 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
33 END IF;
34
35 IF l_start_pos <> 0 THEN
36 l_start_pos := l_start_pos + length(p_token||'=');
37 l_parameter := substr(p_parameter_string, l_start_pos,
38 instr(p_parameter_string||' ', l_delimiter,l_start_pos) - l_start_pos);
39
40 IF p_segment_number IS NOT NULL THEN
41 l_parameter := ':'||l_parameter||':';
42 l_parameter := substr(l_parameter,
43 instr(l_parameter,':',1,p_segment_number)+1,
44 instr(l_parameter,':',1,p_segment_number+1) -1
45 - instr(l_parameter,':',1,p_segment_number));
46 END IF;
47 END IF;
48
49 RETURN l_parameter;
50 IF g_debug THEN
51 hr_utility.set_location(' Leaving Function GET_PARAMETER',20);
52 END IF;
53
54 END GET_PARAMETER;
55
56 --------------------------------------------------------------------------------------
57 -- GET_ALL_PARAMETERS used in SQL to cumulatively decode all legislative parameters
58 --------------------------------------------------------------------------------------
59 PROCEDURE GET_ALL_PARAMETERS
60 (p_payroll_action_id IN NUMBER
61 ,p_business_group_id OUT NOCOPY NUMBER
62 ,p_payroll_id OUT NOCOPY NUMBER
63 ,p_le_id OUT NOCOPY NUMBER
64 ,p_ele_id OUT NOCOPY NUMBER
65 ,p_restr_econtr OUT NOCOPY VARCHAR2
66 ,p_eoy_code OUT NOCOPY VARCHAR2
67 ,p_cost_seg OUT NOCOPY VARCHAR2
68 ,p_effective_date OUT NOCOPY DATE
69 ,p_report_start_date OUT NOCOPY DATE
70 ,p_report_end_date OUT NOCOPY DATE
71 ,p_archive OUT NOCOPY VARCHAR2)
72 IS
73
74
75 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
76 SELECT
77 PAY_NO_PACCR_PKG.GET_PARAMETER(legislative_parameters,'PAYROLL')
78 ,PAY_NO_PACCR_PKG.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER')
79 ,PAY_NO_PACCR_PKG.GET_PARAMETER(legislative_parameters,'RESTRICT_EMPLR_CONTR')
80 ,PAY_NO_PACCR_PKG.GET_PARAMETER(legislative_parameters,'ELEMENT_NAME')
81 ,PAY_NO_PACCR_PKG.GET_PARAMETER(legislative_parameters,'EOY_CODE')
82 ,PAY_NO_PACCR_PKG.GET_PARAMETER(legislative_parameters,'COSTING_SEG')
83 ,effective_date
84 ,fnd_date.canonical_to_date(PAY_NO_PACCR_PKG.GET_PARAMETER(legislative_parameters,'REPORT_START_DATE'))
85 ,fnd_date.canonical_to_date(PAY_NO_PACCR_PKG.GET_PARAMETER(legislative_parameters,'REPORT_END_DATE'))
86 ,PAY_NO_PACCR_PKG.GET_PARAMETER(legislative_parameters,'ARCHIVE')
87 ,business_group_id
88 FROM pay_payroll_actions
89 WHERE payroll_action_id = p_payroll_action_id;
90
91 l_proc VARCHAR2(240):= g_package||'.GET_ALL_PARAMETERS ';
92 --
93 BEGIN
94
95 IF g_debug THEN
96 hr_utility.set_location(' Entering procedure '||l_proc,10);
97 END IF;
98
99 OPEN csr_parameter_info (p_payroll_action_id);
100
101 FETCH csr_parameter_info
102 INTO p_payroll_id
103 ,p_le_id
104 ,p_restr_econtr
105 ,p_ele_id
106 ,p_eoy_code
107 ,p_cost_seg
108 ,p_effective_date
109 ,p_report_start_date
110 ,p_report_end_date
111 ,p_archive
112 ,p_business_group_id;
113 CLOSE csr_parameter_info;
114 --
115 IF g_debug THEN
116 hr_utility.set_location(' Leaving procedure '||l_proc,20);
117 END IF;
118 END GET_ALL_PARAMETERS;
119
120 --------------------------------------------------------------------------------------
121 -- GET_ELEMENT_CODE to get the element code of an element in a particular year
122 --------------------------------------------------------------------------------------
123
124 FUNCTION GET_ELE_CODE(
125 l_element_type_id IN NUMBER
126 ,l_effective_date IN DATE) RETURN VARCHAR2
127 IS
128 CURSOR get_code IS
129 SELECT etei.eei_information3
130 FROM pay_element_type_extra_info etei
131 WHERE etei.element_type_id = l_element_type_id
132 AND etei.eei_information_category = 'NO_EOY_REPORTING_CODE_MAPPING'
133 AND to_number(to_char(l_effective_date,'YYYY'))
134 between to_number(etei.eei_information1) and to_number(nvl(etei.eei_information2,'4712'));
135 --
136 l_ele_code pay_element_type_extra_info.eei_information3%TYPE;
137 --
138 BEGIN
139 --
140 OPEN get_code;
141 FETCH get_code INTO l_ele_code;
142 CLOSE get_code;
143 --
144 RETURN l_ele_code;
145 --
146 END GET_ELE_CODE;
147
148 --------------------------------------------------------------------------------------
149 -- RANGE_CODE to specify ranges of assignments to be processed in the archive.
150 --------------------------------------------------------------------------------------
151
155
152 PROCEDURE RANGE_CODE (pactid IN NUMBER
153 ,sqlstr OUT NOCOPY VARCHAR2)
154 IS
156 -- Variable's declarations
157
158 l_count NUMBER := 0;
159 l_action_info_id NUMBER;
160 l_ovn NUMBER;
161 l_business_group_id NUMBER;
162 l_payroll_id NUMBER;
163
164 l_le_id NUMBER;
165 l_le_name VARCHAR(80);
166 l_le_org_no VARCHAR(80);
167
168 l_effective_date DATE;
169 l_report_end_date DATE;
170 l_report_start_date DATE;
171 l_archive VARCHAR2(80);
172
173 l_from_date VARCHAR2(80);
174 l_to_date VARCHAR2(80);
175
176 l_restr_econtr VARCHAR2(80);
177 l_ele_type_id NUMBER;
178 l_eoy_code VARCHAR2(80);
179 l_cost_seg VARCHAR2(80);
180 l_payroll_name VARCHAR2(80);
181 l_ele_name VARCHAR2(80);
182
183
184
185 /* Cursor to check if Current Archive exists */
186 CURSOR csr_count is
187 SELECT count(*)
188 FROM pay_action_information
189 WHERE action_information_category = 'EMEA REPORT DETAILS'
190 AND action_information1 = 'PYNOPACCA'
191 AND action_context_id = pactid;
192
193
194 /* Cursor to fetch the Legal Employer Details */
195 CURSOR csr_get_le_details(p_le_id NUMBER, p_effective_date DATE, p_business_group_id NUMBER) IS
196 SELECT hou.name NAME
197 , hoi.org_information1 ORGANIZATION_NO
198 FROM hr_organization_units hou
199 ,hr_organization_information hoi
200 WHERE hou.business_group_id = p_business_group_id
201 and hoi.organization_id = hou.organization_id
202 and hoi.org_information_context = 'NO_LEGAL_EMPLOYER_DETAILS'
203 and hou.organization_id = p_le_id
204 and p_effective_date BETWEEN hou.DATE_FROM and nvl(hou.DATE_TO, p_effective_date) ;
205
206
207 rec_le_details csr_get_le_details%ROWTYPE;
208
209 BEGIN
210
211 IF g_debug THEN
212 hr_utility.set_location(' Entering Procedure RANGE_CODE',10);
213 END IF;
214
215
216 -- The sql string to return
217 sqlstr := 'SELECT DISTINCT person_id
218 FROM per_people_f ppf
219 ,pay_payroll_actions ppa
220 WHERE ppa.payroll_action_id = :payroll_action_id
221 AND ppa.business_group_id = ppf.business_group_id
222 ORDER BY ppf.person_id';
223
224
225 -- Fetch the input parameter values
226 PAY_NO_PACCR_PKG.GET_ALL_PARAMETERS(
227 pactid
228 ,l_business_group_id
229 ,l_payroll_id
230 ,l_le_id
231 ,l_ele_type_id
232 ,l_restr_econtr
233 ,l_eoy_code
234 ,l_cost_seg
235 ,l_effective_date
236 ,l_report_start_date
237 ,l_report_end_date
238 ,l_archive);
239
240
241 /* To obtain Reporting From and Reporting To Dates from Span specified in parameters */
242
243 l_to_date := to_char(l_report_end_date,'YYYYMMDD');
244 l_from_date := to_char(l_report_start_date,'YYYYMMDD');
245
246
247 /* To fetch Legal Employer Details */
248 OPEN csr_get_le_details(l_le_id ,fnd_date.canonical_to_date(l_to_date),l_business_group_id);
249 FETCH csr_get_le_details INTO rec_le_details;
250 CLOSE csr_get_le_details;
251
252 l_le_name := rec_le_details.name;
253 l_le_org_no := rec_le_details.organization_no;
254
255 /*To fetch the Payroll Name */
256 BEGIN
257 SELECT ppf.payroll_name INTO l_payroll_name
258 FROM
259 pay_all_payrolls_f ppf
260 WHERE ppf.payroll_id=l_payroll_id;
261 EXCEPTION
262 WHEN OTHERS THEN
263 NULL;
264 END;
265
266 /*To fetch the Element Name */
267 -- Modified for bug fix 5239796
268 BEGIN
269 SELECT nvl(petf.reporting_name,petf.element_name) INTO l_ele_name
270 FROM
271 pay_element_types_f petf
272 WHERE petf.element_type_id=l_ele_type_id;
273 EXCEPTION
274 WHEN OTHERS THEN
275 NULL;
276 END;
277
278
279 /* Archive the Header Information */
280
281 -- Check if we have to archive again
282 IF (l_archive = 'Y') THEN
283 -- Check if record for current archive exists
284 OPEN csr_count;
285 FETCH csr_count INTO l_count;
286 CLOSE csr_count;
287
288 -- Archive Report Details only if no record exists
289 IF (l_count < 1) THEN
290
291 -- Archive the REPORT HEADER
292
293 pay_action_information_api.create_action_information
294 (p_action_information_id => l_action_info_id -- out parameter
295 ,p_object_version_number => l_ovn -- out parameter
296 ,p_action_context_id => pactid -- context id = payroll action id (of Archive)
297 ,p_action_context_type => 'PA' -- context type
298 ,p_effective_date => l_effective_date -- Date of Running the Archive
299 ,p_action_information_category => 'EMEA REPORT DETAILS' -- Information Category
300 ,p_tax_unit_id => l_le_id -- Legal Employer ID
301 ,p_jurisdiction_code => NULL -- Tax Municipality ID
302 ,p_action_information1 => 'PYNOPACCA' -- Conc Prg Short Name
303 ,p_action_information2 => l_business_group_id -- Business Group ID
304 ,p_action_information3 => l_payroll_id -- Payroll ID
305 ,p_action_information4 => 'HDR' -- Specifies data is for File Header
309 ,p_action_information8 => l_le_org_no -- LE's Organization Number
306 ,p_action_information5 => l_from_date -- Report's from date
307 ,p_action_information6 => l_to_date -- Report's to date
308 ,p_action_information7 => l_le_name -- LE's Name
310 ,p_action_information9 => l_cost_seg -- Parameter Costing Flexfield Segments
311 ,p_action_information10 => l_eoy_code -- Parameter End Of Year Code
312 ,p_action_information11 => l_ele_type_id -- Parameter Element Type ID
313 ,p_action_information12 => l_report_start_date -- Parameter Report Start Date
314 ,p_action_information13 => l_report_end_date -- Parameter Report End Date
315 ,p_action_information14 => l_restr_econtr -- Parameter Restrict to Employer Contribution
316 ,p_action_information15 => l_payroll_name -- Parameter Payroll's Name
317 ,p_action_information16 => l_ele_name -- Parameter Element's Name
318 );
319 END IF;
320 END IF;
321 --
322 IF g_debug THEN
323 hr_utility.set_location(' Leaving Procedure RANGE_CODE',20);
324 END IF;
325 --
326 END RANGE_CODE;
327 --------------------------------------------------------------------------------------
328 -- ASSIGNMENT_ACTION_CODE to create the assignment actions to be processed.
329 --------------------------------------------------------------------------------------
330 PROCEDURE ASSIGNMENT_ACTION_CODE
331 (p_payroll_action_id IN NUMBER
332 ,p_start_person IN NUMBER
333 ,p_end_person IN NUMBER
334 ,p_chunk IN NUMBER)
335 IS
336 /* Cursor to fetch useful header information to transfer to body records from already archived header information */
337 CURSOR csr_get_hdr_info(p_payroll_action_id NUMBER) IS
338 SELECT effective_date
339 ,fnd_date.CANONICAL_TO_DATE(action_information5) from_date
340 ,fnd_date.CANONICAL_TO_DATE(action_information6) to_date
341 ,to_number(action_information2) business_group_id
342 ,to_number(action_information3) payroll_id
343 ,action_information10 eoy_code
344 ,tax_unit_id
345 ,to_number(action_information11) ele_type_id
346 ,action_information14 restr_econtr
347 ,action_information9 cost_seg
348 FROM pay_action_information pai
349 WHERE action_context_type = 'PA'
350 AND action_context_id = p_payroll_action_id
351 AND action_information_category = 'EMEA REPORT DETAILS'
352 AND action_information1 = 'PYNOPACCA'
353 AND action_information4 = 'HDR';
354
355
356 /* Cursor to fetch local units for a Legal Employer */
357 CURSOR csr_get_lu_le (p_le_id NUMBER
358 ,p_bg_id NUMBER) IS
359 SELECT to_number(hoi.ORG_INFORMATION1) lu_id
360 FROM HR_ORGANIZATION_UNITS hou
361 , HR_ORGANIZATION_INFORMATION hoi
362 WHERE hou.business_group_id = p_bg_id
363 AND hou.organization_id = p_le_id
364 AND hoi.ORG_INFORMATION_CONTEXT='NO_LOCAL_UNITS'
365 AND hoi.organization_id = hou.organization_id;
366
367
368 /* Cursor to fetch Details of Costing information */
369 CURSOR csr_assignments
370 (p_payroll_action_id NUMBER
371 ,p_payroll_id NUMBER
372 ,p_start_person NUMBER
373 ,p_end_person NUMBER
374 ,p_restr_econtr VARCHAR2
375 ,p_cost_seg VARCHAR2
376 ,p_element_type_id NUMBER
377 ,p_eoy_code VARCHAR2
378 ,p_business_group_id NUMBER
379 ,p_start_date DATE
380 ,p_end_date DATE
381 ,p_local_unit NUMBER
382 ,p_effective_date DATE) IS
383 SELECT distinct pet.element_type_id ELE_TYPE_ID
384 -- Modified for bug fix 5239796
385 ,nvl(pet.reporting_name,pet.element_name) ELE_NAME
386 -- ,pet.element_information1 ELE_EOY_CODE
387 ,paaf.assignment_id ASG_ID
388 ,paaf.assignment_number ASSIGNMENT_NUMBER
389 ,ppf.payroll_name PAYROLL_NAME
390 ,pcak.concatenated_segments ELE_COST_SEG
391 ,pc.cost_allocation_keyflex_id COST_FLEX_ID
392 ,pc.debit_or_credit DEBIT_CREDIT
393 ,pc.costed_value COSTED_VALUE
394 ,ppa.effective_date EFFECTIVE_DATE
395 ,pc.balance_or_cost COST_OR_BAL
396 ,papf.full_name EMP_NAME
397 ,prr.run_result_id RR_ID -- not in use
398 FROM
399 pay_payroll_actions ppa
400 , pay_payrolls_f ppf
401 , per_all_people_f papf
402 , per_all_assignments_f paaf
403 , pay_assignment_actions paa
404 , hr_soft_coding_keyflex hsck
405 , pay_element_types_f pet
406 , pay_input_values_f piv
407 , pay_run_results prr
408 , pay_costs pc
409 , pay_cost_allocation_keyflex pcak
410 WHERE paaf.person_id BETWEEN p_start_person AND p_end_person
411 AND papf.person_id = paaf.person_id
412 -- Added for bug 5242754 - Start
413 AND paaf.effective_start_date <= p_end_date
414 AND paaf.effective_end_date >= p_start_date
415 AND papf.current_employee_flag = 'Y'
416 -- Added for bug 5242754 - End
417 AND ppf.payroll_id = nvl(p_payroll_id,ppf.payroll_id)
418 AND paaf.payroll_id = ppf.payroll_id
419 AND paa.payroll_action_id = ppa.payroll_action_id
420 AND paa.assignment_id = paaf.assignment_id
421 AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
422 AND hsck.enabled_flag = 'Y'
423 AND hsck.segment2 = nvl(to_char(p_local_unit),hsck.segment2)
424 AND pet.element_type_id = nvl(p_element_type_id,pet.element_type_id)
425 -- AND nvl(pet.element_information1,0) = nvl(p_eoy_code,nvl(pet.element_information1,0))
429 AND pet.classification_id IN
426 AND (pet.business_group_id = p_business_group_id OR pet.legislation_code='NO')
427 AND pet.element_type_id = piv.element_type_id
428 -- Modified for bug fix 5242486
430 (select pec2.classification_id
431 from pay_element_classifications pec1
432 , pay_element_classifications pec2
433 -- Added for bug fix 5242486
434 , pay_sub_classification_rules_f pscrf
435 where pec2.classification_id = pec1.parent_classification_id (+)
436 and nvl(pec1.classification_name,'0') like decode(p_restr_econtr
437 , 'Y','%Subject%to%Employer%Contributions%'
438 , '%')
439 and pec2.classification_id = pet.classification_id
440 -- Added for bug fix 5242486
441 -- Modified for bug fix 6069852
442 and pscrf.element_type_id = pet.element_type_id
443 --and pscrf.classification_id = pec1.classification_id
444 and ppa.date_earned between pscrf.effective_start_date and pscrf.effective_end_date
445 -- Added for bug fix 6069852
446 UNION ALL
447 select pec2.classification_id
448 from pay_element_classifications pec1
449 , pay_element_classifications pec2
450 where pec2.classification_id = pec1.parent_classification_id (+)
451 and nvl(pec1.classification_name,'0') like decode(p_restr_econtr
452 , 'N','%')
453 and pec2.classification_id = pet.classification_id
454 )
455 AND piv.name ='Pay Value'
456 AND nvl(pc.distributed_input_value_id, pc.input_value_id) = piv.input_value_id
457 AND prr.element_type_id = pet.element_type_id
458 AND prr.run_result_id = pc.run_result_id
459 AND pc.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id
460 AND pcak.concatenated_segments like replace(nvl(p_cost_seg,'%'),'.','%.')||'%'
461 AND prr.assignment_action_id = paa.assignment_action_id
462 AND ppa.date_earned between pet.effective_start_date and pet.effective_end_date
463 AND ppa.date_earned between p_start_date and p_end_date
464 AND ppa.date_earned between paaf.effective_start_date and paaf.effective_end_date
465 AND ppa.date_earned between papf.effective_start_date and papf.effective_end_date
466 AND ppa.date_earned between piv.effective_start_date and piv.effective_end_date
467 AND ppa.date_earned between ppf.effective_start_date and ppf.effective_end_date;
468
469
470 /* Cursor to get Element Code */
471 -- Modified for bug fix 5239796
472 CURSOR csr_ele_code(p_ele_type_id NUMBER
473 ,p_leg_emp_id NUMBER) IS
474 /*SELECT eei_information1 ele_code
475 FROM pay_element_type_extra_info petei
476 WHERE information_type='NO_ELEMENT_CODES'
477 AND nvl(element_type_id,0) = p_ele_type_id
478 AND nvl(eei_information2,0) = nvl(p_leg_emp_id, 0)
479 AND rownum <=1; */
480 SELECT nvl((select eei_information1 from pay_element_type_extra_info petei
481 where petei.information_type='NO_ELEMENT_CODES'
482 and element_type_id = p_ele_type_id
483 and petei.eei_information2 = p_leg_emp_id
484 and rownum=1),
485 (select eei_information1 from pay_element_type_extra_info petei
486 where petei.information_type='NO_ELEMENT_CODES'
487 and element_type_id = p_ele_type_id
488 and eei_information2 is null
489 and rownum=1)) ele_code from dual;
490
491
492 rec_hdr_info csr_get_hdr_info%ROWTYPE;
493 rec_ele_code csr_ele_code%ROWTYPE;
494
495 -- Variable Declarations
496
497 l_count NUMBER := 0;
498 l_action_info_id NUMBER;
499 l_ovn NUMBER;
500 l_actid NUMBER;
501 l_asgid NUMBER := -999;
502
503 l_archive VARCHAR2(240);
504 l_payroll_id NUMBER;
505
506 l_le_id NUMBER;
507 l_local_unit_id NUMBER;
508 l_restr_econtr VARCHAR2(80);
509 l_cost_seg VARCHAR2(80);
510 l_ele_type_id NUMBER;
511 l_ele_code VARCHAR2(80);
512 -- Uncommenting for bug fix 6069852
513 --l_cost_value NUMBER;
514 l_eoy_code VARCHAR2(80);
515
516 l_effective_date DATE;
517 l_date_from DATE;
518 l_date_to DATE;
519 l_bg_id NUMBER;
520
521
522
523 BEGIN
524 --Hr_utility.trace_on(null,'PRR');
525 Hr_utility.trace('#-ASSIGNMENT_ACTION_CODE ');
526 IF g_debug THEN
527 hr_utility.set_location(' Entering Procedure ASSIGNMENT_ACTION_CODE',10);
528 END IF;
529 Hr_utility.trace('#-Before fetching csr header details ');
530 Hr_utility.trace('#-p_payroll_action_id '|| p_payroll_action_id);
531 -- Fetch Header details
532 OPEN csr_get_hdr_info(p_payroll_action_id);
533 FETCH csr_get_hdr_info INTO rec_hdr_info;
534 CLOSE csr_get_hdr_info;
535 Hr_utility.trace('#-rec_hdr_info.effective_date '|| rec_hdr_info.effective_date);
536 l_effective_date := rec_hdr_info.effective_date;
537 Hr_utility.trace('#-l_effective_date '|| l_effective_date);
538 --
539 Hr_utility.trace('#-rec_hdr_info.business_group_id '|| rec_hdr_info.business_group_id);
540 l_bg_id := rec_hdr_info.business_group_id;
541 Hr_utility.trace('#-l_bg_id '|| l_bg_id);
542 --
543 Hr_utility.trace('#-rec_hdr_info.tax_unit_id '|| rec_hdr_info.tax_unit_id);
544 l_le_id := rec_hdr_info.tax_unit_id;
545 Hr_utility.trace('#-l_le_id '|| l_le_id);
546 --
547 Hr_utility.trace('#-rec_hdr_info.from_date '|| rec_hdr_info.from_date);
548 l_date_from := rec_hdr_info.from_date;
549 Hr_utility.trace('#-l_date_from '|| l_date_from);
550 --
554 --
551 Hr_utility.trace('#-rec_hdr_info.to_date '|| rec_hdr_info.to_date);
552 l_date_to := rec_hdr_info.to_date;
553 Hr_utility.trace('#-l_date_to '|| l_date_to);
555 Hr_utility.trace('#-rec_hdr_info.payroll_id '|| rec_hdr_info.payroll_id);
556 l_payroll_id := rec_hdr_info.payroll_id;
557 Hr_utility.trace('#-l_payroll_id '|| l_payroll_id);
558 --
559 Hr_utility.trace('#-rec_hdr_info.restr_econtr '|| rec_hdr_info.restr_econtr);
560 l_restr_econtr := rec_hdr_info.restr_econtr;
561 Hr_utility.trace('#-l_restr_econtr '|| l_restr_econtr);
562 --
563 Hr_utility.trace('#-rec_hdr_info.cost_seg '|| rec_hdr_info.cost_seg);
564 l_cost_seg := rec_hdr_info.cost_seg;
565 Hr_utility.trace('#-l_cost_seg '|| l_cost_seg);
566 --
567 Hr_utility.trace('#-rec_hdr_info.ele_type_id '|| rec_hdr_info.ele_type_id);
568 l_ele_type_id := rec_hdr_info.ele_type_id;
569 Hr_utility.trace('#-l_ele_type_id '|| l_ele_type_id);
570 --
571 Hr_utility.trace('#-rec_hdr_info.eoy_code '|| rec_hdr_info.eoy_code);
572 l_eoy_code := rec_hdr_info.eoy_code;
573 Hr_utility.trace('#-l_eoy_code '|| l_eoy_code);
574 --
575 Hr_utility.trace('#-Before fetching csr csr_get_lu_le ');
576 Hr_utility.trace('#-with l_le_id '||l_le_id);
577 Hr_utility.trace('#-with l_bg_id '||l_bg_id);
578 --
579 /* To fetch all Local Units belonging to the Legal Employer */
580 FOR rec_get_lu_le IN csr_get_lu_le(l_le_id,l_bg_id)
581
582 -- Fetch all Local Units belonging to the Legal Employer
583 LOOP
584 Hr_utility.trace('#-Rec found in csr_get_lu_le');
585 Hr_utility.trace('#-rec_get_lu_le.lu_id '||rec_get_lu_le.lu_id);
586 l_local_unit_id := rec_get_lu_le.lu_id;
587 Hr_utility.trace('#-l_local_unit_id '||l_local_unit_id);
588
589 -- Fetch Assignment's details for Detailed Report
590 Hr_utility.trace('#-Before fetching csr csr_assignments ');
591 Hr_utility.trace('#-with p_pay roll_action_id '||p_payroll_action_id);
592 Hr_utility.trace('#-with l_payroll_id '||l_payroll_id);
593 Hr_utility.trace('#-with p_start_person '||p_start_person);
594 Hr_utility.trace('#-with p_end_person '||p_end_person);
595 Hr_utility.trace('#-with l_restr_econtr '||l_restr_econtr);
596 Hr_utility.trace('#-with l_cost_seg '||l_cost_seg);
597 Hr_utility.trace('#-with l_ele_type_id '||l_ele_type_id);
598 Hr_utility.trace('#-with l_eoy_code '||l_eoy_code);
599 Hr_utility.trace('#-with l_bg_id '||l_bg_id);
600 Hr_utility.trace('#-with l_date_from '||l_date_from);
601 Hr_utility.trace('#-with l_date_to '||l_date_to);
602 Hr_utility.trace('#-with l_local_unit_id '||l_local_unit_id);
603 Hr_utility.trace('#-with l_effective_date '||l_effective_date);
604
605 FOR csr_rec IN csr_assignments( p_payroll_action_id
606 ,l_payroll_id
607 ,p_start_person
608 ,p_end_person
609 ,l_restr_econtr
610 ,l_cost_seg
611 ,l_ele_type_id
612 ,l_eoy_code
613 ,l_bg_id
614 ,l_date_from
615 ,l_date_to
616 ,l_local_unit_id
617 ,l_effective_date )
618 LOOP
619 Hr_utility.trace('#-record found in csr csr_assignments ');
620 Hr_utility.trace('#-p_pay roll_action_id '||p_payroll_action_id);
621 Hr_utility.trace('#-l_payroll_id '||l_payroll_id);
622 Hr_utility.trace('#-p_start_person '||p_start_person);
623 Hr_utility.trace('#-p_end_person '||p_end_person);
624 Hr_utility.trace('#-l_restr_econtr '||l_restr_econtr);
625 Hr_utility.trace('#-l_cost_seg '||l_cost_seg);
626 Hr_utility.trace('#-l_ele_type_id '||l_ele_type_id);
627 Hr_utility.trace('#-l_eoy_code '||l_eoy_code);
628 Hr_utility.trace('#-l_bg_id '||l_bg_id);
629 Hr_utility.trace('#-l_date_from '||l_date_from);
630 Hr_utility.trace('#-l_date_to '||l_date_to);
631 Hr_utility.trace('#-l_local_unit_id '||l_local_unit_id);
632 Hr_utility.trace('#-l_effective_date '||l_effective_date);
633
634 /*Check for Change of Assignment ID to Create New Assignment Action ID
635 and for Archiving the data */
636 Hr_utility.trace('#- get the next seq val');
637 BEGIN
638 SELECT pay_assignment_actions_s.NEXTVAL INTO l_actid FROM dual;
639 EXCEPTION
640 WHEN OTHERS THEN
641 NULL ;
642 END ;
643 Hr_utility.trace('#- l_actid '||l_actid);
644
645 -- Create the archive assignment action
646 Hr_utility.trace('#- create asg act code');
647 Hr_utility.trace('#- l_actid '||l_actid);
648 Hr_utility.trace('#- csr_rec.asg_id '||csr_rec.asg_id);
649 Hr_utility.trace('#- p_payroll_action_id '||p_payroll_action_id);
650 Hr_utility.trace('#- p_chunk '||p_chunk);
651 hr_nonrun_asact.insact(l_actid ,csr_rec.asg_id ,p_payroll_action_id ,p_chunk,null);
652 Hr_utility.trace('#- done with create asg act code');
653 Hr_utility.trace('#- l_actid '||l_actid);
654 Hr_utility.trace('#- csr_rec.asg_id '||csr_rec.asg_id);
655 Hr_utility.trace('#- p_payroll_action_id '||p_payroll_action_id);
656 Hr_utility.trace('#- p_chunk '||p_chunk);
657 --
658 Hr_utility.trace('#- going to fetch from csr csr_ele_code ');
659 Hr_utility.trace('#- with csr_rec.ele_type_id' || csr_rec.ele_type_id );
660 Hr_utility.trace('#- with l_le_id ' || l_le_id );
661 OPEN csr_ele_code(csr_rec.ele_type_id,l_le_id);
662 FETCH csr_ele_code INTO rec_ele_code;
666 Hr_utility.trace('#- rec_ele_code.ele_code '||rec_ele_code.ele_code);
663 CLOSE csr_ele_code;
664 Hr_utility.trace('#- record found in csr csr_ele_code ');
665 --
667 l_ele_code := rec_ele_code.ele_code;
668 Hr_utility.trace('#- l_ele_code '||l_ele_code);
669 --
670 Hr_utility.trace('#- csr_rec.costed_value '||csr_rec.costed_value);
671 Hr_utility.trace('#- FND_NUMBER.NUMBER_TO_CANONICAL..csr_rec.costed_value '||FND_NUMBER.NUMBER_TO_CANONICAL(csr_rec.costed_value));
672 -- Uncommenting this part for bug fix 6069852
673 --l_cost_value := FND_NUMBER.NUMBER_TO_CANONICAL(csr_rec.costed_value);
674 Hr_utility.trace('#- l_ele_code '||l_ele_code);
675 --
676 Hr_utility.trace('#- l_eoy_code '||l_eoy_code);
677 Hr_utility.trace('#- csr_rec.ele_type_id '||csr_rec.ele_type_id);
678 Hr_utility.trace('#- csr_rec.effective_date '||csr_rec.effective_date);
679 Hr_utility.trace('#- get_ele_code(csr_rec.ele_type_id, csr_rec.effective_date) '||get_ele_code(csr_rec.ele_type_id, csr_rec.effective_date));
680 Hr_utility.trace('#- going to call if 1 ');
681 IF (l_eoy_code IS NULL) OR
682 (l_eoy_code = get_ele_code(csr_rec.ele_type_id, csr_rec.effective_date)) THEN
683 -- Creating Initial Archive Entries
684 Hr_utility.trace('#- qualified in if 1 ');
685 Hr_utility.trace('#- archiving DETL ');
686 --
687 Hr_utility.set_location('#- l_action_info_id '|| l_action_info_id,21 );
688 Hr_utility.set_location('#- l_ovn '|| l_ovn ,22);
689 Hr_utility.set_location('#- l_actid '|| l_actid,23 );
690 Hr_utility.set_location('#- l_effective_date '|| l_effective_date,24 );
691 Hr_utility.set_location('#- csr_rec.asg_id '|| csr_rec.asg_id,25 );
692 Hr_utility.set_location('#- l_le_id '|| l_le_id ,26);
693 Hr_utility.set_location('#- csr_rec.payroll_name '|| csr_rec.payroll_name,27 );
694 Hr_utility.set_location('#- p_payroll_action_id '|| p_payroll_action_id,28 );
695 Hr_utility.set_location('#- csr_rec.assignment_number '|| csr_rec.assignment_number,29 );
696 Hr_utility.set_location('#- l_local_unit_id '|| l_local_unit_id,30 );
697 Hr_utility.set_location('#- csr_rec.ele_type_id '|| csr_rec.ele_type_id,31 );
698 Hr_utility.set_location('#- csr_rec.ele_name '|| csr_rec.ele_name,32 );
699 Hr_utility.set_location('#- int l_eoy_code '|| l_eoy_code,33 );
700 Hr_utility.set_location('#- int csr_rec.ele_type_id '|| csr_rec.ele_type_id,34 );
701 Hr_utility.set_location('#- int csr_rec.effective_date '|| csr_rec.effective_date,35 );
702 -- Hr_utility.trace('#- int csr_rec.effective_date '|| csr_rec.effective_date );
703 Hr_utility.set_location('#- get_ele_code(csr_rec.ele_type_id, csr_rec.effective_date )) '|| get_ele_code(csr_rec.ele_type_id, csr_rec.effective_date ),36 );
704 Hr_utility.set_location('#- csr_rec.ele_cost_seg '|| csr_rec.ele_cost_seg,37 );
705 Hr_utility.set_location('#- csr_rec.cost_flex_id '|| csr_rec.cost_flex_id,38 );
706 Hr_utility.set_location('#- csr_rec.debit_credit '|| csr_rec.debit_credit,39 );
707 Hr_utility.set_location('#- l_ele_code '|| l_ele_code,40 );
708 Hr_utility.set_location('#- csr_rec.costed_value '|| csr_rec.costed_value,41 );
709 Hr_utility.set_location('#- csr_rec.cost_or_bal '|| csr_rec.cost_or_bal,42 );
710 Hr_utility.set_location('#- csr_rec.effective_date '|| csr_rec.effective_date,43 );
711 Hr_utility.set_location('#- csr_rec.emp_name '|| csr_rec.emp_name,44 );
712 --
713 pay_action_information_api.create_action_information
714 (p_action_information_id => l_action_info_id -- OUT parameter
715 ,p_object_version_number => l_ovn -- OUT parameter
716 ,p_action_context_id => l_actid -- Context id = assignment action id (of Archive)
717 ,p_action_context_type => 'AAP' -- Context type
718 ,p_effective_date => l_effective_date -- Date of running the archive
719 ,p_assignment_id => csr_rec.asg_id -- Assignment ID
720 ,p_action_information_category => 'EMEA REPORT INFORMATION' -- Information Category
721 ,p_tax_unit_id => l_le_id -- Legal Employer ID
722 ,p_jurisdiction_code => NULL -- Tax Municipality ID
723 ,p_action_information1 => 'PYNOPACCA' -- Con Program Short Name
724 ,p_action_information2 => csr_rec.payroll_name -- Payroll Name
725 ,p_action_information3 => p_payroll_action_id -- Payroll action id (of Archive)
726 ,p_action_information4 => 'DETL' -- Specifies data is for Detail
727 ,p_action_information5 => csr_rec.assignment_number -- Assignment Number
728 ,p_action_information6 => l_local_unit_id -- Local Unit ID
729 ,p_action_information7 => csr_rec.ele_type_id -- Element Type ID
730 ,p_action_information8 => csr_rec.ele_name -- Element Name
731 ,p_action_information9 => nvl(l_eoy_code,
732 get_ele_code(csr_rec.ele_type_id, csr_rec.effective_date )) -- Element EOY Code
736 ,p_action_information13 => l_ele_code -- Element Code
733 ,p_action_information10 => csr_rec.ele_cost_seg -- Element Costing Flexfield Segments
734 ,p_action_information11 => csr_rec.cost_flex_id -- Costing Flexfield ID
735 ,p_action_information12 => csr_rec.debit_credit -- Debit or Credit Flag
737 ,p_action_information14 => fnd_number.number_to_canonical(csr_rec.costed_value) -- Individual Costing Value
738 ,p_action_information15 => csr_rec.cost_or_bal -- Cost or Balance Flag
739 ,p_action_information16 => csr_rec.effective_date -- Costing Effective Date
740 ,p_action_information17 => csr_rec.emp_name -- Employee Name
741 );
742 Hr_utility.trace('#- archived DETL successfully');
743 END IF;
744 Hr_utility.trace('#- qualified in end if 1 ');
745 --
746 Hr_utility.trace('#- csr_rec.asg_id '||csr_rec.asg_id);
747 l_asgid := csr_rec.asg_id;
748 Hr_utility.trace('#- l_asgid '||l_asgid);
749
750 END LOOP; -- csr_assignments
751 Hr_utility.trace('#- end of loop csr_assignments ');
752
753 END LOOP; -- csr_get_lu_le
754 Hr_utility.trace('#- end of loop csr_get_lu_le ');
755
756 IF g_debug THEN
757 hr_utility.set_location(' Leaving Procedure ASSIGNMENT_ACTION_CODE',20);
758 END IF;
759 Hr_utility.trace('#- end of ASSIGNMENT_ACTION_CODE');
760 END ASSIGNMENT_ACTION_CODE;
761
762
763 PROCEDURE INITIALIZATION_CODE(p_payroll_action_id IN NUMBER)
764 IS
765
766
767 BEGIN
768
769 NULL;
770
771 IF g_debug THEN
772 hr_utility.set_location(' Entering Procedure INITIALIZATION_CODE',10);
773 END IF;
774
775 IF g_debug THEN
776 hr_utility.set_location(' Leaving Procedure INITIALIZATION_CODE',20);
777 END IF;
778
779 EXCEPTION WHEN OTHERS THEN
780 g_err_num := SQLCODE;
781
782 IF g_debug THEN
783 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In INITIALIZATION_CODE',30);
784 END IF;
785
786 END INITIALIZATION_CODE;
787
788
789 PROCEDURE ARCHIVE_CODE(p_assignment_action_id IN NUMBER
790 ,p_effective_date IN DATE)
791 IS
792
793 BEGIN
794 IF g_debug THEN
795 hr_utility.set_location(' Entering Procedure ARCHIVE_CODE',10);
796 END IF;
797
798 IF g_debug THEN
799 hr_utility.set_location(' Leaving Procedure ARCHIVE_CODE',20);
800 END IF;
801
802 END ARCHIVE_CODE;
803
804
805 PROCEDURE DEINITIALIZATION_CODE(p_payroll_action_id IN NUMBER)
806 IS
807
808 CURSOR csr_costing_summary(p_payroll_action_id NUMBER ) IS
809 SELECT to_number(action_information7) element_type_id
810 ,action_information8 element_name
811 ,action_information9 eoy_code
812 ,action_information10 concatenated_segments
813 ,to_number(action_information11) cost_allocation_keyflex_id
814 ,action_information12 debit_or_credit
815 ,sum(fnd_number.canonical_to_number(action_information14)) costed_value
816 ,action_information15 balance_or_cost
817 ,tax_unit_id leg_emp_id
818 ,to_number(action_information6) local_unit_id
819 ,effective_date
820 FROM pay_action_information pai
821 WHERE action_context_type = 'AAP'
822 AND action_information3 = to_char(p_payroll_action_id)
823 AND action_information_category = 'EMEA REPORT INFORMATION'
824 AND action_information1 = 'PYNOPACCA'
825 AND action_information4 = 'DETL'
826 GROUP BY action_information10
827 ,action_information11
828 ,action_information9
829 ,action_information7
830 ,action_information8
831 ,action_information15
832 ,action_information12
833 ,tax_unit_id
834 ,action_information6
835 ,effective_date;
836
837 -- Cursor to get Element Code
838 -- Modified for bug fix 5239796
839 CURSOR csr_ele_code(p_ele_type_id NUMBER
840 ,p_leg_emp_id NUMBER) IS
841 /*SELECT eei_information1 ele_code
842 FROM pay_element_type_extra_info petei
843 WHERE information_type='NO_ELEMENT_CODES'
844 AND nvl(element_type_id,0) = p_ele_type_id
845 AND nvl(eei_information2,0) = nvl(p_leg_emp_id, 0)
846 AND rownum <=1; */
847 SELECT nvl((select eei_information1 from pay_element_type_extra_info petei
848 where petei.information_type='NO_ELEMENT_CODES'
849 and element_type_id = p_ele_type_id
850 and petei.eei_information2 = p_leg_emp_id
851 and rownum=1),
852 (select eei_information1 from pay_element_type_extra_info petei
853 where petei.information_type='NO_ELEMENT_CODES'
854 and element_type_id = p_ele_type_id
855 and eei_information2 is null
856 and rownum=1)) ele_code from dual;
857
858 -- Variable Declarations
859
860 l_ele_type_id NUMBER;
861 l_ele_name VARCHAR2(80);
862 l_ele_eoy_code VARCHAR2(80);
863 l_ele_cost_seg VARCHAR2(80);
864 l_cost_flex_id NUMBER;
865 l_debit_credit VARCHAR2(80);
866 l_sum_cost_value VARCHAR2(80);
867 l_cost_or_bal VARCHAR2(80);
868 l_ele_code VARCHAR2(80);
869 l_le_id NUMBER;
870 l_local_unit_id NUMBER;
871 l_effective_date DATE;
872 l_action_info_id NUMBER;
876 rec_ele_code csr_ele_code%ROWTYPE;
873 l_ovn NUMBER;
874 l_business_group_id NUMBER;
875 l_payroll_id NUMBER;
877 BEGIN
878
879 IF g_debug THEN
880 hr_utility.set_location(' Entering Procedure DEINITIALIZATION_CODE',10);
881 END IF;
882
883 -- Fetch Summary of Detailed Costing Information
884 FOR rec_costing_summary IN csr_costing_summary(p_payroll_action_id)
885 LOOP
886 l_ele_type_id := rec_costing_summary.element_type_id;
887 l_ele_name := rec_costing_summary.element_name;
888 l_ele_eoy_code := rec_costing_summary.eoy_code;
889 l_ele_cost_seg := rec_costing_summary.concatenated_segments;
890 l_cost_flex_id := rec_costing_summary.cost_allocation_keyflex_id;
891 l_debit_credit := rec_costing_summary.debit_or_credit;
892 -- l_sum_cost_value := rec_costing_summary.costed_value; --not reqd
893 l_cost_or_bal := rec_costing_summary.balance_or_cost;
894 l_le_id := rec_costing_summary.leg_emp_id;
895 l_effective_date := rec_costing_summary.effective_date;
896 OPEN csr_ele_code(l_ele_type_id,l_le_id);
897 FETCH csr_ele_code INTO rec_ele_code;
898 CLOSE csr_ele_code;
899 l_ele_code := rec_ele_code.ele_code;
900 l_sum_cost_value := fnd_number.number_to_canonical(rec_costing_summary.costed_value);
901 -- Archive the SUMMARY REPORT DETAILS
902 pay_action_information_api.create_action_information
903 (p_action_information_id => l_action_info_id -- out parameter
904 ,p_object_version_number => l_ovn -- out parameter
905 ,p_action_context_id => p_payroll_action_id -- context id = payroll action id (of Archive)
906 ,p_action_context_type => 'PA' -- context type
907 ,p_effective_date => l_effective_date -- Date of Running the Archive
908 ,p_action_information_category => 'EMEA REPORT DETAILS' -- Information Category
909 ,p_tax_unit_id => l_le_id -- Legal Employer ID
910 ,p_jurisdiction_code => NULL -- Tax Municipality ID
911 ,p_action_information1 => 'PYNOPACCA' -- Conc Prg Short Name
912 ,p_action_information2 => l_business_group_id -- Business Group ID
913 ,p_action_information3 => l_payroll_id -- Payroll ID
914 ,p_action_information4 => 'SUMM' -- Specifies data is for Summary
915 ,p_action_information5 => l_local_unit_id -- Local Unit ID
916 ,p_action_information6 => l_ele_type_id -- Element Type ID
917 ,p_action_information7 => l_ele_name -- Element Name
918 ,p_action_information8 => l_ele_eoy_code -- Element EOY Code
919 ,p_action_information9 => l_ele_cost_seg -- Element Costing Flexfield Segments
920 ,p_action_information10 => l_cost_flex_id -- Costing Flexfield ID
921 ,p_action_information11 => l_debit_credit -- Debit or Credit Flag
922 ,p_action_information12 => l_ele_code -- Element Code
923 ,p_action_information13 => l_sum_cost_value -- Total Costing Value
924 ,p_action_information14 => l_cost_or_bal -- Cost or Balance Flag
925 );
926 Hr_utility.set_location('#- l_business_group_id '|| l_business_group_id,45 );
927 Hr_utility.set_location('#- l_payroll_id '|| l_payroll_id,46 );
928 Hr_utility.set_location('#- l_local_unit_id '|| l_local_unit_id,47 );
929 Hr_utility.set_location('#- l_ele_type_id '|| l_ele_type_id,48 );
930 Hr_utility.set_location('#- l_ele_name '|| l_ele_name,49 );
931 Hr_utility.set_location('#- l_ele_eoy_code '|| l_ele_eoy_code,50 );
932 Hr_utility.set_location('#- l_ele_cost_seg '|| l_ele_cost_seg,51 );
933 Hr_utility.set_location('#- l_cost_flex_id '|| l_cost_flex_id,52 );
934 Hr_utility.set_location('#- l_debit_credit '|| l_debit_credit,53 );
935 Hr_utility.set_location('#- l_ele_code '|| l_ele_code,54 );
936 Hr_utility.set_location('#- l_sum_cost_value '|| l_sum_cost_value,55 );
937 Hr_utility.set_location('#- l_cost_or_bal '|| l_cost_or_bal,56 );
938
939 END LOOP; --csr_costing_summary
940
941 IF g_debug THEN
942 hr_utility.set_location(' Leaving Procedure DEINITIALIZATION_CODE',20);
943 END IF;
944
945 EXCEPTION WHEN OTHERS THEN
946 g_err_num := SQLCODE;
947
948 IF g_debug THEN
949 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In DEINITIALIZATION_CODE',30);
950 END IF;
951
952 END DEINITIALIZATION_CODE;
953
954
955 PROCEDURE POPULATE_DATA_SUMMARY
956 (p_business_group_id IN NUMBER,
957 p_payroll_action_id IN VARCHAR2 ,
958 p_template_name IN VARCHAR2,
959 p_xml OUT NOCOPY CLOB)
960 IS
961
962
963 /* Cursor to fetch Header Information */
964 CURSOR csr_get_hdr_info(p_payroll_action_id NUMBER)
965 IS
966 SELECT *
967 FROM pay_action_information pai
968 WHERE action_context_type = 'PA'
969 AND action_context_id = p_payroll_action_id
970 AND action_information_category = 'EMEA REPORT DETAILS'
971 AND action_information1 = 'PYNOPACCA'
972 AND action_information4 = 'HDR';
973
974 /* Cursor to fetch Costing Summary Information */
975 CURSOR csr_get_summ_info(p_payroll_action_id NUMBER, p_tax_unit_id NUMBER)
976 IS
977 SELECT *
978 FROM pay_action_information pai
979 WHERE action_context_type = 'PA'
980 AND action_context_id = p_payroll_action_id
981 AND action_information_category = 'EMEA REPORT DETAILS'
982 AND action_information1 = 'PYNOPACCA'
983 AND tax_unit_id = p_tax_unit_id
987 ,action_information8
984 AND action_information4 = 'SUMM';
985 /*
986 ORDER BY action_information9
988 ,action_information7
989 ,action_information14
990 ,action_information11;
991 */
992
993 rec_get_hdr_info csr_get_hdr_info%ROWTYPE;
994
995 l_counter NUMBER := 0;
996 l_count NUMBER := 0;
997 l_payroll_action_id NUMBER;
998
999 l_prev_cost_seg VARCHAR2(80) := ' ';
1000 l_prev_eoy_code VARCHAR2(80) := ' ';
1001 l_total_cost_credit NUMBER := 0;
1002 l_total_cost_debit NUMBER := 0;
1003
1004 --Added for bug fix 5244886
1005 l_total_net_credit NUMBER := 0;
1006 l_total_net_debit NUMBER := 0;
1007
1008 BEGIN
1009 --
1010 IF p_payroll_action_id IS NULL THEN
1011 BEGIN
1012 SELECT payroll_action_id
1013 INTO l_payroll_action_id
1014 FROM pay_payroll_actions ppa,
1015 fnd_conc_req_summary_v fcrs,
1016 fnd_conc_req_summary_v fcrs1
1017 WHERE fcrs.request_id = fnd_global.conc_request_id
1018 AND fcrs.priority_request_id = fcrs1.priority_request_id
1019 AND ppa.request_id between fcrs1.request_id and fcrs.request_id
1020 AND ppa.request_id = fcrs1.request_id;
1021 EXCEPTION WHEN OTHERS THEN
1022 NULL;
1023 END ;
1024 ELSE
1025 l_payroll_action_id := p_payroll_action_id;
1026 END IF;
1027 --
1028 hr_utility.set_location('Entered Procedure GETDATA',10);
1029 --
1030 /* Get the File Header Information */
1031 OPEN csr_get_hdr_info(l_payroll_action_id);
1032 FETCH csr_get_hdr_info INTO rec_get_hdr_info;
1033 CLOSE csr_get_hdr_info;
1034 --
1035 hr_utility.set_location('Before populating pl/sql table',20);
1036 --
1037 xml_tab(l_counter).TagName :='FILE_HEADER_START';
1038 xml_tab(l_counter).TagValue :='FILE_HEADER_START';
1039 l_counter := l_counter + 1;
1040 --
1041 hr_utility.set_location('LE_NAME'||rec_get_hdr_info.action_information7,21);
1042 xml_tab(l_counter).TagName :='LE_NAME';
1043 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information7;
1044 l_counter := l_counter + 1;
1045 --
1046 hr_utility.set_location('LE_ORG_NO'||rec_get_hdr_info.action_information8,22);
1047 xml_tab(l_counter).TagName :='LE_ORG_NO';
1048 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information8;
1049 l_counter := l_counter + 1;
1050 --
1051 hr_utility.set_location('REPORT_START_DATE'||rec_get_hdr_info.action_information12,23);
1052 xml_tab(l_counter).TagName :='REPORT_START_DATE';
1053 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information12;
1054 l_counter := l_counter + 1;
1055 --
1056 hr_utility.set_location('REPORT_END_DATE'||rec_get_hdr_info.action_information13,24);
1057 xml_tab(l_counter).TagName :='REPORT_END_DATE';
1058 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information13;
1059 l_counter := l_counter + 1;
1060 --
1061 hr_utility.set_location('PARAM_COST_SEG'||rec_get_hdr_info.action_information9,25);
1062 xml_tab(l_counter).TagName :='PARAM_COST_SEG';
1063 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information9;
1064 l_counter := l_counter + 1;
1065 --
1066 hr_utility.set_location('PARAM_EOY_CODE'||rec_get_hdr_info.action_information10,26);
1067 xml_tab(l_counter).TagName :='PARAM_EOY_CODE';
1068 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information10;
1069 l_counter := l_counter + 1;
1070 --
1071 hr_utility.set_location('PARAM_RESTR_ECONTR'||rec_get_hdr_info.action_information14,27);
1072 xml_tab(l_counter).TagName :='PARAM_RESTR_ECONTR';
1073 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information14;
1074 l_counter := l_counter + 1;
1075 --
1076 hr_utility.set_location('PARAM_PAYROLL_NAME'||rec_get_hdr_info.action_information15,28);
1077 xml_tab(l_counter).TagName :='PARAM_PAYROLL_NAME';
1078 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information15;
1079 l_counter := l_counter + 1;
1080 --
1081 hr_utility.set_location('PARAM_ELE_NAME'||rec_get_hdr_info.action_information16,29);
1082 xml_tab(l_counter).TagName :='PARAM_ELE_NAME';
1083 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information16;
1084 l_counter := l_counter + 1;
1085 --
1086 xml_tab(l_counter).TagName :='COST_RECO_START';
1087 xml_tab(l_counter).TagValue :='COST_RECO_START';
1088 l_counter := l_counter + 1;
1089 --
1090 hr_utility.set_location('FOR LOOP STARTING csr_get_summ_info',31);
1091 FOR rec_get_summ_info IN csr_get_summ_info(l_payroll_action_id,rec_get_hdr_info.tax_unit_id)
1092 LOOP
1093 /* Counter to count records fetched */
1094 l_count := l_count + 1 ;
1095 /*Check cost_seg for grouping */
1096 hr_utility.set_location('Record found',32);
1097 hr_utility.set_location('l_count'||l_count,32);
1098 hr_utility.set_location('l_prev_cost_seg'||l_prev_cost_seg,33);
1099 hr_utility.set_location('rec_get_summ_info.action_information9'||rec_get_summ_info.action_information9,34);
1100 --
1101 IF(l_count = 1 OR rec_get_summ_info.action_information9 <> l_prev_cost_seg ) THEN
1102 IF(l_count <> 1) THEN
1103 xml_tab(l_counter).TagName :='EOY_CODE_GRP_START';
1104 xml_tab(l_counter).TagValue :='EOY_CODE_GRP_END';
1105 l_counter := l_counter + 1;
1106 --
1107 hr_utility.set_location('l_total_cost_credit'||l_total_cost_credit,34);
1108 hr_utility.set_location('l_total_cost_credit'||l_total_cost_credit,35);
1109 --
1110 IF( l_total_cost_credit <> 0 ) THEN
1111 xml_tab(l_counter).TagName :='COST_SEG_GRP_TOT_CREDIT';
1112 xml_tab(l_counter).TagValue := l_total_cost_credit;
1113 l_counter := l_counter + 1;
1114 END IF;
1115
1116 IF( l_total_cost_debit <> 0 ) THEN
1117 xml_tab(l_counter).TagName :='COST_SEG_GRP_TOT_DEBIT';
1121
1118 xml_tab(l_counter).TagValue := l_total_cost_debit;
1119 l_counter := l_counter + 1;
1120 END IF;
1122 -- Added for bug fix 5244886
1123 l_total_net_credit := GREATEST(l_total_cost_credit - l_total_cost_debit,0);
1124 l_total_net_debit := GREATEST(l_total_cost_debit - l_total_cost_credit,0);
1125 hr_utility.set_location('l_total_net_credit'||l_total_net_credit,35);
1126 IF( l_total_net_credit <> 0 ) THEN
1127 xml_tab(l_counter).TagName :='COST_SEG_NET_TOT_CREDIT';
1128 xml_tab(l_counter).TagValue := l_total_net_credit;
1129 l_counter := l_counter + 1;
1130 END IF;
1131 hr_utility.set_location('l_total_net_debit'||l_total_net_debit,35);
1132 IF( l_total_net_debit <> 0 ) THEN
1133 xml_tab(l_counter).TagName :='COST_SEG_NET_TOT_DEBIT';
1134 xml_tab(l_counter).TagValue := l_total_net_debit;
1135 l_counter := l_counter + 1;
1136 END IF;
1137 -- End of bug fix 5244886
1138 xml_tab(l_counter).TagName :='COST_SEG_GRP_START';
1139 xml_tab(l_counter).TagValue :='COST_SEG_GRP_END';
1140 l_counter := l_counter + 1;
1141 END IF;
1142 --
1143 l_total_cost_credit :=0;
1144 l_total_cost_debit :=0;
1145 --Added for bug fix 5244886
1146 l_total_net_credit :=0;
1147 l_total_net_debit :=0;
1148 --
1149 xml_tab(l_counter).TagName :='COST_SEG_GRP_START';
1150 xml_tab(l_counter).TagValue :='COST_SEG_GRP_START';
1151 l_counter := l_counter + 1;
1152 --
1153 xml_tab(l_counter).TagName :='EOY_CODE_GRP_START';
1154 xml_tab(l_counter).TagValue :='EOY_CODE_GRP_START';
1155 l_counter := l_counter + 1;
1156 --
1157 /*Check eoy_code for grouping if no change in cost segments */
1158 ELSIF( l_count = 1 OR rec_get_summ_info.action_information8 <> l_prev_eoy_code ) THEN
1159 hr_utility.set_location('ELSIF',36);
1160 hr_utility.set_location('l_count'||l_count,37);
1161 IF(l_count <> 1) THEN
1162 xml_tab(l_counter).TagName :='EOY_CODE_GRP_START';
1163 xml_tab(l_counter).TagValue :='EOY_CODE_GRP_END';
1164 l_counter := l_counter + 1;
1165 END IF;
1166 xml_tab(l_counter).TagName :='EOY_CODE_GRP_START';
1167 xml_tab(l_counter).TagValue :='EOY_CODE_GRP_START';
1168 l_counter := l_counter + 1;
1169 END IF;
1170 --
1171 xml_tab(l_counter).TagName :='COSTING_START';
1172 xml_tab(l_counter).TagValue :='COSTING_START';
1173 l_counter := l_counter + 1;
1174
1175 -- Suppress same Cost Flexfield value appearing in a group
1176 hr_utility.set_location('l_count'||l_count,38);
1177 hr_utility.set_location('rec_get_summ_info.action_information9'||rec_get_summ_info.action_information9,39);
1178 hr_utility.set_location('l_prev_cost_seg'||l_prev_cost_seg,40);
1179 IF( l_count = 1 OR rec_get_summ_info.action_information9 <> l_prev_cost_seg ) THEN
1180 xml_tab(l_counter).TagName :='COST_SEG';
1181 xml_tab(l_counter).TagValue := rec_get_summ_info.action_information9;
1182 l_counter := l_counter + 1;
1183 END IF;
1184 --
1185 hr_utility.set_location('EOY_CODE'||rec_get_summ_info.action_information8,41);
1186 xml_tab(l_counter).TagName :='EOY_CODE';
1187 xml_tab(l_counter).TagValue := rec_get_summ_info.action_information8;
1188 l_counter := l_counter + 1;
1189 --
1190 hr_utility.set_location('ELE_CODE'||rec_get_summ_info.action_information12,42);
1191 xml_tab(l_counter).TagName :='ELE_CODE';
1192 xml_tab(l_counter).TagValue := rec_get_summ_info.action_information12;
1193 --
1194 IF( rec_get_summ_info.action_information12 IS NOT NULL) THEN
1195 xml_tab(l_counter).TagValue := ' ,' ||xml_tab(l_counter).TagValue;
1196 END IF;
1197 l_counter := l_counter + 1;
1198 --
1199 hr_utility.set_location('ELE_NAME'||rec_get_summ_info.action_information7,43);
1200 xml_tab(l_counter).TagName :='ELE_NAME';
1201 xml_tab(l_counter).TagValue := rec_get_summ_info.action_information7;
1202 l_counter := l_counter + 1;
1203 --
1204 hr_utility.set_location('COST_OR_BAL'||rec_get_summ_info.action_information14,44);
1205 xml_tab(l_counter).TagName :='COST_OR_BAL';
1206 xml_tab(l_counter).TagValue :=rec_get_summ_info.action_information14;
1207 l_counter := l_counter + 1;
1208 --
1209 hr_utility.set_location('COST_DEBIT/COST_CREDIT'||fnd_number.canonical_to_number(rec_get_summ_info.action_information13),45);
1210 hr_utility.set_location('C/D'||rec_get_summ_info.action_information11,46);
1211 IF( rec_get_summ_info.action_information11 = 'D') THEN
1212 xml_tab(l_counter).TagName :='COST_DEBIT';
1213 xml_tab(l_counter).TagValue := fnd_number.canonical_to_number(rec_get_summ_info.action_information13);
1214 l_counter := l_counter + 1;
1215 l_total_cost_debit := l_total_cost_debit + fnd_number.canonical_to_number(rec_get_summ_info.action_information13);
1216 ELSIF( rec_get_summ_info.action_information11 ='C') THEN
1217 xml_tab(l_counter).TagName :='COST_CREDIT';
1218 xml_tab(l_counter).TagValue := fnd_number.canonical_to_number(rec_get_summ_info.action_information13);
1219 l_counter := l_counter + 1;
1220 l_total_cost_credit := l_total_cost_credit + fnd_number.canonical_to_number(rec_get_summ_info.action_information13);
1221 END IF;
1222 --
1223 xml_tab(l_counter).TagName :='COSTING_START';
1224 xml_tab(l_counter).TagValue :='COSTING_END';
1225 l_counter := l_counter + 1;
1226 --
1227 l_prev_eoy_code := rec_get_summ_info.action_information8;
1228 l_prev_cost_seg := rec_get_summ_info.action_information9;
1229 --
1230 END LOOP;
1231 hr_utility.set_location('END LOOP',47);
1232 hr_utility.set_location('l_count'||l_count,48);
1233 IF(l_count = 0) THEN
1234 xml_tab(l_counter).TagName :='COST_SEG_GRP_START';
1235 xml_tab(l_counter).TagValue :='COST_SEG_GRP_START';
1236 l_counter := l_counter + 1;
1237 --
1241 --
1238 xml_tab(l_counter).TagName :='EOY_CODE_GRP_START';
1239 xml_tab(l_counter).TagValue :='EOY_CODE_GRP_START';
1240 l_counter := l_counter + 1;
1242 END IF;
1243 --
1244 xml_tab(l_counter).TagName :='EOY_CODE_GRP_START';
1245 xml_tab(l_counter).TagValue :='EOY_CODE_GRP_END';
1246 l_counter := l_counter + 1;
1247 hr_utility.set_location('l_total_cost_credit'||l_total_cost_credit,49);
1248 IF ( l_total_cost_credit <> 0 ) THEN
1249 xml_tab(l_counter).TagName :='COST_SEG_GRP_TOT_CREDIT';
1250 xml_tab(l_counter).TagValue := l_total_cost_credit;
1251 l_counter := l_counter + 1;
1252 END IF;
1253 hr_utility.set_location('l_total_cost_debit'||l_total_cost_debit,51);
1254 IF ( l_total_cost_debit <> 0 ) THEN
1255 xml_tab(l_counter).TagName :='COST_SEG_GRP_TOT_DEBIT';
1256 xml_tab(l_counter).TagValue := l_total_cost_debit;
1257 l_counter := l_counter + 1;
1258 END IF;
1259
1260 -- Added for bug fix 5244886
1261 l_total_net_credit := GREATEST(l_total_cost_credit - l_total_cost_debit,0);
1262 l_total_net_debit := GREATEST(l_total_cost_debit - l_total_cost_credit,0);
1263 hr_utility.set_location('l_total_cost_debit'||l_total_cost_debit,51);
1264 IF( l_total_net_credit <> 0 ) THEN
1265 xml_tab(l_counter).TagName :='COST_SEG_NET_TOT_CREDIT';
1266 xml_tab(l_counter).TagValue := l_total_net_credit;
1267 l_counter := l_counter + 1;
1268 END IF;
1269 hr_utility.set_location('l_total_net_debit'||l_total_net_debit,52);
1270 IF( l_total_net_debit <> 0 ) THEN
1271 xml_tab(l_counter).TagName :='COST_SEG_NET_TOT_DEBIT';
1272 xml_tab(l_counter).TagValue := l_total_net_debit;
1273 l_counter := l_counter + 1;
1274 END IF;
1275 -- End of bug fix 5244886
1276 xml_tab(l_counter).TagName :='COST_SEG_GRP_START';
1277 xml_tab(l_counter).TagValue :='COST_SEG_GRP_END';
1278 l_counter := l_counter + 1;
1279
1280 xml_tab(l_counter).TagName :='COST_RECO_START';
1281 xml_tab(l_counter).TagValue :='COST_RECO_END';
1282 l_counter := l_counter + 1;
1283
1284 xml_tab(l_counter).TagName :='FILE_HEADER_START';
1285 xml_tab(l_counter).TagValue :='FILE_HEADER_END';
1286 l_counter := l_counter + 1;
1287
1288 hr_utility.set_location('After populating pl/sql table',30);
1289 hr_utility.set_location('Entered Procedure GETDATA',10);
1290
1291 WritetoCLOB (p_xml );
1292
1293 END POPULATE_DATA_SUMMARY;
1294 --
1295 --
1296 --
1297 PROCEDURE POPULATE_DATA_DETAIL
1298 (p_business_group_id IN NUMBER,
1299 p_payroll_action_id IN VARCHAR2 ,
1300 p_template_name IN VARCHAR2,
1301 p_xml OUT NOCOPY CLOB)
1302 IS
1303
1304
1305 /* Cursor to fetch Header Information */
1306 CURSOR csr_get_hdr_info(p_payroll_action_id NUMBER)
1307 IS
1308 SELECT *
1309 FROM pay_action_information pai
1310 WHERE action_context_type = 'PA'
1311 AND action_context_id = p_payroll_action_id
1312 AND action_information_category = 'EMEA REPORT DETAILS'
1313 AND action_information1 = 'PYNOPACCA'
1314 AND action_information4 = 'HDR';
1315
1316
1317 /* Cursors to fetch Costing Detail Information */
1318 CURSOR csr_get_det_info(p_payroll_action_id NUMBER, p_tax_unit_id NUMBER)
1319 IS
1320 SELECT action_information7 --ELE_TYPE_ID
1321 ,action_information8 --ELE_NAME
1322 ,action_information9 --ELE_EOY_CODE
1323 ,assignment_id --ASSIGNMENT_ID
1324 ,action_information5 --ASSIGNMENT_NUMBER
1325 ,action_information2 --PAYROLL_NAME
1326 ,action_information10 --ELE_COST_SEG
1327 ,action_information11 --COST_FLEX_ID
1328 ,action_information12 --DEBIT_CREDIT
1329 ,action_information14 --COSTED_VALUE
1330 ,effective_date --EFFECTIVE_DATE
1331 ,action_information15 --COST_OR_BAL
1332 ,action_information13 --ELE_CODE
1333 ,action_information16 --COST_EFFECTIVE_DATE
1334 ,action_information17 --EMP_NAME
1335 FROM pay_action_information pai
1336 WHERE action_context_type = 'AAP'
1337 AND action_information3 = to_char(p_payroll_action_id)
1338 AND action_information_category = 'EMEA REPORT INFORMATION'
1339 AND action_information1 = 'PYNOPACCA'
1340 AND tax_unit_id = p_tax_unit_id
1341 AND action_information4 = 'DETL'
1342 ORDER BY action_information10
1343 ,action_information9
1344 ,action_information8
1345 ,action_information15
1346 ,action_information12
1347 ,action_information5
1348 ,fnd_date.date_to_canonical(action_information16)
1349 ,fnd_number.canonical_to_number(action_information14)
1350 ,effective_date;
1351
1352
1353 rec_get_hdr_info csr_get_hdr_info%ROWTYPE;
1354
1355 l_counter NUMBER := 0;
1356 l_count NUMBER := 0;
1357 l_payroll_action_id NUMBER;
1358
1359 l_prev_cost_seg VARCHAR2(80) := ' ';
1360 l_prev_eoy_code VARCHAR2(80) := ' ';
1361 l_total_cost_credit NUMBER := 0;
1362 l_total_cost_debit NUMBER := 0;
1363
1364
1365 BEGIN
1366 --
1367 IF p_payroll_action_id IS NULL THEN
1368 --
1369 BEGIN
1370 SELECT payroll_action_id
1371 INTO l_payroll_action_id
1372 FROM pay_payroll_actions ppa,
1373 fnd_conc_req_summary_v fcrs,
1374 fnd_conc_req_summary_v fcrs1
1375 WHERE fcrs.request_id = fnd_global.conc_request_id
1376 AND fcrs.priority_request_id = fcrs1.priority_request_id
1377 AND ppa.request_id between fcrs1.request_id and fcrs.request_id
1378 AND ppa.request_id = fcrs1.request_id;
1379 EXCEPTION WHEN OTHERS THEN
1380 NULL;
1381 END ;
1382 --
1383 ELSE
1384 l_payroll_action_id := p_payroll_action_id;
1385 END IF;
1386 hr_utility.set_location('Entered Procedure GETDATA',10);
1387 --
1391 CLOSE csr_get_hdr_info;
1388 /* Get the File Header Information */
1389 OPEN csr_get_hdr_info(l_payroll_action_id);
1390 FETCH csr_get_hdr_info INTO rec_get_hdr_info;
1392 --
1393 hr_utility.set_location('Before populating pl/sql table',20);
1394 --
1395 xml_tab(l_counter).TagName :='FILE_HEADER_START';
1396 xml_tab(l_counter).TagValue :='FILE_HEADER_START';
1397 l_counter := l_counter + 1;
1398 --
1399 hr_utility.set_location('LE_NAME'||rec_get_hdr_info.action_information7,53);
1400 xml_tab(l_counter).TagName :='LE_NAME';
1401 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information7;
1402 l_counter := l_counter + 1;
1403 --
1404 xml_tab(l_counter).TagName :='LE_ORG_NO';
1405 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information8;
1406 l_counter := l_counter + 1;
1407 --
1408 xml_tab(l_counter).TagName :='REPORT_START_DATE';
1409 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information12;
1410 l_counter := l_counter + 1;
1411 --
1412 xml_tab(l_counter).TagName :='REPORT_END_DATE';
1413 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information13;
1414 l_counter := l_counter + 1;
1415 --
1416 xml_tab(l_counter).TagName :='PARAM_COST_SEG';
1417 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information9;
1418 l_counter := l_counter + 1;
1419 --
1420 xml_tab(l_counter).TagName :='PARAM_EOY_CODE';
1421 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information10;
1422 l_counter := l_counter + 1;
1423 --
1424 xml_tab(l_counter).TagName :='PARAM_RESTR_ECONTR';
1425 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information14;
1426 l_counter := l_counter + 1;
1427 --
1428 xml_tab(l_counter).TagName :='PARAM_PAYROLL_NAME';
1429 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information15;
1430 l_counter := l_counter + 1;
1431 --
1432 xml_tab(l_counter).TagName :='PARAM_ELE_NAME';
1433 xml_tab(l_counter).TagValue := rec_get_hdr_info.action_information16;
1434 l_counter := l_counter + 1;
1435 --
1436 xml_tab(l_counter).TagName :='COST_RECO_START';
1437 xml_tab(l_counter).TagValue :='COST_RECO_START';
1438 l_counter := l_counter + 1;
1439 hr_utility.set_location('HEADER INFO DONE',54);
1440 --
1441 hr_utility.set_location('CSR csr_get_det_info',55);
1442 FOR rec_get_det_info IN csr_get_det_info(l_payroll_action_id,rec_get_hdr_info.tax_unit_id)
1443 LOOP
1444 --
1445 /* Counter to count records fetched */
1446 hr_utility.set_location('record found csr_get_det_info',56);
1447 hr_utility.set_location('l_count'||l_count,57);
1448 l_count := l_count + 1 ;
1449 /*Check cost_seg for grouping */
1450 hr_utility.set_location('rec_get_det_info.action_information10'||rec_get_det_info.action_information10,58);
1451 hr_utility.set_location('l_prev_cost_seg'||l_prev_cost_seg,59);
1452 IF(l_count = 1 OR rec_get_det_info.action_information10 <> l_prev_cost_seg ) THEN
1453 --
1454 IF(l_count <> 1) THEN
1455 xml_tab(l_counter).TagName :='EOY_CODE_GRP_START';
1456 xml_tab(l_counter).TagValue :='EOY_CODE_GRP_END';
1457 l_counter := l_counter + 1;
1458 --
1459 hr_utility.set_location('l_total_cost_credit'||l_total_cost_credit,61);
1460 IF( l_total_cost_credit <> 0 ) THEN
1461 xml_tab(l_counter).TagName :='COST_SEG_GRP_TOT_CREDIT';
1462 xml_tab(l_counter).TagValue := l_total_cost_credit;
1463 l_counter := l_counter + 1;
1464 END IF;
1465 --
1466 hr_utility.set_location('l_total_cost_debit'||l_total_cost_debit,62);
1467 IF( l_total_cost_debit <> 0 ) THEN
1468 xml_tab(l_counter).TagName :='COST_SEG_GRP_TOT_DEBIT';
1469 xml_tab(l_counter).TagValue := l_total_cost_debit;
1470 l_counter := l_counter + 1;
1471 END IF;
1472 --
1473 xml_tab(l_counter).TagName :='COST_SEG_GRP_START';
1474 xml_tab(l_counter).TagValue :='COST_SEG_GRP_END';
1475 l_counter := l_counter + 1;
1476 END IF;
1477 --
1478 l_total_cost_credit :=0;
1479 l_total_cost_debit :=0;
1480 --
1481 xml_tab(l_counter).TagName :='COST_SEG_GRP_START';
1482 xml_tab(l_counter).TagValue :='COST_SEG_GRP_START';
1483 l_counter := l_counter + 1;
1484 --
1485 xml_tab(l_counter).TagName :='EOY_CODE_GRP_START';
1486 xml_tab(l_counter).TagValue :='EOY_CODE_GRP_START';
1487 l_counter := l_counter + 1;
1488 /*Check eoy_code for grouping if no change in cost segments */
1489 ELSIF( l_count = 1 OR rec_get_det_info.action_information9 <> l_prev_eoy_code ) THEN
1490 --
1491 hr_utility.set_location('ELSIF',63);
1492 hr_utility.set_location('l_count'||l_count,64);
1493 IF(l_count <> 1) THEN
1494 xml_tab(l_counter).TagName :='EOY_CODE_GRP_START';
1495 xml_tab(l_counter).TagValue :='EOY_CODE_GRP_END';
1496 l_counter := l_counter + 1;
1497 END IF;
1498 --
1499 xml_tab(l_counter).TagName :='EOY_CODE_GRP_START';
1500 xml_tab(l_counter).TagValue :='EOY_CODE_GRP_START';
1501 l_counter := l_counter + 1;
1502 --
1503 END IF;
1504 --
1505 xml_tab(l_counter).TagName :='COSTING_START';
1506 xml_tab(l_counter).TagValue :='COSTING_START';
1507 l_counter := l_counter + 1;
1508 -- Suppress same Cost Flexfield value appearing in a group
1509 hr_utility.set_location('l_count'||l_count,65);
1510 hr_utility.set_location('rec_get_det_info.action_information10'||rec_get_det_info.action_information10,66);
1511 hr_utility.set_location('l_prev_cost_seg'||l_prev_cost_seg,67);
1512 IF( l_count = 1 OR rec_get_det_info.action_information10 <> l_prev_cost_seg ) THEN
1516 END IF;
1513 xml_tab(l_counter).TagName :='COST_SEG';
1514 xml_tab(l_counter).TagValue := rec_get_det_info.action_information10;
1515 l_counter := l_counter + 1;
1517 --
1518 hr_utility.set_location('EOY_CODE'||rec_get_det_info.action_information9,68);
1519 xml_tab(l_counter).TagName :='EOY_CODE';
1520 xml_tab(l_counter).TagValue := rec_get_det_info.action_information9;
1521 l_counter := l_counter + 1;
1522 --
1523 hr_utility.set_location('ELE_CODE'||rec_get_det_info.action_information13,69);
1524 xml_tab(l_counter).TagName :='ELE_CODE';
1525 xml_tab(l_counter).TagValue := rec_get_det_info.action_information13;
1526 --
1527 hr_utility.set_location('rec_get_det_info.action_information13'||rec_get_det_info.action_information13,71);
1528 IF( rec_get_det_info.action_information13 IS NOT NULL) THEN
1529 xml_tab(l_counter).TagValue := ' ,' ||xml_tab(l_counter).TagValue;
1530 END IF;
1531 l_counter := l_counter + 1;
1532 --
1533 hr_utility.set_location('ELE_NAME'||rec_get_det_info.action_information8,72);
1534 xml_tab(l_counter).TagName :='ELE_NAME';
1535 xml_tab(l_counter).TagValue := rec_get_det_info.action_information8;
1536 l_counter := l_counter + 1;
1537 --
1538 hr_utility.set_location('ASSG_NO'||rec_get_det_info.action_information5,73);
1539 xml_tab(l_counter).TagName :='ASSG_NO';
1540 xml_tab(l_counter).TagValue := rec_get_det_info.action_information5;
1541 l_counter := l_counter + 1;
1542 --
1543 hr_utility.set_location('EMP_NAME'||rec_get_det_info.action_information17,74);
1544 xml_tab(l_counter).TagName :='EMP_NAME';
1545 xml_tab(l_counter).TagValue := rec_get_det_info.action_information17;
1546 l_counter := l_counter + 1;
1547 --
1548 hr_utility.set_location('COST_OR_BAL'||rec_get_det_info.action_information15,75);
1549 xml_tab(l_counter).TagName :='COST_OR_BAL';
1550 xml_tab(l_counter).TagValue := rec_get_det_info.action_information15;
1551 l_counter := l_counter + 1;
1552 --
1553 hr_utility.set_location('COST_DEBIT/COST_CREDIT'||fnd_number.canonical_to_number(rec_get_det_info.action_information14),76);
1554 hr_utility.set_location('C/D'||rec_get_det_info.action_information12,77);
1555 IF( rec_get_det_info.action_information12 = 'D') THEN
1556 xml_tab(l_counter).TagName :='COST_DEBIT';
1557 xml_tab(l_counter).TagValue := fnd_number.canonical_to_number(rec_get_det_info.action_information14);
1558 l_counter := l_counter + 1;
1559 l_total_cost_debit := l_total_cost_debit + fnd_number.canonical_to_number(rec_get_det_info.action_information14);
1560 ELSIF( rec_get_det_info.action_information12 ='C') THEN
1561 xml_tab(l_counter).TagName :='COST_CREDIT';
1562 xml_tab(l_counter).TagValue := fnd_number.canonical_to_number(rec_get_det_info.action_information14);
1563 l_counter := l_counter + 1;
1564 l_total_cost_credit := l_total_cost_credit + fnd_number.canonical_to_number(rec_get_det_info.action_information14);
1565 END IF;
1566 hr_utility.set_location('l_total_cost_debit'||l_total_cost_debit,78);
1567 hr_utility.set_location('l_total_cost_credit'||l_total_cost_credit,79);
1568 --
1569 hr_utility.set_location('COST_EFF_DATE'||rec_get_det_info.action_information16,81);
1570 xml_tab(l_counter).TagName :='COST_EFF_DATE';
1571 xml_tab(l_counter).TagValue := rec_get_det_info.action_information16;
1572 l_counter := l_counter + 1;
1573 --
1574 xml_tab(l_counter).TagName :='COSTING_START';
1575 xml_tab(l_counter).TagValue :='COSTING_END';
1576 l_counter := l_counter + 1;
1577 --
1578 l_prev_eoy_code := rec_get_det_info.action_information9;
1579 l_prev_cost_seg := rec_get_det_info.action_information10;
1580 END LOOP;
1581 hr_utility.set_location('END LOOP',82);
1582 hr_utility.set_location('l_count'||l_count,83);
1583 --
1584 IF(l_count = 0) THEN
1585 xml_tab(l_counter).TagName :='COST_SEG_GRP_START';
1586 xml_tab(l_counter).TagValue :='COST_SEG_GRP_START';
1587 l_counter := l_counter + 1;
1588 --
1589 xml_tab(l_counter).TagName :='EOY_CODE_GRP_START';
1590 xml_tab(l_counter).TagValue :='EOY_CODE_GRP_START';
1591 l_counter := l_counter + 1;
1592 END IF;
1593 --
1594 xml_tab(l_counter).TagName :='EOY_CODE_GRP_START';
1595 xml_tab(l_counter).TagValue :='EOY_CODE_GRP_END';
1596 l_counter := l_counter + 1;
1597 --
1598 hr_utility.set_location('l_total_cost_credit'||l_total_cost_credit,84);
1599 IF( l_total_cost_credit <> 0 ) THEN
1600 xml_tab(l_counter).TagName :='COST_SEG_GRP_TOT_CREDIT';
1601 xml_tab(l_counter).TagValue := l_total_cost_credit;
1602 l_counter := l_counter + 1;
1603 END IF;
1604 --
1605 hr_utility.set_location('l_total_cost_debit'||l_total_cost_debit,85);
1606 IF( l_total_cost_debit <> 0 ) THEN
1607 xml_tab(l_counter).TagName :='COST_SEG_GRP_TOT_DEBIT';
1608 xml_tab(l_counter).TagValue := l_total_cost_debit;
1609 l_counter := l_counter + 1;
1610 END IF;
1611 --
1612 xml_tab(l_counter).TagName :='COST_SEG_GRP_START';
1613 xml_tab(l_counter).TagValue :='COST_SEG_GRP_END';
1614 l_counter := l_counter + 1;
1615 --
1616 xml_tab(l_counter).TagName :='COST_RECO_START';
1617 xml_tab(l_counter).TagValue :='COST_RECO_END';
1618 l_counter := l_counter + 1;
1619 --
1620 xml_tab(l_counter).TagName :='FILE_HEADER_START';
1621 xml_tab(l_counter).TagValue :='FILE_HEADER_END';
1622 l_counter := l_counter + 1;
1623 --
1624 hr_utility.set_location('After populating pl/sql table',30);
1625 hr_utility.set_location('Entered Procedure GETDATA',10);
1626 --
1627 WritetoCLOB (p_xml );
1628 --
1629 END POPULATE_DATA_DETAIL;
1630 --
1631 --
1632 --
1633 PROCEDURE WritetoCLOB(p_xfdf_clob out nocopy CLOB) is
1634 l_xfdf_string clob;
1635 l_str1 varchar2(1000);
1636 l_str2 varchar2(20);
1637 l_str3 varchar2(20);
1638 l_str4 varchar2(20);
1639 l_str5 varchar2(20);
1640 l_str6 varchar2(30);
1641 l_str7 varchar2(1000);
1642 l_str8 varchar2(240);
1643 l_str9 varchar2(240);
1644 l_str10 varchar2(20);
1645 l_str11 varchar2(20);
1646 l_IANA_charset VARCHAR2 (50);
1647
1648 current_index pls_integer;
1649
1650 BEGIN
1651
1652 hr_utility.set_location('Entering WritetoCLOB ',10);
1653
1654
1655 l_IANA_charset := HR_NO_UTILITY.get_IANA_charset ;
1656 l_str1 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT><PAACR>';
1657 l_str2 := '<';
1658 l_str3 := '>';
1659 l_str4 := '</';
1660 l_str5 := '>';
1661 l_str6 := '</PAACR></ROOT>';
1662 l_str7 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT></ROOT>';
1663 l_str10 := '<PAACR>';
1664 l_str11 := '</PAACR>';
1665
1666
1667 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
1668 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
1669
1670 current_index := 0;
1671
1672 IF xml_tab.count > 0 THEN
1673
1674 dbms_lob.writeAppend( l_xfdf_string, length(l_str1), l_str1 );
1675
1676
1677 FOR table_counter IN xml_tab.FIRST .. xml_tab.LAST LOOP
1678
1679 l_str8 := xml_tab(table_counter).TagName;
1680 l_str9 := xml_tab(table_counter).TagValue;
1681
1682 IF l_str9 IN ('FILE_HEADER_START', 'FILE_HEADER_END','COST_RECO_START','COST_RECO_END'
1683 ,'COST_SEG_GRP_START','COST_SEG_GRP_END','EOY_CODE_GRP_START','EOY_CODE_GRP_END','COSTING_START','COSTING_END') THEN
1684
1685 IF l_str9 IN ('FILE_HEADER_START','COST_RECO_START','EOY_CODE_GRP_START','COST_SEG_GRP_START','COSTING_START') THEN
1686 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
1687 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1688 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
1689 ELSE
1690 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
1691 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1692 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
1693 END IF;
1694
1695 ELSE
1696
1697 if l_str9 is not null then
1698
1699 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
1700 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1701 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
1702 dbms_lob.writeAppend(l_xfdf_string, length(l_str9), l_str9);
1703 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
1704 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1705 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
1706 else
1707
1708 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
1709 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1710 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
1711 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
1712 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
1713 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
1714
1715 end if;
1716
1717 END IF;
1718
1719 END LOOP;
1720
1721 dbms_lob.writeAppend(l_xfdf_string, length(l_str6), l_str6 );
1722
1723 ELSE
1724 dbms_lob.writeAppend(l_xfdf_string, length(l_str7), l_str7 );
1725 END IF;
1726
1727 p_xfdf_clob := l_xfdf_string;
1728
1729 hr_utility.set_location('Leaving WritetoCLOB ',20);
1730
1731 EXCEPTION
1732 WHEN OTHERS then
1733 HR_UTILITY.TRACE('sqlerrm ' || SQLERRM);
1734 HR_UTILITY.RAISE_ERROR;
1735 END WritetoCLOB;
1736
1737
1738 END PAY_NO_PACCR_PKG;