Accounts Payables:
Voucher Header
|
PS_VOUCHER
|
Voucher Line
|
PS_VOUCHER_LINE
|
Voucher line distribution
|
PS_DISTRIB_LINE
|
Payment Voucher cross reference
|
PS_PYMNT_VCHR_XREF
|
Voucher Posting
|
PS_VCHR_ACCTG_LINE
|
Payment
|
PS_PAYMENT_TBL
|
Quick Invoice tables
Summary Invoice tables
|
|
Vendors:
Vendor Header table
|
PS_VENDOR
|
Vendor address and phone numbers:
|
PS_VNDR_ADDR_SCROL
PS_VENDOR_ADDR
PS_VENDOR_ADDR_PHN
|
Vendor Contacts and phone numbers:
|
PS_VNDR_CNTCT_SCR
PS_VENDOR_CNTCT
PS_VENDOR_CNTCT_PHN
|
Vendor location:
|
PS_VENDOR_LOC
|
Vendor Payment:
|
PS_VENDOR_PAY
|
Vendor Conversion:
|
PS_VENDOR_CONVER
|
Customer Contracts:
Contract Header
|
PS_CA_CONTR_HDR
PS_CA_PGP_TERMS
PS_CA_CHG_HDR
|
Contract Line
|
PS_CA_DETAIL
|
Bill Plans
|
PS_CA_BILL_PLAN
PS_CA_BP_NOTE
PS_BP_LINES
PS_CA_BP_EVENTS
PS_CA_BP_SCHEDULE
|
Revenue Plans
|
PS_CA_ACCTPLAN
PS_CA_AP_EVENT
PS_CA_AP_EVT_CHG
PS_CA_AP_SCHEDULE
PS_CA_ACCTG_LINE
|
Project Costing:
Project
|
PS_PROJECT
|
Project Status & Type and location
|
PS_PROJECT_STATUS
PS_PROJ_TYPE_TBL
PS_PROJ_LOCATION
|
Project description
|
PS_PROJECT_DESCR
|
Project manager & teams
|
PS_PROJECT_MGR
PS_PROJECT_TEAM
PS_PRO_TEAM_SCHED
|
Activities
|
PS_PROJ_ACTIVITY
PS_PROJ_ACT_DESCR
PS_PROJ_ACT_STATUS
|
Activity teams
|
PS_PROJ_ACT_TEAM
PS_PROJ_RESOURCE
|
Project Rates
|
PS_PC_RATE_HDR
PS_PC_RATE
PS_PC_RATE_DTL
PS_PC_RATE_DTL_LN
|
Accounts Payables process flow:
 |
process Scheduler Related Queries
|
To find out who ran the process , time ,runstatus
SELECT PRCSTYPE,
PRCSNAME,
OPRID,
RUNCNTLID,
RUNDTTM,
RUNSTATUSDESCR
FROM PS_PMN_PRCSLIST
WHERE PRCSNAME = 'PROCESS NAME'
---------------------------------------------
To know the components that are attached to the process
SELECT A.PRCSTYPE,
A.PRCSNAME,
PNLGRPNAME,
DESCRLONG
FROM PS_PRCSDEFN A, PS_PRCSDEFNPNL B
WHERE A.PRCSTYPE = B.PRCSTYPE
AND A.PRCSNAME = B.PRCSNAME
AND B.PRCSNAME = 'process name'
---------------------------------------------
In order to know the Report Repository path of the process scheduler based on Process Instance SELECT
PRCSOUTPUTDIR
FROM PSPRCSPARMS WHERE
PRCSINSTANCE = 'Enter Process Instance'
SELECT
PRCSINSTANCE,
PARMLIST,
PRCSOUTPUTDIR
FROM PSPRCSPARMS
WHERE PRCSINSTANCE= 'Enter Pro Instance'
---------------------------------------------
To know the Run-time of the report/interface based on the Process Instance(MS SQL Server)
SELECT DATEPART(HH,ENDDTTM)-DATEPART(HH,BEGINDTTM) HOURS ,DATEPART(MINUTE,ENDDTTM)-DATEPART(MINUTE,BEGINDTTM) MINUTES
FROM PS_PMN_PRCSLIST WHERE PRCSINSTANCE='Enter Process Instance' --------------------------------------------- Distribution Lists
PS_PRCSDEFNCNTDIST
PS_PRCSJOBCNTDIST
PS_PRCSRQSTDIST
PS_PRCSRUNCNTLDIST
|
Security Related
To check whether a User Profile having access to a particular permission list
SELECT A.ROLENAME, A.CLASSID, B.ROLEUSER, B.ROLENAME FROM PSROLECLASS A, PSROLEUSER B WHERE ( A.CLASSID = 'Permission List' AND A.ROLENAME = B.ROLENAME AND B.ROLEUSER LIKE 'User Name' ) ---------------------------------------------------------------------------------------------------------------------------- To find out ROLES for a user profile
SELECT * FROM PSROLEMEMBER WHERE ROLEUSER='Enter user name';
-------------------------------------------------------------------------------
To know the count of ROLES that are assigned for a particular User profile
SELECT ROLEUSER
,COUNT(ROLENAME) 'Count'
FROM PSROLEMEMBER
WHERE ROLEUSER='Enter user name'
GROUP BY ROLEUSER ;
-------------------------------------------------------------------------------
To find out ROLES difference b/w two user profiles (MS SQL SERVER)
SELECT ROLENAME FROM PSROLEMEMBER WHERE ROLEUSER='Enter user name'
EXCEPT
SELECT ROLENAME FROM PSROLEMEMBER WHERE ROLEUSER='Enter user name'
---------------------------------------------
To know the count of Permission Lists for a roleSELECT ROLENAME ,COUNT(CLASSID) 'Permission Lists' FROM PSROLECLASS GROUP BY ROLENAME; --------------------------------------------- To know the Process Groups which are assigned to Permission Lists and further User Id SELECT OC.OPRID 'USER ID', OC.OPRCLASS 'PERMISSIOIN LIST', AP.PRCSGRP 'PROCESS GROUP' FROM PSOPRCLS OC INNER JOIN PSAUTHPRCS AP on OC.OPRCLASS = AP.CLASSID WHERE OC.OPRID = 'user id' ; --------------------------------------------- Roles & Permission lists for a particular user SELECT A.ROLEUSER 'USER NAME' ,A.ROLENAME 'ROLE NAME' ,B.CLASSID 'PERMISSION LISTS' FROM PSROLEUSER A , PSROLECLASS B WHERE A.ROLEUSER = 'Enter user name'; |
Component Navigation in PIA
SELECT P3.PORTAL_LABEL L3
, P2.PORTAL_LABEL L2
, P1.PORTAL_LABEL L1
, P.PORTAL_LABEL L0
, P.*
FROM PSPRSMDEFN P
, PSPRSMDEFN P1
, PSPRSMDEFN P2
, PSPRSMDEFN P3
WHERE P.PORTAL_URI_SEG2 = 'component name'
AND P.PORTAL_PRNTOBJNAME = P1.PORTAL_OBJNAME
AND P1.PORTAL_PRNTOBJNAME = P2.PORTAL_OBJNAME
AND P2.PORTAL_PRNTOBJNAME = P3.PORTAL_OBJNAME
AND P.PORTAL_NAME = P1.PORTAL_NAME
AND P1.PORTAL_NAME = P2.PORTAL_NAME
AND P2.PORTAL_NAME = P3.PORTAL_NAME
---------------------------------------------
(Or)
Replace ‘ +’ with ‘||’ for Oracle db
SELECT
RTRIM(E.PORTAL_LABEL)
+ ' >> ' + RTRIM(D.PORTAL_LABEL)
+ ' >> ' + RTRIM(C.PORTAL_LABEL)
+ ' >> ' + RTRIM(B.PORTAL_LABEL)
+ ' >> ' + RTRIM(A.PORTAL_LABEL)
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_URI_SEG2 = 'component name'
|
Message Catalog related queries
In order to know about message set Information SELECT * FROM PSMSGSETDEFN; ------------------------------------------- In order to know complete details of message catalog details SELECT * FROM FROM PSMSGCATDEFN --------------------------------------------- In order to know no of messages for a Message Set SELECT MESSAGE_SET_NBR 'Message Set'
,COUNT(MESSAGE_NBR) 'Message Nbr'
FROM PSMSGCATDEFN
WHERE MESSAGE_SET_NBR = 'Enter Message Set Number' GROUP BY MESSAGE_SET_NBR ; |
Complete meta-data tables for all objects
--Projects
select * from PSPROJECTDEFN
select * from PSPROJECTITEM
To know projects created by userid select PROJECTNAME ,PROJECTDESCR ,LASTUPDDTTM from PSPROJECTDEFN where LASTUPDOPRID='user name';
--Fields-------------------------------------
select * from PSDBFIELD
--Records------------------------------------
select * from PSRECDEFN
select * from PSRECFIELD
-- To know about record structure SELECT A.FIELDNUM,A.RECNAME, A.FIELDNAME, CASE B.FIELDTYPE WHEN 0 THEN 'Char' WHEN 3 THEN 'Number' WHEN 4 THEN 'Date' end AS DATATYPE , B.LENGTH FROM PSRECFIELD A, PSDBFIELD B WHERE ( A.RECNAME = 'TI_CIP_IB_TBL' AND A.FIELDNAME = B.FIELDNAME ) ORDER BY 1
--Pages -------------------------------------
select * from PSPNLDEFN
select * from PSPNLFIELD
--components --------------------------------
select * from PSPNLGRPDEFN
select * from PSPNLGROUP
To know about Permission List , Menu , Menu Bar which are assigned to a particular component SELECT CLASSID 'Permission List' ,MENUNAME ,BARNAME FROM PSAUTHITEM WHERE BARITEMNAME='Enter Component Name'; To know where the component is attached In order to know the SELECT MENUNAME ,BARNAME ,BARLABEL ,ITEMNAME ,ITEMLABEL FROM PSMENUITEM WHERE ITEMNAME='Enter Component Name';
--menus -------------------------------------
select * from PSMENUDEFN
select * from PSMENUITEM
--Peoplecode ------------------------------
select * from PSPCMNAME
select * from PSPCMPROG
--User--------------------------------------
select * from PSOPRDEFN
select * from PSROLEUSER
Select * from PSOPRALIAS
select * from PS_ROLEXLATOPR
|
