Tuesday, March 10, 2015

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

No comments:

Post a Comment