Notes from PeopleSoft

Redirecting a User after Login / Logon

2012-02-13T14:35:00.000+10:00

This situation can be common in places like Universities. Students need to be redirected after login to some 'special' page, or an employee needs to be redirected if it is their first login, etcetera.

You could be forgiven for thinking this might be easily configurable somewhere. It doesn't seem to be (leave me a message if it is). Here's how I've done it.

I've modified:
AppPackage.PT_BRANDING, method GetIScriptHPPoweredByFooterHTML()

Now, I must say that at this site the Method has been customised to return a custom HTML object, with the company's own footer. Nonetheless it is a reasonable place to start looking for a location to insert the JavaScript redirect based on the type of user. The good thing here is you have full access to all the PeopleCode methods and functions and dance moves, unlike in Login PeopleCode where you are restricted, and kinda tied up in knots.

Also note: I'm sure there is a better, more elegant way to do this, probably insert JavaScript up in the header somewhere, and I've probably modified this to do that by the time you read this. I'll endeavour to come back and append this post. If not, this is a good starting point and it does actually work.

Anyway, you want to modify the footer HTML that is generated to append a JavaScript redirect to the end of it, based on some condition. Something like:

method GetIScriptHPPoweredByFooterHTML
   /+ Returns String +/;
   Local string &PoweredByHTML;

/* If a special user type then redirect */
If &userType = "X" Then
   &PoweredByHTML = GetHTMLText(HTML.PORTAL_HP_POWEREDBY, %Response.GetImageURL(Image.PT_PORTAL_POWEREDBY), MsgGetText(95, 434, "Powered by Oracle") | "<script type=""text/javascript"">window.top.location.replace(""../c/SOME_MENU.SOME_COMPONENT.GBL"");</script>");
Else
   &PoweredByHTML = GetHTMLText(HTML.PORTAL_HP_POWEREDBY, %Response.GetImageURL(Image.PT_PORTAL_POWEREDBY), MsgGetText(95, 434, "Powered by Oracle"));
End-If;

   Return &PoweredByHTML;
end-method;

Data Integrity Error

2012-02-01T13:36:00.002+10:00

Data Integrity Error (124,85)
The data structure from the page does not match the data structure in the database. This can be caused by... yada yada

This error usually appears after you've made some changes to PeopleCode or similar, and you haven't refreshed the page yet. As a developer you see it all the time and just reload the Component.

However, on some rare occasions it appears out of nowhere for a given Component. And it won't go away. And there's no reason for it.

Run a trace file for each SQL statement.

Search the trace file for 'Deserialization integrity failure'

Some googling about the deserialization error suggested we clear the Application Server cache and restart it. If that failed run the AppEngine VERSION. This app engine looked, at a glance, to be restting the VERSION number of some major Tables back to 1. I didn't want to run this without knowing what all this VERSION stuff is.

Solution: I opened the DERIVED_GBL Record and modified the comment. Saved. Fixed.

I think this was the least intrusive approach.

If you can expand on what the issue is and how to "correctly" fix it please leave a note below. Much appreciated.

Component Menu Path SQL

2012-02-01T09:14:00.004+10:00

Graciously lifted from Jim's PeopleSoft Journal (check out his book too).
http://jjmpsj.blogspot.com.au/2007/12/query-for-component-andor-cref.html

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 )

Custom Homepage Pagelets / Custom Navigation Collections

2012-01-30T11:40:00.002+10:00

Great article:
http://peoplesofttipster.com/2009/04/27/applying-homepage-pagelets-to-others/

Change Font in SQR

2012-01-23T15:44:00.001+10:00

Sometimes the easiest things...

begin-program
alter-printer font=4 ! hp laser, helvetica
...

Reset Page Numbers in XML Publisher (XMLP)

2011-12-21T13:25:00.001+10:00

@section:row_AMS_SOA_PROG_T?>That's it.

Database creation date time

2011-12-15T15:05:00.001+10:00

select created from v$database;

Hiding Elements - Printable Version of a Page

2011-11-01T10:05:00.002+10:00

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.

Add a button or link to your page.
Under the Push Button/Hyperlink Page Properties setup as follows:
Destination = 'External Link'
External Link = 'Dynamic'
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.
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)

2011-09-21T20:07:00.001+10:00

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)

2011-08-22T18:40:00.001+10:00

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

2011-08-17T11:23:00.000+10:00

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

2011-08-12T15:31:00.000+10:00

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

2011-08-09T19:03:00.000+10:00

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

2011-07-27T17:56:00.001+10:00

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.

2011-07-14T11:50:00.001+10:00

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

2011-07-06T10:57:00.000+10:00

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

2011-06-27T16:40:00.002+10:00

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 Path to a Component

2011-06-20T11:03:00.000+10:00

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

2011-06-02T10:45:00.000+10:00

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

2011-05-31T17:44:00.002+10:00

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

2011-05-25T15:56:00.000+10:00

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

XML Publisher and Rowset Class

2011-05-18T16:40:00.001+10:00

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

2011-05-11T12:32:00.000+10:00

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

2011-05-09T17:00:00.000+10:00

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

2011-05-02T15:26:00.002+10:00

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

2011-04-29T13:57:00.000+10:00

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

2011-04-14T13:23:00.000+10:00

View Larger Map

Run Control in App Engine

2011-03-04T16:54:00.001+10:00

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.

Style Sheet Gotchas

2010-12-04T07:22:00.003+10:00

So, you've created your own style sheet class to change the border colour of a Frame or Group Box to red. But, when you view the page rather than a red border you get no border. What is going wrong?
If you look at the HTML source you'll see your frame, which is actually a HTML Table, written there with your new class. Something like:

[table class="REDBORDER" style="border:none"]

Why is it hiding your borders?
Look above and you'll see another table:

[table class="REDBORDERWBO"...

What is this?

On the page definition in Application Designer you can turn a Frame's border on/off. It is in the properties for each Frame object. PeopleSoft adds another Table around your Frame's Table that is used to handle the on/off (hidden) state of the border. If the Frame border is on you'll see the wrapper table with class xxxWBO (with border) and if off you'll see the wraper table with class xxxNBO (no border).
Your custom class definition, in this example, also needs the classes REDBORDERWBO and REDBORDERNBO to handle the on/off state of the Frame border. Same goes for Group Boxes.

Who is SamDestroy?

2010-11-11T15:08:00.000+10:00

To get around the SamDestroy error when comparing projects from file go to Config Manager, Remote Call/AE Tab, and check the Shared Flags "Disable DB Stats" checkbox and you should be fine.

This seems to only be an issue for DB2.

James Court Medina Apartments, Canberra

2010-11-04T08:13:00.003+10:00

As an I.T. contractor I am required to work in different cities, sometimes 3 different contracts/year and 3 different cities.

I am writing a short review of James Court Medina apartments in Canberra to partially vent my frustration, and I know Google will probably pick up this post : )

I did some thinking last night, all the places I had ever lived in, including my first slum/sharehouse as a teenager. James Court Medina is actually the worst apartment I have ever lived in, ever. Including addresses in Russia. DO NOT LIVE HERE. At least my slums had one or two appealing factors, like the beach being across the road or price. They all had a working shower.

1. Location. You will not sleep. Take a look at the location on a map. That is the intersection of two major roads. The car noise is actually fine, it is the trucks that will rob you of any sleep. They compression brake in to those traffic lights all morning, starting at about 4am. BRRRRRRR-rrrrrr-RRRRRR-SQUEEEEEEEEK. You will jump awake the first few times. It's best to go to bed around 8pm in anticipation of being woken very early.

2. Heating. I arrived here in Spring and even then the heater in the living room, a split system from the late 80's, was insufficient to heat the room. I spent weeks watching television in thermals and a beanie. I'd hate to see what it's like in winter. You could turn the fridge off I'm sure.

3. Condition. Nothing has been updated since it was built in the late 80's early 90's. I opened my windows one afternoon and now two weeks later they are still stuck open. I'm still waiting for them to be fixed. This is just lovely at night when a 6 degree wind blows through, My lounge room is now like a fridge. Also, as I mentioned, NO maintenance has been done since the aprtments were built. I had to decalcify the shower heads myself.

4. Price. Seriously WTF? Look elsewhere. I know for a fact that for an extra $50/week you can have lake views and a marble kitchen. The only reason I moved in here was because I was extremely unlucky with my timing, and there was nothing else available. I regret my decision.

To Summarise: LOOK ELSEWHERE. DO NOT LIVE AT James Court Medina, Canberra.

P.S. The fire alarm went off yesterday afternoon. I could hear it from the lounge room but I could not hear it from the bedroom, even though I knew it was ringing. Dodgy.

Inserting Random Test Data

2010-08-10T09:34:00.003+10:00

INSERT INTO PS_SOME_TEST_TBL
SELECT EMPLID, 'Test ID' 
FROM
    (SELECT EMPLID FROM PS_JOB
    ORDER BY DBMS_RANDOM.VALUE)
WHERE ROWNUM < 101;

Component Arrays - Caching

2010-07-22T17:31:00.000+10:00

One Application Engine performance tip I found was to place the results of commonly used queries into Component level arrays that could be searched through on the Application Server instead of making a call to the database. Some code was triggered that many times the DB calls numberd in millions and all the round-trips were expensive.

The following creates component arrays that store the results for a common query, in this case PIN_NUM and PIN_NM from the GP_PIN table.

/* Create a set of global look-up arrays. 
   This will remove the need to call the db many, many times. 

  See CC_FUNCLIB_UOA.LOOKUP.FieldFormula for how to call/use. */

Component array of string &arrPinNumIndex;
Component array of number &arrPinNumVal;

Component array of number &arrPinNmIndex;
Component array of string &arrPinNmVal;

Local SQL &sql;
Local string &pinNm;
Local integer &pinNum, &i;

/* PIN_NUM Lookup: */
&arrPinNumIndex = CreateArrayRept("", 0);
&arrPinNumVal = CreateArrayRept(0, 0);

&sql = CreateSQL("SELECT PIN_NM, PIN_NUM FROM %Table(GP_PIN) ORDER BY 1");
&i = 1;
While &sql.Fetch(&pinNm, &pinNum)
   &arrPinNumIndex [&i] = &pinNm;
   &arrPinNumVal [&i] = &pinNum;
   &i = &i + 1;
End-While;


/* PIN_NM Lookup: */
&arrPinNmIndex = CreateArrayRept(0, 0);
&arrPinNmVal = CreateArrayRept("", 0);

&sql = CreateSQL("SELECT PIN_NUM, PIN_NM FROM %Table(GP_PIN) ORDER BY 1");
&i = 1;
While &sql.Fetch(&pinNum, &pinNm)
   &arrPinNmIndex [&i] = &pinNum;
   &arrPinNmVal [&i] = &pinNm;
   &i = &i + 1;
End-While;

There is a Function that can be called to interrogate the arrays for the value you are searching for:

<* Search the global array that stores cached lookup values from database.
   This removes (a lot of) roundtrips between app server and db server. 

   Example of use:

     /* previously populated arrays - index and value. */
     Component array of string &arrPinNumIndex;
     Component array of number &arrPinNumVal;

     Declare Function GetCached PeopleCode CC_FUNCLIB_UOA.LOOKUP FieldFormula;
     &x = GetCached("ZACC ALA", &arrPinNumIndex, &arrPinNumVal);

   i.e. Pass it a pin name and two, synchronised arrays.
   The first array contains the index, the second contains values that are returned.
   Obviously only works on "2 dimensional arrays"

*>



Function BinarySearch(&arr, &srchVal, &low As integer, &high As integer) Returns integer
   
   If (&high < &low) Then
      Return - 1; /* not found */
   End-If;
   
   &mid = &low + ((&high - &low) / 2);
   If &arr [&mid] > &srchVal Then
      Return BinarySearch(&arr, &srchVal, &low, &mid - 1);
   Else
      If &arr [&mid] < &srchVal Then
         Return BinarySearch(&arr, &srchVal, &mid + 1, &high);
      Else
         Return &mid; /* found */
      End-If;
   End-If;
   
End-Function;

Function GetCached(&srch, &arrIndex, &arrVal) Returns any
   
   &i = BinarySearch(&arrIndex, &srch, 1, &arrIndex.len);
   If &i = - 1 Then
      Return 0; /* not found */
   Else
      Return &arrVal [&i];
   End-If;
   
End-Function;

To use them try the following code:

/* Lookup array arrays: */
Component array of string &arrPinNumIndex;
Component array of number &arrPinNumVal;

&x = GetCached("BASE PAY", &arrPinNumIndex, &arrPinNumVal);

Commitments / Encumbrances

2010-06-29T10:35:00.001+10:00

After building this twice now, both while specs were being written (sigh) I suggest the following approach.

Multiple App Engines.

Main: Contains main outer loop(s)
Grouping Code: Contains calls to Element AEs
Element: Contains Section for each Element calculation.
Functions: Sections for populating component arrays and the like
Population: AE that deals only with splitting, account code mapping and population of temp tables.

... something lik ethat. Would make things more legible, pluggable.

The need for an environment that has accurate pay run data to compare against cannot be stressed enough, preferably dev. The lag between code changes and migrations and runs in downstream envs that have comparable data can be days, very slow and confusing.

Application Engine Performance

2010-06-23T12:21:00.000+10:00

1. Reduce writing to log or trace files. I've seen this add 100%+ to processing times. Really.

2. Reduce calls to DB. If possible use Commponent arrays instead of calling the db 1,000,000 times. Searching throug hsmall arrays i far quicker, usually because of the network, not the DB.

Progress Bar for Processes

2010-06-16T15:28:00.002+10:00

1. Create an HTML area on a page where you are going to query the progress of a Process.

2. You need to setup a "progress" table for your process. You will need to update the values from within the process while it is running.

e.g. fields EMPLOYEES_TOTAL | EMPLOYEES_PROCESSED etc.

You will probably have to run a precursory SQL to count how many rows are expected to be processed up front, and then update the progress as each row or set of rows is processed.

3. Here is some sample code to update the HTML area with details of how the process is progressing. CC_DERIVED_UOA.DESCRLONG is the HTML Area on the page.

Function UpdateProgressBar(&title, &complete, &total) Returns string
   
   Local string &str = "";
   &str = &str | "   <tr><td style=""font-size:small"">" | &title | ":&nbsp;</td>";
   
   /* Calc pct complete. */
   If &total > 0 Then
      &pct = (&complete / &total) * 10;
   Else
      &pct = 10;
   End-If;
   
   /* Update bars. */
   For &i = 1 To 10
      If &pct >= &i Then
         If &total = 0 Then
            &str = &str | "      <td BGCOLOR=""#CCCCCC"">&nbsp;</td>"; /* grey */
         Else
            &str = &str | "      <td BGCOLOR=""#AAFFAA"">&nbsp;</td>"; /* green */
         End-If;
      Else
         &str = &str | "      <td BGCOLOR=""#FFAAD4"">&nbsp;</td>"; /* red */
      End-If;
   End-For;
   
   &str = &str | "      <td style=""font-size:x-small"">&nbsp;&nbsp;" | &complete | " / " | &total | "</td>";
   &str = &str | "   </tr>";
   
   Return &str;
   
End-Function;



Local Rowset &rsCC_PROGRESS_UOA = CreateRowset(Record.CC_PROGRESS_UOA);
&rsCC_PROGRESS_UOA.Fill(); /* only ever one row */
Local Record &recCC_PROGRESS_UOA = &rsCC_PROGRESS_UOA(1).GetRecord(Record.CC_PROGRESS_UOA);

&strl_progbar = &strl_progbar | "<table>";
&strl_progbar = &strl_progbar | UpdateProgressBar("Employees", &recCC_PROGRESS_UOA.CC_FULLTM_COMPLETE.Value, &recCC_PROGRESS_UOA.CC_FULLTM_TOTAL.Value);
&strl_progbar = &strl_progbar | UpdateProgressBar("Casuals", &recCC_PROGRESS_UOA.CC_CASUAL_COMPLETE.Value, &recCC_PROGRESS_UOA.CC_CASUAL_TOTAL.Value);
&strl_progbar = &strl_progbar | UpdateProgressBar("Scholarships", &recCC_PROGRESS_UOA.CC_SCHOL_COMPLETE.Value, &recCC_PROGRESS_UOA.CC_SCHOL_TOTAL.Value);
&strl_progbar = &strl_progbar | "</table><br>";

CC_DERIVED_UOA.DESCRLONG.Value = &strl_progbar;

Filter Retro Payments

2010-06-15T14:09:00.000+10:00


SELECT
 (select pin_nm from ps_gp_pin where pin_num = a.pin_num) AS PIN_NM
, A.EMPLID
, A.EMPL_RCD
, A.PIN_NUM
, Sum(A.CALC_RSLT_VAL)
, A.RATE_RSLT_VAL
, Sum(A.UNIT_RSLT_VAL)
, Sum(A.PCT_RSLT_VAL)

FROM 
  PS_GP_RSLT_ERN_DED A
, PS_GP_PYE_PRC_STAT C

WHERE A.PIN_NUM = 13265 --#PinNum
AND A.EMPLID = '1000269' --$Emplid
AND A.EMPL_RCD = 0 --#Empl_Rcd
AND A.CAL_RUN_ID = '2010F12' --$Calendar_ID

AND C.EMPLID = A.EMPLID
AND C.CAL_RUN_ID = A.CAL_RUN_ID
AND C.EMPL_RCD = A.EMPL_RCD
AND C.GP_PAYGROUP = A.GP_PAYGROUP
AND C.CAL_ID = A.CAL_ID
AND C.ORIG_CAL_RUN_ID = A.ORIG_CAL_RUN_ID
AND C.PRD_TYPE = 'C'

As Of Date in Oracle SQL - Back to the Future

2010-05-14T14:36:00.000+10:00

This works:


select *
from
   (select * from ps_job where emplid = '123')
AS OF TIMESTAMP SYSDATE - 1;

Binary Search

2010-05-14T09:53:00.001+10:00

Example of:

Function BinarySearch(&arr, &srchVal, &low As integer, &high As integer) Returns integer
  
   If (&high < &low) Then
      Return - 1; /* not found */
   End-If;
  
   &mid = &low + ((&high - &low) / 2);
   If &arr [&mid] > &srchVal Then
      Return BinarySearch(&arr, &srchVal, &low, &mid - 1);
   Else
      If &arr [&mid] < &srchVal Then
         Return BinarySearch(&arr, &srchVal, &mid + 1, &high);
      Else
         Return &mid; /* found */
      End-If;
   End-If;
  
End-Function;

2010-05-06T16:53:00.004+10:00

I don't often post random snippets of SQL, but I thought this was a good one. Rare enough to be able to forget it easily, and perhaps will be required again.

This returns schedules based on the schedule id(s) that is/are current for a given employee during a given payment period. If there is no schedule id default to 'UOA01'.

The first part was tricky as sch_assign is an effective dated table, and has no required end date, but each row does end when the next effective date comes into effect. I solved that using a subselect to derive the end_dt. Easy enough.

The next part, defaulting in 'UOA01' was a little trickier. A union to always bring in UOA01, but then a not exists subselect to only bring in where part A of the union returned nothing.

Perhaps easy when you see the solution first, this one took me quite a few attempts, and half the afternoon. Saved for prosperity and that time in the future when I ask myself "I remember doing similar...".

Apart from doing some tricky things with dates this SQL always returns a value. There is a default value in the SQL.

select
  %DateOut(dur)
, b.sched_hrs
from
    (select
      a.schedule_id
    , a.effdt as bgn_dt
    ,
        (select min(x.effdt) - 1
        from ps_sch_assign x
        where x.emplid = a.emplid
        and x.empl_rcd = a.empl_rcd
        and x.effdt < a.effdt) as end_dt
    from %Table(sch_assign) a
    where a.emplid = %Bind(cc_a20_uoa_aet.emplid)
    and a.empl_rcd = %Bind(cc_a20_uoa_aet.empl_rcd)
    and a.schedule_id <> ' '
    and a.effdt < %Bind(cc_a20_uoa_aet.prd_end_dt)
    union
    select 
      'UOA01'
    , to_date('01/01/2000', 'dd/mm/yyyy') as bgn_dt
    , to_date('31/12/9999', 'dd/mm/yyyy') as end_dt
    from dual
    where not exists
        (select 1
        from %Table(sch_assign) a
        where a.emplid = %Bind(cc_a20_uoa_aet.emplid)
        and a.empl_rcd = %Bind(cc_a20_uoa_aet.empl_rcd)
        and a.schedule_id <> ' '
        and a.effdt < %Bind(cc_a20_uoa_aet.prd_end_dt))) a
, %Table(sch_clnd_vw) b
where b.schedule_id = a.schedule_id
and b.dur between %Bind(cc_a20_uoa_aet.prd_bgn_dt) and %Bind(cc_a20_uoa_aet.prd_end_dt)
and (b.dur <= a.end_dt or a.end_dt is null)
and b.sched_hrs > 0
order by dur

HP Quality Center (QC)

2010-03-30T16:09:00.005+10:00

In case I'm ever asked to export from QC again.


/*
Built for Jira 26921
Michael Nitschke 30/03/2010

Examples:
define testPlanRoot = 'SA Master'
define testLabRoot = 'System Testing\SYS1 SA'

define testPlanRoot = 'MTP Testing'
define testLabRoot = 'MTP Testing\MTP 13'

Hint: you can optionally add a trailing back-slash on the testLabRoot variable if you
need to clearly define it as seperate from and similarly named folder,
e.g. 'SYSTEM' and 'SYSTEM1'.
*/

/* notes:
Test Lab runs do not neccessarily line up in the same folders in Test Lab as the folders that are in Test Plan ...if that makes sense.
Joined by TEST_ID but the Test Lab folder Name can be changed, so the lab results for a given test plan may be in a test lab folder that has a different name to the plan.
hard to explain.
*/

define testPlanRoot = 'SA Master'
define testLabRoot = 'System Testing\System Test Phase 1 - SA\'


select * from (

SELECT
  substr(path, 2, instr(path,'\',1,2) - 2) as lev1
, substr(path, instr(path,'\',1,2) + 1, instr(path,'\',1,3) - instr(path,'\',1,2) - 1) as lev2
, substr(path, instr(path,'\',1,3) + 1, instr(path,'\',1,4) - instr(path,'\',1,3) - 1) as lev3
, substr(path, instr(path,'\',1,4) + 1, instr(path,'\',1,5) - instr(path,'\',1,4) - 1) as lev4
, substr(path, instr(path,'\',1,5) + 1, instr(path,'\',1,6) - instr(path,'\',1,5) - 1) as lev5
, substr(path, instr(path,'\',1,6) + 1, instr(path,'\',1,7) - instr(path,'\',1,6) - 1) as lev6
, nvl(users.full_name, 'anon.') || ' (' || test.ts_responsible || ')' as sweet_fa
, test.ts_name as test_script_name
, test.ts_status
, run.rn_status

from (
 select
 al_father_id, al_item_id, sys_connect_by_path(al_description, '\') || '\' as path
 FROM (SELECT * from all_lists where al_father_id <> 2329) -- 2329 is a 'bad', invisible folder. Yes really.
 connect by prior al_item_id = al_father_id
) path
, test
, users
,
 (select run.rn_test_id, run.rn_status
 from run
 where exists
  (select 1
  from cycle
  where cycle.cy_cycle_id = run.rn_cycle_id
  and cycle.cy_folder_id in
   (select cf_item_id from
    (select cf_item_id, sys_connect_by_path(cf_item_name, '\') as path
    from cycl_fold a
    connect by prior cf_item_id = cf_father_id)
   where path like '\' || &testLabRoot || '%'))
 and run.rn_execution_date =
  (select max(r.rn_execution_date)
  from run r
  where r.rn_test_id = run.rn_test_id)
 and run.rn_execution_time =
  (select max(rn_execution_time)
  from run r
  where r.rn_test_id = run.rn_test_id
  and r.rn_execution_date = run.rn_execution_date)) run


where path.path like '\' || &testPlanRoot || '%'

and test.ts_subject (+) = path.al_item_id
and users.user_name (+) = test.ts_responsible

and run.rn_test_id (+) = test.ts_test_id

)
where lev1 = &testPlanRoot -- Removes data where same folder name exists on another branch.
and not lev2 is null -- Don't need base folder only in data (blanks).
and not lev3 is null
order by 1, 2, 3, 4, 5, 6, 8
;

%DateIn or %DateOut

2010-03-17T14:52:00.005+10:00

Sometimes you need both:

%Select(CC_DAY_UOA_AET.DATE_DAY, CC_DAY_UOA_AET.SCHED_HRS)

SELECT
%DateOut(%DateIn(%Bind(CC_PYDT_UOA_AET.PYMT_DT)) + daynum)
, sched_hrs
FROM ps_sch_defn_dtl a
...

Extract Data from Quality Center (QC) 9.2 - SQL

2010-02-19T13:11:00.008+10:00

Quality Center is a testing suite used at a few installations. It comes with a collection of reports, including its own report generator.

Nonetheless it is nice to be able to get the RAW data and do with it what you will.

-- Extract ALL Test Plan Data:
SELECT
Replace(A.PATH, ' \\Subject\System Tests\SA System Test\', '') AS PATH
, B.TS_NAME
, C.DS_STEP_NAME
, REPLACE(REPLACE(REPLACE(REGEXP_REPLACE(REPLACE(C.DS_DESCRIPTION, '
', Chr(13)), '<(.|\n)+?>', ''), '<', '<'), '>', '>'), '"', Chr(34)) AS DS_DESCRIPTION
, REPLACE(REPLACE(REPLACE(REGEXP_REPLACE(REPLACE(C.DS_EXPECTED, '
', Chr(13)), '<(.|\n)+?>', ''), '<', '<'), '>', '>'), '"', Chr(34)) AS DS_EXPECTED
FROM
(SELECT AL_ITEM_ID, LPAD(' ', 2 * LEVEL-1) || SYS_CONNECT_BY_PATH(AL_DESCRIPTION, '\') AS PATH
FROM ALL_LISTS
CONNECT BY PRIOR AL_ITEM_ID = AL_FATHER_ID) A
, TEST B
, DESSTEPS C
WHERE A.PATH LIKE '%\\Subject\System Tests\SA System Test\GRADUATIONS%'
AND B.TS_SUBJECT = A.AL_ITEM_ID
AND C.DS_TEST_ID = B.TS_TEST_ID
ORDER BY A.PATH, B.TS_NAME, C.DS_STEP_ORDER
;

Note I've done some stripping of HTML tags using a combination of Replace() and a regular expression.

We dumped a larger set of the above results into a spreadsheet, connected to it through Access and wrote out all our UAT scripts as a report.

You can also join to the Test Lab to get the Test Results out. Note that it is joined by TEST_ID so the Lab folders may not line up with the Plan folders, you can change folder names etc.

/*
Built for Jira 26921
Michael Nitschke 30/03/2010

Examples:
define testPlanRoot = 'SA Master'
define testLabRoot = 'System Testing\SYS1 SA'

define testPlanRoot = 'MTP Testing'
define testLabRoot = 'MTP Testing\MTP 13'

Hint: you can optionally add a trailing back-slash on the testLabRoot variable if you
need to clearly define it as seperate from and similarly named folder,
e.g. 'SYSTEM' and 'SYSTEM1'.
*/

/* notes:
Test Lab runs do not neccessarily line up in the same folders in Test Lab as the folders that are in Test Plan ...if that makes sense.
Joined by TEST_ID but the Test Lab folder Name can be changed, so the lab results for a given test plan may be in a test lab folder that has a different name to the plan.
hard to explain.
*/

select * from (

SELECT
  substr(path, 2, instr(path,'\',1,2) - 2) as lev1
, substr(path, instr(path,'\',1,2) + 1, instr(path,'\',1,3) - instr(path,'\',1,2) - 1) as lev2
, substr(path, instr(path,'\',1,3) + 1, instr(path,'\',1,4) - instr(path,'\',1,3) - 1) as lev3
, substr(path, instr(path,'\',1,4) + 1, instr(path,'\',1,5) - instr(path,'\',1,4) - 1) as lev4
, substr(path, instr(path,'\',1,5) + 1, instr(path,'\',1,6) - instr(path,'\',1,5) - 1) as lev5
, substr(path, instr(path,'\',1,6) + 1, instr(path,'\',1,7) - instr(path,'\',1,6) - 1) as lev6
, nvl(users.full_name, 'anon.') || ' (' || test.ts_responsible || ')' as sweet_fa
, test.ts_name as test_script_name
, test.ts_status
, run.rn_status

, test.ts_user_06 as Business_Owner
, test.ts_user_07 as Technical_Owner
, test.ts_user_04 as HR_Func_Area
, test.ts_user_05 as Tester
, test.ts_user_09 as Date_Time
, test.ts_user_08 as Location
, test.ts_user_02 as CBS_Test_Effort
, test.ts_user_01 as Summary
, test.ts_user_03 as CR_ID

from (
 select
 al_father_id, al_item_id, sys_connect_by_path(al_description, '\') || '\' as path
 FROM (SELECT * from all_lists where al_father_id <> 2329) -- 2329 is a 'bad', invisible folder. Yes really.
 connect by prior al_item_id = al_father_id
) path
, test
, users
,
 (select run.rn_test_id, run.rn_status
 from run
 where exists
  (select 1
  from cycle
  where cycle.cy_cycle_id = run.rn_cycle_id
  and cycle.cy_folder_id in
   (select cf_item_id from
    (select cf_item_id, sys_connect_by_path(cf_item_name, '\') as path
    from cycl_fold a
    connect by prior cf_item_id = cf_father_id)
   where path like '\' || &testLabRoot || '%'))
 and run.rn_execution_date =
  (select max(r.rn_execution_date)
  from run r
  where r.rn_test_id = run.rn_test_id)
 and run.rn_execution_time =
  (select max(rn_execution_time)
  from run r
  where r.rn_test_id = run.rn_test_id
  and r.rn_execution_date = run.rn_execution_date)) run


where path.path like '\' || &testPlanRoot || '%'

and test.ts_subject (+) = path.al_item_id
and users.user_name (+) = test.ts_responsible

and run.rn_test_id (+) = test.ts_test_id

)
where lev1 = &testPlanRoot -- Removes data where same folder name exists on another branch.
and not lev2 is null -- Don't need base folder only in data (blanks).
and not lev3 is null
order by 1, 2, 3, 4, 5, 6, 8
;

SQL - Recursive Heirarchy Trees

2010-02-18T16:23:00.003+10:00

Assuming a table something like this:

PERSON_ID
PARENT_ID
NAME

Start with this.

SELECT
LPAD(' ', 2*level-1) || SYS_CONNECT_BY_PATH(NAME, '\') "Path"
FROM TABLE_NAME
CONNECT BY PRIOR PERSON_ID = PARENT_ID;

Google "Connect By" for more information.

Page data is inconsistent with database (18,1)

2010-01-29T11:23:00.005+10:00

"Page data is inconsistent with database (18,1)"

I recently came across this during a call to a Component Interface, CI.HCR_PERSONAL_DATA_SRV, that interfaced with a rather large Component, Component.PERSONAL_DATA.

(Interestingly when I tested the CI stand-alone it worked fine, never figured out why/no time.)

I created a Project that contained *all the objects for Component.PERSONAL_DATA. This can take some time if each page has sub-pages, and in turn they too have sub-pages. Be sure to include each record for each page, and each record's associated PeopleCode.
See this post about how to do a lot of this via a SQL script or similar.

I first checked that all the tables and views for the component had been built correctly after a recent patch. They were fine.

Next I checked PeopleCode, namely SavePreChange and SavePostChange as these are the events most likely to cause the given error. Recall that this error happens when data in the buffer pre save and data in the database post save do not match. I simply went through each piece of code and remarked out likely looking code or entire sections...

...until the error stopped occuring. This pointed me to some custom code on SavePost that was updating a row via SQLExec on table x, new delivered code from the patch was updating table x in the buffer in SavePre. Thus the error. All-in-all took me a lot longer than it took to write this post.

Inserting PeopleCode into Project via Script

2010-01-26T15:41:00.002+10:00

Sometimes you want to do big compares between environments, and you've got a rather large component, say Component.PERSONAL_DATA, and you've gone through and manually inserted all the pages, subpages, subpages-subpages and so on, including all the Records.

But doing this hasn't automatically inserted all the Record PeopleCode! Yuck, you have to go through each record... and ... no!!!

Now I've written this I can see it wouldn't be much of a leap to expand on this script to be able to just pass in a Component name. I'll leave that to you.

This script will insert all Record PeopleCode for all the Records in a given Project. Note you may have to clear you PeopleTools cache after you've run the script to see it in the given Project.

INSERT INTO PSPROJECTITEM
(SELECT DISTINCT
'PROJECT_NAME', 8
, OBJECTID1, OBJECTVALUE1, OBJECTID2, OBJECTVALUE2, OBJECTID3, OBJECTVALUE3, OBJECTID4, OBJECTVALUE4
, 0, 0, 0, 0, 1, 0
FROM PSPCMPROG
WHERE OBJECTVALUE1 IN
(SELECT OBJECTVALUE1
FROM PSPROJECTITEM
WHERE PROJECTNAME = 'PROJECT_NAME'
AND OBJECTTYPE=0));

PeopleSoft 9.1 and PeopleTools 8.50

2010-01-16T16:39:00.005+10:00

What's new? Quite a lot. The first ten minutes is a summary of how well Oracle/PeopleSoft is doing. Skip this for a demonstration of the new technology, namely DHTML and AJAX. It is really nice to finally see some examples in an ERP. Doesn't appear to be much in the way of PeopleCode though, not like the jump to 8 for example.

Click here for webinar.

Exporting or Saving App Engine as Text

2009-11-04T14:05:00.007+10:00

You can export an entire App Engine as text, which makes it easier to search through for any relevant code, for example where a table is used.

1. Open the App Engine
2. File/View Report
3. File/Save Report As...

Presto.

Understanding the USEEDIT field in PSRECFIELD

2009-05-21T11:41:00.007+10:00

USEEDIT is a binary number stored as a Decimal. I forget the term right now but each bit represents a field attribute such as List field, Required etc.

edit: new version

--1 1 Key
--2 2 Duplicate Order Key
--3 4 System Maintained
--4 8 Audit Field Add
--5 16 Alternate Search Key
--6 32 List Box Item
--7 64 Descending Key
--8 128 Audit Field Change
--9 256 Required
--10 512 Translate Table Edit
--11 102 Audit Field Delete
--12 2048 Search Key
--13 4096 Reasonable Date Edit
--14 8192 Yes/No Edit
--15 16384 Prompt Table Edit
--16 32768 Auto-Update
--17 65536 Unknown
--18 131072 Unknown
--19 262144 From Search Field
--20 524288 Through Search Field
--21 1048576 1/0 Table Edit
--22 2097152 Disable Advanced Search Options
--23 4194304 Unknown
--24 8388608 Unknown
--25 16777216 Default Search Field
--26 33554432 Unknown
--27 67108864 Unknown
--28 134217728 Unknown
--29 268435456 Search Edit
--30 536870912 Unknown
--31 1073741824 Unknown

SELECT A.FIELDNAME
FROM PSRECFIELDDB A
-- Oracle: WHERE bitand(A.USEEDIT, 512) > 0
-- SQL Server: WHERE A.USEEDIT & 512 > 0
-- DB2: WHERE (USEEDIT/512)/2 <> (USEEDIT/512)/2.0.
WHERE (USEEDIT/512)/2 <> (USEEDIT/512)/2.0.
AND A.FIELDNAME not in (SELECT FIELDNAME FROM PSXLATITEM)
GROUP BY A.FIELDNAME
ORDER BY A.FIELDNAME

edit: here is a nicer code snippet. This finds all fields marked as a XLAT field, that actually aren't : \ 512 is the key.
SELECT A.FIELDNAME
FROM PSRECFIELDDB A
WHERE bitand(A.USEEDIT, 512) > 0
AND A.FIELDNAME not in (SELECT FIELDNAME FROM PSXLATITEM)
GROUP BY A.FIELDNAME
ORDER BY A.FIELDNAME

Also handy

SELECT A.RECNAME, A.FIELDNAME
FROM PSRECFIELDDB A
WHERE bitand(A.USEEDIT, 512) > 0
AND A.FIELDNAME not in (SELECT FIELDNAME FROM PSXLATITEM)
ORDER BY A.RECNAME;

___

Following is a snippet of code I have blatantly copied from a fantastic Blog I found while researching the problem. http://xtrahot.chili-mango.net/

SELECT
A.RECNAME,
A.FIELDNAME,
CASE
WHEN B.FIELDTYPE = 0 THEN
'CHAR'
WHEN B.FIELDTYPE = 1 THEN
'LONG CHAR'
WHEN B.FIELDTYPE = 2 THEN
'NUMBER'
WHEN B.FIELDTYPE = 3 THEN
'SIGNED NBR'
WHEN B.FIELDTYPE = 4 THEN
'DATE'
WHEN B.FIELDTYPE = 5 THEN
'TIME'
WHEN B.FIELDTYPE = 6 THEN
'DATETIME'
WHEN B.FIELDTYPE = 7
OR B.FIELDTYPE = 8 THEN
'IMAGE'
ELSE NULL
END AS FIELDTYPE,
CASE
WHEN B.FIELDTYPE = 2
OR B.FIELDTYPE = 3 THEN
TRIM(TO_CHAR(B.LENGTH)) || '.' || TO_CHAR(B.DECIMALPOS)
ELSE TO_CHAR(B.LENGTH)
END AS FLDLEN,
CASE
WHEN bitand(A.USEEDIT, 256) > 0 THEN
'YES'
ELSE 'NO'
END AS REQ,
CASE
WHEN bitand(A.USEEDIT, 1) > 0 THEN
'KEY'
WHEN bitand(A.USEEDIT, 2) > 0 THEN
'DUP'
WHEN bitand(A.USEEDIT, 16) > 0 THEN
'ALT'
ELSE NULL
END AS KEY_TYPE,
CASE
WHEN bitand(A.USEEDIT, 64) > 0 THEN
'DESC'
WHEN ( bitand(A.USEEDIT, 1) > 0
OR bitand(A.USEEDIT, 2) > 0
OR bitand(A.USEEDIT, 16) > 0 )
AND bitand(A.USEEDIT, 64) = 0 THEN
'ASC'
ELSE NULL
END AS DIR,
CASE
WHEN bitand(A.USEEDIT, 2048) > 0 THEN
'YES'
ELSE 'NO'
END AS SRCH,
CASE
WHEN bitand(A.USEEDIT, 32) > 0 THEN
'YES'
ELSE 'NO'
END AS LIST,
CASE
WHEN bitand(A.USEEDIT, 4) > 0 THEN
'YES'
ELSE 'NO'
END AS SYS,
CASE
WHEN TRIM(A.DEFRECNAME) = '' THEN
A.DEFFIELDNAME
ELSE
TRIM(A.DEFRECNAME) || '.' || A.DEFFIELDNAME
END AS DEFAULT_VALUE,
CASE
WHEN bitand(A.USEEDIT, 8) > 0
AND bitand(A.USEEDIT, 128) = 0
AND bitand(A.USEEDIT, 1024) = 0 THEN
'A'
WHEN bitand(A.USEEDIT, 8) > 0
AND bitand(A.USEEDIT, 128) > 0
AND bitand(A.USEEDIT, 1024) = 0 THEN
'AC'
WHEN bitand(A.USEEDIT, 8) > 0
AND bitand(A.USEEDIT, 128) > 0
AND bitand(A.USEEDIT, 1024) > 0 THEN
'ACD'
WHEN bitand(A.USEEDIT, 8) = 0
AND bitand(A.USEEDIT, 128) > 0
AND bitand(A.USEEDIT, 1024) = 0 THEN
'C'
WHEN bitand(A.USEEDIT, 8) = 0
AND bitand(A.USEEDIT, 128) > 0
AND bitand(A.USEEDIT, 1024) > 0 THEN
'CD'
WHEN bitand(A.USEEDIT, 8) = 0
AND bitand(A.USEEDIT, 128) = 0
AND bitand(A.USEEDIT, 1024) > 0 THEN
'D'
ELSE NULL
END AS AUDT,
CASE
WHEN bitand(A.USEEDIT, 16384) > 0 THEN
'PROMPT'
WHEN bitand(A.USEEDIT, 512) > 0 THEN
'XLAT'
WHEN bitand(A.USEEDIT, 8192) > 0 THEN
'Y/N'
ELSE NULL
END AS EDIT,
A.EDITTABLE AS PROMPT_TABLE,
A.SETCNTRLFLD AS SET_CONTROL_FLD,
CASE
WHEN bitand(A.USEEDIT, 4096) > 0 THEN
'YES'
ELSE 'NO'
END AS REASONABLE_DT,
CASE
WHEN bitand(A.USEEDIT, 32768) > 0 THEN
'YES'
ELSE 'NO'
END AS AUTO_UPDT,
CASE
WHEN bitand(A.USEEDIT, 262144) > 0 THEN
'FROM'
WHEN bitand(A.USEEDIT, 524288) > 0 THEN
'THROUGH'
ELSE NULL
END AS SEARCH_FIELD,
CASE
WHEN A.SUBRECORD = 'Y' THEN
'YES'
ELSE 'NO'
END AS SUBRECORD,
A.LASTUPDDTTM,
A.LASTUPDOPRID
FROM PSRECFIELD A,
PSDBFIELD B
WHERE A.RECNAME = :1
AND A.FIELDNAME = B.FIELDNAME
ORDER BY FIELDNUM

Barcode PeopleCode for code128

2009-05-19T14:05:00.000+10:00

/* This class provides methods to :
* Assist in development of XML/IB Publisher documents.
*/
class XML_Publisher
method XML_Publisher();
method getCode128BarCode(&strDataToFormat As string) Returns string;
end-class;

/* Method :- Config_Data
* Constructor
* Arguments :- None.
* Returns :- None.
*/
method XML_Publisher
end-method;

/* USQSF023, Michael Nitschke 06.05.2009
* Method :- GetFieldValues
* Arguments :- &strDataToFormat = string to format to code128 format..
* Returns :- Formatted string.
*/
/**
* getCode128BarCode
*
* This function generates Code128 barcode for the given string. It uses subsets B and C when generating barcode
* string (not subset A).
* @param In &strDataToFormat - string to format into the barcode.
* Returns: ASCII characters needed to represent the barcode.
*
* ASSUMPTIONS: The &strDataToFormat string does not contain any invalid characters. Only ASCII characters 32 to 126 are
* allowed - if there are invalid characters, an empty string is returned. The function itself appends the necessary special
* ASCII characters to generate the barcode.
*
* The algorithm for creating barcodes is based upon the sample code provided on this site, with slight modifications:
* http://grandzebu.net/index.php?page=/informatique/codbar-en/code128.htm
**/
method getCode128BarCode
/+ &strDataToFormat as String +/
/+ Returns String +/

Local boolean &boolSubsetB = True;
Local boolean &boolIsDigits;
Local string &strBarcode = "";
Local integer &i;
Local integer &intCharNum, &charsToCheck, &intCheckSum;

/* While developing... */
rem &strDataToFormat = "123456789012345678901234567890";

/* No need to process an empty string - simply return what was put in */
If Len(&strDataToFormat) = 0 Then
Return &strBarcode;
End-If;

/* Check for valid characters */
For &i = 1 To Len(&strDataToFormat)
&intCharNum = Code(Substring(&strDataToFormat, &i, 1));
If &intCharNum >= 32 And
&intCharNum <= 126 Then
Else
/* invalid characters found - return empty string */
Return "";
End-If;
End-For;

&i = 1;

While &i <= Len(&strDataToFormat)

If &boolSubsetB Then
/* see if it's worth it to switch to subset C */
/* yes for 4 digits at start or end, else if 6 digits */
If (&i = 1) Or
(&i + 3) = Len(&strDataToFormat) Then
&charsToCheck = 4;
Else
&charsToCheck = 6;
End-If;

&boolIsDigits = False;
If IsDigits(Substring(&strDataToFormat, &i, &charsToCheck)) And
(Len(Substring(&strDataToFormat, &i, &charsToCheck)) = &charsToCheck) Then
&boolIsDigits = True;
End-If;

If &boolIsDigits Then

If &i = 1 Then
/* Put Start C character, to indicate barcode starts with subset C */
&strBarcode = Char(210);
Else
/* Put Code C character, to indicate switch to subset C */
&strBarcode = &strBarcode | Char(204);
End-If;
&boolSubsetB = False;

Else

If &i = 1 Then
/* Put Start B character, to indicate barcode starts with subset B */
&strBarcode = Char(209);
End-If;

End-If;

End-If;

If Not &boolSubsetB Then

/* We are on subset C, try to process 2 digits */
&boolIsDigits = False;

If IsDigits(Substring(&strDataToFormat, &i, 2)) And
(Len(Substring(&strDataToFormat, &i, 2)) = 2) Then
&boolIsDigits = True;
End-If;

If &boolIsDigits Then
/* 2 digits - process using subset C */
&intCharNum = Value(Substring(&strDataToFormat, &i, 2));
If &intCharNum = 0 Then
&intCharNum = 212;
Else
If &intCharNum < 95 Then
&intCharNum = &intCharNum + 32;
Else
&intCharNum = &intCharNum + 105;
End-If;
End-If;

&strBarcode = &strBarcode | Char(&intCharNum);
&i = &i + 2;

Else
/* revert back to subset B */
&strBarcode = &strBarcode | Char(205);
&boolSubsetB = True;

End-If;

End-If;

If &boolSubsetB Then

/* Process 1 digit with subset B */
&strBarcode = &strBarcode | Substring(&strDataToFormat, &i, 1);
&i = &i + 1
End-If;

End-While;

/* Calculation of the checksum */
For &i = 1 To Len(&strBarcode)

&intCharNum = Code(Substring(&strBarcode, &i, 1));
If &intCharNum = 212 Then
&intCharNum = 0;
Else
If &intCharNum < 127 Then
&intCharNum = &intCharNum - 32;
Else
&intCharNum = &intCharNum - 105;
End-If;
End-If;

If &i = 1 Then
&intCheckSum = &intCharNum;
Else
&intCheckSum = &intCheckSum + (&intCharNum * (&i - 1));
End-If;

End-For;

/* Apply modulo 103 to the weighted total to get the checksum */
&intCheckSum = Mod(&intCheckSum, 103);

/* Calculate checksum ASCII code */
If &intCheckSum < 95 Then
&intCheckSum = &intCheckSum + 32;
Else
&intCheckSum = &intCheckSum + 105;
End-If;

/* Add the checksum and the STOP */
&strBarcode = &strBarcode | Char(&intCheckSum) | Char(211);

Return &strBarcode;

end-method;

Generating XSD and XML for XML Publisher

2009-04-24T14:53:00.006+10:00

During design phase of an XML Publisher Report it is usefule to be able to rapidly update your XSD files for the data source template. I found some delivered code that does it very nicely. The run control includes a flag to swith this on/off.

See SSF_PRNT_INV.CALLXMLP.Step02

&MainChunk is/are the final rowset/s generated prior to publishg to XML Publisher.

You'll need to include:
import PSXP_XMLGEN:*;


  REM - Use this code to output both XSD and XML files;
  REM - for use in the design phase;
  If SSF_INVP_AET.SSF_OUTPUT_XML = "X" Or
        SSF_INVP_AET.SSF_OUTPUT_XML = "B" Then
     &oDS = create PSXP_XMLGEN:RowSetDS();
     &MYFILE = GetFile(RTrim(SSF_INVP_AET.JF_FILE_DIR) | SSF_INVP_AET.PROCESS_INSTANCE | "_*" | ".xsd", "N", "UTF8", %FilePath_Absolute);
     If &MYFILE.IsOpen Then
        &sResult = &oDS.getXSDSchema(&MainChunk);
        &MYFILE.WriteLine(&sResult);
        &MYFILE.Close();
     End-If;
     &MYFILE = GetFile(RTrim(SSF_INVP_AET.JF_FILE_DIR) | SSF_INVP_AET.PROCESS_INSTANCE | "_*" | ".xml", "N", "UTF8", %FilePath_Absolute);
     If &MYFILE.IsOpen Then
        &sResult = &oDS.getXMLData(&MainChunk, "");
        &MYFILE.WriteLine(&sResult);
        &MYFILE.Close();
     End-If;
  End-If;
  REM - End of design phase code ;

Project Flowchart (developer centric version)

2009-04-23T18:44:00.003+10:00

Work in progress...

NVAL NVL Null no value in Oracle SQL

2009-04-08T11:49:00.003+10:00

Argh, I seem to always forget this. Very annoying.

select nvl(
    (select emplid from psoprdefn where oprid = 'xxx')
, 'a') from dual

Getting Crystal To Work

2009-03-16T12:41:00.004+10:00

Oh Crystal, when will you be decommissioned?

Sometimes Crystal just doesn't want to play ball. Here's what I recently found:

If the report was using my local ODBC connection for its Database Connection it didn't work on the Server. Of course.

I was receiving the following error message:
```
Crystal Reports Print Engine error text: Error in File e:\psoft\s90dev\crw\eng\usqsf3p.rpt:
Unable to connect: .rpt:
Unable to connect: incorrect log on parameters. %6 %7 %8 %9 (65,30)
Message Set Number: 65
Message Number: 30
Message Reason: Crystal Reports Print Engine error text: Error in File e:\psoft\s90dev\crw\eng\usqsf3p.rpt:
Unable to connect: .rpt:
Unable to connect: incorrect log on parameters. %6 %7 %8 %9 (65,30)
```
This can be simply corrected by using the following steps:

- Go to 'Database/Set Datasource Location...'
- Create a new connection.

- Select both Current Data Source in top pane and new one in bottom, click 'Update'.
- Verify the database.
- Save and perhaps even open and close to ...do something.

... or just never using a local ODBC connection (duh?).
Which user to use for the connection prior to migrating out of DEV and eventually into PROD?
Subreport links.

If found I had to use the following ("sameish" for run control id):

Printing SQR A4 instead of Letter

2009-03-10T11:21:00.002+10:00

Bit of a vague post...

When printing directly to Printer we had a Process Type of 'Crystal Check'. In parameters we added:

-PAPERA4

... to the end of the parameter list. This is not documented.

The Registry - Registering Interfaces

2009-02-17T15:13:00.010+10:00

For following error message:

"HMCR_FRAMEWORK.ServiceFramework.Utilities.ServiceManager.OnExecute Name:LocateService PCPC:1612 Statement:37 Called from:SCC_PERSON_SOA_CONSUMER.PersonServiceConsumer.SCC_PersCitizenshipConsumer.OnExecute Name:getData Statement:6 Called from:CITIZEN_PASSPORT.GBL.PostBuild Statement:5 not found in the registry (18137,5)

The PeopleCode program executed an Error statement, which has produced this message."

See:

PeopleBooks > PeopleSoft Enterprise HRMS 9.0 Application Fundamentals PeopleBook > Registering Interfaces

Which leads to:

Set Up HRMS > System Administration > HCM Registry > Service Registry

Click the 'Refresh Cache' button:

Right Align Field in SQR

2009-01-30T09:59:00.002+10:00

How to align right in SQR?

Sometimes the answers are so obvious you kick yourself when you see them:

Let $total = lpad(To_Char(#total), 15, ' ')

Print $total (, 40) edit $999,999,999.99 bold

Browser Language Problems

2009-01-21T17:25:00.002+10:00

Heard about some problems when browser's default language is not same as Psoft enabled language. Found I couldn't log in when set to Spanish.

Here is the solution
http://docs.google.com/Doc?id=df48r26b_49chqwb3dt

Tricky EFFDT Logic in Query

2009-01-07T17:54:00.003+10:00

Sometimes you want to write something like the following in Query
(code snippet for outer join + effdt solution btw):

AND K.PASSWORD (+) = F.PASSWORD
AND
(K.EFFDT IS NULL
OR K.EFFDT =
(SELECT MAX( M.EFFDT)
FROM PS_UQ_CHK_SIG_FILE M
WHERE M.EFFDT <= SYSDATE))

It would appear that Query doesn't let you, but if you do things in the correct order you can force it : )

Add your subquery
Allow the default Effdt Logic to be added by PeopleSoft/Query
Add a new expression "max(effdt)" and add to the selected field.
Note do not add the alias
Correct the Criteria using field + 'not greater than' + 'sysdate' <- an expression

Queries in Project and number of Records

2008-12-17T17:28:00.002+10:00

select
b.qryname
, b.descr
, b.descrlong
, (select count(*) from PSQRYRECORD where qryname = b.qryname) as Num_Records
from
psprojectitem a
, psqrydefn b
where a.projectname = 'UQXM1210'
and b.qryname = a.objectvalue1
order by 4

App Engine Comments

2008-11-21T10:00:00.004+10:00

Would seem that you can make comments in App Engine SQL Actions if:

#1 You use the /* */ commenting standard,
#2 Comments are after the WHERE clause.
#3 Comments do not contain a "%", such as %Table(RecName).

Field Record Definition for Detailed Design

2008-10-22T15:58:00.003+10:00

run, copy and paste (work in progess):

DEFINE RECNAME = 'UQ_BNK_RCN_PAYM';
-- FIELD, KEY, SEARCHKEY, LIST, REQ, EDIT, PROMPT TABLE, DEFAULT
SELECT
FIELDNAME AS FIELD
, ' ' AS KEY
, ' ' AS SEARCHKEY
, ' ' AS LISTT
, ' ' AS REQ
, ' ' AS EDIT
, ' ' AS PROMPTTABLE
, ' ' AS DEFAULTT
FROM PSRECFIELD
WHERE RECNAME = &RECNAME
ORDER BY FIELDNUM
;

Security for Purchase Orders, Create & Approve.

2008-10-13T11:28:00.002+10:00

Security for Purchase Orders, Create & Approve.

Requestor:
Peopletools > Security > User Profiles
Role: 'xx C PO Officer'
Route Control: (same as the approver)

Set Up Financials/Supply Chain > Common Definitions > User Preferences > Define User Preferences > (Procurement Link)
Operational Unit: (same as the approver)
Requester: 'userA'

> (Purchase Order Authorisations)
Tick everything (if appropriate for environment)
Buyers User Authorised For: 'userA'

Approver:
Peopletools > Security > User Profiles
Role: 'xx W Approver - Fin Lvl 1'
Route Control: (same as the requester)

Set Up Financials/Supply Chain > Common Definitions > User Preferences > Define User Preferences > (Procurement Link)
Operational Unit: (same as the requester)
> (Purchase Order Authorisations)
Tick everything (if appropriate for environment)

File Attachment Utilities

2008-10-09T15:16:00.003+10:00

Found some delivered code that actually made things easier for once. Unbelievable I know.
It even has excellent, descriptive comments!!!

Check the Application Package SCM_ARCH_ATTACH.UI.AttachmentHandler

1. Make sure your record contains the attachment fields SCM_ATTACH_ID and ATT_VERSION (ATTACHUSERFILE would be nice too for the user given filename)

2. Place the fields from Record.PV_ATTACH_WRK on the same scroll level.

3. Enjoy.

Excel-To-CI CI Component Interface PeopleCode

2008-10-09T13:25:00.003+10:00

While building a custom Excel-To-CI Interface we discovered that each row in the spreadsheet is passed, one at a time, to the CI. So, if for example you had data with one header and two lines you would have 2 rows on the spreadsheet. A call would be made to the CI twice.

Following is example PeopleCode that will handle juggling of the unique key values needed:

Component.SavePreChange()

/* UQAP0780 - UQ SIR 178, Michael Nitschke 03.Sep.08

Handle multiples voucher lines and distribution lines.
The ExcelToCI spreadsheet posts across each line as a distinct voucher.
This code detects that the voucher already exists and marks the row as one that
has to be queried by PeopleCode in SavePostChange(). */

If %Mode = "A" Then

/* Get level 0 records for component. */
Local Record &recVCHR_HDR_STG = GetLevel0()(1).GetRecord(Record.VCHR_HDR_STG);
Local Record &recVCHR_VNDR_STG = GetLevel0()(1).GetRecord(Record.VCHR_VNDR_STG);
Local Record &recVCHR_BANK_STG = GetLevel0()(1).GetRecord(Record.VCHR_BANK_STG);

/* Generate a unique VCHR_BLD_KEY_C1 value. */
&recVCHR_HDR_STG.VCHR_BLD_KEY_C1.Value = &recVCHR_HDR_STG.INVOICE_ID.Value | &recVCHR_HDR_STG.INVOICE_DT.Value | &recVCHR_HDR_STG.VENDOR_ID.Value | &recVCHR_HDR_STG.GROSS_AMT.Value;
&recVCHR_VNDR_STG.VCHR_BLD_KEY_C1.Value = VCHR_HDR_STG.VCHR_BLD_KEY_C1.Value;
&recVCHR_BANK_STG.VCHR_BLD_KEY_C1.Value = VCHR_HDR_STG.VCHR_BLD_KEY_C1.Value;

/* If VCHR_BLD_KEY_C1 value already exists we need to create a 2nd key value for VCHR_BLD_KEY_C2.
> See Component.SavePostChange PeopleCode for where this value/mark is used to cleanup rows. */
Local integer &count = 0;
SQLExec("SELECT count(*) FROM %Table(VCHR_HDR_STG) WHERE VCHR_BLD_KEY_C1 = :1", VCHR_HDR_STG.VCHR_BLD_KEY_C1.Value, &count);
If &count <> 0 Then
VCHR_HDR_STG.VCHR_BLD_KEY_C2.Value = "X" | &count;
&recVCHR_VNDR_STG.VCHR_BLD_KEY_C2.Value = VCHR_HDR_STG.VCHR_BLD_KEY_C2.Value;
&recVCHR_BANK_STG.VCHR_BLD_KEY_C2.Value = VCHR_HDR_STG.VCHR_BLD_KEY_C2.Value;
End-If;

End-If;
/* UQAP0780 - UQ SIR 178 End */

Component.SavePostChange()
/* UQAP0780, UQ SIR 178 Michael Nitschke 03.Sep.2008
Cleanup records that have been inserted as single vouchers but are really*
data for child rows (line and distribution) of already existing vouchers.
These are marked with key field UQ_VCHRHDR_ST_V.VCHR_BLD_KEY_C2 value = 'X'.

NOTE that this cleanup process will also allow duplicate vouchers to be posted
from the spreadsheet to the staging tables, the duplicates will be cleaned up/removed. */

/* Level 0 records are easy, if VCHR_BLD_KEY_C1 = "X" then delete the record.
(b is for buffer) */

Local Record &recVCHR_HDR_STGb = GetLevel0()(1).GetRecord(Record.VCHR_HDR_STG);
If Left(&recVCHR_HDR_STGb.VCHR_BLD_KEY_C2.Value, 1) = "X" Then

Local Record &recVCHR_HDR_STG = CreateRecord(Record.VCHR_HDR_STG);
Local Record &recVCHR_VNDR_STG = CreateRecord(Record.VCHR_VNDR_STG);
Local Record &recVCHR_BANK_STG = CreateRecord(Record.VCHR_BANK_STG);

&recVCHR_HDR_STGb.CopyFieldsTo(&recVCHR_HDR_STG);
&recVCHR_HDR_STGb.CopyFieldsTo(&recVCHR_VNDR_STG);
&recVCHR_HDR_STGb.CopyFieldsTo(&recVCHR_BANK_STG);

&x = &recVCHR_HDR_STG.Delete();
&x = &recVCHR_VNDR_STG.Delete();
&x = &recVCHR_BANK_STG.Delete();

End-If;

/* Line record (b for buffer). */
Local Record &recVCHR_LINE_STGb = GetLevel0()(1).GetRowset(Scroll.VCHR_LINE_STG)(1).GetRecord(Record.VCHR_LINE_STG); /* (There will only ever be one row) */
If Left(&recVCHR_LINE_STGb.VCHR_BLD_KEY_C2.Value, 1) = "X" Then

Local Record &recVCHR_LINE_STG = CreateRecord(Record.VCHR_LINE_STG);
&recVCHR_LINE_STGb.CopyFieldsTo(&recVCHR_LINE_STG);
&tmpKeyVal = &recVCHR_LINE_STG.VCHR_BLD_KEY_C2.Value;
&recVCHR_LINE_STG.VCHR_BLD_KEY_C2.Value = "";

/* If a duplicate row exists (sans the "X" value) then delete.
Else a unique row so update/remove the "X". */
If &recVCHR_LINE_STG.SelectByKey() Then
&recVCHR_LINE_STG.VCHR_BLD_KEY_C2.Value = &tmpKeyVal;
&x = &recVCHR_LINE_STG.Delete();
Else
SQLExec("UPDATE %Table(VCHR_LINE_STG) SET VCHR_BLD_KEY_C2 = ' ' WHERE %KeyEqual(:1)", &recVCHR_LINE_STGb);
End-If;

End-If;

/* Distribution record. (b is for buffer) */
Local Record &recVCHR_DIST_STGb = GetLevel0()(1).GetRowset(Scroll.VCHR_LINE_STG)(1).GetRowset(Scroll.VCHR_DIST_STG)(1).GetRecord(Record.VCHR_DIST_STG); /* (There will only ever be one row) */
If Left(&recVCHR_DIST_STGb.VCHR_BLD_KEY_C2.Value, 1) = "X" Then

Local Record &recVCHR_DIST_STG = CreateRecord(Record.VCHR_DIST_STG);
&recVCHR_DIST_STGb.CopyFieldsTo(&recVCHR_DIST_STG);
&tmpKeyVal = &recVCHR_DIST_STG.VCHR_BLD_KEY_C2.Value;
&recVCHR_DIST_STG.VCHR_BLD_KEY_C2.Value = "";

/* If a duplicate row exists (sans the "X" value) then delete.
Else a unique row so update/remove the "X". */
If &recVCHR_DIST_STG.SelectByKey() Then
&recVCHR_DIST_STG.VCHR_BLD_KEY_C2.Value = &tmpKeyVal;
&x = &recVCHR_DIST_STG.Delete();
Else
SQLExec("UPDATE %Table(VCHR_DIST_STG) SET VCHR_BLD_KEY_C2 = ' ' WHERE %KeyEqual(:1)", &recVCHR_DIST_STGb);
End-If;

End-If;

/* UQAP0780, UQ SIR 178 End */

Excel-To-CI Field Processing Order

2008-10-03T14:53:00.002+10:00

When using the Excel-To-CI spreadsheets fields are processed in the same order that they appear on the records in the CI. A problem can occur if field A is using the value of field B to populate a key value on its prompt table as field B will be populated after field A. Note again that the order these fields appear on the page is not taken into consideration.

To get around this problem you need to add a Derived Work record field on the page, up at level ) that will copy its value to Field B. This DW field will be populated 1st.

Pause/Sleep/Wait

2008-09-17T10:53:00.001+10:00

Elegant way to pause or sleep processing for a while without using CPU time. In PeopleCode:

Local JavaObject &thread = GetJavaClass("java.lang.Thread");
&thread.sleep(2500); // this is in milliseconds

Indexes on Delivered Tables

2008-09-16T14:09:00.001+10:00

1. PSMENUITEM
create index psoft.MW_PSMENUITEM on psoft.PSMENUITEM ( PNLGRPNAME, ITEMNAME ) tablespace psindex;

2. PSPNLGROUP
create index psoft.MW_PSPNLGROUP on psoft.PSPNLGROUP ( PNLNAME, ITEMNAME ) tablespace psindex;

3. PSAUTHITEM
create index psoft.MW_PSAUTHITEM on psoft.PSAUTHITEM ( DISPLAYONLY, BARITEMNAME, PNLITEMNAME, CLASSID ) tablespace psindex;

4. PS_VAT_DEFAULT_HDR
create index PSOFT.MW_PS_VAT_DEFAULT_HDR on PSOFT.PS_VAT_DEFAULT_HDR ( VAT_DRIVER, COUNTRY, STATE, VAT_DRIVER_KEY1, VAT_DRIVER_KEY2, VAT_DRIVER_KEY3, EFF_STATUS, EFFDT, END_EFFDT, VAT_DRIVER_KEY4, VAT_DRIVER_KEY5, VAT_SETID );

Selecting CLOB Fields

2008-09-11T16:55:00.002+10:00

Can be hard if you are using DISTINCT or LIKE clauses et al in your statement. Try this Oracle function:

SELECT
dbms_lob.substr(j.comments_2000,1,1000)

Publishing Files to Report Manager/RDS

2008-09-11T10:41:00.004+10:00

See also: http://capital-it.blogspot.com/2008/02/getting-sqrs-to-publish-to-rds.html

In short the generated files must be created in the same folder that is generated for the process. I've found that App Engines at least are very fussy with filenames and they must follow the following convention:

AE_ProcessName_ProcessInstance.txt/csv

I have found, with App Engines at least, that the RDS only likes extensions txt and csv. Worth trying a few more.

The path can be found here:

SELECT PRCSOUTPUTDIR || '\'
FROM PSPRCSPARMS
WHERE PRCSINSTANCE = 11579

Example code:

Local string &pathNm
SQLExec("select prcsoutputdir || '\' from PSPRCSPARMS where prcsinstance = :1", UQ_UQAP0860_AET.PROCESS_INSTANCE.Value, &pathNm);
Local string &fileNm = "AE_UQAP0860_" | UQ_UQAP0860_AET.PROCESS_INSTANCE.Value | ".csv";

Note: I have noticed that the file does not publish to RDS/Report Manager if... well I'm not sure. Check that filename is not too long, does not contain extra periods (don't use %DateTime in the filename) etc., etc..

Finding Unique Index Keys - Oracle

2008-08-29T12:06:00.002+10:00

Will give you a list of indexes used by the table:
select * from all_indexes where table_name = 'T931_ACCOUNT_SWITCHING';

Will return fields (or keys) used by the index:
select * from all_ind_columns where table_name = 'T931_ACCOUNT_SWITCHING'
and index_name = 'T931_ACCOUNT_SWITCHINGP1'
order by 1,2,3,4,6

VBA example - Handling Import and Export Files

2008-08-26T11:45:00.002+10:00

Because sometimes you have to use VBA. This example was for a 3rd party's interface.


Option Compare Database

Public Sub ifcHR185GL_Actuals()
    
    DoCmd.SetWarnings False
    stAurion = CheckEnviron()             ' Get & set Env
    
    locIn = "C:\Temp\"
    locOut = "C:\Temp\"
    
    stFilePathIn = locIn & "gl_actuals.txt"
    stFilePathOutHdr = locOut & "JACT" & Format(Date, "YYMMDD") & "_H.txt"
    stFilePathOutLn = locOut & "JACT" & Format(Date, "YYMMDD") & "_L.txt"
    stFilePathOut = locOut & "JACT" & Format(Date, "YYMMDD") & ".txt"
    
'    stFilePathIn = "U:\INTERFACE\CURRENT\IN\gl_interface_"
'    stFilePathOut = "U:\INTERFACE\CURRENT\OUT\JACT" & Format(Date, "YYMMDD") & ".txt"
    
    'Delete the existing records
    DoCmd.OpenQuery "qryHR185Actuals_Aurion_Del", acNormal, acEdit
    DoCmd.OpenQuery "qryHR185Journal_Line_Actuals_Del", , acEdit
    
    'Import the Aurion text file and create a new table
    DoCmd.TransferText acImportDelim, "HR185Gl_actuals Import Specification", "tblHR185Actuals_Aurion", _
    stFilePathIn, False, ""
        
    'Update header record with system date and PPEDN date to be used for journal lines
    'may be commented out when the file has a PPEND
    'but it requires import spec changes and qry_App changes
    DoCmd.OpenQuery "qryHR185HeaderRecord_Upd", acNormal, acEdit
    
    'Create output table
    ''DoCmd.OpenQuery "qryHR185Journal_Header_Actuals_App", acNormal, acEdit
    DoCmd.OpenQuery "qryHR185Journal_Line_Actuals_App", acNormal, acEdit
    DoCmd.OpenQuery "qryHR185Journal_Line_Actuals_UpdPPEND", acNormal, acEdit

    ' Delete old copy of output files if found
    stScratch = Dir(stFilePathOutHdr)
    If stScratch <> "" Then
     Kill stFilePathOutHdr
    End If
    stScratch = Dir(stFilePathOutLn)
    If stScratch <> "" Then
     Kill stFilePathOutLn
    End If
    stScratch = Dir(stFilePathOut)
    If stScratch <> "" Then
     Kill stFilePathOut
    End If
    
    'Export the Header and Line tables
    DoCmd.TransferText acExportFixed, "tblHR185Journal_Header_Actuals Export Specification", _
    "tblHR185Journal_Header_Actuals", stFilePathOutHdr
    DoCmd.TransferText acExportFixed, "tblHR185Journal_Line_Actuals Export Specification", _
    "qryHR185Journal_Line_Actuals_Export", stFilePathOutLn
            
    ' Merge Header and Line files into final export file.
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fStrmH = fso.OpenTextFile(stFilePathOutHdr)
    Set fStrmL = fso.OpenTextFile(stFilePathOutLn)
    Set fStrmM = fso.CreateTextFile(stFilePathOut, True)
    Do Until fStrmH.AtEndOfStream
        strLine = fStrmH.ReadLine
        fStrmM.writeline strLine
    Loop
    Do Until fStrmL.AtEndOfStream
        strLine = fStrmL.ReadLine
        fStrmM.writeline strLine
    Loop
    
    fStrmH.Close
    fStrmL.Close
    fStrmM.Close
    
    ' Cleanup Header and Line files.
    Kill stFilePathOutHdr
    Kill stFilePathOutLn
    DoCmd.SetWarnings True
    
    '!!!   COMMENT OUT THE LINE BELOW LATER
    MsgBox "completed"
    
End Sub

Simple Example of using an Application Package

2008-08-15T09:59:00.002+10:00

Because I'm always forgetting the syntax:

import APP_PACKAGE:AppClass;

Local APP_PACKAGE:AppClass &objAppClass = create APP_PACKAGE:AppClass();

&x = &objAppClass.Method();

For Loop Gotcha

2008-08-01T10:59:00.003+10:00

From a recent email from a collegue. It seems that For loops only evaluate the conditions once, when the loop is entered:

Hi all,

I have just encountered an ‘interesting’ property of for loops.

It appears that they hard-code the number that they loop through until, and there doesn’t seem to be any way to affect this number.

I.e. If you have a rowset and are looping through it via a for loop - &I = 1 to &rowset.activerowcount, and you add a row to the rowset, the for loop will only go through the original number of rows – the added row will not be part of the &rowset.activerowcount of the loop. (If there were originally 10 rows in the rowset, and I add another 5 rows to the rowset as part of the for loop, &I will only ever get to 10 – not 15).

If you wish to loop through a rowset (or any variable number loops) you should use a while or a repeat loop, as the conditions are re-evaluated each time the loop comes to the conditional part (the top of the while statement, and the until of the repeat statement).

Hope that helps people.

Easy Compare Differences in Tables

2008-07-15T13:08:00.003+10:00

SELECT * FROM TABLE_A
MINUS
SELECT * FROM TABLE_B

This can be expanded out to include the actual rows that are different between the two tables, or missing:


SELECT * FROM (

SELECT
  'T906_TIME_CODE' AS TABLE_NAME
, '@AURPREP' AS DATABASE_NAME
,     DECODE( -- Check if rows are different or missing.  Missing if no row found with matching keys.
        (SELECT 1 FROM T906_TIME_CODE B
        WHERE NVL(B.T906F370_TRAN_CODE_TERM, 'X') = NVL(A.T906F370_TRAN_CODE_TERM, 'X')
        AND NVL(B.T906F005_AWARD_CODE, 'X') = NVL(A.T906F005_AWARD_CODE, 'X')
        AND NVL(B.T906F010_TIME_CODE, 'X') = NVL(A.T906F010_TIME_CODE, 'X')
        AND NVL(B.T906F075_COSTING_ACCOUNT, 'X') = NVL(A.T906F075_COSTING_ACCOUNT, 'X')
        AND NVL(B.T906F310_ACCOUNT, 'X') = NVL(A.T906F310_ACCOUNT, 'X')
        AND NVL(B.T906F074_PC, 'X') = NVL(A.T906F074_PC, 'X')
        AND NVL(B.T906F020_LEAVE_TYPE_AWARD, 'X') = NVL(A.T906F020_LEAVE_TYPE_AWARD, 'X')
        AND NVL(B.T906F025_LEAVE_TYPE_CODE, 'X') = NVL(A.T906F025_LEAVE_TYPE_CODE, 'X')
        AND NVL(B.T906F060_SECURITY_TASK, 'X') = NVL(A.T906F060_SECURITY_TASK, 'X')
        )    , 1, 'DIFFERENCES'
    , 'MISSING IN CURRENT') AS STATUS
, A.*
FROM
    (SELECT * FROM T906_TIME_CODE@AURPREP
    MINUS
    SELECT * FROM T906_TIME_CODE) A

UNION

SELECT
  'T906_TIME_CODE' AS TABLE_NAME
, 'CURRENT' AS DATABASE_NAME
,     DECODE( -- Check if rows are different or missing.  Missing if no row found with matching keys.
        (SELECT 1 FROM T906_TIME_CODE@AURPREP B
        WHERE NVL(B.T906F370_TRAN_CODE_TERM, 'X') = NVL(A.T906F370_TRAN_CODE_TERM, 'X')
        AND NVL(B.T906F005_AWARD_CODE, 'X') = NVL(A.T906F005_AWARD_CODE, 'X')
        AND NVL(B.T906F010_TIME_CODE, 'X') = NVL(A.T906F010_TIME_CODE, 'X')
        AND NVL(B.T906F075_COSTING_ACCOUNT, 'X') = NVL(A.T906F075_COSTING_ACCOUNT, 'X')
        AND NVL(B.T906F310_ACCOUNT, 'X') = NVL(A.T906F310_ACCOUNT, 'X')
        AND NVL(B.T906F074_PC, 'X') = NVL(A.T906F074_PC, 'X')
        AND NVL(B.T906F020_LEAVE_TYPE_AWARD, 'X') = NVL(A.T906F020_LEAVE_TYPE_AWARD, 'X')
        AND NVL(B.T906F025_LEAVE_TYPE_CODE, 'X') = NVL(A.T906F025_LEAVE_TYPE_CODE, 'X')
        AND NVL(B.T906F060_SECURITY_TASK, 'X') = NVL(A.T906F060_SECURITY_TASK, 'X')
        )    , 1, 'DIFFERENCES'
    , 'MISSING IN @AURPREP') AS STATUS
, A.*
FROM
    (SELECT * FROM T906_TIME_CODE
    MINUS
    SELECT * FROM T906_TIME_CODE@AURPREP) A

)
ORDER BY -- (keys)
  T906F370_TRAN_CODE_TERM
, T906F005_AWARD_CODE
, T906F010_TIME_CODE
, T906F075_COSTING_ACCOUNT
, T906F310_ACCOUNT
, T906F074_PC
, T906F020_LEAVE_TYPE_AWARD
, T906F025_LEAVE_TYPE_CODE
, T906F060_SECURITY_TASK
, 2
;

PL/SQL Array example

2008-07-14T17:26:00.001+10:00


declare

    type table_list is table of varchar(128) index by binary_integer;
    tabs table_list;
    i number;

begin

    tabs(1) := 'aaa';
    tabs(2) := 'bbb';
    tabs(3) := 'ccc';
    
    for i in 1..3
    loop
        dbms_output.put_line(tabs(i));
    end loop;

end;

Simple Select and Loop in PL/SQL

2008-07-14T16:10:00.002+10:00

Simple example of using PL/SQL to select and loop through a cursor.
Note: Results are output to the output window.


CREATE OR REPLACE PROCEDURE test(p_table_name in user_tables.table_name%type) IS

    TYPE ref_cursor IS REF CURSOR;
    l_cursor        ref_cursor;

    v_query         varchar2(5000);
    v_name          varchar2(64);

BEGIN
    v_query := 'select T120F005_COST_CATEGORY_CODE from ' || p_table_name;
    OPEN l_cursor FOR v_query;
    LOOP
        FETCH l_cursor INTO v_name;
        EXIT WHEN l_cursor%NOTFOUND;
        dbms_output.put_line('Category Code: ' || v_name);
    END LOOP;
    CLOSE l_cursor;
END;
/

-- Test.
EXEC test('T120_COSTING_CATEGORY');

Access to HE90PS PS

2008-06-06T13:38:00.001+10:00

(little note to self)

GU_DEVELOPER_BSS_D0

Viewing Keys (indexes) at Oracle Level

2008-05-27T14:11:00.001+10:00

select * from system.dba_ind_columns where table_name='ps_table_name';

Application Engine Tracing

2008-05-23T12:24:00.003+10:00

You can turn on tracing for a distinct Application Engine process by appending AE parameters. Append parameter "-TRACE 3" to enable the tracing.

Oracle 10

2008-04-11T14:41:00.001+10:00

From email:

Oracle (since version 8 ? ) has been recommending to all that we move toward the Cost Based Optimizer (CBO). Prior to this Oracle used (and still can use) the RULE based Optimizer, which pretty much just uses predicate logic and knowledge of indexes to do its job. (Also, the order of tables in the FROM clause is important, and the order of the WHERE clauses)

The CBO attempts to come up with the cheapest row access plan based on the actual cost (CPU and IO) of the plan. To do this, Oracle needs accurate statistics on the tables and indexes involved. These statistics are gathered using the Oracle supplied PLSQL procedures within the DBMS_STATS package. These procedures gather a bunch of different information about the tables and indexes, and can also gather histogram information, which can be invaluable on (mainly indexed) columns where there is highly skewed data. Stats gathering is becoming more automagic as time goes on. Everytime Oracle release a new version, there are new and improved auto options for stats gathering. there is also the possibility (we are not doing this presently) of gathering "system stats" as well. These are separate to the data object stats, and must be gathered by monitoring a live system. These stats attempt to place real time values on IO based on system load. If these are available, the CBO correlates its IO estimates with these real time estimates.

The CBO is not perfect though, and occasionally it will choose a less than optimal access path. This happens mainly because stats usually contain imprecise estimates made during stats gathering. It is possible to collect complete and accurate stats, but this is extremely expensive. To give you an example, it takes over 30 hours to gather stats in HE90U5 (using the auto options, which will estimate some things).

Portal Navigation Structure

2008-04-09T09:01:00.000+10:00

select t.portal_name, t.portal_reftype, LPAD (' ', 10 * (LEVEL - 1)) || t.portal_objname as portal_objname, LPAD (' ', 10 * (LEVEL - 1)) || t.portal_label as portal_label, t.portal_ispublic, t.portal_seq_num, t.lastupdoprid, level lev, t.portal_urltext
from sysadm.psprsmdefn t
where t.portal_name = 'EMPLOYEE'
start with t.portal_prntobjname = 'PORTAL_ROOT_OBJECT'
CONNECT BY PRIOR t.portal_objname = t.portal_prntobjname and PRIOR t.portal_name = t.portal_name and PRIOR t.portal_reftype = 'F';

Impact List for Projects

2008-02-28T18:08:00.001+10:00


-- 1. Copy the results
-- 2. Paste into Word then: Table/Convert/Text to Table...
-- 3. You may need copy and paste formatting from other cells.

define projectName = 'GUFNGL018'

--select object, type, ' ', 'New', 'Easy', 0, 0, ' ', ' ' from (
select * from (
SELECT
a.projectname,
A.OBJECTVALUE1 || decode(A.OBJECTVALUE2,' ','','.'||A.OBJECTVALUE2) || decode(A.OBJECTVALUE3,' ','','.')|| A.OBJECTVALUE3 || decode(A.OBJECTVALUE4,' ','','.') || A.OBJECTVALUE4 as Object,
decode(a.objecttype,
'0', 'Record',
'1', 'Index',
'2', 'Field',
'3', 'Field Format',
'4', 'Translate value',
'5', 'Page',
'6', 'Menu',
'7', 'Component',
'8', 'Record Peoplecode',
'9', 'Unknown Object Type',
'10','Query',
'11','Tree Structure',
'12','Tree',
'13','Access Group',
'14','Colours',
'15','Style',
'16','Unknown Object Type',
'17','Business Processes',
'18','Activities',
'19','Roles',
'20','Process Defintions',
'21','Server Definition',
'22','Process Type Definitions',
'23','Job Definitions',
'24','Recurrence Definition',
'25','Message Catalog Entries',
'26','Dimensions',
'27','Cube Definitions',
'28','Cube Instance Definitions',
'29','Business Interlink',
'30','Record SQL',
'31','File Layout Definitions',
'32','Component Interface',
'33','Application Engine Programs',
'34','Application Engine Sections',
'35','Message Node',
'36','Message Channels',
'37','Messages',
'38','Approval Rule Sets',
'39','Unknown Object Type',
'40','Unknown Object Type',
'41','Unknown Object Type',
'42','Unknown Object Type',
'43','Unknown Object Type',
'44','Page Peoplecode',
'45','Unknown Object Type',
'46','Component Peoplecode',
'47','Unknown Object Type',
'48','Component Rec Fld Peoplecode',
'49','Images',
'50','Style Sheet',
'51','HTML',
'52','File Reference',
'53','Permission Lists',
'54','Portal Registry Definitions',
'55','Portal Registry Structures',
'56','URL Definition',
'57','Application Packages',
'58','Unknown Object Type',
'59','Unknown Object Type',
'60','Unknown Object Type',
'61','Archive Template',
'62','Unknown Object Type',
'63','Portal registry User Favourites',
'64','Mobile page',
'PeopleSoft are being smart again with - ' || a.objecttype ) as Type,
decode(UPGRADEACTION,0,'Copy',1,'Delete',2,'Manual',3,'Copy Properties') as Action
from sysadm.PSPROJECTITEM A
where a.objecttype <> '52'
and not (objecttype=0 and objectid2 = 2)
and not (objecttype=2 and objectid2 = 102)
union
SELECT
a.projectname,
decode(A.OBJECTVALUE2,' ','',A.OBJECTVALUE2) || decode(A.OBJECTVALUE1,' ','',A.OBJECTVALUE1) as Object,
'File Reference' as Type,
decode(UPGRADEACTION,0,'Copy',1,'Delete',2,'Manual',3,'Copy Properties') as Action
from sysadm.PSPROJECTITEM A
where a.objecttype = '52'
)
where projectname like &projectName
--order by 2,1
order by 3,2

Getting SQRs to Publish to the RDS/Report Manager Everytime

2008-02-13T14:05:00.009+10:00

See Also: http://capital-it.blogspot.com/2008/09/publishing-files-to-report-managerrds.html

In short if you want a process's output to appear in the RDS it has to end up residing in the same folder that is created for the process at run-time.

For this example we are going to be producing a .ltr file using an SQR process called 'PROCNAME'. In the end we want a file path like this, where 501 is the Process Instance number:

/opt/psoft8/HE900D9A/appserv/prcs/HE90D9A/log_output/SQR_PROCNAME_501/SQR_PROCNAME_501.LTR

Note that up to "log_output' is most likely going to be a system variable, set in something like setfile.sqc so it can be modified for each environment.

Note that the folder name (SQR_PROCNAME_501) must be in this format, where 'SQR' is the Process Type, 'PROCNAME' is the Process Name and '501' is the Process Instance Number.

The file name syntax is the same as the folder name (although at time of writing I think you may actually be able to get away with whatever file name you please).

Update: It appears App Engines require the files to be in the strict format "AE_ProcName_ProcInstance.*" where * is an extension. This was working when output type was Web/TXT.

The SQR code looks something like this:

#DEFINE FILEPREFIX e:\psoft8\he900d9a\appserv\prcs\HE90D9A\log_output\

! Folder
Move '{FILEPREFIX}' to $fileName
Concat 'SQR_PROCNAME_' with $fileName
Concat $prcs_process_instance with $fileName
Concat '\' with $fileName

! File.
Concat 'PROCNAME_' with $fileName
Concat $prcs_process_instance with $fileName
Concat '.LTR' with $fileName

New-Report $fileName

Using Outer Joins AND Effective Dated Logic

2008-01-16T16:19:00.000+10:00

How can you use outer joins on a table and still use effective dated logic? Something like this.

Note that the outer join will either return no rows, and thus a NULL value for EFFDT, or it will return a date and subsequent effective dated logic can be used.

AND C.BUSINESS_UNIT (+) = A.BUSINESS_UNIT
AND C.COMMON_ID (+) = A.COMMON_ID
AND C.SA_ID_TYPE (+) = A.SA_ID_TYPE
AND C.ITEM_NBR_CHARGE (+) = A.ITEM_NBR
AND
   (NULL = C.EFFDT
   OR
       (C.EFFDT =
           (SELECT MAX(C_ED.EFFDT)
           FROM sysadm.ps_ITEM_XREF C_ED
           WHERE C_ED.BUSINESS_UNIT = C.BUSINESS_UNIT
           AND C_ED.COMMON_ID = C.COMMON_ID
           AND C_ED.SA_ID_TYPE = C.SA_ID_TYPE
           AND C_ED.ITEM_NBR_CHARGE = C.ITEM_NBR_CHARGE
           AND C_ED.ITEM_NBR_PAYMENT = C.ITEM_NBR_PAYMENT
           AND C_ED.EFFDT <= SYSDATE)))

XML Publisher Example

2008-01-10T14:49:00.000+10:00

Example of using PeopleCode to generate XML and then merge with a predefined RTF template.

/* GUSAFN02 - HEUP Project, Michael Nitschke 27.11.2007 */
import PSXP_RPTDEFNMANAGER:*;
import GU_EN_ENROLMENTS:*;

Declare Function DeleteLocalFile PeopleCode PSXPFUNCLIB.FUNCLIB FieldFormula;
Declare Function GetDirSeparator PeopleCode PSXPFUNCLIB.FUNCLIB FieldFormula;

/* Function to populate a node based on given SQL. 
   &node:     Create the node in the location you want and pass through to be populated.
   &recName: Record that node data will be based on.  Must also match data structure returned from &sql.
   &sql:     SQL that will populate &rec and the node. */
Function AddNodeFromSQL(&node As XmlNode, &recName As string, &sqlName As string)
 Local Record &rec = CreateRecord(@("Record." | &recName));
 Local SQL &sql = GetSQL(@("SQL." | &sqlName), &emplid);
 While &sql.Fetch(&rec)
    Local XmlNode &nodeRow = &node.AddElement(&node.NodeName | "Row");
    For &f = 1 To &rec.FieldCount
       Local XmlNode &nodeFld = &nodeRow.AddElement(&node.NodeName | "." | &rec.GetField(&f).Name);
       &nodeFld.NodeValue = &rec.GetField(&f).Value;
    End-For;
 End-While;
End-Function;

/* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */

&emplid = %EmployeeId;
Local PSXP_RPTDEFNMANAGER:ReportDefn &oRptDefn;
Local XmlDoc &xmlDoc;
&xmlDoc = CreateXmlDoc("<?xml version='1.0'?><ACADEMIC_CHARGES/>");

/* Create header, config, programs, charges, unapplied charges and discounts xml elements, including data. */
Local XmlNode &nodeHeader = &xmlDoc.DocumentElement.AddElement("Header");
AddNodeFromSQL(&nodeHeader, "GU_STMT_HDR_DW", "GU_STMT_HDR");
Local XmlNode &nodeConfig = &xmlDoc.DocumentElement.AddElement("Config");
AddNodeFromSQL(&nodeConfig, "GU_STMT_CFG_DTL", "GU_STMT_CFG_DTL_AC");
Local XmlNode &nodeProgram = &xmlDoc.DocumentElement.AddElement("Program");
AddNodeFromSQL(&nodeProgram, "GU_STMT_PROG_DW", "GU_STMT_PROG");
Local XmlNode &nodeCharges = &xmlDoc.DocumentElement.AddElement("Charges");
AddNodeFromSQL(&nodeCharges, "GU_STMT_CHRG_DW", "GU_STMT_CHARGES");
Local XmlNode &nodeChargesGST = &xmlDoc.DocumentElement.AddElement("ChargesGST");
AddNodeFromSQL(&nodeChargesGST, "GU_STMT_CGST_DW", "GU_STMT_CHARGES_GST");
Local XmlNode &nodeUnappliedCharges = &xmlDoc.DocumentElement.AddElement("UnappliedPayments");
AddNodeFromSQL(&nodeUnappliedCharges, "GU_STMT_CHRG_DW", "GU_STMT_UNAPPLIED_PAYMENTS");
Local XmlNode &nodeDiscounts = &xmlDoc.DocumentElement.AddElement("Discounts");
AddNodeFromSQL(&nodeDiscounts, "GU_STMT_CHRG_DW", "GU_STMT_DISCOUNTS");
Local XmlNode &nodeMessages = &xmlDoc.DocumentElement.AddElement("Messages");
AddNodeFromSQL(&nodeMessages, "GU_STMT_MSG_DW", "GU_STMT_MESSAGES");

/* Create report definition and connect to the generated XML document. */
&rptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn("GU_ACAD_CHRG");
&rptDefn.Get();
&rptDefn.SetRuntimeDataXMLDoc(&xmlDoc);

/* Create output directories. */
&sDirSep = GetDirSeparator();
&RptOutputDir = GetEnv("PS_SERVDIR") | &sDirSep | "files" | &sDirSep | "XMLP" | &sDirSep | UuidGen();
&OutputDir = &RptOutputDir | &sDirSep | "RptInst";
&DataDir = &RptOutputDir | &sDirSep | "Data";
CreateDirectory(&OutputDir, %FilePath_Absolute);
CreateDirectory(&DataDir, %FilePath_Absolute);

/* Process. */
&rptDefn.OutDestination = &RptOutputDir;
&rptDefn.ProcessReport("", %Language_User, %Date, "");
CommitWork();

/* Display. */
&rptDefn.DisplayOutput();

/* Cleanup. */
DeleteLocalFile(&sOutputFile, %FilePath_Absolute);

Array example

2008-01-10T14:16:00.000+10:00

Nice little example of using arrays in PeopleCode. Probably a more elegant, 2 dimension solution but this works just fine.

This example was required to handle overriding GL distribution by Elements, which are on level 3. and element can appear on level 3 under many level 2 rows. Code loops through all possible rows on level 3 and sums total for each unique Element found. Must add to 100.


/* Check that sum of each given element, across all pin rows, adds to 100%. */

/* 2 arrays to hold all elements and sum of their percentages. */
&arrElements = CreateArray();
&arrPercent = CreateArray();

/* Populate the arrays. */
Local Rowset &rsGP_GL_DIST_DTL = GetRowset(Scroll.GP_GL_DIST_DTL);
For &i = 1 To &rsGP_GL_DIST_DTL.ActiveRowCount
   Local Rowset &rsGP_GL_DIST_PIN = &rsGP_GL_DIST_DTL(&i).GetRowset(Scroll.GP_GL_DIST_PIN);
   
   /* Check there are actually any element rows (if one does they all do, vice-versa). Populate arrays if found.*/
   If &rsGP_GL_DIST_PIN(1).GP_GL_DIST_PIN.PIN_NUM.Value = 0 Then
      &hasElements = False;
      Break;
   Else
      &hasElements = True;
      For &j = 1 To &rsGP_GL_DIST_PIN.ActiveRowCount
         &element = &rsGP_GL_DIST_PIN(&j).GP_PIN_DESCR_VW.DESCR.Value;
         &percent = &rsGP_GL_DIST_PIN(&j).GP_GL_DIST_PIN.PCT_TO_ALLOCATE.Value;
         &found = &arrElements.Find(&element);
         If &found = 0 Then
            &arrElements.Push(&element);
            &arrPercent.Push(&percent);
         Else
            &arrPercent [&found] = &arrPercent [&found] + &percent;
         End-If;
      End-For;
   End-If;
   
End-For;

/* Check for any elements that are <> 100% and advise with warning msg & highlighting the field. */
If &hasElements Then
   For &k = 1 To &arrPercent.Len
      
      &err = False;
      If &arrPercent [&k] <> 100 Then
         &err = True;
      End-If;
      
      For &i = 1 To &rsGP_GL_DIST_DTL.ActiveRowCount
         &rsGP_GL_DIST_PIN = &rsGP_GL_DIST_DTL(&i).GetRowset(Scroll.GP_GL_DIST_PIN);
         For &j = 1 To &rsGP_GL_DIST_PIN.ActiveRowCount
            If &rsGP_GL_DIST_PIN(&j).GP_PIN_DESCR_VW.DESCR.Value = &arrElements [&k] Then
               If &err Then
                  &rsGP_GL_DIST_PIN(&j).GP_GL_DIST_PIN.PCT_TO_ALLOCATE.SetCursorPos(%Page);
                  &rsGP_GL_DIST_PIN(&j).GP_GL_DIST_PIN.PCT_TO_ALLOCATE.Style = "PSERROR";
                  Error "Sum percentages of Element '" | &arrElements [&k] | "' do not total 100%.";
               Else
                  &rsGP_GL_DIST_PIN(&j).GP_GL_DIST_PIN.PCT_TO_ALLOCATE.Style = "PSEDITBOX";
               End-If;
            End-If;
         End-For;
      End-For;
      
   End-For;
End-If;

Decode, If, Case statements in SQL

2007-12-04T11:07:00.000+10:00

Two examples of evaluating 2 values and returning a given value based on which is earlier/bigger:

DECODE((SYSDATE - A.DUE_DT) - ABS(SYSDATE - A.DUE_DT), 0, 'OVERDUE', A.DUE_DT) AS DUE_DATE
CASE WHEN SYSDATE > A.DUE_DT THEN 'OVERDUE' ELSE to_char(A.DUE_DT) END AS DUE_DATE

Both do the same thing.

Related links:
http://www.4guysfromrolla.com/webtech/102704-1.shtml
http://www.sqlteam.com/article/the-case-for-case

Passing Parameters to SQR via the Process

2007-11-08T10:09:00.001+10:00

10.01.2008, update: seems Blogger has lost the uplaoded images for this post. Perhaps they will come back?

Update the Parameter List of your process definition. I this case I have used a Record.Field value.

PeopleCode to set the value of the Field (GU_GRDLPS_RC_WK.DESCR).

In your SQR you pick up the parameter by using the Input statement:

input $procAcadOrg 'Academic Organisation to distribute report to: (Optional, Press ENTER to continue)'

Errors running Application Engines

2007-10-17T15:47:00.003+10:00

If you get the following error:

"Message:
All Processing Suspended: Restart OPRID=S390765, RUNID=31-01-03112240, PI=2988

Description:
A pending process was found for the current Operator id, Run Control id, and Application Engine Program. This process either is running now or has abended. Please restart the pending process if it has abended, and allow it to run to completion, before submitting a new process request."

...you need to cleanup the dead rows in sysadm.ps_aeruncontrol.

delete from ps_aeruncontrol
where oprid = 'UserID';
commit;

Downloading from Grids - IE settings

2007-10-17T14:52:00.000+10:00

Problem:
We found a problem when tring to download to spreadsheets from grids. IE wouldn't open the spreadsheet, just refreshed a few times and then nothing.

Resolution:
Tools/Internet Options/
Security/Custom Level/
Downloading/Automatic prompting for downloads - tick enable.

Bad CSV layout from SQR

2007-10-17T10:44:00.001+10:00

I found that some CSVs had layout problems where the file being produced contained unwanted line breaks. This of course mucked up the layout when the CSV was viewed in Excel or whatever.

I added the following to the top of the SQR:

begin-setup
page-size 79 600
end-setup

The page-size command sets the number of rows and columns, respectively (rows weren't an issue).

Edit: In latest version of SQR use the following:

Declare-Layout my-layout

paper-size=(a4)

orientation=landscape

MAX-COLUMNS=600

End-Declare

Declare-Report THIS_REPORT

Printer-Type=POSTSCRIPT

LayOut=my-layout

End-Declare

Prompts driven by other prompts

2007-10-10T11:38:00.000+10:00

Sometimes you want to be able to filter the values returned by a prompt based on a value you have entered on another prompt on the page. For example you have selected 'Australia' in the first prompt and now you want the second prompt to only list states for that country.

This example uses Pay Groups and Run Types:
When the user has selected a 'Pay Group' the prompt for 'Run Type' will only return run types that are in that pay group:

The first record stores the values, in this case it has no keys.
Note the key structure and list items on the two prompt records:

Component Interfaces - What they can't do.

2007-10-10T10:39:00.000+10:00

The DoSaveNow() PeopleCode function does not trigger when run from a Component Interface. There is no indication, error or warning. There may be other misleading errors or warnings caused by the DoSaveNow() not being triggered.

SQL to lock all objects in a Project

2007-07-30T09:31:00.000+10:00

How to lock all the objects in a project, very useful when migrating mega projects.

INSERT INTO SYSADM.PSCHGCTLLOCK
SELECT
'yourID'
, A.OBJECTTYPE
, A.OBJECTID1
, A.OBJECTVALUE1
, A.OBJECTID2
, A.OBJECTVALUE2
, A.OBJECTID3
, A.OBJECTVALUE3
, A.OBJECTID4
, A.OBJECTVALUE4
, SYSDATE
, A.PROJECTNAME
, ' '
, ' '
FROM SYSADM.PSPROJECTITEM A
WHERE PROJECTNAME = 'projectName'
AND NOT EXISTS
(SELECT 'X'
FROM SYSADM.PSCHGCTLLOCK B
WHERE B.OBJECTTYPE = A.OBJECTTYPE
AND B.OBJECTID1 = A.OBJECTID1
AND B.OBJECTVALUE1 = A.OBJECTVALUE1
AND B.OBJECTID2 = A.OBJECTID2
AND B.OBJECTVALUE2 = A.OBJECTVALUE2)
AND OBJECTTYPE <> 30 -- (SQL for views)

Finding a Component in the Portal Registry

2007-07-30T08:49:00.002+10:00

define Component_Name = 'GU_EX_SCH_DOWNLOAD';

select distinct LEVEL, PORTAL_LABEL
from PSPRSMDEFN
where PORTAL_LABEL <> 'Root'
connect by PORTAL_OBJNAME = prior PORTAL_PRNTOBJNAME
start with PORTAL_URI_SEG2 = &Component_Name
order by LEVEL desc;

undefine Component_Name;

Fun with the DoModalComponent function.

2007-07-13T16:51:00.000+10:00

The example given in PeopleBooks is misleading. Read the description carefully -

The first three parameters are straighforward, and can be drag-dropped from the menu definition in your project (shortcut hint there).

The fourth parameter is a little different:

component_item_name

The component item name of the page to be displayed on top of the modal component when it displays. The component item name is specified in the component definition.

Note "The component item name is specified in the component definition"? Open up the component and find the page you want to navigate to.

For this parameter use the page's "Item Name", not the "Page Name".

It's always a bit nasty when the example is misleading and the explanation slightly obscured, I mean who reads the doco really, really carefully? ; )

Synchronising Delivered & Customised 1-1 tables

2007-06-20T10:13:00.001+10:00

UPDATE (years later): I ask myself why not just use another scroll level below the delivered record/scroll containing the sibling record? PeopleSoft will handle the inserts and updates automagically.

Following is likely wrong, but left for reference.

Rather than add custom fields to a PeopleSoft delivered record it is better to create a new record with the same keys that will serve to store new, custom fields. This table and the delivered table will have a 1-1 relationship and there will be less/no work for future upgrades.

To display and synchronise the data on this new table a work record is used on the page. The example below is using Griffith University (GU) records and delivered PS records.

Set default values on this derived record so that there will always be a row in the new table that matches the original table. (Someone will try to join to the new table and have no data at some stage). That is default the key values of the work record to those of the delivered record.
RowInit PeopleCode (GU Work Rec.RI) displays the synchronised data on the Work Record:

SQLExec("SELECT SOME_FIELD1, SOME_FIELD2 FROM GU_RECORD WHERE KEYFIELD1 = :1", PS_RECORD.KEYFIELD1.Value, GU_WORK_RECORD.SOME_FIELD1.Value);
SavePostChange PeopleCode (Component.PS_RECORD.SPC) then synchronises the tables with a 1-1 relationship (updated 22.10.2007):

Local Record &recGU_RECORD = CreateRecord(Record.GU_RECORD);

/* Populate. */
GetRecord(Record.GU_WORK_RECORD).CopyFieldsTo(&recGU_RECORD);
GetRecord().CopyFieldsTo(&recGU_RECORD);

/* Delete if required. */
If GetRecord().IsDeleted Then
&recGU_RECORD.Delete();
Else

/* Default (specific) values if required. */
If None(&recGU_RECORD.SOME_FIELD.Value) Then
&recGU_RECORD.GU_RECORD.SetDefault();
End-If;

/* Insert/update. */
&saved = &recGU_RECORD.Save();

End-If;

Setup and run 3C Communications

2007-06-07T09:36:00.000+10:00

Document on how to setup and run the new v9 3C communications. (Written by Kevin Spletter)

http://docs.google.com/Doc?id=df48r26b_3ffkjg4

Constrained

2007-05-17T17:51:00.001+10:00

just what is the restraint talking about?

select * from dba/user_constraints
where constraint_name like 'nnn';