Few things every PeopleSoft Consultant should know

Excel2CI:-

Four step process:-

1. Connection
2. Template
3. Data Input
4. Staging and Submission

Grant access to the WEBLIB_SOAPTOCI iScript.

Chunking Factor - Number of rows of data to be transmitted to DB at one time , Default one is 40.

Excel allows a maximum 252 columns and 65,000 rows.

The numbers for each scroll level are incremented based on the number of records that exist at that level. For example:

Level 0 would be 000, 

Level 1 would be 100 and level 2 would be 110, and so on.

SQR:-

Maximum file can be open in a SQR program is - 12

In Begin-Select paragraph we can control mannualy the no of rows retrive from select statement

Example Begin-Select Loops=n

Unstring is use as simplar to PeopleCode Spit when reading a line from file and storing into variable.   

Example: Unstring <variable name>  by <separator - pipe/comma/Tab etc>  into <var-1> <var-2> etc

Load-Lookup example:-

!load lookup for department

let $where = 'dpt.effdt = (select max(effdt) from ps_dept_tbl'

|| ' where setid = dpt.setid '

|| ' and deptid = dpt.deptid and effdt <= '

|| '''' || $asofdate || ''')'

|| ' and dpt.eff_status = ''A'''

Load-Lookup

name = deptid

rows = 20000

table = 'ps_dept_tbl dpt'

key = 'setid || '','' || deptid'

return_value = 'company || '';'' || gl_expense || '';'' || manager_id || '';'' || paygroup || '';'' || location'

where = $where

let $search = {SETID} || ',' || &jb1.deptid

lookup deptid $search $ret_val

unstring $ret_val by ';' into $dept_tbl_company $dept_tbl_gl_expense $dept_tbl_manager_id $dept_tbl_paygroup $dept_tbl_location  

Initial Capital later Logic using SQR:-

! Procedure: PROPER-FORMAT                                                                  !

!            This procedure accepts a string and return with proper format as well as it

!            remove the special character from the input string. 

BEGIN-PROCEDURE PROPER-FORMAT($City,:$outputstring)

#ifdef debugx

  display 'Entering INITCAP.SQC: PROPER-FORMAT'

#end-if

!Trim each field for blanks

let $String1=ltrim(rtrim($City,' '),' ')

!Check for special Character in the string

let $valid_chars ='ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789 ' 

let $invalid_chars = translate($String1,$valid_chars,'') 

let #invalid = length($invalid_chars) 

!Remove special Character in the string 

while #invalid > 0

let $String1 = translate($String1,$invalid_chars,' ') 

let $invalid_chars = translate($String1,$valid_chars,'') 

let #invalid = length($invalid_chars) 

        end-while

DO INITCAP-FORMAT($String1,$outputstring)

END-PROCEDURE PROPER-FORMAT

!--------------------------------------------------------------------------------------------!

!Procedure : INITCAP-FORMAT 

!Description: This procedure will change the string to initial capital

!--------------------------------------------------------------------------------------------!

BEGIN-PROCEDURE INITCAP-FORMAT($String1,:$outputstring)

  Let #count=0

  let #total=length($String1)

  Let $currentval=''

  Let $nextval=''

  while  #count <= #total

  if Substr($String1,#count, 1)=' '

  

  let $currentval=Upper(Substr($String1, #count+1, 1))

  Let $nextval=$nextval||$currentval

 

  else 

  if #count=0 

  let $currentval=Upper(Substr($String1, #count+1, 1))

  Let $nextval=$nextval||$currentval

        else

                Let $currentval=lower(Substr($String1, #count+1,1))

                Let $nextval=$nextval||$currentval

  end-if

  end-if

  let #count=#count+1

  end-while

  Let $outputstring=$nextval

END-PROCEDURE INITCAP-FORMAT  

PeopleCode:-

Open an Email Client(Outlook) from PeopleCode:-

&sEmail = "mailto:someone@example.com?Subject=Hello";

ViewContentURL(&sEmail);

UpdateValue function to update the value of a specified field with the value provided. The value must be of a data type compatible with the field. Example:-

UpdateValue(RECORD.ASGN_CMP_EFFDT,CurrentRownumber(), ITEM_SELECTED, "Y");

FetchValue function to return the value of a buffer field in a specific row of a scroll level. Example:-

&VAL = FetchValue(SCROLL.DEPEND, CurrentRownumber(), DEPEND.DEPEND_ID);

Use the PriorValue function in FieldEdit and FieldChange PeopleCode to obtain the prior value of a buffer field that the user just changed.Example:-

&PRIOR = PriorValue(QUANTITY);

Use the RemoveDirectory function to remove the directory specified by path.

RemoveDirectory("C:\temp\mydir\temp", %filepath_absolute + %remove_subtree);

Use the RenameDBField function to modify a field definition to have a new name. Example:-

&oldcf = "CF1"; &newcf = "XYZ_STORE_ID"; &new = "FIELD." | &newcf; &old = "FIELD." | &oldcf; &ret = ​RenameDBField(@(&new), @(&old));

Use the RenameRecord function to modify a record definition to have a name. 

&ret = ​RenameRecord(RECORD.OrgIdTbl, RECORD.DeptIdTbl);

Use the Days365 function to return the number of days between the Date values date_val1 and date_val2 using a 365-day year. Example:-

&NUMDAYS = Days360(TERM_START_DT, PMT_DT);

AE:-

Application Engine with Disable Restart "off" is going "No Success" in PeopleSoft.

Now the question is, how to resolve this? 

First clear all the temporary processing data from the tables and then run the process again. But this is easier said than done. So lets break it down into the steps needed to resolve this issue.

1.Gather all the temporary table instances and standard SQL tables

In this step we need to collect the names of all the tables into which the program tries to insert the data. The easiest way to do this is by first enabling the Application Engine trace and then checking the SQL trace file. Just search all the INSERT INTO text in the SQL trace file which will give you all the tables where the temporary or the transnational data is inserted.

2.Deleting the data from tables

In this step we will delete the data form all the tables gathered in previous step but perform this task very carefully so that we don't end up deleting any data which which isn't supposed to. First of all, all the data from temp tables can be deleted without any worry. However, before deleting the data from standard SQL tables, we first need to analyse and determine what exactly need to be deleted or updated and only after that data should be deleted.

Run the process again

We can call Section Dynamically from SQL Action (Using Case Contruct) and PeopleCode Action

Meta-SQL Reference:-

%AeSection -This meta-variable to specify a quoted string containing the currently executing Application Engine section name.

%AeStep -This meta-variable to specify a quoted string containing the currently executing Application Engine Step name.

%Session or GetSession();

Exampele:-

Local AESection &Section; &Section = GetAESection("RULES", "DYN_SECT"); /* Open the base section */ &Section.SetTemplate("MY_APPL", "TEMPLATE"); /* Set the template section */ &Section.AddStep("NewStep2"); /* Insert NewStep2 */ /* Do some SQL stuff here */ &Section.SetSQL("DO_SELECT", &MySql); /* Modify the SQL in the added step */ &Section.Save(); &Section.Close(); /* Save and close */

CI:

If the user running the CI and the target page is display only for the user then data will not insert/update to the target.

Tools:

Page and Field Configurator available in release Tools Release 8.58 using Page Composer.