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