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