PeopleSoft: May 2010

Wednesday, May 19, 2010

Search Process of a component in Update mode (UpdateDisplay/UpdateDisplayall) and Component Build Processing or Page build process in Update/Update Displayallmode

When we click on the component portal in menu then the following below PeopleCode events will fire
Search Process
Record Field SearchInit

ComponentRecord SearchInit

-->Click on Search

Record Field SearchSave

ComponentRecord SearchSave

Component Build Process or Page Build processing in Updatedisplay/UpdateDisplayall mode

-->Select the Employee ID from the list then

Record Field RowSelect

ComponentRecord RowSelect

Component Level Prebuild

Record Field FieldFormula

Record Field RowInit

ComponentRecord RowInit

Component level Postbuild

Page Activate fires and displays the page

When we edit the field (Employee ID) then the following below PeopleCode Events will fire

-->Edit the field (Employee ID) and hit on save then the following peoplecode events will fire

Record Field FieldEdit

ComponentRecordField FieldEdit

Record Field FieldChange

ComponentRecordField FieldChange

Record Field FieldFormula
Save Processing PeopleCode Events:

Record Field SaveEdit

ComponentRecord SaveEdit

Record Field SavePrechange

ComponentRecordField SavePrechange

ComponentLevel SavePrechange

Record Field Workflow

ComponentLevel Workflow

Record Field SavePostchange

ComponentRecordlevel SavePostchange

Component Level SavePostchange

Page Start to Display in ADD mode (or) component in ADD mode

When we click on the component portal in menu then (the page with ADD button display) Then the following below PeopleCode Events will fire.
Search Processibg in Add Mode:

Record Field FieldDefault

ComponentRecordField FieldDefault

Record Field FieldFormula

Record Field RowInit

Record Field SearchInit

ComponentRecordlevel SearchInit

-->Enter the Employee and click on Add

Record Field FeildEdit

ComponentRecordField FeildEdit

Record Field FieldChange

ComponentRecordField FieldChange

Record Field Field Formula

Record Field SaveEdit

Record Field SearchSave

ComponentRecordlevel SearchSave
Page Dispaly (or)Component Build Process in ADD Mode:

Component Level Prebuild

Record Field FieldFormula

Record Field RowInit

ComponentRecord RowInit

Component Level PostBuild

Page Activate fires and displays the page

Save Processing:

Record Field SaveEdit

ComponentRecord SaveEdit

Record Field SavePrechange

ComponentRecord SavePrechange

ComponentLevel SavePrechange

Record Field Workflow

ComponentLevel Workflow

Record Field SavePostchange

ComponentRecordlevel SavePostchange

Component Level SavePostchange

Wednesday, May 5, 2010

Inserting the data into 3 levels (0,1,2,3 levels) into database from flat file using Application Engine

Inserting the data into 3 levels (0,1,2,3 levels) into database from flat file using Application Engine Below is the sample code (Ive used 2 fields in all the 3 levels)

Level0 -> Organisational ID, Organisational Name
Level1 -> College ID, College Name
Level2 -> Department ID, Department Name
Level3 -> Employee ID, Employee Name, Age

Local Record &R0, &R1, &R2, &R3;

&R0 = CreateRecord(Record.A_ORG_TBL);
&R1 = CreateRecord(Record.A_COL_TBL);
&R2 = CreateRecord(Record.A_DEPT_TBL);
&R3 = CreateRecord(Record.A_EMPL_TBL);

&myfile = GetFile("C:\temp\harsha.txt", "R", %FilePath_Absolute);
&myfile1 = GetFile("C:\temp\har.txt", "W", "A", %FilePath_Absolute);
&myfile1.writeline("empty array creation");
&myarray = CreateArrayRept("", 0);

If &myfile.Isopen Then
   While &myfile.Readline(&str);
      &myfile1.WRITELINE("FILE OPENED");
      &myfile1.WRITELINE("string value is " | &str);
      &myarray = Split(&str, ",");

      SQLExec("SELECT A_ORGID FROM PS_A_ORG_TBL WHERE A_ORGID = :1", &myarray [1], &ORGID);
      &myfile1.WRITELINE("array value of orgid is " | &ORGID);
      &myfile1.WRITELINE(" A_ORGID VALUE IS " | &ORGID);


      SQLExec("SELECT A_ORGID,A_COLID FROM PS_A_COL_TBL WHERE A_ORGID = :1", &myarray [1], &ORGID1, &COLID1);
      SQLExec("SELECT A_ORGID,A_COLID,A_DEPTID FROM PS_A_DEPT_TBL WHERE A_ORGID = :1", &myarray [1], &ORGID2, &COLID2, &DEPTID);
      SQLExec("SELECT A_ORGID,A_COLID,A_DEPTID FROM PS_A_DEPT_TBL WHERE A_ORGID = :1", &myarray [1], &ORGID2, &COLID2, &DEPTID);
      SQLExec("SELECT A_ORGID,A_COLID,A_DEPTID,A_EMPLID FROM PS_A_EMPL_TBL WHERE A_ORGID = :1", &myarray [1], &ORGID3, &COLID3, &DEPTID3, &EID3);

      If &myarray [1] = &ORGID Then
         &R1.A_ORGID.Value = &myarray [1];
         &R1.A_COLID.Value = &myarray [2];
         &R1.A_COLNAME.Value = &myarray [3];
         &R1.Insert();
         &myfile1.WRITELINE("values inserted at level 1 ");
         rem End-If;
         rem SQLExec("SELECT A_ORGID,A_COLID,A_DEPTID FROM PS_A_DEPT_TBL WHERE A_ORGID = :1", &myarray [1], &ORGID2, &COLID2, &DEPTID);
      Else
         &R0.A_ORGID.Value = &myarray [1];
         &R0.A_ORGNAME.Value = &myarray [2];
         &R0.Insert();
         &myfile1.WRITELINE("values inserted at level 0 ");
      End-If;

      If &myarray [1] = &ORGID1 And
            &myarray [2] = &COLID1 Then

         &R2.A_ORGID.Value = &myarray [1];
         &R2.A_COLID.Value = &myarray [2];
         &R2.A_DEPTID.Value = &myarray [3];
         &R2.A_DEPTNAME.Value = &myarray [4];
         &R2.Insert();
         &myfile1.WRITELINE("values inserted at level 2 ");


      End-If;
      If &myarray [1] = &ORGID2 And
            &myarray [2] = &COLID2 And
            &myarray [3] = &DEPTID Then
         &R3.A_ORGID.Value = &myarray [1];
         &R3.A_COLID.Value = &myarray [2];
         &R3.A_DEPTID.Value = &myarray [3];
         &R3.A_EMPLID.Value = &myarray [4];
         &R3.A_EMPNAME.Value = &myarray [5];
         &R3.A_EMPLAGE.Value = &myarray [6];
         &R3.Insert();
         &myfile1.WRITELINE("values inserted at level 3 ");

      End-If;


   End-While;

   &myfile.Close();
   &myfile1.Close();
End-If;

Steps for creating a GOTO ROW

STEPS FOR CREATING GOTO ROW
===========================
1.Create a new Field (TIN1_GOTOROW)
2.Create a new record (TIN1_GOWTO_WRK) and select the radio button Derived/Work.
Write the following below people code in the Field change in the Field level in TIN1_GOWTO_WRK table.
==============================================================================
&tinku = DoModal(Page.TIN1_GTROW_PNL, "Secondary Page ", - 1, - 1);

If (&tinku = 1) Then
&rowentered = GetRecord(Record.TIN1_GOWTO_WRK).GetField(Field.TIN1_GOTOROW).Value;
rem WinMessage("hai", 64);

If All(&rowentered) Then
&currentrow = GetRowset();

If (1 <= &rowentered And
&rowentered <= &currentrow.activerowcount) Then
REM IN THE BAOVE THE HIGHEST VALUE WILL BE PERSENT;
&reqrow = GetRowset()(&rowentered);
&field = &reqrow.getrecord(1).getfield(1);
&field.SetCursorPos(%Page);
Else
WinMessage("THE ROW IS WITH IN THE TABLE ", 64);

End-If;

End-If;
End-If;

=============================================================================
3.Create a new secondary page (TIN1_GTROW_PNL)
4.Drag and drop the WRK record in to the page.
5.Save it.

Application Data Security

Defnition security is a form of data security.We use it to control access to particular rows of data (object definitions) in PeopleTools tables.

PeopleSoft also provides other methods to control the application data that a user is allowed to access in the PeopleSoft system. This task is also known as setting data permissions.With application data security, you can set data permissions at the following levels:

1.Table Level(Querry Security)
2.Row Level (SQL View)
3.Feild Level (People Code)

Table Level Security:
Table level security is implemented by using Querry security.
We can restrict the permissions to the users in accessing the tables while running and building the componenets

We can do this by creating querry acess group in PeopleSoft Tree manager and then assinning the users to those groups with PeopleSoft querry security.

PeopleSoft Query security is enforced only when usingPeopleSoft Query because it doesn’t control runtime page access to table data.

Row Level Security:
It can be implementd using SQL view's.
It controls the access to individual rows of data stored within application database tables
It enables the data of a particular user is permitted to access.

Field security:
Field level can be implemented using People Code.
Field security use people code to restrict access to particular fields or columns within application tables.

domodalcomponent steps

Drag and drop the 2 fields STUID,NAME from the work record in all the pages

1.Create the required fields and place them in to the record PS_SRI_STUMOD_TBL(*place the Student id and name in to the record*)
2.Create a Derived/Work record with 2 fields (stuid,name from grid) and save it with _WRK.
3.Place a hyperlink/ push button in the grid.In the hyperlink /pushbutton properties select the Destination as PeoplecodeCommand,record name:SRI_STU1_TBL and field name:SRI_STUABC_INFO and select the check button set component changed.

4.IN component level record field level assign the values to the Derived/Work record.
Local Rowset &RS0, &RS1, &RS2, &RS3;
&RS0 = GetLevel0();
&RS1 = &RS0(1).GetRowset(Scroll.SRI_ORG1_TBL);
For &I = 1 To &RS1.ActiveRowCount
&RS2 = &RS1(&I).GetRowset(Scroll.SRI_DEPT1_TBL);
For &J = 1 To &RS2.ActiveRowCount
&RS3 = &RS2(&J).GetRowset(Scroll.SRI_STU1_TBL);
For &K = 1 To &RS3.ActiveRowCount;
&SID = GetRecord(Record.SRI_STU1_TBL).GetField(Field.SRI_STU1_ID).Value;
&SNAME = GetRecord(Record.SRI_STU1_TBL).GetField(Field.SRI_STU1_NAME).Value;
SRI_STUAB_WRK.SRI_STU1_ID.Value = &SID;
SRI_STUAB_WRK.SRI_STU1_NAME.Value = &SNAME;
End-For;
End-For;
End-For;
DoModalComponent(MenuName.SRI_UNV_MNU, BarName.SRI_UNV_MNU, ItemName.SRI_STUABS_CMP, Page.SRI_STUABS_PNL, "A", Record.SRI_STUAB_WRK);
5.Call the other page /component using the DoModalComponent function.
DoModalComponent(MenuName.SRI_UNV_MNU, BarName.SRI_UNV_MNU, ItemName.SRI_STUABS_CMP, Page.SRI_STUABS_PNL, "A", Record.SRI_STUAB_WRK);
6.Create a View for the STUDENTID and NAME (SRI_STUAB_VW)
7.For STUDENTID in the table PS_SRI_STUMOD_TBL give the view (this makes the scroll change when we select different student id's)

Set ID and Business Unit

SetID
An identification code that represents a set of control table information or table sets. Set Ids enable the sharing of a set of control table information across two or more Business Units.

Table sets enable you to share control table information and processing options among business units. The goal is to minimize redundant data and system maintenance tasks. When you assign a setID to a record group in a business unit, you indicate that all of the tables in the record group are shared between that business unit and any other business unit that also assigns that setID to
that record group. For example, you can define a group of common job codes that are shared between several business units. Each business unit that shares the job codes is assigned the same setID for that record group."

Business Unit
An identification code that represents a high-level organization of business information. You can use a business unit to define regional or departmental units within a larger organization.

Business Unit organizes your company or your organization, SetIDs help you organize your data within the system. The HRMS system uses tables (Control Tables or Prompt Tables) that use a high-level key that enables you to identify and retrieve data from the system. A secondary high-level key, referred to as a SetID, has also been added on various tables. SetIDs are simply the labels used to identify a TableSet. Business Unit and SetID functionality in PeopleSoft HRMS also provides you with a higher business level for reporting purposes and other business data roll-up.

Difference between Transcation Tables and Control Tables

Transaction tables store data about day-to-day activities. Because of this, these tables are updated frequently and can be quite large. The type of transaction varies, such as invoices, paychecks, employee names and addresses, job history, benefits data.

Information in transaction tables is organized and stored by Business Unit.

Control tables store information that define the accounting structure and processing rules that are used when transactions are entered into your PeopleSoft applications. Control tables include master lists such as customers, vendors, products, country and location tables. It is important to note that Control tables are static, meaning they only change when you perform specific maintenance on them.

Control table information is organized and stored by a set identifier, commonly called a SetID.

Stand alone RowSet

STAND ALONE ROWSET
In PeopleCode a standalone rowset is an independent rowset object not associated with the component buffer. They allow you to work with data outside of the buffer by getting whatever additional data you need form the database. In this sense they replace the functionality of derived records which were once used as place holders to store data not directly associated with the component. Because a standalone rowset is standalone, there is no automatic action by the component processor on it. This means that if a standalone rowset is used to manipulate data (inserts/updates), code will need to be added to manually save the changes.

Code to create a standalone rowset object
Local Rowset &rsExample;
&rsExample = CreateRowset(Record.REC1);

Filling a standalone rowset
The Fill method in the Rowset class is used to populate the rowset. This parameters to the fill method are a Where clause and bind values. &rExample.Fill("where FIELD1 = :1", REC2.FIELD2);

The following example writes a file using a file layout that contains parent-child records:
Local File &MYFILE;
Local Rowset &rsBusExp, &rsBusExpPer, &rsBusExpDtl;
Local Record &rBusExp, &rBusExpPer, &rBusExpDtl;
Local SQL &SQL1, &SQL2, &SQL3;
&rBusExp = CreateRecord(Record.PERSONAL_DATA);
&rBusExpPer = CreateRecord(Record.BUS_EXPENSE_PER);
&rBusExpDtl = CreateRecord(Record.BUS_EXPENSE_DTL);

&rsBusExp = CreateRowset(Record.PERSONAL_DATA,
CreateRowset(Record.BUS_EXPENSE_PER,
CreateRowset(Record.BUS_EXPENSE_DTL)));
&rsBusExpPer = &rsBusExp.GetRow(1).GetRowset(1);

&MYFILE = GetFile("c:\temp\BUS_EXP.out", "W", %FilePath_Absolute);
&MYFILE.SetFileLayout(FileLayout.BUS_EXP_OUT);
&EMPLID = "8001";

&SQL1 = CreateSQL("%selectall(:1) where EMPLID = :2", &rBusExp, &EMPLID);
&SQL2 = CreateSQL("%selectall(:1) where EMPLID = :2", &rBusExpPer, &EMPLID);

While &SQL1.Fetch(&rBusExp)
&rBusExp.CopyFieldsTo(&rsBusExp.GetRow(1).PERSONAL_DATA);
&I = 1;
While &SQL2.Fetch(&rBusExpPer)
&rBusExpPer.CopyFieldsTo(&rsBusExpPer(&I).BUS_EXPENSE_PER);
&J = 1;
&SQL3 = CreateSQL("%selectall(:1) where EMPLID = :2
and EXPENSE_PERIOD_DT = :3", &rBusExpDtl, &EMPLID,
&rsBusExpPer(&I).BUS_EXPENSE_PER.EXPENSE_PERIOD_DT.Value);
&rsBusExpDtl = &rsBusExpPer.GetRow(&I).GetRowset(1);
While &SQL3.Fetch(&rBusExpDtl)
&rBusExpDtl.CopyFieldsTo(&rsBusExpDtl(&J).BUS_EXPENSE_DTL);
&rsBusExpDtl.InsertRow(&J);
&J = &J + 1;
End-While;

&rsBusExpPer.InsertRow(&I);
&I = &I + 1;
End-While;
&MYFILE.WriteRowset(&rsBusExp);
End-While;
&MYFILE.Close();

Out Put for the above program
CC8001 03/01/199802/15/1998011200 USDConference 00001
CC8001 03/01/199802/16/19980220000 JPYConference 00001

The following code shows an example of reading in a file and inserting the rows into the database:
Local File &MYFILE;
Local Rowset &rsBusExp, &rsBusExpPer, &rsBusExpDtl;
Local Record &rBusExp, &rBusExpPer, &rBusExpDtl;
Local SQL &SQL1;

&rBusExp = CreateRecord(Record.PERSONAL_DATA);
&rBusExpPer = CreateRecord(Record.BUS_EXPENSE_PER);
&rBusExpDtl = CreateRecord(Record.BUS_EXPENSE_DTL);

&rsBusExp = CreateRowset(Record.PERSONAL_DATA,
CreateRowset(Record.BUS_EXPENSE_PER,
CreateRowset(Record.BUS_EXPENSE_DTL)));

&MYFILE = GetFile("c:\temp\BUS_EXP.out", "R", %FilePath_Absolute);
&MYFILE.SetFileLayout(FileLayout.BUS_EXP_OUT);

&SQL1 = CreateSQL("%Insert(:1)");

&rsBusExp = &MYFILE.ReadRowset();
While &rsBusExp <> Null;
&rsBusExp.GetRow(1).PERSONAL_DATA.CopyFieldsTo(&rBusExp);
&rsBusExpPer = &rsBusExp.GetRow(1).GetRowset(1);
For &I = 1 To &rsBusExpPer.ActiveRowCount
&rsBusExpPer(&I).BUS_EXPENSE_PER.CopyFieldsTo(&rBusExpPer);
&rBusExpPer.ExecuteEdits(%Edit_Required);
If &rBusExpPer.IsEditError Then
For &K = 1 To &rBusExpPer.FieldCount
&MYFIELD = &rBusExpPer.GetField(&K);
If &MYFIELD.EditError Then
&MSGNUM = &MYFIELD.MessageNumber;
&MSGSET = &MYFIELD.MessageSetNumber;
End-If;
End-For;
Else
&SQL1.Execute(&rBusExpPer);
&rsBusExpDtl = &rsBusExpPer.GetRow(&I).GetRowset(1);
For &J = 1 To &rsBusExpDtl.ActiveRowCount
&rsBusExpDtl(&J).BUS_EXPENSE_DTL.CopyFieldsTo(&rBusExpDtl);
&rBusExpDtl.ExecuteEdits(%Edit_Required);
If &rBusExpDtl.IsEditError Then
For &K = 1 To &rBusExpDtl.FieldCount
&MYFIELD = &rBusExpDtl.GetField(&K);
If &MYFIELD.EditError Then
&MSGNUM = &MYFIELD.MessageNumber;
&MSGSET = &MYFIELD.MessageSetNumber;
End-If;
End-For;
Else
&SQL1.Execute(&rBusExpDtl);
End-If;
End-For;
End-If;
End-For;
&rsBusExp = &MYFILE.ReadRowset();
End-While;
&MYFILE.Close();

PeopleSoft Finance Tables with Detail Description XLS sheet

Hi Please find the PeopleSoft Finance Tables with Detail Description XLS sheet in the below link

http://www.ziddu.com/download/7856877/PT_501.xls.html

To find all the records in a given component. Below is the query which takes component name as input and the list of all records in that component as

To find all the records in a given component. Below is the query which takes component name as input and the list of all records in that component as output.

SELECT recname
FROM pspnlgroup a, pspnlfield b
WHERE (a.pnlname = b.pnlname OR a.pnlname = b.subpnlname)
AND a.pnlgrpname = :component_name
GROUP BY recname

To find out which tables contain a certain SETID value.

This post is to share another alternative on how to find out which tables contain a certain SETID value.
Navigate to PeopleTools > Archive Data > Find Data
On the "Field Name" field enter "SETID", on the "Value to Match" field enter the value you are looking for.
The system will give a list of each and every table that has the requested value on the requested field.
this can be used for any other table/value, not only SETID.

Another way to find in Oracle database is to run the below 2 SQL statements

SELECT TABLE_NAME
FROM DBA_TAB_COLUMNS
WHERE COLUMN_NAME='SETID'

SELECT TABLE_NAME
FROM DBA_TAB_COLUMNS
WHERE COLUMN_NAME='BUSINESS_UNIT'

For getting a particular row number from a table

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY name) as row FROM sys.databases ) a WHERE row > 5 and row <= 10

The query above returns rows 6 through 10 from sys.databases as ordered by the "name" column. ROW_NUMBER() is the key function we're using here. It's one of a set of ranking functions introduced in 2005. Note that it's always accompanied by an OVER clause that specifies the ordering that the row_number should be based on.

U can find the above query in the blow link
http://blogs.msdn.com/sqlserver/archive/2006/10/25/limit-in-sql-server.aspx

To know the Run Status when running through the Process Scheduler

To know the Run Status while running through the Process Scheduler

select FIELDVALUE, XLATLONGNAME
from PSXLATITEM
where FIELDNAME = 'RUNSTATUS'

The summary of the run status translates (from PeopleTools 8.49). Note that not all of these are active.

Value Status
===== =======
1 Cancel
2 Delete
3 Error
4 Hold
5 Queued
6 Initiated
7 Processing
8 Cancelled
9 Success
10 Not Successful
11 Posted
12 Unable to Post
13 resend
14 Posting
15 Content Generated
16 Pending
17 Success with Warning
18 Blocked
19 Restart
The following below query will give us the summary of the process run statuses in your process request table

select
RUNSTATUS,
(
select XLATSHORTNAME
from PSXLATITEM
where FIELDNAME = 'RUNSTATUS'
and FIELDVALUE = RUNSTATUS
) as RUNSTATUS_DESCR,
count(PRCSINSTANCE)
from
PSPRCSRQST
group by
RUNSTATUS
order by
RUNSTATUS;

To get the process output location in SQR

To get the process output location in SQR

select PRCSOUTPUTDIR
from PSPRCSPARMS
where PRCSINSTANCE = '123456789'

How to read the runcontrol parameters in AE

We can read the Runcontrol Parameters in Application Engine by the following below 2 ways:

SQL
%select(EMPLID)selct EMPLID from PS_AERUNCONTROL where PROCESS_INSTANCE=%PROCESS_INSTANCE AND OPRID=%oprid

PeopleCode
&SQL="Select EMPLID from PS_AERUNCONTROL where PROCESS_INSTANCE="|PS_TEST_AET.PROCESS_INSTANCE|"and OPRID="|%oprid

%PROCESS_INSTANCE or %Bind(PROCESS_INSTANCE) can be used.
%PROCESS_INSTANCE is more efficent and faster

Sample People Code for getting the DEPT ID based on the ORG ID

Below is the 3 level rowset peoplecode for getting the Department ID prompt values when we select the Organization ID from prompt table.(i'e when we select the Organization ID from the prompt table then the departments under the particular Organization should only display)
Local Rowset &RS0, &RS1, &RS2;
&RS0 = GetLevel0();
&RS1 = &RS0(1).GetRowset(Scroll.SRI_ORG1_TBL);
For &I = 1 To &RS1.ActiveRowCount
&ID = &RS1(&I).GetRecord(Record.SRI_ORG1_TBL).GetField(Field.SRI_ORG_ID).Value;
&SQL = CreateSQL("SELECT DESCR FROM PS_EXT_ORG_TBL WHERE EXT_ORG_ID=:1", &ID);
While &SQL.FETCH(&DESCR)
&RS1(&I).GetRecord(Record.SRI_ORG1_TBL).GetField(Field.SRI_ORG1_NAME).Value = &DESCR;
REM WinMessage(&DESCR, 0);

&RS2 = &RS1(&I).GetRowset(Scroll.SRI_DEPT1_TBL);

For &J = 1 To &RS2.ActiveRowCount;
&DI = &RS2(&J).GetRecord(Record.SRI_DEPT1_TBL).GetField(Field.SRI_ORG_ID).Value;
&SQL1 = CreateSQL("SELECT ORG_DEPARTMENT FROM PS_ORG_DEPT WHERE EXT_ORG_ID=:1", &DI);
While &SQL1.FETCH(&ORG_DEPARTMENT)
REM SQLEXEC("SELECT COUNT(EMPLID) FROM ");
If &ORG_DEPARTMENT = "" Then
&RS2(&J).GetRecord(Record.SRI_DEPT1_TBL).GetField(Field.SRI_DEPT_ID).Value = "";
WinMessage("There are no Departments under this Organisation ID", 0);
Else
&RS2(&J).GetRecord(Record.SRI_DEPT1_TBL).GetField(Field.SRI_DEPT_ID).Value = "";

rem WinMessage(&ORG_DEPARTMENT, 0);

End-If;
End-While;
End-For;
End-While;
End-For;

People Code for calucating the student marks,average and grade

&A = STUD_TBL.STUD_MI.Value;
&B = STUD_TBL.STUD_M2.Value;
&C = STUD_TBL.STUD_M3.Value;
STUD_TBL.STUD_TOT = &A + &B + &C;
STUD_TBL.STUD_AVG = (&A + &B + &C) / 3;
If (&A = "") Or
(&B = "") Or
(&C = "") Then
STUD_TBL.STUD_GRADE = "";
Else
If (&A < 35) Or
(&B < 35) Or
(&C < 35) Then
STUD_TBL.STUD_GRADE = "FAIL";
Else
If (STUD_TBL.STUD_AVG > 35) And
(STUD_TBL.STUD_AVG < 50) Or
(STUD_TBL.STUD_AVG > 35) And
(STUD_TBL.STUD_AVG < 50) Or
(STUD_TBL.STUD_AVG > 35) And
(STUD_TBL.STUD_AVG < 50) Then
STUD_TBL.STUD_GRADE = "C";
Else
If (STUD_TBL.STUD_AVG > 50) And
(STUD_TBL.STUD_AVG < 70) Or
(STUD_TBL.STUD_AVG > 50) And
(STUD_TBL.STUD_AVG < 70) Or
(STUD_TBL.STUD_AVG > 50) And
(STUD_TBL.STUD_AVG < 70) Then
STUD_TBL.STUD_GRADE = "B";
Else
If (STUD_TBL.STUD_AVG > 70) And
(STUD_TBL.STUD_AVG <= 100) Or
(STUD_TBL.STUD_AVG > 70) And
(STUD_TBL.STUD_AVG <= 100) Or
(STUD_TBL.STUD_AVG > 70) And
(STUD_TBL.STUD_AVG <= 100) Then
STUD_TBL.STUD_GRADE = "A"
End-If
End-If
End-If
End-If
End-If

Sample 2 level rowset code for Language selection

When we select the the language from the drop down list then the field names in two level's should change automatically.Below is the sample rowset (RS0,RS1) code for 2 levels.

Local Rowset &rs0, &rs1;
&field = GetField(SRI_ORGSAMP_TBL.SRI_ORGSAMP_ID);
&field1 = GetField(SRI_ORGSAMP_TBL.SRI_ORGSAMP_NAME);
&LABELID = &field.Name;
&LABELID1 = &field1.Name;

/*CHINEESE LANGUAGE*/
If SRI_ORGSAMP_TBL.SRI_LANGUAGE.Value = "CHIN" Or
SRI_ORGSAMP_TBL.SRI_LANGUAGE.Value = "" Then
&field.Label = "GRO DI";
&field1.Label = "GRO EMAN";
&rs0 = GetLevel0();
&rs1 = &rs0(1).GetRowset(Scroll.SRI_EMPSAMP_TBL);
For &i = 1 To &rs1.ActiveRowCount
/*Variable*/
&empid = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_ID);
&empid.Label = "EEOLPME DI";
&NAME = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_NAME);
&NAME.Label = "EMAN";
&GENDER = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_GENDER);
&GENDER.Label = "REDNEG";
&COUN = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_COUN);
&COUN.Label = "GNIKROW NOITACOL";
&DOB = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_DOB);
&DOB.Label = "BOD";
&DOJ = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_DOJ);
&DOJ.Label = "JOD";
End-For;

End-If;
/*END OFCHINEESE LANGUAGE*/
/*FRANCE LANGUAGE*/
If SRI_ORGSAMP_TBL.SRI_LANGUAGE.Value = "FRAN" Or
SRI_ORGSAMP_TBL.SRI_LANGUAGE.Value = "" Then
&field.Label = "ORGANI ID";
&field1.Label = "NAMEE";
&rs0 = GetLevel0();
&rs1 = &rs0(1).GetRowset(Scroll.SRI_EMPSAMP_TBL);
For &i = 1 To &rs1.ActiveRowCount
/*Variable*/
&empid = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_ID);
&empid.Label = "EMPOLOD ID";
&NAME = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_NAME);
&NAME.Label = "NAMEE";
&GENDER = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_GENDER);
&GENDER.Label = "DREE";
&COUN = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_COUN);
&COUN.Label = "Workingo Locationio";
&DOB = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_DOB);
&DOB.Label = "birtho";
&DOJ = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_DOJ);
&DOJ.Label = "joino";
End-For;

End-If;
/*END OF FRANCE LANGUAGE*/

/*ENGLISH LANGUAGE*/
If SRI_ORGSAMP_TBL.SRI_LANGUAGE.Value = "ENG" Or
SRI_ORGSAMP_TBL.SRI_LANGUAGE.Value = "" Then
&field.Label = "ORGANIZATION ID";
&field1.Label = "ORG NAME";
&rs0 = GetLevel0();
&rs1 = &rs0(1).GetRowset(Scroll.SRI_EMPSAMP_TBL);
For &i = 1 To &rs1.ActiveRowCount
/*Variable*/
&empid = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_ID);
&empid.Label = "EMPLOYEE ID";
&NAME = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_NAME);
&NAMELabel = "NAME";
&GENDER = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_GENDER);
&GENDER.Label = "GENDER";
&COUN = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_COUN);
&COUN.Label = "WORKING LOCATION ";
&DOB = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_DOB);
&DOB.Label = "DATE OF BIRTH";
&DOJ = &rs1(&i).GetRecord(Record.SRI_EMPSAMP_TBL).GetField(Field.SRI_EMPSAMP_DOJ);
&DOJ.Label = "DATE OF JOIN";
End-For;

End-If;
/*END OF ENGLISH LANGUAGE*/

To get the OprID for the Current Session

For getting the OprID for the current session then write the following below line of code in Record Level Save PreChange Event

SRI_STUORG4_TBL.SRI_STUORG_OPRID = %UserId;

select any check box in the Grid then the remaning check boxes in the same column should hide automatically

Level 0 = Org ID,Name
Level 1 = Org details
Level 2 = 3 Grids (Active locations,Primary Contact,Active Departments)

When we select any check box then the remaning check boxes in the same column should hide automatically.And the corresponding Name (Active locations,Primary Contact,Active Departments)which is in grid should be displayed in the Corresponding fields.

Write the following below code in the Component Record Level Field change event.

Local Rowset &RS0, &RS1, &RS2, &rs4;
&RS0 = GetLevel0();
&RS1 = &RS0(1).GetRowset(Scroll.SRI_STUORG1_TBL);
&CNT = 0;
For &I = 1 To &RS1.ActiveRowCount
&RS2 = &RS1(&I).GetRowset(Scroll.SRI_STUORG3_TBL);
For &J = 1 To &RS2.ActiveRowCount
&chk = &RS2.GetRow(&J).GetRecord(Record.SRI_STUORG3_TBL).GetField(Field.SRI_ORG_PRIMARY).Value;
&cr = CurrentRowNumber(2);

If &RS2.GetRow(&J).GetRecord(Record.SRI_STUORG3_TBL).GetField(Field.SRI_ORG_PRIMARY).Value = "Y" Then

&id = &RS2.GetRow(&J).GetRecord(Record.SRI_STUORG3_TBL).GetField(Field.SRI_ORG_CONTACTNO).Value;
&RS2.GetRow(&J).GetRecord(Record.SRI_STUORG3_TBL).GetField(Field.SRI_ORG_PRIMARYCOU).Value = &id;

&CNT = &CNT + 1;

For &p = 1 To &RS2.ActiveRowCount
REM WinMessage("in for ", 0);
&RS2.GetRow(&p).GetRecord(Record.SRI_STUORG3_TBL).GetField(Field.SRI_ORG_PRIMARY).Visible = False;
End-For;
&RS2.GetRow(&cr).GetRecord(Record.SRI_STUORG3_TBL).GetField(Field.SRI_ORG_PRIMARY).Visible = True;
REM WinMessage("count " | &CNT, 0);
End-If;

If &RS2.GetRow(&J).GetRecord(Record.SRI_STUORG3_TBL).GetField(Field.SRI_ORG_PRIMARY).Value = "N" And
&CNT = 0 Then

&RS2.GetRow(&J).GetRecord(Record.SRI_STUORG3_TBL).GetField(Field.SRI_ORG_PRIMARYCOU).Value = &id;
REM WinMessage("in if1", 0);
&RS2.GetRow(&J).GetRecord(Record.SRI_STUORG3_TBL).GetField(Field.SRI_ORG_PRIMARY).Visible = True;

End-If;

If &RS2.GetRow(&J).GetRecord(Record.SRI_STUORG3_TBL).GetField(Field.SRI_ORG_PRIMARY).Value = "N" And
&CNT > 0 Then

&RS2.GetRow(&J).GetRecord(Record.SRI_STUORG3_TBL).GetField(Field.SRI_ORG_PRIMARYCOU).Value = &id;
If &J <> &cr And
&RS2.GetRow(&cr).GetRecord(Record.SRI_STUORG3_TBL).GetField(Field.SRI_ORG_PRIMARY).Value = "Y" Then
REM WinMessage("in if2", 0);
&RS2.GetRow(&J).GetRecord(Record.SRI_STUORG3_TBL).GetField(Field.SRI_ORG_PRIMARY).Visible = False;
End-If;

If &RS2.GetRow(&cr).GetRecord(Record.SRI_STUORG3_TBL).GetField(Field.SRI_ORG_PRIMARY).Value = "N" Then
&RS2.GetRow(&J).GetRecord(Record.SRI_STUORG3_TBL).GetField(Field.SRI_ORG_PRIMARYCOU).Value = &id;
For &u = 1 To &RS2.ActiveRowCount
REM WinMessage("in if2", 0);
&RS2.GetRow(&u).GetRecord(Record.SRI_STUORG3_TBL).GetField(Field.SRI_ORG_PRIMARY).Visible = True;
End-For;
End-If;
End-If;

End-For;
End-For;

Fill color in grid Cell

FOR &Z = 1 TO &RS.ACTIVEROWCOUNT
&RS(&Z).RSRECNAME.RECFIELDNAME.Style = "STYLEVALUE";
END-FOR

Date display format on Page

In Order to change the date format through Personalization, please follow the steps given below:

1. Navigate, Root > People Tools > Personalization > Personalization Options > Search

Here, you will get the option for the different application for which you want to change the Date Format or you can also choose PPTL (People Tools) which change the Date Format in the Tools itself. Select, PPTL.

2. Now, you can see there is a Definition---> DFRMT, for the Date Format.

3. On the Top of this page and beside the Definition Tab, you will find the Format Tab, just click on this Tab.

4. Now, click on the "Set Option Default Value".

5. Change the Option Default Value as per your requirement MMDDYY and click OK.

6. Save the changes.

========================================
========================================

If you want to change the Date Format for a Specific User on specific page then you can also do this through People Coding.

You can also play with the below code with the help of %oprid, like if %oprid then the below code or w.r.t roles through "IsUserInRole" function.

There is one built-in function "DateTimeToLocalizedString". You can use this if you want to change the date format through people code for your PIA pages.

How to use:

Example 1:
&String = DateTimeToLocalizedString(&Date, "M/d/y");
/* if you are picking the date from any record field and using &Date for that field */

Example 2:
&String = DateTimeToLocalizedString(%Date, "M/d/y");
/* if you want to pick the system date and want to use it on your page with different formats. */

-- Assign this String variable (&String) to your desired field.

Note: Here 'd' for date and 'y' for year both must be in smaller letters only. And, 'M' for months should be in Caps.

For getting the current DAteTime when ever we change or edit any field (Using Rowsets)

Write the following below code in the Record Level in Save Prechange

Local Rowset &RS0, &RS1, &RS2;

&RS0 = GetLevel0();
&RS1 = &RS0(1).GetRowset(Scroll.SRI_STUORG1_TBL);
For &I = 1 To &RS1.ActiveRowCount
&RS2 = &RS1(&I).GetRowset(Scroll.SRI_STUORG4_TBL);
For &J = 1 To &RS2.ActiveRowCount

If &RS2.GetRow(&J).IsChanged Then

&RS2.GetRow(&I).GetRecord(Record.SRI_STUORG4_TBL).GetField(Field.SRI_STUORG_DTTIME).Value = %Datetime;
End-If;

End-For;
End-For;

Inserting a New Scroll Row from within the Same Scroll

In PeopleCode, when inserting or deleting a row on a scroll, it is required that you perform the action on a parent row of the rowset being inserted/deleted to. PeopleTools doesn’t allow PeopleCode (using the built-in functions/methods InsertRow and DeleteRow) to insert or delete a row on the same scroll within which it is currently running. If you attempt to do this, PeopleTools will give an error complaining about changing the current program context.

However, there are some cases where inserting within the same scroll might be desirable. For example, based on the data entered on a row of a scroll, a new matching row must be inserted within the same scroll.

Background

The Rowset class has a SelectNew method, which is quite similar to the Select method. The main difference is that rows populated through SelectNew are marked as new in the component buffer. Both Select and SelectNew allows the record source of the data being loaded to the rowset to be different from the primary record of the rowset. There is a requirement, however, that the source record must contain at least one of the key fields in the primary record. Though, none of the fields in the source record have to be keys as well.
The Technique

Step 1. Create a new Dynamic View record containing at least one of the keys of the primary record of the scroll. For this tutorial, assume that the name of the new record is SCROLL_INS_ROW. This is important: none of the fields in this record must be set to a key.

Step 2. Set the SQL of the view to the following:

SELECT NULL
FROM PS_INSTALLATION

Of course, if you’ve included more fields, then SELECT the appropriate number of NULL’s on the SQL.

Step 3. The PeopleCode for inserting a row within the same scroll would be the following:

Local Rowset &this_rowset = GetRowset();
&this_rowset.SelectNew(Record.SCROLL_INS_ROW);

Month difference PeopleCode

Function diff_months(&DATE1 As date, &DATE2 As date) Returns number;
Local integer &YEAR1 = Year(&DATE1);
Local integer &MONTH1 = Month(&DATE1);
Local integer &DAY1 = Day(&DATE1);
Local integer &YEAR2 = Year(&DATE2);
Local integer &MONTH2 = Month(&DATE2);
Local integer &DAY2 = Day(&DATE2);
If &DATE1 <= &DATE2 Then
If &DAY1 > &DAY2 Then
If &MONTH1 = 12 Then
&MONTH1 = 1;
&YEAR1 = &YEAR1 + 1;
Else
&MONTH1 = &MONTH1 + 1;
End-If;
End-If;
Else
If &DAY1 < &DAY2 Then
If &MONTH2 = 12 Then
&MONTH2 = 1;
&YEAR2 = &YEAR2 + 1;
Else
&MONTH2 = &MONTH2 + 1;
End-If;
End-If;
End-If;
Local integer &DIFF_MONTHS = 12 * (&YEAR2 - &YEAR1) + &MONTH2 - &MONTH1;
Return &DIFF_MONTHS;

End-Function;

File operations using DOS commands in SQR

We can use the following as an sqc, for performing file operations in SQR.

!*********************************************************************
!Program Name : FileOprs.sqc
!Description : File operations using DOS commands.
!*********************************************************************

!*********************************************************************
!Procedure Name : Make_Dir
!Description : Creates a new path based on the input. Path must be
! a complete directory tree listing.
!Usage : Do Make_Dir($Directory_NewPath)
!*********************************************************************
begin-procedure Make_Dir($NewPath)
#debugy show ‘In Procedure : Make_Dir’

Let $Commmand_line = ‘cmd /c mkdir ‘ || $NewPath
Do Exec_Cmd($Commmand_line)

end-procedure ! Make_Dir

!*********************************************************************
!Procedure Name : Exec_Cmd
!Description : Executes $Commmand_line on the system and reports
! errors for debugging.
!Usage : Do Exec_Cmd($Commmand_line)
!*********************************************************************
begin-procedure Exec_Cmd($Commmand_line)
#debugy show ‘In Procedure : Exec_Cmd’

call system using $Commmand_line #status nowait
If #status <> 0
#debugy show ‘Command ‘ $Commmand_line ‘ failed to execute.’
End-If

end-procedure ! Exec_Cmd

!*********************************************************************
!Procedure Name : Copy_Files
!Description : Copy files from old to new path. Old information should be of
! form \pathname\filename, while new information can just be a path.
!Usage : Do Copy_Files($old_file_name, $new_file_name)
!*********************************************************************
begin-procedure Copy_Files($old, $new)
#debugy show ‘In Procedure : Copy_Files’

Let $Commmand_line = ‘cmd /c copy ‘ || $old || ‘ ‘ || $new
Do Exec_Cmd($Commmand_line)

end-procedure ! Copy_Files

!*********************************************************************
!Procedure Name : Move_Files
!Description : Move files from old to new path. Old information should be of
! form \pathname\filename, while new information can just be a path.
!Usage : Do Move_Files($old_file_name, $new_file_name)
!*********************************************************************
begin-procedure Move_Files($old, $new)
#debugy show ‘In Procedure : Move_Files’

Let $Commmand_line = ‘cmd /c move ‘ || $old || ‘ ‘ || $new
Do Exec_Cmd($Commmand_line)

end-procedure ! Move_Files

!*********************************************************************
!Procedure Name : Delete_Files
!Description : Deletes the files passed in variable $files
!Usage : Do Delete_Files($files)
!*********************************************************************
begin-procedure Delete_Files($files)
#debugy show ‘In Procedure : Delete_Files’

Let $Commmand_line = ‘cmd /c del ‘ || $files
Do Exec_Cmd($Commmand_line)

end-procedure ! Delete_Files

Running an SQR from within your PeopleCode program

The best and safest way to launch an SQR program is to use the PeopleCode functions CreateProcessRequest() and Schedule().

The CreateProcessRequest function allows you to create a ProcessRequest object.
The CreateProccessRequest function takes 2 arguments. The Process Type and the Process Name.

REM Declare your Variables;
Local ProcessRequest &MYRQST;
Local String &MySQR;
&MySQR = "DDDAUDIT"
REM Create My Process Request Object;
&MYRQST = CreateProcessRequest("SQR Process", &MySQR);

REM Set Properties of My Process Request Object;
&MYRQST.RunControlID = "MYRUNCNTRL_ID"

REM Set Properties of My Process Request Object;
&MYRQST.SetOutputOption("Web", "PDF", "", &MySQR);

The above example creates a ProcessRequest object for the DDDAUDIT SQR named &MYRQST. Also specified Run Control ID and the output options. I can now take this Object and use the Schedule() method agains it to Schedule the SQR. Here is an example.

&MYRQST.Schedule();
If &MYRQST.Status = 0 then
/* Schedule succeeded. */
Else
/* Process (job) not scheduled, do error processing */
End-If;

Date related calucations using rowsets and without rowset

Date related calucations using rowsets

Local Rowset &RS0, &RS1;
Local date &From_Date, &To_Dt;
&Duration = 0;
&Tot_Holi = 0;
&Sat = 0;
&Sun = 0;
&RS0 = GetLevel0();
&RS1 = &RS0(1).GetRowset(Scroll.SRI_STUMOD_TBL);
For &I = 1 To &RS1.ActiveRowCount
&From_Date=&RS1(I).GetRecord(Record.SRI_STUMOD_TBL).GetField(Field.SRI_STUAB_STD).Value;
&To_Dt =&RS1(&I).GetRecord(Record.SRI_STUMOD_TBL).GetField(Field.SRI_STUAB_END).Value;
While (&From_Date <= &To_Dt);

If (Weekday(&From_Date) = 7) Then
&Sat = &Sat + 1;
End-If;

If (Weekday(&From_Date) = 1) Then
&Sun = &Sun + 1;
End-If;

If Weekday(&From_Date) <> 7 And
Weekday(&From_Date) <> 1 And
&flag <> "x" Then
&Duration = &Duration + 1;

End-If;
&From_Date = AddToDate(&From_Date, 0, 0, 1);

End-While;

&RS1(&I).GetRecord(Record.SRI_STUMOD_TBL).GetField(Field.SRI_STUAB_DURATION).Value = &Duration;
If &Duration > 20 Then
Error MsgGet(11100, 180, "Only 20 leaves per annum please select the correct dates");
End-If;

&RS1(&I).GetRecord(Record.SRI_STUMOD_TBL).GetField(Field.SRI_STUAB_SAT).Value = &Sat;
WinMessage(&Sat, 0);
&Sat = 0;

&RS1(&I).GetRecord(Record.SRI_STUMOD_TBL).GetField(Field.SRI_STUAB_SUN).Value = &Sun;
&Sun = 0;

End-For;

Date related calucations without RowSet

Local date &From_Date, &To_Dt;

&Duration = 0;
&Tot_Holi = 0;
&Sat = 0;
&Sun = 0;

&From_Dt = SRI_STUMOD_TBL.SRI_STUAB_STD.Value;
&To_Dt = SRI_STUMOD_TBL.SRI_STUAB_END.Value;

While (&From_Dt <= &To_Dt);
&From_Dt = AddToDate(&From_Dt, 0, 0, 1);
If (Weekday(&From_Dt) = 7) Then
&Sat = &Sat + 1;
End-If;
If (Weekday(&From_Dt) = 1) Then
&Sun = &Sun + 1;
End-If;
If Weekday(&From_Dt) <> 7 And
Weekday(&From_Dt) <> 1 And
&flag <> "x" Then
&Duration = &Duration + 1;
End-If;

SQLExec("select 'x' from PS_HOLIDAY_DATE WHERE HOLIDAY=:1", &From_Dt, &flag);
If &flag = "x" Then
&Tot_Holi = &Tot_Holi + 1;
End-If;

End-While;
SRI_STUMOD_TBL.SRI_STUAB_SAT.Value = &Sat;
&Sat = 0;
SRI_STUMOD_TBL.SRI_STUAB_SUN.Value = &Sun;
&Sun =0;
SRI_STUMOD_TBL.SRI_STUAB_DURATION.Value = &Duration;

Time Clock code in Peoplesoft

PEOPLESOFT HOT KEY'S

We all probably know that “CTRL J” hot key gives us the system information. But did you know that there are a lot more available. But you do not have to trouble your brain to remember them, simply press navigate to a search or transaction page and invoke hot key “CTRL K” to get all the information. Below are the results from PeopleSoft “CTRL K“.
Accessing your application using the keyboard
Keyboard navigation is controlled by Hot keys and Access keys .

List of Hot Keys
Alt 1– Executes different buttons depending on the page type

> Save button on the Toolbar in a page
> OK button on a secondary page
> Search or Add button on a Search or Lookup page

Alt 2 — Return to Search
Alt 3 — Next in List
Alt 4 — Previous in List
Alt 5 — Valid Lookup Values
Alt 6 — Related Links
Alt 7 — Insert Row in grid or scroll area
Alt 8 — Delete Row in grid or scroll area
Alt 0 — Refreshes the page by invoking the Refresh button on the Toolbar
Alt . — Next set of rows in grid or scroll area [e.g., Alt period]
Alt , — Previous set of rows in grid or scroll area [e.g., Alt comma]
Alt /– Find in grid or scroll area [e.g., Alt forward slash]
Alt ‘ — View All in grid or scroll area [e.g., Alt prime]
Alt \– Toggle between Add and Update on the Search page [e.g., Alt backslash]
Ctrl J– System Information
Ctrl K> — Keyboard Information
Ctrl Y>– Toggle menu between collapse and expand.
Ctrl Tab> — Toggles focus through the frame set
Enter >– Invokes the following buttons where present: OK, Search, Lookup
Esc >– Cancel

List of Access Keys
Alt 9> — Takes you to the Help line
Alt \ >– Takes you to the Toolbar [e.g., Alt backslash Enter]
Ctrl Z> — Takes you to the Search box of the Menu

Menu Access Keys
The Ctrl Z combination will focus your cursor onto the menuing system. From there, you can use your tab key (or shift-tab to reverse direction) to navigate through the menu hierarchy.

About Access keys and Hot keys
> An Access Key is an Alt key combination that moves focus to a specified field on the current page.
For example, Alt \ moves focus to first button on the Toolbar. Then pressing the Enter key would invoke that action. Or, you may use the Tab key to move you to the next Toolbar button.
> A Hot Key performs an immediate action. For example, when focus is in a field that has lookup processing, Alt 5 invokes the Lookup page without having to press the Enter key.

Difference between Writeline and WriteString in AppEngine PeopleCode

Write String prints the data continuously in one line.

Write line prints the data line by line

The Structure of PS_HOME

The PS_HOME directory on a typical PeopleSoft file server are below:

* appserv - application server/process scheduler files for each domain
* bin - client, server and sqr binaries
* class - common Java classes
* class_ca - more Java classes ?
* crw - Crystal reports
* data - data used used to populate PeopleTools and application demo databases
* dict - language dictionaries ?
* doc - not sure - empty?
* etc - not sure ?
* excel - nVision, Excel to CI files
* fonts - truetype fonts
* jre - Java runtime environment
* log - installation log files
* nvision - nvision reporting
* projects - delivered application designer projects
* PSEMAgent - PeopleSoft environment management agent
* PSEMViewer - PeopleSoft environment management viewer
* psreports - report repository
* scripts - installation sql and datamover scripts
* sdk - software development kit and API information (e.g. Integration Broker and component interfaces)
* SecurityAddIns - Oracle single signon plugin?
* secvault - encryption keys (e.g for pscipher)
* sendmaster - SendMaster utility for testing PeopleSoft Integration Broker
* setup - Various installers (e.g. PIA, change assistant, database configuration wizard)
* sqr - SQRs and SQCs
* sqrfonts - SQR font loader
* src - COBOL and other miscellaneous source files
* template - nothing?
* tuxedo - tuxedo files
* utility - various utlities
* verity - verity search engine
* web - where PeopleSoft Java Object adapter .jar file is located. Not sure what else this is for.
* webserv - possibly where the web server files have been installed or most likely empty
* winword - Microsoft Word templates and macros

Files

* peopletools.properties - Basic PeopleTools install properties

SQR output and Fonts

Below is the extract from the same PS delivered file. You may try with any font which is not listed below and in case if that font is not supported, PS converts it to the default font, which is Courier.

List of defined fonts in PeopleSoft:

Number --------------- Name

---------------------------------

3 ----------------------- Courier
300 -------------------- Courier-Bold
4 ----------------------- Helvetica
400 -------------------- Helvetica-Bold
5 ----------------------- Times-Roman
500 -------------------- Times-Bold
6 ----------------------- AvantGarde-Book
8 ----------------------- Palatino-Roman
800 -------------------- Palatino-Bold
11 ---------------------- Symbol
12 ---------------------- ZapfDingbats
17 ---------------------- ZapfChancery-MediumItalic
18 ---------------------- Bookman-Light
23 ---------------------- NewCenturySchlbk-Roman
2300 ------------------- NewCenturySchlbk-Bold
30 ---------------------- Courier-Oblique
3000 ------------------- Courier-BoldOblique
31 ---------------------- Helvetica-Oblique
3100 ------------------- Helvetica-BoldOblique
32 ---------------------- Times-Italic
3200 ------------------- Times-BoldItalic
33 ---------------------- AvantGarde-Demi
34 ---------------------- AvantGarde-BookOblique
35 ---------------------- AvantGarde-DemiOblique
36 ---------------------- Palatino-Italic
3600 ------------------- Palatino-BoldItalic
37 ---------------------- NewCenturySchlbk-Italic
3700 ------------------- NewCenturySchlbk-BoldItalic
38 ---------------------- Helvetica-Narrow
3800 ------------------- Helvetica-Narrow-Bold
39 ---------------------- Helvetica-Narrow-Oblique
3900 ------------------- Helvetica-Narrow-BoldOblique
40 ---------------------- Bookman-Demi
41 ---------------------- Bookman-LightItalic
42 ---------------------- Bookman-DemiItalic

You can get this list of delivered SQR fonts in postscri.str file which you can access from the machine where scheduler is installed and configured.

Hope this helps... suggestions are always welcome in case I missed out something...

Process Definition

When a process definition has been created, there are three main tables where it makes an entry and store definition of the . Those are:

PS_PRCSDEFN -- The main page where you start creating "Process Definition".
PS_PRCSDEFNPNL -- "Process Definition Options" page where you define components, process group, recurrence etc...
PS_PRCSDEFNGRP -- "Process Definition Options" page where you define components, process group, recurrence etc...

Apart from that, there are some more tables which get affects depends on options you have choosen while creating a process definition. Below are the same with description...

PS_PRCSDEFNCNTDIST -- If you have specified "Output Destination Options" for some process, enteries goes to this table.
PS_PRCSDEFNXFER -- If you have specified "Page Transfer Information" for some process, enteries goes to this table.
PS_PRCSDEFNNOTIFY -- If you have specified " Notifications" for some process, enteries goes to this table.
PS_PRCSDEFNMETA -- If you have specified some value in "OS390 Option", enteries goes to this table.
PS_PRCSDEFNMESSAGE -- If you have specified some value for message from page "Schedule Message Information", enteries goes to this table.

So there are two query sets here to delete any process definition...

First case in which you just have created a wrong process definition:

DELETE FROM PS_PRCSDEFN where PRCSNAME = 'process name'
DELETE FROM PS_PRCSDEFNPNL where PRCSNAME = 'process name'
DELETE FROM PS_PRCSDEFNGRP where PRCSNAME = 'process name'

Second case in which you have created a wrong process definition with the options like Output destination, Page transfer etc...:

DELETE FROM PS_PRCSDEFN where PRCSNAME = 'process name'
DELETE FROM PS_PRCSDEFNPNL where PRCSNAME = 'process name'
DELETE FROM PS_PRCSDEFNGRP where PRCSNAME = 'process name'
DELETE FROM PS_PRCSDEFNCNTDIST where PRCSNAME = 'process name'
DELETE FROM PS_PRCSDEFNXFER where PRCSNAME = 'process name'
DELETE FROM PS_PRCSDEFNNOTIFY where PRCSNAME = 'process name'
DELETE FROM PS_PRCSDEFNMETA where PRCSNAME = 'process name'
DELETE FROM PS_PRCSDEFNMESSAGE where PRCSNAME = 'process name

Multiple Report Generation in SQR

For generating multiple reports in sqr , Use different "Declare-Report" and use the different report name in various procedures Given an example code:- Here we get two reports

begin-setup
!#define MAX_LABEL_LINES 10
!#define LINES_BETWEEN_LABELS 3begin-setup
declare-layout labels
paper-size&H3D(10,11) left-margin&H3D0.33
end-declare
declare-layout form_letter
end-declare
declare-layout listing
end-declare
declare-report labels
layout&H3Dlabels
end-declare
declare-report form_letter
layout&H3Dform_letter
end-declare
declare-report listing
layout&H3Dlisting
end-declare
end-setup

begin-program
do aa
do bb
end-program

begin-procedure aa
begin-select
emplid (,1)
position (+1)
from psoprdefn
end-select
use-report form_letter --> First report
end-procedure

begin-procedure bb
print 'hello' (1,1)
use-report listing --> Second Report
end-procedure

You can use diferent layouts if you want as given above.

To know the Menu,Bar,Item name's to use in Transfer,Transfer Exact,Domodal component

To know the Menu,Bar,Item name's to use in Transfer,Transfer Exact,Domodal component(when we click on the hyper link or push button in grid to transfer to another component) use the following below SQL

select *
from PSAUTHITEM
where MENUNAME = 'your menu name'
and PNLITEMNAME = 'your page name'

Derived/ Work record

When we want to call another component (The fields(Student ID and Name) in level 3 should be displayed in the other page fields)when we click on the hyperlink or Push button in Grid then

1.Create the Derived/ Work record with appropriate fields(Make sure that they are key fields).
2.Put the fields in the level 0 in both the pages.
3.Assign the values(Student ID and Name)
4.Use the Domaodalcomponent function
SYNTAX:DoModalComponent(MENUNAME.menuname, BARNAME.barname, ITEMNAME.menuitem_name, PAGE.component_item_name, action, RECORD.shared_record_name [, keylist])

Steps to Run the Appilaction Engine Program (Inbound/OutBound) Program through thr Process Scheduler

In Application Designer:
1. Open a New page.
2. Save the page.
3. Click on Insert-->Subpage-->PRCSRUNCNTL_SBP to PRCSRUNCNTL
4. Save it.
5. Drag and drop the Page into component and save it.
6. Register the component.
7. Open the existing menu and drag and drop the component into it.
8. Save it.
Process Scheduler:
1. Open the PS signin page.
2. Click on people tools-->Process Scheduler-->Process
3. Click on Add a new value.
4. Select the process type as Application Engine
5. Enter the process name (ABC1_STU_AE)
6. Click on Process Definition tab and enter the description (Sample student)
7. Click on Process Definition Options and select the component (ABC1_STU_RUN_CMP).
8. Select the Process Groups as ALLPAGES
9. Save it.

Employee Change Request Application Enginer Code

1.The Final Record format should be Emplid,Name,Date, Total working hours,Total working mins,Late time in hours ,Late time in mins.
2 Late Time Caluculation Criterias:
Consider the Shift Begin time as Actual entry time and Shift End time as Actual Exit time.
   Late Time=(Entry time - Actual entry time) if employee is coming late   OR
(Actual Exit Time – ExitTime) if employee is leaving early OR
   [(Entry time - Actual entry time) + (Actual Exit Time – ExitTime)] for both coming late and leaving early.
Negative values thus obtained should be considered as 0.
In case of multiple Time-In and Time-Out on any particular day, Late Time would be:
(First TimeIn - Actual entry time) + (Second TimeIn – First TimeOut) + (Third TimeIn – Second TimeOut) + (Actual Exit Time – Third TimeOut).

Consider the Following fields as constant in calculation of Late Time :
                Actual entry time=8.00
               Actual Exit time=16.00

3.Suppose if the employee dont have either Entry time or Exit time,consider that employee is absent for that particular day (Means Track working Time is Zero).

If the entry time is more than actual entry time consider that as Actual entry time & if the exit time is more than actual exit time consider that as Actual exit time.

In the data base tables if out time is not there for employee u have to insert intime and vice versa.

If i add any new data in the file (for example next day attendence) it should be inserted in the database .

Employee Change Request Application Enginer Code

&MYFILE = GetFile("C:\Documents and Settings\Trainees.AST\Desktop\CR2.csv", "R", %FilePath_Absolute);
&MYFILE1 = GetFile("C:\Documents and Settings\Trainees.AST\Desktop\EMPChange Request AE.TXT", "W", %FilePath_Absolute);
&array = CreateArrayRept("", 0);
&array3 = CreateArrayRept("", 0);
&array4 = CreateArrayRept("", 0);
&intime = CreateArrayRept("", 0);
&outtime = CreateArrayRept("", 0);
&entrytime = 8 * 60;
&exittime = 16 * 60;
&num = 8;
rem Local string &str, &str1;
Local Record &R0;

&R0 = CreateRecord(Record.ABC_EMPCR_TBL);

If &MYFILE.IsOpen Then
While &MYFILE.Readline(&string);
&array = Split(&string, ",");
REM &MYFILE1.WRITELINE(&array.LEN);

&MYFILE1.WRITELINE(&array [1]);
&R0.EMPLID.Value = &array [1];
rem &MYFILE1.WRITELINE(&array [2]);
&R0.ABC_EMPCR_NAME.Value = &array [2];
rem &MYFILE1.WRITELINE(&array [4]);
If &array [4] = "" Then
&R0.ABC_EMP_DATE.Value = "";
Else
&R0.ABC_EMP_DATE.Value = DateValue(&array [4]);
End-If;

If &array [5] <> "" And
&array [6] <> "" Then
&str = TimeValue(&array [5]);
&intime = Split(&str, ".");
&str1 = TimeValue(&array [6]);
&outtime = Split(&str1, ".");

&str = &intime [1] * 60;

rem &MYFILE1.writeline("---Tot Late Min when =8-----" | &str);

If &str = &entrytime Then
&inhrs = 8;
&inmins = &intime [2];
&inmins1 = (&intime [1] * 60) + &intime [2];
&entrymins = 0;
&latentrymin = &inmins - &entrymins;
&MYFILE1.writeline("---Total Late Min when Employee logs at 8=" | &latentrymin);
End-If;

If &str < &entrytime Then
&inhrs = 8;
&inmin = 0;
&MYFILE1.writeline("---Total Late Min when Employee logs at <8-----" | &inmin);
&inmins1 = 480;
End-If;
If &str > &entrytime Then
&inmins = (&intime [1] * 60) + &intime [2];
&entrymins = 8 * 60;
&latentrymin = &inmins - &entrymins;
&MYFILE1.writeline("---Total Late Min when Employee logs at >8-----" | &latentrymin);
&inmins1 = (&intime [1] * 60) + &intime [2];
End-If;

If &outtime [1] > 16 Then
&outhrs = 16;
&outmin = 0;
&MYFILE1.writeline("---Total OUT Late Min when Employee logs OUT at >16-----" | &outmin);
&outmins1 = 16 * 60;
End-If;

If &outtime [1] < 16 Then
&outmins = (&outtime [1] * 60) + &outtime [2];
&exitmins = 16 * 60;
&latexitmin = &exitmins - &outmins;
&MYFILE1.writeline("---Total Late Min when Employee logs OUT at <16-----" | &latexitmin);
&outmins1 = (&outtime [1] * 60) + &outtime [2];
End-If;

If &outtime [1] = 16 Then
&outmins = &outtime [2];
&exitmins = 0;
&outmins1 = (&outtime [1] * 60) + &outtime [2];
rem &latexitmin = &outmins - &exitmins;
rem &MYFILE1.writeline("---Tot Late Min when =16-----" |&latentrymin);
End-If;
&totlatemin = &latentrymin + &latexitmin;
&MYFILE1.writeline("---Tot Late Min = &latentrymin + &latexitmin-----" | &totlatemin);

If Mod(&totlatemin, 60) = 0 Then
&latehrs1 = &totlatemin / 60;
&latehrs2 = Round(&latehrs1, 2);
&MYFILE1.writeline("---Total Late hrs when mod=0-----" | &latehrs2);
Else
&latehrs1 = &totlatemin / 60;
&hrs1 = Substring(&latehrs1, 1, 1);
rem &MYFILE1.writeline("---Tot Late hrs when mod != 0 -----" | &hrs1);
&mins1 = Mod(&totlatemin, 60);
&totlthrs = &hrs1 | ":" | &mins1;
rem late hrs1 and late min;
&MYFILE1.writeline("---Employee Total Late hrs when mod != 0 -----" | &totlthrs);
End-If;

&MYFILE1.writeline("---TOTAL OUT MINUTES---" | &outmins1);
&MYFILE1.writeline("---TOTAL IN MINUTES -----" | &inmins1);
&wrkhrs1 = &outmins1 - &inmins1;
&MYFILE1.writeline("---TOTAL WORKING MINUTES &outmins1 - &inmins1-----" | &wrkhrs1);

/*WORKING HOURS*/
If Mod(&wrkhrs1, 60) = 0 Then

&wrkhrs2 = &wrkhrs1 / 60;

&wrkhrs3 = Round(&wrkhrs2, 0);

&MYFILE1.writeline("---Total WORK hrs when mod=0-----" | &wrkhrs3);
Else

&wrkhrs2 = &wrkhrs1 / 60;
&wrkhrs3 = Substring(&wrkhrs2, 1, 1);
rem &MYFILE1.writeline("---Tot Late hrs when mod != 0 -----" | &hrs1);
&wrkmins1 = Mod(&wrkhrs1, 60);
&totwrkhrs = &wrkhrs3 | ":" | &wrkmins1;
rem Tot Working wrkhrs3 and Tot Working wrkmins1;

&MYFILE1.writeline("---EMPLOYEE TOTAL WORKING HOURS -----" | &totwrkhrs);
End-If;

Else
&wrkhrs3 = 0;
&wrkmins1 = 0;
&hrs1 = 0;
&mins1 = 0;
End-If;

&R0.ABC_EMP_WRKTIME.Value = &wrkhrs3;
&R0.ABC_EMP_TOTMIN.Value = &wrkmins1;
&R0.ABC_EMP_LATEHOUR.Value = &hrs1;
&R0.ABC_EMP_LATEMIN.Value = &mins1;

&R0.Insert();

End-While;
End-If;

rem &R0.Update();
&MYFILE.close();

String to Date conversion (30-dec-2009 to 12/30/2009) in PeopleCode

Below is the code to convert the string to date (30-dec-2009 to 12/30/2009)
&str1 = &array [5];
If &array [5] = "NULL" Then
&oSStu2Tbl.date = "" | "NULL";
&fileLog.WriteLine("&oSStu2Tbl.date = " | &oSStu2Tbl.date);

End-If;
If &str1 <> "" And
Len(&str1) = 9 Then
&str2 = Substring(&str1, 1, 2);
&str3 = Substring(&str1, 4, 3);
Evaluate &str3
When = "Jan"
&mm = 1;
Break;
When = "Feb"
&mm = 2;
Break;
When = "Mar"
&mm = 3;
Break;
When = "Apr"
&mm = 4;
Break;
When = "May"
&mm = 5;
Break;
When = "June"
&mm = 6;
Break;
When = "July"
&mm = 7;
Break;
When = "Aug"
&mm = 8;
Break;
When = "Sep"
&mm = 9;
Break;
When = "Oct"
&mm = 10;
Break;
When = "Nov"
&mm = 11;
Break;
When = "Dec"
&mm = 12;
Break;

End-Evaluate;

rem &b = Month(&str3);

&str4 = Substring(&str1, 8, 2);
&dte = &str2 | "/" | &mm | "/" | &str4;

&oSStu2Tbl.S_STU_DATE = &dte;
&fileLog.WriteLine("&oSStu2Tbl.date = " | &str2 | "/" | &mm | "/"|&str4);
End-If;

If &str1 <> "" And
Len(&str1) = 8 Then
&str2 = Substring(&str1, 1, 1);
&str3 = Substring(&str1, 3, 3);
Evaluate &str3
When = "Jan"
&mm = 1;
Break;
When = "Feb"
&mm = 2;
Break;
When = "Mar"
&mm = 3;
Break;
When = "Apr"
&mm = 4;
Break;
When = "May"
&mm = 5;
Break;
When = "June"
&mm = 6;
Break;
When = "July"
&mm = 7;
Break;
When = "Aug"
&mm = 8;
Break;
When = "Sep"
&mm = 9;
Break;
When = "Oct"
&mm = 10;
Break;
When = "Nov"
&mm = 11;
Break;
When = "Dec"
&mm = 12;
Break;

End-Evaluate;

rem &b = Month(&str3);
&str4 = Substring(&str1, 7, 2);
&dte = &str2 | "/" | &mm | "/" | &str4;
&oSStu2Tbl.S_STU_DATE = &dte;
&fileLog.WriteLine("&oSStu2Tbl.date = " | &str2 | "/" | &mm | "/" | &str4);
End-If;

To overwrite search record on component by using peoplecode

Provide the search keys values in searchinit event.
Use Setsearchdialogbehaviour() function.
Make sure, in the component properties,"Force Search processing" is switched off.

Code to print a image in SQR

let $imgpath = $DB_Server_Path || 'logo.jpeg'
print-image (1,1)
type=jpeg-file
image-size=(75,9)
ource= $imgpath

SQR Migration Strategies

your expert comments and thoughts are invited to further refine this tool.

Type of SQR Migration Strategy

Inbound Interface – Data source: File File Layout + AE + CI if insertion is done to a component

Inbound Interface – Data source: Peoplesoft

App Messaging/IB

Mass Data Changes (Mass hires, Mass update of a table etc.)

AE + CI if updation to a component is done. Analyse whether the Peoplesoft Mass Change functionality can be utilized.

Outbound Interface (Flat files, Dataset)

AE + XML Publisher for report generation/ Web Services if 3rd party supports

SQCs

Custom SQCs can be replaced by Application Classes to be invoked from Peoplecode.

Utility programs (Clean up, File manipulation, Audits, Status Checks)

Audits, File Operations, OS related processes are better done by SQRs, retain them. Utilities that affect database can be replaced by AE.

Letters and Forms (online forms as well as letters that are mailed)

XML Publisher + Peoplecode notification classes for mails

Reports – Simple single data source (reports with one BEGIN-SELECT)

XML Publisher

Reports – Simple multiple data sources (reports with more than one BEGIN-SELECT and simple calculations)

Migration of these might require the use of AE to execute the calculations and XML/XSL modifications. Move to XML Publisher if the logic is not complicated, otherwise retain SQRs.

Reports – On-breaks and multiple reports

XML Publishers

Reports – Complex with multiple BEGIN-SELECTS and complex conditional logic

Do not migrate – the effort is not worth it.

Reports – Status of batch process

This category contains the production of error reports and status reports of processing done by an AE or other batch programs – move to XML Publisher.

SQR Optimisation Techniques

1. Check your SQL queries: Most of the performance issue can be attributed to poorly written SQLs. Critically review each SQL statement of your SQR and ensure their correctness.

2. Usage of Temporary Tables: This is number two on my list. A strategy that I adopt when multiple tables have to be joined and the report involves complex processing, is to pull out the master data and insert the same into a temporary table that has been created in the begin-setup section of the SQR. Further processing can be done by referring the data in the temporary table and this reduces trips to transaction tables whereby giving us significant performance enhancements.

3. Usage of arrays: Arrays are excellent instruments to improve the maintainability of the program. I would always use arrays to hold values of totals and other variables that are to be re-initialised multiple times in the report (note that the values of an array can be reset by using the clear-array command). If variables were used instead, the number of variables that would have to be maintained, the initialisation routine etc. would add to the complexity to the program.

4. Dynamic SQLs: The usage of dynamic sqls greatly reduces the length of a program and helps in improving its performance. One requirement I had was to generate two reports with the same output fields and filter criteria, but one sorted by EMPLID and another by NAME. In this case, the only difference was the ORDER BY clause and instead of writing two different BEGIN-SELECTs, the requirement was achieved by maintaining a single BEGIN-SELECT and passing the order by clause as a dynamic SQL.

5. Break down your SQLs: This is something you will find in Peoplebooks also. Make sure that you are querying the database only when necessary. Let's say you have to produce a report which lists the EMPLID, NAME, BIRTHDATE, MANAGER'S NAME and JOBCODE DESCRIPTION. This will involve the join of 4 tables (including PS_NAMES aliased twice) and considering the key structure of these tables, will take considerable execution time. An alternate strategy would be to have a different procedure to get the JOBCODE descr where only the JOBCODE_TBL is queried for instead of making it a part of the join.

6. Minimal code and re-usable logic: The beauty of SQRs is that it is the most flexible tool in the Peoplesoft technical environment (especially considering the rigidity and ready made nature of an AE, CI or Peoplecode) and offers the developer multiple paths to one solution. Evolve a logic that re-uses your procedures and minimises database trips.

What about Load Lookups? Documentation says that Load Lookups give significant performance enhancements. Practically I have not been able to achieve this. The look up gets loaded at the start of the report and the time taken to load the lookup offsets any advantage that this concept delivers. Usage of temporary tables is a much better alternatives - in effect the concept of the two are inherently the same - reduce database trips and querying of large transaction tables.

Auto creation of user profiles

There are multiple scenarios where its required to create new user profiles in bulk.
The best approach to this problem is to achieve the same by a CI built on the USER_SAVEAS component (Copy User profile component).
Was mulling over the various options to auto generate passwords for the user profiles and ran into the delivered function in DERIVED_HINT.EMAILPSWD.Fieldformula event.

Dynamic Views as Prompt Tables

Dynamic Views are an excellent object type provided by Peoplesoft - a pseudo view that can take any shape! They are excellent candidates where the SQL condition of a view changes dynamically according and are not even a database objects.
There are two major limitations I have come across when you use a Dynamic View as a prompt:
1. If the SQL of the dynamic view contains joins and if the search fields are used in the join.
2. If the SQL of the dynamic view contains joins.

What causes the failure is that the component processor takes the search fields from the prompt and appends to the SQL statement like 'and = '. Now, unfortunately the processor does not do any aliasing which causes the common 'ambiguous column definition' SQL error.

To overcome this, either put the SQL in a view and query from the view or better, treat the SQL as an inline view and query from the inline view.
For example, if the initial SQL of the DV was something like:
SELECT EMPLID FROM PSOPRDEFN UNION SELECT EMPLID FROM PSOPRDEFN, for this to work in a dynamic view, it has to be called from an inline view like:
SELECT EMPLID FROM (SELECT EMPLID FROM PSOPRDEFN UNION SELECT EMPLID FROM PSOPRDEFN)

Gray out an entire page except one field using Peplecode

Option 1 : Use GRAY() function in the page activate event to gray out individual fields.
Option2: Make page display only. and then in the peoplecode use enable property for the field that needs to be enabled.
OR
Loop through
&Rec= Your Record
for &i =1 to &rec.fieldcount
if &rec.field.name <> 'your req field' then
&rec.field.displayonly =true;
else
&rec.field.displayonly =true;
end-if;
end-for;

To display the long name from translate table and display it output in SQR

To display the long name from translate table and display it output in SQR

BEGIN-SELECT
XLATLONGNAME (,132)
FROM
PSXLATITEM
WHERE
FIELDVALUE = &RELATION
AND FIELDNAME = 'RELATIONSHIP'
END-SELECT
(Note:Call it in a procedure)

How to delete process definition

The BEST way to delete any PeopleTools definition that you can insert as an object into application designer is by setting the action to delete for the relevant definitions, copying the project to file and reimporting the project

The exact steps are covered below:
http://peoplesoft.wikidot.com/deleting-definitions

You are not authorized for this page error in PeopleSoft

These are the steps you will have to follow further to avoid the error:
a) Go to Peopletools > Portal > Structure and Content. (Navigate to your component)
b) Go to security tab
c) Change the security to public(makes all permission list to go) and now again Permission List access (making all permission list to reappear).
d) Save the page
e) Check if the Permission list is shown after the save..
f) Now sign-out and delete the application server and browser cache.
g) Try login again and access the page

XML navigations Creating the data Source through PS QUERY (Reporting Tools) in PeopleSoft

Creating the data Source through PS QUERY (Reporting Tools)

Defining Data source:

1.Click on Reporting Tools.
2.Click on XML Publisher.
3.Click on Data Source.
4.Click on Add a New Value.
5.Select the Data Source type as PS Query.
6.In Data Source ID select ur Query (Ex:HARSHA_QRY)
7.Click on ADD.
8.Enter the Description Name (Ex:Harsha)
9.Selct the Object Owner ID as Human Resource.
10.Click on Save.

Step2: Defining Data Source

11.In the Related Files-->Sample Data File-->Click on Generate
12.In the Related Files-->Schema File-->Click on Generate.
13.Click on Save.
14.Open the XML file and Save As Harsha1_Qry on Desktop.

Step3: Building/Creating Template:

1.Open MS Word.
2.Click On Add-Ins.
3.Click on Template Builder-->Data-->Load XML data.
4.Click on Template Builder-->Insert-->Table/ Form
5.Drag and dorp the Rows in to Template Work sapce and click on All Nodes.
4.Click on Ok.
5.Save the MS Wrod sheet with same name given to the XML(HARSHA_QRY) with RTF

Step4: CLICK ON REPORT DEFINITION

1.Select the Existing saved Report(HARSHA_QRY)
2.In the Object owner Id make it empty.
3.Clickon template tab
4.Change the status to Active.
5.Click on Upload.
6.Selct the Existing Word doc
7.Click on Save.

CLICK ON QUERY REPORT VIEWER

1.In the Query Report Viewer select ur existing report.
2.Click on View Report.

PeopleSoft WorkFlow Navigations

WorkFlow:-
TO CREATE THE WORKFLOW IN APPLICATION DESIGNER
INSERTING AN WORKLIST INTO THE EXISTING PROJECT:
1. Before that inserting the worklist
2. Copy the existing worklist from R_TT_WL
3. Save it as SRI_EMP_WL
4. Click on F7
CREATING THE BUSINESS PROCESS:
1. Click on New-->Business Process.
2. Drag and drop the Activity1 on to the work space.
3. Drag and drop the Activity2 on to the work space.
4. Link Both Activity1 and Activity2
5. Save the Business process as SRI_EMP_BP.
6. Click on F7.
Creating Activity
1. Open an Existing Project.
2. New--> Activity
Click on the Step (Blue color) and Click on the Work space.
-->Double click on the step and Name it as SHE_EMP_ACT1
-->Give the required Icon Description.
-->Click on the Attributes and Select the Menu, Bar, Item, Page, Action (as add) Names in the project.
-->Click on Ok.
Click on the Event (Yellow color) and Click on the Work space beside the step.
-->Double click on the Event
-->Name: SRI_EVENT and Icon Descr as SRI_EVENT.
-->Select the record name SRI_EMP_TBL.
-->Click on Edit Business Rules which opens a window. And write the following below code:
&I=TriggerBusinessEvent(BUSPROCESS.bus_proc_name,BUSACTIVITY.activity_name,BUSEVENT.bus_event_name)
-->Save it.
-->Click on Ok.
Click on the Worklist (Yellow color rectangle) and Click on the Work space beside the Event.
-->Double click on the Work List.
-->Name: Worklist and Icon Descr: Worklist.
-->Click on Attributes.
-->Select the worklist from the drop down list (SRI_EMP_WL).
-->Select the Business Process from the Drop downlist (SRI_EMP_Bp)
-->Select the Activity2 from the drop down list(SRI_EMP_ACT2)
-->Click on oK.
-->Click on Field Mapping.
-->Click on ADD
-->Select the Field name as OPRID and Map module as copy(by default)
-->Select the radio button Constant.
-->Give the name of the people who want to see the work report (Ex:SARAT)
-->Click on Ok.
-->Click on ADD.
-->Select the Field name as SRI_EMPAE_TBL and Map module as COPY
-->Select the radio button RecField. (sELECT THE FIELD NAME TO BE MAPPED)
-->Click on ok.
--->Click on the link(beside the ?) and point it on the Step towards the Event in the workspace and same to the Worklist.
2. Again create another activity with only one event and save it as SRI_EMP_ACT2
-->Select the Menu Name as ADMINISTER_WORKFORCE_(GBL)
-->Select Bar Name and Use
-->Item Name as JOB_DATA
-->Page Name as EMPLOYEMENT_DATA
-->Action as &UPDATE/DISPLAY
-->Click on Ok
NOTE:-IF WE GET SQL ERROR THEN CHECK OUT WITH THE BUSINESS PROCESS NAME

Pages

Wednesday, May 19, 2010

Wednesday, May 5, 2010

Search This Blog

About Me

Blog Archive

Live Traffic Feed