Exposing PeopleSoft Component using iFrame Tag

Iframe Tag is one of the powerful html tag which helps you to expose a PeopleoSoft component from one instance to another. Eg: if you want to show employee info in EPM, but the data has to be retrieved from HCM database. Assuming a component has already exist in HCM, we can use iframe tag to show the HCM component within EPM window.

Steps:
1. Create a page in EPM.
2. Place a HTML Area Control.
3. Refer the HCM component url using iframe tag shown below:

Note: SSO has to be enabled to achieve this.

SQL Query to fetch in csv format

Query to fetch in csv format

DECLARE @theList varchar(100)
SELECT
@theList = COALESCE(@theList + ', ', '') + RTRIM(ACCOMPLISHMENT)
FROM PS_ACCOMPLISHMENTS
WHERE EMPLID = '3820001'
SELECT @theList

Removing new line character from a string in SQR

Removing new line character in a string

let $enter = chr(13) chr(10)
let $newComment = replace($Comment, $enter, ' ')

Lpad in SQL Server

By converting to a string and padding with the string..

Eg:

SELECT RIGHT(REPLICATE('0',6)+ CONVERT(VARCHAR(6),'7'),6)

SELECT RIGHT(REPLICATE('0',6)+ CONVERT(VARCHAR(6),SUBSTRING(RTRIM('T000006'),2,LEN(MAX('T000006'))-1)+1),6) FROM PS_PERSON

Recursion in SQR

Sometimes, especially if the data is organized in a hierarchical or tree structure, it makes sense to have a procedure call itself. This is known as a recursive call. SQR, unlike most languages, does not have a call stack. So, in order for the recursion to work as expected we have to emulate one in the SQR code itself. It gets even a bit more complicated if we need "begin-select" sections in the recursive procedure. If SQR encounters the "begin-select" line again before the previous cursor is closed it will just fetch the next row. Below are two examples, each with a correct program and one that you might think would work at first glance.

Fibonacci Sequence: Each number is the sum of the two previous numbers. So, the sequence starts: 1,1,2,3,5,8,13,21,34 . . . fib_right.sqr fib_wrong.sqr

Family Tree: This example descends a family tree starting with an ancestor. recurse_right.sqr recurse_wrong.sqr

Source: http://www.ontko.com/sqr/

Justifying text in SQR Reports

Blocktxt.sqr is a program that illustrates the use of a technique of printing a text string so that it wraps, with justified text. In other words, it will be flush left AND flush right, with spaces placed through the text. This style of print is often used in newspaper columns.

!
! This program shows how to block a paragraph (also known as flush right).
! It is not intended to handle all situations and is not supported by MITI.
!
begin-report
do main
end-report

begin-setup
#define paragraph_width 60 ! define the width of the paragraph
end-setup

begin-procedure main

let $str = 'This is a paragraph of text that will be printed as a block. In other words, it will be printed in newspaper terminology as "flush right". The basic technique is divide the white space at the end of each portion into the gaps.'
let $str = $str $sta
let $str = $str $sta
let $str = $str $sta
let $str = $str $sta
let $str = $str $sta
let $str = $str $sta

while 1
!
! loop and process portions of the string
!
do get_next_str($str,$substr)
if $str = ''
!
! null string indicates that the last portion was processed
! only print this part, don't add blanks to it
!
print $substr (+1,10)
break
else
!
! add blanks to the current sub-string
!
do add_blanks_to_gaps($substr)
print $substr (+1,10)
end-if
end-while
end-procedure

begin-procedure get_next_str(:$str,:$substr)
!
! this procedure returns a portion of a string
! and sets the original string to the remainder
!
let #l = length($str)
if #l <= {paragraph_width}
!
! last portion of the string, so return that and set original string to null
!
move $str to $substr
move '' to $str
else
!
! determine how much of the string to return as the substring
!
let #i = {paragraph_width} + 1
let $substr = substr($str,1,#i)
while #i > 0
let #j = instr($substr,' ',#i)
subtract 1 from #i
if #j > 0
break
end-if
end-while
!
! form the $substr and return the remainder of the string in $str
!
let $substr = substr($str,1,#i)
let $str = substr($str,#i+2,#l-#i-1)
end-if
end-procedure

begin-procedure add_blanks_to_gaps(:$substr)
!
! this procedure adds spaces to the "gap" in the substring
! so that the length of the substring is equal to the paragraph width
!
! first determine how long the substring is (#l)
! and how many spaces to add to the substring
!
move ' ' to $gap
let #l = length($substr)
let #s = {paragraph_width} - #l
move 1 to #i
while #s > 0
!
! loop and look for the next "gap"
!
let #j = instr($substr,$gap,#i)
if #j = 0
!
! no more "gaps" in the string, increase $gap and go back to the start
!
let $gap = $gap ' '
move 1 to #i
break
else
!
! found a gap, form a string that will have an extra space at the "gap"
!
let #l = length($substr)
let $substr = substr($substr,1,#j)
substr($substr,#j,length($substr)-#j+1)
let #i = #j + 2
subtract 1 from #s
end-if
end-while
end-procedure

! Here is the sample output

! This is a paragraph of text that will be printed as a block.
! In other words, it will be printed in newspaper terminology
! as "flush right". The basic technique is divide the white
! space at the end of each portion into the gaps.

source: http://www.sqrug.org/ftp/blocktxt/

SQLExec

It helps you to execute SQL command from within Peoplecode programs.
It bypasses the component processor and interacts directly with the database.

Syntax
SQLExec({sqlcmd SQL.sqlname}, bindexprs, outputvars)

Limitations:

SQLExec can only select single row of data. If the SELECT statement retrieves more than one row of data, Output variables will hold only the first row of data and the remaining rows will get discarded.

SQLExec statements that result in a database update (specifically, UPDATE, INSERT, and DELETE) can only be issued in the following events: (SavePreChange,WorkFlow,SavePostChange,FieldChange)

The number of output variables cannot exceed 64.

Examples:

Using Bind Variables in SQLExec
SQLExec(“Select first_name from ps_names where emplid = :1 and name_type = :2”,&emplid, &name_type,&out_first_name)

Using Inline Bind Variables
Inline bind variables are included directly in the SQL string in the form:
:recordname.fieldname
SQLExec("Select sum(posted_total_amt)
from PS_LEDGER
where deptid between :LEDGER.DEPTID_FROM
and :LEDGER.DEPTID_TO", &sum);

Note: You have to change the Inline Bind Variables inside quotes if you are changing the record field name.

Output Variables in SQLExec
SQLExec(“Select first_name from ps_names where emplid = :1 and name_type = :2”,&emplid, &name_type,&out_first_name)

Using Arrays for Bind Variables
By using parameter of type "Array of Any" in place of a list of bind values or in place of a list of fetch result variables, helps you to simplify the statement. This is generally used when you don't know how many values are needed until the code runs.
&Stmt = "INSERT INTO PS_TESTREC (TESTF1, TESTF2, TESTF3, TESTF4, . . . N) VALUES (:1, :2, %DateTimeIn(:3), %TextIn(:4), . . .N)";

&AAny = CreateArrayAny("a", 1, %DateTime, "abcdefg", . . .N);

SQLExec(&Stmt, &AAny);