[Home] [Help]
PACKAGE BODY: APPS.PAY_PAYSLIP_REPORT
Source
1 PACKAGE BODY pay_payslip_report AS
2 /* $Header: pygpsrep.pkb 120.2.12020000.3 2012/07/06 10:46:43 vmaripal 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 -- GET_SORT_ORDER
101 --------------------------------------------------------------------------------
102 FUNCTION get_sort_order( p_type IN VARCHAR2
103 ,p_legislation_code IN VARCHAR2 ) RETURN VARCHAR2 IS
104 --
105 l_sort_order VARCHAR2(20);
106 BEGIN
107 IF p_type = 'LE' THEN
108 --
109 EXECUTE IMMEDIATE 'SELECT pay_'||p_legislation_code||'_rules.get_payslip_sort_order1 FROM DUAL'
110 INTO l_sort_order;
111 ELSIF p_type = 'ORG' THEN
112 --
113 EXECUTE IMMEDIATE 'SELECT pay_'||p_legislation_code||'_rules.get_payslip_sort_order2 FROM DUAL'
114 INTO l_sort_order;
115 ELSIF p_type = 'NAME' THEN
116 --
117 EXECUTE IMMEDIATE 'SELECT pay_'||p_legislation_code||'_rules.get_payslip_sort_order3 FROM DUAL'
118 INTO l_sort_order;
119 END IF;
120 --
121 RETURN l_sort_order;
122 EXCEPTION
123 WHEN OTHERS THEN
124 RETURN NULL;
125 END get_sort_order;
126 --
127 --------------------------------------------------------------------------------
128 -- QUALIFYING_PROC
129 --------------------------------------------------------------------------------
130 PROCEDURE qualifying_proc(p_assignment_id IN NUMBER
131 ,p_qualifier OUT NOCOPY VARCHAR2 ) IS
132 --
133 l_actid NUMBER;
134 l_rep_group pay_report_groups.report_group_name%TYPE;
135 l_rep_category pay_report_categories.category_name%TYPE;
136 l_effective_date DATE;
137 l_business_group_id NUMBER;
138 l_assignment_set_id NUMBER;
139 l_assignment_id NUMBER;
140 l_inc_exc VARCHAR2(1);
141 l_asg_inc_exc VARCHAR2(1);
142 --
143 l_payroll_id NUMBER;
144 l_consolidation_set_id NUMBER;
145 l_start_date VARCHAR2(20);
146 l_end_date VARCHAR2(20);
147 l_legislation_code VARCHAR2(10);
148 l_start_dt DATE;
149 l_end_dt DATE;
150 l_qualifier VARCHAR2(1);
151 --
152 sql_cur NUMBER;
153 l_rows NUMBER;
154 statem VARCHAR2(256);
155 --
156 CURSOR csr_asg(c_assignment_id NUMBER
157 ,c_payroll_id NUMBER
158 ,c_consolidation_set_id NUMBER
159 ,c_start_date DATE
160 ,c_end_date DATE
161 ,c_pa_token VARCHAR2
162 ,c_cs_token VARCHAR2
163 ,c_legislation_code VARCHAR2) IS
164 SELECT 'Y'
165 FROM pay_assignment_actions paa
166 ,pay_payroll_actions ppa
167 ,hr_lookups hrl
168 ,pay_action_information pai
169 ,per_time_periods ptp
170 WHERE paa.assignment_id = c_assignment_id
171 AND paa.payroll_action_id = ppa.payroll_action_id
172 AND paa.source_action_id IS NULL
173 AND ppa.effective_Date BETWEEN c_start_date
174 AND c_end_date
175 AND ppa.report_type = hrl.meaning
176 AND hrl.lookup_type = 'PAYSLIP_REPORT_TYPES'
177 AND hrl.lookup_code = c_legislation_code
178 AND NVL(c_payroll_id,NVL(get_parameter(ppa.legislative_parameters,c_pa_token),-1))
179 = NVL(get_parameter(ppa.legislative_parameters,c_pa_token),-1)
180 AND c_consolidation_set_id = get_parameter(ppa.legislative_parameters,c_cs_token)
181 AND pai.assignment_id = paa.assignment_id
182 AND pai.action_context_type = 'AAP'
183 AND pai.action_information_category = 'EMPLOYEE DETAILS'
184 AND pai.action_context_id = paa.assignment_action_id
185 AND ptp.time_period_id = pai.ACTION_INFORMATION16
186 AND pay_us_employee_payslip_web.check_emp_personal_payment
187 ( paa.assignment_id, ptp.payroll_id, pai.action_information16,
188 pai.action_context_id, pai.effective_date) = 'Y' ;
189 --
190 CURSOR csr_inc_asg(c_assignment_id NUMBER
191 ,c_payroll_id NUMBER
192 ,c_consolidation_set_id NUMBER
193 ,c_start_date DATE
194 ,c_end_date DATE
195 ,c_pa_token VARCHAR2
196 ,c_cs_token VARCHAR2
197 ,c_legislation_code VARCHAR2
198 ,c_assignment_set_id NUMBER ) IS
199 SELECT 'Y'
200 FROM pay_assignment_actions paa
201 ,pay_payroll_actions ppa
202 ,hr_lookups hrl
203 ,hr_assignment_set_amendments hasa
204 ,pay_action_information pai
205 ,per_time_periods ptp
206 WHERE paa.assignment_id = c_assignment_id
207 AND paa.payroll_action_id = ppa.payroll_action_id
208 AND paa.source_action_id IS NULL
209 AND ppa.effective_Date BETWEEN c_start_date
210 AND c_end_date
211 AND ppa.report_type = hrl.meaning
212 AND hrl.lookup_type = 'PAYSLIP_REPORT_TYPES'
213 AND hrl.lookup_code = c_legislation_code
214 AND NVL(c_payroll_id,NVL(get_parameter(ppa.legislative_parameters,c_pa_token),-1))
215 = NVL(get_parameter(ppa.legislative_parameters,c_pa_token),-1)
216 AND c_consolidation_set_id = get_parameter(ppa.legislative_parameters,c_cs_token)
217 AND paa.assignment_id = hasa.assignment_id
218 AND hasa.assignment_set_id = c_assignment_set_id
219 AND hasa.include_or_exclude = 'I'
220 AND pai.assignment_id = paa.assignment_id
221 AND pai.action_context_type = 'AAP'
222 AND pai.action_information_category = 'EMPLOYEE DETAILS'
223 AND pai.action_context_id = paa.assignment_action_id
224 AND ptp.time_period_id = pai.ACTION_INFORMATION16
225 AND pay_us_employee_payslip_web.check_emp_personal_payment
226 ( paa.assignment_id, ptp.payroll_id, pai.action_information16,
227 pai.action_context_id, pai.effective_date) = 'Y' ;
228 --
229 -- The Assignment Set Logic is handled only for either Include or Exclude
230 -- and not for both. This doesn't handle the assignment_set_criteria.
231 --
232 CURSOR csr_inc_exc(c_assignment_set_id NUMBER
233 ,c_assignment_id NUMBER) IS
234 SELECT include_or_exclude
235 FROM hr_assignment_set_amendments
236 WHERE assignment_set_id = c_assignment_set_id
237 AND assignment_id = nvl(c_assignment_id,assignment_id);
238 --
239 BEGIN
240 --hr_utility.trace('###### IN Qualifying Proc');
241 --
242 l_actid := pay_proc_environment_pkg.get_pactid;
243 --
244 get_all_parameters(l_actid
245 ,l_payroll_id
246 ,l_consolidation_set_id
247 ,l_start_date
248 ,l_end_date
249 ,l_rep_group
250 ,l_rep_category
251 ,l_assignment_set_id
252 ,l_assignment_id
253 ,l_effective_date
254 ,l_business_group_id
255 ,l_legislation_code);
256 --
257 l_start_dt := TO_DATE(l_start_date,'YYYY/MM/DD');
258 l_end_dt := TO_DATE(l_end_date,'YYYY/MM/DD');
259 --EXECUTE IMMEDIATE 'SELECT pay_'||l_legislation_code||'_rules.get_payroll_token FROM DUAL' into l_token;
260 --
261 IF pay_payslip_report.g_pa_token IS NULL THEN
262 DECLARE
263 BEGIN
264 statem := 'BEGIN pay_'||l_legislation_code||'_rules.get_token_names(:p_pa_token, :p_cs_token); END;';
265 --hr_utility.trace(statem);
266 sql_cur := dbms_sql.open_cursor;
267 dbms_sql.parse(sql_cur
268 ,statem
269 ,dbms_sql.v7);
270 dbms_sql.bind_variable(sql_cur, 'p_pa_token', pay_payslip_report.g_pa_token, 50);
271 dbms_sql.bind_variable(sql_cur, 'p_cs_token', pay_payslip_report.g_cs_token, 50);
272 l_rows := dbms_sql.execute(sql_cur);
273 dbms_sql.variable_value(sql_cur, 'p_pa_token', pay_payslip_report.g_pa_token);
274 dbms_sql.variable_value(sql_cur, 'p_cs_token', pay_payslip_report.g_cs_token);
275 dbms_sql.close_cursor(sql_cur);
276 Exception
277 WHEN OTHERS THEN
278 pay_payslip_report.g_pa_token := NVL(pay_payslip_report.g_pa_token,'PAYROLL_ID');
279 pay_payslip_report.g_cs_token := NVL(pay_payslip_report.g_cs_token,'CONSOLIDATION_SET_ID');
280 --
281 IF dbms_sql.IS_OPEN(sql_cur) THEN
282 dbms_sql.close_cursor(sql_cur);
283 END IF;
284 END;
285 END IF;
286 --
287 IF l_assignment_id IS NOT NULL THEN
288 IF l_assignment_id = p_assignment_id THEN
289 --
290 p_qualifier := 'Y' ;
291 --
292 END IF;
293 ELSE
294 --
295 --hr_utility.trace('###### 1.p_assignment_id '||p_assignment_id);
296 --
297 IF l_assignment_set_id IS NOT NULL THEN
298 OPEN csr_inc_exc(l_assignment_set_id
299 ,NULL);
300 FETCH csr_inc_exc INTO l_inc_exc;
301 CLOSE csr_inc_exc;
302 END IF;
303 --
304 IF l_assignment_set_id IS NULL OR nvl(l_inc_exc,'E') = 'E' THEN
305 OPEN csr_asg(p_assignment_id
306 ,l_payroll_id
307 ,l_consolidation_set_id
308 ,l_start_dt
309 ,l_end_dt
310 ,pay_payslip_report.g_pa_token
311 ,pay_payslip_report.g_cs_token
312 ,l_legislation_code );
313 FETCH csr_asg INTO l_qualifier;
314 CLOSE csr_asg;
315 --
316 IF l_assignment_set_id IS NOT NULL THEN
317 OPEN csr_inc_exc(l_assignment_set_id
318 ,p_assignment_id);
319 FETCH csr_inc_exc INTO l_asg_inc_exc;
320 CLOSE csr_inc_exc;
321 END IF;
322 --
323 --hr_utility.trace('###### 2.l_asg_inc_exc '||l_asg_inc_exc);
324 --hr_utility.trace('###### 2.l_qualifier '||l_qualifier);
325 --
326 IF NVL(l_asg_inc_exc,'X') <> 'E' AND l_qualifier = 'Y' THEN
327 --
328 p_qualifier := 'Y' ;
329 --
330 END IF;
331 ELSIF l_inc_exc = 'I' THEN
332 OPEN csr_inc_asg(p_assignment_id
333 ,l_payroll_id
334 ,l_consolidation_set_id
335 ,l_start_dt
336 ,l_end_dt
337 ,pay_payslip_report.g_pa_token
338 ,pay_payslip_report.g_cs_token
339 ,l_legislation_code
340 ,l_assignment_set_id );
341 FETCH csr_inc_asg INTO l_qualifier;
342 CLOSE csr_inc_asg;
343 --
344 IF l_qualifier = 'Y' THEN
345 p_qualifier := 'Y' ;
346 END IF;
347 --
348 END IF;
349 --
350 END IF;
351 --
352 END qualifying_proc;
353 --------------------------------------------------------------------------------
354 -- XML_ASG
355 --------------------------------------------------------------------------------
356 PROCEDURE xml_asg IS
357 l_xml BLOB;
358 --
359 l_actid pay_payroll_actions.payroll_action_id%TYPE;
360 l_payroll_id NUMBER;
361 l_consolidation_set_id NUMBER;
362 l_start_date VARCHAR2(20);
363 l_end_date VARCHAR2(20);
364 l_legislation_code VARCHAR2(10);
365 l_rep_group pay_report_groups.report_group_name%TYPE;
366 l_rep_category pay_report_categories.category_name%TYPE;
367 l_effective_date DATE;
368 l_business_group_id NUMBER;
369 l_assignment_set_id NUMBER;
370 l_assignment_id NUMBER;
371 l_start_dt DATE;
372 l_end_dt DATE;
373 sql_cur NUMBER;
374 l_rows NUMBER;
375 statem VARCHAR2(256);
376 get_xml_statem VARCHAR2(256);
377 lv_custom_xml_code VARCHAR2(180);
378 --
379 -- There can be multiple payslips for each assignment.
380 -- The number of payslips is based on the number of "EMPLOYEE DETAILS" records
381 -- for that assignment(The same logic is used while generating online payslips)
382 --
383 CURSOR csr_archive_act(c_payroll_id NUMBER
384 ,c_consolidation_set_id NUMBER
385 ,c_start_date DATE
386 ,c_end_Date DATE
387 ,c_pa_token VARCHAR2
388 ,c_cs_token VARCHAR2
389 ,c_legislation_code VARCHAR2) IS
390 SELECT pai.action_context_id
391 FROM pay_temp_object_actions ptoa
392 ,pay_action_information pai
393 ,pay_assignment_Actions paa
394 ,pay_payroll_actions ppa
395 ,hr_lookups hrl
396 WHERE ptoa.object_Action_id = pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
397 AND ptoa.object_id = paa.assignment_id
398 AND paa.payroll_action_id = ppa.payroll_action_id
399 AND ppa.effective_Date BETWEEN c_start_date
400 AND c_end_date
401 AND ppa.report_type = hrl.meaning--'ES_PS_ARCHIVE'
402 AND hrl.lookup_type = 'PAYSLIP_REPORT_TYPES'
403 AND hrl.lookup_code = c_legislation_code
404 AND NVL(c_payroll_id,NVL(get_parameter(ppa.legislative_parameters,c_pa_token),-1))
405 = NVL(get_parameter(ppa.legislative_parameters,c_pa_token),-1)
406 AND c_consolidation_set_id = get_parameter(ppa.legislative_parameters,c_cs_token)
407 AND pai.assignment_id = paa.assignment_id
408 AND pai.action_context_type = 'AAP'
409 AND pai.action_information_category = 'EMPLOYEE DETAILS'
410 AND pai.action_context_id = paa.assignment_action_id;
411 --
412 BEGIN
413 --hr_utility.trace('###### IN XML_ASG');
414 --
415 l_actid := pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
416 --
417 get_all_parameters(l_actid
418 ,l_payroll_id
419 ,l_consolidation_set_id
420 ,l_start_date
421 ,l_end_date
422 ,l_rep_group
423 ,l_rep_category
424 ,l_assignment_set_id
425 ,l_assignment_id
426 ,l_effective_date
427 ,l_business_group_id
428 ,l_legislation_code);
429
430 /*Begin Bug 8350884 */
431 IF pay_payslip_report.g_pa_token IS NULL THEN
432 DECLARE
433 BEGIN
434 statem := 'BEGIN pay_'||l_legislation_code||'_rules.get_token_names(:p_pa_token, :p_cs_token); END;';
435 --hr_utility.trace(statem);
436 sql_cur := dbms_sql.open_cursor;
437 dbms_sql.parse(sql_cur
438 ,statem
439 ,dbms_sql.v7);
440 dbms_sql.bind_variable(sql_cur, 'p_pa_token', pay_payslip_report.g_pa_token, 50);
441 dbms_sql.bind_variable(sql_cur, 'p_cs_token', pay_payslip_report.g_cs_token, 50);
442 l_rows := dbms_sql.execute(sql_cur);
443 dbms_sql.variable_value(sql_cur, 'p_pa_token', pay_payslip_report.g_pa_token);
444 dbms_sql.variable_value(sql_cur, 'p_cs_token', pay_payslip_report.g_cs_token);
445 dbms_sql.close_cursor(sql_cur);
446 Exception
447 WHEN OTHERS THEN
448 pay_payslip_report.g_pa_token := NVL(pay_payslip_report.g_pa_token,'PAYROLL_ID');
449 pay_payslip_report.g_cs_token := NVL(pay_payslip_report.g_cs_token,'CONSOLIDATION_SET_ID');
450 --
451 IF dbms_sql.IS_OPEN(sql_cur) THEN
452 dbms_sql.close_cursor(sql_cur);
453 END IF;
454 END;
455 END IF;
456 /*End Bug 8350884 */
457 --
458 l_start_dt := TO_DATE(l_start_date,'YYYY/MM/DD');
459 l_end_dt := TO_DATE(l_end_date,'YYYY/MM/DD');
460 --
461 /*Begin Bug 13969852, 13969858*/
462 BEGIN
463 get_xml_statem := 'BEGIN PAY_'||l_legislation_code||'_RULES.get_custom_xml_routine(:lv_document_type, :lv_xml_routine); END;';
464 sql_cur := dbms_sql.open_cursor;
465 dbms_sql.parse(sql_cur
466 ,get_xml_statem
467 ,dbms_sql.v7);
468 dbms_sql.bind_variable(sql_cur, 'lv_document_type', 'PAYSLIP');
469 dbms_sql.bind_variable(sql_cur, 'lv_xml_routine', lv_custom_xml_code, 50);
470 l_rows := dbms_sql.execute(sql_cur);
471 dbms_sql.variable_value(sql_cur, 'lv_xml_routine', lv_custom_xml_code);
472 dbms_sql.close_cursor(sql_cur);
473 EXCEPTION
474 WHEN OTHERS THEN
475 lv_custom_xml_code := null;
476 --
477 IF dbms_sql.IS_OPEN(sql_cur) THEN
478 dbms_sql.close_cursor(sql_cur);
479 END IF;
480 END;
481 /*End Bug 13969852, 13969858*/
482
483 hr_utility.trace('The Custom XML code is: '||lv_custom_xml_code);
484 --
485 pay_core_files.write_to_magtape_lob('<?xml version="1.0" encoding="'||hr_mx_utility.get_IANA_charset||'"?>');
486 pay_core_files.write_to_magtape_lob('<PAYSLIP_REPORT>');
487 --
488 FOR csr_archive_act_rec in csr_archive_act(l_payroll_id
489 ,l_consolidation_set_id
490 ,l_start_dt
491 ,l_end_dt
492 ,pay_payslip_report.g_pa_token
493 ,pay_payslip_report.g_cs_token
494 ,l_legislation_code) LOOP
495 --
496 pay_payroll_xml_extract_pkg.generate(
497 P_ACTION_CONTEXT_ID => csr_archive_act_rec.action_context_id
498 ,P_CUSTOM_XML_PROCEDURE => lv_custom_xml_code
499 ,P_GENERATE_HEADER_FLAG => 'N'
500 ,P_ROOT_TAG => 'PAYSLIP'
501 ,P_DOCUMENT_TYPE => 'PAYSLIP'
502 ,P_XML => l_xml);
503 --
504 --hr_utility.trace('#### act_id '||csr_archive_act_rec.action_context_id);
505 pay_core_files.write_to_magtape_lob(l_xml);
506 --
507 END LOOP;
508 pay_core_files.write_to_magtape_lob('</PAYSLIP_REPORT>');
509 --
510 END xml_asg;
511 --
512 END pay_payslip_report;