Employees who have a certain Element Entries with no values restrictions

select --Decode(asg.BUSINESS_GROUP_ID ,81,'DED',84,'DOH',120,'DCA',305,'DMD',346,'DIW',1456,'DPD',4507,'DTCM',4508,'DLD',4509,'DPD', 'New') DPT, --ppa.BUSINESS_GROUP_ID BG),

asg.ASSIGNMENT_NUMBER Emp_Num --, pee.ELEMENT_ENTRY_ID-- -, asg.ASSIGNMENT_ID, pet.ELEMENT_TYPE_ID

--,pep.ATTRIBUTE7 Emp_Num_DM

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

,pet.ELEMENT_NAME

--, pee.SOURCE_ID

--, pee.CREATOR_TYPE

--,pet.PROCESSING_PRIORITY

--,piv.NAME Inupt

,peev.SCREEN_ENTRY_VALUE Days

--Sum(peev.SCREEN_ENTRY_VALUE) Leave

from pay_element_types_f pet,

pay_input_values_f piv,

pay_element_entries_f pee,

pay_element_entry_values_f peev,

per_all_people_f pep,

per_all_Assignments_f Asg,

pay_element_links_f pel

where 1=1

and asg.business_group_id = 1456

and pet.business_group_id = asg.business_group_id

and pep.BUSINESS_GROUP_ID = asg.BUSINESS_GROUP_ID

and Upper(pet.ELEMENT_NAME) like upper('ANNUAL LEAVE BAL ADJ')  -- ('ANNUAL LEAVE BAL ADJ', 'ANNUAL LEAVE ACCRUAL RETRO')

and pet.element_type_id = pel.element_type_id

and pel.BUSINESS_GROUP_ID = asg.BUSINESS_GROUP_ID

--and pet.CLASSIFICATION_ID in (96,110)

and pel.element_link_id = pee.element_link_id

and pee.Assignment_id =  asg.Assignment_id

--and asg.ASSIGNMENT_STATUS_TYPE_ID = 2  -- 1  Active         2  Suspended        3  Terminated

and pep.PERSON_ID = asg.PERSON_ID

--and asg.ASSIGNMENT_NUMBER IN ('110045')

and to_date('1-2-2007','dd-mm-yyyy') between asg.EFFECTIVE_START_DATE and asg.EFFECTIVE_END_DATE

and sysdate between pep.EFFECTIVE_START_DATE and pep.EFFECTIVE_END_DATE

and pee.ELEMENT_ENTRY_ID = peev.ELEMENT_ENTRY_ID

and peev.INPUT_VALUE_ID = piv.INPUT_VALUE_ID

--and Upper(piv.name) like  upper('%Days')

--and piv.UOM = 'N'

and peev.SCREEN_ENTRY_VALUE is not null

and peev.SCREEN_ENTRY_VALUE != '0'

--and to_date('01-6-2006','dd-mm-yyyy') between pee.EFFECTIVE_START_DATE and pee.EFFECTIVE_END_DATE

--and pee.EFFECTIVE_START_DATE between  to_date('01-1-2000','dd-mm-yyyy')  and to_date('1-6-2006','dd-mm-yyyy')

group by asg.ASSIGNMENT_NUMBER

,pep.ATTRIBUTE7