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