[Home] [Help]
PACKAGE BODY: APPS.PAY_SE_PAYSLIP_REPORT
Source
1 PACKAGE BODY pay_se_payslip_report AS
2 /* $Header: pysepsrp.pkb 120.0.12010000.2 2008/08/06 08:18:01 ubhat ship $ */
3 --
4 -- Globals
5 --
6 g_pa_token VARCHAR2(50);
7 g_cs_token VARCHAR2(50);
8 -------------------------------------------------------------------------------
9 -- GET_PARAMETER
10 -------------------------------------------------------------------------------
11 FUNCTION get_parameter(p_parameter_string IN VARCHAR2
12 ,p_token IN VARCHAR2
13 ,p_segment_number IN NUMBER DEFAULT NULL ) RETURN VARCHAR2
14 IS
15 --
16 l_parameter pay_payroll_actions.legislative_parameters%TYPE := NULL;
17 l_start_pos NUMBER;
18 l_delimiter varchar2(1) := ' ';
19 --
20 BEGIN
21 --
22 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
23 --
24 IF l_start_pos = 0 THEN
25 l_delimiter := '|';
26 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
27 END IF;
28
29 IF l_start_pos <> 0 THEN
30 l_start_pos := l_start_pos + length(p_token||'=');
31 l_parameter := substr(p_parameter_string,
32 l_start_pos,
33 instr(p_parameter_string||' ',
34 l_delimiter,l_start_pos)
35 - l_start_pos);
36 IF p_segment_number IS NOT NULL THEN
37 l_parameter := ':'||l_parameter||':';
38 l_parameter := substr(l_parameter,
39 instr(l_parameter,':',1,p_segment_number)+1,
40 instr(l_parameter,':',1,p_segment_number+1) -1
41 - instr(l_parameter,':',1,p_segment_number));
42 END IF;
43 END IF;
44 --
45 RETURN l_parameter;
46 END get_parameter;
47 --
48 -------------------------------------------------------------------------------
49 -- GET_ALL_PARAMETERS Gets data from Legislative Parameters in Pay_payroll_actions
50 -------------------------------------------------------------------------------
51 PROCEDURE get_all_parameters(p_payroll_action_id IN NUMBER
52 ,p_payroll_id OUT NOCOPY NUMBER
53 ,p_consolidation_set_id OUT NOCOPY NUMBER
54 ,p_start_date OUT NOCOPY VARCHAR2
55 ,p_end_date OUT NOCOPY VARCHAR2
56 ,p_rep_group OUT NOCOPY VARCHAR2
57 ,p_rep_category OUT NOCOPY VARCHAR2
58 ,p_assignment_set_id OUT NOCOPY NUMBER
59 ,p_assignment_id OUT NOCOPY NUMBER
60 ,p_effective_date OUT NOCOPY DATE
61 ,p_business_group_id OUT NOCOPY NUMBER
62 ,p_legislation_code OUT NOCOPY VARCHAR2 ) IS
63 --
64 CURSOR csr_parameter_info(c_payroll_action_id NUMBER) IS
65 SELECT get_parameter(ppa.legislative_parameters,'PAYROLL_ID')
66 ,get_parameter(ppa.legislative_parameters,'CONSOLIDATION_SET_ID')
67 ,get_parameter(ppa.legislative_parameters,'START_DATE')
68 ,get_parameter(ppa.legislative_parameters,'END_DATE')
69 ,get_parameter(ppa.legislative_parameters,'REP_GROUP')
70 ,get_parameter(ppa.legislative_parameters,'REP_CAT')
71 ,get_parameter(ppa.legislative_parameters,'ASSIGNMENT_SET_ID')
72 ,get_parameter(ppa.legislative_parameters,'ASSIGNMENT_ID')
73 ,ppa.effective_date
74 ,ppa.business_group_id
75 ,pbg.legislation_code
76 FROM pay_payroll_actions ppa
77 ,per_business_groups pbg
78 WHERE ppa.payroll_action_id = c_payroll_action_id
79 AND ppa.business_group_id = pbg.business_group_id;
80 --
81 BEGIN
82 --
83 OPEN csr_parameter_info (p_payroll_action_id);
84 FETCH csr_parameter_info INTO p_payroll_id
85 ,p_consolidation_set_id
86 ,p_start_date
87 ,p_end_date
88 ,p_rep_group
89 ,p_rep_category
90 ,p_assignment_set_id
91 ,p_assignment_id
92 ,p_effective_date
93 ,p_business_group_id
94 ,p_legislation_code ;
95 CLOSE csr_parameter_info;
96 --
97 END get_all_parameters;
98 --
99 --------------------------------------------------------------------------------
100 -- QUALIFYING_PROC
101 --------------------------------------------------------------------------------
102 PROCEDURE qualifying_proc(p_assignment_id IN NUMBER
103 ,p_qualifier OUT NOCOPY VARCHAR2 ) IS
104 --
105 l_actid NUMBER;
106 l_rep_group pay_report_groups.report_group_name%TYPE;
107 l_rep_category pay_report_categories.category_name%TYPE;
108 l_effective_date DATE;
109 l_business_group_id NUMBER;
110 l_assignment_set_id NUMBER;
111 l_assignment_id NUMBER;
112 l_inc_exc VARCHAR2(1);
113 l_asg_inc_exc VARCHAR2(1);
114 --
115 l_payroll_id NUMBER;
116 l_consolidation_set_id NUMBER;
117 l_start_date VARCHAR2(20);
118 l_end_date VARCHAR2(20);
119 l_legislation_code VARCHAR2(10);
120 l_start_dt DATE;
121 l_end_dt DATE;
122 l_qualifier VARCHAR2(1);
123 --
124 sql_cur NUMBER;
125 l_rows NUMBER;
126 statem VARCHAR2(256);
127 --
128 CURSOR csr_asg(c_assignment_id NUMBER
129 ,c_payroll_id NUMBER
130 ,c_consolidation_set_id NUMBER
131 ,c_start_date DATE
132 ,c_end_date DATE
133 ,c_pa_token VARCHAR2
134 ,c_cs_token VARCHAR2
135 ,c_legislation_code VARCHAR2) IS
136 SELECT 'Y'
137 FROM pay_assignment_actions paa
138 ,pay_payroll_actions ppa
139 ,hr_lookups hrl
140 ,pay_action_information pai
141 ,per_time_periods ptp
142
143 WHERE paa.assignment_id = c_assignment_id
144 AND paa.payroll_action_id = ppa.payroll_action_id
145 AND paa.source_action_id IS NULL
146 AND ppa.effective_Date BETWEEN c_start_date
147 AND c_end_date
148 AND ppa.report_type = hrl.meaning
149 AND hrl.lookup_type = 'PAYSLIP_REPORT_TYPES'
150 AND hrl.lookup_code = c_legislation_code
151 AND NVL(c_payroll_id,NVL(get_parameter(ppa.legislative_parameters,c_pa_token),-1)) = NVL(get_parameter(ppa.legislative_parameters,c_pa_token),-1)
152 AND c_consolidation_set_id = get_parameter(ppa.legislative_parameters,c_cs_token)
153 AND pai.assignment_id = paa.assignment_id
154 AND pai.action_context_type = 'AAP'
155 AND pai.action_information_category = 'EMPLOYEE DETAILS'
156 AND pai.action_context_id = paa.assignment_action_id
157 AND ptp.time_period_id = pai.ACTION_INFORMATION16
158 AND pay_us_employee_payslip_web.check_emp_personal_payment
159 ( paa.assignment_id, ptp.payroll_id, pai.action_information16,
160 pai.action_context_id, pai.effective_date) = 'Y';
161
162 --
163 CURSOR csr_inc_asg(c_assignment_id NUMBER
164 ,c_payroll_id NUMBER
165 ,c_consolidation_set_id NUMBER
166 ,c_start_date DATE
167 ,c_end_date DATE
168 ,c_pa_token VARCHAR2
169 ,c_cs_token VARCHAR2
170 ,c_legislation_code VARCHAR2
171 ,c_assignment_set_id NUMBER ) IS
172 SELECT 'Y'
173 FROM pay_assignment_actions paa
174 ,pay_payroll_actions ppa
175 ,hr_assignment_set_amendments hasa
176 ,hr_lookups hrl
177 ,pay_action_information pai
178 ,per_time_periods ptp
179
180 WHERE paa.assignment_id = c_assignment_id
181 AND paa.payroll_action_id = ppa.payroll_action_id
182 AND paa.source_action_id IS NULL
183 AND ppa.effective_Date BETWEEN c_start_date
184 AND c_end_date
185 AND ppa.report_type = hrl.meaning
186 AND hrl.lookup_type = 'PAYSLIP_REPORT_TYPES'
187 AND hrl.lookup_code = c_legislation_code
188 AND NVL(c_payroll_id,NVL(get_parameter(ppa.legislative_parameters,c_pa_token),-1)) = NVL(get_parameter(ppa.legislative_parameters,c_pa_token),-1)
189 AND c_consolidation_set_id = get_parameter(ppa.legislative_parameters,c_cs_token)
190 AND paa.assignment_id = hasa.assignment_id
191 AND hasa.assignment_set_id = c_assignment_set_id
192 AND hasa.include_or_exclude = 'I'
193 AND pai.assignment_id = paa.assignment_id
194 AND pai.action_context_type = 'AAP'
195 AND pai.action_information_category = 'EMPLOYEE DETAILS'
196 AND pai.action_context_id = paa.assignment_action_id
197 AND ptp.time_period_id = pai.ACTION_INFORMATION16
198 AND pay_us_employee_payslip_web.check_emp_personal_payment
199 ( paa.assignment_id, ptp.payroll_id, pai.action_information16,
200 pai.action_context_id, pai.effective_date) = 'Y' ;
201
202 --
203 -- The Assignment Set Logic is handled only for either Include or Exclude
204 -- and not for both. This doesn't handle the assignment_set_criteria.
205 --
206 CURSOR csr_inc_exc(c_assignment_set_id NUMBER
207 ,c_assignment_id NUMBER) IS
208 SELECT include_or_exclude
209 FROM hr_assignment_set_amendments
210 WHERE assignment_set_id = c_assignment_set_id
211 AND assignment_id = nvl(c_assignment_id,assignment_id);
212 --
213 BEGIN
214 --hr_utility.trace('###### IN Qualifying Proc');
215 --
216 Fnd_file.put_line(FND_FILE.LOG,'######## IN Qualifying Proc');
217 l_actid := pay_proc_environment_pkg.get_pactid;
218 --
219 get_all_parameters(l_actid
220 ,l_payroll_id
221 ,l_consolidation_set_id
222 ,l_start_date
223 ,l_end_date
224 ,l_rep_group
225 ,l_rep_category
226 ,l_assignment_set_id
227 ,l_assignment_id
228 ,l_effective_date
229 ,l_business_group_id
230 ,l_legislation_code);
231 --
232 l_start_dt := TO_DATE(l_start_date,'YYYY/MM/DD');
233 l_end_dt := TO_DATE(l_end_date,'YYYY/MM/DD');
234 --EXECUTE IMMEDIATE 'SELECT pay_'||l_legislation_code||'_rules.get_payroll_token FROM DUAL' into l_token;
235 --
236 IF g_pa_token IS NULL THEN
237 DECLARE
238 BEGIN
239 statem := 'BEGIN pay_'||l_legislation_code||'_rules.get_token_names(:p_pa_token, :p_cs_token); END;';
240 --hr_utility.trace(statem);
241 sql_cur := dbms_sql.open_cursor;
242 dbms_sql.parse(sql_cur
243 ,statem
244 ,dbms_sql.v7);
245 dbms_sql.bind_variable(sql_cur, 'p_pa_token', g_pa_token, 50);
246 dbms_sql.bind_variable(sql_cur, 'p_cs_token', g_cs_token, 50);
247 l_rows := dbms_sql.execute(sql_cur);
248 dbms_sql.variable_value(sql_cur, 'p_pa_token', g_pa_token);
249 dbms_sql.variable_value(sql_cur, 'p_cs_token', g_cs_token);
250 dbms_sql.close_cursor(sql_cur);
251 Exception
252 WHEN OTHERS THEN
253 g_pa_token := NVL(g_pa_token,'PAYROLL_ID');
254 g_cs_token := NVL(g_cs_token,'CONSOLIDATION_SET_ID');
255 --
256 IF dbms_sql.IS_OPEN(sql_cur) THEN
257 dbms_sql.close_cursor(sql_cur);
258 END IF;
259 END;
260 END IF;
261 --
262 IF l_assignment_id IS NOT NULL THEN
263 IF l_assignment_id = p_assignment_id THEN
264 --
265 p_qualifier := 'Y' ;
266 --
267 END IF;
268 ELSE
269 --
270 --hr_utility.trace('###### 1.p_assignment_id '||p_assignment_id);
271 --
272 IF l_assignment_set_id IS NOT NULL THEN
273 OPEN csr_inc_exc(l_assignment_set_id
274 ,NULL);
275 FETCH csr_inc_exc INTO l_inc_exc;
276 CLOSE csr_inc_exc;
277 END IF;
278 --
279 IF l_assignment_set_id IS NULL OR nvl(l_inc_exc,'E') = 'E' THEN
280 OPEN csr_asg(p_assignment_id
281 ,l_payroll_id
282 ,l_consolidation_set_id
283 ,l_start_dt
284 ,l_end_dt
285 ,g_pa_token
286 ,g_cs_token
287 ,l_legislation_code );
288 FETCH csr_asg INTO l_qualifier;
289 CLOSE csr_asg;
290 --
291 IF l_assignment_set_id IS NOT NULL THEN
292 OPEN csr_inc_exc(l_assignment_set_id
293 ,p_assignment_id);
294 FETCH csr_inc_exc INTO l_asg_inc_exc;
295 CLOSE csr_inc_exc;
296 END IF;
297 --
298 --hr_utility.trace('###### 2.l_asg_inc_exc '||l_asg_inc_exc);
299 --hr_utility.trace('###### 2.l_qualifier '||l_qualifier);
300 --
301 IF NVL(l_asg_inc_exc,'X') <> 'E' AND l_qualifier = 'Y' THEN
302 --
303 p_qualifier := 'Y' ;
304 --
305 END IF;
306 ELSIF l_inc_exc = 'I' THEN
307 OPEN csr_inc_asg(p_assignment_id
308 ,l_payroll_id
309 ,l_consolidation_set_id
310 ,l_start_dt
311 ,l_end_dt
312 ,g_pa_token
313 ,g_cs_token
314 ,l_legislation_code
315 ,l_assignment_set_id );
316 FETCH csr_inc_asg INTO l_qualifier;
317 CLOSE csr_inc_asg;
318 --
319 IF l_qualifier = 'Y' THEN
320 p_qualifier := 'Y' ;
321 END IF;
322 --
323 END IF;
324 --
325 END IF;
326 --
327 END qualifying_proc;
328 --------------------------------------------------------------------------------
329 -- xml_header
330 --------------------------------------------------------------------------------
331 PROCEDURE xml_header IS
332 l_final_xml_string VARCHAR2(32000) := NULL;
333 EOL VARCHAR2(5);
334
335
336 l_actid NUMBER;
337 l_payroll_id NUMBER;
338 l_consolidation_set_id NUMBER;
339 l_start_date VARCHAR2(20);
340 l_end_date VARCHAR2(20);
341 l_legislation_code VARCHAR2(10);
342 l_rep_group pay_report_groups.report_group_name%TYPE;
343 l_rep_category pay_report_categories.category_name%TYPE;
344 l_effective_date DATE;
345 l_business_group_id NUMBER;
346 l_assignment_set_id NUMBER;
347 l_assignment_id NUMBER;
348
349 l_sender VARCHAR2(50) := NULL;
350 l_receiver VARCHAR2(50) := NULL;
351 l_message VARCHAR2(50) := NULL;
352 l_string VARCHAR2(200);
353 ------
354
355 CURSOR csr_assg_id(p_payroll_id NUMBER,p_start_date VARCHAR2, p_end_date VARCHAR2)
356 IS
357 SELECT PAF.ASSIGNMENT_ID assignment_id
358 FROM PER_ALL_ASSIGNMENTS_F PAF,
359 PAY_ASSIGNMENT_ACTIONS PAA,
360 PAY_PAYROLL_ACTIONS PPA
361 WHERE PAF.payroll_id=p_payroll_id
362 AND PPA.PAYROLL_ACTION_ID=PAA.PAYROLL_ACTION_ID
363 AND PAF.ASSIGNMENT_ID=PAA.ASSIGNMENT_ID
364 AND PPA.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE AND PAF.EFFECTIVE_END_DATE
365 AND PPA.EFFECTIVE_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(p_start_date)
366 AND FND_DATE.CANONICAL_TO_DATE(p_end_date)
367 AND ppa.ACTION_TYPE ='X'
368 AND ROWNUM < 2;
369
370 CURSOR csr_payroll_post_header(l_payroll_id NUMBER,l_legislation_code VARCHAR2 ) IS
371
372 SELECT PRL_INFORMATION1,PRL_INFORMATION2,PRL_INFORMATION3
373 FROM pay_payrolls_f
374 WHERE PAYROLL_ID = l_payroll_id
375 AND PRL_INFORMATION_CATEGORY = l_legislation_code;
376
377 lr_payroll_post_header csr_payroll_post_header%ROWTYPE;
378
379
380
381 CURSOR csr_local_unit_id(p_assignment_id NUMBER, p_effective_date DATE) IS
382 SELECT SCL.segment2 local_unit_id
383 , business_group_id
384 FROM
385 per_all_assignments_f PAA ,
386 hr_soft_coding_keyflex SCL
387 WHERE ASSIGNMENT_ID = p_assignment_id
388 AND PAA.soft_coding_keyflex_id = SCL.soft_coding_keyflex_id
389 AND p_effective_date BETWEEN PAA.effective_start_date AND PAA.effective_end_date ;
390
391 lr_local_unit_id csr_local_unit_id%ROWTYPE;
392
393 CURSOR csr_legal_employer_id (p_business_group_id NUMBER , p_organization_id NUMBER) IS
394 SELECT hoi3.organization_id legal_employer_id
395 FROM hr_organization_units o1
396 , hr_organization_information hoi1
397 , hr_organization_information hoi2
398 , hr_organization_information hoi3
399 WHERE o1.business_group_id =p_business_group_id
400 AND hoi1.organization_id = o1.organization_id
401 AND hoi1.organization_id = p_organization_id
402 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
403 AND hoi1.org_information_context = 'CLASS'
404 AND o1.organization_id = hoi2.org_information1
405 AND hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
406 AND hoi2.organization_id = hoi3.organization_id
407 AND hoi3.ORG_INFORMATION_CONTEXT='CLASS'
408 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER' ;
409
410 lr_legal_employer_id csr_legal_employer_id%ROWTYPE;
411
412
413
414
415 CURSOR csr_post_header(l_legal_emp_org_id NUMBER)
416 IS
417 SELECT ORG_INFORMATION1,ORG_INFORMATION2,ORG_INFORMATION3
418 FROM hr_organization_information
419 WHERE organization_id = l_legal_emp_org_id
420 AND org_information_context = 'SE_POST_PAYSLIP_INFO';
421
422 lr_post_header csr_post_header%ROWTYPE;
423
424
425
426
427 BEGIN
428 --
429 Fnd_file.put_line(FND_FILE.LOG,'######## IN Header');
430 -- pay_core_files.write_to_magtape_lob('<?xml version="1.0" encoding="'||hr_mx_utility.get_IANA_charset||'"?>');
431 -- pay_core_files.write_to_magtape_lob('<PAYSLIP_REPORT>');
432 --
433 l_final_xml_string := NULL ;
434 l_string := NULL ;
435
436 l_actid := pay_proc_environment_pkg.get_pactid;
437
438 Fnd_file.put_line(FND_FILE.LOG,'l_actid'|| l_actid);
439
440 get_all_parameters(l_actid
441 ,l_payroll_id
442 ,l_consolidation_set_id
443 ,l_start_date
444 ,l_end_date
445 ,l_rep_group
446 ,l_rep_category
447 ,l_assignment_set_id
448 ,l_assignment_id
449 ,l_effective_date
450 ,l_business_group_id
451 ,l_legislation_code);
452
453
454 Fnd_file.put_line(FND_FILE.LOG,'l_start_date'||l_start_date);
455 Fnd_file.put_line(FND_FILE.LOG,'l_end_date'||l_end_date);
456
457
458 OPEN csr_assg_id(l_payroll_id,l_start_date,l_end_date);
459 FETCH csr_assg_id into l_assignment_id;
460 CLOSE csr_assg_id;
461
462 Fnd_file.put_line(FND_FILE.LOG,'l_assignment_id'||l_assignment_id);
463 Fnd_file.put_line(FND_FILE.LOG,'l_effective_date'||l_effective_date);
464
465
466
467 OPEN csr_local_unit_id(l_assignment_id, l_effective_date);
468 FETCH csr_local_unit_id into lr_local_unit_id;
469 CLOSE csr_local_unit_id;
470
471 --Fnd_file.put_line(FND_FILE.LOG,'l_business_group_id'||l_business_group_id);
472 --Fnd_file.put_line(FND_FILE.LOG,'lr_local_unit_id.local_unit_id'||lr_local_unit_id.local_unit_id);
473
474 OPEN csr_legal_employer_id (l_business_group_id ,lr_local_unit_id.local_unit_id);
475 FETCH csr_legal_employer_id into lr_legal_employer_id;
476 CLOSE csr_legal_employer_id;
477
478
479 --Fnd_file.put_line(FND_FILE.LOG,'Legal_empID'||lr_legal_employer_id.legal_employer_id);
480
481 open csr_post_header(lr_legal_employer_id.legal_employer_id) ;
482 fetch csr_post_header into lr_post_header ;
483 close csr_post_header ;
484
485
486 open csr_payroll_post_header(l_payroll_id,l_legislation_code) ;
487 fetch csr_payroll_post_header into lr_payroll_post_header ;
488 close csr_payroll_post_header ;
489
490 l_sender := lr_payroll_post_header.PRL_INFORMATION1 ;
491 l_receiver := lr_payroll_post_header.PRL_INFORMATION2 ;
492 l_message := lr_payroll_post_header.PRL_INFORMATION3 ;
493
494 --Fnd_file.put_line(FND_FILE.LOG,'l_sender'||l_sender);
495 --Fnd_file.put_line(FND_FILE.LOG,'lr_post_header.ORG_INFORMATION1'||lr_post_header.ORG_INFORMATION1);
496
497 IF l_sender is NULL
498 THEN
499 l_sender := lr_post_header.ORG_INFORMATION1 ;
500 l_receiver := lr_post_header.ORG_INFORMATION2 ;
501 l_message := lr_post_header.ORG_INFORMATION3 ;
502 END IF;
503
504 Fnd_file.put_line(FND_FILE.LOG,'l_sender_after'||l_sender);
505
506
507 l_string := '<?POSTEN SND="'||l_sender||'" REC= "'||l_receiver||'" MSGTYPE="'||l_message||'" ?>';
508
509 --
510 SELECT fnd_global.local_chr(13) || fnd_global.local_chr(10)
511 INTO EOL
512 FROM DUAL ;
513 --
514
515 l_final_xml_string := l_string||'<PAYSLIP_REPORT>' || EOL ;
516
517
518 --l_final_xml_string := '<PAYSLIP_REPORT>' || EOL ;
519 --
520 pay_core_files.write_to_magtape_lob(l_final_xml_string);
521 --
522 Fnd_file.put_line(FND_FILE.LOG,'######## IN Header End');
523 END xml_header;
524 --------------------------------------------------------------------------------
525 -- xml_footer
526 --------------------------------------------------------------------------------
527 PROCEDURE xml_footer IS
528 l_final_xml_string VARCHAR2(32000) := NULL;
529 BEGIN
530 --
531 Fnd_file.put_line(FND_FILE.LOG,'######## IN Footer');
532 -- pay_core_files.write_to_magtape_lob('</PAYSLIP_REPORT>');
533 l_final_xml_string := NULL ;
534 --
535 l_final_xml_string := '</PAYSLIP_REPORT>' ;
536 --
537 pay_core_files.write_to_magtape_lob(l_final_xml_string);
538 --
539 Fnd_file.put_line(FND_FILE.LOG,'######## IN Footer End');
540 --
541 END xml_footer;
542 --
543 --------------------------------------------------------------------------------
544 -- XML_ASG
545 --------------------------------------------------------------------------------
546 PROCEDURE xml_asg IS
547 l_xml BLOB;
548 --
549 l_actid pay_payroll_actions.payroll_action_id%TYPE;
550 l_payroll_id NUMBER;
551 l_consolidation_set_id NUMBER;
552 l_start_date VARCHAR2(20);
553 l_end_date VARCHAR2(20);
554 l_legislation_code VARCHAR2(10);
555 l_rep_group pay_report_groups.report_group_name%TYPE;
556 l_rep_category pay_report_categories.category_name%TYPE;
557 l_effective_date DATE;
558 l_business_group_id NUMBER;
559 l_assignment_set_id NUMBER;
560 l_assignment_id NUMBER;
561 l_start_dt DATE;
562 l_end_dt DATE;
563 --
564 -- There can be multiple payslips for each assignment.
565 -- The number of payslips is based on the number of "EMPLOYEE DETAILS" records
566 -- for that assignment(The same logic is used while generating online payslips)
567 --
568 CURSOR csr_archive_act(c_payroll_id NUMBER
569 ,c_consolidation_set_id NUMBER
570 ,c_start_date DATE
571 ,c_end_Date DATE
572 ,c_pa_token VARCHAR2
573 ,c_cs_token VARCHAR2
574 ,c_legislation_code VARCHAR2) IS
575 SELECT pai.action_context_id
576 FROM pay_temp_object_actions ptoa
577 ,pay_action_information pai
578 ,pay_assignment_Actions paa
579 ,pay_payroll_actions ppa
580 ,hr_lookups hrl
581 WHERE ptoa.object_Action_id = pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
582 AND ptoa.object_id = paa.assignment_id
583 AND paa.payroll_action_id = ppa.payroll_action_id
584 AND ppa.effective_Date BETWEEN c_start_date
585 AND c_end_date
586 AND ppa.report_type = hrl.meaning--'ES_PS_ARCHIVE'
587 AND hrl.lookup_type = 'PAYSLIP_REPORT_TYPES'
588 AND hrl.lookup_code = c_legislation_code
589 AND NVL(c_payroll_id,get_parameter(ppa.legislative_parameters,c_pa_token)) = get_parameter(ppa.legislative_parameters,c_pa_token)
590 AND c_consolidation_set_id = get_parameter(ppa.legislative_parameters,c_cs_token)
591 AND pai.assignment_id = paa.assignment_id
592 AND pai.action_context_type = 'AAP'
593 AND pai.action_information_category = 'EMPLOYEE DETAILS'
594 AND pai.action_context_id = paa.assignment_action_id;
595 --
596 BEGIN
597 --hr_utility.trace('###### IN XML_ASG');
598 --
599 l_xml := NULL ;
600 l_actid := pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
601 --
602 get_all_parameters(l_actid
603 ,l_payroll_id
604 ,l_consolidation_set_id
605 ,l_start_date
606 ,l_end_date
607 ,l_rep_group
608 ,l_rep_category
609 ,l_assignment_set_id
610 ,l_assignment_id
611 ,l_effective_date
612 ,l_business_group_id
613 ,l_legislation_code);
614 --
615 l_start_dt := TO_DATE(l_start_date,'YYYY/MM/DD');
616 l_end_dt := TO_DATE(l_end_date,'YYYY/MM/DD');
617 --
618 --pay_core_files.write_to_magtape_lob('<?xml version="1.0" encoding="'||hr_mx_utility.get_IANA_charset||'"?>');
619 --pay_core_files.write_to_magtape_lob('<PAYSLIP_REPORT>');
620 Fnd_file.put_line(FND_FILE.LOG,'######## IN Out of the Loop');
621 --
622 FOR csr_archive_act_rec in csr_archive_act(l_payroll_id
623 ,l_consolidation_set_id
624 ,l_start_dt
625 ,l_end_dt
626 ,g_pa_token
627 ,g_cs_token
628 ,l_legislation_code) LOOP
629 --
630 Fnd_file.put_line(FND_FILE.LOG,'######## IN Side the Loop');
631 Fnd_file.put_line(FND_FILE.LOG,'######## '||csr_archive_act_rec.action_context_id);
632
633 pay_payroll_xml_extract_pkg.generate(
634 P_ACTION_CONTEXT_ID => csr_archive_act_rec.action_context_id
635 ,P_CUSTOM_XML_PROCEDURE => NULL
636 ,P_GENERATE_HEADER_FLAG => 'N'
637 ,P_ROOT_TAG => 'PAYSLIP'
638 ,P_DOCUMENT_TYPE => 'PAYSLIP'
639 ,P_XML => l_xml);
640 --
641 pay_core_files.write_to_magtape_lob(l_xml);
642 --
643 END LOOP;
644 --
645 --pay_core_files.write_to_magtape_lob('</PAYSLIP_REPORT>');
646 --
647 END xml_asg;
648 --
649 END pay_se_payslip_report;