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: here is a nicer code snippet
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(X.FIELD, VAL_IF_NULL)
FROM Table X

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