[Home] [Help]
PACKAGE BODY: APPS.PAY_IE_P35_MAGTAPE
Source
1 PACKAGE BODY pay_ie_p35_magtape AS
2 /* $Header: pyiep35m.pkb 120.10.12010000.1 2008/07/27 22:50:06 appldev ship $ */
3 --
4 -- Constants
5 --
6 l_package CONSTANT VARCHAR2 (31) := 'pay_ie_p35_magtape.';
7 --------------------------------------------------------------------------------+
8
9 FUNCTION get_parameter (
10 p_payroll_action_id IN NUMBER,
11 p_token_name IN VARCHAR2
12 )
13 RETURN VARCHAR2
14 AS
15 CURSOR csr_parameter_info (p_pact_id NUMBER, p_token CHAR)
16 IS
17 SELECT SUBSTR (
18 legislative_parameters,
19 INSTR (legislative_parameters, p_token)
20 + ( LENGTH (p_token)
21 + 1
22 ),
23 INSTR (
24 legislative_parameters,
25 ' ',
26 INSTR (legislative_parameters, p_token)
27 )
28 - ( INSTR (legislative_parameters, p_token)
29 + LENGTH (p_token)
30 )
31 ),
32 business_group_id bg_id
33 FROM pay_payroll_actions
34 WHERE payroll_action_id = p_pact_id;
35
36 l_business_group_id NUMBER;
37 l_token_value VARCHAR2 (50);
38 BEGIN
39 hr_utility.set_location ( 'p_token_name = '
40 || p_token_name, 20);
41 OPEN csr_parameter_info (p_payroll_action_id, p_token_name);
42 FETCH csr_parameter_info INTO l_token_value, l_business_group_id;
43 CLOSE csr_parameter_info;
44
45 IF p_token_name = 'BG_ID'
46 THEN
47 l_token_value := TO_CHAR (l_business_group_id);
48 ELSE
49 l_token_value := TRIM (l_token_value);
50 END IF;
51
52 RETURN l_token_value;
53 END get_parameter;
54
55 --------------------------------------------------------------------------------+
56 -- Range cursor returns the ids of the assignments to be archived
57 --------------------------------------------------------------------------------+
58 PROCEDURE range_code (p_payroll_action_id IN NUMBER,
59 p_sqlstr OUT NOCOPY VARCHAR2
60 )
61 IS
62 l_proc_name VARCHAR2 (100) := l_package|| 'range_code';
63 l_dummy NUMBER;
64 p35_error EXCEPTION;
65 l_payroll_action_message VARCHAR2 (255);
66 l_out_var VARCHAR2 (30);
67 l_start_date DATE;
68 l_end_date DATE;
69 l_bg_id NUMBER;
70
71 CURSOR csr_p35_process
72 IS
73 SELECT NVL (MIN (ppa.payroll_action_id), 0)
74 FROM pay_payroll_actions ppa
75 WHERE ppa.report_type = 'IEP35'
76 AND ppa.action_status = 'C'
77 AND TO_DATE (
78 pay_ie_p35.get_parameter (
79 ppa.payroll_action_id,
80 'END_DATE'
81 ),
82 'YYYY/MM/DD'
83 ) BETWEEN l_start_date AND l_end_date
84 AND ppa.business_group_id = l_bg_id;
85
86 BEGIN
87 --hr_utility.trace_on(null,'MAGTRC');
88 l_out_var :=
89 pay_ie_p35.get_parameter (
90 p_payroll_action_id=> p_payroll_action_id,
91 p_token_name=> 'END_DATE'
92 );
93 l_end_date := TO_DATE (l_out_var, 'YYYY/MM/DD');
94 l_out_var :=
95 pay_ie_p35.get_parameter (
96 p_payroll_action_id=> p_payroll_action_id,
97 p_token_name=> 'START_DATE'
98 );
99 l_start_date := TO_DATE (l_out_var, 'YYYY/MM/DD');
100 l_out_var :=
101 pay_ie_p35.get_parameter (
102 p_payroll_action_id=> p_payroll_action_id,
103 p_token_name=> 'BG_ID'
104 );
105 l_bg_id := TO_NUMBER (l_out_var);
106
107 -- Check to make sure there is a p35 process run
108 -- existing for business group, otherwise fail
109 OPEN csr_p35_process;
110 FETCH csr_p35_process INTO l_dummy;
111
112 IF l_dummy = 0
113 THEN
114 CLOSE csr_p35_process;
115 RAISE p35_error;
116 END IF;
117
118 CLOSE csr_p35_process;
119 --
120 -- Changed the cursor to reduce the cost (5042843)
121 p_sqlstr :=
122 ' SELECT distinct asg.person_id
123 FROM per_periods_of_service pos,
124 per_assignments_f asg,
125 pay_payroll_actions ppa
126 WHERE ppa.payroll_action_id = :payroll_action_id
127 AND pos.person_id = asg.person_id
128 AND pos.period_of_service_id = asg.period_of_service_id
129 AND pos.business_group_id = ppa.business_group_id
130 AND asg.business_group_id = ppa.business_group_id
131 ORDER BY asg.person_id';
132 /* Added code for BUG 2987230 */
133 hr_utility.set_location (l_proc_name, 20);
134
135 EXCEPTION
136 WHEN OTHERS
137 THEN
138 -- Write to the conc logfile, and try to archive err msg.
139 l_payroll_action_message :=
140 SUBSTR (
141 'P35 Report Process Failed: No P35 Process exist for the Business group in the reporting year.',
142 1,
143 240
144 );
145 fnd_file.put_line (fnd_file.LOG, l_payroll_action_message);
146 p_sqlstr :=
147 'select 1 from dual where to_char(:payroll_action_id) = dummy';
148 END range_code;
149
150
151 --------------------------------------------------------------------------------+
152 -- Creates assignment action id for all the valid person id's in
153 -- the range selected by the Range code.
154 --------------------------------------------------------------------------------+
155 PROCEDURE action_creation (
156 pactid IN NUMBER,
157 stperson IN NUMBER,
158 endperson IN NUMBER,
159 CHUNK IN NUMBER
160 )
161 IS
162 l_proc_name VARCHAR2 (100) := l_package || 'assignment_action_code';
163 l_actid NUMBER;
164 l_out_var VARCHAR2 (30);
165 l_start_date DATE;
166 l_end_date DATE;
167 l_bg_id NUMBER;
168 l_segment4 hr_soft_coding_keyflex.segment4%TYPE;
169 l_assignment_set_id hr_assignment_sets.assignment_set_id%TYPE;
170 l_payroll_id pay_all_payrolls_f.payroll_id%TYPE;
171 l_set_flag hr_assignment_set_amendments.include_or_exclude%TYPE ;
172
173 CURSOR csr_get_flag_from_set
174 IS
175 SELECT DISTINCT hasa.include_or_exclude FROM hr_assignment_set_amendments hasa, hr_assignment_sets has
176 WHERE hasa.assignment_set_id = has.assignment_set_id
177 AND has.business_group_id = l_bg_id
178 AND has.assignment_set_id = l_assignment_set_id;
179
180
181 CURSOR csr_locked_asgs
182 IS
183 SELECT DISTINCT paa.assignment_id,paa.assignment_action_id,paa.payroll_action_id
184 FROM pay_assignment_actions paa,
185 pay_payroll_actions ppa,
186 pay_action_information pai,
187 per_assignments_f paaf,
188 pay_all_payrolls_f ppf,
189 hr_soft_coding_keyflex flex
190 WHERE paa.payroll_action_id = ppa.payroll_action_id
191 AND paa.action_status = 'C'
192 AND ppa.action_type ='X'
193 AND ppa.business_group_id = l_bg_id
194 AND paa.source_action_id IS NULL
195 AND pai.action_context_id = paa.assignment_action_id
196 AND pai.action_information_category = 'IE P35 DETAIL'
197 AND ppa.report_type = 'IEP35'
198 AND paa.assignment_id = pai.assignment_id
199 AND paaf.assignment_id = paa.assignment_id
200 --AND paaf.primary_flag = 'Y'
201 AND paaf.business_group_id = ppa.business_group_id
202 AND paaf.payroll_id = ppf.payroll_id
203 AND ppf.effective_start_date <= l_end_date
204 AND ppf.effective_end_date >= l_start_date
205 AND flex.soft_coding_keyflex_id = ppf.soft_coding_keyflex_id
206 AND flex.segment4 = l_segment4
207 AND paaf.person_id BETWEEN stperson AND endperson
208 AND paaf.effective_start_date <= l_end_date
209 AND paaf.effective_end_date >= l_start_date
210 AND TO_DATE (
211 pay_ie_p35.get_parameter (
212 ppa.payroll_action_id,
213 'END_DATE'
214 ),
215 'YYYY/MM/DD'
216 ) BETWEEN l_start_date AND l_end_date
217 AND (ppf.payroll_id in (select b.payroll_id FROM per_assignments_f a,per_assignments_f b
218 where a.payroll_id = l_payroll_id
219 and a.person_id = b.person_id
220 and a.person_id = paaf.person_id
221 --bug 6642916
222 and a.effective_start_date<= l_end_date
223 and a.effective_end_date>= l_start_date)
224 OR l_payroll_id IS NULL)
225 --AND (ppf.payroll_id =l_payroll_id or l_payroll_id is null)
226 --AND PAY_IE_P35.check_assignment_in_set(paa.assignment_id,l_assignment_set_id,l_bg_id)=1;
227 AND ((l_assignment_set_id IS NOT NULL
228 AND (l_set_flag ='I' AND EXISTS(SELECT 1
229 FROM hr_assignment_set_amendments hasa
230 , hr_assignment_sets has
231 , per_assignments_f paf
232 --, pay_payrolls_f pay
233 --, hr_soft_coding_keyflex hflex
234 WHERE has.assignment_set_id = hasa.assignment_set_id
235 AND has.business_group_id = l_bg_id
236 AND has.assignment_set_id = l_assignment_set_id
237 AND hasa.assignment_id = paf.assignment_id
238 AND paf.person_id = paaf.person_id)
239 --AND paf.payroll_id = pay.payroll_id
240 --AND pay.soft_coding_keyflex_id = hflex.soft_coding_keyflex_id
241 --AND hflex.segment4 = l_segment4)
242 OR l_set_flag = 'E' AND NOT EXISTS(SELECT 1
243 FROM hr_assignment_set_amendments hasa
244 , hr_assignment_sets has
245 , per_assignments_f paf
246 -- , pay_payrolls_f pay
247 -- , hr_soft_coding_keyflex hflex
248 WHERE has.assignment_set_id = hasa.assignment_set_id
249 AND has.business_group_id = l_bg_id
250 AND has.assignment_set_id = l_assignment_set_id
251 AND hasa.assignment_id = paf.assignment_id
252 AND paf.person_id = paaf.person_id
253 --AND paf.payroll_id = pay.payroll_id
254 --AND pay.soft_coding_keyflex_id = hflex.soft_coding_keyflex_id
255 --AND hflex.segment4 = l_segment4
256 )))
257 OR l_assignment_set_id IS NULL)
258 AND NOT EXISTS (
259 SELECT 1
260 FROM pay_assignment_actions paa_p35,
261 pay_payroll_actions ppa_p35,
262 per_assignments_f paaf_p35,
263 pay_all_payrolls_f ppf_p35,
264 hr_soft_coding_keyflex flex_p35,
265 pay_action_interlocks plock
266 WHERE ppa_p35.report_type = 'IEP35MAG'
267 AND paa_p35.action_status = 'C'
268 AND TO_DATE (
269 pay_ie_p35.get_parameter (
270 ppa_p35.payroll_action_id,
271 'END_DATE'
272 ),
273 'YYYY/MM/DD'
274 ) BETWEEN l_start_date AND l_end_date
275 AND ppa_p35.payroll_action_id = paa_p35.payroll_action_id
276 --AND paa_p35.assignment_id = asg.assignment_id
277 AND paa_p35.assignment_id = paaf_p35.assignment_id
278 AND paaf_p35.person_id = paaf.person_id
279 AND paa_p35.assignment_action_id = plock.locking_action_id
280 AND plock.locked_action_id IN (SELECT assignment_action_id FROM pay_assignment_actions
281 WHERE assignment_id=paaf.assignment_id)
282 AND paaf_p35.payroll_id = ppf_p35.payroll_id
283 AND ppf_p35.soft_coding_keyflex_id = flex_p35.soft_coding_keyflex_id
284 AND flex_p35.segment4 = l_segment4);
285
286
287
288
289 BEGIN
290
291 hr_utility.set_location ('pactid passed to action creation::'|| pactid ||'::' ,910);
292 hr_utility.set_location ('stperson passed to action creation::'||stperson , 920);
293 hr_utility.set_location ('endperson passed to action creation::'||endperson,930);
294 hr_utility.set_location ('CHUNK passed to action creation::'||CHUNK ,940);
295 hr_utility.set_location (l_proc_name, 10);
296
297 -- l_end_date := pay_ie_p35.get_end_date;
298
299
300 l_out_var :=
301 pay_ie_p35.get_parameter (
302 p_payroll_action_id=> pactid,
303 p_token_name=> 'END_DATE'
304 );
305 l_end_date := TO_DATE (l_out_var, 'YYYY/MM/DD'); --4641756
306
307 l_start_date := pay_ie_p35.get_start_date;
308
309 l_out_var := pay_ie_p35.get_parameter (
310 p_payroll_action_id=> pactid,
311 p_token_name=> 'BG_ID'
312 );
313
314 l_bg_id := TO_NUMBER (l_out_var);
315
316 hr_utility.set_location ( 'Start date -'
317 || TO_CHAR (l_start_date), 11);
318 hr_utility.set_location ( 'End date -'
319 || TO_CHAR (l_end_date), 12);
320 hr_utility.set_location ( 'Bus Group id -'
321 || TO_CHAR (l_bg_id), 13);
322
323
324 --
325 l_segment4 := pay_ie_p35.get_parameter( p_payroll_action_id=> pactid,
329 p_token_name=> 'ASSIGNMENT_SET_ID'
326 p_token_name=> 'EMP_NO');
327 l_out_var := pay_ie_p35.get_parameter (
328 p_payroll_action_id=> pactid,
330 );
331 l_assignment_set_id := to_number(l_out_var);
332 --bug 6642916
333 l_out_var := pay_ie_p35.get_parameter (
334 p_payroll_action_id=> pactid,
335 p_token_name=> 'PAYROLL'
336 );
337 l_payroll_id := to_number(l_out_var);
338
339 OPEN csr_get_flag_from_set;
340 FETCH csr_get_flag_from_set into l_set_flag;
341 CLOSE csr_get_flag_from_set;
342
343 FOR c_rec in csr_locked_asgs
344 LOOP
345 hr_utility.set_location ('Selected assignment ::'|| c_rec.assignment_id || ' ::and action ::passed to action creation::'||
346 c_rec.assignment_action_id||'::',960);
347 hr_utility.set_location ('Selected payroll_action::'||c_rec.payroll_action_id ,960);
348
349 SELECT pay_assignment_actions_s.NEXTVAL
350 INTO l_actid
351 FROM DUAL;
352
353 -- insert into pay_assignment_actions.
354 hr_nonrun_asact.insact (
355 l_actid,
356 c_rec.assignment_id,
357 pactid,
358 CHUNK,
359 NULL
360 );
361
362 hr_utility.set_location ('Created aact::'||l_actid ||' for assignment::'|| c_rec.assignment_id ||':: and aact::'||
363 c_rec.assignment_action_id||'::',970);
364
365 hr_nonrun_asact.insint ( l_actid,c_rec.assignment_action_id);
366 --Fnd_file.put_line(FND_FILE.LOG,'Locked Assignment Action ID'||c_rec.assignment_action_id );
367 hr_utility.set_location ('Locked Assignment Action ID::'||c_rec.assignment_action_id ||' :: using aact::'||l_actid,980);
368 l_arc_payroll_action_id := c_rec.payroll_action_id;
369
370 END LOOP;
371 --Fnd_file.put_line(FND_FILE.LOG,l_arc_payroll_action_id );
372
373 EXCEPTION
374 WHEN OTHERS
375 THEN
376 hr_utility.set_location ('Error in assignment action code ', 100);
377 RAISE;
378 END action_creation;
379
380 --
381 FUNCTION get_start_date
382 RETURN DATE
383 AS
384 l_start_date DATE;
385 BEGIN
386 SELECT fnd_date.canonical_to_date (
387 SUBSTR (fpov.profile_option_value, 1, 4)
388 || '01/01 00:00:00'
389 )
390 INTO l_start_date
391 FROM fnd_profile_option_values fpov, fnd_profile_options fpo
392 WHERE fpo.profile_option_id = fpov.profile_option_id
393 AND fpo.application_id = fpov.application_id
394 AND fpo.profile_option_name = 'PAY_IE_P35_REPORTING_YEAR'
395 AND fpov.level_id = 10001
396 AND fpov.level_value = 0;
397
398 RETURN l_start_date;
399 END get_start_date;
400
401
402 --
403 FUNCTION get_end_date
404 RETURN DATE
405 AS
406 l_end_date DATE;
407 BEGIN
408 SELECT fnd_date.canonical_to_date (
409 SUBSTR (fpov.profile_option_value, 1, 4)
410 || '12/31 23:59:59'
411 )
412 INTO l_end_date
413 FROM fnd_profile_option_values fpov, fnd_profile_options fpo
414 WHERE fpo.profile_option_id = fpov.profile_option_id
415 AND fpo.application_id = fpov.application_id
416 AND fpo.profile_option_name = 'PAY_IE_P35_REPORTING_YEAR'
417 AND fpov.level_id = 10001
418 AND fpov.level_value = 0;
419
420 RETURN l_end_date;
421 END get_end_date;
422
423 /* Function for getting Pension Details */
424 FUNCTION get_pension_details(emp_rbs IN OUT NOCOPY NUMBER,
425 emp_rbs_bal IN OUT NOCOPY NUMBER,
426 empr_rbs IN OUT NOCOPY NUMBER,
427 empr_rbs_bal IN OUT NOCOPY NUMBER,
428 emp_prsa IN OUT NOCOPY NUMBER,
429 emp_prsa_bal IN OUT NOCOPY NUMBER,
430 empr_prsa IN OUT NOCOPY NUMBER,
431 empr_prsa_bal IN OUT NOCOPY NUMBER,
432 emp_rac IN OUT NOCOPY NUMBER,
433 emp_rac_bal IN OUT NOCOPY NUMBER,
434 p_payroll_action_id NUMBER,
435 p_taxable_benefits IN OUT NOCOPY NUMBER) RETURN NUMBER IS
436 cursor get_pension_details is
437 select count(decode(pact.action_information2,0,null,null,null,1)) EMP_RBS,
438 sum(to_number(pact.action_information2))*100 EMP_RBS_BAL,
439 count(decode(pact.action_information3,0,null,null,null,1)) EMPR_RBS,
440 sum(to_number(pact.action_information3))*100 EMPR_RBS_BAL,
441 count(decode(pact.action_information4,0,null,null,null,1)) EMP_PRSA,
442 sum(to_number(pact.action_information4))*100 EMP_PRSA_BAL,
443 count(decode(pact.action_information5,0,null,null,null,1)) EMPR_PRSA,
444 sum(to_number(pact.action_information5))*100 EMPR_PRSA_BAL,
445 count(decode(pact.action_information6,0,null,null,null,1)) EMP_RAC,
446 sum(to_number(pact.action_information6))*100 EMP_RAC_BAL,
447 sum(to_number(pact.action_information1))*100 TAXABLEBENEFITS
448 FROM pay_assignment_actions paa
449 ,pay_action_information pact
450 ,pay_action_interlocks pai
451 WHERE paa.payroll_action_id = p_payroll_action_id
452 AND paa.source_action_id IS NULL
453 AND pai.locking_action_id = paa.assignment_action_id
454 AND pact.action_context_id = pai.locked_action_id
455 --AND pact.action_context_id = paa.assignment_action_id
456 AND pact.action_information_category = 'IE P35 ADDITIONAL DETAILS'
457 AND pact.action_context_type = 'AAP';
458 BEGIN
459 OPEN get_pension_details;
460 FETCH get_pension_details INTO emp_rbs,
461 emp_rbs_bal,
462 empr_rbs,
463 empr_rbs_bal,
464 emp_prsa,
465 emp_prsa_bal,
466 empr_prsa,
467 empr_prsa_bal,
468 emp_rac,
469 emp_rac_bal,
470 p_taxable_benefits;
471 CLOSE get_pension_details;
472 RETURN 1;
473 END get_pension_details;
474
475 FUNCTION raise_warning(l_flag varchar2) return number is
476 l_status BOOLEAN;
477 BEGIN
478 IF l_flag = 'Y' THEN
479 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS
480 (
481 status => 'WARNING',
482 message => 'PRSI Insurable Weeks exceed max limit. Please Check the Log File for more details'
483 );
484 END IF;
485 return 1;
486 END raise_warning;
487
488 -- For bug 6275544
489 FUNCTION test_XML(P_STRING VARCHAR2) RETURN VARCHAR2 AS
490 l_string varchar2(300);
491 begin
492 l_string := p_string;
493
494 IF 'a' <> COMPOSE('a'|| UNISTR('\0301')) THEN
495 l_string := replace(l_string,COMPOSE ('A'|| UNISTR('\0301')),'Á');
496
497 l_string := replace(l_string,COMPOSE ('E'|| UNISTR('\0301')),'É');
498
499 l_string := replace(l_string,COMPOSE ('I'|| UNISTR('\0301')),'Í');
500
501 l_string := replace(l_string,COMPOSE ('O'|| UNISTR('\0301')),'Ó');
502
503 l_string := replace(l_string,COMPOSE ('U'|| UNISTR('\0301')),'Ú');
504
505 l_string := replace(l_string,COMPOSE ('a'|| UNISTR('\0301')),'á');
506 l_string := replace(l_string,COMPOSE ('e'|| UNISTR('\0301')),'é');
507
508 l_string := replace(l_string,COMPOSE ('i'|| UNISTR('\0301')),'í');
509
510 l_string := replace(l_string,COMPOSE ('o'|| UNISTR('\0301')),'ó');
511
512 l_string := replace(l_string,COMPOSE ('u'|| UNISTR('\0301')),'ú');
513 END IF;
514
515 RETURN l_string;
516 END ;
517
518 END pay_ie_p35_magtape;