Absence Manage Global Payroll Packages

Create/Export Rule Package

Set Up HCM > Product Related > Global Payroll & Absence Mgmt > Elements > Manage Global Payroll Packages > Create/Export Rule Package

select GP_PKG_ID, DESCR, DESCRSHORT, GP_VERSION_IND, GPDBNAME, LANGUAGE_CD, PKG_CREATE_DTTM, PKG_SCRIPTS_DTTM, PKG_EXPORT_DTTM 

from ps_GP_PKG_DFN where gp_pkg_id like '%Pkg%';

select PIN_NUM, PIN_NM from ps_GP_PIN_NM_VW;

select GP_PKG_ID, PIN_NUM, PIN_CODE, COUNTRY, PIN_NM, PIN_TYPE, PIN_OWNER, GP_PKG_UPG_ACTION, PKG_LNG_OPTN, PIN_NUM_TGT, GP_PKG_NEW_PIN_FLG, GP_PKG_ELEM_CMP_ST, GP_PKG_FAIL_REASON, GP_PKG_UPG_OPTN, GP_PKG_ELEM_UPG_ST 

from ps_GP_PKG_ELEMENTS

where gp_pkg_id like '%Pkg%';

Create Non-Rule Package

Set Up HCM  Product Related  Global Payroll & Absence Mgmt  Elements  Manage Global Payroll Packages  Create Non-Rule Package

 

select GP_PKG_ID, DESCR, DESCRSHORT, GPDBNAME, PKG_CREATE_DTTM, PKG_EXPORT_DTTM, PKG_EXPORT_NR_DTTM, PKG_IMPORT_DTTM, PKG_COMPARE_DTTM, PKG_IMPORT_NR_DTTM, PKG_UPGRADE_DTTM, COMMENTS

from ps_GP_NR_PKG_DFN;

select GP_PKG_ID, RECNAME, FIELDNAME, FLD_FMT, GP_SQL_OPERATOR, DATE_VALUE, DECIMAL_VALUE, CHARACTER

from ps_GP_NR_PKG_DTL;

select GP_PKG_ID, RECNAME, RECNAME_EXPORT, WHERE_CLAUSE_OPTN, GRP_WHERE_CLS 

from ps_GP_NR_PKG_RECS;

Some examples

Absence Take Types

Set Up HCM  Product Related  Global Payroll & Absence Mgmt  Elements  Absence Elements  Absence Take Types

select * from ps_GP_ABS_TYPE where ABS_TYPE_OPTN in ('TFW','TRN');

select * from ps_GP_ABS_REASON where ABS_TYPE_OPTN in ('TFW','TRN');

 

Eligibility Groups

Set Up HCM  Product Related  Global Payroll & Absence Mgmt  Framework  Organizational  Eligibility Groups

 

Select * from ps_GP_ELIG_GRP where  ELIG_GRP like 'HDS%%';

Select * from ps_GP_ELIG_GRP_MBR where  ELIG_GRP like 'HDS%%';

 

Time Reporting Codes – TRC

Set Up HCM  Product Related  Time and Labor  Time Reporting  Time Reporting Codes – TRC

/EMPLOYEE/HRMS/c/DEFINE_TIME_AND_LABOR.TL_TRC_MAIN_PNLGRP.GBL

Select * from ps_TL_TRC_TBL where  TRC like 'HS%%';

Select * from ps_TL_ERNCD_TBL where  TRC like 'HS%%';

 

TRC Program

/EMPLOYEE/HRMS/c/DEFINE_TIME_AND_LABOR.TL_TRCPGM_PNLGRP.GBL

 

Select * from ps_TL_TRC_PGM_TBL where  TRC_PROGRAM like 'USRH%%' and effdt = '01-Oct-2020';

Select * from ps_TL_TRCPGELE_TBL where  TRC_PROGRAM like 'USRH%%' and effdt = '01-Oct-2020';

 

Absence Earns/Deductn Mapping

Set Up HCM  Product Related  Global Payroll & Absence Mgmt  Integration  Absence Earns/Deductn Mapping

/EMPLOYEE/HRMS/c/DEFINE_PAYROLL_RULES_(GBL).GP_ABS_ERN_DED_MAP.GBL

 

Select * from ps_GP_ABS_ERN_DED where ABS_PAY_SYSTEM_CD = 'PI' and effdt = '01-Oct-2020';

Select * from ps_GP_ABS_ERNDDMAP where ABS_PAY_SYSTEM_CD = 'PI' and effdt = '01-Oct-2020';

 

 

Country Take

Set Up HCM  Product Related  Global Payroll & Absence Mgmt  Absence Management  Country Take

/EMPLOYEE/HRMS/c/DEFINE_PAYROLL_RULES_(GBL).GP_ABS_BAL_SS_DEF.GBL

select * from ps_GP_ABS_SS where effdt = '01-Oct-2020' and country = 'USA'

and pin_take_num in (select pin_num from ps_gp_pin gp where gp.pin_num = pin_take_num and effdt = '01-Oct-2020'

and (pin_nm like 'ANF HDS%' or pin_nm like 'ANF HDCT%') );

SELECT USED_BY, COUNTRY, ABS_TYPE_OPTN, EFFDT, EFF_STATUS, DESCR, DESCRSHORT, REQUEST_IND 
FROM ps_GP_ABS_TYPE 
WHERE ABS_TYPE_OPTN in ('TFW','TRN');
SELECT USED_BY, COUNTRY, ABS_TYPE_OPTN, EFFDT, ABSENCE_REASON, DESCR, DESCRSHORT 
FROM ps_GP_ABS_REASON 
WHERE ABS_TYPE_OPTN in ('TFW','TRN');
SELECT ELIG_GRP, EFFDT, EFF_STATUS, DESCR, DESCRSHORT 
FROM ps_GP_ELIG_GRP 
WHERE ELIG_GRP like 'HDS%%';
SELECT ELIG_GRP, EFFDT, PIN_ELEM_GRP_NUM 
FROM ps_GP_ELIG_GRP_MBR 
WHERE ELIG_GRP like 'HDS%%';
SELECT TRC, EFFDT, EFF_STATUS, DESCR, DESCRSHORT, TRC_TYPE, UNIT_OF_MEASURE, CURRENCY_CD, SEND_TO_PAYROLL, MAX_QTY, MIN_QTY, FACTOR_MULT, FACTOR_RATE_ADJ, PER_INSTANCE, PER_INST_OVR_FLAG, RATE, RATE_OVR_FLAG, ADD_GROSS, DILUTE_LBDST_FLAG, HRS_REPRESENT_IND, SEND_TO_TCD, COMP_LEAV_IND, USED_COPY_FROM , TL_COPY_FROM , GP_ABSENCE_FLG, SYNCID, TRC_SUM_CATEGORY, TRC_PUB_FLG, TRC_FILTER, TL_APPR_PAY_ADHOC, TL_APPR_PAY_DEFN, TL_APPR_PAY_FLG, TL_APPR_PAY_GRP1, TL_APPR_PAY_GRP2, TL_APPR_PAY_PRCS, TL_APPR_RPT_ADHOC, TL_APPR_RPT_DEFN, TL_APPR_RPT_GRP1, TL_APPR_RPT_GRP2, TL_APPR_RPT_PRCS, TL_APRV_RPT_FLG, LASTUPDDTTM, COMMENTS 
FROM ps_TL_TRC_TBL 
WHERE TRC like 'HS%%';
SELECT TRC, EFFDT, PAY_SYSTEM, TL_ERNCD, TL_COPY_FROM , EFF_STATUS, TL_TAKECD, ERNCD 
FROM ps_TL_ERNCD_TBL 
WHERE TRC like 'HS%%';
SELECT TRC_PROGRAM, EFFDT, DESCR, DESCRSHORT 
FROM ps_TL_TRC_PGM_TBL 
WHERE TRC_PROGRAM like 'USRH%%' and effdt = '01-Oct-2020';
SELECT TRC_PROGRAM, EFFDT, TRC, TRC_ACCESS, ORDER_BY_SEQ 
FROM ps_TL_TRCPGELE_TBL 
WHERE TRC_PROGRAM like 'USRH%%' and effdt = '01-Oct-2020';
SELECT ABS_PAY_SYSTEM_CD, EFFDT, EFF_STATUS 
FROM ps_GP_ABS_ERN_DED 
WHERE ABS_PAY_SYSTEM_CD = 'PI' and effdt = '01-Oct-2020';
SELECT ABS_PAY_SYSTEM_CD, EFFDT, PIN_NUM, ENTRY_TYPE_TAKE, ERNCD, PI_FIELD_VALUE 
FROM ps_GP_ABS_ERNDDMAP 
WHERE ABS_PAY_SYSTEM_CD = 'PI' and effdt = '01-Oct-2020';
SELECT * 
FROM ps_GP_ABS_SS 
WHERE effdt = '01-Oct-2020' and country = 'USA'
and pin_take_num in (SELECT pin_num 
FROM ps_gp_pin gp 
WHERE gp.pin_num = pin_take_num and effdt = '01-Oct-2020' 
and (pin_nm like 'ANF HDS%' or pin_nm like 'ANF HDCT%') );

Apply Rule Package

Set Up HCM  Product Related  Global Payroll & Absence Mgmt  Elements  Manage Global Payroll Packages  Apply Rule Package

Instruction to import Rule and Non-Rule packages

Import Rule package:

1)      Run the clean-up script first in Target environment.

-- *************************************************************************

-- ATTENTION: This script will delete all 50,000,000+ pin numbers that      

--            exist on Element Definition tables.                           

--                                                                          

--            You should ONLY run this if you need to clean-up 50,000,000+  

--            pin numbers.      

--                                                                          

--            You should NOT run this if you are in the MIDDLE of applying  

--            a Rule Package (unless you want to back out what you have     

--            just imported). Do NOT run this script and then try to run    

--            a Package Compare or Upgrade (as you will have deleted the    

--            elements that the package is trying to compare/upgrade and    

--            this could also cause the original element definitions to be  

--            deleted).                                                     

-- ************************************************************************

SET LOG gp_cleanup.log;

DELETE FROM PS_GP_ABS_ENTL WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_ABS_TAKE WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_ABS_TAKE_CFG WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_ABS_TAKE_DAY WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_ABS_TAKE_ELM WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_ABS_TAKE_ENT WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_ABS_TK_FCST WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_ACCUMULATOR WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_ACM_MBR WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_ARRAY WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_ARRAY_FLD WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_ARRAY_KEY WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_ARRAY_PRC WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_AUTOGEN_DFLT WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_BRACKET WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_BRACKET_DTL WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_COUNT WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_DATE WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_DURATION WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_DUR_GNRN WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_ELEM_GRP WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_ELEM_GRP_MBR WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_ELM_DFN_SOVR WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_ERN_DED WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_FC_IN WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_FC_OUT WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_FC_OUT_DTL WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_FC_SEG WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_FC_TBL WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_FORMULA WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_FORMULA_CLUE WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_FORMULA_DTL WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_FORMULA_VAR WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_GCTL WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_GCTL_DTL WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_HIST_ELEM WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_HIST_RULE WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_PIN WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_PIN_CMPNT WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_PROCESS WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_PROCESS_DTL WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_PRORATION WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_RATE_CODE WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_ROUND_RULE WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_SECTION WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_SECTION_DTL WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_SYSTEM_PIN WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_VARIABLE WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_WA_ARRAY WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_WA_FLD WHERE PIN_NUM > 50000000;

DELETE FROM PS_GP_PIN_LANG WHERE PIN_NUM > 50000000;

UPDATE PS_GP_PKG_DFN SET PKG_IMPORT_DTTM = %DateTimeNull, PKG_COMPARE_DTTM = %DateTimeNull WHERE PKG_UPGRADE_DTTM IS NULL;

2)      Import the Rule package

Run the DMS script in Datamover and should load successfully without any error.

3)      Navigate to the below path in Target environment to Compare and Upgrade the rule package.

Main Menu -> Set Up HCM -> Product Related -> Global Payroll&Absence Mgmt -> Elements -> Manage Global Payroll Packages -> Apply Rule Package

As we have already run the script in Data mover, by default imported check box is tick marked as shown in above snip.

 

1)      On the same page check the Compare package, create compare report and select the appropriate compare report option as shown in below snip. Also provide the server path in script location and the click on process. Wait till the process get success.

1)      After successful compare report, please check the compare report from the View Log/Trace from the process monitor. If everything is fine please continue with the upgrade of rule package if not please check and correct from step one.

2)      Navigate to the Apply rule package page (the path given in step 3) and open the respective Rule package and the check the box for upgrade as shown in below diagram. And then click on process and wait till the package copy process went to success

 

Check for the package status

Import Non-Rule package:

1)      Run the import elements script first in target Datamover and make sure the script loaded completely.

Sample Export Script

SET OUTPUT pkg_elements.dat;

SET LOG pkg_elements_exp.log;

UPDATE PS_GP_NR_PKG_DFN SET PKG_EXPORT_DTTM=%CURRENTDATETIMEIN WHERE GP_PKG_ID='pkg';

EXPORT GP_NR_PKG_DFN WHERE GP_PKG_ID = 'pkg';

EXPORT GP_NR_PKG_RECS WHERE GP_PKG_ID = 'pkg';

EXPORT GP_NR_PKG_DTL WHERE GP_PKG_ID = 'pkg';

EXPORT GP_NR_PKG_ELMTS WHERE GP_PKG_ID = 'pkg';

Sample Import Script

SET INPUT pkg_elements.dat;

SET LOG pkg_elements_imp.log;

DELETE FROM PS_GP_NR_PKG_DFN WHERE GP_PKG_ID = 'pkg';

DELETE FROM PS_GP_NR_PKG_RECS WHERE GP_PKG_ID = 'pkg';

DELETE FROM PS_GP_NR_PKG_DTL WHERE GP_PKG_ID = 'pkg';

DELETE FROM PS_GP_NR_PKG_ELMTS WHERE GP_PKG_ID = 'pkg';

IMPORT PS_GP_NR_PKG_DFN;

IMPORT PS_GP_NR_PKG_RECS;

IMPORT PS_GP_NR_PKG_DTL;

IMPORT PS_GP_NR_PKG_ELMTS;

UPDATE PS_GP_NR_PKG_DFN SET PKG_IMPORT_DTTM=%CURRENTDATETIMEIN WHERE GP_PKG_ID='pkg';

UPDATE PS_GP_NR_PKG_DFN SET PKG_CREATE_DTTM = %DateTimeNull WHERE GP_PKG_ID='pkg';

UPDATE PS_GP_NR_PKG_DFN SET PKG_EXPORT_DTTM = %DateTimeNull WHERE GP_PKG_ID='pkg';

UPDATE PS_GP_NR_PKG_DFN SET PKG_EXPORT_NR_DTTM = %DateTimeNull WHERE GP_PKG_ID='pkg';

2)      Navigate to the below path to compare the non-rule package as we have already imported the script from Datamover(as given in above step).

Main Menu -> Set Up HCM -> Product Related -> Global Payroll&Absence Mgmt -> Elements -> Manage Global Payroll Packages -> Compare Non-Rule Package

 

Please select the appropriate Non-Rule package and go to the Package Elements tab as shown in below snip. Click on compare and should completed successfully, in case of any issue please check the error and correct it.

 

Check for the package status.

 

3)      Run Import script for Non-Rule Records in Datamover and make sure script loaded completely.

Sample Export Script

SET OUTPUT _records.dat;

SET LOG _records_exp.log;

UPDATE PS_GP_NR_PKG_DFN SET PKG_EXPORT_NR_DTTM=%CURRENTDATETIMEIN WHERE GP_PKG_ID='pkg';

EXPORT GP_ABS_NRP_EDVW  where  ABS_PAY_SYSTEM_CD='PI' and EFFDT=%datein('2019-06-01');

EXPORT GP_ABS_ERN_DED  where  ABS_PAY_SYSTEM_CD='PI' and EFFDT=%datein('2019-06-01');

EXPORT GP_ABS_NRP_FMVW  where  EFFDT=%datein('2019-06-01');

EXPORT GP_ABS_REASON  where  EFFDT=%datein('2019-06-01');

EXPORT GP_NRP_ASS_VW  where  COUNTRY='ESP' and EFFDT=%datein('2019-06-01');

EXPORT GP_ABS_CFNRP_VW  where  EFFDT=%datein('2019-06-01');

EXPORT GP_ABS_TYPE  where  EFFDT=%datein('2019-06-01');

EXPORT GP_ELIG_GRP  where  EFFDT=%datein('2019-06-01');

EXPORT GP_NRP_EGMBR_VW  where  EFFDT=%datein('2019-06-01');

EXPORT TL_ERNCD_TBL  where  EFFDT=%datein('2019-06-01');

EXPORT TL_TRCPGELE_TBL  where  EFFDT=%datein('2019-06-01') and TRC_PROGRAM like '%';

EXPORT TL_TRC_PGM_TBL  where  DESCR like '%' and EFFDT=%datein('2019-06-01');

4)      Upgrade Non-Rule Package: Navigate to the below path

5)      Main Menu -> Set Up HCM -> Product Related -> Global Payroll&Absence Mgmt -> Elements -> Manage Global Payroll Packages -> Upgrade Non-Rule Package

 

 

 

So, the over all steps followed as under for any non-rule based package import -

·       Run the Import element script in Datamover in target environment.

·       Compare the Non-rule package.

·       Import the Non-rule Records script in Datamover in target environment.

·       Upgrade the Non-rule package.

Earnings Program Table

Set Up HCM > Product Related > Payroll for North America > Compensation and Earnings > Earnings Program Table

select ERN_PROGRAM, EFFDT, EFF_STATUS, DESCR, DESCRSHORT 

from ps_ERN_PROGRAM_TBL;

select ERN_PROGRAM, EFFDT, ERNCD 

from ps_ERN_PROGRAM_DEF;

Earnings Table

 Set Up HCM > Product Related  >Payroll for North America > Compensation and Earnings > Earnings Table

select ERNCD, to_char(effdt,'yyyy-mm-dd') as effdt, EFF_STATUS, DESCR, DESCRSHORT, ERN_SEQUENCE, MAINTAIN_BALANCES, BUDGET_EFFECT, ALLOW_EMPLTYPE, PAYMENT_TYPE, HRLY_RT_MAXIMUM, PERUNIT_OVR_RT, EARN_FLAT_AMT, ADD_GROSS, SUBJECT_FWT, SUBJECT_FICA, SUBJECT_FUT, SUBJECT_CIT, SUBJECT_CUI, SUBJECT_CUI_HOURS, SUBJECT_CPP, SUBJECT_QIT, SUBJECT_QPP, SUBJECT_TRUE_T4GRS, SUBJECT_TRUE_RVGRS, SUBJECT_PAY_TAX, SUBJECT_REG, WITHHOLD_FWT, HRS_ONLY, SHIFT_DIFF_ELIG, TAX_GRS_COMPNT, SPEC_CALC_RTN, FACTOR_MULT, FACTOR_RATE_ADJ, FACTOR_HRS_ADJ, FACTOR_ERN_ADJ, GL_EXPENSE, SUBTRACT_EARNS, DEDCD_PAYBACK, TAX_METHOD, EARN_YTD_MAX, BASED_ON_TYPE, BASED_ON_ERNCD, BASED_ON_ACC_ERNCD, AMT_OR_HOURS, PNA_USE_SGL_EMPL, ELIG_FOR_RETROPAY, USED_TO_PAY_RETRO, EFFECT_ON_FLSA, FLSA_CATEGORY, REG_PAY_INCLUDED, TIPS_CATEGORY, ADD_DE, SUBJECT_T4A, SUBJECT_RV2, GVT_BENEFITS_RATE, GVT_CPDF_ERNCD, GVT_OTH_PAY, GVT_PAY_CAP, GVT_PREM_PAY, GVT_OT_PAY_IND, GVT_ADD_TO_SF50_52, GVT_INCLUDE_LOC, GVT_IRR_REPORTABLE, GVT_IRR_LWOP, GVT_SF113A_LUMPSUM, GVT_SF113A_WAGES, GVT_FEFFLA, GVT_FMLA, GVT_LV_EARN_TYPE, INCOME_CD_1042, PERMANENCY_NLD, TAX_CLASS_NLD, HRS_DIST_SW, HP_ADMINSTIP_FLAG, SUBJECT_QPIP

from ps_EARNINGS_TBL 

where erncd like 'MP%';

Create Additional Pay

/c/MAINTAIN_PAYROLL_DATA_US.ADDITIONAL_PAY.USA

 Payroll for North America > Employee Pay Data USA > Create Additional Pay

Level 0

select EMPLID, EMPL_RCD, ERNCD, RECORD_SOURCE 

from ps_ADDL_PAY_ERNCD  

where erncd like 'M%';

Level 1

select EMPLID, EMPL_RCD, ERNCD, to_char(effdt,'yyyy-mm-dd') as effdt, RECORD_SOURCE 

from ps_ADDL_PAY_EFFDT  

where erncd like 'M%';

Level 2

select EMPLID, EMPL_RCD, ERNCD, to_char(effdt,'yyyy-mm-dd') as effdt, ADDL_SEQ, DEPTID, JOBCODE, POSITION_NBR, ACCT_CD, GL_PAY_TYPE, ADDL_PAY_SHIFT, OTH_HRS, HOURLY_RT, OTH_PAY, ADDLPAY_REASON, SEPCHK, EARNINGS_END_DT, GOAL_AMT, GOAL_BAL, OK_TO_PAY, DISABLE_DIR_DEP, PRORATE_ADDL_PAY, PRORATE_CUI_WEEKS, PAY_PERIOD1, PAY_PERIOD2, PAY_PERIOD3, PAY_PERIOD4, PAY_PERIOD5, STATE, LOCALITY, TAX_PERIODS, TAX_METHOD, ADDL_PAY_FREQUENCY, DED_TAKEN, DED_SUBSET_ID, DED_TAKEN_GENL, DED_SUBSET_GENL, PLAN_TYPE, BUSINESS_UNIT, COMP_RATECD, RECORD_SOURCE 

from PS_ADDL_PAY_DATA 

where erncd like 'M%';

 Handling File Attachment Size Dynamically:

We can easily hardcode the File Size limit by setting a value to the &size variable in below code.

But if we want to use it dynamically, then we can get the value and use Int to convert the value to an integer and use it. Since Value(&size) returns a decimal value, it is not accepted.

&size = Int(&fsize);

&RETCODE = AddAttachment(URL.AF_MOBILE_USER_SS_FILE, &UniqueSysFilename, "", &ATTACHUSERFILE, &size * 1024);

The dynamic value can be stored in any setup or in a message catalog and pulled using below code, where 678 id default value in case message catalog 123,45 doesnt have this value.

&fsize = MsgGetText(123, 45, "678");

File Extension List

Setting up and Migrating File Extension List

select * from ps_PTFX_EXTLSTDEFN;

select * from PS_PTFX_EXTLST where PTFX_EXTLST_NAME =  'GSS_EXTENSION_LIST'; 

For more configuration details check the below:

https://docs.oracle.com/cd/F13639_01/psft/pdf/pt857tsvt-b122018.pdf 

Chapter 9 

Using PeopleTools Utilities

For Migration suggestions check the below:

https://docs.oracle.com/cd/E40124_01/psft/acrobat/PeopleSoft-Portal-Solutions-9.0-to-9.1-Revision-2-Upgrade_032013.pdf

Applying Application Changes 

Chapter 5

Task 5-13-21: Exporting File Extension Lists This script exports the definition and contents of every file extension list defined for attachments in the new release. The script name for your upgrade path is: PTFX_EXTLSTS_EXP.DMS

Task 5-13-22: Importing File Extension Lists This script imports the definition and contents of every file extension list delivered in the new release. Note that for any duplicates, this script will overwrite any customizations that were made. The script name for your upgrade path is: PTFX_EXTLSTS_IMP.DMS

PeopleSoft Fluid

 Dashboards 

/c/PTAL_ADMIN.PTAL_DASHBD_PAGES.GBL 

à

à

 à Save

Output

Fluid Icon

/c/PORTAL_ADMIN.PORTAL_OBJ_LIST.GBL

à

à Save 

Output

Custom Fluid Icon

à Save

Output à ?

Still Testing

Tile Wizard

/c/NUI_FRAMEWORK.PTGPLT_WIZARD_NUI.GBL

à

à

à

à

à

Output

Still Testing

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'  

);