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