Absence Management Tables - Project

Absence Management Tables - Project

While creating a project we may forget we created. Below is a way to find out.

This shows the Project and its details.
 Set Up HCM  Product Related  Global Payroll & Absence Mgmt  Elements  Manage Global Payroll Packages  Create/Export Rule Package

select * from ps_GP_PIN a1, ps_GP_PKG_CRIT1 b1 where gp_pkg_id = 'NV1' and a1.pin_num = b1.pin_num ;
select * from ps_GP_PIN a1, ps_GP_PKG_ELEMENTS b1 where gp_pkg_id = 'NV1' and a1.pin_num = b1.pin_num ;


select * from ps_GP_PIN where LAST_UPDT_OPRID like 'xxx%' and country in ('USA','ALL'); -- and pin_code like '%USA%';

Set Up HCMProduct RelatedGlobal Payroll & Absence MgmtElementsSupporting ElementsDurations

select * from ps_GP_PIN a1, ps_GP_DURATION b1 where a1.pin_num = b1.pin_num and EFFDT = '01-Sep-2019';

Set Up HCMProduct RelatedGlobal Payroll & Absence MgmtElementsSupporting ElementsVariables

select * from ps_GP_PIN a1, ps_GP_variable b1 where a1.pin_num = b1.pin_num and EFFDT = '01-Sep-2019';

Set Up HCMProduct RelatedGlobal Payroll & Absence MgmtElementsSupporting ElementsBrackets

select * from ps_GP_PIN a1, ps_GP_bracket b1 where a1.pin_num = b1.pin_num and EFFDT = '01-Sep-2019';

 Set Up HCM  Product Related  Global Payroll & Absence Mgmt  Elements  Supporting Elements  Formulas
select * from ps_GP_PIN a1, ps_GP_formula b1 where a1.pin_num = b1.pin_num and EFFDT = '01-Sep-2019';



select * from ps_GP_PIN a1, ps_GP_PKG_CRIT1 b1 where gp_pkg_id like '%USA32' and a1.pin_num = b1.pin_num ; -- all objects in project

select * from ps_GP_PIN a1 where LAST_UPDT_OPRID like 'RPATR%' and country in ('USA','ALL') -- objects not in project
and a1.pin_num not in (select b11.pin_num from ps_GP_PIN a11, ps_GP_PKG_CRIT1 b11 where gp_pkg_id like '%USA32' and a11.pin_num = b11.pin_num and a1.pin_num = b11.pin_num)

order by 2,LAST_UPDT_DTTM desc;

select * from ps_GP_PIN a1, ps_GP_DURATION b1 where a1.pin_num = b1.pin_num and EFFDT >= '01-Sep-2019'  -- duration not in proj
and b1.pin_num not in (select b11.pin_num from ps_GP_PIN a11, ps_GP_PKG_CRIT1 b11 
where gp_pkg_id like '%USA32' and a11.pin_num = b11.pin_num and b1.pin_num = b11.pin_num);

select a1.* from ps_GP_PIN a1, ps_GP_variable b1 where a1.pin_num = b1.pin_num and EFFDT >= '01-Sep-2019' -- variable not in proj
and b1.pin_num not in (select b11.pin_num from ps_GP_PIN a11, ps_GP_PKG_CRIT1 b11 
where gp_pkg_id = 'ANFUSA32' and a11.pin_num = b11.pin_num and b1.pin_num = b11.pin_num);

select distinct a1.PIN_NUM, PIN_CODE, PIN_NM, PIN_TYPE, DESCR, FLD_FMT, a1.DEFN_ASOFDT_OPTN, CHECK_GENER_IND, RECALC_IND, USED_BY, COUNTRY, PIN_CATEGORY, PIN_INDUSTRY, PIN_OWNER, PIN_CLASS, OVRD_IND_CAL, OVRD_IND_PYENT, OVRD_IND_PG, OVRD_IND_PYE, OVRD_IND_PI, OVRD_IND_ELEM, OVRD_IND_SOVR, STORE_RSLT, STORE_RSLT_IF_ZERO, PIN_PARENT_NUM, PIN_CUSTOM1, PIN_CUSTOM2, PIN_CUSTOM3, PIN_CUSTOM4, PIN_CUSTOM5, FCST_IND, FCST_REQ_IND, PIN_DRIVER_NUM, ENTRY_TYPE_USER_F1, PIN_USER_FLD1_NUM, ENTRY_TYPE_USER_F2, PIN_USER_FLD2_NUM, ENTRY_TYPE_USER_F3, PIN_USER_FLD3_NUM, ENTRY_TYPE_USER_F4, PIN_USER_FLD4_NUM, ENTRY_TYPE_USER_F5, PIN_USER_FLD5_NUM, ENTRY_TYPE_USER_F6, PIN_USER_FLD6_NUM, RTO_DELTA_UF_LVL, LAST_UPDT_DTTM, LAST_UPDT_OPRID, AUTO_ASSIGNED_TYPE, a1.GP_VERSION
from ps_GP_PIN a1, ps_GP_formula b1,ps_GP_PKG_CRIT1 c1 where a1.pin_num = b1.pin_num and EFFDT = '01-Sep-2019' -- formula not in proj

and b1.pin_num not in (select b11.pin_num from ps_GP_PIN a11, ps_GP_PKG_CRIT1 b11 
where gp_pkg_id like '%USA32' and a11.pin_num = b11.pin_num and b1.pin_num = b11.pin_num)

;


select * from ps_GP_PIN a1, ps_GP_bracket b1 where a1.pin_num = b1.pin_num and EFFDT >= '01-Sep-2019'-- bracket not in proj
and b1.pin_num not in (select b11.pin_num from ps_GP_PIN a11, ps_GP_PKG_CRIT1 b11 

where gp_pkg_id like '%USA32' and a11.pin_num = b11.pin_num and b1.pin_num = b11.pin_num);


select * from ps_GP_PIN a1, ps_GP_array b1 where a1.pin_num = b1.pin_num and country = 'USA' and pin_nm like '%' '-- array not in proj
and b1.pin_num not in (select b11.pin_num from ps_GP_PIN a11, ps_GP_PKG_CRIT1 b11 

where gp_pkg_id like '%USA32' and a11.pin_num = b11.pin_num and b1.pin_num = b11.pin_num);