[Home] [Help]
PACKAGE BODY: APPS.PAY_MX_ISR_FORMAT37
Source
1 PACKAGE body pay_mx_isr_format37 AS
2 /* $Header: paymxformat37mt.pkb 120.2 2005/11/15 13:13:42 kthirmiy noship $ */
3
4 /*
5 +=====================================================================+
6 | Copyright (c) 1997 Orcale Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +=====================================================================+
10 Name : paymxisrformat37mt.pkb
11 Description : This package contains functions and procedures which are
12 used to return values for the Format 37 MX ISR Tax report.
13
14 Change List
15 -----------
16
17 Version Date Author ER/CR No. Description of Change
18 -------+---------+----------+---------+--------------------------
19 115.0 26-Sep-05 kthirmiy Created
20 115.1 03-Nov-05 kthirmiy Modified range_cursor and
21 Action creation.
22 115.2 14-Nov-05 kthirmiy Bug fix 4728549
23 ********************************************************************************/
24 --
25 -- < PRIVATE GLOBALS > ---------------------------------------------------
26 --
27
28 -- flag to write the debug messages in the concurrent program log file
29 g_concurrent_flag VARCHAR2(1) ;
30 -- flag to write the debug messages in the trace file
31 g_debug_flag VARCHAR2(1) ;
32
33
34 /******************************************************************************
35 Name : msg
36 Purpose : Log a message, either using fnd_file, or hr_utility.trace
37 ******************************************************************************/
38
39 PROCEDURE msg(p_text VARCHAR2)
40 IS
41 --
42 BEGIN
43 -- Write to the concurrent request log
44 fnd_file.put_line(fnd_file.log, p_text);
45
46 END msg;
47
48 /******************************************************************************
49 Name : dbg
50 Purpose : Log a message, either using fnd_file, or hr_utility.trace
51 if debuggging is enabled
52 ******************************************************************************/
53 PROCEDURE dbg(p_text VARCHAR2) IS
54
55 BEGIN
56
57 IF (g_debug_flag = 'Y') THEN
58 IF (g_concurrent_flag = 'Y') THEN
59 -- Write to the concurrent request log
60 fnd_file.put_line(fnd_file.log, p_text);
61 ELSE
62 -- Use HR trace
63 hr_utility.trace(p_text);
64 END IF;
65 END IF;
66
67 END dbg;
68
69
70
71
72 /******************************************************************
73 Name : get_parameter
74 Purpose : returns the parameter value
75 ******************************************************************/
76 function get_parameter(name in varchar2,
77 parameter_list varchar2) return varchar2
78 is
79 start_ptr number;
80 end_ptr number;
81 token_val pay_payroll_actions.legislative_parameters%type;
82 par_value pay_payroll_actions.legislative_parameters%type;
83 begin
84 token_val := name||'=';
85 start_ptr := instr(parameter_list, token_val) + length(token_val);
86 end_ptr := instr(parameter_list, ' ',start_ptr);
87
88 /* if there is no spaces use then length of the string */
89 if end_ptr = 0 then
90 end_ptr := length(parameter_list)+1;
91 end if;
92
93 /* Did we find the token */
94 if instr(parameter_list, token_val) = 0 then
95 par_value := NULL;
96 else
97 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
98 end if;
99 return par_value;
100
101 end get_parameter;
102
103
104 /******************************************************************
105 Name : range_cursor
106 Purpose : range_cursor to select personids for format37
107 ******************************************************************/
108 procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
109
110 l_year number ;
111 l_legal_employer_id pay_assignment_actions.tax_unit_id%type;
112 l_org_id per_assignments_f.organization_id%type;
113 l_loc_id per_assignments_f.location_id%type;
114 l_per_id per_assignments_f.person_id%type;
115 l_curp per_people_f.national_identifier%type;
116 l_asg_set_id number;
117 l_effective_date date;
118
119 begin
120
121 g_debug_flag := 'Y' ;
122 -- g_concurrent_flag := 'Y' ;
123
124 begin
125 select to_number(pay_mx_isr_format37.get_parameter('Year',ppa.legislative_parameters)),
126 to_number(pay_mx_isr_format37.get_parameter('LEGAL_EMPLOYER_ID',ppa.legislative_parameters)),
127 to_number(pay_mx_isr_format37.get_parameter('ORG_ID',ppa.legislative_parameters)),
128 to_number(pay_mx_isr_format37.get_parameter('LOC_ID',ppa.legislative_parameters)),
129 to_number(pay_mx_isr_format37.get_parameter('PER_ID',ppa.legislative_parameters)),
130 pay_mx_isr_format37.get_parameter('CURP',ppa.legislative_parameters),
131 to_number(pay_mx_isr_format37.get_parameter('ASG_SET',ppa.legislative_parameters)),
132 ppa.effective_date
133 into l_year,
134 l_legal_employer_id,
135 l_org_id,
136 l_loc_id,
137 l_per_id,
138 l_curp,
139 l_asg_set_id,
140 l_effective_date
141 from pay_payroll_actions ppa /* PYUGEN payroll action id */
142 where ppa.payroll_action_id = pactid ;
143 Exception
144 when no_data_found then
145 dbg('Legislative parameters not found for pactid '||to_char(pactid));
146 raise;
147 end ;
148
149 dbg('Before the range cursor');
150
151 sqlstr := 'select distinct to_number(paa.serial_number)
152 from pay_payroll_actions ppa,
153 pay_assignment_actions paa
154 where ppa.report_type = ''MX_YREND_ARCHIVE''
155 and ppa.action_status = ''C''
156 and pay_mx_isr_format37.get_parameter(''TRANSFER_LEGAL_EMPLOYER'',ppa.legislative_parameters) = ' || l_legal_employer_id ||
157 ' and to_number(to_char(ppa.effective_date,''YYYY'')) = ' || l_year ||
158 ' and paa.payroll_action_id = ppa.payroll_action_id
159 and paa.action_status =''C''
160 and :payroll_action_id is not null
161 and NOT EXISTS(
162 SELECT ''x''
163 FROM pay_payroll_actions ppa1,
164 pay_assignment_actions paa1,
165 pay_action_interlocks palock
166 WHERE paa1.payroll_action_id = ppa1.payroll_action_id
167 AND ppa1.report_type = ''ISR_TAX_FORMAT37''
168 AND ppa1.report_qualifier = ''DEFAULT''
169 AND ppa1.report_category = ''REPORT''
170 AND paa1.action_status = ''C''
171 AND to_number(pay_mx_isr_format37.get_parameter(''LEGAL_EMPLOYER_ID'',ppa1.legislative_parameters)) ='
172 || l_legal_employer_id ||
173 ' AND to_number(to_char(ppa1.effective_date,''YYYY'')) = ' || l_year ||
174 ' AND palock.locking_action_id = paa1.assignment_action_id
175 and palock.locked_action_id = paa.assignment_action_id )
176 order by to_number(paa.serial_number) ';
177
178 dbg('After the range cursor');
179 dbg(sqlstr) ;
180
181 end range_cursor;
182
183 /******************************************************************
184 Name : action_creation_format37
185 Purpose : action creation procedure for format37
186 ******************************************************************/
187 procedure action_creation_format37(
188 pactid in number,
189 stperson in number,
190 endperson in number,
191 chunk in number,
192 p_year in number,
193 p_legal_employer_id in number,
194 p_org_id in number,
195 p_loc_id in number,
196 p_per_id in number,
197 p_curp in varchar2,
198 p_asg_set_id in number,
199 p_effective_date in date,
200 p_report_type in varchar2,
201 p_report_category in varchar2,
202 p_report_qualifier in varchar2 )
203 is
204
205 l_procedure_name VARCHAR2(100);
206 l_report_format pay_report_format_mappings_f.report_format%type;
207 l_range_person_on BOOLEAN;
208
209 lockingactid number;
210 lockedactid number;
211 assignid number;
212 greid number;
213 l_serial_number number;
214 l_person_id number;
215 l_eff_date date ;
216 l_pai_eff_date date ;
217 num number;
218
219 TYPE RefCurType is REF CURSOR;
220 c_actions RefCurType;
221
222 c_actions_sql varchar2(10000);
223
224 begin
225 l_procedure_name := 'action_creation_format37';
226 hr_utility.set_location(l_procedure_name, 1);
227
228 Begin
229 select report_format
230 into l_report_format
231 from pay_report_format_mappings_f
232 where report_type = p_report_type
233 and report_qualifier = p_report_qualifier
234 and report_category = p_report_category
235 and p_effective_date between
236 effective_start_date and effective_end_date;
237 Exception
238 When Others Then
239 l_report_format := Null ;
240 End ;
241
242 hr_utility.set_location(l_procedure_name, 2);
243 l_range_person_on := pay_ac_utility.range_person_on
244 ( p_report_type => p_report_type,
245 p_report_format => l_report_format,
246 p_report_qualifier => p_report_qualifier,
247 p_report_category => p_report_category);
248
249 /* when no selection is entered */
250 if((p_loc_id is null ) and
251 (p_org_id is null ) and
252 (p_per_id is null ) and
253 (p_curp is null ) and
254 (p_asg_set_id is null )) then
255
256 hr_utility.set_location(l_procedure_name, 5);
257 dbg('Selection criteria is Null') ;
258
259 if l_range_person_on = TRUE Then
260 hr_utility.set_location(l_procedure_name, 10);
261 dbg('Range Person ID Functionality is enabled') ;
262 c_actions_sql :=
263 'SELECT paa_arch.serial_number,
264 pai.effective_date,
265 paa_arch.assignment_action_id,
266 paa_arch.assignment_id,
267 paa_arch.tax_unit_id
268 FROM pay_payroll_actions ppa_arch,
269 pay_assignment_actions paa_arch,
270 pay_action_information pai,
271 pay_population_ranges ppr
272 WHERE ppa_arch.report_type=''MX_YREND_ARCHIVE''
273 AND to_number(pay_mx_isr_format37.get_parameter(''TRANSFER_LEGAL_EMPLOYER'',ppa_arch.legislative_parameters)) = '
274 || p_legal_employer_id ||
275 ' and to_number(to_char(ppa_arch.effective_date,''YYYY'')) = ' || p_year ||
276 ' AND ppa_arch.action_status =''C''
277 AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
278 AND paa_arch.action_status =''C''
279 AND ppr.payroll_action_id = ' || pactid || '
280 AND ppr.chunk_number = ' || chunk || '
281 AND ppr.person_id = to_number(paa_arch.serial_number)
282 and pai.action_information_category = ''MX YREND EE DETAILS''
283 and pai.action_context_id = paa_arch.assignment_action_id
284 AND NOT EXISTS(
285 SELECT ''x''
286 FROM pay_payroll_actions ppa1,
287 pay_assignment_actions paa1,
288 pay_action_interlocks palock
289 WHERE paa1.payroll_action_id = ppa1.payroll_action_id
290 AND ppa1.report_type = ''ISR_TAX_FORMAT37''
291 AND ppa1.report_qualifier = ''DEFAULT''
292 AND ppa1.report_category = ''REPORT''
293 AND paa1.action_status = ''C''
294 AND to_number(pay_mx_isr_format37.get_parameter(''LEGAL_EMPLOYER_ID'',ppa1.legislative_parameters)) ='
295 || p_legal_employer_id ||
296 ' and to_number(to_char(ppa1.effective_date,''YYYY'')) = ' || p_year ||
297 ' AND palock.locking_action_id = paa1.assignment_action_id
298 and palock.locked_action_id = paa_arch.assignment_action_id )
299 order by paa_arch.serial_number ';
300
301 else
302 hr_utility.set_location(l_procedure_name, 20);
303 dbg('Range Person ID Functionality is NOT enabled') ;
304 c_actions_sql :=
305 'SELECT paa_arch.serial_number,
306 pai.effective_date,
307 paa_arch.assignment_action_id,
308 paa_arch.assignment_id,
309 paa_arch.tax_unit_id
310 FROM pay_payroll_actions ppa_arch,
311 pay_assignment_actions paa_arch,
312 pay_action_information pai
313 WHERE ppa_arch.report_type=''MX_YREND_ARCHIVE''
314 AND to_number(pay_mx_isr_format37.get_parameter(''TRANSFER_LEGAL_EMPLOYER'',ppa_arch.legislative_parameters)) = '
315 || p_legal_employer_id ||
316 ' and to_number(to_char(ppa_arch.effective_date,''YYYY'')) = ' || p_year ||
317 ' AND ppa_arch.action_status =''C''
318 AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
319 AND paa_arch.action_status =''C''
320 AND to_number(paa_arch.serial_number) between ' || stperson || ' and ' || endperson || '
321 and pai.action_information_category = ''MX YREND EE DETAILS''
322 and pai.action_context_id = paa_arch.assignment_action_id
323 AND NOT EXISTS(
324 SELECT ''x''
325 FROM pay_payroll_actions ppa1,
326 pay_assignment_actions paa1,
327 pay_action_interlocks palock
328 WHERE paa1.payroll_action_id = ppa1.payroll_action_id
329 AND ppa1.report_type = ''ISR_TAX_FORMAT37''
330 AND ppa1.report_qualifier = ''DEFAULT''
331 AND ppa1.report_category = ''REPORT''
332 AND paa1.action_status = ''C''
333 AND to_number(pay_mx_isr_format37.get_parameter(''LEGAL_EMPLOYER_ID'',ppa1.legislative_parameters)) ='
334 || p_legal_employer_id ||
335 ' and to_number(to_char(ppa1.effective_date,''YYYY'')) = ' || p_year ||
336 ' AND palock.locking_action_id = paa1.assignment_action_id
337 and palock.locked_action_id = paa_arch.assignment_action_id )
338 order by paa_arch.serial_number ';
339
340 end if ; -- l_range_person_on
341
342 end if; /* End of when no selection is entered */
343
344 /* when location is entered */
345
346 if p_loc_id is not null then
347
348 hr_utility.set_location(l_procedure_name, 30);
349 dbg('Selection criteria is Location') ;
350
351 if l_range_person_on = TRUE Then
352 hr_utility.set_location(l_procedure_name, 35);
353 c_actions_sql :=
354 'SELECT paa_arch.serial_number,
355 pai.effective_date,
356 paa_arch.assignment_action_id,
357 paa_arch.assignment_id,
358 paa_arch.tax_unit_id
359 FROM per_periods_of_service pps,
360 per_assignments_f paf,
361 pay_payroll_actions ppa_arch,
362 pay_assignment_actions paa_arch,
363 pay_action_information pai,
364 pay_population_ranges ppr
365 WHERE ppa_arch.report_type=''MX_YREND_ARCHIVE''
366 AND to_number(pay_mx_isr_format37.get_parameter(''TRANSFER_LEGAL_EMPLOYER'',ppa_arch.legislative_parameters)) = '
367 || p_legal_employer_id ||
368 ' and to_number(to_char(ppa_arch.effective_date,''YYYY'')) = ' || p_year ||
369 ' AND ppa_arch.action_status =''C''
370 AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
371 AND paa_arch.action_status =''C''
372 AND paa_arch.assignment_id = paf.assignment_id
373 AND nvl(pps.final_process_date,''' || p_effective_date || ''')
374 between paf.effective_start_date and paf.effective_end_date
375 AND paf.location_id = ' || p_loc_id || '
376 AND paf.effective_start_date =
377 (select max(paf2.effective_start_date)
378 from per_assignments_f paf2
379 where paf2.assignment_id = paf.assignment_id
380 and paf2.effective_start_date <= ''' || p_effective_date || ''')
381 AND paf.effective_end_date >= ppa_arch.start_date
382 AND paf.assignment_type = ''E''
383 AND pps.period_of_service_id = paf.period_of_service_id
384 AND ppr.payroll_action_id = ' || pactid || '
385 AND ppr.chunk_number = ' || chunk || '
386 AND paf.person_id = ppr.person_id
387 and pai.action_information_category = ''MX YREND EE DETAILS''
388 and pai.action_context_id = paa_arch.assignment_action_id
389 AND NOT EXISTS(
390 SELECT ''x''
391 FROM pay_payroll_actions ppa1,
392 pay_assignment_actions paa1,
393 pay_action_interlocks palock
394 WHERE paa1.payroll_action_id = ppa1.payroll_action_id
395 AND ppa1.report_type = ''ISR_TAX_FORMAT37''
396 AND ppa1.report_qualifier = ''DEFAULT''
397 AND ppa1.report_category = ''REPORT''
398 AND paa1.action_status = ''C''
399 AND to_number(pay_mx_isr_format37.get_parameter(''LEGAL_EMPLOYER_ID'',ppa1.legislative_parameters)) ='
400 || p_legal_employer_id ||
401 ' and to_number(to_char(ppa1.effective_date,''YYYY'')) = ' || p_year ||
402 ' AND palock.locking_action_id = paa1.assignment_action_id
403 and palock.locked_action_id = paa_arch.assignment_action_id )
404 order by paa_arch.serial_number ';
405
406 else
407 hr_utility.set_location(l_procedure_name, 40);
408 c_actions_sql :=
409 'SELECT paa_arch.serial_number,
410 pai.effective_date,
411 paa_arch.assignment_action_id,
412 paa_arch.assignment_id,
413 paa_arch.tax_unit_id
414 FROM per_periods_of_service pps,
415 per_assignments_f paf,
416 pay_payroll_actions ppa_arch,
417 pay_assignment_actions paa_arch,
418 pay_action_information pai
419 WHERE ppa_arch.report_type=''MX_YREND_ARCHIVE''
420 AND to_number(pay_mx_isr_format37.get_parameter(''TRANSFER_LEGAL_EMPLOYER'',ppa_arch.legislative_parameters)) = '
421 || p_legal_employer_id ||
422 ' and to_number(to_char(ppa_arch.effective_date,''YYYY'')) = ' || p_year ||
423 ' AND ppa_arch.action_status =''C''
424 AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
425 AND paa_arch.action_status =''C''
426 AND paa_arch.assignment_id = paf.assignment_id
427 AND nvl(pps.final_process_date,''' || p_effective_date || ''')
428 between paf.effective_start_date and paf.effective_end_date
429 AND paf.location_id = ' || p_loc_id || '
430 AND paf.effective_start_date =
431 (select max(paf2.effective_start_date)
432 from per_assignments_f paf2
433 where paf2.assignment_id = paf.assignment_id
434 and paf2.effective_start_date <= ''' || p_effective_date || ''')
435 AND paf.effective_end_date >= ppa_arch.start_date
436 AND paf.assignment_type = ''E''
437 AND pps.period_of_service_id = paf.period_of_service_id
438 AND paf.person_id between ' || stperson || ' and ' || endperson || '
439 and pai.action_information_category = ''MX YREND EE DETAILS''
440 and pai.action_context_id = paa_arch.assignment_action_id
441 AND NOT EXISTS(
442 SELECT ''x''
443 FROM pay_payroll_actions ppa1,
444 pay_assignment_actions paa1,
445 pay_action_interlocks palock
446 WHERE paa1.payroll_action_id = ppa1.payroll_action_id
447 AND ppa1.report_type = ''ISR_TAX_FORMAT37''
448 AND ppa1.report_qualifier = ''DEFAULT''
449 AND ppa1.report_category = ''REPORT''
450 AND paa1.action_status = ''C''
451 AND to_number(pay_mx_isr_format37.get_parameter(''LEGAL_EMPLOYER_ID'',ppa1.legislative_parameters)) ='
452 || p_legal_employer_id ||
453 ' and to_number(to_char(ppa1.effective_date,''YYYY'')) = ' || p_year ||
454 ' AND palock.locking_action_id = paa1.assignment_action_id
455 and palock.locked_action_id = paa_arch.assignment_action_id )
456 order by paa_arch.serial_number ';
457
458 end if ;
459
460 end if; /* End of when location is entered */
461
462
463 /* when org is entered */
464 if p_org_id is not null then
465
466 hr_utility.set_location(l_procedure_name, 50);
467 dbg('Selection criteria is Organization') ;
468
469 if l_range_person_on = TRUE Then
470 hr_utility.set_location(l_procedure_name, 60);
471 dbg('Range Person ID Functionality is enabled') ;
472 c_actions_sql :=
473 'SELECT paa_arch.serial_number,
474 pai.effective_date,
475 paa_arch.assignment_action_id,
476 paa_arch.assignment_id,
477 paa_arch.tax_unit_id
478 FROM per_periods_of_service pps,
479 per_assignments_f paf,
480 pay_payroll_actions ppa_arch,
481 pay_assignment_actions paa_arch,
482 pay_action_information pai,
483 pay_population_ranges ppr
484 WHERE ppa_arch.report_type=''MX_YREND_ARCHIVE''
485 AND to_number(pay_mx_isr_format37.get_parameter(''TRANSFER_LEGAL_EMPLOYER'',ppa_arch.legislative_parameters)) = '
486 || p_legal_employer_id ||
487 ' and to_number(to_char(ppa_arch.effective_date,''YYYY'')) = ' || p_year ||
488 ' AND ppa_arch.action_status =''C''
489 AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
490 AND paa_arch.action_status =''C''
491 AND paa_arch.assignment_id = paf.assignment_id
492 AND nvl(pps.final_process_date,''' || p_effective_date || ''')
493 between paf.effective_start_date and paf.effective_end_date
494 AND paf.organization_id = ' || p_org_id || '
495 AND paf.effective_start_date =
496 (select max(paf2.effective_start_date)
497 from per_assignments_f paf2
498 where paf2.assignment_id = paf.assignment_id
499 and paf2.effective_start_date <= ''' || p_effective_date || ''')
500 AND paf.effective_end_date >= ppa_arch.start_date
501 AND paf.assignment_type = ''E''
502 AND pps.period_of_service_id = paf.period_of_service_id
503 AND ppr.payroll_action_id = ' || pactid || '
504 AND ppr.chunk_number = ' || chunk || '
505 AND paf.person_id = ppr.person_id
506 and pai.action_information_category = ''MX YREND EE DETAILS''
507 and pai.action_context_id = paa_arch.assignment_action_id
508 AND NOT EXISTS(
509 SELECT ''x''
510 FROM pay_payroll_actions ppa1,
511 pay_assignment_actions paa1,
512 pay_action_interlocks palock
513 WHERE paa1.payroll_action_id = ppa1.payroll_action_id
514 AND ppa1.report_type = ''ISR_TAX_FORMAT37''
515 AND ppa1.report_qualifier = ''DEFAULT''
516 AND ppa1.report_category = ''REPORT''
517 AND paa1.action_status = ''C''
518 AND to_number(pay_mx_isr_format37.get_parameter(''LEGAL_EMPLOYER_ID'',ppa1.legislative_parameters)) ='
519 || p_legal_employer_id ||
520 ' and to_number(to_char(ppa1.effective_date,''YYYY'')) = ' || p_year ||
521 ' AND palock.locking_action_id = paa1.assignment_action_id
522 and palock.locked_action_id = paa_arch.assignment_action_id )
523 order by paa_arch.serial_number ';
524
525 else
526
527 hr_utility.set_location(l_procedure_name, 70);
528 c_actions_sql :=
529 'SELECT paa_arch.serial_number,
530 pai.effective_date,
531 paa_arch.assignment_action_id,
532 paa_arch.assignment_id,
533 paa_arch.tax_unit_id
534 FROM per_periods_of_service pps,
535 per_assignments_f paf,
536 pay_payroll_actions ppa_arch,
537 pay_assignment_actions paa_arch,
538 pay_action_information pai
539 WHERE ppa_arch.report_type=''MX_YREND_ARCHIVE''
540 AND to_number(pay_mx_isr_format37.get_parameter(''TRANSFER_LEGAL_EMPLOYER'',ppa_arch.legislative_parameters)) = '
541 || p_legal_employer_id ||
542 ' and to_number(to_char(ppa_arch.effective_date,''YYYY'')) = ' || p_year ||
543 ' AND ppa_arch.action_status =''C''
544 AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
545 AND paa_arch.action_status =''C''
546 AND paa_arch.assignment_id = paf.assignment_id
547 AND nvl(pps.final_process_date,''' || p_effective_date || ''')
548 between paf.effective_start_date and paf.effective_end_date
549 AND paf.location_id = ' || p_org_id || '
550 AND paf.effective_start_date =
551 (select max(paf2.effective_start_date)
552 from per_assignments_f paf2
553 where paf2.assignment_id = paf.assignment_id
554 and paf2.effective_start_date <= ''' || p_effective_date || ''')
555 AND paf.effective_end_date >= ppa_arch.start_date
556 AND paf.assignment_type = ''E''
557 AND pps.period_of_service_id = paf.period_of_service_id
558 AND paf.person_id between ' || stperson || ' and ' || endperson || '
559 and pai.action_information_category = ''MX YREND EE DETAILS''
560 and pai.action_context_id = paa_arch.assignment_action_id
561 AND NOT EXISTS(
562 SELECT ''x''
563 FROM pay_payroll_actions ppa1,
564 pay_assignment_actions paa1,
565 pay_action_interlocks palock
566 WHERE paa1.payroll_action_id = ppa1.payroll_action_id
567 AND ppa1.report_type = ''ISR_TAX_FORMAT37''
568 AND ppa1.report_qualifier = ''DEFAULT''
569 AND ppa1.report_category = ''REPORT''
570 AND paa1.action_status = ''C''
571 AND to_number(pay_mx_isr_format37.get_parameter(''LEGAL_EMPLOYER_ID'',ppa1.legislative_parameters)) ='
572 || p_legal_employer_id ||
573 ' and to_number(to_char(ppa1.effective_date,''YYYY'')) = ' || p_year ||
574 ' AND palock.locking_action_id = paa1.assignment_action_id
575 and palock.locked_action_id = paa_arch.assignment_action_id )
576 order by paa_arch.serial_number ';
577
578 end if ;
579
580 end if; /* End of when org is entered */
581
582
583 /* when person or CURP is entered */
584 if (p_per_id is not null OR p_curp is not null ) then
585
586 hr_utility.set_location(l_procedure_name, 80);
587 dbg('Selection criteria is either Employee Name or CURP') ;
588
589 c_actions_sql := 'SELECT paa_arch.serial_number,
590 pai.effective_date,
591 paa_arch.assignment_action_id,
592 paa_arch.assignment_id,
593 paa_arch.tax_unit_id
594 FROM per_assignments_f paf,
595 pay_payroll_actions ppa_arch,
596 pay_assignment_actions paa_arch,
597 pay_action_information pai
598 WHERE ppa_arch.report_type=''MX_YREND_ARCHIVE''
599 AND to_number(pay_mx_isr_format37.get_parameter(''TRANSFER_LEGAL_EMPLOYER'',ppa_arch.legislative_parameters)) = '
600 || p_legal_employer_id ||
601 ' and to_number(to_char(ppa_arch.effective_date,''YYYY'')) = ' || p_year ||
602 ' AND ppa_arch.action_status =''C''
603 AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
604 AND paa_arch.action_status =''C''
605 AND paa_arch.assignment_id = paf.assignment_id
606 AND paf.PERSON_ID = ' || p_per_id || '
607 AND paa_arch.assignment_id = paf.assignment_id
608 AND paf.effective_start_date = (select max(paf2.effective_start_date)
609 from per_assignments_f paf2
610 where paf2.assignment_id = paf.assignment_id
611 and paf2.effective_start_date <= ''' || p_effective_date || ''')
612 AND paf.effective_end_date >= ppa_arch.start_date
613 AND paf.assignment_type = ''E''
614 AND paf.person_id between ' || stperson || ' and ' || endperson || '
615 and pai.action_information_category = ''MX YREND EE DETAILS''
616 and pai.action_context_id = paa_arch.assignment_action_id
617 AND NOT EXISTS(
618 SELECT ''x''
619 FROM pay_payroll_actions ppa1,
620 pay_assignment_actions paa1,
621 pay_action_interlocks palock
622 WHERE paa1.payroll_action_id = ppa1.payroll_action_id
623 AND ppa1.report_type = ''ISR_TAX_FORMAT37''
624 AND ppa1.report_qualifier = ''DEFAULT''
625 AND ppa1.report_category = ''REPORT''
626 AND paa1.action_status = ''C''
627 AND to_number(pay_mx_isr_format37.get_parameter(''LEGAL_EMPLOYER_ID'',ppa1.legislative_parameters)) ='
628 || p_legal_employer_id ||
629 ' and to_number(to_char(ppa1.effective_date,''YYYY'')) = ' || p_year ||
630 ' AND palock.locking_action_id = paa1.assignment_action_id
631 and palock.locked_action_id = paa_arch.assignment_action_id )
632 order by paa_arch.serial_number ';
633
634 end if; /* End of when person or CURP is entered */
635
636 /* when assignment set is entered */
637 if p_asg_set_id is not null then
638
639 hr_utility.set_location(l_procedure_name, 90);
640 dbg('Selection criteria is Assignment set') ;
641
642 if l_range_person_on = TRUE Then
643 hr_utility.set_location(l_procedure_name, 100);
644 dbg('Range Person ID Functionality is enabled') ;
645 c_actions_sql :=
646 'SELECT paa_arch.serial_number,
647 pai.effective_date,
648 paa_arch.assignment_action_id,
649 paa_arch.assignment_id,
650 paa_arch.tax_unit_id
651 FROM per_assignments_f paf,
652 pay_payroll_actions ppa_arch,
653 pay_assignment_actions paa_arch,
654 pay_action_information pai,
655 pay_population_ranges ppr
656 WHERE ppa_arch.report_type=''MX_YREND_ARCHIVE''
657 AND to_number(pay_mx_isr_format37.get_parameter(''TRANSFER_LEGAL_EMPLOYER'',ppa_arch.legislative_parameters)) = '
658 || p_legal_employer_id ||
659 ' and to_number(to_char(ppa_arch.effective_date,''YYYY'')) = ' || p_year ||
660 ' AND ppa_arch.action_status =''C''
661 AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
662 AND paa_arch.action_status =''C''
663 AND paa_arch.assignment_id = paf.assignment_id
664 AND paf.effective_start_date = (select max(paf2.effective_start_date)
665 from per_assignments_f paf2
666 where paf2.assignment_id = paf.assignment_id
667 and paf2.effective_start_date <= ''' || p_effective_date || ''')
668 AND paf.effective_end_date >= ppa_arch.start_date
669 AND paf.assignment_type = ''E''
670 AND ppr.payroll_action_id = ' || pactid || '
671 AND ppr.chunk_number = ' || chunk || '
672 AND paf.person_id = ppr.person_id
673 and pai.action_information_category = ''MX YREND EE DETAILS''
674 and pai.action_context_id = paa_arch.assignment_action_id
675 AND NOT EXISTS(
676 SELECT ''x''
677 FROM pay_payroll_actions ppa1,
678 pay_assignment_actions paa1,
679 pay_action_interlocks palock
680 WHERE paa1.payroll_action_id = ppa1.payroll_action_id
681 AND ppa1.report_type = ''ISR_TAX_FORMAT37''
682 AND ppa1.report_qualifier = ''DEFAULT''
683 AND ppa1.report_category = ''REPORT''
684 AND paa1.action_status = ''C''
685 AND to_number(pay_mx_isr_format37.get_parameter(''LEGAL_EMPLOYER_ID'',ppa1.legislative_parameters)) ='
686 || p_legal_employer_id ||
687 ' and to_number(to_char(ppa1.effective_date,''YYYY'')) = ' || p_year ||
688 ' AND palock.locking_action_id = paa1.assignment_action_id
689 and palock.locked_action_id = paa_arch.assignment_action_id )
690 AND exists ( select 1 /* Selected Assignment Set */
691 from hr_assignment_set_amendments hasa
692 where hasa.assignment_set_id = ' || p_asg_set_id || '
693 and hasa.assignment_id = paa_arch.assignment_id
694 and upper(hasa.include_or_exclude) = ''I'')
695 order by paa_arch.serial_number ';
696
697 else
698 hr_utility.set_location(l_procedure_name, 110);
699 c_actions_sql :=
700 'SELECT paa_arch.serial_number,
701 pai.effective_date,
702 paa_arch.assignment_action_id,
703 paa_arch.assignment_id,
704 paa_arch.tax_unit_id
705 FROM per_assignments_f paf,
706 pay_payroll_actions ppa_arch,
707 pay_assignment_actions paa_arch,
708 pay_action_information pai
709 WHERE ppa_arch.report_type=''MX_YREND_ARCHIVE''
710 AND to_number(pay_mx_isr_format37.get_parameter(''TRANSFER_LEGAL_EMPLOYER'',ppa_arch.legislative_parameters)) = '
711 || p_legal_employer_id ||
712 ' and to_number(to_char(ppa_arch.effective_date,''YYYY'')) = ' || p_year ||
713 ' AND ppa_arch.action_status =''C''
714 AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
715 AND paa_arch.action_status =''C''
716 AND paa_arch.assignment_id = paf.assignment_id
717 AND paf.effective_start_date = (select max(paf2.effective_start_date)
718 from per_assignments_f paf2
719 where paf2.assignment_id = paf.assignment_id
720 and paf2.effective_start_date <= ''' || p_effective_date || ''')
721 AND paf.effective_end_date >= ppa_arch.start_date
722 AND paf.assignment_type = ''E''
723 AND paf.person_id between ' || stperson || ' and ' || endperson || '
724 and pai.action_information_category = ''MX YREND EE DETAILS''
725 and pai.action_context_id = paa_arch.assignment_action_id
726 AND NOT EXISTS(
727 SELECT ''x''
728 FROM pay_payroll_actions ppa1,
729 pay_assignment_actions paa1,
730 pay_action_interlocks palock
731 WHERE paa1.payroll_action_id = ppa1.payroll_action_id
732 AND ppa1.report_type = ''ISR_TAX_FORMAT37''
733 AND ppa1.report_qualifier = ''DEFAULT''
734 AND ppa1.report_category = ''REPORT''
735 AND paa1.action_status = ''C''
736 AND to_number(pay_mx_isr_format37.get_parameter(''LEGAL_EMPLOYER_ID'',ppa1.legislative_parameters)) ='
737 || p_legal_employer_id ||
738 ' and to_number(to_char(ppa1.effective_date,''YYYY'')) = ' || p_year ||
739 ' AND palock.locking_action_id = paa1.assignment_action_id
740 and palock.locked_action_id = paa_arch.assignment_action_id )
741 AND exists ( select 1 /* Selected Assignment Set */
742 from hr_assignment_set_amendments hasa
743 where hasa.assignment_set_id = ' || p_asg_set_id || '
744 and hasa.assignment_id = paa_arch.assignment_id
745 and upper(hasa.include_or_exclude) = ''I'')
746 order by paa_arch.serial_number ';
747 end if ;
748
749 end if; /* End of when assignment set is entered */
750
751
752 hr_utility.set_location(l_procedure_name, 120);
753 dbg('Opening c_actions cursor');
754 dbg(c_actions_sql);
755
756 l_serial_number := null ;
757 l_eff_date := null ;
758
759 OPEN c_actions FOR c_actions_sql;
760 num := 0;
761 loop
762 fetch c_actions into l_person_id, l_pai_eff_date, lockedactid,assignid,greid;
763 if c_actions%found then
764 num := num + 1;
765 dbg('In the c_actions%found in action cursor');
766 else
767 dbg('In the c_actions%notfound in action cursor');
768 exit;
769 end if;
770
771 hr_utility.set_location(l_procedure_name, 125);
772
773 dbg( to_char(l_serial_number)) ;
774 dbg( to_char(l_eff_date,'DD-MON-YYYY') ) ;
775 dbg( to_char(l_person_id)) ;
776 dbg( to_char(l_pai_eff_date,'DD-MON-YYYY') ) ;
777
778
779 if l_serial_number is null or
780 l_eff_date is null or
781 l_serial_number <> l_person_id or
782 l_eff_date <> l_pai_eff_date then
783
784 dbg('Inserting action record');
785 dbg('Record ' || to_char(num) );
786 select pay_assignment_actions_s.nextval
787 into lockingactid
788 from dual;
789
790 -- insert the action record.
791 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
792
793 dbg('Updating serial number');
794
795 -- Update serial_number of Pay_assignment_actions with the
796 -- assignment_action_id .
797 update pay_assignment_actions
798 set serial_number = lockedactid
799 where assignment_action_id = lockingactid;
800
801 l_serial_number := l_person_id ;
802 l_eff_date := l_pai_eff_date ;
803
804 end if;
805
806 dbg('Before Inserting action interlock record');
807 dbg('lockingactionid ' || to_char(lockingactid) );
808 dbg('lockedactionid ' || to_char(lockedactid) );
809
810 -- insert record in action interlocks
811 hr_nonrun_asact.insint(lockingactid, lockedactid);
812
813 dbg('After Inserting action interlock record');
814
815 end loop;
816 close c_actions;
817
818 hr_utility.set_location(l_procedure_name, 300);
819 dbg('End of the action creation format37');
820
821 end action_creation_format37;
822
823
824 /******************************************************************
825 Name : action_creation
826 Purpose : main action creation procedure
827 ******************************************************************/
828 procedure action_creation(pactid in number,
829 stperson in number,
830 endperson in number,
831 chunk in number) is
832
833
834 l_year number ;
835 l_legal_employer_id pay_assignment_actions.tax_unit_id%type;
836 l_org_id per_assignments_f.organization_id%type;
837 l_loc_id per_assignments_f.location_id%type;
838 l_per_id per_assignments_f.person_id%type;
839 l_curp per_people_f.national_identifier%type;
840 l_asg_set_id number;
841 l_effective_date date;
842 l_report_type pay_payroll_actions.report_type%TYPE;
843 l_report_category pay_payroll_actions.report_category%type;
844 l_report_qualifier pay_payroll_actions.report_qualifier%type;
845
846 begin
847
848 g_debug_flag := 'Y' ;
849 -- g_concurrent_flag := 'Y' ;
850
851 -- hr_utility.trace_on(null,'ORACLE');
852 hr_utility.set_location('procpyr',1);
853 dbg('In the action cursor');
854 Begin
855 select to_number(pay_mx_isr_format37.get_parameter('Year',ppa.legislative_parameters)),
856 to_number(pay_mx_isr_format37.get_parameter('LEGAL_EMPLOYER_ID',ppa.legislative_parameters)),
857 to_number(pay_mx_isr_format37.get_parameter('ORG_ID',ppa.legislative_parameters)),
858 to_number(pay_mx_isr_format37.get_parameter('LOC_ID',ppa.legislative_parameters)),
859 to_number(pay_mx_isr_format37.get_parameter('PER_ID',ppa.legislative_parameters)),
860 pay_mx_isr_format37.get_parameter('CURP',ppa.legislative_parameters),
861 to_number(pay_mx_isr_format37.get_parameter('ASG_SET',ppa.legislative_parameters)),
862 ppa.effective_date,
863 ppa.report_type,
864 ppa.report_category,
865 ppa.report_qualifier
866 into l_year,
867 l_legal_employer_id,
868 l_org_id,
869 l_loc_id,
870 l_per_id,
871 l_curp,
872 l_asg_set_id,
873 l_effective_date,
874 l_report_type,
875 l_report_category,
876 l_report_qualifier
877 from pay_payroll_actions ppa /* PYUGEN payroll action id */
878 where ppa.payroll_action_id = pactid ;
879 Exception
880 when no_data_found then
881 dbg('Legislative parameters not found for pactid '||to_char(pactid));
882 raise;
883 End ;
884 dbg('report_type = '||l_report_type);
885
886
887 /* Now the CURP value set return person_id. Since the submission is based on
888 selection citeria only one value can be entered so in case l_curp is not
889 null then it is safe to assume l_per_id is null */
890
891 if l_curp is not null then
892 l_per_id := l_curp;
893 end if;
894
895 if l_report_type = 'ISR_TAX_FORMAT37' then /* Format 37 */
896
897 action_creation_format37(pactid,
898 stperson,
899 endperson,
900 chunk,
901 l_year,
902 l_legal_employer_id,
903 l_org_id,
904 l_loc_id,
905 l_per_id,
906 l_curp,
907 l_asg_set_id,
908 l_effective_date,
909 l_report_type,
910 l_report_category,
911 l_report_qualifier
912 );
913 end if;
914
915 end action_creation;
916
917
918 end pay_mx_isr_format37;