Query to find out Frequently used Custom Workbook and worksheet details
========================================
SELECT DISTINCT 'DISCODEV4I' SCHEMANAME,
STATS.QS_DOC_OWNER WORKBOOK_OWNER,
WORKBOOKS.DOC_NAME WORKBOOKNAME,
STATS.QS_DOC_DETAILS WORKSHEETNAME,
TRUNC(MAX(STATS.QS_CREATED_DATE)) SHEET_LAST_RUN_DATE
FROM EUL_US.EUL4_BAS BUSUNIT,
EUL_US.EUL4_BA_OBJ_LINKS BALINKS,
EUL_US.EUL4_OBJS FOLDERS,
EUL_US.EUL4_QPP_STATS STATS,
EUL_US.EUL4_DOCUMENTS WORKBOOKS
WHERE BALINKS.BOL_BA_ID = BUSUNIT.BA_ID
AND BALINKS.BOL_OBJ_ID = FOLDERS.OBJ_ID
AND TO_CHAR(FOLDERS.OBJ_ID) = SUBSTR (STATS.QS_OBJECT_USE_KEY, 1, LENGTH(FOLDERS.OBJ_ID))
AND STATS.QS_DOC_NAME = WORKBOOKS.DOC_NAME
AND UPPER(WORKBOOKS.DOC_NAME)= UPPER('<Work Book Name>')
AND STATS.QS_ID IN
(SELECT DISTINCT MAX(STATS1.QS_ID)
FROM EUL_US.EUL4_QPP_STATS STATS1
WHERE STATS.QS_DOC_DETAILS = STATS1.QS_DOC_DETAILS
)
AND STATS.QS_DOC_OWNER = 'DISCODEV4I'
GROUP BY STATS.QS_DOC_OWNER,
BUSUNIT.BA_NAME,
WORKBOOKS.DOC_NAME,
STATS.QS_DOC_DETAILS
ORDER BY WORKBOOKNAME
Query to find Custom Business Area and Custom Folders:
=======================================
SELECT DISTINCT EB.BA_NAME "CUSTOM BUSINESS AREA" ,
EO.OBJ_NAME "CUSTOM FOLDER"
FROM EUL_US.EUL4_BAS EB ,
EUL_US.EUL4_OBJS EO ,
EUL_US.EUL4_BA_OBJ_LINKS EBOL ,
EUL_US.EUL4_EXPRESSIONS EE
WHERE EBOL.BOL_BA_ID = EB.BA_ID
AND EBOL.BOL_OBJ_ID = EO.OBJ_ID
AND EE.IT_OBJ_ID = EO.OBJ_ID
AND UPPER(EB.BA_NAME) LIKE 'XX%'
AND UPPER(EO.OBJ_NAME) LIKE 'XX%'
ORDER BY 1
========================================
SELECT DISTINCT 'DISCODEV4I' SCHEMANAME,
STATS.QS_DOC_OWNER WORKBOOK_OWNER,
WORKBOOKS.DOC_NAME WORKBOOKNAME,
STATS.QS_DOC_DETAILS WORKSHEETNAME,
TRUNC(MAX(STATS.QS_CREATED_DATE)) SHEET_LAST_RUN_DATE
FROM EUL_US.EUL4_BAS BUSUNIT,
EUL_US.EUL4_BA_OBJ_LINKS BALINKS,
EUL_US.EUL4_OBJS FOLDERS,
EUL_US.EUL4_QPP_STATS STATS,
EUL_US.EUL4_DOCUMENTS WORKBOOKS
WHERE BALINKS.BOL_BA_ID = BUSUNIT.BA_ID
AND BALINKS.BOL_OBJ_ID = FOLDERS.OBJ_ID
AND TO_CHAR(FOLDERS.OBJ_ID) = SUBSTR (STATS.QS_OBJECT_USE_KEY, 1, LENGTH(FOLDERS.OBJ_ID))
AND STATS.QS_DOC_NAME = WORKBOOKS.DOC_NAME
AND UPPER(WORKBOOKS.DOC_NAME)= UPPER('<Work Book Name>')
AND STATS.QS_ID IN
(SELECT DISTINCT MAX(STATS1.QS_ID)
FROM EUL_US.EUL4_QPP_STATS STATS1
WHERE STATS.QS_DOC_DETAILS = STATS1.QS_DOC_DETAILS
)
AND STATS.QS_DOC_OWNER = 'DISCODEV4I'
GROUP BY STATS.QS_DOC_OWNER,
BUSUNIT.BA_NAME,
WORKBOOKS.DOC_NAME,
STATS.QS_DOC_DETAILS
ORDER BY WORKBOOKNAME
Query to find Custom Business Area and Custom Folders:
=======================================
SELECT DISTINCT EB.BA_NAME "CUSTOM BUSINESS AREA" ,
EO.OBJ_NAME "CUSTOM FOLDER"
FROM EUL_US.EUL4_BAS EB ,
EUL_US.EUL4_OBJS EO ,
EUL_US.EUL4_BA_OBJ_LINKS EBOL ,
EUL_US.EUL4_EXPRESSIONS EE
WHERE EBOL.BOL_BA_ID = EB.BA_ID
AND EBOL.BOL_OBJ_ID = EO.OBJ_ID
AND EE.IT_OBJ_ID = EO.OBJ_ID
AND UPPER(EB.BA_NAME) LIKE 'XX%'
AND UPPER(EO.OBJ_NAME) LIKE 'XX%'
ORDER BY 1
0 comments