1 package body ben_determine_communications1 as
2 /*$Header: bentmpc1.pkb 120.0 2007/11/23 13:03:24 sallumwa noship $*/
3 --
4 /*
5 History
6 Version Date Author Comment
7 -------+----------+----------+---------------------------------------------------
8 115.0 19-Dec-06 mhoyes Created. Bug5598664.
9 115.2 22-Dec-06 mhoyes Bug5598664 - Added in mod restriction on pep
10 restriction.
11 115.3 27-Dec-06 mhoyes Bug5598664 - Removed mod restriction on pep.
12 115.4 08-Mar-07 mhoyes Bug5919794 - Removed person or condition.
13 ---------------------------------------------------------------------------------
14 */
15 --
16 g_package varchar2(80) := 'ben_determine_communications1';
17 --
18 procedure get_mssmlg_perids
19 (p_per_id number
20 ,p_effdt date
21 ,p_bgp_id number
22 ,p_pet_id number
23 ,p_elig_enrol_cd varchar2
24 ,p_pgm_id number
25 ,p_pl_nip_id number
26 ,p_plan_in_pgm_flag varchar2
27 ,p_org_id number
28 ,p_loc_id number
29 --
30 ,p_perid_va out nocopy benutils.g_number_table
31 )
32 is
33 --
34 l_perid_va benutils.g_number_table := benutils.g_number_table();
35 l_modperid_va benutils.g_number_table := benutils.g_number_table();
36 --
37 cursor c_per_mssmlgmodallper
38 (c_effdt date
39 ,c_bgp_id number
40 ,c_pet_id number
41 ,c_elig_enrol_cd varchar2
42 ,c_pgm_id number
43 ,c_pl_nip_id number
44 ,c_plan_in_pgm_flag varchar2
45 ,c_org_id number
46 ,c_loc_id number
47 ,c_work_id number
48 ,c_workers number
49 )
50 is
51 select /*+ bentmpc1.c_per_mssmlgmodallper 20 */
52 person_id
53 from per_all_people_f ppf
54 where c_effdt between ppf.effective_start_date
55 and ppf.effective_end_date
56 and mod(ppf.person_id,c_workers) = c_work_id
57 and ppf.business_group_id = c_bgp_id
58 and ppf.business_group_id = c_bgp_id
59 and (c_pet_id is null
60 or
61 ppf.person_id in (select ppu.person_id
62 -- exists (select null
63 from per_person_type_usages_f ppu
64 where
65 -- ppf.person_id = ppu.person_id
66 -- and
67 ppu.person_type_id = c_pet_id
68 and c_effdt
69 between ppu.effective_start_date and ppu.effective_end_date
70 )
71 )
72 --
73 -- The elig_enrol_cd could be either NULL or ELIG or ENROL
74 --
75 and (
76 (c_elig_enrol_cd = 'ELIG'
77 and
78 ppf.person_id in (select elig.person_id
79 -- exists (select 's'
80 from ben_elig_per_f elig,
81 ben_per_in_ler pil
82 -- where elig.person_id = ppf.person_id
83 -- and
84 where
85 (c_pgm_id is null or
86 elig.pgm_id = c_pgm_id
87 )
88 and ((c_pl_nip_id is null
89 and c_plan_in_pgm_flag = 'Y'
90 and elig.pgm_id is not null
91 )
92 or
93 (c_pl_nip_id is null
94 and c_plan_in_pgm_flag = 'N'
95 and elig.pgm_id is null
96 )
97 or
98 (c_pl_nip_id = elig.pl_id)
99 )
100 and elig.elig_flag = 'Y'
101 and c_effdt
102 between elig.effective_start_date and elig.effective_end_date
103 and pil.per_in_ler_id(+)=elig.per_in_ler_id
104 and pil.business_group_id(+)=elig.business_group_id
105 and (pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
106 or pil.per_in_ler_stat_cd is null
107 )
108 )
109 )
110 or
111 (c_elig_enrol_cd = 'ENROL'
112 and
113 ppf.person_id in (select pen.person_id
114 -- exists (select 's'
115 from ben_prtt_enrt_rslt_f pen
116 where
117 -- pen.person_id = ppf.person_id
118 -- and
119 (c_pgm_id is null or
120 pen.pgm_id = c_pgm_id
121 )
122 and ((c_pl_nip_id is null
123 and c_plan_in_pgm_flag = 'Y'
124 and pen.pgm_id is not null
125 )
126 or
127 (c_pl_nip_id is null
128 and c_plan_in_pgm_flag = 'N'
129 and pen.pgm_id is null
130 )
131 or
132 (c_pl_nip_id = pen.pl_id)
133 )
134 and pen.sspndd_flag = 'N'
135 and pen.prtt_enrt_rslt_stat_cd is null
136 and pen.business_group_id = c_bgp_id
137 and nvl(pen.enrt_cvg_thru_dt, hr_api.g_eot) = hr_api.g_eot
138 and c_effdt
139 between pen.effective_start_date and pen.effective_end_date
140 and pen.effective_end_date = hr_api.g_eot
141 )
142 )
143 or
144 (c_elig_enrol_cd is null)
145 )
146 and ((c_org_id is null and
147 c_loc_id is null
148 )
149 or
150 ppf.person_id in (select asg.person_id
151 -- exists (select 's'
152 FROM per_all_assignments_f asg,
153 per_assignment_status_types ast
154 WHERE
155 -- asg.person_id = ppf.person_id
156 -- AND
157 asg.primary_flag = 'Y'
158 and (c_org_id is null
159 or asg.organization_id = c_org_id
160 )
161 and (c_loc_id is null
162 or asg.location_id = c_loc_id
163 )
164 AND c_effdt
165 BETWEEN asg.effective_start_date AND asg.effective_end_date
166 AND asg.assignment_status_type_id = ast.assignment_status_type_id
167 and asg.business_group_id = c_bgp_id
168 AND ((assignment_type = 'E'
169 AND (ast.per_system_status = 'ACTIVE_ASSIGN'
170 OR (ast.per_system_status = 'TERM_ASSIGN'
171 AND NOT EXISTS (SELECT assignment_id
172 FROM per_all_assignments_f asg1,
173 per_assignment_status_types ast1
174 WHERE asg1.assignment_type = 'B'
175 AND asg1.primary_flag = 'Y'
176 AND asg1.person_id = ppf.person_id
177 AND asg1.assignment_status_type_id = ast1.assignment_status_type_id
178 AND ast1.per_system_status = 'ACTIVE_ASSIGN'
179 AND c_effdt
180 BETWEEN asg1.effective_start_date AND asg1.effective_end_date
181 )
182 )
183 )
184 )
185 OR
186 (assignment_type = 'B'
187 AND NOT EXISTS (SELECT assignment_id
188 FROM per_all_assignments_f asg2,
189 per_assignment_status_types ast2
190 WHERE asg2.assignment_type = 'E'
191 AND asg2.primary_flag = 'Y'
192 AND asg2.person_id = ppf.person_id
193 AND asg2.assignment_status_type_id = ast2.assignment_status_type_id
194 AND ast2.per_system_status = 'ACTIVE_ASSIGN'
195 AND c_effdt
196 BETWEEN asg2.effective_start_date AND asg2.effective_end_date
197 )
198 )
199 )
200 )
201 );
202 --
203 cursor c_per_mssmlgmodoneper
204 (c_per_id number
205 ,c_effdt date
206 ,c_bgp_id number
207 ,c_pet_id number
208 ,c_elig_enrol_cd varchar2
209 ,c_pgm_id number
210 ,c_pl_nip_id number
211 ,c_plan_in_pgm_flag varchar2
212 ,c_org_id number
213 ,c_loc_id number
214 ,c_work_id number
215 ,c_workers number
216 )
217 is
218 select /*+ bentmpc1.c_per_mssmlgmodoneper 20 */
219 person_id
220 from per_all_people_f ppf
221 where ppf.person_id = c_per_id
222 and c_effdt between ppf.effective_start_date
223 and ppf.effective_end_date
224 and mod(ppf.person_id,c_workers) = c_work_id
225 and ppf.business_group_id = c_bgp_id
226 and ppf.business_group_id = c_bgp_id
227 and (c_pet_id is null
228 or
229 ppf.person_id in (select ppu.person_id
230 -- exists (select null
231 from per_person_type_usages_f ppu
232 where
233 -- ppf.person_id = ppu.person_id
234 -- and
235 ppu.person_type_id = c_pet_id
236 and c_effdt
237 between ppu.effective_start_date and ppu.effective_end_date
238 )
239 )
240 --
241 -- The elig_enrol_cd could be either NULL or ELIG or ENROL
242 --
243 and (
244 (c_elig_enrol_cd = 'ELIG'
245 and
246 ppf.person_id in (select elig.person_id
247 -- exists (select 's'
248 from ben_elig_per_f elig,
249 ben_per_in_ler pil
250 -- where elig.person_id = ppf.person_id
251 -- and
252 where
253 (c_pgm_id is null or
254 elig.pgm_id = c_pgm_id
255 )
256 and ((c_pl_nip_id is null
257 and c_plan_in_pgm_flag = 'Y'
258 and elig.pgm_id is not null
259 )
260 or
261 (c_pl_nip_id is null
262 and c_plan_in_pgm_flag = 'N'
263 and elig.pgm_id is null
264 )
265 or
266 (c_pl_nip_id = elig.pl_id)
267 )
268 and elig.elig_flag = 'Y'
269 and c_effdt
270 between elig.effective_start_date and elig.effective_end_date
271 and pil.per_in_ler_id(+)=elig.per_in_ler_id
272 and pil.business_group_id(+)=elig.business_group_id
273 and (pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
274 or pil.per_in_ler_stat_cd is null
275 )
276 )
277 )
278 or
279 (c_elig_enrol_cd = 'ENROL'
280 and
281 ppf.person_id in (select pen.person_id
282 -- exists (select 's'
283 from ben_prtt_enrt_rslt_f pen
284 where
285 -- pen.person_id = ppf.person_id
286 -- and
287 (c_pgm_id is null or
288 pen.pgm_id = c_pgm_id
289 )
290 and ((c_pl_nip_id is null
291 and c_plan_in_pgm_flag = 'Y'
292 and pen.pgm_id is not null
293 )
294 or
295 (c_pl_nip_id is null
296 and c_plan_in_pgm_flag = 'N'
297 and pen.pgm_id is null
298 )
299 or
300 (c_pl_nip_id = pen.pl_id)
301 )
302 and pen.sspndd_flag = 'N'
303 and pen.prtt_enrt_rslt_stat_cd is null
304 and pen.business_group_id = c_bgp_id
305 and nvl(pen.enrt_cvg_thru_dt, hr_api.g_eot) = hr_api.g_eot
306 and c_effdt
307 between pen.effective_start_date and pen.effective_end_date
308 and pen.effective_end_date = hr_api.g_eot
309 )
310 )
311 or
312 (c_elig_enrol_cd is null)
313 )
314 and ((c_org_id is null and
315 c_loc_id is null
316 )
317 or
318 ppf.person_id in (select asg.person_id
319 -- exists (select 's'
320 FROM per_all_assignments_f asg,
321 per_assignment_status_types ast
322 WHERE
323 -- asg.person_id = ppf.person_id
324 -- AND
325 asg.primary_flag = 'Y'
326 and (c_org_id is null
327 or asg.organization_id = c_org_id
328 )
329 and (c_loc_id is null
330 or asg.location_id = c_loc_id
331 )
332 AND c_effdt
333 BETWEEN asg.effective_start_date AND asg.effective_end_date
334 AND asg.assignment_status_type_id = ast.assignment_status_type_id
335 and asg.business_group_id = c_bgp_id
336 AND ((assignment_type = 'E'
337 AND (ast.per_system_status = 'ACTIVE_ASSIGN'
338 OR (ast.per_system_status = 'TERM_ASSIGN'
339 AND NOT EXISTS (SELECT assignment_id
340 FROM per_all_assignments_f asg1,
341 per_assignment_status_types ast1
342 WHERE asg1.assignment_type = 'B'
343 AND asg1.primary_flag = 'Y'
344 AND asg1.person_id = ppf.person_id
345 AND asg1.assignment_status_type_id = ast1.assignment_status_type_id
346 AND ast1.per_system_status = 'ACTIVE_ASSIGN'
347 AND c_effdt
348 BETWEEN asg1.effective_start_date AND asg1.effective_end_date
349 )
350 )
351 )
352 )
353 OR
354 (assignment_type = 'B'
355 AND NOT EXISTS (SELECT assignment_id
356 FROM per_all_assignments_f asg2,
357 per_assignment_status_types ast2
358 WHERE asg2.assignment_type = 'E'
359 AND asg2.primary_flag = 'Y'
360 AND asg2.person_id = ppf.person_id
361 AND asg2.assignment_status_type_id = ast2.assignment_status_type_id
362 AND ast2.per_system_status = 'ACTIVE_ASSIGN'
363 AND c_effdt
364 BETWEEN asg2.effective_start_date AND asg2.effective_end_date
365 )
366 )
367 )
368 )
369 );
370 --
371 l_proc varchar2(80) := g_package || '.get_mssmlg_perids';
372 --
373 l_perid_en pls_integer;
374 l_mod pls_integer;
375 --
376 begin
377 --
378 hr_utility.set_location('Entering : ' || l_proc, 10);
379 --
380 l_perid_va.delete;
381 l_perid_en := 1;
382 l_mod := 20;
383 --
384 for workid in 1..l_mod
385 loop
386 --
387 l_modperid_va.delete;
388 --
389 if p_per_id is null
390 then
391 --
392 open c_per_mssmlgmodallper
393 (c_effdt => p_effdt
394 ,c_bgp_id => p_bgp_id
395 ,c_pet_id => p_pet_id
396 ,c_elig_enrol_cd => p_elig_enrol_cd
397 ,c_pgm_id => p_pgm_id
398 ,c_pl_nip_id => p_pl_nip_id
399 ,c_plan_in_pgm_flag => p_plan_in_pgm_flag
400 ,c_org_id => p_org_id
401 ,c_loc_id => p_loc_id
402 ,c_work_id => workid-1
403 ,c_workers => l_mod
404 );
405 fetch c_per_mssmlgmodallper bulk collect into l_modperid_va;
406 close c_per_mssmlgmodallper;
407 --
408 else
409 --
410 open c_per_mssmlgmodoneper
411 (c_per_id => p_per_id
412 ,c_effdt => p_effdt
413 ,c_bgp_id => p_bgp_id
414 ,c_pet_id => p_pet_id
415 ,c_elig_enrol_cd => p_elig_enrol_cd
416 ,c_pgm_id => p_pgm_id
417 ,c_pl_nip_id => p_pl_nip_id
418 ,c_plan_in_pgm_flag => p_plan_in_pgm_flag
419 ,c_org_id => p_org_id
420 ,c_loc_id => p_loc_id
421 ,c_work_id => workid-1
422 ,c_workers => l_mod
423 );
424 fetch c_per_mssmlgmodoneper bulk collect into l_modperid_va;
425 close c_per_mssmlgmodoneper;
426 --
427 end if;
428 --
429 if l_modperid_va.count > 0
430 then
431 --
432 for modvaen in l_modperid_va.first..l_modperid_va.last
433 loop
434 --
435 l_perid_va.extend(1);
436 l_perid_va(l_perid_en) := l_modperid_va(modvaen);
437 l_perid_en := l_perid_en+1;
438 --
439 end loop;
440 --
441 end if;
442 --
443 end loop;
444 --
445 p_perid_va := l_perid_va;
446 --
447 hr_utility.set_location('Leaving : ' || l_proc, 10);
448 --
449 end get_mssmlg_perids;
450 --
451 end ben_determine_communications1;
[Home] [Help]