Tuesday, March 10, 2015

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





No comments:

Post a Comment