PeopleSoft Tips & Tricks

Element Browser Filter

Element Browser. It’s the subpage GPSC_ELM_FLTR_SCF on the page GPSC_ELM_BRW_LFL, component GPSC_ELM_BRW_FL. You can get there with this URL.

URL: EMPLOYEE/HRMS/c/DEFINE_PAYROLL_RULES_(GBL).GPSC_ELM_BRW_FL.GBL

Navigation: Set Up HCM > Product Related > Global Payroll & Absence Mgmt > Elements > Element Browser

The checkbox is interesting. It’s added as a control in app designer:

The checkbox column uses the styles: psc_width-15em psc_padding-left2em

The description column uses the styles: psc_width-15em psc_padding-left2em

Finding the Grids

First, I need to find grids. The FIELDTYPE field in the PSPNLFIELD table contains the type of controls on the page. Field Type #19 is a grid.

Next, I wanted to look for grids setup with the “Div Grid Layout” grid style. That’s configured on the Use tab of the page-field properties:

That setting is stored in the database in the table PSPNLCNTRLDATA, field PTGRDLAYOUT. Here are the 12 different options:

Value	Description
0	Classic Grid Layout
1	Classic Scrollable Grid Layout
2	List Grid Layout (Unordered)
3	Data Grid Layout
4	Div Grid Layout
5	Flex Grid Layout
6	Classic List Grid Layout (Unordered)
7	Classic List Grid Layout (Ordered)
8	Classic Presentation Grid Layout
9	List Grid Layout (Ordered)
10	Menu Grid Layout
11	Tab Set Grid Layout

So, to put that all together, here’s some SQL that will find all of the Div Grid Layouts:

SELECT A.PNLNAME, B.PNLFLDID, 
      C.PNLTYPE, C.DESCR, 
      D.PNLNAME, E.PNLGRPNAME, F.DESCR, 
      G.PORTAL_LABEL, G.PORTAL_URI_SEG1 || '.' || G.PORTAL_URI_SEG2 || '.' || G.PORTAL_URI_SEG3
FROM PSPNLCNTRLDATA A
JOIN PSPNLFIELD B
  ON A.PNLNAME = B.PNLNAME
  AND A.PNLFLDID = B.PNLFLDID
JOIN PSPNLDEFN C
  ON A.PNLNAME = C.PNLNAME
LEFT OUTER JOIN PSPNLFIELD D
  ON C.PNLNAME = D.SUBPNLNAME
LEFT OUTER JOIN PSPNLGROUP E
  ON C.PNLNAME = E.PNLNAME
  OR D.PNLNAME = E.PNLNAME
LEFT OUTER JOIN PSPNLGRPDEFN F
  ON E.PNLGRPNAME = F.PNLGRPNAME
LEFT OUTER JOIN PSPRSMDEFN G
  ON G.PORTAL_URI_SEG2 = F.PNLGRPNAME
WHERE A.PTGRDLAYOUT = 4
AND B.FIELDTYPE = 19

Fluid versus Classic

Not all of the types work with fluid. Now, that doesn’t mean you’ll get an error if you use a classic type on a fluid page. The fields just won’t work on the fluid page.

Classic	Fluid
Classic Grid Layout	List Grid Layout (Unordered)
Classic Scrollable Grid Layout	Data Grid Layout
Classic List Grid Layout (Unordered)	Div Grid Layout
Classic List Grid Layout (Ordered)	Flex Grid Layout
Classic Presentation Grid Layout	List Grid Layout (Ordered)
	Menu Grid Layout
	Tab Set Grid Layout

Div Grid Layout

The Div Grid Layout style turns everything into a <div> tag. Then, you need to use CSS styles to make the layout look the way you want. Here are the styles at the different levels:

ps_grid-div ps_grid-body
- ps_grid-row
  - ps_grid-cell

Example: Benefits

The page BEN_SUMM_GRID_FL, component BEN_SUMM_GRID_FL has a grid that uses the Div Grid Layout. At first, I thought it was the “Benefit Plans” grid, but that uses Flex Grid Layout. It’s the two areas on the right: Contact Information and Resource Information. It has no search page, so you can just go directly to the URL.

URL: EMPLOYEE/HRMS/c/EF_BENEFITS_FL.BEN_SUMM_GRID_FL.GBL

Read / Write CSV File PeopleCode Example using Java Object

1. Writing CSV File in PeopleCode:-

/* Create CSV File in PeopleSoft - OpenCSV Example */

/* Create java.io.Writer with CSV File Details */

Local JavaObject &ioWriter = CreateJavaObject("java.io.FileWriter", "PeopleSoft_CSV_File.CSV");

/* Create a CSV Writer Object Now */

Local JavaObject &CSVWriter = CreateJavaObject("au.com.bytecode.opencsv.CSVWriter", &ioWriter);

/* Write SQL Query for CSV Output */

Local SQL &dumpSQL = CreateSQL("SELECT OPRID||','||VERSION FROM SYSADM.PSOPRDEFN");

/* Fetch Data */

Local string &csvLine;

/* We create a Java Array that we can pass to CSV Writer */

Local JavaObject &Jarray = CreateJavaArray("java.lang.String[]", 1);

While &dumpSQL.Fetch(&csvLine)

/* Push the input to Peoplecode Array */

Local array of any &classListArray = CreateArrayAny();

&classListArray.Push(&csvLine);

/* Copy input to Java Array */

CopyToJavaArray(&classListArray, &Jarray);

/* Write Java Array to CSV File */

&CSVWriter.writeNext(&Jarray);

End-While;

/* Close SQL Cursor */

&dumpSQL.Close();

/* Close CSV Writer */

&CSVWriter.close();

2.Reading CSV File in PeopleCode:-

/* Read / Parse CSV File PeopleCode Example */

/* Read Input File through FileReader Object */

Local JavaObject &ioReader = CreateJavaObject("java.io.FileReader", "PeopleSoft_CSV_File.CSV");

/* Create a CSV Reader Object Now */

Local JavaObject &CSVReader = CreateJavaObject("au.com.bytecode.opencsv.CSVReader", &ioReader);

/* Create Java String Array that can read CSV Data */

Local JavaObject &Jarray;

/* Read File line by line now */

While True

&Jarray = &CSVReader.readNext();

/* You have the line in a Java Array now */

/* You can now use CopyFromJavaArray and take it to PeopleCode Array */

End-While;

&CSVReader.close();

XMLP using ROWSET as Datasource

XML Publisher - Example program using Rowset as Data source taking JOB and COMPENSATION delivered table

Step 1:

Create an App engine to supply: a) XML Sample Data File

b) XSD Schema File

Following code to populate Row set in app engine (People code Action)

import PSXP_RPTDEFNMANAGER:*;

import PSXP_XMLGEN:*;

/*Create Rowset*/

&rs_comp = CreateRowset(Record.COMPENSATION);

&rs_job = CreateRowset(Record.JOB, &rs_comp);

/*Fill Parent Rowset*/

&rs_job.FILL("WHERE EMPLID LIKE 'K0G00%'");

/*Fill child Rowset*/

For &i = 1 To &rs_job.ActiveRowCount

&row = &rs_job.GetRow(&i);

&rs = &row.GetRowSet(Scroll.COMPENSATION);

&rs.Fill("WHERE EMPLID = :1", &row.JOB.EMPLID.Value);

End-For;

/*Create Schema XSD */

&rds = create PSXP_XMLGEN:RowSetDS(); /*example package method*/

&mySchema = &rds.GetXSDSchema(&rs_job);

&f1 = GetFile("c:\temp\JOB_XSD.xsd", "W", %FilePath_Absolute);

&f1.WriteLine(&mySchema);

&f1.Close();

/*Create Sample XML File*/

&myXMLFile = &rds.GetXMLData(&rs_job, "c:\temp\JOB_XSD.xsd");

&f2 = GetFile("c:\temp\JOB_XML.xml", "W", %FilePath_Absolute);

&f2.WriteLine(&myXMLFile);

&f2.Close();

Step 2:

Execute the above code following XML file and XSD file will be generated in the above mention path in the code i;e "c:\temp\JOB_XML.xml"

Step 3:

Create an RTF Template file using XML sample data file in ms-word

Step 4:

With in People soft (3 tire) create a) Data source Definition

b) Report Definition

a) Data source Definition path Reporting Tools –XML Publisher-Data Source.

Add new value - Data source type : Rowset

Data Source id : any name

Object Owner id : Human Resources

Upload XML file and XSD File.

Step 4: b) Report Definition.

path Reporting Tools –XML Publisher-Report Definition.

Add new value – Report Name : Any Name

Data source type : Rowset

Data Source id : above given name (rowset name)

In Definitaion Tab:

Report Status: Active

Report Category id: Allusers

Object Owner id: Human Resources

In Template Tab:

Status : Active

Upload: RTF file which is created using XML sample data.

In Out Put Tab: Location : File Destination : any path (eg: c:\temp)

Step 5 :

Update Your app engine with the following code to output full data then run it by scheduling it app engine

import PSXP_RPTDEFNMANAGER:*;

import PSXP_XMLGEN:*;

Local PSXP_RPTDEFNMANAGER:ReportDefn &ReportID;

/*Set-Up Report */

&ReportDef = create PSXP_RPTDEFNMANAGER:ReportDefn("Report name given in step4")

&ReportDef.Get();

………….above code…………in step 1…………..

/*Provide a Data Source for the Report*/

&ReportDef.SetRuntimeDataRowset(&rs_job);

/*Generate the Report*/

&ReportDef.ProcessReport("Templete name in template tab", "ENG", %Date, "PDF");

/*Publish the Report */ // is_job_aet is a state record

&ReportDef.Publish("", "c:\temp\", "XMLP", IS_JOB_AET.PROCESS_INSTANCE);

Note: the output of XML Publisher report doesn’t go to process monitor .instead report manager

File Layout for a CSV from PeopleCode

Example of a CSV being created via a file layout (the file layout must be defined as type ‘CSV’)

/* Declare varaibles */

Global File &CSVFile, &fileLog;

Local Rowset &fileRS;

/* Define CSV file and write column headings */

&CSVFile = GetFile("file_name.csv", "w", "a", %FilePath_Relative);

&CSVFile.WriteLine("Column 1,Column 2,Column 3,Column 4");

/* Set the file layout for the CSV file */

&CSVFile.SetFileLayout(FileLayout.FILE_LAYOUT_NAME);

/* Populate the rowset for the file layout */

&fileRS = &CSVFile.CreateRowset();

&fileRS.Fill("where EMPLID = :1", &currentEmplid);

/* Write the rowset to file and close the file */

&CSVFile.WriteRowset(&fileRS, True);

&CSVFile.Close();

*********************************************************************

Direct Write for a CSV

Example of a CSV being created directly, without the use of a file layout:

Local integer &i;
Local string &CSVLine;
Local Record &acadCareerRec;
Local Rowset &acadCareerRS;
Local File &CSVFile;

/* Define CSV file and write column headings */
&CSVFile = GetFile("file_name.csv", "w", "a", %FilePath_Relative);
&CSVFile.WriteLine("Column 1,Column 2,Column 3,Column 4");

/* Define rowset and fill with values */
&acadCareerRS = CreateRowset(Record.ACAD_CAR_TBL);
&acadCareerRS.Fill();

/* Loop through each row of the rowset */
for &i = 1 to &acadCareerRS.ActiveRowCount
   &acadCareerRec = &acadCareerRS.GetRow(&i).ACAD_CAR_TBL;

   /* Write the current details to file */
   &CSVLine = &acadCareerRec.INSTITUTION.Value | "," |
              &acadCareerRec.ACAD_CAREER.Value | "," |
              &acadCareerRec.DESCR.Value;
   &CSVFile.Writeline(&CSVLine);
end-for;

/* Close the CSV File */
&CSVFile.Close();
*****************************************************