Rowset to HTML Table

Another possibly useless tool:

<*--------------------------------------------------------------------------

 UNSW WUHR005 Michael Nitschke 12/11/2012
 Utility to take a rowset and return all its values.
 At time of writing only handles a single record, flat rowset.

 Example:

   import NS_NEXTGEN:Utilities:RowsetToHTML;

   /* Create and fill a rowset. */
   Local Rowset &rowset = CreateRowset(Record.SOME_RECORD);
   &rowset.Fill();

   /* Put the rowset's data into an HTML Area on a Page. */
   Local NS_NEXTGEN:Utilities:RowsetToHTML &rs2HTML = create NS_NEXTGEN:Utilities:RowsetToHTML(&rowset);
   NS_DERIVED.HTML_AREA_01.Value = &rs2HTML.RowsetAsHTML;

 The above will populate a HTML Area on a page with the values of the rowset.
 This utility could be extended to include heirarchical data.

----------------------------------------------------------------------------*>

class RowsetToHTML
   method RowsetToHTML(&rs As Rowset);
   property string RowsetAsHTML;
end-class;

method RowsetToHTML
   /+ &rs as Rowset +/
   
   Local string &style;
   &style = &style | "<style type=""text/css"">";
   &style = &style | "table.rowset {";
   &style = &style | "    border-width: 3px;";
   &style = &style | "    border-spacing: 0px;";
   &style = &style | "    border-style: solid;";
   &style = &style | "    border-color: black;";
   &style = &style | "    border-collapse: collapse;";
   &style = &style | "    background-color: white;";
   &style = &style | "}";
   &style = &style | "table.rowset th {";
   &style = &style | "    border-width: 1px;";
   &style = &style | "    padding: 4px;";
   &style = &style | "    border-style: dotted;";
   &style = &style | "    border-color: gray;";
   &style = &style | "    background-color: rgb(200, 200, 200);";
   &style = &style | "    -moz-border-radius: ;";
   &style = &style | "    font-size: small ;";
   &style = &style | "}";
   &style = &style | "table.rowset th.recname {";
   &style = &style | "    background-color: rgb(0, 0, 0);";
   &style = &style | "    color: white;";
   &style = &style | "    font-size: large;";
   &style = &style | "    font-weight: normal;";
   &style = &style | "    text-align: left;";
   &style = &style | "}";
   &style = &style | "table.rowset tr.r0 td {";
   &style = &style | "    background-color: rgb(240, 240, 240);";
   &style = &style | "}";
   &style = &style | "table.rowset tr.r1 td {";
   &style = &style | "    background-color: rgb(250, 250, 250);";
   &style = &style | "}";
   &style = &style | "table.rowset td {";
   &style = &style | "    border-width: 1px;";
   &style = &style | "    padding: 4px;";
   &style = &style | "    border-style: dotted;";
   &style = &style | "    border-color: gray;";
   &style = &style | "    background-color: white;";
   &style = &style | "    -moz-border-radius: ;";
   &style = &style | "    font-size: small ;";
   &style = &style | "}";
   &style = &style | "</style>";
   
   Local string &html;
   &html = &html | "<table class=""rowset"">";
   &html = &html | "<tr>";
   &html = &html | "<th class=""recname"" colspan=""" | (&rs(1).GetRecord(1).FieldCount + 1) | """>" | &rs(1).GetRecord(1).Name | "</th>";
   &html = &html | "</tr>";
   &html = &html | "<tr>";
   Local integer &f;
   &html = &html | "<th>row</th>";
   For &f = 1 To &rs(1).GetRecord(1).FieldCount
      &html = &html | "<th>" | &rs(1).GetRecord(1).GetField(&f).Name | "</th>";
   End-For;
   &html = &html | "</tr>";
   Local string &alternate = "1";
   Local integer &i;
   For &i = 1 To &rs.RowCount
      If &alternate = "1" Then
         &alternate = "0";
      Else
         &alternate = "1";
      End-If;
      &html = &html | "<tr class=""r" | &alternate | """>";
      &html = &html | "<td>" | &i | "</td>";
      For &f = 1 To &rs(&i).GetRecord(1).FieldCount
         &html = &html | "<td>" | &rs(&i).GetRecord(1).GetField(&f).Value | "</td>";
      End-For;
      &html = &html | "</tr>";
   End-For;
   &html = &html | "</table>";
   
   %This.RowsetAsHTML = &style | &html;
   
end-method;

Modifying v7 SQL that uses Variable Data

While upgrading (if there are any more upgrades) you'll eventually encounter a piece of SQL that filters on, say PS_PERSON_COMMUNICATION.ADMIN_FUNCTION and ACAD_CAREER. The latter is now on many child tables; VAR_DATA_XXXX.

For example:

select
  to_char(a.comment_dttm,'dd/mm/yyyy hh24:mi:ss')
, a.cmnt_category
, a.deptid
, a.cmnt_id
, a.comment_dt
, a.comments
from
  ps_person_comment a
where a.emplid = :1
and a.admin_function = :4
and a.acad_career=:2
and a.stdnt_car_nbr = :3
;

This can be resolved to something like the following, where the subselect/temp-table/on-the-fly thingies there are populated by a union between all VAR_DATA_XXXX tables that use ACAD_CAREER and STDNT_CAR_NBR

select
  to_char(a.comment_dttm,'dd/mm/yyyy hh24:mi:ss')
, a.cmnt_category
, a.deptid
, a.cmnt_id
, a.comment_dt
, a.comments

from
  ps_person_comment a
,
    (select 'ADMA' as admin_function, common_id, var_data_seq, acad_career, stdnt_car_nbr from ps_VAR_DATA_ADMA
    union
    select 'ADMP' as admin_function, common_id, var_data_seq, acad_career, stdnt_car_nbr from ps_VAR_DATA_ADMP
    union
    select 'CASN' as admin_function, common_id, var_data_seq, acad_career, stdnt_car_nbr from ps_VAR_DATA_CASN
    union
    select 'IPT1' as admin_function, common_id, var_data_seq, acad_career, stdnt_car_nbr from ps_VAR_DATA_IPT1
    union
    select 'SPRG' as admin_function, common_id, var_data_seq, acad_career, stdnt_car_nbr from ps_VAR_DATA_SPRG
    union
    select 'THES' as admin_function, common_id, var_data_seq, acad_career, stdnt_car_nbr from ps_VAR_DATA_THES) x

where a.common_id = &1
and a.admin_function = &4
and a.cmnt_id = b.emplid
and x.admin_function = a.admin_function
and x.common_id = a.common_id
and x.var_data_seq = a.var_data_seq
and x.acad_career = &2
and x.stdnt_car_nbr = &3
;

VAR_DATA_xxxx Tables

In version 7 variable data specific to each ADMIN_FUNCTION for a Communication  was stored on the COMMUNICATION Table. This has changed in v9, with a specific Table for each Admin Function.

For example: Records VAR_DATA_ADMA stores variable data for Admin function ADMA, VAR_DATA_SPRG for SPRG and so on.

PS_COMMUNICATION joins to PS_VAR_DATA_xxxx on COMMON_ID and VAR_DATA_SEQ.


IMPORTANT: This is completely different to a normal child table in PeopleSoft (and one has to question wtf they did it for). VAR_DATA_xxxx is not a child table of COMMUNICATION, in the typical PeopleSoft fashion. COMMUNICATION.VAR_DATA_SEQ is a Foreign Key, as in a standard, relational database. The reasoning for this is unknown to me as it doesn't match the other 30,000 Tables.

There is PeopleCode on COMMUNICATION.VAR_DATA_SEQ that handles synchronising its value between the two Tables. It might pay to spend 5 minutes understanding it.
______________________

Common scenario: Business was using a variable data field in v7 that is not on the associated VAR_DATA_xxxx Record in v9. What to do in v9?

Solution:

1. Add the Field(s) onto the VAR_DATA_xxxx Record.
2. Each Admin Function has an associated Variable Data page called COMM_VAR_xxxx, where xxxx is the Admin Function code. Add the fields to this page from Record.CM_DERIVED.
3. Use the following script to populate the v9 tables:

-- Update configuration table.

UPDATE PS_ADM_FUNCTN_TBL

SET

  ACAD_CAREER_AF = 'Y', STDNT_CAR_NBR_AF = 'Y', AID_YEAR_AF = 'Y', CAMPUS_EVNT_NBR_AF = 'Y', EVENT_MTG_NBR_AF = 'Y'

, ADMIN_FCN_PEOPLE = 'N', SSR_RS_CANDNBR_AF = 'N'

WHERE ADMIN_FUNCTION = 'THES';

COMMIT;

-- Move data from v7 PS_COMMUNICATION into v9 VAR_DATA_xxxx

DELETE FROM PS_VAR_DATA_THES;

INSERT INTO PS_VAR_DATA_THES (

  COMMON_ID

, VAR_DATA_SEQ

, SSR_RS_CANDIT_NBR

, SSR_RS_SUBMSSN_NBR

, ACAD_CAREER              -- < New field

, STDNT_CAR_NBR         -- < New field

, AID_YEAR             -- < New field

, CAMPUS_EVENT_NBR         -- < New field

, EVENT_MTG_NBR         -- < New field

)

SELECT

  EMPLID

, (ROW_NUMBER() OVER(PARTITION BY EMPLID ORDER BY EMPLID)) - 1 AS VAR_DATA_SEQ

, 0

, 0

, ACAD_CAREER

, STDNT_CAR_NBR

, AID_YEAR

, CAMPUS_EVENT_NBR

, EVENT_MTG_NBR

FROM

    (SELECT DISTINCT

      EMPLID

    , ACAD_CAREER

    , STDNT_CAR_NBR

    , AID_YEAR

    , CAMPUS_EVENT_NBR

    , EVENT_MTG_NBR

    FROM PS_COMMUNICATION@NSCONV.UNSW.EDU.AU

    WHERE ADMIN_FUNCTION = 'THES')

;

-- Update ps_communication to point to correct var_data/var_data_seq

-- This updates ps_communication by first finding the matching row in v7, and then finding the converted var data in v9.

-- It points to the new v9 data by updating ps_communication.var_data_seq.

update ps_communication x

set x.var_data_seq =

                (select var_Data_Seq

                from

                  ps_communication@nsconv.unsw.edu.au a

                , ps_var_data_thes b

                where a.emplid = x.common_id

                and a.comm_dttm = x.comm_dttm

                and a.institution = x.institution

                and a.admin_function = x.admin_function

                and a.comm_category = x.comm_category

                and b.common_id = a.emplid

                and b.acad_career = a.acad_career

                and b.stdnt_car_nbr = a.stdnt_car_nbr

                and b.aid_year = a.aid_year

                and b.campus_event_nbr = a.campus_event_nbr

                and b.event_mtg_nbr = a.event_mtg_nbr

                )

where x.admin_function = 'THES'

;

commit;

Dynamic Variables

For want of a better name (there probably is a correct nomenclature) I've never know there are "dynamic variables" in PeopleCode. This works:

&x = @(&VAR_FLD1);

Could not connect to SMTP host. Connection refused.

Are you sure this is running on the correct server? Really, really sure? Sure it's not running on PSNT for some reason? Really, really sure?

Sometimes we spend hours going down dead-ends, expecting to see what we expect to see, when the whole time the error is right under our noses.

In this case I just spent hours, over days, trying to track down why emails from App Engines wouldn't go through the SMTP server. I was fully expecting them to be running on the Unix box, but for one reason or another all App Engines were being sent to the NT box by default, where there was no SMTP server setup.

http://en.wikipedia.org/wiki/Cognitive_bias


Errors include:

Could not connect to SMTP host: localhost, port: 25 

SMTP sendMail failed (server :0).  Cannot send email to localhost, port: 25;

Stealth Migrations

Run the SQL below to check for any Record Fields that may be contained in NS_XXX records. These will need to be replaced/removed to ensure they do not get migrated to the CS9 environments (stealth migration).

select distinct r.projectname, r.objectvalue1, rf.fieldname from
(select distinct projectname, objectvalue1
 from psprojectitem p
 where p.objectvalue1 in (select distinct objectvalue1 prj1
                          from psprojectitem prj1
                          where prj1.projectname like 'NS%'
                          and prj1.objecttype = 0
                          and prj1.projectname = p.projectname)
) r,
psrecfield rf
where r.objectvalue1 = rf.recname
and rf.fieldname in (select distinct rf.fieldname
                     from psrecfieldall rf
                     where rf.recname in (select distinct b.objectvalue1
                                          from psprojectitem b
                                          where b.objecttype = 0)
                    and rf.fieldname not like 'NS%'
minus
select d1.fieldname from psdbfield@NSFLDCHK.UNSW.EDU.AU d1);

Adding File Type to Report Manager Output

To add a new file type/extension tot eh lsit of possibel extension that Rpt. Mgr. will display, do this:

Add the following line in web.xml (/data/psoft/ocsv2/psft/pt/8.52/webserv/ocsv2/applications/peoplesoft/PORTAL.war/WEB-INF/web.xml)

<mime-mapping> <extension> uef </extension> <mime-type> text/plain </mime-type> </mime-mapping>

 

Reboot webserver

PeopleTools > Process Scheduler > System Settings 

Add the UEF in Tab=> Distribution File Options and reboot the process scheduler.

Population Selection - Context Definition

It looks like by default it is not in 'development' mode, and you can't make changes to the delivered Context Definition, or its Selection Mapping.
Change the configured value and all the fields on the configuration pages become editable.

SELECT *
FROM PS_SCCPS_INSTALL;

This should equal 'DEV'.

Instance Variables

This does not work:

If any(&instanceVariable) Then
...

This does work:

Local string&x = &instanceVariable;
If any(&x) Then
...

Haven't had time to look at why that is the case yet.

User has no access to Component Interface - Security

Use the following to compare a user that does have access to a user that doesn't. Provide the user that does, the user that doesn't, and the CI name.

Will return the Role, Permission List that is missing.

select * from (
select
  r.rolename
, c.oprclass
, ci.bcname
from
  PSOPRCLS c
, PSROLECLASS r
, PSAUTHBUSCOMP ci
where c.oprid = 'E87637'  -- User that DOES have access.
and r.classid = c.oprclass
and ci.classid = c.oprclass

minus

select
  r.rolename
, c.oprclass
, ci.bcname
from
  PSOPRCLS c
, PSROLECLASS r
, PSAUTHBUSCOMP ci
where c.oprid = 'S3286436' -- User that is missing access
and r.classid = c.oprclass
and ci.classid = c.oprclass

--) where bcname like 'HCR_PERSONAL_DATA_SRV'
) where bcname like 'AMS_CI_CHESSN_DATA'

Handling XML Files in SQR

Nice little example of reading an XML file, {input}, and using it to update values in a Table. Note that it first inserts the XML file's data into a CLOB field at the end of the Table it is updating. Field.RAWXML.

!*******************************************************************************
! Procedure:  Read-Input-File                                                  *
! Description: Loads Request File data into Suspense tables                    *
!              using Oracle XML DB functions to extract data from the XML      *      
!*******************************************************************************
!
begin-procedure Read-Input-File    

#debugp show ''
#debugp show 'Enter procedure Read-Input-File'

  ! Read file in 
  open $Request_Filename as {INPUT} for-reading record=32767:fixed_nolf status=#result
  if #result <> 0
    show {REQUEST_FILE_OPEN_ERR_MSG}
    stop quiet
  end-if

  let $SQL-STATEMENT = 'amssr197.sqr, Read-Input-File, insert into PS_AMS_CART_RQ_HDR' 
  
begin-sql on-error=Sql-Error
INSERT INTO PS_AMS_CART_RQ_HDR (
        PRCSINSTANCE,
        AMS_SYSTEM_CODE,
        AMS_CART_TIME_STMP,
        AMS_ABN_NBR,
        AMS_CART_USER_ID,
        AMS_CART_RECS,
        RAWXML
   ) values ( 
        #prcs_process_instance,
        ' ',
        0,
        0,
        ' ',
        0,
        empty_clob()
    )        
end-sql

  
  while 1=1
    let $xml = ''
    read {INPUT} into $xml:32767 status=#result
    
    if #result <> 0
        show {SUSPENSE_TABLE_ERR_MSG}
        stop quiet
    end-if
    if #end-file
        break
    end-if
    
        let #xml = length($xml)
        
    ! Begin: QC1555
    ! The given XML file may have Microsoft Byte Order Mark at the beginning. If so just trim it off else can't be read.
    ! http://en.wikipedia.org/wiki/UTF-8#Byte_order_mark
    let $checkBOM = substr($xml, 1,4) 
    if $checkBOM = '0xEF' or $checkBOM = '0xBB' or $checkBOM = '0xBF'
        let $xml = substr($xml, 5, #xml - 4)
    end-if 
    let $checkBOM = substr($xml, 1,3) 
    if $checkBOM = '' 
        let $xml = substr($xml, 4, #xml - 3)
    end-if
    let #xml = length($xml)
    ! End: QC1555
    
#ifdef debugx
         show '$checkBOM= ' $checkBOM
        show '    xml : ' $xml
#endif 

    let $SQL-STATEMENT = 'amssr197.sqr, Read-Input-File, update PS_AMS_CART_RQ_HDR(RAWXML)' 
  
begin-sql on-error=Sql-Error
declare
    cartXml CLOB;;
begin
    SELECT rawXML INTO cartXML
    FROM PS_AMS_CART_RQ_HDR 
    WHERE PRCSINSTANCE = #prcs_process_instance
    FOR UPDATE;;
    
    DBMS_LOB.WRITEAPPEND(cartXml, #xml, $xml);;
end;;
end-sql
#ifdef debugx
    commit
#endif

  end-while
  
  let $SQL-STATEMENT = 'amssr197.sqr, Read-Input-File, select PS_AMS_CART_RQ_HDR' 
  
begin-select on-error=Sql-Error
hdr.systemCode
hdr.cartTimeStamp
hdr.instAbn
hdr.userId
hdr.numRecords

   FROM 
   XMLTABLE('/studentStudyCircRequest005'
      PASSING (SELECT XMLTYPE.createxml(rawXML) FROM PS_AMS_CART_RQ_HDR WHERE PRCSINSTANCE = #prcs_process_instance) 
      COLUMNS
        systemCode      varchar2(4)  PATH 'header/system',
        cartTimeStamp   number(14,0) PATH 'header/timestamp',
        instAbn         number(11,0) PATH 'header/instAbn',
        userId          varchar2(12) PATH 'header/userId',
        numRecords      number(38,0) PATH 'trailer/numRecords'
        ) hdr
end-select

    let $SQL-STATEMENT = 'amssr197.sqr, Read-Input-File, update PS_AMS_CART_RQ_HDR' 
  
begin-sql !on-error=Sql-Error
UPDATE PS_AMS_CART_RQ_HDR 
        set AMS_SYSTEM_CODE    = &hdr.systemCode,
            AMS_CART_TIME_STMP = &hdr.cartTimeStamp,
            AMS_ABN_NBR        = &hdr.instAbn,
            AMS_CART_USER_ID   = &hdr.userId,
            AMS_CART_RECS      = &hdr.numRecords
   WHERE PRCSINSTANCE = #prcs_process_instance 
end-sql   

Redirecting a User after Login / Logon

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


Note You're going to need a Global Variable to hold whether the user has been redirected yet or not, we only want to redirect once. You could easily put them into an endless redirect loop.

Global boolean &blnStudentHasBeenRedirected;

(You have to place that just after end-class;)

There is a Method getHomepageJS that, as its name implies, get JavaScript for the home page.

Return GetHTMLText(HTML.PT_HP2_JS_INCLUDE, &cookieJS, &refreshJS, &refreshPgltJS, &saveWarnCrossDomainJS, &saveWarnJS, &hpDndJS | Char(10) | Char(13) | %This.getCSSOverride());

Note the getCSSOverride method. This is where I've added my extra JavaScript, the 6th bind variable.

method getCSSOverride
   /+ Returns String +/
   
   /* derived classes can override this method to add their own css override */
   Local string &override = "";
   If %Request.BrowserType = "IE" Then
      &override = "<!--[if lt IE 9]>" | Char(10) | Char(13) | "<script type=""text/javascript"" src=""" | %Response.GetJavaScriptURL(HTML.AMS_IE9) | """></script>" | Char(10) | Char(13) | "<![endif]-->";
   End-If;
   Local AMS_PT_BRANDING:AmsHeader &myAmsHeader = create AMS_PT_BRANDING:AmsHeader();
   If &myAmsHeader.isStudent Then
      &override = &override | Char(10) | Char(13) | "<link rel=""stylesheet"" href=""" | %Response.GetStyleSheetURL(StyleSheet.AMS_SSS_STYLE_REF) | """ type=""text/css"">";
      
      /* Begin: QC1302 */
      If &blnStudentHasBeenRedirected <> True Then
         &blnStudentHasBeenRedirected = True;
         &override = &override | Char(10) | Char(13) | "<script type=""text/javascript"">window.top.location.replace(""../c/AMS_EOL.AMSW3_STDNT_HOME.GBL"");</script>";
      End-If;
      /* End: QC1302 */
      
   End-If;
   Return &override;
end-method;

If they're a student, and they haven't already been redirected once, redirect them to the student home page.

Data Integrity Error

Data Integrity Error (124,85)

The data structure from the page does not match the data structure in the database.

This error often appears after you've made a change to the Component. 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. And you've been forced to search the web for an answer, and now you're reading this sentence like approximately one person per day does. Hello.

Run a trace file for each SQL statement.

You'll need to log out and back in again, this time via the link for tracing on the log in page. If you can't see it go ask your friendly infrastructure team to/how to enable it.

Trace each SQL statement and then search the trace file for 'deserialization integrity failure'. This will point you to the object that is causing the issue.

What's happened is that the version number on the object, and the version number on Table PSVERSION no longer match. There is an inconsistency between PeopleSoft's list of latest versions of objects and the version as reported on the object.

This can be fixed in two ways:

1) by running the AppEngine VERSION, or


2) If you want a quick fix and you don't mind having your ID as the last person that updated the object, you can simply open the object causing trouble, make any minor change to it, save it, and now the versions will match up again.

More detail here:
http://peoplesoftdbafriend.blogspot.com.au/2012/09/version-application-engine.html

Content Reference Menu Path SQL

How to find a "lost"content reference.

(Note that the 'Valid From Date' of the parent Folder must be less than a content reference/common mistake post migration)

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

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

Change Font in SQR

Sometimes the easiest things...

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