Quick post!
We had a PDF generated using BI Publisher and the same file was stored as an attachment in a record PSFILE_ATTDET.
However, while trying to view the attachment using the ViewAttachment function, the PDF file prompted to be downloaded to local storage, instead of opening directly in a new browser tab. This was earlier working with PeopleTools 8.53, but not with upgraded PeopleTools 8.58.
Turns out, that starting PeopleTools 8.55, X-Content-Type-Options: nosniff response header is added to content retrieved using the ViewAttachment function.
This was the code I had:
Local number &retcode = ViewAttachment(URL.XXX, XXXX.ATTACHSYSFILENAME, "Attachments");
As this response header was not part of PeopleTools 8.53, though I did not specify the file extension in the aforementioned code, PeopleSoft was able to determine the appropriate application type and open the PDF file without any issues.
However, with 8.58, without the file extension, it appears that the response header blocked auto-detection and hence, the issue. This was fixed this by adding .pdf as below.
Local number &retcode = ViewAttachment(URL.XXX, XXXX.ATTACHSYSFILENAME, "Attachments.pdf");
Hope this helps! 😊
Mani 2.0 
I am in higher education and new to using PeopleSoft Query Manager, very new, and I have been reviewing your posts, but I haven’t found anything that gets at what I need to build a query that pulls all the 10k rows that I need it to pull.
Table A has a “Where clause” field (examples provided below) that has SUBJECT field CATALOG_NBR field values of the courses that I need the query to pull from Table B (course = subject + catalog number). In the “Where clause” field, the values have underscores to represent the different variations of catalog numbers of the course that I need the query to pull from Table B (course = subject + catalog number), which has specific SUBJ + CATALOG_NBR. The “Where clause” field may have SUBJECT only, which means all catalog numbers in that subject or may only have CATALOG_NBR, meaning all subjects with the given catalog number value(s), if the”Where clause” field has both, it needs to satisfy both criteria to be included.
Example #1: The WHERE_CLAUSE: CATALOG_NBR LIKE ‘ 5__’ OR CATALOG_NBR LIKE ‘ 5___’ – would pull ALL Subjects with 3-4 character Catalog numbers starting with 5. The characters can be numbers or letters. Like AMD 500, WMST 599, ART 500A, VPA 5999.
Example #2: The WHERE_CLAUSE: SUBJECT = ‘EDUC’ AND (CATALOG_NBR LIKE ‘ 2__’) – would pull all Subjects = EDUC with catalog numbers with 3 characters, starting with 2, like EDUC 200, EDUC 20A, EDUC 299, etc.
Example #3: The WHERE_CLAUSE: SUBJECT = ‘MGMT’ – would pull all catalog numbers with the Subject = MGMT, like MGMT 100, MGMT 102, MGMT 100A, MGMT 2000, etc.
(From Table A – CLST_DETL_TBL)
1. SAA_WHERE_CLAUSE
(From Table B – CRSE_OFFER_TCVW)
2. SUBJECT
3. CATALOG_NBR
Do you have ANY idea how to make this query in Query Manager? THANK YOU!
Sally – ignore my earlier comment. So, ideally, you’d like to execute each where clause from PS_CLST_DETL_TBL against table CRSE_OFFER_TCVW and retrieve specific columns from CRSE_OFFER_TCVW? Is my understanding correct?
Yes, that’s exactly what I need! The field values in SUBJECT & CATALOG_NBR based on the SAA_WHERE_CLAUSE value.
Sorry for the delay, work is too much some times – REALLY appreciate the help!!! If you can see it, please feel free to email me directly.
Hi Sally – sorry for the delay. There’s no way to do this in a PS Query directly – way too complex and PeopleSoft doesn’t support the keywords. There are two ways to do it:
(1) Create an Application Engine process in PeopleSoft that constructs the SQL dynamically and executes it for each row (easier method)
(or)
(2) Write an Oracle SQL (I think it should be possible, but complex)
I did not see your email address in either comment, so haven’t had a chance to respond.
Hope this helps!
Is there any chance you have come up with a way to pull this info? I haven’t gotten any further with it 😦
Any ideas (at all) about how to do this would be super appreciated!
Sally – you’d have to create a SQL that looks like this:
SELECT table_name,
TO_NUMBER (
EXTRACTVALUE (
xmltype (
DBMS_XMLGEN.getxml (
‘select count(*) c from ‘ || table_name)),
‘/ROWSET/ROW/C’))
AS row_count
FROM all_tables
WHERE table_name LIKE ‘PS_%’
You’ll notice that DBMS_XMLGEN is not supported in a PS Query. So, as mentioned in my earlier comment, an option is to create an Application Engine (or) write a SQL that constructs the SQL on the fly and access the correct value.
Thank you so much for spending time on this! I will see if I can contact my tech friends with ability & access to see if they can implement it (I can’t do it myself). Seriously, this is so very much further than I’ve gotten.
Dear Mani –
I really don’t see how this would give me the fields that I need (1. SAA_WHERE_CLAUSE from Table: CLST_DETL_TBL, 2. SUBJECT & 3. CATALOG_NBR from Table: CRSE_OFFER_TCVW).
The SQL code you provided appears to be looking at all tables and giving me the number of rows in each…
I need the program to use the WHERE_CLAUSE field from the CLST_DETL_TBL table to pull all of the right/indicated SUBJECT & CATALOG_NBR values from the CRSE_OFFER_TCVW table.
Is there any way to do what I’m asking for with SQL?
Sally – that SQL I had provided earlier was just a sample, to give you an idea on how the SQL should look like.
I don’t have access to a Campus Solutions instance, but based on your earlier comments/descriptions, can you try this SQL and let me know if it works?
SELECT COURSE
FROM PS_CLST_DETL_TBL
CROSS JOIN
XMLTABLE (
‘/ROWSET/ROW’
PASSING XMLTYPE (
DBMS_XMLGEN.getxml (
‘SELECT subject||chr(32)||CATALOG_NBR COURSE FROM PS_CRSE_OFFER_TCVW WHERE ‘
|| SAA_WHERE_CLAUSE))
COLUMNS COURSE VARCHAR2 (100) PATH ‘COURSE’);