[Home] [Help]
98: ,max(pil.person_id)
99: ,max(pil.per_in_ler_id)
100: FROM ben_cwb_person_info per
101: ,ben_per_in_ler pil
102: ,ben_cwb_group_hrchy hrchy
103: ,hr_all_organization_units bg
104: WHERE per.group_pl_id = v_group_pl_id
105: AND per.lf_evt_ocrd_dt = v_lf_evt_orcd_date
106: AND pil.per_in_ler_id = per.group_per_in_ler_id
113: where person_rates.group_per_in_ler_id = pil.per_in_ler_id
114: and person_rates.group_pl_id = pil.group_pl_id
115: AND person_rates.elig_flag = 'Y')
116: AND (hrchy.lvl_num = (SELECT MAX (lvl_num)
117: FROM ben_cwb_group_hrchy hr
118: WHERE hr.emp_per_in_ler_id = hrchy.emp_per_in_ler_id)))
119: OR
120: ((v_target_pop = 'EPO')
121: AND EXISTS( select * from ben_cwb_person_rates person_rates
123: and person_rates.group_pl_id = pil.group_pl_id
124: and ((person_rates.pay_proposal_id is not null)
125: or (person_rates.element_entry_value_id is not null)))
126: AND (hrchy.lvl_num = (SELECT MAX (lvl_num)
127: FROM ben_cwb_group_hrchy hr
128: WHERE hr.emp_per_in_ler_id = hrchy.emp_per_in_ler_id)))
129: OR
130: ((v_target_pop = 'MAN')
131: AND (hrchy.lvl_num = 0))
175: ((v_target_pop = 'MFX')
176: AND (hrchy.lvl_num = 0)
177: AND EXISTS (select ler.per_in_ler_stat_cd
178: from ben_per_in_ler ler
179: ,ben_cwb_group_hrchy hier
180: where hier.mgr_per_in_ler_id = pil.per_in_ler_id
181: and ler.per_in_ler_id = hier.emp_per_in_ler_id
182: and ler.per_in_ler_stat_cd = 'PROCD'))
183: OR
184: ((v_target_pop = 'MEL')
185: AND (hrchy.lvl_num = 0)
186: AND EXISTS (select rts.elig_flag
187: from ben_cwb_person_rates rts
188: ,ben_cwb_group_hrchy hier
189: where hier.mgr_per_in_ler_id = hrchy.mgr_per_in_ler_id
190: and rts.group_per_in_ler_id = hier.emp_per_in_ler_id
191: and rts.elig_flag = 'Y'
192: and hier.lvl_num <> 0))
202: )
203: OR
204: ((v_target_pop IS NULL)
205: AND (hrchy.lvl_num = (SELECT MAX (lvl_num)
206: FROM ben_cwb_group_hrchy hr
207: WHERE hr.emp_per_in_ler_id = hrchy.emp_per_in_ler_id)))
208: )
209: AND (((v_req_acc_lvl IS NOT NULL)
210: AND
211: (
212: ((v_req_acc_lvl = 'AL')
213: AND EXISTS (select gr.access_cd
214: from ben_cwb_person_groups gr
215: , ben_cwb_group_hrchy hr
216: where hr.emp_per_in_ler_id = pil.per_in_ler_id
217: and hr.lvl_num = 0
218: and gr.group_per_in_ler_id = hr.emp_per_in_ler_id
219: and gr.group_pl_id = pil.group_pl_id
222: OR
223: ((v_req_acc_lvl = 'FR')
224: AND EXISTS (select gr.access_cd
225: from ben_cwb_person_groups gr
226: , ben_cwb_group_hrchy hr
227: where hr.emp_per_in_ler_id = pil.per_in_ler_id
228: and hr.lvl_num = 0
229: and gr.group_per_in_ler_id = pil.per_in_ler_id
230: and gr.group_pl_id = pil.group_pl_id
233: OR
234: ((v_req_acc_lvl = 'FU')
235: AND EXISTS (select gr.access_cd
236: from ben_cwb_person_groups gr
237: , ben_cwb_group_hrchy hr
238: where hr.emp_per_in_ler_id = pil.per_in_ler_id
239: and hr.lvl_num = 0
240: and gr.group_per_in_ler_id = hrchy.emp_per_in_ler_id
241: and gr.group_pl_id = pil.group_pl_id
244: OR
245: ((v_req_acc_lvl = 'NA')
246: AND EXISTS (select gr.access_cd
247: from ben_cwb_person_groups gr
248: , ben_cwb_group_hrchy hr
249: where hr.emp_per_in_ler_id = pil.per_in_ler_id
250: and hr.lvl_num = 0
251: and gr.group_per_in_ler_id = hrchy.emp_per_in_ler_id
252: and gr.group_pl_id = pil.group_pl_id
255: OR
256: ((v_req_acc_lvl = 'RO')
257: AND EXISTS (select gr.access_cd
258: from ben_cwb_person_groups gr
259: , ben_cwb_group_hrchy hr
260: where hr.emp_per_in_ler_id = pil.per_in_ler_id
261: and hr.lvl_num = 0
262: and gr.group_per_in_ler_id = hrchy.emp_per_in_ler_id
263: and gr.group_pl_id = pil.group_pl_id