PeopleSoft Developer Information (Flash Flash : PeopleTools 8.55 released)

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	VCHR_HDR_QV VCHR_LINE_QV VCHR_DIST_QV VCHR_PYMT_QV VCHR_MSCH_QV

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:

Voucher Build (AP_VCHRBLD).
Matching (AP_MATCH).
Pay Cycle (AP_APY2015).
Voucher Posting (AP_PSTVCHR).
Payment Posting (AP_PSTPYMNT).
Document Tolerance (FS_DOC_TOL).

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 role

SELECT 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

In PeopleSoft parlance all payees are called as Vendors .In recent versions this has been changed to a suitable name called as Suppliers (Active from version 9.2) . PeopleSoft delivered functionality offers us to store so much information about Vendors/Suppliers. Most of the times Suppliers component (Component name -VNDR_ID) will be populated with the following information.

Vendor General Information (Vendor Id, Short name ,Long name , Vendor Status ,Vendor class ,Persistence etc)

Vendor Address (Multiple addresses with phone numbers)

Vendor Contacts (Multiple contacts with phone numbers )

Vendor location (Location , Currency , Currency rate type ..)

Vendor Payment (Payment method, Payment Group code..)

Different ways to bring Vendor Information into PeopleSoft:

1) Vendors can be entered through online by using following navigation

Main menu - Vendors - Vendor Information - Add/Update - Vendor

2) Using VNDR_IMPORT application engine

VNDR_IMPORT is having some of the limitations and can be used only within the following situations

-> VNDR_IMPORT process takes the information from flat files, so we need to create so many flat files matching the record structures that we want to populate. This process internally uses File Layouts for data insertion , If we are getting all the Vendor data in a single file and wants to use this process then we needs to extract the data into different files by developing an another custom program before getting started.

-> This program does not do any business validations except field edit validations (prompt table edit/no edit). Needs to apply all the validations prior using this AE.

-> Before getting started with this program , We need to do data mapping and store each file with record name . Suppose we are generating a file for Vendor record we need to store the file in App Server as Vendor.txt

- > Run Vendor detail listing report , Vendor summary listing report to validate the data

3) Using Delivered Component Interface (VNDR_ID_EXCEL) which is created on the top of VNDR_ID component. Loading data through this CI is common as any other data load by using AE+CI combination.

4) By using IB messaging

We can leverage delivered IB messages, Web services to subscribe and publish data between PeopleSoft modules and third party systems. Delivered messages are

Message Name	Function
VENDOR_SYNC	Incremental publish and subscribe.
VENDOR_FULLSYNC	Full-table publishes and subscribe.
VENDOR_SYNC_EFF	Incremental publish of current effective-dated data.
VENDOR_FULLSYNC_EFF	Full-table publish of current effective-dated data.

PeopleSoft Developer Information (Flash Flash : PeopleTools 8.55 released)

Total Pageviews

PeopleSoft Important tables , Process flows and Queries

Vendors In PeopleSoft

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	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