PeopleSoft : Reading excel file using jxl in peoplesoft

Reading Excel using Java:

Step1: Create a java class to read excel and compile and place the class file in /app/psoft/psadmin/appserv/classes  .

/* Java class to read excel  file */

import java.io.*;

import jxl.*;

public class ReadExcelFile {

public static void main(String[] args){

System.out.println("Hi");

}

public Workbook Readworkbook(String Filename){

try {

Workbook ReadExcel = Workbook.getWorkbook(new File(Filename));

return ReadExcel;

}

catch (Exception i)

        {

            System.out.println(i);

            return null;

        }

}

}

Step 2: Use the below code to read data

Local string &strGetEnv = GetEnv("PS_HOME");

Local string &St_path_l = &strGetEnv | "/user/" | %DbName | "/ABC.xls";

Local JavaObject &Obj_class_l = CreateJavaObject("nExcel");

Local JavaObject &Obj_getworkbook_l = &Obj_class_l.readWorkbook(&St_path_l);

&rows = &Obj_getworkbook_l.getSheet(0).getRows();

&cols = &Obj_getworkbook_l.getSheet(0).getColumns();

WinMessage("No of rows:" | &rows | " No of columns:" | &cols, 0);

Local string &St_returnvalue_l;

For &i = 1 To &rows - 1

   For &j = 0 To &cols - 1;

      &St_returnvalue_l = &Obj_getworkbook_l.getSheet(0).getCell(&j, &i).getContents();

      MessageBox(0, "", 0, 0, "" | &St_returnvalue_l);

   End-For;

End-For;

&Obj_getworkbook_l.close();

PeopleSoft : Changing tablespace of a record.

If you want to assign a record to the new tablespace that you created 

open your record, and go to Tools --> Data Administration --> Set Tablespace.

then you can assign new table space to record.

PeopleSoft : New features in peopletools 8.53

New Features :

       1.       New styles for a contemporary appearance.

       2.       More flexibility in WorkCenters and Dashboards.

       3.       Improvements to the Related Content Framework, Related Actions, and Activity Guides.

PeopleSoft Secure by Default Initiative

       1.       Safeguards for ensuring that passwords for default user accounts are set during installation.

       2.        Additional mitigation for brute force and denial-of-service attempts.

       3.        Additional security for file attachments and the Report Repository.

PeopleSoft Search Framework

      1.       Multiple FTP credential support: The PeopleSoft Search Framework now supports the use of multiple FTP servers for storing attachments. As long as the FTP server is defined as a URL definition in PeopleTools and is listed as an FTP URL within the Search Framework, search results will recognize the FTP location.

PeopleSoft Update Manager

      1.       PeopleSoft Update Manager (PUM) is a new capability that simplifies the process of applying

Maintenance to meet customers’ needs.

      2.       Customers now have the option of reviewing fixes provided in application maintenance and deciding which fixes and enhancements will be of value to them.

      3.       Reduce the number of unnecessary fixes customers must apply to their system to resolve a

particular issue.

      4.       Decrease the risk of applying individual fixes rather than complete maintenance packs or bundles.

Data Migration Workbench

      1.       The Data Migration Workbench is a new tool that helps users manage the process of moving configuration data from one system to another.

      2.       It assists with the following process steps:

a.       Define structured configuration data in one system .

b.      Define conditions for extraction of the data in preparation of migration.

c.       Extract the data from the source system.

d.      Migrate/import the data to one or more target systems according to defined import rules.

JavaScript Object Notation (JSON) from Documents

      1.       Documents can now read and write from JavaScript Object Notation (JSON).

      2.       In 8.53 standard PeopleCode can be generated automatically and used by developers as a template to read and write data from a document.

Reporting and Analysis Tools

     1.       New My Reports Pagelet

a.       Opening of the actual report directly instead of a page to display a list of files.

b.      Grouping of multiple instances of the same report.

     2.       Chart Enhancements

a.       Making use HTML, CSS3, and JavaScript instead of the server-based chart engine.

     3.       Pivot Grid Enhancements

a.       Multi Select Filters that provide the ability to filter data on multiple values instead of a single value.

b.      Progressive Filtering that shows only valid values for the current filter selection when a filter value is modified.

c.        Dynamic passing of prompt values into the Pivot Grid being displayed in a Related Content frame or in Related Actions.

PeopleSoft : AE Peoplecode to read data from excel

Local array of string &a;

&sFilePath = "C:\temp\template.xls";

&oWorkApp = CreateObject("COM", "Excel.Application");

ObjectSetProperty(&oWorkApp, "Visible", True);

&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;

MessageBox(0, "", 0, 0, "RowCount:" | &rowcount);

MessageBox(0, "", 0, 0, "CellCount:" | &cellcount);

MessageBox(0, "", 0, 0, "ColumnsCount:" | &columncount);

For &i = 1 To &rowcount

   rem  &a = CreateArray("");

   For &j = 1 To &columncount

      &data = &oWorkSheet.Cells(&i, &j).Value;

      rem &a.Push(&data);

      MessageBox(0, "", 0, 0, "data in" | &i | " row " | &j | " column :" | &data);

   End-For;

End-For;

&oWorkApp.ActiveWorkBook.Close();

&oWorkApp.DisplayAlerts = "True";

&oWorkApp.Quit();

PeopleSoft : DMS Scripts

PeopleSoft Data Mover enables you to:

·         Transfer application data between PeopleSoft databases.

·         Move PeopleSoft databases across operating systems and database platforms.

·         Execute Structured Query Language (SQL) statements against any PeopleSoft database, regardless of the underlying operating system or database platform.

Most Used DMS Scripts:

--DMS for exporting a record.

set log D:\DataMover-Scripts\ ORG_JOB _EXPORT.log;

set output D:\DataMover-Scripts\ ORG_JOB.dat;

export ORG_JOB_TBL;

--DMS for importing a record.

set log D:\DataMover-Scripts\ORG_JOB_IMPORT.log;

set input  D:\DataMover-Scripts\ ORG_JOB.dat;

import  ORG_JOB_TBL;

PeopleSoft : Random Password Generation

Local string &a;
&j = Int(Rand() * 25) + 65;
&a = &a | Char(&j);
&j = Int(Rand() * 35) + 97;
&a = &a | Char(&j);
&j = Int(Rand() * 9) + 48;
&a = &a | Char(&j);
&j = Int(Rand() * 12) + 33;
&a = &a | Char(&j);
&j = Int(Rand() * 9) + 48;
&a = &a | Char(&j);

&j = Int(Rand() * 35) + 97;
&a = &a | Char(&j);
&j = Int(Rand() * 25) + 65;
&a = &a | Char(&j);

WinMessage(&a);

PeopleSoft : Add drop down list in order.

 For &j = 1 To &Rs.ActiveRowCount
   &value = &Rs(&j).CH_RATING_TBL.CH_RATING.Value;
   &descr = &Rs(&j).CH_RATING_TBL.DESCR.Value;

   N_JOBSKILL1_TBL.CH_PROFLEVEL.AddDropDownItem(&value, Rept(Char(9), &j) | &descr);
End-For;

&j should be activerowcount -1

the translate value which u want first in the list should be appended with activerowcount number of char(9)

PeopleSoft Charts

To create a chart:

1. Open PeopleSoft Application Designer.
2. Open the page where the chart is to be inserted.
3. Insert the chart control by either:

·                     Clicking on the chart icon in the toolbar, or

·                     Selecting Insert, Chart

4. Draw the chart control on the page.
5. Associate the chart control with a record field

Every chart control must be associated with a record field. This is just the field for the chart control. It is not the field used for drilling down for data in the chart.

Bring up the chart control properties by either

·                     Double-clicking on the chart control, or

·                     Right-clicking on the chart control and selecting Page Field Properties.

6. Select the record name and field for the chart.

On the Record tab of the chart control properties, select the record and field for the chart.

To make the control a page anchor, select the Enable as Page Anchor on the General tab.

7. Write your PeopleCode.

In some event on the page, such as Activate, put the PeopleCode you need for populating the chart.

8. Get the chart.

The first thing you must do is get the chart. You must use the record and field name that you associated with the chart control on the page.

&oChart = GetChart(QE_CHART_DUMREC.QE_CHART_FIELD);

9. Set the data records.

Set the data record. The SetData function associates the record data with the chart. Then use the SetDataYAxis and SetDataXAxis functions to associate specific fields in the record with the Y axis data and the X axis data.

&oChart.SetData(Record.QE_CHART_RECORD);

&oChart.SetDataYAxis(QE_CHART_RECORD.QE_CHART_SALES);

&oChart.SetDataXAxis(QE_CHART_RECORD.QE_CHART_PRODUCT);

This is all the code needed to create a chart. You don’t need to set the chart type: the default is a 2D bar chart. You don't need to set the series unless you want to group your data. Everything else is also optional.

10. (Optional) Set the data series.

In this example, we want to set the region as the series. This means that the data will be grouped according to the region.

&oChart.SetDataSeries(QE_CHART_RECORD.QE_CHART_REGION);

11. (Optional) Set the chart type.

Because we want a stacked bar for the chart, we must set the Type property of the chart. This means that each product (footballs, rackets, and so on) will have a single bar, and the data series (California, Oregon, and so on) will be what is 'stacked'.

&oChart.Type = %ChartType_2DStackedBar;

12. (Optional) Set legend and label attributes.

For this example, we want a legend, and want it to appear in the right side. In addition, because the text of the series labels is so large, they must be vertical to display all of them.

&oChart.HasLegend = True; &oChart.LegendPosition = %ChartLegend_Right;

&oChart.XAxisLabelOrient = %ChartText_Vertical;

Sample code to  prepare chart:

Local Chart &cChart;

Local Rowset &wecntrs;

Local SQL &WSQL;

Local number &i, &empcnt;

Local string &perrating;

/* Get data by using SQL*/

&wecntrs = GetLevel0()(1).GetRowset(Scroll.PERFORM_RATIN);

&WSQL = CreateSQL("select  COUNT(EMPLID),PERFORM_RATING from PS_PERFORM_RATIN group by PERFORM_RATING");

&i = 1;

/* Prepare Data */

While &WSQL.Fetch(&empcnt, &perrating)

   &wecntrs(&i).PERFORM_WRK.EMPL_COUNT.Value = &empcnt;

   &wecntrs(&i).PERFORM_WRK.PERFORM_RATING.Value = &perrating;

   &wecntrs.InsertRow(&i);

   &i = &i + 1;

End-While;

/**Chart properties **/

&cChart = GetChart(PERFORM_WRK.PERFORM_RATING);/* PERFORM_WRK.PERFORM_RATING assingned to chart */

&cChart.HasLegend = True;

&cChart.LegendPosition = %ChartLegend_Right;

&cChart.Type = %ChartType_3DPie;

&cChart.SetData(&wecntrs);  /* &wecntrs  is a rowset ,this contains data. */

&cChart.SetDataXAxis(PERFORM_WRK.PERFORM_RATING);

&cChart.SetDataYAxis(PERFORM_WRK.EMPL_COUNT);

&cChart.IsDrillable = True;  

/* set data hints */

&cChart.SetDataHints(PERFORM_WRK.PERFORM_RATING);

/* set data colors */

&cSliceColors = CreateArray(12, 10, 2, 5);

&cChart.SetColorArray(&cSliceColors);

/* To explode data */

Local array of number &ExplodedArray;

&ExplodedArray = CreateArray(4);

&cChart.SetExplodedSectorsArray(&ExplodedArray);

/* Set axis text properties */

&cChart.XAxisLabelOrient = %ChartText_Horizontal;

&cChart.XAxisTitle = "Performance";

&cChart.YAxisTitle = "Performance Count";

Sample Output:

NOTE:

•      &cChart.IsDrillable = True;  

If set this property as true then y-axis field change will trigger when we click on part of a chart.

PeopleSoft : App Engine: XML Export File using File Layout

Creating a File Layout.

1. Create or Select a Record for your data.  I used a view, to avoid having to deal with joins in PeopleCode.
2. Create a File Layout Definition.
3. Create an Application Engine Program, with a tiny bit of code in it.
4. Stuff all these things into a project so you can maintain your sanity at a later time.

After you have selected the record with the data you want to export, create a new File Layout Definition (File->New Definition->File Layout).

On the definition tab, right click “NEW FILE” and select Insert Record.  Search for the record you are using for your data, and double click it.  The Record Definition, and its columns will show up under “NEW FILE” now.  Save your File Layout Definition.

Click the Property Icon , then click the “Use” tab, and select XML in the “File Layout Format” drop down. Click OK.

Create a new Application Engine Program (File->New->Application Engine Program).  Right click “Step01″ and select “Insert Action”.  Click where it says “SQL”, and select “PeopleCode” from the drop down.

Double click the new action to edit the PeopleCode.  Copy and paste this PeopleCode into the window:
Local Record &Rec;
Local File &File;
Local SQL &SQL;

&File = GetFile("c:\temp\export_sample.xml", "W", %FilePath_Absolute);
If &File.IsOpen Then
If &File.SetFileLayout(FileLayout.YOUR_FILE_LAYOUT_DEFINITION) Then
&Rec = CreateRecord(Record.YOUR_RECORD_DEFINITION);
&SQL = CreateSQL("%Selectall(:1)", &Rec);
While &SQL.Fetch(&Rec)
&File.WriteRecord(&Rec);
&File.WriteLine("</YOUR_RECORD_DEFINITION>");
End-While;
Else
Error ("File Layout Not Correct");
End-If;
Else
Error ("file not open");
End-If;
&File.Close();

PeopleSoft : Differences between App Engine and SQR

App Engine	SQR
Advantages	Disadvantages
Maintained with in PeopleSoft, so Impact analysis is much easier. Changes to objects need not be dealt with every A.E every time.	Lies outside PeopleSoft, so separate Impact analysis is required. Changes need to be done manually in every file.
A.E. is a PeopleTool object designed as an alternative for SQR, COBOL, etc for background processing using SQL and PeopleCode.
Can be – Run from Application Designer. – Called from People Code function. – Running from DOS Environment (Debugging). – Running from Application Engine People Tool. – Running from People soft Application.	Can be – Process Scheduler –Command Line (Can take user input) –Winddoes
Reusability Can use Component Interface Integration Broker Message Catalogs
Ease of coding We can use Meta-SQL in PeoplCode/SQL. These expand to SQL in runtime thus decreasing the effort of coding For e.g. %Join àJoins the given tables using common fields or keys, can exclude list of fields.  Avoids rework even when record structure changes. %EffdtCheck à Creates the sub query for effective date check %Coalesce à Avoids database dependent coding for NVL
State table/AET table (SQL Table or Derived/Work Record) used to store/share Program level variables. Temp Tables to break complicated logic/sql and perform faster.  Since all is done in database the processing is faster. Output doesn't reach permanent tables until program is successful. Avoids deadlocks etc. in case it abends it can be fixed and rerun again.	Have to use variables or arrays to pass values.
Restartability à In case the program abends due to some issue it can be set to restart from that point instead of running from start all over again. AET table must be SQL Table for this. Repeatability à SQLs can be set for reusability thus saving recompile time and running faster.
Different sections can be created for different Markets, or different Platforms (databases)
Debugging- Has built in debugger Trace can be set for various levels of debugging	Must use SHOW or DISPLAY for debugging. No debugger.
Can call other A.E. using Call Section or use Functions in FuncLib or App Packages.	Can call functions in SQCs.
Disadvantages
Programming technique is very restrictive. Cant use SQL and PeopleCode together.  Has its own advantages.
Restricted View Can see one SQL/peopleCode at a time.	Can see full program. Understanding is easy.  Even editor can be of our choice.
Control Structure is fixed	Flow is not restricted.

PeopleSoft : Creating MSExcel with new sheets through peoplecode:

Local object &oWorkApp, &oWorkBook;

&sFilePath = "C:\temp\TEST_FL.xlsx";

&sFileDestPath = "C:\temp\Test.xlsx";

&oWorkApp = CreateObject("COM", "Excel.Application");

&oWorkApp.DisplayAlerts = "False";

ObjectSetProperty(&oWorkApp, "Visible", True);

&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");

&oWorkBook.Open(&sFilePath);

&oWorkSheet = &oWorkApp.Worksheets("Sheet1");

&oWorkSheet.Range("A1:C5").Font.Bold = True;

&oWorkApp.WorkSheets.Add().Name = "Test";

&oWorkSheet = &oWorkApp.Worksheets("Test");

&oWorkSheet.Range("A1:C5").Font.Bold = True;

&oWorkSheet.Cells(1, 1).Value = "I'm adding stuff to be bolded";

&oWorkSheet.Cells(1, 1).Font.Bold = True;

&oWorkSheet.Cells(1, 1).Font.Size = 24;

&oWorkSheet.Cells(1, 1).Font.ColorIndex = 3;

&oWorkApp.ActiveWorkbook.SaveAs(&sFileDestPath);

&oWorkApp.ActiveWorkBook.Close();

&oWorkApp.DisplayAlerts = "True";

&oWorkApp.Quit();

PeopleSoft : Save Project Peoplecode into file:

If you want to save the entire peoplecode of your project you can do it through application designer.
Bellow are the steps to save peoplecode to file.

In application designer
1.Go to Edit->Find In option.
2.Enter find character as semicolon(;) and select your project.
3.Check the Save PeopleCode to file option.
4.Click Find button it will ask a file name.
5.Provide the file name and location where you want to save the file.

Then the peoplecode of your project stored into file.