This is a request from a friend to post a sample code that calls SQR from PeopleCode.This can be applicable if you need your page or app engine to call an independent SQR process/report. The following example was done in App Engine PeopleCode;
Local ProcessRequest &RQST;
&sRunCntlId = "Test";&aProcessType = "SQR Report"; /*(or "SQR Process")*/&aProcessName = "MYSQRRPT";
&dttmRunDateTime = %Datetime;
&aTimeZone = "EST";
&aOutDestType = "WEB";
&aOutDestFormat = "PDF";
/* Create the ProcessRequest Object */
&RQST = CreateProcessRequest();
/* Set all the Required Properties */
&RQST.RunControlID = &aRunCntlId;
&RQST.ProcessType = &aProcessType;
&RQST.ProcessName = &aProcessName;
/* Set any Optional Properties for this Process */
&RQST.RunDateTime = &dttmRunDateTime;
&RQST.TimeZone = &aTimeZone;
&RQST.OutDestType = &aOutDestType;
&RQST.OutDestFormat = &aOutDestFormat;
/* Schedule the Process */
&RQST.Schedule();
If &RQST.Status = 0 Then
Else
End-If;
When coding page PeopleCode, developers uses function SQLExec once or twice, and even more than 10. It looks messy and sometimes you have to deal with lenghty SQL statements. What I have are codes taken from a delivered program that simplify large amount of SQLExec coding (I modified some of it according to my needs). Transfer all SQL statements from peoplecode to SQL objects and named it appropriately (e.g. HR_PERS_INSERT_01 when Insert statement , and HR_PERS_UPDATE_01 when Update statement). These SQL Object names are stored in PeopleSoft table, so I created a view that query PSSQLDEFN. The SQL statement for that view would look like these:SELECT SQLID FROM PSSQLDEFN WHERE SQLID LIKE 'HR_PERS%' AND SQLTYPE = '0'As you can see the SQLTYPE = '0' represents stand alone SQL objects. The requirements changes when a particular condition is true the program must use inserts statements and if false it uses update statements. Here's the code;
&RS_SQL = CreateRowset(Record.HR_PERS_VW);If &DataInsert = true &RS_SQL.Fill("WHERE SQLID LIKE 'HR_PERS_INS%'");else &RS_SQL.Fill("WHERE SQLID LIKE 'HR_PERS_UPD%'");End-If;&RS_SQL.Sort(HR_PERS_VW.SQLID, "A"); /*A for Ascending and D for Descending*/For &I = 1 To &RS_SQL.ActiveRowCount &SQLName = &RS_SQL.GetRow(&I).HR_PERS_VW.SQLID.Value; SQLExec(@("SQL." &SQLName), &DeptID);End-For;
If you expand the SQLExec code, it will look like this;SQLExec("Update PS_Table Set FLAG = 'Y' where DEPT_ID =:1", &DeptID);
This is more specific to Student Award Entry Component. Recently, I have a requirement to automate the cancellation of award in the Award Entry page (PeopleSoft Student Administration: Financial Aid). The approach was to make a Component Interface and run it thru App Engine as batch process. In the page there are 4 buttons and 2 of these buttons should be click to validate and post data in the database. I created a new custom methods in my CI and put the peoplecode behind the button so that I can just call these methods in my AppEngine, but I encountered an error while running my program. I learned that I can't use RemoteCall inside the CI method. So after several trial and error, I came up with the solution to separate the remotecall from other codes and put it on the AppEngine PeopleCode. The AppEngine/CI Peoplecode looks like these;Function Validate()commitwork();RemoteCall(call validateCOBOL program.......);End-Function;Function Post()commitwork();
RemoteCall(call postCOBOL program.......);
End-Function;try &ci = &session.GetCompIntfc(CompIntfc.yourCI);...&ci.PreValidate(); /*my custom CI method contains code to validate before RemoteCall*/Validate(); &ci.PostValidate(); /*my custom CI method contains code to validate after RemoteCall*/&ci.PrePost(); /*my custom CI method contains code to post before RemoteCall*/
Post();
&ci.Posted(); /*my custom CI method contains code to post after RemoteCall*/...end-try;
Now, who's up for some ice cream? Yes, you've heard me.. for me Word Document is like an ice cream at least compare to text (.txt) document. Did you know that you can create a formatted word document from PeopleSoft using App Engine/PeopleCode? Just like creating Excel Document (discuss in my previous post Creating MS EXCEL Using CreateObject in PeopleSoft) I used the CreateObject PeopleCode Function to create Word Document. Note: I only tested this code thru NT Server.
&oWORD = CreateObject("COM", "Word.Application");
ObjectSetProperty(&oWORD, "Visible", True);
&oWORD.Documents.Add();
&oPara1 = &oWORD.Selection;
&oPara1.Style = "Heading 1";
&oPara1.TypeText("Hello World ");
&oPara1.Font.Bold = True;
&oPara1.TypeText("Bold ");
&oPara1.Font.Bold = False;
&oPara1.Font.Italic = True;
&oPara1.TypeText("Italic ");
&oPara1.Font.Italic = False;
&oPara1.Font.Underline = True;
&oPara1.TypeText("Underline ");
&oPara1.Font.Underline = False;
&oPara1.Font.Name = "Arial";
&oPara1.TypeText("Arial ");
&oPara1.Font.Name = "Times Roman";
&oPara1.Font.Size = "16";
&oPara1.TypeText("Times Roman ");
&oPara1.Font.Size = "12";
&oPara1.TypeText("Example ");
&oPara1.Start = "50";
&oPara1.End = "59";
&oPara1.Font.Name = "Tahoma";
&oWORD.ActiveDocument.SaveAs("C:\temp\Format.doc");
&oWORD.Quit();
Application Engine is great for batch processing, but it is not a reporting tool. Recently, our users requires every single AE to have a formatted output report, so my teammate came up with the solution that writes .txt file using writeline function. Its very simple, you just need to code it manually. Getting bored of the text file that comes out on my report, I look for some other ways to create a more decent output. Of course, XML Publisher would have been nice but I dont want to go thru that hassle creating a report definition, data source, etc. I browse thru PeopleBooks and found this PeopleCode function CreateObject! It has an example using Excel.Application, a few trial and error, and voala! I created an Excel Report! I only tested it on the NT Server though.
/*How to read data from one cell and writes to another*/
&oWorkApp = CreateObject("COM", "Excel.Application");
ObjectSetProperty(&oWorkApp, "Visible", True);
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");
&oWorkBook.Open("C:\temp\TEST1.xls");
&oWorkSheet = &oWorkApp.Worksheets("Sheet1");
&oData = &oWorkSheet.Range("A1").Value;
&oWorkSheet.Range("A2").Value = &oData;
&oWorkApp.ActiveWorkbook.SaveAs("C:\temp\TestXLS.xls");
/*How to read data from one cell and writes to different sheet*/
&oWorkApp = CreateObject("COM", "Excel.Application");
ObjectSetProperty(&oWorkApp, "Visible", True);
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");
&oWorkBook.Open("C:\temp\TEST1.xls");
&oWorkSheet = &oWorkApp.Worksheets("Sheet1");
&oData = &oWorkSheet.Range("A1").Value;
&oWorkSheet2 = &oWorkApp.Worksheets(2);
&oWorkSheet2.Range("A1").Value = &oData;
&oWorkApp.ActiveWorkbook.SaveAs("C:\temp\TestXLS.xls");
/*Add data to cells of the first worksheet in the new workbook*/
&oWorkApp = CreateObject("COM", "Excel.Application");
ObjectSetProperty(&oWorkApp, "Visible", True);
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");
&oWorkBook.Open("C:\temp\TEST1.xls");
&oWorkSheet = &oWorkApp.Worksheets("Sheet1");
&oWorkSheet.Range("A1").Value = "Last Name";
&oWorkSheet.Range("B1").Value = "First Name";
&oWorkSheet.Range("A1:B1").Font.Bold = True;
&oWorkSheet.Range("A2").Value = "Doe";
&oWorkSheet.Range("B2").Value = "John";
&oWorkApp.ActiveWorkbook.SaveAs("C:\temp\TestXLS.xls");
/*Transfer the data to Excel from Rowset*/
&oWorkApp = CreateObject("COM", "Excel.Application");
ObjectSetProperty(&oWorkApp, "Visible", True);
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");
&oWorkBook.Open("C:\temp\TEST1.xls");
&oWorkBook = &oWorkApp.Workbooks.Add();
&rs_Awards = CreateRowset(Record.PERTBL);
&rs_Awards.Fill("WHERE FILL.YEAR = '2008' AND FILL.STATUS = 'C'");
&oWorkSheet = &oWorkApp.Worksheets("Sheet1");
For &ie = 1 To &rs_Awards.activerowcount
&oWorkSheet.Cells(&ie, 1).Value = &rs_Awards.getrow(&ie).PERTBL.ID.Value;
&oWorkSheet.Cells(&ie, 2).Value = &rs_Awards.getrow(&ie).PERTBL.COMP.Value;
End-For;
&oWorkApp.ActiveWorkbook.SaveAs("C:\temp\TestXLS.xls");
/*Save an xls file as a CSV*/
&oWorkApp = CreateObject("COM", "Excel.Application");
ObjectSetProperty(&oWorkApp, "Visible", True);
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");
&oWorkBook.Open("C:\temp\TEST1.xls");
&oWorkApp.ActiveWorkbook.SaveAs("C:\temp\TestXLS.csv", 6);
