Reset Page Numbers in XML Publisher (XMLP)

@section:row_AMS_SOA_PROG_T?> That's it.

Database creation date time

select created from v$database;

Hiding Elements - Printable Version of a Page

Rather than create a printable version of a page that is a copy of a given page sans buttons, headers and other elements that don't look good on a hard-copy, you can add a simple button and then define which fields you don't want to be printable. Much easier.

1. Add a button or link to your page.
2. Under the Push Button/Hyperlink Page Properties setup as follows:
   Destination = 'External Link'
   External Link = 'Dynamic'
3. Add some PeopleCode:
   DERIVED_REC.JS_PRINT.Value = "javascript:window.print()";
   
   Now you have a basic link on your page that when clicked will print it from the browser.
4. Add a HTML Area to the page with the following value:
   
   <style type="text/css">
@media print {
#PAGEBAR, .PSHEADERTOOLBARFRAME, #PRINT_BTN,
#INFO,
#DERIVED_REC_JS_PRINT { display: none }
}
</style>
   
   Note that #DERIVED_REC_JS_PRINT is the link you made in step 1.
   #Info in this example is a chunk of HTML text I wrapped in <span id="Info">
   You can add any field, usually in the format RECORD_NAME_FIELD_NAME, check your HTML source to be sure.

PDF Stamper (iText)

Download the JAR file from here:
http://itextpdf.com/download.php

Place it on the App Server, where all the other JAR files are (somewhere)

Note you'll need Acrobat Pro to add fields to the PDF and give them names.

Example PeopleCode. Will Instantiate the Java object, open the Template file (source) and the target file (...target) and then map data to the defined fields on your template.

Will also create an attachment and then view ti - pop up the PDF output.

Local JavaObject &Outputstream, &PDFWriter, &PDFReader, &PDFStamper, &TemplateFields;

/* File name */
Evaluate &recAMS_CAF_DAT_VW.AMS_CAF_TYPE.Value
When = "C"
   /* Pivot date for old/new form is 22/12/2010 */
   If &recAMS_CAF_DAT_VW.EFFDT.Value >= Date(20101222) Then
      &TemplatePathname = GetEnv("PS_HOME") | "/rmit/tmp/1290A_NEW_TEMPLATE.pdf";
   Else
      &TemplatePathname = GetEnv("PS_HOME") | "/rmit/tmp/1290A_OLD_TEMPLATE.pdf";
   End-If;
When = "F"
   /* There is a really old form version, pre 2009. */
   If &recAMS_CAF_DAT_VW.EFFDT.Value >= Date(20090101) Then
      &TemplatePathname = GetEnv("PS_HOME") | "/rmit/tmp/1292A_OLDOLD_TEMPLATE.pdf";
   Else
      /* Pivot date for old/new form is 22/12/2010 */
      If &recAMS_CAF_DAT_VW.EFFDT.Value >= Date(20101222) Then
         &TemplatePathname = GetEnv("PS_HOME") | "/rmit/tmp/1292A_NEW_TEMPLATE.pdf";
      Else
         &TemplatePathname = GetEnv("PS_HOME") | "/rmit/tmp/1292A_OLD_TEMPLATE.pdf";
      End-If;
   End-If;
When = "V"
   /* Pivot date for old/new form is 22/12/2010 */
   If &recAMS_CAF_DAT_VW.EFFDT.Value >= Date(20101222) Then
      &TemplatePathname = GetEnv("PS_HOME") | "/rmit/tmp/1296A_NEW_TEMPLATE.pdf";
   Else
      &TemplatePathname = GetEnv("PS_HOME") | "/rmit/tmp/1296A_OLD_TEMPLATE.pdf";
   End-If;
End-Evaluate;

&FILE = %OperatorId | "_" | %Datetime | "_eCAF_PDF_STAMPER_OUTPUT.pdf";

/* Output File path */
/* ps_home = /software/peoplesoft/product/pscs/pscsdv4 */
&InOutputPdfPathname = GetEnv("PS_HOME") | "/rmit/tmp/" | &FILE;


/* Create the "input" Template PDF */
&PDFReader = CreateJavaObject("com.lowagie.text.pdf.PdfReader", &TemplatePathname);
&PDFReader.consolidateNamedDestinations();
&PDFReader.removeUnusedObjects();

/* Create the "Output" Template PDF */
&Outputstream = CreateJavaObject("java.io.FileOutputStream", &InOutputPdfPathname);
&PDFStamper = CreateJavaObject("com.lowagie.text.pdf.PdfStamper", &PDFReader, &Outputstream);

/* Flatten the form */
&PDFStamper.setFormFlattening( True);

/* Get the fields to stamp */
&TemplateFields = &PDFStamper.getAcroFields();

/* Stamp fields */
&TemplateFields.setField("FAMILY_NAME", "Test family name");
rem &TemplateFields.setField("name", "John Student");
rem &TemplateFields.setField("address", "Baeyensstraat 121, Sint-Amandsberg");
rem &TemplateFields.setField("postal_code", "BE-9040");
rem &TemplateFields.setField("email", "s1234567@somewhere.edu.au");

/* Close the stamper */
&PDFStamper.close();

/* Needed if in think-time */
CommitWork();


/* Put PDF file temporarily into database, so we can send to users browser using the ViewAttachment function */
&Ret = PutAttachment("record://AMS_ECAF_ATTACH", &FILE, &InOutputPdfPathname);
If &Ret = %Attachment_Success Then;
   
   /* Send Output file to the browser */
   &Ret = ViewAttachment("record://AMS_ECAF_ATTACH", &FILE, &FILE, True, True);
   
   /* Remove temporary PDF file from database */
   &Ret = DeleteAttachment("record://AMS_ECAF_ATTACH", &FILE, True);
   
End-If;

Sequential Numbers - SQL (2)

MERGE INTO ps_sf_acctg_ln
USING (SELECT ROW_NUMBER() OVER(PARTITION BY run_dt,seqnum ORDER BY run_dt,seqnum, sf_line_nbr) sf_line_nbr, ROWID rid FROM ps_sf_acctg_ln) SOURCE
ON (ps_sf_acctg_ln.ROWID = SOURCE.rid)
WHEN MATCHED THEN UPDATE SET ps_SF_ACCTG_LN.SF_LINE_NBR = SOURCE.sf_line_nbr;
commit;

Sequential Numbers - SQL

This will creating a running sequence number for each unique combination of RUN_DT and SEQNUM; NEW_SF_LINE_NBR

SELECT
run_dt,
seqnum,
ROW_NUMBER() OVER(PARTITION BY run_dt,seqnum ORDER BY run_dt, seqnum) new_sf_line_nbr
FROM ps_sf_acctg_ln

Using DateTime Fields in SQR - Gotcha #343

This will return unexpected results:

AND A.CLASS_PRICE_DTTM <= $RC_FromDate

This will return expected results:

AND A.CLASS_PRICE_DTTM <= to_date($RC_FromDate, 'DD-MON-YYYY')

Where $RC_FromDate is a variable populated from a Date field on your Run Control page.

DATETIMESTAMP Field - Inserting into in SQR or SQL

UPDATE TABKLE_X
set LAST_UPDATE_DTTM = to_timestamp($SysDateTime, 'DD-MON-YYYY_HH:MI:SS.FF6_AM')

...something about version 9 :S

Last Process Instance Number in PeopleSoft

Lifted from Praj's most excellent Wiki:
http://peoplesoft.wikidot.com

The table PS_PRCSSEQUENCE stores the last sequence numbers for the Process Scheduler and Report Manager. In PeopleTools 8.48.15 there are 5 process sequence keys (PRCSSEQKEY) which you can view yourself by looking at the translates on this field:

0 = Process Instance
1 = Report Instance
2 = Transfer Instance
3 = Report ID
4 = Folder ID

It's probably a good health check to ensure that the numbers in PS_PRCSSEQUENCE match those in the relevant tables:

select max(PRCSINSTANCE) as PRCSSEQKEY0 from PSPRCSRQST;
select max(CONTENTID) PRCSSEQKEY1 from PS_CDM_AUTH;
select max(TRANSFERINSTANCE) PRCSSEQKEY2 from PS_CDM_LIST;
select max(PSRF_REPORT_ID) PRCSSEQKEY3 from PSRF_RINFO_TBL;
select max(PSRF_FOLDER_ID) PRCSSEQKEY4 from PSRF_FINFO_TBL;

Especially if you happen to be clearing out data from these tables manually. I guess if you do clear out processes/reports you'll want to update the PS_PRCSSEQUENCE accordingly.

Reports and/or log file not posting. N/A status.

You've tried everything. New run control ids, deleting rows from proc sched, holding your tongue the other way, mucking around with server reboots and caches, etc. etc.

Yet if you log in as somebody else, it works.

Delete your user. Start again.

SQL to Create Insert Script; Replace DMS

Instead using DMS to move data around you could run the following to insert a zillion rows. PL/SQL creates the SQL file for you.

CREATE OR REPLACE FUNCTION GET_INSERT_SCRIPT(V_TABLE_NAME VARCHAR2)
RETURN VARCHAR2 AS
B_FOUND BOOLEAN := FALSE;
V_TEMPA VARCHAR2 (8000);
V_TEMPB VARCHAR2 (8000);
V_TEMPC VARCHAR2 (255);
BEGIN
    FOR TAB_REC IN (SELECT TABLE_NAME
                    FROM ALL_TABLES
                    WHERE TABLE_NAME = UPPER (V_TABLE_NAME)) LOOP
        B_FOUND := TRUE;
        V_TEMPA := 'select ''insert into ' || TAB_REC.TABLE_NAME || ' (';
        FOR COL_REC IN (SELECT *
                        FROM ALL_TAB_COLUMNS
                        WHERE TABLE_NAME = TAB_REC.TABLE_NAME
                        ORDER BY COLUMN_ID) LOOP
            IF COL_REC.COLUMN_ID = 1 THEN
                V_TEMPA := V_TEMPA || '''||chr(10)||''';
            ELSE
                V_TEMPA := V_TEMPA || ',''||chr(10)||''';
                V_TEMPB := V_TEMPB || ',''||chr(10)||''';
            END IF;

            V_TEMPA := V_TEMPA || COL_REC.COLUMN_NAME;
            IF INSTR (COL_REC.DATA_TYPE, 'CHAR') > 0 THEN
                V_TEMPC := '''''''''||' || COL_REC.COLUMN_NAME || '||''''''''';
            ELSIF INSTR (COL_REC.DATA_TYPE, 'DATE') > 0 THEN
                V_TEMPC := '''to_date(''''''||to_char('
                        || COL_REC.COLUMN_NAME
                        || ',''mm/dd/yyyy hh24:mi'')||'''''',''''mm/dd/yyyy hh24:mi'''')''';
            ELSE
                V_TEMPC := COL_REC.COLUMN_NAME;
            END IF;

            V_TEMPB := V_TEMPB
                    || '''||decode('
                    || COL_REC.COLUMN_NAME
                    || ',Null,''Null'','
                    || V_TEMPC
                    || ')||''';
        END LOOP;

        V_TEMPA := V_TEMPA
                || ') values ('
                || V_TEMPB
                || ');'' from '
                || TAB_REC.TABLE_NAME
                || ';';
    END LOOP;

    IF NOT B_FOUND THEN
        V_TEMPA := '-â?? Table ' || V_TABLE_NAME || ' not found';
    ELSE
        V_TEMPA := V_TEMPA || CHR (10) || 'select ''-- commit;'' from dual;';
    END IF;

    RETURN V_TEMPA;
END;
/
SHOW ERRORS

And the Code to run it:

set head off
set pages 0
set trims on
set lines 2000
set feed off
set echo off
spool c:\Temp\genInsertForMyTable.sql
SELECT get_insert_script('PS_SAD_TAC_MAP_BOA') FROM DUAL;  -- change table here
spool off

XML Publisher: How To Guide

Summary of the 1,000,000 XML Publisher posts below. Quick little guide on how to create a basic Report Definition in v9 using Rowsets and Queries. You could modify to include Connected Queries (put data into rowset first).

XML Publisher: How to Guide

The Class referenced in the Guide (code)

Menu Navigation Path to a Component

Find your way like this:

(Also in 8.5 version of Tools you can enter the Component Name under Menu > Search Menu. Easier)

DEFINE COMPONENT_NAME = "SSS_STDNCTR_OPT";
SELECT
  A.PORTAL_URI_SEG1 AS Menu
, A.PORTAL_URI_SEG2 AS Component
, D.PNLNAME AS Page
, C.PORTAL_LABEL || '-> ' || B.PORTAL_LABEL || '-> ' || A.PORTAL_LABEL AS Portal_Path
FROM
  PSPRSMDEFN C
, PSPRSMDEFN A
    LEFT OUTER JOIN PSPRSMDEFN B
      ON A.PORTAL_PRNTOBJNAME = B.PORTAL_OBJNAME , PSPNLGROUP D WHERE C.PORTAL_OBJNAME = B.PORTAL_PRNTOBJNAME
  AND D.PNLGRPNAME = A.PORTAL_URI_SEG2
  AND C.PORTAL_NAME = A.PORTAL_NAME
  AND A.PORTAL_URI_SEG2 LIKE '&COMPONENT_NAME'
  ORDER BY 1,2,3,4;

XML Publisher to Window

To open directly to a pop-up window, not using the Process Scheduler:

/********************************************************************************
FRS576 CR000222 Michael Nitschke 01/06/2011
Trigger the Job that will produce the XMLP Report.
********************************************************************************/
/* FRS576: Begin */
import PSXP_RPTDEFNMANAGER:*;

Local PSXP_RPTDEFNMANAGER:ReportDefn &oRptDefn;
Local string &Report, &TemplateId, &LanguageCD, &OutputFormat;
Local date &AsOfDate;
Local Record &parms;

&Report = "AMSW3_INVCEQ";
&TemplateId = "AMSW3_INVCEQ_1";
&LanguageCD = "";
&AsOfDate = %Date;
&OutputFormat = "";

/* Create Report Definition. */
&oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&Report);
&oRptDefn.Get();

/* Setup Query Parameters. */
&parms = &oRptDefn.GetPSQueryPromptRecord();
&parms.INVOICE_ID.Value = AMSW3_STDNT_INV.INVOICE_ID.Value;
&oRptDefn.SetPSQueryPromptRecord(&parms);

/* Run Report. */
&oRptDefn.ProcessReport(&TemplateId, &LanguageCD, &AsOfDate, &OutputFormat);
CommitWork(); /* must do this */;

/* Display report in separate window. */
&oRptDefn.DisplayOutput();

/* FRS576 End */

Fixing Duplicate Student and Organisation IDs

Like this:

-- ************************************************************************************
-- CSUPGRD-727 Michael Nitschke 31/05/2011
-- Correct situation where Students and Organisations have duplicate IDs.
-- This has occurred where users have overwritten the 'NEW' value when adding an Org.
-- ************************************************************************************

BEGIN
    -- Build working tables for this process.

    -- This one stores all the Records that will potentially be updated.
    -- OCCURSCOUNT1 stores rows of data to be updated
    -- OCCURSCOUNT2 stores rows of data to be updated *after* the fix has been run. Should be 0.
    FOR i IN (SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME = 'PS_AMS_DUPID_RECNM') LOOP
        EXECUTE IMMEDIATE 'DROP TABLE PS_AMS_DUPID_RECNM';
    END LOOP;
    EXECUTE IMMEDIATE '
        CREATE TABLE PS_AMS_DUPID_RECNM (
            DESCR VARCHAR2(100) NOT NULL,
            RECNAME VARCHAR2(15) NOT NULL,
               FIELDNAME VARCHAR2(18) NOT NULL,
               OCCURSCOUNT1 SMALLINT NOT NULL,
            OCCURSCOUNT2 SMALLINT NOT NULL)
         TABLESPACE SAAPP STORAGE (INITIAL 40000 NEXT 100000 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PCTUSED 80'
         ;

    -- This one stores the old EXT_ORG_ID and the new EXT_ORG_ID for each Organisation that is having its ID updated.
    -- May be useful if something unexpected happens.
    FOR i IN (SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME = 'PS_AMS_DUPID_OLDNW') LOOP
        EXECUTE IMMEDIATE 'DROP TABLE PS_AMS_DUPID_OLDNW';
    END LOOP;
    EXECUTE IMMEDIATE '
        CREATE TABLE PS_AMS_DUPID_OLDNW (
            EXT_ORG_ID_OLD VARCHAR2(11) NOT NULL,
            EXT_ORG_ID VARCHAR2(11) NOT NULL)
        TABLESPACE SAAPP STORAGE (INITIAL 40000 NEXT 100000 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PCTUSED 80'
        ;

END;
/

-- This procedure takes a given Record and finds all Records that use it as a prompt, where that prompt field is not EXT_ORG_ID (as record would be picked up in base select already).
-- Note that this function is recursive. Use the maxDepth value to set how far down the tree to go.
-- Too high a value and you will run out of cursors.
CREATE OR REPLACE PROCEDURE FindWhereRecUsedAsPrompt(varRecname VARCHAR2, currentDepth integer) IS

    CURSOR cRecPrompts IS
        SELECT 'PROMPT' X, A.RECNAME AS RECNAME, A.FIELDNAME AS FIELDNAME
        FROM
          PSRECFIELD A
        , PSRECDEFN B
        , PSDBFIELD C
        WHERE A.FIELDNAME <> 'EXT_ORG_ID'
        AND A.EDITTABLE = varRecname
        AND B.RECNAME = A.RECNAME
        AND B.RECTYPE = 0  -- SQL Table
        AND C.FIELDNAME = A.FIELDNAME
        AND C.FIELDTYPE = 0; -- Character

    depth integer;
    maxDepth integer;

BEGIN

    maxDepth:= 1; -- Set how deep you want to traverse down the prompt tables
    depth:= currentDepth;

    FOR recP in cRecPrompts
    LOOP
        EXECUTE IMMEDIATE 'INSERT INTO PS_AMS_DUPID_RECNM (DESCR, RECNAME, FIELDNAME, OCCURSCOUNT1, OCCURSCOUNT2) VALUES (''' || recP.x || '(depth: ' || depth || ', parent:' || varRecname || ')'' , ''' || recP.RECNAME || ''', ''' || recP.FIELDNAME || ''' ,0 ,0)';
        IF depth < maxDepth THEN
            FindWhereRecUsedAsPrompt(recP.RECNAME, depth+1);
        END IF;
    END LOOP;
    depth:= depth-1;

END FindWhereRecUsedAsPrompt;
/

-- This procedure counts number of rows in affected tables.
-- It uses the temp table PS_AMS_DUPID_RECNM
-- OCCURSCOUNT1 stores the number of rows beofore the fix/update is applied.
-- OCCURSCOUNT2 stores the number of rows after the fix/update has been applied (should = 0).
CREATE OR REPLACE PROCEDURE analyseRowCount(fldUpdate VARCHAR2) IS

    CURSOR cTableList IS
        SELECT DISTINCT RECNAME, FIELDNAME, OCCURSCOUNT1 FROM PS_AMS_DUPID_RECNM
        ;

    sqlStatement VARCHAR2(300);
    rowCount INTEGER;

BEGIN
    FOR rec in cTableList
    LOOP
        -- Two steps for legibility's sake.
        BEGIN
            If substr(rec.RECNAME, 1, 2) = 'PS'
            THEN
                sqlStatement:= 'SELECT COUNT(*) FROM ' || rec.RECNAME || ' WHERE SUBSTR(' || rec.FIELDNAME || ',1,7) IN (SELECT SUBSTR(EXT_ORG_ID,1,7) FROM PS_EXT_ORG_TBL B WHERE SUBSTR(B.EXT_ORG_ID,1,7) IN (SELECT A.EMPLID FROM PS_PERSONAL_DATA A WHERE A.EMPLID = SUBSTR(B.EXT_ORG_ID,1,7)))';
--                dbms_output.put_line('SELECT COUNT(*) FROM ' || rec.RECNAME || ' WHERE SUBSTR(' || rec.FIELDNAME || ',1,7) IN ');
            ELSE
                sqlStatement:= 'SELECT COUNT(*) FROM PS_' || rec.RECNAME || ' WHERE SUBSTR(' || rec.FIELDNAME || ',1,7) IN (SELECT SUBSTR(EXT_ORG_ID,1,7) FROM PS_EXT_ORG_TBL B WHERE SUBSTR(B.EXT_ORG_ID,1,7) IN (SELECT A.EMPLID FROM PS_PERSONAL_DATA A WHERE A.EMPLID = SUBSTR(B.EXT_ORG_ID,1,7)))';
--                dbms_output.put_line('SELECT COUNT(*) FROM PS_' || rec.RECNAME || ' WHERE SUBSTR(' || rec.FIELDNAME || ',1,7) IN ');
            END IF;

            EXECUTE IMMEDIATE sqlStatement INTO rowCount;
            sqlStatement:= 'UPDATE PS_AMS_DUPID_RECNM SET ' || fldUpdate || ' = ' || rowCount || ' WHERE RECNAME = ''' || rec.RECNAME || ''' AND FIELDNAME = ''' || rec.FIELDNAME || '''';
            EXECUTE IMMEDIATE sqlStatement;

        EXCEPTION
            WHEN OTHERS THEN
            dbms_output.put_line('Error at analyseRowCount(): ' || sqlstatement);
        END;

    END LOOP;
END analyseRowCount;
/


------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


-- << MAIN PROGRAM. >>
DECLARE

    -- Duplicate IDs.
    CURSOR cDuplicateIDs IS
        SELECT
          EXT_ORG_ID
        FROM PS_EXT_ORG_TBL A
        WHERE A.EXT_ORG_ID IN
            (SELECT B.EXT_ORG_ID
            FROM PS_EXT_ORG_TBL B
            WHERE SUBSTR(B.EXT_ORG_ID,1,7) IN
                (SELECT C.EMPLID
                FROM PS_PERSONAL_DATA C
                WHERE C.EMPLID = SUBSTR(B.EXT_ORG_ID,1,7)))
        --and rownum < 4 -- << LOOK!!! used for building.
        ;

    -- Base tables.
    -- Tables that have EXT_ORG_ID field.
    CURSOR cBaseTables IS
        SELECT
          'Base' X
        , A.RECNAME RECNAME
        , A.FIELDNAME FIELDNAME
        FROM
          PSRECFIELD A
        , PSRECDEFN B
        WHERE A.FIELDNAME = 'EXT_ORG_ID'
        AND B.RECNAME = A.RECNAME
        AND B.RECTYPE = 0
        AND B.RECNAME NOT LIKE '%TMP'
        AND NOT EXISTS -- Not used as an audit record.
            (SELECT 1
            FROM PSRECDEFN
            WHERE AUDITRECNAME = RECNAME)
        ;

    CURSOR cTableList IS
        SELECT DISTINCT RECNAME, FIELDNAME, OCCURSCOUNT1 FROM PS_AMS_DUPID_RECNM
        ;

    sqlStatement VARCHAR2(300);
    rowCount INTEGER;
    newExtOrgId PS_EXT_ORG_TBL.EXT_ORG_ID%type;
    duplicateID INTEGER;

BEGIN

    -- Loop through the base tables that contain EXT_ORG_ID.
    FOR rec in cBaseTables
    LOOP

        -- Insert into working table a list of both the base tables and any tables that use those base tables as a prompt.
        -- These are the list of tables that potentially need to be updated.
        EXECUTE IMMEDIATE 'INSERT INTO PS_AMS_DUPID_RECNM (DESCR, RECNAME, FIELDNAME, OCCURSCOUNT1, OCCURSCOUNT2) VALUES (''' || rec.x || ''', ''' || rec.RECNAME || ''', ''' || rec.FIELDNAME || ''',0 ,0)';
        FindWhereRecUsedAsPrompt(rec.RECNAME, 1);

    END LOOP;

    -- Now we have a list of tables built we'll analyse how many rows we expect to update.
    -- PS_AMS_DUPID_RECNM.OCCURSCOUNT1 stores the number of rows that will be updated.
    analyseRowCount('OCCURSCOUNT1');

    -- Update the duplicate IDs (this is the really exciting part).
    For rec in cDuplicateIDs
    LOOP

        -- Create a new ID.
        duplicateID := 1;
        WHILE duplicateID <> 0
        LOOP
            SELECT EMPLID_LAST_EMPL + 1 INTO newExtOrgId FROM PS_INSTALLATION;
            EXECUTE IMMEDIATE 'SELECT count(*) FROM PS_PERSONAL_DATA WHERE EMPLID = ''' || newExtOrgId || '''' INTO duplicateID;
            IF duplicateID = 0
            THEN
                EXECUTE IMMEDIATE 'SELECT count(*) FROM PS_EXT_ORG_TBL WHERE EXT_ORG_ID = ''' || newExtOrgId || '''' INTO duplicateID;
                --dbms_output.put_line('duplicateID= ' || duplicateID || ', newExtOrgId= ' || newExtOrgId);
            END IF;
        END LOOP;
        UPDATE PS_INSTALLATION SET EMPLID_LAST_EMPL = newExtOrgId;

        -- Update the old value to the new value in every record that has at least one affected row.
        FOR recUpd in cTableList
        LOOP
            If recUpd.OCCURSCOUNT1 > 0 THEN

                If substr(recUpd.RECNAME, 1, 2) = 'PS'
                THEN
                    sqlStatement:= 'UPDATE ' || recUpd.RECNAME || ' SET ' || recUpd.FIELDNAME || ' = ''' || newExtOrgId || ''' WHERE ' || recUpd.FIELDNAME || ' = ''' || rec.EXT_ORG_ID || '''';
                ELSE
                    sqlStatement:= 'UPDATE PS_' || recUpd.RECNAME || ' SET ' || recUpd.FIELDNAME || ' = ''' || newExtOrgId || ''' WHERE ' || recUpd.FIELDNAME || ' = ''' || rec.EXT_ORG_ID || '''';
                END IF;

                BEGIN
                    EXECUTE IMMEDIATE sqlStatement;
                    --dbms_output.put_line(sqlStatement);
                EXCEPTION
                    WHEN OTHERS THEN
                    dbms_output.put_line('Error at (main program): ' || sqlstatement);
                END;

            END IF;
        END LOOP;

        -- Record the old and new values.
        EXECUTE IMMEDIATE 'INSERT INTO PS_AMS_DUPID_OLDNW (EXT_ORG_ID_OLD, EXT_ORG_ID) VALUES (''' || rec.EXT_ORG_ID || ''', ''' || newExtOrgId || ''')';

    END LOOP;

    -- Analyse the number of rows again. We expect 0.
    -- OCCURSCOUNT2 stores the number of rows post update/fix. Should be 0.
    analyseRowCount('OCCURSCOUNT2');

END;
/

-- Show the analysis/working tables.
select * from PS_AMS_DUPID_OLDNW;
SELECT * FROM PS_AMS_DUPID_RECNM where occurscount1 > 0;

Always Return one Row in Oracle SQL

SELECT Min(FIELD) FIELD
FROM SOME_TABLE
WHERE FIELD = 'X'

XML Publisher and Rowset Class

Example Class for an Application Package. Takes a Rowset and passes to the given Publisher Report and Template.

/********************************************************************************
SAMS_BRD220X Michael Nitschke 12/05/2011 XML Publisher
Common Class for XML Publisher

Users can use the method CreateXMLFilesFromRS to create only XML and XSD file,
very useful for XMLPublisher Template creation.

The PublishXMLReportFromRS method will create the XML Files and publish the given 
report.
********************************************************************************/
import PSXP_XMLGEN:*;
import PSXP_RPTDEFNMANAGER:*;

class XMLPublisher
   method XMLPublisher();
   method PublishXMLReportFromRS();
   method CreateXMLFilesFromRS();
   
   property Rowset rsData;
   property string sFileNameXSD;
   property string sFileNameXML;
   property string sRptDefn;
   property string sReportTemplate;
   property number processInstance;
   property string xmlFileName;
end-class;

method XMLPublisher
   /* Constructor. */
end-method;


/* Takes the rsData rowset property and uses it to publish a report, using other property values. */
method PublishXMLReportFromRS
   
   %This.CreateXMLFilesFromRS();
   
   /* Generate the report. */
   Local any &oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&sRptDefn); /* report definition name */
   &oRptDefn.Get();
   &oRptDefn.SetRuntimeDataXMLFile(%This.xmlFileName); /* Connect report and data. */
   &oRptDefn.ProcessReport(&sReportTemplate, %Language_User, %Date, ""); /* report's template ID */
   &oRptDefn.OutDestination = %FilePath;
   
   /* Publish to Report Manager. */
   &oRptDefn.Publish("", "", "", &processInstance);
   
end-method;

method CreateXMLFilesFromRS
   
   /* Create XSD file. */
   Local PSXP_XMLGEN:RowSetDS &oXML_GENERATOR = create psxp_xmlgen:RowSetDS();
   Local string &my_schema = &oXML_GENERATOR.getXSDSchema(&rsData);
   Local File &XSD_File = GetFile(&sFileNameXSD, "W");
   &XSD_File.WriteLine(&my_schema);
   &XSD_File.Close();
   
   /* Create XML file. */
   Local string &my_xml = &oXML_GENERATOR.getXMLData(&rsData, "");
   Local File &XML_File = GetFile(&sFileNameXML, "W");
   &XML_File.WriteLine(&my_xml);
   %This.xmlFileName = &XML_File.Name;
   &XML_File.Close();
   
end-method;

Creating Section Breaks in XML Publisher/BI Publisher

Rather than using for-each:GROUP_NAME you use for-each@section:GROUP_NAME, every time xmlp hits a new member of GROUP_NAME the page numbering and header is reset.

(from www.orafaq.com forums)

Setting Up BI Publisher/XML Publisher

This can be fun. I had a lot of trouble with an error message stating I couldn't install the plug-in while Word was running. Shut down process after process to no avail.

Solution:
1. Download to your desktop the install file from Main Menu > Reporting Tools > XML Publisher > Setup > Design Helper.
2. Restart the machine in Safe Mode and install.

I also encountered further problems when trying to use some of the features such as the Chart object. Reinstall BI Publisher by navigating to: C:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\DotNetInstallFiles\setup.exe and following the prompts (next, ok, next, yes etc.)

XML / BI Publisher Example, using XML File as the Data Source

Note that using a Data Source of "XMLDoc" or "RowSet" has been deprecated. If you are going to create a new XML Publisher Report you are going to have to use a data source of "XML File" or "PS Query".

This is probably a good way of going about it anyway because you need to create the XML and XSD files before you can create the template anyway (unless you want to make them by hand).

Following is a simple example that could use some flourish, but illustrates the method clearly.

/* Super simple example of creating a XML Publisher report based on RowSet data.
   Note that producing reports from RowSet data or XMLDoc data has been deprecated.
   This example uses a Data Source of 'XML File'. */

import PSXP_XMLGEN:*;
import PSXP_RPTDEFNMANAGER:*;


/* Create Rowset(s) data for the report, however you need. */
Local Rowset &rsData = CreateRowset(Record.AMS_SOA_PROG_VW);
&rsData.Fill("WHERE EMPLID LIKE '3064%'");


Local string &sFileNameXSD = "Rowset.xsd";
Local string &sFileNameXML = "Rowset.xml";
Local string &sRptDefn = "AMS_SOA_XML";
Local string &sReportTemplate = "AMS_SOA_XML_1";

/* Create XSD file. */
Local PSXP_XMLGEN:RowSetDS &oXML_GENERATOR = create psxp_xmlgen:RowSetDS();
Local string &my_schema = &oXML_GENERATOR.getXSDSchema(&rsData);
Local File &XSD_File = GetFile(&sFileNameXSD, "W");
&XSD_File.WriteLine(&my_schema);
&XSD_File.Close();

/* Create XML file. */
Local string &my_xml = &oXML_GENERATOR.getXMLData(&rsData, "");
Local File &XML_File = GetFile(&sFileNameXML, "W");
&XML_File.WriteLine(&my_xml);
Local string &xmlFileName = &XML_File.Name; /* File.Name not available after File.Close() */
&XML_File.Close();


/* Generate the report. */
&oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&sRptDefn); /* report definition name */
&oRptDefn.Get();
&oRptDefn.SetRuntimeDataXMLFile(&xmlFileName); /* Connect report and data. */
&oRptDefn.ProcessReport(&sRptTemplate, %Language_User, %Date, ""); /* report's template ID */
&oRptDefn.OutDestination = %FilePath;

/* Publish to Report Manager. */
&oRptDefn.Publish("", "", "", AMS_SOA_AET.PROCESS_INSTANCE);

Creating XML Publisher Documents with PeopleCode

Uploaded to own site in case it ever disappears from Oracle (or moves)
Nice doco on how to create XML from PeopleCode to use in BI Publisher (XML Publisher) or what have you.

Creating XML Publisher Documents with PeopleCode

My New View

View Larger Map

Run Control in App Engine

The Default State Record must be built as an SQL Table. This is not documented (I think).

You can then use the Run Control by binding as in the following example. If it is a Derived Work table, not an SQL Table, the %Bind value will be Null.