Ask Question
Help Others
Home

Blogs

Find Duplicate Objects in Application Desinger Projects

Tue, 05/13/2008 - 4:48pm — Lepa

The sql will identify duplicate objects in different application designer projects to eliminate duplicate work by developers.

You will find the SQL very handy during the analysis phase of a peoplesoft upgrade project. You 1st need to identify your projects and run compare and report through application designer. This will flag objects in the source database that are different than the target database to be marked for upgrade.

Permission Lists Assigned to a User

Thu, 05/08/2008 - 3:32pm — Lepa

SQL that I find useful in many occasions. It will return a list of permissions that are assigned to a specific user.

SELECT   d.oprid, d.oprdefndesc, c.roleuser, a.rolename, a.classid,
         b.classdefndesc
    FROM psroleclass a, psclassdefn b, psroleuser c, psoprdefn d
   WHERE a.classid = b.classid
     AND c.rolename = a.rolename
     AND d.oprid = c.roleuser
     AND d.oprid = :userid
GROUP BY d.oprid,
         d.oprdefndesc,
         c.roleuser,
         a.rolename,
         a.classid,
         b.classdefndesc;

Compare Report in CSV or HTML format

Wed, 05/07/2008 - 2:05pm — Ashar

Click here -> Compare Report in HTML format to see how you can generate compare reports in HTML or CSV format.

Download Collaborate 08 Education Sessions

Mon, 05/05/2008 - 1:56pm — Lepa

The education presentations from COLLABORATE 08 will be available on questdirect.org website until May 15th.

Filter on PeopleSoft sessions and download the ones you are interested in. Here is the link.

Trace Application Engine Processes Using Process Definitions

Tue, 04/29/2008 - 3:31pm — Lepa

In this post I will show you how to trace an application engine process using the Process Definitions without setting the trace in the Process Scheduler’s psprcs.cfg configuration file.

Navigate to the process definition of the application engine that you would like to trace (PeopleTools > Process Scheduler > Processes). Go to the Overrride Options tab, and from the Parameter List drop down select Append, and in the edit box next to it add the following line:

-TRACE 7 -TOOLSTRACEPC 4044

TRACE application engine using process definition

Vendor Pending Transactions

Thu, 04/24/2008 - 4:56pm — Lepa

A quick function that will allow you to verify if a specific vendor has any pending transaction(s).

Function vendor_pnding_transactions();

      &VENDOR = VENDOR_ID;
      SQLExec("select 'X' from ps_voucher a, ps_pymnt_vchr_xref b where a.voucher_id = b.voucher_id and a.business_unit = b.business_unit and a.vendor_id = :1 and a.entry_status in ('P', 'R')and a.close_status = 'O' and b.pymnt_selct_status in ('N', 'D', 'R')", &VENDOR, &EXISTS);
      If All(&EXISTS) Then
         Error Messagebox(0,"",0,0,("Vendor has pending transaction(s).");
      End-If;

Troubleshoot the SMTP Server

Thu, 04/17/2008 - 2:29pm — Lepa

The SendMail function in PeopleSoft uses the SMTP server to send emails. The first thing to do when troubleshooting problems with emails not being sent is to make sure your SMTP server is up and running. In this post, I will show you how to verify if you can connect to the SMTP server as well as manually sending an email!

Note: I’m using Unix Telnet to connect to the SMTP sever.

Let’s get started by trying to connect to the server:

Open a telnet session and login to the server box you would like to test sending emails from. I will be logging in to a DEV environment.

Send Emails from PeopleCode (SendMail Function)

Tue, 04/15/2008 - 11:25am — Lepa

You can use the SendMail PeopleCode function to send emails from within PeopleCode. You can also call this function from an Application Engine.

Note: Make sure your SMTP server is configured properly or the SendMail function will fail.

Local string &MAIL_CC, &MAIL_TO, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TITLES, &MAIL_TEXT, &MAIL_FILES, &MAIL_FROM, &REPLYTO, &SENDER;
Local number &MAIL_FLAGS;

&MAIL_FLAGS = 0;
&MAIL_TO = "email-address-message-going-to";
&MAIL_CC = "";
&MAIL_BCC = "";
&MAIL_SUBJECT = "Test email";

Compare Data from the Same Table in two Different Environments

Mon, 04/14/2008 - 1:25pm — Lepa

One of the requirements I have lately is to compare table data between two different environments (Development and Test).
The Oracle SQL below compares table1 that has 2 key fields and 3 regular fields.

Note: For the SQL below to work, your password needs to be the same in both environments. If not, then
a connection using the below will not be possible.

FROM table1 tst, table1@dev_database dev

-- Compare data from the same table in two different environments
SELECT   tst.fieldkey1, tst.fieldkey2,
         (CASE tst.field1

Compare data in different tables with same structure

Mon, 04/14/2008 - 5:05am — Ashar

Here is the SQL to compare the differences in the data in different tables with same structure. Will be helpful for comparing the data between databases. This works in MS SQL Server if both databases are on same server or if there is a linked server configured for the other database.

SELECT  * FROM
(SELECT Max(TableName) AS TableName, FIELD1, FIELD2, FIELD3
FROM (SELECT 'HRDB1..PS_MY_RECORD' AS TableName, FIELD1, FIELD2, FIELD3
FROM HRDB1..PS_MY_RECORD
UNION ALL SELECT 'HRDB2..PS_MY_RECORD' AS TableName, FIELD1, FIELD2, FIELD3
FROM HRDB2..PS_MY_RECORD ) A