PeopleSoft Learning

To Change the Access ID password, possible methods are listed below:

There are ONLY 3 ways to do this. These options MUST be done in 2 tier and your application servers, Process Schedulers, and Web Servers MUST BE DOWN and ALL users need to be logged off if possible.

METHOD # 1 - (Preferred Method) Log into Data Mover as the Access ID (Bootstrap mode).

Then run this command:

CHANGE_ACCESS_PASSWORD sa1 (This is your symbolic ID) cloud123 (This is the new password for the Access ID);

And hit the Green traffic light.

NOTE - This method does NOT work in PT 8.14 or 8.15, but is now fixed in PT 8.16 (T-JPELAY-AU4QW) so use method 2 or method 3. You MUST also make sure that the password for the Access ID has successfully been changed at the Database level because prior to PT 8.17 this method would not change the database level password. To do this go through your SQL tool (SQL Plus, Query Analyzer) and make the change in the database users table.

METHOD # 2 - (Second best method) You need to change the Access ID Password at database level then log into Data Mover in bootstrap mode and run:

update PSACCESSPRFL set ACCESSID = 'what your Access ID is' , ACCESSPSWD = 'your New Access Password' , ENCRYPTED = 0, where SYMBOLICID = 'whatever the SYMBOLICID is';

Then run

ENCRYPT_PASSWORD *;

Note: If you have more than 45,000 rows in the PSOPRDEFN table then you may not be able to run the ENCRYPT_PASSWORD *; command in Data Mover. In that case just logon as a valid user in 2 tier and that will encrypt the Access ID in the PSACCESSPRFL table.

This will synch up the PSACCESSPRFL table with the same password stored at the database level for your Access ID.

METHOD # 3 - (Last and most discouraged method) Go into Application Designer / Tools / Miscellaneous Objects / Access Profiles.

Highlight the Symbolic ID and push the Edit button, enter the old password, new password, and confirm new password.

If you change it in Application Designer it does not change at the database level. This is because we do not GRANT to the database in PT 8. You MUST also make sure that the password for the Access ID has successfully been changed at the DB level. To do this go through your SQL tool (SQL Plus, Query Analyzer) and make the change in the database users table.

Creating HTML output from SQR

To use the HTML procedures listed below, your program must reference the HTML procedures.

Issue the following two commands at the start of your program:

#include html.inc

do html_on

The file html.inc is located in the SAMPLE (or SAMPLEW) directory.  Use the command-line flag -I to indicate this location.

There are 6 types of HTML procedure that can be used to easily create HTML o/p files.

General Purpose Procedures

Heading Procedures

Highlighting Procedures

Hypertext Link Procedures

List Procedures

Table Procedures

Eg:-        

1) Html_br

Produces the specified number of line breaks in a paragraph using the HTML<BR> tag.

This causes the paragraph to be continued onto the next line.

Syntax: html_br(number count, string attributes)

count    = the number of HTML <BR> tags that are inserted.

            Attributes          =          the HTML attributes that are incorporated inside the HTML <BR> tag.

Example: Producing a line break:

            Print 'Here is some text' ()

            Do html_br (3,'')

            Print 'Here is some three lines down' ()

2) Inserts an image using the HTML <IMG> tag. This can also be done using the command PRINT-IMAGE;

However, the procedure html_img provides the ability to specify the full set of available HTML attributes.

Syntax:             html_img (string attributes)

Attributes          =          the HTML attributes that are incorporated inside the HTML <IMG> tag.

Some common attributes:

Src       =          URL of the image to be inserted (Ex:  src=/images/abc.gif)

Height =            height of the image in pixels (Ex:  height=200)

Width   =           width of the image in pixels (Ex:  width=400)

Example:          Producing an image:

Do html_img('src="/images/stop.gif"')

-Posted by

Ganesh A.M

http://www.roughsea.com/vids/SQL_Best_Practices.html

-Ganesh A.M

Field default event Peoplecode in Peoplesoft

The People code in this event is used to initialize the default values for the fields in the component.

What happens when the field has default value defined in the record field properties and has People code assign the different value in the Field default people?

The Field default event People code will only fired when there is not default value defined for that field in the record field properties, so the value assigned in the record field properties will be initialized to that field.

-Ganesh A.M

Common Query Access Issues

a) User cannot see a table they wish to query from in query manage

   User does not have access to the table via one of their Access Group. Double-Click that they have the Proper Access group on one of their Permission lists an ad the table to an Access group if necessary.

b) A user cannot see a public query from search screen in either query manager or query viewer.

     Once you have determined that the query does in fact exist, and I s indeed a public query, then the user probably does not have access to all the tables being used in the query. If a user does not have access to all the tables involved in a query, the search screen itself will filter out the query, so that it does not even appear in the list. Double-Check that they have the proper Access group on one of their permission lists and add the table to an Access group if necessary.

Posted by

-Ganesh A.M

Row Level Security in Peoplesoft HRMS

The Default department security is name DEPT_SECURITY and the SetID is the share, although you may choose to create one to operate with a different Setid, depending on your environment.

Root level on this tree represents your company or organization. Below that you are adding departments in a hierarchical manner until you have built on organizational char of your company’s department structure.

When you click the link on one of department levels to either insert a child or a sibling node, you will be allowed to either choose an existing department that has been created or to create a new department on the fly by clicking Add.

Tie date Permission lists to Your Trees

Define Business Rules - > Administer HR systems -> Use -> Maintain Row level Security

1)       Select a Permission list – It needs to created in the maintain security menu.

2)       Peoplesoft standard is to generally preface them with DP

3)       In the Data security profile page, you need to add the Departments that this permission list will have access to.

4)       You need to provide SetID for the Department Security Tree you are using and then enter the DeptID for the node you are choosing.

5)       Department Security Tree is hierarchical, that by adding an access to a department, you are automatically granting access to all the departments below that node as well.

If you need to exclude the data from a particular ‘Sub-Department’ you will have need to add that department separately to this list, and choose ‘No Access’ as the Acess code instead of the default of ‘Read/Write’.

Change the security Basis for the System

The final step to implement Row level security in HR is to change all the search views controlling which data is accessed on various pages. Luckily you don’t have to change it manually change each one, as Peoplesoft has delivered a process that takes care of it all for you. Please note that you only run this Process when turning on Department security System-Wide, Or when removing it from the system.

Define Business Rules -> Define General options - > Process - > Change Row Security Basis.

You need to choose option for Department security, to turn Department security on, or the Option of ‘None’, to turn Department security off.

-Posted by

Ganesh A.M

To enable change control in the Application Designer, navigate to the link [shown in the picture] in the application designer and check the check box appropriately.

• Use change control locking

• Use change control history

Exception Handling in Peoplesoft

Exception handling is the processing you initiate when an exception occurs. You can handle errors in PeopleCode using the Catch statement. The places in your code where you want exceptions handled must be enclosed by the Try and End-Try statements.

The Exception class encapsulates exceptional runtime conditions. It can be used with most PeopleCode programs.

The process of error handling can be broken down as follows:

• An error occurs (either a hardware or software error).

• The error is detected and an exception is thrown (either by the system or by your program).

• Your exception handler provides the response.

You can create exceptions in 2 ways

1) Creating an Exception base class that encapsulates the built-in function call and handles its function parameters consistently. This is the preferred way.

2) Calling the built-in function CreateException.

Application code often continues some form of processing even in the event of an exception; you should not assume that the script all processing if an exception occurs.

The error message that PeopleTools displays might not be appropriate for end-users because of its technical information.

The Exception class does not work with any of the PeopleSoft APIs, that is, the classes whose objects are declared as type ApiObject. This includes the Tree classes, the Query classes, Search classes, and so on.

try

Protected StatementList

catch QualifiedID &Id

StatementList

End-try;

Example 1:-

Import ExceptionPackage:*; 

try               /* Code to manipulate &MyArray here */

 &MyArray = GetArrayData(&Param1, &Param2, &Param3); 

catch ExceptionNull &Ex1

 If &Ex1.MessageSetNumber = 2 and &Ex1.MessageNumber = 236 Then 

   /* this is the message set and message number for &MyArray being Null */ 

   /* do data error handling */

   End-if;

End-try;

Example 2:-

Local Exception &ex;  

Function t1(&i As integer) Returns number 

   Local number &res = &i / 0;     

End-Function; 

Function t2 

   throw CreateException (2, 160, "'%1' doesn't support property or method '%2'", "SomeClass", "SomeMethod"); 

End-Function; 

/* this will cause a divide by 0 leading to an exception. This code will never be caught since t1(2) will resume execution in the catch block below. It is here to show how an exception can be thrown directly bythe Peoplecode itself. */

try 

  t2 ();   

Local number &res = t1 (2); 

Catch Exception &caught 

   MessageBox (0, "", 0, 0, "Caught exception: " | &caught.ToString()); 

end-try;

Posted by

Ganesh

Application Classes in Peoplesoft – Reference sheet / Points remember

• Importing packages or classes

<Pacakge name>:<Subpacakge name>:<…>:<Class name or wild card>

• Class Extensions represents the “is-a” relationship.

      When a class extends another class, it’s called a subclass of that class.

• No Multiple inheritances or Concept of interface in Application classes.

• Instance variables in the class are accessible in all the objects of that class.

• Application programs generally pass parameters by value, which is not the same as for existing functions.

• Parameter passing with object data types is by reference.

• When the objects are passed to method and it reassign the object with new object it will not reflected in the calling method.

• Application Programs use the out specifier to pass a parameter by reference.

Method increment (&Value as number out); rem passed by reference.

• Create is the key word used to create the class object.

            Local MyObjectClass &o1 = Create MyobjectClass (‘A’);

            Local MyObjectClass &o1 = Create Test: Examples: MyobjectClass (‘A’);

• If parameter in a method is having out specifier then parameter should be a variable and cannot be constant.

• A class that extends another class must have constructor, and in the constructor it must initialize its super class.

          To initialize a superobject, an instance of the superclass is assigned to the keyword %Super in the constructor for the subclass.

Class Example extends ExampleBase

    Method Example ();

           End-class;

           Method Example         

              %Super = create ExampleBase ();

              &BaseStrin = &currentBaseString;

             &Slashstring = &BaseString;                

          End-Method;

• Before the constructor runs, the instance variables for the class are set by default takes on their declared types.

• An application class doesn’t have destructors, that is, a method called just before an object is destroyed. The People code runtime environment generally cleans up any held resources.

• When application class properties and instance variables are used as the argument to functions or methods, they are always passed by value, never by reference.

• %This is can be used to self reference. This is to refer the same object.

• %Super = %This – this should never be done it will go to an infinite loop.

• Import Fruit:* - imports all the classes in the application package.

• Import statements are validated when you save the Peoplecode.

• Peoplesoft recommends that you use read-write or ready only properties instead of creating methods name GetXxx and SetXxx.

• Getter and Setter syntax

Get Propertyname

 ---

End-get;

• External Functions Declarations are allowed in application classes, in the global and component variable declarations, after the class declaration (after the end-class) and before the method definition.

• %Super is only required to access superclass members that are hidden by overriding members in the current time.

• Downcasting is the process of determining if an object is of a particular subclass type. If the object has that subtype (either the object is of that subtype, or is a subtype of it), a reference to the subject is returned, otherwise Null is returned. In either case, the type of the resulting value is compatible with the name subclass type.

• Class Fruit

          Property number fruitcount;

End-class;

           Class Banana extends Fruit

              Property number BananaCount;

          End-Class; 

      Local Banana &MyBanana = Create Banana ();

     Local Fruit &MyFruit = &MyBanana;   /* Okay, Banana is a subtype of Fruit */

    Local number &num = & MyBanana.BananaCount;

         &MyBanana = &MyFruit as Banana;   /* &MyFruit is currently a Banana at runtime */

        &Num = (&MyFruit as Banana).BananaCount; /* same as &MyBanana.BananaCount */

        &MyFruit = Create Fruit ();

   &MyBanana = &MyFruit as Banana; /* Assigns Null - &Myfruit isn’t a Banana */

• /* */ and /**  */ comments are allowed. Comments enclosed in  /** -- */ are potentially be used to generate API documentation.

• Method header comments are uses some tags which helps in API documentation

Some of the tags are

·        @Parameter N

·        @exception name

·        @return type

Class header comments contains tags

·        @version X (value of version)

·        @author  name

Example

/**

 * Class header comments

* @Version 1.0

* @author Ganesh

*/

Import PWWPack: ExampleBase

Class Example extends ExampleBase

   Method Example ();                                     /*Constructor*/

   Method NumToStr (&Num As number) Returns string ();

   Method AppendSlash ();

   Property number SlashCount get;                   /*Get properties */

   Property number ImportantDayofWeek get set; /*Get and set properties */

   Property string Slashstring readonly;   /* values can be assigned in constructor */

   Property date ImporantDate;

Private

   Method NextDayofWeek (&Dow As number) returns date;

   Constant &Sunday = 1;              /*Constants */

   Instance string &Basestring;      /* Instance variables are like static variable */

End-class;                                                                                                           

Declare function getusername Peoplecode FUNCLIB_PP.STRING_FUNCTIONS FieldFormula;

/**

* Method header comments example

* @param Dow is a number parameter

* @exception it throws Invalid day exception if the number is negative.

* @return   it is returns the date as of that week.

*/

Method NextDayofWeek  (&Dow)

      ---------

    ---------

End-method;

/* get block */

Get SlashCount

  Return (Slashcount);

End-get;

- Posted by Ganesh

View to analyze the Process in the process monitor in Peoplesoft.

This view gives clear information about the process with time taken for the execution in hours minutes and seconds.

SELECT PRCSINSTANCE

, PRCSNAME

, OPRID

, RUNCNTLID

, SERVERNAMERUN

, RQSTDTTM

, RUNDTTM

, BEGINDTTM

, ENDDTTM

, floor(TO_CHAR((enddttm-begindttm)*24*60)) AS MINUTES

, lpad(FLOOR(((TO_CHAR((enddttm-begindttm)*24*60))-floor(TO_CHAR((enddttm-begindttm)*24*60)))*60)

,2

,0) AS SECONDS

, decode (FLOOR(TO_CHAR((enddttm-begindttm)*24*60))||':'||lpad(FLOOR(((TO_CHAR((enddttm-begindttm)*24*60))-floor(TO_CHAR((enddttm-begindttm)*24*60)))*60)

,2

,0)

, ':'

,' '

,FLOOR(TO_CHAR((enddttm-begindttm)*24*60))||':'||lpad(FLOOR(((TO_CHAR((enddttm-begindttm)*24*60))-floor(TO_CHAR((enddttm-begindttm)*24*60)))*60)

,2

,0)) AS MINSEC

, RUNSTATUSDESCR

FROM PS_PMN_PRCSLIST

ORDER BY RUNDTTM DESC

Posted by

Ganesh A.M

%InsertSelect

Syntax

%InsertSelect([DISTINCT, ]insert_recname, select_recname [ correlation_id][, select_recname_n [ correlation_id_n]] [, override_field = value]. . .)

Description

The %InsertSelect function will generate an INSERT statement with a SELECT for you. It does not generate the FROM statement. You must specify all the select records before you specify any over ride fields.

The INSERT column list is composed of all the fields in the specified insert_recname, with the exception of LONG_CHAR or IMAGE fields.

The following code:

%InsertSelect(AE_SECTION_TBL, AE_STEP_TBL S, AE_SECTION_TYPE = ' ')

   FROM PS_AE_STEP_TBL S, PS_AS_STMT_TBL T

WHERE. . .

will resolve into the following:

INSERT INTO PS_AE_SECTION_TBL (AE_APPLID, AE_SECTION,..., AE_SECTION_TYPE)

SELECT S.AE_APPL_ID, S.AE_SECTION, ... ' '

FROM PS_AE_STEP_TBL S, PS_AS_STMT_TBL T

   WHERE. . .

%InsertSelect(MY_TEMP, MY_TABLE1, MY_TABLE2 T2)

   FROM PS_MY_TABLE1 T1, PS_MY_TABLE2 T2

WHERE %Join(COMMON_KEYS, MY_TABLE1 T1, MY_TABLE2 T2) . . .

This code will resolve into:

INSERT INTO PS_MY_TEMP (FIELD1, FIELD2 . . .)

   SELECT T2.FIELD1, T2.FIELD2, . . .

FROM PS_MY_TABLE1 T1, PS_MYTABLE2 T2

WHERE T1.FIELD1 = T2.FIELD1

AND T1.FIELD2 = T2.FIELD2 . . .

-Posted by

Ganesh A.M

1. How to trace AE?

There are a few ways to trace an AE program. You can enable the trace using some of the below methods:

1. In the process scheduler configuration file psprcs.cfg
2. You can enable the trace in the “Process Definition”.
3. Grab the Oracle session information and generate SQL Trace

It is not advised to enable the trace using psprcs.cfg because it will enable trace for ALL AE programs using that scheduler. However, this might be the best choice in an environment where you want to trace multiple AE programs or you do not have access to modify the process definition. Exercise this option with caution knowing that it might generate trace files for all AE programs.

To enable trace using “Process Definition”, make the below change. You can use the “-TRACE” or “-TOOLSTRACESQL” or “-TOOLSTRACEPC” option depending on what information you require.

2. Which AE program has trace enabled in its process definition?

The below SQL will help you determine all the AE programs that have TRACE enabled. It will be useful to use this SQL in your monitoring scripts if you notice developers enabling trace using the process definitions and getting the program with its process definition migrated all the way to production or if your analyst or DBA have the tendency to enable the trace and forget about it.

SELECT PRCSNAME, PARMLIST

FROM PS_PRCSDEFN

WHERE UPPER(PARMLIST) LIKE '%TRACE%'

AND PRCSTYPE = 'Application Engine';

Posted by

Ganesh A.M

Peoplesoft Page Security View.

This view will give the clear mapping of the oprid and permission that he has on the set of the pages in particular menu path.

This view also displays ROLENAME and CLASSID through which oprid has the permission to a particular page.

SELECT DISTINCT h.oprid

, h.oprdefndesc

, g.rolename

, a.classid

,Decode(SUBSTR (b.menugroup

, 1

, INSTR (b.menugroup

, '&') - 1) || SUBSTR (b.menugroup

, INSTR (b.menugroup

, '&') + 1

, LENGTH (b.menugroup) )

,'PeopleSoft'

,'PeopleTools'

,SUBSTR (b.menugroup

, 1

, INSTR (b.menugroup

, '&') - 1) || SUBSTR (b.menugroup

, INSTR (b.menugroup

, '&') + 1

, LENGTH (b.menugroup) )) AS menugroup_label

, SUBSTR (b.menulabel

, 1

, INSTR (b.menulabel

, '&') - 1) || SUBSTR (b.menulabel

, INSTR (b.menulabel

, '&') + 1

, LENGTH (b.menulabel) ) AS menu_label

, a.menuname

, c.barname

, SUBSTR (c.barlabel

, 1

, INSTR (c.barlabel

, '&') - 1) || SUBSTR (c.barlabel

, INSTR (c.barlabel

, '&') + 1

, LENGTH (c.barlabel) ) AS bar_label

, SUBSTR (c.itemlabel

, 1

, INSTR (c.itemlabel

, '&') - 1) || SUBSTR (c.itemlabel

, INSTR (c.itemlabel

, '&') + 1

, LENGTH (c.itemlabel) ) AS menuitem_label

, c.pnlgrpname AS component

, DECODE (a.authorizedactions

, 1

, 'Add'

, 2

, 'Update/Display'

, 3

, 'Add Update/Display'

, 4

, 'Update/Display All'

, 5

, 'Add Update/Display All'

, 6

, 'Update/Display Update/Display All'

, 7

, 'Add Update/Display Update/Display All'

, 8

, 'Correction'

, 9

, 'Add Correction'

, 10

, 'Update/Display Correction'

, 11

, 'Add Update/Display Correction'

, 12

, 'Update/Display All Correction'

, 13

, 'Add Update/Display All Correction'

, 14

, 'Update/Display Update/Display All Correction'

, 15

, 'Add Update/Display Update/Display All Correction'

, 129

, 'Add Data Entry'

, 131

, 'Add Update/Display Data Entry'

, 135

, 'Add Update/Display Update/Display All Data Entry'

, 143

, 'Add Update/Display Update/Display All Correction Data Entry'

, 139

, 'Add Update/Display Correction Data Entry'

, 133

, 'Add Update/Display All Data Entry'

, 137

, 'Add Correction Data Entry'

, 141

, 'Add Update/Display All Correction Data Entry'

, 130

, 'Update/Display Data Entry'

, 134

, 'Update/Display Update/Display All Data Entry'

, 138

, 'Update/Display Correction Data Entry'

, 142

, 'Update/Display Update/Display All Correction, Data Entry'

, 132

, 'Update/Display All Data Entry'

, 140

, 'Update/Display All Correction Data Entry'

, 136

, 'Correction Data Entry'

, 128

, 'Data Entry' ) AS authorized_actions

, SUBSTR (d.itemlabel

, 1

, INSTR (d.itemlabel

, '&') - 1) || SUBSTR (d.itemlabel

, INSTR (d.itemlabel

, '&') + 1

, LENGTH (d.itemlabel) ) AS componentitem_label

, d.pnlname AS pagename

, DECODE (a.displayonly

, 1

, 'READONLY'

, 0

, 'WRITE') AS read_write

, c.itemnum

, d.subitemnum

FROM psauthitem a

, psmenudefn b

, psmenuitem c

, pspnlgroup d

, pspnlgrpdefn e

, psroleclass f

, psroleuser g

, psoprdefn h

WHERE a.menuname = b.menuname

AND a.menuname = c.menuname

AND a.barname = c.barname

AND a.baritemname = c.itemname

AND a.PNLITEMNAME=d.ITEMNAME

AND c.itemtype = 5

AND d.pnlgrpname = c.pnlgrpname

AND d.market = c.market

AND d.pnlgrpname = e.pnlgrpname

AND d.market = e.market

AND e.pnlgrpname = c.pnlgrpname

AND e.market = c.market

AND f.classid = a.classid

AND f.rolename = g.rolename

AND g.roleuser = h.oprid

AND h.acctlock = 0

-Posted by

Ganesh A.M