0
0
EXISTS (SELECT 1 FROM PER_ALL_ASSIGNMENTS_M ASG,PER_PERIODS_OF_SERVICE PS
WHERE ASG.ASSIGNMENT_TYPE IN('E','C','N','P') AND
ASG.EFFECTIVE_LATEST_CHANGE='Y' AND SYSDATE BETWEEN
LEAST(SYSDATE,ASG.EFFECTIVE_START_DATE) AND ASG.EFFECTIVE_END_DATE AND
ASG.ASSIGNMENT_ID=&TABLE_ALIAS.ASSIGNMENT_ID AND
PS.PERIOD_OF_SERVICE_ID=ASG.PERIOD_OF_SERVICE_ID AND
(ASG.ASSIGNMENT_STATUS_TYPE IN ('ACTIVE','SUSPENDED') OR
(ASG.ASSIGNMENT_STATUS_TYPE IN ('INACTIVE') AND NOT EXISTS (SELECT 1 FROM
PER_ALL_ASSIGNMENTS_M EXASG WHERE EXASG.ASSIGNMENT_TYPE IN('E','C','N','P')
AND EXASG.EFFECTIVE_LATEST_CHANGE = 'Y' AND EXASG.PERSON_ID = ASG.PERSON_ID
AND SYSDATE BETWEEN LEAST(SYSDATE,EXASG.EFFECTIVE_START_DATE) AND
EXASG.EFFECTIVE_END_DATE AND EXASG.ASSIGNMENT_STATUS_TYPE IN
('ACTIVE','SUSPENDED')) AND PS.ACTUAL_TERMINATION_DATE = (SELECT
MAX(ALLPS.ACTUAL_TERMINATION_DATE) FROM PER_PERIODS_OF_SERVICE ALLPS WHERE
ALLPS.PERSON_ID = ASG.PERSON_ID AND ALLPS.ACTUAL_TERMINATION_DATE IS NOT
NULL))))
I hope this blog post was helpful for you. If you have any questions or feedback, please leave a comment below.