Script to delete Absences between 2 dates

Script to delete Absences between 2 dates 

set log xyz.log;

set output xyz.dat;

--Export from Target

EXPORT PS_EOAW_USERINST WHERE EOAWSTEP_INSTANCE IN 

(SELECT EOAWSTEP_INSTANCE FROM PS_EOAW_STEPINST  WHERE EOAWTHREAD_ID IN 

(SELECT EOAWTHREAD_ID FROM PS_GP_ABSSS_V_XREF WHERE BGN_DT BETWEEN %datein('yyyy-mm-dd') AND %Datein('yyyy-mm-dd') AND PIN_TAKE_NUM IN 

(SELECT PIN_NUM FROM PS_GP_PIN WHERE COUNTRY = 'COUNTRY')));

EXPORT PS_EOAW_STEPINST  WHERE EOAWTHREAD_ID IN 

(SELECT EOAWTHREAD_ID FROM PS_GP_ABSSS_V_XREF WHERE BGN_DT BETWEEN %datein('yyyy-mm-dd') AND %Datein('yyyy-mm-dd') AND PIN_TAKE_NUM IN 

(SELECT PIN_NUM FROM PS_GP_PIN WHERE COUNTRY = 'COUNTRY'));

EXPORT PS_GP_ABS_SS_STA WHERE BGN_DT BETWEEN  %datein('yyyy-mm-dd') AND %Datein('yyyy-mm-dd') AND PIN_TAKE_NUM IN 

(SELECT PIN_NUM FROM PS_GP_PIN WHERE COUNTRY = 'COUNTRY');

EXPORT PS_GP_ABS_EVENT WHERE BGN_DT BETWEEN %datein('yyyy-mm-dd') AND %Datein('yyyy-mm-dd') AND PIN_TAKE_NUM IN 

(SELECT PIN_NUM FROM PS_GP_PIN WHERE COUNTRY = 'COUNTRY');

EXPORT  PS_GP_ABSSS_V_XREF WHERE BGN_DT BETWEEN %datein('yyyy-mm-dd') AND %Datein('yyyy-mm-dd') AND PIN_TAKE_NUM IN 

(SELECT PIN_NUM FROM PS_GP_PIN WHERE COUNTRY = 'COUNTRY');

EXPORT PS_GP_ABS_SS_DAT WHERE BGN_DT BETWEEN %datein('yyyy-mm-dd') AND %Datein('yyyy-mm-dd') AND PIN_TAKE_NUM IN 

(SELECT PIN_NUM FROM PS_GP_PIN WHERE COUNTRY = 'COUNTRY');

EXPORT PS_GP_RSLT_ABS WHERE ABSENCE_DATE BETWEEN %datein('yyyy-mm-dd') AND %Datein('yyyy-mm-dd') AND GP_PAYGROUP LIKE 'GP_PAYGROUP%';

EXPORT PS_GP_PI_GEN_DATA WHERE GP_PAYGROUP LIKE 'GP_PAYGROUP%'  AND ABS_PRD_BGN_DT BETWEEN %datein('yyyy-mm-dd') AND %Datein('yyyy-mm-dd') 

AND PIN_NUM IN 

(SELECT PIN_NUM FROM PS_GP_PIN WHERE COUNTRY = 'COUNTRY');

--Execute in Target

DELETE FROM PS_EOAW_USERINST WHERE EOAWSTEP_INSTANCE IN 

(SELECT EOAWSTEP_INSTANCE FROM PS_EOAW_STEPINST  WHERE EOAWTHREAD_ID IN 

(SELECT EOAWTHREAD_ID FROM PS_GP_ABSSS_V_XREF WHERE BGN_DT BETWEEN %datein('yyyy-mm-dd') AND %Datein('yyyy-mm-dd') AND PIN_TAKE_NUM IN 

(SELECT PIN_NUM FROM PS_GP_PIN WHERE COUNTRY = 'COUNTRY')));

DELETE FROM PS_EOAW_STEPINST  WHERE EOAWTHREAD_ID IN 

(SELECT EOAWTHREAD_ID FROM PS_GP_ABSSS_V_XREF WHERE BGN_DT BETWEEN %datein('yyyy-mm-dd') AND %Datein('yyyy-mm-dd') AND PIN_TAKE_NUM IN 

(SELECT PIN_NUM FROM PS_GP_PIN WHERE COUNTRY = 'COUNTRY'));

DELETE FROM PS_GP_ABS_SS_STA WHERE BGN_DT BETWEEN  %datein('yyyy-mm-dd') AND %Datein('yyyy-mm-dd') AND PIN_TAKE_NUM IN 

(SELECT PIN_NUM FROM PS_GP_PIN WHERE COUNTRY = 'COUNTRY');

DELETE FROM PS_GP_ABS_EVENT WHERE BGN_DT BETWEEN  %datein('yyyy-mm-dd') AND %Datein('yyyy-mm-dd') AND PIN_TAKE_NUM IN 

(SELECT PIN_NUM FROM PS_GP_PIN WHERE COUNTRY = 'COUNTRY');

DELETE FROM PS_GP_ABSSS_V_XREF WHERE BGN_DT BETWEEN %datein('yyyy-mm-dd') AND %Datein('yyyy-mm-dd') AND PIN_TAKE_NUM IN 

(SELECT PIN_NUM FROM PS_GP_PIN WHERE COUNTRY = 'COUNTRY');

DELETE FROM PS_GP_ABS_SS_DAT WHERE BGN_DT BETWEEN %datein('yyyy-mm-dd') AND %Datein('yyyy-mm-dd') AND PIN_TAKE_NUM IN 

(SELECT PIN_NUM FROM PS_GP_PIN WHERE COUNTRY = 'COUNTRY');

DELETE FROM PS_GP_RSLT_ABS WHERE ABSENCE_DATE BETWEEN %datein('yyyy-mm-dd') AND %Datein('yyyy-mm-dd') AND GP_PAYGROUP LIKE 'GP_PAYGROUP%';

DELETE FROM PS_GP_PI_GEN_DATA WHERE GP_PAYGROUP LIKE 'GP_PAYGROUP%'  AND ABS_PRD_BGN_DT BETWEEN %datein('yyyy-mm-dd') AND %Datein('yyyy-mm-dd') 

AND PIN_NUM IN 

(SELECT PIN_NUM FROM PS_GP_PIN WHERE COUNTRY = 'COUNTRY');

======================

--Export from Target

EXPORT PS_GP_ABS_SS_STA WHERE 

(BGN_DT BETWEEN  TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD')

OR

END_DT BETWEEN  TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD'))

 AND PIN_TAKE_NUM IN 

(SELECT PIN_NUM FROM PS_GP_PIN WHERE COUNTRY = 'COUNTRY') AND EMPLID IN 

(SELECT ABS.EMPLID FROM PS_GP_ABS_EVENT ABS, PS_GP_PIN PIN , ps_tl_dates_tbl TL , PS_JOB JOB, PS_PERSONAL_DATA PD

where 

(BGN_DT BETWEEN TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD')

OR

END_DT BETWEEN  TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD'))

 AND PIN.PIN_NUM = ABS.PIN_TAKE_NUM AND TL.THE_DATE BETWEEN ABS.BGN_DT AND ABS.END_DT AND JOB.EMPLID = ABS.EMPLID

AND JOB.EMPLID = PD.EMPLID AND job.effdt = 

( SELECT MAX(effdt)   FROM PS_JOB J  WHERE emplid = job.emplid AND empl_rcd = job.empl_rcd AND EFFDT <= TL.THE_DATE ) 

AND job.effseq = 

(  SELECT MAX(effseq) FROM ps_job WHERE emplid = job.emplid AND empl_rcd = job.empl_rcd AND EFFDT = job.EFFDT ) AND job.EMPL_STATUS = 'A'  

);

EXPORT PS_GP_ABS_EVENT WHERE 

(BGN_DT BETWEEN  TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD')

OR

END_DT BETWEEN  TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD'))

 AND PIN_TAKE_NUM IN 

(SELECT PIN_NUM FROM PS_GP_PIN WHERE COUNTRY = 'COUNTRY') AND EMPLID IN 

(SELECT ABS.EMPLID FROM PS_GP_ABS_EVENT ABS, PS_GP_PIN PIN , ps_tl_dates_tbl TL , PS_JOB JOB, PS_PERSONAL_DATA PD

where 

(BGN_DT BETWEEN TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD')

OR

END_DT BETWEEN  TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD'))

 AND PIN.PIN_NUM = ABS.PIN_TAKE_NUM AND TL.THE_DATE BETWEEN ABS.BGN_DT AND ABS.END_DT AND JOB.EMPLID = ABS.EMPLID

AND JOB.EMPLID = PD.EMPLID AND job.effdt = 

( SELECT MAX(effdt)   FROM PS_JOB J  WHERE emplid = job.emplid AND empl_rcd = job.empl_rcd AND EFFDT <= TL.THE_DATE ) 

AND job.effseq = 

(  SELECT MAX(effseq) FROM ps_job WHERE emplid = job.emplid AND empl_rcd = job.empl_rcd AND EFFDT = job.EFFDT ) AND job.EMPL_STATUS = 'A'  

);

EXPORT PS_GP_ABSSS_V_XREF WHERE 

(BGN_DT BETWEEN TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD')

OR

END_DT BETWEEN  TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD'))

 AND PIN_TAKE_NUM IN 

(SELECT PIN_NUM FROM PS_GP_PIN WHERE COUNTRY = 'COUNTRY') AND EMPLID IN 

(SELECT ABS.EMPLID FROM PS_GP_ABS_EVENT ABS, PS_GP_PIN PIN , ps_tl_dates_tbl TL , PS_JOB JOB, PS_PERSONAL_DATA PD

where 

(BGN_DT BETWEEN TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD')

OR

END_DT BETWEEN  TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD'))

 AND PIN.PIN_NUM = ABS.PIN_TAKE_NUM AND TL.THE_DATE BETWEEN ABS.BGN_DT AND ABS.END_DT AND JOB.EMPLID = ABS.EMPLID

AND JOB.EMPLID = PD.EMPLID AND job.effdt = 

( SELECT MAX(effdt)   FROM PS_JOB J  WHERE emplid = job.emplid    AND empl_rcd = job.empl_rcd AND EFFDT <= TL.THE_DATE ) 

AND job.effseq = 

(  SELECT MAX(effseq)   FROM ps_job  WHERE emplid = job.emplid    AND empl_rcd = job.empl_rcd    AND EFFDT = job.EFFDT ) AND job.EMPL_STATUS = 'A'  

);

EXPORT PS_GP_ABS_SS_DAT WHERE 

(BGN_DT BETWEEN TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD')

OR

END_DT BETWEEN  TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD'))

 AND PIN_TAKE_NUM IN 

(SELECT PIN_NUM FROM PS_GP_PIN WHERE COUNTRY = 'COUNTRY') AND EMPLID IN 

(SELECT ABS.EMPLID FROM PS_GP_ABS_EVENT ABS, PS_GP_PIN PIN , ps_tl_dates_tbl TL , PS_JOB JOB, PS_PERSONAL_DATA PD

where 

(BGN_DT BETWEEN TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD')

OR

END_DT BETWEEN  TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD'))

 AND PIN.PIN_NUM = ABS.PIN_TAKE_NUM AND TL.THE_DATE BETWEEN ABS.BGN_DT AND ABS.END_DT AND JOB.EMPLID = ABS.EMPLID

AND JOB.EMPLID = PD.EMPLID AND job.effdt = 

( SELECT MAX(effdt)   FROM PS_JOB J  WHERE emplid = job.emplid    AND empl_rcd = job.empl_rcd AND EFFDT <= TL.THE_DATE ) 

AND job.effseq = 

(  SELECT MAX(effseq)   FROM ps_job  WHERE emplid = job.emplid    AND empl_rcd = job.empl_rcd    AND EFFDT = job.EFFDT ) AND job.EMPL_STATUS = 'A'  

);

--===========

EXPORT PS_GP_RSLT_ABS WHERE ABSENCE_DATE BETWEEN TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD')

 AND GP_PAYGROUP LIKE 'GP_PAYGROUP%'AND EMPLID IN 

(SELECT ABS.EMPLID FROM PS_GP_ABS_EVENT ABS, PS_GP_PIN PIN , ps_tl_dates_tbl TL , PS_JOB JOB, PS_PERSONAL_DATA PD

where 

(BGN_DT BETWEEN TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD')

OR

END_DT BETWEEN  TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD'))

 AND PIN.PIN_NUM = ABS.PIN_TAKE_NUM AND TL.THE_DATE BETWEEN ABS.BGN_DT AND ABS.END_DT AND JOB.EMPLID = ABS.EMPLID

AND JOB.EMPLID = PD.EMPLID AND job.effdt = 

( SELECT MAX(effdt)   FROM PS_JOB J  WHERE emplid = job.emplid    AND empl_rcd = job.empl_rcd AND EFFDT <= TL.THE_DATE ) 

AND job.effseq = 

(  SELECT MAX(effseq)   FROM ps_job  WHERE emplid = job.emplid    AND empl_rcd = job.empl_rcd    AND EFFDT = job.EFFDT ) AND job.EMPL_STATUS = 'A'  

);

EXPORT PS_GP_PI_GEN_DATA WHERE GP_PAYGROUP LIKE 'GP_PAYGROUP%'

AND (ABS_PRD_BGN_DT BETWEEN TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD')

 OR ABS_PRD_END_DT BETWEEN  TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD'))

 AND PIN_NUM IN 

(SELECT PIN_NUM FROM PS_GP_PIN WHERE COUNTRY = 'COUNTRY')

AND EMPLID IN 

(SELECT ABS.EMPLID FROM PS_GP_ABS_EVENT ABS, PS_GP_PIN PIN , ps_tl_dates_tbl TL , PS_JOB JOB, PS_PERSONAL_DATA PD

where 

(BGN_DT BETWEEN TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD')

OR

END_DT BETWEEN  TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD'))

 AND PIN.PIN_NUM = ABS.PIN_TAKE_NUM AND TL.THE_DATE BETWEEN ABS.BGN_DT AND ABS.END_DT AND JOB.EMPLID = ABS.EMPLID

AND JOB.EMPLID = PD.EMPLID AND job.effdt = 

( SELECT MAX(effdt)   FROM PS_JOB J  WHERE emplid = job.emplid    AND empl_rcd = job.empl_rcd AND EFFDT <= TL.THE_DATE ) 

AND job.effseq = 

(  SELECT MAX(effseq)   FROM ps_job  WHERE emplid = job.emplid    AND empl_rcd = job.empl_rcd    AND EFFDT = job.EFFDT ) AND job.EMPL_STATUS = 'A'  

);

--Execute in Target

DELETE FROM PS_GP_ABS_SS_STA WHERE 

(BGN_DT BETWEEN  TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD')

OR

END_DT BETWEEN  TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD'))

 AND PIN_TAKE_NUM IN 

(SELECT PIN_NUM FROM PS_GP_PIN WHERE COUNTRY = 'COUNTRY') AND EMPLID IN 

(SELECT ABS.EMPLID FROM PS_GP_ABS_EVENT ABS, PS_GP_PIN PIN , ps_tl_dates_tbl TL , PS_JOB JOB, PS_PERSONAL_DATA PD

where 

(BGN_DT BETWEEN TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD')

OR

END_DT BETWEEN  TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD'))

 AND PIN.PIN_NUM = ABS.PIN_TAKE_NUM AND TL.THE_DATE BETWEEN ABS.BGN_DT AND ABS.END_DT AND JOB.EMPLID = ABS.EMPLID

AND JOB.EMPLID = PD.EMPLID AND job.effdt = 

( SELECT MAX(effdt)   FROM PS_JOB J  WHERE emplid = job.emplid AND empl_rcd = job.empl_rcd AND EFFDT <= TL.THE_DATE ) 

AND job.effseq = 

(  SELECT MAX(effseq) FROM ps_job WHERE emplid = job.emplid AND empl_rcd = job.empl_rcd AND EFFDT = job.EFFDT ) AND job.EMPL_STATUS = 'A'  

);

DELETE FROM PS_GP_ABS_EVENT WHERE 

(BGN_DT BETWEEN  TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD')

OR

END_DT BETWEEN  TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD'))

 AND PIN_TAKE_NUM IN 

(SELECT PIN_NUM FROM PS_GP_PIN WHERE COUNTRY = 'COUNTRY') AND EMPLID IN 

(SELECT ABS.EMPLID FROM PS_GP_ABS_EVENT ABS, PS_GP_PIN PIN , ps_tl_dates_tbl TL , PS_JOB JOB, PS_PERSONAL_DATA PD

where 

(BGN_DT BETWEEN TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD')

OR

END_DT BETWEEN  TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD'))

 AND PIN.PIN_NUM = ABS.PIN_TAKE_NUM AND TL.THE_DATE BETWEEN ABS.BGN_DT AND ABS.END_DT AND JOB.EMPLID = ABS.EMPLID

AND JOB.EMPLID = PD.EMPLID AND job.effdt = 

( SELECT MAX(effdt)   FROM PS_JOB J  WHERE emplid = job.emplid AND empl_rcd = job.empl_rcd AND EFFDT <= TL.THE_DATE ) 

AND job.effseq = 

(  SELECT MAX(effseq) FROM ps_job WHERE emplid = job.emplid AND empl_rcd = job.empl_rcd AND EFFDT = job.EFFDT ) AND job.EMPL_STATUS = 'A'  

);

DELETE FROM PS_GP_ABSSS_V_XREF WHERE 

(BGN_DT BETWEEN TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD')

OR

END_DT BETWEEN  TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD'))

 AND PIN_TAKE_NUM IN 

(SELECT PIN_NUM FROM PS_GP_PIN WHERE COUNTRY = 'COUNTRY') AND EMPLID IN 

(SELECT ABS.EMPLID FROM PS_GP_ABS_EVENT ABS, PS_GP_PIN PIN , ps_tl_dates_tbl TL , PS_JOB JOB, PS_PERSONAL_DATA PD

where 

(BGN_DT BETWEEN TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD')

OR

END_DT BETWEEN  TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD'))

 AND PIN.PIN_NUM = ABS.PIN_TAKE_NUM AND TL.THE_DATE BETWEEN ABS.BGN_DT AND ABS.END_DT AND JOB.EMPLID = ABS.EMPLID

AND JOB.EMPLID = PD.EMPLID AND job.effdt = 

( SELECT MAX(effdt)   FROM PS_JOB J  WHERE emplid = job.emplid    AND empl_rcd = job.empl_rcd AND EFFDT <= TL.THE_DATE ) 

AND job.effseq = 

(  SELECT MAX(effseq)   FROM ps_job  WHERE emplid = job.emplid    AND empl_rcd = job.empl_rcd    AND EFFDT = job.EFFDT ) AND job.EMPL_STATUS = 'A'  

);

DELETE FROM PS_GP_ABS_SS_DAT WHERE 

(BGN_DT BETWEEN TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD')

OR

END_DT BETWEEN  TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD'))

 AND PIN_TAKE_NUM IN 

(SELECT PIN_NUM FROM PS_GP_PIN WHERE COUNTRY = 'COUNTRY') AND EMPLID IN 

(SELECT ABS.EMPLID FROM PS_GP_ABS_EVENT ABS, PS_GP_PIN PIN , ps_tl_dates_tbl TL , PS_JOB JOB, PS_PERSONAL_DATA PD

where 

(BGN_DT BETWEEN TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD')

OR

END_DT BETWEEN  TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD'))

 AND PIN.PIN_NUM = ABS.PIN_TAKE_NUM AND TL.THE_DATE BETWEEN ABS.BGN_DT AND ABS.END_DT AND JOB.EMPLID = ABS.EMPLID

AND JOB.EMPLID = PD.EMPLID AND job.effdt = 

( SELECT MAX(effdt)   FROM PS_JOB J  WHERE emplid = job.emplid    AND empl_rcd = job.empl_rcd AND EFFDT <= TL.THE_DATE ) 

AND job.effseq = 

(  SELECT MAX(effseq)   FROM ps_job  WHERE emplid = job.emplid    AND empl_rcd = job.empl_rcd    AND EFFDT = job.EFFDT ) AND job.EMPL_STATUS = 'A'  

);

DELETE FROM PS_GP_RSLT_ABS WHERE ABSENCE_DATE BETWEEN TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD')

-- OR END_DT BETWEEN  TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD'))

 AND GP_PAYGROUP LIKE 'GP_PAYGROUP%'AND EMPLID IN 

(SELECT ABS.EMPLID FROM PS_GP_ABS_EVENT ABS, PS_GP_PIN PIN , ps_tl_dates_tbl TL , PS_JOB JOB, PS_PERSONAL_DATA PD

where 

(BGN_DT BETWEEN TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD')

OR

END_DT BETWEEN  TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD'))

 AND PIN.PIN_NUM = ABS.PIN_TAKE_NUM AND TL.THE_DATE BETWEEN ABS.BGN_DT AND ABS.END_DT AND JOB.EMPLID = ABS.EMPLID

AND JOB.EMPLID = PD.EMPLID AND job.effdt = 

( SELECT MAX(effdt)   FROM PS_JOB J  WHERE emplid = job.emplid    AND empl_rcd = job.empl_rcd AND EFFDT <= TL.THE_DATE ) 

AND job.effseq = 

(  SELECT MAX(effseq)   FROM ps_job  WHERE emplid = job.emplid    AND empl_rcd = job.empl_rcd    AND EFFDT = job.EFFDT ) AND job.EMPL_STATUS = 'A'  

);

DELETE FROM PS_GP_PI_GEN_DATA WHERE GP_PAYGROUP LIKE 'GP_PAYGROUP%'

AND (ABS_PRD_BGN_DT BETWEEN TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD')

 OR ABS_PRD_END_DT BETWEEN  TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD'))

 AND PIN_NUM IN 

(SELECT PIN_NUM FROM PS_GP_PIN WHERE COUNTRY = 'COUNTRY')

AND EMPLID IN 

(SELECT ABS.EMPLID FROM PS_GP_ABS_EVENT ABS, PS_GP_PIN PIN , ps_tl_dates_tbl TL , PS_JOB JOB, PS_PERSONAL_DATA PD

where 

(BGN_DT BETWEEN TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD')

OR

END_DT BETWEEN  TO_DATE(yyyy-mm-dd,'YYYY-MM-DD') AND TO_DATE(yyyy-mm-dd,'YYYY-MM-DD'))

 AND PIN.PIN_NUM = ABS.PIN_TAKE_NUM AND TL.THE_DATE BETWEEN ABS.BGN_DT AND ABS.END_DT AND JOB.EMPLID = ABS.EMPLID

AND JOB.EMPLID = PD.EMPLID AND job.effdt = 

( SELECT MAX(effdt)   FROM PS_JOB J  WHERE emplid = job.emplid    AND empl_rcd = job.empl_rcd AND EFFDT <= TL.THE_DATE ) 

AND job.effseq = 

(  SELECT MAX(effseq)   FROM ps_job  WHERE emplid = job.emplid    AND empl_rcd = job.empl_rcd    AND EFFDT = job.EFFDT ) AND job.EMPL_STATUS = 'A'  

);