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