Searching with USEEDIT

Because many of the record properties are hidden in the Bit Map field USEEDIT, it is hard to search for properties.  If you are on Oracle, here’s some SQL that might help…

This SQL lists all of the fields with their properties broken out of that field:

SELECT FIELDNAME,
       USEEDIT,
       Bitand(USEEDIT, 1)         KEY,
       Bitand(USEEDIT, 2)         DUP_KEY,
       Bitand(USEEDIT, 4)         SYS_MAINT,
       Bitand(USEEDIT, 8)         AUDIT_ADD,
       Bitand(USEEDIT, 16)        ALT_SEARCH_KEY,
       Bitand(USEEDIT, 32)        LIST_BOX_ITEM,
       Bitand(USEEDIT, 64)        ASCENDING_KEY,
       Bitand(USEEDIT, 128)       AUDIT_CHANGE,
       Bitand(USEEDIT, 256)       REQUIRED,
       Bitand(USEEDIT, 512)       XLAT,
       Bitand(USEEDIT, 1024)      AUDIT_DEL,
       Bitand(USEEDIT, 2048)      SEARCH_KEY,
       Bitand(USEEDIT, 4096)      EDIT_REASONABLE_DATE,
       Bitand(USEEDIT, 8192)      EDIT_YES_NO,
       Bitand(USEEDIT, 16384)     EDIT_PROMPT_TABLE,
       Bitand(USEEDIT, 32768)     AUTO_UPDATE,
       Bitand(USEEDIT, 65536)     BIT65536,
       Bitand(USEEDIT, 131072)    BIT131072,
       Bitand(USEEDIT, 262144)    FROM_SEARCH,
       Bitand(USEEDIT, 524288)    TO_SEARCH,
       Bitand(USEEDIT, 1048576)   EDIT_BINARY,
       Bitand(USEEDIT, 2097152)   DISABLE_ADV_SEARCH,
       Bitand(USEEDIT, 4194304)   REGULAR_FIELD,
       Bitand(USEEDIT, 8388608)   DEFAULT_SEARCH_FIELD,
       Bitand(USEEDIT, 16777216)  BIT16777216,
       Bitand(USEEDIT, 33554432)  SEARCH_EDIT_KEYS,
       Bitand(USEEDIT, 67108864)  BIT67108864,
       Bitand(USEEDIT, 134217728) BIT134217728,
       Bitand(USEEDIT, 268435456) BIT268435456
FROM   PSRECFIELDALL
WHERE  RECNAME = 'JOB'; 

(more…)

Share

Search for a Field with Translates

I was looking for an “Internal/External” field.  I wanted a field already built that had two translate values: “E” and “I”.  This SQL did the trick:

SELECT A.FIELDNAME,
A.XLATLONGNAME,
B.XLATLONGNAME,
A.XLATSHORTNAME,
B.XLATSHORTNAME
FROM   PSXLATITEM A,
PSXLATITEM B
WHERE  A.FIELDNAME = B.FIELDNAME
AND A.FIELDVALUE = 'E'
AND B.FIELDVALUE = 'I'
AND Upper(A.XLATLONGNAME) LIKE 'EXT%'
AND NOT EXISTS (SELECT 'X'
FROM   PSXLATITEM C
WHERE  A.FIELDNAME = C.FIELDNAME
AND C.FIELDVALUE NOT IN ( 'E', 'I' )) 

I found the INTERNAL_EXTERNAL field, which works just perfect for me.

Share

Anyone need remote help?

Does anyone know of any good contracts that would support mostly remote work?

I started this blog years ago with the idea that it would be a good place to search for work should I ever need it. I think the time has come to just ask to see if projects are available through this avenue. If you know of anything, I would love to speak with you. Would you mind commenting below or sending me a “Linked In” message?

Share
Posted in Uncategorized. No Comments »

Annual Report from WordPress

Recently, I received my annual report from WordPress/Jetpack.  I have always enjoyed these reports, so I thought I would make it public.

For the complete report, view it here: PSST0101 – 2014 Year in Blogging

(more…)

Share
Posted in Blog. No Comments »

New PeopleCode Dump Method

One of my old tricks was to create a PeopleCode dump from the system.  Then I could use a text editor tool such as gVim or Ultraedit to search through the code to find examples or certain uses of definitions.

Basically, a PeopleCode dump is easily created by searching for (Edit > Find In) a semicolon.  Because every statement must have a semicolon, it matches every statement of code in the system.  On the Find In dialog, checking the Save to File option will write out each line to a text file.

The problem is that Application Designer seems to load every line into memory.  If you don’t have a client machine with a ton of memory, you end up looking like this:

App Designer Out of Memory Error

 

Instead, I found this new tool called Decode PeopleCode.  You can download the latest zipped version of the program from here.

Once extracted, I had to configured the URL property in the DecodePC.properties file.  I set it to something like this:

url=jdbc:oracle:thin:@<server name>:1521/<db name>

I also had a problem with the JDBC driver for some reason.  It didn’t find the odbc5.jar file that was delivered.  I already had a copy of the odbc6.jar, so I just adjusted the classpath to include that.  If you run into the same problem, adjust the DecodePCODE.sh or DecodePCODE.bat file to contain the full path to the jar file.

Also, adjust the “outdir” property in the DecodePC.properties file to point to the directory where you want the output dumped.  Note that it will create a bunch of folders and files in that directory, so you may want to point it to an empty directory.

Then, I ran the program with this command to capture everything:

sh DecodePCODE.sh since 1901/01/01

Now, that creates each PeopleCode program in its own file.  The problem is that I want it all in a single file that I can run regular expressions against.  I could use grep, but I am used to a single file.  I cobbled this script together to build that single file:

#!/bin/bash


processFile() {
   outdirlen=$((${#OUTDIR} + 1))
   filename=$1
   lastUpdateFile=$( echo "$filename" | sed 's/\.pcode$/.last_update/' )
   peoplecodePath=${filename:$outdirlen}
   peoplecodePath=$( echo "$peoplecodePath" | sed 's/\//: /' | sed 's/\.pcode$//' | sed 's/\//./g')
   echo -e "\e[0K\r Writing: $peoplecodePath"
   echo "[$peoplecodePath]" >> "$OUTFILE"
   echo '/*   Details:' >> "$OUTFILE"
   echo '   Last Update User: ' $(head -n 1 "$lastUpdateFile") >> "$OUTFILE"
   echo '   Last Update Date/Time: ' $(tail -n 1 "$lastUpdateFile") >> "$OUTFILE"
   echo '*/' >> "$OUTFILE"
   cat "$filename" >> "$OUTFILE"
}


#  ------------------------------
#    Main
#  ------------------------------

OUTDIR=$( grep "outdir=" DecodePC.properties | sed 's/^outdir=//' | sed 's/\r//')
DUMPDATE=$(stat -c %y $(ls -rt | tail -n 1) | cut -d ' ' -f1 )
DATABASE=$( grep "^url=" DecodePC.properties | sed 's/^url=.*\///' | sed 's/\r//')
OUTFILE="$OUTDIR/all.pcode"
echo "outdir -- $OUTDIR  dump date -- $DUMPDATE  database -- $DATABASE"

echo "PeopleCode Dump $DATABASE   $DUMPDATE" > "$OUTFILE"
find "$OUTDIR" -type f -name \*.pcode | while read file; do processFile "$file"; done

That seemed to get the trick done for me nicely.

This program has more features than just this. You should explore it. It can check the code into a sourcecode repository to track changes. Kudos to whoever wrote it!

Resources

 

Share
Posted in PeopleCode. No Comments »

Jethro List: Data Mover Woes

Ok.  Maybe this is a rant, but Data Mover can sometimes get under my skin!  Some things you just expect to work, and when they don’t, it is very frustrating.

Here’s my problem.  I created this very, very simple data import:


set log C:\temp\aa_setup_import_FADEV.log;
set input C:\temp\aa_setup_export_AATST.dat;

replace_data RQ_GRP_SHR_SET;
delete PS_RQ_GRP_TBL where RQRMNT_USEAGE = 'ADV';
import RQ_GRP_TBL where RQRMNT_USEAGE = 'ADV';

It gave me this error:

Importing RQ_GRP_TBL
Error: Syntax error in where clause for RQ_GRP_TBL 

Then, when I changed it to use parameters, it suddenly works:


set log C:\temp\aa_setup_import_FADEV.log;
set input C:\temp\aa_setup_export_AATST.dat;

replace_data RQ_GRP_SHR_SET;
delete PS_RQ_GRP_TBL where RQRMNT_USEAGE = 'ADV';
import RQ_GRP_TBL where RQRMNT_USEAGE = :1;CHAR,ADV;

In my opinion, there is no call for that error.  I thought both syntaxes were supposed to work the same.  Am I missing something?  Or, is this a bug in data mover?

 

Next, I ran into a worse problem.  I couldn’t make my subquery work on the import, so I had to move it to the export script instead.  I tried this:


set log C:\temp\aa_setup_export_AATST.log;
set output C:\temp\aa_setup_export_AATST.dat;

export RQ_GRP_SHR_SET;
export RQ_GRP_TBL where RQRMNT_USEAGE = :1;CHAR,ADV;
export RQ_GRP_DETL_TBL where
EXISTS (SELECT 'X' FROM PS_RQ_GRP_TBL A
 WHERE A.RQRMNT_GROUP = PS_RQ_GRP_DETL_TBL.RQRMNT_GROUP
 AND A.RQRMNT_USEAGE = :1);CHAR,ADV;

Every time that I tried it, it would crash Data Mover.  Data Mover closed with no error message, warning, or anything.  When I changed it to this, it worked fine:


set log C:\temp\aa_setup_export_AATST.log;
set output C:\temp\aa_setup_export_AATST.dat;

export RQ_GRP_SHR_SET;
export RQ_GRP_TBL where RQRMNT_USEAGE = :1;CHAR,ADV;
export RQ_GRP_DETL_TBL where
EXISTS (SELECT 'X' FROM PS_RQ_GRP_TBL A
 WHERE A.RQRMNT_GROUP = PS_RQ_GRP_DETL_TBL.RQRMNT_GROUP
 AND A.RQRMNT_USEAGE = 'ADV');

Again, why?  I don’t care what your argument is, it is a bug when a program closes with no message.  If I did something wrong, tell me.  Don’t just close.

Resources

Oracle Forums: DATAMOVER: Error: Syntax error in where clause for PSOPRDEFN

Share

PeopleSoft HCM PUM Image with PeopleTools 8.54

I just noticed that the HCM PUM image is now available on PeopleSoft support.  You can download it for the HCM updates, but you can also take a peak at what PeopleTools 8.54 looks like.  If you have an Oracle Support account, here’s the link:   PeopleSoft Update Image HCM 9.2.008.

PUM HCM Image

Just for reference, the image is a total of 11 files and 34.7G.

PUM HCM Image Files List

Resources

Share

PeopleTools 8.54.01 Exalogic OVM Template

I ran across this announcement on Oracle’s blogs about the new PeopleTools 8.54 OVM Template.  The problem is that I don’t have Oracle VM and don’t really want to delve into messing with it at the moment.  Virtualbox works better for my playing around.  It turns out that it wasn’t too hard to get this running in Virtualbox.

So, here are my notes…

(more…)

Share

PeopleTools 8.54 Released

I see that PeopleTools 8.54 is released according to the PeopleSoft Technology Blog.  I’m eager to get digging into the new features, but I’m afraid it may be a while before I get to play with it.

Currently, it looks like you won’t be able to play with it unless you can go through a full install.  Upgrades are not supported until after a patch or two is released:

Today, PeopleTools 8.54 is Generally Available for new installations.  Customers that want to upgrade to 8.54 from earlier releases will be able to upgrade in the near future when the 02 patch is available.

Of course, the easiest way to play with it would be to download it pre-installed from Oracle.  Here’s the way their blog says it:

Many of our customers have shown interest in Fluid and have asked us the best way to get productive quickly.  Our answer is to use the working examples they will find in the upcoming PSFT 9.2 application images.

The current images are PeopleTools 8.53.  I looked at the release dates from last year, and it looks like images came around the August time frame.  So, my unofficial guess is that we will get a fully installed version of PeopleTools 8.54 to play with sometime in August.  In the meantime, I may have to live vicariously through other’s experiences like the PeopleSoft Tipster.

Resources

Share

Jethro List: Ctrl + A

This post  is part of my Jethro List.  You can read more about the list on the Jethro List page.

Can I make another suggestion?  Some of the text boxes in App Designer don’t work like regular Windows controls.  Namely, they don’t support things like Control-A to select the entire text in the box.  This is slightly frustrating and also makes App Designer feel antiquated.

One of those places is the File Layout Field Properties dialog.  If you go to the “Field Name” text box, you can press Ctrl+A to select the entire field name easier.  That makes it easy to either copy the name or type a new name in it’s place.

File Layout Field Name

 

If you go to the Field Description box in the lower right hand corner, you will find that Ctrl+A doesn’t work.  You have to either use the mouse or hit Home, Shift + Ctrl + End.

File Layout Field Description

 

On a rabbit trail, the file layout UI could use some attention.  A feature that I would like for Flat Files is the ability to sort by the Start Position.  If you come behind someone who wasn’t as organized and didn’t put the fields in order, it can be annoying.  Also, file layouts seem to have issues with caching.  I have had many a time that I changed a file layout and saved only to reopen it and find my changes missing.

The Application Engine UI has a similar problem.  The descriptions for the sections, steps, and actions all don’t allow the use of Ctrl + A.

App Engine description fields

In conclusion, I think it would be helpful if Oracle could update the App Designer UI to match the standard features that we have come to expect in Windows applications.  I can live with it this way and can get my job done, but it sure would be nice to have a nicer feel to development.

 

Share