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 [Link] "IP",
to_char([Link],'MM/DD/YYYY HH24') "DTTM"
from psaccesslog a
where [Link] > 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.*, [Link] from psaccesslog a, psoprdefn b
where [Link] > sysdate - 7 -- This indicates today and the past seven days...
and [Link] = [Link]
and [Link] like '%Smith%'
ORDER BY [Link] 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)
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, [Link], [Link], ([Link] * 26), [Link],
c.min_rt_annual, c.mid_rt_annual, c.max_rt_annual, a.sal_admin_plan ,E.HIRE_DT,
TO_CHAR(((SYSDATE - [Link]) / 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 [Link] = [Link]
and [Link] = [Link]
and [Link] = [Link]
and b.name_type = 'PRI'
and [Link] = (select max([Link]) from ps_names b1
where [Link] = [Link]
and b1.name_type = 'PRI'
and [Link] <= sysdate)
and [Link] = '&setid'
and [Link] = [Link]
and c.sal_admin_plan = a.sal_admin_plan
and [Link] = (select max([Link]) from ps_sal_grade_tbl c1
where [Link] = [Link]
and [Link] = [Link]
and c1.sal_admin_plan = c.sal_admin_plan
and [Link] <= sysdate)
and [Link] = (select max([Link]) from ps_job a1
where [Link] = [Link]
and a1.empl_rcd = a.empl_rcd
and [Link] = [Link]
and [Link] <= sysdate)
and [Link] = (select max([Link] ) from ps_job a2
where [Link] = [Link]
and a2.empl_rcd = a.empl_rcd
and [Link] = [Link] )
order by a.reports_to, total_years_worked desc, [Link]
Finding Menu Path
Here are some good SQL's to have in your library.
When a process name is known:
SELECT DISTINCT [Link] , [Link] , [Link] ,
[Link] as Component , 'Home > ' RTRIM([Link]) ' > '
RTRIM([Link]) ' > ' RTRIM([Link]) ' > ' [Link] as
MenuPathFROM PSMENUDEFN MENU , PSMENUITEM ITEM , PS_PRCSDEFNPNL PAGE ,
PS_PRCSDEFN PRCSWHERE [Link] = [Link]
[Link] = [Link] [Link] =
[Link] [Link] = [Link] [Link] =
'process-name'
When a component name is known:
SELECT DISTINCT [Link] , [Link] , [Link] ,
[Link] as Component , 'Home > ' RTRIM([Link]) ' > '
RTRIM([Link]) ' > ' RTRIM([Link]) ' > ' [Link] as
MenuPathFROM PSMENUDEFN MENU , PSMENUITEM ITEM , PS_PRCSDEFNPNL PAGE ,
PS_PRCSDEFN PRCSWHERE [Link] = [Link]
[Link] = [Link] [Link] =
[Link] [Link] = [Link] [Link] =
'component-name'
When a record name is known:
SELECT DISTINCT [Link] , [Link] as Page , 'Home > '
RTRIM([Link]) ' > ' RTRIM([Link]) ' > '
RTRIM([Link]) ' > ' [Link] as MenuPathFROM PSMENUDEFN MENU
, PSMENUITEM ITEM , PSPNLGROUP COMP , PSPNLFIELD PFLDWHERE
[Link] = [Link] [Link] =
[Link] [Link] = [Link] [Link] =
'record-name'
When a page name is known:
SELECT DISTINCT [Link] as Page , 'Home > ' RTRIM([Link]) ' >
' RTRIM([Link]) ' > ' RTRIM([Link]) ' > ' [Link] as
MenuPathFROM PSMENUDEFN MENU , PSMENUITEM ITEM , PSPNLGROUP
COMPWHERE [Link] = [Link] [Link] =
[Link] [Link] = 'page-name'
Peoplecode to Read and Write a File - Send email within Peoplecode using
SendMail() function
Here is some sample code to read and write a file. You will have to design your code
to handle your environment paths...
/*** Read a file ***/
Local File &ASU_ADDR_WRK;
Local Rowset &rsInput_Rowset;
Local Record &Rec1;
Local SQL &SQL1;
&Rec1 = CreateRecord(Record.ASU_ADDR_WRK);
&SQL1 = CreateSQL("%Insert(:1)");
If ASU_ADDR_AET.PROCESS_INSTANCE > 0 Then
&pshome = GetEnv("PS_HOME");
&InboundDirectory = &pshome "/datafiles/interfacein/";
&Filename = &InboundDirectory “asu_addr.csv";
End-If;
If FileExists(&Filename, %FilePath_Absolute) Then
&ASU_ADDR_WRK = GetFile(&Filename, "R", "A", %FilePath_Absolute);
End-If;
&ASU_ADDR_WRK.SetFileLayout(FileLayout.ASU_ADDR_WRK);
&rsInput_Rowset = CreateRowset(Record.ASU_ADDR_WRK);
&rsInput_Rowset = &ASU_ADDR_WRK.ReadRowset();
While &rsInput_Rowset <> Null
&rsInput_Rowset.GetRow(1).ASU_ADDR_WRK.CopyFieldsTo(&Rec1);
&[Link](&Rec1);
&rsInput_Rowset = &ASU_ADDR_WRK.ReadRowset();
End-While;
&ASU_ADDR_WRK.Close();
/*** Write to a file ***/
Local File &ASU_ADDR_DUP;
Local Record &Rec3;
Local SQL &SQL3;
&Rec3 = CreateRecord(Record.ASU_ADDR_DUP);
&SQL3 = CreateSQL("%SelectAll (:1) ORDER BY EMPLID, ADDRESS_TYPE", &Rec3);
If ASU_ADDR_AET.PROCESS_INSTANCE > 0 Then
&pshome = GetEnv("PS_HOME");
&OutboundDirectory = &pshome "/ datafiles/interfaceout/"; ";
&FileName2 = &OutboundDirectory “asu_addr_dup.csv";
End-If;
&ASU_ADDR_DUP = GetFile(&FileName2, "W", "A", %FilePath_Absolute);
If &ASU_ADDR_DUP.IsOpen Then
If &ASU_ADDR_DUP.SetFileLayout(FileLayout.ASU_ADDR_DUP) Then;
While &[Link](&Rec3)
&ASU_ADDR_DUP.WriteRecord(&Rec3);
End-While;
End-If;
End-If;
&ASU_ADDR_DUP.Close();
/*** Send and email ***/
&MAIL_FLAGS = 0;
&MAIL_TO = “[Link]@[Link]";
&MAIL_CC = "";
&MAIL_BCC = "";
&MAIL_SUBJECT = “ASU Application Engine Training Class";
&MAIL_TEXT = "See attachment for the file created in AE class";
&pshome = GetEnv("PS_HOME");
&pshome "/datafiles/interfaceout/";
&MAIL_FILES = &pshome "asu_addr_dup.csv";
&MAIL_TITLES = “asu_addr_dup.csv";
&RET = SendMail(&MAIL_FLAGS, &MAIL_TO, &MAIL_CC, &MAIL_BCC, &MAIL_SUBJECT,
&MAIL_TEXT, &MAIL_FILES, &MAIL_TITLES);