SQL Query to fetch Assignment Level Element Entries

In this article, we will fetch the Element Entries attached to assignment level

SQL Query:

SELECT DISTINCT
		PPN.FIRST_NAME ,
		PPN.LAST_NAME ,
		PPN.MIDDLE_NAMES ,
               PPL.PERSON_NUMBER ,
		HAUFT.NAME "Business Entity",
		PEE.EFFECTIVE_START_DATE "Effective Date",
		PPL.START_DATE "Hire Date",
		PPN1.FULL_NAME "Line Manager Name",
		PJF.JOB_CODE "Job Code",
		PEEVF.SCREEN_ENTRY_VALUE "Rate",
		PET.BASE_ELEMENT_NAME "Element Name"
FROM
        PER_ALL_PEOPLE_F      PPL  ,
        PAY_ELEMENT_TYPES_F   PET  ,
        PAY_ELEMENT_ENTRIES_F PEE  ,
        PAY_ENTRY_USAGES      PEU  ,
        PAY_REL_GROUPS_DN     PASG ,
        PER_ALL_ASSIGNMENTS_F PAF,
		PAY_ELEMENT_ENTRY_VALUES_F PEEVF,
		PAY_INPUT_VALUES_F PIVF,
		PER_PERSON_NAMES_F PPN,
		PER_PERSON_NAMES_F PPN1,
		PER_JOBS_F PJF,
		HR_ORGANIZATION_UNITS_F_TL HAUFT,
		PER_ASSIGNMENT_SUPERVISORS_F PASF
WHERE
        PEE.ELEMENT_TYPE_ID   = PET.ELEMENT_TYPE_ID
AND     PPL.PERSON_ID         = PEE.PERSON_ID
AND 	PET.BASE_ELEMENT_NAME IN ('<Element Names List')
AND PAF.PRIMARY_FLAG='Y'
   AND PAF.ASSIGNMENT_STATUS_TYPE= 'ACTIVE'
   AND PAF.PRIMARY_ASSIGNMENT_FLAG='Y'
  AND PAF.ASSIGNMENT_TYPE IN ('E','P','O')
   AND PIVF.BASE_NAME='Amount'
   AND PPN.NAME_TYPE='GLOBAL'
   AND PPN1.NAME_TYPE='GLOBAL'
AND     SYSDATE BETWEEN PPL.EFFECTIVE_START_DATE AND     PPL.EFFECTIVE_END_DATE
AND     SYSDATE BETWEEN PET.EFFECTIVE_START_DATE AND     PET.EFFECTIVE_END_DATE
AND     SYSDATE BETWEEN PIVF.EFFECTIVE_START_DATE AND     PIVF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN HAUFT.EFFECTIVE_START_DATE AND HAUFT.EFFECTIVE_END_DATE
AND     PEE.ELEMENT_ENTRY_ID      = PEU.ELEMENT_ENTRY_ID
AND PPN.PERSON_ID               = PEE.PERSON_ID
AND PEEVF.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID
AND PJF.JOB_ID = PAF.JOB_ID
AND HAUFT.ORGANIZATION_ID = PAF.LEGAL_ENTITY_ID
AND PIVF.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PIVF.INPUT_VALUE_ID = PEEVF.INPUT_VALUE_ID
AND     PEU.PAYROLL_ASSIGNMENT_ID = PASG.RELATIONSHIP_GROUP_ID
AND     PASG.ASSIGNMENT_ID        = PAF.ASSIGNMENT_ID
AND     PASF.ASSIGNMENT_ID        = PAF.ASSIGNMENT_ID
AND     PASF.MANAGER_ID        = PPN1.PERSON_ID
AND     PEE.EFFECTIVE_START_DATE BETWEEN PAF.EFFECTIVE_START_DATE AND     PAF.EFFECTIVE_END_DATE
AND     PEE.EFFECTIVE_START_DATE BETWEEN PASF.EFFECTIVE_START_DATE AND     PASF.EFFECTIVE_END_DATE
ORDER BY PEE.EFFECTIVE_START_DATE DESC

If you like the content, please follow us on LinkedIn, Facebook, and Twitter to get updated with the latest content.