[Home] [Help]
PACKAGE BODY: APPS.BEN_BENBATCH_PERSONS
Source
1 PACKAGE BODY ben_benbatch_persons AS
2 /* $Header: benbatpe.pkb 120.8.12010000.5 2009/02/24 10:58:03 krupani ship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10
11 Name
12 Benefit Batch Persons
13 Purpose
14 This package is used to create person actions for batch related
15 tasks.
16 History
17 Date Who Version What?
18 ---- --- ------- -----
19 11-AUG-98 GPERRY 110.0 Created, moved code from benmngle.pkb.
20 26-AUG-98 GPERRY 115.1 Added p_person_selection_rule_id
21 parameter and added function
22 check_sleection_rule.
23 16-SEP-98 GPERRY 115.2 Fixed bug in restart process.
24 23-SEP-98 GPERRY 115.3 Added parameter p_commit_data
25 for use in prasads stuff.
26 29-SEP-98 GPERRY 115.4 p_commit_data = 'Y' to commit
27 not 'N'. Added num_persons parameter.
28 27-OCT-98 MHOYES 115.5 Replaced business_group_id joins
29 with p_business_group_id in
30 cursor c_person_life.
31 31-OCT-98 MHOYES 115.6 Backed out nocopy previous change.
32 03-DEC-98 MHOYES 115.7 Tuned cursor c_person_life.
33 Removed + 0 from the business
34 group condition. Improved
35 cursor gets from 2591 down to 236.
36 25-JAN-99 GPERRY 115.8 Changed ler_id so it creates a
37 null instead of a 0.
38 11-MAR-99 GPERRY 115.9 != to <>.
39 05-APR-99 mhoyes 115.10 - Un-datetrack of per_in_ler_f changes.
40 - Removed DT restriction from
41 - create_life_person_actions/c_person_life
42 09-APR-99 GPERRY 115.11 Change per Denise. We no
43 longer pick up persons with
44 potentials that are manual.
45 Also rewrote c_person_life
46 cursor to make it more efficient.
47 21-APR-99 GPERRY 115.12 Changes for temporal mode.
48 11-JUN-99 bbulusu 115.13 Made cursors dynamic. Tuned cursors.
49 Modified check_sel_rule to accept the
50 assignment_id as a parameter.
51 16-JUN-99 GPERRY 115.14 Fixed outer join to per_assignments_f
52 errors.
53 11-JUL-99 mhoyes 115.15 - Added new trace messages.
54 - Removed + 0s from all cursors.
55 15-JUL-99 mhoyes 115.16 - Added new trace messages.
56 15-JUL-99 pbodla 115.18 - This is a leap frog : as there a alias error
57 (ler.ler_id instead of ptn.ler_id)
58 in dynamic SQL at open c_person_life
59 20-JUL-99 Gperry 115.19 genutils -> benutils package rename.
60 03-AUG-99 Gperry 115.20 performance enhancements.
61 31-AUG-99 Gperry 115.21 Changed ben_ptnl_ler_for_per cursor
62 to look for all statuses apart from
63 PROCD and VOIDD.
64 22-DEC-99 Gperry 115.22 Fixed bug 2752.
65 WWBUG 1096742.
66 Added all criteria to make life event work.
67 07-JAN-00 Gperry 115.23 Fixed bug 3503.
68 WWBUG 1096828.
69 ler_id bind was not working for life
70 event mode, was defaulting to 0.
71 18-JAN-00 pbodla 115.24 Fixed bug 4146(WWBUG 1120687)
72 p_business_group_id added to benutils.formula
73 call.
74 03-Apr-00 mmogel 115.25 Added tokens to BEN_91329_FORMULA_
75 RETURN message
76 21-Jun-00 mhoyes 115.26 - Modified create_life_person_actions
77 and create_normal_person_actions to
78 restrict by benefit group id on
79 per_all_people_f to avoid the full table
80 scan of per_all_people_f
81 30-Jun-00 dharris 115.27 - Re-wrote major sections of the code to
82 use bulk fetching and inserting.
83 - Modified the dynamically created SQL
84 statement to not to used TO_DATE end of
85 time check for bind variables. Now the SQL
86 statement will use IS NOT NULL.
87 03-Aug-00 mhoyes 115.28 - Added restriction to
88 create_normal_person_actions to eliminate
89 person types of 'DPNT' and 'BNF' in temporal
90 mode.
91 28-Aug-00 rchase 115.29 - bug 1386636. Modified restart to recycle
92 old control tables since reports need to have
93 the info. To do this needed to delete errored
94 person_actions.
95 29-Aug-00 jcarpent 115.30 - same bug. Must delete old error when restart.
96 06-Sep-00 cdaniels 115.31 - OraBug # 6606. Added logic to update the
97 request_id in ben_benefit_actions to the
98 new concurrent request id generated for the
99 restart. wwbug 1386632.
100 18-Sep-2000 pbodla 115.32 - Healthnet changes : PB : Added parameter
101 p_lmt_prpnip_by_org_typ_id
102 30-nov-01 tjesumic 115.33 - joint between payroll and assignment table fixed
103 01-dec-01 tjesumic 115.34 - 2119804
104 01-dec-01 tjesumic 115.35 - set verify added
105 18-Feb-02 rpillay 115.36 - Bug 2224299. Made changes to select persons
106 correctly when User defined Person Type is
107 passed in as a parameter
108 19-Feb-02 rpillay 115.37 - Added checkfile line
109 06-Mar-02 ikasire 115.38 Bug 2248822 to process only the system_person_type
110 of EMP and EX_EMP for CWB .
111 25-Mar-02 pbodla 115.39 - Bug 2279394 : where clause is not formed
112 correctly, as and is missing in where clause.
113 08-Jun-02 pabodla 115.40 Do not select the contingent worker
114 assignment when assignment data is
115 fetched.
116 26-jun-02 nhunur 115.42 added exception handling code in check_selection_rule.
117 26-jun-02 pbodla 115.43 ABSNCES - in case of absence mode
118 consider persons with absence
119 potential life events only.
120 31-Jan-03 pbodla 115.44 GRADE/STEP - modified create_life_person_actions
121 to support extra parameters.
122 06-Feb-02 tjesumic 115.45 l_typ_cd varaible has value 'no in ' whne the value assind
123 to varaible '=' added with that, it create synex error
124 10-Feb-03 pbodla 115.46 GRADE/STEP - Restrict to only Employees.
125 01-Aug-03 rpgupta 115.47 2940151
126 GRADE/STEP - Added new parameters and person
127 selection logic
128 01-Aug-03 rpgupta 115.47 Changed query for org hierarchy and grade ladder
129 25-Aug-03 pbodla 115.48 GSP New : When p_asg_events_to_all_sel_dt is set
130 no need to check for existence of potentials.
131 06-Nov-03 rpgupta 115.49 Iniaitlised bind variable l_grade_ladder_date4_bind
132 and changed the sql accordingly
133 29-Dec-03 mmudigon 115.50 Bug 3232194. Removed +0 from c_batch_ranges
134 28-jan-04 nhunur 115.52 Bug 3394157. conditionally append where caluse
135 if p_ler_id is not null.
136 11-Mar-04 rpgupta 115.53 cwbglobal - Added query to restrict ptnl life events
137 with status VOIDD and PROCD to procedure
138 create_normal_life_events .
139 19-Apr-2004 nhunur 115.54 bug 3537113 - for cwb person should have a valid assignment
140 16-Aug-2004 nhunur 115.55 bug 3537113 - Added effective_date bind while opening cursor.
141 28-Sep-2004 hmani 115.56 IREC - Main Line Front port 115.52.15102.2
142 05-Oct-2004 kmahendr 115.57 bug 3537113 - added another effective date condition if the
143 mode is not W
144 14-Oct-2004 abparekh 115.58 GSP Rate Sync changes : Added p_lf_evt_oper_cd to procedure
145 create_life_person_actions
146 08-Nov-2004 abparekh 115.59 CWB : Bug 3981941 - Modified create_normal_person_actions to rectify the check
147 not to pick up persons with CWB Started person life event.
148 09-Nov-04 nhunur 115.60 Commented above check. The check needs to be relooked at to prevent issues
149 like bug : 4001483
150 27-Dec-04 abparekh 115.62 Bug 4030438 : For GSP if person has not grade ladder assigned,
151 process the person if business group has DEFAULT grade ladder.
152 17-nov-05 nhunur 115.63 bug - 4743143 - GSP query needed a parenthesis.
153 03-Jan-06 nhunur 115.64 cwb - changes for person type param.
154 03-feb-06 nhunur 115.65 cwb - changes for picking active assignment.
155 03-Nov-06 swjain 115.67 Bug 5331889 - passed person_id as input param in check_selection_rule
156 and added input1 as additional param for future use
157 31-jul-06 nhunur 115.68 cagr - changes for picking people with coll. agreement.
158 09-Aug-07 vvprabhu 115.69 Bug 5857493 - added g_audit_flag to
159 control person selection rule error logging
160 28-May-08 krupani 115.70 Bug 6718304 - Relevant changes done in CWB where clause
161 21-Jan-08 krupani 115.71 Bug 7307975 - Changes to improve performance in GSP
162 24-Feb-08 krupani 115.72 Bug 7307975 - Further changes
163 25-Feb-08 krupani 115.73 Bug 7307975 - Corrected the fix in version 115.72
164 */
165 --------------------------------------------------------------------------------
166 g_package VARCHAR2(80) := 'ben_benbatch_persons';
167 TYPE g_number_table_type IS TABLE OF NUMBER
168 INDEX BY BINARY_INTEGER;
169 --
170 FUNCTION check_selection_rule(
171 p_person_selection_rule_id IN NUMBER
172 ,p_person_id IN NUMBER
173 ,p_business_group_id IN NUMBER
174 ,p_effective_date IN DATE
175 ,p_input1 in varchar2 default null -- Bug 5331889
176 ,p_input1_value in varchar2 default null)
177 RETURN BOOLEAN IS
178 --
179 l_outputs ff_exec.outputs_t;
180 l_assignment_id NUMBER;
181 l_package VARCHAR2(80) := g_package || '.check_selection_rule';
182 value_exception exception ;
183 --
184 BEGIN
185 --
186 IF p_person_selection_rule_id IS NULL THEN
187 --
188 RETURN TRUE;
189 --
190 ELSE
191 --
192 l_assignment_id :=
193 benutils.get_assignment_id(p_person_id=> p_person_id
194 ,p_business_group_id => p_business_group_id
195 ,p_effective_date => p_effective_date);
196 --
197 if l_assignment_id is null
198 then
199 raise ben_batch_utils.g_record_error;
200 end if ;
201 --
202
203 l_outputs :=
204 benutils.formula(p_formula_id=> p_person_selection_rule_id
205 ,p_effective_date => p_effective_date
206 ,p_business_group_id => p_business_group_id
207 ,p_assignment_id => l_assignment_id
208 ,p_param1 => 'BEN_IV_PERSON_ID' -- Bug 5331889
209 ,p_param1_value => to_char(p_person_id)
210 ,p_param2 => p_input1
211 ,p_param2_value => p_input1_value);
212 --
213 IF l_outputs(l_outputs.FIRST).VALUE = 'Y' THEN
214 --
215 RETURN TRUE;
216 --
217 ELSIF l_outputs(l_outputs.FIRST).VALUE = 'N' THEN
218 --
219 RETURN FALSE;
220 --
221 ELSIF upper(l_outputs(l_outputs.FIRST).VALUE) not in ('Y', 'N') THEN
222 --
223 RAISE value_exception;
224 --
225 END IF;
226 --
227 END IF;
228 --
229 EXCEPTION
230 --
231 When ben_batch_utils.g_record_error then
232 hr_utility.set_location(l_package ,10);
233 if g_audit_flag = true then
234 fnd_message.set_name('BEN','BEN_91698_NO_ASSIGNMENT_FND');
235 fnd_message.set_token('ID' ,to_char(p_person_id) );
236 fnd_message.set_token('PROC',l_package ) ;
237 Ben_batch_utils.write(p_text => '<< Person id : '||to_char(p_person_id)||' failed.'||
238 ' Reason : '|| fnd_message.get ||' >>' );
239 end if;
240 RETURN FALSE;
241 When value_exception then
242 hr_utility.set_location(l_package ,20);
243 fnd_message.set_name('BEN','BEN_91329_FORMULA_RETURN');
244 fnd_message.set_token('RL','person_selection_rule_id :'||p_person_selection_rule_id);
245 fnd_message.set_token('PROC',l_package ) ;
246 Ben_batch_utils.write(p_text => '<< Person id : '||to_char(p_person_id)||' failed.'||
247 ' Reason : '|| fnd_message.get ||' >>' );
248 RETURN FALSE;
249 WHEN OTHERS THEN
250 hr_utility.set_location(l_package ,30);
251 Ben_batch_utils.write(p_text => '<< Person id : '||to_char(p_person_id)||' failed.'||
252 ' Reason : '|| SQLERRM ||' >>' );
253 RETURN FALSE;
254 --
255 END check_selection_rule;
256 --
257 PROCEDURE create_normal_person_actions(
258 p_benefit_action_id IN NUMBER
259 ,p_mode_cd IN VARCHAR2
260 ,p_business_group_id IN NUMBER
261 ,p_person_id IN NUMBER
262 ,p_ler_id IN NUMBER
263 ,p_person_type_id IN NUMBER
264 ,p_benfts_grp_id IN NUMBER
265 ,p_location_id IN NUMBER
266 ,p_legal_entity_id IN NUMBER
267 ,p_payroll_id IN NUMBER
268 ,p_pstl_zip_rng_id IN NUMBER
269 ,p_organization_id IN NUMBER
270 ,p_ler_override_id IN NUMBER
271 ,p_person_selection_rule_id IN NUMBER
272 ,p_effective_date IN DATE
273 ,p_mode IN VARCHAR2
274 ,p_chunk_size IN NUMBER
275 ,p_threads IN NUMBER
276 ,p_num_ranges OUT NOCOPY NUMBER
277 ,p_num_persons OUT NOCOPY NUMBER
278 ,p_commit_data IN VARCHAR2
279 ,p_lmt_prpnip_by_org_flag IN VARCHAR2
280 ,p_popl_enrt_typ_cycl_id in number default NULL
281 ,p_cwb_person_type IN VARCHAR2 default NULL
282 ,p_lf_evt_ocrd_dt in date) IS
283 --
284 cursor c_cwb_asg_date_chk is
285 select greatest (b.start_date
286 ,to_date(nvl(c.strt_mo,1) ||'/'||nvl(c.strt_day,1)||'/'||to_char(b.start_date,'YYYY'),'MM/DD/YYYY')) wth_start_date
287 ,greatest(b.end_date
288 ,to_date(nvl(c.end_mo,1)||'/'||nvl(c.end_day,1)||'/'||to_char(b.end_date,'YYYY'),'MM/DD/YYYY')) wth_end_date
289 from BEN_ENRT_PERD A,
290 ben_yr_perd b,
291 ben_wthn_yr_perd c,
292 ben_popl_enrt_typ_cycl_f pet,
293 ben_ler_f ler
294 WHERE a.popl_enrt_typ_cycl_id = p_popl_enrt_typ_cycl_id
295 and a.yr_perd_id = b.yr_perd_id
296 and a.wthn_yr_perd_id = c.wthn_yr_perd_id (+)
297 and a.popl_enrt_typ_cycl_id = pet.popl_enrt_typ_cycl_id
298 and pet.business_group_id = a.business_group_id
299 and p_effective_date between pet.effective_start_date and pet.effective_end_date
300 -- and p_effective_date between b.start_date and b.end_date --Bug 6718304
301 and a.ler_id = ler.ler_id
302 and ler.typ_cd = 'COMP'
303 and p_effective_date between ler.effective_start_date and ler.effective_end_date
304 and a.asnd_lf_evt_dt = p_lf_evt_ocrd_dt ;
305 --
306 l_wth_start_date date;
307 l_wth_end_date date;
308 --
309 -- Native dynamic PLSQL cursor
310 --
311 TYPE cur_type IS REF CURSOR;
312 c_person cur_type;
313 l_start_person_action_id NUMBER;
314 l_end_person_action_id NUMBER;
315 --
316 l_person_id_fetch NUMBER;
317 l_person_id_process ben_benbatch_persons.g_number_table_type;
318 l_person_action_id_table ben_benbatch_persons.g_number_table_type;
319 l_to_chunk_loop NUMBER := 0;
320 --
321 -- Local variables
322 --
323 l_query_str VARCHAR2(5000);
324 l_person_id_bind NUMBER;
325 l_person_type_id_bind NUMBER;
326 l_benfts_grp_id_bind NUMBER;
327 l_location_id_date_bind DATE;
328 l_location_id_bind NUMBER;
329 l_legal_entity_id_date_bind DATE;
330 l_legal_entity_id_bind NUMBER;
331 l_payroll_id_date_bind DATE;
332 l_payroll_id_bind NUMBER;
333 l_payroll_id_date2_bind DATE;
334 l_pstl_zip_rng_id_date_bind DATE;
335 l_pstl_zip_rng_id_date2_bind DATE;
336 l_pstl_zip_rng_id_date3_bind DATE;
337 l_pstl_zip_rng_id_bind NUMBER;
338 l_pstl_zip_rng_id_date4_bind DATE;
339 l_organization_id_date_bind DATE;
340 l_organization_id_bind NUMBER;
341 l_organization_id_date2_bind DATE;
342 l_organization_id_date3_bind DATE;
343 --
344 l_temp_whclause LONG;
345 --
346 l_person_type_id_date_bind DATE; -- Bug 2224299
347 l_cwb_whclause LONG; -- Bug 2248822
348 --
349 BEGIN
350
351
352 -- Check for temporal mode
353 -- When running in temporal mode we can exclude contacts without PTUs
354 -- The assumption is that only contacts exist without PTUs
355 hr_utility.set_location('Entering create_normal_person_actions ',19);
356 hr_utility.set_location(' p_effective_date '||p_effective_date, 19);
357 --
358 IF p_mode_cd = 'T'
359 and p_person_selection_rule_id is null
360 THEN
361 --
362 l_temp_whclause :=
363 ' and exists ' ||
364 ' (select null ' ||
365 ' from per_person_type_usages_f ptu, ' ||
366 ' per_person_types pet ' ||
367 ' where ptu.person_id = ppf.person_id ' ||
368 ' and ptu.person_type_id = pet.person_type_id ' ||
369 ' and pet.system_person_type not in( ' ||
370 ''''||'DPNT'||''''||', '||''''||'BNF'||''''||') ' ||
371 ' )';
372 --
373 ELSE
374 --
375 l_temp_whclause := ' ';
376 --
377 END IF;
378 --
379 -- Create the main query
380 --
381 -- Bind in business group id
382 --
383 -- Modified by SMONDAL
384
385 IF p_lmt_prpnip_by_org_flag = 'N' then
386 --
387 l_query_str :=
388 ' select ppf.person_id from per_all_people_f ppf' ||
389 ' where ppf.business_group_id = :business_group_id ' ||
390 ' and :effective_date is not null ' ||
391 l_temp_whclause;
392 --
393 ELSE
394 --
395 l_query_str :=
396 ' select ppf.person_id from per_all_people_f ppf' ||
397 ', per_all_assignments_f paf1' ||
398 ' where ppf.business_group_id = :business_group_id ' ||
399 ' and ppf.person_id = paf1.person_id(+) ' ||
400 ' and ppf.business_group_id = paf1.business_group_id(+) ' ||
401 ' and paf1.primary_flag(+) = ''Y'' ' ||
402 ' and (paf1.assignment_id is null ' ||
403 ' or paf1.assignment_id = ' ||
404 ' ( select min(paf2.assignment_id) ' ||
405 ' from per_all_assignments_f paf2 ' ||
406 ' where paf2.person_id = paf1.person_id ' ||
407 ' and paf2.assignment_type <> ''C'''||
408 ' and paf1.business_group_id = paf2.business_group_id ' ||
409 ' and paf2.primary_flag = ''Y'' ' ||
410 ' and :effective_date between paf2.effective_start_date' ||
411 ' and paf2.effective_end_date' ||
412 ' ) ' ||
413 ' )' ||
414 l_temp_whclause;
415 --
416 END IF;
417 -- Bug 2248822 Restrict the persons to only Employees and Ex-employees
418 -- for the CWB Process.
419 IF p_mode_cd = 'W' THEN
420 --
421 hr_utility.set_location(' p_cwb_person_type '||p_cwb_person_type, 19);
422 fnd_file.put_line(fnd_file.log, p_cwb_person_type );
423 if nvl(p_cwb_person_type,'AEXE') = 'AEXE'
424 then
425 l_cwb_whclause := ' and exists ( select ''x'' from per_person_type_usages_f ptu, '||
426 ' per_person_types ppt '||
427 ' where ppt.person_type_id = ptu.person_type_id '||
428 ' and ppt.system_person_type in ( ''EMP'', ''EX_EMP'' ) '||
429 ' and ppt.business_group_id = ppf.business_group_id '||
430 ' and ptu.person_id = ppf.person_id '||
431 ' and ppt.active_flag = ''Y'' ' ||
432 ' and :effective_date between ptu.effective_start_date '||
433 ' and ptu.effective_end_date ) ' ;
434 elsif p_cwb_person_type = 'AE'
435 then
436 l_cwb_whclause := ' and exists ( select ''x'' from per_person_type_usages_f ptu, '||
437 ' per_person_types ppt '||
438 ' where ppt.person_type_id = ptu.person_type_id '||
439 ' and ppt.system_person_type = ''EMP'' '||
440 ' and ppt.business_group_id = ppf.business_group_id '||
441 ' and ptu.person_id = ppf.person_id '||
442 ' and ppt.active_flag = ''Y'' ' ||
443 ' and :effective_date between ptu.effective_start_date '||
444 ' and ptu.effective_end_date ) ' ;
445 else
446 l_cwb_whclause := ' and exists ( select ''x'' from per_person_type_usages_f ptu, '||
447 ' per_person_types ppt '||
448 ' where ppt.person_type_id = ptu.person_type_id '||
449 ' and ppt.system_person_type in ( ''EMP'', ''EX_EMP'' ) '||
450 ' and ppt.business_group_id = ppf.business_group_id '||
451 ' and ptu.person_id = ppf.person_id '||
452 ' and ppt.active_flag = ''Y'' ' ||
453 ' and :effective_date between ptu.effective_start_date '||
454 ' and ptu.effective_end_date ) ' ;
455 end if;
456 -- bug 3537113
457 -- for cwb, person should have a valid assignment
458 --
459 if nvl(p_cwb_person_type,'AEXE') in ( 'AE' , 'AEXE' )
460 then
461 l_cwb_whclause := l_cwb_whclause || ' and exists ( select 1 from per_all_assignments_f asgn'
462 || ' where asgn.person_id = ppf.person_id '
463 || ' and asgn.PRIMARY_FLAG = ''Y'' '
464 || ' and :effective_date between asgn.effective_start_date '
465 || ' and asgn.effective_end_date '
466 || ' and :l_wth_start_date is null )' ;
467 else
468 hr_utility.set_location(' p_popl_enrt_typ_cycl_id '|| p_popl_enrt_typ_cycl_id, 19);
469 hr_utility.set_location(' p_lf_evt_ocrd_dt '|| p_lf_evt_ocrd_dt, 19);
470 --
471 fnd_file.put_line(fnd_file.log, p_popl_enrt_typ_cycl_id );
472 fnd_file.put_line(fnd_file.log, p_lf_evt_ocrd_dt );
473
474 if p_popl_enrt_typ_cycl_id is not null
475 and p_lf_evt_ocrd_dt is not null
476 then
477 open c_cwb_asg_date_chk;
478 fetch c_cwb_asg_date_chk into l_wth_start_date, l_wth_end_date ;
479 close c_cwb_asg_date_chk;
480 --
481 hr_utility.set_location(' l_wth_start_date '|| l_wth_start_date, 19);
482 fnd_file.put_line(fnd_file.log, l_wth_start_date );
483 --
484 if l_wth_start_date is not null
485 then
486 l_cwb_whclause := l_cwb_whclause
487 || ' and exists ( select 1 from per_all_assignments_f asgn'
488 || ' , per_assignment_status_types pat '
489 || ' where asgn.person_id = ppf.person_id '
490 || ' and pat.assignment_status_type_id = asgn.assignment_status_type_id '
491 || ' and pat.per_system_status = ''ACTIVE_ASSIGN'' '
492 || ' and asgn.PRIMARY_FLAG = ''Y'' '
493 || ' and asgn.assignment_type = ''E'' '
494 || ' and :effective_date is not null '
495 || ' and asgn.effective_end_date >= :l_wth_start_date )' ;
496 --Bug 6718304
497 else
498 l_cwb_whclause := l_cwb_whclause
499 || ' and exists ( select 1 from per_all_assignments_f asgn'
500 || ' , per_assignment_status_types pat '
501 || ' where asgn.person_id = ppf.person_id '
502 || ' and pat.assignment_status_type_id = asgn.assignment_status_type_id '
503 || ' and pat.per_system_status = ''ACTIVE_ASSIGN'' '
504 || ' and asgn.PRIMARY_FLAG = ''Y'' '
505 || ' and asgn.assignment_type = ''E'' '
506 || ' and :effective_date between asgn.effective_start_date '
507 || ' and asgn.effective_end_date '
508 || ' and :l_wth_start_date is null )' ;
509 -- Bug 6718304
510 end if;
511 else
512 l_cwb_whclause := l_cwb_whclause
513 || ' and exists ( select 1 from per_all_assignments_f asgn'
514 || ' , per_assignment_status_types pat '
515 || ' where asgn.person_id = ppf.person_id '
516 || ' and pat.assignment_status_type_id = asgn.assignment_status_type_id '
517 || ' and pat.per_system_status = ''ACTIVE_ASSIGN'' '
518 || ' and asgn.PRIMARY_FLAG = ''Y'' '
519 || ' and asgn.assignment_type = ''E'' '
520 || ' and :effective_date between asgn.effective_start_date '
521 || ' and asgn.effective_end_date '
522 || ' and :l_wth_start_date is null )' ;
523 end if;
524 end if;
525 -- bug 3537113
526 --
527 l_query_str := l_query_str||l_cwb_whclause ;
528 --
529 hr_utility.set_location('Building cwb where clause ',29);
530 --
531 ELSE -- if p_mode_cd <> 'W' then
532 --
533 -- Bug 2279394 : where clause is not formed correctly, as and is missing
534 -- in where clause.
535 --
536 l_cwb_whclause := ' and :effective_date is NOT NULL '
537 || ' and :effective_date is not null '
538 || ' and :l_wth_start_date is null ';
539 l_query_str := l_query_str||l_cwb_whclause ;
540 --
541 END IF;
542 --
543 -- If a person id was specified, use it in the main query.
544 --
545 IF p_person_id IS NOT NULL THEN
546 --
547 -- Bind in required person id
548 --
549 l_person_id_bind := p_person_id;
550 l_query_str := l_query_str || ' and ppf.person_id = :person_id';
551 --
552 ELSE
553 --
554 l_person_id_bind := -1;
555 l_query_str := l_query_str || ' and -1 = :person_id';
556 END IF;
557 --
558 -- cwbglobal
559 -- dont pick ptnl ler's with status processed or voided
560 /*
561 if p_mode_cd = 'W' then
562 -- Bug 3981941
563 -- We dont want to pick up persons that have CWB person life event in Started State.
564 -- Corrected the following cursor.
565
566 l_query_str :=
567 l_query_str || ' and exists (select null' ||
568 ' from ben_ptnl_ler_for_per ptn' ||
569 ' ,ben_ler_f ler' ||
570 ' where ptn.person_id = ppf.person_id' ||
571 ' and ler.ler_id = ptn.ler_id' ||
572 ' and ptn.lf_evt_ocrd_dt between ler.effective_start_date and ler.effective_end_date' ||
573 ' and ler.typ_cd =''COMP'' ' ||
574 ' and ptn.ptnl_ler_for_per_stat_cd not in(''VOIDD'',''PROCD''))';
575
576 l_query_str :=
577 l_query_str || ' and not exists (select null' ||
578 ' from ben_per_in_ler pil' ||
579 ' ,ben_ler_f ler' ||
580 ' where pil.person_id = ppf.person_id' ||
581 ' and ler.ler_id = pil.ler_id' ||
582 ' and pil.lf_evt_ocrd_dt between ler.effective_start_date and ler.effective_end_date' ||
583 ' and ler.typ_cd =''COMP'' ' ||
584 ' and pil.per_in_ler_stat_cd in(''STRTD''))';
585 end if;
586 */
587 hr_utility.set_location('CWB group pl id'|| ben_manage_cwb_life_events.g_cache_group_plan_rec.group_pl_id ,10);
588
589 -- end cwbglobal
590
591
592 -- If a person type id was specified, use it in a subquery.
593 --
594 IF p_person_type_id IS NOT NULL THEN
595 --
596 -- Bind in required person type id
597 --
598 l_person_type_id_bind := p_person_type_id;
599
600 -- Changed the query for fixing Bug 2224299
601 -- l_query_str :=
602 -- l_query_str || ' and exists (select null from per_person_types ppt' ||
603 -- ' where ppf.person_type_id = ppt.person_type_id' ||
604 -- ' and ppt.person_type_id = :person_type_id' ||
605 -- ' and ppt.active_flag = ''Y'')';
606
607 l_person_type_id_date_bind := p_effective_date;
608
609 l_query_str :=
610 l_query_str || ' and exists (select null from per_person_types ppt,per_person_type_usages_f ptu ' ||
611 ' where ppt.person_type_id = ptu.person_type_id' ||
612 ' and ppt.person_type_id = :person_type_id' ||
613 ' and ptu.person_id = ppf.person_id' ||
614 ' and :person_type_id_date between ptu.effective_start_date and ptu.effective_end_date' ||
615 ' and ppt.active_flag = ''Y'')';
616
617 --
618 ELSE
619 --
620 l_person_type_id_bind := -1;
621
622 -- Changed the query for fixing Bug 2224299
623 -- l_query_str := l_query_str || ' and -1 = :person_type_id';
624
625 l_person_type_id_date_bind := hr_api.g_sot;
626 l_query_str := l_query_str ||
627 ' and -1 = :person_type_id and :person_type_id_date IS NOT NULL';
628 --
629 END IF;
630 --
631 -- If a benfts_grp_id was specified, use it in a subquery.
632 --
633 IF p_benfts_grp_id IS NOT NULL THEN
634 --
635 -- Bind in required person type id
636 --
637 l_benfts_grp_id_bind := p_benfts_grp_id;
638 l_query_str :=
639 l_query_str || ' and ppf.benefit_group_id = :benfts_grp_id';
640 --
641 ELSE
642 --
643 l_benfts_grp_id_bind := -1;
644 l_query_str := l_query_str || ' and -1 = :benfts_grp_id';
645 --
646 END IF;
647 --
648 -- If a location_id was specified, use it the main query.
649 --
650 IF p_location_id IS NOT NULL THEN
651 --
652 -- Bind in required variables
653 --
654 l_location_id_bind := p_location_id;
655 l_location_id_date_bind := p_effective_date;
656 l_query_str :=
657 l_query_str || ' and exists (select null' ||
658 ' from per_all_assignments_f paf' ||
659 ' where paf.person_id = ppf.person_id' ||
660 ' and paf.assignment_type <> ''C'''||
661 ' and paf.primary_flag = ''Y''' ||
662 ' and paf.business_group_id = ppf.business_group_id' ||
663 ' and :location_id_date' ||
664 ' between paf.effective_start_date' ||
665 ' and paf.effective_end_date' ||
666 ' and paf.location_id = :location_id)';
667 --
668 ELSE
669 --
670 l_location_id_bind := -1;
671 l_location_id_date_bind := hr_api.g_sot;
672 l_query_str :=
673 l_query_str ||
674 ' and :location_id_date IS NOT NULL and -1 = :location_id ';
675 END IF;
676 --
677 -- If a legal_entity_id was specified, use it in a subquery.
678 --
679 IF p_legal_entity_id IS NOT NULL THEN
680 --
681 -- Bind in required legal entity id
682 --
683 l_legal_entity_id_date_bind := p_effective_date;
684 l_legal_entity_id_bind := p_legal_entity_id;
685 l_query_str :=
686 l_query_str || ' and exists (select null' ||
687 ' from hr_soft_coding_keyflex hsc,' ||
688 ' per_all_assignments_f paf' ||
689 ' where paf.person_id = ppf.person_id' ||
690 ' and paf.assignment_type <> ''C'''||
691 ' and paf.primary_flag = ''Y''' ||
692 ' and paf.business_group_id = ppf.business_group_id' ||
693 ' and :legal_entity_id_date' ||
694 ' between paf.effective_start_date' ||
695 ' and paf.effective_end_date ' ||
696 ' and paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id' ||
697 ' and hsc.segment1 = to_char(:legal_entity_id)) ';
698 --
699 /* Note the use of to_char this is for CBO joins between varchar2
700 and number columns */
701 --
702 ELSE
703 --
704 l_legal_entity_id_date_bind := hr_api.g_sot;
705 l_legal_entity_id_bind := -1;
706 l_query_str :=
707 l_query_str ||
708 ' and :legal_entity_id_date IS NOT NULL and -1 = :legal_entity_id ';
709 --
710 END IF;
711 --
712 -- If a payroll_id was specified, use it in a subquery.
713 --
714 IF p_payroll_id IS NOT NULL THEN
715 --
716 -- Bind in required payroll id
717 --
718 l_payroll_id_date_bind := p_effective_date;
719 l_payroll_id_bind := p_payroll_id;
720 l_payroll_id_date2_bind := p_effective_date;
721 --
722 l_query_str :=
723 l_query_str || ' and exists (select null' || ' from pay_payrolls_f pay,' ||
724 ' per_all_assignments_f paf' ||
725 ' where paf.person_id = ppf.person_id ' ||
726 ' and paf.assignment_type <> ''C'''||
727 ' and paf.primary_flag = ''Y''' ||
728 ' and paf.business_group_id = ppf.business_group_id' ||
729 ' and :payroll_id_date' ||
730 ' between paf.effective_start_date' ||
731 ' and paf.effective_end_date ' ||
732 ' and pay.payroll_id = :payroll_id' ||
733 ' and pay.payroll_id = paf.payroll_id' ||
734 ' and :payroll_id_date2' ||
735 ' between pay.effective_start_date' ||
736 ' and pay.effective_end_date)';
737 --
738 ELSE
739 --
740 l_payroll_id_date_bind := hr_api.g_sot;
741 l_payroll_id_bind := -1;
742 l_payroll_id_date2_bind := hr_api.g_sot;
743 --
744 l_query_str :=
745 l_query_str ||
746 ' and :payroll_id_date IS NOT NULL and -1 = :payroll_id' ||
747 ' and :payroll_id_date2 IS NOT NULL ';
748 --
749 END IF;
750 --
751 -- If a pstl_zip_rng_id was specified, use it in a subquery.
752 --
753 IF p_pstl_zip_rng_id IS NOT NULL THEN
754 --
755 -- Bind in required pstl zip rng id
756 --
757 l_pstl_zip_rng_id_date_bind := p_effective_date;
758 l_pstl_zip_rng_id_date2_bind := p_effective_date;
759 l_pstl_zip_rng_id_date3_bind := p_effective_date;
760 l_pstl_zip_rng_id_bind := p_pstl_zip_rng_id;
761 l_pstl_zip_rng_id_date4_bind := p_effective_date;
762 --
763 l_query_str :=
764 l_query_str || ' and exists (select null' || ' from per_addresses pad,' ||
765 ' ben_pstl_zip_rng_f rzr' ||
766 ' where pad.person_id = ppf.person_id' ||
767 ' and pad.primary_flag = ''Y''' ||
768 ' and :ptl_zip_rng_id_date' ||
769 ' between nvl(pad.date_from,:pstl_zip_rng_id_date2)' ||
770 ' and nvl(pad.date_to,:pstl_zip_rng_id_date3)' ||
771 ' and rzr.pstl_zip_rng_id = :pstl_zip_rng_id' ||
772 ' and pad.postal_code' ||
773 ' between rzr.from_value' ||
774 ' and rzr.to_value' ||
775 ' and :pstl_zip_rng_id_date4' ||
776 ' between rzr.effective_start_date' ||
777 ' and rzr.effective_end_date)';
778 --
779 ELSE
780 --
781 l_pstl_zip_rng_id_date_bind := hr_api.g_sot;
782 l_pstl_zip_rng_id_date2_bind := hr_api.g_sot;
783 l_pstl_zip_rng_id_date3_bind := hr_api.g_sot;
784 l_pstl_zip_rng_id_bind := -1;
785 l_pstl_zip_rng_id_date4_bind := hr_api.g_sot;
786 --
787 l_query_str :=
788 l_query_str || ' and :pstl_zip_rng_id_date IS NOT NULL' ||
789 ' and :pstl_zip_rng_id_date2 IS NOT NULL' ||
790 ' and :pstl_zip_rng_id_date3 IS NOT NULL' ||
791 ' and -1 = :pstl_zip_rng_id' ||
792 ' and :pstl_zip_rng_id_date4 IS NOT NULL ';
793 --
794 END IF;
795 --
796 -- If an organization_id was specified, use it in a subquery.
797 --
798 IF p_organization_id IS NOT NULL THEN
799 --
800 -- Bind in required organization id
801 --
802 l_organization_id_date_bind := p_effective_date;
803 l_organization_id_bind := p_organization_id;
804 l_organization_id_date2_bind := p_effective_date;
805 l_organization_id_date3_bind := p_effective_date;
806 --
807 l_query_str :=
808 l_query_str || ' and exists (select null' ||
809 ' from hr_organization_units org,' ||
810 ' per_all_assignments_f paf' ||
811 ' where paf.person_id = ppf.person_id' ||
812 ' and paf.assignment_type <> ''C'''||
813 ' and paf.primary_flag = ''Y''' ||
814 ' and paf.business_group_id = ppf.business_group_id' ||
815 ' and :organization_id_date' ||
816 ' between paf.effective_start_date' ||
817 ' and paf.effective_end_date' ||
818 ' and paf.organization_id = org.organization_id' ||
819 ' and org.organization_id = :organization_id ' ||
820 ' and :organization_id_date2' ||
821 ' between org.date_from' ||
822 ' and nvl(org.date_to,:organization_id_date3))';
823 --
824 ELSE
825 --
826 l_organization_id_date_bind := hr_api.g_sot;
827 l_organization_id_bind := -1;
828 l_organization_id_date2_bind := hr_api.g_sot;
829 l_organization_id_date3_bind := hr_api.g_sot;
830 --
831 l_query_str :=
832 l_query_str || ' and :organization_id_date IS NOT NULL' ||
833 ' and -1 = :organization_id' ||
834 ' and :organization_id_date2 IS NOT NULL' ||
835 ' and :organization_id_date3 IS NOT NULL ';
836 --
837 END IF;
838 --
839 -- Finish the main query.
840 --
841 IF p_lmt_prpnip_by_org_flag = 'N' THEN
842 --
843 l_query_str :=
844 l_query_str || ' and :effective_date' ||
845 ' between ppf.effective_start_date' ||
846 ' and ppf.effective_end_date' ||
847 ' and :effective_date is not null ' ||
848 ' order by ppf.full_name';
849 --
850 ELSE
851 --
852 l_query_str :=
853 l_query_str || ' and :effective_date' ||
854 ' between ppf.effective_start_date' ||
855 ' and ppf.effective_end_date' ||
856 ' and :effective_date between paf1.effective_start_date(+)' ||
857 ' and paf1.effective_end_date(+)' ||
858 ' order by paf1.organization_id';
859 --
860 END IF;
861 --
862 -- fnd_file.put_line(fnd_file.log, l_query_str );
863 -- Open the query using business_group_id as the bind variable.
864 --
865 OPEN c_person FOR l_query_str
866 USING p_business_group_id
867 ,p_effective_date
868 ,p_effective_date -- added for Bug 2248822
869 ,p_effective_date -- added for Bug 3537113
870 ,l_wth_start_date
871 ,l_person_id_bind
872 ,l_person_type_id_bind
873 ,l_person_type_id_date_bind -- added for Bug 2224299
874 ,l_benfts_grp_id_bind
875 ,l_location_id_date_bind
876 ,l_location_id_bind
877 ,l_legal_entity_id_date_bind
878 ,l_legal_entity_id_bind
879 ,l_payroll_id_date_bind
880 ,l_payroll_id_bind
881 ,l_payroll_id_date2_bind
882 ,l_pstl_zip_rng_id_date_bind
883 ,l_pstl_zip_rng_id_date2_bind
884 ,l_pstl_zip_rng_id_date3_bind
885 ,l_pstl_zip_rng_id_bind
886 ,l_pstl_zip_rng_id_date4_bind
887 ,l_organization_id_date_bind
888 ,l_organization_id_bind
889 ,l_organization_id_date2_bind
890 ,l_organization_id_date3_bind
891 ,p_effective_date
892 ,p_effective_date;
893 --
894 -- ------------------------------------------------------------
895 -- NOTE: for some reason the following FETCH statement fails
896 -- at runtime with an invalid cursor type error:
897 -- FETCH c_person BULK COLLECT INTO l_person_id_table;
898 -- To get around this problem, each row is selected
899 -- individually (how disappointing).
900 -- ------------------------------------------------------------
901 LOOP
902 FETCH c_person INTO l_person_id_fetch;
903 EXIT WHEN c_person%NOTFOUND;
904 -- Only process if person passed selection rule or there is no rule.
905 IF check_selection_rule(p_person_selection_rule_id=> p_person_selection_rule_id
906 ,p_person_id => l_person_id_fetch
907 ,p_business_group_id => p_business_group_id
908 ,p_effective_date => p_effective_date)
909 or p_mode = 'I' -- IREC
910 THEN
911 --
912 l_person_id_process(l_person_id_process.COUNT + 1) :=
913 l_person_id_fetch;
914 END IF;
915 END LOOP;
916 CLOSE c_person;
917 --
918 IF l_person_id_process.COUNT > 0 THEN
919 -- bulk insert all person action(s)
920 FORALL l_count IN l_person_id_process.FIRST .. l_person_id_process.LAST
921 --
922 INSERT INTO ben_person_actions
923 (
924 person_action_id
925 ,person_id
926 ,ler_id
927 ,benefit_action_id
928 ,action_status_cd
929 ,object_version_number)
930 VALUES(
931 ben_person_actions_s.nextval
932 ,l_person_id_process(l_count)
933 ,p_ler_override_id
934 ,p_benefit_action_id
935 ,'U'
936 ,1)
937 RETURNING person_action_id BULK COLLECT INTO l_person_action_id_table;
938 --
939 -- 99999 Delete the duplicates from the ben_person_actions
940 -- here.
941
942
943 IF MOD(l_person_action_id_table.COUNT
944 ,p_chunk_size) = 0 THEN
945 l_to_chunk_loop :=
946 TRUNC(l_person_action_id_table.COUNT / p_chunk_size);
947 ELSE
948 l_to_chunk_loop :=
949 TRUNC(l_person_action_id_table.COUNT / p_chunk_size) + 1;
950 END IF;
951 --
952 FOR i IN 1 .. l_to_chunk_loop LOOP
953 -- set the starting point range
954 l_start_person_action_id :=
955 l_person_action_id_table(((i - 1) * p_chunk_size) + 1);
956 IF i <> l_to_chunk_loop THEN
957 l_end_person_action_id :=
958 l_person_action_id_table(i * p_chunk_size);
959 ELSE
960 l_end_person_action_id :=
961 l_person_action_id_table(l_person_action_id_table.COUNT);
962 END IF;
963 --
964 INSERT INTO ben_batch_ranges
965 (
966 range_id
967 ,benefit_action_id
968 ,range_status_cd
969 ,starting_person_action_id
970 ,ending_person_action_id
971 ,object_version_number)
972 VALUES(
973 ben_batch_ranges_s.nextval
974 ,p_benefit_action_id
975 ,'U'
976 ,l_start_person_action_id
977 ,l_end_person_action_id
978 ,1);
979 --
980 END LOOP;
981 IF p_commit_data = 'Y' THEN
982 -- This is for What IF Functionality
983 COMMIT;
984 END IF;
985 END IF;
986 --
987 p_num_ranges := l_to_chunk_loop;
988 p_num_persons := l_person_action_id_table.COUNT;
989 --
990 END create_normal_person_actions;
991 --
992 PROCEDURE create_life_person_actions(
993 p_benefit_action_id IN NUMBER
994 ,p_business_group_id IN NUMBER
995 ,p_person_id IN NUMBER
996 ,p_ler_id IN NUMBER
997 ,p_person_type_id IN NUMBER
998 ,p_benfts_grp_id IN NUMBER
999 ,p_location_id IN NUMBER
1000 ,p_legal_entity_id IN NUMBER
1001 ,p_payroll_id IN NUMBER
1002 ,p_pstl_zip_rng_id IN NUMBER
1003 ,p_organization_id IN NUMBER
1004 ,p_person_selection_rule_id IN NUMBER
1005 ,p_effective_date IN DATE
1006 ,p_chunk_size IN NUMBER
1007 ,p_threads IN NUMBER
1008 ,p_num_ranges OUT NOCOPY NUMBER
1009 ,p_num_persons OUT NOCOPY NUMBER
1010 ,p_commit_data IN VARCHAR2
1011 ,p_lmt_prpnip_by_org_flag IN VARCHAR2
1012 -- GRADE/STEP : Added for grade/step benmngle
1013 ,p_org_heirarchy_id in number default null
1014 ,p_org_starting_node_id in number default null
1015 ,p_grade_ladder_id in number default null
1016 ,p_asg_events_to_all_sel_dt in date default null
1017 ,p_rate_id in number default null
1018 ,p_per_sel_dt_cd in varchar2 default null
1019 ,p_per_sel_dt_from in date default null
1020 ,p_per_sel_dt_to in date default null
1021 ,p_year_from in number default null
1022 ,p_year_to in number default null
1023 ,p_cagr_id in number default null
1024 ,p_qual_type in number default null
1025 ,p_qual_status in varchar2 default null
1026 -- 2940151
1027 ,p_per_sel_freq_cd in varchar2 default 'Y'
1028 ,p_id_flex_num in number default null
1029 ,p_concat_segs in varchar2 default null
1030 -- end 2940151
1031 ,p_mode IN VARCHAR2 default null
1032 ,p_lf_evt_oper_cd IN VARCHAR2 default null /* GSP Rate Sync */
1033 ) IS
1034 --
1035 -- Native dynamic PLSQL definition
1036 --
1037 TYPE cur_type IS REF CURSOR;
1038 c_person_life cur_type;
1039 --
1040 --
1041 l_person_id_fetch NUMBER;
1042 l_person_id_process ben_benbatch_persons.g_number_table_type;
1043 l_person_action_id_table ben_benbatch_persons.g_number_table_type;
1044 l_to_chunk_loop NUMBER := 0;
1045 --
1046 --
1047 -- Local variables
1048 --
1049 l_query_str VARCHAR2(5000);
1050 l_start_person_action_id NUMBER;
1051 l_end_person_action_id NUMBER;
1052 l_person_id_bind NUMBER;
1053 l_person_type_id_bind NUMBER;
1054 l_benfts_grp_id_bind NUMBER;
1055 l_location_id_date_bind DATE;
1056 l_location_id_bind NUMBER;
1057 l_cagr_id_date_bind DATE;
1058 l_cagr_id_bind NUMBER;
1059 l_legal_entity_id_date_bind DATE;
1060 l_legal_entity_id_bind NUMBER;
1061 l_typ_cd varchar2(200);
1062 l_payroll_id_date_bind DATE;
1063 l_payroll_id_bind NUMBER;
1064 l_payroll_id_date2_bind DATE;
1065 l_pstl_zip_rng_id_date_bind DATE;
1066 l_pstl_zip_rng_id_date2_bind DATE;
1067 l_pstl_zip_rng_id_date3_bind DATE;
1068 l_pstl_zip_rng_id_bind NUMBER;
1069 l_pstl_zip_rng_id_date4_bind DATE;
1070 l_organization_id_date_bind DATE;
1071 l_organization_id_bind NUMBER;
1072 l_organization_id_date2_bind DATE;
1073 l_organization_id_date3_bind DATE;
1074 -- 2940151
1075 l_grade_ladder_bind NUMBER;
1076 l_grade_ladder_date_bind DATE;
1077 l_grade_ladder_date2_bind DATE;
1078 l_grade_ladder_date3_bind DATE;
1079 l_grade_ladder_date4_bind DATE;
1080 l_qual_type_bind NUMBER;
1081 l_qual_status_bind VARCHAR2(30);
1082 l_rate_id_bind NUMBER;
1083 l_rate_id_date_bind DATE;
1084 l_per_sel_dt_from DATE;
1085 l_per_sel_dt_to DATE;
1086 l_per_sel_freq_cd NUMBER;
1087 l_year_from NUMBER;
1088 l_year_to NUMBER;
1089 l_freq NUMBER;
1090 l_seg_value VARCHAR2(100);
1091 l_def_flag VARCHAR2(1);
1092 l_dflt_grade_ldr_id NUMBER;
1093 l_count number := 0; -- the count of the individual segments
1094 l_pos number := 1; -- the position of the individual segment
1095 l_pos_sep number; -- the position of the separator
1096 l_length number; -- the length of the string
1097 l_org_heirarchy_date DATE;
1098 l_org_heirarchy number;
1099 l_org_starting_node_id number;
1100 l_concat_segs_date DATE;
1101 -- end 2940151
1102 l_ler_id_bind NUMBER;
1103 l_life_date_bind DATE;
1104 l_person_date_bind DATE;
1105 --
1106 l_person_type_id_date_bind DATE; -- Bug 2224299
1107 l_gsp_whclause LONG;
1108 --
1109 -- irec
1110 l_irec_whclause varchar2(2000);
1111 l_assignment_id_bind number;
1112 -- irec end
1113
1114
1115 -- 2940151 --cursors
1116 -- Get the delimiter for the kff structure
1117 cursor c_delimiter is
1118 select concatenated_segment_delimiter
1119 from fnd_id_flex_structures
1120 where id_flex_num = p_id_flex_num
1121 and application_id = 801
1122 and id_flex_code = 'GRP';
1123
1124 l_concat_sep varchar2(1);
1125
1126 -- Get the segments in the order defined in the structure
1127 cursor c_application_column_name is
1128 select application_column_name
1129 from fnd_id_flex_segments
1130 where id_flex_num = p_id_flex_num
1131 and application_id = 801
1132 and id_flex_code = 'GRP'
1133 and enabled_flag = 'Y'
1134 order by segment_num;
1135
1136 l_application_column_name c_application_column_name%ROWTYPE;
1137
1138 -- get the default grade ladder
1139 cursor c_dflt_grade_ladder is
1140 -- select pgm.pgm_prvds_no_dflt_enrt_flag, pgm.pgm_id /* Bug 4030438 */
1141 select pgm.dflt_pgm_flag, pgm.pgm_id
1142 from ben_pgm_f pgm
1143 where pgm.pgm_id = p_grade_ladder_id
1144 and p_effective_date between effective_start_date and effective_end_date
1145 and business_group_id = p_business_group_id ;
1146
1147
1148 --
1149 BEGIN
1150 --
1151 IF p_lmt_prpnip_by_org_flag = 'N' then
1152 --
1153 l_query_str :=
1154 ' select ppf.person_id from per_all_people_f ppf' ||
1155 ' where ppf.business_group_id = :bus_grp_id' ||
1156 ' and :effective_date is not null ' ;
1157 --
1158 else
1159 --
1160 l_query_str :=
1161 ' select ppf.person_id from per_all_people_f ppf' ||
1162 ', per_all_assignments_f paf1' ||
1163 ' where ppf.business_group_id = :bus_grp_id' ||
1164 ' and ppf.person_id = paf1.person_id(+) ' ||
1165 ' and ppf.business_group_id = paf1.business_group_id(+) ' ||
1166 ' and paf1.primary_flag(+) = ''Y'' ' ||
1167 ' and (paf1.assignment_id is null ' ||
1168 ' or paf1.assignment_id = ' ||
1169 ' ( select min(paf2.assignment_id) ' ||
1170 ' from per_all_assignments_f paf2 ' ||
1171 ' where paf2.person_id = paf1.person_id ' ||
1172 ' and paf2.assignment_type <> ''C'''||
1173 ' and paf1.business_group_id = paf2.business_group_id ' ||
1174 ' and paf2.primary_flag = ''Y'' ' ||
1175 ' and :effective_date between paf2.effective_start_date' ||
1176 ' and paf2.effective_end_date' ||
1177 ' ) ' ||
1178 ' )' ;
1179 --
1180 end if;
1181 --
1182 -- If a person id was passed use it in the query.
1183 IF p_person_id IS NOT NULL THEN
1184 l_person_id_bind := p_person_id;
1185 l_query_str := l_query_str || ' and ppf.person_id = :person_id';
1186 ELSE
1187 l_person_id_bind := -1;
1188 l_query_str := l_query_str || ' and -1 = :person_id';
1189 END IF;
1190 --
1191 -- Attach the subquery
1192 -- GRADE/STEP : In case mode is grade step and need to create
1193 -- grade/step life events for everyone then don't check for
1194 -- potential existence; potentials will be created for everyone.
1195 --
1196
1197 if (p_mode = 'M' or (p_mode = 'G' and p_asg_events_to_all_sel_dt is null)) then
1198 --
1199 -- ABSENCES : Only consider persons with absence or grade/step potentials.
1200 --
1201 if p_mode = 'M' then
1202 l_typ_cd := '''ABS''';
1203 elsif p_mode = 'G' then
1204 l_typ_cd := '''GSP''';
1205 end if;
1206 --
1207 l_query_str :=
1208 l_query_str || ' and exists (select null' ||
1209 ' from ben_ptnl_ler_for_per ptn' ||
1210 ' ,ben_ler_f ler_abse' ||
1211 ' where ptn.person_id = ppf.person_id' ||
1212 ' and ler_abse.ler_id = ptn.ler_id' ||
1213 ' and ptn.lf_evt_ocrd_dt between ler_abse.effective_start_date and ler_abse.effective_end_date' ||
1214 ' and ler_abse.typ_cd = ' || l_typ_cd ||
1215 ' and ptn.ptnl_ler_for_per_stat_cd not in(''VOIDD'',''PROCD'')';
1216 --
1217 elsif p_mode = 'L' then
1218 --
1219 l_typ_cd := 'not in ( ''COMP'', ''ABS'', ''GSP'')';
1220 --
1221
1222 l_query_str :=
1223 l_query_str || ' and exists (select null' ||
1224 ' from ben_ptnl_ler_for_per ptn' ||
1225 ' ,ben_ler_f ler_ben' ||
1226 ' where ptn.person_id = ppf.person_id' ||
1227 ' and ler_ben.ler_id = ptn.ler_id' ||
1228 ' and ptn.lf_evt_ocrd_dt between ler_ben.effective_start_date and ler_ben.effective_end_date' ||
1229 -- l_type_cd has not in so = not be allowed
1230 ' and ler_ben.typ_cd ' || l_typ_cd ||
1231 ' and ptn.ptnl_ler_for_per_stat_cd not in(''VOIDD'',''PROCD'')';
1232 --
1233 end if;
1234
1235 -- If a ler_id was passed then attach it to the subquery
1236 IF p_ler_id IS NOT NULL THEN
1237 --
1238 -- 3394157
1239 if ((p_mode <> 'G' or (p_mode = 'G' and p_asg_events_to_all_sel_dt is null))
1240 and p_mode not in ( 'I','A') ) then
1241 l_ler_id_bind := p_ler_id;
1242 l_query_str := l_query_str || ' and ptn.ler_id = :ler_id ';
1243 --
1244 else
1245 -- When p_asg_events_to_all_sel_dt is set no need to check
1246 -- for existence of potentials.
1247 l_ler_id_bind := -1;
1248 l_query_str := l_query_str || ' and -1 = :ler_id ';
1249 end if;
1250 -- 3394157
1251 ELSE
1252 --
1253 l_ler_id_bind := -1;
1254 l_query_str := l_query_str || ' and -1 = :ler_id ';
1255 --
1256 END IF;
1257 --
1258 -- If a person type id was specified, use it in a subquery.
1259 --
1260 IF p_person_type_id IS NOT NULL THEN
1261 --
1262 -- Bind in required person type id
1263 --
1264 l_person_type_id_bind := p_person_type_id;
1265
1266 -- Changed the query for fixing Bug 2224299
1267 -- l_query_str :=
1268 -- l_query_str || ' and exists (select null' ||
1269 -- ' from per_person_types ppt' ||
1270 -- ' where ppf.person_type_id = ppt.person_type_id' ||
1271 -- ' and ppt.person_type_id = :person_type_id' ||
1272 -- ' and ppt.active_flag = ''Y'')';
1273
1274 l_person_type_id_date_bind := p_effective_date;
1275
1276 l_query_str :=
1277 l_query_str || ' and exists (select null from per_person_types ppt,per_person_type_usages_f ptu ' ||
1278 ' where ppt.person_type_id = ptu.person_type_id' ||
1279 ' and ppt.person_type_id = :person_type_id' ||
1280 ' and ptu.person_id = ppf.person_id' ||
1281 ' and :person_type_id_date between ptu.effective_start_date and ptu.effective_end_date' ||
1282 ' and ppt.active_flag = ''Y'')';
1283
1284 ELSE
1285 l_person_type_id_bind := -1;
1286
1287 -- Changed the query for fixing Bug 2224299
1288 -- l_query_str := l_query_str || ' and -1 = :person_type_id';
1289
1290 l_person_type_id_date_bind := hr_api.g_sot;
1291 l_query_str := l_query_str ||
1292 ' and -1 = :person_type_id and :person_type_id_date IS NOT NULL';
1293
1294 END IF;
1295 -- If a benfts_grp_id was specified, use it in a subquery.
1296 IF p_benfts_grp_id IS NOT NULL THEN
1297 -- Bind in required person type id
1298 l_benfts_grp_id_bind := p_benfts_grp_id;
1299 l_query_str :=
1300 l_query_str || ' and ppf.benefit_group_id = :benfts_grp_id';
1301 ELSE
1302 l_benfts_grp_id_bind := -1;
1303 l_query_str := l_query_str || ' and -1 = :benfts_grp_id';
1304 END IF;
1305 --
1306 -- If a location_id was specified, use it the main query.
1307 --
1308 IF p_location_id IS NOT NULL THEN
1309 -- Bind in required variables
1310 l_location_id_bind := p_location_id;
1311 l_location_id_date_bind := p_effective_date;
1312 l_query_str :=
1313 l_query_str || ' and exists (select null' ||
1314 ' from per_all_assignments_f paf' ||
1315 ' where paf.person_id = ppf.person_id' ||
1316 ' and paf.assignment_type <> ''C'''||
1317 ' and paf.primary_flag = ''Y''' ||
1318 ' and paf.business_group_id = ppf.business_group_id' ||
1319 ' and :location_id_date' ||
1320 ' between paf.effective_start_date' ||
1321 ' and paf.effective_end_date' ||
1322 ' and paf.location_id = :location_id)';
1323 ELSE
1324 l_location_id_bind := -1;
1325 l_location_id_date_bind := hr_api.g_sot;
1326 l_query_str :=
1327 l_query_str ||
1328 ' and :location_id_date IS NOT NULL and -1 = :location_id ';
1329 END IF;
1330 --
1331 -- GRADE/STEP : If a collective_agreement_id was specified, use it the main query.
1332 --
1333 IF p_cagr_id IS NOT NULL THEN
1334 -- Bind in required variables
1335 l_cagr_id_bind := p_cagr_id;
1336 l_cagr_id_date_bind := p_effective_date;
1337 l_query_str :=
1338 l_query_str || ' and exists (select null' ||
1339 ' from per_all_assignments_f paf' ||
1340 ' where paf.person_id = ppf.person_id' ||
1341 ' and paf.assignment_type <> ''C'''||
1342 ' and paf.primary_flag = ''Y''' ||
1343 ' and paf.business_group_id = ppf.business_group_id' ||
1344 ' and :cagr_id_date' ||
1345 ' between paf.effective_start_date' ||
1346 ' and paf.effective_end_date' ||
1347 ' and paf.collective_agreement_id = :cagr_id)';
1348 ELSE
1349 l_cagr_id_bind := -1;
1350 l_cagr_id_date_bind := hr_api.g_sot;
1351 l_query_str :=
1352 l_query_str ||
1353 ' and :cagr_id_date IS NOT NULL and -1 = :cagr_id ';
1354 END IF;
1355 --
1356 -- GRADE/STEP Restrict the persons to only Employees
1357 --
1358 IF p_mode = 'G' THEN
1359 --
1360 l_gsp_whclause := ' and exists ( select ''x'' from per_person_type_usages_f ptu, '||
1361 ' per_person_types ppt '||
1362 ' where ppt.person_type_id = ptu.person_type_id '||
1363 ' and ppt.system_person_type = ''EMP'''||
1364 ' and ppt.business_group_id = ppf.business_group_id '||
1365 ' and ptu.person_id = ppf.person_id '||
1366 ' and :effective_date between ptu.effective_start_date '||
1367 ' and ptu.effective_end_date ) ' ;
1368 l_query_str := l_query_str||l_gsp_whclause ;
1369 --
1370 hr_utility.set_location('Building cwb where clause ',29);
1371 --
1372 ELSE
1373 --
1374 -- Bug 2279394 : where clause is not formed correctly, as and is missing
1375 -- in where clause.
1376 --
1377 l_gsp_whclause := ' and :effective_date is NOT NULL ' ;
1378 l_query_str := l_query_str||l_gsp_whclause ;
1379 --
1380 END IF;
1381 --
1382 --
1383 -- IREC
1384 -- if its iREC pick up only applicants
1385 IF p_mode = 'I' THEN
1386 l_assignment_id_bind := ben_manage_life_events.g_irec_ass_rec.assignment_id;
1387 l_irec_whclause := ' and exists ( select ''x'' from per_person_type_usages_f ptu, '||
1388 ' per_person_types ppt, per_all_assignments_f apl_ass '||
1389 ' where ppt.person_type_id = ptu.person_type_id '||
1390 ' and ppt.system_person_type in( ''APL'', ''APL_EX_APL'',''EMP_APL'', ''EX_EMP_APL'')'||
1391 ' and ppt.business_group_id = ppf.business_group_id '||
1392 ' and ptu.person_id = ppf.person_id '||
1393 ' and :effective_date between ptu.effective_start_date '||
1394 ' and ptu.effective_end_date ' ||
1395 ' and apl_ass.person_id = ppf.person_id '||
1396 ' and apl_ass.assignment_id = :assignment_id'||
1397 ' and apl_ass.assignment_type =''A'''||
1398 ' and :effective_date between apl_ass.effective_start_date '||
1399 ' and apl_ass.effective_end_date ) ' ;
1400
1401 l_query_str := l_query_str||l_irec_whclause ;
1402 ELSE
1403 --
1404 -- Bug 2279394 : where clause is not formed correctly, as and is missing
1405 -- in where clause.
1406 --
1407 l_assignment_id_bind := -1;--ben_manage_life_events.g_irec_ass_rec.assignment_id;
1408 l_irec_whclause := ' and :effective_date is NOT NULL and :assignment_id =-1 and :effective_date is NOT NULL ' ;
1409 l_query_str := l_query_str||l_irec_whclause ;
1410 --
1411 END IF;
1412
1413 -- If a legal_entity_id was specified, use it in a subquery.
1414 IF p_legal_entity_id IS NOT NULL THEN
1415 -- Bind in required legal entity id
1416 l_legal_entity_id_date_bind := p_effective_date;
1417 l_legal_entity_id_bind := p_legal_entity_id;
1418 l_query_str :=
1419 l_query_str || ' and exists (select null' ||
1420 ' from hr_soft_coding_keyflex hsc,' ||
1421 ' per_all_assignments_f paf' ||
1422 ' where paf.person_id = ppf.person_id' ||
1423 ' and paf.assignment_type <> ''C''' ||
1424 ' and paf.primary_flag = ''Y''' ||
1425 ' and paf.business_group_id = ppf.business_group_id' ||
1426 ' and :legal_entity_id_date' ||
1427 ' between paf.effective_start_date' ||
1428 ' and paf.effective_end_date ' ||
1429 ' and paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id' ||
1430 ' and hsc.segment1 = to_char(:legal_entity_id)) ';
1431 /* Note the use of to_char this is for CBO joins between varchar2
1432 and number columns */
1433 ELSE
1434 l_legal_entity_id_date_bind := hr_api.g_sot;
1435 l_legal_entity_id_bind := -1;
1436 l_query_str :=
1437 l_query_str ||
1438 ' and :legal_entity_id_date IS NOT NULL and -1 = :legal_entity_id ';
1439 END IF;
1440 -- If a payroll_id was specified, use it in a subquery.
1441 IF p_payroll_id IS NOT NULL THEN
1442 -- Bind in required payroll id
1443 l_payroll_id_date_bind := p_effective_date;
1444 l_payroll_id_bind := p_payroll_id;
1445 l_payroll_id_date2_bind := p_effective_date;
1446 l_query_str :=
1447 l_query_str || ' and exists (select null' || ' from pay_payrolls_f pay,' ||
1448 ' per_all_assignments_f paf' ||
1449 ' where paf.person_id = ppf.person_id' ||
1450 ' and paf.assignment_type <> ''C''' ||
1451 ' and paf.primary_flag = ''Y''' ||
1452 ' and paf.business_group_id = ppf.business_group_id' ||
1453 ' and :payroll_id_date' ||
1454 ' between paf.effective_start_date' ||
1455 ' and paf.effective_end_date ' ||
1456 ' and pay.payroll_id = :payroll_id' ||
1457 ' and pay.payroll_id = paf.payroll_id' ||
1458 ' and :payroll_id_date2' ||
1459 ' between pay.effective_start_date' ||
1460 ' and pay.effective_end_date)';
1461 ELSE
1462 l_payroll_id_date_bind := hr_api.g_sot;
1463 l_payroll_id_bind := -1;
1464 l_payroll_id_date2_bind := hr_api.g_sot;
1465 l_query_str :=
1466 l_query_str ||
1467 ' and :payroll_id_date IS NOT NULL and -1 = :payroll_id' ||
1468 ' and :payroll_id_date2 IS NOT NULL ';
1469 END IF;
1470 -- If a pstl_zip_rng_id was specified, use it in a subquery.
1471 IF p_pstl_zip_rng_id IS NOT NULL THEN
1472 -- Bind in required pstl zip rng id
1473 l_pstl_zip_rng_id_date_bind := p_effective_date;
1474 l_pstl_zip_rng_id_date2_bind := p_effective_date;
1475 l_pstl_zip_rng_id_date3_bind := p_effective_date;
1476 l_pstl_zip_rng_id_bind := p_pstl_zip_rng_id;
1477 l_pstl_zip_rng_id_date4_bind := p_effective_date;
1478 --
1479 l_query_str :=
1480 l_query_str || ' and exists (select null' || ' from per_addresses pad,' ||
1481 ' ben_pstl_zip_rng_f rzr' ||
1482 ' where pad.person_id = ppf.person_id' ||
1483 ' and pad.primary_flag = ''Y''' ||
1484 ' and :ptl_zip_rng_id_date' ||
1485 ' between nvl(pad.date_from,:pstl_zip_rng_id_date2)' ||
1486 ' and nvl(pad.date_to,:pstl_zip_rng_id_date3)' ||
1487 ' and rzr.pstl_zip_rng_id = :pstl_zip_rng_id' ||
1488 ' and pad.postal_code' ||
1489 ' between rzr.from_value' ||
1490 ' and rzr.to_value' ||
1491 ' and :pstl_zip_rng_id_date4' ||
1492 ' between rzr.effective_start_date' ||
1493 ' and rzr.effective_end_date)';
1494 ELSE
1495 l_pstl_zip_rng_id_date_bind := hr_api.g_sot;
1496 l_pstl_zip_rng_id_date2_bind := hr_api.g_sot;
1497 l_pstl_zip_rng_id_date3_bind := hr_api.g_sot;
1498 l_pstl_zip_rng_id_bind := -1;
1499 l_pstl_zip_rng_id_date4_bind := hr_api.g_sot;
1500 l_query_str :=
1501 l_query_str || ' and :pstl_zip_rng_id_date IS NOT NULL' ||
1502 ' and :pstl_zip_rng_id_date2 IS NOT NULL' ||
1503 ' and :pstl_zip_rng_id_date3 IS NOT NULL' ||
1504 ' and -1 = :pstl_zip_rng_id' ||
1505 ' and :pstl_zip_rng_id_date4 IS NOT NULL ';
1506 END IF;
1507 -- If an organization_id was specified, use it in a subquery.
1508 IF p_organization_id IS NOT NULL THEN
1509 -- Bind in required organization id
1510 l_organization_id_date_bind := p_effective_date;
1511 l_organization_id_bind := p_organization_id;
1512 l_organization_id_date2_bind := p_effective_date;
1513 l_organization_id_date3_bind := p_effective_date;
1514 l_query_str :=
1515 l_query_str || ' and exists (select null' ||
1516 ' from hr_organization_units org,' ||
1517 ' per_all_assignments_f paf' ||
1518 ' where paf.person_id = ppf.person_id' ||
1519 ' and paf.assignment_type <> ''C''' ||
1520 ' and paf.primary_flag = ''Y''' ||
1521 ' and paf.business_group_id = ppf.business_group_id' ||
1522 ' and :organization_id_date' ||
1523 ' between paf.effective_start_date' ||
1524 ' and paf.effective_end_date' ||
1525 ' and paf.organization_id = org.organization_id' ||
1526 ' and org.organization_id = :organization_id' ||
1527 ' and :organization_id_date2' ||
1528 ' between org.date_from' ||
1529 ' and nvl(org.date_to,:organization_id_date3))';
1530 ELSE
1531 l_organization_id_date_bind := hr_api.g_sot;
1532 l_organization_id_bind := -1;
1533 l_organization_id_date2_bind := hr_api.g_sot;
1534 l_organization_id_date3_bind := hr_api.g_sot;
1535 l_query_str :=
1536 l_query_str || ' and :organization_id_date IS NOT NULL' ||
1537 ' and -1 = :organization_id' ||
1538 ' and :organization_id_date2 IS NOT NULL' ||
1539 ' and :organization_id_date3 IS NOT NULL ';
1540 END IF;
1541 -- Finish the subquery
1542 l_life_date_bind := p_effective_date;
1543 --
1544 -- GSP New : When p_asg_events_to_all_sel_dt is set no need to check
1545 -- for existence of potentials.
1546 --
1547 if ((p_mode <> 'G' or (p_mode = 'G' and p_asg_events_to_all_sel_dt is null)))
1548 and p_mode NOT IN ('I','A') then
1549 l_query_str :=
1550 l_query_str || ' and ptn.lf_evt_ocrd_dt <= :life_date)';
1551 else
1552 l_query_str :=
1553 l_query_str || ' and :life_date is not null';
1554 end if;
1555 --
1556 -- GSP New
1557 --
1558 --
1559 -- 2940151
1560 -- Grade / Step - if grade ladder parameter is specified
1561
1562
1563 IF p_org_heirarchy_id is NOT NULL THEN
1564 -- bind variables
1565 l_org_heirarchy_date := p_effective_date;
1566 l_org_heirarchy := p_org_heirarchy_id;
1567 l_org_starting_node_id := p_org_starting_node_id;
1568
1569 l_query_str :=
1570 l_query_str ||' and exists'||
1571 ' (select 1 from per_org_structure_elements ose'||
1572 ' , per_all_assignments_f paf' ||
1573 ' where paf.person_id = ppf.person_id' ||
1574 ' and paf.assignment_type <> ''C''' ||
1575 ' and paf.primary_flag = ''Y''' ||
1576 ' and paf.business_group_id = ppf.business_group_id' ||
1577 ' and :org_heirarchy_date' ||
1578 ' between paf.effective_start_date' ||
1579 ' and paf.effective_end_date' ||
1580 ' and ose.org_structure_version_id = :org_hierarchy'||
1581 ' and paf.organization_id = ose.organization_id_child'||
1582 ' and paf.assignment_type = ''E'''||
1583 ' connect by prior ose.organization_id_child = ose.organization_id_parent'||
1584 ' and ose.org_structure_version_id = :org_hierarchy'||
1585 ' start with ose.organization_id_parent = :org_starting_node_id'||
1586 ' and ose.org_structure_version_id = :org_hierarchy'||
1587 ' union all'||
1588 ' select 1 from per_all_assignments_f paf' ||
1589 ' where paf.person_id = ppf.person_id' ||
1590 ' and paf.assignment_type <> ''C''' ||
1591 ' and paf.primary_flag = ''Y''' ||
1592 ' and paf.business_group_id = ppf.business_group_id' ||
1593 ' and :org_heirarchy_date' ||
1594 ' between paf.effective_start_date' ||
1595 ' and paf.effective_end_date' ||
1596 ' and paf.organization_id = :org_starting_node_id'||
1597 ' and paf.assignment_type = ''E'''||
1598 ' )';
1599 ELSE
1600 -- bind variables
1601 l_org_heirarchy_date := hr_api.g_sot;
1602 l_org_heirarchy := -1;
1603 l_org_starting_node_id := -1;
1604 l_query_str :=
1605 l_query_str || ' and :org_heirarchy_date is not null'||
1606 ' and -1 = :org_hierarchy '||
1607 ' and -1 = :org_hierarchy '||
1608 ' and -1 = :org_starting_node_id'||
1609 ' and -1 = :org_hierarchy ' ||
1610 ' and :org_heirarchy_date is not null'||
1611 ' and -1 = :org_starting_node_id';
1612 END IF;
1613
1614 --
1615 IF p_grade_ladder_id is NOT NULL THEN
1616 -- bind variables
1617 l_grade_ladder_bind := p_grade_ladder_id;
1618 l_grade_ladder_date_bind := p_effective_date;
1619 l_grade_ladder_date2_bind := p_effective_date;
1620 l_grade_ladder_date3_bind := p_effective_date;
1621 -- bug 3171229
1622 l_grade_ladder_date4_bind := p_effective_date;
1623
1624 -- check if the grade ladder specified is the default grade ladder
1625 open c_dflt_grade_ladder;
1626 fetch c_dflt_grade_ladder into l_def_flag, l_dflt_grade_ldr_id;
1627 close c_dflt_grade_ladder;
1628
1629 if l_def_flag ='Y' THEN
1630 l_query_str :=
1631 l_query_str ||
1632 ' and exists'||
1633 ' (select 1 from per_all_assignments_f paf'||
1634 ' where paf.person_id = ppf.person_id '||
1635 -- ' and paf.assignment_type <> ''C''' ||
1636 ' and paf.assignment_type = ''E''' || /* Bug 7307975 Assumption is that GSP is run only for Employees */
1637 ' and paf.primary_flag = ''Y''' ||
1638 -- ' and paf.business_group_id = ppf.business_group_id' || /* Bug 7307975 */
1639 ' and (paf.grade_ladder_pgm_id = :grade_ladder_id'||
1640 ' or (paf.grade_ladder_pgm_id is null'||
1641 ' and paf.grade_id in'||
1642 ' ( select pln.mapping_table_pk_id'||
1643 ' from ben_pl_f pln'||
1644 ' , ben_pgm_f pgm'||
1645 ' , ben_plip_f plip'||
1646 ' where pln.pl_id = plip.pl_id'||
1647 ' and plip.pgm_id = pgm.pgm_id'||
1648 ' and pgm.dflt_pgm_flag = ''Y'''||
1649 -- ' and pgm.pgm_prvds_no_dflt_enrt_flag = ''Y'''|| /* Bug 4030438 */
1650 ' and :grade_ladder_id_date between pln.effective_start_date and pln.effective_end_date'||
1651 ' and :grade_ladder_id_date2 between pgm.effective_start_date and pgm.effective_end_date'||
1652 ' and :grade_ladder_id_date3 between plip.effective_start_date and plip.effective_end_date)))'||
1653 ' and :grade_ladder_id_date4 between paf.effective_start_date and paf.effective_end_date)'
1654 ;
1655 else
1656 -- bug 3171229 -- make variables date2,3,4 as sot
1657 -- let l_grade_ladder_date_bind be effective date
1658
1659 l_grade_ladder_date2_bind := hr_api.g_sot;
1660 l_grade_ladder_date3_bind := hr_api.g_sot;
1661 l_grade_ladder_date4_bind := hr_api.g_sot;
1662
1663
1664 -- bug 3171229 -- changed the bind variable order in the sql
1665 l_query_str :=
1666 l_query_str || ' and exists('||
1667 ' select 1 from per_all_assignments_f paf' ||
1668 ' where paf.person_id = ppf.person_id' ||
1669 -- ' and paf.assignment_type <> ''C''' ||
1670 ' and paf.assignment_type = ''E''' || /* Bug 7307975 Assumption is that GSP is run only for Employees */
1671 ' and paf.primary_flag = ''Y''' ||
1672 -- ' and paf.business_group_id = ppf.business_group_id' || /* Bug 7307975 */
1673 ' and paf.grade_ladder_pgm_id = :grade_ladder_id'||
1674 ' and :grade_ladder_id_date between paf.effective_start_date and paf.effective_end_date)'||
1675 ' and :grade_ladder_id_date2 is not null'||
1676 ' and :grade_ladder_id_date3 is not null'||
1677 ' and :grade_ladder_id_date4 is not null';
1678 end if;
1679 ELSE
1680 /* GSP Rate Sync
1681 * Here we need to check that if operation code = SYNC, then only those persons should
1682 * be selected, whose grade ladder allows salary updates. Remember that LOV for Grade Ladder
1683 * in GSP Rate Sync conc prog would bring up only those grade ladders which allow salary updates
1684 */
1685 if p_mode = 'G' and p_lf_evt_oper_cd = 'SYNC'
1686 then
1687 --
1688 l_grade_ladder_bind := -1;
1689 l_grade_ladder_date_bind := p_effective_date; /* GSP Rate Sync */
1690 l_grade_ladder_date2_bind := p_effective_date;
1691 l_grade_ladder_date3_bind := hr_api.g_sot;
1692 l_grade_ladder_date4_bind := hr_api.g_sot;
1693
1694 /* Bug 4030438
1695 -- For GSP Rate Sync, check EITHER
1696 -- (a) the person is assigned to Grade Ladder that allows salary updates
1697 -- OR (b) the business group has default grade ladder which allows salary updates
1698 */
1699 l_query_str :=
1700 l_query_str || ' and -1 =:grade_ladder_id'||
1701 ' and exists'||
1702 ' (select 1 from per_all_assignments_f paf '||
1703 ' where paf.person_id = ppf.person_id '||
1704 ' and paf.assignment_type <> ''C''' ||
1705 ' and paf.primary_flag = ''Y''' ||
1706 ' and :grade_ladder_id_date2 between paf.effective_start_date and paf.effective_end_date ' ||
1707 ' and exists ' ||
1708 ' ( select 1 from ben_pgm_f pgm ' ||
1709 ' where ' ||
1710 ' ( pgm.pgm_id = paf.grade_ladder_pgm_id OR ' ||
1711 ' pgm.dflt_pgm_flag = ''Y'' ' ||
1712 ' ) ' ||
1713 ' and :grade_ladder_id_date between pgm.effective_start_date and pgm.effective_end_date ' ||
1714 ' and pgm.update_salary_cd <> ''NO_UPDATE'' ' ||
1715 ' and pgm.business_group_id = paf.business_group_id ' ||
1716 ' ) ' ||
1717 ' ) ' ||
1718 ' and :grade_ladder_id_date3 is not null'||
1719 ' and :grade_ladder_id_date4 is not null';
1720
1721 else
1722 --
1723 l_grade_ladder_bind := -1;
1724 l_grade_ladder_date_bind := hr_api.g_sot;
1725 l_grade_ladder_date2_bind := hr_api.g_sot;
1726 l_grade_ladder_date3_bind := hr_api.g_sot;
1727 l_grade_ladder_date4_bind := hr_api.g_sot;
1728
1729 l_query_str :=
1730 l_query_str || ' and -1 =:grade_ladder_id'||
1731 ' and :grade_ladder_id_date is not null'||
1732 ' and :grade_ladder_id_date2 is not null'||
1733 ' and :grade_ladder_id_date3 is not null'||
1734 ' and :grade_ladder_id_date4 is not null';
1735 --
1736 end if;
1737 /* GSP Rate Sync */
1738 --
1739 END IF; -- p_grade_ladder_id is NOT NULL
1740
1741 IF p_qual_type IS NOT NULL THEN
1742
1743 -- bind variables
1744 l_qual_type_bind := p_qual_type;
1745 l_qual_status_bind := p_qual_status;
1746
1747 l_query_str :=
1748 l_query_str || ' and exists'||
1749 ' (select 1'||
1750 ' from per_qualifications pq'||
1751 ' where ppf.person_id = pq.person_id'||
1752 ' and pq.qualification_type_id = :qual_type'||
1753 ' and nvl(pq.status,''xxx'' ) = nvl(nvl(:qual_status, pq.status), ''xxx''))';
1754 ELSE
1755 l_qual_type_bind := -1 ;
1756 l_qual_status_bind := -1 ;
1757
1758 l_query_str :=
1759 l_query_str || ' and -1 = :qual_type'||
1760 ' and -1 = :qual_status';
1761 END IF;
1762
1763 IF p_rate_id IS NOT NULL THEN
1764
1765 --bind variables
1766 l_rate_id_bind := p_rate_id ;
1767 l_rate_id_date_bind := p_effective_date;
1768
1769 l_query_str :=
1770 l_query_str || ' and exists'||
1771 ' (select 1'||
1772 ' from per_spinal_point_placements_f spp'||
1773 ' , per_all_assignments_f paf'||
1774 ' where paf.person_id = ppf.person_id'||
1775 ' and :effective_date between paf.effective_start_date and paf.effective_end_date'||
1776 ' and paf.assignment_type <> ''C''' ||
1777 ' and paf.primary_flag = ''Y''' ||
1778 ' and paf.business_group_id = ppf.business_group_id' ||
1779 ' and paf.assignment_id = spp.assignment_id'||
1780 ' and spp.parent_spine_id = :rate_id'||
1781 ' and :effective_date between spp.effective_start_date and spp.effective_end_date)';
1782 ELSE
1783 l_rate_id_bind := -1 ;
1784 l_rate_id_date_bind := hr_api.g_sot;
1785 l_query_str :=
1786 l_query_str || ' and :effective_date is not null'||
1787 ' and -1 = :rate_id'||
1788 ' and :effective_date is not null';
1789 END IF;
1790
1791 IF p_per_sel_dt_cd IS NOT NULL THEN
1792
1793 -- bind variables
1794
1795 l_per_sel_dt_from := p_per_sel_dt_from ;
1796 l_per_sel_dt_to := p_per_sel_dt_to ;
1797 --l_per_sel_freq_cd := p_per_sel_freq_cd ;
1798 l_year_from := p_year_from;
1799 l_year_to := p_year_to;
1800
1801 IF p_per_sel_freq_cd = 'M' THEN
1802 l_freq := 1;
1803 ELSE
1804 l_freq := 12;
1805 END IF;
1806
1807
1808 IF p_per_sel_dt_cd = 'AOJ' THEN
1809
1810
1811
1812 l_query_str :=
1813 l_query_str || ' and('||
1814 ' add_months( ppf.original_date_of_hire, '||p_year_from||' *'|| l_freq||' )'||
1815 ' between '''||to_char(p_per_sel_dt_from, 'DD-MON-YYYY')||''' and '''||to_char(p_per_sel_dt_to, 'DD-MON-YYYY') ||''' or'||
1816 ' add_months( ppf.original_date_of_hire, '||p_year_to||' * '|| l_freq||' )'||
1817 ' between '''||to_char(p_per_sel_dt_from, 'DD-MON-YYYY')||''' and '''||to_char(p_per_sel_dt_to, 'DD-MON-YYYY') ||''' or'||
1818 ' months_between ('''||to_char(p_per_sel_dt_from, 'DD-MON-YYYY')||''', ppf.original_date_of_hire )/ '|| l_freq ||
1819 ' between nvl('||p_year_from||',floor( months_between ('''||to_char(p_per_sel_dt_from, 'DD-MON-YYYY')||''', ppf.original_date_of_hire )/ '|| l_freq||')) and nvl('||p_year_to||',floor( months_between ('''||
1820 to_char(p_per_sel_dt_to, 'DD-MON-YYYY')||''', ppf.original_date_of_hire )/ '||l_freq||')) or'||
1821 ' months_between ('''||to_char(p_per_sel_dt_to, 'DD-MON-YYYY')||''', ppf.original_date_of_hire )/ '||l_freq||
1822 ' between nvl('||p_year_from||',floor( months_between ('''||to_char(p_per_sel_dt_from, 'DD-MON-YYYY')||''', ppf.original_date_of_hire )/ '|| l_freq||')) and nvl('||p_year_to||',floor( months_between ('''||
1823 to_char(p_per_sel_dt_to, 'DD-MON-YYYY')||''', ppf.original_date_of_hire )/ '||l_freq||')))'
1824 ;
1825
1826 ELSIF p_per_sel_dt_cd = 'DOB' THEN
1827
1828
1829 l_query_str :=
1830 l_query_str || ' and('||
1831 ' add_months( ppf.date_of_birth, '||p_year_from||' *'|| l_freq||' )'||
1832 ' between '''||to_char(p_per_sel_dt_from, 'DD-MON-YYYY')||''' and '''||to_char(p_per_sel_dt_to, 'DD-MON-YYYY') ||''' or'||
1833 ' add_months( ppf.date_of_birth, '||p_year_to||' * '|| l_freq||' )'||
1834 ' between '''||to_char(p_per_sel_dt_from, 'DD-MON-YYYY')||''' and '''||to_char(p_per_sel_dt_to, 'DD-MON-YYYY') ||''' or'||
1835 ' months_between ('''||to_char(p_per_sel_dt_from, 'DD-MON-YYYY')||''', ppf.date_of_birth )/ '|| l_freq ||
1836 ' between nvl('||p_year_from||',floor( months_between ('''||to_char(p_per_sel_dt_from, 'DD-MON-YYYY')||''', ppf.date_of_birth )/ '|| l_freq||')) and nvl('||p_year_to||',floor( months_between ('''||
1837 to_char(p_per_sel_dt_to, 'DD-MON-YYYY')||''', ppf.date_of_birth )/ '||l_freq||')) or'||
1838 ' months_between ('''||to_char(p_per_sel_dt_to, 'DD-MON-YYYY')||''', ppf.date_of_birth )/ '||l_freq||
1839 ' between nvl('||p_year_from||',floor( months_between ('''||to_char(p_per_sel_dt_from, 'DD-MON-YYYY')||''', ppf.date_of_birth )/ '|| l_freq||')) and nvl('||p_year_to||',floor( months_between ('''||
1840 to_char(p_per_sel_dt_to, 'DD-MON-YYYY')||''', ppf.date_of_birth )/ '||l_freq||')))'
1841 ;
1842
1843 ELSIF p_per_sel_dt_cd = 'ASD' THEN
1844
1845 l_query_str :=
1846 l_query_str || ' and exists('||
1847 ' select 1 from per_periods_of_service pos'||
1848 ' where pos.person_id = ppf.person_id'||
1849 ' and ('||
1850 ' add_months( pos.adjusted_svc_date, '||p_year_from||' *'|| l_freq||' )'||
1851 ' between '''||to_char(p_per_sel_dt_from, 'DD-MON-YYYY')||''' and '''||to_char(p_per_sel_dt_to, 'DD-MON-YYYY') ||''' or'||
1852 ' add_months( pos.adjusted_svc_date, '||p_year_to||' * '|| l_freq||' )'||
1853 ' between '''||to_char(p_per_sel_dt_from, 'DD-MON-YYYY')||''' and '''||to_char(p_per_sel_dt_to, 'DD-MON-YYYY') ||''' or'||
1854 ' months_between ('''||to_char(p_per_sel_dt_from, 'DD-MON-YYYY')||''', pos.adjusted_svc_date )/ '|| l_freq ||
1855 ' between nvl('||p_year_from||',floor( months_between ('''||to_char(p_per_sel_dt_from, 'DD-MON-YYYY')||''', pos.adjusted_svc_date )/ '|| l_freq||')) and nvl('||p_year_to||',floor( months_between ('''||
1856 to_char(p_per_sel_dt_to, 'DD-MON-YYYY')||''', pos.adjusted_svc_date )/ '||l_freq||')) or'||
1857 ' months_between ('''||to_char(p_per_sel_dt_to, 'DD-MON-YYYY')||''', pos.adjusted_svc_date )/ '||l_freq||
1858 ' between nvl('||p_year_from||',floor( months_between ('''||to_char(p_per_sel_dt_from, 'DD-MON-YYYY')||''', pos.adjusted_svc_date )/ '|| l_freq||')) and nvl('||p_year_to||',floor( months_between ('''||
1859 to_char(p_per_sel_dt_to, 'DD-MON-YYYY')||''', pos.adjusted_svc_date )/ '||l_freq||'))))'
1860 ;
1861
1862 ELSIF p_per_sel_dt_cd = 'LHD' THEN
1863
1864 l_query_str :=
1865 l_query_str || ' and exists('||
1866 ' select 1 from per_periods_of_service pos'||
1867 ' where pos.person_id = ppf.person_id'||
1868 ' and ('||
1869 ' add_months( pos.date_start, '||p_year_from||' *'|| l_freq||' )'||
1870 ' between '''||to_char(p_per_sel_dt_from, 'DD-MON-YYYY')||''' and '''||to_char(p_per_sel_dt_to, 'DD-MON-YYYY') ||''' or'||
1871 ' add_months( pos.date_start, '||p_year_to||' * '|| l_freq||' )'||
1872 ' between '''||to_char(p_per_sel_dt_from, 'DD-MON-YYYY')||''' and '''||to_char(p_per_sel_dt_to, 'DD-MON-YYYY') ||''' or'||
1873 ' months_between ('''||to_char(p_per_sel_dt_from, 'DD-MON-YYYY')||''', pos.date_start )/ '|| l_freq ||
1874 ' between nvl('||p_year_from||',floor( months_between ('''||to_char(p_per_sel_dt_from, 'DD-MON-YYYY')||''', pos.date_start )/ '|| l_freq||')) and nvl('||p_year_to||',floor( months_between ('''||
1875 to_char(p_per_sel_dt_to, 'DD-MON-YYYY')||''', pos.date_start )/ '||l_freq||')) or'||
1876 ' months_between ('''||to_char(p_per_sel_dt_to, 'DD-MON-YYYY')||''', pos.date_start )/ '||l_freq||
1877 ' between nvl('||p_year_from||',floor( months_between ('''||to_char(p_per_sel_dt_from, 'DD-MON-YYYY')||''', pos.date_start )/ '|| l_freq||')) and nvl('||p_year_to||',floor( months_between ('''||
1878 to_char(p_per_sel_dt_to, 'DD-MON-YYYY')||''', pos.date_start )/ '||l_freq||'))))'
1879 ;
1880
1881 END IF; -- p_per_sel_dt_cd
1882
1883 END IF;
1884
1885 IF p_concat_segs IS NOT NULL THEN
1886 -- if a people group is specified, frame the query accordingly
1887 l_concat_segs_date := p_effective_date;
1888
1889 OPEN c_delimiter;
1890 FETCH c_delimiter INTO l_concat_sep;
1891 CLOSE c_delimiter;
1892 -- append the common portion of the query
1893 l_query_str :=
1894 l_query_str || ' and exists ('||
1895 ' select 1 from pay_people_groups ppg'||
1896 ' , per_all_assignments_f paf'||
1897 ' where paf.people_group_id = ppg.people_group_id'||
1898 ' and paf.person_id = ppf.person_id'||
1899 ' and :effective_date between paf.effective_start_date and paf.effective_end_date'||
1900 ' and paf.assignment_type <> ''C''' ||
1901 ' and paf.primary_flag = ''Y''' /*||
1902 ' and paf.business_group_id = ppf.business_group_id' */
1903 ;
1904
1905 FOR l_application_column_name in c_application_column_name
1906 LOOP
1907 l_count := l_count + 1;
1908 l_pos_sep := instr (p_concat_segs, l_concat_sep, 1, l_count);
1909 --
1910 if (l_pos_sep = 0) then -- the search failed (end of string)
1911 l_seg_value := rtrim (substr (p_concat_segs, l_pos));
1912 else
1913 l_length := l_pos_sep - l_pos;
1914 l_seg_value := substr (p_concat_segs, l_pos, l_length);
1915 end if;
1916 l_pos := l_pos + l_length + 1; -- skip on to next segment
1917
1918 -- if the value entered by the user is null
1919 -- dont filter by that segment
1920 -- hence dont add the condition to the query
1921
1922 IF l_seg_value IS NOT NULL THEN
1923 l_query_str :=
1924 l_query_str || ' and ppg.'||l_application_column_name.application_column_name||
1925 ' = '''||l_seg_value||'''';
1926 END IF;
1927
1928 END LOOP;
1929
1930 l_query_str := l_query_str || ')' ;
1931
1932 ELSE
1933 l_concat_segs_date := hr_api.g_sot;
1934
1935 l_query_str :=
1936 l_query_str || ' and :effective_date is not null';
1937
1938 END IF; -- p_concat_segs is not null
1939
1940 --end if;
1941
1942 ---
1943
1944 -- Finish the main query
1945 l_person_date_bind := p_effective_date;
1946 --
1947 IF p_lmt_prpnip_by_org_flag = 'N' THEN
1948 --
1949 l_query_str :=
1950 l_query_str || ' and :people_date' || ' between ppf.effective_start_date' ||
1951 ' and ppf.effective_end_date' ||
1952 ' and :people_date is not null ' ||
1953 ' order by ppf.full_name';
1954 --
1955 else
1956 --
1957 l_query_str :=
1958 l_query_str || ' and :people_date' || ' between ppf.effective_start_date' ||
1959 ' and ppf.effective_end_date' ||
1960 ' and :people_date between paf1.effective_start_date(+)' ||
1961 ' and paf1.effective_end_date(+)' ||
1962 ' order by paf1.organization_id';
1963 --
1964 end if;
1965
1966
1967 --
1968 -- Open the query using business_group_id as the bind variable.
1969 --
1970
1971 /* Uncomment the following lines when you debug the dynamic sql
1972 text
1973 for i in 1..60 loop
1974 hr_utility.set_location(substr(l_query_str, 60*i-59, 60), 1234);
1975 if (60*i-59 > length(l_query_str)) then
1976 exit;
1977 end if;
1978 end loop;
1979 */
1980 OPEN c_person_life FOR l_query_str
1981 USING p_business_group_id
1982 ,l_person_date_bind
1983 ,l_person_id_bind
1984 ,l_ler_id_bind
1985 ,l_person_type_id_bind
1986 ,l_person_type_id_date_bind -- added for Bug 2224299
1987 ,l_benfts_grp_id_bind
1988 ,l_location_id_date_bind
1989 ,l_location_id_bind
1990 ,l_cagr_id_date_bind
1991 ,l_cagr_id_bind
1992 ,l_person_date_bind
1993 ,l_person_date_bind-- -- irec
1994 ,l_assignment_id_bind -- irec
1995 ,l_person_date_bind-- --irec
1996 ,l_legal_entity_id_date_bind
1997 ,l_legal_entity_id_bind
1998 ,l_payroll_id_date_bind
1999 ,l_payroll_id_bind
2000 ,l_payroll_id_date2_bind
2001 ,l_pstl_zip_rng_id_date_bind
2002 ,l_pstl_zip_rng_id_date2_bind
2003 ,l_pstl_zip_rng_id_date3_bind
2004 ,l_pstl_zip_rng_id_bind
2005 ,l_pstl_zip_rng_id_date4_bind
2006 ,l_organization_id_date_bind
2007 ,l_organization_id_bind
2008 ,l_organization_id_date2_bind
2009 ,l_organization_id_date3_bind
2010 ,l_life_date_bind
2011 -- 2940151
2012 ,l_org_heirarchy_date
2013 ,l_org_heirarchy
2014 ,l_org_heirarchy
2015 ,l_org_starting_node_id
2016 ,l_org_heirarchy
2017 ,l_org_heirarchy_date
2018 ,l_org_starting_node_id
2019 ,l_grade_ladder_bind
2020 ,l_grade_ladder_date_bind
2021 ,l_grade_ladder_date2_bind
2022 ,l_grade_ladder_date3_bind
2023 ,l_grade_ladder_date4_bind
2024 ,l_qual_type_bind
2025 ,l_qual_status_bind
2026 ,l_rate_id_date_bind
2027 ,l_rate_id_bind
2028 ,l_rate_id_date_bind
2029 , l_concat_segs_date
2030
2031 -- end 2940151
2032 ,l_person_date_bind
2033 ,l_person_date_bind;
2034 -- ------------------------------------------------------------
2035 -- NOTE: for some reason the following FETCH statement fails
2036 -- at runtime with an invalid cursor type error:
2037 -- FETCH c_person_life BULK COLLECT INTO l_person_id_table;
2038 -- To get around this problem, each row is selected
2039 -- individually (how disappointing).
2040 -- ------------------------------------------------------------
2041 LOOP
2042 FETCH c_person_life INTO l_person_id_fetch;
2043
2044
2045 EXIT WHEN c_person_life%NOTFOUND;
2046
2047 -- Only process if person passed selection rule or there is no rule.
2048 IF check_selection_rule(p_person_selection_rule_id=> p_person_selection_rule_id
2049 ,p_person_id => l_person_id_fetch
2050 ,p_business_group_id => p_business_group_id
2051 ,p_effective_date => p_effective_date)
2052 or p_mode='I' -- irec
2053 THEN
2054 --
2055 l_person_id_process(l_person_id_process.COUNT + 1) :=
2056 l_person_id_fetch;
2057 END IF;
2058 END LOOP;
2059
2060 CLOSE c_person_life;
2061 IF l_person_id_process.COUNT > 0 THEN
2062 -- bulk insert all person action(s)
2063 FORALL l_count IN l_person_id_process.FIRST .. l_person_id_process.LAST
2064 INSERT INTO ben_person_actions
2065 (
2066 person_action_id
2067 ,person_id
2068 ,ler_id
2069 ,benefit_action_id
2070 ,action_status_cd
2071 ,object_version_number)
2072 VALUES(
2073 ben_person_actions_s.nextval
2074 ,l_person_id_process(l_count)
2075 ,NULL
2076 ,p_benefit_action_id
2077 ,'U'
2078 ,1)
2079 RETURNING person_action_id BULK COLLECT INTO l_person_action_id_table;
2080 --
2081 IF MOD(l_person_action_id_table.COUNT
2082 ,p_chunk_size) = 0 THEN
2083 l_to_chunk_loop :=
2084 TRUNC(l_person_action_id_table.COUNT / p_chunk_size);
2085 ELSE
2086 l_to_chunk_loop :=
2087 TRUNC(l_person_action_id_table.COUNT / p_chunk_size) + 1;
2088 END IF;
2089 --
2090 FOR i IN 1 .. l_to_chunk_loop LOOP
2091 -- set the starting point range
2092 l_start_person_action_id :=
2093 l_person_action_id_table(((i - 1) * p_chunk_size) + 1);
2094 IF i <> l_to_chunk_loop THEN
2095 l_end_person_action_id :=
2096 l_person_action_id_table(i * p_chunk_size);
2097 ELSE
2098 l_end_person_action_id :=
2099 l_person_action_id_table(l_person_action_id_table.COUNT);
2100 END IF;
2101 --
2102 INSERT INTO ben_batch_ranges
2103 (
2104 range_id
2105 ,benefit_action_id
2106 ,range_status_cd
2107 ,starting_person_action_id
2108 ,ending_person_action_id
2109 ,object_version_number)
2110 VALUES(
2111 ben_batch_ranges_s.nextval
2112 ,p_benefit_action_id
2113 ,'U'
2114 ,l_start_person_action_id
2115 ,l_end_person_action_id
2116 ,1);
2117 --
2118 END LOOP;
2119 IF p_commit_data = 'Y' THEN
2120 -- This is for What IF Functionality
2121 COMMIT;
2122 END IF;
2123 END IF;
2124 --
2125 p_num_ranges := l_to_chunk_loop;
2126 p_num_persons := l_person_action_id_table.COUNT;
2127 --
2128 END create_life_person_actions;
2129 --
2130 PROCEDURE create_restart_person_actions(
2131 p_benefit_action_id IN NUMBER
2132 ,p_effective_date IN DATE
2133 ,p_chunk_size IN NUMBER
2134 ,p_threads IN NUMBER
2135 ,p_num_ranges OUT NOCOPY NUMBER
2136 ,p_num_persons OUT NOCOPY NUMBER
2137 ,p_commit_data IN VARCHAR2) IS
2138 --
2139 CURSOR c_person_actions IS
2140 SELECT act.person_action_id, act.person_id, act.benefit_action_id
2141 FROM ben_person_actions act
2142 WHERE act.action_status_cd = 'E'
2143 AND act.benefit_action_id = p_benefit_action_id
2144 FOR UPDATE;
2145 --
2146 CURSOR c_batch_ranges IS
2147 SELECT brng.range_id
2148 FROM ben_batch_ranges brng
2149 WHERE brng.benefit_action_id = p_benefit_action_id
2150 AND brng.range_status_cd <> 'U'
2151 AND EXISTS (SELECT null
2152 FROM ben_person_actions act
2153 WHERE act.person_action_id between brng.starting_person_action_id and brng.ending_person_action_id
2154 AND act.benefit_action_id = brng.benefit_action_id
2155 AND act.action_status_cd <> 'P')
2156 FOR UPDATE;
2157 --
2158 l_package VARCHAR2(80) := g_package || '.create_restart_person_actions';
2159 l_to_chunk_loop NUMBER := 0;
2160 --
2161 BEGIN
2162 --
2163 -- hr_utility.set_location('Entering '||l_package,10);
2164 --
2165 -- Updating ranges from ben_batch_ranges table
2166 -- Rolling back ranges with erred or partially unfinished person actions
2167 --
2168 update ben_benefit_actions
2169 set request_id = fnd_global.conc_request_id
2170 where benefit_action_id = p_benefit_action_id;
2171 --
2172 for r_batch_ranges in c_batch_ranges loop
2173 update ben_batch_ranges
2174 set range_status_cd = 'U'
2175 where range_id = r_batch_ranges.range_id;
2176 l_to_chunk_loop:=l_to_chunk_loop+1;
2177 end loop;
2178 --
2179 p_num_ranges := l_to_chunk_loop;
2180 --
2181 -- Updating person actions from ben_person_actions table
2182 -- Rolling back erred person_actions
2183 --
2184 for r_person_actions in c_person_actions loop
2185 update ben_person_actions
2186 set action_status_cd = 'U'
2187 where person_action_id = r_person_actions.person_action_id;
2188 --
2189 delete from ben_reporting
2190 where person_id = r_person_actions.person_id
2191 and benefit_action_id = r_person_actions.benefit_action_id;
2192 end loop;
2193 --
2194 select count(*)
2195 into p_num_persons
2196 from ben_person_actions
2197 where benefit_action_id = p_benefit_action_id
2198 and action_status_cd = 'U';
2199 --
2200 if p_commit_data = 'Y' then
2201 -- This is for What IF Functionality
2202 commit;
2203 end if;
2204 --
2205 -- hr_utility.set_location('Leaving '||l_package,10);
2206 --
2207 END create_restart_person_actions;
2208 --
2209 END ben_benbatch_persons;