Time and Labor Scripts

Time and Labor Scripts 

EXPORT tl_rptd_time 

 WHERE emplid = '02326475' and dur = %datein('2020-08-03') and seq_nbr = 10202; 

UPDATE PS_TL_RPTD_TIME  

   set seq_nbr = 999

 WHERE emplid = '02326475' and dur = %datein('2020-08-03') and seq_nbr = 10202;  

export TL_EXCEPTION where exception_status='U' and emplid = '02086261';

export tl_tr_status where emplid = '02086261';

delete PS_TL_EXCEPTION where exception_status='U' and emplid = '02086261';

update ps_tl_tr_status set TA_STATUS ='N' where emplid = '02086261';

Script to unfinalize an absence calendar

Script to unfinalize an absence calendar 

EXPORT PS_GP_CAL_RUN WHERE CAL_RUN_ID = 'CAL_RUN_ID'; 

EXPORT PS_GP_CAL_RUN_DTL WHERE CAL_RUN_ID = 'CAL_RUN_ID'; 

EXPORT PS_GP_PYE_SEG_STAT WHERE CAL_RUN_ID = 'CAL_RUN_ID'; 

UPDATE PS_GP_CAL_RUN SET RUN_FINALIZED_IND = 'N', RUN_FINALIZED_TS = NULL, RUN_OPEN_IND = 'Y' WHERE CAL_RUN_ID = 'CAL_RUN_ID'; 

UPDATE PS_GP_CAL_RUN_DTL SET CAL_FINAL_TS = NULL  WHERE CAL_RUN_ID = 'CAL_RUN_ID'; 

UPDATE PS_GP_PYE_SEG_STAT SET PYE_CALC_STAT = '50' WHERE CAL_RUN_ID = 'CAL_RUN_ID'; 

Query to find which App Class is used in IB

Query to find which App Class is used in IB 

select IB_OPERATIONNAME, HANDLERNAME, PACKAGEID, PACKAGEROOT pkg, QUALIFYPATH, APPCLASSID clas5, APPCLASSMETHOD m3thod from psoperationac ;

Query to get peoplecode in a project

Query to get peoplecode in a project 

select PROJECTNAME,p1.OBJECTVALUE1, p1.OBJECTVALUE2,p1.OBJECTVALUE3, p1.OBJECTVALUE4,p1.OBJECTVALUE5, OBJECTID6, OBJECTVALUE6,'~`~`~`', PCTEXT,'~`~`~`' from pspcmtxt p1,psprojectitem p2

where

PROJECTNAME like 'AF_RP_WEEKLY_SCHEDULE' and

p1.OBJECTVALUE1 = p2.OBJECTVALUE1 and

p1.OBJECTVALUE2 = p2.OBJECTVALUE2 and

p1.OBJECTVALUE3 = p2.OBJECTVALUE3 ;

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'  

);