Best Of
Re: AP_INVOICES_PKG.GET_APPROVAL_STATUS package extremely slow
First, I would mention the possibility of performance issues related to running Gather Schema Statistics. Since performance seems to be degrading over time, it might be possible that you need to run Gather Schema Statistics, even in test instances, to get the best performance. This has resolved many performance issues for us.
What method are you using to do the report? That could have an impact as well.
Are you filtering the data any or are you returning all rows?
On which release are you? We are on 12.2 and I ran your query as-is in our test instance. With 5,743,834 rows, it returned data within seconds. A solution could be somewhat related to whether you are on 12.2 or an earlier release. As another quick test, I modified the query to return only those which were not APPROVED, ensuring all rows would be read. It returned 42,143 rows in about 45 minutes. So depending upon whether you are filtering the data by anything and how many rows you have, it could take quite some time.
Finally, consider this Note, which provides various methods to find different Status': What are the Invoice Statuses in the Invoice Workbench? How can you Query the Statuses from SQL? (Doc ID 2507071.1)
I hope this helps answer your question.
DGray
Re: PS Query Drilling URL - Attachment URL to URL which is a FTP site, NOT a database table
Oracle Support responded with a Bug request.
This is a big wet blanket to our Tools 8.53 upgrade benefits , if others are interested please log a case and reference the bug to raise its priority.
Bug 19429388 : FILE ATTACHMENTS HELD ON FTP SERVER ARE NOT SUPPORTED FOR DRILLURL ON PSQUERY
PS Query Drilling URL - Attachment URL to URL which is a FTP site, NOT a database table
Is it possible to use a FTP type URL in a drilling URL in a PS Query? The only way I've had it work and actually produce the document in the FTP site/folder is to use an Query - Drilling URL - Attachment URL- URL Type = External URL. If I set the URL Type to Internal URL, I get the error message "URL Identifier {URL Name} in request is not of record type".
The problem with using the External URL as the URL Type is that the URL definition contains the user name and password in it. When you perform an "inspect element" in the Chrome browser, you can see that the User Name and Password is available for anyone to see. The user name and password will also appear in the browser tab if the target document is not found in the URL site (FTP URL).
I was hoping I could abstract the user name and password with the URL Properties, but I don't seem to be able to get the Query Drilling URL to do anything with the PS URL Properties.
A Drilling URL - Attachment URL - URL Type = Internal URL to a document stored in a URL that is a FILEDB type URL works nicely and only shows the URL reference and the document name reference in the resulting HTML.
Re: Is there a master list of all delivered roles from Oracle?
Here is the SQL that you can run against you database to list out all the roles and the permission lists attached to those roles.
SELECT A.ROLENAME, A.DESCR, A.DESCRLONG, B.CLASSID, C.CLASSDEFNDESC
FROM PSROLEDEFN A,
PSROLECLASS B,
PSCLASSDEFN C
WHERE A.ROLENAME = B.ROLENAME
AND B.CLASSID = C.CLASSID
Re: How to disable countries from the list of value?
I'm not aware of a way to enable/disable countries but you could take a couple of steps to make them unusable.
Create an address format that is unusable - has one field and the field defaults to 'country not permitted' or such. In Receivables under Setup > System > Countries, assign that address format to the countries you don't want used. When a user tries to create an address, the format will come up as 'country not permitted' Technically, they could save it that way, but if someone tried to send something they would have to manually update the real address.
In Trading Community Administration > Trading Community > Administration > Geography Hierarchy , don't define a structure, or if the countries are defined under a structure, remove them from it. This will impact some of the modules like HR and OM.
Or you could use personalizations on the form(s) to check the country. The condition would be the country is in the list you specify and the action is a message of type Error (which will prevent saving) and the message text can be whatever you like. As always, test in a test instance first.
Psst! Did Someone Say Health Check Analyzer?
Psst! Did someone say health check analyzer?
That’s right, we have a PeopleSoft monitoring analyzer to assist with finding common data and configuration issues, along with recommendations on how to fix them.
PeopleSoft Monitoring Analyzer 2890448.1. https://bit.ly/3H3lcQ3