How to Trace your APP Engine

AE Process Definition - Override options tab

-TRACE 7 -TOOLSTRACEPC 3596 -TOOLSTRACESQL 131 (review peoplebooks on which tracing values you need to place)

PSACCESLOG - Review users who login

Here are some queries to detail who has logged into the Peoplesoft system (Web Tier)


-- Unique IP Address Count, per hour, for the last 24 hours
-- Change the "1" to be "7" for seven days of activity
select DTTM, count(*)
from (select distinct a.logipaddress "IP",
to_char(a.logindttm,'MM/DD/YYYY HH24') "DTTM"
from psaccesslog a
where a.logindttm > sysdate - 1)
group by DTTM;


-- Find the IP Activity for a individual, for the last 24 hours
-- Change the "1" to be "7" for seven days of activity
select a.*, b.oprdefndesc from psaccesslog a, psoprdefn b
where a.logindttm > sysdate - 7 -- This indicates today and the past seven days...
and a.oprid = b.oprid
and b.oprdefndesc like '%Smith%'
ORDER BY A.LOGINDTTM DESC;

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

I found a great blog entry about defaulting query prompts from Keton K. I wanted to post on here as well.

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.

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.

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)

Objects within Peoplesoft Projects

This SQL was given to me by a great colleague - Jan Jerabek. This SQL will pull all the objects out of your project for cut and paste efforts :-)

select decode(OBJECTTYPE, '0','Record', '1','Index', '2','Field', '3','Field Format', '4','Translate Value', '5','Pages', '6','Menus', '7','Components', '8','PeopleCode Record PeopleCode', '9','PeopleCode Menu PeopleCode', '10','Query', '11','Tree Structures', '12','Trees', '13','Access group', '14','Color', '15','Style', '16','Not used', '17','Business process', '18','Activity', '19','Role', '20','Process Definition', '21','Server Definition', '22','Process Type Definition', '23','Job Definitions', '24','Recurrence Definition', '25','Message Catalog Entries', '26','Dimension', '27','Cube Definitions', '28','Cube Instance Definitions', '29','Business Interlink', '30','SQL', '31','File Layout Definition', '32','Component Interfaces', '33','Application Engine program', '34','Application Engine section', '35','Message Node', '36','Message Channel', '37','Message', '38','Approval rule set', '39','PeopleCode Message PeopleCode', '40','PeopleCode Subscription PeopleCode', '41','Not Used', '42','PeopleCode Component Interface PeopleCode', '43','PeopleCode Application Engine PeopleCode', '44','PeopleCode Page PeopleCode', '45','PeopleCode Page Field PeopleCode', '46','PeopleCode Component PeopleCode', '47','PeopleCode Component Record PeopleCode', '48','PeopleCode Component Rec Fld PeopleCode', '49','Image', '50','Style sheet', '51','HTML', '52','Not used', '53','Permission List', '54','Portal Registry Definitions', '55','Portal Registry Structures', '56', 'URL Definitions', '57', 'Application Packages', '58', 'PeopleCode Application Package Peoplecode', '59', 'Portal Registry User Homepage', '60', 'Problem Type', '61', 'Archive Templates', '62', 'XSLT', '63', 'Portal Registry User Favorite', '64', 'Mobile Page', '65', 'Relationships', '66', 'PeopleCode Component Interface Property Peoplecode', '67', 'Optimization Models', '68', 'File References', '69', 'File Type Codes', '70', 'Archive Object Definitions', '71', 'Archive Templates (Type 2)', '72', 'Diagnostic Plug In', '73', 'Analytic Model','UNKNOWN','80','Service Operations' ,'81' ,'Service Operation Handlers','82','Service Operation Versions','83','Service Operation Routings',
'84','IB Queues','85','XMLP Template Definition','86',
'XMLP Report Definition' ,'87','XMLP File Definition','88',
'XMLP Data Source Definition') OBJECTTYPE, '', OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3, OBJECTVALUE4 from PSPROJECTITEM where PROJECTNAME = 'Project name goes here' order by objecttype, OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3

Years of Service / Salary Query - Reports_to

Here is a sql/report that I needed to write to track years of service and salary info. (Set the values in red)


select distinct a.reports_to, a.emplid, b.name, (a.comprate * 26), a.grade, c.min_rt_annual, c.mid_rt_annual, c.max_rt_annual, a.sal_admin_plan ,E.HIRE_DT,
TO_CHAR(((SYSDATE - P.BIRTHDATE) / 365), 99) AGE
,TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) TOTAL_YEARS_WORKED
,TO_CHAR((SYSDATE - E.HIRE_DT),999999) TOTAL_DAYS_WORKED
,(CASE WHEN TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) >= 5 THEN '5 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - E.HIRE_DT) - 1825) * -1), 99999) END) TOTAL_DAYS_UNTIL_5_YRS_SERV
,(CASE WHEN TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) >= 10 THEN '10 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - E.HIRE_DT) - (1825 * 2)) * -1), 999999) END) TOTAL_DAYS_UNTIL_10_YRS_SERV
,(CASE WHEN TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) >= 15 THEN '15 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - E.HIRE_DT) - (1825 * 3)) * -1), 999999) END) TOTAL_DAYS_UNTIL_15_YRS_SERV
,(CASE WHEN TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) >= 20 THEN '20 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - E.HIRE_DT) - (1825 * 4)) * -1), 999999) END) TOTAL_DAYS_UNTIL_20_YRS_SERV
,(CASE WHEN TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) >= 25 THEN '25 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - E.HIRE_DT) - (1825 * 5)) * -1), 999999) END) TOTAL_DAYS_UNTIL_25_YRS_SERV
,(CASE WHEN TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) >= 30 THEN '30 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - E.HIRE_DT) - (1825 * 6)) * -1), 999999) END) TOTAL_DAYS_UNTIL_30_YRS_SERV
,(CASE WHEN TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) >= 35 THEN '35 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - E.HIRE_DT) - (1825 * 7)) * -1), 999999) END) TOTAL_DAYS_UNTIL_35_YRS_SERV
,(CASE WHEN TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) >= 40 THEN '40 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - E.HIRE_DT) - (1825 * 8)) * -1), 999999) END) TOTAL_DAYS_UNTIL_40_YRS_SERV
from ps_job a, ps_names b, ps_sal_grade_tbl c, ps_employment e, ps_person p
where a.reports_to in ('&reports_to')
and a.emplid = b.emplid
and a.emplid = e.emplid
and a.emplid = p.emplid
and b.name_type = 'PRI'
and b.effdt = (select max(b1.effdt) from ps_names b1
where b1.emplid = b.emplid
and b1.name_type = 'PRI'
and b1.effdt <= sysdate)
and c.setid = '&setid'
and c.grade = a.grade
and c.sal_admin_plan = a.sal_admin_plan
and c.effdt = (select max(c1.effdt) from ps_sal_grade_tbl c1
where c1.setid = c.setid
and c1.grade = c.grade
and c1.sal_admin_plan = c.sal_admin_plan
and c1.effdt <= sysdate)
and a.effdt = (select max(a1.effdt) from ps_job a1
where a1.emplid = a.emplid
and a1.empl_rcd = a.empl_rcd
and a1.effseq = a.effseq
and a1.effdt <= sysdate)
and a.effseq = (select max(a2.effseq ) from ps_job a2
where a2.emplid = a.emplid
and a2.empl_rcd = a.empl_rcd
and a2.effdt = a.effdt )
order by a.reports_to, total_years_worked desc, b.name

Finding Menu Path

Here are some good SQL's to have in your library.

When a process name is known:

SELECT DISTINCT
PRCS.PRCSTYPE,
PRCS.PRCSNAME,
PRCS.DESCR,
PAGE.PNLGRPNAME as Component,
'Home > ' || RTRIM(M.MENUGROUP) || ' > ' || RTRIM(M.MENULABEL) || ' > ' || RTRIM(ITEM.BARLABEL) || ' > ' || ITEM.ITEMLABEL as Location
FROM PSMENUDEFN M,
PSMENUITEM ITEM,
PS_PRCSDEFNPNL PAGE,
PS_PRCSDEFN PRCS
WHERE M.MENUNAME = ITEM.MENUNAME
AND ITEM.PNLGRPNAME = PAGE.PNLGRPNAME
AND PAGE.PRCSTYPE = PRCS.PRCSTYPE
AND PAGE.PRCSNAME = PRCS.PRCSNAME
AND PRCS.PRCSNAME = 'PY_PULL_COST'


When a component name is known:

SELECT DISTINCT PRCS.PRCSTYPE , PRCS.PRCSNAME , PRCS.DESCR ,
PAGE.PNLGRPNAME as Component , 'Home > ' || RTRIM(MENU.MENUGROUP) || ' > ' ||
RTRIM(MENU.MENULABEL) || ' > ' || RTRIM(ITEM.BARLABEL) || ' > ' || ITEM.ITEMLABEL as MenuPath
FROM PSMENUDEFN MENU , PSMENUITEM ITEM , PS_PRCSDEFNPNL PAGE , PS_PRCSDEFN PRCS
WHERE MENU.MENUNAME = ITEM.MENUNAMEAND ITEM.PNLGRPNAME = PAGE.PNLGRPNAME
AND PAGE.PRCSTYPE = PRCS.PRCSTYPE
AND PAGE.PRCSNAME = PRCS.PRCSNAME
AND PAGE.PNLGRPNAME = 'component-name'

When a record name is known:

SELECT DISTINCT PFLD.RECNAME ,
PFLD.PNLNAME as Page , 'Home > ' || RTRIM(MENU.MENUGROUP) || ' > ' ||
RTRIM(MENU.MENULABEL) || ' > ' || RTRIM(ITEM.BARLABEL) || ' > ' || ITEM.ITEMLABEL as MenuPath
FROM PSMENUDEFN MENU , PSMENUITEM ITEM , PSPNLGROUP COMP , PSPNLFIELD PFLD
WHERE MENU.MENUNAME = ITEM.MENUNAME
AND ITEM.PNLGRPNAME = COMP.PNLGRPNAME
AND COMP.PNLNAME = PFLD.PNLNAME
AND PFLD.RECNAME = 'record-name'

When a page name is known:

SELECT DISTINCT COMP.PNLNAME as Page , 'Home > ' ||
RTRIM(MENU.MENUGROUP) || ' > ' || RTRIM(MENU.MENULABEL) || ' > ' ||
RTRIM(ITEM.BARLABEL) || ' > ' || ITEM.ITEMLABEL as MenuPath
FROM PSMENUDEFN MENU , PSMENUITEM ITEM , PSPNLGROUP COMP
WHERE MENU.MENUNAME = ITEM.MENUNAME
AND ITEM.PNLGRPNAME = COMP.PNLGRPNAME
AND COMP.PNLNAME = 'page-name'