AE/SQR output files to show up in the Process Monitor/Report Repository

Application Engine Example

In tools 8.4x and beyond, any files opened using the GetFile will actually be automatically transfered to the Report Repository.

Local File &f;
&f = GetFile("summary_log.txt", "w", %FilePath_Relative);
&f.WriteLine("Hello World");
&f.close()

The key thing about this code is the %FilePath_Relative parameter. 

SQR Example

Here is a procedure that you can put in an SQC. A variable named $weboutputdir will be populated with the director where you want to open the file.

begin-procedure get-web-outdir

 if $sqr-platform = 'WINDOWS-NT'
    let $dirSep = '\'
 else
    let $dirSep = '/'
 end-if

begin-select
CDM.PRCSOUTPUTDIR
  let $weboutputdir = rtrim(&CDM.PRCSOUTPUTDIR, ' ')  ||  $dirSep

  FROM PS_CDM_LIST CDM WHERE CDM.PRCSINSTANCE = #prcs_process_instance

end-select
end-procedure

Integration broker - third party integration

In case of any issues, check the below steps:

1. Check if web server/s are up and running. 

2. See if communication between app server/s and web server/s are fine for specific port

3. Ensure firewall is allowing packets to pass through and there is no block. Do transaction from PIA and let network team monitor.

4. Telnet service is always running in App server/s and Web server/s

5. Password is encrypted in advanced gateway properties file

6. Ensure third party certificates are installed using pskeymanager and web servers has been rebooted

7. Check the host name has valid entry in host file (Start -> Run -> Drivers | etc\hosts file). Need to restart the computer after any change.

8. Third party has done the required configuration at their end.

PeopleSoft Coding Standard

1. Declaring Local Variables
- When writing PeopleCode, declare ALL local variables that will be used. Declaring the specific type of variable needed improves performance.
- When variables are declared, the PeopleCode editor can check the compatibility of variables used in an operation, or passed as function arguments during compile time.
- For programming languages that allow dynamic allocation of variables, it is a common programming error to misspell variable names. However, on the PeopleCode editor, when all variables are consciously declared, it is easy to check for misspelled variable names.

2. Usage of the %Table() meta-SQL When Writing SQL Statements
- When writing SQLExec statements, the common practice is to hard-code table/view names. For example:
SQLExec("Select FIELD1, FIELD2 From PS_RECORD_TBL Where CRITER1 = :1 AND CRITER2 > :2", &IN1, &IN2, &OUT1, &OUT2);
A problem with the above code is that information in the PeopleCode string literals cannot be classified by the compiler and saved into the PeopleTools reference tables.

Use the meta-SQL %Table() in SQL statements to refer to SQL tables or views. With a little effort, the above code can be transformed into the following:
SQLExec("Select FIELD1, FIELD2 From %Table(:1) Where CRITER1 = :2 AND CRITER2 > :3", Record.RECORD_TBL, &IN1, &IN2, &OUT1, &OUT2);

Standalone Rowset’s Fill Method

A standalone rowset can be populated with rows directly from the database by using the Fill method. It’s quite common to see where strings in the Fill method to reference the FILL correlation ID inside subqueries to limit the resultset.

Local Rowset &rs = CreateRowset(Record.RF_INST_PROD);

&rs.Fill("WHERE RBTACCTID = :1 AND EXISTS (SELECT 1 FROM PS_RF_INST_PROD_ST WHERE SETID = FILL.SETID AND INST_PROD_ID = FILL.INST_PROD_ID AND INST_PROD_STATUS = 'INS')", &acctid);

 However, if you observe it closely (ignoring related-language processing), the Fill method would basically just generate a SQL statement in the following manner:

SELECT {list of fields from primary record} FROM %TABLE({primary record}) FILL {wherestring}

Interestingly, it also means you could fill a rowset with data not coming from the primary record by using a UNION. For example (notice the fields doesn’t even have to match the primary record):

Local Rowset &rs = CreateRowset(Record.PSOPRCLS); /* Fields: OPRID, OPRCLASS */

&rs.Fill("WHERE 1=0 UNION SELECT OPRID, ROWSECCLASS FROM PS_ABS_DEPT2_VW WHERE EMPLID = :1", &emplid);

However, it might be handy for quick prototypes and filling message rowsets with data directly without the need for new SQL Views and CopyTo operation.

Run JavaScript on your PeopleSoft pages conditionally

Here, PeopleCode sets the logic that determines when the JavaScript code will run.

This is not as simple as dropping a HTML Area on your page and setting the script in PeopleCode. This is because the value in the HTML Area field remains and the JavaScript code will keep executing at subsequent page refreshes.

Steps:

Lets have a derived/work record TEST_WRK And field HTMLAREA (TEST_WRK – HTMLAREA).

1. Create a HTML definition as your javascript template. Include all the necessary user-defined javascript functions that you need. Eg Html Definition is TESTJS

<input type="hidden" name="USERJSINJECTION" value=""/>
<script type="text/javascript">
function addLoadEvent(func) {
  var oldonload = window.onload;
  if (typeof window.onload != 'function') {
   window.onload = func;
  } else {
   window.onload = function() {
     oldonload();
     func();
   }
  }
}
 
function user_function1() {
  window.open("","toolbar = no");
}
function user_function2() {
  alert('Hello from user javascript');
}
 
addLoadEvent(function() {
  %bind(:1)
});
</script>

2. At the scroll level 0 of your PS page, insert a HTML Area control. Assign this to the TEST_WRK.HTMLAREA field.

3. Again at scroll level 0 of your page, insert an editbox and assign this again to TEST_WRK.HTMLAREA. And Set the Following Field Property

a. On the Use tab, check Invisible and Modifiable by JavaScript.

b. On the General tab, set Page Field Name to USERJSINJECTION.

4. Now in PeopleCode, to execute your javascript function.

GetLevel0()(1).TEST_WRK.HTMLAREA.Value = GetHTMLText(HTML.TESTJS, "user_function1()");

Traversing component buffer - one-line "shortcut" example

F = GetLevel0()(1).EMPL_CHECKLIST(1).EMPL_CHKLST_ITM(1).EMPL_CHKLST_ITM.BRIEFING_STATUS;
      Rowset   Row      Rowset   Row     Rowset     Row     Record           Field

COBOL Trace

PART I – IDENTIFY THE TRACE SETTING

The trace setting that you choose to use will be determined by a single number that is the sum of the values corresponding to each option that you wish to include.   These are the values for each of the options available to you:

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 SSBs

32 = Set Select Buffers (holds values of columns to be selected.

64 = Database API specific calls

128 = COBOL statement timings

256 = Sybase Bind information

512 = Sybase Fetch information

So if you only wanted to trace the SQL Statements, SQL Statement variables and Row Fetch, you would use 11 (1 + 2 + 8 = 11).

If your analyst provides you with a setting to use, use that setting.

If you do not know which setting to use, use 255.

PART II – PROCESS SCHEDULER TRACING

1. Go to process type COBOL SQL and copy the parameter list

2. Search your COBOL process and go to Override parameters tab. Select override and paste the content from clipboard

3. Now locate the pair of forward slashes between INSTANCE and DBFLAG in the parameter list and insert your trace setting in between these two slashes (in my case I am using 255)

PART III – TRACING ON THE APP SERVER

Step 1 – Set the trace on the App Server Configuration File

TraceSQLMask is the variable that you will set.  Use the value that you determined in Part I.  If you do not know what setting to use, use 255.

Once the trace has been set on the App Server, ALL remote call cobol jobs will be traced, so use this judiciously in your production environment.  Once you have retreived the trace, be certain to turn it off.

Step 2 – Retrieve the trace

Once the job has been completed, you can turn off the trace at the App Server and retrieve the trace.  The trace will be located in:

<PSHOME>/Appserv/<domain>/Logs/

The name of the trace will start with ‘cobsql_xxxxxx.trc’ where xxxxxx is a date time stamp indicating when the trace was made.

Dynamically hide a column in grid

Use the below code:

GetGrid(Page.PAGE_NAME, "GRID_NAME").GetColumn("COLUMN_FIELDNAME").Visible = False

Ensure providing the COLUMN_FIELDNAME general tab of in page field property.

Finding navigation based on component name - II

Another alternative way is to use SQL. However this requires creating a PL/SQL function and is only applicable for ORACLE database only. Other database platforms may need to write their own functions to implement this functionality.

PL/SQL source code for the Function:

CREATE OR replace FUNCTION fx_get_portal_map (l_portal_name    VARCHAR2, 

                                              l_portal_reftype VARCHAR2, 

                                              l_portal_objname VARCHAR2, 

                                              l_level          NUMBER, 

                                              l_type           VARCHAR2, 

l_count_max      INTEGER DEFAULT 10) 

RETURN VARCHAR2 

IS 

  pl_count              INTEGER := 0; 

  pl_portal_objname     psprsmdefn.portal_objname%TYPE := l_portal_objname; 

  pl_portal_seq_num     psprsmdefn.portal_seq_num%TYPE := 0; 

  pl_portal_label       psprsmdefn.portal_label%TYPE := ' '; 

  pl_portal_prntobjname psprsmdefn.portal_prntobjname%TYPE := ' '; 

  CURSOR cur_1 IS 

    SELECT portal_prntobjname, 

           portal_label, 

           portal_seq_num 

    FROM   psprsmdefn 

    WHERE  portal_name = l_portal_name 

           AND portal_reftype = l_portal_reftype 

           AND portal_objname = pl_portal_objname; 

BEGIN 

  WHILE pl_count <> l_level LOOP 

      pl_count := pl_count + 1; 

      EXIT WHEN pl_count > l_count_max; 

      OPEN cur_1; 

      FETCH cur_1 INTO pl_portal_prntobjname, pl_portal_label, pl_portal_seq_num 

      ; 

      IF cur_1%found THEN 

        pl_portal_objname := pl_portal_prntobjname; 

      ELSE 

        pl_portal_label := ' '; 

        pl_portal_seq_num := 0; 

        EXIT WHEN cur_1%notfound; 

      END IF; 

      CLOSE cur_1; 

  END LOOP; 

  IF l_type = 'S' THEN 

    RETURN pl_portal_seq_num; 

  ELSE 

    RETURN pl_portal_label; 

  END IF; 

END; -- Function FX_GET_PORTAL_MAP

SQL Query to Get the Navigation. Note you can uncomment the portal_uri_seg2 to query for a specific component.

SELECT a.portal_objname, 

       a.portal_linkobjname, 

       a.portal_seq_num                              seq, 

       Ltrim(Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 7, 'L') 

             || 

       Decode( 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 7, 'L'), ' ', '', 

                                                                  ' > ') 

       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 6, 'L') 

       || 

       Decode( 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 6, 'L'), ' ', '', 

                                                                  ' > ') 

       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 5, 'L') 

       || 

       Decode( 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 5, 'L'), ' ', '', 

                                                                  ' > ') 

       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 4, 'L') 

       || 

       Decode( 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 4, 'L'), ' ', '', 

                                                                  ' > ') 

       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 3, 'L') 

       || 

       Decode( 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 3, 'L'), ' ', '', 

                                                                  ' > ') 

       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 2, 'L') 

       || 

       Decode( 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 2, 'L'), ' ', '', 

                                                                  ' > ') 

       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 1, 'L') 

       || Decode(a.portal_label, ' ', '', 

                                 ' > ' 

                                 || a.portal_label)) navigation, 

       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,1,'L') LABEL1, 

       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,2,'L') LABEL2, 

       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,3,'L') LABEL3, 

       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,4,'L') LABEL4, 

       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,5,'L') LABEL5, 

       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,6,'L') LABEL6, 

       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,7,'L') LABEL7, 

       a.portal_uri_seg1                             menuname, 

       a.portal_uri_seg2                             component, 

       a.portal_uri_seg3                             market, 

       a.portal_urltext 

FROM   psprsmdefn a 

WHERE  a.portal_name = 'EMPLOYEE' 

       AND a.portal_reftype = 'C' 

       AND a.portal_prntobjname <> ' ' 

--AND A.PORTAL_URI_SEG2 = 'COMPONENT_NAME' 

ORDER  BY Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 1, 'S') 

          || 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 1, 'L'), 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 2, 'S') 

|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 2, 'L'), 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 3, 'S') 

|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 3, 'L'), 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 4, 'S') 

|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 4, 'L'), 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 5, 'S') 

|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 5, 'L'), 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 6, 'S') 

|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 6, 'L'), 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 7, 'S') 

|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 7, 'L')  

Finding navigation based on component name - I

1) Run the below SQL to get the content reference name for your component

SELECT PORTAL_NAME,

 PORTAL_OBJNAME AS CONTENT_REFERENCE,

 PORTAL_LABEL,

 PORTAL_URI_SEG1 AS MENU,

 PORTAL_URI_SEG2 AS COMPONENT,

 PORTAL_URI_SEG3 AS MARKET

FROM psprsmdefn

 WHERE PORTAL_NAME = 'EMPLOYEE'

   AND PORTAL_URI_SEG2 = :1;

-- Replace :1 with the component name you are looking for.

2) From the query above - copy the value in the CONTENT_REFERENCE field and replace the ":1" variable and you will have the path to your component.

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);

So, the 1st query is to get the content reference for a component name that you know and then using Jim's query to find the path!

==================

==================

==================

==================

==================

Another SQL:

SELECT DISTINCT
Page = PNL.PNLNAME, 
Component = P1.PORTAL_URI_SEG2, 
Record = FLD.RECNAME, 
Navigation = ISNULL(P6.PORTAL_LABEL, '') + ' > '   + ISNULL(P5.PORTAL_LABEL, '') 
+ ' > '   + ISNULL(P4.PORTAL_LABEL, '') + ' > '   + ISNULL(P3.PORTAL_LABEL, '') 
+ ' > ' + ISNULL(P2.PORTAL_LABEL, '')    + ' > ' + ISNULL(P1.PORTAL_LABEL, 'Nav not found') 
FROM PSPNLGROUP PNL LEFT JOIN PSPNLFIELD FLD ON PNL.PNLNAME = FLD.PNLNAME 
LEFT JOIN PSPRSMDEFN P1 ON PNL.PNLGRPNAME = P1.PORTAL_URI_SEG2 
LEFT JOIN PSPRSMDEFN P2 ON P1.PORTAL_PRNTOBJNAME = P2.PORTAL_OBJNAME AND (P1.PORTAL_NAME = P2.PORTAL_NAME OR P1.PORTAL_NAME IS NULL) 
LEFT JOIN PSPRSMDEFN P3 ON P2.PORTAL_PRNTOBJNAME = P3.PORTAL_OBJNAME AND (P2.PORTAL_NAME = P3.PORTAL_NAME OR P2.PORTAL_NAME IS NULL) 
LEFT JOIN PSPRSMDEFN P4 ON P3.PORTAL_PRNTOBJNAME = P4.PORTAL_OBJNAME AND (P3.PORTAL_NAME = P4.PORTAL_NAME OR P3.PORTAL_NAME IS NULL) 
LEFT JOIN PSPRSMDEFN P5 ON P4.PORTAL_PRNTOBJNAME = P5.PORTAL_OBJNAME AND (P4.PORTAL_NAME = P5.PORTAL_NAME OR P4.PORTAL_NAME IS NULL) 
LEFT JOIN PSPRSMDEFN P6 ON P5.PORTAL_PRNTOBJNAME = P6.PORTAL_OBJNAME AND (P5.PORTAL_NAME = P6.PORTAL_NAME OR P5.PORTAL_NAME IS NULL) 
WHERE 
--P1.PORTAL_URI_SEG2 = 'JOB_DATA'
FLD.RECNAME IN (


)

Content reference or folder not visible

If folder is not visible:

1. opened some other folder 

2. checked hide from navigation checkbox and saved it. 

3. Navigated back to the same folder. 

4. unchecked hide from navigation checkbox and saved it.

If content reference is not visible:

In case bouncing the webserver is not a feasible solution, below steps can be done.

-- Go to the Structure and Contents. PeopleTools > Portal > Structure and Contents.

-- Click on appropriate content which is not coming up.

-- Change its sequence number from zero to something else.

-- Save.

-- Go back to the same content again.

-- Change its sequence number back to zero.

-- Save.

-- Signout from the application.

-- Clear your browser's cache.

-- Signin again and check. 

Calling java from PeopleSoft

The java path would need to be added to your app server configuration (psappsrv.cfg), or you can copy the jar files into the <PSHOME>appserv/classes folder. Either way, make sure to bounce the app server.

The path variable in the appserver configuration can be found in the PSTOOLS section. That should be in the My Computer properties. Go to the Advanced tab, Environment variables. CLASSPATH should be in the system variables section.