Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

(Back to V-MS Micellaneous Items SQL Posted to Wiki Listing - V-MS Micellaneous Items SQL Posted to Wiki   )

TYPE: Frequently Requested SQL

AUTHOR NAME: Zal Parchem

PURPOSE AND BACKGROUND: This SQL was created in 2007A SP01 PL05. For some reason SAP B1 does not allow you to put in some of the tables used below in the Query Generator and it is a bit difficult to know what SQL is in your system unless you go through each Category of the Query Manager on SAP B1.  This SQL gives you a list of the SQL on your SAP B1 System so you can get a starting point to know what is there for possible re-use.  For good measures with auditing purposes, it also includes the Groups Permission set up during posting to the Query Manager in SAP B1 (in case you insitute the Authorizations by Category Group).  Exporting it to Excel is good for making notes or creating a list of SQL to review with End Users.  If you find this SQL helpful, then use “copy and paste” to place and save it using the Query Generator of your SAP B1 installation.

Here is a screen print of the SQL results:

V-MS Listing of All SQL on System.png

Copy and Paste this SQL:

--V-MS Listing of All SQL on System Ver 1 ZP 2011 02 05

--DESCRIPTION:  Lists all SQL on the System along with the person who created it.  NOTE: "-2" indicates SQL provided by SAP during initial implementation.

--AUTHOR(s):
--Version 1 Zal Parchem 2011 02 05

SELECT

T0.IntrnalKey AS 'Query Key',
T0.QCategory AS 'Query Mgr Placement',
T0.QName AS 'Query Name',

T1.PermMask AS 'Group Permissions',

T2.U_Name AS 'Created By',
T2.SuperUser AS 'Super User?'

FROM OUQR T0

LEFT OUTER JOIN OQCN T1
ON T0.Qcategory = T1.CategoryID

LEFT OUTER JOIN OUSR T2
ON T1.UserSign = T2.Internal_K

ORDER BY

T0.QCategory,
T0.Qname

FOR BROWSE

  • No labels