DBA Data[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;