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?

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