Creating Outbound File from File Layout Using App Engine

Sometimes we to write program that are used for outbound processing, meaning creating files that will be read by other/external programs. Most of the times these files are enormous in size, because they are years or quarters or months worth of data. We need to create a program that are efficient in processing large amount of data. With these in mind, we think of App Engine because its used for bulk processing. But App Engine is not built for reporting purposes. Of course we can used SQR, it can process data and its built for reporting. The only problem is the performance when processing large amount of data. My solution is to use File Layout to create files;

1. Create an App Engine that will process all the data.
2. Dump all processed data into staging tables.
3. Create a view out of those staging tables. The view structure will be similar to file layout segment. If you have a parent-child relationship on the file layout, the view key structure should be similar to those file layout.
4. Let say you have a level 1 parent-child relationship in your file layout;


If All(&FileDirectory) Then
&TST_FILE = GetFile(&FileDirAndName, "A", %FilePath_Absolute);
Else
&TST_FILE = GetFile(&FileName, "A", %FilePath_Relative);
End-If;


&TST_FILE.SetFileLayout(FileLayout.FILE_LAYOUT_NAME);
&RS_TST_FL = &TST_FILE.CreateRowset();
&REC_TST_FL_LVL0 = &RS_TST_FL(1).GetRecord(Record.SGMT_TST_LVL0);
&REC_TST_FL_LVL1 = &RS_TST_FL(1).GetRowset(Scroll.SGMT_TST_LVL1).GetRow(1).GetRecord(Record.SGMT_TST_LVL1);


/*These will be the view created from staging*/
&REC_TST_LVL0_VW = CreateRecord(Record.BN_TST_LVL0_VW);

/*Copy data from the view to file layout segment level 0*/
&REC_TST_LVL0_VW .CopyFieldsTo(&REC_TST_FL_LVL0);

/*Write data from file layout*/
&TST_FILE.WriteRecord(&REC_TST_FL_LVL0);

/*These will be the view created from staging*/
&REC_TST_LVL1_VW = CreateRecord(Record.BN_TST_LVL1_VW);
/*Copy data from the view to file layout segment level 1*/
&REC_TST_LVL1_VW .CopyFieldsTo(&REC_TST_FL_LVL1);
/*Write data from file layout*/
&TST_FILE.WriteRecord(&REC_TST_FL_LVL1);

Calling SQR from PeopleCode (Page, Record, App Engine)

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;

Component Interface for Student Award Entry

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;

Creating Word Document Using App Engine!

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();

Creating MS EXCEL Using CreateObject in PeopleSoft

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

App Engine running XML Publisher with PS Query Data Source

Haven't you notice that every posts up to this point are XML Publisher related? I just can get enough of this tools, i like discovering what this tool can do for me as a developer. If using Rowset Data Source in XMLP was too complicated for you, try using the PS Query as the Data Source. You don't need to create a code for sample data file and schema file because you can generate those files within PeopleSoft. Just replace the Rowset code in the XML Publisher Part 2 with this code;

/*fill prompt record*/
&rcdQryPrompts = &ReportDef.GetPSQueryPromptRecord();
If Not &rcdQryPrompts = Null Then
If Not Prompt(&ReportDef.GetDataSource().Name, "", &rcdQryPrompts) Then
Exit;
End-If;
&ReportDef.ProcessReport(&sTemplateId, %Language_User, %Date, &sOutputFormat);
End-If;

Sending Email Using Application Engine (XML Publisher Report as Attachment)

Our understanding of XML Publisher are getting broader. There are a lot that you can do with this tool, I decided to incorporate the email functionality of PeopleSoft with the XML Publisher. How would you like if the batch that produced report output last night can be emailed to you automatically? I know a lot of people would love that!

With our knowledge on how to code XMLP report, we will just add codes to our existing PeopleCode in XML Publisher Part 2 that tells the PeopleSoft to send that report to an email. Here's the code (added codes are in bold text).
import PSXP_RPTDEFNMANAGER:*;
import PSXP_XMLGEN:*;
import PT_MCF_MAIL:*;

/*Create an email object by setting individual parameters*/
Local PT_MCF_MAIL:MCFOutboundEmail &eMail = create PT_MCF_MAIL:MCFOutboundEmail();

&sRptDefn = "JOB_DEFN";
&sTemplateId = "JOB_TEMP";
&sLangCode = "";
&dtAsOfDate = %Date;
&sOutputFmt = "PDF";
&RptOutputDir = "c:\temp\" "XMLP";

&ReportDef.OutDestination = &RptOutputDir;

/*Set-Up Report*/
&ReportDef = create PSXP_RPTDEFNMANAGER:ReportDefn(&sRptDefn);
&ReportDef.Get();

/*Create Rowset*/
&rs = CreateRowset(Record.PERSONAl_DATA);

/*Fill Rowset*/&rs.FILL("WHERE FILL.EMPLID LIKE 'EID000%'");

/*Create Schema*/
&rds = create PSXP_XMLGEN:RowSetDS(); /*package method*/
&mySchema = &rds.GetXSDSchema(&rs);
&f1 = GetFile("c:\temp\JOB_XSD.xsd", "W", %FilePath_Absolute);
&f1.WriteLine(&mySchema);
&f1.Close();

/*Create Sample XML File*/
&myXMLFile = &rds.GetXMLData(&rs, "c:\temp\JOB_XSD.xsd");
&f2 = GetFile("c:\temp\JOB_XML.xml", "W", %FilePath_Absolute);
&f2.WriteLine(&myXMLFile);
&f2.Close();

/* output format */
&sOutputFormat = &sOutputFmt;

/*Provide a Data Source for the Report*/
&ReportDef.SetRuntimeDataRowset(&rs);

/*Generate the Report*/
&ReportDef.ProcessReport(&sTemplateId, %Language_User, %Date, &sOutputFormat);

/*Publish the Report*/
&ReportDef.Publish("", &RptOutputDir, "XMLP", JOB_AET.PROCESS_INSTANCE);
&sFileExt = GetFileExtension(&sOutputFormat);


/*Send Mail*/&ToList = "to_user@yahoo.com";&FromList = "from_user@acme.com";&ReplyToList = "from_user@acme.com";
&Subject = "Batch Run Email";
&eMail.Recipients = &ToList; /*comma separeted list of email addresses*/&eMail.From = &FromList; /*from email address*/&eMail.ReplyTo = &ReplyToList; /*in case the reply is to be sent to a different email address*/
&eMail.Subject = &Subject;

/*Body for multiple parts*/
Local string &plain_text = "Test for XML Email from PeopleSoft";
Local PT_MCF_MAIL:MCFBodyPart &text = create PT_MCF_MAIL:MCFBodyPart();
&text.Text = &plain_text;

Local PT_MCF_MAIL:MCFBodyPart &attach = create PT_MCF_MAIL:MCFBodyPart();&attach.SetAttachmentContent(&RptOutputDir "JOB_DEFN.pdf", %FilePath_Absolute, "JOB_DEFN.pdf", "JOB_DEFN", "", "");

Local PT_MCF_MAIL:MCFMultiPart &mp = create PT_MCF_MAIL:MCFMultiPart();
&mp.AddBodyPart(&text);
&mp.AddBodyPart(&attach);
&eMail.Multipart = ∓

/*Override the default SMTP parameters specified in app server configuration file*/
&eMail.SMTPServer = "smtp.service.acme.com"; /*just an example*/
&eMail.SMTPPort = 25; /*usually this is 25 by default*/

Local integer &resp = &eMail.Send();
/*now check &resp for the result*/
Local boolean &done;
Evaluate &resp
When %ObEmail_Delivered
/*every thing ok*/
&done = True;Break;
When %ObEmail_NotDelivered
/*check &eMail.InvalidAddresses, &eMail.ValidSentAddresses and &eMail.ValidUnsentAddresses*/
&done = False;Break;
When %ObEmail_PartiallyDelivered/*check &eMail.InvalidAddresses, &eMail.ValidSentAddresses and &eMail.ValidUnsentAddresses*/
&done = True;Break;
When %ObEmail_FailedBeforeSending
/*get the formatted messages from &eMail.ErrorDescription, &eMail.ErrorDetails*/
&done = False;
Break;
End-Evaluate;

CommitWork();