(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:
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.
--Version 1 Zal Parchem 2011 02 05
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