Using Dynamic View

Since I wrote the article titled "Running Dynamic SQL Object in PeopleCode" I got a hand full of feedback that the title is confusing. They always thought that it was about Dynamic View, well its probably a bad choice of title on my part. I will make it up to anyone who's looking for the Dynamic View code or how to use it.

Suppose the requirements is to have a different prompt table depending on the settings of other fields. To illustrate, if the user enter name on the other field, they want to see a prompt of personel number. If they enter product information, they want to see the inventory number, lot number, etc.. You could use %EDITTABLE to dynamically specify the prompt table. However
in this case there are too many possible values or involved combinations . These will require you to create too many views. It would be nice if you can create view on the fly depending on what the end-user wants. The answer is YES, we can do that, we can generate the desired SQL text in the PeopleCode based on what the user enters.

1. Get the field that has a promt using GetField function.
&fld_CarNbr = GetField(Field.CAR_NBR);
2. Get all the information user enters.
&data_Name = CAR_TBL.NAME;
&data_Model = CAR_TBL.MODEL;
3. Create the SQLTEXT.
&sqltext = "SELECT ID, NAME, MODEL, NBR FROM INV_TBL WHERE NAME = " &data_Name " AND MODEL = " &data_Model;
4. Override Dynamic View SQL
&fld_StdntCarNbr.SqlText = &sqltext;


Here another example. These replaces the data of the dynamic view.

&sqltext = CreateSQL("SELECT ID, NAME, MODEL, NBR FROM INV_TBL WHERE NAME = " &data_Name " AND MODEL = " &data_Model);

&RSTemp = GetRowset(Scroll.DYNAMIC_VW);
&RSTemp.Flush();
&Rec = CreateRecord(Record.DYNAMIC_VW);
While &Sql.Fetch(&Rec) &i = &i + 1;
&RSTemp.InsertRow(&i);
&Rec.CopyFieldsTo(&RSTemp.GetRow(&i).DYNAMIC_VW);
End-While;

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;

Running Dynamic SQL Object in PeopleCode

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

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;