Showing posts with label PeopleCode. Show all posts
Showing posts with label PeopleCode. Show all posts

XML publisher report from online button and SQL cursor

XML All in One Desk Reference for Dummies

An Introduction to Object COBOL
Requirement:
Build and run xml publisher report from online button

Solution:
Button fieldchange event Peoplecode written
Xmlp Report uses PS Query which uses three tables joined together and builds output

One of the tables in the query is temporarily populated using the scroll and rowset properties.

Below is the whole logic.
  
import PSXP_RPTDEFNMANAGER:*;

Component string &c_transfer_sodca;
Local PSXP_RPTDEFNMANAGER:ReportDefn &oRptDefn;
Local Record &rcdQryPrompts, &rcdQryRunParms, &REC;
Local SQL &sqlSelectQryParms, &SQL;
Local string &reportid, &sDirName, &print, &ssn;
Local number &j;


SQLExec("delete from ps_nc_sodca_tmp1");


&SQL = CreateSQL("%INSERT(:1)");

&REC = CreateRecord(Record.<temp table>);

&SQL.BulkMode = True;

For &j = 1 To ActiveRowCount(Scroll.<view name>)
  
   &ssn = FetchValue(Scroll.<view name>, &j, <view name>.SSN);
   &print = FetchValue(Scroll.<view name>, &j, <view name>.NC_PRINT);
  
   rem MessageBox(0, "", 0, 0, "ssn = %1", &ssn);
   rem MessageBox(0, "", 0, 0, "Print = %1", &print);
   &REC.OPRID.Value = %OperatorId;
   &REC.SSN.Value = &ssn;
   &REC.NC_PRINT.Value = &print;
   &REC.DATETIME_STAMP.Value = %Datetime;
   &SQL.Execute(&REC);
  
   
End-For;

&SQL.Close(); /*crucial line*/

&reportid = <xmlp report name>;
/* create report definition */

&oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&reportid);
&oRptDefn.Get();
&oRptDefn.ProcessReport("xmlp report name", "ENG", %Date, "PDF");
&sDirName = &oRptDefn.OutDestination;
&oRptDefn.Publish("", &sDirName, "", 0);
CommitWork();
&oRptDefn.DisplayOutput();

Issues faced:
=รจ &SQL.Close(); line was missing in the original code and when I click on the print button, fieldchange event used to throw error that ‘think time function viewattachment() can not work while database transaction update happening’

Apparently &oRptDefn.DisplayOutput(); internally calls viewattachment() which checks if there are any open cursors present, so closing the SQL object did the trick.
It was a good learning experience. Always close the SQL cursor though they are usually harmless unless some situation like this occurs.









Read data from file and then process row by row using component interface inside application engine


Requirement (I am on HCM 9.1 and peopletools 8.51.08)

Read from a file – EMPLID and Full-time / part-time student flag – only two columns

Call CI for personal data and update the flag if the latest row (current row) has a flag different from the one in the file


Pseudo logic

  • Create an application engine program
  • Get CI for PERSONAL_DATA
  • Save
  • *Handle exceptions and store error messages into a log file


Approach 1

Store file data into a table

Create a step with two actions: Do-select and Peoplecode

Write Do-select with select/fetch to get one row at a time

Next Action – Peoplecode to process the Personal data CI

Issue – (I am on tools 8.51)

If for some reasons CI fails, do-select cursor is closed and application engine abends irrespective of the way you handle that error. Even try-catch block does not seem to work

Approach 2

Store file data into a table

Create a step with only one action: Peoplecode

Peoplecode to process the Personal data CI and error handling


No issues with this as far save is concerned. Rows with no errors save successfully and the ones with errors are logged correctly into a log file

Approach 3

Store file data into a table


Create a step with two actions: Do-select with 'reselect' (frequency value does not matter) and peoplecode

Write Do-select to get one row at a time

Next Action – peoplecode, first delete the current selected row so that it would be skipped in the next iteration of 'reselect', otherwise do-select would practically never end.
next write a logic to process personal data CI

No issues with this as far save is concerned. Rows with no errors save successfully and the ones with errors are logged correctly into a log file
Note:

There is a popular discussion within peoplesoft community around the web on this issue.
Popular searches are 'application engine abends when CI save fails' or  'application engine component interface combo error or issue'

I remember using Approach 1 prior to 8.50 people tools without any issues, so may be this is happening only with newer tools versions.

GenerateComponentContentURL

Tools 8.49

GenerateComponentContentURL - This uses the node on which your application is running and may not be same as your default local node.

Understanding arrays

Local array of array of any &AAAny = CreateArray(CreateArrayAny(1, 2), CreateArrayAny("hi"), CreateArrayAny("there", "2", "dsfds", "444444dsfds"));

Local array of array of number &AAnumber = CreateArray(CreateArray(1, 2), CreateArray(777, 555, 55999, 22), CreateArray(3, 888, 560, 7124));

Local array of array of array of number &AAnumber_3 = CreateArray(CreateArray(CreateArray(1, 2), CreateArray(CreateArray(1000, 2000)), CreateArray(CreateArray(777, 555, 55999, 22)), CreateArray(CreateArray(3, 888, 560, 7124))));

rem 444444dsfds;
WinMessage(&AAAny [3][4]);

rem 7124;
WinMessage(&AAnumber [3][4]);

rem 2000;
WinMessage(&AAnumber_3 [1][2][2]);

Thousand separator in a string; not just on a PS page

I had a requirement where the amount value on the page needed to have thousand separator. To show it on a peoplesoft page is easy, just select the checkbox on the Page Field properties for that amount field.
But, to show the exact value in a thousand separated format in an email is not straightforward. So I wrote this function:

Function ThousandSeparatorString(&InputNumber As number) Returns string
&InputString = String(&InputNumber);
If Find(".", &InputString) > 0 Then
&InputString = Substring(&InputString, 1, Find(".", &InputString) - 1);
End-If;

&OutputString = "";
Evaluate Mod(Len(&InputString), 3)
When 1
&NumberofIterations = Round((Len(&InputString) - 1) / 3, 0);
&OutputString = Substring(&InputString, 1, 1) | ",";
For &i = 1 To &NumberofIterations
&OutputString = &OutputString | Substring(&InputString, &i * 3 - 1, 3) | ",";
End-For;
&OutputString = Substring(&OutputString, 1, Len(&InputString) + &NumberofIterations);
Break;
When 2
&NumberofIterations = Round((Len(&InputString) - 2) / 3, 0);
&OutputString = Substring(&InputString, 1, 2) | ",";
For &i = 1 To &NumberofIterations
&OutputString = &OutputString | Substring(&InputString, &i * 3, 3) | ",";
End-For;
&OutputString = Substring(&OutputString, 1, Len(&InputString) + &NumberofIterations);
Break;
When 0
&NumberofIterations = Round((Len(&InputString)) / 3, 0);
For &i = 1 To &NumberofIterations
&OutputString = &OutputString | Substring(&InputString, &i * 3 - 2, 3) | ",";
End-For;
&OutputString = Substring(&OutputString, 1, Len(&InputString) + &NumberofIterations - 1);
Break;
End-Evaluate;
Return &OutputString;
End-Function;

Navigations for all the components in peoplesoft database

I needed to generate the navigations for all the content references in peoplesoft as some of the users needed the ready reference. I had created some custom objects and then populated a custom table with all the necessary information.


Step 1:
Create a work record with a dummy field.

step 2:
On the fieldchange peoplecode of that work field add the following code.

step 3:
import PTLT_SETUP:Utility;

&FindObjects = create PTLT_SETUP:Utility();
SQLEXEC("DELETE FROM PS_PSOFT_NAV");

&SQL = CreateSQL("SELECT PORTAL_NAME,PORTAL_URI_SEG1,PORTAL_URI_SEG2,PORTAL_URI_SEG3 FROM PSPRSMDEFN WHERE PORTAL_URI_SEG2<>'' and PORTAL_NAME=:1", %Portal, &PORTAL, &MENUNAME, &PNLGRPNAME, &MARKET);

While &SQL.fetch(&PORTAL, &MENUNAME, &PNLGRPNAME, &MARKET)

&NAVIGATION = &FindObjects.GetNavigationFromComponent2(&PNLGRPNAME, &MARKET, &MENUNAME, &PORTAL);

SQLExec("insert into PS_PSOFT_NAV VALUES (:1,:2,:3,:4,:5)", &PORTAL, &MENUNAME, &PNLGRPNAME, &MARKET, &NAVIGATION);

End-While;



step 4:
add this work record.field to any page in EMPLOYEE/CUSTOMER/SUPPLIER or any other portal you use.

step 5:
from the PIA for that portal, fire this code

step 6:
SELECT * FROM PS_PSOFT_NAV

Insert Line feed and Space character in PeopleCode

Function InsertSpaceChars(&count) Returns string
&spaceString = "";
For &i = 1 To &count
&spaceString = &spaceString | Char(32);
End-For;
Return &spaceString;
End-Function;

Function InsertLineFeedChars(&count) Returns string
&NextLineString = "";
For &i = 1 To &count
&NextLineString = &NextLineString | Char(10);
End-For;
Return &NextLineString;
End-Function;

CSV File Layout and issue with Leading zeros in excel

I created a SQL view and tied that to a file layout with csv format, double quote as qualifier and comma as delimiter.
In the peoplecode to generate the csv file I just used WriteRecord method of File class.

The problem I faced was for all the character fields with Leading Zeros,
when I open the file in notepad it was ok
when I open the file in excel it was removing the leading zeros

So I used a workaround.
Excel treats ="001020" as 001020 and "001020" as 1020 while displaying the cell content(!!)

Written following logic instead of Writerecord, I used Writeline and an array with any data type.

Code snippet: In this example, Position number, department, jobcode and location were having issues with leading zeros.


&sqlobject = CreateSQL("SELECT * FROM (view with 44 fields)");
&array_AAny = CreateArrayAny();

&FileObject = GetFile(&FileName, "W", %FilePath_Absolute);
&FileObject.WriteLine("Employee ID" | "," | "Current Month" | "," | "Current Month + 1" | "," | "Current Month + 2" | "," | "Current Month + 3" | "," | "Current Month + 4" | "," | "Current Month + 5" | "," | "Current Month + 6" | "," | "Current Month + 7" | "," | "Current Month + 8" | "," | "Current Month + 9" | "," | "Current Month + 10" | "," | "Current Month + 11" | "," | "Home Phone" | "," | "Cell Phone" | "," | "Empl First Name" | "," | "Empl Last Name" | "," | "AD username" | "," | "Empl email" | "," | "Employment Type" | "," | "Empl Status" | "," | "Position number" | "," | "Position status" | "," | "Vendor ID" | "," | "Vendor Name" | "," | "Onsite/Offshore" | "," | "Hire Date" | "," | "Term Date" | "," | "Dept" | "," | "Dept Name" | "," | "Company" | "," | "Location" | "," | "Job Code" | "," | "Job Title" | "," | "Address 1" | "," | "Address 2" | "," | "City" | "," | "State" | "," | "Postal" | "," | "Country" | "," | "Supervisor ID" | "," | "Supervisor First Name" | "," | "Supervisor Last Name" | "," | "Supervisor Email");
If &FileObject.IsOpen Then
If &FileObject.SetFileLayout(FileLayout.(view with 44 fields)) Then
While &sqlobject.Fetch(&array_AAny)
&FileObject.writeline("""" | &array_AAny [1] | """,""" | &array_AAny [2] | """,""" | &array_AAny [3] | """,""" | &array_AAny [4] | """,""" | &array_AAny [5] | """,""" | &array_AAny [6] | """,""" | &array_AAny [7] | """,""" | &array_AAny [8] | """,""" | &array_AAny [9] | """,""" | &array_AAny [10] | """,""" | &array_AAny [11] | """,""" | &array_AAny [12] | """,""" | &array_AAny [13] | """,""" | &array_AAny [14] | """,""" | &array_AAny [15] | """,""" | &array_AAny [16] | """,""" | &array_AAny [17] | """,""" | &array_AAny [18] | """,""" | &array_AAny [19] | """,""" | &array_AAny [20] | """,""" | &array_AAny [21] | """," | "=" | """" | &array_AAny [22] | """,""" | &array_AAny [23] | """,""" | &array_AAny [24] | """,""" | &array_AAny [25] | """,""" | &array_AAny [26] | """,""" | &array_AAny [27] | """,""" | &array_AAny [28] | """," | "=" | """" | &array_AAny [29] | """,""" | &array_AAny [30] | """,""" | &array_AAny [31] | """," | "=" | """" | &array_AAny [32] | """," | "=" | """" | &array_AAny [33] | """,""" | &array_AAny [34] | """,""" | &array_AAny [35] | """,""" | &array_AAny [36] | """,""" | &array_AAny [37] | """,""" | &array_AAny [38] | """,""" | &array_AAny [39] | """,""" | &array_AAny [40] | """,""" | &array_AAny [41] | """,""" | &array_AAny [42] | """,""" | &array_AAny [43] | """,""" | &array_AAny [44] | """");
End-While;
End-If;
End-If;

This worked well for the users!!

Code snippet to schedule a new process request from peoplecode.

This is an asynchronous call versus the CallAppEngine function call.
&NewProcessRequest = CreateProcessRequest();
&NewProcessRequest.ProcessType = "Application Engine/SQR/YOur process Type";
&NewProcessRequest.ProcessName = "";
&NewProcessRequest.RunControlID = Substring(String(Rand() * Rand() * Rand()), 1, 5);
&NewProcessRequest.Schedule();

SetCursorPos function

Example to set the cursor in a field on level 2

SetCursorPos(Panel.[page name], Record.[level 1], [curr row number on level 1], Record.[level 2], [curr row number on level 2], [level 2 record].[field]);

Schedule a process request from peoplecode

&RQST = CreateProcessRequest(); /* Schedule AE Process */ &RQST.ProcessType = "Application Engine";
&RQST.ProcessName = "Process Name";
&RQST.RunControlID = &RunCntlID;
&RQST.RunLocation = &wfServer;
&RQST.OutDestType = "%OutputDirectory%";
&RQST.OutDestFormat = "SPF";
&curdttmoffset = &curdttmoffset + &Delay;
&RQST.RunDateTime = AddToDateTime(%Datetime, 0, 0, 0, 0, &curdttmoffset, 0);
&RQST.TimeZone = %ServerTimeZone;
&RQST.Schedule();

Error Statement in PeopleCode and Message Catalog

When you use Error("some hard-coded string") in peoplecode to generate and show error message, then on the screen users see the following line gets appended to the error message:


The PeopleCode program executed an Error statement, which has produced this message.


To avoid showing this line, use the message catalog for storing the error message and use error statement like this:


Error MsgGetText (message set number,message number,"some default text")

Peoplecode compilation error

One of the things to try out:

Clear the appserver cache and bounce the appserver

Query PSPCMPROG for peoplecode last update dttm and OPRID to ge tmore details on changes

Function for Date in dd-mon-yyyy format

I am not sure whether there is any delivered function to get the date in DD-MON-YYYY format in Peoplesoft.
I use this one.
Function GetDatewithMonth(&InputDate As date) Returns string
Evaluate Month(&InputDate)
When 1
&MonthinWords = "JAN";
Break;
When 2
&MonthinWords = "FEB";
Break;
When 3
&MonthinWords = "MAR";
Break;
When 4
&MonthinWords = "APR";
Break;
When 5
&MonthinWords = "MAY";
Break;
When 6
&MonthinWords = "JUN";
Break;
When 7
&MonthinWords = "JUL";
Break;
When 8
&MonthinWords = "AUG";
Break;
When 9
&MonthinWords = "SEP";
Break;
When 10
&MonthinWords = "OCT";
Break;
When 11
&MonthinWords = "NOV";
Break;
When 12
&MonthinWords = "DEC";
Break;
When-Other
Break;
End-Evaluate;
Return Day(&InputDate) | "-" | &MonthinWords | "-" | Year(&InputDate);
End-Function;

Quickly create a basic Graph/ Chart

rem On the peoplesoft page, first get the chart control and associate it to a work record field;

&oChart2 = GetChart();
&oChart2.Reset();
&myrowset = CreateRowset(Record.);

rem fill this rowset using fill method or copyrowset from a rowset already loaded into the buffer memory;

&NumArray = CreateArray(1, 10, 12, 9);
rem this is for different colors that will be used in the graph;
&oChart2.SetColorArray(&NumArray);

&oChart2.XAxisScaleResolution = %ChartAxisResolution_Coarse;
&oChart2.YAxisScaleResolution = %ChartAxisResolution_Coarse;
&oChart2.SetData(&myrowset);
&oChart2.SetDataXAxis(Myrecord.fld1);
&oChart2.SetDataYAxis(Myrecord.fld2);
&oChart2.XAxisLabelOrient = %ChartText_Vertical;
&oChart2.Type = %ChartType_2DBar;

Simple Email sent using MCF Mail classes

MCF class does not need app server SMTP server and Port settings

import PT_MCF_MAIL:SMTPSession;
import PT_MCF_MAIL:MCFEmail;
import PT_MCF_MAIL:MCFMultipart;
import PT_MCF_MAIL:MCFBodyPart;
import PT_MCF_MAIL:MCFPart;
import PT_MCF_MAIL:*;

&OuboundEmail = create PT_MCF_MAIL:MCFOutboundEmail(); &OuboundEmail.smtpserver = "";
&OuboundEmail.SMTPPort = 25;
&OuboundEmail.from = "xyz@abc.com";
&OuboundEmail.recipients = 'pqr@lmn.com';
&OuboundEmail.subject = "Test Subject";
&OuboundEmail.text='' ;
&ret = &OuboundEmail.send();

Gray all the fields in a record

&REC_GRAY = GetRecord(Record.TEST);

&INDEX1 = 1;

For &y = &INDEX1 To &REC_GRAY.fieldcount

&REC_GRAY.GetField(@("FIELD." -concat operator in peoplesoft-&REC_GRAY.GetField(&y).Name)).DisplayOnly = True;

End-For;

Code to dynamically change the dropdown values for a field

Local Rowset &Xlat;
&FLD = GetRecord(Record.JOB).GetField(Field.ACTION);
&FLD.ClearDropDownList();
--- This particular part is useful only if the dropdown values are coming from translate table

&Xlat = CreateRowset(Record.PSXLATITEM);
&Xlat.Fill("WHERE FILL.FIELDNAME = 'myfieldname'");
&Xlat_cnt = &Xlat.ActiveRowCount;
For &I = 1 To &Xlat_cnt
&CodeIn = &Xlat.GetRow(&I).GetRecord(1).FIELDVALUE.Value;
&DescIn = &Xlat.GetRow(&I).GetRecord(1).XLATLONGNAME.Value; &FLD.AddDropDownItem(&CodeIn, &DescIn);
End-For;