Recent Forum Posts

From categories:
page 1123...next »

Below query may help to get the sql executed by SQR, it works best only when single process running on the process scheduler
select
v.SQL_FULLTEXT,
bc.position,
bc.value_string
from v$sql v , v$sql_bind_capture bc
,PSPRCSRQST p
where bc.sql_id = v.sql_id
and bc.child_number = v.child_number
and to_date(last_LOAD_TIME,'YYYY-MM-DD hh24:mi:ss') BETWEEN p.BEGINDTTM AND p.ENDDTTM
AND module LIKE '%sqrw%'
AND p.PRCSINSTANCE=16208274
order by last_LOAD_TIME ASC ,v.sql_id, bc.position;

adding debug statement to sqr procedure using notepad++ regular expression

in find field add

begin-procedure (.*)

and in replace field add

begin-procedure \1 \r\n #ifdef debugx \r\n show '\1' \r\n #endif \r\n

sample debug statement added

original:-

begin-procedure Init-Report

new:—-
begin-procedure Init-Report
#ifdef debugx
show 'Init-Report'
#endif

another way
SELECT LISTAGG (COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY COLUMN_ID)
FROM USER_TAB_COLS WHERE TABLE_NAME = 'YOUR_TABLE'

guest (guest) 13 Nov 2019 07:50
in discussion Forum / Help » Connected Query passing prompt value w pcode

Hi ,

I have similar requirement where user have same field(BU) used as prompt multiple times.
Say Business_Unit1, Business_Unit2
I tried above approach but I am not getting correct output, its only taking value of one prompt BU1 parameter only .
I have given messagebox and able to see value for other prompt BU2, but report is running for BU1 only.
here is code which i have written -
&CQPromptRec.GetField(1).Value = &BU1;
&CQPromptRec.GetField(2).Value = &BU2;

Please help me on this.

by guest (guest), 13 Nov 2019 07:50
cohort retention query
Bill (guest) 28 Oct 2019 14:48
in discussion Forum / Help » cohort retention query

I am new to PeopleSoft and would like to have a query available that summarizes fall to fall student retention rates by program. If anyone has a query they are willing to share please let me know.

cohort retention query by Bill (guest), 28 Oct 2019 14:48
Record (Table) Name to Page Map
Jacob (guest) 16 Oct 2019 19:07
in discussion Forum / Help » Record (Table) Name to Page Map

Hello all,

I am new to Campus Solutions (am using 9.2), and am wondering if there exists a document that shows how the out-of-the-box Record (Table) Names map to various pages throughout the software. I am referrring to the Record (Table) Names shown at Set Up SACR > Common Definitions > Common Attributes Setup > Record Context. I understand that this would be a rather large/extensive document, but I figure it is worth asking.

I am being tasked with learning this software as quickly as possible, and am currently stumbling through how to use the Common Attribute Framework (CAF). If a document as outlined above exists somewhere, I imagine it will save me quite a bit of time in trail and error/research in learning where fields in specific tables can be found/displayed on various pages.

Hopefully my question isn't too vague. If so, I will try my best to add more detail. However, I am in the beginning stages of this learning process.

Thank you in advance for any help/rference you may be able to provide!

Record (Table) Name to Page Map by Jacob (guest), 16 Oct 2019 19:07
anku (guest) 16 Oct 2019 11:39
in discussion Forum / Help » Web Service Help

I am new to PeopleSoft Development, actually i am facing some issues that is, some application messages are stuck in working status and when i saw the details it seems no errors but in XML file it is showing one's oprid is reassigning to other user instead of submitting.I checked both oprid are in active status.I am not getting exactly what could be the issue. Could you please do needful regarding this.

Thanks in advance.

by anku (guest), 16 Oct 2019 11:39

I'm using the current case statement as an expression in my query to find service anniversaries. The why it's working now is if I run it anytime during September it's gives me employees whose anniversary is during September. I need to change the case statement so that when I run it in September, it gives me list of employees whose anniversary is in October. When I run it in October, I should get employee's whose service anniversary is in November.

Any help would be appreciated.

CASE
WHEN (ADD_MONTHS(B.SERVICE_DT, 60)) BETWEEN (TO_DATE((TO_DATE((ADD_MONTHS(LAST_DAY(SYSDATE),-1)), 'YYYY-MM-DD') + 1),'YYYY-MM-DD')) AND (TO_DATE ((LAST_DAY(SYSDATE)),'YYYY-MM-DD')) THEN 5
WHEN (ADD_MONTHS(B.SERVICE_DT, 120)) BETWEEN (TO_DATE((TO_DATE((ADD_MONTHS(LAST_DAY(SYSDATE),-1)), 'YYYY-MM-DD') + 1),'YYYY-MM-DD')) AND (TO_DATE ((LAST_DAY(SYSDATE)),'YYYY-MM-DD')) THEN 10
WHEN (ADD_MONTHS(B.SERVICE_DT, 180)) BETWEEN (TO_DATE((TO_DATE((ADD_MONTHS(LAST_DAY(SYSDATE),-1)), 'YYYY-MM-DD') + 1),'YYYY-MM-DD')) AND (TO_DATE ((LAST_DAY(SYSDATE)),'YYYY-MM-DD')) THEN 15
WHEN (ADD_MONTHS(B.SERVICE_DT, 240)) BETWEEN (TO_DATE((TO_DATE((ADD_MONTHS(LAST_DAY(SYSDATE),-1)), 'YYYY-MM-DD') + 1),'YYYY-MM-DD')) AND (TO_DATE ((LAST_DAY(SYSDATE)),'YYYY-MM-DD')) THEN 20
WHEN (ADD_MONTHS(B.SERVICE_DT, 300)) BETWEEN (TO_DATE((TO_DATE((ADD_MONTHS(LAST_DAY(SYSDATE),-1)), 'YYYY-MM-DD') + 1),'YYYY-MM-DD')) AND (TO_DATE ((LAST_DAY(SYSDATE)),'YYYY-MM-DD')) THEN 25
WHEN (ADD_MONTHS(B.SERVICE_DT, 360)) BETWEEN (TO_DATE((TO_DATE((ADD_MONTHS(LAST_DAY(SYSDATE),-1)), 'YYYY-MM-DD') + 1),'YYYY-MM-DD')) AND (TO_DATE ((LAST_DAY(SYSDATE)),'YYYY-MM-DD')) THEN 30
WHEN (ADD_MONTHS(B.SERVICE_DT, 420)) BETWEEN (TO_DATE((TO_DATE((ADD_MONTHS(LAST_DAY(SYSDATE),-1)), 'YYYY-MM-DD') + 1),'YYYY-MM-DD')) AND (TO_DATE ((LAST_DAY(SYSDATE)),'YYYY-MM-DD')) THEN 35
WHEN (ADD_MONTHS(B.SERVICE_DT, 480)) BETWEEN (TO_DATE((TO_DATE((ADD_MONTHS(LAST_DAY(SYSDATE),-1)), 'YYYY-MM-DD') + 1),'YYYY-MM-DD')) AND (TO_DATE ((LAST_DAY(SYSDATE)),'YYYY-MM-DD')) THEN 40
WHEN (ADD_MONTHS(B.SERVICE_DT, 540)) BETWEEN (TO_DATE((TO_DATE((ADD_MONTHS(LAST_DAY(SYSDATE),-1)), 'YYYY-MM-DD') + 1),'YYYY-MM-DD')) AND (TO_DATE ((LAST_DAY(SYSDATE)),'YYYY-MM-DD')) THEN 45
WHEN (ADD_MONTHS(B.SERVICE_DT, 600)) BETWEEN (TO_DATE((TO_DATE((ADD_MONTHS(LAST_DAY(SYSDATE),-1)), 'YYYY-MM-DD') + 1),'YYYY-MM-DD')) AND (TO_DATE ((LAST_DAY(SYSDATE)),'YYYY-MM-DD')) THEN 50
WHEN (ADD_MONTHS(B.SERVICE_DT, 660)) BETWEEN (TO_DATE((TO_DATE((ADD_MONTHS(LAST_DAY(SYSDATE),-1)), 'YYYY-MM-DD') + 1),'YYYY-MM-DD')) AND (TO_DATE ((LAST_DAY(SYSDATE)),'YYYY-MM-DD')) THEN 55
WHEN (ADD_MONTHS(B.SERVICE_DT, 720)) BETWEEN (TO_DATE((TO_DATE((ADD_MONTHS(LAST_DAY(SYSDATE),-1)), 'YYYY-MM-DD') + 1),'YYYY-MM-DD')) AND (TO_DATE ((LAST_DAY(SYSDATE)),'YYYY-MM-DD')) THEN 60
WHEN (ADD_MONTHS(B.SERVICE_DT, 780)) BETWEEN (TO_DATE((TO_DATE((ADD_MONTHS(LAST_DAY(SYSDATE),-1)), 'YYYY-MM-DD') + 1),'YYYY-MM-DD')) AND (TO_DATE ((LAST_DAY(SYSDATE)),'YYYY-MM-DD')) THEN 65
WHEN (ADD_MONTHS(B.SERVICE_DT, 840)) BETWEEN (TO_DATE((TO_DATE((ADD_MONTHS(LAST_DAY(SYSDATE),-1)), 'YYYY-MM-DD') + 1),'YYYY-MM-DD')) AND (TO_DATE ((LAST_DAY(SYSDATE)),'YYYY-MM-DD')) THEN 70
ELSE 0 END

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

SET SERVEROUTPUT ON SIZE 100000

DECLARE
match_count INTEGER;
BEGIN
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

EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM ' || t.owner || '.' || t.table_name ||
' WHERE '||t.column_name||' = :1'
INTO match_count
USING 'PSNT';

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

END LOOP;

END;

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

Thanks!

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

SELECT distinct ROUND(a.FILE_SIZE/(1024*1024)) as SIZE_MB,
a.prcsinstance,
b.prcsname,
b.oprid,
b.RUNCNTLID,
C.DESCR,
—,
—c.descrlong
outputdir
FROM PS_CDM_FILE_LIST a,
PSprcsrqst b,
PS_PRCSDEFN C,
PS_CDM_LIST D
WHERE ROUND(a.FILE_SIZE/(1024*1024)) >100
AND b.prcsinstance =a.prcsinstance
AND b.PRCSTYPE =c.PRCSTYPE
AND C.PRCSNAME =b.PRCSNAME
and d.prcsinstance =a.prcsinstance
order by prcsinstance desc

SELECT servername,
listagg(PRCSCAtegory
||'#Prty-'
||DECODE(prcspriority,1,'Low',5,'Medium',9,'High')
||'#maxc-'
||maxconcurrent, ',') within GROUP (
ORDER BY PRCSCAtegory) AS PRCSCATEGORY_CONCURRENT
FROM ps_servercategory
WHERE servername NOT IN ('PSOS390','PSCDB','PSNT4','PSNT5')
GROUP BY servername;

SELECT servername,
listagg(prcstype
||'-'
||'#Prty-'||DECODE(prcspriority,1,'Low',5,'Medium',9,'High')
||'#maxc-'
||maxconcurrent, ',') within GROUP (
ORDER BY prcstype) AS Swim_lanes
FROM PS_SERVERCLASS
WHERE servername NOT IN ('PSOS390','PSCDB','PSNT4','PSNT5')
GROUP BY servername;

SET echo ON;
SET serveroutput ON;
DECLARE
BEGIN
FOR i IN
(SELECT DISTINCT TABLE_NAME,
num_rows
FROM all_tables c
WHERE TABLE_NAME IN
(SELECT table_name
FROM dba_TAB_STATISTICS
WHERE STALE_STATS='YES'
AND owner ='FSUSER'
—and num_rows between 100000 and 500000
)
)
LOOP
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);
END IF ;
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);
COMMIT;
END LOOP;
END;
/

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
page 1123...next »
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License