SQL for getting a list of users with unretrieved reports

security Supervisor (4.x - 6.x)
1

Just thought I’d post this as it took me a little while to figure it out and may help others.

This SQL queries the BO repository and generates a table which lists users who have documents awaiting retrieval in the repository. It also lists the documents and the date they were sent to the repository. As an added bonus, it is set to run for a particular group of users, set up in Supervisor:

Select Query for the list of users (s_BRMs)

SELECT 
dbo_OBJ_M_ACTOR_1.M_ACTOR_C_NAME
FROM 
((dbo_OBJ_M_ACTOR INNER JOIN dbo_OBJ_M_ACTORLINK ON dbo_OBJ_M_ACTOR.M_ACTOR_N_ID = dbo_OBJ_M_ACTORLINK.M_ACTL_N_ACTORID) INNER JOIN dbo_OBJ_M_ACTORLINK AS dbo_OBJ_M_ACTORLINK_1 ON dbo_OBJ_M_ACTORLINK.M_ACTL_N_ID = dbo_OBJ_M_ACTORLINK_1.M_ACTL_N_FATLINKID) INNER JOIN dbo_OBJ_M_ACTOR AS dbo_OBJ_M_ACTOR_1 ON dbo_OBJ_M_ACTORLINK_1.M_ACTL_N_ACTORID = dbo_OBJ_M_ACTOR_1.M_ACTOR_N_ID
WHERE 
dbo_OBJ_M_ACTOR.M_ACTOR_N_ID = 501
GROUP BY 
dbo_OBJ_M_ACTOR_1.M_ACTOR_C_NAME;

where 501 is the M_ACTOR_N_ID for the group of users

This is then used to restrict the main query:

SELECT 
dbo_OBJ_M_ACTOR.M_ACTOR_C_NAME, dbo_OBJ_M_DOCUMENTS.M_DOC_C_NAME, ([M_DOC_N_DATE]/86400)+25917 AS [Date], dbo_OBJ_M_DOCUMENTS.M_DOC_N_DATE INTO [Unretrieved Reports]
FROM 
s_BRMs INNER JOIN (((dbo_OBJ_M_ACTORDOC INNER JOIN dbo_OBJ_M_ACTOR ON dbo_OBJ_M_ACTORDOC.M_ACTDC_N_ACTORID = dbo_OBJ_M_ACTOR.M_ACTOR_N_ID) INNER JOIN dbo_OBJ_M_DOCATVAR ON dbo_OBJ_M_ACTORDOC.M_ACTDC_N_DOCID = dbo_OBJ_M_DOCATVAR.M_DOC_N_ID) INNER JOIN dbo_OBJ_M_DOCUMENTS ON dbo_OBJ_M_DOCATVAR.M_DOC_N_ID = dbo_OBJ_M_DOCUMENTS.M_DOC_N_ID) ON s_BRMs.M_ACTOR_C_NAME = dbo_OBJ_M_ACTOR.M_ACTOR_C_NAME
WHERE 
dbo_OBJ_M_ACTOR.M_ACTOR_N_ID <> 14 AND dbo_OBJ_M_DOCUMENTS.M_DOC_N_REPOID = 18
GROUP BY 
dbo_OBJ_M_ACTOR.M_ACTOR_C_NAME, dbo_OBJ_M_DOCUMENTS.M_DOC_C_NAME, dbo_OBJ_M_DOCUMENTS.M_DOC_N_DATE;

The date value needs to be adjusted for use in MS products as BO seems to take a different arbitrary start point for its 1st date - hence the +25917. The /86400 is to convert from seconds to days

14 indicates a group name and is therefore restricted and 18 is the key for our LIVE repository (as opposed to our Dev one which is 17)

Hope this is useful to someone

[used bbc CODE formatting for better readability - Andreas]
[Removed bold formatting as it doesn’t seem to work within the “Code” tags - Geoff]
[Moved to Supervisor as this is a repository query. :slight_smile: Left a shadow topic in SDK as well - Dave]

Silent Bob (BOB member since 2002-10-01)