Element Entry - MainDS

SELECT DISTINCT PEEF.ELEMENT_ENTRY_ID ENTRY_ID
    , PEEF.PERSON_ID PER_ID
    , PEEVF.ELEMENT_ENTRY_VALUE_ID VALUE_ID
    , PEEF.EFFECTIVE_START_DATE EFF_ST_DT
    , PEEF.EFFECTIVE_END_DATE EFF_END_DT
    , PEEVF.SCREEN_ENTRY_VALUE VAL
    , PEEF.MULTIPLE_ENTRY_COUNT CNT
    , PETF.BASE_ELEMENT_NAME ELEMENT_NAME
    , PIVF.BASE_NAME
--    , PEU.PAYROLL_RELATIONSHIP_ID
    , PRGN.ASSIGNMENT_ID ASG_ID
    , PPRD.PAYROLL_RELATIONSHIP_NUMBER PAY_REL_NUM
    , PPRD.START_DATE PAY_REL_ST_DT
    , PPRD.END_DATE PAY_REL_END_DT
FROM PAY_ELEMENT_ENTRIES_F PEEF
    , PAY_ELEMENT_TYPES_F PETF
    , PAY_INPUT_VALUES_F PIVF
    , PAY_ELEMENT_ENTRY_VALUES_F PEEVF
    , PAY_ENTRY_USAGES PEU
    , PAY_REL_GROUPS_DN PRGN
    , PAY_PAY_RELATIONSHIPS_DN PPRD
    , PER_ALL_ASSIGNMENTS_M PAAM
    , PER_PERIODS_OF_SERVICE PPOS
WHERE (PETF.BASE_ELEMENT_NAME LIKE '%401K%'
        or PETF.BASE_ELEMENT_NAME LIKE '%401k%'
        or PETF.BASE_ELEMENT_NAME in ('PR Retirement Savings','PR Retirement Savings Unmatched','PR Retirement Savings Catchup'))
    AND :AsOfDate BETWEEN PEEF.EFFECTIVE_START_DATE AND PEEF.EFFECTIVE_END_DATE
    AND PETF.ELEMENT_TYPE_ID = PEEF.ELEMENT_TYPE_ID
    AND :AsOfDate BETWEEN PETF.EFFECTIVE_START_DATE AND PETF.EFFECTIVE_END_DATE
    AND PIVF.ELEMENT_TYPE_ID = PEEF.ELEMENT_type_ID
    AND ((PIVF.BASE_NAME = 'Percentage') or (trim(PEEVF.SCREEN_ENTRY_VALUE) is not null and PIVF.BASE_NAME = 'Override IRS Limit'))
    AND :AsOfDate BETWEEN PIVF.EFFECTIVE_START_DATE AND PIVF.EFFECTIVE_END_DATE
    AND PEEVF.ELEMENT_ENTRY_ID = PEEF.ELEMENT_ENTRY_ID
    AND PEEVF.INPUT_VALUE_ID = PIVF.INPUT_VALUE_ID
    AND :AsOfDate BETWEEN PEEVF.EFFECTIVE_START_DATE AND PEEVF.EFFECTIVE_END_DATE
    AND PEU.ELEMENT_ENTRY_ID = PEEF.ELEMENT_ENTRY_ID
    AND PRGN.PAYROLL_RELATIONSHIP_ID = PEU.PAYROLL_RELATIONSHIP_ID
    AND PRGN.ASSIGNMENT_ID IS NOT NULL
    AND PPRD.PAYROLL_RELATIONSHIP_ID = PEU.PAYROLL_RELATIONSHIP_ID
    AND PAAM.ASSIGNMENT_ID = PRGN.ASSIGNMENT_ID
    AND PAAM.ASSIGNMENT_TYPE = 'E'
    AND PEEF.EFFECTIVE_START_DATE BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
    AND PPOS.PERIOD_OF_SERVICE_ID = PAAM.PERIOD_OF_SERVICE_ID
    AND PEEF.EFFECTIVE_START_DATE BETWEEN PPOS.DATE_START AND NVL(PPOS.ACTUAL_TERMINATION_DATE, TO_DATE('12314712', 'MMDDYYYY'))
/* and peef.person_id IN (100000442385831) */