Move a File Using PeopleCode

There's no generic function to move files in the PeopleCode universe, only to open them. Here's an elegant solution using the Java io package.

Local JavaObject &fromFile = CreateJavaObject("java.io.File", &strFromPath);
Local JavaObject &toFile = CreateJavaObject("java.io.File", &strToPath);
&fromFile.renameTo(&toFile);

And this is where our ED workflow redesign team went insane.

Tracing Application Engines

Tracing particular Process Definitions
This SQL script can make things a little clearer.

select '-TRACE ' || (
1    + -- Initiate the Application Engine Step trace
2    + -- Initiate the Application Engine SQL trace
4    + -- Trace dedicated Temp Table Allocation to AET file
128  + -- Initiate the Statement Timings trace to file, which is similar to the COBOL timings trace to file
256  + -- Initiate the PeopleCode Detail to the file for the Timings trace
1024 + -- Initiate the Statement Timings trace, but, instead of writing to the trace file, this trace stores the results in the following tables: PS_BAT_TIMINGS_LOG and PS_BAT_TIMINGS_DTL
2048 + -- Adding this value requests a database optimizer trace file
4096 + -- Request a database optimizer to be inserted in the Explain Plan Table of the current database
8192   -- This value sets a trace for Integration Broker transform programs
) as TRACE from dual;

select '-TOOLSTRACESQL ' || (
1    + -- Trace SQL statements
2    + -- Trace SQL statement variables
4    + -- Trace SQL connect, disconnect, commit and rollback
8    + -- Show fetched rows (indicates that it occurred, not data)
16   + -- Show all other API calls except ssb
32   + -- Set Select Buffers (identifies the attributes of columns to be selected).
64   + -- Show database API specific calls
128  + -- Show COBOL statement timings
256  + -- Show Sybase bind information
512  + -- Show Sybase fetch information
4096 + -- Show manager information
8192   -- Show Mapcore information
) as TOOLSTRACESQL from dual;

select '-TOOLSTRACEPC ' || (
1    + -- Trace instructions
2    + -- List the program
4    + -- Show assignments to variables
8    + -- Show fetched values
16   + -- Show stack
64   + -- Trace start of programs
128  + -- Trace external function calls
256  + -- Trace internal function calls
512  + -- Show parameter values
1024 + -- Show function return values
2048   -- Trace each statement in program
) as TOOLSTRACEPC from dual;

Tracing on the Server:
./psft/pt/8.52/appserv/*/psappsrv.cfg is where you can set tracing for Batch server instances.

[Trace]
;=========================================================================
; Trace settings
;=========================================================================
; SQL Tracing Bitfield
;
; Bit       Type of tracing
; ---       ---------------
; 1         - SQL statements
; 2         - SQL statement variables
; 4         - SQL connect, disconnect, commit and rollback
; 8         - Row Fetch (indicates that it occurred, not data)
; 16        - All other API calls except ssb
; 32        - Set Select Buffers (identifies the attributes of columns
;             to be selected).
; 64        - Database API specific calls
; 128       - COBOL statement timings
; 256       - Sybase Bind information
; 512       - Sybase Fetch information
; 1024      - SQL Informational Trace
; Dynamic change allowed for TraceSql and TraceSqlMask
TraceSQL=0

;-------------------------------------------------------------------------
; PeopleCode Tracing Bitfield
;
; Bit       Type of tracing
; ---       ---------------
; 1         - Trace Evaluator instructions  (not recommended)
; 2         - List Evaluator program        (not recommended)
; 4         - Show assignments to variables
; 8         - Show fetched values
; 16        - Show stack
; 64        - Trace start of programs
; 128       - Trace external function calls
; 256       - Trace internal function calls
; 512       - Show parameter values
; 1024      - Show function return value
; 2048      - Trace each statement in program  (recommended)
; Dynamic change allowed for TracePC
TracePC=0

;-------------------------------------------------------------------------
; AE Tracing Bitfield
;
; Bit       Type of tracing
; ---       ---------------
; 1         - Trace STEP execution sequence to AET file
; 2         - Trace Application SQL statements to AET file
; 4         - Trace Dedicated Temp Table Allocation to AET file
; 8         - not yet allocated
; 16        - not yet allocated
; 32        - not yet allocated
; 64        - not yet allocated
; 128       - Timings Report to AET file
; 256       - Method/BuiltIn detail instead of summary in AET Timings Report
; 512       - not yet allocated
; 1024      - Timings Report to tables
; 2048      - DB optimizer trace to file
; 4096      - DB optimizer trace to tables
TraceAE=0

;------------------------------------------------------------------------
; Performance Monitor Tracing Bitfield
;
; Bit       Type of tracing
; ---       ---------------
; 1         - Trace All performance monitor agent activity
TracePPM=0

Portal Navigation Path - Oracle PL/SQL Function

Thanks to Jake Gowens a.k.a. Zapp Brannigan

FUNCTION navString (prtlName IN CHAR, prtlObjName IN CHAR) RETURN VARCHAR2 IS

  prtlLabel         VARCHAR2(30);
  prtlParent        VARCHAR2(30);
  result            VARCHAR2(255);

BEGIN

  prtlLabel := '';

  SELECT MAX(PORTAL_LABEL), MAX(PORTAL_PRNTOBJNAME)
    INTO prtlLabel, prtlParent
    FROM PSPRSMDEFN
   WHERE PORTAL_NAME = prtlName
     AND PORTAL_OBJNAME = prtlObjName
     AND PORTAL_REFTYPE = 'C';

  if prtlLabel IS NOT NULL then
    if prtlParent = 'PORTAL_ROOT_OBJECT' then
      result := prtlLabel;
    else
      result := navStringF(prtlName, prtlParent) || ' > ' || prtlLabel;
    end if;
  end if;

  return result;

END navString;

Example:
Select navString(a.portal_name, a.portal_objname)
From psprsmdefn a
Where portal_uri_seg2 = 'JOB_DATA';

Returns:
Workforce Administration > Job Information > Job Data

Automatically Generate Upgrade Scripts

Snippet of PeopleCode that takes a Project and creates an upgrade script for all the Records in that Project. Does 90%+ of the work for you.

Requires a Prompt to select the Project, a button to put this code under, and a HTML Area to output the results to.

If None(NS_MN_TEST.PROJECTNAME.Value) Then
   MessageBox(0, "", 0, 0, "Enter a Project Name");
Else
   
   Local string &title, &result, &fields;
   Local integer &f;
   
   Local Record &recPSPROJECTDEFN = CreateRecord(Record.PSPROJECTDEFN);
   &recPSPROJECTDEFN.PROJECTNAME.Value = NS_MN_TEST.PROJECTNAME.Value;
   &recPSPROJECTDEFN.SelectByKey();
   
   Local Record &recPSOPRDEFN = CreateRecord(Record.PSOPRDEFN);
   &recPSOPRDEFN.OPRID.Value = &recPSPROJECTDEFN.LASTUPDOPRID.Value;
   &recPSOPRDEFN.SelectByKey();
   
   &title = &title | "/*" | Char(13);
   &title = &title | "     WORK UNIT:     " | &recPSPROJECTDEFN.PROJECTNAME.Value | Char(13);
   &title = &title | "     DEVELOPER:     " | &recPSOPRDEFN.OPRDEFNDESC.Value | Char(13);
   &title = &title | "     DATE:          " | String(%Date) | Char(13);
   &title = &title | "     DESCRIPTION:   " | &recPSPROJECTDEFN.PROJECTDESCR.Value | Char(13);
   &title = &title | "     INSTRUCTIONS:  EXECUTE IN THE TARGET" | Char(13);
   &title = &title | "     DEPENDENCIES:  " | Char(13);
   &title = &title | "*/" | Char(13) | Char(13);
   
   &title = &title | "-- List of Records that have data converted by this script:" | Char(13);
   
   
   Local SQL &sql = CreateSQL("SELECT OBJECTVALUE1 FROM PSPROJECTITEM A, PSRECDEFN B WHERE A.PROJECTNAME = :1 AND A.OBJECTTYPE = 0 AND A.OBJECTID2 = 0 AND B.RECNAME = A.OBJECTVALUE1 AND B.RECTYPE = 0", NS_MN_TEST.PROJECTNAME.Value);
   
   Local string &recName;
   While &sql.Fetch(&recName)
      
      If Left(&recName, 3) = "NS_" Then
         
         &title = &title | "--    " | &recName | Char(13);
         
         &result = &result | Char(13) | "-- " | &recName | Char(13);
         &result = &result | "Delete From ps_" | &recName | ";" | Char(13);
         Local Record &rec = CreateRecord(@("record." | &recName));
         &result = &result | "Insert Into ps_" | &recName | "(" | Char(13);
         For &f = 1 To &rec.FieldCount
            If &f = 1 Then
               &fields = "  " | &rec.GetField(&f).Name | Char(13);
            Else
               &fields = &fields | ", " | &rec.GetField(&f).Name | Char(13);
            End-If;
         End-For;
         &result = &result | &fields | ")" | Char(13);
         &result = &result | "Select " | Char(13) | &fields | "From ps_" | &recName | "@nsconv.unsw.edu.au;" | Char(13);
         
      End-If;
      
   End-While;
   
   &title = &title | Char(13) | "SPOOL ON;" | Char(13);
   &title = &title | "SET ECHO ON;" | Char(13);
   &title = &title | "SPOOL 'c:\temp\" | Substring(&recPSPROJECTDEFN.PROJECTNAME.Value, 4, 7) | "_data_conversion.log' REPLACE;" | Char(13);
   
   
   NS_MN_TEST.HTMLAREA1.Value = &title | Char(13) | Char(13) | &result | Char(13) | Char(13) | "commit;" | Char(13) | Char(13) | "SPOOL OFF;";
   
End-If;
Paste your text here.

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.