USP_DATALIST_RELATIONSHIPCONFIGURATION

CREATE procedure dbo.USP_DATALIST_RELATIONSHIPCONFIGURATION 
(
  @INCLUDEINACTIVE bit = 0
)
as
  set nocount on;

  declare @SHOWSTUDENTFLAG bit = 0;

  if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('f5ac53c4-d0ce-4e20-bca6-aacdfc01b302') = 1
    set @SHOWSTUDENTFLAG = 1;

  select    
    RELATIONSHIPTYPECODE.ID,
    RELATIONSHIPTYPECODE.DESCRIPTION,
    RELATIONSHIPTYPECODE.ACTIVE,
    coalesce(GENDER, 'Any'),
    case
      when APPLIESTOINDIVIDUALS = 1 and APPLIESTOORGANIZATIONS = 0 and APPLIESTOGROUPS = 0 and (@SHOWSTUDENTFLAG = 0 or APPLIESTOSTUDENTS = 0) then 'Individual'
      when APPLIESTOINDIVIDUALS = 0 and APPLIESTOORGANIZATIONS = 1 and APPLIESTOGROUPS = 0 and (@SHOWSTUDENTFLAG = 0 or APPLIESTOSTUDENTS = 0) then 'Organization'
      when APPLIESTOINDIVIDUALS = 0 and APPLIESTOORGANIZATIONS = 0 and APPLIESTOGROUPS = 1 and (@SHOWSTUDENTFLAG = 0 or APPLIESTOSTUDENTS = 0) then 'Group/Household'
      when APPLIESTOINDIVIDUALS = 0 and APPLIESTOORGANIZATIONS = 0 and APPLIESTOGROUPS = 0 and (@SHOWSTUDENTFLAG = 1 and APPLIESTOSTUDENTS = 1) then 'Student'
      else 'Multiple' end APPLIESTO,
    case
      when RELATESTOINDIVIDUALS = 1 and RELATESTOORGANIZATIONS = 0 and RELATESTOGROUPS = 0 and (@SHOWSTUDENTFLAG = 0 or RELATESTOSTUDENTS = 0) then 'Individuals'
      when RELATESTOINDIVIDUALS = 0 and RELATESTOORGANIZATIONS = 1 and RELATESTOGROUPS = 0 and (@SHOWSTUDENTFLAG = 0 or RELATESTOSTUDENTS = 0) then 'Organizations'
      when RELATESTOINDIVIDUALS = 0 and RELATESTOORGANIZATIONS = 0 and RELATESTOGROUPS = 1 and (@SHOWSTUDENTFLAG = 0 or RELATESTOSTUDENTS = 0) then 'Groups/Households'
      when RELATESTOINDIVIDUALS = 0 and RELATESTOORGANIZATIONS = 0 and RELATESTOGROUPS = 0 and (@SHOWSTUDENTFLAG = 1 and RELATESTOSTUDENTS = 1) then 'Students'
      else 'Multiple' end RELATESTO,
    case
      when EMPLOYEECORPORATERELATIONSHIPTYPE.ID is not null then 1
      else 0 end ISEMPLOYEEERLATIONSHIPTYPE,
    ISPEERRELATIONSHIPTYPE
  from
    dbo.RELATIONSHIPTYPECODE
  left join 
    dbo.RELATIONSHIPCONFIGURATION on RELATIONSHIPCONFIGURATION.ID = RELATIONSHIPTYPECODE.ID
  left join
    dbo.EMPLOYEECORPORATERELATIONSHIPTYPE on EMPLOYEECORPORATERELATIONSHIPTYPE.RELATIONSHIPTYPECODEID = RELATIONSHIPTYPECODE.ID
  where 
    (@INCLUDEINACTIVE = 1 or RELATIONSHIPTYPECODE.ACTIVE = 1)
  order by
    case when dbo.UFN_GETLISTSORTMETHOD('RELATIONSHIPTYPECODE') = 1 then RELATIONSHIPTYPECODE.SEQUENCE else 0 end,
    RELATIONSHIPTYPECODE.DESCRIPTION;