Recent Forum Posts

From categories:
page »
sony (guest) 14 Aug 2019 10:47
in discussion Forum / Help » PSQUERY App Engine email query results

Hey anyone can help on this? I am not receiving email with attachment. What confi should i tell admin to check w.r.t SMTP server?Please put your thoughts.

by sony (guest), 14 Aug 2019 10:47
NAG (guest) 13 Aug 2019 16:20
in discussion Forum / Help » SQRs completing, but showing Error in process scheduler

I am having same issue, can you please let me know any solution for this.

by NAG (guest), 13 Aug 2019 16:20


match_count INTEGER;
FOR t IN (SELECT owner, table_name, column_name
FROM all_tab_columns
WHERE owner = 'FSUSER' and data_type LIKE '%CHAR%' and COLUMN_NAME like '%SERVER%') LOOP

'SELECT COUNT(*) FROM ' || t.owner || '.' || t.table_name ||
' WHERE '||t.column_name||' = :1'
INTO match_count

IF match_count > 0 THEN
dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );



data finder sql by RahulAgrawalRahulAgrawal, 09 Aug 2019 08:51

I need to develop a query for Admissions where they can see the ADMT row and corresponding Action Reason, even if the person has been Matriculated. The MATR row covers up the ADMT/Action Reason. Can anyone help?

creating a query to get the row *prior* to MATR by Janet Renze (guest), 15 May 2019 15:56
danry (guest) 15 May 2019 02:36
in discussion Forum / Help » Connected Query passing prompt value w pcode

Thanks, it worked.

by danry (guest), 15 May 2019 02:36
Thomas (guest) 19 Apr 2019 11:11
in discussion Forum / Help » Overriding a Component's Search Record for a Component Interface

Yeong (or anyone else), have you figured out how to override the component's Add Search Record when running a component interface? When the CI executes we want users to be able to insert a new row into a component that typically they wouldn't be able to online because of business unit security. ie if they went online to "Add" a row, they wouldn't have the business unit available to choose on the Add Search record…but if we are adding it through the CI we want it to ignore BU Security. As it stands when the CI executes it says prompt value is invalid since the create keys are specifying a BU that they don't have access to….


by Thomas (guest), 19 Apr 2019 11:11

SELECT distinct ROUND(a.FILE_SIZE/(1024*1024)) as SIZE_MB,
PSprcsrqst b,
WHERE ROUND(a.FILE_SIZE/(1024*1024)) >100
AND b.prcsinstance =a.prcsinstance
and d.prcsinstance =a.prcsinstance
order by prcsinstance desc

SELECT servername,
||maxconcurrent, ',') within GROUP (
FROM ps_servercategory
WHERE servername NOT IN ('PSOS390','PSCDB','PSNT4','PSNT5')
GROUP BY servername;

SELECT servername,
||maxconcurrent, ',') within GROUP (
ORDER BY prcstype) AS Swim_lanes
WHERE servername NOT IN ('PSOS390','PSCDB','PSNT4','PSNT5')
GROUP BY servername;

SET echo ON;
SET serveroutput ON;
FROM all_tables c
(SELECT table_name
AND owner ='FSUSER'
—and num_rows between 100000 and 500000
dbms_output.put_line(TO_CHAR(sysdate,'dd-mon-yyyy:hh24:mi:ss')||' start '|| i.table_name);
IF i.num_rows <5000000 then
dbms_stats.gather_table_stats('FSUSER',i.table_name,estimate_percent => dbms_stats.auto_sample_size,degree => 32,METHOD_OPT =>'FOR ALL INDEXED COLUMNS SIZE 1');
dbms_output.put_line(TO_CHAR(sysdate,'dd-mon-yyyy:hh24:mi:ss')||' end '|| i.table_name);

Can anyone help me to write a PS query expression to get only Date and Month from Birthdate? We are using PS 9.2

Gary Chismar (guest) 11 Dec 2018 20:06
in discussion Forum / Help » Retrieve Multiple Rows from a Single CreateSQL /SQL Fetch into a Variable

Did this ever get resolved? If not, what database / version are you on? Oracle has a function that will retrieve multiple rows of data into one row with a separator. That comes to mind after reading this post. I believe it is call LISTAGG. If you search for Oracle with LISTAGG you should find what you are looking for. That is, if my assumption is correct.

by Gary Chismar (guest), 11 Dec 2018 20:06
Anand (guest) 06 Dec 2018 06:06
in discussion Forum / Help » CI Based Web Service - Generic Error messages generated

This may be because of the reason that the connector properties of the routing of the given service operation is not configured properly. Please make sure the primary URL of the connector property is configured properly. I faced the same issue and in my case the URL was not correct.

by Anand (guest), 06 Dec 2018 06:06

We've reached the limit of an int field (int -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) 4 Bytes)
and getting the error "Arithmetic overflow error converting expression to data type int." when running an application engine job to create user profiles.
What can we do? Our immediate thoughts are to
1. Change int to bigint for the field VERSION on table PSVERSION
2. Reset the Version number for UPM to 0
However, we do not know the impact of doing this.
Also contacted Oracle for resolution

Limit reached on PSVERSION.VERSION field by Peter Morris (guest), 04 Dec 2018 22:12
Ricky (guest) 24 Oct 2018 17:43
in discussion Forum / Help » Connected Query passing prompt value w pcode

Works! Thanks!

by Ricky (guest), 24 Oct 2018 17:43

I haven't been able to get a clear answer on this and I am hoping someone here can help. I have an App Engine Program that I am modifying the PeopleCode and I have a CreateSQL function that I know will sometimes result in more than 1 row being returned (at least in the DBMS/SQL Server). My question is whether the &SQL.Fetch(&UOMResult) will store all resulting row values into a variable in a single fetch (non-looping), or am I required to loop through and can only select a single row/value per iteration? Ideally I would like to be able to use the first method and evaluate all the values retrieved in a single fetch against another variable in the program. Is this possible and how do I access each row returned and do a comparison? I wasn't sure if some type of array might be required.

I would like to get the result set (1 column/1 or more rows) and then use an If statement to compare the value(s) in the &UOMResult variable to another variable called &UOM, and do some logic on it, else do other logic. I can provide additional clarification if needed. Thank you for any feedback / code examples you can provide.

SQR 3203 file writing issue
Vins (guest) 13 Oct 2018 12:27
in discussion Forum / Help » SQR 3203 file writing issue

I am running a process in which we are processing many employees but for any certain employee I am getting sqr 3203 file writing error.
Please help me as if it is file issue i should get it for first employee itself

SQR 3203 file writing issue by Vins (guest), 13 Oct 2018 12:27

In Service Operations Setup there is an option to check "User/Password Required".

If I do not check it I can make a simple call with this body and it works fine:

<?xml version='1.0' encoding='utf-8'?>
<Envelope xmlns:s1='test'>
<GetCompIntfcPO_EXPRESS_CI >
</GetCompIntfcPO_EXPRESS_CI >

But when I check that box I get this:

User Password required for Service Operation CI_PO_EXPRESS_CI_G. (158,535)

Which is OK, just telling me to fill in the header. Here is the header I add:


Which as far as I can tell is correct. But PeopleSoft gives me the exact same error, as though it does not even bother to read the Header data.

Is there something else required? Do I need to fill in HTTP headers as well?

Are all of the user profiles tied back to an EMPLID in PSOPRDEN. If so, I can imagine a query that links PSOPRDEFN and PSROLEUSER to come up with a distinct list of roles that each user should have based on the combination of the roles all of their accounts had. From there, you'd have to determine how to programmatically choose which profile will be the one that is kept. You would then insert the necessary rows into PSROLEUSER. Unfortunately, I think that's the "easy" part.

The harder part would be all of the row level permissions, favorites, user defaults, etc. *IF* you knew where all of those things were stored, I guess you could do something similar to above.

Besides that, there are numerous places where a user name may be hard coded (e.g. process notifications, BI report group security, etc.). Those may need to be updated to refer to the proper user.

I'm in the same situation. If anyone comes up with a solution, I'd love to see it.

We currently have some users with two or more user profiles that we need to merge into one user profile. Has anyone had this situation before? If so, how did you tackle it? I wondered about using ExcelToCI however that will require creating a new component interface which we're not looking to do. There are a lot of them so we're not wanting to do it manually either. Ideas? Thoughts?

Merge User Profiles, PeopleSoft 9.0, PT 8.54 by Tracy (guest), 21 Sep 2018 15:21
Dave (guest) 06 Sep 2018 20:43
in discussion Forum / Help » Connected Query passing prompt value w pcode

Just wanted to say thank you to Johan for writing this article. I'm new to PeopleSoft and just spent two weeks trying to get a connected query BI Publisher report working from Process Scheduler. This write up gave me the information I needed to make it work.

by Dave (guest), 06 Sep 2018 20:43
page »
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License