Find users with a particular Service Provider

Please sign in to leave a comment.

• 

  Travis Depuy

  Hey Preetha,
  Let's try that again. I remembered another client was looking for similar information but for the protocols. You might be able to tweak this to show you the service providers:
  /*
  These are some queries to determine what Service and Protocol Providers are in use (or not in use) by users.
  This was written for MS SQL, but could be ported to Oracle with ease.

  Tables:
  Service Providers: ORG_SVC_PROVS
  Service Provider Protocol Mapping: ORG_SVC_PROV_PROTS
  Protocols: PROV_PROTS
  
  Devices: DVC
  */
  
  /* Unused Protocol Providers */
  
  select h_osp.name as 'Service Provider Name', h_osp.DESCRION as 'Service Provider Desc', h_prov.NAME as 'Protocol Name', h_prov.DESCRION as 'Protocol Desc' 
  from xmuser.PROV_PROTS as h_prov, XMUSER.ORG_SVC_PROVS as h_osp, XMUSER.ORG_SVC_PROV_PROTS as h_ospp
  WHERE h_osp.ORG_SVC_PROV_ID = h_ospp.ORG_SVC_PROV_ID and h_ospp.PROV_PROT_ID = h_prov.PROV_PROT_ID and h_prov.PROV_PROT_ID not in (
  
      select pprts.PROV_PROT_ID
      from xmuser.dvc, xmuser.org_svc_provs as osp, xmuser.ORG_SVC_PROV_PROTS as ospp, XMUSER.PROV_PROTS as pprts, xmuser.RECIPIENTS
      where dvc.ORG_SVC_PROV_ID = osp.ORG_SVC_PROV_ID and osp.ORG_SVC_PROV_ID = ospp.ORG_SVC_PROV_ID and ospp.PROV_PROT_ID = pprts.PROV_PROT_ID and dvc.person_id = RECIPIENTS.RECIPIENT_ID 
  
  )
  
  
  /* Used Protocol Providers*/
  
  
  select RECIPIENTS.TARGET_NAME, dvc.descrion as 'Device Description', 
         osp.name as 'Service Provider Name', osp.descrion as 'Service Provider Desc',
         pprts.NAME as 'Protocol Name', pprts.DESCRION as 'Protocol Desc'
  from xmuser.dvc, xmuser.org_svc_provs as osp, xmuser.ORG_SVC_PROV_PROTS as ospp, XMUSER.PROV_PROTS as pprts, xmuser.RECIPIENTS
  where dvc.ORG_SVC_PROV_ID = osp.ORG_SVC_PROV_ID and osp.ORG_SVC_PROV_ID = ospp.ORG_SVC_PROV_ID and ospp.PROV_PROT_ID = pprts.PROV_PROT_ID and dvc.person_id = RECIPIENTS.RECIPIENT_ID 
  

  February 10, 2015 01:21

  0
• 

  Preetha Mathew

  Hi Travis,

  I really appreciate your quick reply. I could make it work with a few tweaks.
  Thanks very much.
  Preetha

  February 10, 2015 04:14

  0