SELECT [name] AS [FIELD] FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE type = ‘U’ AND [NAME] = ‘PS_JOB’)
Apr 16 2010
While SQL Select using CASE like IF-Condition
SELECT CASE WHEN MAX(NOTE_SEQ_NBR) IS NULL THEN 1 ELSE MAX(NOTE_SEQ_NBR) + 1 END FROM PS_RC_CASE_NOTE
Apr 14 2010
To Validate an Email id format
/*To Validate the Email id’s*/
Local JavaObject &email_regex = CreateJavaObject(“java.lang.String”, “\b[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}\b”);
Local JavaObject &test_string1 = CreateJavaObject(“java.lang.String”, &Emailtovalidate);
If &test_string1.matches(&email_regex) Then
&MAIL_TO = &Emailtovalidate;
Else
&MAIL_TO = “”;
End-If;
Apr 14 2010
Copy file from PS Folder to FTP Folder using 8.49 and above tools
&Filepath = “cmd.exe /c copy \\(Source Server name)\psreports| filepath| ” \\(Destination Server Name)\Folder\”;
&ExitCode = Exec(&Filepath, %Exec_Asynchronous + %FilePath_Absolute);
Apr 14 2010
To check the process status and wait untill it went success.
SQLExec(“SELECT DISTSTATUS FROM PS_PMN_PRCSLIST WHERE PRCSINSTANCE=:1″, QC_TCN_AET.PROCESS_INSTANCE2, &DISTSTATUS);
While &DISTSTATUS <> 5
SQLExec(“SELECT DISTSTATUS FROM PS_PMN_PRCSLIST WHERE PRCSINSTANCE=:1″, QC_TCN_AET.PROCESS_INSTANCE2, &DISTSTATUS);
End-While;
Apr 14 2010
Send email with attachments in SQR
BEGIN-PROCEDURE sendmail
let $subject = ‘Subject of the Email Here’
let $to_list = ‘email@email.com’
let $ReportID = ‘G:\PeopleSoft\Reports\Report_Name.pdf’
let $alias =’Attachment File Name Alias including the file extension’
let $enter = chr(10)chr(13)
let $body_txt = ‘Hi,’$enter$enter’Please Find the Report attached with this email.’$enter$enter’Regards,’$enter ‘Peoplesoft Application Support’$enter$enter’PS: We request you not to reply to this automated mail trigger.’ let $mail-cmd = ‘F:\PSFT819\bin\server\winx86\psmail -TO”‘$to_list’” -SUBJECT”‘$subject’” -BODY”‘$body_txt’” -FILE”‘ $ReportID ‘” -ALIAS”‘$alias’”‘
CALL SYSTEM USING $mail-cmd #Status
end-procedure
You would have to change the value of the variable $mail-cmd based on th path of your PS_HOME.
Apr 14 2010
To Check PeopleSoft Process Status
SELECT a.prcsname, a.oprid, a.servernamerun, b.xlatlongname AS STATUS,
a.rundttm,
TRUNC (MOD ((a.enddttm – a.rundttm) * 24, 24)) “Hr”,
TRUNC (MOD ((a.enddttm – a.rundttm) * 24 * 60, 60)) mi,
TRUNC (MOD ((a.enddttm – a.rundttm) * 24 * 60 * 60, 60)) sec,
a.prcsinstance, c.outputdir
FROM psprcsrqst a, xlattable b, ps_cdm_list c
WHERE b.fieldname = ‘RUNSTATUS’
AND b.fieldvalue = a.runstatus
AND a.prcsinstance = c.prcsinstance
AND a.prcsname = ‘YourProcessName’
Apr 14 2010
Show Users Assigned a Specific Role
SELECT C.OPRID ,
C.OPRDEFNDESC ,
A.ROLENAME ,
A.DESCR
FROM PSROLEDEFN A,
PSROLEUSER B ,
PSOPRDEFN C
WHERE B.ROLENAME = A.ROLENAME
AND C.OPRID = B.ROLEUSER
AND B.ROLENAME LIKE UPPER(:1)
GROUP BY C.OPRID,
C.OPRDEFNDESC ,
A.ROLENAME ,
A.DESCR
ORDER BY C.OPRID;
Apr 14 2010
Get the Roles Assigned to a Specific User
SELECT C.OPRID,
C.OPRDEFNDESC ,
A.ROLENAME ,
A.DESCR
FROM PSROLEDEFN A,
PSROLEUSER B ,
PSOPRDEFN C
WHERE B.ROLENAME = A.ROLENAME
AND C.OPRID = B.ROLEUSER
AND (C.OPRID =:1
OR C.OPRDEFNDESC LIKE upper(:2))
GROUP BY C.OPRID,
C.OPRDEFNDESC ,
A.ROLENAME ,
A.DESCR;
Apr 14 2010
Roles Permissions Users Assigned to Portal Content Reference or Component
SELECT DISTINCT c.portal_name, c.portal_objname, b.rolename, e.descr
FROM psroleclass b, psprsmperm c, psprsmdefn d, psroledefn e
WHERE c.portal_objname = d.portal_objname
AND b.classid = c.portal_permname
AND b.rolename = e.rolename
AND (d.portal_uri_seg2 = :1 OR d.portal_objname = :2);
Next Page »