| NEW_CT |
0 |
Y |
select nbrjobs_pidm pidm,
(select ptrcaln_year
from ptrcaln
where ptrcaln_pict_code = 'UI'
and base.nbrjobs_effective_date between ptrcaln_start_date and ptrcaln_end_date) pay_year,
nbrjobs_effective_date start_date,
nbrjobs_effective_date + 30 end_date,
null eff_date
from nbrjobs base
where 1=1
and nbrjobs_effective_date > sysdate - 14
and nbrjobs_status = 'A'
and uilib.ecls.is_ba_hourly(nbrjobs_ecls_code) = 'Y'
and not exists (select * from table(uilib.jobs.pidm(base.nbrjobs_pidm, base.nbrjobs_effective_date - 2))
where status <> 'T'
and uilib.ecls.is_ba_hourly(ecls) = 'Y')
and not exists (select 'Y' from nwrelct
where nwrelct_pidm = base.nbrjobs_pidm
and nwrelct_window_open = base.nbrjobs_effective_date)
order by nbrjobs_effective_Date desc |
W:ANORRIS |
09-APR-2018 |
| ANN_CT |
0 |
N |
select nbrjobs_pidm
from nbrjobs base
where nbrjobs_status <> 'T'
and nbrjobs_effective_date = (
select max(nbrjobs_effective_date)
from nbrjobs j2
where j2.nbrjobs_pidm = base.nbrjobs_pidm
and j2.nbrjobs_posn = base.nbrjobs_posn
and j2.nbrjobs_suff = base.nbrjobs_suff
and j2.nbrjobs_effective_date <= sysdate)
and uilib.ecls.is_ba_hourly(nbrjobs_ecls_code) = 'Y'
-- 15-Jan-2015 CJE Exclude people with simultaneous classified and non-classified jobs
and not exists (
select 'Y' from nbrjobs sub
where nbrjobs_status <> 'T'
and sub.nbrjobs_pidm = base.nbrjobs_pidm
and sub.nbrjobs_posn <> base.nbrjobs_posn
and nbrjobs_effective_date = (
select max(nbrjobs_effective_date)
from nbrjobs
where nbrjobs_pidm = sub.nbrjobs_pidm
and nbrjobs_posn = sub.nbrjobs_posn
and nbrjobs_suff = sub.nbrjobs_suff
and nbrjobs_effective_date <= sysdate)
and uilib.ecls.is_ba_hourly(nbrjobs_ecls_code) = 'N') |
W:ANORRIS |
09-APR-2018 |
| DEF_PAY |
30 |
N |
select nbrjobs_pidm
from nbrjobs base
where nbrjobs_status <> 'T'
and nbrjobs_effective_date = (
select max(nbrjobs_effective_date)
from nbrjobs j2
where j2.nbrjobs_pidm = base.nbrjobs_pidm
and j2.nbrjobs_posn = base.nbrjobs_posn
and j2.nbrjobs_suff = base.nbrjobs_suff
and j2.nbrjobs_effective_date <= case
when (extract (month from sysdate)) <= 6 then to_date('1-Jul-' || extract(year from sysdate))
else to_date('1-Jul-' || (extract(year from sysdate) + 1))
end
)
and nbrjobs_dfpr_code is not null -- they have a deferred pay code
union -- classified employees on "spread pay"
select nbrjobs_pidm
from nbrjobs JOBS
where nbrjobs_status <> 'T'
and nbrjobs_effective_date = (
select max (JOBS2.nbrjobs_effective_date)
from nbrjobs JOBS2
where nbrjobs_pidm = JOBS.nbrjobs_pidm
and nbrjobs_posn = JOBS.nbrjobs_posn
and nbrjobs_suff = JOBS.nbrjobs_suff
and nbrjobs_effective_date <= case
when (extract (month from sysdate)) <= 6 then to_date('1-Jul-' || extract(year from sysdate))
else to_date('1-Jul-' || (extract(year from sysdate) + 1))
end
)
and uilib.ecls.is_ba(nbrjobs_ecls_code) = 'Y'
and nbrjobs_fte < 1
and nbrjobs_hrs_pay = 80
and nbrjobs_pays in (26, 27) -- 27 to account for one pay year every 11 years that has 27 pays |
W:DEREKJ |
06-APR-2020 |
