How to Skip / Bypass a PeopleSoft search record

There are some situations for example Self Service Pages where we want the user to directly land on the require page by skipping the search page information .

We can bypass the component search page in two  different ways.

1. Set Installation table as component search record

 Any record with no search keys is being set as component search record is called Installation table .

2.  we can write the following peoplecode on SearchInit event of  component level search record


     SetSearchDialogBehavior(0);

For Example , If the page extracts information of the user by taking emplid , we can write the following peoplecode on above mentioned event 

     Search_Record.EMPLID = %EmployeeId ;
     SetSearchDialogBehavior(0);
 

 

Send SMS Using Peoplecode

Local String &MobileNumbr = "03434605956" ;

Local String &rand1 , &MSG_ID , &reqStr ;

/* Random Msg ID generation */

   &rand1 = Int(Rand() * 10);
   &rand2 = Int(Rand() * 10);
   &rand3 = Int(Rand() * 10);
   &rand4 = Int(Rand() * 10);
   &rand5 = Int(Rand() * 10);
   &rand6 = Int(Rand() * 10);
   &rand7 = Int(Rand() * 25) + 65;
   &rand8 = Int(Rand() * 25) + 65;
  
   &MSG_ID = &rand1 | &rand2 | &rand3 | &rand7 | &rand4 | &rand5 | &rand6 | &rand8;

   &reqStr = "<?xml version=""1.0"" encoding=""utf-8""?>  <soapenv:Envelope xmlns:soapenv=";
   &reqStr = &reqStr | """http://schemas.xmlsoap.org/soap/envelope/""";
   &reqStr = &reqStr | " xmlns:urn=" | """urn:webService""";
   &reqStr = &reqStr | "> <soapenv:Header>  <wsse:Security soapenv:mustUnderstand=""1"" ";
   &reqStr = &reqStr | "xmlns:wsse=";

   &reqStr = &reqStr | """http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-   secext-1.0.xsd""";

   &reqStr = &reqStr | ">   <wsse:UsernameToken> <wsse:Username>PS</wsse:Username> <wsse:Password Type=";

   &reqStr = &reqStr | """http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText""";

   &reqStr = &reqStr | ">PS</wsse:Password> </wsse:UsernameToken> </wsse:Security> </soapenv:Header> <soapenv:Body><urn:sendSMS><arrayData>  <!--You may enter the following 6 items in any order-->  <mobile>";

   &reqStr = &reqStr | &MOBILE;
   &reqStr = &reqStr | "</mobile> <password>password1234</password> <sender>";
   &reqStr = &reqStr | &SENDER;
   &reqStr = &reqStr | "</sender> <numbers>";
   &reqStr = &reqStr | &MOBILE_PHONE;
   &reqStr = &reqStr | "</numbers> <msg>";
   &reqStr = &reqStr | &BODY;
   &reqStr = &reqStr | "</msg> <msgId>";
   &reqStr = &reqStr | &MSG_ID;
   &reqStr = &reqStr | "</msgId></arrayData> </urn:sendSMS>   </soapenv:Body>
</soapenv:Envelope>";

 &length = Len(&BODY);
  
   If &length <= 156 Then
     
     
      /* (1) Create Request Message from Service Operation */
      &reqMsg = CreateMessage(Operation.SENDSMS10005, %IntBroker_Request);

      /* (2) Create XML Document from Request XML Text String */
      &inXml = CreateXmlDoc(&reqStr);

      /* (3) Set XML Document on Request Message */
      &reqMsg.SetXmlDoc(&inXml);

      /* (4) Invoke SyncRequest for Request Message */
      &respMsg = %IntBroker.SyncRequest(&reqMsg);

      /* (5) Get XML Document from Response Message */
      &outXml = &respMsg.GetXmlDoc();
     
      &userProfile = &outXml.DocumentElement;
      Local number &SMSReturn = &userProfile.GetElementsByTagName("return")[1].NodeValue;


Else
      &toLong = "Message body is too long.";
      MessageBox(0, "", 0, 0, "Message Not Sent." | &toLong);
   End-If;
  
  
  
   If  &SMSReturn = 1 Then
      MessageBox(0, "", 0, 0, "Message Sent.");
     
   Else
      If &SMSReturn <> 1 Then
         &Return = "2";
         MessageBox(0, "", 0, 0, "Message Sending Failed. And Return Code Is = " | &SMSReturn);
      End-If;
   End-If;
  

XML Publisher

Download the XML Publisher Toolbar

–  Log In to PeopleSoft
–  Navigate : Home> Reporting Tools> XML Publisher> Setup> Design Helper

– Unzip & Execute “xmlp_desktop.zip”

– Installs the XML Toolbar in Microsoft Word

Create Report Category

Navigate: Home> Reporting Tools> XML Publisher> Setup> Report Category

Identify / Add New Data Source 

–  PSQuery is preconfigured XML Data Source
– Other Data Sources: Rowset, XML File, XMLDoc Object -Require Additional Config
– Navigate: Home> Reporting Tools> XML Publisher> Data Source

Enter Data Source description, and object owner

Generate & Save Sample Data File and Schema File

Creating an XML Publisher Report

Create Report template in Microsoft Word

– Import XML file generated from the Data Source creation

– Import XSD file generated from the Data Source creation

Insert Table from Wizard

Select table characteristics

Select fields from Data Source for Report

Select table criteria

Edit Field names prior to table generation

Initial table generated from wizard

Portal Registering of Component

Navigation : PeopleTools > Portal > Structure and Content

First, navigate to the folder where you want to add the content reference. Then, click "Add Content Reference" at the bottom of the page.

If you are editing a content reference, you must click on the "Edit" link next to the content reference to change any information.

Import / Export Message Catalog

Export Message Catalog


SET NO TRACE;
SET LOG C:\temp\EXPORT_MSGCATLG.log;
SET OUTPUT C:\temp\MSGCATLG_DATA.dat;

Export PSMSGSETDEFN Where MESSAGE_SET_NBR=13000;
Export PSMSGCATDEFN Where MESSAGE_SET_NBR=13000 And ((MESSAGE_NBR > 0 And MESSAGE_NBR <650));

Export PSMSGSETLANG Where MESSAGE_SET_NBR=13000;
Export PSMSGCATLANG Where MESSAGE_SET_NBR=13000 And ((MESSAGE_NBR > 0 And MESSAGE_NBR <650));



Import Message Catalog
 
SET NO TRACE;
SET LOG C:\temp\IMPORT_MSGCATLG.log;
SET INPUT C:\temp\MSGCATLG_DATA.dat;

IMPORT PSMSGSETDEFN;
IMPORT PSMSGCATDEFN;
IMPORT PSMSGSETLANG;
IMPORT PSMSGCATLANG;

Data Mover Script Templates

Export Record data to File

SET OUTPUT C:\TEMP\PROG_PLN_DATA.dat
EXPORT PS_ACAD_PROG_TBL;
EXPORT PS_ACAD_PLAN_TBL;

Import Record data from File to System

SET IGNORE_DUPS;
SET INPUT C:\TEMP\PROG_PLN_DATA.dat
IMPORT PS_ACAD_PROG_TBL;
IMPORT PS_ACAD_PLAN_TBL;

‘IGNORE_DUPS’ command will ignore the duplicate rows with the same key structure, but it will not check if there is any data change in non key fields.

OR

SET INPUT C:\TEMP\PROG_PLN_DATA.dat
REPLACE_DATA PS_ACAD_PROG_TBL;
REPLACE_DATA PS_ACAD_PLAN_TBL;


‘REPLACE_DATA’ (without ‘IGNORE_DUPS) command will delete the existing data and re-insert the newly available data in the table .



OR

SET INPUT C:\TEMP\PROG_PLN_DATA.dat
REPLACE_ALL PS_ACAD_PROG_TBL;
REPLACE_ALL PS_ACAD_PLAN_TBL;


‘REPLACE_ALL’ (without ‘IGNORE_DUPS) command will drop the table / indexes and then recreate the table and insert  the newly available data in the table .

Random Password Generation

 In PeopleCode, we can use the Rand function to generate a random number/password  greater than or equal to 0 and less than 1. For example:
&password="ABC" | Int(Rand( )*1000);

In case we want to generate random password using SQL ( say in Set-based processing) we can use DBMS_RANDOM package in Oracle.
SELECT DBMS_RANDOM.STRING('P', 10) FROM PS_INSTALLATION;

Read Excel File Using Java in peoplecode

I have placed the jxl.jar file here "C:\PT8.53\appserv\classes" ,  here is the code



Local JavaObject &workbook = GetJavaClass("jxl.Workbook");
Local Sting &sFilePath = "C:\Users\Administrator\Desktop\munib\Book1.xls";


Local string &FILE_NAME = &sFilePath;
Local JavaObject &file = CreateJavaObject("java.io.File", &FILE_NAME);
Local JavaObject &WB = &workbook.getWorkbook(&file);
Local JavaObject &sheet = &WB.getSheet(0);

For &i = 0 To &sheet.getRows() - 1
   try
      MessageBox(0, "", 0, 0, "" | &sheet.getCell(0, &i).getContents());
   
   
   
   catch Exception &e
      MessageBox(0, "Excep-----", 0, 0, &e.ToString());
   end-try;
 
End-For;

Read Excel File Using Peoplecode

Local object &oWorkApp, &oWorkBook;
Local string &sFilePath ;
&sFilePath = "C:\Users\Administrator\Desktop\munib\Book1.xls";


&oWorkApp = CreateObject("COM", "Excel.Application");
&oWorkApp.DisplayAlerts = "False";
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");


&oWorkBook.Open(&sFilePath);
&oWorkSheet = &oWorkApp.Worksheets("Sheet1");
&rowcount = &oWorkSheet.Usedrange.rows.count;
&cellcount = &oWorkSheet.Usedrange.cells.count;
&columncount = &oWorkSheet.Usedrange.columns.count;

For &i = 1 To &rowcount
         For &j = 1 To &columncount;
      &data = &oWorkSheet.Cells(&i, &j).Value;
      MessageBox(0, "", 0, 0, "data in" | &i | " row " | &j | " column :" | &data);
   
   End-For;
End-For;

&oWorkApp.ActiveWorkBook.Close();
&oWorkApp.DisplayAlerts = "True";
&oWorkApp.Quit();

Inserting Rows Using PeopleCode

Record Insert method

 If you do a single insert, use the Record Insert method.

The following is an example of using the Record Insert method:

&REC = CreateRecord(Record.GREG); 
&REC.DESCR.Value = "Y" | &I; 
&REC.EMPLID.Value = &I; 
&REC.Insert();

SQL object

 If you are in a loop and,therefore, calling the insert more than once, use the SQL object. The SQL object uses dedicated cursors and, if the database you are working with supports it, bulk insert.

A dedicated cursor means that the SQL gets compiled only once on the database, so PeopleTools looks for the meta-SQL only once. This can increase performance.

For bulk insert, inserted rows are buffered and sent to the database server only when the buffer is full or a commit occurs. This reduces the number of round-trips to the database. Again, this can increase performance.

The following is an example using a SQL object to insert rows:

&SQL = CreateSQL("%INSERT(:1)"); 
&REC = CreateRecord(Record.GREG); 
&SQL.BulkMode = True; 
For &I = 1 to 10 
   &REC.DESCR.Value = "Y" | &I; 
   &REC.EMPLID.Value = &I; 
   &SQL.Execute(&REC); 
End-For;

PeopleCode-Update Statement

Local  Record &REC ;
Local String &UPDSTRING;


Function getUpdateString(&REC As Record) Returns string;

For &COUNT = 1 To &REC.FieldCount;

&UPDSTRING = "Update %TABLE(:1) set ";

&UPDSTRING = &UPDSTRING | &REC.GetField(&COUNT).Name | " = ";
&UPDSTRING = &UPDSTRING | &REC.GetField(&COUNT).Value | " ";


End-For;

 &UPDSTRING = &UPDSTRING | " where %KeyEqual(:1)";
 Return &UPDSTRING;
End-Function;

-------------------------------------------------------------

Local Record &REC;
Local Sql   &SQL , SQL_UP ;


&REC = CreateRecord(Record.PSOPRDEFN);

&SQL = CreateSQL("%SELECTALL(:1) WHERE OPRID =:2", &REC, &USERID);
&SQL_UP = CreateSQL("%Update(:1)");
While &SQL.Fetch(&REC)

&SQL_UP.EXECUTE(&REC);
End-While;
&SQL_UP.CLOSE();

Setting Variables to Null in PeopleCode

 String

Always use space to assign a null value to a string.

 Local String &Test_Str = " ";

 Number

 Use 0 (zero) to assign a null value to a number.

  Local Number &Test_Num = 0;

 Date


If you try to assign a date variable a null value you will get an error saying that the left and right sides of the assignment are not compatible.

For instance, the following PeopleCode would error out: 


Local Date &Test_DT =NULL;

To perform this kind of assignment, you may use the Date function in the following way:

Local Date &Test_DT;

&Test_DT = DATE(0);

The Date function takes a number in the form YYYYMMDD and returns a corresponding Date value. If the date is invalid, Date displays an error message.

&Test_DT = Date(19970713);

 

Peoplecode-Changing color of Field Text

Here is the situation .

I am making a check that Interview Date should not be empty.

Peoplecode on Field Change Event of the Interview Date Field


TIME_REC.INTRVW_DT_FLD.Style = "PSEDITBOX";



Peoplecode on Field Change Event of the page save button
If (TIME_REC.INTRVW_DT_FLD.Value = "") Then
  
   SetCursorPos(Page.PageName , TIME_REC.INTRVW_DT_FLD , CurrentRowNumber());
   TIME_REC.INTRVW_DT_FLD.Style = "PSERROR";
   &MsgTXt = MsgGetText(20000, 76, "Interview Date Should not be empty.");
   Error (&MsgTXt);
End-If;

Rowsets - Accessing data at scroll level 2

Local Rowset &rsLvl1, &rsLvl2, &rsLvl0;
Local Field &EXTERNAL_SYSTEM_ID;

Local Message &MSG;
Local Rowset &RS1;


/* Get Level 0 Rowset */
&rsLvl0 = GetLevel0();

/* Get Level 1 Rowset */
&rsLvl1 = GetLevel0()(1).GetRowset(Scroll.EXTERNAL_SYSKEY);


For &i = 1 To &rsLvl1.ActiveRowCount
  
   /* Get Level 2 Rowset */
   &rsLvl2 = &rsLvl1(&i).GetRowset(Scroll.EXTERNAL_SYSTEM);
  
  
   &EXTERNAL_SYSTEM_ID = &rsLvl2.GetRow(&i).GetRecord(Record.EXTERNAL_SYSTEM).GetField(Field.EXTERNAL_SYSTEM_ID).VALUE;
  
  
   REM  MessageBox(0, "", 0, 0, "" | &EXTERNAL_SYSTEM_ID )
  
 
  
  
End-For;

Grid Column Label is displaying field value

Here is the situation 

Data is being fetched in a rowset and further being displayed in a grid on a Result Display Page. Now
three of the field values have hyperlinks which opens the relative page in a new window. For example the Column is Interview ID, Field Value = 00001. Everything is fine. But the Column Label is also displaying the value "00001" instead of "Interview ID".
If there are multiple rows: 00001, 00002, 00003,00004 & 00005, then the value of the first row is being displayed in the column label

Solution


Local Grid &myGrid;
Local GridColumn &myGridColumn, &myGridColumn1;
&myGrid = GetGrid(Page.PageName, "RecordName");
&myGridColumn1 = &myGrid.GetColumn("GridColumnName");

If (%Language = "ENG") Then
   &myGridColumn1.Label = "Interview ID";
End-If;

If (%Language = "ARA") Then
     &myGridColumn1.Label = "رقم المقابلة";
End-If;

Difference between Transfer and TransferPage

TransferPage()

It is used to move from one page to another page within the same component .

Syntax

TransferPage (Page.PageName);   


Transfer()

It is used to move from one page to another page of different component .

Syntax


Transfer(new_instance, MENUNAME.menuname, BARNAME.barname, ITEMNAME.menu_itemname, PAGE.component_item_name, action [, keylist] [, AutoSearch]);


For example , I want to move from page A to page B of different Component .
these are the key fields , we have on page B
Institution , Acad_career ,Acad_prog , Strm , Acad_plan

Here is the peoplecode 

Local Record &record;

&record = CreateRecord(Record.PageA_Record);

&record.INSTITUTION.Value = PageA_Record.INSTITUTION.Value;
&record.ACAD_CAREER.Value = PageA_Record.ACAD_CAREER.Value;
&record.ACAD_PROG.Value   = PageA_Record.ACAD_PROG.Value;
&record.STRM.Value        = PageA_Record.STRM.Value;
&record.ACAD_PLAN.Value   = PageA_Record.ACAD_PLAN.Value;

Transfer( False, MenuName.KS_RU2_ADM_MN, BarName.ADMISSION, ItemName.KS_RU2_AD_INTERVW, Page.PageBName, "U", &record);

New Instance can be either true or false (boolean). True means you want to create a new window, false means that it will reuse the existing window.
To figure out the MENUNAME, BARNAME, ITEMNAME, PAGE query the PSAUTHITEM table. You can use the following query provided you know the menu and page names. The more you know, the better. Remember that this returns all permission lists (CLASSID) associated with menu item:

select * 
from PSAUTHITEM
where MENUNAME = 'YOUR_MENU'
and PNLITEMNAME = 'YOUR_PAGE'

The corresponding values in this table are:

• MENUNAME = MENUNAME
• BARNAME = BARNAME
• ITEMNAME = BARITEMNAME
• PAGE = PNLITEMNAME

You have the following actions available (%Action):

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

Make sure that the users will have the appropriate action available on the page you are transferring to.


 

Send Emails from Peoplecode

import PT_MCF_MAIL:*;
import PT_MCF_MAIL:MCFOutboundEmail;
import PT_MCF_MAIL:MCFEmail;

Local PT_MCF_MAIL:MCFOutboundEmail &email = create PT_MCF_MAIL:MCFOutboundEmail();
Local PT_MCF_MAIL:MCFBodyPart &html = create PT_MCF_MAIL:MCFBodyPart();
Local PT_MCF_MAIL:MCFBodyPart &attachment_Letter = create PT_MCF_MAIL:MCFBodyPart();
Local PT_MCF_MAIL:MCFMultipart &mp = create PT_MCF_MAIL:MCFMultipart();
Local string &NL = "<br>";
&go = True;

&email.Recipients = " munib776@yahoo.com";
&email.Subject = "Application Submission Confirmation - Test Email ";

/* Email Body */


&MAIL_GREET = "<font face ='sans-serif'>";
&MAIL_FOOTER = "</font>";


&smstxtL4 = "Copies of following Documents are required: " | &NL;

&smstxtL5 = "1.        Certificate & Mark sheet of SSC, O-Level Certificate or any other ." | &NL;
&smstxtL6 = "2.      Certificate & Mark Sheet of HSC, A-Level Certificate, High School Diploma,or any other (Equivalency from IBCC required)." | &NL;
&smstxtL7 = "3.        Bachelor's Degree and Mark sheet/ Transcript." | &NL;
&smstxtL8 = "4.        Master's Degree and Mark sheet/ Transcript." | &NL;
&smstxtL9 = "5.      National Identity Card or Form B." | &NL;
&smstxtL10 = "6.    Copy of Employment Certificate(s) for MBA (Weekend) candidates only. " | &NL;
&smstxtL11 = "7.    NTS GAT/Subject result for PhD Candidates." | &NL;
&smstxtL12 = "8.    Those  have A-Level or other International qualification must submit equivalency certificate issued by the IBCC. " | &NL;
&smstxtL13 = "9.    Two recent passport size photographs. " | &NL;
&smstxtL14 = "10.    Copies of documents must be attached starting with the last qualification." | &NL | &NL;
&smstxtL15 = "Note: Result awaiting candidates must submit Admit Card/ Statement of Entry." | &NL;









&smstxt = &MAIL_GREET | &smstxtL4 | &smstxtL5 | &smstxtL6 | &smstxtL7 | &smstxtL8 | &smstxtL9 | &smstxtL10 | &smstxtL11 | &smstxtL12 | &smstxtL13 | &smstxtL14 | &smstxtL15 | &MAIL_FOOTER;
&smstxt = Substitute(Substitute(Substitute(&smstxt, Char(13), " "), Char(10), " "), ",", " ");
&html.Text = &smstxt;









&html.ContentType = "text/html";
&html.Charset = "utf-8";


&mp.AddBodyPart(&html);
&email.MultiPart = &mp;

/* begin - uncomment for DEV only */
rem &email.SMTPServer = "10.71.32.74"; /* CSDVL SMTPServer */
rem &email.SMTPPort = 25; /*-- Usually this is 25 by default */
/* end - uncomment for DEV only */

Local integer &res = &email.Send();

Evaluate &res
When %ObEmail_Delivered
   /* every thing ok */
 
   &done = True;
   Break;
 
When %ObEmail_NotDelivered
   /*-- Check &email.InvalidAddresses, &email.ValidSentAddresses
and &email.ValidUnsentAddresses */
   &Err_Msg = &email.InvalidAddresses | "," | &email.ValidSentAddresses | "," | &email.ValidUnsentAddresses;
 
   &done = False;
   Break;
 
When %ObEmail_PartiallyDelivered
   /* Check &email.InvalidAddresses, &email.ValidSentAddresses
and &email.ValidUnsentAddresses; */
 
   &Err_Msg = &email.InvalidAddresses | "," | &email.ValidSentAddresses | "," | &email.ValidUnsentAddresses;
 
   &done = True;
   Break;
 
When %ObEmail_FailedBeforeSending
   /* Get the Message Set Number, message number;
Or just get the formatted messages from &email.ErrorDescription,
&email.ErrorDetails;*/
 
   &Err_Msg = &email.ErrorDescription | "," | &email.ErrorDetails;
 
   &done = False;
   Break;
End-Evaluate;

Check for Data Duplicates on a Grid

Here is a piece of code to prevent duplicate data on a specific field on a page grid. You can of course modify it to check for multiple fields or even the whole row.

/* Check for data duplicates on a grid. */
Local Row &row1, &row2;
Local number &r, &r1;

&rs = GetLevel0().GetRow(1).GetRowset(Scroll.grid_table);
For &r = 1 To &rs.ActiveRowCount
/*Get grid row*/
&row1 = &rs.GetRow(&r);
/*once we have a row, we are going to loop through the grid rows and make sure a specific field value is unique*/
For &r1 = 1 To &rs.ActiveRowCount
&row2 = &rs.GetRow(&r1);
/* if this is a different row, and the field_name value matches then throw an error*/
If &r1 <> &r And
&row1.grid_table.field_name.Value = &row2.grid_table.field_name.Value Then
MessageBox(0, "", 0, 0, "Error. Duplicate values are not allowed.");
End-If;
End-For;
End-For;

Auto Increment In peoplecode

In my case , I am incrementing the Interview ID



1. Execute this SQL statement 

CREATE SEQUENCE SYSADM.InterviewIDSeqNum    -- this is Your  variable
  START WITH 1
  MAXVALUE 99999999999999999999
  MINVALUE 1
  CYCLE
  CACHE 20 
  NOORDER;

2. Commit;

3. Write this peoplecode 

Local Number &InterviewID;

If %Mode = "A" Then

   SQLExec("SELECT InterviewIDSeqNum.NEXTVAL FROM DUAL", &InterviewID);

   RecordName.FieldName.Value = &InterviewID;

End-If;

Converting Character Datatype Field Value to Numeric Datatype Value in Peoplecode

Incrementing  interview ID  i.e  from  000001  to 000002

local number &i = 1;
local number   &max_interview_id ;
Local String &InterviewID ;


&InterviewID  = NumberToString("%06", &i );

/* I have used value()  from character to numeric data type conversion  */

&max_interview_id = Value(LTrim(RTrim(&InterviewID)));
&InterviewID = NumberToString("%06", &max_interview_id + 1);
  
  

Converting Numeric Datatype Field Value to Character Datatype Value in Peoplecode

I am trying to create an interview ID  i.e  000001

local number &i = 1;

Local String &InterviewID ;

/* I have used NumberToString()  from numeric to character  data type conversion  */

&InterviewID  = NumberToString("%06", &i );

SQL Query to get Component Navigation in Portal

SELECT a.PORTAL_URI_SEG2 as component
, E.PORTAL_LABEL || '>' || D.PORTAL_LABEL || '>' ||C.PORTAL_LABEL ||
'>' || B.PORTAL_LABEL || '>' || A.PORTAL_LABEL as Menu_Path
, A.PORTAL_URLTEXT
, A.PORTAL_ISPUBLIC as Active
, A.LASTUPDOPRID
, A.LASTUPDDTTM
FROM PSPRSMDEFN A, PSPRSMDEFN B, PSPRSMDEFN C, PSPRSMDEFN D, PSPRSMDEFN E
WHERE 1=1
AND A.PORTAL_URI_SEG2 = 'UR Component Name '
AND A.PORTAL_PRNTOBJNAME = B.PORTAL_OBJNAME(+)
AND B.PORTAL_PRNTOBJNAME = C.PORTAL_OBJNAME(+)
AND C.PORTAL_PRNTOBJNAME = D.PORTAL_OBJNAME(+)
AND D.PORTAL_PRNTOBJNAME = E.PORTAL_OBJNAME(+)
AND (E.PORTAL_NAME IS NULL OR E.PORTAL_NAME = 'EMPLOYEE')
AND (A.PORTAL_NAME = 'EMPLOYEE' OR A.PORTAL_NAME IS NULL )
AND (B.PORTAL_NAME = 'EMPLOYEE' OR B.PORTAL_NAME IS NULL )
AND (C.PORTAL_NAME = 'EMPLOYEE' OR C.PORTAL_NAME IS NULL )
AND (D.PORTAL_NAME = 'EMPLOYEE')

Exclude PeopleSoft Authentication Token

http://peoplesoft.wikidot.com/exclude-peoplesoft-authentication-token

User not authorized to invoke service operation

http://peoplesoft.wikidot.com/exclude-peoplesoft-authentication-token

Code to make Comma Seprated String in peoplecode

&UserRoles = CreateArray("");
Local array of string &roles = %Roles;
Local string &rolesStr = "";
If &roles.Len >= 1 Then
   &rolesStr = "'" | &UserRoles [1] | "'";
End-If;
For &i = 2 To &roles.Len
   &rolesStr = &rolesStr | ", ";
   &UserRoles [&i] = &roles [&i];
   &rolesStr = &rolesStr | "'" | &UserRoles [&i] | "'";
End-For;





If %Language = %Language_Base Then
  
  
   FORM_ADD_VW.FORM_TYPE.SqlText = "SELECT A.FORM_TYPE, A.PORTAL_LABEL FROM PS_KS_FORM_TYPE_VW A  WHERE A.ROLEUSER IN (" | &rolesStr | ") AND A.FORM_TYPE_STATUS ='A' AND A.PORTAL_NAME='" | %Portal | "' AND A.EFFDT_FROM <= %CurrentDateIn AND (A.EFFDT_TO is null or A.EFFDT_TO >= %CurrentDateIn)";
  
  
Else
  
   FORM_ADD_VW.FORM_TYPE.SqlText = "SELECT A.FORM_TYPE, B.PORTAL_LABEL FROM PS_KS_FORM_TYPE_VW A , PS_FORM_TYPE_LG B WHERE A.ROLEUSER IN (" | &rolesStr | ") AND  A.FORM_TYPE_STATUS ='A' AND A.PORTAL_NAME='" | %Portal | "' AND A.EFFDT_FROM <= %CurrentDateIn AND (A.EFFDT_TO is null or A.EFFDT_TO >= %CurrentDateIn) AND A.FORM_TYPE=B.FORM_TYPE AND B.LANGUAGE_CD='" | %Language | "'";
  
  
End-If;

Campus Solutions Tables

http://peoplesoft.wikidot.com/campus-solutions-tables

Integration Broker Messages Stuck in New State

http://peoplesofttutorial.com/integration-broker-messages-stuck-in-new-state/

http://kiran0208.wordpress.com/2012/10/26/integration-broker-troubleshooting-steps-part-1/

List of PeopleSoft Integration Broker Tables

– Message Nodes

PSMSGNODEDEFN;
PSNODEDEFNLANG;
PSCONNECTSTRING;
PSNODEURITEXT;
PSNODECONPROP;
PSNODCONPRPLANG;
PSNODELOCKDOWN;
PSNODEPROP;
PSNODEPROPLANG;
PSNODESDOWN;
PSTRUSTNODES;

– Message Channels

PSCHNLDEFN;
PSCHNLDEFNLANG;
PSCHNLNODE;
PSSUBCHNL;

– Message Definitions

PSMSGATTR;
PSMSGDEFN;
PSMSGDEFNLANG;
PSMSGFLDOVR;
PSMSGPARTS;
PSMSGREC;
PSMSGVER;

– App Messaging

PSAPMSGARCHPC;
PSAPMSGARCHPD;
PSAPMSGARCHPH;
PSAPMSGARCHSC;
PSAPMSGARCHTMP;
PSAPMSGPUBATTR;
PSAPMSGPUBCERR;
PSAPMSGPUBCERRP;
PSAPMSGPUBCLOCK;
PSAPMSGPUBCON;
PSAPMSGPUBCSYNC;
PSAPMSGPUBDATA;
PSAPMSGPUBERR;
PSAPMSGPUBERRP;
PSAPMSGPUBHDR;
PSAPMSGPUBINST;
PSAPMSGPUBLOCK;
PSAPMSGPUBSYNC;
PSAPMSGSUBCERR;
PSAPMSGSUBCERRP;
PSAPMSGSUBCLOCK;
PSAPMSGSUBCON;
PSAPMSGSUBCSYNC;
PSAPMSGSUBPRCID;
PSAPMSGXTB;

– Services
PSSERVICE;
PSSERVICELANG;
PSSERVICEOPR;
PSSERVICEUDDI;

– Service Operation
PSOPERATION;
PSOPERATIONLANG;
PSOPERATIONURI;

– Service Operation Routings
PSIBRTNGDEFN;
PSRTNGDFNLANG;
PSRTNGDFNCONPRP;
PSRTNGDFNPROP;
PSRTNGDFNPROPLG;
PSIBRTNGSUBDEFN;
PSRTNGDFNPARM;

– Service Operation Handlers
PSOPRHDLR;
PSOPRHDLRLANG;
PSOPERATIONAC;
PSOPERATIONAE;
PSOPERATIONCI;
PSOPERATIONDMS;

– Service Operation Versions
PSOPRVERDFN;
PSOPRVERDFNLANG;
PSOPRVERDFNPARM;
PSOPRVERQUEPARM;
PSOPRGENRTGPARM;
PSOPRVERDEPLOY;
PSOPRVERDPLYVER;

– Integration Broker Queues
PSQUEUEDEFN;
PSIBQUEUEINST;
PSQUEUEDEFNLANG;
PSQUEUEOVRD;
PSQUEUEPART;

– Integration Group
PSIBGROUPDEFN;
PSIBGROUPLANG;
PSIBSBGROUPLANG;
PSIBSRVGROUP;
PSIBSUBGROUP;
PSIBSUBSRVGROUP;

– Integration Broker Schema

PSIBSCMADFN;
PSIBSCMADATA;

– Integration Broker WSDL

PSIBWSDLDFN;
PSIBWSDLDATA;

– XML Publisher Data Source

PSXPDATASRC;
PSXPDATASRCLNG;
PSXPSCHEMAFLMN;
PSXPSMPLDTMN;

– XML Publisher File Definition

PSFILEDEFN;
PSFILEDATA;

– XML Publisher Report Definition

PSXPRPTDEFN;
PSXPRPTDEFNLNG;
PSXPRPTOUTFMT;
PSXPRPTPROP;
PSXPRPTSCOPEFLD;
PSXPRPTSRCHKEYS;
PSXPRPTTMPL;
PSXPRPTTMPLCTRL;
PSXPRPTVIEWER;

– XML Publisher Template Definition

PSXPTMPLDEFN;
PSXPTMPLDEFNLNG;
PSXPTMPLFILEDEF;
PSXPTMPLTRINFO;

– XML Service Info
PS_XMLSERVICEINFO;

WriteToLog function to output the snapshot to the Application Server log directory as a tracesql file.

http://www.peoplesoftwiki.com/peoplecode-record-dump

Asynchronous Integration broker service operation handler PeopleCode

http://manoranjangupta.blogspot.com/2013/05/asynchronous-integration-broker-service.html

PSCAMA Audit Actn Field Remains Blank Even if Row is Altered

PSCAMA record is tagged with each record in the message rowset and it would be appended only if we use CopyRowsetDelta method .

In my case , I am trying to trigger my incremental EIP for student external system ID , I have written the following code on my component save post change event and its working fine .


/*CRM4HE Online EIP */
Local Rowset &rsLvl1, &rsLvl2, &rsLvl0;
Local Field &fldLevel2;

Local Message &MSG;
Local Rowset &RS1;


/* Get Level 0 Rowset */
&rsLvl0 = GetLevel0();

/* Get Level 1 Rowset */
&rsLvl1 = GetLevel0()(1).GetRowset(Scroll.EXTERNAL_SYSKEY);


For &i = 1 To &rsLvl1.ActiveRowCount
  
   /* Get Level 2 Rowset */
   &rsLvl2 = &rsLvl1(&i).GetRowset(Scroll.EXTERNAL_SYSTEM);
  
  
 REM   &fldLevel2 = &rsLvl2.GetRow(&i).GetRecord(Record.EXTERNAL_SYSTEM).GetField(Field.EXTERNAL_SYSTEM_ID);
  
  REM  &Level2FldValue = &fldLevel2.Value;
   REM  MessageBox(0, "", 0, 0, "" | &Level2FldValue);
  
  
   &MSG = CreateMessage(Message.KS_BADGE_ID_SYNC);
   &MSG.CopyRowsetDelta(&rsLvl2, Record.EXTERNAL_SYSTEM, Record.EXTERNAL_SYSTEM);
   &MSG.Publish();
  
  
End-For;

Fetching array element 0: index is not in range 1 to 0. (180,252) EOL_PUBLISH.PUBDTL.GBL.default.190 0-01-01.Step05.OnExecute PCPC:14896 Statement:234

I have followed these steps to solve this issue .

1.  Execute this SQL statement 
     Select DEFAULTVER  from  PSMSGDEFN where  MSGNAME = 'UR MSG NAME ' ,
     IF the value of DEFAULTVER  is empty .

2. Execute this SQL statement

     UPDATE PSMSGDEFN SET DEFAULTVER = 'UR MSG VER. NO.' WHERE MSGNAME = 'UR MSG NAME' .


3. Commit.

4. Execute the Full Table Publish program EOP_PUBLUSHT.

FULLSYNC tables between PeopleSoft databases

http://www.cedarhillsgroup.com/knowledge-base/kbarticles/how-to-fullsync-tables-between-databases