Wednesday, December 21, 2011

The number of users connected on the environment at the moment

The below query used to get the number of users connected on the environment at the moment.

   1:  select DISTINCT OPRID,
   2:                  LOGIPADDRESS,
   3:                  TO_CHAR(LOGINDTTM, 'YYYY-MM-DD:hh:mi:ss') LOGINTIME,
   4:                  TO_CHAR(LOGOUTDTTM, 'YYYY-MM-DD:hh:mi:ss') LOGOUTIME,
   5:                  TO_CHAR((sysdate), 'YYYY-MM-DD:hh:mi:ss') CURRTIME
   6:    FROM sysadm.PSACCESSLOG WHERE
   7:   (sysdate) - cast(LOGINDTTM as date)  >= 0
   8:   and cast(LOGOUTDTTM as date) - to_date(sysdate) >= 0
   9:   and LOGOUTDTTM = LOGINDTTM;
 
The information is still not accurate (If the user closes the browser or the connection crash).

Thursday, December 1, 2011

Trace Application Engines

Application Engines can be traced on two ways.

1. On The PIA.

2. On the Process Scheduler and Application Server.

Trace Application Engines On The PIA:

On the PIA, we can activate tracing for a particular Application Engine.

When activating tracing on the Client or on the Process Scheduler, any Application Engine run will generate a trace file.

Suggested Process Scheduler Trace Setting:

-TRACE 391- TOOLSTRACESQL 143 -TOOLSTRACEPC 1996

clip_image001

Go to the ‘Override Options’ tab. Set the ‘Parameter List’ option to ‘Append’ and add the tracing flags as explained below.

Tracing Options:

Application Engine tracing:

Add the sum of all the options you want to use at the end of the parameter list and precede the number by ‘–TRACE + a space’. For example:

-TRACE 391

Here is the list of options and associated numbers for each option:

1 Initiates the Application Engine Step trace

2 Initiates the Application Engine SQL trace

4 Trace dedicated Temp Table Allocation to AET file

128 Initiates the Statement Timings trace to file, which is similar to the COBOL timings trace to file

256 Initiates the PeopleCode Detail to the file for the Timings trace

1024 Initiates the Statement Timings trace, but, instead of writing to the trace file, this trace stores the results in the following tables: PS_BAT_TIMINGS_LOG and PS_BAT_TIMINGS_DTL

2048 Adding this value requests a database optimizer trace file

4096 Requests a database optimizer to be inserted in the Explain Plan Table of the current database

8192 This value sets a trace for Integration Broker transform programs

To turn traces on, sum all the options you want to use and enter the results at the end of the parameter list and precede the number by ‘–TRACE + a space’ as shown above. For example, you should use ‘391 (1+2+4+128+256) to trace what is shown in bold above.

SQL tracing:

Add the sum of all the options you want to use at the end of the parameter list and precede the number by ‘–TOOLSTRACESQL + a space’. For example:

-TRACE 391 -TOOLSTRACESQL 143

Here is the list of options and associated numbers for each option:

1 Trace SQL statements

2 Trace SQL statement variables

4 Trace SQL connect, disconnect, commit and rollback

8 Show fetched rows (indicates that it occurred, not data)

16 Show all other API calls except ssb

32 Set Select Buffers (identifies the attributes of columns to be selected).

64 Show database API specific calls

128 Show COBOL statement timings

256 Show Sybase bind information

512 Show Sybase fetch information

4096 Show manager information

8192 Show Mapcore information

To turn traces on, sum all the options you want to use and enter the results at the end of the parameter list and precede the number by ‘–TOOLSTRACESQL + a space’ as shown above. For example, you should use ‘135’ (1+2+4+128) to trace what is shown in bold above.

PeopleCode tracing:

Add the sum of all the options you want to use at the end of the parameter list and precede the number by ‘–TOOLSTRACEPC + a space’. For example:

-TRACE 391 -TOOLSTRACEPC 1996

or

-TRACE 391 -TOOLSTRACESQL 135 -TOOLSTRACEPC 1996

Here is the list of options and associated numbers for each option:

1 Trace instructions

2 List the program

4 Show assignments to variables

8 Show fetched values

16 Show stack

64 Trace start of programs

128 Trace external function calls

256 Trace internal function calls

512 Show parameter values

1024 Show function return values

2048 Trace each statement in program

To turn traces on, sum all the options you want to use and enter the results at the end of the parameter list and precede the number by ‘–TOOLSTRACEPC + a space’ as shown above. For example, you should use ‘2012’ (4+8+16+64+128+256+512+1024) to trace what is shown in bold above.

clip_image002

NOTE: DO NOT FORGET TO TURN TRACING OFF by setting the ‘Parameter List’ option to ‘None’ in the ‘Processes’ component – ‘Override Options’ tab.

Wednesday, November 30, 2011

How to embed videos into a Peoplesoft system

To show/play a video on the PeopleSoft page, you don't have to copy the video to PeopleSoft. Instead, it just needs to be available from a web server and then you just use an iframe, flash player, or whatever object/embed html is required for the media type.
The way you embed the video depends on your tools release. If it is 8.49 or earlier, you will have to modify the delivered page. If it is 8.50, you can create an HTML based Pagelet Wizard pagelet and then configure the PeopleSoft page/component to use this pagelet in related content. If you have PeopleTools 8.51, then you can either use related content as in 8.50 or you can configure the page/component to be a work center and display the pagelet wizard pagelet in the work center. The great thing about these 8.50/51 options is that they are configurations that survive an upgrade.
If the videos are already available on an internal site, then you can just source that content, you don't have to copy it into your PeopleSoft web server. If they don't, then just put the videos in the PORTAL directory of your web server installation. You should probably create a new sub directory of the PORTAL directory and put them in that new directory.
Using a separate web server (apache, nginx, IIS etc) for hosting the videos would be a lot better though. If you are using load balanced web servers, you would have to put the videos on every prod instance of the load balanced web servers.

Source: Oracle support [Doc ID: 1327375.1]

Thursday, November 17, 2011

Query to find the 10 Largest Objects in DB

 
SELECT * FROM
(
select
SEGMENT_NAME,
SEGMENT_TYPE,
BYTES/1024/1024/1024 GB,
TABLESPACE_NAME
from
dba_segments
order by 3 desc
) WHERE
ROWNUM <= 10

Friday, November 4, 2011

Query to remove HTML Tags from Rich Text Long edit box


Below query used to remove HTML tags from Rich text box field.
SELECT REGEXP_REPLACE(DESCRLONG,'<[^>]*>',' '), DESCRLONG FROM PS_HRSTOR_QA_TBL;
Note: Above query only works on Oracle database.

Wednesday, August 24, 2011

Run Process from hyperlink without code

The following steps help to run process from hyperlink or from push button without any code.

 clip_image002

clip_image002[11]

image

Friday, August 12, 2011

Link To Portal Folder

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

Declare Function NavPageURL PeopleCode EOPP_SCRTN_WRK.FUNCLIB FieldFormula;

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

Thursday, August 11, 2011

Objects that can't be copied through File

Following objects can’t be copied through Copy project to File option.

· Access Groups
· Trees
· Roles
· Message Catalog Entries

Sunday, August 7, 2011

SQL To find Who Modified PeopleCode

The below SQL helps to identify who modified PeopleCode.

SELECT objectvalue1 record_name, objectvalue2 field_name,
       objectvalue3 peoplecode_event, lastupddttm, lastupdoprid
  FROM pspcmprog
WHERE objectvalue1 = :record_name
   AND objectvalue2 = :field_name
   AND UPPER (objectvalue3) = UPPER (:peoplecode_event);

Thursday, August 4, 2011

Set the number of links available under a Portal folder

Navigate to PeopleTools > Portal > Portal Utilities > System Options and update "Maximum Columns" to 3.

image

Out Put:

image

Wednesday, August 3, 2011

Functions with EFFDT and EFFSEQ to get row

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

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

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


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



3. Obtain the row number of Next Effective date row


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



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


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



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



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


Friday, July 15, 2011

PeopleSoft Permission List Queries

1. Component Permission List Query:
This query identify the permission lists and its description associated with component.
SELECT   menu.menuname, compdfn.pnlgrpname, auth.classid permission_list,
CLASS.classdefndesc permission_desc
FROM psauthitem auth,
psmenudefn menu,
psmenuitem menuitm,
pspnlgroup comp,
pspnlgrpdefn compdfn,
psclassdefn CLASS
WHERE menu.menuname = menuitm.menuname
AND menuitm.pnlgrpname = comp.pnlgrpname
AND compdfn.pnlgrpname = comp.pnlgrpname
AND compdfn.pnlgrpname LIKE UPPER (:component_name)
AND auth.menuname = menu.menuname
AND auth.barname = menuitm.barname
AND auth.baritemname = menuitm.itemname
AND auth.pnlitemname = comp.itemname
AND auth.classid = CLASS.classid
GROUP BY menu.menuname, compdfn.pnlgrpname, auth.classid, CLASS.classdefndesc
ORDER BY menu.menuname, compdfn.pnlgrpname, permission_list;

2. Content Reference accessed by a permission list:

This query identifies Content references accessed by Permission List.
SELECT   a.portal_label AS PORTAL_LINK_NAME, a.portal_objname, a.portal_name, a.portal_reftype
FROM psprsmdefn a, psprsmperm b, psclassdefn c
WHERE a.portal_reftype = 'C'
AND a.portal_cref_usgt = 'TARG'
AND a.portal_name = b.portal_name
AND a.portal_reftype = b.portal_reftype
AND a.portal_objname = b.portal_objname
AND c.classid = b.portal_permname
AND a.portal_uri_seg1 <> ' '
AND a.portal_uri_seg2 <> ' '
AND a.portal_uri_seg3 <> ' '
AND c.classid = :permissionlist
AND a.portal_name = :portalname
ORDER BY portal_label;



FRMT = Frame Template

HPGC = Pagelet

HPGT = Homepage Tab

HTMT = HTML template

LINK = Content Reference Link

3. Page Access By Permission List:

SELECT b.menuname, b.barname, b.baritemname, b.pnlitemname AS pagename,
       c.pageaccessdescr,
       DECODE (b.displayonly, 0, 'No', 1, 'Yes') AS displayonly
  FROM psclassdefn a, psauthitem b, pspgeaccessdesc c
WHERE a.classid = b.classid
   AND a.classid = :1
   AND b.baritemname > ' '
   AND b.authorizedactions = c.authorizedactions;

4. PeopleTools Accessed By a Permission List:
SELECT DISTINCT b.menuname
FROM psclassdefn a, psauthitem b
WHERE a.classid = b.classid
AND (   b.menuname = 'CLIENTPROCESS'
OR b.menuname = 'DATA_MOVER'
OR b.menuname = 'IMPORT_MANAGER'
OR b.menuname = 'APPLICATION_DESIGNER'
OR b.menuname = 'OBJECT_SECURITY'
OR b.menuname = 'QUERY'
)
AND a.classid = :PermissionList;

5. Roles Assigned to a Permission List:
SELECT b.rolename, b.classid AS permission_list
FROM psclassdefn a, psroleclass b
WHERE a.classid = b.classid AND a.classid = :permissionlist;
6. User IDs assigned to a Permission List:
SELECT   c.roleuser AS USER_IDs
FROM psclassdefn a, psroleclass b, psroleuser c
WHERE a.classid = b.classid
AND b.rolename = c.rolename
AND a.classid = :permissionlist
GROUP BY c.roleuser;


Tuesday, July 5, 2011

Schedule Application Engine through Peoplecode

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

Code to Schedule App Engine thru PeopleCode:

Local ProcessRequest &MYRQST;

&MyAppName = "MY_APP";
&MYRQST = CreateProcessRequest("Application Engine", &MyAppName);
&MYRQST.RunControlID = "TEST";
&MYRQST.RunLocation = "PSUNX";
&MYRQST.Schedule();

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

End-If;
Code to know whether the scheduled App Engine ran to success or not:
If &MYRQST.Status = 0 Then /* if Schedule status is success */

&LOOP = 0;
While &LOOP = 0
SQLExec("SELECT A.DISTSTATUS, A.RUNSTATUSDESCR FROM PS_PMN_PRCSLIST A WHERE A.PRCSNAME = :1 AND A.PRCSINSTANCE = (SELECT MAX(B.PRCSINSTANCE) FROM PS_PMN_PRCSLIST B WHERE B.PRCSNAME = A.PRCSNAME)", &MyAppName, &POSTED, &STATUS);

If &STATUS = "Success" And
&POSTED = 5 Then /* Posted */
&LOOP = 1
End-If;

If &STATUS = "Success" And /* Not Posted */
&POSTED = 4 Then
&LOOP = 2
End-If;

If &STATUS = "No Success" Or
&STATUS = "Error" Then
&LOOP = 3;
End-If;

End-While;

If &LOOP = 1 Then
MessageBox(0, "", 27333, 594, ""); /* Success Msg */
Else
If &LOOP = 2 Or
&LOOP = 3 Then
MessageBox(0, "", 27333, 595, ""); /* Failed Error Msg */
End-If;
End-If; /* If &LOOP = 1 Then */
End-If;
 
The below function can also be used for schedule application engine: (Alternative Function)
Function ScheduleSetupAEProcess(&RESULT_SET_COMP)
&RUN_SETUP_AE_REQST = CreateProcessRequest("Application Engine", "PTLT_SETUP");
&REC_IMPL_RUN = CreateRecord(Record.PTLT_PROJ_RUN);

/* Run Control ID = Configuration Set Name */
&RUN_CNTL_ID = &RESULT_SET_COMP;

&RUN_SETUP_AE_REQST.RunControlID = &RUN_CNTL_ID;
&RUN_SETUP_AE_REQST.Schedule();

If &RUN_SETUP_AE_REQST.Status = 0 Then
&PROCESS_INSTANCE = &RUN_SETUP_AE_REQST.ProcessInstance;
/* Insert into Setup Manager Run Control Record */
&REC_IMPL_RUN.OPRID.Value = %OperatorId;
&REC_IMPL_RUN.RUN_CNTL_ID.Value = &RUN_CNTL_ID;
&REC_IMPL_RUN.PTLT_PROJ_NAME.Value = &RESULT_SET_COMP;
&REC_IMPL_RUN.PROCESSINSTANCE.Value = &PROCESS_INSTANCE;

If Not &REC_IMPL_RUN.Update() Then
&REC_IMPL_RUN.Insert();
End-If;
Else
Error MsgGet(218, 154, "Process cannot be scheduled");
End-If;
End-Function;





Saturday, July 2, 2011

Object–Based PeopleCode

 

What is Object – Based PeopleCode:

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

Why do we need Object Based PeopleCode:

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

Advantage of Object and Methods:

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

Object – Based PeopleCode Rule:

DECLARE the object
INSTANTIATE the object

Object–Based Code to avoid use of SQLEXEC

 

Below code can be used to avoid use of SQLExec.

SQLExec Code:

SQLExec to copy a row from ORD_HDR to ORD_HDR_EXT.

&ORD_NO = &NEW_ORD_NO;

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

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

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

Same logic using Object Based code:

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

Wednesday, June 8, 2011

Tuxedo 10gR3_VS2005 Installation for PeopleSoft.

While installing Tuxedo 10gR3_VS2005 oh higher you may get error because NLSPATH and TUXDIR registry may not be created properly.

So follow the below steps:

Step 1. Create a folder to install Tuxedo and specify the path in the registry as mentioned below.

image

Step 2. Add the registry content as mentioned below.

image

Add String value.

image

Then Edit String as follows.

image

image

Step 3. Run the Tuxedo setup.

image

image

image

Friday, May 27, 2011

Hiding Delivered Buttons based on condition

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

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

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

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

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

function appsDisablePTControls() {

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

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

var aControls, oTableNode;

var bFound = false;

var i=0;

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

{

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

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

{

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

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

{

if (bButtons)

oTableNode = oTableNode.parentNode;

oTableNode.style.display='none';

bFound = true;

}

}

i++;

}
}
addLoadEvent(function() {
%bind(:1)
});
</script>
Then assign the following code to Page Activated event:
HIDE_WRK.HTMLAREA.Value = GetHTMLText(HTML.HIDE_BT_HTML, "appsDisablePTControls()");
 
Note: Refer the post for how to use Html Area: http://pawan-mundhra.blogspot.com/2011/05/run-javascript-on-your-peoplesoft-pages.html



Friday, May 6, 2011

Run JavaScript on your PeopleSoft pages conditionally

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

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

Steps:

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

1. Create a HTML definition as your javascript template. Include all the necessary user-defined javascript functions that you need. Eg Html Definition is TESTJS
<input type="hidden" name="USERJSINJECTION" value=""/>
<script type="text/javascript">
function addLoadEvent(func) {
var oldonload = window.onload;
if (typeof window.onload != 'function') {
window.onload = func;
} else {
window.onload = function() {
oldonload();
func();
}
}
}

function user_function1() {
window.open("","toolbar = no");
}
function user_function2() {
alert('Hello from user javascript');
}

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

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


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



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


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


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


GetLevel0()(1).TEST_WRK.HTMLAREA.Value = GetHTMLText(HTML.TESTJS, "user_function1()");
 
 
Thanks to http://xtrahot.chili-mango.net/.



Saturday, April 23, 2011

Code to Schedule App Engine through PeopleCode

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

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

Query for PeopleSoft Project items

 

SELECT PROJECTNAME, 
(CASE R.OBJECTTYPE
When 0 then 'Records'
When 1 then 'Indexes'
When 2 then 'Fields'
When 3 then 'Field Formats'
When 4 then 'Translate Values'
When 5 then 'Pages'
When 6 then 'Menus'
When 7 then 'Components'
When 8 then 'Record PeopleCode'
When 9 then 'Menu PeopleCode'
When 10 then 'Queries'
When 11 then 'Tree Structures'
When 12 then 'Trees'
When 13 then 'Access Groups'
When 14 then 'Colors'
When 15 then 'Styles'
When 16 then 'Business Process Maps'
When 17 then 'Business Processes'
When 18 then 'Activities'
When 19 then 'Roles'
When 20 then 'Process Definitions'
When 21 then 'Server Definitions'
When 22 then 'Process Type Definitions'
When 23 then 'Job Definitions'
When 24 then 'Recurrence Definitions'
When 25 then 'Message Catalogue Entries'
When 26 then 'Dimensions'
When 27 then 'Cube Definitions'
When 28 then 'Cube Instance Definitions'
When 29 then 'Business Interlink'
When 30 then 'SQL'
When 31 then 'File Layout Definitions'
When 32 then 'Component Interfaces'
When 33 then 'Application Engine Programs'
When 34 then 'Application Engine Sections'
When 35 then 'Message Nodes'
When 36 then 'Message Channels'
When 37 then 'Messages'
When 38 then 'Approval Rule Sets'
When 39 then 'Message PeopleCode'
When 40 then 'Subscription PeopleCode'
When 41 then 'unused'
When 42 then 'Comp. Interface PeopleCode'
When 43 then 'Application Engine PeopleCode'
When 44 then 'Page PeopleCode'
When 45 then 'Page Field PeopleCode'
When 46 then 'Component PeopleCode'
When 47 then 'Component Record PeopleCode'
When 48 then 'Component Rec Fld PeopleCode'
When 49 then 'Images'
When 50 then 'Style Sheets'
When 51 then 'HTML'
When 52 then 'unused'
When 53 then 'Permission Lists'
When 54 then 'Portal Registry Definitions'
When 55 then 'Portal Registry Structures'
When 56 then 'URL Definitions'
When 57 then 'Application Packages'
When 58 then 'Application Package PeopleCode'
When 59 then 'Portal Registry User Homepages'
When 60 then 'Analytic Types'
When 61 then 'Archive Templates'
When 62 then 'XSLT'
When 63 then 'Portal Registry User Favourites'
When 64 then 'Mobile Pages'
When 65 then 'Relationships'
When 66 then 'CI Property PeopleCode'
When 67 then 'Optimization Models'
When 68 then 'File References'
When 69 then 'File Type Codes'
When 70 then 'Archive Object Definitions'
When 71 then 'Archive Templates (Type 2)'
When 72 then 'Diagnostic Plug-Ins'
When 73 then 'Analytic Models'
When 74 then 'unused'
When 75 then 'Java Portlet User Preferences'
When 76 then 'WSRP Remote Producers'
When 77 then 'WSRP Remote Portlets'
When 78 then 'WSRP Cloned Portlets Handles'
When 79 then 'Services'
When 80 then 'Service Operations'
When 81 then 'Service Operation Handlers'
When 82 then 'Service Operation Versions'
When 83 then 'Service Operation Routings'
When 84 then 'IB Queues'
When 85 then 'XMLP Template Defn'
When 86 then 'XMLP Report Defn'
When 87 then 'XMLP File Defn'
When 88 then 'XMLP Data Src Defn'
Else 'Unknown' END) As ObjectType
, R.OBJECTVALUE1
, R.OBJECTVALUE2
, R.OBJECTVALUE3
, R.OBJECTVALUE4
FROM PSPROJECTITEM R WHERE PROJECTNAME = '<Project Name>'
ORDER BY OBJECTTYPE ;

Sunday, April 10, 2011

PeopleTools 8.5x - Windows - Missing or invalid version of SQL library PSORA (200,0)

Error:
PeopleSoft Application Server fails to start on a Windows system where the PeopleTools version is 8.50. Specifically, the PSAPPSRV process generates an initialization error when booting. The following messages are logged to the APPSRV log:


PSAPPSRV.2692 (0) [01/26/10 14:10:10](0) Cache Directory being used: C:\Documents and Settings\hrtuxedo\psft\pt\8.50\appserv\dmo91\CACHE\PSAPPSRV_2\
PSAPPSRV.2692 (0) [01/26/10 14:10:10](1) GenMessageBox(200, 0, M): PS General SQL Routines: Missing or invalid version of SQL library PSORA (200,0)
PSAPPSRV.2692 (0) [01/26/10 14:10:10](1) GenMessageBox(0, 0, M): Database Signon: Could not sign on to database DMOHR191 with user PS.
PSAPPSRV.2692 (0) [01/26/10 14:10:10](0) Server failed to start
PSADMIN.3116 (0) [01/26/10 14:10:17](0) End boot attempt on domain dmo91
 
 
Cause:
The first logged error message refers to an invalid or missing PSORA library file. This message indicates that the PSAPPSRV process is not able to load the library. It may be that it cannot locate it or that the library located is not compatible with this current version of Tools.

Solution:

PeopleTools 8.5x has been certified to run on 64bit Windows platforms but the program itself is 32bit. This means that any library it needs to load also needs to be 32bit.

The Oracle client lib 'PSORA' for instance, must be the 32bit version rather than the 64bit version.

If you have the 64bit Oracle client installed please uninstall it and install the 32bit version. Also, make sure the 'Add to Path' entry in the psappsrv.cfg file reflects the 32 bit binaries location.

If PSAPPSRV is successfully loading the PSORA library then you will not see this logged message:


GenMessageBox(200, 0, M): PS General SQL Routines: Missing or invalid version of SQL library PSORA (200,0)

Wednesday, April 6, 2011

ORA-12560 "TNS protocol adapter error" (Tns: Protocol Adapter Error Ora-12560 Connecting Via Sqlplus)


Cause:  The ORACLE_SID is not set in the environment

Solution:

To implement the solution, please execute the following steps (for Multiple Oracle Homes Or One oracle Home):
===============================================================
ORACLE_SID variables should only be set in the appropriate registry key that corresponds to the software installation. In this case it should be set back to Original (old) instance name for taking it as default ORACLE_SID.


For 8i and 9i, this would be:
----------------------------
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME(n)


For 10g and 11g, this would be:
-----------------------------------
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ORACLE\KEY_

OR

You may execute the following steps (If only one Oracle Home installed ) to set variables at system level :
======================================================================

1. Go to: My Computer >> right click >> Properties >> Advanced >> Env variables >> System

2. Add new variable

3. Enter ORACLE_SID as the variable name

4. Enter the database sid as the value

Thursday, March 31, 2011

To add embedded help to page controls

 

1.   Open a page definition.

2.   Double-click the scroll area or grid to access the properties dialog box.

3.   Click the Label tab.

4.   In the Embedded Help Popup section, select the Message Catalog Popup option.

5.   Enter the Message Set/Number values of the message that you want to appear in the embedded help pop-up page.

The message should appear in the Text field.

6.   Verify that the message in the Text field is correct.

7.   Click the OK button.

8.   Save the page definition.

9.   In the browser, access the page and test the embedded help

New User Interface features of PeopleTools 8.51

1. Smart Navigation

  • Hierarchical data navigation(e.g Trees) using the menu or Chart Navigation
  • Search results can persisted in menu for easy reuse
  • Related Action (Contextual Popup menus) : Relate additional information to page fields.

2.  Related Content Improvements

    • Support for Level 1,2 and Hidden Fields.
    • Related action content can be shown in Related Content frame

3.  Modal Secondary Page Improvements

    • Facilitates showing a variety of secondary pages in modal popup

4. Embedded help pop-up for Group box, Grid & Scroll Area

    • Help icon beside label for  embedded help

5. WorkCentre (framework)

    • Enable Application teams and customers to build role-based launching pads

6.  Org Chart Improvements

    • Rounded corners on nodes
    • Curved connecting lines
    • Alternative scrolling method
    • Better 3D rendering (openviz)

Monday, March 21, 2011

GenerateComponentContentURL

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

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

Friday, March 18, 2011

PSPCMPROG - Table stores PeopleCode of the objects | Explanation

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

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

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

Monday, March 14, 2011

Changing/Selecting Grid value based on Condition

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

Local Rowset &rsGrid, &rsDataSource;

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

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

 


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

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

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

If All(PER_CHECKLIST.CHECKLIST_CD) Then

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

End-If;

Wednesday, March 9, 2011

Dynamic Prompt / Edittable Prompt

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

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

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

image

Now consider the design.

image

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

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

SELECTION_TYPE

CHAR_VALUE

PROCESS

CHAR_VALUE should have %EDITTABLE Prompt Table Edit Type

image

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

Local string &Type;
Function hide_unhide_popfields(&Row As Row);
&Type = &Row.GetRecord(Record.GR_CRI_TBL).SELECTION_TYPE.Value;
Evaluate &Type
When "D" /* Department */
&Row.GetRecord(Record.DERIVED).EDITTABLE.Value = Record.DEPT_TBL;
Break;
When "J" /* Job Code */
&Row.GetRecord(Record.DERIVED).EDITTABLE.Value = Record.JOBCODE_TBL;
Break;
When "L" /* Location */
&Row.GetRecord(Record.DERIVED).EDITTABLE.Value = Record.LOCATION_TBL;
Break;
End-Evaluate;
End-Function;
Call the above function based on FieldChange and RowInit.
 
Declare Function hide_unhide_popfields PeopleCode GR_CRI_TBL.SELECTION_TYPE FieldFormula;
SetDefault(GR_CRI_TBL.CHAR_VALUE);
hide_unhide_popfields(GetRow());




Thursday, March 3, 2011

Person Model Setup Tables

Person of Interest Type Setup Table

1. POI_TYPE_TBL : This setup table holds the definition of the person of interest types.

Component: POI_TYPE_TBL

Navigation: Set Up HRMS, Foundation Tables, Organization, Person of Interest Type

Note: You can make any of the delivered types inactive ( except the Unknown type) , change the descriptions, and add additional types.

Person Object Installation Setup Table

2. INSTALL_PERSON: This setup table is used define how (or if) the person checklist feature will be used within PERSONAL_DATA and also whether an automatic search/match of possible duplicate people will be done when you save a person.

Note: There will be only one row in INSTALL_PERSON

Component: INSTALL_PERSON

Navigation: Set Up HRMS, Install, Person Object Installation

JOB Actions

3. ACTION_TBL: This setup table contains the actions that are available in the JOB pages.

Component: ACTION_TBL

Navigation: Set Up HRMS, Product Related, Workforce Administration, Actions

Note: ACTION_STAT_TBL - This setup table contains the rules for status and validation of actions that are available in the JOB pages

PERSONAL_DATA Snapshot Reporting Table

4. PERSON_DT_SETUP: This setup table which defines what fields will be maintained in the PERSONAL_DATA snapshot table.

Component: PERSON_DT_SETUP

Navigation: Set Up HRMS , System Administration, Database Processes, PERSONAL_DATA Settings

Note: ACTION_STAT_TBL - This setup table contains the rules for status and validation of actions that are available in the JOB pages.

Tuesday, March 1, 2011

Query To Find All Records under a specified component

Following is the query that can be useful to find out all records under a specified component.

 

SELECT DISTINCT (recname)
FROM psrecdefn
WHERE recname IN
(SELECT DISTINCT (recname)
FROM pspnlfield
WHERE pnlname IN
(SELECT DISTINCT (b.pnlname)
FROM pspnlgroup a, pspnlfield b
WHERE (   a.pnlname = b.pnlname OR a.pnlname =b.subpnlname)
AND a.pnlgrpname = 'Component Name'                 -- specify your component name)
AND recname <> ' ')
UNION
SELECT DISTINCT (recname)
FROM pspnlfield
WHERE pnlname IN
(SELECT DISTINCT (b.subpnlname)
FROM pspnlgroup a,pspnlfield b
WHERE (a.pnlname = b.pnlname OR a.pnlname = b.subpnlname )
AND a.pnlgrpname = 'Component Name')               -- specify your component name)
AND recname <> ' ')
AND rectype = '0'                                                 -- specify record type
order by recname asc

 

Note: 0 is a record type of Sql Table.

Friday, February 25, 2011

Access Log (PSACCESSLOG)

Peoplesoft stores the access log details in PSACCESSLOG table.
This record has following fields:
1. OPRID (Stores OPRID)
2. LOGIPADDRESS (Stores IP Address or System name)
3. LOGINDTTM ( Stores date login time).
4. LOGUOTDTTM (Stores date logout time).
5. PT_SIGNON_TYPE (Stores Signon Type).

Tuesday, February 22, 2011

Creating MS EXCEL Using CreateObject in PeopleSoft

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

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

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

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

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



/*Add data to cells of the first worksheet in the new workbook*/
&oWorkApp = CreateObject("COM", "Excel.Application");
ObjectSetProperty(&oWorkApp, "Visible", True);
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");
&oWorkBook.add();
&oWorkSheet = &oWorkApp.Worksheets("Sheet1");
&oWorkSheet.Range("A1").Value = "Last Name";
&oWorkSheet.Range("B1").Value = "First Name";
&oWorkSheet.Range("A1:B1").Font.Bold = True;
&oWorkSheet.Range("A2").Value = "Doe";
&oWorkSheet.Range("B2").Value = "John";
&oWorkApp.ActiveWorkbook.SaveAs("E:\Personal\dummy\TestXLS.xls");


/*How to read data from one cell and writes to another*/
&oWorkApp = CreateObject("COM", "Excel.Application");
ObjectSetProperty(&oWorkApp, "Visible", True);
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");
&oWorkBook.Open("E:\Personal\dummy\TEST1.xls");
&oWorkSheet = &oWorkApp.Worksheets("Sheet1");
&oData = &oWorkSheet.Range("A1").Value;
&oWorkSheet.Range("A2").Value = &oData;
&oWorkApp.ActiveWorkbook.SaveAs("E:\Personal\dummy\TestXLS.xls");


/*How to read data from one cell and writes to different sheet*/
&oWorkApp = CreateObject("COM", "Excel.Application");
ObjectSetProperty(&oWorkApp, "Visible", True);
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");
&oWorkBook.Open("E:\Personal\dummy\TEST1.xls");
&oWorkSheet = &oWorkApp.Worksheets("Sheet1");
&oData = &oWorkSheet.Range("A1").Value;
&oWorkSheet2 = &oWorkApp.Worksheets(2);
&oWorkSheet2.Range("A1").Value = &oData;
&oWorkApp.ActiveWorkbook.SaveAs("E:\Personal\dummy\TestXLS.xls");
/*Add data to cells of the first worksheet in the new workbook*/
&oWorkApp = CreateObject("COM", "Excel.Application");
ObjectSetProperty(&oWorkApp, "Visible", True);
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");
&oWorkBook.Open("E:\Personal\dummy\TEST1.xls");
&oWorkSheet = &oWorkApp.Worksheets("Sheet1");
&oWorkSheet.Range("A1").Value = "Last Name";
&oWorkSheet.Range("B1").Value = "First Name";
&oWorkSheet.Range("A1:B1").Font.Bold = True;
&oWorkSheet.Range("A2").Value = "Doe";
&oWorkSheet.Range("B2").Value = "John";
&oWorkApp.ActiveWorkbook.SaveAs("E:\Personal\dummy\TestXLS.xls");
 
/*Transfer the data to Excel from Rowset*/
&oWorkApp = CreateObject("COM", "Excel.Application");
ObjectSetProperty(&oWorkApp, "Visible", True);
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");
&oWorkBook.Open("E:\Personal\dummy\TEST1.xls");
&oWorkBook = &oWorkApp.Workbooks.Add();
&rs_Awards = CreateRowset(Record.PERTBL);
&rs_Awards.Fill("WHERE FILL.YEAR = '2008' AND FILL.STATUS = 'C'");
&oWorkSheet = &oWorkApp.Worksheets("Sheet1");
For &ie = 1 To &rs_Awards.activerowcount
&oWorkSheet.Cells(&ie, 1).Value = &rs_Awards.getrow(&ie).PERTBL.ID.Value;
&oWorkSheet.Cells(&ie, 2).Value = &rs_Awards.getrow(&ie).PERTBL.COMP.Value;
End-For;
&oWorkApp.ActiveWorkbook.SaveAs("E:\Personal\dummy\TestXLS.xls");

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