Lepa's blog

How to Read a File Using SQR?

Here is some SQR code that will enable you to read a file. The code will also track # of records being read and will print out how many records have been rejected as well as total records read.

!------------------------------------!
! Procedure: Read-Input-File !
! Descr:     Read file               !
!-----------------------------------!
Begin-Procedure Read-Input-File
#debug show 'Entering Read-Input-File'

Read 1 into $Record:3000  !skip the headings

Move 0 to #Tot_Recs
Move 0 to #Recs_Rej

While Not #end-file
        Read 1 into $Input:3000 Status=#Read_stat

Oracle CASE Expressions

I have used Oracle CASE in one of my previous articles and this is an attempt to explain how it works.

CASE expressions let you use IF ... THEN ... ELSE logic in SQL statements without having to invoke procedures.

Here is a simple CASE example from Oracle documentation:

Extract Project Objects Using an SQL

There have been times where I needed to document objects included in an Application Designer. The process could get very time consuming if you have a project with hundreds of PeopleSoft objects in it. PeopleSoft doesn't provide a "copy paste" type of approach to ease things, so I came up with my own!

The query below will return all the objects inside of an Application Designer project. You can then copy the rows and paste them where ever you'd like.

SELECT (CASE OBJECTTYPE WHEN 0 THEN 'Record'
WHEN 1 THEN 'Index'
WHEN 2 THEN 'Field'
WHEN 3 THEN 'Field Format'

Close an Open File Using SQR

Here is an SQR code to close a file that has been opened for reading. Check my other article if you would like to see how to open a file for reading using SQR.

!---------------------------------!
! Procedure: Close-File       !
! Descr:     Close a file that !
! has been opened for reading.!
!--------------------------------------!
Begin-Procedure Close-File
#debug show 'Entering Close-File'

If $file_open = {TRUE}
    close 1
    Move {FALSE} To $file_open
    Show 'File ' $your_file_name ' Closed successfully.'

Query for Component Path within PeopleSoft Portal

While back, I've created an SQL to find the exact same thing but with some level limitations. I have came across another query written by Jim's PeopleSopft Journal blog that works a little bit better and thought to share it with my visitors.

Jim's query however requires the developer to know the content reference of a component before the query could be used. What I really wanted is the ability to find the path by just knowing the component and not the content reference. Chances are, if you know the component reference you know the path to that component as well. So, to find the component path without knowing the content reference, follow the steps below:

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.

Find Component Path Within PeopleSoft Portal

Update: You might want to check my new post to find the path with no level limitations.

PeopleSoft Portal Link as an External URL

I had fun trying to figure this one out at work today. I wanted to
associate a link in PeopleSoft Portal to open an external URL (website)
in a new window and without the portal frame (template) around it. To
do so, follow the steps below:

Navigate to PeopleTools >
Portal > Structure and Content. Chose the folder you would like your
link to be in or you can add a new folder just for this new link if
you'd like.

Now scroll all the way to the bottom and click "Add Content Reference".

Run an SQR From a PeopleSoft Application Engine

Here is a good sample of how to launch an SQR from within PeopleCode. You can use the peoplecode function called “ScheduleProcess”. Here is the example:

&PROCESS_NAME = "SQRNAME";
&PROCESS_TYPE = "SQR Report"; (or "SQR Process")
&RUN_CNTL_ID = "YOUR_RUN_ID";
&RUN_CONTROL = ScheduleProcess(&PROCESS_TYPE, &PROCESS_NAME, "2", &RUN_CNTL_ID, &PRCS_INST);

If &RC != 0 /*"1" = client "2" = server*/
Winmessage("Error: Error Scheduling SQR Process, RC = " | &RUN_CONTROL);
End-If;

Calling an Application Engine from PeopleCode

The PeopleCode below will show you how to call an Application Engine from PeopleCode. Some refer to this as “Real-Time Application Engine Processing.” For example, when a user save’s a page, you may want a specific App engine program to run. You can also execute this code in a field change event.

The following code calls the Application Engine program named YouAppEngProg, and passes it the necessary initialization values.

&MYRECORD = CreateRecord(RECORD.MY_INIT_VALUES);

PeopleSoft Validate AP Business Unit

The select statement below is preformed by AP Voucher Build (AP_VCHRBLD) Application Engine to validate an AP Business Unit:

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
, 1
, 'BUSINESS_UNIT'
, HDR.BUSINESS_UNIT
, ' '
, ' '
FROM %TABLE(VCHR_HDR_TMP) HDR
WHERE PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)
AND BUSINESS_UNIT NOT IN (
SELECT BUSINESS_UNIT