SQL: Important SQLs

Helpful Queries

--Alter Date format to display date + time
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

--Alter session for sysadm access
alter session set current_schema=sysadm;

--Any batch process (Finance)
Select a.RUNSTATUS, a.* from psprcsrqst a where jobinstance = '4111574' order by prcsjobseq desc;
Select a.RUNSTATUS, a.* from psprcsrqst a where PRCSNAME='VCH_EMAIL’;

--PIA Operator details
SELECT * FROM psoprdefn WHERE oprid = ' ';

--LDAP directory setup
Select * from ps_DIRECTORYATTRIB;
Select * from ps_DIRECTORYSETUP;

--To see the current accesses to database
Select * from v$session where type <> 'BACKGROUND';
Select * from v$process;

--To see the indexes and other characteristics of columns
Select *
FROM dba_ind_columns
Where table_name='PS_PROJ_RESOURCE';
Select * from PSINDEXDEFN where recname = 'PROJ_RESOURCE';

--To see object modification details
Select * from dba_objects where object_name = 'PS_AUDIT_CA_DETAIL';

--To see Key positions in a record. Contains 1 row per key field defined for an index.
Select * from PSKEYDEFN where recname = 'PROJ_RESOURCE';

--Page Permissions
Select * from psauthitem;

--Portal Structure Permissions
Select * from PSPRSMPERM;


--To find the roles, components, menus a user has access to.
Select RUSR.OPRID,
       RUSR.ROLENAME,
       RCL.CLASSID,
       MNU.MENUNAME,
       CMP.BARITEMNAME Component
  From PSROLEUSER_VW RUSR,
       PSROLECLASS RCL,
       PS_PSACLMENU_VW2 MNU,
       PS_ACLCOMPONENT_V2 CMP
 Where RUSR.ROLENAME  = RCL.ROLENAME
   And RUSR.OPRID     = 'PSAAS'
   And RCL.CLASSID    = MNU.CLASSID
   And MNU.MENUNAME   = CMP.MENUNAME
   Order by MNU.CLASSID;


--To find out which roles have a particular permission list.
Select distinct RCL.ROLENAME,
RCL.CLASSID
From PSROLEUSER_VW  RUSR,
PSROLECLASS  RCL
Where RCL.CLASSID = 'ALLPNLS'
Order by RCL.ROLENAME;


--Components, Pages, Records, Fields
/* All the Components */
SELECT * FROM PSPNLGROUP;
/*All the records and views*/
SELECT * FROM PSRECDEFN;
/* Covers record and field associated in a page */
SELECT * FROM PSPNLFIELD WHERE PNLNAME = 'H_HR_CHNG_ASGMNT';
/*capture the objects present in the project*/
Select * from PSPROJECTITEM where projectname='project name';
/*Menu objects*/
Select * from PSMENUDEFN;
/*Record Fields and their properties*/
Select * from PSRECFIELD;
/*Location - When a process name is known:*/
SELECT DISTINCT
PRCS.PRCSTYPE,
PRCS.PRCSNAME,
PRCS.DESCR,
PAGE.PNLGRPNAME as Component,
'Home > ' || RTRIM(M.MENUGROUP) || ' > ' || RTRIM(M.MENULABEL) || ' > ' || RTRIM(ITEM.BARLABEL) || ' > ' || ITEM.ITEMLABEL as Location
FROM PSMENUDEFN M,
PSMENUITEM ITEM,
PS_PRCSDEFNPNL PAGE,
PS_PRCSDEFN PRCS
WHERE M.MENUNAME = ITEM.MENUNAME
AND ITEM.PNLGRPNAME = PAGE.PNLGRPNAME
AND PAGE.PRCSTYPE = PRCS.PRCSTYPE
AND PAGE.PRCSNAME = PRCS.PRCSNAME
AND PRCS.PRCSNAME = 'PY_PULL_COST';

/*Location - When a component name is known:*/
SELECT DISTINCT PRCS.PRCSTYPE , PRCS.PRCSNAME , PRCS.DESCR ,
PAGE.PNLGRPNAME as Component , 'Home > ' || RTRIM(MENU.MENUGROUP) || ' > ' ||
RTRIM(MENU.MENULABEL) || ' > ' || RTRIM(ITEM.BARLABEL) || ' > ' || ITEM.ITEMLABEL as MenuPath
FROM PSMENUDEFN MENU, PSMENUITEM ITEM, PS_PRCSDEFNPNL PAGE, PS_PRCSDEFN PRCS
WHERE MENU.MENUNAME = ITEM.MENUNAME AND ITEM.PNLGRPNAME = PAGE.PNLGRPNAME
AND PAGE.PRCSTYPE = PRCS.PRCSTYPE
AND PAGE.PRCSNAME = PRCS.PRCSNAME
AND PAGE.PNLGRPNAME = 'component-name';

/*Location - When a record name is known:*/
SELECT DISTINCT PFLD.RECNAME ,
PFLD.PNLNAME as Page , 'Home > ' || RTRIM(MENU.MENUGROUP) || ' > ' ||
RTRIM(MENU.MENULABEL) || ' > ' || RTRIM(ITEM.BARLABEL) || ' > ' || ITEM.ITEMLABEL as MenuPath
FROM PSMENUDEFN MENU, PSMENUITEM ITEM, PSPNLGROUP COMP, PSPNLFIELD PFLD
WHERE MENU.MENUNAME = ITEM.MENUNAME
AND ITEM.PNLGRPNAME = COMP.PNLGRPNAME
AND COMP.PNLNAME = PFLD.PNLNAME
AND PFLD.RECNAME = 'record-name';

/*Location - When a page name is known:*/
SELECT DISTINCT COMP.PNLNAME as Page, 'Home > ' ||
RTRIM (MENU.MENUGROUP) || ' > ' || RTRIM (MENU.MENULABEL) || ' > ' ||
RTRIM (ITEM.BARLABEL) || ' > ' || ITEM.ITEMLABEL as MenuPath
FROM PSMENUDEFN MENU, PSMENUITEM ITEM, PSPNLGROUP COMP
WHERE MENU.MENUNAME = ITEM.MENUNAME
AND ITEM.PNLGRPNAME = COMP.PNLGRPNAME
AND COMP.PNLNAME = 'page-name’;


--Which roles have access to a particular query tree or trees
Select distinct ROL.ROLENAME,
ROL.CLASSID,
SAG.TREE_NAME
From PSROLECLASS ROL,
PS_SCRTY_ACC_GRP SAG
Where ROL.CLASSID = SAG.CLASSID
And SAG.TREE_NAME in (Query Tree List);


--To see which query trees a particular user has access to use.
Select distinct USR.ROLENAME,
ROL.CLASSID,
SAG.TREE_NAME
From PSROLEUSER_VW USR,
PSROLECLASS ROL,
PS_SCRTY_ACC_GRP SAG
Where USR.OPRID = 'Operator ID'
And USR.ROLENAME = ROL.ROLENAME
And ROL.CLASSID = SAG.CLASSID;


--To see which users have access to a particular query tree.
Select distinct ROL.ROLENAME,
OPR.OPRDEFNDESC
From PSROLECLASS ROL,
PS_SCRTY_ACC_GRP SAG,
PSROLEUSER_VW USR,
PSOPRDEFN OPR
Where ROL.CLASSID = SAG.CLASSID
And SAG.TREE_NAME = 'QRY_TMUK_ALL'
And ROL.ROLENAME = USR.ROLENAME
And USR.OPRID = OPR.OPRID
Order by ROLENAME, OPR.OPRDEFNDESC;