How to Default sysdate (Current Date) for Crystal Report and PeopleTools Query prompt automatically

If you have a Query or Crystal Report, that has a date prompt and you want to schedule the report daily, so that date value is defaulted to sysdate (Current date), you can do this as follows.

Open the Query in PeopleTools Query tool.

Go to Criteria tab. Right Click on Expression2 Column and Select Expression or Expr-Expr if you are using between operator.

Type the following in Edit Expression.

Type	Default Date	Expression
Criteria	current date	decode(:1,TO_DATE('1900-01-01','YYYY-MM-DD'),trunc(sysdate),:1)
	current date - 1	decode(:1,TO_DATE('1900-01-01','YYYY-MM-DD'),trunc(sysdate)-1,:1)
	current date + 1	decode(:1,TO_DATE('1900-01-01','YYYY-MM-DD'),trunc(sysdate)+1,:1)

 

replace :1 with the actual prompt value. You must first create this prompt.

Now you need to pass 01/01/1900 as an input parameter if you want to run the query for current date. Passing any other values will make the query run for that date.

This way you can achieve both i.e. run the query for a user selected date or run a query for current date which can be used to schedule. Note : you can use any date as a replacement for sysdate and not just 01/01/1900.

Also you can default it to any day relative to current date for e.g. trunc(sysdate) - 1 or trunc(sysdate) + 1 etc.

If you want to know what parameter user has passed in your report, you can add the following expression as field in your query.

Create an expression of type Date in left hand side Under Expressions.

Type	Default Date	Expression
Field	Current Date	decode(:1,'1900-01-01',to_char(trunc(sysdate),'YYYY-MM-DD') ,:1)
	Current Date - 1	decode(:1,'1900-01-01',to_char((trunc(sysdate)-1),'YYYY-MM-DD') ,:1)
	Current Date + 1	decode(:1,'1900-01-01',to_char((trunc(sysdate)+1),'YYYY-MM-DD') ,:1)

I have tested this on Oracle 9.2.0.8 and PeopleTools 8.48.12.

ORA-00022: invalid session ID; access denied

If you are getting this error, here is a quick fix.

Set the following value in psappsrv.cfg (appserver) and psprcs.cfg (batch server) for all the configured appservers and batch servers. You may have to reconfigure the appserver and batch servers and restart them.

DbFlags=8

Default value is DbFlags=0 which means use Persistent Secondary DB Connection.

Setting DbFlags=4 is not recommended by PeopleSoft, which completely disables the secondary database connection.

Setting it to 8 disables Persistent Secondary DB Connection, but it still uses on demand Secondary DB Connection for each request. This is required for using GetNextNumberWithGapsCommit (GNNWGC) function, which is internally used by PeopleSoft for workflow transactions to generate APPR_INSTANCE

If you do not do this, you may get row inserted in PS_APPR_INST_LOG with

APPR_INSTANCE = 0

which may cause, some undesired workflow routings.

The easiest solution I have found is

delete from ps_appr_inst_log where APPR_INSTANCE = 0

Please make sure that you backup the data and test it.

Here is some more information on GetNextNumberWithGapsCommit (GNNWGC) function from peoplebooks.

Use this function instead of the GetNextNumberWithGaps function. The GetNextNumberWithGaps function is very restrictive in its usage. The GetNextNumberWithGapsCommit function can be used in any event. The sequence number (record.field ) is incremented right away and it doesn't hold any database internal row lock beyond the execution of this function.

Note. A secondary database connection is used to increment and retrieve record.field. The default behavior is to keep the secondary database connection persistent in order to improve performance for the next GetNextNumberWithGapsCommit usage. If the database administrator finds the persistent connection too high an overhead for the production environment (which should not be the case since PeopleSoft uses application server to mulitplex the database connection), the database administrator can change the default behavior to use an on-demand connection method. The persistent second connection is disabled using DbFlags bit eight in the application server and process scheduler configuration files. The second connection can be completely disabled using DbFlags bit four in the application server and process scheduler configuration files

This issue may be happening in Tools 8.45 and higher (8.46, 8.47, 8.48, 8.49 etc.), as secondary connection is introduced in 8.45.

See Peoplesoft Customer connection Resolutions for  more information.

Resolution : 201049233 - E-ORACLE:10g Master Performance Solution for Oracle 10g 

In this resolution, PeopleSoft generally recommends to Set DbFlags=8. I hope thay deliver this value by default in future peopletools releases.

Resolution : 201049902 - E-PC: Sporadic ORA-00022 Errors in AE w/ GetNextNumberWithGapsCommit PCode

Resolution : 201022463 - E-NV nVision reports that are run on Client Machine fail - Error message Invalid Cursor Number

Resolution : 201015931 - E-SEC: SQL looking for inactive roles causes slow logins for users

Resolution : 201023068 - E-WF:Deadlocks whenever a high load of transactions create worklists 

Resolution : 201024183 - E-NV Running nVision via Define Report Book gives 8055 cursor entry error

Resolution : 200987809 - E-NV: nVision reports are queued when running in Windows 3 tier mode

Note : All the discussion is surrounding oracle database only. I found that it is disabled automatically for informix. For all other databases you may need to test the functionality your self.

Alternative to SendMail Function : PT_MCF_MAIL

You can use Application package PT_MCF_MAIL (PeopleTools 8.46 or higher only) to generate emails instead of using SendMail peoplecode function.

Go to the PeopleBooks to get the complete description.

Home > PeopleBooks > Enterprise PeopleTools 8.48 PeopleBook: PeopleCode API Reference > Mail Classes

Go to Mail Classed Example section to see How to use these classes. Following examples are provided.

• Creating Text Email
• Creating Email and Overriding SMTP Settings
• Creating HTML Email
• Creating Multi-Part Email With Both Text and HTML Parts
• Creating HTML Email with Images
• Creating Email with Attachments
• Creating Email Attachments Specifying a URL
• Creating Multiple Emails
• Authenticating Email While Sending

Advantages

• Structured Code
• Can generate HTML Email
• Can override Default SMTP Parameters
• Better Error Handling
• Send emails  return receipt request (email reception notification)
• Ability to set Message priority

 

Reference

• Rich emails using SendMail()

 

Issues

•   You might not be able to use GetHTMLText in Application Engine program run using process scheduler or in 2 tier mode, as this function does not support bind variables in 2 tier mode. PeopleBooks list the following constraints for this function.
  
  Restrictions on Use

Use this function with the PeopleSoft Pure Internet Architecture. If run from a 2 tier environment, the parameter substitution does not take place.

Oracle Openworld 2007 PeopleSoft Conference Sessions

You can download pdf file of the conference presentations for PeopleSoft at the following link.

PeopleSoft Sessions - Oracle Openworld 2007

Microsoft Oracle and PeopleSoft White papers

This site provides the white papers specifically targeting Oracle PeopleSoft Enterprise Customers using Microsoft technologies for e.g. Share Point server, SQL Server. It also offers tips and techniques and provided a step by step instruction for integrating share point portal with PeopleSoft using web services.

Microsoft - Oracle - PeopleSoft white papers

Following are some of the interesting white papers that customers can take advantage of.

• Connecting Microsoft Office InfoPath 2003 to PeopleSoft
• Connecting PeopleSoft and Microsoft Excel using the Information Bridge Framework
• Integrating Microsoft Office SharePoint Portal Server 2007 with PeopleSoft Enterprise Applications
• Peoplesoft Performance Tuning on Microsoft SQL Server
• PeopleSoft Red Paper: Microsoft SQL Server Tuning Tips for PeopleSoft 8.x
• Connecting to PeopleSoft Web Services with Microsoft Visual Studio .NET
• Connecting Microsoft Office InfoPath 2003 to Peoplesoft

 

You may also want to look at following Oracle Open World 2007 Session PDF file.

Integrating PeopleSoft Applications with Microsoft Oracle. (Steve Fox from Microsoft presented the session).

He demonstrated how to use Microsoft Visual studio 2008 Office programming to connect outlook with PeopleSoft back end data. Demo showed a form in outlook that displayed recruitment information from PeopleSoft in outlook.

Determine Languages installed for your PeopleSoft Application.

You can use this SQL to find out which languages are already installed for your installation.

select * from PSLANGUAGES where installed = 1

Find if the current user has been assigned the named role

You can use this in your peoplecode to determine if the current logged in user has role assigned to him.

&IND = %Roles.Find("MYROLENAME");

IF &IND <> 0 Then

     /* User has this Role */

or 

   /* User does not have this Role */

end-if

You can also use the peoplecode function IsUserInRole("ROLENAME") to find the same.

Export to PDF Fix for Crystal Report 8.5

If you try to run Export to PDF from Crystal Report 8.5 Report designer, it does not work. To resolve this, download this hot fix from businessobject web site and install it. Click here to get list of all downloads available for 8.5

Crystal Report Tip: Know the height and width of a object

In Crystal Report, you can right click on any field or object and select Object Size and Position.

This can help you align two objects horizontally or vertically using same x and y coordinate and also allow you to set the same height and width. Very useful, when you need to delete one field and add another from different source. Just note down the all the values before deleting and set the values after adding the new object.

How to link Sub Report to Main Crystal Report

Sub reports allows to use another query as a data source, different from the main query. You can have as many subreports as you want. Sub reports can not have another sub report inside it. One of the challenge is to link the sub report parameters with main report, so that sub report query is executed for the every row retrieved from the main report. This is easy.

I will explain this using a Sample. Let's say we are designing a report that prints user profile information. The main query is based on psoprdefn and is used in the main report. The Sub report is based on psroleuser and is used in the subreport to print role names for each user selected in the main query.

Click Insert -> Sub Report

Select Create a Sub Report and Type in PSROLEUSER and click on Report Expert.

Click on Database and Select the Query name. Complete the rest of steps by clicking on Next. Once you return to main Insert Subreport dialog box, Select Link tab. You can also change links for sub report later on by right clicking on sub report and selecting Change Subreport links.

Select Oprid from Available Fields and Click on > button to move it to Fields(s) to link To.

Now Click on Subreport Parameter fields to use:

Click on tiny Scroll bar, (Note: Do not use ?Pm-GF_C_PSOPRDEFN.OPRID as your parameter to link to).

Once you see the parameter field for the sub query i.e. user id select it.

Note: Both main query and subquery has a common parameter i.e. oprid or Userid. We need to match the Oprid field from main query and assign it to Parameter field of the sub query and do not use autogenerated Crystal Report parameter. There may be other ways to achieve the same results, but i found this works best.

Once you insert the subreport to main report, Right click on it and select border and colors. Set Line style to none for Left, Right, Top and Bottom. Right click on Subreport and Select Edit SubReport. You may want to Delete Report Header and Report footer in the subreport and arrange the fields and add any additional information you need. Also delete the Print Date special field.

Note: This is based on Crystal Report 8.5

Crystal Report 8.5 and SubReports

If you have a linked sub report in Main Crystal Report, every time you open the main report, it re imports the sub report if it founds the sub report in the same location. This is despite the fact that Re-import Subreports when Opening Reports check box is unchecked.

This seems to me a bug. Similar bug in Crystal 10 has been reported on businessobject support web site.

Clearing the 'Re-import Subreport on Open' check box does not work (c2017466)

This creates a annoying behavior, as Crystal report will remove the links between main report and sub report. Only workaround I have found is to rename the sub reports or move them to other folders, once they are imported in main report. If Crystal Report does not find the sub report in the original location, it does not re import the sub report.

If any body has encountered the similar issues, please post it in the comments section.

Determine Hidden Folders and Content References for Portal Navigation

If you do not see a folder or content Reference (Menu Item) in Left hand side navigation, (Applicable to 8.4x ) then you must check to see if the folder or content reference is not marked as hidden. Other reasons could be security. Here is a SQL to find out all the objects that are hidden.

To find all the folders which are hidden from Portal Navigation.

select * from PSPRSMSYSATTRVL where portal_name = 'EMPLOYEE' and PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and portal_Reftype = 'F'

select a.portal_objname,b.portal_label,b.portal_prntobjname,c.portal_label
from PSPRSMSYSATTRVL a,psprsmdefn b,psprsmdefn c where a.portal_name = 'EMPLOYEE'
and a.PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV'
and b.portal_Reftype = 'F' and a.portal_name = b.portal_name
and a.portal_objname = b.portal_objname
and b.portal_name = c.portal_name
and b.portal_prntobjname = c.portal_objname

To find all the content references which are hidden from Portal Navigation.

select * from PSPRSMSYSATTRVL where portal_name = 'EMPLOYEE' and PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and portal_Reftype = 'C'

select a.portal_objname,b.portal_label,b.portal_prntobjname,c.portal_label
from PSPRSMSYSATTRVL a,psprsmdefn b,psprsmdefn c where a.portal_name = 'EMPLOYEE'
and a.PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV'
and b.portal_Reftype = 'C' and a.portal_name = b.portal_name
and a.portal_objname = b.portal_objname
and b.portal_name = c.portal_name
and b.portal_prntobjname = c.portal_objname

 

Replace EMPLOYEE with Your portal name. Some of the Other names are.

CUSTOMER
DEMOSITE
EMPLOYEE
MOBILE
PORTAL
PS_SITETEMPLATE
SUPPLIER

Portal Content Reference/folder Attributes are stored in

PSPRSMSYSATTR

PSPRSMSYSATTRVL

Online Performance Configuration Guidelines for PeopleTools 8.45, 8.46 and 8.47, 8.48

You should carefully review and implement ther performance guidelines to get Optimal performance from your PeopleSoft Application. This document is posted on Customer connection and you will need a loginid and password to access it. Here is the direct link.

Online Performance Configuration Guidelines for PeopleTools 8.45, 8.46 and 8.47, 8.48

Troubleshoot Workflow EMAIL (SMTP Server) issues.

Many time End users get an Error message : Unable to Send Email, Invalid ID, while saving the page. This usually means some kind of issue while sending email from your appserver or batchserver using the SMTP Server. The error message is not correct and to get the exact reason, why send email is failing, you need to add/Update the following in your appserver configuration (PSAPPSERV.CFG) or batch server Configuration (PSPRCS.CFG) under SMTP Settings.

SMTPTrace=1

1 means enabled, 0 - Disabled. This setting is sometimes not present in PSPRCS.CFG. You have to add it manually opening the file. This setting is dynamic and does not require reboot of the Server, which is very good for troubleshooting in production environments.

Once turned on, it will generate Trace file SMTP.LOG in LOGS Folder under $PS_HOME/tools/appserv/DOMAIN for Appserver and $PS_HOME/tools/appserv/prcs/DOMAIN for Batchserver.

Open SMTP.LOG and fix the issues logged in it. This will make your users to save the page.

Login to Two Tier using your Enterprise LDAP ID.

Normally, If you use LDAP Authentication for logging into your PeopleSoft application, you can not use the same id to login in Two Tier. This will force you to have multiple ids as you can do following in only two tier mode.

• Data Mover access
• Run Compare Reports
• Build SQL Tables

This is not very convenient and not a secure approach. PeopleSoft  SOLUTION ID 200735608 - E-LDAP: LDAP Authentication does not work in 2-tier describes this behavior.

I have found this undocumented Trick to use the same LDAP ID to login to App Designer in Two tier mode. I have tested this in PeopleTools 8.43 only and it may not work in higher tools release. (Let me know in comments) Note: This trick does not bypass peoplesoft security. If you do not have access to underlying components or menu items (for e.g. DATA MOVER Or Run compare reports or Build SQL), you won't be able to perform this.

Here are the Steps.

1. Login to App Designer in Three Tier. If you get an error message (You cannot sign on because the password for this user (PSOPRDEFN.OPERPSWD) isn't encrypted. Run encrypt_password * in Datamover, or change the password in Maintain Security.) , Press OK to continue and you will be logged in App Designer. The above message is a warning only and it does not prevent you from signon. This is identified as a known issue and is fixed in PeopleTools 8.46. See SOLUTION ID 200776124 - E-LDAP: Cannot logon 3 tier with LDAP user as ENCRYPTED field on PSOPRDEFN is set to 0
2. Now Login to App Designer by running this from Start -> Run or create a shortcut on the desktop.
   
   <path to pside>pside.exe -CT ORACLE -CD dbname -CO userid -CI people -SUBSEQUENT -MN"APPLICATION_DESIGNER"
   
   Replace ORACLE With your DBType, userid with your LDAP Userid, people with your connectid if different. Note you do not need password as you are already logged in.
3. You can run Data Mover from Go -> Data Mover Command once you are logged in 2 Tier mode.

Use Dynamic SQL for Prompts - SqlText

Normally you can not use dynamically generated SQL at runtime as your prompt record. You can use %EDITTABLE to specify the prompt table you want to use, but it has to be predefined. There is this little known property SqlText for Field Class, which allows you to do exactly that.

Here is the description and sample code taken from peoplebooks.

This property is valid only for fields that have a dynamic view as their prompt record. If you set SqlText to a non-null value, that text is used instead of the dynamic view's normal text used for prompting.

Suppose you wanted to have a different prompt table depending on the settings of other fields in the row. Normally you could use %EDITTABLE to dynamically specify the prompt table you want. However in this case there are too many possible combinations of values, which would require too many views. Furthermore, the values are customizable by the end-user or the application, which means even if you, the developer, wanted to, you couldn't provide all the combinations of views necessary. However you can generate the desired SQL text for the view in PeopleCode based on what the user enters.

If you use a dynamic view as the prompt table, and have the dynamic view contain a SQL object that is updated from PeopleCode, you could achieve this functionality. However, a SQL object is a shared object, so if multiple users used the same page, they overwrite each other's settings and the SQL object contains the SQL for the most recent user. Similarly if a single user had multiple rows on a page, the SQL object is valid only for the most recent row. This means if the user went to another row and did a prompt, they would get the wrong values again.

The purpose of this property is to enable you to specify the generated SQL text independently for each occurrence in each transaction. It enables you to override the text of a dynamic view being used as a prompt table on a field by field basis.

It is up to the developer to verify that the text specified for this property is valid, that is, that it selects the correct number of fields for the record definition, and so on.

This property is read-write.

Local string &SQLSTRING;

Function set_jrnl_id_prompt();
&SQLSTRING = "SELECT DISTINCT JOURNAL_ID, BUSINESS_UNIT_IU, JOURNAL_DATE, LEDGER_GROUP, SOURCE, SYSTEM_SOURCE, PROC_PART_ID, JRNL_HDR_STATUS, DESCR FROM PS_JRNL_HEADER WHERE JRNL_HDR_STATUS IN ('N','E','V')"
If All(JRNL_EDIT_REQ.BUSINESS_UNIT) Then
&SQLSTRING = &SQLSTRING | " AND BUSINESS_UNIT_IU='" | JRNL_EDIT_REQ.BUSINESS_UNIT | "'"
End-If;
If All(JRNL_EDIT_REQ.LEDGER_GROUP) Then
&SQLSTRING = &SQLSTRING | " AND LEDGER_GROUP='" | JRNL_EDIT_REQ.LEDGER_GROUP | "'"
End-If;
If All(JRNL_EDIT_REQ.SOURCE) Then
&SQLSTRING = &SQLSTRING | " AND SOURCE='" | JRNL_EDIT_REQ.SOURCE | "'"
End-If;
If All(JRNL_EDIT_REQ.SYSTEM_SOURCE) Then
&SQLSTRING = &SQLSTRING | " AND SYSTEM_SOURCE='" | JRNL_EDIT_REQ.SYSTEM_SOURCE | "'"
End-If;
If All(JRNL_EDIT_REQ.PROC_PART_ID) Then
&SQLSTRING = &SQLSTRING | " AND PROC_PART_ID='" | JRNL_EDIT_REQ.PROC_PART_ID | "'"
End-If;
GetRecord().GetField(Field.JOURNAL_ID_FROM).SqlText = &SQLSTRING;
GetRecord().GetField(Field.JOURNAL_ID_TO).SqlText = &SQLSTRING;
End-Function;