Archive

Archive for the ‘PT’ Category

When was a query last executed?

August 22, 2008 2 comments

PSQRYSTATS table tells you about when was a particular query last executed and more details. This PSQRYSTATS table has following fileds.

OPRID
QRYNAME
EXECCOUNT  – Count of Query Execution
AVGEXECTIME  – Average Query Execution Time
AVGFETCHTIME – Average Fetch Time
LASTEXECDTTM – DateTime of Last Execution
AVGNUMROWS – Average Number of Rows Fetched
NUMKILLS – Number of Times Killed

**********************

PSQRYDEFN has a field QRYTYPE. It is a number field and It’s values are listed below.
7  – ARCHIVE
5  – PROCESS
4  – ROLEQRY
1  – QUERY

Categories: PT Tags: ,

Portal Content LOCAL_NODE

August 13, 2008 Leave a comment

On the Portal > Structure and Content, I opened a Content Ref Administration Page, on the General page, I observed Node Name as LOCAL_NODE. I clicked on the prompt for the field, it did show a LOCAL_NODE as valid value and in addition it showed this node as local and default node along with PT_LOCAL as default and local node. I was surprised as there cannot be two local and default nodes in a PIA. So I opened the component PORTAL_CREF_ADM and in that I opened the page PORTAL_CREF_ADM. Node Name field MSGNODENAME was from a derived work record PORTAL_CREF_ADM, I checked the prompt view for MSGNODENAME field, it was dynamic view PORTAL_NODE_VW, the SQL for this view is given below.

SELECT FILL.MSGNODENAME
 , FILL.DESCR
 , FILL.LOCALNODE
 , FILL.LOCALDEFAULTFLG
 , FILL.NODE_TYPE
  FROM PSMSGNODEDEFN FILL
  UNION
 SELECT ‘LOCAL_NODE’
 , ‘ ‘
 , 1
 , ‘Y’
 , ‘PIA’
  FROM PSMSGNODEDEFN FILL

The LOCAL_NODE I saw on structure and content page was a dummy node.

People Tools version 8.49

Categories: PT Tags:

Record Audit

December 7, 2007 24 comments

PeopleTools ver 8.45 

To audit an already exisiting PeopleSoft record, we need to create a record definition and SQL table in which we store audit information. When creating the audit record, remove any attributes such as
PARENT records, Query Security Records, and PeopleCode. The easiest way to create an audit table is to open the record definition of the base record that you wish to audit. Save it as a new record, prefaced with AUDIT_.

Remove the all edit and key attributes from the newly saved record. Also remove any attributes such as
PARENT records, Query Security Records, and PeopleCode. Add to the top of the audit record the following three special audit-specific fields in the same order given below.

  • AUDIT_OPRID
  • AUDIT_STAMP
  • AUDIT_ACTN
  • AUDIT_RECNAME

In most cases you should include AUDIT_OPRID, AUDIT_STAMP, AUDIT_ACTN. The AUDIT_STAMP must be given the attribute AUTOUPDATE. You might also add AUDIT_RECNAME if you are creating an audit table to audit more than one record definition.

Make these fields required and keys. Then build the table. Make sure you can query this table using sql editor.

The purpose of each audit specific field is explained below.

AUDIT_OPRID – Identifies the user who caused the system to trigger the audits—either by performing an add, change, or delete to an audited field.
AUDIT_STAMP – Identifies the date and time the audit was triggered.
AUDIT_ACTN  – Indicates the type of action that the system audited. Possible actions include:

  • A: Row inserted.
  • D: Row deleted.
  • C: Row changed (updated), but no key fields changed. The system writes old values to the audit table.
  • K: Row changed (updated), and at least one key field changed. The system writes old values to the audit table.
  • N: Row changed (updated), and at least one key field changed. The system writes new values to the audit table.

AUDIT_RECNAME –  Identifies the name of the record definition that was audited.

The audit table does not have to include all the columns of the base table. In fact, for performance reasons, it’s best to only include those fields in your audit record that are deemed

Open the record properties for the record you want to audit, Under the Record Audit, we have the following options

Record Name – Specify the user-defined audit record.

Audit Options – following are the audit options to choose for auditing the record.

  • Add –  Inserts an audit table row whenever a new row is added to the table underlying this record definition.
  • Change –  Inserts one or two audit table rows whenever a row is changed on the table underlying this record definition.
  • Selective –  Inserts one or two audit table rows whenever a field that is also included in the record definition for the audit table is changed.
  • Delete –  Inserts an audit table row whenever a row is deleted from the table underlying this record definition.

Now perform online transactions on the audited table, query the audit table to know what is changed and who changed it at what time.

Categories: PT, Security Tags: