Employees who have a certain Element Entries with no values restrictions

select distinct

--asg.BUSINESS_GROUP_ID,

--pee.CREATOR_TYPE, pee.ENTRY_TYPE,

/*asg.ASSIGNMENT_ID,

pee.ELEMENT_ENTRY_ID,

pet.ELEMENT_TYPE_ID,*/

asg.ASSIGNMENT_NUMBER EMP_NO,

--,pep.TITLE||' '||pep.FIRST_NAME||' '||pep.LAST_NAME Name

--,substr(pg.GROUP_NAME,instr(pg.GROUP_NAME,'.')+1,instr(pg.GROUP_NAME,'.',1,2) - instr(pg.GROUP_NAME,'.')-1) Housing_Status

--,grd.NAME

pet.ELEMENT_NAME

-- --,pet.PROCESSING_PRIORITY

-- --,pee.SOURCE_ASG_ACTION_ID

-- --,pee.SOURCE_ID

 ,to_char(pee.EFFECTIVE_START_DATE,'dd/mm/yyyy')  Effective_Date

--,to_char(pee.EFFECTIVE_END_DATE,'dd/mm/yyyy')  END

--,pee.LAST_UPDATE_DATE Last_Update

--,pee.CREATED_BY

--Count (1)

from pay_element_types_f pet

,pay_element_entries_f pee

,per_all_Assignments_f Asg

,PER_ASSIGNMENT_STATUS_TYPES past

,pay_element_links_f pel

,pay_people_groups pg

,HR_GRADE_LOV_V GRD

,hr_organization_units org

--,per_all_people_f pep

--,xx.xx_emp_num rta

where 1=1

 and asg.business_group_id  in (4508)

and past.ASSIGNMENT_STATUS_TYPE_ID = asg.ASSIGNMENT_STATUS_TYPE_ID

and asg.GRADE_ID = grd.GRADE_ID

--and grd.NAME not like '%DSF%'

/*and  last_day(to_date('1-1-2006','dd-mm-yyyy')) between pep.EFFECTIVE_START_DATE and pep.EFFECTIVE_END_DATE

and pep.BUSINESS_GROUP_ID = asg.BUSINESS_GROUP_ID

and pep.PERSON_ID = asg.PERSON_ID*/

--and upper(past.USER_STATUS)   like Upper('Suspend Assignment')

and pet.business_group_id = asg.business_group_id

--and Upper(pet.ELEMENT_NAME) like  upper('%AUTH LV WITHOUT PAY TAKEN%')

--and not Upper(pet.ELEMENT_NAME) like  upper('annual_leave_provision')

and pet.element_type_id = pel.element_type_id

and pel.BUSINESS_GROUP_ID = asg.BUSINESS_GROUP_ID

--and pet.CLASSIFICATION_ID in (96)    --   96  Earning      110  Deduction

and pel.element_link_id = pee.element_link_id

and pee.Assignment_id =  asg.Assignment_id

and asg.ASSIGNMENT_NUMBER in ('81')

--and pep.EMPLOYEE_NUMBER = rta.EMP_NUM

and pg.PEOPLE_GROUP_ID = asg.PEOPLE_GROUP_ID

--and org.NAME like 'Finance Department'

and asg.ORGANIZATION_ID = org.ORGANIZATION_ID

--and  upper(pg.GROUP_NAME) like upper('%%EXPAT%')

and last_day(to_date('30-9-2006','dd-mm-yyyy')) between asg.EFFECTIVE_START_DATE and asg.EFFECTIVE_END_DATE

--and pee.LAST_UPDATE_DATE >= to_date('25-4-2006','dd-mm-yyyy')

and last_day(to_date('30-10-2006','dd-mm-yyyy')) between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE

--and pee.EFFECTIVE_START_DATE between to_date('1-10-2006','dd-mm-yyyy') and last_day(to_date('1-10-2006','dd-mm-yyyy'))

and asg.PAYROLL_ID is not null

--and pee.CREATOR_TYPE = 'H'

--and pet.CREATION_DATE >= to_date('1-1-2006','dd-mm-yyyy')

--and pet.PROCESSING_TYPE = 'R'

order by effective_date desc

group by

pee.ELEMENT_ENTRY_ID,

pet.ELEMENT_TYPE_ID,

asg.ASSIGNMENT_NUMBER

,pet.ELEMENT_NAME

,pet.PROCESSING_PRIORITY

--,pee.SOURCE_ASG_ACTION_ID

--,pee.SOURCE_ID

,to_char(pee.EFFECTIVE_START_DATE,'dd/mm/yyyy') 

--order by assignment_number