Searching Through Views

If you make a change on a Record or View you should check its impact. App Designer doesn't search through SQL definitions but you can do that yourself in SQL:

select SQLID from PSSQLTEXTDEFN
where sqltext like '%SOME_TABLE%'

Posting CommGen to Report Manager

As delivered Communication Generation (CommGen) produces the final, merged output of letters on the Server. There is no way to configure it to send to Report Manager or Process Monitor.

However, there is an easy fix.

Each Process Instance has its own directory made at run-time, and all files in that directory are published to Process Monitor and Report Manager. We just need to make a simple tweak to create the Merged File Output in the process's Process Instance directory instead of the file destination directory as defined on the Run Control (by the way it would pay to modify the run control accordingly as well; hide the now unused File Output field etc.).

Application Package SCC_COMMGEN:Model:CommGen

/* This method merges all the communications that have been generated so far */
method MergeCommunications

...

   rem &sFileDestination = &rsRunCtlParms(1).SCC_CG_RUNCTL.FILE_PATH.Value;
   &sFileDestination = "log_output/AE_SCC_COMMGEN_" | &ProcessInstance;

Just change the one line, as above. This will have the merged file go to Report Manager and Process Monitor instead of a directory on the server. I suspect this issue may be a left over from version 7, where users could run on the client and have it appear on their local drive.

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;