I have a table - job

this table contains employess deatils - hire date, status, action, effdt... etc,

I have to select employees (who are consultants, who will be hired, termed, rehired, termed, rehired...) who are rehired recently, eventhough they have multiple hire/rehire records.The consultants have action = 'ADD' when they are hired or rehired.

the sql should do the following :

Select the consultants recent rehireed record by checking the max effdt for rehire(action = ADD), and that date > the max effdt for Termed(action = TER). This will avoid selecting the new rehires
who have action = ADD for the first time.

Here is the sql that I use which is not working good, but pulls all the hires and rehires with action = ADD.

SELECT a.EMPLID
,a.EMPL_RCD
,b.EFFDT
,'A'
,a.TIME_RPTR_IND
,a.ELP_TR_TMPLT_ID
,a.PCH_TR_TMPLT_ID
,a.PERIOD_ID
,b.emplid, b.action, b.action_reason, b.effdt
,decode(substr(a.emplid
,1
,1)
, 'C'
, 'FMNONEMPL'
, decode(b.flsa_status
, 'E'
, 'FMEXEMPT'
, 'N'
, decode(b.empl_type
, 'H'
, 'FMINTERN'
, 'FMNONEXMPT' )) )
,a.TASKGROUP
,a.TASK_PROFILE_ID
,a.TCD_GROUP_ID
,a.RESTRICTION_PRF_ID
,a.TL_TIME_TO_PAY
,a.RULE_ELEMENT_1
,a.RULE_ELEMENT_2
,a.RULE_ELEMENT_3
,a.RULE_ELEMENT_4
,a.RULE_ELEMENT_5
,a.TIMEZONE
FROM ps_tl_empl_data a
, ps_job b
WHERE a.emplid = b.emplid
AND a.empl_rcd = b.empl_rcd
AND b.effdt > '01-JAN-2000'
and b.effdt = (
select max(effdt) from ps_job c
where c.emplid = a.emplid
and c.effdt <= sysdate)
AND b.action = 'ADD'
AND a.empl_rcd = b.empl_rcd
AND a.effdt = (
SELECT MAX(effdt)
FROM ps_tl_empl_data
WHERE emplid = a.emplid
AND empl_rcd = a.empl_rcd
AND effdt <= sysdate)
AND NOT EXISTS (
SELECT 'X'
FROM ps_tl_empl_data o
WHERE o.emplid = b.emplid
AND o.empl_rcd = b.empl_rcd
AND o.effdt = b.effdt)

any help is welcome.