Lepa's blog

PeopleSoft Validate Vendor ID

The select statement below is preformed by AP Voucher Build (AP_VCHRBLD) Application Engine to validate a Vendor ID:

INSERT INTO %TABLE(VCHR_HDR_QV_ER) (PROCESS_INSTANCE
, BUSINESS_UNIT
, VOUCHER_ID
, MESSAGE_SET_NBR
, MESSAGE_NBR
, FIELDNAME
, MESSAGE_PARM1
, MESSAGE_PARM2
, MESSAGE_PARM3)
SELECT %Bind(PROCESS_INSTANCE)
, HDR.BUSINESS_UNIT
, HDR.VOUCHER_ID
, 7220
, 2
, 'VENDOR_ID'
, HDR.VENDOR_SETID
, HDR.VENDOR_ID
, ' '
FROM %TABLE(VCHR_HDR_TMP) HDR
WHERE HDR.PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)
AND VENDOR_ID NOT IN (
SELECT VENDOR_ID

Application Engine ReUse Statement in PeopleSoft

The ReUse in Application Engine was brought to my attention by a great blog that I follow regularly.  The original post can be found at PeopleSoftTipster

The ReUse statement setting on an Application Engine is a great way to increase performance. It’s useful when you have a SQL object that is called multiple times, for instance when you have a DoSelect that runs a SQL Step repeatedly, substituting different Bind variables in each time.

Hide Fields on PeopleSoft Page for Some Users

Sometimes you would like to hide fields from some users and not others. The only way that I know of is by creating a new role and assigns it to users who should not be seeing the fields.

This is how to go about doing that:

Date Validation Using SQR

Here is some SQR to help you validate a date format. You can modify the code to fit your own needs and date format you desire.

!-------------------------------------!
! Procedure: Date-Validation !
! Descr: Validate $entered_date. !
!-------------------------------------!
Begin-Procedure Date-Validation
#debug show 'Entering Date-Validation'
Let $Date_Validation = 'N'
!Show '$entered_date: ' $entered_date
Let #1st_Slash_position = instr($entered_date, '/', 1)
!Show '#1st_Slash_position: ' #1st_Slash_position

Open a File for Reading Using SQR

Here is some SQR code that will open a file for reading:

Begin-Procedure Open-File
#debug show 'Entering Open-File'
!file name and where it is located.
Let $new_file = $PATHNAME || 'your_file_name.txt'
Show '$new_file = ' $new_file
!check if the filel exists before trying to open it
Let #estatus = exists($new_file)
If #estatus = 0
Show 'Log File ' $new_file ' exists. Will be opened for reading.'
!opening file for reading
Open $new_file As 1 For-Reading Record=3000
Status=#FileStat
!couldn't open file if #FileStat is not zero
If #FileStat != 0
Move 'N' To $file_open

Validate Character Limits Using Visual Basic

This is a VB excel macro code to prevent users from entering data that exceeds the field limit.

For example, if a cell needs to be only 5 characters long and user entered 7 characters, an error message will popup notifying the user and highlighting the cell containing the 7 characters.

ActiveSheet.Range("A" + posrow).Activate
If Len(ActiveCell.Value) > 5 Then
   Application.ScreenUpdating = True
   ActiveSheet.Range("A" + posrow).Activate
   MsgBox "This Cell Can Not Exceed 5 Characters.", 0, "Data Error."
End If

Force Value to Upper Case Visual Basic Excel Macro

The below Visual Basic code is used in an excel macro. It reads the values on Row "R", trim spaces, checks if the length of the value is equal to zero (nothing in that cell). If so the it will output the letter "N" otherwise it will output an upper case value entered by the user (Format(UCase((ActiveCell.Value))).

The "!@" is the VB way in saying you want to output one character. So, if you have a cell that has 8 character you would code "!@@@@@@@@"

ActiveSheet.Range("R" + posrow).Activate
ActiveCell.Value = Left(Trim(ActiveCell.Value), 1)

Check Disk Space or Capacity in Unix

df (abbreviated from disk free) is a standard Unix computer program used to display the amount of available disk space for filesystems on which the invoking user has appropriate read access, df is usually implemented by reading the mtab file or using statfs.

using it is very simple, command:

df /<your_directory>

%EffDtCheck PeopleCode Built-in Function

Effective dated quires are used often in PeopleSoft and a great build-in function that will save you time is the %EffDtCheck. Lets take a closer look!

Syntax

%EffDtCheck(recordname [correlation_id1], correlation_id2, as_of_date)

The %EffDtCheck construct expands into an effective date subquery suitable for a Where clause.

IsAlpha and IsAlphaNumeric Built-in Functions

IsAlpha

Use the IsAlpha function to determine if
String contains only textual characters. It excludes all punctuation,
numerics, spaces and control codes.

The function returns a
boolean value: true if the string contains only alphabetic and textual
characters, false if it contains any umbers, punctuation or spaces.