Send Emails in SQR

Here is the piece of code you can use to 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

PeopleSoft IDs

User Id

The User Id is the ID associated with the individual user logging onto the PeopleSoft application, via PeopleTools.Validation of this ID is performed by the PeopleTools themselves at log-in time, by virtue of verifying that a row has been defined in the PSOPRDEFN table for this ID. The row in the PSOPERDEFN table contains information pertinant to PeopleSoft internal security administration, such as the access profile defining what areas of the application this ID will have access to, and a link to the ACCESS ID (defined later) associated with the UserId.

Connect Id

The Connect Id is the actual ID that makes the initial log on connection to the database.It requires SELECT ONLY access to three tables: PSSTATUS, PSOPRDEFN, and PSACCESSPRFL.Once the row is located in PSOPRDEFN associated with the User Id entered, the associated Access Id is validated within PeopleTools, and the Connect Id is disconnected, and the Access Id is logged on.

Symbolic Id

The Symbolic Id isn't really an Id at all, but is referred to as such in the PeopleTools documentation. The Symbolic Id provides the "link" between the row containing the User Id in table PSOPRDEFN with the row containing the corresponding Access Id and Password in table PSACCESSPRFL. The Symbolic Id field is a regular field on table PSOPRDEFN and the key field on table PSACCESSPRFL. For each Access Id defined, you should define a unique Symbolic Id within the PeopleTools table PSACCESSPRFL. The Access Id is associated with the unique Symbolic Id in table PSACCESSPRFL, and the Symbolic ID is then linked with the specific User Id in table PSOPRDEFN, by updating the PSOPRDEFN.SYMBOLICID column.

Access Id

The Access Id is the ID under which all DML is initiated.

PeopleSoft Panel Navigation

Here is the MS SQL query which will give you the panel/page navigation in PeopleSoft.

Select Distinct
RTrim(LTrim(IsNull(Level13.PORTAL_LABEL,''))) + Case When Level13.PORTAL_LABEL Is Null Then '' Else ' > ' End +
RTrim(LTrim(IsNull(Level12.PORTAL_LABEL,''))) + Case When Level12.PORTAL_LABEL Is Null Then '' Else ' > ' End +
RTrim(LTrim(IsNull(Level11.PORTAL_LABEL,''))) + Case When Level11.PORTAL_LABEL Is Null Then '' Else ' > ' End +
RTrim(LTrim(IsNull(Level10.PORTAL_LABEL,''))) + Case When Level10.PORTAL_LABEL Is Null Then '' Else ' > ' End +
RTrim(LTrim(IsNull(Level9.PORTAL_LABEL,''))) + Case When Level9.PORTAL_LABEL Is Null Then '' Else ' > ' End +
RTrim(LTrim(IsNull(Level8.PORTAL_LABEL,''))) + Case When Level8.PORTAL_LABEL Is Null Then '' Else ' > ' End +
RTrim(LTrim(IsNull(Level7.PORTAL_LABEL,''))) + Case When Level7.PORTAL_LABEL Is Null Then '' Else ' > ' End +
RTrim(LTrim(IsNull(Level6.PORTAL_LABEL,''))) + Case When Level6.PORTAL_LABEL Is Null Then '' Else ' > ' End +
RTrim(LTrim(IsNull(Level5.PORTAL_LABEL,''))) + Case When Level5.PORTAL_LABEL Is Null Then '' Else ' > ' End +
RTrim(LTrim(IsNull(Level4.PORTAL_LABEL,''))) + Case When Level4.PORTAL_LABEL Is Null Then '' Else ' > ' End +
RTrim(LTrim(IsNull(Level3.PORTAL_LABEL,''))) + Case When Level3.PORTAL_LABEL Is Null Then '' Else ' > ' End +
RTrim(LTrim(IsNull(Level2.PORTAL_LABEL,''))) + Case When Level2.PORTAL_LABEL Is Null Then '' Else ' > ' End +
RTrim(LTrim(IsNull(Level1.PORTAL_LABEL,'')))
From PSPRSMDEFN Level1
Left Outer Join PSPRSMDEFN Level2
On Level2.PORTAL_OBJNAME = Level1.PORTAL_PRNTOBJNAME
And Level2.PORTAL_NAME = Level1.PORTAL_NAME
Left Outer Join PSPRSMDEFN Level3
On Level3.PORTAL_OBJNAME = Level2.PORTAL_PRNTOBJNAME
And Level3.PORTAL_NAME = Level2.PORTAL_NAME
Left Outer Join PSPRSMDEFN Level4
On Level4.PORTAL_OBJNAME = Level3.PORTAL_PRNTOBJNAME
And Level4.PORTAL_NAME = Level3.PORTAL_NAME
Left Outer Join PSPRSMDEFN Level5
On Level5.PORTAL_OBJNAME = Level4.PORTAL_PRNTOBJNAME
And Level5.PORTAL_NAME = Level4.PORTAL_NAME
Left Outer Join PSPRSMDEFN Level6
On Level6.PORTAL_OBJNAME = Level5.PORTAL_PRNTOBJNAME
And Level6.PORTAL_NAME = Level5.PORTAL_NAME
Left Outer Join PSPRSMDEFN Level7
On Level7.PORTAL_OBJNAME = Level6.PORTAL_PRNTOBJNAME
And Level7.PORTAL_NAME = Level6.PORTAL_NAME
Left Outer Join PSPRSMDEFN Level8
On Level8.PORTAL_OBJNAME = Level7.PORTAL_PRNTOBJNAME
And Level8.PORTAL_NAME = Level7.PORTAL_NAME
Left Outer Join PSPRSMDEFN Level9
On Level9.PORTAL_OBJNAME = Level8.PORTAL_PRNTOBJNAME
And Level9.PORTAL_NAME = Level8.PORTAL_NAME
Left Outer Join PSPRSMDEFN Level10
On Level10.PORTAL_OBJNAME = Level9.PORTAL_PRNTOBJNAME
And Level10.PORTAL_NAME = Level9.PORTAL_NAME
Left Outer Join PSPRSMDEFN Level11
On Level11.PORTAL_OBJNAME = Level10.PORTAL_PRNTOBJNAME
And Level11.PORTAL_NAME = Level10.PORTAL_NAME
Left Outer Join PSPRSMDEFN Level12
On Level12.PORTAL_OBJNAME = Level11.PORTAL_PRNTOBJNAME
And Level12.PORTAL_NAME = Level11.PORTAL_NAME
Left Outer Join PSPRSMDEFN Level13
On Level13.PORTAL_OBJNAME = Level12.PORTAL_PRNTOBJNAME
And Level13.PORTAL_NAME = Level12.PORTAL_NAME
Where Level1.PORTAL_NAME = 'EMPLOYEE'
And (Level1.PORTAL_URI_SEG2 In (Select PNLGRPNAME From PSPNLGRPDEFN Where LOWER(DESCR) Like '%component description here%')
Or Level1.PORTAL_URI_SEG2 In (Select PNLGRPNAME From PSPNLGROUP Where PNLNAME In
(Select PNLNAME From PSPNLDEFN Where LOWER(DESCR) Like '%page description here%'))
Or LOWER(Level1.PORTAL_LABEL) Like '%link label here%')