Thursday, October 4, 2012

Peoplesoft Delivered Security scripts

In this post, I am explaining some delivered scripts (Datamover, located in PSHOME directory)

1.Userexport.dms
2.userimport.dms
3. mvprdexp.dms


1.userexport.dms
SET OUTPUT USEREXPORT.DAT;
SET LOG USEREXPORT.LOG;

EXPORT PSOPRDEFN;
EXPORT PSOPRALIAS;
EXPORT PSROLEUSER;
EXPORT PSUSERATTR;
EXPORT PSUSEREMAIL;
EXPORT PSUSERPRSNLOPTN;
EXPORT PS_ROLEXLATOPR;
EXPORT PS_RTE_CNTL_RUSER

Note:This script only exports user and user role data not  roles and permission lists 

3. mvprdexp.dms
This is script contains almost all Peopletools core tables information and can be used to upgrade or move core tables.. Click here to download

Message Node tables (Content Provider node and Database node)
SELECT * FROM PSPRDMCNTPRV
SELECT * FROM PSMSGNODEDEFN
SELECT * FROM PSNODEURITEXT
SELECT * FROM PSTRUSTNODES

Portal definition
SELECT * FROM PSPRDMDEFN /*list of all portals*/
SELECT * FROM PSPRDMCNTPRV /*node templates for content provider nodes*/
SELECT * FROM PSPRDMDEFN

Portal Registry tables
SELECT * FROM PSPRSMDEFN ORDER BY PORTAL_OBJNAME, PORTAL_PRNTOBJNAME /* CRefs and Folders */
SELECT * FROM PSPRSMPERM ORDER BY PORTAL_OBJNAME /* CRef & Folder permissions */
SELECT * FROM PSPRSMATTR ORDER BY PORTAL_OBJNAME
SELECT * FROM PSPRSMATTRVAL ORDER BY PORTAL_OBJNAME
SELECT * FROM PSPRSMSYSATTR ORDER BY PORTAL_OBJNAME
SELECT * FROM PSPRSMSYSATTRVL ORDER BY PORTAL_OBJNAME

Portal User Data for 8.4
SELECT * FROM PSPRUHDEFN /*User Homepage */
SELECT * FROM PSPRUHTAB /*User Home Page tab */
SELECT * FROM PSPRUHTABPGLT ORDER BY 1,2,4 /*User Home Page tab pagelets */
SELECT * FROM PSPRUFDEFN /*Favorites */
SELECT * FROM PSPRSMHPASGPGLT /*assigned Home Page pagelets */

PORTAL_URLTEXT field in the PSPRSMDEFN table which contains the URL definitions of the Content References. There are also other PSPRSM prefix tables which are all related to the Portal and Mobile setup, but which are much less frequently needed (and easily found if they are). This case will be updated as required by version changes or customer request, but it should serve as the main repository.

Peoplesoft Tax update Generic and Specific instructions

I worked for Tax update in 2008, and through this post I will try to explain my experience on Peoplesoft Tax update  for my blog visitors and students

FAQ's on Tax Update:
-----------------------

1.How to now Tax update releases?
Ans. Oracle releases the Tax update calender every year, you can mark dates relevant to your Application . you can access calender by logon to  support.oracle.com

2.My current Oracle support covers Tax Updates?
Ans. Oracle offers 3 level of customer support  a.Premium Support b.Extended Support c.Sustaining support.  Tax, Legal, and Regulatory Updates not covered in Sustaining support. Click here to Download Oracle Lifetime support policy doc


Peoplesoft tax update general steps:
Oracle publishes the tax update calendar for a year. You can get the release
dates from that calendar. The generic steps to be followed:

1. Keep the tax update calendar and mark the dates when the tax updates will
be available in support.oracle.com
2. Download the tax update file from the Oracle
3. Pass on the release notes to the payroll analyst/concern authority to analyze any
functional and technical impacts and plan for testing.

After Approval
On Demo Environment
1,Backup Demo

2.Run Compare and report against Test DB
3. Apply tax updates in DEMO environment.
4.Analyze the changes

On Test Environment (Replica of Prod)
1.Backup Test Environment
2. Pre-Update Comp report
3. Apply tax updates in testing environment.
4.Post-Update Comp report
5. Carry out functional and technical testing in testing environment.

Raising change request for Prod from change board

1.Backup Test Environment
2. Pre-Update Comp report
3. Apply tax updates in Prod environment.
4.Post-Update Comp report
5. Carry out functional and technical testing

9. Move tax updates to production.

Apart from those steps, PSAdmin should create own excel sheet to keep update impacts and must follow Oracle docs specific to that Tax update