[Home] [Help]
PACKAGE BODY: APPS.HR_US_W2_MT
Source
1 PACKAGE body hr_us_w2_mt AS
2 /* $Header: pyusw2mt.pkb 120.13.12010000.3 2008/08/06 08:42:50 ubhat ship $ */
3
4 /*
5 +=====================================================================+
6 | Copyright (c) 1997 Orcale Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +=====================================================================+
10 Name : pyusw2mt.pkb
11 Description : This package contains functions and procedures which are
12 used to return values for the W2 US Payroll reports.
13
14 Change List
15 -----------
16
17 Version Date Author ER/CR No. Description of Change
18 -------+---------+----------+---------+--------------------------
19 40.0 AAsthana Created for multi-threaded report.
20 115.4 20-JUL-01 irgonzal Modified action_creation and sort_
21 action procedures. Bug fixes:
22 1850043, 1488083, 1894165.
23 115.5 01-AUG-01 irgonzal Modified ACTION_CREATION cursor:
24 Removed a)'order by' and 'for update'
25 clauses, b) calls to hr_us_w2_rep
26 functions (added queries to retrieve
27 the values).
28 Modified RANGE_CURSOR and removed
29 calls to get_parameter function.
30
31 115.6 10-AUG-01 irgonzal Modified action_creation cursor and
32 removed reference to
33 hr_us_w2_rep.get_w2_arch_bal function.
34 115.7 30-AUG-01 irgonzal Modified range_cursor and added
35 condition that includes :payroll_action_id
36 parameter.
37 Remove identation in SORT cursor.
38 Replaced 'YEAR' by 'Year'.
39 115.9 31-AUG-01 ssarma added to_char to tax_unit_id join to
40 fic1.context
41 115.10 09-SEP-01 kthirmiy added ppa.payroll_action_id in the action_creation
42 procedure while selecting the l_eoy_payroll_action_id
43 Also changed to ppa.effective_date=ppa1.effective_date
44 instead of
45 ppa.effective_date = to_date('31-DEC-'||
46 hr_us_w2_mt.get_parameter
47 ('Year',ppa1.legislative_parameters), 'DD-MON-YYYY')
48 115.11 11-DEC-01 meshah changed the assignment_action cursor for
49 performance reason. There was a dramatic performance
50 gain at inhouse. No each selection criteria are
51 a seperate cursor.
52 115.14 12-DEC-01 rsirigir GSCC COMPLIANCE CHECK, added
53 REM checkfile:~PROD:~PATH:~FILE
54 changed date format from
55 select to_date('31-DEC-'||to_char(l_year),
56 'DD-MON-YYYY') to
57 select to_date('31-DEC-'||to_char(l_year),
58 'DD/MM/YYYY')
59 changed date format from
60 where to_date('31-DEC-'||to_char(l_year),'DD-MON-YYYY')
61 > l_dt to
62 where to_date('31-DEC-'||to_char(l_year),'DD/MM/YYYY')
63 > l_dt
64
65 115.15 10-Jan-02 kthirmiy For TAR 1874418.995 to improve performance changed in
66 sort_action function . Removed the tables
67 pay_payroll_actions ppa_arch and
68 pay_assignment_actions and to go directly to
69 pay_assignment_actions mt table.
70 115.16 18-JAN-02 meshah changed the sort cursor again. Need to fetch
71 zip code for the live address.
72 115.20 12-FEB-02 meshah changed the action_creation cursor. Now seperate
73 procedures are called for Employee and Employer
74 W2. This is because state paramter is required
75 for Employer W2 and optional for Employee W2.
76 115.21 19-Aug-02 fusman Added Puerto Rico W-2 report type.
77 115.22 10-SEP-02 kthirmiy Added hr_us_w2_rep.get_agent_tax_unit_id
78 for Agent GRE setup validation check
79 in the range_cursor
80 115.23 11-SEP-02 kthirmiy changed ppa1.report_type instead of ppa.report_type
81 changed update of mt.assignment_action_id instead of
82 paf.assignment_id in sort_action
83 115.24 12-Sep-02 fusman Bug:2565342
84 Changed the ssn datatype from number to varchar2.
85 115.25 17-SEP-02 kthirmiy Removed Pre-Process Check - Agent GRE setup
86 for Bug 2573499
87 115.26 31-JUL-03 meshah 2576942 modified cursors c_actions_with_location,
88 c_actions_with_org and c_actions_with_state.
89 A new cursor c_state_ueid has been created to
90 fetch the user_entity_id only once.
91 Same cursors have been modified for ee and er.
92 115.26 08-AUG-03 meshah 3052020 passing report_type as a parameter to
93 action_creation_for_ee. We do not print paper
94 W2 for employee who have opted not to receive a
95 paper W2.
96 115.28 29-SEP-03 meshah backed out the call to
97 pay_us_employee_payslip_web.
98 115.29 03-OCT-03 meshah changed the c_actions_no_selection cursor for
99 ee and er for performance reason.
100 115.30 20-JUL-2004 asasthan NO CODE CHANGES Only comments have been added
101 BUG: 3343607, 3624090
102 Changes for action_creation
103 with state and org was done
104 by meshah earlier.
105 Action Creation with SSN
106 seems to be taking optimal
107 path.
108 Sort Action: put on hold
109 after discussing with meshah.
110 115.31 30-JUL-2004 asasthan 3343607 cursor c_actions_with_ssn is not
111 used at all. Removing the cursor
112 from the code for EE W2 Report.
113 115.32 03-RAUG2004 asasthan 3343607 cursor c_actions_with_ssn is not
114 used for ER W2 Report. Removing
115 cursor and commented out code.
116 115.34 06-AUG-2004 rsethupa 3052020 Changes for optionally printing W2
117 115.35 19-AUG-2004 meshah there was a to_char on serial_number
118 when comparing with person_id.
119 this will cause the package to be
120 invalid on 8.1.7.4x DB. Changed to
121 to_number.
122 115.36 01-SEP-2004 asasthan 3052020 Employer W2 should print
123 irrespective of Self-Service
124 Preferences set for W2.
125 115.37 14-MAR-2005 sackumar 4222032 Change in the Range Cursor removing redundant
126 use of bind Variable (:payroll_action_id)
127 115.40 24-AUG-2005 pragupta 4152323 Range Person ID functionality enhancement:
128 The cursors for action_creation_for_ee and
129 action_creation_for_er have been replaced by
130 ref cursors. The aim is to improve the
131 performance of the cursor queries.
132 115.41 07-SEP-2005 ynegoro 2538173 Support new parameter, locality
133 115.42 12-SEP-2005 sodhingr 3688789 Added W2_XML report format for action
134 creation
135 115.43 21-SEP-2005 ahanda Changed action creation to support
136 locality
137 115.44 22-SEP-2005 ahanda Changed select stmt for locality param.
138 115.55 26-OCT-2005 kvsankar 4645408 Added the check for the User Entity
139 'A_CITY_WK_WITHHELD_PER_JD_GRE_YTD'
140 as employees who have both Wages and Taxes
141 withheld should only be reported for the
142 specified locality
143 115.46 04-JAN-2006 pragupta 4886044 Added the check for the User Entity
144 'A_COUNTY_WITHHELD_PER_JD_GRE_YTD'
145 and 'A_SCHOOL_WITHHELD_PER_JD_GRE_YTD'
146 as employees who have both Wages and Taxes
147 withheld should only be reported for the
148 specified locality
149 115.47 24-JAN-2006 asasthan 4951715 Removed suppression of index
150 on per_assignments_f in sort cursor
151 115.48 10-AUG-2006 sodhingr 5169849 Changed action_creation for EE report to
152 print the W-2 for terminated EE only
153 115.49 29-AUG-2006 saurgupt 5169849 Changed the function action_creation_term_ee. Removed
154 condition which checks that the actual_termination_date should
155 be between eoy_start_date and session_date.
156 115.50 07-SEP-2006 jdevasah 5513289 Commented the cursor c_actions_with_person of
157 action_creation_for_ee procedure. This cursor is
158 no longer required since this is replaced by a dymanic
159 cursor.
160 115.51 20-02-2008 svannian 6809739 action creation cursor of ER will pick up employees
161 when either sit wages or sit tax is greater than zero
162
163 ********************************************************************************/
164
165 ----------------------------------- range_cursor -------------------------------
166 procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
167
168 l_eoy_payroll_action_id pay_payroll_actions.payroll_action_id%type;
169
170 l_business_group_id pay_payroll_actions.business_group_id%type;
171
172 l_agent_tax_unit_id pay_assignment_actions.tax_unit_id%type;
173 l_error_mesg varchar2(100);
174 l_year number ;
175 l_report_type varchar2(30) ;
176
177 begin
178
179 l_error_mesg := null ;
180
181 begin
182 select ppa.payroll_action_id
183 , ppa.business_group_id
184 , to_number(hr_us_w2_mt.get_parameter('Year',ppa1.legislative_parameters))
185 , ppa1.report_type
186 into l_eoy_payroll_action_id
187 ,l_business_group_id
188 ,l_year
189 ,l_report_type
190 from pay_payroll_actions ppa, /* EOY payroll action id */
191 pay_payroll_actions ppa1 /* PYUGEN payroll action id */
192 where ppa1.payroll_action_id = pactid
193 and ppa.effective_date = ppa1.effective_date
194 and ppa.report_type = 'YREND'
195 and hr_us_w2_mt.get_parameter
196 ('GRE_ID',ppa1.legislative_parameters) =
197 hr_us_w2_mt.get_parameter
198 ('TRANSFER_GRE',ppa.legislative_parameters);
199 exception
200 when others then
201 hr_utility.trace('Legislative parameters not found for pactid '||to_char(pactid));
202 raise;
203 end;
204
205 -- If it is not a PR W2 Report then only do the preprocess Agent GRE check
206 if l_report_type <> 'PRW2PAPER' then
207 hr_utility.trace('Checking for Preprocess Agent GRE setup');
208 hr_us_w2_rep.get_agent_tax_unit_id ( l_business_group_id
209 ,l_year
210 ,l_agent_tax_unit_id
211 ,l_error_mesg ) ;
212
213 if l_error_mesg is not null then
214
215 if substr(l_error_mesg,1,45) is not null then
216 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
217 pay_core_utils.push_token('record_name',' ');
218 pay_core_utils.push_token('description',substr(l_error_mesg,1,45));
219 end if;
220
221 if substr(l_error_mesg,46,45) is not null then
222 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
223 pay_core_utils.push_token('record_name',' ');
224 pay_core_utils.push_token('description',substr(l_error_mesg,46,45));
225 end if;
226
227 if substr(l_error_mesg,91,45) is not null then
228 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
229 pay_core_utils.push_token('record_name',' ');
230 pay_core_utils.push_token('description',substr(l_error_mesg,91,45));
231
232
233 end if;
234
235 if substr(l_error_mesg,136,45) is not null then
236 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
237 pay_core_utils.push_token('record_name',' ');
238 pay_core_utils.push_token('description',substr(l_error_mesg,136,45));
239 end if;
240
241 hr_utility.raise_error;
242 end if;
243
244 end if;
245
246
247 hr_utility.trace('Before the range cursor');
248 hr_utility.trace('EOY Payroll action id = '||l_eoy_payroll_action_id);
249
250 sqlstr :=
251
252 'SELECT
253 to_number(paa_arch.serial_number)
254 FROM
255 PAY_ASSIGNMENT_ACTIONS paa_arch
256 WHERE paa_arch.payroll_action_id = ' || l_eoy_payroll_action_id ||
257 ' AND :payroll_action_id is not null
258 AND paa_arch.action_status = ''C''
259 order by to_number(paa_arch.serial_number) ';
260
261 hr_utility.trace('After the range cursor');
262
263 end range_cursor;
264
265
266 FUNCTION action_creation_term_ee (p_select IN varchar2,
267 p_where IN varchar2,
268 p_eoy_start_date IN date,
269 p_session_date IN date)
270 RETURN VARCHAR2 IS
271 c_select varchar2(32767);
272 c_where varchar2(32767);
273 c_complete_sql varchar2(32767);
274 begin
275 c_select := p_select || ',per_periods_of_service PDS ';
276 c_where := p_where ||
277 ' and pds.actual_termination_date is not null
278 and pds.period_of_service_id = paf.period_of_service_id ';
279
280 -- Bug 5169849 : This is not needed as employee is already archived by Year End Pre Process. Also, the
281 -- actual_termination_date can be prior to p_eoy_start_date. But it cannot be null.
282 /*
283 c_where := p_where ||
284 ' and nvl(pds.actual_termination_date,paf.effective_end_date) between ' ||
285 '''' || p_eoy_start_date || ''' and '''
286 || p_session_date
287 ||''' and pds.period_of_service_id = paf.period_of_service_id ';
288 */
289 c_complete_sql := c_select|| c_where;
290 return c_complete_sql;
291
292
293 end;
294
295
296 ---------------------------------- action_creation_for_ee -----------------------------
297 procedure action_creation_for_ee(
298 pactid in number,
299 stperson in number,
300 endperson in number,
301 chunk in number,
302 p_year in number,
303 p_gre_id in number,
304 p_org_id in number,
305 p_loc_id in number,
306 p_per_id in number,
307 p_ssn in varchar2,
308 p_state_code in pay_us_states.state_code%type,
309 p_asg_set_id in number,
310 p_session_date in date,
311 p_eoy_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
312 p_eoy_start_date in date,
313 p_report_type in varchar2 ,
314 p_locality_code in varchar2,
315 p_print_term in varchar2) is
316
317 lockingactid number;
318 lockedactid number;
319 assignid number;
320 greid number;
321 num number;
322 l_effective_end_date DATE;
323
324 l_effective_date DATE; /* 4152323 variables definitions start */
325 l_report_type pay_payroll_actions.report_type%type;
326 l_report_category pay_payroll_actions.report_category%type;
327 l_report_qualifier pay_payroll_actions.report_qualifier%type;
328 l_report_format pay_report_format_mappings_f.report_format%type;
329 l_range_person_on BOOLEAN;
330
331 l_procedure_name VARCHAR2(100);
332
333 /* when person is selected */
334 -- Bug# 5513289 : This cursor is not needed. A dynamic cursor created to replace
335 -- this to fix this bug.
336 /* CURSOR c_actions_with_person is
337 SELECT paa_arch.assignment_action_id,
338 paa_arch.assignment_id,
339 paa_arch.tax_unit_id,
340 paf.effective_end_date
341 FROM per_assignments_f paf,
342 pay_assignment_actions paa_arch
343 WHERE paa_arch.payroll_action_id = p_eoy_payroll_action_id
344 AND paa_arch.action_status = 'C'
345 AND paf.PERSON_ID = p_per_id
346 AND paa_arch.assignment_id = paf.assignment_id
347 AND paf.effective_start_date = (select max(paf2.effective_start_date)
348 from per_assignments_f paf2
349 where paf2.assignment_id = paf.assignment_id
350 and paf2.effective_start_date <= p_session_date)
351 AND paf.effective_end_date >= p_eoy_start_date
352 AND paf.assignment_type = 'E'
353 AND paf.person_id between stperson and endperson;
354 */
355 CURSOR c_state_context (p_context_name varchar2) is
356 select context_id from ff_contexts
357 where context_name = p_context_name;
358
359 l_tuid_context ff_contexts.context_id%TYPE;
360 l_juri_context ff_contexts.context_id%TYPE;
361
362 CURSOR c_state_ueid (p_user_entity_name varchar2) is
363 select user_entity_id
364 from ff_user_entities
365 where user_entity_name = p_user_entity_name
366 and legislation_code = 'US';
367
368 l_city_wk_whld ff_user_entities.user_entity_name%TYPE;
369 l_subj_whable ff_user_entities.user_entity_name%TYPE;
370 l_subj_nwhable ff_user_entities.user_entity_name%TYPE;
371 l_county_wheld ff_user_entities.user_entity_name%TYPE;
372 l_school_wheld ff_user_entities.user_entity_name%TYPE;
373
374 TYPE RefCurType is REF CURSOR;
375 c_actions_no_selection RefCurType;
376 c_actions_with_location RefCurType;
377 c_actions_with_org RefCurType;
378 c_actions_with_state RefCurType;
379 c_actions_with_assign_set RefCurType;
380 c_actions_with_person RefCurType;
381
382 c_actions_no_selection_sql varchar2(10000);
383 c_actions_with_location_sql varchar2(10000);
384 c_actions_with_org_sql varchar2(10000);
385 c_actions_with_state_sql varchar2(10000);
386 c_actions_with_assign_sql varchar2(10000);
387 c_actions_with_person_sql varchar2(10000);
388 c_print_term_employee varchar2(10000);
389 c_actions_where varchar2(10000);
390
391 begin
392 l_procedure_name := 'action_creation_for_ee';
393 hr_utility.set_location(l_procedure_name, 1);
394 /* 4152323 { */
395 select effective_date,
396 report_type,
397 report_qualifier,
398 report_category
399 into l_effective_date,
400 l_report_type,
401 l_report_qualifier,
402 l_report_category
403 from pay_payroll_actions
404 where payroll_action_id = pactid;
405
406 Begin
407 select report_format
408 into l_report_format
409 from pay_report_format_mappings_f
410 where report_type = l_report_type
411 and report_qualifier = l_report_qualifier
412 and report_category = l_report_category
413 and l_effective_date between
414 effective_start_date and effective_end_date;
415 Exception
416 When Others Then
417 l_report_format := Null ;
418 End ;
419
420 hr_utility.set_location(l_procedure_name, 2);
421 l_range_person_on := pay_ac_utility.range_person_on
422 ( p_report_type => l_report_type,
423 p_report_format => l_report_format,
424 p_report_qualifier => l_report_qualifier,
425 p_report_category => l_report_category);
426 /* } 4152323 */
427
428
429 /* when no selection is entered */
430 if((p_loc_id is null ) and
431 (p_org_id is null ) and
432 (p_per_id is null ) and
433 (p_ssn is null ) and
434 (p_state_code is null ) and
435 (p_asg_set_id is null )) then
436
437 hr_utility.set_location(l_procedure_name, 5);
438 if l_range_person_on = TRUE Then
439 hr_utility.set_location(l_procedure_name, 10);
440 hr_utility.trace('Range Person ID Functionality is enabled') ;
441 c_actions_no_selection_sql :=
442 'SELECT paa_arch.assignment_action_id,
443 paa_arch.assignment_id,
444 paa_arch.tax_unit_id,
445 paf.effective_end_date
446 FROM per_assignments_f paf,
447 pay_assignment_actions paa_arch,
448 pay_population_ranges ppr ';
449
450 c_actions_where :=
451 ' WHERE paa_arch.action_status = ''C''
452 AND paa_arch.payroll_action_id +0= ' || p_eoy_payroll_action_id || '
453 AND paa_arch.assignment_id = paf.assignment_id
454 AND paf.effective_start_date =
455 (select max(paf2.effective_start_date)
456 from per_assignments_f paf2
457 where paf2.assignment_id = paf.assignment_id
458 and paf2.effective_start_date <= ''' || p_session_date || ''')
459 AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
460 AND paf.assignment_type = ''E''
461 and paf.primary_flag = ''Y''
462 AND ppr.payroll_action_id = ' || pactid || '
463 AND ppr.chunk_number = ' || chunk || '
464 AND paf.person_id = ppr.person_id
465 and paf.person_id = to_number(paa_arch.serial_number)';
466
467 IF nvl(p_print_term,'N') = 'Y' THEN
468 /* c_actions_no_selection_sql := c_actions_no_selection_sql ||
469 ',per_periods_of_service PDS ';
470 c_actions_where := c_actions_where ||
471 ' and nvl(pds.actual_termination_date,paf.effective_end_date) between ' ||
472 '''' || p_eoy_start_date || ''' and '''
473 || p_session_date
474 ||''' and pds.period_of_service_id = paf.period_of_service_id ';
475 */
476 c_actions_no_selection_sql := action_creation_term_ee (c_actions_no_selection_sql,
477 c_actions_where,
478 p_eoy_start_date,
479 p_session_date);
480 ELSE
481 c_actions_no_selection_sql := c_actions_no_selection_sql || c_actions_where;
482 END IF;
483
484
485 -- c_actions_no_selection_sql := c_actions_no_selection_sql || c_actions_where;
486 hr_utility.trace(' c_actions_no_selection_sql' ||c_actions_no_selection_sql);
487 else
488 hr_utility.set_location(l_procedure_name, 15);
489 c_actions_no_selection_sql :=
490 'SELECT paa_arch.assignment_action_id,
491 paa_arch.assignment_id,
492 paa_arch.tax_unit_id,
493 paf.effective_end_date
494 FROM per_assignments_f paf,
495 pay_assignment_actions paa_arch ';
496
497 c_actions_where := ' WHERE paa_arch.action_status = ''C''
498 AND paa_arch.payroll_action_id +0= ' || p_eoy_payroll_action_id || '
499 AND paa_arch.assignment_id = paf.assignment_id
500 AND paf.effective_start_date =
501 (select max(paf2.effective_start_date)
502 from per_assignments_f paf2
503 where paf2.assignment_id = paf.assignment_id
504 and paf2.effective_start_date <= ''' || p_session_date || ''')
505 AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
506 AND paf.assignment_type = ''E''
507 and paf.primary_flag = ''Y''
508 AND paf.person_id between ' || stperson || ' and ' || endperson || '
509 and paf.person_id = to_number(paa_arch.serial_number) ';
510
511 IF nvl(p_print_term,'N') = 'Y' THEN
512 c_actions_no_selection_sql := action_creation_term_ee (c_actions_no_selection_sql,
513 c_actions_where,
514 p_eoy_start_date,
515 p_session_date);
516 ELSE
517 c_actions_no_selection_sql := c_actions_no_selection_sql || c_actions_where;
518 END IF;
519 hr_utility.trace(' c_actions_no_selection_sql' ||c_actions_no_selection_sql);
520
521 end if ;
522
523 hr_utility.set_location(l_procedure_name, 20);
524 OPEN c_actions_no_selection FOR c_actions_no_selection_sql;
525 num := 0;
526
527 loop
528 fetch c_actions_no_selection into lockedactid,assignid,greid,l_effective_end_date;
529 if c_actions_no_selection%found then
530 num := num + 1;
531 hr_utility.trace('In the c_actions_no_selection%found in action cursor');
532 else
533 hr_utility.trace('In the c_actions_no_selection%notfound in action cursor');
534 exit;
535 end if;
536
537 if pay_us_employee_payslip_web.get_doc_eit('W-2',
538 'PRINT',
539 'ASSIGNMENT',
540 assignid,
541 l_effective_end_date) = 'Y' then
542
543 -- we need to insert one action for each of the
544 -- rows that we return from the cursor (i.e. one
545 -- for each assignment/pre-payment/reversal).
546 hr_utility.set_location(l_procedure_name, 25);
547 hr_utility.trace('Before inserting the action record');
548
549 select pay_assignment_actions_s.nextval
550 into lockingactid
551 from dual;
552
553 -- insert the action record.
554 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
555
556 -- Update serial_numbrt of Pay_assignment_actions with the
557 -- assignment_action_id .
558 update pay_assignment_actions
559 set serial_number = lockedactid
560 where assignment_action_id = lockingactid;
561 end if;
562 end loop;
563 close c_actions_no_selection;
564
565 end if;
566 hr_utility.set_location(l_procedure_name, 30);
567
568 /* when location is entered */
569 if p_loc_id is not null then
570 if l_range_person_on = TRUE Then
571 hr_utility.set_location(l_procedure_name, 35);
572 c_actions_with_location_sql :=
573 'SELECT paa_arch.assignment_action_id,
574 paa_arch.assignment_id,
575 paa_arch.tax_unit_id,
576 paf.effective_end_date
577 FROM per_periods_of_service pps,
578 per_assignments_f paf,
579 pay_assignment_actions paa_arch,
580 pay_population_ranges ppr ';
581 c_actions_where := '
582 /* disabling the index for performance reason */
583 WHERE paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
584 AND paa_arch.action_status = ''C''
585 AND paa_arch.assignment_id = paf.assignment_id
586 AND nvl(pps.final_process_date,''' || p_session_date || ''')
587 between paf.effective_start_date
588 and paf.effective_end_date
589 AND paf.location_id = ' || p_loc_id || '
590 AND paf.effective_start_date =
591 (select max(paf2.effective_start_date)
592 from per_assignments_f paf2
593 where paf2.assignment_id = paf.assignment_id
594 and paf2.effective_start_date <= ''' || p_session_date || ''')
595 AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
596 AND paf.assignment_type = ''E''
597 AND pps.period_of_service_id = paf.period_of_service_id
598 AND ppr.payroll_action_id = ' || pactid || '
599 AND ppr.chunk_number = ' || chunk || '
600 AND paf.person_id = ppr.person_id ' ;
601
602 else
603 hr_utility.set_location(l_procedure_name, 40);
604 c_actions_with_location_sql :=
605 'SELECT paa_arch.assignment_action_id,
606 paa_arch.assignment_id,
607 paa_arch.tax_unit_id,
608 paf.effective_end_date
609 FROM per_periods_of_service pps,
610 per_assignments_f paf,
611 pay_assignment_actions paa_arch ';
612 c_actions_where := '
613 /* disabling the index for performance reason */
614 WHERE paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
615 AND paa_arch.action_status = ''C''
616 AND paa_arch.assignment_id = paf.assignment_id
617 AND nvl(pps.final_process_date,''' || p_session_date || ''')
618 between paf.effective_start_date
619 and paf.effective_end_date
620 AND paf.location_id = ' || p_loc_id || '
621 AND paf.effective_start_date =
622 (select max(paf2.effective_start_date)
623 from per_assignments_f paf2
624 where paf2.assignment_id = paf.assignment_id
625 and paf2.effective_start_date <= ''' || p_session_date || ''' )
626 AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
627 AND paf.assignment_type = ''E''
628 AND pps.period_of_service_id = paf.period_of_service_id
629 AND paf.person_id between ' || stperson || ' and ' || endperson || '';
630
631 end if ;
632
633 IF nvl(p_print_term,'N') = 'Y' THEN
634 c_actions_with_location_sql := action_creation_term_ee (c_actions_with_location_sql,
635 c_actions_where,
636 p_eoy_start_date,
637 p_session_date);
638 ELSE
639 c_actions_with_location_sql := c_actions_with_location_sql || c_actions_where;
640 END IF;
641 hr_utility.trace(' c_actions_with_location_sql ' ||c_actions_with_location_sql);
642
643
644 hr_utility.set_location(l_procedure_name, 40);
645 OPEN c_actions_with_location FOR c_actions_with_location_sql;
646 num := 0;
647
648 loop
649 fetch c_actions_with_location into lockedactid,assignid,greid,l_effective_end_date;
650
651 if c_actions_with_location%found then
652 num := num + 1;
653 hr_utility.trace('In the c_actions_with_location%found in action cursor');
654 else
655 hr_utility.trace('In the c_actions_with_location%notfound in action cursor');
656 exit;
657 end if;
658
659 if pay_us_employee_payslip_web.get_doc_eit('W-2',
660 'PRINT',
661 'ASSIGNMENT',
662 assignid,
663 l_effective_end_date) = 'Y' then
664
665 -- we need to insert one action for each of the
666 -- rows that we return from the cursor (i.e. one
667 -- for each assignment/pre-payment/reversal).
668 hr_utility.set_location(l_procedure_name, 45);
669 hr_utility.trace('Before inserting the action record');
670
671 select pay_assignment_actions_s.nextval
672 into lockingactid
673 from dual;
674
675 -- insert the action record.
676 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
677
678 -- Update serial_numbrt of Pay_assignment_actions with the
679 -- assignment_action_id .
680 update pay_assignment_actions
681 set serial_number = lockedactid
682 where assignment_action_id = lockingactid;
683 end if;
684 end loop;
685 close c_actions_with_location;
686
687 end if;
688 hr_utility.set_location(l_procedure_name, 50);
689
690
691 /* when org is entered */
692 if p_org_id is not null then
693
694 if l_range_person_on = TRUE Then
695 hr_utility.set_location(l_procedure_name, 60);
696 hr_utility.trace('Range Person ID Functionality is enabled') ;
697 c_actions_with_org_sql :=
698 'SELECT paa_arch.assignment_action_id,
699 paa_arch.assignment_id,
700 paa_arch.tax_unit_id,
701 paf.effective_end_date
702 FROM per_periods_of_service pps,
703 per_assignments_f paf,
704 pay_assignment_actions paa_arch,
705 pay_population_ranges ppr ';
706 c_actions_where := '
707 /* disabling the index for performance reason */
708 WHERE paa_arch.payroll_action_id +0 = ' || p_eoy_payroll_action_id || '
709 AND paa_arch.action_status = ''C''
710 AND nvl(pps.final_process_date,''' || p_session_date || ''')
711 between paf.effective_start_date
712 and paf.effective_end_date
713 AND paf.organization_id = ' || p_org_id || '
714 AND paa_arch.assignment_id = paf.assignment_id
715 AND paf.effective_start_date =
716 (select max(paf2.effective_start_date)
717 from per_assignments_f paf2
718 where paf2.assignment_id = paf.assignment_id
719 and paf2.effective_start_date <= ''' || p_session_date || ''')
720 AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
721 AND paf.assignment_type = ''E''
722 AND pps.period_of_service_id = paf.period_of_service_id
723 AND ppr.payroll_action_id = ' || pactid || '
724 AND ppr.chunk_number = ' || chunk || '
725 AND paf.person_id = ppr.person_id ';
726
727
728 else
729 hr_utility.set_location(l_procedure_name, 70);
730 c_actions_with_org_sql :=
731 'SELECT paa_arch.assignment_action_id,
732 paa_arch.assignment_id,
733 paa_arch.tax_unit_id,
734 paf.effective_end_date
735 FROM per_periods_of_service pps,
736 per_assignments_f paf,
737 pay_assignment_actions paa_arch ';
738 c_actions_where := '
739 /* disabling the index for performance reason */
740 WHERE paa_arch.payroll_action_id +0 = ' || p_eoy_payroll_action_id || '
741 AND paa_arch.action_status = ''C''
742 AND nvl(pps.final_process_date,''' || p_session_date || ''')
743 between paf.effective_start_date
744 and paf.effective_end_date
745 AND paf.organization_id = ' || p_org_id || '
746 AND paa_arch.assignment_id = paf.assignment_id
747 AND paf.effective_start_date =
748 (select max(paf2.effective_start_date)
749 from per_assignments_f paf2
750 where paf2.assignment_id = paf.assignment_id
751 and paf2.effective_start_date <= ''' || p_session_date || ''')
752 AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
753 AND paf.assignment_type = ''E''
754 AND pps.period_of_service_id = paf.period_of_service_id
755 AND paf.person_id between ' || stperson || ' and ' || endperson ||'';
756 end if ;
757
758 IF nvl(p_print_term,'N') = 'Y' THEN
759 c_actions_with_org_sql := action_creation_term_ee (c_actions_with_org_sql,
760 c_actions_where,
761 p_eoy_start_date,
762 p_session_date);
763 ELSE
764 c_actions_with_org_sql := c_actions_with_org_sql || c_actions_where;
765 END IF;
766 hr_utility.trace(' c_actions_with_org_sql ' ||c_actions_with_org_sql);
767
768
769 hr_utility.set_location(l_procedure_name, 80);
770 OPEN c_actions_with_org FOR c_actions_with_org_sql;
771 num := 0;
772
773 loop
774 fetch c_actions_with_org into lockedactid,assignid,greid,l_effective_end_date;
775
776 if c_actions_with_org%found then
777 num := num + 1;
778 hr_utility.trace('In the c_actions_with_org%found in action cursor');
779 else
780 hr_utility.trace('In the c_actions_with_org%notfound in action cursor');
781 exit;
782 end if;
783
784 if pay_us_employee_payslip_web.get_doc_eit('W-2',
785 'PRINT',
786 'ASSIGNMENT',
787 assignid,
788 l_effective_end_date) = 'Y' then
789
790 -- we need to insert one action for each of the
791 -- rows that we return from the cursor (i.e. one
792 -- for each assignment/pre-payment/reversal).
793 hr_utility.set_location(l_procedure_name, 90);
794 hr_utility.trace('Before inserting the action record');
795
796 select pay_assignment_actions_s.nextval
797 into lockingactid
798 from dual;
799
800 -- insert the action record.
801 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
802
803 -- Update serial_numbrt of Pay_assignment_actions with the
804 -- assignment_action_id .
805 update pay_assignment_actions
806 set serial_number = lockedactid
807 where assignment_action_id = lockingactid;
808 end if;
809 end loop;
810 close c_actions_with_org;
811
812 end if;
813
814 hr_utility.set_location(l_procedure_name, 100);
815 /* when person or SSN is entered */
816 if (p_per_id is not null OR p_ssn is not null ) then
817
818 /* Bug# 5513289: If p_print_term is set to 'Y' then assignment_action_ids
819 of terminated employees alone are selected */
820 c_actions_with_person_sql := 'SELECT paa_arch.assignment_action_id,
821 paa_arch.assignment_id,
822 paa_arch.tax_unit_id,
823 paf.effective_end_date
824 FROM per_assignments_f paf,
825 pay_assignment_actions paa_arch';
826
827 c_actions_where := '
828 WHERE paa_arch.payroll_action_id = ' || p_eoy_payroll_action_id ||'
829 AND paa_arch.action_status = ''C''
830 AND paf.PERSON_ID = '|| p_per_id || '
831 AND paa_arch.assignment_id = paf.assignment_id
832 AND paf.effective_start_date = (select max(paf2.effective_start_date)
833 from per_assignments_f paf2
834 where paf2.assignment_id = paf.assignment_id
835 and paf2.effective_start_date <= ''' ||p_session_date ||''')
836 AND paf.effective_end_date >='''|| p_eoy_start_date || '''
837 AND paf.assignment_type = ''E''
838 AND paf.person_id between ' || stperson || ' and ' || endperson ||' ';
839
840 IF nvl(p_print_term,'N') = 'Y' THEN
841 c_actions_with_person_sql := action_creation_term_ee (c_actions_with_person_sql,
842 c_actions_where,
843 p_eoy_start_date,
844 p_session_date);
845 ELSE
846 c_actions_with_person_sql := c_actions_with_person_sql || c_actions_where;
847 END IF;
848 hr_utility.trace(' c_actions_with_person_sql ' ||c_actions_with_person_sql);
849
850
851 open c_actions_with_person for c_actions_with_person_sql;
852 /* Bug# 5513289 :Ending here */
853 num := 0;
854 loop
855 hr_utility.set_location('procpyr',2);
856 hr_utility.trace('after the loop in c_actions_with_person');
857 fetch c_actions_with_person into lockedactid,assignid,greid,l_effective_end_date;
858
859 if c_actions_with_person%found then
860 num := num + 1;
861 hr_utility.trace('In the c_actions_with_person%found in action cursor');
862 else
863 hr_utility.trace('In the c_actions_with_person%notfound in action cursor');
864 exit;
865 end if;
866
867 if pay_us_employee_payslip_web.get_doc_eit('W-2',
868 'PRINT',
869 'ASSIGNMENT',
870 assignid,
871 l_effective_end_date) = 'Y' then
872
873 -- we need to insert one action for each of the
874 -- rows that we return from the cursor (i.e. one
875 -- for each assignment/pre-payment/reversal).
876 hr_utility.set_location(l_procedure_name, 110);
877 hr_utility.trace('Before inserting the action record');
878
879 select pay_assignment_actions_s.nextval
880 into lockingactid
881 from dual;
882
883 -- insert the action record.
884 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
885
886 -- Update serial_numbrt of Pay_assignment_actions with the
887 -- assignment_action_id .
888 update pay_assignment_actions
889 set serial_number = lockedactid
890 where assignment_action_id = lockingactid;
891 end if;
892 end loop;
893 close c_actions_with_person;
894
895 end if;
896
897 hr_utility.set_location(l_procedure_name, 120);
898 /* when state is entered */
899 if p_state_code is not null then
900 hr_utility.set_location(l_procedure_name, 130);
901
902 hr_utility.trace('p_state_code = ' || p_state_code);
903 open c_state_context('TAX_UNIT_ID');
904 fetch c_state_context into l_tuid_context;
905 close c_state_context;
906
907 open c_state_context('JURISDICTION_CODE');
908 fetch c_state_context into l_juri_context;
909 close c_state_context;
910
911 if l_range_person_on = TRUE Then
912 hr_utility.set_location(l_procedure_name, 140);
913 hr_utility.trace('Range Person ID Functionality is enabled') ;
914 c_actions_with_state_sql :=
915 'SELECT paa_arch.assignment_action_id,
916 paa_arch.assignment_id,
917 paa_arch.tax_unit_id,
918 paf.effective_end_date
919 FROM per_assignments_f paf,
920 pay_assignment_actions paa_arch,
921 pay_population_ranges ppr ';
922 c_actions_where := '
923 WHERE paa_arch.payroll_action_id +0 = ' || p_eoy_payroll_action_id || '
924 AND paa_arch.action_status = ''C''
925 AND paa_arch.assignment_id = paf.assignment_id
926 AND paf.effective_start_date =
927 (select max(paf2.effective_start_date)
928 from per_assignments_f paf2
929 where paf2.assignment_id = paf.assignment_id
930 and paf2.effective_start_date <= ''' || p_session_date || ''')
931 AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
932 AND paf.assignment_type = ''E''
933 AND ppr.payroll_action_id = ' || pactid || '
934 AND ppr.chunk_number = ' || chunk || '
935 AND paf.person_id = ppr.person_id ';
936 else
937 hr_utility.set_location(l_procedure_name, 150);
938 c_actions_with_state_sql :=
939 'SELECT paa_arch.assignment_action_id,
940 paa_arch.assignment_id,
941 paa_arch.tax_unit_id,
942 paf.effective_end_date
943 FROM per_assignments_f paf,
944 pay_assignment_actions paa_arch ';
945 c_actions_where := '
946 WHERE paa_arch.payroll_action_id +0 = ' || p_eoy_payroll_action_id || '
947 AND paa_arch.action_status = ''C''
948 AND paa_arch.assignment_id = paf.assignment_id
949 AND paf.effective_start_date =
950 (select max(paf2.effective_start_date)
951 from per_assignments_f paf2
952 where paf2.assignment_id = paf.assignment_id
953 and paf2.effective_start_date <= ''' || p_session_date || ''')
954 AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
955 AND paf.assignment_type = ''E''
956 AND paf.person_id between ' || stperson || ' and ' || endperson;
957 end if;
958
959 IF nvl(p_print_term,'N') = 'Y' THEN
960 c_actions_with_state_sql := action_creation_term_ee (c_actions_with_state_sql,
961 c_actions_where,
962 p_eoy_start_date,
963 p_session_date);
964 ELSE
965 c_actions_with_state_sql := c_actions_with_state_sql || c_actions_where;
966 END IF;
967 hr_utility.trace(' c_actions_with_state_sql ' ||c_actions_with_state_sql);
968
969
970 hr_utility.set_location(l_procedure_name, 160);
971 if p_locality_code is null then
972 open c_state_ueid('A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD');
973 fetch c_state_ueid into l_subj_whable;
974 close c_state_ueid;
975
976 open c_state_ueid('A_SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD');
977 fetch c_state_ueid into l_subj_nwhable;
978 close c_state_ueid;
979
980 hr_utility.set_location(l_procedure_name, 170);
981 c_actions_with_state_sql := c_actions_with_state_sql ||
982 ' AND exists ( select 1 from dual
983 where 1 =
984 (select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
985 from ff_archive_items fai,
986 ff_archive_item_contexts fic1,
987 ff_archive_item_contexts fic2
988 where fai.context1 = paa_arch.assignment_action_id
989 and fai.user_entity_id in (' || l_subj_whable || ',
990 ' || l_subj_nwhable || ')
991 and fai.archive_item_id = fic1.archive_item_id
992 and fic1.context_id = ' || l_tuid_context || '
993 and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
994 and fai.archive_item_id = fic2.archive_item_id
995 and fic2.context_id = ' || l_juri_context || '
996 and substr(ltrim(rtrim(fic2.context)),1,2) = ' || p_state_code || ' ))';
997 --
998 -- County
999 --
1000 elsif length(p_locality_code) = 11 and
1001 substr(p_locality_code, 8,4) = '0000' then
1002 hr_utility.set_location(l_procedure_name, 180);
1003 --Bug #4886044
1004 -- Added the check for the User Entity 'A_COUNTY_WITHHELD_PER_JD_GRE_YTD'
1005 -- Only employees who have both Wages and Taxes withheld
1006 -- from the specified locality shoule be reported for that
1007 -- The below exist clause will check the Tax part. The following exist clause
1008 -- checks the Wages part of the query.
1009 open c_state_ueid('A_COUNTY_WITHHELD_PER_JD_GRE_YTD');
1010 fetch c_state_ueid into l_county_wheld;
1011 close c_state_ueid;
1012
1013 c_actions_with_state_sql := c_actions_with_state_sql ||
1014 ' AND exists ( select 1 from dual
1015 where 1 =
1016 (select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
1017 from ff_archive_items fai,
1018 ff_archive_item_contexts fic1,
1019 ff_archive_item_contexts fic2
1020 where fai.context1 = paa_arch.assignment_action_id
1021 and fai.user_entity_id in (' || l_county_wheld || ')
1022 and fai.archive_item_id = fic1.archive_item_id
1023 and fic1.context_id = ' || l_tuid_context || '
1024 and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
1025 and fai.archive_item_id = fic2.archive_item_id
1026 and fic2.context_id = ' || l_juri_context || '
1027 and substr(ltrim(rtrim(fic2.context)),1,6) = substr(''' || p_locality_code || ''',1,6) ))';
1028
1029 open c_state_ueid('A_COUNTY_SUBJ_WHABLE_PER_JD_GRE_YTD');
1030 fetch c_state_ueid into l_subj_whable;
1031 close c_state_ueid;
1032
1033 open c_state_ueid('A_COUNTY_SUBJ_NWHABLE_PER_JD_GRE_YTD');
1034 fetch c_state_ueid into l_subj_nwhable;
1035 close c_state_ueid;
1036
1037 c_actions_with_state_sql := c_actions_with_state_sql ||
1038 ' AND exists ( select 1 from dual
1039 where 1 =
1040 (select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
1041 from ff_archive_items fai,
1042 ff_archive_item_contexts fic1,
1043 ff_archive_item_contexts fic2
1044 where fai.context1 = paa_arch.assignment_action_id
1045 and fai.user_entity_id in (' || l_subj_whable || ',
1046 ' || l_subj_nwhable || ')
1047 and fai.archive_item_id = fic1.archive_item_id
1048 and fic1.context_id = ' || l_tuid_context || '
1049 and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
1050 and fai.archive_item_id = fic2.archive_item_id
1051 and fic2.context_id = ' || l_juri_context || '
1052 and substr(ltrim(rtrim(fic2.context)),1,6) = substr(''' || p_locality_code || ''',1,6) ))';
1053 --
1054 -- City
1055 --
1056 elsif length(p_locality_code) = 11 and
1057 substr(p_locality_code, 8,4) <> '0000' then
1058 hr_utility.set_location(l_procedure_name, 190);
1059
1060 -- Bug 4645408
1061 -- Added the check for the User Entity 'A_CITY_WK_WITHHELD_PER_JD_GRE_YTD'
1062 -- Only employees who have both Wages and Taxes withheld
1063 -- from the specified locality shoule be reported for that
1064 -- The below exist clause will check the Tax part. The following exist clause
1065 -- checks the Wages part of the query.
1066 -- open c_state_ueid('A_CITY_WK_WITHHELD_PER_JD_GRE_YTD');
1067 open c_state_ueid('A_CITY_WITHHELD_PER_JD_GRE_YTD'); /* 6909926 */
1068 fetch c_state_ueid into l_city_wk_whld;
1069 close c_state_ueid;
1070
1071 hr_utility.set_location(l_procedure_name, 170);
1072 c_actions_with_state_sql := c_actions_with_state_sql ||
1073 ' AND exists ( select 1 from dual
1074 where 1 =
1075 (select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
1076 from ff_archive_items fai,
1077 ff_archive_item_contexts fic1,
1078 ff_archive_item_contexts fic2
1079 where fai.context1 = paa_arch.assignment_action_id
1080 and fai.user_entity_id in (' || l_city_wk_whld || ')
1081 and fai.archive_item_id = fic1.archive_item_id
1082 and fic1.context_id = ' || l_tuid_context || '
1083 and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
1084 and fai.archive_item_id = fic2.archive_item_id
1085 and fic2.context_id = ' || l_juri_context || '
1086 and substr(ltrim(rtrim(fic2.context)),1,11) = ''' || p_locality_code || ''' ))';
1087
1088 open c_state_ueid('A_CITY_SUBJ_WHABLE_PER_JD_GRE_YTD');
1089 fetch c_state_ueid into l_subj_whable;
1090 close c_state_ueid;
1091
1092 open c_state_ueid('A_CITY_SUBJ_NWHABLE_PER_JD_GRE_YTD');
1093 fetch c_state_ueid into l_subj_nwhable;
1094 close c_state_ueid;
1095
1096 c_actions_with_state_sql := c_actions_with_state_sql ||
1097 ' AND exists ( select 1 from dual
1098 where 1 =
1099 (select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
1100 from ff_archive_items fai,
1101 ff_archive_item_contexts fic1,
1102 ff_archive_item_contexts fic2
1103 where fai.context1 = paa_arch.assignment_action_id
1104 and fai.user_entity_id in (' || l_subj_whable || ',
1105 ' || l_subj_nwhable || ')
1106 and fai.archive_item_id = fic1.archive_item_id
1107 and fic1.context_id = ' || l_tuid_context || '
1108 and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
1109 and fai.archive_item_id = fic2.archive_item_id
1110 and fic2.context_id = ' || l_juri_context || '
1111 and substr(ltrim(rtrim(fic2.context)),1,11) = ''' || p_locality_code || ''' ))';
1112 --
1113 -- School District
1114 --
1115 elsif length(p_locality_code) = 8 then
1116 hr_utility.set_location(l_procedure_name, 200);
1117 --Bug #4886044
1118 -- Added the check for the User Entity 'A_SCHOOL_WITHHELD_PER_JD_GRE_YTD'
1119 -- Only employees who have both Wages and Taxes withheld
1120 -- from the specified locality shoule be reported for that
1121 -- The below exist clause will check the Tax part. The following exist clause
1122 -- checks the Wages part of the query.
1123 open c_state_ueid('A_SCHOOL_WITHHELD_PER_JD_GRE_YTD');
1124 fetch c_state_ueid into l_school_wheld;
1125 close c_state_ueid;
1126
1127 c_actions_with_state_sql := c_actions_with_state_sql ||
1128 ' AND exists ( select 1 from dual
1129 where 1 =
1130 (select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
1131 from ff_archive_items fai,
1132 ff_archive_item_contexts fic1,
1133 ff_archive_item_contexts fic2
1134 where fai.context1 = paa_arch.assignment_action_id
1135 and fai.user_entity_id in (' || l_school_wheld || ')
1136 and fai.archive_item_id = fic1.archive_item_id
1137 and fic1.context_id = ' || l_tuid_context || '
1138 and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
1139 and fai.archive_item_id = fic2.archive_item_id
1140 and fic2.context_id = ' || l_juri_context || '
1141 and substr(ltrim(rtrim(fic2.context)),1,8) = ''' || p_locality_code || '''))';
1142
1143 open c_state_ueid('A_SCHOOL_SUBJ_WHABLE_PER_JD_GRE_YTD');
1144 fetch c_state_ueid into l_subj_whable;
1145 close c_state_ueid;
1146
1147 open c_state_ueid('A_SCHOOL_SUBJ_NWHABLE_PER_JD_GRE_YTD');
1148 fetch c_state_ueid into l_subj_nwhable;
1149 close c_state_ueid;
1150
1151 c_actions_with_state_sql := c_actions_with_state_sql ||
1152 ' AND exists ( select 1 from dual
1153 where 1 =
1154 (select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
1155 from ff_archive_items fai,
1156 ff_archive_item_contexts fic1,
1157 ff_archive_item_contexts fic2
1158 where fai.context1 = paa_arch.assignment_action_id
1159 and fai.user_entity_id in (' || l_subj_whable || ',
1160 ' || l_subj_nwhable || ')
1161 and fai.archive_item_id = fic1.archive_item_id
1162 and fic1.context_id = ' || l_tuid_context || '
1163 and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
1164 and fai.archive_item_id = fic2.archive_item_id
1165 and fic2.context_id = ' || l_juri_context || '
1166 and substr(ltrim(rtrim(fic2.context)),1,8) = ''' || p_locality_code || '''))';
1167 end if;
1168 hr_utility.set_location(l_procedure_name, 210);
1169
1170
1171 num := 0;
1172 OPEN c_actions_with_state FOR c_actions_with_state_sql;
1173 loop
1174 fetch c_actions_with_state into lockedactid,assignid,greid,l_effective_end_date;
1175
1176 if c_actions_with_state%found then
1177 num := num + 1;
1178 hr_utility.trace('In the c_actions_with_state%found in action cursor');
1179 else
1180 hr_utility.trace('In the c_actions_with_state%notfound in action cursor');
1181 exit;
1182 end if;
1183
1184 if pay_us_employee_payslip_web.get_doc_eit('W-2',
1185 'PRINT',
1186 'ASSIGNMENT',
1187 assignid,
1188 l_effective_end_date) = 'Y' then
1189
1190 -- we need to insert one action for each of the
1191 -- rows that we return from the cursor (i.e. one
1192 -- for each assignment/pre-payment/reversal).
1193 hr_utility.set_location(l_procedure_name, 220);
1194 hr_utility.trace('Before inserting the action record');
1195
1196 select pay_assignment_actions_s.nextval
1197 into lockingactid
1198 from dual;
1199
1200 -- insert the action record.
1201 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1202
1203 -- Update serial_numbrt of Pay_assignment_actions with the
1204 -- assignment_action_id .
1205 update pay_assignment_actions
1206 set serial_number = lockedactid
1207 where assignment_action_id = lockingactid;
1208 end if;
1209 end loop;
1210 close c_actions_with_state;
1211
1212 end if;
1213 hr_utility.set_location(l_procedure_name, 230);
1214
1215 /* when assignment set is entered */
1216 if p_asg_set_id is not null then
1217
1218 if l_range_person_on = TRUE Then
1219 hr_utility.set_location(l_procedure_name, 240);
1220 hr_utility.trace('Range Person ID Functionality is enabled') ;
1221 c_actions_with_assign_sql :=
1222 'SELECT paa_arch.assignment_action_id,
1223 paa_arch.assignment_id,
1224 paa_arch.tax_unit_id,
1225 paf.effective_end_date
1226 FROM per_assignments_f paf,
1227 pay_assignment_actions paa_arch,
1228 pay_population_ranges ppr ';
1229 c_actions_where := '
1230 WHERE paa_arch.action_status = ''C''
1231 AND paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
1232 AND paa_arch.assignment_id = paf.assignment_id
1233 AND paf.effective_start_date = (select max(paf2.effective_start_date)
1234 from per_assignments_f paf2
1235 where paf2.assignment_id = paf.assignment_id
1236 and paf2.effective_start_date <= ''' || p_session_date || ''')
1237 AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
1238 AND paf.assignment_type = ''E''
1239 AND ppr.payroll_action_id = ' || pactid || '
1240 AND ppr.chunk_number = ' || chunk || '
1241 AND paf.person_id = ppr.person_id
1242 AND exists ( select 1 /* Selected Assignment Set */
1243 from hr_assignment_set_amendments hasa
1244 where hasa.assignment_set_id = ' || p_asg_set_id || '
1245 and hasa.assignment_id = paa_arch.assignment_id
1246 and upper(hasa.include_or_exclude) = ''I'') ';
1247 else
1248 hr_utility.set_location(l_procedure_name, 250);
1249 c_actions_with_assign_sql :=
1250 'SELECT paa_arch.assignment_action_id,
1251 paa_arch.assignment_id,
1252 paa_arch.tax_unit_id,
1253 paf.effective_end_date
1254 FROM per_assignments_f paf,
1255 pay_assignment_actions paa_arch ';
1256 c_actions_where := '
1257 WHERE paa_arch.action_status = ''C''
1258 AND paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
1259 AND paa_arch.assignment_id = paf.assignment_id
1260 AND paf.effective_start_date = (select max(paf2.effective_start_date)
1261 from per_assignments_f paf2
1262 where paf2.assignment_id = paf.assignment_id
1263 and paf2.effective_start_date <= ''' || p_session_date || ''')
1264 AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
1265 AND paf.assignment_type = ''E''
1266 AND paf.person_id between ' || stperson || ' and ' || endperson || '
1267 AND exists ( select 1 /* Selected Assignment Set */
1268 from hr_assignment_set_amendments hasa
1269 where hasa.assignment_set_id = ' || p_asg_set_id || '
1270 and hasa.assignment_id = paa_arch.assignment_id
1271 and upper(hasa.include_or_exclude) = ''I'') ';
1272 end if ;
1273
1274 IF nvl(p_print_term,'N') = 'Y' THEN
1275 c_actions_with_assign_sql := action_creation_term_ee (c_actions_with_assign_sql,
1276 c_actions_where,
1277 p_eoy_start_date,
1278 p_session_date);
1279 ELSE
1280 c_actions_with_assign_sql := c_actions_with_assign_sql || c_actions_where;
1281 END IF;
1282 hr_utility.trace(' c_actions_with_assign_sql ' ||c_actions_with_assign_sql);
1283
1284 hr_utility.set_location(l_procedure_name, 260);
1285 OPEN c_actions_with_assign_set FOR c_actions_with_assign_sql;
1286 num := 0;
1287
1288 loop
1289 fetch c_actions_with_assign_set into lockedactid,assignid,greid,l_effective_end_date;
1290
1291 if c_actions_with_assign_set%found then
1292 num := num + 1;
1293 hr_utility.trace('In the c_actions_with_assign_set%found in action cursor');
1294 else
1295 hr_utility.trace('In the c_actions_with_assign_set%notfound in action cursor');
1296 exit;
1297 end if;
1298
1299 if pay_us_employee_payslip_web.get_doc_eit('W-2',
1300 'PRINT',
1301 'ASSIGNMENT',
1302 assignid,
1303 l_effective_end_date) = 'Y' then
1304
1305 -- we need to insert one action for each of the
1306 -- rows that we return from the cursor (i.e. one
1307 -- for each assignment/pre-payment/reversal).
1308 hr_utility.set_location(l_procedure_name, 270);
1309 hr_utility.trace('Before inserting the action record');
1310
1311 hr_utility.set_location('procpyr',3);
1312
1313 select pay_assignment_actions_s.nextval
1314 into lockingactid
1315 from dual;
1316
1317 -- insert the action record.
1318 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1319
1320 -- Update serial_numbrt of Pay_assignment_actions with the
1321 -- assignment_action_id .
1322 update pay_assignment_actions
1323 set serial_number = lockedactid
1324 where assignment_action_id = lockingactid;
1325 end if;
1326 end loop;
1327 close c_actions_with_assign_set;
1328
1329 end if;
1330 hr_utility.set_location(l_procedure_name, 300);
1331 hr_utility.trace('End of the action cursor');
1332
1333 end action_creation_for_ee;
1334
1335 ---------------------------------- action_creation_for_er -----------------------------
1336 -----
1337 --
1338 procedure action_creation_for_er(pactid in number,
1339 stperson in number,
1340 endperson in number,
1341 chunk in number,
1342 p_year in number,
1343 p_gre_id in number,
1344 p_org_id in number,
1345 p_loc_id in number,
1346 p_per_id in number,
1347 p_ssn in varchar2,
1348 p_state_code in pay_us_states.state_code%type,
1349 p_asg_set_id in number,
1350 p_session_date in date,
1351 p_eoy_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
1352 p_eoy_start_date in date) is
1353
1354 lockingactid number;
1355 lockedactid number;
1356 assignid number;
1357 greid number;
1358 num number;
1359 l_effective_end_date DATE;
1360
1361 l_effective_date DATE; /* 4152323 variables definitions start */
1362 l_report_type pay_payroll_actions.report_type%type;
1363 l_report_category pay_payroll_actions.report_category%type;
1364 l_report_qualifier pay_payroll_actions.report_qualifier%type;
1365 l_report_format pay_report_format_mappings_f.report_format%type;
1366 l_range_person_on BOOLEAN;
1367 /* 4152323 variables definitions end */
1368
1369 CURSOR c_state_context (p_context_name varchar2) is
1370
1371 select context_id from ff_contexts
1372 where context_name = p_context_name;
1373
1374 l_tuid_context ff_contexts.context_id%TYPE;
1375 l_juri_context ff_contexts.context_id%TYPE;
1376
1377 CURSOR c_state_ueid (p_user_entity_name varchar2) is
1378
1379 select user_entity_id
1380 from ff_user_entities
1381 where user_entity_name = p_user_entity_name
1382 and legislation_code = 'US';
1383
1384 l_sit_subj_whable ff_user_entities.user_entity_name%TYPE;
1385 l_sit_subj_nwhable ff_user_entities.user_entity_name%TYPE;
1386 l_sit_withheld ff_user_entities.user_entity_name%TYPE; /* 6809739 */
1387
1388 /* when person is selected */
1389
1390 CURSOR c_actions_with_person is
1391 SELECT paa_arch.assignment_action_id,
1392 paa_arch.assignment_id,
1393 paa_arch.tax_unit_id,
1394 paf.effective_end_date
1395 FROM per_assignments_f paf,
1396 pay_assignment_actions paa_arch
1397 WHERE paa_arch.payroll_action_id = p_eoy_payroll_action_id
1398 AND paa_arch.action_status = 'C'
1399 AND paf.PERSON_ID = p_per_id
1400 AND paa_arch.assignment_id = paf.assignment_id
1401 AND paf.effective_start_date = (select max(paf2.effective_start_date)
1402 from per_assignments_f paf2
1403 where paf2.assignment_id = paf.assignment_id
1404 and paf2.effective_start_date <= p_session_date)
1405 AND paf.effective_end_date >= p_eoy_start_date
1406 AND paf.assignment_type = 'E'
1407 AND paf.person_id between stperson and endperson
1408 AND exists ( select 1 from dual
1409 where 1 =
1410 (select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
1411 from ff_archive_items fai,
1412 ff_archive_item_contexts fic1,
1413 ff_archive_item_contexts fic2
1414 where fai.context1 = paa_arch.assignment_action_id
1415 and fai.user_entity_id in
1416 ( l_sit_subj_whable,
1417 l_sit_subj_nwhable,
1418 l_sit_withheld) /* 6809739 */
1419 and fai.archive_item_id = fic1.archive_item_id
1420 and fic1.context_id = l_tuid_context
1421 and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
1422 and fai.archive_item_id = fic2.archive_item_id
1423 and fic2.context_id = l_juri_context
1424 and substr(ltrim(rtrim(fic2.context)),1,2) = p_state_code )) ;
1425
1426 TYPE RefCurType is REF CURSOR;
1427 c_actions_no_selection RefCurType;
1428 c_actions_with_location RefCurType;
1429 c_actions_with_org RefCurType;
1430 c_actions_with_assign_set RefCurType;
1431
1432 c_actions_no_selection_sql varchar2(10000);
1433 c_actions_with_location_sql varchar2(10000);
1434 c_actions_with_org_sql varchar2(10000);
1435 c_actions_with_assign_sql varchar2(10000);
1436
1437 begin
1438 hr_utility.set_location('procpyr',1);
1439 hr_utility.trace('In the ER action cursor');
1440
1441 /* 4152323 { */
1442 select effective_date,
1443 report_type,
1444 report_qualifier,
1445 report_category
1446 into l_effective_date,
1447 l_report_type,
1448 l_report_qualifier,
1449 l_report_category
1450 from pay_payroll_actions
1451 where payroll_action_id = pactid;
1452
1453 Begin
1454 select report_format
1455 into l_report_format
1456 from pay_report_format_mappings_f
1457 where report_type = l_report_type
1458 and report_qualifier = l_report_qualifier
1459 and report_category = l_report_category
1460 and l_effective_date between
1461 effective_start_date and effective_end_date;
1462 Exception
1463 When Others Then
1464 l_report_format := Null ;
1465 End ;
1466
1467 l_range_person_on := pay_ac_utility.range_person_on
1468 ( p_report_type => l_report_type,
1469 p_report_format => l_report_format,
1470 p_report_qualifier => l_report_qualifier,
1471 p_report_category => l_report_category);
1472 /* } 4152323 */
1473
1474 open c_state_context('TAX_UNIT_ID');
1475 fetch c_state_context into l_tuid_context;
1476 close c_state_context;
1477
1478 open c_state_context('JURISDICTION_CODE');
1479 fetch c_state_context into l_juri_context;
1480 close c_state_context;
1481
1482 open c_state_ueid('A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD');
1483 fetch c_state_ueid into l_sit_subj_whable;
1484 close c_state_ueid;
1485
1486 open c_state_ueid('A_SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD');
1487 fetch c_state_ueid into l_sit_subj_nwhable;
1488 close c_state_ueid;
1489
1490 open c_state_ueid('A_SIT_WITHHELD_PER_JD_GRE_YTD'); /* 6809739 */
1491 fetch c_state_ueid into l_sit_withheld;
1492 close c_state_ueid;
1493
1494 /* when no selection is entered */
1495
1496 if((p_loc_id is null ) and
1497 (p_org_id is null ) and
1498 (p_per_id is null ) and
1499 (p_ssn is null ) and
1500 (p_asg_set_id is null )) then
1501
1502 if l_range_person_on = TRUE Then
1503 hr_utility.trace('Range Person ID Functionality is enabled') ;
1504 c_actions_no_selection_sql :=
1505 'SELECT paa_arch.assignment_action_id,
1506 paa_arch.assignment_id,
1507 paa_arch.tax_unit_id,
1508 paf.effective_end_date
1509 FROM per_assignments_f paf,
1510 pay_assignment_actions paa_arch,
1511 pay_population_ranges ppr
1512 WHERE paa_arch.action_status = ''C''
1513 AND paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
1514 AND paa_arch.assignment_id = paf.assignment_id
1515 AND paf.effective_start_date = (select max(paf2.effective_start_date)
1516 from per_assignments_f paf2
1517 where paf2.assignment_id = paf.assignment_id
1518 and paf2.effective_start_date <= ''' || p_session_date || ''')
1519 AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
1520 AND paf.assignment_type = ''E''
1521 and paf.primary_flag = ''Y''
1522 --AND paf.person_id between stperson and endperson
1523 and paf.person_id = to_number(paa_arch.serial_number)
1524 AND ppr.payroll_action_id = ' || pactid || '
1525 AND ppr.chunk_number = ' || chunk || '
1526 AND paf.person_id = ppr.person_id
1527 AND exists ( select 1 from dual
1528 where 1 =
1529 (select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
1530 from ff_archive_items fai,
1531 ff_archive_item_contexts fic1,
1532 ff_archive_item_contexts fic2
1533 where fai.context1 = paa_arch.assignment_action_id
1534 and fai.user_entity_id in
1535 ( ' || l_sit_subj_whable || ',
1536 ' || l_sit_subj_nwhable || ',
1537 ' || l_sit_withheld || ') /* 6809739 */
1538 and fai.archive_item_id = fic1.archive_item_id
1539 and fic1.context_id = ' || l_tuid_context || '
1540 and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
1541 and fai.archive_item_id = fic2.archive_item_id
1542 and fic2.context_id = ' || l_juri_context || '
1543 and substr(ltrim(rtrim(fic2.context)),1,2) = ' || p_state_code || ' )) ';
1544 else
1545 c_actions_no_selection_sql :=
1546 'SELECT paa_arch.assignment_action_id,
1547 paa_arch.assignment_id,
1548 paa_arch.tax_unit_id,
1549 paf.effective_end_date
1550 FROM per_assignments_f paf,
1551 pay_assignment_actions paa_arch
1552 WHERE paa_arch.action_status = ''C''
1553 AND paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
1554 AND paa_arch.assignment_id = paf.assignment_id
1555 AND paf.effective_start_date = (select max(paf2.effective_start_date)
1556 from per_assignments_f paf2
1557 where paf2.assignment_id = paf.assignment_id
1558 and paf2.effective_start_date <= ''' || p_session_date || ''')
1559 AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
1560 AND paf.assignment_type = ''E''
1561 and paf.primary_flag = ''Y''
1562 AND paf.person_id between ' || stperson || ' and ' || endperson || '
1563 and paf.person_id = to_number(paa_arch.serial_number)
1564 AND exists ( select 1 from dual
1565 where 1 =
1566 (select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
1567 from ff_archive_items fai,
1568 ff_archive_item_contexts fic1,
1569 ff_archive_item_contexts fic2
1570 where fai.context1 = paa_arch.assignment_action_id
1571 and fai.user_entity_id in
1572 ( ' || l_sit_subj_whable || ',
1573 ' || l_sit_subj_nwhable || ',
1574 ' || l_sit_withheld || ') /* 6809739 */
1575 and fai.archive_item_id = fic1.archive_item_id
1576 and fic1.context_id = ' || l_tuid_context || '
1577 and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
1578 and fai.archive_item_id = fic2.archive_item_id
1579 and fic2.context_id = ' || l_juri_context || '
1580 and substr(ltrim(rtrim(fic2.context)),1,2) = ' || p_state_code || ' )) ';
1581 end if ;
1582 OPEN c_actions_no_selection for c_actions_no_selection_sql;
1583 num := 0;
1584
1585 loop
1586 hr_utility.set_location('procpyr',2);
1587 hr_utility.trace('after the loop in action cursor');
1588 fetch c_actions_no_selection into lockedactid,assignid,greid,l_effective_end_date;
1589
1590 if c_actions_no_selection%found then
1591 num := num + 1;
1592 hr_utility.trace('In the c_actions_no_selection%found in action cursor');
1593 else
1594 hr_utility.trace('In the c_actions_no_selection%notfound in action cursor');
1595 exit;
1596 end if;
1597 -- we need to insert one action for each of the
1598 -- rows that we return from the cursor (i.e. one
1599 -- for each assignment/pre-payment/reversal).
1600 hr_utility.trace('Before inserting the action record');
1601
1602 hr_utility.set_location('procpyr',3);
1603
1604 select pay_assignment_actions_s.nextval
1605 into lockingactid
1606 from dual;
1607
1608 -- insert the action record.
1609 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1610
1611 -- Update serial_numbrt of Pay_assignment_actions with the
1612 -- assignment_action_id .
1613 update pay_assignment_actions
1614 set serial_number = lockedactid
1615 where assignment_action_id = lockingactid;
1616 end loop;
1617 close c_actions_no_selection;
1618
1619 end if;
1620
1621 /* when location is entered */
1622
1623 if p_loc_id is not null then
1624
1625 if l_range_person_on = TRUE Then
1626 hr_utility.trace('Range Person ID Functionality is enabled') ;
1627 c_actions_with_location_sql :=
1628 'SELECT paa_arch.assignment_action_id,
1629 paa_arch.assignment_id,
1630 paa_arch.tax_unit_id,
1631 paf.effective_end_date
1632 FROM per_periods_of_service pps,
1633 per_assignments_f paf,
1634 pay_assignment_actions paa_arch,
1635 pay_population_ranges ppr
1636 /* disabling the index for performance reason */
1637 WHERE paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
1638 AND paa_arch.action_status = ''C''
1639 AND nvl(final_process_date,''' || p_session_date || ''')
1640 between paf.effective_start_date
1641 and paf.effective_end_date
1642 AND paf.location_id = ' || p_loc_id || '
1643 AND paa_arch.assignment_id = paf.assignment_id
1644 AND paf.effective_start_date = (select max(paf2.effective_start_date)
1645 from per_assignments_f paf2
1646 where paf2.assignment_id = paf.assignment_id
1647 and paf2.effective_start_date <= ''' || p_session_date || ''')
1648 AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
1649 AND paf.assignment_type = ''E''
1650 AND pps.period_of_service_id = paf.period_of_service_id
1651 --AND paf.person_id between stperson and endperson
1652 AND ppr.payroll_action_id = ' || pactid || '
1653 AND ppr.chunk_number = ' || chunk || '
1654 AND paf.person_id = ppr.person_id
1655 AND exists ( select 1 from dual
1656 where 1 =
1657 (select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
1658 from ff_archive_items fai,
1659 ff_archive_item_contexts fic1,
1660 ff_archive_item_contexts fic2
1661 where fai.context1 = paa_arch.assignment_action_id
1662 and fai.user_entity_id in
1663 ( ' || l_sit_subj_whable || ',
1664 ' || l_sit_subj_nwhable || ',
1665 ' || l_sit_withheld || ') /* 6809739 */
1666 and fai.archive_item_id = fic1.archive_item_id
1667 and fic1.context_id = ' || l_tuid_context || '
1668 and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
1669 and fai.archive_item_id = fic2.archive_item_id
1670 and fic2.context_id = ' || l_juri_context || '
1671 and substr(ltrim(rtrim(fic2.context)),1,2) = ' || p_state_code || ' ))';
1672 else
1673 c_actions_with_location_sql :=
1674 'SELECT paa_arch.assignment_action_id,
1675 paa_arch.assignment_id,
1676 paa_arch.tax_unit_id,
1677 paf.effective_end_date
1678 FROM per_periods_of_service pps,
1679 per_assignments_f paf,
1680 pay_assignment_actions paa_arch
1681 /* disabling the index for performance reason */
1682 WHERE paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
1683 AND paa_arch.action_status = ''C''
1684 AND nvl(final_process_date,''' || p_session_date || ''')
1685 between paf.effective_start_date
1686 and paf.effective_end_date
1687 AND paf.location_id = ' || p_loc_id || '
1688 AND paa_arch.assignment_id = paf.assignment_id
1689 AND paf.effective_start_date = (select max(paf2.effective_start_date)
1690 from per_assignments_f paf2
1691 where paf2.assignment_id = paf.assignment_id
1692 and paf2.effective_start_date <= ''' || p_session_date || ''')
1693 AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
1694 AND paf.assignment_type = ''E''
1695 AND pps.period_of_service_id = paf.period_of_service_id
1696 AND paf.person_id between ' || stperson || ' and ' || endperson || '
1697 AND exists ( select 1 from dual
1698 where 1 =
1699 (select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
1700 from ff_archive_items fai,
1701 ff_archive_item_contexts fic1,
1702 ff_archive_item_contexts fic2
1703 where fai.context1 = paa_arch.assignment_action_id
1704 and fai.user_entity_id in
1705 ( ' || l_sit_subj_whable || ',
1706 ' || l_sit_subj_nwhable || ',
1707 ' || l_sit_withheld || ') /* 6809739 */
1708 and fai.archive_item_id = fic1.archive_item_id
1709 and fic1.context_id = ' || l_tuid_context || '
1710 and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
1711 and fai.archive_item_id = fic2.archive_item_id
1712 and fic2.context_id = ' || l_juri_context || '
1713 and substr(ltrim(rtrim(fic2.context)),1,2) = ' || p_state_code || ' ))';
1714 end if ;
1715 OPEN c_actions_with_location for c_actions_with_location_sql;
1716 num := 0;
1717
1718 loop
1719 hr_utility.set_location('procpyr',2);
1720 hr_utility.trace('after the loop in action cursor');
1721 fetch c_actions_with_location into lockedactid,assignid,greid,l_effective_end_date;
1722
1723 if c_actions_with_location%found then
1724 num := num + 1;
1725 hr_utility.trace('In the c_actions_with_location%found in action cursor');
1726 else
1727 hr_utility.trace('In the c_actions_with_location%notfound in action cursor');
1728 exit;
1729 end if;
1730
1731
1732 -- we need to insert one action for each of the
1733 -- rows that we return from the cursor (i.e. one
1734 -- for each assignment/pre-payment/reversal).
1735 hr_utility.trace('Before inserting the action record');
1736
1737 hr_utility.set_location('procpyr',3);
1738
1739 select pay_assignment_actions_s.nextval
1740 into lockingactid
1741 from dual;
1742
1743 -- insert the action record.
1744 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1745
1746 -- Update serial_numbrt of Pay_assignment_actions with the
1747 -- assignment_action_id .
1748 update pay_assignment_actions
1749 set serial_number = lockedactid
1750 where assignment_action_id = lockingactid;
1751 end loop;
1752 close c_actions_with_location;
1753
1754 end if;
1755
1756
1757 /* when org is entered */
1758
1759 if p_org_id is not null then
1760
1761 if l_range_person_on = TRUE Then
1762 hr_utility.trace('Range Person ID Functionality is enabled') ;
1763 c_actions_with_org_sql :=
1764 'SELECT paa_arch.assignment_action_id,
1765 paa_arch.assignment_id,
1766 paa_arch.tax_unit_id,
1767 paf.effective_end_date
1768 FROM per_periods_of_service pps,
1769 per_assignments_f paf,
1770 pay_assignment_actions paa_arch,
1771 pay_population_ranges ppr
1772 /* disabling the index for performance reason */
1773 WHERE paa_arch.payroll_action_id +0 = ' || p_eoy_payroll_action_id || '
1774 AND paa_arch.action_status = ''C''
1775 AND nvl(final_process_date,''' || p_session_date || ''')
1776 between paf.effective_start_date
1777 and paf.effective_end_date
1778 AND paf.organization_id = ' || p_org_id || '
1779 AND paa_arch.assignment_id = paf.assignment_id
1780 AND paf.effective_start_date = (select max(paf2.effective_start_date)
1781 from per_assignments_f paf2
1782 where paf2.assignment_id = paf.assignment_id
1783 and paf2.effective_start_date <= ''' || p_session_date || ''')
1784 AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
1785 AND paf.assignment_type = ''E''
1786 AND pps.period_of_service_id = paf.period_of_service_id
1787 --AND paf.person_id between stperson and endperson
1788 AND ppr.payroll_action_id = ' || pactid || '
1789 AND ppr.chunk_number = ' || chunk || '
1790 AND paf.person_id = ppr.person_id
1791 AND exists ( select 1 from dual
1792 where 1 =
1793 (select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
1794 from ff_archive_items fai,
1795 ff_archive_item_contexts fic1,
1796 ff_archive_item_contexts fic2
1797 where fai.context1 = paa_arch.assignment_action_id
1798 and fai.user_entity_id in
1799 ( ' || l_sit_subj_whable || ',
1800 ' || l_sit_subj_nwhable || ',
1801 ' || l_sit_withheld || ') /* 6809739 */
1802 and fai.archive_item_id = fic1.archive_item_id
1803 and fic1.context_id = ' || l_tuid_context || '
1804 and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
1805 and fai.archive_item_id = fic2.archive_item_id
1806 and fic2.context_id = ' || l_juri_context || '
1807 and substr(ltrim(rtrim(fic2.context)),1,2) = ' || p_state_code || '))';
1808 else
1809 c_actions_with_org_sql :=
1810 'SELECT paa_arch.assignment_action_id,
1811 paa_arch.assignment_id,
1812 paa_arch.tax_unit_id,
1813 paf.effective_end_date
1814 FROM per_periods_of_service pps,
1815 per_assignments_f paf,
1816 pay_assignment_actions paa_arch
1817 /* disabling the index for performance reason */
1818 WHERE paa_arch.payroll_action_id +0 = ' || p_eoy_payroll_action_id || '
1819 AND paa_arch.action_status = ''C''
1820 AND nvl(final_process_date,''' || p_session_date || ''')
1821 between paf.effective_start_date
1822 and paf.effective_end_date
1823 AND paf.organization_id = ' || p_org_id || '
1824 AND paa_arch.assignment_id = paf.assignment_id
1825 AND paf.effective_start_date = (select max(paf2.effective_start_date)
1826 from per_assignments_f paf2
1827 where paf2.assignment_id = paf.assignment_id
1828 and paf2.effective_start_date <= ''' || p_session_date || ''')
1829 AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
1830 AND paf.assignment_type = ''E''
1831 AND pps.period_of_service_id = paf.period_of_service_id
1832 AND paf.person_id between ' || stperson || ' and ' || endperson || '
1833 AND exists ( select 1 from dual
1834 where 1 =
1835 (select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
1836 from ff_archive_items fai,
1837 ff_archive_item_contexts fic1,
1838 ff_archive_item_contexts fic2
1839 where fai.context1 = paa_arch.assignment_action_id
1840 and fai.user_entity_id in
1841 ( ' || l_sit_subj_whable || ',
1842 ' || l_sit_subj_nwhable || ',
1843 ' || l_sit_withheld || ') /* 6809739 */
1844 and fai.archive_item_id = fic1.archive_item_id
1845 and fic1.context_id = ' || l_tuid_context || '
1846 and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
1847 and fai.archive_item_id = fic2.archive_item_id
1848 and fic2.context_id = ' || l_juri_context || '
1849 and substr(ltrim(rtrim(fic2.context)),1,2) = ' || p_state_code || '))';
1850 end if ;
1851 OPEN c_actions_with_org for c_actions_with_org_sql;
1852 num := 0;
1853
1854 loop
1855 hr_utility.set_location('procpyr',2);
1856 hr_utility.trace('after the loop in c_actions_with_org');
1857 fetch c_actions_with_org into lockedactid,assignid,greid,l_effective_end_date;
1858
1859 if c_actions_with_org%found then
1860 num := num + 1;
1861 hr_utility.trace('In the c_actions_with_org%found in action cursor');
1862 else
1863 hr_utility.trace('In the c_actions_with_org%notfound in action cursor');
1864 exit;
1865 end if;
1866
1867
1868 -- we need to insert one action for each of the
1869 -- rows that we return from the cursor (i.e. one
1870 -- for each assignment/pre-payment/reversal).
1871 hr_utility.trace('Before inserting the action record');
1872
1873 hr_utility.set_location('procpyr',3);
1874
1875 select pay_assignment_actions_s.nextval
1876 into lockingactid
1877 from dual;
1878
1879 -- insert the action record.
1880 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1881
1882 -- Update serial_numbrt of Pay_assignment_actions with the
1883 -- assignment_action_id .
1884 update pay_assignment_actions
1885 set serial_number = lockedactid
1886 where assignment_action_id = lockingactid;
1887 end loop;
1888 close c_actions_with_org;
1889
1890 end if;
1891
1892 /* when person is entered */
1893
1894 if ( p_per_id is not null OR p_ssn is not null ) then
1895 open c_actions_with_person;
1896 num := 0;
1897
1898 loop
1899 hr_utility.set_location('procpyr',2);
1900 hr_utility.trace('after the loop in c_actions_with_person');
1901 fetch c_actions_with_person into lockedactid,assignid,greid,l_effective_end_date;
1902
1903 if c_actions_with_person%found then
1904 num := num + 1;
1905 hr_utility.trace('In the c_actions_with_person%found in action cursor');
1906 else
1907 hr_utility.trace('In the c_actions_with_person%notfound in action cursor');
1908 exit;
1909 end if;
1910
1911 -- we need to insert one action for each of the
1912 -- rows that we return from the cursor (i.e. one
1913 -- for each assignment/pre-payment/reversal).
1914 hr_utility.trace('Before inserting the action record');
1915
1916 hr_utility.set_location('procpyr',3);
1917
1918 select pay_assignment_actions_s.nextval
1919 into lockingactid
1920 from dual;
1921
1922 -- insert the action record.
1923 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
1924
1925 -- Update serial_numbrt of Pay_assignment_actions with the
1926 -- assignment_action_id .
1927 update pay_assignment_actions
1928 set serial_number = lockedactid
1929 where assignment_action_id = lockingactid;
1930 end loop;
1931 close c_actions_with_person;
1932
1933 end if;
1934
1935 /* when assignment set is entered */
1936
1937 if p_asg_set_id is not null then
1938
1939 if l_range_person_on = TRUE Then
1940 hr_utility.trace('Range Person ID Functionality is enabled') ;
1941 c_actions_with_assign_sql :=
1942 'SELECT paa_arch.assignment_action_id,
1943 paa_arch.assignment_id,
1944 paa_arch.tax_unit_id,
1945 paf.effective_end_date
1946 FROM per_assignments_f paf,
1947 pay_assignment_actions paa_arch,
1948 pay_population_ranges ppr
1949 WHERE paa_arch.action_status = ''C''
1950 AND paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
1951 AND paa_arch.assignment_id = paf.assignment_id
1952 AND paf.effective_start_date = (select max(paf2.effective_start_date)
1953 from per_assignments_f paf2
1954 where paf2.assignment_id = paf.assignment_id
1955 and paf2.effective_start_date <= ''' || p_session_date || ''')
1956 AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
1957 AND paf.assignment_type = ''E''
1958 --AND paf.person_id between stperson and endperson
1959 AND ppr.payroll_action_id = ' || pactid || '
1960 AND ppr.chunk_number = ' || chunk || '
1961 AND paf.person_id = ppr.person_id
1962 AND exists ( select 1 /* Selected Assignment Set */
1963 from hr_assignment_set_amendments hasa
1964 where hasa.assignment_set_id = ' || p_asg_set_id || '
1965 and hasa.assignment_id = paa_arch.assignment_id
1966 and upper(hasa.include_or_exclude) = ''I'')
1967 AND exists ( select 1 from dual
1968 where 1 =
1969 (select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
1970 from ff_archive_items fai,
1971 ff_archive_item_contexts fic1,
1972 ff_archive_item_contexts fic2
1973 where fai.context1 = paa_arch.assignment_action_id
1974 and fai.user_entity_id in
1975 ( ' || l_sit_subj_whable || ',
1976 ' || l_sit_subj_nwhable || ',
1977 ' || l_sit_withheld || ') /* 6809739 */
1978 and fai.archive_item_id = fic1.archive_item_id
1979 and fic1.context_id = ' || l_tuid_context || '
1980 and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
1981 and fai.archive_item_id = fic2.archive_item_id
1982 and fic2.context_id = ' || l_juri_context || '
1983 and substr(ltrim(rtrim(fic2.context)),1,2) = ' || p_state_code || ' ))';
1984 else
1985 c_actions_with_assign_sql :=
1986 'SELECT paa_arch.assignment_action_id,
1987 paa_arch.assignment_id,
1988 paa_arch.tax_unit_id,
1989 paf.effective_end_date
1990 FROM per_assignments_f paf,
1991 pay_assignment_actions paa_arch
1992 WHERE paa_arch.action_status = ''C''
1993 AND paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
1994 AND paa_arch.assignment_id = paf.assignment_id
1995 AND paf.effective_start_date = (select max(paf2.effective_start_date)
1996 from per_assignments_f paf2
1997 where paf2.assignment_id = paf.assignment_id
1998 and paf2.effective_start_date <= ''' || p_session_date || ''')
1999 AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
2000 AND paf.assignment_type = ''E''
2001 AND paf.person_id between ' || stperson || ' and ' || endperson || '
2002 AND exists ( select 1 /* Selected Assignment Set */
2003 from hr_assignment_set_amendments hasa
2004 where hasa.assignment_set_id = ' || p_asg_set_id || '
2005 and hasa.assignment_id = paa_arch.assignment_id
2006 and upper(hasa.include_or_exclude) = ''I'')
2007 AND exists ( select 1 from dual
2008 where 1 =
2009 (select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
2010 from ff_archive_items fai,
2011 ff_archive_item_contexts fic1,
2012 ff_archive_item_contexts fic2
2013 where fai.context1 = paa_arch.assignment_action_id
2014 and fai.user_entity_id in
2015 ( ' || l_sit_subj_whable || ',
2016 ' || l_sit_subj_nwhable || ',
2017 ' || l_sit_withheld || ') /* 6809739 */
2018 and fai.archive_item_id = fic1.archive_item_id
2019 and fic1.context_id = ' || l_tuid_context || '
2020 and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
2021 and fai.archive_item_id = fic2.archive_item_id
2022 and fic2.context_id = ' || l_juri_context || '
2023 and substr(ltrim(rtrim(fic2.context)),1,2) = ' || p_state_code || ' ))';
2024 end if ;
2025 OPEN c_actions_with_assign_set for c_actions_with_assign_sql;
2026 num := 0;
2027
2028 loop
2029 hr_utility.set_location('procpyr',2);
2030 hr_utility.trace('after the loop in c_actions_with_assign_set');
2031 fetch c_actions_with_assign_set into lockedactid,assignid,greid,l_effective_end_date;
2032
2033 if c_actions_with_assign_set%found then
2034 num := num + 1;
2035 hr_utility.trace('In the c_actions_with_assign_set%found in action cursor');
2036 else
2037 hr_utility.trace('In the c_actions_with_assign_set%notfound in action cursor');
2038 exit;
2039 end if;
2040
2041 -- we need to insert one action for each of the
2042 -- rows that we return from the cursor (i.e. one
2043 -- for each assignment/pre-payment/reversal).
2044 hr_utility.trace('Before inserting the action record');
2045
2046 hr_utility.set_location('procpyr',3);
2047
2048 select pay_assignment_actions_s.nextval
2049 into lockingactid
2050 from dual;
2051
2052 -- insert the action record.
2053 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
2054
2055 -- Update serial_numbrt of Pay_assignment_actions with the
2056 -- assignment_action_id .
2057 update pay_assignment_actions
2058 set serial_number = lockedactid
2059 where assignment_action_id = lockingactid;
2060 end loop;
2061 close c_actions_with_assign_set;
2062
2063 end if;
2064 hr_utility.trace('End of the action cursor');
2065
2066 end action_creation_for_er;
2067
2068 ---------------------------------- action_creation -----------------------------
2069 -----
2070 --
2071 /* CHANGED THE ACTION_CREATION CURSOR. NOW SEPERATE PROCEDURES ARE CALLED FOR
2072 EMPLOYEE AND EMPLOYER W2. THIS IS BECAUSE STATE PARAMTER IS REQUIRED FOR
2073 EMPLOYER W2 AND OPTIONAL FOR EMPLOYEE W2.
2074 MAKE SURE CHANGES ARE MADE IN BOTH THE PROCEDURES.
2075 */
2076
2077 procedure action_creation(pactid in number,
2078 stperson in number,
2079 endperson in number,
2080 chunk in number) is
2081
2082 l_session_date date;
2083 l_year number ;
2084 l_gre_id pay_assignment_actions.tax_unit_id%type;
2085 l_org_id per_assignments_f.organization_id%type;
2086 l_loc_id per_assignments_f.location_id%type;
2087 l_per_id per_assignments_f.person_id%type;
2088 l_ssn per_people_f.national_identifier%type;
2089 l_state_code pay_us_states.state_code%type;
2090 l_asg_set_id number;
2091 l_eoy_payroll_action_id pay_payroll_actions.payroll_action_id%type;
2092 l_eoy_start_date date;
2093 -- BUG2538173
2094 l_locality_code varchar2(20);
2095 l_print_term varchar2(2);
2096
2097
2098 l_report_type pay_payroll_actions.report_type%TYPE;
2099
2100 begin
2101 -- hr_utility.trace_on(null,'ORACLE');
2102 hr_utility.set_location('procpyr',1);
2103 hr_utility.trace('In the action cursor');
2104 Begin
2105 select to_number(hr_us_w2_mt.get_parameter('Year',ppa1.legislative_parameters)),
2106 to_number(hr_us_w2_mt.get_parameter('GRE_ID',ppa1.legislative_parameters)),
2107 to_number(hr_us_w2_mt.get_parameter('ORG_ID',ppa1.legislative_parameters)),
2108 to_number(hr_us_w2_mt.get_parameter('LOC_ID',ppa1.legislative_parameters)),
2109 to_number(hr_us_w2_mt.get_parameter('PER_ID',ppa1.legislative_parameters)),
2110 hr_us_w2_mt.get_parameter('SSN',ppa1.legislative_parameters),
2111 hr_us_w2_mt.get_parameter('STATE',ppa1.legislative_parameters),
2112 to_number(hr_us_w2_mt.get_parameter('ASG_SET',ppa1.legislative_parameters)),
2113 ppa.effective_date,
2114 ppa.payroll_action_id,
2115 ppa.start_date,
2116 ppa1.report_type
2117 --,ppa1.legislative_parameters
2118 ,hr_us_w2_mt.get_parameter('LOCALITY',ppa1.legislative_parameters)
2119 ,hr_us_w2_mt.get_parameter('PRINT_TERM',ppa1.legislative_parameters)
2120 into l_year,
2121 l_gre_id,
2122 l_org_id,
2123 l_loc_id,
2124 l_per_id,
2125 l_ssn,
2126 l_state_code,
2127 l_asg_set_id,
2128 l_session_date,
2129 l_eoy_payroll_action_id,
2130 l_eoy_start_date,
2131 l_report_type
2132 ,l_locality_code -- BUG2538173
2133 ,l_print_term
2134 from pay_payroll_actions ppa, /* EOY payroll action id */
2135 pay_payroll_actions ppa1 /* PYUGEN payroll action id */
2136 where ppa1.payroll_action_id = pactid
2137 and ppa.effective_date = ppa1.effective_date
2138 and ppa.report_type = 'YREND'
2139 and hr_us_w2_mt.get_parameter
2140 ('GRE_ID',ppa1.legislative_parameters) =
2141 hr_us_w2_mt.get_parameter
2142 ('TRANSFER_GRE',ppa.legislative_parameters);
2143 Exception
2144 when no_data_found then
2145 hr_utility.trace('Legislative parameters not found for pactid '||to_char(pactid));
2146 raise;
2147 End ;
2148 hr_utility.trace('report_type = '||l_report_type);
2149 hr_utility.trace('l_locality_code = '||l_locality_code);
2150
2151
2152 /* Now the SSN value set return person_id. Since the submittion is based on
2153 selection citeria only only value can be entered so in case l_ssn is not
2154 null then it is safe to assume l_per_id is null */
2155
2156 if l_ssn is not null then
2157 l_per_id := l_ssn;
2158 end if;
2159
2160
2161 if l_report_type = 'EMP_W2PAPER' then /* Employer W2 */
2162
2163 action_creation_for_er(pactid,stperson,endperson,chunk,l_year,
2164 l_gre_id,l_org_id,l_loc_id,l_per_id,
2165 l_ssn,l_state_code,l_asg_set_id,
2166 l_session_date,l_eoy_payroll_action_id,
2167 l_eoy_start_date );
2168
2169 elsif l_report_type in ('W2_XML', 'W2PAPER') then /*Employee W2 paper/XML */
2170
2171 action_creation_for_ee(pactid,stperson,endperson,chunk,l_year,
2172 l_gre_id,l_org_id,l_loc_id,l_per_id,
2173 l_ssn,l_state_code,l_asg_set_id,
2174 l_session_date,l_eoy_payroll_action_id,
2175 l_eoy_start_date,l_report_type
2176 ,l_locality_code,l_print_term);
2177
2178
2179 elsif l_report_type = 'PRW2PAPER' then /*Puerto Rico Employee W2*/
2180
2181 l_state_code :=null;
2182
2183 hr_utility.trace('Action creation for Puerto Rico Employee W2');
2184 hr_utility.trace('stperson ' ||to_char(stperson));
2185 hr_utility.trace('endperson ' ||to_char(endperson));
2186 hr_utility.trace('l_eoy_payroll_action_id = '||to_char(l_eoy_payroll_action_id));
2187
2188 hr_utility.trace('pactid = '||to_char(pactid));
2189 action_creation_for_ee(pactid,stperson,endperson,chunk,l_year,
2190 l_gre_id,l_org_id,l_loc_id,l_per_id,
2191 l_ssn,l_state_code,l_asg_set_id,
2192 l_session_date,l_eoy_payroll_action_id,
2193 l_eoy_start_date,l_report_type ,l_locality_code
2194 ,l_print_term);
2195
2196 end if;
2197
2198 end action_creation;
2199
2200 ---------------------------------- sort_action ------------------------------
2201
2202 procedure sort_action
2203 (
2204 payactid in varchar2,
2205 sqlstr in out nocopy varchar2,
2206 len out nocopy number
2207 ) is
2208
2209 l_dt date;
2210 l_year number ;
2211 l_gre_id pay_assignment_actions.tax_unit_id%type;
2212 l_org_id per_assignments_f.organization_id%type;
2213 l_loc_id per_assignments_f.location_id%type;
2214 l_per_id per_assignments_f.person_id%type;
2215 l_ssn per_people_f.national_identifier%type;
2216 l_state_code pay_us_states.state_code%type;
2217 l_sort1 varchar2(60);
2218 l_sort2 varchar2(60);
2219 l_sort3 varchar2(60);
2220 l_year_start date;
2221 l_year_end date;
2222 l_bg_id pay_payroll_actions.business_group_id%type ;
2223
2224 begin
2225
2226 begin
2227 select hr_us_w2_mt.get_parameter('Year',ppa.legislative_parameters),
2228 hr_us_w2_mt.get_parameter('GRE_ID',ppa.legislative_parameters),
2229 hr_us_w2_mt.get_parameter('ORG_ID',ppa.legislative_parameters),
2230 hr_us_w2_mt.get_parameter('LOC_ID',ppa.legislative_parameters),
2231 hr_us_w2_mt.get_parameter('PER_ID',ppa.legislative_parameters),
2232 hr_us_w2_mt.get_parameter('SSN',ppa.legislative_parameters),
2233 hr_us_w2_mt.get_parameter('STATE',ppa.legislative_parameters),
2234 hr_us_w2_mt.get_parameter('S1',ppa.legislative_parameters),
2235 hr_us_w2_mt.get_parameter('S2',ppa.legislative_parameters),
2236 hr_us_w2_mt.get_parameter('S3',ppa.legislative_parameters),
2237 to_date(hr_us_w2_mt.get_parameter('EFFECTIVE_DATE',ppa.legislative_parameters),'YYYY/MM/DD'),
2238 ppa.effective_date,
2239 ppa.business_group_id
2240 into l_year,
2241 l_gre_id,
2242 l_org_id,
2243 l_loc_id,
2244 l_per_id,
2245 l_ssn,
2246 l_state_code,
2247 l_sort1 ,
2248 l_sort2,
2249 l_sort3,
2250 l_dt, --session_date
2251 l_year_end,
2252 l_bg_id
2253 from pay_payroll_actions ppa
2254 where ppa.payroll_action_id = payactid;
2255
2256 exception when no_data_found then
2257 hr_utility.trace('Error in Sort Procedure - getting legislative param');
2258 raise;
2259 end;
2260 /* changed this with the if statement below
2261 begin
2262 select to_date('31-DEC-'||to_char(l_year),'DD/MM/YYYY')
2263 into l_dt
2264 from dual
2265 where to_date('31-DEC-'||to_char(l_year),'DD/MM/YYYY') > l_dt;
2266 exception
2267 when others then null;
2268 end;
2269 */
2270 /*
2271 if to_date('31-DEC-'||to_char(l_year),'DD/MM/YYYY') > l_dt
2272 then
2273 l_dt := to_date('31-DEC-'||to_char(l_year),'DD/MM/YYYY') ;
2274 end if;
2275 */
2276
2277
2278 if l_year_end > l_dt then
2279 l_dt := l_year_end;
2280 end if;
2281
2282 hr_utility.trace('Beginning of the sort_action cursor');
2283
2284 sqlstr :=
2285 'select mt.rowid
2286 from hr_organization_units hou, hr_locations_all hl, per_periods_of_service pps, per_assignments_f paf,
2287 pay_assignment_actions mt where mt.payroll_action_id = :pactid and
2288 paf.assignment_id = mt.assignment_id and paf.effective_start_date = (select max(paf2.effective_start_date)
2289 from per_assignments_f paf2 where paf2.assignment_id = paf.assignment_id
2290 and paf2.effective_start_date <= to_date(''31-DEC-''||'''||l_year||''',''DD/MM/YYYY''))
2291 and paf.effective_end_date >= to_date(''01-JAN-''||'''||l_year||''',''DD/MM/YYYY'')
2292 and paf.assignment_type = ''E'' and pps.period_of_service_id = paf.period_of_service_id
2293 and pps.person_id = paf.person_id and hl.location_id = paf.location_id
2294 and hou.organization_id = paf.organization_id and hou.business_group_id + 0 = '''||l_bg_id||'''
2295 order by decode('''||l_sort1||''', ''Employee_Name'',
2296 hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_LAST_NAME'')||'' ''
2297 ||hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_FIRST_NAME'')||'' ''
2298 ||substr(hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_MIDDLE_NAMES''),1,1),
2299 ''SSN'',nvl(hr_us_w2_rep.get_per_item( to_number(mt.serial_number), ''A_PER_NATIONAL_IDENTIFIER''), ''Applied For''),
2300 ''Zip_Code'',hr_us_w2_rep.get_w2_postal_code( paf.person_id,'''||l_dt||'''),
2301 ''Organization'',hou.name, ''Location'',hl.location_code,
2302 ''Termination_Reason'',decode(leaving_reason,null,''ZZ'',hr_us_w2_rep.get_leav_reason(leaving_reason)),
2303 hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_LAST_NAME'')||'' ''
2304 ||hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_FIRST_NAME'')||'' ''
2305 ||substr(hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_MIDDLE_NAMES''),1,1)),
2306 decode('''||l_sort2||''', ''Employee_Name'',
2307 hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_LAST_NAME'')||'' ''
2308 ||hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_FIRST_NAME'')||'' ''
2309 ||substr(hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_MIDDLE_NAMES''),1,1),
2310 ''SSN'',nvl(hr_us_w2_rep.get_per_item(
2311 to_number(mt.serial_number), ''A_PER_NATIONAL_IDENTIFIER''), ''Applied For''),
2312 ''Zip_Code'',hr_us_w2_rep.get_w2_postal_code( paf.person_id,'''||l_dt||'''),
2313 ''Organization'',hou.name, ''Location'',hl.location_code,
2314 ''Termination_Reason'',decode(leaving_reason,null,''ZZ'',hr_us_w2_rep.get_leav_reason(leaving_reason)),
2315 hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_LAST_NAME'')||'' ''
2316 ||hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_FIRST_NAME'')||'' ''
2317 ||substr(hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_MIDDLE_NAMES''),1,1)),
2318 decode('''||l_sort3||''', ''Employee_Name'', hr_us_w2_rep.get_per_item(to_number(mt.serial_number),
2319 ''A_PER_LAST_NAME'')||'' ''||hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_FIRST_NAME'')||'' ''
2320 ||substr(hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_MIDDLE_NAMES''),1,1),
2321 ''SSN'',nvl(hr_us_w2_rep.get_per_item( to_number(mt.serial_number), ''A_PER_NATIONAL_IDENTIFIER''),
2322 ''Applied For''), ''Zip_Code'',hr_us_w2_rep.get_w2_postal_code( paf.person_id,'''||l_dt||'''),
2323 ''Organization'',hou.name, ''Location'',hl.location_code, ''Termination_Reason'',decode(leaving_reason,null,''ZZ'',hr_us_w2_rep.get_leav_reason(leaving_reason)),
2324 hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_LAST_NAME'')||'' ''
2325 ||hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_FIRST_NAME'')||'' ''
2326 ||substr(hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_MIDDLE_NAMES''),1,1))
2327 for update of mt.assignment_action_id' ;
2328
2329 -- changed on 11sep02
2330 -- for update of paf.assignment_id';
2331
2332 len := length(sqlstr); -- return the length of the string.
2333 hr_utility.trace('End of the sort_Action cursor');
2334 end sort_action;
2335 --
2336 ------------------------------ get_parameter -------------------------------
2337 function get_parameter(name in varchar2,
2338 parameter_list varchar2) return varchar2
2339 is
2340 start_ptr number;
2341 end_ptr number;
2342 token_val pay_payroll_actions.legislative_parameters%type;
2343 par_value pay_payroll_actions.legislative_parameters%type;
2344 begin
2345 --
2346 token_val := name||'=';
2347 --
2348 start_ptr := instr(parameter_list, token_val) + length(token_val);
2349 end_ptr := instr(parameter_list, ' ',start_ptr);
2350
2351 --
2352 /* if there is no spaces use then length of the string */
2353 if end_ptr = 0 then
2354 end_ptr := length(parameter_list)+1;
2355 end if;
2356 --
2357 /* Did we find the token */
2358 if instr(parameter_list, token_val) = 0 then
2359 par_value := NULL;
2360 else
2361 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
2362 end if;
2363 --
2364 return par_value;
2365 --
2366 end get_parameter;
2367
2368 end hr_us_w2_mt;