SELECT pe.person_key, pe.flite_id,PE.NAME_FIRST fname,PE.NAME_LAST lname, RA.RANK_ABBRV rank,
4 as user_type,4 as access_level, 0 as is_archived, 1 as is_active,oi.office_key,
17168 as created_by, TO_CHAR(sysdate, 'MM/DD/YYYY') as created_date,
TO_CHAR(sysdate+ numtoyminterval(2, 'YEAR'), 'MM/DD/YYYY') as expiration_date,pr.email_address,
17168 as updated_by, TO_CHAR(sysdate, 'MM/DD/YYYY') as upd_date
from roster.m_person pe join roster.m_role ro on pe.PERSON_KEY = ro.person_fk
join roster.m_person_email pr on pe.PERSON_KEY = pr.person_fk
join roster.m_slot sl on sl.SLOT_KEY = ro.slot_fk
join roster.m_section se on se.SECTION_KEY = sl.SECTION_FK
join roster.m_office oi on oi.office_key = se.OFFICE_FK
join roster.m_unit un on un.unit_key = oi.unit_fk
join roster.m_location lo on lo.location_key = un.location_fk
join roster.l_ranks ra on ra.RANK_KEY = ro.ASSGND_RANK_GRADE
WHERE (RO.ASSGND_RANK_GRADE BETWEEN 1 AND 19 OR RO.ASSGND_RANK_GRADE BETWEEN 153 AND 167 )
AND RO.POSITION IN (84,92,100,122,128,129,135)
AND RO.ASSGND_AFSC IN (12,162,183,184,185)
AND RO.duty_title like 'Area Defense Counsel%'
UNION
SELECT pe.person_key, pe.flite_id,PE.NAME_FIRST fname,PE.NAME_LAST lname, RA.RANK_ABBRV rank,
5,5,0,1,oi.office_key,17168 as created_by, TO_CHAR(sysdate, 'MM/DD/YYYY') as created_date,
TO_CHAR(sysdate+ numtoyminterval(2, 'YEAR'), 'MM/DD/YYYY') as expiration_date,pr.email_address,
17168 as updated_by,TO_CHAR(sysdate, 'MM/DD/YYYY') as upd_date
from roster.m_person pe join roster.m_role ro on pe.PERSON_KEY = ro.person_fk
join roster.m_person_email pr on pe.PERSON_KEY = pr.person_fk
join roster.m_slot sl on sl.SLOT_KEY = ro.slot_fk
join roster.m_section se on se.SECTION_KEY = sl.SECTION_FK
join roster.m_office oi on oi.office_key = se.OFFICE_FK
join roster.m_unit un on un.unit_key = oi.unit_fk
join roster.m_location lo on lo.location_key = un.location_fk
join roster.l_ranks ra on ra.RANK_KEY = ro.ASSGND_RANK_GRADE
WHERE (RO.ASSGND_RANK_GRADE BETWEEN 1 AND 19 OR RO.ASSGND_RANK_GRADE BETWEEN 153 AND 167 )
AND RO.POSITION IN (84,92,100,122,128,129,135)
AND RO.ASSGND_AFSC IN (12,162,183,184,185)
AND RO.duty_title like 'Senior Defense Counsel%'
UNION
SELECT pe.person_key, pe.flite_id,PE.NAME_FIRST fname,PE.NAME_LAST lname, RA.RANK_ABBRV rank,
5,6,0,1,oi.office_key,17168 as created_by, TO_CHAR(sysdate, 'MM/DD/YYYY') as created_date,
TO_CHAR(sysdate+ numtoyminterval(2, 'YEAR'), 'MM/DD/YYYY') as expiration_date,pr.email_address,
17168 as updated_by,TO_CHAR(sysdate, 'MM/DD/YYYY') as upd_date
from roster.m_person pe join roster.m_role ro on pe.PERSON_KEY = ro.person_fk
join roster.m_person_email pr on pe.PERSON_KEY = pr.person_fk
join roster.m_slot sl on sl.SLOT_KEY = ro.slot_fk
join roster.m_section se on se.SECTION_KEY = sl.SECTION_FK
join roster.m_office oi on oi.office_key = se.OFFICE_FK
join roster.m_unit un on un.unit_key = oi.unit_fk
join roster.m_location lo on lo.location_key = un.location_fk
join roster.l_ranks ra on ra.RANK_KEY = ro.ASSGND_RANK_GRADE
WHERE (RO.ASSGND_RANK_GRADE BETWEEN 1 AND 19 OR RO.ASSGND_RANK_GRADE BETWEEN 153 AND 167 )
AND RO.POSITION IN (84,92,100,122,128,129,135)
AND RO.ASSGND_AFSC IN (13,14,15,16,122,123)
AND (RO.duty_title like '%Defense Paralegal%')