cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
831
Views
0
Helpful
7
Replies

RequestCenter Select Queries

RequestCenter Select Queries

Anyone in this group have any experience with select queries against the RequestCenter DB? I know that this is not generally supported, but we ocassionally have use cases where we need to retrieve transaction data in a finer grained format in real time.

We have numerous SQL routines in our library (simple selects all -- of course), but there is a specific case that I haven't been able to solve yet. If there are any tech resources out there with good knowledge of the schema I'd be very interested to exchange ideas.

7 Replies 7

Mihir Mihir
Level 1
Level 1

Hi Doug,

We occassionaly do run SQLs againgst RequestCenter DB to generate some very specific reports demanded by end-users. Let me know what data you are looking for to pull up from DB.

-Mihir

I was looking for this, by serviceId.

Submitted Date

Approved Date

Completed Date

Time to Approve

Time to Deliver Once Approved

Total Time from Submit to Completed

For services with due date during a 30 month window that were completed, but not cancelled

Emir E
Level 1
Level 1

more info pls

Emir E
Level 1
Level 1

The reporting packages should be able to help you out with this

Emir E
Level 1
Level 1

This should get you started:

 

 

select r.RequisitionId,
  s.Name            as "Service Name",
  r.createdOn       as "Created Date",
  case when r.Statusid = 0 then 'Preparation'
 when r.Statusid = 1 then 'Ongoing'
 when r.Statusid = 2 then 'Closed'
 when r.Statusid = 3 then 'Cancelled'
 when r.Statusid = 4 then 'Rejected'
 when r.Statusid = 5 then 'Delivery Cancelled'
 else 'Unknown'
  end As Status,
  rb.FirstName || ' ' || rb.Last

Thanks so much for your help on this! This is where I got to, but it looks like your queries will do the trick. Thanks again.

select
TxRequisition.RequisitionID,
DefService.Name,
TxRequisition.StartedDate as SubmittedDate,
TxRequisitionEntry.StartedDate as ApprovedDate,
TxRequisitionEntry.DueDate,
TxRequisition.ClosedDate,
DATEDIFF( day, TxRequisitionEntry.StartedDate, TxRequisition.ClosedDate ) as DeliveryTimeDays,
TxRequisition.ActualDuration/10 as TotalTime, /* b

Mihir Mihir
Level 1
Level 1

Doug,

txActivity, txProcess and txRquisition tables should help you get all the above information.

 txActivity.PerformerID - Actual Approver

txActivity.StartedOn - Date Started

Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: