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 )
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 )
No comments:
Post a Comment