OnBase Database Query

[Mary_Steiner]

I need a query that will bring back all document types that were scanned within a date range, including the fields Document type, created by,  revision dates, viewed by and pages 

[Roger_Linhart]

@Mary Steiner 

The hsi.itemdata table has an entry for each document in OnBase. The only tough part is the "scanned" criteria. Scanned documents would be in a batch but so would sweep imports. I don't know if there's another way to identify a scanned document but you could try WHERE (hsi.itemdata.batchnum <> 0) AND (hsi.itemdata.status <> 16). The hsi.itemtypnum column can be joined to hsi.itemtype to get itemtypename (Doc Type). Likewise hsi.itemdata.usernum with hsi.useraccount. The other columns you're looking for are obvious in hsi.itemdata. I'm not sure where you'd find viewed by or page count though.

[David_Hoffman]

In addition to using usernum, I am assuming your scanned batches are ingested via a Scan Queue (even if from disconnected scanning) - if so, there will be a batchnum (<> 0) in itemdata table.  You can use the batchnum to join to archivedcommitq table to determine the batch (queuenum to scanqueue table, too).

[Ediaz]

The hsi.itemdatapage table could serve you, in this table there is one record for each page of the document, that is, if you have a MULTITIF with 5 pages in this table, 5 records will appear with the same itemnum, one record per page

[Ryan_Wakefield]

What you will need to do is combine a couple tables. I say this because in the hsi.itemdata table there is no clean understanding how the documents created the batch. However, if you leverage the hsi.scanninglog table (more specifically eventnum=2, actionnum=200, and subactionnum=1), then you will receive the batches to where the batch was created by a user scanning said batch (from paper). Then you can add other fields as you see fit.

The only pieces that could be difficult are the revision dates and viewed by as the viewed by is going to be in the hsi.transactionxlog table and the revisions you will find in another table. So you might need to break up your query a little bit to make it more manageable.