Rowset FILL Method with JOIN / UNION Keyword

FILL method helps to fill stand alone rowset from database directly.

But sometimes it is required to fill rowset Using JOIN / UNION Keyword.

1. Using JOIN Keyword.

Local Rowset &rs = Createrowset(Record.TL_RPTD_TIME);

&rs.Fill("INNER JOIN PS_TL_RPTD_TIME TM ON TM.EMPLID = FILL.EMPLID WHERE FILL.PUNCH_TYPE = '0' AND FILL.EMPLID = :1", &emplid);

2. Using UNION Keyword.

Local Rowset &rs = Createrowset(Record.PERSON);

&rs.Fill("WHERE 1=0 UNION SELECT EMPLID, %DateOut(BIRTHDATE),BIRTHPLACE,BIRTHCOUNTRY,BIRTHSTATE,%DateOut(DT_OF_DEATH),%DateTimeOut(LAST_CHILD_UPDDTM) FROM PS_PERSON WHERE EMPLID = :1", &emplid);

Sending Multiple attachment using MCF Send mail method.

Below is the sample code to send multiple attachment in mail using MCF Send() mail method.

Local any &FromURL = "record://PV_ATT_DB_SRV/";
Local string &FILENAME;
Local Rowset &rwln1 = CreateRowset(Record.PV_ATTACHMENTS);
Local SQL &Sql1 = CreateSQL("select attachsysfilename from PS_PV_ATTACHMENTS a,ps_SPF_RESUME_ATT b where a.scm_attach_id=b.scm_attach_id  and b.person_id=:1", "000000000000025", &FILENAME);

While &Sql1.Fetch(&FILENAME)
   &attach = create PT_MCF_MAIL:MCFBodyPart();
   Local string &ToFile = "C:/user/" | &FILENAME;
   Local any &errorCode = GetAttachment(&FromURL, &FILENAME, &ToFile);

   If &i = 1 Then
      &multiattach = CreateArray(&attach);
      &multiattach [&i] = &attach;
   Else
      &multiattach [&i] = &attach;
   End-If;
   &multiattach [&i].SetAttachmentContent(&ToFile, %FilePath_Absolute, &FILENAME, " ", "", "");
   &mp.AddBodyPart(&multiattach [&i]);
End-While;

Local PT_MCF_MAIL:MCFBodyPart &test1 = create PT_MCF_MAIL:MCFBodyPart();
Local PT_MCF_MAIL:MCFBodyPart &eMail1 = create PT_MCF_MAIL:MCFBodyPart();

&eMail1.Text = &Message;
&mp.AddBodyPart(&eMail1);
&eMail.MultiPart = ∓
&res = &eMail.Send();

Local boolean &done;

Evaluate &res
When %ObEmail_Delivered
   /* every thing ok */
   &done = True;
   MessageBox(0, "", 0, 0, "Email Sent Successfully");
   Break;
When %ObEmail_NotDelivered
   /* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
   /* Check &email.InvalidAddresses, &email.ValidSentAddresses */
   /*   and &email.ValidUnsentAddresses                        */
   /* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
   &done = False;
   MessageBox(0, "", 0, 0, "Email Not delivered" | &eMail.InvalidAddresses | &eMail.ValidSentAddresses | &eMail.ValidUnsentAddresses);
   Break;
When %ObEmail_PartiallyDelivered
   /* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
   /* Check &email.InvalidAddresses, &email.ValidSentAddresses */
   /*   and &email.ValidUnsentAddresses                        */
   /* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
   &done = True;
   MessageBox(0, "", 0, 0, "Email Partially delivered" | &eMail.InvalidAddresses | &eMail.ValidSentAddresses | &eMail.ValidUnsentAddresses);
   Break;
When %ObEmail_FailedBeforeSending
   /* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
   /* Get the Message Set Number, message number;              */
   /*   Or just get the formatted messages from                */
   /*   &email.ErrorDescription, email.ErrorDetails;           */
   /* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
   &done = False;
   MessageBox(0, "", 0, 0, "Email Failed Before Sending" | &eMail.ErrorDescription | &eMail.ErrorDetails);
   Break;
End-Evaluate;

If &done = True Then
   While &Sql1.Fetch(&FILENAME)
      Local string &deleteFile = "C:/user/" | &FILENAME;
      Local JavaObject &f = CreateJavaObject("java.io.File", &deleteFile);
      &f.delete();
  End-While;
End-If;

Note: This is sample code. This required to include necessary application package and to declare objects.

Move and Delete files using PeopleCode

We can use the below java object and code to move or deleting files from some location.

Moving file:

Local JavaObject &source = CreateJavaObject("java.io.File", "/source/file.txt");
Local JavaObject &target = CreateJavaObject("java.io.File", "/target/file.txt");

&source.renameTo(&target); 

Deleting file:

Local JavaObject &DelLoc = CreateJavaObject("java.io.File", "/source/file.txt");

&DelLoc.delete(); 

Alternate code for deleting file:

&tmpfile = GetFile(“c:\temp\file.txt”, “W”, “A”, %FilePath_Absolute);
&tmpfile.Delete();

Add duration(number value) to Time field.

People Code to add duration to time field.

Example:

Here START_TIME, END_TIME is time field and DURATION is number field.

InPut: StartTime(START_TIME ) = 8:00AM and Duration(DURATION ) = 12

OutPut: EndTime(END_TIME) = 8:00PM

Sample Code:

Declare Function Return_DateTime_value PeopleCode TIME_TBL.START_TIME FieldFormula;
Declare Function Return_Hour_Min_Part PeopleCode TIME_TBL.START_TIME FieldFormula;

Local datetime &TIME_VALUE;
Local integer &MINUTES;

&DUR_VALUE = TIME_TBL.DURATION.Value;
&StartTime = TIME_TBL.START_TIME.Value;

Return_DateTime_value(&StartTime, &DUR_VALUE, &TIME_VALUE);
Return_Hour_Min_Part(&DUR_VALUE, &HOUR_PART, &MINUTES);

TIME_TBL.END_TIME.Value = TimePart(AddToDateTime(&TIME_VALUE, 0, 0, 0, &HOUR_PART, &MINUTES, 0));

Defining Functions:

Function Return_DateTime_value(&StartTime, &DUR_VALUE, &TIME_VALUE As datetime);
   Local date &RefDate;
   &RefDate = Date3(2000, 1, 1);
   &TIME_VALUE = &RefDate + &StartTime;
End-Function;

Function Return_Hour_Min_Part(&DUR_VALUE, &iHour, &iMinutes As integer)
   &temp = String(&DUR_VALUE);
   &iHour = Value(Substring(&temp, 1, 2));
   &iPos = (Find(".", &temp));
   If &iPos > 0 Then
      &sMinutes = Substring(&temp, &iPos + 1, 2);
      If Substring(&sMinutes, 1, 1) = "0" Or
            Len(&sMinutes) > 1 Then
         &iMinutes = Value(&sMinutes);
         &iMinutes = ((&iMinutes / 100) * 60)
      Else
         &iMinutes = Value(&sMinutes);
         &iMinutes = ((&iMinutes / 10) * 60)
      End-If;
   Else
      &iMinutes = 0;
   End-If;
End-Function;

Image URL from PeopleCode

Below code helps  to get Image URL.

%Response.GetImageURL(Image.PT_PIXEL);

Accessing Application Package class object using CreateObject method.

Let’s say we have 2 application package.

1. UserID and

2. Notification.

Here Notification application package uses dynamic changing application package say based on setup.

For our example we are using UserID application package as setup driven application package and it has UserID as Class.

Structure of UserID application package.

class UserID
   method GetOpridsByApplicationPack() Returns array of string;
   property array of string oprid_arr;
end-class;

method UserID
  
end-method;

method GetOpridsByApplicationPack
   /+ Returns Array of String +/
   
   %This.oprid_arr = CreateArrayRept("", 0);
   Local string &SupervisorId, &ReportsTo;
    
   %This.oprid_arr.push('PS'); /* Logic to populate data in array. */

   Return %This.oprid_arr;

end-method;

Below is “Notification” application package structure.

In this application package we are using CreateObject to access “GetOpridsByApplicationPack” method of UserID class of UserID application package .

We are passing Package name and Class Name while calling so they are variable for us. (In this case its UserID and UserID).

class NotificationManager
method GetOpridsByApplicationPack(&PkgRoot As string, &Appcls_Path As string) Returns array of string;

end-class;

method NotificationManager

end-method;

method GetOpridsByApplicationPack
   /+ &PkgRoot as String, +/
   /+ &Appcls_Path as String +/
   /+ Returns Array of String +/
   Local array of string &aryOpridTo;
   Local string &Classname;
   Local object &u;
   &aryOpridTo = CreateArrayRept(" ", 0);
   &Classname = &PkgRoot | ":" | &Appcls_Path;
   &u = CreateObject(&Classname);
   ObjectDoMethod(&u, "GetOpridsByApplicationPack");
   &aryOpridTo = &u.oprid_arr;
   Return &aryOpridTo;
end-method;

Exit(0) And Exit(1) Function in Application Engine.

Exit(0) – Terminates the current PeopleCode Action in application engine.

Exit(1) – Terminates the current Step in application engine.

Link To Portal Folder

The below code can be used for a link to navigate to your portal folder.

Declare Function NavPageURL PeopleCode EOPP_SCRTN_WRK.FUNCLIB FieldFormula;

&LINKURL = NavPageURL(%Portal, %Node, "your-folder-object-name", "PSC", "", "", "False", "", "", "");
%Response.RedirectURL(&LINKURL);

Functions with EFFDT and EFFSEQ to get row

1. Obtain the row number of Prior Effective date row.  Does not use row with same date and sequence number as itself.

Function prior_effdt_row(&EFFDT, &EFFSEQ, &PRIORDT, &PRIORSEQ, &PRIOR_ROW);
   &ACTIVE_ROW = ActiveRowCount(JOB.EMPLID);
   For &I = 1 To &ACTIVE_ROW;
      &FETCH_EFFDT = FetchValue(JOB.EFFDT, &I);
      &FETCH_EFFSEQ = FetchValue(JOB.EFFSEQ, &I);
      If (&FETCH_EFFDT < &EFFDT Or
            (&FETCH_EFFDT = &EFFDT And
               &FETCH_EFFSEQ < &EFFSEQ)) And
            (&FETCH_EFFDT > &PRIORDT Or
               (&FETCH_EFFDT = &PRIORDT And
                  &FETCH_EFFSEQ > &PRIORSEQ)) Then
         &PRIORDT = &FETCH_EFFDT;
         &PRIORSEQ = &FETCH_EFFSEQ;
         &PRIOR_ROW = &I;
      End-If;
   End-For;
End-Function;

2. Obtain the row number of Prior Effective date row. If it finds row with same effdt and seq row that is not itself, it uses that row.

Function prior_same_effdt(&EFFDT, &EFFSEQ, &CURRENT_ROW, &PRIORDT, &PRIORSEQ, &PRIOR_ROW);
   &ACTIVE_ROW = ActiveRowCount(JOB.EMPLID);
   For &I = 1 To &ACTIVE_ROW;
      &FETCH_EFFDT = FetchValue(JOB.EFFDT, &I);
      &FETCH_EFFSEQ = FetchValue(JOB.EFFSEQ, &I);
      If (&FETCH_EFFDT < &EFFDT Or
            (&FETCH_EFFDT = &EFFDT And
               &FETCH_EFFSEQ <= &EFFSEQ)) And
            (&FETCH_EFFDT > &PRIORDT Or
               (&FETCH_EFFDT = &PRIORDT And
                  &FETCH_EFFSEQ > &PRIORSEQ)) And
            &CURRENT_ROW <> &I Then
         &PRIORDT = &FETCH_EFFDT;
         &PRIORSEQ = &FETCH_EFFSEQ;
         &PRIOR_ROW = &I;
      End-If;
   End-For;
End-Function;

3. Obtain the row number of Next Effective date row

Function next_effdt_row(&EFFDT, &EFFSEQ, &NEXTDT, &NEXTSEQ, &NEXT_ROW);
   &NEXTDT = Date(30001231);
   &NEXTSEQ = 9;
   &ACTIVE_ROW = ActiveRowCount(JOB.EMPLID);
   For &I = 1 To &ACTIVE_ROW;
      &FETCH_EFFDT = FetchValue(JOB.EFFDT, &I);
      &FETCH_EFFSEQ = FetchValue(JOB.EFFSEQ, &I);
      If (&FETCH_EFFDT > &EFFDT Or
            (&FETCH_EFFDT = &EFFDT And
               &FETCH_EFFSEQ > &EFFSEQ)) And
            (&FETCH_EFFDT < &NEXTDT Or
               (&FETCH_EFFDT = &NEXTDT And
                  &FETCH_EFFSEQ < &NEXTSEQ)) Then
         &NEXTDT = &FETCH_EFFDT;
         &NEXTSEQ = &FETCH_EFFSEQ;
         &NEXT_ROW = &I;
      End-If;
   End-For;
End-Function;

4. Obtain the row number of First Effective date row.

Function first_effdt_row(&FIRSTDT, &FIRSTSEQ, &FIRST_ROW);
   &FIRSTDT = Date(30001231);
   &FIRSTSEQ = 9;
   &ACTIVE_ROW = ActiveRowCount(JOB.EMPLID);
   For &I = 1 To &ACTIVE_ROW
      &FETCH_EFFDT = FetchValue(JOB.EFFDT, &I);
      &FETCH_EFFSEQ = FetchValue(JOB.EFFSEQ, &I);
      If (&FETCH_EFFDT < &FIRSTDT Or
            (&FETCH_EFFDT = &FIRSTDT And
               &FETCH_EFFSEQ < &FIRSTSEQ)) Then
         &FIRSTDT = &FETCH_EFFDT;
         &FIRSTSEQ = &FETCH_EFFSEQ;
         &FIRST_ROW = &I;
      End-If;
   End-For;
End-Function;

5. Obtain the row number of row matching date and sequence.

Function det_effdt_row(&EFFDT, &EFFSEQ, &ROW);
   &ACTIVE_ROW = ActiveRowCount(JOB.EMPLID);
   For &I = 1 To &ACTIVE_ROW
      &FETCH_EFFDT = FetchValue(JOB.EFFDT, &I);
      &FETCH_EFFSEQ = FetchValue(JOB.EFFSEQ, &I);
      If &FETCH_EFFDT = &EFFDT And
            &FETCH_EFFSEQ = &EFFSEQ Then
         &ROW = &I;
         &I = &ACTIVE_ROW
      End-If;
   End-For;
End-Function;

Schedule Application Engine through Peoplecode

The following sample code can be used to Schedule App Engine through PeopleCode.

Code to Schedule App Engine thru PeopleCode:

Local ProcessRequest &MYRQST;
   
   &MyAppName = "MY_APP";
   &MYRQST = CreateProcessRequest("Application Engine", &MyAppName);
   &MYRQST.RunControlID = "TEST";
   &MYRQST.RunLocation = "PSUNX";       
   &MYRQST.Schedule();
   
   If &MYRQST.Status = 0 Then /* if Schedule status is success */
 
  End-If;

Code to know whether the scheduled App Engine ran to success or not:

If &MYRQST.Status = 0 Then /* if Schedule status is success */

      &LOOP = 0;
      While &LOOP = 0
         SQLExec("SELECT A.DISTSTATUS, A.RUNSTATUSDESCR FROM PS_PMN_PRCSLIST A WHERE A.PRCSNAME = :1 AND A.PRCSINSTANCE = (SELECT MAX(B.PRCSINSTANCE) FROM PS_PMN_PRCSLIST B WHERE B.PRCSNAME = A.PRCSNAME)", &MyAppName, &POSTED, &STATUS);
         
         If &STATUS = "Success" And
               &POSTED = 5 Then /* Posted */
            &LOOP = 1
         End-If;
         
         If &STATUS = "Success" And /* Not Posted */
               &POSTED = 4 Then
            &LOOP = 2
         End-If;
         
         If &STATUS = "No Success" Or
               &STATUS = "Error" Then
            &LOOP = 3;
         End-If;
         
      End-While;
      
      If &LOOP = 1 Then
            MessageBox(0, "", 27333, 594, "");  /* Success Msg */
      Else
         If &LOOP = 2 Or
               &LOOP = 3 Then
            MessageBox(0, "", 27333, 595, "");  /* Failed Error Msg */
         End-If;
      End-If; /* If &LOOP = 1 Then */ 
End-If;

The below function can also be used for schedule application engine: (Alternative Function)

Function ScheduleSetupAEProcess(&RESULT_SET_COMP)
   &RUN_SETUP_AE_REQST = CreateProcessRequest("Application Engine", "PTLT_SETUP");
   &REC_IMPL_RUN = CreateRecord(Record.PTLT_PROJ_RUN);
   
   /* Run Control ID = Configuration Set Name */
   &RUN_CNTL_ID = &RESULT_SET_COMP;
   
   &RUN_SETUP_AE_REQST.RunControlID = &RUN_CNTL_ID;
   &RUN_SETUP_AE_REQST.Schedule();
   
   If &RUN_SETUP_AE_REQST.Status = 0 Then
      &PROCESS_INSTANCE = &RUN_SETUP_AE_REQST.ProcessInstance;
      /* Insert into Setup Manager Run Control Record */
      &REC_IMPL_RUN.OPRID.Value = %OperatorId;
      &REC_IMPL_RUN.RUN_CNTL_ID.Value = &RUN_CNTL_ID;
      &REC_IMPL_RUN.PTLT_PROJ_NAME.Value = &RESULT_SET_COMP;
      &REC_IMPL_RUN.PROCESSINSTANCE.Value = &PROCESS_INSTANCE;
      
      If Not &REC_IMPL_RUN.Update() Then
         &REC_IMPL_RUN.Insert();
      End-If;
   Else
      Error MsgGet(218, 154, "Process cannot be scheduled");
   End-If;
End-Function;

Object–Based PeopleCode

 

What is Object – Based PeopleCode:

• Introduced in PeopleTools 8
• Evolution of procedural-based PeopleCode
• Provides for complex object definitions
• Overcomes procedural data scope limitations
• Similar to Visual Basic notation
• Interpreted (like VB)
• Not completely object oriented (like C++), but it’s getting there!
• Does not support polymorphisms
• Does support multiple inheritance
• Backwards compatible

Why do we need Object Based PeopleCode:

• Greater ability to write generic code
• Release 8 Integration
                       1. Component Interface
                       2. Application Messaging
                       3. Business Interlinks
• Ability to define custom classes (Release 8.4)
                       1. Application Classes/Packages
                       2. Replaces FUNCLIB Processing

Advantage of Object and Methods:

• Tight code
• Syntax is validated*
• No or low maintenance required

Object – Based PeopleCode Rule:

• DECLARE the object
• INSTANTIATE the object

Object–Based Code to avoid use of SQLEXEC

 

Below code can be used to avoid use of SQLExec.

SQLExec Code:

SQLExec to copy a row from ORD_HDR to ORD_HDR_EXT.

&ORD_NO = &NEW_ORD_NO;

SQLExec("select %timeout(order_dt), training_loc, vendor_cd, order_status, status_dt, deliver_method from ps_ord_hdr
where order_nbr = :1", &ORD_NO, &ORD_DT, &TRAIN_LOC,&VEND_CD, &ORD_STAT, &STAT_DT, &SHIP_VIA);

SQLExec("delete from ps_ord_hdr_ext where order_nbr = :1", &ORD_NO);

SQLExec("insert into ps_ord_hdr_ext (order_nbr, order_dt,training_loc, vendor_cd, order_status, status_dt,
deliver_method) values(:1,%datein(:2),:3,:4,:5,:6,:7)",&ORD_NO, &ORD_DT, &TRAIN_LOC, &VEND_CD, &ORD_STAT, &STAT_DT,&SHIP_VIA);

Same logic using Object Based code:

Local Record &REC1, &REC2;
&REC1 = GetRecord(RECORD.ORD_HDR);
&REC2 = CreateRecord(RECORD.ORD_HDR_EXT);
&REC1.ORD_NO = &NEW_ORD_NO;
&REC1.SelectByKey();
&REC1.CopyFieldsTo(&REC2);
&REC2.Delete();
&REC2.Insert();

Hiding Delivered Buttons based on condition

One way to hide PeopleSoft delivered component events buttons (Save, ReturnToList, Add, UpdateDisplay…. etc.) by using JavaScript. Follow the below steps to hide the delivered buttons.

This way can be used if we have Many pages in component and we don’t want delivered buttons on some pages. Here are steps.

Step1: Place HTML area in page and assign derived and work record (Say: HIDE_WRK)and field (Say: HTMLAREA) to it.

Create HTML Definition(Say: HIDE_BT_HTML) and Add following JavaScript.

<input type="hidden" name="USERJSINJECTION" value=""/>
<script type="text/javascript">
function addLoadEvent(func) {
  var oldonload = window.onload;
  if (typeof window.onload != 'function') {
   window.onload = func;
  } else {
   window.onload = function() {
     oldonload();
     func();
   }
  }
}
 
function appsDisablePTControls() { 

var aPTButtons = ['#ICSave', '#ICList', '#ICNextInList', '#ICPrevInList', '#ICSendNotify', '#ICNext', '#ICSpellCheck', '#ICRefresh', '#ICAdd', '#ICUpdateAll', '#ICCorrection', '#ICUpdate']; 

appsDisableTable(aPTButtons,true); 
} 
function appsDisableTable(aPTControls,bButtons) { 

var aControls, oTableNode; 

var bFound = false; 

var i=0; 

while (i < aPTControls.length && !bFound) 

{ 

aControls = document.getElementsByName(aPTControls[i]); 

if (aControls != null && aControls.length > 0) 

{ 

oTableNode = aControls[aControls.length-1].parentNode.parentNode.parentNode.parentNode; 

if (oTableNode != null && oTableNode.id != 'RBTBHEADER' && oTableNode.id != 'RBTBFOOTER') 

{ 

if (bButtons) 

oTableNode = oTableNode.parentNode; 

oTableNode.style.display='none'; 

bFound = true; 

} 

} 

i++; 

} 
} 
addLoadEvent(function() {
  %bind(:1)
});
</script>

Then assign the following code to Page Activated event:

HIDE_WRK.HTMLAREA.Value = GetHTMLText(HTML.HIDE_BT_HTML, "appsDisablePTControls()");

Note: Refer the post for how to use Html Area: http://pawan-mundhra.blogspot.com/2011/05/run-javascript-on-your-peoplesoft-pages.html

Run JavaScript on your PeopleSoft pages conditionally

Here, PeopleCode sets the logic that determines when the JavaScript code will run.

This is not as simple as dropping a HTML Area on your page and setting the script in PeopleCode. This is because the value in the HTML Area field remains and the JavaScript code will keep executing at subsequent page refreshes.

Steps:

Lets have a derived/work record TEST_WRK And field HTMLAREA (TEST_WRK – HTMLAREA).

1. Create a HTML definition as your javascript template. Include all the necessary user-defined javascript functions that you need. Eg Html Definition is TESTJS

<input type="hidden" name="USERJSINJECTION" value=""/>
<script type="text/javascript">
function addLoadEvent(func) {
  var oldonload = window.onload;
  if (typeof window.onload != 'function') {
   window.onload = func;
  } else {
   window.onload = function() {
     oldonload();
     func();
   }
  }
}
 
function user_function1() {
  window.open("","toolbar = no");
}
function user_function2() {
  alert('Hello from user javascript');
}
 
addLoadEvent(function() {
  %bind(:1)
});
</script>

2. At the scroll level 0 of your PS page, insert a HTML Area control. Assign this to the TEST_WRK.HTMLAREA field.

3. Again at scroll level 0 of your page, insert an editbox and assign this again to TEST_WRK.HTMLAREA. And Set the Following Field Property

a. On the Use tab, check Invisible and Modifiable by JavaScript.

b. On the General tab, set Page Field Name to USERJSINJECTION.

4. Now in PeopleCode, to execute your javascript function.

GetLevel0()(1).TEST_WRK.HTMLAREA.Value = GetHTMLText(HTML.TESTJS, "user_function1()");

Thanks to http://xtrahot.chili-mango.net/.

Code to Schedule App Engine through PeopleCode

The following code can be used to schedule App Engine Through PeopleCode.

Local ProcessRequest &MYRQST;
&MyAppName = "TEST_AE";
&MYRQST = CreateProcessRequest("Application Engine", &MyAppName);
&MYRQST.RunControlID = "TEST";
&MYRQST.RunLocation = "PSUNX";
&MYRQST.Schedule();
If &MYRQST.Status = 0 Then /* if Schedule status is success */
End-If;

GenerateComponentContentURL

The below sample PeopleCode can be used for Generate Component Content URL.

If All(SCH_MGR_SRCH_VW.EMPLID.Value) Then
   Local string &URL;
   &URL = GenerateComponentContentURL(%Portal, %Node, MenuName.ROLE_MANAGER, "GBL", Component.TL_MGR_USER_PREF, Page.SCH_EE_PREF, "C", SCH_MGR_SRCH_VW.EMPLID);
   ViewContentURL(&URL);
End-If;

PSPCMPROG - Table stores PeopleCode of the objects | Explanation

Below information about the table that stores PSPCMPROG the PeopleCode of objects.
PSPCMPROG -
Key Fields :OBJECTID1, OBJECTID2, OBJECTID3, OBJECTID4, OBJECTID5, OBJECTID6, OBJECTID7
ObjectID values reference

1	Record
2	Field
3	Menu
4	Bar Name
5	Item Name
9	Page
10	Component
12	Event
20	Database Type
21	Effective Date
39	Market
60	Message
66	Application Engine Program
74	Component Interface
77	Section
78	Step
87	Subscription
104	Application Package
105	Class
106	Class
107	Class

Key Fields: OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3, OBJECTVALUE4, OBJECTVALUE5, OBJECTVALUE6, OBJECTVALUE7
Refers to object name.
Key Field: PROGSEQ
Refers to Program sequence number
Non Key Fields:
VERSION                 Version 
NAMECOUNT           ?? 
PROGLEN                 PeopleCode Program Length 
PROGRUNLOC           Program Run Location 
PROGFLAGS             ?? 
LICENSE_CODE         License Code 
LASTUPDDTTM         the date and time of the last update to the entry
LASTUPDOPRID        the OPRID which made the last update to the entry 
PROGEXTENDS         ?? 
PROGTXT                 PeopleCode Program

Changing/Selecting Grid value based on Condition

The following PeopleCode can be used to change the grid value based on condition.

Local Rowset &rsGrid, &rsDataSource;

&rsGrid = GetLevel0().GetRow(1).GetRowset(SCROLL.TEST_VW);
&rsGrid.Flush();

&rsDataSource = CreateRowset(Record.TEST_VW);
&rsDataSource.Flush();
&rsDataSource.Fill(" WHERE EMPLID = :1 AND EMPL_RCD = :2 " &sEmplId,&nEmplRcd);
&rsDataSource.CopyTo(&rsGrid);

 

The following code can be used to Select grid value based on FieldChange(any condition) event.

&LVL1 = GetLevel0().GetRow(1).GetRowset(Scroll.PER_CHECKLIST);
&LVL2 = &LVL1(CurrentRowNumber()).GetRowset(Scroll.PER_CHKLST_ITM);

If &LVL2.ActiveRowCount = 1 And
      &LVL2(1).IsNew And
      Not &LVL2(1).IsChanged Then
Else
   For &i = &LVL2.ActiveRowCount To 1 Step - 1
      &LVL2.DeleteRow(&i);
   End-For;
End-If;

If All(PER_CHECKLIST.CHECKLIST_CD) Then
   
   &LVL2.SelectNew(Record.CHECKLIST_ITEM, "WHERE CHECKLIST_CD = :1 and EFFDT = (SELECT MAX(A.EFFDT) FROM PS_CHECKLIST_ITEM A WHERE A.CHECKLIST_CD = PS_CHECKLIST_ITEM.CHECKLIST_CD AND A.EFFDT <= %DateIn(:2))", PER_CHECKLIST.CHECKLIST_CD, PER_CHECKLIST.CHECKLIST_DT);
   
End-If;

Dynamic Prompt / Edittable Prompt

The following steps help to make Dynamic prompt (Edit Table Prompt).

Lets consider a scenario, based on selection Criteria the Character value prompt need to change.

For Department Character value prompt should be DEPT_TBL and for Job Code the Character value prompt should be JOBCODE_TBL.

Now consider the design.

The Selection Criteria, Character Value, Process are fields in SQL Record (May change in different case) and Edit Table(EDITTABLE field) is from DERIVED (delivered) derived and work record.

in the above  example your need a record(e.g GR_CRI_TBL) has following fields

SELECTION_TYPE

CHAR_VALUE

PROCESS

CHAR_VALUE should have %EDITTABLE Prompt Table Edit Type

Write the following function in any FIELDFORMULA and call the function as given below.

Local string &Type;
Function hide_unhide_popfields(&Row As Row);
   &Type = &Row.GetRecord(Record.GR_CRI_TBL).SELECTION_TYPE.Value;
   Evaluate &Type
   When "D" /* Department */
      &Row.GetRecord(Record.DERIVED).EDITTABLE.Value = Record.DEPT_TBL;
      Break;
   When "J" /* Job Code */
      &Row.GetRecord(Record.DERIVED).EDITTABLE.Value = Record.JOBCODE_TBL;
      Break;
   When "L" /* Location */
      &Row.GetRecord(Record.DERIVED).EDITTABLE.Value = Record.LOCATION_TBL;
      Break;
   End-Evaluate;
End-Function;

Call the above function based on FieldChange and RowInit.

Declare Function hide_unhide_popfields PeopleCode GR_CRI_TBL.SELECTION_TYPE FieldFormula;
SetDefault(GR_CRI_TBL.CHAR_VALUE);
hide_unhide_popfields(GetRow());

Creating MS EXCEL Using CreateObject in PeopleSoft

Here are the few examples how to create EXCEL file and how to read EXCEL file using PeopleCode. This can be used in AppEngine And Online PeopleCode.

This can be used in Excel Reporting and Formatting Excel file (Like changing cell colour etc.… ).

/* Set up the Excel COM objects and open the template file */
Local object &oWorkApp, &oWorkBook;
&oWorkApp = CreateObject("COM", "Excel.Application");
&oWorkApp.DisplayAlerts = "False";
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");
&oWorkBook.Open("C:\some_path_to\template.xls");
&oWorkSheet = &oWorkApp.Worksheets("Sheet1");
&oWorkApp.ActiveWorkBook.SaveAs("C:\your_output_file.xls");

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

&oWorkApp.ActiveWorkBook.Save();
&oWorkApp.ActiveWorkBook.Close();
&oWorkApp.DisplayAlerts = "True";
&oWorkApp.Quit();

/*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.add();
&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("E:\Personal\dummy\TestXLS.xls");

/*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("E:\Personal\dummy\TEST1.xls"); 
&oWorkSheet = &oWorkApp.Worksheets("Sheet1"); 
&oData = &oWorkSheet.Range("A1").Value; 
&oWorkSheet.Range("A2").Value = &oData;
&oWorkApp.ActiveWorkbook.SaveAs("E:\Personal\dummy\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("E:\Personal\dummy\TEST1.xls"); 
&oWorkSheet = &oWorkApp.Worksheets("Sheet1"); 
&oData = &oWorkSheet.Range("A1").Value; 
&oWorkSheet2 = &oWorkApp.Worksheets(2);
&oWorkSheet2.Range("A1").Value = &oData;
&oWorkApp.ActiveWorkbook.SaveAs("E:\Personal\dummy\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("E:\Personal\dummy\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("E:\Personal\dummy\TestXLS.xls");

/*Transfer the data to Excel from Rowset*/
&oWorkApp = CreateObject("COM", "Excel.Application"); 
ObjectSetProperty(&oWorkApp, "Visible", True); 
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks"); 
&oWorkBook.Open("E:\Personal\dummy\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("E:\Personal\dummy\TestXLS.xls");

/*Save an xls file as a CSV*/
&oWorkApp = CreateObject("COM", "Excel.Application"); 
ObjectSetProperty(&oWorkApp, "Visible", True); 
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks"); 
&oWorkBook.Open("E:\Personal\dummy\TEST1.xls"); 
&oWorkApp.ActiveWorkbook.SaveAs("E:\Personal\dummy\TestXLS.csv", 6);