Difference between SQLExec and SelectSQL in PeopleSoft

The general commands that we use to select data through People Code are
1. SQL Exec - To select different values of a single row.
2. Select SQL - To select multiple values of multiple rows.

1. SQL Exec Syntax 
SQLExec("select distinct EMPLID,ACAD_PROG from PS_ACAD_PROG where emplid = :1", "20163-19955", "20066", &getEmplidID,&getAcadProg); 
2. Select SQL Syntax

Local SQL &GRD_SQL;
Local Rowset &GRD_RS;
Local Record &rec;
Local Rowset &rsGrid, &rsDataSource;

&GRD_RS.Flush();
 &sSQLWhere = "acad_career = 'UGRD'";
&GRD_SQL = CreateSQL("select distinct emplid , phone from ps_hon_no_vw  where " | &sSQLWhere | "");

  While &GRD_SQL.Fetch(&emplid, &phone_nbr)
        
         &GRD_RS(1).PHN_NO_WRK.EMPLID.Value = &emplid;
        
         &GRD_RS(1).PHN_NO_WRK.PHONE.Value = &phone_nbr;
        
         &GRD_RS.InsertRow(0);
      End-While;
      &GRD_RS.DeleteRow(1);
      &GRD_SQL.Close();
   End-If;
 

Sending E-Mail through People Code

To send E-Mail through People Code, it is essential that your SMTP server is configured.

Use this code for sending E-Mail

      &line1 = "This is your Test E-Mail";
      &line2 = "Hi";
      &line3 = "How are you??";
      &line4 = "Hope you Learn it well";
      &line5 = "Sending by Saim-PeopleSoft";
     
     
     
      &MAIL_FLAGS = 0;
      &MAIL_TO = "saim_anwar@live.com";
      &MAIL_CC = "";
      &MAIL_BCC = "";
      &MAIL_SUBJECT = "My First E-Mail through PeopleCode";
      &MAIL_TEXT = "&line1 | Char(13) | &line2 | Char(13) | &line3 | Char(13) | Char(13) | &line5 | Char(13) | Char(13) | &line4 | Char(13) | Char(13) | Char(13) | "Kind Regards" | Char(13) | "Saim Anwer";
      rem &MAIL_TEXT = "Dear ";
     
      &MAIL_FILES = "";
      &MAIL_TITLES = "";
      &MAIL_FROM = "saim@testemail.com";
      &MAIL_SEP = ";";
      &CONTTYPE = "";
      &REPLYTO = "";
      &SENDER = "";
     
      &RET = SendMail(&MAIL_FLAGS, &MAIL_TO, &MAIL_CC, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TEXT, &MAIL_FILES, &MAIL_TITLES, &MAIL_FROM, &MAIL_SEP, &CONTTYPE, &REPLYTO, &SENDER);
     
      If &RET <> 0 Then
         MessageBox(0, "", 0, 0, "Return code from SendMail= " | &RET);
         /*Do error processing here*/
      End-If;
     

Page Transfer

Transfer Page Dynamically

use the following Peoplecode

Transfer( True, MenuName.IOBM_FINANCE_PROCESS, BarName.IOBM_PROCESS_FINANCE, ItemName.IOBM_USER_PROF_CP, Page.IOBM_USER_PROF_PG, "A");





True for new Window
False for Current Window

You can perform following actions(%Action)

• A = add
• U = update
• L = update/display all
• C = correction
• E = data entry

Reporting through Peoplecode - Report

import PSXP_RPTDEFNMANAGER:*;

Local Record &rcdQryPrompts;
Local string &LanguageCd, &MyTemplate, &MyReportName, &OutFormat;
Local date &AsOfDate;
Local number &count;

/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
/* Set XML Publisher report required parameters                            */
/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
&EMPID = IOBM_TRNSPT_REC.EMPLID.Value;
SQLExec("select max(strm) from ps_iobm_tran_setup t", &sstrm);






&LanguageCd = "ENG";
&AsOfDate = %Date;
&OutFormat = "PDF";

/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
/* Create a PDF using XML Report Definition MY_XMLP_DEMO                   */
/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
&MyReportName = "IOBM_TRANSP";
&MyTemplate = "IOBM_TRANSP_1";





/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
/* Hard code my State vale - would normally pull from a field on the page */
/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
/* Declare and Instantiate (construct) your Report Definition Object       */
/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
Local PSXP_RPTDEFNMANAGER:ReportDefn &oReportDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&MyReportName);

/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
/* Get a handle on your Report Definition                                  */
/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
WinMessage("PROD     MAIN   .. 1..1   " | &EMPID, 0);
&oReportDefn.Get();

/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
/* Since there is a prompt to the query used in this report, you need to   */
/* provide the value for the prompt                                        */
/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
rem WinMessage("PROD     MAIN   .. 1..1 ..1  " | &EMPID, 0);
&rcdQryPrompts = &oReportDefn.GetPSQueryPromptRecord();
If Not &rcdQryPrompts = Null Then
   &oReportDefn.SetPSQueryPromptRecord(&rcdQryPrompts);
   /* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
   /* Provide a value to the State Prompt                                  */
   /* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */

   &rcdQryPrompts.EMPLID.Value = &EMPID;

  
End-If;
rem WinMessage("befor commit  " | &EMPID, 0);

/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
/* Kick of the report process                                              */
/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
CommitWork();

&oReportDefn.ProcessReport(&MyTemplate, &LanguageCd, &AsOfDate, &OutFormat);

CommitWork();
/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
/* CommitWork must be called prior to displaying the output since the      */
/* application package performed work and SQL statements. If you do        */
/* not commit the work performed to this point you will receive an         */
/* error like “Think-time PeopleCode event (ViewAttachment), but a SQL     */
/* update has occurred in the commit interval. (2, 148)”                   */
/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */


/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
/* Display Report to the user                                              */
/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
/*
Local String &reportPath = GetEnv("PS_SERVDIR") | "\files\reports";
Local String &folderName = "General";
Local string &serverName = "PSNT";

psreports
*/
rem &FilePath = GetEnv("PS_SERVDIR");
rem MessageBox(0, "", 31000, 1, "File Path = " | &FilePath, "File Path = " | &FilePath);
rem WinMessage("befor commit  and lst one " | &EMPID, 0);
&oReportDefn.DisplayOutput();


&oReportDefn.PrintOutput("%DESTPRINTER%");

How to call user profile CI through Peoplecode on Field Change

/* ===>

This is a dynamically generated PeopleCode template to be used only as a helper

to the application developer.

You need to replace all references to '[*]' OR default values with  references to

PeopleCode variables and/or a Rec.Fields. */







Local ApiObject &oSession, &oIobmUserProfile;

Local ApiObject &oEmailAddressesCollection, &oEmailAddresses;

Local ApiObject &oIDTypesCollection, &oIDTypes;

Local ApiObject &oAttributesCollection, &oAttributes;

Local ApiObject &oRolesCollection, &oRoles;

Local ApiObject &oRouteControlsCollection, &oRouteControls;



Function errorHandler()
 
   Local ApiObject &oPSMessageCollection, &oPSMessage;
 
   Local number &i;
 
   Local string &sErrMsgSetNum, &sErrMsgNum, &sErrMsgText, &sErrType;
 
 
 
   &oPSMessageCollection = &oSession.PSMessages;
 
   For &i = 1 To &oPSMessageCollection.Count
    
      &oPSMessage = &oPSMessageCollection.Item(&i);
    
      &sErrMsgSetNum = &oPSMessage.MessageSetNumber;
    
      &sErrMsgNum = &oPSMessage.MessageNumber;
    
      &sErrMsgText = &oPSMessage.Text;
    
      &fileLog.WriteLine(&sErrType | " (" | &sErrMsgSetNum | "," | &sErrMsgNum | ") - " | &sErrMsgText);
      rem  WinMessage(&sErrType | " (" | &sErrMsgSetNum | "," | &sErrMsgNum | ") - " | &sErrMsgText);
   End-For;
 
   rem ***** Delete the Messages from the collection *****;
 
   &oPSMessageCollection.DeleteAll();
 
End-Function;



try
 
   rem ***** Set the Log File *****;
 
 
   rem ***** Get current PeopleSoft Session *****;
 
   &oSession = %Session;
 
 
 
   rem ***** Set the PeopleSoft Session Error Message Mode *****;
 
   rem ***** 0 - None *****;
 
   rem ***** 1 - PSMessage Collection only (default) *****;
 
   rem ***** 2 - Message Box only *****;
 
   rem ***** 3 - Both collection and message box *****;
 
   &oSession.PSMessagesMode = 3;
 
 
 
   rem ***** Get the Component Interface *****;
 
   &oIobmUserProfile = &oSession.GetCompIntfc(CompIntfc.IOBM_USER_PROFILE);
 
   If &oIobmUserProfile = Null Then
    
      errorHandler();
    
      throw CreateException(0, 0, "GetCompIntfc failed");
    
   End-If;
 
 
 
   rem ***** Set the Component Interface Mode *****;
 
   &oIobmUserProfile.InteractiveMode = False;
 
   &oIobmUserProfile.GetHistoryItems = True;
 
   &oIobmUserProfile.EditHistoryItems = False;
 
 
 
   rem ***** Set Component Interface Get/Create Keys *****;
   &userid = Substring(IOBM_USER_PROF.EMPLID.Value, 7, 11);
   &oIobmUserProfile.UserID = &userid;
 
 
 
 
   rem ***** Execute Get *****;
   rem WinMessage(&a);
   <*
   If Not &oIobmUserProfile.Get() Then
    
      rem ***** No rows exist for the specified keys.*****;
      WinMessage("abc");
      errorHandler();
    
      throw CreateException(0, 0, "Get failed");
    
   End-If;
   *>
 
 
   rem ***** Execute Create ******;
   rem &a = &oIobmUserProfile.Create();
   rem WinMessage(&a);
   rem for creating new profile;
   If Not &oIobmUserProfile.Create() Then;
    
      rem ***** Unable to Create Component Interface for the Add keys provided. *****;
    
      errorHandler();
    
      throw CreateException(0, 0, "Create failed");
    
    
   End-If;
 
 
 
 
 
   rem ***** Begin: Get/Set Component Interface Properties *****;
 
   rem ***** Get/Set Level 0 Field Properties *****;
 
   &oIobmUserProfile.UserID = &userid;
   SQLExec("select distinct get_empl_name(emplid) from PS_ACAD_PROG where emplid = :1", IOBM_USER_PROF.EMPLID.Value, &Name);
   &oIobmUserProfile.UserDescription = &Name;
 
   &oIobmUserProfile.PrimaryPermissionList = "HCPPALL";
 
   &oIobmUserProfile.RowSecurityPermissionList = "HCDPALL";
 
   &oIobmUserProfile.OPERPSWD = &userid;
 
   &oIobmUserProfile.Encrypted = "1";
 
   &oIobmUserProfile.SymbolicID = "SYSADM1";
 
   &oIobmUserProfile.LanguageCode = "ENG";
 
   rem &oIobmUserProfile.MultiLanguageEnabled = [*];
 
   &oIobmUserProfile.CurrencyCode = "PKR";
 
   rem &oIobmUserProfile.AccountLocked = [*];
 
   &oIobmUserProfile.ProcessProfilePermissionList = "HCSPPRFL";
 
   &oIobmUserProfile.NavigatorHomePermissionList = "HCSPNAVHP";
 
   rem &oIobmUserProfile.FailedLogins = [*];
 
   rem &oIobmUserProfile.ExpertEntry = [*];
 
   rem &oIobmUserProfile.Opertype = [*];
 
   rem &oIobmUserProfile.UserIDAlias = [*];
 
   rem &oIobmUserProfile.AllowSwitchUser = [*];
 
   &oIobmUserProfile.OPERPSWDCONF = &userid;
 
   rem &oIobmUserProfile.ReassignUserID = [*];
 
   rem &oIobmUserProfile.ReassignWork = [*];
 
   rem &oIobmUserProfile.MPDEFAULMP = [*];
 
   rem &oIobmUserProfile.WorklistUser = [*];
 
   rem &oIobmUserProfile.EmailUser = [*];
 
   rem &oIobmUserProfile.AlternateUserID = [*];
 
   rem &oIobmUserProfile.SupervisingUserID = [*];
 
   rem &oIobmUserProfile.EffectiveDateFrom = [*];
 
   rem &oIobmUserProfile.EffectiveDateTo = [*];
 
 
 
   rem ***** Set/Get EmailAddresses Collection Field Properties -- Parent: PS_ROOT Collection *****;
 
   &oEmailAddressesCollection = &oIobmUserProfile.EmailAddresses;
 
   Local integer &i131;
 
   For &i131 = 1 To &oEmailAddressesCollection.Count;
    
      &oEmailAddresses = &oEmailAddressesCollection.Item(&i131);
    
    
      &oEmailAddresses.EmailType = "BUS";
    
    
      &oEmailAddresses.EmailAddress = "std_" | &userid | "@iobm.edu.pk";
    
    
      &oEmailAddresses.PrimaryEmail = "Y";
    
   End-For;
 
 
 
   rem ***** Set/Get IDTypes Collection Field Properties -- Parent: PS_ROOT Collection *****;
 
   &oIDTypesCollection = &oIobmUserProfile.IDTypes;
 
   Local integer &i135;
 
   For &i135 = 1 To &oIDTypesCollection.Count;
    
      &oIDTypes = &oIDTypesCollection.Item(&i135);
    
    
      &oIDTypes.IDType = "EMP";
    
    
    
      rem ***** Set/Get Attributes Collection Field Properties -- Parent: IDTypes Collection *****;
    
      &oAttributesCollection = &oIDTypes.Attributes;
    
      Local integer &i237;
    
      For &i237 = 1 To &oAttributesCollection.Count;
       
         &oAttributes = &oAttributesCollection.Item(&i237);
       
       
         &oAttributes.Fieldname = "EMPLID";
       
       
         &oAttributes.Recname = "PERSONAL_DATA";
       
       
         &oAttributes.AttributeValue = IOBM_USER_PROF.EMPLID.Value;
       
       
         &oAttributes.AttributeName = "EmplID";
         rem  WinMessage(&oAttributesCollection.Count);
      End-For;
    
   End-For;
 
 
   rem ***** Set/Get Roles Collection Field Properties -- Parent: PS_ROOT Collection *****;
 
 
   &oRolesCollection = &oIobmUserProfile.Roles;
   &oRoles = &oRolesCollection.Item(1);
   &oRoles.RoleName = "CS - Student";
 
 
   &oRolesCollection = &oIobmUserProfile.Roles;
   &oRoles = &oRolesCollection.InsertItem(1);
   &oRoles.RoleName = "PeopleSoft Guest";
 
 
 
   rem ***** End: Get/Set Component Interface Properties *****;
 
 
   rem ***** Execute Save *****;
   rem &a = &oIobmUserProfile.Save();
 
   If Not &oIobmUserProfile.Save() Then;
    
      errorHandler();
    
      throw CreateException(0, 0, "Save failed");
   Else
      MessageBox(0, "", 0, 0, "" | "Profile Created");
   End-If;
 
 
 
   rem ***** Execute Cancel *****;
 
   rem If Not &oIobmUserProfile.Cancel() Then;
 
   rem     errorHandler();
 
   rem     throw CreateException(0, 0, "Cancel failed");
 
   rem End-If;
 
 
 
catch Exception &ex
 
   MessageBox(0, "", 0, 0, "" | &ex.ToString());
 
 
end-try;

Integration Broker - Consume 3rd Party Web Service (WSDL)

To connect PeopleSoft with any third party tool or to move data from PeopleSoft to any other Application you need to consume the web services (WSDL).
The WSDL is consumed and can be use by this method in PeopleSoft.

We are going to consume a Free WSDL of a calculator.

Note: Before performing the steps please make sure that Integration Broker is configured

Step 1: Setting up Node

First set up the node for your web service.
Open the Following Navigation.

 Click on WSDL_NODE and copy the node.

Enter your Node name and Save it.

Step 2:  Consume Web Service

Once you set-up the node, now you have to consume your web service.
Follow the navigation to consume your web service

Enter you WSDL Link here and click on next button
I used this open WSDL link(Many open WSDL are available on internet)
http://www.dneonline.com/calculator.asmx?WSDL

Your WSDL will be available here, click on next button

Select your desired service and click on next

Select the functions of WSDL you want to consume and click on next button

 

The the operations of your WSDL will set-up like this. Click on next button

Select use existing node and click on Finish Button

After clicking on finish button following screen will appear.

Step 3: Applying web service on your Node

Enter your WSDL name, click on search button and open your WSDL

 

Go to connectors tab and select connect ID : HTTPTARGET

and on Primary URL write the Primary URL address of your WSDL
http://www.dneonline.com/calculator.asmx

Now Ping the node.

If Success means your WSDL node is successfully connected.

Now save the node

Step 4: Setting up Operation Security.

Goto following navigation and search your service, by service name.

When you click on Search button, your operations will visible.

Open the operation and click on "Service Operation Security"

Add the following three services and service of your access page you want to give
1. HSCPSERVICE
2. HCSPSERVICETL
3. HCSPSOASERVICES
and save it.

Now click View Message and check the Message type

Message type should be Non Rowset Based, now click on save button

 Your WSDL is finally set-up on PeopleSoft, Now you have to utilize it according to your need.

Step  5: Calling WSDL using PeopleCode

Open the Application Designer and make fields according to your WSDL.
I made 2 Fields and a Button, and I used an static function + of WSDL.

 

 Now open the Button and click on View Record Peoplecode

and add the following Peoplecode on Field Change of your Button

Now Save the PeopleCode and button and Publish it on Front-End.

Now click on Get Response to check the response.

Finally your WSDL is Consumed and data is available on Peoplesoft, now you can utilize it as you like.

Thank You :)