Exporting or Saving App Engine as Text

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

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

/* 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

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)

Work in progress...

NVAL NVL Null no value in Oracle SQL

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

Oh Crystal, when will you be decommissioned?

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

1. 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?).
   
   
2. Which user to use for the connection prior to migrating out of DEV and eventually into PROD?
   
   
3. Subreport links.
   
   If found I had to use the following ("sameish" for run control id):
   
   
   

Printing SQR A4 instead of Letter

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

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

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

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

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 to do something it was specifically designed to not do. : )

1. Add your subquery
2. Allow the default Effdt Logic to be added by PeopleSoft/Query
3. Add a new expression "max(effdt)" and add as a selected field.
   Note: do not add the alias, just effdt.
4. Correct the Criteria.

Some subqueries don't use effdt logic, the important step is to not add the alias in the expression, until you've finished all the criteria.