Read the Translate Table from SQR

Suggested this SQC when met one colleague trying to write one procedure to read from the XLAT table. Will be a handy tip.

The above requirement is as simple as including an SQC (readxlat.sqc) and passing the parameters to it to get the value back. Lets code smart.

Include the SQC

Pass the Parameters

Get your need fulfilled.

Other two procedures which can be handy at times below.

PeopleSoft 9.2 InfoGraphic

Found this interesting creation from Oracle and thought of sharing it.

Source Link from Oracle

Example For Strings Table in SQR

An example for a standard, online configurable SQR program using Strings Table is BEN007.sqr

It is always recommended to follow this method to provide the client a better way to manage the object property via online.

The strings table can be found at this path: Menu > PeopleTools > Utilities > Administration > Strings Table

Search with the program ID, for BEN007 it is the same name.

Reuse SQR XML Tag creation process

I have wrote a post on Payroll Customization Check/Advice stub Modification, few weeks back. There was some interesting learnings that came in the way.

First thing is the XML creation tag, which can be extended to a new SQR with XML Tag creation requirement. This will make the code standard and flexible enough to extend for any data requirement that might come into picture in future.

It is always recommended to code in a way which makes the application configurable and easily extendable. And this one piece will make the life easy for the developer.

The below code part is from the PAY003.sqr file. If you notice to the end of it, there are a lot of procedure calling statements like do Process-SSP-XML-Tag-Beg, do  Process-SSP-XML-Tag-Content, do Process-SSP-XML-Tag-End tags. These are the heros in creating the XML's for the report to the end.

You can leverage these elements for adding new sections to the report upon the requirement that might come up later in the stages.

PeopleSoft Search Framework

Thought of sharing.

Using %ExecuteEdits in SQL

A handy tip.

Insert Example:
INSERT INTO PS_MY_ERROR_TABLE SELECT %Bind(PROCESS_INSTANCE) ,'Staging Table' ,'MY_STAGING_TABLE' , A.EMPLID,'Execute Edits Failed' FROM MY_STAGING_TABLE A WHERE %ExecuteEdits(%EDIT_REQUIRED + %EDIT_PROMPTTABLE + %EDIT_TRANSLATETABLE + %Edit_YesNo, MY_STAGING_TABLE)

Update Example:
UPDATE MY_STAGING_TABLE A2 SET A2.ERROR_FLAG = 'Y' WHERE %ExecuteEdits(%EDIT_REQUIRED + %EDIT_PROMPTTABLE + %EDIT_TRANSLATETABLE + %Edit_YesNo, MY_STAGING_TABLE)

Payroll Customization Check/Advice stub Modification - Objects Involved

An interesting customization. The requirement was to modify the look and feel of the delivered Customization Check and Advice stub.

The objects involved in this:

• PAY003 – Print Pay Checks: SQR Program
• DDP003 - Create PDF Paychecks: SQR Program
• PYCHKUSA - Print US Checks: PSJob
• PAY003
• PY_SSP_BUILD (AE)
• PY_SSP_PRINT (AE)

• PYDDAUSA - Print US Direct Deposit Advice: PSJob
• DDP003
• PY_SSP_BUILD (AE)
• PY_SSP_PRINT (AE)

The above objects are for two purposes. The SQR Programs creates PDF version of the report along with the XML which is used by the PSJobs to print and display the online versions of the check/advice.

The Report Definitions used by the PSJobs can be found at: Main Menu > Product Related > Payroll for North America > Payroll Processing Controls > Paycheck Options Table

Those are the 4 RTF templates need to be modified for completing the above customization.

So total we have to modify 7 objects. You might be thinking that I got a mistake in the calculation and it should be 6.

Actually 7th one is the pysspxml.sqc program which is used by the above two SQR programs, which plays an integral part in creating the XML tags for the PSJobs.

If there need to be a data change in the RTF templates, you need to touch the XML creation code. And that is far more interesting to work with.

Dynamic SQL Creation - A Shortcut

It is often seen in Payroll projects to create a run control page with company, business unit, department combination and the reports need to be run with any of the above combinations.

The way I have seen a lot of people doing is to create a dynamic SQL based on the parameter check. 

Which you can easily do with the DECODE function. 

The screenshot from a SQR program given below. You can use the same in SQL.

Will be a handy tip for beginners.

Tip of the day: %TruncateTable

One of my colleague was asking the other day why we have to use %TruncateTable instead of DELETE. The below pb line has the answer.

• On all databases, the use of %TruncateTable causes an implicit commit.
• The rows deleted by this command, and any other pending database updates, are all committed.
• Execution is faster than either of the SQL statements.

To postpone the commit until subsequent database updates have been successfully completed, use the SQL statement DELETE FROM table_name or the statement IMPORT REPLACE WITH NULL instead of %TruncateTable(table_name).