This query selects the DATE_START from PER_PERIODS_OF_SERVICE and formats it as DD-MON-YYYY using the TO_CHAR function. It joins PER_ALL_PEOPLE_F with PER_ALL_ASSIGNMENTS_F to identify the employee’s assignment and ensures that only primary assignments with assignment types Employee (E) or Contingent Worker (C) are considered. The query filters assignments that are currently effective based on SYSDATE and ensures the record reflects the latest effective change. It also joins PER_PERIODS_OF_SERVICE to retrieve the employee’s service start date associated with the assignment. Additional joins to checklist tables are present but currently commented out. The query returns the formatted start date for the employee’s period of service.
SQL Query Section
SELECT
TO_CHAR(PPOSS3.DATE_START, 'DD-MON-YYYY')
FROM
PER_ALL_PEOPLE_F PAPFF1 ,
PER_ALL_ASSIGNMENTS_F ASG_ACTT2 ,
PER_PERIODS_OF_SERVICE PPOSS3 ,
PER_ALLOCATED_CHECKLISTS_VL PACC4 ,
PER_ALLOCATED_TASKS_VL PATVV5
WHERE
ASG_ACTT2.PERSON_ID = PAPFF1.PERSON_ID
AND UPPER(ASG_ACTT2.PRIMARY_FLAG(+)) = UPPER('Y')
AND ASG_ACTT2.ASSIGNMENT_TYPE IN ('E','C')
AND TRUNC(SYSDATE) BETWEEN NVL(ASG_ACTT2.EFFECTIVE_START_DATE(+), SYSDATE) AND NVL(ASG_ACTT2.EFFECTIVE_END_DATE(+), SYSDATE)
AND ASG_ACTT2.EFFECTIVE_LATEST_CHANGE = 'Y'
AND PPOSS3.PERSON_ID(+) = ASG_ACTT2.PERSON_ID
AND PPOSS3.PERIOD_OF_SERVICE_ID(+) = ASG_ACTT2.PERIOD_OF_SERVICE_ID
--AND TRUNC(SYSDATE) BETWEEN NVL(PAPFF1.EFFECTIVE_START_DATE, SYSDATE) AND NVL(PAPFF1.EFFECTIVE_END_DATE, SYSDATE)
--AND (PPOSS3.ACTUAL_TERMINATION_DATE(+) >= TRUNC(SYSDATE)
--OR PPOSS3.ACTUAL_TERMINATION_DATE(+) IS NULL)
--AND PAPFF1.PERSON_ID = PACC4.PERSON_ID
--AND PACC4.ALLOCATED_CHECKLIST_ID = PATVV5.ALLOCATED_CHECKLIST_ID
--AND PACC4.ALLOCATED_CHECKLIST_ID = :{PARAMETER.ALLOCATED_CHECKLIST_ID}
--AND rownum = 1 Output Results
