Multiple Reports in SQR at ease

You can print multiple reports with ease using the simple structure below. 

DECLARE-REPORT can declare n number of reports (max 99) and use it for the respective printing. There is catch at this point. What if the reports are dynamic and you cannot forcast the number of reports that gets generated.

Then the below code can be handy.

You have the while loop and NEW-REPORT part inside it. Make the file name dynamic based on the variables in your code. And that's it. 

No headaches of declaring multiple reports in the begin-setup and you can get n number of reports (max infinity), without the limitation of 99.

Hope, this will be a handy code for you.

XML Publisher - Clean Up

Cleaning Up BI Publisher Metadata

Some people are very specific about the way the data is managed in the system. I have seen several Admins who is very serious about the accurate data in the system, they will always clean up the data in the system, like the perfect housewives maintains a good home. 

In BI Publisher we have a delivered way to do this act. The Application Engine program name is PSXPCLEAN. It tracks and manages the orphaned objects in the system and provides you a report too.

What the different sections does in the AE. It find outs the orphaned definitions to the state record and deletes it in a sequence.

Various sections performs this duty.

This application engine program is delivered in Report and Delete mode. To run the program in Report Only mode, open the application engine program PSXPCLEAN in Application Designer and remove the comment in the following statement in PSXPCLEAN:Main:Start PeopleCode action:

rem PSXPFILECLN_AET.REPORT_ONLY_FLAG = "Y" (which is marked in Yellow below)

From here, the AE starts collecting the info about the missing definitions.

From this point the clean up starts.

A handy tip to know about the AE and the main tables in XMP Publisher for quick reference.

My Reports with No Reports?

You might have came this scenario, if you have worked on the My Reports Pagelet. When you use it for the first time, the reports might now appear here.

When you click on the Report Manager link beneath, it takes you to the Explorer tab, where again you will get the same message.

But the Administration tab might be having the reports but not in the Explorer tab.

To rectify this and to start posting the reports to teh Explorer tab, which will ultimately display the data in the My Reports pagelet, you have to check and activate the following Routing - Service Operations at the Default Local Node.

• PSRF_FOLDER_CREATE
• PSRF_REPORT_CREATE
• PSRF_REPORT_DATE_CHANGE
• PSRF_REPORT_DELETE

Once you set this up and bounced the server (if required), the My Reports Pagelet will start populating the reports.

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

PeopleTools with Applications Matrix

Click this link for the download (You need to have Oracle Support Account)

Handy information for sure.