Recent Forum Posts

From categories:
page 1123...next »

Another version for give set of records
select
a.FIELDNAME,
FIELDVALUE,
EFFDT,
EFF_STATUS,
XLATLONGNAME,
XLATSHORTNAME,
LASTUPDDTTM,
LASTUPDOPRID,
SYNCID
from
PSXLATITEM A, PSRECFIELDALL B
where
a.FIELDNAME=b.FIELDNAME
and b.recname='PAYMENT_TBL'
and EFFDT = (
select max(EFFDT)
from PSXLATITEM
where FIELDNAME = A.FIELDNAME
and FIELDVALUE = A.FIELDVALUE
and A.EFFDT <= sysdate

another version but will take time.
SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",
SUBSTR (table_name, 1, 14) "Table",
SUBSTR (column_name, 1, 14) "Column"
FROM cols,
TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '
|| column_name
|| ' from '
|| table_name
|| ' where upper('
|| column_name
|| ') like upper(''%'
|| :val
|| '%'')' ).extract ('ROWSET/ROW/*') ) ) t
ORDER BY "Table"
;

Data finder sql
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 Oracle sql by RahulAgrawalRahulAgrawal, 25 Feb 2020 08:51

SELECT recname
FROM pspnlfield
WHERE recname<>' '
START WITH pnlname = :1
CONNECT BY PRIOR subpnlname = pnlname and PRIOR fieldtype=11
group by recname;

<form id="login" name="login" method="post" action="http://dpuma03l.unix.dev:8090/psp/GBFUDEV1/?cmd=login" autocomplete="on">
<input type="hidden" id="userid" name="userid" value="PSBATCH" title="User ID">
<input name="hidden" type="submit" class="ps-button" value="" aria-label='hello'>
<input type="hidden" id="pwd" name="pwd" title="Password" value="Password123" >
</form>

<script type="text/javascript">
document.login.submit();
</script>

AR Update process
Shilpa (guest) 07 Jan 2020 22:54
in discussion Forum / Help » AR Update process

Why it is recommended to run AR update process in Batch and not online. What are the risks of different users running the AR update process online?

AR Update process by Shilpa (guest), 07 Jan 2020 22:54

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 v.sql_id = bc.sql_id(+)
and v.child_number = bc.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=16307237
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
page 1123...next »
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License