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

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

Enter the SQL query

Enter the SQL query