[Home] [Help]
PACKAGE BODY: APPS.PAY_YREND_REPORTS_PKG
Source
1 package body pay_yrend_reports_pkg as
2 /* $Header: pyusw2cu.pkb 120.3.12010000.3 2008/09/16 11:34:15 asgugupt ship $ */
3 /*
4 Copyright (c) Oracle Corporation 1991,1992,1993. All rights reserved
5
6 Name :This package defines the cursors needed to run Year End Reports Multi-Threaded
7
8 Change List
9 -----------
10 Date Name Vers Description
11 ----------- ---------- ----- -----------------------------------
12 09-MAR-1999 meshah 40.0 created
13 20-JUL-2001 irgonzal 115.1 Modified action_creation and sort_
14 action procedures. Bug fixes:
15 1850043, 1488083, 1894165.
16 22-JUL-2001 ndorai 115.2 Commented the join clause and the
17 call to API hr_us_w2_rep.person_in_set
18 in range_cursor to improve performance and
19 modified the query of action_creation.
20 26-JUL-2001 ndorai 115.3 Modified the action_creation cursor and
21 range_cursor.
22 30-JUL-2001 ndorai 115.4 Replaced per_assignments_f with
23 per_all_assignments_f in sort_action_cursor.
24 26-SEP-2001 ndorai 115.5 Fixed the sort_action string so that the
25 variable value will be substituted properly.
26 24-DEC-2001 meshah 115.8 Changed hr_locations to hr_locations_all.
27 06-JAN-2002 meshah 115.9 inlcuded a space in range cursor while
28 constructing the sqlstr.
29 11-SEP-2002 ppanda 115.10 Sort cursor sql string changed to use
30 pay_assignment_actions instead of per_all_assignments_f
31 in for update clause
32
33 09-JAN-2003 ahanda 115.13 Sort cursor changed ti Fix Bug 2743186
34 09-JAN-2003 asasthan 115.14 nocopy changes
35 07-AUG-2003 jgoswami 115.15 Action cursor changed to Fix Bug 2573628
36 split into two cursors as c_actions_with_asg_set
37 and c_actions_without_asg_set
38 11-AUG-2003 jgoswami 115.16 Commented the to_char(USERENV('SESSIONID'))
39 05-SEP-2003 ahanda 115.17 Changed sort_action to not go to secure view.
40 As the action is already created the sort_cursor
41 should go to the base table(Bug 3131302).
42 09-SEP-2004 rsethupa 115.18 Modified cursors in the action_creation
43 procedure to fetch only from
44 secure view per_assignments_f.
45 14-MAR-2005 sackumar 115.19 Bug 4222032
46 Change in the Range Cursor removing redundant
47 use of bind Variable (:pactid)
48 25-MAY-2005 ahanda 115.20 Bug 4378773
49 Changed function get_parameter to check for
50 exact name i.e. ' ' || name || '='
51 12-SEP-2005 ynegoro 115.21 Bug 2538173, added locality parameter
52 21-SEP-2005 ynegoro 115.22 Bug 2538173, Modifed for locality parameter
53 22-SEP-2005 ahanda 115.23 Changed action creation for locality param.
54 31-AUG-2006 saurgupt 115.24 Bug 3913757 : Made change to sort_action. Added the employee
55 name in the sort2 and sort3 if no sort option is provided in
56 sort2 and sort3.
57 12-MAY-2008 keyazawa 115.24 bug 5896290 added deinitialize_code
58 16-SEP-2008 asgugupt 115.25 Changed where Clause in action_creation procedure
59 when state_code is passed
60 */
61 --
62 c_package constant varchar2(31) := 'pay_yrend_reports_pkg.';
63 c_commit_num constant number := 1000;
64 --
65 g_debug boolean := hr_utility.debug_enabled;
66 --
67 ----------------------------------- range_cursor ----------------------------------
68 procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
69
70
71 l_assign_year number;
72 l_tax_unit_id pay_assignment_actions.tax_unit_id%type;
73 l_eoy_payroll_action_id pay_payroll_actions.payroll_action_id%type;
74 begin
75
76 /* EOY reg code */
77 /* year end pre-processor stores person id in serial number */
78 -- Query is added to fetch the tax_unit_id, year, EOY payroll_action_id to be
79 -- passed to the sqlstring to improve the performance.
80 --
81 select pay_yrend_reports_pkg.get_parameter('T_U_ID',ppa1.legislative_parameters),
82 pay_yrend_reports_pkg.get_parameter('P_YEAR',ppa1.legislative_parameters),
83 ppa.payroll_action_id
84 into l_tax_unit_id,
85 l_assign_year,
86 l_eoy_payroll_action_id
87 from pay_payroll_actions ppa, /* EOY payroll action id */
88 pay_payroll_actions ppa1 /* PYUGEN payroll action id */
89 where ppa1.payroll_action_id = pactid
90 and ppa.effective_date = to_date('31-DEC-'||
91 pay_yrend_reports_pkg.get_parameter
92 ('P_YEAR',ppa1.legislative_parameters), 'DD-MON-YYYY')
93 and ppa.report_type = 'YREND'
94 and pay_yrend_reports_pkg.get_parameter
95 ('T_U_ID',ppa1.legislative_parameters) =
96 pay_yrend_reports_pkg.get_parameter
97 ('TRANSFER_GRE',ppa.legislative_parameters);
98
99 sqlstr := 'select distinct to_number(act.serial_number)
100 from pay_assignment_actions act /* W2 Register Information */
101 where :pactid is not null
102 and act.payroll_action_id = ' || l_eoy_payroll_action_id ||
103 ' and act.tax_unit_id = ' || l_tax_unit_id ||
104 ' order by to_number(act.serial_number)';
105
106 --hr_utility.trace('Session ID = '||to_char(USERENV('SESSIONID')));
107 end range_cursor;
108
109 ---------------------------------- action_creation ----------------------------------
110 procedure action_creation(pactid in number,
111 stperson in number,
112 endperson in number,
113 chunk in number) is
114 -- cursor has been modified by adding new parameter c_pay_action_id and removed
115 -- the reference to pay_payroll_actions table by fetching values in a separate query.
116 --
117 -- cursor will be used when asignment_set is selected.
118
119 CURSOR c_actions_with_asg_set
120 (
121 pactid number,
122 stperson number,
123 endperson number,
124 c_assign_year number,
125 c_tax_unit_id pay_assignment_actions.tax_unit_id%type,
126 c_assign_set hr_assignment_set_amendments.assignment_set_id%type,
127 c_eoy_payroll_action_id pay_payroll_actions.payroll_action_id%type
128 ) is
129 select act.assignment_action_id,
130 act.assignment_id,
131 act.tax_unit_id,
132 to_number(act.serial_number)
133 -- need to select person id to check for assignment set
134 from pay_assignment_actions act
135 where act.payroll_action_id = c_eoy_payroll_action_id
136 and act.tax_unit_id = c_tax_unit_id
137 and to_number(act.serial_number) between stperson and endperson
138 and exists ( select 1
139 from per_assignments_f paf,
140 hr_assignment_set_amendments hasa
141 where hasa.assignment_set_id = c_assign_set
142 and hasa.assignment_id = paf.assignment_id
143 and upper(hasa.include_or_exclude) = 'I'
144 and c_assign_set is not null
145 and paf.person_id = to_number(act.serial_number)
146 );
147
148 /* when assignment_set is not selected */
149 -- #3871087 Included join with per_assignments_f
150 CURSOR c_actions_without_asg_set
151 (
152 pactid number,
153 stperson number,
154 endperson number,
155 c_assign_year number,
156 c_tax_unit_id pay_assignment_actions.tax_unit_id%type,
157 c_eoy_payroll_action_id pay_payroll_actions.payroll_action_id%type,
158 c_effective_date pay_payroll_actions.effective_date%TYPE,
159 c_start_date pay_payroll_actions.start_date%TYPE
160 ) is
161 select act.assignment_action_id,
162 act.assignment_id,
163 act.tax_unit_id,
164 to_number(act.serial_number)
165 -- need to select person id to check for assignment set
166 from pay_assignment_actions act,
167 per_assignments_f paf
168 where act.payroll_action_id = c_eoy_payroll_action_id
169 and act.tax_unit_id = c_tax_unit_id
170 and paf.assignment_id = act.assignment_id
171 and paf.effective_start_date =
172 (select max(paf2.effective_start_date)
173 from per_assignments_f paf2
174 where paf2.assignment_id = paf.assignment_id
175 and paf2.effective_start_date <=
176 c_effective_date )
177 AND paf.effective_end_date >= c_start_date
178 AND paf.assignment_type = 'E'
179 and to_number(act.serial_number) between stperson and endperson;
180
181 lockingactid number;
182 lockedactid number;
183 assignid number;
184 greid number;
185 num number;
186 p_person_id number;
187 l_assign_set number;
188 l_assign_year number;
189 l_tax_unit_id pay_assignment_actions.tax_unit_id%type;
190 l_eoy_payroll_action_id pay_payroll_actions.payroll_action_id%type;
191
192 l_effective_date pay_payroll_actions.effective_date%TYPE;
193 l_start_date pay_payroll_actions.start_date%TYPE;
194
195 -- BUG2538173
196 CURSOR c_state_context (p_context_name varchar2) is
197 select context_id from ff_contexts
198 where context_name = p_context_name;
199
200 l_state_code pay_us_states.state_code%type;
201 l_locality_code varchar2(20);
202
203 TYPE RefCurType is REF CURSOR;
204 c_actions_with_state RefCurType;
205 c_actions_with_state_sql varchar2(10000);
206 l_tuid_context ff_contexts.context_id%TYPE;
207 l_juri_context ff_contexts.context_id%TYPE;
208
209 CURSOR c_state_ueid (p_user_entity_name varchar2) is
210 select user_entity_id
211 from ff_user_entities
212 where user_entity_name = p_user_entity_name
213 and legislation_code = 'US';
214
215 l_subj_whable ff_user_entities.user_entity_id%TYPE;
216 l_subj_nwhable ff_user_entities.user_entity_id%TYPE;
217
218 l_procedure_name VARCHAR2(100);
219
220 begin
221 -- hr_utility.trace_on(null, 'W2REG');
222 l_procedure_name := 'action_creation';
223 hr_utility.set_location(l_procedure_name, 1);
224 --
225 -- Query has been added to fetch tax_unit_id, year to be passed to the cursor
226 select pay_yrend_reports_pkg.get_parameter('ASSIGN_SET',ppa1.legislative_parameters),
227 pay_yrend_reports_pkg.get_parameter('T_U_ID',ppa1.legislative_parameters),
228 pay_yrend_reports_pkg.get_parameter('P_YEAR',ppa1.legislative_parameters),
229 ppa.payroll_action_id,
230 ppa.effective_date,
231 ppa.start_date
232 ,hr_us_w2_mt.get_parameter('STATE',ppa1.legislative_parameters)
233 ,hr_us_w2_mt.get_parameter('LOCALITY',ppa1.legislative_parameters)
234 into l_assign_set,
235 l_tax_unit_id,
236 l_assign_year,
237 l_eoy_payroll_action_id,
238 l_effective_date,
239 l_start_date
240 ,l_state_code -- BUG2538173
241 ,l_locality_code
242 from pay_payroll_actions ppa, /* W2 payroll action id */
243 pay_payroll_actions ppa1 /* PYUGEN payroll action id */
244 where ppa1.payroll_action_id = pactid
245 and ppa.effective_date = to_date('31-DEC-'|| pay_yrend_reports_pkg.get_parameter
246 ('P_YEAR',ppa1.legislative_parameters), 'DD-MON-YYYY')
247 and ppa.report_type = 'YREND'
248 and pay_yrend_reports_pkg.get_parameter
249 ('T_U_ID',ppa1.legislative_parameters) =
250 pay_yrend_reports_pkg.get_parameter
251 ('TRANSFER_GRE',ppa.legislative_parameters);
252
253 hr_utility.trace('l_assign_set= ' || l_assign_set);
254 hr_utility.trace('l_tax_unit_id=' || l_tax_unit_id);
255 hr_utility.trace('l_eoy_payroll_action_id=' || l_eoy_payroll_action_id);
256 hr_utility.trace('l_assign_year=' || l_assign_year);
257 hr_utility.trace('pactid=' || pactid);
258 hr_utility.trace('stperson=' || stperson);
259 hr_utility.trace('endperson=' || endperson);
260 hr_utility.set_location(l_procedure_name, 10);
261
262 if l_assign_set is not null then
263 hr_utility.set_location(l_procedure_name, 20);
264 open c_actions_with_asg_set(pactid, stperson, endperson,
265 l_assign_year, l_tax_unit_id,
266 l_assign_set, l_eoy_payroll_action_id);
267 num := 0;
268 loop
269 fetch c_actions_with_asg_set into lockedactid,assignid,greid,p_person_id;
270 if c_actions_with_asg_set%found then num := num + 1; end if;
271 exit when c_actions_with_asg_set%notfound;
272
273 -- Commenting the IF clause as this condition is already taken care
274 -- in the action_creation cursor.
275 -- if (hr_assignment_set.person_in_set(l_assign_set,p_person_id)='Y') then
276
277 -- we need to insert one action for each of the
278 -- rows that we return from the cursor (i.e. one
279 -- for each assignment/pre-payment/reversal).
280
281 select pay_assignment_actions_s.nextval
282 into lockingactid
283 from dual;
284
285 hr_utility.set_location(l_procedure_name, 30);
286 -- insert the action record.
287 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
288
289 /* update pay_assignment_actions with the year end assignment_actions into serial number
290 this might help in faster processing at report level and avoid some joins */
291
292 update pay_assignment_actions
293 set serial_number = lockedactid
294 where assignment_action_id = lockingactid;
295
296
297 -- insert an interlock to this action.
298 -- Bug fix 1850043
299 -- hr_nonrun_asact.insint(lockingactid,lockedactid);
300
301 -- end if;
302
303 end loop;
304 close c_actions_with_asg_set;
305 --
306 -- BUG2538173
307 --
308 elsif l_state_code is not null then
309 hr_utility.set_location(l_procedure_name, 40);
310 open c_state_context('TAX_UNIT_ID');
311 fetch c_state_context into l_tuid_context;
312 close c_state_context;
313
314 open c_state_context('JURISDICTION_CODE');
315 fetch c_state_context into l_juri_context;
316 close c_state_context;
317
318 hr_utility.set_location(l_procedure_name, 50);
319 c_actions_with_state_sql :=
320 'select act.assignment_action_id,
321 act.assignment_id,
322 act.tax_unit_id,
323 to_number(act.serial_number)
324 -- need to select person id to check for assignment set
325 from pay_assignment_actions act,
326 per_assignments_f paf
330 and paf.effective_start_date =
327 where act.payroll_action_id = ' || l_eoy_payroll_action_id || '
328 and act.tax_unit_id = ' || l_tax_unit_id || '
329 and paf.assignment_id = act.assignment_id
331 (select max(paf2.effective_start_date)
332 from per_assignments_f paf2
333 where paf2.assignment_id = paf.assignment_id
334 and paf2.effective_start_date <= ''' ||
335 l_effective_date || ''' )
336 AND paf.effective_end_date >= ''' || l_start_date || '''
337 AND paf.assignment_type = ''E''
338 and to_number(act.serial_number) between ' || stperson || ' and ' ||endperson;
339
340 hr_utility.set_location(l_procedure_name, 60);
341 if l_locality_code is null then
342 open c_state_ueid('A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD');
343 fetch c_state_ueid into l_subj_whable;
344 close c_state_ueid;
345
346 open c_state_ueid('A_SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD');
347 fetch c_state_ueid into l_subj_nwhable;
348 close c_state_ueid;
349
350 hr_utility.set_location(l_procedure_name, 70);
351 c_actions_with_state_sql := c_actions_with_state_sql ||
352 ' AND exists ( select 1 from dual
353 where 1 =
354 --bug 7392315
355 -- (select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
356 (select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,1,1)
357 --bug 7392315
358 from ff_archive_items fai,
359 ff_archive_item_contexts fic1,
360 ff_archive_item_contexts fic2
361 where fai.context1 = act.assignment_action_id
362 and fai.user_entity_id in (' || l_subj_whable || ',
363 ' || l_subj_nwhable || ')
364 and fai.archive_item_id = fic1.archive_item_id
365 and fic1.context_id = ' || l_tuid_context || '
366 and ltrim(rtrim(fic1.context)) = to_char(act.tax_unit_id)
367 and fai.archive_item_id = fic2.archive_item_id
368 and fic2.context_id = ' || l_juri_context || '
369 and substr(ltrim(rtrim(fic2.context)),1,2) = ' || l_state_code || ' ))';
370
371
372 --
373 -- County
374 --
375 elsif length(l_locality_code) = 11 and
376 substr(l_locality_code, 8,4) = '0000' then
377 hr_utility.set_location(l_procedure_name, 80);
378 open c_state_ueid('A_COUNTY_SUBJ_WHABLE_PER_JD_GRE_YTD');
379 fetch c_state_ueid into l_subj_whable;
380 close c_state_ueid;
381
382 open c_state_ueid('A_COUNTY_SUBJ_NWHABLE_PER_JD_GRE_YTD');
383 fetch c_state_ueid into l_subj_nwhable;
384 close c_state_ueid;
385
386 hr_utility.set_location(l_procedure_name, 90);
387 c_actions_with_state_sql := c_actions_with_state_sql ||
388 ' AND exists ( select 1 from dual
389 where 1 =
390 (select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
391 from ff_archive_items fai,
392 ff_archive_item_contexts fic1,
393 ff_archive_item_contexts fic2
394 where fai.context1 = act.assignment_action_id
395 and fai.user_entity_id in (' || l_subj_whable || ',
396 ' || l_subj_nwhable || ')
397 and fai.archive_item_id = fic1.archive_item_id
398 and fic1.context_id = ' || l_tuid_context || '
399 and ltrim(rtrim(fic1.context)) = to_char(act.tax_unit_id)
400 and fai.archive_item_id = fic2.archive_item_id
401 and fic2.context_id = ' || l_juri_context || '
402 and substr(ltrim(rtrim(fic2.context)),1,6) = substr(''' || l_locality_code || ''',1,6) ))';
403
404 --
405 -- City
406 --
407 elsif length(l_locality_code) = 11 and
408 substr(l_locality_code, 8,4) <> '0000' then
409 hr_utility.set_location(l_procedure_name, 100);
410
411 open c_state_ueid('A_CITY_SUBJ_WHABLE_PER_JD_GRE_YTD');
412 fetch c_state_ueid into l_subj_whable;
413 close c_state_ueid;
414
415 open c_state_ueid('A_CITY_SUBJ_NWHABLE_PER_JD_GRE_YTD');
416 fetch c_state_ueid into l_subj_nwhable;
417 close c_state_ueid;
418 hr_utility.trace('l_subj_whable='||l_subj_whable);
419 hr_utility.trace('l_subj_nwhable='||l_subj_nwhable);
420
421 c_actions_with_state_sql := c_actions_with_state_sql ||
422 ' AND exists ( select 1 from dual
423 where 1 =
424 (select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
425 from ff_archive_items fai,
426 ff_archive_item_contexts fic1,
427 ff_archive_item_contexts fic2
428 where fai.context1 = act.assignment_action_id
429 and fai.user_entity_id in (' || l_subj_whable || ',
433 and ltrim(rtrim(fic1.context)) = to_char(act.tax_unit_id)
430 ' || l_subj_nwhable || ')
431 and fai.archive_item_id = fic1.archive_item_id
432 and fic1.context_id = ' || l_tuid_context || '
434 and fai.archive_item_id = fic2.archive_item_id
435 and fic2.context_id = ' || l_juri_context || '
436 and substr(ltrim(rtrim(fic2.context)),1,11) = ''' || l_locality_code || ''' ))';
437
438 --
439 -- School District
440 --
441 elsif length(l_locality_code) = 8 then
442
443 hr_utility.set_location(l_procedure_name, 120);
444 open c_state_ueid('A_SCHOOL_SUBJ_WHABLE_PER_JD_GRE_YTD');
445 fetch c_state_ueid into l_subj_whable;
446 close c_state_ueid;
447
448 open c_state_ueid('A_SCHOOL_SUBJ_NWHABLE_PER_JD_GRE_YTD');
449 fetch c_state_ueid into l_subj_nwhable;
450 close c_state_ueid;
451
452 c_actions_with_state_sql := c_actions_with_state_sql ||
453 ' AND exists ( select 1 from dual
454 where 1 =
455 (select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
456 from ff_archive_items fai,
457 ff_archive_item_contexts fic1,
458 ff_archive_item_contexts fic2
459 where fai.context1 = act.assignment_action_id
460 and fai.user_entity_id in (' || l_subj_whable || ',
461 ' || l_subj_nwhable || ')
462 and fai.archive_item_id = fic1.archive_item_id
463 and fic1.context_id = ' || l_tuid_context || '
464 and ltrim(rtrim(fic1.context)) = to_char(act.tax_unit_id)
465 and fai.archive_item_id = fic2.archive_item_id
466 and fic2.context_id = ' || l_juri_context || '
467 and substr(ltrim(rtrim(fic2.context)),1,8) = ''' || l_locality_code || '''))';
468
469 end if;
470 hr_utility.set_location(l_procedure_name, 150);
471
472 num := 0;
473 OPEN c_actions_with_state FOR c_actions_with_state_sql;
474 loop
475 fetch c_actions_with_state into lockedactid,assignid,greid,p_person_id;
476 if c_actions_with_state%found then
477 num := num + 1;
478 hr_utility.trace('In the c_actions_with_state%found in action cursor');
479 else
480 hr_utility.trace('In the c_actions_with_state%notfound in action cursor');
481 exit;
482 end if;
483
484 hr_utility.set_location(l_procedure_name, 160);
485 select pay_assignment_actions_s.nextval
486 into lockingactid
487 from dual;
488
489 -- insert the action record.
490 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
491
492 update pay_assignment_actions
493 set serial_number = lockedactid
494 where assignment_action_id = lockingactid;
495
496 end loop;
497 close c_actions_with_state;
498 -- end of l_state_code
499 else
500 hr_utility.set_location(l_procedure_name, 170);
501 open c_actions_without_asg_set(pactid, stperson, endperson,
502 l_assign_year, l_tax_unit_id,
503 l_eoy_payroll_action_id,l_effective_date,l_start_date);
504 num := 0;
505 loop
506 fetch c_actions_without_asg_set into lockedactid,assignid,greid,p_person_id;
507 if c_actions_without_asg_set%found then num := num + 1; end if;
508 exit when c_actions_without_asg_set%notfound;
509
510 -- Commenting the IF clause as this condition is already taken care
511 -- in the action_creation cursor.
512 -- if (hr_assignment_set.person_in_set(l_assign_set,p_person_id)='Y') then
513
514 -- we need to insert one action for each of the
515 -- rows that we return from the cursor (i.e. one
516 -- for each assignment/pre-payment/reversal).
517
518 hr_utility.set_location(l_procedure_name, 180);
519 select pay_assignment_actions_s.nextval
520 into lockingactid
521 from dual;
522
523 -- insert the action record.
524 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
525
526 /* update pay_assignment_actions with the year end assignment_actions into serial number
527 this might help in faster processing at report level and avoid some joins */
528
529 update pay_assignment_actions
530 set serial_number = lockedactid
531 where assignment_action_id = lockingactid;
532
533
534 -- insert an interlock to this action.
535 -- Bug fix 1850043
536 -- hr_nonrun_asact.insint(lockingactid,lockedactid);
537
538 -- end if;
539
540 end loop;
541 close c_actions_without_asg_set;
542 end if;
543 hr_utility.set_location(l_procedure_name, 250);
544 end action_creation;
545
546 ---------------------------------- sort_action ----------------------------------
547 procedure sort_action
548 (
549 payactid in varchar2, /* payroll action id */
553
550 sqlstr in out nocopy varchar2, /* string holding the sql statement */
551 len out nocopy number /* length of the sql string */
552 ) is
554 l_dt date;
555 l_year number ;
556 l_gre_id pay_assignment_actions.tax_unit_id%type;
557 l_org_id per_assignments_f.organization_id%type;
558 l_loc_id per_assignments_f.location_id%type;
559 l_per_id per_assignments_f.person_id%type;
560 l_ssn per_people_f.national_identifier%type;
561 l_state_code pay_us_states.state_code%type;
562 l_sort1 varchar2(60);
563 l_sort2 varchar2(60);
564 l_sort3 varchar2(60);
565 l_year_start date;
566 l_year_end date;
567 l_eoy_payroll_action_id pay_payroll_actions.payroll_action_id%type;
568 l_bg_id pay_payroll_actions.business_group_id%type ;
569
570 begin
571 begin
572 select hr_us_w2_mt.get_parameter('YEAR',ppa1.legislative_parameters),
573 hr_us_w2_mt.get_parameter('GRE_ID',ppa1.legislative_parameters),
574 hr_us_w2_mt.get_parameter('ORG_ID',ppa1.legislative_parameters),
575 hr_us_w2_mt.get_parameter('LOC_ID',ppa1.legislative_parameters),
576 hr_us_w2_mt.get_parameter('PER_ID',ppa1.legislative_parameters),
577 hr_us_w2_mt.get_parameter('SSN',ppa1.legislative_parameters),
578 hr_us_w2_mt.get_parameter('STATE',ppa1.legislative_parameters),
579 hr_us_w2_mt.get_parameter('P_S1',ppa1.legislative_parameters),
580 hr_us_w2_mt.get_parameter('P_S2',ppa1.legislative_parameters),
581 hr_us_w2_mt.get_parameter('P_S3',ppa1.legislative_parameters),
582 ppa1.effective_date,
583 ppa1.business_group_id
584 into l_year,
585 l_gre_id,
586 l_org_id,
587 l_loc_id,
588 l_per_id,
589 l_ssn,
590 l_state_code,
591 l_sort1 ,
592 l_sort2,
593 l_sort3,
594 l_dt, --session_date
595 l_bg_id
596 from pay_payroll_actions ppa1 /* PYUGEN payroll action id */
597 where ppa1.payroll_action_id = payactid;
598 exception
599 when no_data_found then
600 hr_utility.trace('Error in Sort Procedure - getting legislative param');
601 raise;
602 end;
603 sqlstr := 'select paa1.rowid
604 /* we need the row id of the assignment actions that are
605 created by PYUGEN */
606 from hr_all_organization_units hou,
607 hr_locations_all loc,
608 per_all_people_f ppf, -- #1894165
609 per_all_assignments_f paf,
610 /*pay_assignment_actions paa,*/
611 pay_payroll_actions ppa1,
612 pay_assignment_actions paa1 /* PYUGEN assignment action */
613 where ppa1.payroll_action_id = :pactid
614 and paa1.payroll_action_id = ppa1.payroll_action_id
615 and paf.assignment_id = paa1.assignment_id
616 and paf.effective_start_date =
617 (select max(paf2.effective_start_date)
618 from per_all_assignments_f paf2 -- #3871087
619 where paf2.assignment_id = paf.assignment_id
620 and paf2.effective_start_date <= ppa1.effective_date)
621 and paf.effective_end_date >= ppa1.start_date
622 and paf.assignment_type = ''E''
623 /* if assignments organization_id is null pick assignment
624 business_group_id to avoid assertion error. Bug No: 1894165 */
625 and hou.organization_id =
626 nvl(paf.organization_id,paf.business_group_id) -- #1894165
627 /* if assignments location_id is null pick assignments
628 organization/business groups location_id to avoid assertion
629 error. Bug No: 1894165 */
630 and loc.location_id = nvl(paf.location_id,hou.location_id)
631 and ppf.person_id = paf.person_id
632 and ppa1.effective_date between
633 ppf.effective_start_date and ppf.effective_end_date
634 order by
635 decode(' || '''' || l_sort1 || '''' ||
636 ',''Employee_Name'', ppf.last_name||'' ''||ppf.first_name ||'' ''||ppf.middle_names,
637 ''Social_Security_Number'',ppf.national_identifier,
638 ''Organization'',hou.name,
639 ''Location'',loc.location_code,
640 ppf.last_name||'' ''||ppf.first_name ||'' ''||ppf.middle_names),
641 decode(' || '''' || l_sort2 || '''' ||
642 ',''Employee_Name'',ppf.last_name||'' ''||ppf.first_name ||'' ''||ppf.middle_names,
643 ''Social_Security_Number'',ppf.national_identifier,
644 ''Organization'',hou.name,
645 ''Location'',loc.location_code,
646 ppf.last_name||'' ''||ppf.first_name ||'' ''||ppf.middle_names),
647 decode(' || '''' || l_sort3 || '''' ||
648 ',''Employee_Name'',ppf.last_name||'' ''||ppf.first_name ||'' ''||ppf.middle_names,
649 ''Social_Security_Number'',ppf.national_identifier,
650 ''Organization'',hou.name,
651 ''Location'',loc.location_code,
652 ppf.last_name||'' ''||ppf.first_name ||'' ''||ppf.middle_names)
653 ';
654 -- Bug 3913757
655 -- Assignment_id is taken from pay_assignment_action istead of per_all_assignments_f
656 -- for update of paf.assignment_id
657 --
658 --
659 len := length(sqlstr); -- return the length of the string.
660 end sort_action;
661
662 ------------------------------ get_parameter -------------------------------
663 function get_parameter(name in varchar2,
664 parameter_list varchar2) return varchar2
665 is
666 start_ptr number;
667 end_ptr number;
668 token_val pay_payroll_actions.legislative_parameters%type;
669 par_value pay_payroll_actions.legislative_parameters%type;
670 begin
671
672 token_val := ' ' || name||'=';
673
674 start_ptr := instr(parameter_list, token_val) + length(token_val);
675 end_ptr := instr(parameter_list, ' ',start_ptr);
676
677 /* if there is no spaces use then length of the string */
678 if end_ptr = 0 then
679 end_ptr := length(parameter_list)+1;
680 end if;
681
682 /* Did we find the token */
683 if instr(parameter_list, token_val) = 0 then
684 par_value := NULL;
685 else
686 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
687 end if;
688
689 return par_value;
690
691 end get_parameter;
692 --
693 procedure deinitialize_code(
694 p_payroll_action_id in number)
695 is
696 --
697 l_proc varchar2(80) := c_package||'deinitialize_code';
698 --
699 l_inv_act varchar2(1);
700 l_commit_cnt number := 0;
701 l_del_cnt number := 0;
702 --
703 cursor csr_inv_act
704 is
705 select 'Y'
706 from dual
707 where exists(
708 select /*+ ORDERED */
709 null
710 from pay_payroll_actions ppa,
711 pay_us_rpt_totals purt
712 where ppa.payroll_action_id = p_payroll_action_id
713 and ppa.action_status = 'E'
714 and purt.session_id = p_payroll_action_id);
715 --
716 -- cannot use for update (record lock) because commit is needed in middle loop of cursor.
717 -- this will make plsql error.
718 cursor csr_del
719 is
720 select rowid
721 from pay_us_rpt_totals
722 where session_id = p_payroll_action_id;
723 --
724 l_csr_del csr_del%rowtype;
725 --
726 begin
727 --
728 if g_debug then
729 hr_utility.set_location(l_proc,0);
730 hr_utility.trace('pay_yrend_report_pkg start deinitialize code');
731 end if;
732 --
733 -- following procedure will be called ordinarily
734 -- without calling directly.
735 --pay_archive.standard_deinit(p_payroll_action_id);
736 --
737 open csr_inv_act;
738 fetch csr_inv_act into l_inv_act;
739 close csr_inv_act;
740 --
741 if l_inv_act = 'Y' then
742 --
743 open csr_del;
744 loop
745 --
746 fetch csr_del into l_csr_del;
747 exit when csr_del%notfound;
748 --
749 delete from pay_us_rpt_totals
750 where rowid = l_csr_del.rowid;
751 --
752 l_commit_cnt := l_commit_cnt + 1;
753 l_del_cnt := l_del_cnt + 1;
754 --
755 if l_commit_cnt > c_commit_num then
756 --
757 commit;
758 l_commit_cnt := 0;
759 --
760 end if;
761 --
762 end loop;
763 close csr_del;
764 --
765 if g_debug then
766 hr_utility.trace('pay_yrend_report_pkg delete '||to_char(l_del_cnt)||' records');
767 end if;
768 --
769 if l_del_cnt > 0 then
770 --
771 commit;
772 --
773 end if;
774 --
775 end if;
776 --
777 if g_debug then
778 hr_utility.set_location(l_proc,1000);
779 hr_utility.trace('pay_yrend_report_pkg end deinitialize code');
780 end if;
781 --
782 end deinitialize_code;
783 --
784 end pay_yrend_reports_pkg;