SQL Queries - CAST Knowledge Base - Queries on Links - How to get the Caller Id Callee Id Caller Name and Caller Id per Application

Purpose of Query

This page provides a query to list the link details like Caller Id, Caller Name, Callee Id, Callee Name if you have more than an application present in the same Local Schema. 

Applicable CAST Version
Applicable RDBMS
Oracle Server(question)
Microsoft SQL Server(question)
Query for CSS

You can run the below query by giving the correct Local Base and Management Base details.

with ApplicationProjects as
select cpa.Object_Id as ApplicationId,
       cpa.Object_Name as ApplicationName,
       kp.IdKey as ProjectId,
       kp.KeyNam as ProjectName,
       tp.IdTyp as ProjectTypeId,
       tp.TypNam as ProjectTypeName
  from <MNGT Schema Name>.CMS_Portf_Application cpa
  join <MNGT Schema Name>.CMS_DynamicFields cdf
    on cdf.Object_Id = cpa.Object_Id
   and cdf.Field_GUID = 'entry'
  join <MNGT Schema Name>.CMS_Sync_Translation cst
    on cst.EntryObjPmc = cdf.Field_Value
  join <Local Schema Name>.UsrProRoot upr
    on upr.IdUsrPro = cst.IdObjService
  join <Local Schema Name>.ProDep pd
    on pd.IdProMain = upr.IdRoot
  join <Local Schema Name>.Keys kp
    on kp.IdKey = pd.IdPro
  join <Local Schema Name>.Typ tp
   on tp.IdTyp = kp.ObjTyp
select distinct ap.ApplicationId as "MB Application ID",
       ap.ApplicationName as "Application Name",
       kclr.IdKey as "Caller Id",
       kclr.KeyNam as "Caller Name",
       kcle.IdKey as "Callee Id",
       kcle.KeyNam as "Callee Name"
  from ApplicationProjects ap
  join <Local Schema Name>.Acc a
    on a.IdPro = ap.ProjectId
  join <Local Schema Name>.Keys kclr
    on kclr.IdKey = a.IdClr
  join <Local Schema Name>.Keys kcle
    on kcle.IdKey = a.IdCle
  order by 2
Query result example

Query result interpretation

This query will list the links created per application if there are more than one application present in the same Local base. 

Query for Oracle
Enter the SQL query
Query result example

Query result interpretation

Query for SQL server
Enter the SQL query
Query result example

Query result interpretation


Related Pages