Friday, December 11, 2009

Retrieve File Date

picked up from Amit..

Local array of string &aryFileNames;
Local JavaObject &javaFile, &javaSDF;

Local string &strFileName;

&aryFileNames = FindFiles("C:\Temp\*.*", %FilePath_Absolute);

While &aryFileNames.Len > 0

   &strFileName = LTrim(RTrim(&aryFileNames.Shift()));

   &javaFile = CreateJavaObject("java.io.File", &strFileName);
   &javaSDF = CreateJavaObject("java.text.SimpleDateFormat", "MM/dd/yyyy");
   &mod = &javaSDF.format(&javaFile.lastModified());

   MessageBox(0, "", 0, 0, "File: " | &strFileName | " Date Modified: " | &mod);

End-While;

Monday, December 7, 2009

xmlp remove repeated fields

picked up from ItToolBox & Amit..

<?fld_EMPLID[not(.=preceding::fld_EMPLID)]?>

Sunday, November 8, 2009

AE & Run Control Tables

AELOCKMGR
AETEMPTBLMGR used to reserve the instance number (CURTEMPINSTANCE)
AEREQUESTTBL
AEREQUESTPARM
AE_REQUEST
AERUNCONTROL If you get message to restart a process instance - look here first
AE_RUN_CONTROL
AERUNCONTROLPC
PRCSRQSTDLGLIST
PRCSRUNCNTL
PSPRCSQUE holds jobs in No Success and in Queue
PSPRCSRQST
PRCSRQSTDIST

Tuesday, November 3, 2009

Portal User tables

Portal user home page definition
PSPRUHDEFN

Personal pagelet??
PSPRUHPERSPGLT

Portal user home page tab
PSPRUHTAB

Portal user home page tab pagelet
PSPRUHTABPGLT

Monday, November 2, 2009

HTML popup using PeopleCode

Thanks to this post I was able to do this as well.

1. Create two work records, one for the web library (e.g. WEBLIB_ACTEST) and one for the buffer field (e.g. ACTEST_WRK). The first record should just have one field (i.e. ISCRIPT1) and the second record should have at least a field to be used as an HTML area and probably a long field (e.g. HTMLAREA).

2. On your page add the HTML area field, e.g. ACTEST_WRK.HTMLAREA, as a field of type HTML Area.

3. Create an HTML object (lets call it ACTEST_1) which will be the actual popup page. For example:

<HTML>
<HEAD>
<TITLE>Popup Window</TITLE>
<BODY>
some text goes here...
</BODY>
</HEAD>

4. Create another HTML object (or alternatively it could be pasted directly into the HTML area as a constant rather than a field) that contains the anchor link (lets call it ACTEST_2):

<a href="#" onclick="window.open('%BIND(:1)','whatevername','width =400,height=400')">Link Name</a>

5. Create an iScript that will allow you to write the html in a new window. An example of the PeopleCode is:

Function Iscript_AcTest
   
   Local string &html;
   
   &html = GetHTMLText(HTML.ACTEST_1);
   %Response.Write(&html);
   
End-Function;

6. Populate the value of the html area on the page. For example on ACTEST_WRK.RowInit:

Local string &subrul, &url;

&suburl = "Iscript_AcTest";
&url = GenerateScriptContentURL(%Portal, %Node, Record.WEBLIB_ACTEST, Field.ISCRIPT1, "FieldFormula", &suburl);
ACTEST_WRK.HTMLAREA.Value = GetHTMLText(HTML.ACTEST_2, &url);

7. Don't forget to assign security for the iScript.

Friday, August 7, 2009

Generating xls from PeopleCode

You can generate xml in a format that MS Excel will recognise. Refer to the files below.



Refer to http://sourceforge.net/projects/sqr2xml for the original files etc

Sunday, August 2, 2009

App Engine & Cobol trace

AE Process Definition - Override options tab
-TRACE 7 -TOOLSTRACEPC 3596 -TOOLSTRACESQL 131 (review peoplebooks on which tracing values you need to place)

Cobol SQL Process Type
.. for the platform you are running on. Enter 255 in the command line field between two forward slashes “//”.
i.e. %%PRCSNAME%% %%DBTYPE%%/%%DBNAME%%/%%OPRID%%/%%OPRPSWD%%/%%RUNCNTLID%%/%%INSTANCE%%/255/%%DBFLAG%%

Friday, July 31, 2009

Choose a template

<?choose:?>
  <?when: EEName='test'?>
   <?call:ANDREW1?>
  <?end when?>
  <?otherwise:?>
   <?call:ANDREW2?>
  <?end otherwise?>
<?end choose?>

Thursday, July 30, 2009

Calculate age

SELECT TRUNC(MONTHS_BETWEEN(SYSDATE,A.BIRTHDATE)/12) AS AGE
FROM PS_PERSON A

Clear out tracefile & set trace

Just replace the ip address..
Local File &file;
&file = GetFile(GetEnv("PS_SERVDIR") | "/LOGS/" | %OperatorId | "_172.18.31.49.tracesql", "W", %FilePath_Absolute);
rem &file.Delete();
&file.Close();
SetTraceSQL(3);
SetTracePC(2060);

Wednesday, July 29, 2009

Generate XML from the Rowset

   Local PSXP_XMLGEN:RowSetDS &ds = create PSXP_XMLGEN:RowSetDS();
   Local string &xml;
   &xml = &ds.getXMLData(GetLevel0(), "");
   WinMessage(&xml);

Monday, July 27, 2009

XML - XML File Source

1. Generate your xml file using PeopleCode and then use the following:
method DisplayReport
   
   Local PSXP_RPTDEFNMANAGER:ReportDefn &C1_appReport;
   
   rem Create the ReportDefn class;
   &C1_appReport = create PSXP_RPTDEFNMANAGER:ReportDefn(&C1_strRptDefnID);
   
   rem Close the file;
   &C1_appXmlFile.CloseFile();
   
   try
      
      rem Get the report definition and set the xml file for the run time and then display the report;
      &C1_appReport.Get();
      
      &C1_appReport.SetRuntimeDataXMLFile(&C1_appXmlFile.C1_strDirAndName);
      &C1_appReport.ProcessReport(&C1_strTmpltID, "", %Date, "");
      
      CommitWork();
      &C1_appReport.DisplayOutput();
      &C1_appXmlFile.DeleteFile();
      
   catch Exception &Err
      Local string &sSub1, &sSub2, &sSub3, &sSub4, &sSub5;
      Evaluate &Err.SubstitutionCount
      When > 4
         &sSub5 = &Err.GetSubstitution(5);
      When > 3
         &sSub4 = &Err.GetSubstitution(4);
      When > 2
         &sSub3 = &Err.GetSubstitution(3);
      When > 1
         &sSub2 = &Err.GetSubstitution(2);
      When > 0
         &sSub1 = &Err.GetSubstitution(1);
      End-Evaluate;
      Error MsgGet(&Err.MessageSetNumber, &Err.MessageNumber, &Err.ToString(), &sSub1, &sSub2, &sSub3, &sSub4, &sSub5);
      
   end-try;
   
end-method;

Tuesday, June 16, 2009

running XMLP from Command Line

1) Create and CD to a working directory (e.g. c:\psxmlp\enginetest)
2) Copy your RTF template and XML data to that directory
3) Set PS_HOME=c:\PT848 (set your PS_HOME)
4) Set your CLASSPATH as follows:

set CLASSPATH=%PS_HOME%\class\psxmlp.jar
set CLASSPATH=%CLASSPATH%;%PS_HOME%\class\xdo56.jar
set CLASSPATH=%CLASSPATH%;%PS_HOME%\class\xmlparserv2.jar
set CLASSPATH=%CLASSPATH%;%PS_HOME%\class\peoplecode.jar
set CLASSPATH=%CLASSPATH%;%PS_HOME%\class\aolj.jar
set CLASSPATH=%CLASSPATH%;%PS_HOME%\class\i18nAPI_v3.jar
set CLASSPATH=%CLASSPATH%;%PS_HOME%\class\axdoparser.jar
set CLASSPATH=%CLASSPATH%;%PS_HOME%\class\bicmn.jar
set CLASSPATH=%CLASSPATH%;%PS_HOME%\class\bipres.jar
set CLASSPATH=%CLASSPATH%;%PS_HOME%\class\jewt4.jar
set CLASSPATH=%CLASSPATH%;%PS_HOME%\class\share.jar
Run SET PATH to make sure CLASSPATH variable is defined.
For example:
C:\psxmlp\enginetest>set PATH

Point the PATH variable to JRE location: PSHOME/jre/bin
for example:
set PATH=C:\PT848l\JRE\bin;%PATH%

Run SET PATH again to make sure PATH variable is defined.
For example:
C:\psxmlp\enginetest>set PATH 

5) Convert your RTF template to xsl format:
for example:
C:\psxmlp\enginetest\java -Xmx512m -Xms256m com.peoplesoft.pt.xmlpublisher.PTRTFPocessor -genxsl xrfwin.rtf xrfwin.xsl 

Test Successful 

6) Generate your report (2 is for PDF output: RTF = 1, PDF = 2, HTML = 3, EXCEL = 4)

for example:
C:\psxmlp\enginetest\java -Xmx512m -Xms256m com.peoplesoft.pt.xmlpublisher.PTFOProcessor -genoutput xrfwin.xsl xrfwin.xml report.pdf 2

Test Successful 

The Above step will create a report.pdf file in the psxmlp\enginetest directory.

some xmlp commands

Page break
<?split-by-page-break:?>
Format Dates and Numbers
<?format-number:SALARY;'999G999D99 MI'?>
<?format-number:SALARY;'PR 999G999D99'?>
<?format-number:SALARY;'PT 999G999D99'?>
<?format-number:SALARY;'S 999G999D99'?>
<?format-currency:value;currency_code;'truefalse'?>
<?format-date:HIRE_DATE;'YYYY-MM-DD'?>
<?format-date:HIRE_DATE;'YYYY-MM-DD'?>
Define the Body, i.e. anything else is footer or header
<?start:body?>
<?end:body?>
Loop through a Rowset
<?for-each:row_C1_RPTS_SAL_DVW?>
<?end for-each?
Using "If" to display a field conditionally
<?if:fld_C1_SAL_SUM_NAME!=''?>
<?end if?

Friday, June 12, 2009

XMLP Tables

Template File Definition
SELECT A.*
FROM PSXPTMPLFILEDEF A
WHERE A.TMPLDEFN_ID = :1

Reset Process Status

When the process has errored and you can't remove it from the process monitor.
UPDATE PSPRCSRQST
SET RUNSTATUS = '2'
,DISTSTATUS = '5'
WHERE PRCSINSTANCE = 191596
/
-- DISTSTATUS OF 5 = POSTED TO WEB
-- RUNSTATUS  OF 2 = DELETE

Portal SQL

Retrieve a Portal Hierarchy using the portal object name

WITH PORTAL_REGISTRY AS
(SELECT RTRIM(REVERSE(SYS_CONNECT_BY_PATH(REVERSE(PORTAL_LABEL), ' >> ')), ' >> ') PATH
,LEVEL LVL
FROM PSPRSMDEFN
WHERE PORTAL_NAME = 'EMPLOYEE'
START WITH PORTAL_OBJNAME = :1
CONNECT BY PRIOR PORTAL_PRNTOBJNAME = PORTAL_OBJNAME)
SELECT PATH
FROM PORTAL_REGISTRY
WHERE LVL = (SELECT MAX(LVL)
       FROM PORTAL_REGISTRY)
Retrieve a Portal Hierarchy using the Menu, Component & Market
WITH PORTAL_REGISTRY AS
(SELECT RTRIM(REVERSE(SYS_CONNECT_BY_PATH(REVERSE(PORTAL_LABEL), ' >> ')), ' >> ') PATH
, LEVEL LVL
FROM PSPRSMDEFN
WHERE PORTAL_NAME = 'EMPLOYEE'
START WITH PORTAL_REFTYPE = 'C'
AND PORTAL_URI_SEG1 = :1
AND PORTAL_URI_SEG2 = :2
AND PORTAL_URI_SEG3 = :3
CONNECT BY PRIOR PORTAL_PRNTOBJNAME = PORTAL_OBJNAME)
SELECT PATH
FROM PORTAL_REGISTRY
WHERE LVL = (SELECT MAX(LVL)
       FROM PORTAL_REGISTRY )

Retrieve a Reporting Hierarchy

SELECT LEVEL
,A.POSITION_NBR
,A.REPORTS_TO
FROM (
      SELECT A.POSITION_NBR
      ,A.REPORTS_TO
      FROM PS_POSITION_DATA A
      WHERE A.EFFDT = (SELECT MAX(A1.EFFDT)
                       FROM PS_POSITION_DATA A1
                       WHERE A1.POSITION_NBR = A.POSITION_NBR
                       AND A1.EFFDT <= SYSDATE ) 
      ) A  
START WITH A.POSITION_NBR = :1  
CONNECT BY PRIOR A.POSITION_NBR = A.REPORTS_TO  
AND A.POSITION_NBR <> A.REPORTS_TO
ORDER BY 1