Some of the
tables that underwent changes from PS8.9 to PS9.1
version (fields added,fields deleted,renamed..) are listed below:
- PS_CDM_LIST
:PRCSBURSTRPT is added in 9.1 version
- PS_CO_STATETAX_TBL
: SUI_MAX_GROSS, SUI_OVERRIDE are removed in 9.1 version
- PS_DEDUCTION_CLASS
: SUPPRESS_GL Is replaced by EMPLR_MATCH_OPT
- PS_DEPT_TBL: no changes in PS9.1 Version
- PS_EARNINGS_TBL:
PNA_USE_SGL_EMPL is added in PS9.1
version
- PS_EMPLOYEES:
SECURITY_CLEARANCE is renamed as SEC_CLEARANCE_TYPE in PS9.1 Version.
- PS_FLAT_RATE_TBL
is not available in PS9.1 version. It has been
removed. Instead PS_BN_RATE_DATA, PS_BN_RATE_TBL are added in PS9.1.
- PS_GARN_SPEC:
VNDOR_LOC is added in PS9.1 version
- PS_INSTALLATION
Table also undergone changes from PS8.9 TO ps9.1 version
- PS_JOB: SETID_EMPL_CLASS
is added in PS9.1 Version
- PS_JOB_JR:SUSBTIT_REASON_ESP,SUSBTIT_EMPLID_ESP,
PART_SUBROGTN_ESP,LABOR_RLTNSHIP_ESP,LABOR_RLTNSHIP_ESP, INACTIVITY_TYP_ESP, PART_STRK_ACT_ESP
- PS_JOBCODE_TBL:AVAIL_TELEWORK,GVT_NFC_FUNCTN_CD,GVT_NFC_PI_IND_OVR, GVT_OFF_TITLEPREFX, LASTUPDDTTM,LASTUPDOPRID,MIL_RANK, MILITARY_SERVICE are added in PS9.1.
- JOB_PROFILE_ID in
PS8.9 is renamed as KEY_JOBCODE in PS9.1 version
- PS_PAY_TAX
: WORK_PSD_CD, RES_PSD_CD are removed in PS9.1 version.
Important SQLS:
To find the structure of a table :
Exec sp_columns
<Table_Name>
Eg: exec sp_columns PS_JOB
To find the navigation of a Component Online:
SQL:
SELECT
a.portal_name,
e.portal_label
AS parent4_folder,
d.portal_label
AS parent3_folder,
c.portal_label
AS parent2_folder,
b.portal_label
AS parent_folder,
a.portal_label
AS component
FROM
psprsmdefn a
LEFT JOIN
psprsmdefn b ON b.portal_name = a.portal_name
AND
b.portal_objname = a.portal_prntobjname
LEFT JOIN
psprsmdefn c ON c.portal_name = b.portal_name
AND
c.portal_objname = b.portal_prntobjname
LEFT JOIN
psprsmdefn d ON d.portal_name = c.portal_name
AND
d.portal_objname = c.portal_prntobjname
LEFT JOIN
psprsmdefn e ON e.portal_name = d.portal_name
AND
e.portal_objname = d.portal_prntobjname
WHERE
a.portal_reftype = 'C'
AND
a.portal_uri_seg2 = 'ADM_APPL_MAINTNCE'(Use your component name here to find
its navigation)
Alternative
Query(Oracle):
SELECTdistinctA.ROLENAME, A.CLASSID,IN2.navigation,'/EMPLOYEE/ERP/c/'||MI.MENUNAME
||'.'||PNLGRPNAME ||'.GBL' AS NAVIGATION1,
C.MENUNAME,
C.BARNAME, C.BARITEMNAME, C.PNLITEMNAME, C.DISPLAYONLY, C.AUTHORIZEDACTIONS,
D.PAGEACCESSDESCR,B.ROLENAME
FROM PSROLECLASS A,
PSROLEDEFN B, PSAUTHITEM C, PSPGEACCESSDESC D ,PSMENUITEM MI,
(select
SYS_CONNECT_BY_PATH(A.PORTAL_LABEL,'->') navigation, '/EMPLOYEE/ERP/c/' ||
PORTAL_URI_SEG1 || '.' || PORTAL_URI_SEG2 || '.' || PORTAL_URI_SEG3 url
from (SELECT
distinct A.PORTAL_NAME, A.PORTAL_LABEL, A.PORTAL_OBJNAME, a.PORTAL_PRNTOBJNAME,
A.PORTAL_URI_SEG1,
A.PORTAL_URI_SEG2, A.PORTAL_URI_SEG3, A.PORTAL_REFTYPE
FROM PSPRSMDEFN A
WHERE
portal_name =
'EMPLOYEE' and
portal_objname
<> portal_prntobjname and
not exists (
select 'x'
from PSPRSMSYSATTRVL
where portal_name =
A.PORTAL_NAME AND
portal_Reftype =
A.PORTAL_REFTYPE and
portal_objname =
A.PORTAL_OBJNAME and
PORTAL_ATTR_NAM =
'PORTAL_HIDE_FROM_NAV' and
A.PORTAL_OBJNAME
not in ('CO_NAVIGATION_COLLECTIONS', 'PORTAL_BASE_DATA')
)
) A
start with
A.portal_prntobjname = 'PORTAL_ROOT_OBJECT'
connect by prior
A.portal_objname = A.portal_prntobjname
) IN2
WHERE A.ROLENAME =
B.ROLENAME
AND A.CLASSID =
C.CLASSID
AND C.MENUNAME NOT
IN ('WEBLIB_MENU')
AND C.BARNAME NOT
IN ('FieldFormula')
—AND A.ROLENAME =
'GL_GENERIC_ROLE'
AND
D.AUTHORIZEDACTIONS = C.AUTHORIZEDACTIONS
AND C.MENUNAME =
MI.MENUNAME
AND C.BARNAME =
MI.BARNAME
AND C.BARITEMNAME =
MI.ITEMNAME
and
url='/EMPLOYEE/ERP/c/'||MI.MENUNAME ||'.'||PNLGRPNAME ||'.GBL' and URL like
'%CSG_PROMOTION_DEF1%' (from wiki )